跳到主要内容

高级公式

问题​

数据分析中常用的 Excel 高级公式有哪些？如何选择合适的查找函数？

答案​

查找函数对比​

函数优势局限推荐度
VLOOKUP简单直观只能向右查找、整列匹配⭐⭐⭐
INDEX-MATCH灵活、可左向查找公式稍长⭐⭐⭐⭐
XLOOKUP最强大、默认精确匹配Office 365+⭐⭐⭐⭐⭐

VLOOKUP​

=VLOOKUP(查找值, 表格区域, 列号, 匹配类型)
参数说明
查找值要匹配的值
表格区域查找范围（查找值必须在第一列）
列号返回第几列的值
匹配类型FALSE=精确匹配，TRUE=近似匹配
=VLOOKUP(A2, 产品表!$A:$D, 3, FALSE)
VLOOKUP 常见陷阱
  • 查找值必须在区域的第一列——无法向左查找
  • 列号是硬编码数字，插入/删除列后容易出错
  • 近似匹配（TRUE）要求数据已排序
  • 大数据量下性能较差

INDEX-MATCH 组合​

=INDEX(返回区域, MATCH(查找值, 查找区域, 匹配类型))
=INDEX(C:C, MATCH(A2, B:B, 0))

优势：

  • 可以向左查找
  • 返回区域和查找区域可以分开
  • 插入列不影响结果
  • 性能优于 VLOOKUP

XLOOKUP（推荐）​

=XLOOKUP(查找值, 查找区域, 返回区域, [未找到], [匹配模式], [搜索模式])
=XLOOKUP(A2, 产品表!A:A, 产品表!C:C, "未找到")
匹配模式值说明
精确匹配0默认
通配符2支持 * ?
下一个较小值-1近似匹配
下一个较大值1近似匹配
XLOOKUP 高级用法
// 返回多列
=XLOOKUP(A2, 产品表!A:A, 产品表!B:D)

// 反向搜索（从后往前）
=XLOOKUP(A2, 区域, 返回区域, , 0, -1)

条件统计函数​

函数用途示例
COUNTIF单条件计数=COUNTIF(A:A, "北京")
COUNTIFS多条件计数=COUNTIFS(A:A, "北京", B:B, ">100")
SUMIF单条件求和=SUMIF(A:A, "北京", C:C)
SUMIFS多条件求和=SUMIFS(C:C, A:A, "北京", B:B, ">100")
AVERAGEIF条件平均=AVERAGEIF(A:A, "北京", C:C)
MAXIFS条件最大值=MAXIFS(C:C, A:A, "北京")
SUMIF vs SUMIFS 参数顺序不同
  • SUMIF(条件区域, 条件, 求和区域) — 求和区域在最后
  • SUMIFS(求和区域, 条件区域1, 条件1, ...) — 求和区域在最前

文本函数​

函数用途示例
LEFT/RIGHT/MID提取子串=LEFT(A2, 4) → 前4字符
TRIM去多余空格=TRIM(A2)
SUBSTITUTE替换文本=SUBSTITUTE(A2, "-", "")
TEXT格式化=TEXT(A2, "YYYY-MM")
TEXTJOIN拼接=TEXTJOIN(",", TRUE, A2:A10)
CONCAT合并=CONCAT(A2, "-", B2)

日期函数​

函数用途示例
YEAR/MONTH/DAY提取日期部分=YEAR(A2)
EOMONTH月末日期=EOMONTH(A2, 0) → 本月末
DATEDIF日期差=DATEDIF(A2, B2, "M") → 月差
WEEKDAY星期几=WEEKDAY(A2, 2) → 1=周一
WORKDAY工作日=WORKDAY(A2, 10) → 10个工作日后

数组公式与溢出​

Excel 365 支持动态数组，公式自动溢出到相邻单元格：

函数用途示例
UNIQUE去重=UNIQUE(A2:A100)
SORT排序=SORT(A2:B100, 2, -1)
FILTER条件筛选=FILTER(A:C, B:B>100)
SEQUENCE生成序列=SEQUENCE(10, 1, 1)
SORTBY按指定列排序=SORTBY(A:C, C:C, -1)
RANDARRAY随机数组=RANDARRAY(5, 3)
动态数组组合技
// 去重 + 排序
=SORT(UNIQUE(A2:A100))

// 筛选 + 排序
=SORT(FILTER(A:C, B:B="北京"), 3, -1)

LET 函数​

Excel 365 中避免重复计算的利器：

=LET(
  data, FILTER(A:C, B:B>"2024-01-01"),
  total, SUM(INDEX(data, , 3)),
  avg, AVERAGE(INDEX(data, , 3)),
  "合计: " & total & " 均值: " & avg
)

LAMBDA 自定义函数​

// 名称管理器中定义 LAMBDA
名称: Tax
引用: =LAMBDA(amount, rate, amount * rate)

// 使用
=Tax(A2, 0.13)

常见面试问题​

Q1: VLOOKUP 找不到值怎么排查？​

答案（排查清单）：

  1. 数据类型不一致：查找值是数字、查找区域是文本（或反之）
  2. 多余空格：用 TRIM 清理
  3. 不可见字符：用 CLEAN 清理
  4. 匹配类型错误：第 4 个参数应为 FALSE（精确匹配）
  5. 查找值不在第一列：改用 INDEX-MATCH

Q2: 如何提取每个地区的 Top3 销量产品？​

答案：

=SORT(FILTER(产品列, 地区列=目标地区), 销量列, -1)

取前 3 行：

=INDEX(SORT(FILTER(A:C, B:B="北京"), 3, -1), SEQUENCE(3), {1,2,3})

Q3: VLOOKUP 和 INDEX-MATCH 在性能上有什么差异？​

答案：

  • VLOOKUP：每次查找扫描整个表格区域，列数越多越慢
  • INDEX-MATCH：只扫描两列（查找列和返回列），性能更好
  • 在 10 万行以上数据时差异明显
  • XLOOKUP 性能与 INDEX-MATCH 接近，且使用更简单

相关链接​