1. 赛题拆解：Excel竞赛到底在考什么

每年到了赛季，后台和群里问得最多的一句话就是：这玩意儿到底考什么，是不是把函数背熟了就能上。我先把结论摆在这儿——Excel竞赛考的不是函数记忆量，是在有限时间里把一堆不听话的数据变成一份能交差的报表的能力。函数只是工具，真正拉开差距的是你对数据结构、业务口径和边界情况的理解。这篇就围绕我刚做完的一套模拟赛题（自己按公开题型拼的，代号就叫 Excel-Ninja），把思路、公式、坑和赛场上那点时间管理，一条一条摊开讲。

先说适合谁看。完全零基础的朋友，建议先把 VLOOKUP、IF、SUM 这三个函数和表格的基本操作摸熟再回来，不然中间讲动态数组会有点吃力；有一点基础、平时做报表但总感觉“能用但不利索”的朋友，这篇是最对口的，我会把每个选择背后的理由说清楚；已经能熟练写公式的朋友，重点看第 3 章和第 5 章，Power Query 的清洗套路和排查表大概率能帮你省下赛场上十几分钟。

这套赛题的结构其实很典型，一张“原始数据”大表，配两到三张“参考表”（比如区域对照、产品目录、目标值），然后是若干道题，要求输出汇总表、排名表、异常清单。题目描述里通常会埋几个限定词，“按季度”“剔除退货”“仅统计已完成”，这些词就是分数所在。我第一次做模拟题的时候就是因为漏看一个“剔除”，整张汇总表全错，一道大题直接归零。所以后面我会反复强调一件事：先读题，再动手，把限定条件抄成一张清单贴在工作表旁边。

1.1 从题面反推考点分布

把一套赛题的所有小问摊开，你会发现它们基本落在四类里：清洗（脏数据、格式不统一、重复项）、匹配（多表关联、查找返回值）、聚合（分组统计、条件求和）、呈现（排序、排名、格式、图表）。这四类的分值分布通常不是一个平均分，而是清洗和聚合占大头，因为这两块最能体现“真实工作能力”。匹配是基本功，呈现是加分项。

我在做拆解的时候习惯画一张简单的考点表，把每个小问归到哪一类、需要哪些函数或工具、预计耗时写下来。这个动作花五分钟，但能让你在赛场上不会做到一半突然发现“这道题得用 Power Query，可我已经在公式里绕了二十分钟”。

1.2 为什么赛题偏爱“脏数据”

很多人抱怨题目故意恶心人，日期一半是文本一半是序列号，金额里混着空格和全角符号。但这恰恰是最贴近真实工作的部分。你从系统导出的表，几乎没有一次是干净的。赛题设计者把清洗放在前面，其实是在筛人：能不能识别脏在哪、能不能用可复现的方式洗干净，而不是一个个手工改。

我个人的判断是，清洗环节的正确做法是“过程可回放”。也就是说，你洗完的数据，别人拿到你的文件，点一下刷新就能重来一遍。手工改单元格看着快，一旦原始数据更新就全废，而且赛题里经常有一问是“原始数据新增一行后，结果是否正确”，这一问就是专门打手工改的。

1.3 评分逻辑反推备考优先级

模拟赛题一般按结果给分，公式写得再漂亮，结果错一个数就是零分。这意味着备考优先级应该是：结果正确 > 过程稳健 > 写法优雅。我见过有人为了用 LAMBDA 写一个炫技的递归，结果边界条件没处理，反而丢分。

所以我的建议是，平时练习时先用最笨但最稳的方法做对，再回头优化。赛场上更是如此，先拿到能拿的分，再考虑优化。这个顺序想反了，很容易在最后十分钟手忙脚乱。

2. 核心考点拆解：函数与动态数组怎么用才不翻车

这一章是重头戏。Excel 这几年最大的变化就是动态数组，它不只是多了几个函数，而是改变了你写公式的思维方式。以前一个公式只能返回一个值，你需要往下拖；现在一个公式可以“溢出”出一整片区域，你只需要写一次。这个变化对竞赛来说是双刃剑：写对了省一半时间，写错了就是满屏 #SPILL!。

先把最常用的几类函数过一遍，我不按字母顺序讲，按“你在赛场上会怎么用”来讲。

2.1 查找匹配：XLOOKUP 与 INDEX+MATCH 的取舍

匹配是每套题必考的内容。XLOOKUP 的语法是XLOOKUP(查找值, 查找数组, 返回数组, [未找到值], [匹配模式], [搜索模式])，比 VLOOKUP 强在三点：不用数列、可以向左查、可以指定找不到时返回什么。写起来是这样的：

=XLOOKUP($A2, 产品目录[产品编号], 产品目录[产品名称], "未匹配", 0)

最后一个参数 0 表示精确匹配。这里特别提醒，第四个参数“未找到值”一定要填，不填就返回 #N/A，一片红看着就慌，而且后面的聚合函数会被错误值带崩。填一个“未匹配”或者 0，至少能让汇总还能算下去，你也能通过筛选快速定位问题行。

INDEX+MATCH 还要不要学？要。原因有两个：一是某些老版本环境不支持 XLOOKUP，赛题如果给了兼容性要求你就得换；二是 INDEX+MATCH 在“返回区域需要动态偏移”的场景下更灵活，比如你要根据行号列号取交叉单元格，用INDEX(数据区, MATCH(行条件, 行标题, 0), MATCH(列条件, 列标题, 0))这种二维查表，写起来比嵌套 XLOOKUP 清爽。

注意：匹配失败最常见的原因不是函数写错，是两边的数据类型不一致。一边是文本型数字“1001”，一边是数值 1001，肉眼看一模一样，匹配就是找不到。排查方法是在空白单元格写=A2=B2，返回 FALSE 就说明类型不同。修的时候用--或者VALUE()转一下，或者干脆在 Power Query 里统一类型。

2.2 动态数组：溢出思维与它的三个坑

FILTER、SORT、UNIQUE、SEQUENCE 这几个函数，我建议你至少要熟练到不用查语法。它们的价值在于把“筛选—复制—粘贴”这套动作压缩成一个公式。

=FILTER(明细表, (明细表[区域]=$B$1)*(明细表[状态]="已完成"), "无数据")

这里用了乘法把两个条件“与”起来。为什么用乘号不用 AND？因为 AND 只能返回单个 TRUE/FALSE，而数组运算需要逐行判断，乘法会把 TRUE/FALSE 转成 1/0 再相乘，得到的就是逐行的布尔数组。同理，“或”的关系用加号。这个技巧在 SUMPRODUCT 里也一样通用，属于必须刻在脑子里的东西。

动态数组有三个坑，我按踩到的频率排：

第一个是#SPILL!，溢出区域被挡住了。原因通常是下方或右侧有内容，哪怕是一个空格。解决办法是先清空溢出区域，或者把公式挪到右边和下边都空的地方。判断溢出范围很简单，选中公式单元格，会出现一个虚线框，那个框就是它要占的地。

第二个是在表格（Ctrl+T 生成的超级表）里写动态数组。老版本的表格不支持溢出，会返回 #SPILL! 或者隐式交集。如果你非要在表格里用，就只能返回单值，或者改用 Power Query。我现在的习惯是，需要溢出的计算一律放在表格外面的普通区域，表格只负责存原始数据。

第三个是引用整列导致性能崩。FILTER(A:A, B:B="某值")看着简洁，实际是在算一百万行。数据量大的时候卡到怀疑人生。正确做法是引用具体范围或者用结构化引用明细表[字段名]，Excel 会自动只算有数据的部分。

2.3 聚合组合拳：SUMIFS、SUMPRODUCT 与 AGGREGATE

分组汇总是赛题里分值最密集的地方。SUMIFS 是最稳的，语法清晰，性能好：

=SUMIFS(明细表[金额], 明细表[区域], $A2, 明细表[季度], B$1, 明细表[状态], "已完成")

注意这里的引用写法，行标题用$A2（锁列不锁行），列标题用B$1（锁行不锁列），这样往右往下拖都不会错位。这个细节每年都有人栽，拖完发现整张表数字一样，回头查半天是引用没锁。

SUMPRODUCT 是万金油，条件求和、条件计数、加权平均都能干：

=SUMPRODUCT((明细表[区域]=$A2)*(明细表[季度]=B$1)*明细表[金额])

它的好处是不用辅助列也能做多条件，坏处是全范围运算，数据量大时慢。另外它有个隐式转换的坑：如果金额列里混了文本，会当成 0 处理，不报错但结果偏小。所以我一般在用 SUMPRODUCT 之前，一定先确认目标列是纯数值。

AGGREGATE 是很多人忽略的宝藏函数，它的强项是忽略错误值和隐藏行。语法是AGGREGATE(函数号, 选项, 数组)，函数号 9 是 SUM，14 是 LARGE，15 是 SMALL；选项 6 是忽略错误值，5 是忽略隐藏行，7 是两者都忽略。举个例子，你要算某个区域的最大值，但区域里有 #N/A：

=AGGREGATE(14, 6, (明细表[区域]=$A2)*明细表[金额], 1)

这一串的意思是：在满足区域条件的金额里取第 1 大的值，同时忽略错误。这个写法在“取每个分组的第一名”这类题里非常好用，不用先清洗错误值。

2.4 LET 与 LAMBDA：把复杂公式变成可读的公式

LET 是我近几年最愿意推荐给别人的函数。它让你在公式里定义中间变量，避免同一段计算重复写五遍：

=LET( 区域数据, FILTER(明细表[金额], 明细表[区域]=$A2), 有效数据, FILTER(区域数据, ISNUMBER(区域数据)), IF(COUNT(有效数据)=0, 0, AVERAGE(有效数据)) )

这段写法的好处是可读性，别人（包括三个月后的你自己）能一眼看懂在干什么。赛题里如果有一道题需要多步计算，用 LET 写出来，检查错误的时候能省大量时间。

LAMBDA 是自定义函数，语法是LAMBDA(参数1, 参数2, 计算式)，定义完可以在名称管理器里起个名字反复调用。竞赛里用得不多，但有一种场景很合适：同一套清洗逻辑要在十几个列上重复执行。比如统一清洗文本：

=LET( 清洗, LAMBDA(t, TRIM(CLEAN(SUBSTITUTE(t, CHAR(160), " ")))), 清洗(A2) )

这里把不换行空格（CHAR(160)）替换成普通空格再 TRIM，是处理从网页或系统导出的文本的固定动作。这个细节很小，但不做的话，你看着两个一模一样的“北京 ”，匹配就是失败。

3. 数据清洗与建模：Power Query 才是效率分水岭

如果说函数部分决定你能不能做完，Power Query 决定你能做多快。我在模拟赛里做过对比：同一套清洗任务，纯公式加手工大概 40 分钟，Power Query 12 分钟，而且后者可以一键刷新。赛题里如果出现“原始数据会更新”这种提示，基本就是在告诉你该用 PQ 了。

3.1 清洗流程的固定动作

我现在处理任何一张脏表的顺序都是固定的，形成肌肉记忆之后不会漏步骤：

第一步，提升标题行。很多导出表前面有几行说明文字，直接导入会把标题当数据。在 PQ 里用“将第一行用作标题”，如果前面有空行，先用“删除前几行”。

第二步，改数据类型。这一步最容易出问题。日期列如果被识别成文本，后面所有日期运算全废。金额列如果混了货币符号，会变成文本。我的做法是先全部设为“任意”，看一眼样本，再逐列指定。特别注意日期，PQ 会按区域设置解析，如果原始格式是“日/月/年”而你本地是“月/日/年”，会静默解析错，而且不报错。这种情况要在“更改类型”里选“使用区域设置”，明确指定源数据的日期格式。

第三步，去重与去空。用“删除重复项”之前想清楚按哪几列去重，全选去重容易误删。空行用“删除空行”，但要注意有些行是部分字段为空，这种不能删，要用筛选把关键字段为空的行挑出来单独处理。

第四步，拆分与合并。“姓名+工号”挤在一列的情况很常见，用“按分隔符拆分列”，注意选“每次出现分隔符时”还是“最左侧/最右侧”，选错了拆出来是乱的。合并列的时候注意中间要不要加分隔符，不加的话“张”+“三”会变成“张三”，加了就是“张-三”，看你后续怎么用。

第五步，逆透视。这是我认为 PQ 最有价值的功能。原始表是“一月、二月、三月”横向排列的宽表，你要做成纵向的长表才能做透视汇总。选中要保留的列，右键“逆透视其他列”，三秒钟搞定。用公式做这件事非常痛苦，要写一堆 INDEX 加 COLUMN 的组合。

心得：PQ 的每一步都会记录在右侧“应用的步骤”里，做完之后回头看一眼，把步骤重命名成有意义的名字，比如“统一日期格式”“剔除测试订单”。赛题如果要求你说明处理过程，这些命名就是现成的答案。另外，步骤顺序可以拖动调整，但乱拖容易报错，改之前先看看后一步依赖什么。

3.2 合并查询与追加查询的区别

这两个功能名字像，用途完全不同，每年都有人搞混。

**追加（Append）**是把结构相同的多张表上下叠起来，比如一月、二月、三月的明细各一个文件，追加成一张全年明细。要求是列名和列的顺序能对上，对不上的会变成 null。

**合并（Merge）**是把两张表按某个键左右拼起来，类似 VLOOKUP 的多列版本。合并的时候要注意连接类型：左外连接保留左表全部行，内连接只保留两边都匹配上的行。做“补全产品名称”这种任务，一定要选左外，否则匹配不上的行会被吃掉，你的总额就少了。

我踩过的一个坑是合并之后的展开步骤。合并完会多一个“Table”类型的列，要点右上角的展开图标，选需要的列。如果你只展开了一部分列，后面发现少字段了，还得回去改。所以合并的时候我一般先想清楚要哪些列，展开时一次性勾选，顺手把“使用原始列名作为前缀”取消掉，不然列名会变成“表2.产品名称”这种，后面引用很烦。

3.3 数据模型与度量值的基本思路

如果赛题的数据量超过几十万行，或者要求做多维分析，就该上数据模型（Power Pivot）了。它的核心是两件事：建关系和写度量值。

建关系就是把事实表（明细）和维度表（产品、区域、日期）按主键连起来，方向一般是维度表指向事实表的一对多。写度量值用的是 DAX，最常用的三个是 SUM、COUNTROWS 和 DISTINCTCOUNT，配合 CALCULATE 做上下文筛选。

举个典型场景，算“已完成订单的客户数”：

已完成客户数 := CALCULATE( DISTINCTCOUNT(明细表[客户编号]), 明细表[状态] = "已完成" )

CALCULATE 的作用是修改筛选上下文，把“状态=已完成”这个条件加到当前筛选之上。这个思路和 SUMIFS 的条件参数本质是一样的，只是 DAX 更灵活，能处理复杂的上下文嵌套。

不过我要说句实话，大部分竞赛题用不到数据模型。透视表加辅助列就能解决 90% 的汇总需求。数据模型的优势在数据量和大规模复用，如果你时间紧，优先把透视表和 SUMIFS 练熟，别在 DAX 上耗太久。

4. 全流程实操复现：从一张脏表到一份能交的报表

光讲知识点容易飘，我把模拟赛题的主线完整走一遍。虚构的场景是这样：某公司给了一份销售明细，字段有订单号、日期、区域、产品编号、数量、单价、金额、状态，另有两张对照表（产品目录、区域目标），要求输出四张结果表：按区域季度的销售额汇总、各产品销量排名、异常订单清单、完成率对比。

4.1 需求拆解与字段规划

先别急着写公式，把四张结果表的表头先搭出来。这一步看着笨，其实非常关键，因为表头决定了你要准备哪些字段。比如“按区域季度汇总”，你需要区域、季度两个维度，以及销售额这个度量。季度这个字段原始表里没有，得从日期算出来。

如果没有 PQ，季度用="Q"&ROUNDUP(MONTH(A2)/3,0)就能算。为什么要 ROUNDUP 而不是 ROUND？因为月份 1-3 要归到 Q1，除以 3 得到 0.33、0.67、1，向上取整正好是 1；4-6 月是 1.33、1.67、2，向上取整是 2。用 ROUND 的话 1 月会变成 0，就错了。这个推导过程建议自己动手算一遍，比背公式管用。

异常订单清单的定义要在题目里找，通常是“金额为负”“数量为 0”“状态为取消但金额不为 0”这类组合条件。我习惯先用辅助列把每条规则单独标出来，最后用 OR 合并，这样哪条规则命中了看得清楚，方便核对。

4.2 关键步骤与公式实现

第一步，在 PQ 里导入明细表，提升标题，把日期列设为日期类型（指定区域），金额列去掉货币符号转数值。这一步我建议单独建一个查询叫“明细_清洗后”，不要把清洗步骤和后续的分析混在一个查询里，分层清楚，改起来方便。

第二步，用合并查询把产品名称和区域负责人补进来。左外连接，展开需要的列。做完之后上载到工作表，同时勾选“将此数据添加到数据模型”（如果后面要用透视表的话）。

第三步，建汇总表。区域放行，季度放列，用 SUMIFS：

=SUMIFS(明细_清洗后[金额], 明细_清洗后[区域], $A3, 明细_清洗后[季度], B$2, 明细_清洗后[状态], "<>取消")

注意这里的条件是“不等于取消”，写法是"<>取消"，引号不能少。这一步很多人写成"≠取消"，Excel 不认。

第四步，排名表。用 SORT 加 SEQUENCE 一次性生成，或者用 RANK.EQ 算名次再排序：

=RANK.EQ(B3, $B$3:$B$20, 0)

第三个参数 0 表示降序。并列名次的问题要有心理准备，RANK.EQ 遇到并列会给相同名次，后面跳号（1、1、3）。如果题目要求连续名次，就得用RANK.EQ(...)+COUNTIF($B$3:B3,B3)-1这种写法去重。题目没要求的话，别自作聪明，按最简单的来。

第五步，完成率对比。用实际销售额除以目标，格式设成百分比，保留一位小数。这里有个细节，目标为 0 或空的时候会返回 #DIV/0!，用 IFERROR 包一层：

=IFERROR(C3/D3, "无目标")

第六步，异常清单。用 FILTER 一次性拉出来：

=FILTER(明细_清洗后, (明细_清洗后[金额]<0)+(明细_清洗后[数量]=0), "无异常")

两个条件之间用加号表示“或”。如果你写成乘号，就变成“且”，结果永远是空，这个错误我见过不止一次。

4.3 结果校验：交卷前的五分钟自检

做完不等于做对。我固定会做三个校验：

一是总量校验。把所有分组的销售额加起来，和明细表的总金额对比。如果清洗过程中剔除了取消订单，那就要减去对应的金额再比。对不上就说明有行被漏掉或重复计算了，一般是合并查询用了内连接，或者 FILTER 条件写反了。

二是抽样校验。随机挑三到五行，手工按条件算一遍，和公式结果比。抽样不用多，但一定要挑边界情况，比如金额为 0 的行、状态为空的行、日期在季度交界（3 月 31 日、4 月 1 日）的行。

三是格式校验。日期是不是真的日期（看右对齐还是左对齐，左对齐多半是文本），金额有没有保留两位小数，百分比是不是显示成 0.85 而不是 85%。这些不影响计算但影响评分，尤其是题目明确要求格式的时候。

5. 常见问题与排查实录：这些坑我基本都踩过

赛场上的问题大多不是“不会”，而是“卡住”。卡住的原因往往很小，但找起来费时间。这一章我把高频问题整理成速查表，配上排查顺序，你可以直接照着查。

5.1 公式类问题速查

#SPILL!的排查顺序：先看溢出区域有没有内容（包括看不见的空格），再看是不是在超级表里写的，最后看是不是引用了整列导致范围过大。

#VALUE!八成是数据类型问题。文本参与了算术运算，或者日期被当成文本。用ISNUMBER测一下，返回 FALSE 就是类型不对。

#N/A是匹配不到。先确认两边有没有多余空格（用 LEN 比长度），再确认是不是文本型数字，最后确认匹配列里有没有重复值导致返回了意外的行。

结果全一样基本是引用没锁。检查公式里该有$的地方是不是漏了。判断方法：选中公式往右拖一格，看引用的区域有没有跟着跑，跟着跑就是没锁。

循环引用是公式引用了自己所在的单元格，或者间接引用成环。Excel 会弹窗提示，顺着提示找就行。有时候是整列引用惹的祸，比如在 B 列写=SUM(B:B)。

提示：排查公式时不要一行行看，用“公式”选项卡里的“追踪引用单元格”和“追踪从属单元格”，能直观看到谁引用了谁。公式特别长的时候，选中公式里的一段按 F9，可以直接看到那一段的计算结果，这个技巧能省大量时间。看完记得按 Esc，不然那段就被替换成值了。

5.2 Power Query 刷新类问题

最常见的报错是“找不到列”。原因是你改了源表的列名，或者源表删了某列。解决办法是回到出错的步骤，重新指定列，或者干脆把源表的列名固定下来，别乱改。

第二个是刷新后数据变少。多半是源文件路径变了，或者筛选条件保留了。检查“应用的步骤”里有没有残留的筛选步骤，很多时候是上一次操作留下的筛选没删掉。

第三个是日期解析错误，前面提过，用“使用区域设置”明确指定。这个问题最阴，因为它不报错，只是默默算错。

第四个是性能问题。数据量大又做了很多合并查询时会很慢。优化思路是尽早筛选掉不需要的行和列，“筛选行”和“删除列”要放在流程前面，别等到最后才做。PQ 是流式的，前置过滤能大幅减少后续步骤的计算量。

5.3 赛场节奏与时间分配

我自己的节奏是这样：前 5 分钟通读全部题目，把限定词抄成清单；然后用 20-25 分钟做清洗，这一步做完基本一半的分就稳了；中间 40 分钟做汇总和匹配，这部分是主体；最后 20 分钟做呈现和校验，留 10 分钟机动。

有两个习惯我认为必须养成。一是每做完一道大题就存一次盘，文件名带时间和版本号，比如“模拟赛_v3_1030”。赛场上的意外太多了，软件崩、误删、覆盖保存，有版本备份就不慌。二是不要在最后十分钟改结构，那时候改公式很容易连锁出错，宁可留着一个不完美但正确的结果。

另外，遇到卡住的题先跳过。竞赛是按点给分的，一道题卡 15 分钟，可能就少做两道简单题。先把能拿的分拿到手，回头再看难题，心态也会不一样。

6. 备考路线：我建议的三周练法

最后说说怎么练。我的建议是分三段，每段一周，每段有明确的目标，不要一上来就刷整套题。

第一周打地基，重点是把 XLOOKUP、SUMIFS、IF、COUNTIF 这几个函数练到不用想语法，同时把结构化引用的写法习惯改过来。练法是找一张自己的数据表，把每个函数都套一遍，做十道小练习，每道不超过三分钟。

第二周练工具，主攻 Power Query 的五个动作：提升标题、改类型、去重、拆分、逆透视，外加合并查询。练法是找一份宽表，试着把它转成长表再转回去，来回做三遍，动作就熟了。这一周还要把数据透视表的“值显示方式”玩一遍，排名、占比、累计这些选项在竞赛里很好用。

第三周做套题，严格计时，一次做完一整套，做完立刻复盘。复盘不是看答案，是看自己哪一步耗时最长、哪个错误重复出现。我建议建一个错误本，把每次的错因记一行，比如“日期区域设置没指定”“FILTER 条件用乘号”“引用没锁列”。三周下来这本子就是你的个人避坑指南，比任何教程都值钱。

我个人在实际操作中的体会是，Excel 这东西真正的门槛不在函数数量，而在对数据的敏感度。你看到一张表，能立刻判断出哪几列可能有问题、哪个字段适合做主键、汇总口径该怎么定，这个能力比多背十个函数有用得多。竞赛只是把这个判断过程压缩到几个小时里，逼你在压力下做决策。所以别把它当成考试，当成一次高强度的工作模拟，做完一套，你对真实业务数据的掌控力就会上一个台阶。