【Excel实战】如何解决2维平面记录数据维度不足的问题？看这里

引子

2个思路：

1.是记录流水，每一个点占一行，引申到这么多指标分多列记录，要统计的时候我们可以用函数或者数据透视表；缺点是不直观，你还得额外生成一些统计表；

2.反其道而行之，以一个汇总的方式记录，这样显得直观，所见即所得，而且有利于共享操作，互不影响，问题是指标多了，平面的Excel该如何记录这么多参数？

前面一个问题好理解，我们今天的重点着墨于后一个问题。

BPF report

二维表，到底如何能尽可能多的表示更多的指标？

无外乎如下几种方式：

1.复合表头 ：

类似于上图的，Y轴栏位有产品线、启运港、供应商、型号名、数量以及柜量（集装箱），X轴记录时间，有日期和星期

X轴

Y轴

2.单元格格式：

格式就有太多了，恨不得菜单栏里面有的全部都可以，但是有的在视觉上表现力不是那么好，我们就有针对性的挑一些做点取舍（并不是不可能，只是效果不好）

• 颜色管理 ：这个在上上期的分享有提到过，比如仓位的3种状态按照红绿灯的模式管理，就可以定义3种状态。

我们可以定义更多的颜色，如下，我就定义了几十种颜色，表示不同的状态，每种状态给其定义特定的含义

color definition

比如，玫红代表未排产的，黄色代表排产计划下达，而且采取了订舱动作，而绿色代表仓位确认，介于中间的异常状态，我们可以用中间的过渡色表示，这样就一目了然了。一眼扫去，就知道哪些是OK的，哪些还有问题。

在供应链管理操作中，及时出货是个很重要的指标，而它要同时满足2个条件，1）产品能做出来；2）有工具能运出去，缺一不可。而在旺季，对仓位的把控有时候可能比生产要素更加重要。

颜色管理最重要的就是预定义。提前定义一套规则大家去遵守就行了，原则上有多少种颜色，就可以有多少指标，但是前提是颜色要容易区分。比如是常用的颜色，大家都主题色都一样，不然更新起来会比较麻烦，一个简单的办法就是大家直接刷定义的单元格颜色套用就好了，不用去颜色区识别选取。

• 字体

1. 加粗——比如加粗的 1 ，我用来定义delay的出货，用来表示上月计划未达成的；

2. 倾斜——我用来定义特殊的货代，比如我们刚签了一个，目前在trail run阶段，这时候需要标记格外关注；

3. 下划线——用来区分40尺平柜和高柜的区别，虽然在柜量统计上都是一个柜子，但是有时候费用上可能有差别，我需要单独记录HQ的数量。

• 边框

红色加粗的单元格边框——我用来定义非常重要的出货，必须按照计划出货，不能被甩柜，highlight出来是希望能够获得进出口部门的格外关照，提前跟carrier锁定仓位，保证不被roll。

我们来看看其他可能利用到的格式，ctrl+1进入单元格格式设置：

• 中划线是个不错的选项，视觉上比较明显；

• 边框的线型和颜色又可以做文章，就专门颜色可以扩展不少定义了；

• 填充样式还可以做做文章

3.单元格备注：

单元格备注主要就是备注的内容上动心思，这个也要提前预定义 标准来进行填写规范约束才行。

比如我要显示ETD,CARRIER,和note3个信息，我要求必须按照一定的规则来，如下图所示：

ETD:

By:

Re:

这样小组成员在更新的时候都能按照统一的格式要求来更新，方便日后的数据抓取。如果没有统一的要求，则后续文本信息的处理会很麻烦，从而失去定义多个指标的优势。

我们可以参照这个模式定义很多的指标，只要满足一定的规范即可，比如字段，冒号等。

基本上通过上述3种方式，我们可以定义很多的参数指标，一个小小的单元格就能极大的扩展信息的容量，从而为数据可视化提供支撑。

当然， 如果没有技术途径解决，说实话，上面那些设置可能到后面统计会成为让人头疼的地方。不会编程的建议用第一种方法（即先输入数据库后汇总的模式）。

有些统计可以用技巧、函数来实现，但是大部分只能通过 VBA编程来实现，上期的两个分享大家已经见识了。

---

下面进入今天的正题，在数据维护后，我要如何获取订舱数据的统计？

基本上这是第一种思路的实现，唯一的区别是用程序来抓出来数据

比如我们新建一个sheet叫booking summary，设计成数据库的形式，下面的数据需要通过VBA从上面的更新表中读取

代码如下 ：

code 1

分析：代码会读取黄色和绿色的单元格区域，因为这两个颜色定义代表了正在订舱和订舱确认了，最重要的信息来自于备注中，我们要用文本处理函数来获取这些信息，当然这个过程交由代码自动完成，代码本身很简单，没有什么好说的。

读取了信息后，我们需要对数据进行一个统计，因为我们的目的是跟logistics给的分配目标进行比对的，如果偏离目标太远，我们需要及时的纠正，调整我们的订舱策略。

假如下面是我们的目标，按照百分比分配的配额，我们要尽可能的靠近这个指标。

• Mar Allocation %

• COSCO 40%

• MAERSK 20%

• CMA 14%

• NYK 24%

• EVERGREEN 2%

下面是惯常使用的套路，在有了类似于数据库结构的数据清单后，我们大可以用数据透视表来快速的统计结果。

1.定义一个动态引用区域

我们使用名称，定义一个data区域，用offset的方式动态引用生成的数据区域，这个方式早已经司空见惯，大家要掌握，函数的知识我就不在这里进行扩充

2.建立chart的新sheet，用于创建数据透视表

这一步我们需要把上一步建立的data赋予数据透视表的data source，这样数据更新后数据透视表的源数据能自动更新

为了避免数据更新后需要手动刷新，我们加个工作表事件来自动帮我们刷新，代码可以录制宏来实现。在VBE环境下chart的代码区域输入下面代码，透视表名称新建的时候可以改，这里跟代码保持一致就行了。这样每次激活chart这个sheet，透视表会自动刷新数据。

code 2

3.生成数据透视图

百分比自然用饼图，我们把数据标签的百分比调出来

4.最后跟物流部门的指标进行对比

偏差太大的在后续订舱中要注意倾斜，慢慢靠拢，最终在月底的时候达成基本目标。

---

好了，写到这，不知道大家注意到没？我已经把思路一和二都展示了一遍，实际应用中我们很难说用到一个思路，往往是二者的结合，比如可视化我就得用第二种思路，在有了规范的数据集后，就不能简单事情变复杂了，这时候可以用思路一简化我们的工作，当然整个过程也可以完全代码化，这个需要取舍，看值不值，总之能熟练运用学到的技能来实现你最初设定的目标。

思路要宽泛，运用要灵活！

希望对大家有帮助！

---------------

运营：Excel365

本案例来自供应链管理工作实践