跳到主要内容

Excel高级函数应用

本章概览​

掌握高级函数是Excel从入门到精通的关键一步。本章将深入讲解查找引用、逻辑判断、数组公式等高级函数的应用,帮助你解决复杂的数据处理问题。

学习目标

  • 精通VLOOKUP/XLOOKUP等查找函数
  • 掌握IF系列逻辑函数的嵌套应用
  • 学会数组公式和动态数组
  • 熟练运用INDEX/MATCH组合
  • 掌握SUMIFS/COUNTIFS等多条件函数

23.1 查找引用函数​

23.1.1 VLOOKUP函数​

语法

=VLOOKUP(查找值, 数据表, 列号, [匹配模式])

参数说明

  • 查找值:要查找的内容
  • 数据表:查找范围
  • 列号:返回第几列的值
  • 匹配模式:0或FALSE(精确匹配),1或TRUE(近似匹配)

基础示例

# 查询产品价格
      A        B
1   产品     价格
2   苹果      5
3   香蕉      3
4   橙子      4

# 在E2查询"香蕉"的价格
=VLOOKUP("香蕉",A2:B4,2,0)    → 3
=VLOOKUP(D2,A2:B4,2,0)        # D2单元格="香蕉"

注意事项

  1. 查找值必须在数据表第一列
  2. 列号从1开始计数
  3. 匹配模式0(精确)vs 1(近似)
  4. 数据表使用绝对引用:$A$2:$B$4

常见错误

# #N/A错误:找不到值
=IFERROR(VLOOKUP(D2,A2:B4,2,0),"未找到")

# #REF!错误:列号超出范围
=VLOOKUP(D2,A2:B4,3,0)  # 错误!只有2列

近似匹配应用

# 成绩等级查询
分数    等级
0      不及格
60     及格
70     中等
80     良好
90     优秀

=VLOOKUP(85,分数表,2,1)  → "良好"
# 注意:数据必须升序排列

23.1.2 HLOOKUP函数​

语法

=HLOOKUP(查找值, 数据表, 行号, [匹配模式])

应用场景 横向查找(数据按行排列)

示例

      A      B      C      D
1   产品   苹果   香蕉   橙子
2   价格     5      3      4
3   库存   100     80     60

=HLOOKUP("香蕉",A1:D3,2,0)  → 3 (价格)
=HLOOKUP("香蕉",A1:D3,3,0)  → 80 (库存)

23.1.3 XLOOKUP函数(Microsoft 365)​

语法

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

优势

  1. 可以向左查找
  2. 默认精确匹配
  3. 可设置找不到时的返回值
  4. 支持反向搜索

基础用法

=XLOOKUP(D2, A2:A10, B2:B10)
# 在A2:A10查找D2,返回B2:B10对应值

找不到时返回默认值

=XLOOKUP(D2, A2:A10, B2:B10, "未找到")

反向查找

# 根据价格查产品名
=XLOOKUP(5, B2:B10, A2:A10)  # 向左查找

返回多列

=XLOOKUP(D2, A2:A10, B2:D10)  # 返回3列数据

23.1.4 INDEX+MATCH组合​

为什么使用INDEX+MATCH

  • 比VLOOKUP更灵活
  • 可以向左查找
  • 列插入删除不影响公式
  • 性能更好

基本语法

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

示例

# 查询产品价格
=INDEX(B2:B10, MATCH("香蕉", A2:A10, 0))

二维查找

=INDEX(数据区域, MATCH(行查找值,行范围,0), MATCH(列查找值,列范围,0))

# 示例:查询"香蕉"在"二月"的销量
      A      B      C      D
1   产品   一月   二月   三月
2   苹果   100    120    110
3   香蕉    80     90    100

=INDEX(B2:D3, MATCH("香蕉",A2:A3,0), MATCH("二月",B1:D1,0))
→ 90

优势演示

# VLOOKUP无法实现的场景
# 查询价格对应的产品名(向左查找)

      A        B
1   产品     价格
2   苹果      5
3   香蕉      3

# 根据价格3查询产品名
=INDEX(A2:A3, MATCH(3, B2:B3, 0))  → "香蕉"

23.2 逻辑函数​

23.2.1 IF函数嵌套​

单条件判断

=IF(A1>=60, "及格", "不及格")

多条件嵌套

=IF(A1>=90, "优秀",
  IF(A1>=80, "良好",
    IF(A1>=70, "中等",
      IF(A1>=60, "及格", "不及格"))))

嵌套限制

  • Excel 2007及以后支持64层嵌套
  • 建议不超过7层(可读性)

优化方案 使用IFS函数(Microsoft 365)或VLOOKUP

23.2.2 IFS函数(Microsoft 365)​

语法

=IFS(条件1, 值1, 条件2, 值2, ...)

示例

=IFS(
  A1>=90, "优秀",
  A1>=80, "良好",
  A1>=70, "中等",
  A1>=60, "及格",
  TRUE, "不及格"
)

注意

  • 按顺序检查条件
  • 最后用TRUE设置默认值
  • 简洁明了,易于维护

23.2.3 AND/OR/NOT函数​

AND函数

=AND(条件1, 条件2, ...)  # 所有条件为TRUE才返回TRUE

# 示例:检查成绩是否在60-100之间
=AND(A1>=60, A1<=100)

OR函数

=OR(条件1, 条件2, ...)  # 任一条件为TRUE就返回TRUE

# 示例:检查是否周末
=OR(WEEKDAY(A1,2)=6, WEEKDAY(A1,2)=7)

NOT函数

=NOT(条件)  # 反转逻辑值

# 示例:不是周末
=NOT(OR(WEEKDAY(A1,2)=6, WEEKDAY(A1,2)=7))

组合使用

# 判断:销售额>10000 且 (地区="北京" 或 地区="上海")
=IF(AND(B2>10000, OR(C2="北京", C2="上海")), "达标", "未达标")

23.2.4 IFERROR/IFNA函数​

IFERROR函数

=IFERROR(公式, 错误时返回值)

# 示例
=IFERROR(A1/B1, 0)           # 除零错误返回0
=IFERROR(VLOOKUP(...), "未找到")  # 查找错误返回"未找到"

IFNA函数

=IFNA(公式, #N/A时返回值)

# 只处理#N/A错误,其他错误仍显示
=IFNA(VLOOKUP(...), "未找到")

区别

  • IFERROR:处理所有错误
  • IFNA:只处理#N/A错误

23.3 数组公式​

23.3.1 传统数组公式​

输入方式 Ctrl + Shift + Enter (CSE数组公式)

示例1:批量计算

# 计算A1:A10乘以B1:B10的和
=SUM(A1:A10*B1:B10)  # 按Ctrl+Shift+Enter

# 显示为:{=SUM(A1:A10*B1:B10)}

示例2:多条件计数

# 统计A列="苹果"且B列>10的数量
=SUM((A1:A10="苹果")*(B1:B10>10))  # CSE

示例3:提取唯一值

# 提取A1:A10的唯一值(老版本Excel)
=IFERROR(INDEX($A$1:$A$10,MATCH(0,COUNTIF($B$1:B1,$A$1:$A$10),0)),"")
# CSE输入后向下复制

23.3.2 动态数组(Microsoft 365)​

自动溢出

  • 无需Ctrl+Shift+Enter
  • 结果自动填充到相邻单元格
  • 显示蓝色边框

FILTER函数

=FILTER(数组, 包含条件, [找不到时返回值])

# 筛选销售额>10000的记录
=FILTER(A2:C10, B2:B10>10000, "无符合条件数据")

# 多条件筛选
=FILTER(A2:C10, (B2:B10>10000)*(C2:C10="北京"))

SORT函数

=SORT(数组, [排序列号], [升降序], [按行排序])

# 按第2列降序排序
=SORT(A2:C10, 2, -1)

# 多列排序:先按列2降序,再按列3升序
=SORT(A2:C10, {2,3}, {-1,1})

SORTBY函数

=SORTBY(数组, 排序依据数组1, [顺序1], ...)

# 按销售额降序排列产品信息
=SORTBY(A2:C10, B2:B10, -1)

UNIQUE函数

=UNIQUE(数组, [按列], [仅出现一次])

# 提取唯一值
=UNIQUE(A2:A10)

# 提取只出现一次的值
=UNIQUE(A2:A10, , TRUE)

SEQUENCE函数

=SEQUENCE(行数, [列数], [起始值], [步长])

# 生成1-10的序列
=SEQUENCE(10)

# 生成3行4列的序列
=SEQUENCE(3, 4)

# 生成10,20,30...100
=SEQUENCE(10, 1, 10, 10)

RANDARRAY函数

=RANDARRAY([行数], [列数], [最小值], [最大值], [整数])

# 生成5个1-100的随机整数
=RANDARRAY(5, 1, 1, 100, TRUE)

23.3.3 动态数组实战​

案例1:动态排名

# 自动生成Top 10列表
=SORT(FILTER(A2:B100, B2:B100<>""), 2, -1)
# 结果自动溢出,无需手动填充

案例2:条件提取

# 提取销售额>10000的所有记录
=FILTER(A2:D100, B2:B100>10000)

# 多条件:北京地区且销售额>10000
=FILTER(A2:D100, (C2:C100="北京")*(B2:B100>10000))

案例3:动态下拉列表

# 数据验证引用动态数组
=UNIQUE(FILTER(A2:A100, A2:A100<>""))
# 自动去重并排除空值

23.4 SUMIFS/COUNTIFS系列​

23.4.1 SUMIFS函数​

语法

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

基础示例

# 统计北京地区的销售额
=SUMIFS(C2:C100, B2:B100, "北京")

# 多条件:北京地区且产品为"苹果"
=SUMIFS(E2:E100, C2:C100, "北京", D2:D100, "苹果")

通配符应用

# 统计姓名包含"张"的销售额
=SUMIFS(C2:C100, A2:A100, "张*")

# 统计编号以"BJ"开头的销售额
=SUMIFS(C2:C100, B2:B100, "BJ*")

日期条件

# 统计2024年1月的销售额
=SUMIFS(C2:C100, A2:A100, ">=2024-1-1", A2:A100, "<2024-2-1")

# 统计本月销售额
=SUMIFS(C2:C100, A2:A100, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

大于平均值

=SUMIFS(B2:B100, B2:B100, ">"&AVERAGE(B2:B100))

23.4.2 COUNTIFS函数​

语法

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

示例

# 统计北京地区记录数
=COUNTIFS(B2:B100, "北京")

# 统计销售额>10000且地区="上海"的记录数
=COUNTIFS(C2:C100, ">10000", B2:B100, "上海")

# 统计本月新增客户数
=COUNTIFS(A2:A100, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

23.4.3 AVERAGEIFS函数​

语法

=AVERAGEIFS(平均区域, 条件区域1, 条件1, ...)

示例

# 计算北京地区的平均销售额
=AVERAGEIFS(C2:C100, B2:B100, "北京")

# 计算技术部门的平均工资
=AVERAGEIFS(工资列, 部门列, "技术")

23.4.4 MAXIFS/MINIFS函数​

语法

=MAXIFS(最大值区域, 条件区域1, 条件1, ...)
=MINIFS(最小值区域, 条件区域1, 条件1, ...)

示例

# 查找北京地区的最高销售额
=MAXIFS(C2:C100, B2:B100, "北京")

# 查找技术部门的最低工资
=MINIFS(工资列, 部门列, "技术")

23.5 文本处理高级函数​

23.5.1 TEXTJOIN函数​

语法

=TEXTJOIN(分隔符, 忽略空值, 文本1, [文本2], ...)

示例

# 合并姓名
=TEXTJOIN(" ", TRUE, A2, B2, C2)  # "张 三 丰"

# 合并地址
=TEXTJOIN("", TRUE, A2, B2, C2, D2)

# 合并区域(支持数组)
=TEXTJOIN(",", TRUE, A2:A10)  # "苹果,香蕉,橙子,..."

条件合并

# 合并销售额>10000的产品名
=TEXTJOIN(",", TRUE, IF(B2:B10>10000, A2:A10, ""))
# 需要CSE输入(非365版本)

# 365版本
=TEXTJOIN(",", TRUE, FILTER(A2:A10, B2:B10>10000))

23.5.2 TEXTSPLIT函数(Microsoft 365)​

语法

=TEXTSPLIT(文本, 列分隔符, [行分隔符])

示例

# 拆分文本
=TEXTSPLIT("苹果,香蕉,橙子", ",")
# 结果溢出到3列

# 拆分多行
=TEXTSPLIT("张三,28;李四,30;王五,32", ",", ";")
# 结果:3行2列的数组

23.5.3 CONCAT/CONCATENATE​

CONCAT函数

=CONCAT(文本1, [文本2], ...)

# 支持区域引用
=CONCAT(A1:A10)  # 直接连接,无分隔符

与TEXTJOIN对比

  • CONCAT:无分隔符,不忽略空值
  • TEXTJOIN:有分隔符,可忽略空值

23.6 日期时间高级函数​

23.6.1 DATEDIF函数​

语法

=DATEDIF(开始日期, 结束日期, 单位)

单位说明

  • "Y":年数
  • "M":月数
  • "D":天数
  • "YM":年数忽略后的月数
  • "MD":月和年数忽略后的天数
  • "YD":年数忽略后的天数

示例

# 计算年龄
=DATEDIF(A2, TODAY(), "Y")

# 计算工龄(年+月)
=DATEDIF(A2,TODAY(),"Y")&"年"&DATEDIF(A2,TODAY(),"YM")&"月"

# 计算两个日期相差天数
=DATEDIF(A2, B2, "D")

23.6.2 EDATE/EOMONTH函数​

EDATE函数

=EDATE(开始日期, 月数)

# 3个月后的日期
=EDATE(TODAY(), 3)

# 半年前的日期
=EDATE(TODAY(), -6)

EOMONTH函数

=EOMONTH(开始日期, 月数)  # 返回该月最后一天

# 本月最后一天
=EOMONTH(TODAY(), 0)

# 下月最后一天
=EOMONTH(TODAY(), 1)

# 本月第一天
=EOMONTH(TODAY(), -1) + 1

23.6.3 NETWORKDAYS函数​

语法

=NETWORKDAYS(开始日期, 结束日期, [节假日])

应用

# 计算工作日天数
=NETWORKDAYS(A2, B2)

# 排除节假日
=NETWORKDAYS(A2, B2, 节假日列表)

# 计算N个工作日后的日期
=WORKDAY(TODAY(), 20, 节假日列表)

23.7 实战综合案例​

案例1:动态成绩单​

需求 根据学号自动显示学生信息和各科成绩

公式

# A2输入学号,自动显示姓名
=XLOOKUP(A2, 学生表[学号], 学生表[姓名], "未找到该学号")

# 显示语文成绩
=XLOOKUP(A2, 成绩表[学号], 成绩表[语文])

# 显示总分
=SUMIFS(成绩表[总分], 成绩表[学号], A2)

# 显示排名
=COUNTIFS(成绩表[总分], ">"&B2) + 1

案例2:销售数据分析​

多维度汇总

# 北京地区苹果的销售额
=SUMIFS(销售额, 地区, "北京", 产品, "苹果")

# 本月销售额
=SUMIFS(销售额, 日期, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

# 销售额前10名的产品列表
=SORT(UNIQUE(FILTER(产品, 销售额>LARGE(销售额,10))), 1, 1)

案例3:考勤异常统计​

复杂条件判断

# 判断考勤状态
=IFS(
  AND(上班时间>TIME(9,0,0), 上班时间<=TIME(9,30,0)), "迟到",
  上班时间>TIME(9,30,0), "严重迟到",
  下班时间<TIME(18,0,0), "早退",
  TRUE, "正常"
)

# 统计本月迟到次数
=COUNTIFS(姓名列, "张三", 日期列, ">="&本月第一天, 状态列, "*迟到*")

案例4:动态报表​

自适应数据更新

# 自动扩展的数据汇总
=SUMIFS(OFFSET(销售额,0,0,COUNTA(销售额),1),
        OFFSET(日期列,0,0,COUNTA(日期列),1), ">="&本月第一天)

# 或使用表格(推荐)
=SUMIFS(销售表[销售额], 销售表[日期], ">="&本月第一天)

23.8 函数优化技巧​

23.8.1 性能优化​

避免易失性函数

# 不推荐(每次重算都变)
=TODAY()
=NOW()
=RAND()
=OFFSET()

# 推荐:计算一次存储为值

减少数组公式

# 不推荐(数组公式)
=SUM(IF(A1:A1000="苹果",B1:B1000,0))

# 推荐(SUMIF)
=SUMIF(A1:A1000,"苹果",B1:B1000)

限定计算范围

# 不推荐(整列)
=SUMIF(A:A,"苹果",B:B)

# 推荐(具体范围)
=SUMIF(A2:A1000,"苹果",B2:B1000)

23.8.2 公式简化​

使用辅助列

# 复杂公式拆分为多步
# 第1列:提取年份
=YEAR(A2)

# 第2列:判断条件
=IF(B2=2024,"本年","往年")

# 第3列:汇总
=SUMIF(C:C,"本年",D:D)

使用表格结构化引用

# 传统引用
=SUMIF($A$2:$A$100,"苹果",$B$2:$B$100)

# 表格引用(更清晰)
=SUMIF(销售表[产品],"苹果",销售表[金额])

本章小结​

核心函数速查

类别函数用途
查找VLOOKUP纵向查找
查找XLOOKUP智能查找(365)
查找INDEX+MATCH灵活查找组合
逻辑IF/IFS条件判断
逻辑AND/OR/NOT逻辑组合
逻辑IFERROR/IFNA错误处理
统计SUMIFS/COUNTIFS多条件汇总
统计AVERAGEIFS多条件平均
数组FILTER条件筛选(365)
数组SORT/SORTBY排序(365)
数组UNIQUE去重(365)
文本TEXTJOIN智能合并
日期DATEDIF日期差计算
日期EDATE/EOMONTH日期推算

学习建议

  1. 从基础函数开始,逐步组合
  2. 理解函数参数和返回值
  3. 多练习实际案例
  4. 善用F9键调试公式
  5. 关注新函数(Microsoft 365)

下一步学习

  • 第24章:Excel商业图表制作
  • 第25章:Excel财务函数应用
  • 第26章:Power Query数据处理

思考练习​

  1. 用VLOOKUP制作产品价格查询表
  2. 用INDEX+MATCH实现双向查找
  3. 用SUMIFS统计多维度销售数据
  4. 用FILTER和SORT制作动态排名表(365版本)
  5. 综合运用多个函数制作员工信息查询系统

练习提示

  • 先拆解问题,确定需要哪些函数
  • 分步骤编写公式,逐步测试
  • 用IFERROR处理可能的错误
  • 添加注释说明公式逻辑