上周培训Excel课程的时候，学员小A说道：“甘老师，我手头上有两张明细表，一张是物料入库明细。”

“还有一张是物料出库明细，我现在是用VLOOKUP函数，根据物料编码查询出入库和出库的数量，相减之后计算算出每个物料的结余。现在的问题是两张表的数据特别多，每次处理这个表格的时候特别慢，请问有什么方法能优化这个表格？”

我们来观察一下这两张明细表格的结构，你会注意到前面13列的数据都是一样的，都是这个物料的属性数据。为什么小A在设计表格结构的时候，要在两张表格中都重复的输入一个物料的所有属性信息，显然这样的表格结构会造成大量的冗余数据，导致运算速度变慢。

记住Excel不是电子版的记事本，每张工作表也不应该孤立存在。要知道，它其实是一个小型的数据处理系统，以系统的思维看待它，方能领悟制表、用表的一般规律。

一个好的Excel表格的设计结构，最好是由三张工作表组成的：


第一张表：参数表

参数表里的数据可以等同于系统的配置参数，供源数据表和汇总表调用，属于基础数据，通常为表示数据匹配关系或者某属性明细等不会经常变更的数据。

像小A的这个出入库管理表，应该要建立一个物料参数表，记录下来每个物料的所有属性。这样在入库和出库明细表中，仅仅只需要输入这个物料的编号，就可以连接到物料参数表，找到需要查看的物料的任何一个属性。

▲参数表

在填写数据的时候，要留意数据的格式规范化。比如有效期这一列中输入的日期，有“2027.10.24”、还有“20251004”，这两个都不是规范的日期格式，在输入日期型数据的时候，一定要记得用-或/间隔年月日。如果已经输入了这种不规范的日期数据，可以利用“数据”选项卡中的“分列”功能转换成规范的日期格式。


第二张表：明细表

明细表，也叫源数据表，它等同于系统的录入界面。系统界面能让录入时的视觉效果更直观，但在系统里录入数据和在Excel里录入数据，本质是一样的，只不过在系统里表现为输入栏，而在Excel里表现为单元格。

Excel中一切与数据录入相关的工作，都在明细表中进行，我们的日常工作最主要就是做好明细表。为了让明细表可以隔行变色、冻结首行、公式自动填充、自动关联图表和透视表，请一定要记得在开始选项卡中为明细表套用表格格式。

正确的明细表应该满足以下条件：①一维数据；②一个标题行；③字段分类清晰；④数据属性完整；⑤数据连续；⑥无合并单元格；⑦无合计行；⑧无分隔行/列；⑨数据区域中无空白单元格；⑩单元格内容禁用短语或句子。

▲入库明细表



▲出库明细表



第三张表：汇总表

Excel工作的最终目的，是得到分类汇总结果，所以第三张表应该就是汇总表。

在企业系统中，操作员只需要进行简单的设置，就可以自动获得汇总报表。同理，Excel中的分类汇总表也可以自动获得，只要通过数据透视表就能得到。不过传统的数据透视表，数据源只能来自于一张工作表，而我们现在的数据源有三张表，这时候就需要用到“Power Pivot”这个超级透视表。

通过点击“Power Pivot”选项卡中的“添加到数据模型”，将数据加载到Power Pivot中，在“关系视图”中利用“物料编码”建立三张表的关联。

添加一个入库数量-领用数量的结余的度量值之后，点击“数据透视表”，创建数据透视表。

从三张表中选择需要的列，生成每个物料的入库、领用和结余数量。

以后明细表的数据增加了，只需要在任何一张透视表上右键-刷新，所有透视表都将自动更新。

Excel功能再强大，如果数据结构设计不合理，数据内容不规范，再强大的工具也将束手无策。面对不断变化的业务模式所带来的挑战，我们要善用工具提高效率才能从容应对挑战，并以最大限度进行创新！