化繁为简 高效制作自动化报表

俞木发

制作综合报表时，我们经常要汇总多个工作表的数据。常规的方法是打开指定文件，然后将多个报表数据复制粘贴到一个表中，最后再进行计算汇总。由于涉及的数据较多，这样操作不仅麻烦，而且很容易出错。借助恰当的公式，则可以高效、自动地完成。

案例介绍

公司技术部门每个月都要求制作一个复核报表，内容是对质检部的检验结果进行复核。日常的检验报表是每个部门各自独立制作一份工作簿文件，最终要按特定格式汇总在“抽检复核”报表中（图1）。抽检复核报表格式和每个子表并不同，因为公司的产品型号非常多，每个月技术部只是对每个部门随机抽取若干个品名，然后根据已有的结果进行复核（图2）。以前的做法是，每个月收集好各个车间统计数据，然后技术部确定随机品名后再从中复制指定数据到报表中供复核比对，由于每个车间报表数据很多，抽检的品名又不固定，手工操作很费事。现在进行改良，需要借助公式高效自动完成。

首先来分析复核报表的要求，需要复核时由技术部在B2：B8单元格根据生产部门品名，随机输入对应的复核品名如G1，然后在C2：D2区域依次填入“一车间xls”文件中和G1对应的数据，如果随机输入的是二车间的品名，则填入对应的“二车间xls”文件中的数据。也就是说要在复核报表文件中引用不同报表文件中对应的数据，这里可以借助VLOOKUP函数进行引用完成。

因为最终目的是要自动生成，为了方便对工作簿中工作表的引用，要求每个单位制作的工作簿的名称要和最终复核报表中部门的名称一致，工作表名称则统一使用默认的Sheet1。如一车间的报表名称要为“一车间.xlsx”，打开后其工作表的名称为Sheet1，其他工作簿的要求类似（图3）。

接着在自己的电脑上新建一个名为“K：＼检验报表文件”的目录，右击将其设置为“共享”，并且将共享目录对Everyone设置读写权限，也就是企业内部局域网的用户都可以访问该共享目录（图4）。

完成上述要求后，以后每个部门完成自己的报表后只要上传到该目录中保存。打开“抽检复核”报表定位到C 3，输入公式“=VLOOKUP（B3，INDIRECT（"["&A3&".xlsx] sheet1！A2：D27"），2，FALSE）”并下拉，这样即可完成对各个工作簿数据的引用。

公式解释

B3的值是搜索内容，即从每个报表中查找出对应的品名，这里是随机选择每个单位品名数值。

A2：D27则为每个报表的数据区域，即从这个区域里找出符合要求的数据，因为这里是需要从各个子表中查找数据，而每个子表的数据范围可能不同，为了确保数据都可以找到，这里需要以数据范围最广的子表为准。

Sheet1是每个工作簿对应的工作表的名称，这里统一引用第一个工作表的数据。

"["&A3&".xlsx]是一个文本函数，将A3单元格的内容和“.xlsx”字符串联合起来，组成一个新字符串。因为A列单元格的内容是和对应工作簿的名称一致的，这样通过文本函数的联合，就可以实现对不同工作簿的引用。这里注意报表文件都要放置在同一个目录，工作簿文件名要用[]引用，工作表则用“！”引用。

INDIRECT：通过上述文本函数，INDIRECT可以实现对工作簿文件的动态引用，并将这个引用作为Vlookup函数查找范围。

2表示引用子表中的2列的内容即产量的数值。FALSE表示查找时要求完全匹配。

定位到C3输入公式并下拉“=VLOOKUP（B3， INDIRECT（"["&A3&".xlsx]sheet1！A2：D27"）， 3 ， F A L S E ）”，定位到D 3输入公式并下拉“=VLOOKUP（B3，INDIRECT（"["&A3&".xlsx] sheet1！A2：D27"），4，FALSE）”，依次引用工作簿的数据。

完成上述操作后，如果技术部需要使用这个报表进行复核，只要全选“K：＼检验报表文件”目录下所有xlsx文件回车打开，这样在“抽检复核.xlsx”的A2开始随机输入一车间对应的品名，输入数值后即可自动进行引用，最终只要在复核结果輸入复核的数值进行比对，报表即可自动生成了（图5）。

以后每个部门只要完成报表的输入后传输到共享目录（文件名不要更改），打开“抽检复核.xlsx”随机输入检测品名，即可快速生成所需的报表了，各个工作簿数据的更改会自动被同步引用（图6）。