Excel中平常用不到，但一旦用到，就是王炸的10个函数

Excel萌新：请问Excel中平常用不到，但一旦用到，就是王炸的函数有哪些？

千万别学Excel：在Excel的庞大函数库中，有些函数可能不常出现在日常列表中，但当你遇到特定的、棘手的难题时，它们能提供极其优雅且高效的解决方案，堪称“王炸”。以下我列出10个符合这一特性的函数（仅作为个人意见参考）：

1. AGGREGATE (函数编号, 选项, 数组, [k])

* 核心作用： 终极汇总函数。它整合了19个常用功能（求和、平均、计数、最大/最小等），并具备忽略错误值、隐藏行、嵌套SUBTOTAL等的强大选项。

* 为何是“王炸”： 当你的数据集包含错误值（如#N/A, #DIV/0!）或有隐藏行/筛选时，SUM/AVERAGE等常规函数会罢工或返回错误。AGGREGATE可以轻松穿透这些障碍，得到准确结果。例如，

“=AGGREGATE(9, 5, A1:A10)” 表示忽略错误值和隐藏行，对A1:A10求和（9=SUM, 5=忽略错误+隐藏行）。

* 场景： 处理来自数据库、外部链接的、易产生错误值的复杂数据；创建动态仪表盘需忽略筛选结果中的隐藏行。

2. TEXTJOIN (分隔符, ignore_empty, text1, [text2], …)

* 核心作用： 用指定的分隔符连接文本字符串列表，并可选择忽略空白单元格。

* 为何是“王炸”： 比古老的CONCATENATE或&运算符强大无数倍。轻松连接多个列或区域的文本，中间自动加逗号、空格等。

“ignore_empty”参数避免了令人烦恼的多余分隔符。例如，

“=TEXTJOIN(“, “, TRUE, A1:A5)” 会忽略A1:A5中的空单元格，用逗号+空格连接非空值。

* 场景： 将客户姓名列表组合成邮件收件人（带分隔）；生成带分隔符的SKU列表；将多个单元格注释合并到一处。

3. FREQUENCY (data_array, bins_array)

* 核心作用： 以一列垂直数组返回一组数据在指定边界（分箱）中的频率分布（计数）。是一个需要按Ctrl+Shift+Enter（CSE）输入的数组公式（较新版本自动支持）。

* 为何是“王炸”： 无需复杂的数据透视表或COUNTIFS嵌套，直接计算数据在预定区间的频次，是统计分析的利器。例如，统计员工年龄在20-29，30-39等各组的人数。

* 场景： 快速创建直方图所需的数据源；统计考试成绩分布区间；分析销售额在不同档位的订单数量。

4. NETWORKDAYS (start_date, end_date, [holidays])

* 核心作用： 计算两个日期之间的工作日天数（自动剔除周末），并可指定需要排除的节假日列表。

* 为何是“王炸”： 对于项目管理、人力资源、财务计算处理时长等场景是必备。手动剔除周末和节假日极其繁琐，此函数完美解决。其姊妹函数NETWORKDAYS.INTL还能自定义周末是哪几天。

* 场景： 计算项目的实际工作日工期；计算人力资源的处理天数（如招聘周期、请假时长估算）；贷款利息按工作日计算。

5. WORKDAY (start_date, days, [holidays])

* 核心作用： 给定一个起始日期和一个工作日天数，计算未来或过去的某个工作日的日期（剔除周末和可选节假日）。

* 为何是“王炸”： 规划任务结束日期、计算到期日、确定交付日期等场景下的核心工具。配合NETWORKDAYS使用。

* 场景： “这个任务需要5个工作日完成，今天开始，哪天能完成（考虑节假日）？”；计算发票到期日（T+10工作日）；安排后续会议。

6. XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

* 核心作用： VLOOKUP/HLOOKUP的现代、功能更强大的终极继任者。

* 为何是“王炸”： 几乎克服了VLOOKUP的所有缺点：

* 双向查找： 无需关心查找列在返回列的左边还是右边。

* 指定匹配方式： 精确匹配、近似匹配、通配符匹配等明确指定。

* 返回数组： 一次可返回相邻的多列值（动态数组功能）。

* 默认精确匹配： 更安全。

* 搜索模式： 支持从下往上（最后一项）、二分查找（排序后更快）。

* 自定义错误信息：

“[if_not_found]”参数可指定找不到时的友好提示。

* 场景： 在复杂表格中查找数据，替代并超越VLOOKUP的一切应用场景。

7. INDIRECT (ref_text, [a1])

* 核心作用： 根据文本字符串生成有效的单元格或区域引用。常被称为“间接引用”。

* 为何是“王炸”： 它能实现高度动态的引用。当引用对象需要根据其他单元格的值变化，或者需要组合不同的引用时，它是唯一的解决方案。例如，

“=SUM(INDIRECT(“Sheet2!” & B1))”，其中B1中是”A1:A10″，则公式等同于

“=SUM(Sheet2!A1:A10)”。

* 场景： 动态引用不同工作表的区域；基于用户选择动态更新图表数据源；创建汇总多个分表数据的公式模板。

8. MATCH (lookup_value, lookup_array, [match_type])

* 核心作用： 在数组中查找指定值，并返回其相对位置（行号或列号）。

* 为何是“王炸”： 虽然常用，但真正理解其威力在于它常作为INDEX函数的完美搭档（INDEX+MATCH组合被认为是比VLOOKUP更灵活强大的查找方案）。单独使用MATCH也常用于确定项目在列表中的位置、排序状态或作为其他函数的索引。

* 场景： 创建灵活的交叉查找（INDEX+MATCH）；确定最大值/最小值所在行；检查列表中是否存在某个值（搭配ISNUMBER/ISERROR）；为其他函数（如OFFSET）提供位置参数。

9. UNIQUE (array, [by_col], [exactly_once])

* 核心作用： 从区域或数组返回唯一值（去重）列表。

* 为何是“王炸”： 一键完成列表去重！无需复杂的数据透视表、高级筛选或公式嵌套（过去常用FREQUENCY等组合）。输出的是动态数组，结果自动扩展。

* 场景： 快速提取客户名单、产品列表等中的唯一项；为下拉列表提供动态的唯一值源；生成不重复的报表维度。

10. ARRAYTOTEXT (array, [format])

* 核心作用： 将数组或区域转换为单个文本字符串。

* 为何是“王炸”： 在调试和理解复杂的数组公式、需要将公式的结果以文本形式嵌入到消息中、或者需要将表格部分内容转化为文本块时，它非常好用。可选择以逗号分隔列表（

“0”）或显示数组格式（包括花括号

“{}”）（

“1”）。

* 场景： 快速查看复杂公式（如FILTER, SORT等）返回的整个数组结果；创建包含公式计算结果的日志或通知信息；将表格数据准备成邮件正文。

重要提示

* 版本要求： TEXTJOIN、XLOOKUP、UNIQUE、ARRAYTOTEXT等是较新的函数，在Microsoft 365、Excel 2021+中可用。如果你的版本较旧，可能无法使用。

总结一下：掌握这些函数，能让你的Excel数据处理能力上升到一个新的维度，从容应对各种复杂挑战。

Excel萌新同学，你学会了吗？

下面我要来考考你咯，看看你是不是真正掌握了我所讲的这些知识点，请做完再看答案哦！

---

选择题：

1. 当数据区域中存在隐藏行和错误值（如#N/A）时，使用以下哪个函数可以准确求和？

A) SUM()

B) SUBTOTAL(9, …)

C) AGGREGATE(9, 5, …)

D) SUMIF()

2. 需要根据开始日期和所需工作日天数计算任务截止日期（自动跳过周末和节假日），应使用哪个函数？

A) DATEADD()

B) EDATE()

C) NETWORKDAYS()

D) WORKDAY()

3. 以下哪项是XLOOKUP函数相比VLOOKUP的核心优势？

A) 只能在右向查找数据

B) 默认使用模糊匹配

C) 可自由返回查找列左侧的数据

D) 不支持自定义错误提示

---

答案：

1. C) AGGREGATE(9, 5, …) 解析：AGGREGATE 的选项 5 表示“忽略隐藏行和错误值”，9 表示求和（等效于SUM），完美应对此场景。
2. D) WORKDAY() 解析：WORKDAY(start_date, days, [holidays]) 专门用于计算N个工作日后的日期，自动排除周末和自定义节假日。NETWORKDAYS() 用于计算两个日期之间的工作日天数（不能直接生成截止日）。
3. C) 可自由返回查找列左侧的数据 解析：XLOOKUP 无需限定返回列在查找列右侧（这是VLOOKUP的硬伤），实现双向灵活查找。

Excel同学会VIP会员

8套Excel核心课程+专属VIP答疑＋定制插件，只要499元！

一次开通，长期有效