Excel做表缺少的思维习惯，请逼自己养成……|vba|数据源|明细表|汇总表|源文件

点击蓝字【秋叶 Excel】

发送【6】

免费领 1000+篇 Excel 精选教程！

本文作者：小花

本文编辑：竺兰

文末获取本文配套源文件！

小花一直认为，学习 Excel，不仅要专注于函数、技巧、图表等「务实」技能，也需要适当地「务虚」，去系统总结、归纳和锤炼 Excel 思维。

前者是量变，后者是质变。

今天，小花就围绕如何设计工作汇总表做一些分享，希望能助力小伙伴们提升 Excel 运用境界。

区分数据汇总表和明细表

把 Excel 电子表当成手工表使用，数据汇总和录入混杂，是很多 Excel 使用者最经常犯的思维错误。

比如下面这张统计合同付款表。

设计者以每一个合同为一行，每次付款都按列次逐一登记付款时间和付款金额，然后使用 SUMIF 函数来汇总每个合同的付款总额。

从对 SUMIF 函数的熟悉程度看，设计者并非小白，但仍设计出这种灾难级别的 Excel 表格，可见其 Excel 数据底层思维是严重缺失的。

这套表的显著缺陷至少包括：

❶ 输入非常繁琐，需要找到合同所在行，拖动到该行记录的最后一列，才能开始录入，这无疑降低了数据录入的效率，而且一旦录入错误，很难检查和修改。 ❷ 可录入的关联数据非常有限，比如支付的银行、支付方式等都无法涵盖。 ❸ 数据分布范围不可控（数据记录的列数因付款次数不同而长短不一），数据信息错落（同样信息分布在不同列，不同信息彼此穿叉），很难进行数据统计，特别是当汇总需求发生变化时，基本没有适应能力。比如各月度成本支付总额的统计公式就很难设置，即使勉力为之，计算量也非常大。

当然，我们可以使用 Power Query 处理上述表格，但是 Excel 实战的思路绝对不是没苦硬吃。

正确的思路是

将合同付款表拆分成「合同情况表」和「付款明细表」。

前者需要统计的合同付款总额仍可以通过 SUMIF 来实现，而后者由于其二维表结构，能够更好的满足其他统计需求，也更方便录入。

区分数据汇总表和数据明细表，是小花认为最重要的 Excel 思维之一。

它能够真正发挥 Excel 数据统计能力，而非将 Excel 作为一个电脑版计算器使用。

这种思维将至少带来以下便利：

❶ 明细表和汇总表都方便与其他相关表格数据进行横向拉通，进一步降低明细数据获取难度和工作量；还能够根据需要扩展明细数据的其他维度信息，而不影响汇总表的结构和计算。

❷ 同列数据为相同维度信息，不同行数据互相独立，统计公式设置更为简洁，且方便筛选、透视等进一步数据操作。

❸ 当汇总需求发生变化时，只需通过修改公式即可实现，无需对明细数据动刀。当需要多人协作录入明细数据、汇总需求不明确或多变时，这样做就显得十分必要。

比如，此时统计 8 月付款总额，就非常容易。

让数据获取更简单

职场中数据处理工作量最大的环节，莫过于数据获取。

并且，这一工作基本是由基层通过录入来完成，这就要求数据录入简单且明确。

表格设计者需要做的，是让数据录入兼顾效率与准确性。

❶ 仅要求录入关键字和与关键字相关的必要数据，避免重复操作。

比如，上一案例中，付款明细表中的合同金额就是一个不需要重复录入的，这一信息在合同情况表中即可查询。

而合同名称虽然也可以通过关键字合同编号查询，但它是数据录入中必要的提示信息（录入人员很难通过合同编码明确识别付款内容）。

因此，我们应当通过公式来自动获取这一数据，而非要求手工录入，后者既低效又容易出错。

❷ 录入数据的格式必须统一、数据维度必须单一。

比如，像下面这样的数据填写方式，都是不合理的，会给后续的数据处理造成麻烦。

要规范数据录入，除了在设置数据字段时要做好明确拆分，还可以借助数据有效性、条件格式等功能来对数据录入加以约束。

常见的如下拉列表、字段长度限制或重复标识提醒，复杂的也可以通过公式来设置。

比如，在付款明细录入中，当累计付款金额超过合同总额时，几乎可以确定录入有误，这种情况下就可以设置数据有效性来加以规范。

数据校验公式：

=SUMIF($A$1:A16,A16,$D$1:D16)<=VLOOKUP(A16,合同情况表!A:C,3,0)

所谓让数据获取更简单，不仅在于数据完整性和单一性，还在于让录入者更容易达到设计者的要求，更不容易出错，更可能适应多变的需求。

虽然，我们可以使用分列、查找替换等一系列方法来对数据源进行整理以达到使用标准。

但是一个优秀的 Excel 使用者绝不是一个技能高超的「裱糊匠」，而应当是一个深思熟路、运筹帷幄的「指挥家」。

让统计方式更智能

如果说设计明细表是一场控制他人的谋略，那么汇总表的编制则更像是表格设计者的自我修炼。

最好的设计思路是基于现有的明细表，根据复杂变化的统计需求，通过公式、VBA、透视表、PQ 甚至 SQL来实现汇总统计需求，想尽一切可能的办法，让统计更为智能。

还是以 8 月付款总额为例，我们使用了两组不同的参数。

公式一：

=SUMIFS(D:D,$C:$C,">2024-7-31",$C:$C,"<=2024-8-31")

公式二：

=SUMIFS(D:D,$C:$C,">=2024-8-1",$C:$C,"<2024-9-1")

这两个公式结果一致，看似都正确，其实不然，应选用后者。

为什么呢？

这是因为，单纯的日期是整数，而包含具体时间的日期则是对应日期整数和代表时间的小数之和，比如，2024-8-31，其数值是 45535，2024-8-31 12:00:00 的数值则是 45535.5。

日期型条件判断的本质是数值比较。显然在公式一中，2024-8-31 12:00:00 不符合条件，仅有 2024-8-31 符合条件，但是统计 8 月付款总额时，显然这两者都应该是符合条件的，公式一的统计结果出错，而此时，公式二却是正确的。

同样的，2024-7-31 12:00:00 也会造成这种情况。

所以，公式二显然是更为智能的函数参数组合。

此时，不能准确设置公式的表格设计者，就会要求付款日期必须填整数，然而有时数据源的日期天然就带有具体时间，这就必然增加了很多修改数据源的无用功，耗时耗力耗人，还很难一次到位。

这个例子告诉我们，一定要努力让公式尽可能适应复杂数据变化，而不是让数据去适应公式的设置。

毕竟，公式几乎是一蹴而就的，而基础数据维护则是日积月累、聚沙成塔的，可能一个公式字符的改变，就能为表格上下游的参与者节约大量的工作时间。

同时，我们还应当尽量避免设置含明细表辅助列的公式或者运算量极大的公式。前者很容易因辅助列没有及时填充或删除而出问题，后者经常制造无法打开、运行极慢甚至导致崩溃的「巨无霸」表格。

除此之外，使用 VBA、PQ、SQL 等工具直接对相关表格（可能来源于网络、办公系统或其他现有表格）进行处理，跳过中间的明细表环节直接获取统计结果，也是极佳的思路。

以下是一段简单 VBA 代码，其功能是直接取用现有的销售明细表，提取其中的非车位面积，并按项目汇总，跳过了要求各项目销售人员手动筛选、填列、收集再合并的繁琐步骤，大大提高了工作效率。

Sub 提取销售面积()Application.ScreenUpdating = FalseDim d, k, tDim xm, yt, mjSet d = CreateObject("Scripting.Dictionary")reselect:Sf = Application.GetOpenFilename(FileFilter:="MicroSoft Excel 文件(*.xls*),*.xls*", MultiSelect:=False, Title:="请选择销售明细表")If TypeName(Sf) = "Boolean" Then respons = MsgBox("您没有选中任何工作簿，是否重新选择", vbYesNo)    If respons = vbYes Then       GoTo reselect:    Else: GoTo ExitHandler:    End IfEnd IfWorkbooks.Open Filename:=SfFor i = 4 To ActiveWorkbook.Sheets(1).[a100000].End(xlUp).Row - 1    xm = ActiveWorkbook.Sheets(1).Cells(i, 5)    yt = ActiveWorkbook.Sheets(1).Cells(i, 10)    mj = ActiveWorkbook.Sheets(1).Cells(i, 15)    If InStr(1, yt, "车") = 0 Then        If Not d.exists(xm) Then            d.Add xm, mj        Else            d(xm) = d(xm) + mj        End If    End IfNext iActiveWorkbook.Close savechanges:=Falsek = d.keyst = d.itemsThisWorkbook.ActiveSheet.[a2].Resize(d.Count, 1) = Application.Transpose(k)ThisWorkbook.ActiveSheet.[b2].Resize(d.Count, 1) = Application.Transpose(t)[a1].Resize(1, 2) = Array("项目", "面积")Set d = NothingExitHandler:Application.ScreenUpdating = TrueApplication.Calculation = xlCalculationAutomaticExit SubEnd Sub

▲ 文末获取练习文件

其核心功能语句不过 7 行，却极大地提高了统计的智能化水平。

值得一提的是，工具的使用者并不懂 VBA，是思维的力量催生了这段代码。

让统计更智能，其底层逻辑是让表格工具为人服务，成为脑力工作的延申，而不是让人为表格服务，沦为 CPU 和软件算法的附庸。

这一过程中，持续学习和修炼 Excel 技能必不可少。

但建立为什么而学的思维模式，比学习本身更为重要。

IT 大佬们常常挂在最边的一句话是：

技术固然重要，但比技术更重要的，是思维。

希望小花分享的这些 Excel 思维心得，能开启你的一点灵犀。

如果你想领取源文件，在我们公众号后台回复关键词【思维】即可下载！

如果你想学习更多关于表格设计、数据处理、数据分析、汇报表达的知识，想在工作中利用 Excel 提高效率，那么今天就是一个很好的机会：

《秋叶 Excel 3 天集训营》