Excel 学习笔记 · 函数篇

百灵鸟的笔记本 —— 新系列开篇。跟着网课学 Excel，把函数和技巧整理成篇。

一、函数全景速查

先上速查表。42 个高频函数按场景分类，忘了就回来翻：

基础统计

文本处理

查找引用

日期时间

逻辑判断

数学计算

二、高频操作技巧

快捷键与鼠标操作

1. 1. Ctrl + ;：快速输入当天日期
2. 2. 双击列的左右边线：调整至合适列宽
3. 3. 双击单元格上下边线：跳到最前 / 最后一个单元格
4. 4. 日期单元格右键拖拽：可选按天 / 工作日 / 年 / 月填充
5. 5. 日期转星期：自定义单元格格式 aaa
6. 6. F4：切换相对引用 / 绝对引用
7. 7. Ctrl + 回车：批量填充

查找替换与通配符

替换时勾选「单元格匹配」：单元格必须与查找内容完全相同才会被替换。

通配符三兄弟：

定位条件的四个妙用

路径：开始 → 查找和选择 → 定位条件

原理：合并单元格解散后，Excel 只认为第一行有值，其余全是空值——先定位空值，再一键填充。

筛选的三个细节

1. 1. 筛选后只复制可见单元格：定位条件 → 可见单元格（否则隐藏行也会被复制走）
2. 2. 高级筛选不重复值：数据 → 高级 → 勾选「选择不重复的记录」
3. 3. 用公式区域做筛选条件：条件区域的表头要「故意写错」，不能与原表头相同

三、条件计数与求和：COUNTIF / SUMIF 家族

COUNTIF：单条件计数

=COUNTIF(范围, 条件)

典型应用：查重复数据、数据有效性、条件格式。

⚠️ 15 位数字陷阱：COUNTIF 对超过 15 位的数字只识别前 15 位，6223888811112222678 和 6223888811112222223 会被判为相等。正确写法是拼接通配符：=COUNTIF(A:A, A2&"*")。

COUNTIFS：多条件计数

=COUNTIFS(条件区域1, 条件1, [条件区域2, 条件2], ...)

SUMIF 与 SUMIFS：参数顺序正好相反

=SUMIF(条件区域, 条件, [求和区域])=SUMIFS(求和区域, 条件区域1, 条件1, [条件区域2, 条件2], ...)

SUMIF 常见用法：

记忆点：SUMIF 的求和区域在最后，SUMIFS 的求和区域在最前。一个后置、一个前置，写混必报错。

⚠️ 注意：条件区域和求和区域行数必须一致；文本和通配符要加双引号，数字和单元格引用不用。

四、查找引用三巨头

VLOOKUP

=VLOOKUP(查找值, 表格区域, 返回列号, 匹配模式)

四条铁律：

1. 1. 查找列必须位于区域第一列，VLOOKUP 只能向右找
2. 2. 精确匹配一律写 0，别省
3. 3. 按 F4 锁定查找区域（$A$2:$D$100），防止下拉偏移
4. 4. 找不到返回 #N/A，用 IFERROR 兜底：=IFERROR(VLOOKUP(...), "未找到")

近似匹配：区间分级（成绩等级、税率、折扣都靠它）

=VLOOKUP(C2, A$2:B$4, 2, 1)

C2 = 3000 时返回 5%（3000 ≥ 1000 且 < 5000）。区间下限必须升序排列。

XLOOKUP：新一代查找

=XLOOKUP(查找值, 查找数组, 返回数组, [未找到时返回的值], [匹配模式], [搜索模式])

核心优势：默认精确匹配、支持向左查找、未找到值直接写在第四参数（不用嵌套 IFERROR）、还能设置搜索模式从后往前找。

INDEX + MATCH

=INDEX(返回值列, MATCH(查找值, 查找列, 0))

向左查找（VLOOKUP 做不到）——根据姓名反查学号：

=INDEX(A2:A100, MATCH(F2, B2:B100, 0))

指定返回区域中的第几列：

=INDEX(B2:D100, MATCH(E2, A2:A100, 0), 2)

三者怎么选

五、多条件查找的两种写法

写法一：辅助列

VLOOKUP 不支持多条件，就自己造一列合并条件：在原始表最前面加一列 =C2&D2（姓名&月份），然后：

=VLOOKUP(F2&G2, A$2:E$100, 4, 0)

写法二：LOOKUP 万能公式（不用辅助列）

=LOOKUP(1, 0/((条件1区域=条件1)*(条件2区域=条件2)), 返回区域)

例：销售部里找张三的销售额：

=LOOKUP(1, 0/((A:A="销售部")*(B:B="张三")), C:C)

拆解：

• • 条件满足返回 TRUE、不满足返回 FALSE，相乘后全部满足 = 1，否则 = 0
• • 0/1 = 0，0/0 = #DIV/0!
• • LOOKUP 找 1 找不到，就返回「小于等于 1 的最大值」——也就是那些 0 的位置
• • 多个匹配时返回最后一个，这是 LOOKUP 的独门优势（VLOOKUP 只能返回第一个）

INDEX + MATCH 也有数组公式版本：

=INDEX(C2:C100, MATCH(1, (A2:A100=E2)*(B2:B100=F2), 0))

LOOKUP 模糊匹配：区间定等级

=LOOKUP(B2, {0,60,80,90}, {"不及格","及格","良好","优秀"})

查找向量必须升序，返回「小于等于查找值的最大值」对应的等级。65 分落在 60~80，返回"及格"。

六、文本函数与身份证实战

基础函数一张表：

记忆点：LENB(A1)-LEN(A1) > 0，说明单元格里混了汉字。

一张身份证，四个公式全覆盖（B2 为身份证号）：

拆解（性别）：身份证第 17 位代表性别，奇数男、偶数女。MID 取出第 17 位 → MOD 除 2 取余 → IF 判断。

七、日期函数速查

DATEDIF 还有三个进阶类型：

八、INDIRECT：把文本变成引用

=INDIRECT(文本引用, [a1])

⚠️ 工作表名含空格或特殊字符时必须用单引号包裹：=INDIRECT("'Sheet 2'!A1")。

经典应用：二级下拉列表

1. 1. 给每个二级选项区域定义名称，名称 = 一级选项文本（如"北京"对应 F1:F3 的三个区）
2. 2. 一级下拉：数据验证 → 序列 → 来源 =$E$1:$E$3
3. 3. 二级下拉：数据验证 → 序列 → 来源 =INDIRECT(A1)

一级选"北京"，二级就只剩北京的区可选。

九、数学函数与舍入

记忆技巧：INT 是"地板"，负数要小心——INT(-3.01) 不是 -3 而是 -4。

MOD 除了判身份证性别，还能把假期小时数换算成"X 天 Y 小时"：

=INT(A1/8) & "天" & MOD(A1,8) & "小时"

OFFSET 动态图表、宏表函数、数据透视表，留给下一篇。

百灵鸟的笔记本 · Excel 函数篇网课啃完一章记一章，整理成篇方便以后翻。