有些系统化做的一般的公司，业务系统数据无法直接用于分析。通常需要业务员结合实际情况调整系统导出的数据并分别填表，再由一位同事进行统一汇总。因此本期我们来分享一下Excel多文件汇总。

1.基础数据结构

我们先看下每个sheet大致的数据结构

每个工作簿有2~3个sheet，每个sheet的结构如下：

列字段为每个月份具体的数据以及合计数

行字段为不同的数据项目，包括：销售数据、采购数据等等

2.汇总要求

（1）把不同工作簿的工作表合并到一个工作簿

把业务员李二、刘六、李四、李五提交的这4份独立Excel，合并到一个工作簿

（2）新建一张标准汇总表

格式如下：

行：所有产品（产品A、产品B…）

列：固定项目（含税/无税销售、含税/无税采购，分2025/2026）

值：自动取数、自动统一单位等

其他要求：

25年数据为全年合计数，26年数据为分析当月的数据

二、完整汇总流程

1. Step1：使用VBA合并文件到一个工作簿

（1）Step1.1：新建空白Excel

命名为：汇总表

同时将汇总表另存为.xlsm格式，即：Excel启用宏的工作簿

之后删除原.xlsx文件

（2）Step1.2：新建文件夹

【注意】把待汇总文件+汇总表，都放到同一个文件夹

文件夹名称没有要求，但是尽量规范易识别

（3）Step1.3：粘贴VBA代码并运行

VBA的常规操作流程：

在.xlsm文件中同时按：ALT+F11（笔记本电脑可能还需要按FN）

会出现如下界面

在插入选项卡的下拉菜单中点击模块，并输入代码

完整代码如下：

Sub 合并文件()

    Dim fso As Object, fd As Object, file As Object

    Dim wb As Workbook, ws As Worksheet

    Application.ScreenUpdating = False

    Set fso = CreateObject(“Scripting.FileSystemObject”)

    Set fd = fso.GetFolder(ThisWorkbook.Path)

    For Each file In fd.Files

        If file.Name Like “*.xlsx” And UCase(file.Name) <> UCase(ThisWorkbook.Name) Then

            Set wb = Workbooks.Open(file.Path, ReadOnly:=True)

            For Each ws In wb.Sheets

                ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

            Next

            wb.Close False

        End If

    Next

    Application.ScreenUpdating = True

    MsgBox “完成”

End Sub

（4）Step1.4：运行宏

按“ALT+F5”运行宏

所有sheet自动进入总表，结果如下：

2.提取工作表名，作为汇总表的产品行

（1）Step2.1：把sheet1改名为汇总总表

因为工作簿“汇总表”新建时自带了一个空白sheet：sheet1，所以只需要改名即可

注意此处改名汇总总表是为了和如下VBA代码中的“汇总总表”对应，因此如需重命名成其他名称，需要对应修改VBA代码

（2）Step2.2：用VBA代码提取所有sheet名

VBA使用方式同前，VBA代码如下：

Sub 提取表名()

    Dim i As Integer

    Sheets.Add(Before:=Sheets(1)).Name = “汇总总表”

    For i = 2 To Sheets.Count

        Cells(i – 1, 1) = Sheets(i).Name

    Next

End Sub

（3）Step2.3：运行后，A列自动生成所有sheet名称

3.Step3：清洗名称-汇总表&明细sheet

因为有人在sheet名加了姓名，必须标准化：

（1）Step3.1：汇总表-按分隔符号分列

选中汇总表A列->数据选项卡->选择分列->选择按分隔符号

（2）Step3.2：汇总表-选择分隔符号

在分隔符号中的其他中，输入“-”

（3）Step3.3：汇总表-忽略列

点击下一步，选中“刘六”对应列，选择“不导入此列（跳过）”

点击“完成”，结果如下：

（4）Step3.4：修改对应明细sheet的名称

右击sheet名->点击重命名->删除“-刘六”

4.Step4：搭建汇总总表表头

复制明细表中的项目，并使用粘贴的转置功能，粘贴到汇总总表的列上

结果如下：

5.Step5：取数：INDEX+INDIRECT

（1）标准取数公式（以B2单元格为例）

=INDEX(INDIRECT($A2&"!N:N"),MATCH(B$1,INDIRECT($A2&"!A:A"),0))

其中：N列对应各明细表的合计列，A列为各明细表的具体项目列

（2）公式注意事项

由于2026年取的是当月数，故需要相应调整上述公式中的N列至分析当月，需要每月调整公式

=INDEX(INDIRECT($A2&"!D:D"),MATCH(F$1,INDIRECT($A2&"!A:A"),0))

更推荐配合对应月份参数来设计公式，但是需要每月修改月份参数：

=INDEX(  INDIRECT($A2&"!A:N"),  MATCH(F$1, INDIRECT($A2&"!A:A"), 0),  MATCH($L$2, INDIRECT($A2&"!1:1"), 0))

（3）公式拆解

（4）为什么一定要用INDEX    

如果直接用“=具体的单元格”，一旦有人随意删行/插行/改变行位置，直接引用，数据会有问题，必须用INDEX按名称精准定位

6.Step6：结果数据清洗

（1）处理#N/A报错

匹配函数（比如XLOOKUP/INDEX/MATCH）中的#N/A报错通常是双方的“配对值”不一样导致的

我们定位到产品A/产品B/产品C对应的明细表，发现项目名称中，2026年的采购数据被人为修改成了“采购额”，而非“采购金额”，导致匹配不一致

对此，我们逐一修改明细表中的数据即可

我以产品A为例进行修改

大家可以参考上述步骤，尝试修改产品B/产品C对应的项目名称

（2）沟通数据单位差异

都汇总完之后，财务分析/财务BP通常需要对数据的质量再次进行检查。此时我们发现产品G/H/I数字特别小，就需要和对应业务员李五沟通原因

【情形一】统一数据单位至元

李五反馈其使用万元为单位进行填写，此时需要和李五沟通更新成单位元

【情形二】统一数据单位至万元

问题：有人填元（数据量级百万级），有人填万元（数据量级100以内）。

方法：用IF自动判断，统一为万元

（3）调整产品顺序

现在的汇总总表，产品名称是乱序的，需要调整成和字母表顺序一致，使用自定义排序功能：

全选表格->开始->筛选/排序->自定义排序

三、建立数据规范

截止到现在，我们所有汇总的工作才全部结束。给人的感觉是“就这么几行数、几张表，搞了好半天”，根本原因就是没有建立前置的数据规范。

如果有规范的环境，我们就可以省去上面的清洗过程；如果基层员工工作不细致或者有其他客观原因，上面的清洗过程还是要财务的小伙伴们学会并在实际工作中应用。

建立数据规范，包括2方面内容：一是前置要求；二是文件管理规范

1.前置要求-要求业务员

每次群发，通知到位：

每个文件只放对应产品工作表，不夹带无关表格

sheet名统一为：产品+字母，如：产品A，不加姓名、不加符号

文档名统一为：业务数据 – 姓名，如：业务数据 – 李五

第一行列名严禁修改：1月-12月、合计

项目名称严禁修改：严禁插行、删行、合并单元格

金额统一填写万元，自然保留小数即可

2.文件管理规范-要求财务

（1）文件命名及保存

每次汇总放在同一文件夹，文件（夹）命名要清晰可读，保持前后一致

（2）对填写数据设置数据验证

需要根据实际业务情况，规范填写范围

并在“数据”选项卡中的“数据验证”进行设置

（3）对模板框架加密

加密的范围包括行和列

全选工作表，按“CTRL+1”设置单元格格式，在“保护”菜单中，取消勾选“锁定”；再选择需要加密的范围，按“CTRL+1”设置单元格格式，在“保护”菜单中，勾选“锁定”

之后进入“审阅”选项卡，点击保护工作表，设置密码并记住密码

结果如下：

修改单元格时，会报出如下提示，没有密码无法修改单元格

【注意】必须要先设置好其他内容，最后再对工作表进行保护

【本期总结】

本期主讲如何用EXCEL的VBA及各类公式汇总多个工作簿的同格式数据。

建议大家一定要实际操练起来，多做几次之后，汇总工作就会做的越来越快。更建议大家能前置性地做好数据规范工作，助力汇总提效。

可以后台私信我获取上述报表文件和VBA代码

【下期预告】

下期讲讲INDIRECT函数