Excel · 第 3 篇
常用函数与公式
透视表能做 80% 的汇总分析，但剩下 20% 需要函数和公式来完成——数据匹配、条件计算、文本处理、日期运算，这些都是函数的主场。
本篇按使用频率排列，覆盖数据分析师日常最高频的 Excel 函数。
第一梯队：查找匹配函数
VLOOKUP — 使用频率最高的函数
VLOOKUP 用于根据关键字从另一张表中查找对应的值，是数据分析师用得最多的函数，没有之一。
语法：
=VLOOKUP(查找值, 查找范围, 返回列号, 匹配模式)
| 参数 | 说明 |
|---|---|
| 查找值 | 你要查的关键字（如用户 ID） |
| 查找范围 | 包含关键字和返回值的表格范围 |
| 返回列号 | 要返回查找范围中第几列的值 |
| 匹配模式 | 0 = 精确匹配（99% 的场景用 0） |
案例：匹配用户城市信息
你有两张表：订单表只有 user_id 和 amount，用户表有 user_id 和 city。需要把城市信息匹配到订单表中。
订单表（Sheet1）：
| user_id | amount |
|---|---|
| U1001 | 258 |
| U1002 | 136 |
用户表（Sheet2）：
| user_id | city |
|---|---|
| U1001 | 北京 |
| U1002 | 上海 |
在订单表 C2 中写：
=VLOOKUP(A2, Sheet2!$A:$B, 2, 0)
VLOOKUP 的局限：
| 局限 | 说明 |
|---|---|
| 只能向右查 | 查找值必须在查找范围的第一列 |
| 查找范围不能有合并单元格 | 否则结果错误 |
| 大数据量性能差 | 10 万行以上会明显变慢 |
| 插入列后可能出错 | 返回列号是硬编码的数字 |
INDEX + MATCH — VLOOKUP 的升级版
INDEX + MATCH 组合解决了 VLOOKUP 的所有局限，是进阶分析师的首选。
语法：
=INDEX(返回范围, MATCH(查找值, 查找范围, 0))
MATCH(查找值, 查找范围, 0)— 返回查找值在查找范围中的行号INDEX(返回范围, 行号)— 返回指定范围中对应行号的值
案例：同样匹配用户城市，但这次用户表的列顺序是 city 在前、user_id 在后。
用户表（Sheet2）：
| city | user_id |
|---|---|
| 北京 | U1001 |
| 上海 | U1002 |
VLOOKUP 做不到（因为查找值不在第一列），但 INDEX-MATCH 可以：
=INDEX(Sheet2!$A:$A, MATCH(A2, Sheet2!$B:$B, 0))
INDEX-MATCH vs VLOOKUP 对比：
| 对比维度 | VLOOKUP | INDEX-MATCH |
|---|---|---|
| 查找方向 | 只能向右 | 任意方向 |
| 插入列的影响 | 会出错 | 不受影响 |
| 性能 | 较慢 | 更快 |
| 可读性 | 更直观 | 略复杂 |
| 推荐程度 | 简单场景可用 | 强烈推荐 |
XLOOKUP — 新一代查找函数（Excel 365/2021+）
如果你使用的是 Excel 365 或 2021 版本，XLOOKUP 是最简洁的选择：
=XLOOKUP(查找值, 查找范围, 返回范围, [找不到时的默认值])
=XLOOKUP(A2, Sheet2!$A:$A, Sheet2!$B:$B, "未找到")
优点：语法简洁、支持任意方向、可设置默认值。
第二梯队：条件判断函数
IF — 基础条件判断
语法：
=IF(条件, 条件为真的返回值, 条件为假的返回值)
案例：判断订单等级
=IF(B2>=1000, "大额订单", "普通订单")
嵌套 IF — 多条件判断
=IF(B2>=5000, "VIP",
IF(B2>=1000, "大额",
IF(B2>=100, "普通", "小额")))
建议：超过 3 层嵌套就用 IFS 函数或者 SWITCH 函数替代，否则可读性极差。
IFS — 多条件判断（Excel 2019+）
=IFS(B2>=5000, "VIP", B2>=1000, "大额", B2>=100, "普通", TRUE, "小额")
IFERROR — 处理错误值
当 VLOOKUP 找不到值时会返回 #N/A 错误。用 IFERROR 包裹可以给出友好的默认值：
=IFERROR(VLOOKUP(A2, Sheet2!$A:$B, 2, 0), "未找到")
第三梯队：条件统计函数
COUNTIF / COUNTIFS — 条件计数
COUNTIF：单条件计数
=COUNTIF(城市列, "北京")
统计城市列中”北京”出现了多少次。
COUNTIFS：多条件计数
=COUNTIFS(城市列, "北京", 品类列, "电子产品")
统计”北京 + 电子产品”的订单数量。
SUMIF / SUMIFS — 条件求和
SUMIF：单条件求和
=SUMIF(城市列, "北京", 销售额列)
SUMIFS：多条件求和
=SUMIFS(销售额列, 城市列, "北京", 品类列, "电子产品")
注意：SUMIFS 的参数顺序和 SUMIF 不同！SUMIFS 的求和范围在前，SUMIF 的求和范围在后。
AVERAGEIF / AVERAGEIFS — 条件平均值
=AVERAGEIFS(销售额列, 城市列, "北京", 渠道列, "线上")
计算北京线上渠道的平均销售额。
实战案例：用条件函数做销售分析
| 分析需求 | 公式 |
|---|---|
| 北京的订单总数 | =COUNTIF(C:C, "北京") |
| 北京的总销售额 | =SUMIF(C:C, "北京", F:F) |
| 北京电子产品的总销售额 | =SUMIFS(F:F, C:C, "北京", D:D, "电子产品") |
| 3月份的平均客单价 | =AVERAGEIF(B:B, ">=2025-03-01", F:F) 需配合日期条件 |
| 销售额>1000的订单占比 | =COUNTIF(F:F, ">1000") / COUNTA(F:F) |
第四梯队：文本处理函数
拼接与拆分
| 函数 | 用途 | 示例 |
|---|---|---|
CONCATENATE 或 & |
文本拼接 | =A2 & "-" & B2 |
TEXTJOIN |
带分隔符拼接 | =TEXTJOIN(",", TRUE, A2:D2) |
LEFT |
取左边 N 个字符 | =LEFT(A2, 4) → “2025” |
RIGHT |
取右边 N 个字符 | =RIGHT(A2, 2) → “01” |
MID |
取中间字符 | =MID(A2, 5, 2) → “03” |
LEN |
计算文本长度 | =LEN(A2) → 10 |
查找与替换
| 函数 | 用途 | 示例 |
|---|---|---|
FIND |
查找文本位置（区分大小写） | =FIND("@", "[email protected]") → 2 |
SEARCH |
查找文本位置（不区分大小写） | =SEARCH("test", "Testing") → 1 |
SUBSTITUTE |
替换文本 | =SUBSTITUTE(A2, "北京市", "北京") |
REPLACE |
按位置替换 | =REPLACE(A2, 1, 3, "XXX") |
格式化
| 函数 | 用途 | 示例 |
|---|---|---|
TRIM |
去除首尾空格 | =TRIM(" 北京 ") → “北京” |
CLEAN |
去除不可打印字符 | =CLEAN(A2) |
UPPER |
转大写 | =UPPER("abc") → “ABC” |
LOWER |
转小写 | =LOWER("ABC") → “abc” |
PROPER |
首字母大写 | =PROPER("hello world") → “Hello World” |
TEXT |
数值转文本格式 | =TEXT(0.85, "0.0%") → “85.0%” |
实战案例：从订单编号中提取信息
订单编号格式：BJ-2025-03-00123（城市缩写-年-月-序号）
| 提取内容 | 公式 |
|---|---|
| 城市缩写 | =LEFT(A2, 2) → “BJ” |
| 年份 | =MID(A2, 4, 4) → “2025” |
| 月份 | =MID(A2, 9, 2) → “03” |
| 序号 | =RIGHT(A2, 5) → “00123” |
第五梯队：日期函数
获取日期部分
| 函数 | 用途 | 示例（假设 A2 = 2025-03-15） |
|---|---|---|
YEAR |
提取年份 | =YEAR(A2) → 2025 |
MONTH |
提取月份 | =MONTH(A2) → 3 |
DAY |
提取日 | =DAY(A2) → 15 |
WEEKDAY |
星期几 | =WEEKDAY(A2, 2) → 6（周六） |
WEEKNUM |
第几周 | =WEEKNUM(A2) → 11 |
日期计算
| 函数 | 用途 | 示例 |
|---|---|---|
TODAY() |
返回今天日期 | =TODAY() → 2025-03-15 |
DATEDIF |
两个日期之间的间隔 | =DATEDIF(A2, B2, "D") → 天数 |
EDATE |
向前/向后推 N 个月 | =EDATE(A2, -1) → 上个月同日 |
EOMONTH |
某月的最后一天 | =EOMONTH(A2, 0) → 2025-03-31 |
NETWORKDAYS |
工作日天数 | =NETWORKDAYS(A2, B2) → 排除周末 |
实战案例：计算用户生命周期
| 需求 | 公式 |
|---|---|
| 用户注册天数 | =TODAY() - 注册日期 |
| 用户注册月数 | =DATEDIF(注册日期, TODAY(), "M") |
| 上月同日 | =EDATE(TODAY(), -1) |
| 本月第一天 | =EOMONTH(TODAY(), -1) + 1 |
| 本月最后一天 | =EOMONTH(TODAY(), 0) |
| 30天内是否有购买 | =IF(最近购买日期 >= TODAY()-30, "活跃", "沉默") |
实战综合案例：构建销售分析看板
场景
你是一家零售公司的数据分析师，需要在 Excel 中构建一个销售分析看板。原始数据是一份订单明细表，包含：订单 ID、日期、门店、品类、销售额、数量。
需要计算的指标
| 指标 | 公式 |
|---|---|
| 本月总销售额 | =SUMIFS(销售额列, 日期列, ">="&本月第一天, 日期列, "<="&本月最后一天) |
| 上月总销售额 | =SUMIFS(销售额列, 日期列, ">="&上月第一天, 日期列, "<="&上月最后一天) |
| 环比增长率 | =(本月销售额 - 上月销售额) / 上月销售额 |
| 本月订单数 | =COUNTIFS(日期列, ">="&本月第一天, 日期列, "<="&本月最后一天) |
| 本月客单价 | =本月销售额 / 本月订单数 |
| 各门店排名 | =RANK(该门店销售额, 所有门店销售额列) |
| TOP 门店名称 | =INDEX(门店列, MATCH(1, RANK列, 0)) |
辅助公式
本月第一天 = EOMONTH(TODAY(), -1) + 1
本月最后一天 = EOMONTH(TODAY(), 0)
上月第一天 = EOMONTH(TODAY(), -2) + 1
上月最后一天 = EOMONTH(TODAY(), -1)
函数使用的最佳实践
1. 用绝对引用锁定范围
在 VLOOKUP 的查找范围中使用 $ 号锁定，否则下拉填充时范围会偏移：
=VLOOKUP(A2, $G$2:$H$100, 2, 0) ← 正确：用 $ 锁定
=VLOOKUP(A2, G2:H100, 2, 0) ← 错误：下拉填充后范围会偏移
2. 用命名范围提高可读性
给常用的范围命名（公式 → 名称管理器）：
=VLOOKUP(A2, 用户表, 2, 0) ← 比 Sheet2!$A$2:$H$1000 更易读
3. 用 IFERROR 处理异常
任何查找函数都应该用 IFERROR 包裹：
=IFERROR(VLOOKUP(A2, 用户表, 2, 0), "未匹配")
4. 避免整列引用
=VLOOKUP(A2, A:B, 2, 0) 会扫描整列 100 万行，非常慢。尽量指定精确范围。
5. 用辅助列简化复杂公式
如果一个公式嵌套了 5 层以上，把中间步骤拆到辅助列，方便调试和维护。
练习题
-
你有一份订单表和一份产品表。订单表有”产品ID”和”数量”，产品表有”产品ID”、”产品名称”、”单价”。请用 VLOOKUP 和 INDEX-MATCH 两种方式，在订单表中匹配产品名称和单价，并计算每笔订单的总金额。
-
给定一份包含”日期”和”销售额”的数据，请计算：本月累计销售额、上月同期累计销售额、同比增长率。
-
一份用户数据包含”注册日期”和”最近登录日期”。请用函数将用户分为”活跃用户”（30天内登录）、”沉默用户”（30-90天未登录）、”流失用户”（90天以上未登录）三类。
小结
| 函数类别 | 核心函数 | 使用场景 |
|---|---|---|
| 查找匹配 | VLOOKUP、INDEX-MATCH、XLOOKUP | 跨表匹配数据 |
| 条件判断 | IF、IFS、IFERROR | 分类、异常处理 |
| 条件统计 | COUNTIFS、SUMIFS、AVERAGEIFS | 多条件汇总计算 |
| 文本处理 | LEFT、MID、SUBSTITUTE、TRIM | 数据清洗、信息提取 |
| 日期计算 | YEAR、MONTH、DATEDIF、EOMONTH | 时间维度分析 |
下一篇我们将学习 图表制作与美化——用专业的可视化呈现你的分析结果。