学完42个Excel函数,发现最好用的居然不在里面

一、42 个常用函数，其实就 7 类

这次整理的 42 个常用函数，拆开看就是 7 大类、每类 6 个：

学习顺序建议：先学基础汇总（SUM 一家）→ 再学条件统计（SUMIF / COUNTIF 这一对）→ 最后学查找和动态数组。前面是地基，后面才是加速度。

42 个名字看着吓人，日常八成场景其实就靠这 7 条：

① 查找类：按姓名或编号把对应的值取回来，向左向右都能查

F2 要找的值（姓名或编号） ｜ A:A 去哪一列里找 ｜ C:C 找到后，返回同一行哪一列的值 ｜ 第 4 参数：找不到时返回什么——不写会报 #N/A，写成 "查无此人" 就显示这句提示 ｜ 第 5、6 参数管匹配方式和查找方向，默认就是精确匹配 + 从上往下找（代码是0），平时不用写

② 逻辑类：多档条件从上往下依次判断，比一层层嵌套 IF 好读

D2>=90 第一个条件 ｜ 条件成立时返回结果"A"——条件和结果成对出现，先条件、逗号、再结果 ｜ TRUE 最后的兜底条件（永远成立），等于"剩下的都归它"，返回结果"C"｜ 判断是从上往下走、命中即停，所以最严格的条件要放最前面 ｜ 要多个条件同时成立，可以把 AND / OR 套进条件里即可

③ 文本类：只改显示格式，原数值不动

B2 要转换的数值 ｜ "0.0%" 格式代码：按百分比显示、保留 1 位小数（0.123 显示成 12.3%） ｜ 换日期用 "yyyy-mm-dd"、要千分位用 "#,##0"，需要什么格式就套什么代码（不知道格式代码的，可以右键单元格看单元格格式，选一个格式去左边“自定义”里查看代码） ｜ 公式的结果是文本，只负责显示，不能拿回去参与求和

④ 日期类：算两个日期隔了几年，算工龄、账龄都用它

A2 开始日期（比如入职日期） ｜ TODAY() 结束日期，跟着当天走 ｜ "Y" 单位：Y 整年、M 整月、D 天数；另外还有 "YM" 不满一年的零头月数、"MD" 不满一月的零头天数 ｜ 顺序不能反——结束日期早于开始日期会报 #NUM! ｜ 它是隐藏函数，输入时没有参数提示，参数全靠记

⑤ 汇总类：多条件求和，多一个条件就多写一组「区域, 条件」

D:D 求和区域，写在最前面 ｜ A:A, F2 第一组条件：条件区域 + 条件（这里 F2 是引用单元格） ｜ B:B, ">0" 第二组条件；条件里带比较符时必须用引号包住 ｜ 注意和 SUMIF 的顺序正好相反——SUMIF 是条件在前、求和区域在后｜ 条件最多能挂到 127 组，一般够用

⑥ 统计类：多条件计数，和 SUMIFS 一个套路

A:A, F2 第一组条件：条件区域 + 条件 ｜ B:B, ">100" 第二组条件，多一个条件就多写一组 ｜ 没有求和区域，因为只数个数 ｜ 几组条件是"并且"关系，全部满足才计数

⑦ 动态数组：一个条件筛出一片结果，写法在第二节第 3 小节细讲

A2:D100 要返回的整片区域，想返回几列就写到几列 ｜ C2:C100="华东" 筛选条件，算出来是一串 TRUE / FALSE；行数必须和返回区域一致 ｜ 第 3 参数：一条都没命中时返回什么（比如 "无记录"），不写会报 #CALC! ｜ 结果会自动溢出到下方，占用的单元格不用手动拉公式

二、三个新函数：TOCOL、VSTACK、FILTER

这 7 类里，我觉得最值得单独展开的就是动态数组这一类——一个公式直接吐出一片结果，不用下拉填充。

下面这三个是我用下来最省事的：FILTER 本来就在上面那份清单里，TOCOL 和 VSTACK 则是整理时另外碰到的，两个都没进那 42 个的名单。

1. TOCOL：把多列压成一列

场景一：多列名单去重。三个部门各自的签到名单散在三列里，要一份不重复的总名单：

TOCOL 先把多列压成一列（第二参数 1 = 忽略空白），UNIQUE 再去掉重复项。以前要"复制到一列→删除重复项"两步操作，现在一个公式搞定。

场景二：二维表转一维。行是项目、列是月份的交叉表，做数据分析前得拉平成一列：

按行扫描拉成一列，空白单元格自动跳过。这就是 Power Query 里的"逆透视"，一个公式就办了。

场景三：跨表合并。6 张结构一样的分表（北京、广州、南京、苏州、西安、郑州），要汇总进一张总表：

首尾表名加冒号（三维引用），中间所有工作表这一列一口气合并。再横向拖拽填充，整张表就展开了。

2. VSTACK：把几张表上下叠成一张

语法很简单：VSTACK(区域1, 区域2, …)，就是把多个区域垂直摞起来。单看平平无奇，跟别的函数一组合就是大招——5 个实战用法：

① 跨表查询，不用来回切表：

两张表叠成一张，VLOOKUP 按姓名直接查金额，一步返回。

② 多表排序，一键排名：

叠完直接按第 2 列降序，金额从高到低排好。

③ 多表合并，总表不用手工拼：

只要格式一样，中间隔几张表都能一键捏成一张总表。

④ 条件筛选，叠完接着筛：

先叠再筛，只保留金额大于 200 的行，结果自动溢出到下方单元格。

⑤ 分类求和：叠成一张总表之后，套一个 SUMIF 就能按类别汇总——比在每张分表里各算一遍再手工加总，稳得多，分表一更新结果也跟着走。

PS：用 VSTACK 叠列数对不齐的区域时，缺口会显示 #N/A，外面套一层 IFNA(公式, "") 就干净了。

3. FILTER：按条件筛出一片结果

语法是 FILTER(要返回的区域, 筛选条件, [没结果时返回什么])，条件成立的行整行溢出，不用下拉填充。三个常见用法：

① 单条件筛选。把华东区的记录整行拉出来：

② 多条件筛选。「并且」用 * 连接，「或者」用 + 连接，每个条件都要加括号：

上面这条是"华东 并且 金额大于 1000"；把中间的 * 换成 +，就变成"华东 或者 金额大于 1000"，两种写法只差一个符号。

③ 筛不到时给个兜底。条件太严一条都没命中时，FILTER 会报 #CALC!，把第三个参数写上默认值就干净了：

FILTER 跟 VSTACK 是天生一对——前面 VSTACK 案例④里那条「叠完再筛」的公式，就是两者的组合。

三、三点学习心得

1. 别按字母顺序背，按场景分类学。查找类解决"去别的表拿数"，汇总类解决"算总数"，动态数组解决"一个公式吐一片"。知道自己卡在哪一类，就去补哪一类。

2. 老函数不是没用，是"稳"。VLOOKUP、SUMIF 这些我到现在天天在用；新函数的价值是把"复制粘贴 + 手工整理"这种活儿整个干掉，而不是取代谁。

3. 学新函数先看版本。公司电脑是老版本的话，这些函数直接报 #NAME?。先确认手里的 Excel 能不能用，再决定投入时间。

这几篇笔记我自己也是边学边整理。下一篇打算把动态数组里另外几个——UNIQUE、SORT、SORTBY——单独写一篇。你手上有没有哪个函数一直没搞明白的？评论区丢过来，我优先写它。