导语：为什么你的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数据可视化时，需要专业的图表素材和图标支持。

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

---

总结

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

记住这三点建议：

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

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

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

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

🌟 加关注获取每日资源推荐

畅榴云平台为你提供海量素材每日更新各类精选资源推荐让创作更高效，让灵感不间断

💡 温馨提示

更多精彩内容，欢迎关注我们觉得有用请点个「在看」支持一下吧~