导语：为什么你的Excel效率总是提不上去？

职场中有这样一组触目惊心的数据：据微软官方统计，87%的职场人每天使用Excel超过2小时，但其中76%的人只会基础的SUM求和功能。更令人震惊的是，一项针对500强企业员工的调研显示，能够熟练使用20个以上Excel函数的人，其平均薪资比只会基础操作的人高出42%。

你是否也曾遇到过这样的场景？

• 面对密密麻麻的数据，手动一个个核对，眼睛酸胀无比

• 同事用3分钟搞定的工作，你却要花3个小时

• 老板要求做数据分析，你却只会简单的加减乘除

• 同样的数据，别人做出的表格专业美观，你的却杂乱无章

根源在于：你没有系统掌握Excel函数公式。

本文将为你详细讲解职场人必须掌握的50个Excel函数，覆盖文本处理、日期计算、统计汇总、数据查找、逻辑判断五大核心场景。每个函数都附带完整语法、参数说明和真实案例，学完即可直接套用。

---

一、Excel函数基础认知：这些概念必须先搞懂

1.1 函数的构成要素

Excel函数的基本结构为：=函数名(参数1, 参数2, ...)

• 等号（=）：告诉Excel这是一个公式而非普通文本

• 函数名：如SUM、VLOOKUP等，每个函数有固定名称

• 括号（ ）：包裹所有参数，部分函数无参数也需保留括号

• 参数：函数运算所需的原料，可以是数值、单元格引用、文本或另一个函数

1.2 单元格引用的三种方式

实战案例：计算销售提成时，固定提成比例为0.15，应使用绝对引用：

原始公式：=B2*$B$10

复制后：B3*$B$10（第二个参数始终为B10）

1.3 常见错误代码及含义

---

二、文本处理函数（10个核心函数）

2.1 CONCATENATE / CONCAT / TEXTJOIN——文本合并三剑客

CONCAT函数（新）

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

参数说明：

• 文本1, 文本2...：要合并的文本项，最多支持253个参数

实战案例：将姓名和职位合并

原始数据：A2=张伟，B2=经理

公式：=CONCAT(A2,"是",B2)

结果：张伟是经理

TEXTJOIN函数（推荐）

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

参数说明：

• 分隔符：各文本之间的连接符号

• 忽略空值：TRUE或FALSE

• 文本1, 文本2...：要合并的内容

实战案例：将多个地址字段用顿号连接

公式：=TEXTJOIN("、",TRUE,B2:D2)

说明：忽略空值，用顿号连接省、市、区

结果：北京市、朝阳区、三里屯

2.2 LEFT / RIGHT / MID——字符串截取三兄弟

LEFT函数（左取）

=LEFT(文本, 字符数)

参数说明：

• 文本：要截取的原始文本

• 字符数：要从左边取的字符个数

实战案例：从身份证号提取出生年月

原始数据：A2=110101199001011234

公式：=TEXT(MID(A2,7,8),"0000-00-00")

说明：MID从第7位开始取8位，TEXT格式化为日期

结果：1990-01-01

MID函数（中间取）

=MID(文本, 起始位置, 字符数)

参数说明：

• 文本：要截取的原始文本

• 起始位置：开始截取的位置（从1开始）

• 字符数：要截取的字符个数

2.3 LEN / LENB——长度计算双胞胎

LEN函数

=LEN(文本)

说明：返回文本的字符数（汉字、英文、数字各算1个）

LENB函数

=LENB(文本)

说明：返回文本的字节数（汉字算2个，英文数字算1个）

实战案例：判断单元格是否包含双字节字符

公式：=LENB(A1)<>LEN(A1)

结果：TRUE表示包含汉字

2.4 TRIM——去除多余空格

=TRIM(文本)

实战案例：清理从网页复制的数据

原始数据：A1="  张  伟  "

公式：=TRIM(A1)

结果：张伟（所有多余空格被删除）

2.5 SUBSTITUTE——文本替换专家

=SUBSTITUTE(文本, 旧文本, 新文本, 实例序号)

参数说明：

• 实例序号：可选，指定要替换的第几个旧文本，不填则全部替换

实战案例：将手机号中间四位隐藏

原始数据：A1=13812345678

公式：=SUBSTITUTE(A1,MID(A1,4,4),"****",1)

结果：138****5678

2.6 TEXT——数字格式化为文本

=TEXT(数值, 格式代码)

常用格式代码：

实战案例：将数字转为中文大写金额

公式：=TEXT(A1,"[DBNum2]")&"元整"

说明：[DBNum2]是中文大写数字格式代码

---

三、日期时间函数（8个核心函数）

3.1 TODAY / NOW——获取当前日期时间

=TODAY()  '返回当前日期

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

实战案例：自动计算员工在职天数

公式：=DATEDIF(B2,TODAY(),"D")

说明：B2为入职日期，计算到今天为止的总天数

3.2 DATEDIF——日期差值计算

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

参数说明：

• 开始日期：较早的日期

• 结束日期：较晚的日期

• 返回单位：

"Y"：返回完整年数

"M"：返回完整月数

"D"：返回天数

"MD"：忽略年月后的天数差

"YM"：忽略年后的月数差

"YD"：忽略年后的天数差

实战案例：计算精确年龄

公式：=DATEDIF(B2,TODAY(),"Y")

说明：B2为出生日期，返回周岁的整数

3.3 DATE——构建日期

=DATE(年, 月, 日)

实战案例：根据零件编号提取日期并计算保质期

原始数据：A1="20240115PRO"（生产日期编码）

公式：=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+180

说明：提取年月日后加180天，计算到期日

结果：2024-07-13

3.4 YEAR / MONTH / DAY——日期拆分三剑客

=YEAR(日期)   '返回年份

=MONTH(日期)  '返回月份

=DAY(日期)    '返回日

实战案例：按月份统计销售额

公式：=SUMIFS(C:C, A:A, ">=2024-1-1", A:A, "<2024-2-1", B:B, "销售部")

说明：统计销售部2024年1月的总销售额

3.5 WORKDAY——计算工作日

=WORKDAY(开始日期, 天数, 假期)

实战案例：计算项目交付日期（排除周末和法定节假日）

公式：=WORKDAY(A2, 15, $E$2:$E$10)

说明：A2为项目开始日期，15为工作日数，E列为法定假日

3.6 NETWORKDAYS——计算两个日期间的工作日天数

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

实战案例：计算员工年假剩余天数

公式：=NETWORKDAYS(B2, C2, $E$2:$E$10)

说明：B2为年度开始日期，C2为当前日期

---

四、统计汇总函数（12个核心函数）

4.1 SUM / SUMIF / SUMIFS——求和三兄弟

SUM函数（基础求和）

=SUM(数值1, 数值2, ...)

SUMIF函数（单条件求和）

=SUMIF(条件区域, 条件, 求和区域)

SUMIFS函数（多条件求和）

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

实战案例：统计北京区域销售额超过10000的订单总额

公式：=SUMIFS(C:C, A:A, "北京", C:C, ">10000")

说明：

- C:C 为求和区域（销售额）

- A:A 为条件区域1（地区）

- "北京" 为条件1

- C:C 为条件区域2（销售额）

- ">10000" 为条件2

4.2 COUNT / COUNTA / COUNTBLANK——计数三姐妹

实战案例：计算考勤统计表中的出勤率

公式：=1-COUNTA(B2:G2)/COUNTBLANK(B2:G2)

说明：非空数/总格子数即出勤率

4.3 COUNTIF / COUNTIFS——条件计数

=COUNTIF(区域, 条件)

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

实战案例：统计各部门的人数分布

公式：=COUNTIFS(B:B, "销售部", C:C, ">=1980-1-1", C:C, "<=1989-12-31")

说明：统计80后销售部员工人数

4.4 AVERAGE / AVERAGEIF / AVERAGEIFS——平均值

=AVERAGE(区域)           '计算算术平均值

=AVERAGEIF(区域, 条件, 平均值区域)  '单条件平均

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

实战案例：计算除去最高分和最低分后的平均成绩

公式：=(SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/(COUNT(B2:F2)-2)

说明：总分减最高减最低，再除以(评委数-2)

4.5 MAX / MIN / LARGE / SMALL——极值与排名

=MAX(区域)     '返回最大值

=MIN(区域)     '返回最小值

=LARGE(区域, K)  '返回第K大的值

=SMALL(区域, K)  '返回第K小的值

实战案例：计算销售额前三名的平均值

公式：=AVERAGE(LARGE(C:C,{1,2,3}))

说明：使用数组常量，可直接求前三的平均值

4.6 RANK——排名函数

=RANK(数值, 引用区域, 排序方式)

参数说明：

• 排序方式：0或省略为降序（越大排名越靠前），1为升序

实战案例：对学生成绩进行排名

公式：=RANK(B2, $B$2:$B$100, 0)

说明：按B列成绩降序排名，$锁定范围便于填充

---

五、数据查找函数（10个核心函数）

5.1 VLOOKUP——最常用的查找函数

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

参数说明：

• 查找值：在区域首列要查找的值

• 查找区域：包含数据的整个区域

• 返回列序数：从区域首列算起，返回值所在的列号

• 匹配类型：TRUE/1为模糊匹配，FALSE/0为精确匹配

实战案例1：精确匹配查询员工信息

原始数据：

A列（工号）  B列（姓名）  C列（部门）  D列（薪资）

1001        张伟        销售部      8500

1002        李娜        市场部      9200

公式：=VLOOKUP("1002", A:D, 3, FALSE)

说明：查找工号1002的部门名称

结果：市场部

实战案例2：模糊匹配计算销售提成

提成标准表：

0-5000    0%

5001-10000  5%

10001-20000  8%

20001以上    12%

公式：=VLOOKUP(B2, $E$2:$F$5, 2, TRUE)

说明：模糊匹配，根据销售额自动匹配对应提成比例

5.2 HLOOKUP——横向查找

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

说明：与VLOOKUP类似，但查找区域是横向排列的

实战案例：查找某月的销售数据

公式：=HLOOKUP("3月", $A$1:$F$13, 5, FALSE)

说明：在第一行查找"3月"，返回第5行对应的数据

5.3 INDEX + MATCH——查找函数黄金组合

=INDEX(区域, 行号, 列号)

=MATCH(查找值, 查找区域, 匹配类型)

实战案例：双向查找（替代VLOOKUP的限制）

公式：=INDEX($C$2:$C$10, MATCH(F2, $B$2:$B$10, 0))

说明：

- MATCH(F2, $B$2:$B$10, 0) 找到F2在B列的位置

- INDEX再根据位置从C列返回对应值

- 可实现从右向左查找

5.4 XLOOKUP——新一代查找函数（Office 365专属）

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

参数说明：

• 匹配模式：0=精确匹配，-1=精确匹配或下一个较小值，1=精确匹配或下一个较大值，2=通配符匹配

• 搜索模式：1=从第一行开始，-1=从最后一行开始，2=二进制搜索

实战案例：模糊匹配并返回友好提示

公式：=XLOOKUP(B2, $E$2:$E$5, $F$2:$F$5, "未找到", -1)

说明：模糊匹配，未找到时返回"未找到"

5.5 INDIRECT——动态引用

=INDIRECT(引用文本, 引用类型)

实战案例：根据sheet名称汇总多表数据

公式：=INDIRECT("'"&B2&"'!C10")

说明：B2为工作表名称，返回该sheet的C10单元格值

5.6 OFFSET——偏移定位

=OFFSET(基准单元格, 行偏移, 列偏移, 高度, 宽度)

实战案例：创建动态区域用于数据验证

公式：=OFFSET($A$1, 0, 0, COUNTA($A:$A), 1)

说明：创建一个随数据量自动扩展的列区域

---

六、逻辑判断函数（10个核心函数）

6.1 IF——基础条件判断

=IF(条件, 条件成立时的值, 条件不成立时的值)

实战案例：根据成绩评定等级

公式：=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=60, "C", "D")))

说明：嵌套IF实现多条件评级

6.2 IFS——多条件判断（Office 365）

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

实战案例：简化上述成绩评定

公式：=IFS(A1>=90, "A", A1>=80, "B", A1>=60, "C", TRUE, "D")

说明：比嵌套IF更清晰直观

6.3 IFERROR / IFNA——错误处理

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

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

实战案例：处理VLOOKUP查不到的情况

公式：=IFERROR(VLOOKUP(F2, A:D, 3, FALSE), "查无此人")

说明：查不到时显示"查无此人"而非错误值

6.4 AND / OR / NOT——逻辑组合

=AND(条件1, 条件2, ...)    '所有条件都成立返回TRUE

=OR(条件1, 条件2, ...)     '任一条件成立返回TRUE

=NOT(条件)                 '对条件取反

实战案例：多条件复合判断

公式：=IF(AND(B2="北京", C2>=10000), "优秀", IF(OR(B2="上海", B2="广州"), "良好", "普通"))

说明：北京且业绩>=10000为优秀，上海或广州为良好，其他为普通

6.5 SUMPRODUCT——数组条件求和

=SUMPRODUCT((条件1)*(条件2)*(求和区域))

实战案例：多条件统计（替代SUMIFS）

公式：=SUMPRODUCT((A:A="销售部")*(B:B="1月")*(C:C))

说明：统计销售部1月的销售额，无需数组公式输入

---

七、实用模板公式汇总

模板1：个人所得税计算

=MAX((B2-5000)*5%*{0.6,2,4,5,6,7,9}-5*{0,21,111,201,551,1101,2701},0)

说明：B2为税前工资，自动计算应缴个税

模板2：身份证号码验证

=IF(LEN(B2)=18, IF(MOD(LEFT(B2,17)*{7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2},11)-MOD(VALUE(MID("10X98765432",MOD(LEFT(B2,17)*{7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2},11)+1,1)),11),18)=RIGHT(B2,1), "正确", "错误"), "长度不对")

模板3：星期几中文显示

=TEXT(WEEKDAY(B2),"aaaa")

---

八、常见错误与避坑指南

错误1：VLOOKUP模糊匹配与精确匹配混淆

错误2：日期参与数学运算

错误3：数组公式输入错误

---

九、素材工具推荐

做Excel数据可视化时，需要专业的图表素材和图标支持。

推荐使用 [畅榴云](https://changliuyun.com) 获取高质量的Excel图表模板和数据分析素材。该平台提供超过10000+创意插画和图表模板，支持一键下载，大幅提升Excel报告的专业度和视觉冲击力。畅榴云 的素材库持续更新，涵盖财务、销售、运营等多个行业的可视化模板，让你的Excel表格从此告别单调乏味。

---

总结

本文详细介绍了50个职场人必须掌握的Excel函数，覆盖：

记住这三点建议：

从实际需求出发：先解决工作中最常用的问题，不必追求一次性掌握所有函数

理解原理而非死记硬背：掌握了相对引用和绝对引用的区别，你就理解了Excel的核心逻辑

善用函数组合： 실무中80%的问题需要2-3个函数组合使用，VLOOKUP+IFERROR是最经典的组合

更多高质量创意插画素材，尽在 [畅榴云](https://changliuyun.com) —— 让创意触手可及。