Excel新函数革命:27个神级公式,个个都是yyds,让你彻底告别加班

新函数时代，一场效率的“生存战”已打响

  别再嘲笑 Excel 是“表哥表姐”的专属工具了。当别人用FILTER三秒完成数据筛选，用GROUPBY一键生成汇总报表，而你还在手忙脚乱地拖拽数据透视表、编写一长串VLOOKUP嵌套公式时，效率的鸿沟已悄然划下。

  这不是危言耸听。微软Office 365和WPS近年新增的这30多个新函数，正在彻底重塑数据处理的工作流。

  它们的核心优势在于动态数组和直指问题本质的设计逻辑。一个公式，结果自动填满一片区域；一个函数，解决过去需要多个步骤的复杂问题。

  今天，我为你系统梳理出27个经过实战检验的“YYDS”级新函数公式。这不仅仅是一个“收藏清单”，更是一份让你在效率竞赛中保持领先的生存指南。准备好，我们开始“升级”。

基础重塑：去重与统计的秒级操作

  过去，提取不重复值和计数是数据清洗的常见痛点，需要借助“删除重复项”功能或复杂的数组公式。现在，一切都变得极为优雅。

1. UNIQUE - 一键提取唯一值

  =UNIQUE(数据区域)

  实战场景：A列是包含上千条重复记录的客户名单。将此公式输入任一单元格，比如B1，按下回车，所有不重复的客户名瞬间向下“溢出”填满，无需任何拖动或菜单操作。

2. COUNTA+UNIQUE - 统计唯一值个数

  =COUNTA(UNIQUE(数据区域))

  实战场景：快速统计销售表中到底有多少个不同的产品SKU。=COUNTA(UNIQUE(B2:B1000))，直接得出数字。这是数据透视表“值计数”的公式解法，速度快到无需等待。

  如何提取出现次数大于1的重复值？用一个组合公式即可破解：

  =UNIQUE(FILTER(A:A, COUNTIF(A:A, A:A)>1))

  这个公式先用COUNTIF判断哪些是重复的，再用FILTER筛出来，最后UNIQUE去重，一步到位。

文本处理：合并拆分，告别“连接符”地狱

  文本拼接与分列是Excel中的高频操作。新函数让这些工作从“体力活”变成了“智能活”。

3. CONCAT - 无脑合并所有文本

  =CONCAT(区域)

  核心价值：彻底取代复杂的&连接符。=CONCAT(A1:A5)，无论区域中有多少内容，直接合并成一个字符串。

4. TEXTJOIN - 自定义分隔符合并

  =TEXTJOIN(分隔符, 是否忽略空单元格, 区域1, [区域2], ...)

  实战场景：生成邮件列表、产品编码。=TEXTJOIN("; ", TRUE, B2:B100)，将B列的所有非空内容用“； ”连接，第二个参数TRUE能自动跳过空白格，这是&符无法实现的智能。

5. TEXTSPLIT - 拆分文本的终极武器

  =TEXTSPLIT(文本, 列分隔符, 行分隔符, [是否忽略空], [填充值])

  这是一个革命性的函数，其灵活性超乎想象：

•   分列：=TEXTSPLIT(A1, "-") 将“A-B-C”拆成横向三列。
•   分行：=TEXTSPLIT(A1, , "-") 将“A-B-C”拆成竖向三行。
•   分矩阵：=TEXTSPLIT(A1, "，", "-") 可将“苹果，香蕉-橙子，葡萄”按逗号分列、减号分行，直接生成二维表格。

6. TEXTBEFORE / TEXTAFTER - 精准截取

  =TEXTBEFORE(文本, 分隔符, [实例编号], [匹配模式], [未找到时返回值])

  =TEXTAFTER(文本, 分隔符, [实例编号], [匹配模式], [未找到时返回值])

  实战场景：处理不规范但有一定规律的字符串，如“姓名-部门-工号-入职日期”。

•   取工号：=TEXTAFTER(A1, "-", 2) 表示取第二个“-”之后的内容。
•   取部门：=TEXTBEFORE(TEXTAFTER(A1, "-"), "-") 嵌套使用，先取第一个“-”之后，再取其中的第一个“-”之前。

  用TEXTSPLIT批量清洗数据。例如，清除字符串中所有数字：

  =LET(t, TEXTSPLIT(A1, SEQUENCE(10, ,0)), CONCAT(t))

  这里用SEQUENCE生成0-9的数组作为分隔符，TEXTSPLIT按每个数字拆分文本，拆出一堆片段，再用CONCAT将非数字的片段合并回去，实现纯文本提取。

表格手术：随心所欲的“剪辑”艺术

  想象你的数据表是一块面团，新函数就是最锋利的刀，想怎么切就怎么切。

7. TAKE / DROP - 截取或丢弃首尾行/列

  =TAKE(数据区域, 行数, [列数]) // 正数取前N，负数取后N

  =DROP(数据区域, 行数, [列数]) // 正数丢前N，负数丢后N

  实战场景：

•   预览数据：=TAKE(A:G, 10) 取前10行。
•   去掉末尾汇总行：=DROP(数据!A:G, -1) 丢弃最后一行。
•   制作样本：=TAKE(DROP(原数据, 1, 1), 20, 5) 先去标题行和首列，再取20行5列，快速创建一个干净的样本数据集。

8. CHOOSECOLS / CHOOSEROWS - 按位置选择列/行

  =CHOOSECOLS(数据区域, 列索引1, [列索引2], ...)

  =CHOOSEROWS(数据区域, 行索引1, [行索引2], ...)

  实战场景：从20列的原始数据中，只提取你需要的第1， 4， 7， 9列。=CHOOSECOLS(A:T, 1, 4, 7, 9)，顺序还可以自定义，比如(9,1,4,7)。

9. WRAPROWS / WRAPCOLS - 行列转换新姿势

  =WRAPROWS(一维数组, 每行多少列, [不足时填充值])

  =WRAPCOLS(一维数组, 每列多少行, [不足时填充值])

  实战场景：

•   名单排版：=WRAPROWS(A1:A100, 5, "") 将100个名字排成5列，自动生成20行，最后一格不足用空值填充。
•   数据分组：=WRAPCOLS(SEQUENCE(12), 3) 将1-12的数字，每3个为一组排成一列，共4行3列，这是制作季度报表数据的绝佳起点。

数据整合：合并与筛选的“上帝视角

  面对多表合并和复杂筛选，你不再需要手动复制粘贴或编写令人头疼的数组公式。

10. VSTACK / HSTACK - 垂直/水平合并

  =VSTACK(数组1, [数组2], ...) // 上下堆叠

  =HSTACK(数组1, [数组2], ...) // 左右拼接

  实战场景：

•   合并全年12个月报表：=VSTACK(1月!A2:G100, 2月!A2:G100, …， 12月!A2:G100)。更强大的是，如果12个工作表名称连续，可以用=VSTACK(1月:12月!A2:G100)一句搞定。
•   添加总计行/列：=VSTACK(销售数据, {"总计", SUM(销售额区域)}) 在数据下方添加一行总计。{}内是手工输入的数组。

11. FILTER - 动态筛选，告别筛选器

  =FILTER(要返回的数组, 条件1, [条件2], ... , [无结果时返回值])

  这是新函数中的“王牌”。它返回的是动态数组，源数据一改，结果自动变。

•   多条件筛选：=FILTER(A2:F100, (C2:C100="销售部")*(D2:D100>10000), "无符合条件记录") 筛选销售部且销售额大于1万的记录。*表示“且”。
•   或条件筛选：=FILTER(A2:F100, (C2:C100="北京")+(C2:C100="上海"), "") 筛选北京或上海的记录。+表示“或”。

12. SORT / SORTBY - 智能排序

  =SORT(数组, [排序列], [升序1], ...) // 按列索引排

  =SORTBY(数组, 排序列1, [升序1], ...) // 按指定列区域排

  实战场景：

•   简单排序：=SORT(A2:G100, 3, -1) 按第3列（金额）降序（-1）排列。
•   复杂排序：=SORTBY(A2:G100, C2:C100, -1, B2:B100, 1) 先按部门（C列）降序，部门相同的再按姓名（B列）升序排。SORTBY更直观。

高级统计：分组与序列生成

13. GROUPBY - 数据透视表的公式形态

  =GROUPBY(分组列, 值列, 汇总函数, [总计模式], [筛选数组])

  （此函数目前仅在部分Beta版本可用，但代表了未来方向）

  它像一句SQL查询语言。=GROUPBY(A2:A100, B2:B100, SUM, 3) 对A列分组，对B列求和，参数3表示包含行列总计。一键生成分组汇总表。

14. SEQUENCE - 序列生成器

  =SEQUENCE(行数, [列数], [开始数], [步长])

  实战场景：

•   生成序号：=SEQUENCE(100) 生成1-100的垂直序列。
•   制作日历模型：=SEQUENCE(6, 7, DATE(2023,10,1)-WEEKDAY(DATE(2023,10,1),2)+1, 1) 可以生成一个以指定日期所在周为起点的6行7列日期矩阵，是制作动态日历的核心。
•   构建复杂索引：=SEQUENCE(, 10, 0, 100) 生成一个1行10列，从0开始，步长为100的数组：{0,100,200…900}。

效率跃迁：组合技与实战心法

  单个函数是利器，组合使用方能成绝招。

  心法一：用LET函数命名中间变量，让复杂公式可读

  处理复杂文本时，可以这样写：

  =LET(原始文本, A1, 分割后数组, TEXTSPLIT(原始文本, {"-", "_"}), 清理后文本, CONCAT(分割后数组), 清理后文本)

  这样，公式逻辑层次一目了然，易于调试和修改。

  心法二：动态引用整个“溢出区域”

  新函数的结果是一个动态数组（#标识的区域）。在另一个公式中引用这个结果时，直接引用其左上角的单元格即可，Excel会自动识别整个范围。例如：

  =SUM(UNIQUE(FILTER(销售额, 部门="A"))) // 对动态筛选+去重后的结果直接求和

  心法三：拥抱错误处理

  新函数组合容易产生#N/A等错误。用IFERROR包装：

  =IFERROR(INDEX(TEXTSPLIT(A1, "-"), 5), "无此分段")

新函数的“使用说明书”与未来

版本要求

•   Excel：需要Microsoft 365订阅版或Excel 2021及以上版本。动态数组是核心，旧版本不支持。
•   WPS：个人版最新版本已支持绝大部分新函数（如FILTER, UNIQUE, XLOOKUP等），但GROUPBY等个别最新函数可能暂未跟进。WPS的REGEXP（正则）和EVALUATE（计算文本算式）是特色功能。

核心概念：动态数组与“溢出”

  这是新函数的基石。你只需在一个单元格输入公式，结果会自动填充到相邻的空白单元格区域。这个区域是一个整体，无法单独编辑其中一格，被称为“溢出区域”。务必确保公式下方和右方有足够的空白单元格，否则会返回#SPILL!错误。

自我测验：你真的掌握了吗？

  学习完毕，不妨用三道题检验一下你的理解程度。答案藏在文末。

1. 如果你想从A2:A1000的订单号中（格式如“SH-20230405-001”），快速提取出所有不重复的日期（“20230405”部分），最高效的公式组合是？

• A. =UNIQUE(MID(A2:A1000, 4, 8))
• B. =UNIQUE(TEXTAFTER(TEXTBEFORE(A2:A1000, "-", 2), "-"))
• C. 使用数据透视表的“值筛选”功能
• D. 使用分列功能，再用删除重复项

2. 你有一个表格Data（A1:E1000），列依次是：日期、产品、地区、销售员、销售额。现在需要快速生成一个“动态看板”，展示“华东地区”、“销售员张三”在2023年4月所有销售额大于5000的记录，并按销售额从高到低排序。以下哪个公式最接近目标？

• A. =FILTER(Data, (Data[地区]="华东")*(Data[销售员]="张三")*(MONTH(Data[日期])=4)*(Data[销售额]>5000))
• B. =SORT(FILTER(Data, (Data[地区]="华东")*(Data[销售员]="张三")*(TEXT(Data[日期],"yyyymm")="202304")*(Data[销售额]>5000)), 5, -1)
• C. 使用高级筛选功能
• D. =SORTBY(FILTER(Data, (Data[地区]="华东")*(Data[销售员]="张三")*(Data[日期]>=DATE(2023,4,1))*(Data[日期]<=DATE(2023,4,30))*(Data[销售额]>5000)), Data[销售额], -1)

3. 你收到一列混乱的数据在A列，格式是“姓名-手机号-地址”，但分隔符有时是“-”，有时是“_”，有时是空格。你想把姓名、手机号、地址快速拆分成规整的三列，应该使用哪个函数？

• A. TEXTSPLIT(A1, "-_ ") // 用花括号指定多个分隔符
• B. 连续使用三次TEXTBEFORE和TEXTAFTER
• C. 使用分列功能，并手动指定多种分隔符
• D. 使用SUBSTITUTE函数将所有分隔符统一成一种，再进行拆分

测试题答案

  1. B。TEXTBEFORE取第二个“-”之前的部分（“SH-20230405”），再用TEXTAFTER取第一个“-”之后的部分（“20230405”），最后用UNIQUE去重。A选项假设了所有订单号长度一致，不严谨。C和D是传统方法，不够“高效”。

  2. D。这是最规范、可读性最高的写法。FILTER部分用多个*连接“且”条件，其中日期范围用>=和<=两个条件构造，比用TEXT或MONTH更灵活通用。外层用SORTBY按“销售额”列（显式引用）降序排序。B选项使用TEXT函数处理日期也可以，但SORT的第三个参数是列索引，不如SORTBY直观。A缺少排序。C是传统方法。

  3. A。TEXTSPLIT函数允许在第二个参数中，以常量数组形式（如{"-", "_", " "}）指定多个分隔符，这是处理这种混乱数据的完美解决方案。B理论上可行但公式冗长。C是传统GUI操作，无法用公式动态实现。D是旧函数的思路，多了一步。