Excel 2010实战：数据清洗、透视表与VBA自动化全链路

很多团队的技术栈已经换了两三轮，但打开财务、生产、仓储、采购这些部门的电脑，装的还是 Excel 2010。不是他们不想升级，而是老系统导出的报表格式、十年前写好的宏、跟 ERP 对接的模板都绑在这套环境里，一动就出问题。于是出现一个很尴尬的局面：想学的人找不到像样的教程，网上搜到的例子要么版本对不上，要么只贴一段公式不讲上下文，要么直接跳到 Office 365 的新函数，在 2010 里根本敲不出来。

这篇文章想解决的就是这件事。我不打算把 Excel 2010 当成一个怀旧对象来写，而是把它当成一套还在生产环境里跑的工具，用五个能直接落地的实例，把数据规范化、数据透视表、函数公式、VBA 自动化、Power Query 清洗这条链路完整走一遍。每个实例都给可复制的代码或公式，每个环节都说清楚为什么这么做、做错会怎样。

先给一个明确判断：Excel 2010 真正的瓶颈从来不是"版本老、功能少"，而是绝大多数人把它当成电子记事本在用——数据随手合并单元格、表头拆成两行、明细和汇总混在一张表里、每个月的报表靠手工复制粘贴。这些问题在 Excel 365 里同样存在，只是新版给了你更多补救工具，让你误以为是版本问题。把数据结构理顺，Excel 2010 完全能撑起中小规模的报表自动化。

所以这篇文章适合三类读者：一是维护老旧 Excel 报表、必须留在 2010 环境里的业务支持岗；二是要给业务部门做数据工具、又拿不到数据库权限的开发；三是刚开始学 Excel 自动化、想找一个能跑通的完整范例的新手。读完之后，你至少能独立完成"原始台账导入、清洗、汇总、自动出表"这一整条流程。

1. 为什么还要认真对待 Excel 2010

1.1 它的能力边界比很多人以为的宽

先纠正一个流传很广的误解：很多人觉得 Excel 2010 只能处理几万行数据，其实从 Excel 2007 开始，单张工作表的行数上限就已经是 1,048,576 行、列数上限是 16,384 列，2010 沿用同一套规格。真正限制你的不是行数，而是普通公式在几十万行上的重算速度，以及文件体积膨胀之后打开变慢。换句话说，十万行以内的明细做透视和条件汇总，Excel 2010 是够用的，前提是你别用整列引用（比如 A:A）去写 VLOOKUP。

更值得注意的是，Excel 2010 恰恰是几个关键功能的起点。切片器（Slicer）就是 2010 引入的，在这之前筛选透视表只能一个个点下拉框，没法做那种点一下就联动的按钮面板；迷你图（Sparklines）也是 2010 新增的，可以在一个单元格里画出趋势线；64 位版本的 Office 同样是 2010 才正式提供，这让大内存机器跑大数据量文件成为可能。所以从功能演进的角度看，2010 是一个分水岭版本，它之后的 2013、2016 更多是在这个基础上做体验优化和新增函数。

这也解释了一个现象：为什么很多企业级报表模板在 2010 上能跑，到了某些精简版或者 WPS 上反而出问题。因为它用到了切片器、表格结构化引用、Power Pivot 这些在当年属于"新特性"的东西，兼容性依赖比较深。

1.2 真正的成本在哪里

我把实际项目里遇到的开销归成三类。第一类是数据不规范带来的重复劳动：源数据里合并单元格、多行表头、单位混在数字里、日期是文本格式，每次做汇总都要手工收拾一遍。第二类是流程靠人肉串联：每月从系统导出十个文件，手工复制到一个总表，再手工分部门拆出去发邮件，中间任何一步出错都要重来。第三类是知识断层：写宏的人离职了，宏还在跑，没人敢改，出问题只能全表回滚。

这三类成本里，第一类和第二类是完全可以用本文讲的方法消掉的，第三类则需要在工程习惯上做约束，比如模块化、注释、版本留档。而后面的实例，就是按照"先解决数据规范、再解决汇总、最后解决自动化"这个顺序组织的，因为顺序错了会很痛苦。

2. 核心概念与能力边界

2.1 几个必须分清的层级

Excel 的对象模型是分层的：最外层是工作簿（Workbook，对应一个 .xlsx 或 .xlsm 文件），里面可以有多个工作表（Worksheet），工作表由单元格（Range/Cells）组成。写 VBA 的时候如果搞混这三层，最常见的报错就是"下标越界"或者"对象变量未设置"。

再往上一层有一个容易被忽略的概念：表格（Table，在界面上叫"套用表格格式"）。它和普通数据区域看着差不多，但本质不同。普通区域只是一个坐标范围，插入行之后公式引用不会自动扩展；表格是一个有名字的对象，插入行、加列、写公式都会自动扩展，而且可以用 表名[列名] 这种结构化引用代替 $A$2:$A$1000。这一点在后面的实例里会反复用到，因为它直接决定了你的公式是"写一次管一辈子"还是"每月改一次"。

还有一个概念是宏（Macro）和 VBA 的关系。宏是一段录制或编写的自动化脚本，VBA 是写这段脚本的语言。Excel 2010 里的 VBA 版本是 7.0，语法和现在差别不大，你从网上找到的大部分 VBA 代码稍作调整就能用。但要注意，含宏的文件必须保存成 .xlsm 格式，保存成 .xlsx 会直接丢掉代码，而且关闭时不会有任何提醒。

2.2 五类工具的分工

很多人学 Excel 卡住，是因为不知道什么问题该用什么工具解。下面这张表是我自己在用的判断依据，可以当成一张选型速查表。

这张表的核心意思是：能用透视表解决的，别写公式；能用 Power Query 解决的，别写 VBA；只有当操作涉及"打开文件、保存文件、发邮件、改格式"这类动作时，才轮到 VBA 出场。反过来，如果你用 VBA 去循环几十万行做加减乘除，那基本等于自己给自己挖坑，跑一晚上都不一定出结果。

2.3 2010 版本的功能缺口

说清楚边界也要说清楚缺口，免得读者按着新版教程踩空。Excel 2010 原生不支持动态数组（FILTER、UNIQUE、SORT、XLOOKUP 这些都是后来的版本才有的），所以新表里的查找还是得用 VLOOKUP 或 INDEX+MATCH；不支持 TEXTJOIN，多条件拼接要靠 & 或者自定义函数；Power Query 在 2010 里是独立加载项，不是内置功能，需要单独安装；Power Pivot 同理。

知道这些缺口有什么用？用处在于，当你搜索到一个漂亮的新函数写法时，能立刻判断出它在 2010 上会不会报 #NAME?，而不是花半小时调试一个根本不存在的函数。

3. 环境准备与前置条件

3.1 基础环境确认

动手之前先确认三件事，这几步花不了五分钟，但能省掉后面大量莫名其妙的报错。

第一，确认 Office 版本和位数。打开 Excel，点"文件 - 帮助"，能看到版本号和是否 64 位。这一步很关键，因为 32 位和 64 位在调用 Windows API 时写法不同，网上大量老代码用了 Declare Function 而没有加 PtrSafe，在 64 位环境下会直接编译报错。如果不是必须，建议在 32 位环境里开发，兼容性最好。

第二，确认 Office 2010 已经打上 SP2 补丁。SP2 修复了大量稳定性问题，尤其是涉及透视表和数据连接的场景。

第三，确认文件保存位置有写入权限，并且不在网络盘上直接运行宏。网络盘路径经常在 Workbooks.Open 时出现锁文件冲突，这一点在合并多文件的场景里特别明显。

3.2 打开开发工具选项卡

Excel 2010 默认不显示"开发工具"选项卡，需要手动开。路径是：文件 - 选项 - 自定义功能区，在右侧主选项卡列表里勾选"开发工具"。打开之后你才能看到"宏""Visual Basic""录制宏""宏安全性"这些入口。

同时要把文件另存为"Excel 启用宏的工作簿（.xlsm）"。这一步不要偷懒，先另存再写代码，否则你写完一段代码，点了保存，关掉，再打开发现代码没了，那种感觉非常糟糕。

3.3 宏安全设置

路径是：开发工具 - 宏安全性。这里有四个选项：

• 禁用所有宏，并且不通知：最安全，但你的宏完全跑不了。
• 禁用所有宏，并发出通知：推荐设置，打开文件时顶部会出现黄色提示条，点"启用内容"才运行。
• 禁用所有宏，除了数字签名的宏：适合企业统一分发。
• 启用所有宏：不推荐，尤其是你会打开别人发来的文件时。

实际项目里我建议用第二项，并且配合"受信任位置"。把存放宏文件的目录加到受信任位置（文件 - 选项 - 信任中心 - 信任中心设置 - 受信任位置），这样该目录下的文件打开时不会弹提示，而其他来源的文件依然受保护。这是一个在安全性和便利性之间比较平衡的做法。

3.4 安装 Power Query 加载项

Power Query 在 Excel 2010 里是一个需要单独下载安装的微软官方加载项。安装完成后，功能区会多出一个"Power Query"选项卡。如果装完之后没看到，按这个顺序排查：先看"文件 - 选项 - 加载项"，把下方的"管理"切到"COM 加载项"点"转到"，确认对应项被勾选；如果还是不行，检查 .NET Framework 版本是否满足要求。

需要提醒的是，2010 版本能装的 Power Query 版本相对较老，部分较新的 M 函数（比如一些日期和文本处理函数）可能不可用。写 M 代码时如果遇到函数名报错，先去查这个函数是哪个版本引入的，别怀疑自己写错了。

4. 实例一：把杂乱台账变成可分析的数据表

4.1 一维表原则

这是整篇文章里最重要的一条，比后面任何一个技巧都重要：数据表必须是一维的。

什么叫一维？每一列只表达一个字段，每一行只表达一条记录，第一行是且仅是一行表头。举个反面例子，很多台账长这样：第一行是"2024年销售台账"，第二行是"部门、一月、二月、三月……"，然后把部门写在第一列，每个月的数据横向排开。这种表人看着舒服，但它对透视表、对函数、对任何自动化都是灾难，因为"月份"这个维度被拆到了列名里。

正确的做法是做成三列：部门、月份、金额，每个月一行。表变长了，但所有工具都能直接用。这个转换过程叫"逆透视"，手工做很痛苦，用 Power Query 一条命令就能完成，具体在第七节讲。

4.2 清洗的四个动作

拿到一份原始数据之后，按下面顺序处理，基本能解决 90% 的问题。

第一步，删掉完全空白的行和列，还有那些为了"美观"插进去的空行分隔线。判断方法是选中数据区域，用 Ctrl+G 定位空值看一眼。

第二步，拆掉合并单元格。合并单元格是 Excel 自动化的头号杀手，因为合并区域里只有左上角那个单元格有值，其他位置读出来都是空。拆的方法是选中区域，取消合并，然后 Ctrl+G 定位空值，输入 = 加上方单元格，按 Ctrl+Enter 批量填充。

第三步，处理文本型数字和文本型日期。判断依据是看有没有绿色小三角，或者用 =ISNUMBER(A2) 测试。文本型数字转数值可以用"分列"功能直接点完成，或者用 =VALUE()；文本型日期用"分列"里指定日期格式最稳。

第四步，统一单位。把"万元""元""千元"这种混在数字里的单位拆成单独一列，否则你永远算不对汇总。

4.3 套用表格格式并命名

清洗完之后选中数据区域，按 Ctrl+T 套用表格格式，勾选"表包含标题"。然后在"表格工具 - 设计"里把表名改成一个有意义的名字，比如 tblSales。命名有两个约定值得坚持：一是加 tbl 前缀，一眼就能区分表格和普通区域；二是不用中文和空格，因为结构化引用里带空格要加特殊语法，容易出错。

命名之后，你的公式就可以写成这样：

excel复制=SUMIFS(tblSales[金额],tblSales[部门],$A2,tblSales[日期],">="&DATE(2024,1,1))

这行公式的意思是：在 tblSales 这张表里，对"金额"列求和，条件是"部门"列等于 A2 单元格的值，并且"日期"列不早于 2024 年 1 月 1 日。注意这里没有写任何行号，以后表格增加行，公式自动覆盖新数据，不用改。

对比一下老写法 =SUMIFS(Sheet1!$F$2:$F$1000,Sheet1!$C$2:$C$1000,$A2,...)，问题在于 1000 行一旦被突破，结果就少算了，而且这种错误非常隐蔽，因为公式不报错，只是数字偏小。这就是我前面说的"写一次管一辈子"和"每月改一次"的区别。

4.4 用数据验证做下拉

数据验证（旧称数据有效性）在 2010 里的位置是"数据 - 数据验证"。最常用的场景是给"部门""状态"这类字段做下拉列表，防止手工输入出现"销售部"和"销售部门"这种同义不同字的情况。

做法是先把可选项写在一个单独的工作表里（比如叫 参数），选中那一列并命名成 lstDept，然后回到明细表，选中要限制的列，数据验证里选"序列"，来源填 =lstDept。这样下拉选项就跟着参数表走了，加一个部门只需要在参数表里加一行。

这一步看着不起眼，但它是保证数据质量的成本最低的手段。很多汇总出错，根子上就是录入环节的自由度太大。

5. 实例二：数据透视表做多维度汇总

5.1 建透视表的正确姿势

选中表格里任意单元格，插入 - 数据透视表，数据源会自动带上表名。这里有个细节：如果你用的是表格（而不是普通区域），后续表格新增数据，透视表只需要刷新就能包含新行；如果用的是固定区域，就得手动改数据源范围。这是选择表格而不是普通区域的又一个理由。

放置位置建议放在新工作表，不要放在明细表旁边。原因很实际：透视表刷新之后行数会变，如果旁边还有别的数据，很容易被覆盖或者挤压变形。

字段拖放的基本逻辑是：行放维度（部门、产品、月份），列放次要维度（比如年份），值放度量（金额、数量）。想改汇总方式，右键值字段 - 值字段设置，可以切求和、计数、平均值、最大值等。

5.2 切片器带来的交互变化

切片器是 2010 版本值得单独说一句的功能。选中透视表，在"数据透视表工具 - 选项"里点"插入切片器"，勾选你要筛选的字段，就会出现一个按钮面板，点一下即时筛选。相比下拉框，切片器的好处是当前选中了哪些值一目了然，而且可以同时连到多个透视表上——如果你做了四张透视表分别看部门、产品、月份、客户，把同一个切片器连到这四张表上，点一下就全部联动。

连接多个透视表的操作是：右键切片器 - 数据透视表连接，勾选需要联动的表。这个功能在仪表板场景里几乎是标配，而很多人不知道 2010 就有。

5.3 计算字段与刷新陷阱

透视表里的"计算字段"可以在汇总结果上做二次运算，比如用"金额"除以"数量"得到均价。路径是：透视表工具 - 分析 - 字段、项目和集 - 计算字段。要注意计算字段是在汇总结果上算的，不是逐行算完再汇总，所以平均值类的指标用计算字段可能和逐行计算的结果有细微差异，涉及除法的场景要特别小心分母为零。

刷新是另一个高频坑。透视表不会自动跟着源数据变化，手工刷新是右键 - 刷新，或者按 Alt+F5。如果希望打开文件时自动刷新，可以在"数据透视表选项 - 数据"里勾选"打开文件时刷新数据"。但要注意，如果源数据来自外部连接，打开时刷新会触发连接，速度会慢，这时更稳妥的做法是给一个手动刷新的按钮，配合下面这段 VBA：

vba复制' 文件路径：当前工作簿的模块中
Sub RefreshAllPivot()
    Dim pt As PivotTable
    Dim ws As Worksheet
    Dim count As Long
    
    Application.ScreenUpdating = False
    
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
            count = count + 1
        Next pt
    Next ws
    
    Application.ScreenUpdating = True
    MsgBox "已刷新 " & count & " 张透视表", vbInformation
End Sub

这段代码遍历所有工作表的透视表逐个刷新，最后告诉你有多少张。之所以加 ScreenUpdating = False，是因为刷新过程中屏幕会闪烁，数据量大时非常影响观感，关掉之后速度也会快一些。

5.4 格式保持问题

透视表刷新后列宽变回去、自定义格式丢失，是老版本的通病。有一个不算完美但有效的做法：在"数据透视表选项 - 布局和格式"里勾选"更新时保留单元格格式"，同时在"自动调整列宽"上取消勾选。这样刷新之后列宽和填充色基本能保住，但数值格式（比如千分位）还是会丢，需要在字段设置里单独设。

如果对格式要求很高，建议把透视表当成计算层，最终展示交给另一个工作表用公式引用，或者用 VBA 在刷新结束后统一刷一遍格式。

6. 实例三：VBA 批量自动化

6.1 合并文件夹下所有工作簿

这是实际工作中需求最强烈的场景：每个月各部门交上来十几个结构相同的表，要合并成一张总表。手工复制粘贴既慢又容易错。

先约定目录结构：把当前工作簿放在一个文件夹里，同级的 source 子文件夹放待合并的文件，汇总表放在名为"汇总"的工作表里，第一行是表头。

vba复制' 文件路径：合并工具.xlsm 的标准模块 Module1
Option Explicit

Sub MergeAllWorkbooks()
    Dim fso As Object
    Dim srcFolder As Object
    Dim f As Object
    Dim wbSrc As Workbook
    Dim wsSrc As Worksheet
    Dim wsDst As Worksheet
    Dim folderPath As String
    Dim dstRow As Long
    Dim lastRow As Long
    Dim lastCol As Long
    Dim fileCount As Long
    
    Set wsDst = ThisWorkbook.Worksheets("汇总")
    folderPath = ThisWorkbook.Path & Application.PathSeparator & "source"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(folderPath) Then
        MsgBox "目录不存在：" & folderPath, vbExclamation
        Exit Sub
    End If
    Set srcFolder = fso.GetFolder(folderPath)
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each f In srcFolder.Files
        If LCase(fso.GetExtensionName(f.Name)) = "xlsx" _
           Or LCase(fso.GetExtensionName(f.Name)) = "xlsm" Then
            
            Set wbSrc = Workbooks.Open(f.Path, ReadOnly:=True)
            Set wsSrc = wbSrc.Worksheets(1)
            
            lastRow = wsSrc.Cells(wsSrc.Rows.Count, 1).End(xlUp).Row
            lastCol = wsSrc.Cells(1, wsSrc.Columns.Count).End(xlToLeft).Column
            
            If lastRow >= 2 Then
                dstRow = wsDst.Cells(wsDst.Rows.Count, 1).End(xlUp).Row + 1
                wsSrc.Range(wsSrc.Cells(2, 1), wsSrc.Cells(lastRow, lastCol)).Copy
                wsDst.Cells(dstRow, 1).PasteSpecial xlPasteValues
                fileCount = fileCount + 1
            End If
            
            wbSrc.Close False
        End If
    Next f
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    MsgBox "已合并 " & fileCount & " 个文件", vbInformation
End Sub

几个关键点值得解释。第一，用 CreateObject("Scripting.FileSystemObject") 后期绑定，而不是在工程里引用 Microsoft Scripting Runtime，好处是把文件发给同事时不依赖引用设置，避免"缺少引用"报错。第二，PasteSpecial xlPasteValues 只粘贴值，不带格式，这样可以避免源文件的乱七八糟格式污染总表。第三，wbSrc.Close False 关闭时不保存，因为源文件是只读打开的，这里传 False 只是保证不弹保存提示。

容易踩的坑有三个：一是总表如果只有表头、没有数据行，End(xlUp).Row 会返回 1，加 1 之后是 2，起始位置正确；二是源文件第一行如果不是表头，会误把表头当数据合并进来，所以一定要先统一模板；三是网络盘路径下 Workbooks.Open 可能因为锁文件失败，建议先把文件拷到本地再跑。

6.2 按部门拆分并导出独立文件

反向需求同样常见：一张总表要按部门拆成十几个文件分别发出去。手工筛选复制一天就过去了，用下面这段代码几秒钟完成。

vba复制' 文件路径：合并工具.xlsm 的标准模块 Module1
Sub SplitByDepartment()
    Dim wsSrc As Worksheet
    Dim wbNew As Workbook
    Dim dict As Object
    Dim lastRow As Long
    Dim i As Long
    Dim dept As String
    Dim rngData As Range
    Dim outPath As String
    Dim key As Variant
    
    Set wsSrc = ThisWorkbook.Worksheets("明细")
    lastRow = wsSrc.Cells(wsSrc.Rows.Count, 1).End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "明细表没有数据", vbExclamation
        Exit Sub
    End If
    
    Set rngData = wsSrc.Range("A1").CurrentRegion
    Set dict = CreateObject("Scripting.Dictionary")
    
    For i = 2 To lastRow
        dept = Trim(CStr(wsSrc.Cells(i, 1).Value))
        If Len(dept) > 0 Then
            If Not dict.Exists(dept) Then dict.Add dept, 1
        End If
    Next i
    
    outPath = ThisWorkbook.Path & Application.PathSeparator & "拆分"
    If Dir(outPath, vbDirectory) = "" Then MkDir outPath
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each key In dict.Keys
        rngData.AutoFilter Field:=1, Criteria1:=CStr(key)
        Set wbNew = Workbooks.Add
        rngData.SpecialCells(xlCellTypeVisible).Copy
        wbNew.Worksheets(1).Range("A1").PasteSpecial xlPasteValues
        wbNew.Worksheets(1).Range("A1").PasteSpecial xlPasteColumnWidths
        wbNew.SaveAs outPath & Application.PathSeparator & CStr(key) & ".xlsx"
        wbNew.Close False
    Next key
    
    rngData.AutoFilter
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    MsgBox "已拆分为 " & dict.Count & " 个文件，输出目录：" & outPath, vbInformation
End Sub

这段代码的逻辑是先扫一遍 A 列（假设第一列是部门）收集所有不重复的部门名，再用自动筛选 + 可见单元格复制的方式逐部门导出。用字典而不是数组去重，是因为字典的 Exists 判断效率高，几万行也很快。

有两个细节容易被忽略。一是 SpecialCells(xlCellTypeVisible) 必须在筛选之后调用，筛选之前调用会把所有单元格都算进去；二是 PasteSpecial xlPasteColumnWidths 单独粘一次列宽，否则导出的文件列宽全是默认值，打开看着很难受。另外 Application.DisplayAlerts = False 是必须的，因为 Workbooks.Add 和 SaveAs 在重名时会弹确认框，批量执行时会被卡住。

6.3 事件与按钮

把宏挂到按钮上是让非技术同事能用起来的关键。做法是插入一个形状，右键 - 指定宏，选中对应过程名。这样对方点一下按钮就能跑，不需要打开 VBA 编辑器。

这里有一个安全提醒：不要在 Workbook_Open 事件里放破坏性操作，比如自动清空数据、自动保存。万一宏被误触发，损失不可逆。稳妥的做法是让所有写操作都需要用户点按钮确认，并且每次执行前把结果输出到带时间戳的新文件里，而不是覆盖原文件。

7. 实例四：Power Query 数据清洗

7.1 用 M 语言完成逆透视

回到第 4 节提到的"月份横排"问题。假设你有一张表叫 tblWide，列是部门、一月、二月、三月……要把它转成一维表，Power Query 的操作路径是：选中部门列，右键 - 逆透视其他列，然后把属性列改名成"月份"，值列改名成"金额"。

界面上点完之后，在"高级编辑器"里能看到生成的 M 代码，大致是这样：

text复制let
    源 = Excel.CurrentWorkbook(){[Name="tblWide"]}[Content],
    更改的类型 = Table.TransformColumnTypes(源,{{"部门", type text}}),
    逆透视的列 = Table.UnpivotOtherColumns(更改的类型, {"部门"}, "月份", "金额"),
    更改的类型1 = Table.TransformColumnTypes(逆透视的列,{{"金额", type number}}),
    筛选的行 = Table.SelectRows(更改的类型1, each [金额] <> null)
in
    筛选的行

逐行解释一下。Excel.CurrentWorkbook() 读取当前工作簿里的表格或命名区域，[Name="tblWide"] 指定名称，[Content] 取出数据内容。Table.TransformColumnTypes 做类型转换，这一步很关键，因为不转类型的话金额会被当成文本，后续求和全是零。Table.UnpivotOtherColumns 是逆透视的核心，第二个参数是"要保持不动的列"，剩下的列会被转成属性-值两列。最后一个 Table.SelectRows 过滤掉空值。

这段代码的价值在于它可以保存下来，下个月数据换了一批，只要点一下"全部刷新"，整套清洗步骤自动重跑，不需要再手工点一遍。

7.2 合并文件夹下的多个文件

比单表清洗更有价值的是多文件合并。Power Query 里选"从文件 - 从文件夹"，指定目录，它会列出所有文件。点"合并"之后，如果文件结构一致，会自动生成一个示例转换函数，代码如下：

text复制let
    源 = Folder.Files("C:\Data\source"),
    筛选的文件 = Table.SelectRows(源, each [Extension] = ".xlsx"),
    调用自定义函数 = Table.AddColumn(筛选的文件, "数据", each Excel.Workbook([Content])),
    展开的列 = Table.ExpandTableColumn(调用自定义函数, "数据", {"Data"}, {"表数据"}),
    展开的表 = Table.ExpandTableColumn(展开的列, "表数据", {"部门", "日期", "金额"}, {"部门", "日期", "金额"}),
    更改的类型 = Table.TransformColumnTypes(展开的表,{{"日期", type date}, {"金额", type number}})
in
    更改的类型

这里需要注意两点。第一，Excel.Workbook([Content]) 的第二个参数可以指定是否把首行当标题，如果你的源文件格式统一，用默认值即可；不统一时要在展开前手工调整。第二，最后的列名列表必须和源文件实际的列名完全一致，差一个空格都会报错，所以源模板的规范程度直接决定了这段代码的稳定性。

还有一个实际使用中的提醒：Power Query 的加载结果默认输出到新工作表，不要直接覆盖源数据所在的位置，否则刷新时会形成循环引用。稳妥的做法是查询只负责输出，源数据单独存在另一个文件或另一张表里。

7.3 加载与刷新策略

"关闭并上载"的时候会问你加载到哪。如果只是想看结果，选"表"加载到新工作表；如果数据量大，选"仅创建连接"，然后用透视表去接这个连接，性能和文件体积都会好很多。

刷新策略上，Power Query 的查询默认在打开文件时不自动刷新，需要手动点"全部刷新"。如果数据源是本地文件且量不大，可以在"连接属性"里勾选"打开文件时刷新数据"；如果数据源在网络上，建议保持手动，避免打开文件时长时间卡住。

8. 运行结果验证与常见问题排查

8.1 怎么判断跑对了

自动化脚本最怕的是"跑完了但结果是错的"，因为它不报错。所以我一般会做三层验证。

第一层是行数核对。合并 N 个文件之后，源文件数据行数之和应该等于总表行数减一（减掉表头）。这个数字用 =COUNTA(汇总!A:A)-1 就能看出来，对不上就说明有文件被跳过或者读错了。

第二层是金额合计核对。用 =SUM(汇总!F:F) 和各个源文件分别求和再相加的结果比对，差值为零才算通过。这一步能抓到"某个文件列顺序不同导致数据错位"这种隐蔽问题。

第三层是抽样检查。从结果里随机挑三到五行，手工回到源文件找到对应记录，确认字段一一对应，尤其是日期和金额这两列，格式错位最容易发生在这两列上。

对于 Power Query 的输出，除了上面三层，还要额外检查一步：点开查询的"应用的步骤"，从第一步开始逐个点，看每一步的行数变化是否符合预期。比如逆透视之后行数应该变成原来的列数倍，如果没变，说明逆透视的列选错了。

8.2 常见问题对照表

这张表里的每一条都是实际会遇到的高频问题，尤其是"透视表看不到新增数据"和"SUMIFS 结果为 0"这两条，前者九成是因为数据源没用表格，后者九成是因为数字是文本格式。遇到问题先按这两条对号入座，能省不少时间。

9. 最佳实践与后续学习路径

9.1 分层设计

我在维护长期使用的 Excel 工具时，坚持把工作簿分成三层，并且严格禁止跨层引用。

第一层是原始数据层，一个或多个工作表，只存清洗后的一维表，不做任何计算，不加任何格式。第二层是计算层，用公式、透视表或 Power Query 在这里产出结果，只引用数据层，不引用展示层。第三层是展示层，放排版好的报表，只引用计算层，不直接引用原始数据。

这么分的好处是改动的爆炸半径可控。源数据格式变了，只改数据层；报表样式变了，只改展示层；中间的计算逻辑稳定，不会被误伤。很多"改一个公式全表崩"的惨剧，根子就是三层混在一起。

9.2 命名与结构规范

几个具体的约定，成本很低但收益很大。工作表按层级命名，比如 01_数据、02_计算、03_报表，前面加序号的好处是顺序固定，不会因为重命名而乱。表格一律用 tbl 前缀，命名区域用 rng 前缀，参数用 lst 前缀。VBA 里的过程名用动词加名词的驼峰写法，比如 MergeAllWorkbooks、SplitByDepartment，不要用 宏1、test。

模块也要拆。按功能拆成 modMerge、modSplit、modUtil，不要把几十个过程全塞进 Module1。工具函数比如"获取最后一行""判断文件是否存在"单独放一个模块，复用时直接导入。

9.3 版本与备份

.xlsm 文件不适合做文本比对，因为里面的代码和二进制内容混在一起，Git 之类的工具只能识别"文件变了"，看不出改了什么。更实用的做法的有两条：一是把 VBA 模块导出成 .bas 文件单独留存，导出路径是 VBA 编辑器里右键模块 - 导出文件，这样代码就可以做版本对比了；二是在关键版本上另存一份带日期后缀的副本，比如 合并工具_20240115.xlsm。

写操作之前做备份这件事，无论多熟练都要坚持。自动化的价值在于省时间，但如果它把数据改坏了又没有备份，损失远超过省下来的时间。

9.4 性能优化的几个具体手段

第一条，计算模式改成手动。公式多的工作簿，每输入一个值就全表重算，输入体验会非常差。路径是公式 - 计算选项 - 手动，需要算的时候按 F9。

第二条，公式里不要用整列引用。VLOOKUP(A2,Sheet2!A:D,4,0) 这种写法看着简洁，实际上是把 104 万行都拉进了计算范围，数据量一上来就卡。改成 Sheet2!$A$2:$D$50000 或者直接引用表格，速度快一个数量级。

第三条，能写 IFERROR 就不要让错误值扩散。错误值会沿着引用链一路传下去，一个 #N/A 可能污染整张报表，而 IFERROR 一行就能兜住。

第四条，VBA 里批量操作前一定关掉 ScreenUpdating 和 Calculation，循环结束后再打开。这条规则对遍历几千行的场景能带来十倍的提速。

9.5 接下来可以学什么

如果你已经把本文的五个实例跑通，下一步我建议按这个顺序深入。

先学 Power Pivot 和数据模型。2010 里的 Power Pivot 加载项可以处理百万行级别的数据，用类似数据库的方式建表关系，这是从"表格思维"转向"数据模型思维"的入口。再学 VBA 里的事件处理（Worksheet_Change、Workbook_BeforeSave），它能让工具在合适的时机自动做事，而不是等人点按钮。最后，如果条件允许，了解一下用 ODBC 或 ADO 让 Excel 直接读数据库，这能彻底摆脱"每月导出一次文件"的手工环节。

不过要提醒一句：Excel 2010 毕竟是十几年前的产品，微软早已停止对它的技术支持，安全补丁也不再更新。如果这套工具要长期承载关键业务，建议在合适的时机评估升级路径。在升级之前，本文讲的这些规范——一维表、表格化、分层、模块化——都是可以平移的，不会白学。

最后留一个可以直接上手的动作：挑你手头最烦的那张月度报表，先只做一件事，把它从横排改成竖排的一维结构，存成一个表格，然后在这个基础上插一张透视表。这一步花不了半小时，但你会立刻感觉到后续所有的汇总工作都变轻了。剩下的事情，再按本文的顺序一步步往上叠。