★ FEATURED ARTICLE 1. 赛题拆解Excel竞赛到底在考什么每年到了赛季后台和群里问得最多的一句话就是这玩意儿到底考什么是不是把函数背熟了就能上。我先把结论摆在这儿——Excel竞赛考的不是函数记忆量是在有限时间里把一堆不听话的数据变成一份能交差的报表的能力。函数只是工具真正拉开差距的是你对数据结构、业务口径和边界情况的理解。这篇就围绕我刚做完的一套模拟赛题自己按公开题型拼的代号就叫 Excel-Ninja把思路、公式、坑和赛场上那点时间管理一条一条摊开讲。先说适合谁看。完全零基础的朋友建议先把 VLOOKUP、IF、SUM 这三个函数和表格的基本操作摸熟再回来不然中间讲动态数组会有点吃力有一点基础、平时做报表但总感觉“能用但不利索”的朋友这篇是最对口的我会把每个选择背后的理由说清楚已经能熟练写公式的朋友重点看第 3 章和第 5 章Power Query 的清洗套路和排查表大概率能帮你省下赛场上十几分钟。这套赛题的结构其实很典型一张“原始数据”大表配两到三张“参考表”比如区域对照、产品目录、目标值然后是若干道题要求输出汇总表、排名表、异常清单。题目描述里通常会埋几个限定词“按季度”“剔除退货”“仅统计已完成”这些词就是分数所在。我第一次做模拟题的时候就是因为漏看一个“剔除”整张汇总表全错一道大题直接归零。所以后面我会反复强调一件事先读题再动手把限定条件抄成一张清单贴在工作表旁边。1.1 从题面反推考点分布把一套赛题的所有小问摊开你会发现它们基本落在四类里清洗脏数据、格式不统一、重复项、匹配多表关联、查找返回值、聚合分组统计、条件求和、呈现排序、排名、格式、图表。这四类的分值分布通常不是一个平均分而是清洗和聚合占大头因为这两块最能体现“真实工作能力”。匹配是基本功呈现是加分项。我在做拆解的时候习惯画一张简单的考点表把每个小问归到哪一类、需要哪些函数或工具、预计耗时写下来。这个动作花五分钟但能让你在赛场上不会做到一半突然发现“这道题得用 Power Query可我已经在公式里绕了二十分钟”。题号类型典型问法首选工具备选方案常见失分点清洗类统一日期格式、拆分姓名与部门、去重Power Query分列TRIMCLEAN只改了显示没改值匹配类按编号补全名称、按区域补目标XLOOKUPINDEXMATCH匹配列有空格或文本型数字聚合类按区域季度汇总、Top NSUMIFS/数据透视表SUMPRODUCT条件区域引用没锁定呈现类排名、条件格式、图表RANK.EQ条件格式排序后手工填排名并列处理不对1.2 为什么赛题偏爱“脏数据”很多人抱怨题目故意恶心人日期一半是文本一半是序列号金额里混着空格和全角符号。但这恰恰是最贴近真实工作的部分。你从系统导出的表几乎没有一次是干净的。赛题设计者把清洗放在前面其实是在筛人能不能识别脏在哪、能不能用可复现的方式洗干净而不是一个个手工改。我个人的判断是清洗环节的正确做法是“过程可回放”。也就是说你洗完的数据别人拿到你的文件点一下刷新就能重来一遍。手工改单元格看着快一旦原始数据更新就全废而且赛题里经常有一问是“原始数据新增一行后结果是否正确”这一问就是专门打手工改的。1.3 评分逻辑反推备考优先级模拟赛题一般按结果给分公式写得再漂亮结果错一个数就是零分。这意味着备考优先级应该是结果正确 过程稳健 写法优雅。我见过有人为了用 LAMBDA 写一个炫技的递归结果边界条件没处理反而丢分。所以我的建议是平时练习时先用最笨但最稳的方法做对再回头优化。赛场上更是如此先拿到能拿的分再考虑优化。这个顺序想反了很容易在最后十分钟手忙脚乱。2. 核心考点拆解函数与动态数组怎么用才不翻车这一章是重头戏。Excel 这几年最大的变化就是动态数组它不只是多了几个函数而是改变了你写公式的思维方式。以前一个公式只能返回一个值你需要往下拖现在一个公式可以“溢出”出一整片区域你只需要写一次。这个变化对竞赛来说是双刃剑写对了省一半时间写错了就是满屏 #SPILL!。先把最常用的几类函数过一遍我不按字母顺序讲按“你在赛场上会怎么用”来讲。2.1 查找匹配XLOOKUP 与 INDEXMATCH 的取舍匹配是每套题必考的内容。XLOOKUP 的语法是XLOOKUP(查找值, 查找数组, 返回数组, [未找到值], [匹配模式], [搜索模式])比 VLOOKUP 强在三点不用数列、可以向左查、可以指定找不到时返回什么。写起来是这样的XLOOKUP($A2, 产品目录[产品编号], 产品目录[产品名称], 未匹配, 0)最后一个参数 0 表示精确匹配。这里特别提醒第四个参数“未找到值”一定要填不填就返回 #N/A一片红看着就慌而且后面的聚合函数会被错误值带崩。填一个“未匹配”或者 0至少能让汇总还能算下去你也能通过筛选快速定位问题行。INDEXMATCH 还要不要学要。原因有两个一是某些老版本环境不支持 XLOOKUP赛题如果给了兼容性要求你就得换二是 INDEXMATCH 在“返回区域需要动态偏移”的场景下更灵活比如你要根据行号列号取交叉单元格用INDEX(数据区, MATCH(行条件, 行标题, 0), MATCH(列条件, 列标题, 0))这种二维查表写起来比嵌套 XLOOKUP 清爽。注意匹配失败最常见的原因不是函数写错是两边的数据类型不一致。一边是文本型数字“1001”一边是数值 1001肉眼看一模一样匹配就是找不到。排查方法是在空白单元格写A2B2返回 FALSE 就说明类型不同。修的时候用--或者VALUE()转一下或者干脆在 Power Query 里统一类型。2.2 动态数组溢出思维与它的三个坑FILTER、SORT、UNIQUE、SEQUENCE 这几个函数我建议你至少要熟练到不用查语法。它们的价值在于把“筛选—复制—粘贴”这套动作压缩成一个公式。FILTER(明细表, (明细表[区域]$B$1)*(明细表[状态]已完成), 无数据)这里用了乘法把两个条件“与”起来。为什么用乘号不用 AND因为 AND 只能返回单个 TRUE/FALSE而数组运算需要逐行判断乘法会把 TRUE/FALSE 转成 1/0 再相乘得到的就是逐行的布尔数组。同理“或”的关系用加号。这个技巧在 SUMPRODUCT 里也一样通用属于必须刻在脑子里的东西。动态数组有三个坑我按踩到的频率排第一个是#SPILL!溢出区域被挡住了。原因通常是下方或右侧有内容哪怕是一个空格。解决办法是先清空溢出区域或者把公式挪到右边和下边都空的地方。判断溢出范围很简单选中公式单元格会出现一个虚线框那个框就是它要占的地。第二个是在表格CtrlT 生成的超级表里写动态数组。老版本的表格不支持溢出会返回 #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 是 SUM14 是 LARGE15 是 SMALL选项 6 是忽略错误值5 是忽略隐藏行7 是两者都忽略。举个例子你要算某个区域的最大值但区域里有 #N/AAGGREGATE(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季度用QROUNDUP(MONTH(A2)/3,0)就能算。为什么要 ROUNDUP 而不是 ROUND因为月份 1-3 要归到 Q1除以 3 得到 0.33、0.67、1向上取整正好是 14-6 月是 1.33、1.67、2向上取整是 2。用 ROUND 的话 1 月会变成 0就错了。这个推导过程建议自己动手算一遍比背公式管用。异常订单清单的定义要在题目里找通常是“金额为负”“数量为 0”“状态为取消但金额不为 0”这类组合条件。我习惯先用辅助列把每条规则单独标出来最后用 OR 合并这样哪条规则命中了看得清楚方便核对。4.2 关键步骤与公式实现第一步在 PQ 里导入明细表提升标题把日期列设为日期类型指定区域金额列去掉货币符号转数值。这一步我建议单独建一个查询叫“明细_清洗后”不要把清洗步骤和后续的分析混在一个查询里分层清楚改起来方便。第二步用合并查询把产品名称和区域负责人补进来。左外连接展开需要的列。做完之后上载到工作表同时勾选“将此数据添加到数据模型”如果后面要用透视表的话。第三步建汇总表。区域放行季度放列用 SUMIFSSUMIFS(明细_清洗后[金额], 明细_清洗后[区域], $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%。这些不影响计算但影响评分尤其是题目明确要求格式的时候。校验项操作方法不通过时先查什么总量一致汇总合计 vs 明细合计合并查询连接类型、筛选条件抽样正确手工复算 3-5 行引用是否锁对、条件是否写反日期类型看对齐方式、ISNUMBER 判断是否被识别成文本空值处理筛选取值为空的行IFERROR 是否覆盖全重复行对主键列 COUNTIF 大于 1去重步骤是否漏做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 这东西真正的门槛不在函数数量而在对数据的敏感度。你看到一张表能立刻判断出哪几列可能有问题、哪个字段适合做主键、汇总口径该怎么定这个能力比多背十个函数有用得多。竞赛只是把这个判断过程压缩到几个小时里逼你在压力下做决策。所以别把它当成考试当成一次高强度的工作模拟做完一套你对真实业务数据的掌控力就会上一个台阶。
阅读完成 · 觉得有帮助？
