Excel函数综合应用技巧

Excel函数综合应用技巧

一、基础函数应用

1. 文本处理函数

1.1 LEFT/RIGHT/MID函数

从左侧提取字符：

LEFT(文本, 提取长度)

示例：LEFT("Hello World", 5) → "Hello"

从右侧提取字符：

RIGHT(文本, 提取长度)

示例：RIGHT("Hello World", 5) → "World"

从中间提取字符：

MID(文本, 起始位置, 提取长度)

示例：MID("Hello World", 7, 5) → "World"

1.2 CONCATENATE/CONCAT函数

合并文本：

CONCATENATE(文本1, 文本2, ...)

示例：CONCATENATE("Hello", " ", "World") → "Hello World"

新函数（Excel 365）：

CONCAT(文本1, 文本2, ...)

1.3 TEXT函数

格式化数字为文本：

TEXT(数值, 格式代码)

示例：TEXT(12345.67, "¥#,##0.00") → "¥12,345.67"

2. 逻辑函数

2.1 IF函数

条件判断：

IF(条件, 满足时返回值, 不满足时返回值)

示例：IF(A1>60, "及格", "不及格")

2.2 AND/OR/NOT函数

多条件判断：

AND(条件1, 条件2, ...)

OR(条件1, 条件2, ...)

NOT(条件)

示例：IF(AND(A1>60, B1>60), "双科及格", "未达标")

2.3 IFERROR函数

错误处理：

IFERROR(表达式, 错误时返回值)

示例：IFERROR(VLOOKUP(A1, B:C, 2, 0), "未找到")

二、查找与引用函数

1. VLOOKUP函数

垂直查找：

VLOOKUP(查找值, 查找区域, 返回列数, 匹配类型)

示例：查找员工姓名对应的部门

=VLOOKUP(A2, 员工表!A:B, 2, 0)

2. HLOOKUP函数

水平查找：

HLOOKUP(查找值, 查找区域, 返回行数, 匹配类型)

示例：查找月份对应的销售额

=HLOOKUP("一月", A1:F2, 2, 0)

3. INDEX-MATCH组合

双向查找：

INDEX(返回区域, MATCH(行查找值, 行区域, 0), MATCH(列查找值, 列区域, 0))

示例：根据姓名和科目查找成绩

=INDEX(成绩表!B2:D10, MATCH(A2, 成绩表!A2:A10, 0), MATCH(B2, 成绩表!B1:D1, 0))

4. XLOOKUP函数（Excel 365）

新一代查找函数：

XLOOKUP(查找值, 查找区域, 返回区域, [未找到时返回值], [匹配模式], [搜索模式])

示例：=XLOOKUP(A2, 员工表!A:A, 员工表!B:B, "未找到")

三、统计函数

1. COUNT/COUNTA/COUNTBLANK

计数函数：

COUNT(区域) - 统计数字单元格

COUNTA(区域) - 统计非空单元格

COUNTBLANK(区域) - 统计空单元格

2. SUM/SUMIF/SUMIFS

求和函数：

SUM(区域) - 简单求和

SUMIF(区域, 条件, [求和区域]) - 单条件求和

SUMIFS(求和区域, 条件区域1, 条件1, ...) - 多条件求和

示例：计算销售一部的总销售额

=SUMIF(销售表!B:B, "销售一部", 销售表!C:C)

示例：计算销售一部2024年的总销售额

=SUMIFS(销售表!C:C, 销售表!B:B, "销售一部", 销售表!A:A, "2024*")

3. AVERAGE/AVERAGEIF/AVERAGEIFS

平均值函数：

AVERAGE(区域) - 简单平均值

AVERAGEIF(区域, 条件, [计算区域]) - 单条件平均值

AVERAGEIFS(计算区域, 条件区域1, 条件1, ...) - 多条件平均值

4. MAX/MIN/MAXIFS/MINIFS

极值函数：

MAX(区域) - 最大值

MIN(区域) - 最小值

MAXIFS(数值区域, 条件区域1, 条件1, ...) - 多条件最大值

MINIFS(数值区域, 条件区域1, 条件1, ...) - 多条件最小值

四、日期与时间函数

1. TODAY/NOW函数

当前日期时间：

=TODAY() - 返回当前日期

=NOW() - 返回当前日期时间

2. YEAR/MONTH/DAY/HOUR/MINUTE/SECOND

提取日期时间组件：

=YEAR(A1) - 提取年份

=MONTH(A1) - 提取月份

=DAY(A1) - 提取日

=HOUR(A1) - 提取小时

=MINUTE(A1) - 提取分钟

=SECOND(A1) - 提取秒

3. DATEDIF函数

计算日期差：

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

单位代码：

- "Y" - 年

- "M" - 月

- "D" - 日

- "MD" - 忽略年和月的天数差

- "YM" - 忽略年和日的月数差

- "YD" - 忽略年的天数差

示例：计算员工工龄（年）

=DATEDIF(入职日期, TODAY(), "Y")

4. EOMONTH函数

计算指定月份的最后一天：

EOMONTH(日期, 月份偏移)

示例：计算本月最后一天

=EOMONTH(TODAY(), 0)

示例：计算下个月最后一天

=EOMONTH(TODAY(), 1)

五、高级函数应用

1. 数组公式

数组公式语法（Excel 365前需按Ctrl+Shift+Enter）：

{=函数(数组参数)}

示例：计算多条件下的唯一值数量

=SUM(1/COUNTIF(A2:A10, A2:A10))

2. TEXTJOIN函数（Excel 365）

合并文本并指定分隔符：

TEXTJOIN(分隔符, 忽略空单元格, 文本1, 文本2, ...)

示例：合并多个单元格内容

=TEXTJOIN(", ", TRUE, A1:A5)

3. FILTER函数（Excel 365）

筛选数据：

FILTER(数组, 条件数组, [未找到时返回值])

示例：筛选销售一部的所有记录

=FILTER(销售表!A:C, 销售表!B:B="销售一部")

4. SORT/SORTBY函数（Excel 365）

排序函数：

SORT(数组, [排序列], [排序顺序], [是否按列排序])

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

示例：按销售额降序排序

=SORTBY(销售表!A:C, 销售表!C:C, -1)

5. XMATCH函数（Excel 365）

增强版MATCH：

XMATCH(查找值, 查找区域, [匹配模式], [搜索模式])

示例：反向查找

=XMATCH(A2, B:B, 0, -1)

六、实战案例

1. 案例一：员工考勤统计

统计每个员工的最早和最晚考勤时间：

最早时间：=MINIFS(考勤表!B:B, 考勤表!A:A, D2)

最晚时间：=MAXIFS(考勤表!B:B, 考勤表!A:A, D2)

计算考勤时长：

=TEXT(最晚时间-最早时间, "hh:mm")

2. 案例二：销售数据分析

计算各区域销售额占比：

=SUMIF(销售表!B:B, A2, 销售表!C:C)/SUM(销售表!C:C)

计算同比增长率：

=(本年销售额-去年销售额)/去年销售额

3. 案例三：数据清洗

去除文本中的空格：

=TRIM(A1)

提取身份证号码中的出生日期：

=TEXT(MID(A1, 7, 8), "0000-00-00")

判断身份证性别（15位和18位兼容）：

=IF(MOD(MID(A1, IF(LEN(A1)=15, 15, 17), 1), 2)=1, "男", "女")

七、常用快捷键

八、技巧总结

1. 函数嵌套技巧

- 将多个函数嵌套使用，实现复杂逻辑

- 注意括号的配对和层次结构

2. 相对引用与绝对引用

- 使用$符号锁定行或列

- $A$1 - 绝对引用

- $A1 - 绝对列，相对行

- A$1 - 相对列，绝对行

3. 错误排查

- 使用IFERROR捕获错误

- 利用公式求值功能分步检查

- 注意数据类型匹配

4. 性能优化

- 避免在整列上使用函数

- 使用表格功能和结构化引用

- 对于大数据量，考虑使用数据透视表

5、文章下载链接：

https://download.csdn.net/download/m0_67097444/92890200?spm=1001.2014.3001.5503