ARTICLE · 1092032
Excel 学习笔记 · 函数篇
Excel 学习笔记 · 函数篇
百灵鸟的笔记本 —— 新系列开篇。跟着网课学 Excel，把函数和技巧整理成篇。
一、函数全景速查
先上速查表。42 个高频函数按场景分类，忘了就回来翻：
基础统计
文本处理
查找引用
日期时间
逻辑判断
数学计算
二、高频操作技巧
快捷键与鼠标操作
1. Ctrl + ;：快速输入当天日期2. 双击列的左右边线：调整至合适列宽 3. 双击单元格上下边线：跳到最前 / 最后一个单元格 4. 日期单元格右键拖拽：可选按天 / 工作日 / 年 / 月填充 5. 日期转星期：自定义单元格格式 aaa6. F4：切换相对引用 / 绝对引用 7. Ctrl + 回车：批量填充
查找替换与通配符
替换时勾选「单元格匹配」：单元格必须与查找内容完全相同才会被替换。
通配符三兄弟：
* | |
? | |
~ |
定位条件的四个妙用
路径：开始 → 查找和选择 → 定位条件
原理：合并单元格解散后，Excel 只认为第一行有值，其余全是空值——先定位空值，再一键填充。
筛选的三个细节
1. 筛选后只复制可见单元格：定位条件 → 可见单元格（否则隐藏行也会被复制走） 2. 高级筛选不重复值：数据 → 高级 → 勾选「选择不重复的记录」 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. 查找列必须位于区域第一列，VLOOKUP 只能向右找 2. 精确匹配一律写 0，别省3. 按 F4 锁定查找区域（ $A$2:$D$100），防止下拉偏移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. 给每个二级选项区域定义名称，名称 = 一级选项文本（如"北京"对应 F1:F3 的三个区） 2. 一级下拉：数据验证 → 序列 → 来源 =$E$1:$E$33. 二级下拉：数据验证 → 序列 → 来源 =INDIRECT(A1)
一级选"北京"，二级就只剩北京的区可选。
九、数学函数与舍入
记忆技巧：INT 是"地板"，负数要小心——INT(-3.01) 不是 -3 而是 -4。
MOD 除了判身份证性别，还能把假期小时数换算成"X 天 Y 小时"：
=INT(A1/8) & "天" & MOD(A1,8) & "小时"OFFSET 动态图表、宏表函数、数据透视表，留给下一篇。
百灵鸟的笔记本 · Excel 函数篇网课啃完一章记一章，整理成篇方便以后翻。