ARTICLE · 980923
这13组Excel新函数,复制就能用,建议收藏
小伙伴们，大家好。
今天这篇，我把自己平时用得最多、最顺手、最能救命的13组新函数整理出来了。不废话，直接上公式，复制粘贴就能用。建议先收藏，下次遇到类似场景，直接翻出来套。
一、多表查询：再也不用一个个VLOOKUP了
场景： 左边一张员工表，右边一张薪资表，要根据工号把薪资查过来。
表格实例（复制到A1:E5）：
查询公式（放在G2）：=VLOOKUP(F2,VSTACK($A$2:$B$4,$D$2:$E$4),2,0)
以前做多表查询，是不是得先复制到一张表里，再VLOOKUP？现在不用了。VSTACK直接把两张表上下拼起来，拼完当成一个查询区域，VLOOKUP照常跑。
说人话： 以前要手动搬数据，现在函数自己帮你搬。
二、自动汇总多张表：Ctrl+T之后，一行公式搞定
场景： 1月、2月两张表，格式一样，想合并成一张。
表1（Sheet名：1月）：
表2（Sheet名：2月）：
先分别按Ctrl+T转成超级表，命名为"表1""表2"。
合并公式：=VSTACK(表1,表2)
结果就是4行数据自动堆在一起。
什么时候用： 每月各部门交上来的表格式一样，你要合并汇总的时候。
三、提取前几名：排序+截取，一步到位
场景： 一张成绩表，想快速取前3名。
表格实例（A1:C7）：
取前3名公式：=TAKE(SORT(A2:C7,3,-1),3)
取后3名：=TAKE(SORT(A2:C7,3,-1),-3)
SORT先按第3列从大到小排，TAKE再取前3行。想取后3名？把最后的3改成-3就行。
说人话： 以前要排序、复制、再删，现在一个公式直接出结果。
四、随机抽取：抽奖、选人、抽样都能用
场景： 从10个人里随机抽2个。
表格实例（A2:A11）：
随机抽2人公式：=TAKE(SORTBY(A2:A11,RANDARRAY(COUNTA(A2:A11))),2)
想抽3个，把最后的2改成3。
RANDARRAY生成随机数，SORTBY按随机数排序，TAKE取前2个。
什么时候用： 年会抽奖、随机分组、从名单里抽人检查。
五、多表汇总：跨sheet引用，一个公式搞定
场景： 1月到3月，三张sheet，A2:A15都是销量数据，想合并成一列。
1月sheet：
2月sheet：
3月sheet：
汇总公式：=TOCOL('1月:3月'!A2:A15,3)
输入时先打公式，再点1月sheet，按住Shift点3月sheet，选A2:A15，第二参数写3，回车，向右拖。
说人话： 以前要一个个sheet点过去复制，现在一个公式拉完。
六、多列转一列：数据堆成一列，方便透视
场景： 两列数据，想堆成一列。
表格实例（A3:B6）：
转换公式：=TOCOL(A3:B6)
结果变成一列8行：张三、100、李四、120……
什么时候用： 做数据透视前，需要把多列指标堆成一列的时候。
七、多行转一行：横向堆数据
场景： 三行数据，想横着排成一行。
表格实例（A1:F3）：
转换公式：=TOROW(A1:F3)
结果变成一行18个数字。
八、一行转多列：按固定列数换行
场景： 一列18个数字，想每3个一行。
表格实例（A2:A19）：
转换公式：=WRAPROWS(A2:A19,3,"")
结果变成6行3列。不够一行的，用"填充值"补上。
说人话： 比如你把一列姓名，变成3列排班表。
九、一列转多行：竖向换列
场景： 同样一列18个数字，想每3个一列往下排。
转换公式：=WRAPCOLS(A2:A19,3,"")
结果变成3列6行。
十、公式透视：不用拖透视表，直接出结果
场景： 商品名称、采购方式、数量，想按商品和采购方式求和。
表格实例（A1:D10）：
透视公式：=PIVOTBY(B1:B10,A1:A10,D1:D10,SUM)
第一参数行字段，第二参数列字段，第三参数值，第四参数SUM。
说人话： 以前要拖透视表，现在一个公式直接出透视结果。
十一、正则函数：提取文字、数字，精准命中
场景： 从乱文本里提取中文或数字。
表格实例（B3、B9）：
提取文字公式（B3）：=REGEXEXTRACT(B3,"[一-龟]+",1)
提取数字公式（B9）：=REGEXEXTRACT(B9,"[0-9.]+",1)
[一-龟]+匹配中文，[0-9.]+匹配数字和小数点。
什么时候用： 从一堆乱糟糟的文本里，只把你要的部分抠出来。
十二、合并单元格计算：破解"合并单元格不能计算"的魔咒
场景： 部门列是合并单元格，想按部门求和。
表格实例（A2:C12）：
求和公式：
=VSTACK({"部门","销量"},GROUPBY(SCAN(,A2:A12,LAMBDA(x,y,IF(y<>"",y,x))),C2:C12,SUM,,0))A2:A12是合并单元格区域，C2:C12是销量区域。复制公式，只需改这两处。
说人话： 以前合并单元格是噩梦，现在一个公式直接算。
十三、批量替换：一次换掉多个单位
场景： 数量后面带单位，想批量去掉单位变成数字。
表格实例（C2:C5）：
替换公式：=REDUCE(C2,{"袋";"kg";"个"},LAMBDA(x,y,SUBSTITUTE(x,y,"")))*1
把"袋、kg、个"批量替换成空，再乘1变成数字。
什么时候用： 从系统导出的数据，单位混在一起，要批量清理的时候。
最后说两句
这13组函数，不是让你一次全记住。你只需要记住三个场景：多表合并、数据转换、批量计算。 下次遇到类似问题，翻回来找对应的公式，复制、改区域、回车，完事。
如果你觉得这篇有用，点个"在看"，或者转发给那个还在熬夜拉表的同事。说不定，他能少加一天班。