2026Excel函数大全-HR财务运营必会的50个公式

2026最新Excel函数大全——HR/财务/运营必会的

50个公式

职场效率｜合集资料｜HR/财务/运营｜Excel函数·公式模板·场景示例

2026年

本文档基于Excel2021/365版本整理，涵盖截至2026年主流函数。若需确认最新版本特性，建议以微软官方发布为

准。

一、50个函数总表（按岗位分类）

HR常用函数（18个）

序号函数一句话说明主岗位也适用

1VLOOKUP按条件查找匹配数据HR财务/运营

2IF条件判断返回不同结果HR财务/运营

3COUNTIF按条件统计数量HR财务/运营

4SUMIF按条件求和HR财务/运营

5DATEDIF计算两个日期之间的差值HR—

6TEXT将数值转换为指定格式文本HR财务/运营

7LEFT从左侧截取指定长度字符HR财务/运营

8RIGHT从右侧截取指定长度字符HR财务/运营

9MID从中间截取指定长度字符HR财务/运营

10CONCATENATE合并多个文本HR财务/运营

11IFERROR捕获公式错误并返回指定值HR财务/运营

12ROUND四舍五入到指定小数位HR财务

13TODAY返回当前日期HR财务/运营

14YEAR提取日期中的年份HR财务/运营

15MONTH提取日期中的月份HR财务/运营

16NETWORKDAYS计算两个日期之间的工作日数HR—

17RANK返回数值在列表中的排名HR运营

18SUBTOTAL对筛选后的数据进行汇总HR财务/运营

财务常用函数（18个）

序号函数一句话说明主岗位也适用

19SUM求和财务HR/运营

20SUMIFS多条件求和财务运营

21COUNTIFS多条件统计数量财务运营

22AVERAGE计算平均值财务HR/运营

23ROUNDUP向上舍入财务—

24ROUNDDOWN向下舍入财务—

25ABS返回绝对值财务运营

26PMT计算贷款每期还款额财务—

27FV计算未来值财务—

28PV计算现值财务—

29NPV计算净现值财务—

30IRR计算内部收益率财务—

31SLN计算直线折旧额财务—

32DDB计算双倍余额递减折旧财务—

33EOMONTH返回某月最后一天财务HR

34EDATE返回某日期前后N个月的日期财务HR

35YEARFRAC计算两个日期之间的年数占比财务HR

36INDEX按行列号返回数据财务HR/运营

运营常用函数（14个）

序号函数一句话说明主岗位也适用

37MATCH返回查找值在区域中的位置运营HR/财务

38INDEX+MATCH组合查找，比VLOOKUP更灵活运营HR/财务

39IF+AND多条件同时满足判断运营HR/财务

40IF+OR多条件任一满足判断运营HR/财务

41IF嵌套多层级条件判断运营HR/财务

42COUNTA统计非空单元格数量运营HR/财务

序号函数一句话说明主岗位也适用

43COUNTBLANK统计空单元格数量运营HR/财务

44LARGE返回第N大的值运营财务

45SMALL返回第N小的值运营财务

46PERCENTILE返回百分位数运营财务

47QUARTILE返回四分位数运营财务

48STDEV计算标准差运营财务

49CORREL计算相关系数运营财务

50TREND返回线性趋势值运营财务

函数1：VLOOKUP——按条件查找匹配数据

要素1：基础信息

语法：

=VLOOKUP(查找值,查找区域,返回列号,精确/近似匹配)

参数说明：

查找值：你要找什么。比如“EMP001”这个员工编号。可以是单元格引用（如A2），也可以是直接写的值。

查找区域：去哪里找。比如“员工信息表”的A到D列。查找值必须在这个区域的第一列。

返回列号：找到之后，返回第几列的数据。从查找区域的第一列开始数，不是从A列开始数。

精确/近似匹配：FALSE或0表示精确匹配（完全一样才算找到），TRUE或1表示近似匹配（差不多就行）。日

常查找都用FALSE。

要素2：完整公式模板

基础模板（直接复制到单元格）：

=VLOOKUP(A2,员工信息表!$A$2:$D$201,3,FALSE)

变体1（返回多列，用COLUMN自动定位列号）：

=VLOOKUP($A2,员工信息表!$A$2:$D$201,COLUMN(B1),FALSE)

把这个公式放在B2，下拉到C2、D2，会自动返回第2、3、4列。

变体2（找不到时显示“未找到”，避免#N/A错误）：

=IFERROR(VLOOKUP(A2,员工信息表!$A$2:$D$201,3,FALSE),"未找到")

要素3：真实工作场景完整案例

背景：你是某互联网公司的HR，每月要制作人力报表。现在需要从“员工信息表”中调取员工的部门名称和岗位等级，

填到当前报表里。员工信息表有200行数据，包含编号（A列）、姓名（B列）、部门（C列）、岗位等级（D列）。

数据：

员工信息表在名为“员工信息表”的工作表中，数据在A2:D201

当前报表的A2单元格是员工编号“EMP001”

你需要在B2显示部门，在C2显示岗位等级

零基础操作步骤：

第一步：打开当前报表，点击B2单元格

在Excel底部点击当前报表的工作表标签

用鼠标点击B2单元格（B列第2行），单元格边框会变粗，表示已选中

第二步：输入公式

在键盘上先打一个等号=（所有Excel公式都以等号开头）

然后依次输入：VLOOKUP(A2,员工信息表!$A$2:$D$201,3,FALSE)

输入时注意：

所有括号、逗号都是英文半角，不能是中文全角

$符号表示“锁定”，$A$2:$D$201表示锁定A2到D201这个区域不变化

输入员工信息表!表示引用另一个工作表，那个感叹号也是英文的

第三步：按Enter键确认

按键盘上的Enter键

B2单元格会显示“EMP001”对应的部门名称，比如“技术部”

同时编辑栏会显示完整的公式

第四步：向下填充

点击B2单元格，单元格右下角有一个小方块（叫“填充柄”）

把鼠标移到填充柄上，鼠标会变成黑色十字

方法一：按住鼠标左键向下拖拽到B201

方法二：双击填充柄，Excel会自动填充到有数据的最后一行

第五步：同样操作C2单元格

点击C2，输入：=VLOOKUP(A2,员工信息表!$A$2:$D$201,4,FALSE)

注意这里返回列号改成4（因为岗位等级在第4列）

按Enter后向下填充

预期结果：

B2显示“技术部”，C2显示“P6”

B列全部显示部门名称，C列全部显示岗位等级

编辑栏可以看到每行的公式（A2变成A3、A4……）

如果报错了怎么办：

报错显示原因解决办法

#N/A查找值在查找区域第一列找不到检查A2的值是否真的在员工信息表A列中存在

#REF!返回列号超出了区域范围检查员工信息表是否真的有4列

#VALUE!参数格式错误检查逗号是否为英文，引号是否为英文

显示错误的值忘记加FALSE，用了近似匹配在最后加上FALSE

要素4：与其他函数的组合用法

组合1：VLOOKUP+IFERROR（查找失败时显示友好提示）

=IFERROR(VLOOKUP(A2,员工信息表!$A$2:$D$201,3,FALSE),"编号不存在")

适用场景：当查找值不存在时，避免显示#N/A错误，改为显示“编号不存在”。操作方法同上，只是在外面包了一层

IFERROR。

组合2：VLOOKUP+MATCH（插入新列时公式不错位）

=VLOOKUP(A2,员工信息表!$A$2:$D$201,MATCH("部门",员工信息表!$A$1:$D$1,0),FALSE)

适用场景：当返回列不确定时，用MATCH动态定位“部门”在哪一列。这样以后在员工信息表中插入新列，公式不会错

位。

要素5：常见错误与正确写法对比

错误1：查找值不在查找区域的第一列

错误写法：=VLOOKUP(A2,B2:D201,3,FALSE)

这个公式从B列开始查找，但A2的值在A列，不在B列，所以找不到

正确写法：=VLOOKUP(A2,A2:D201,4,FALSE)

从A列开始查找，返回第4列（D列）

原理：VLOOKUP只能从查找区域的第一列开始查找

错误2：忘记加FALSE导致近似匹配

错误写法：=VLOOKUP(A2,A2:D201,4)

省略了第四参数，Excel默认用近似匹配

结果可能返回一个“差不多”但不正确的值

正确写法：=VLOOKUP(A2,A2:D201,4,FALSE)

加上FALSE，强制精确匹配

找不到就返回#N/A，而不是错误的值

要素6：进阶技巧或性能优化建议

性能优化：当数据量超过10000行时，VLOOKUP的查找速度会明显变慢。建议改用INDEX+MATCH组合，速度可提升

3-5倍。原理是VLOOKUP每次都要遍历整个查找区域，而INDEX+MATCH只需定位到具体位置。

进阶技巧：使用通配符进行模糊查找。

=VLOOKUP("张*",A2:D201,4,FALSE)

这个公式会查找姓“张”的第一个员工。星号*表示任意多个字符。

要素7：版本差异说明

Excel2016及更早版本：VLOOKUP是查找函数的主流选择，不支持XLOOKUP。

Excel2019：新增IFS函数，但VLOOKUP仍是查找主力。

Excel2021/365：新增XLOOKUP函数，支持双向查找、默认精确匹配、查找失败返回指定值。语法为

=XLOOKUP(查找值,查找列,返回列,"未找到")。新版本用户可优先使用XLOOKUP。

函数2：IF——条件判断返回不同结果

要素1：基础信息

语法：

=IF(判断条件,条件为真时返回,条件为假时返回)

参数说明：

判断条件：一个能判断对错的表达式。比如B2>=60（B2大于等于60吗），B2="在职"（B2是“在职”吗）。结

果只能是TRUE（对）或FALSE（错）。

条件为真时返回：判断为“对”时显示什么。可以是文字（要加英文引号）、数字、公式。

条件为假时返回：判断为“错”时显示什么。

要素2：完整公式模板

基础模板：

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

变体1（多层级嵌套）：

=IF(B2>=90,"优秀",IF(B2>=80,"良好",IF(B2>=60,"及格","不及格")))

从高到低依次判断，第一个条件不满足就进入下一层。

变体2（返回数值）：

=IF(B2>=60,B2*0.1,0)

条件满足时返回B2的10%，不满足返回0。

要素3：真实工作场景完整案例

背景：你是某制造企业的HR，需要根据绩效考核分数自动判定员工等级，并计算对应的奖金系数。绩效分数在B列，

奖金系数规则为：90分以上系数1.5，80-89分系数1.2，60-79分系数1.0，60分以下系数0。共300名员工。

数据：

B2:B301是员工绩效分数

C列要填等级，D列要填奖金系数

零基础操作步骤：

第一步：点击C2单元格

第二步：输入等级判断公式

打等号，输入：IF(B2>=90,"优秀",IF(B2>=80,"良好",IF(B2>=60,"及格","不及格")))

注意：

所有文字都要加英文双引号，如"优秀"

逗号是英文逗号

括号要配对，有几个IF就有几对括号

第三步：按Enter确认

C2会显示B2对应员工的等级，比如“优秀”

第四步：向下填充

双击C2单元格右下角的填充柄，自动填充到C301

第五步：同样操作D2单元格

点击D2，输入：=IF(B2>=90,1.5,IF(B2>=80,1.2,IF(B2>=60,1.0,0)))

这里返回的是数字，不需要加引号

按Enter后向下填充

预期结果：

C列显示“优秀/良好/及格/不及格”

D列显示“1.5/1.2/1.0/0”

分数变化时，等级和系数自动更新

如果报错了怎么办：

报错显示原因解决办法

#NAME?函数名拼写错误检查是不是写成了IF，而不是if

#VALUE!文字没加引号检查“优秀”是否加了英文引号

结果全是一个值判断条件写反了检查是否用了>=而不是=<

要素4：与其他函数的组合用法

组合1：IF+AND（两个条件同时满足）

=IF(AND(B2>=60,C2>=60),"通过","不通过")

适用场景：两项考核（B列和C列）同时达标才算通过。AND函数里所有条件都为TRUE时返回TRUE。

组合2：IF+OR（任一条件满足）

=IF(OR(B2="离职",C2="停薪留职"),"不在岗","在岗")

适用场景：离职或停薪留职任一满足即视为不在岗。OR函数里任一条件为TRUE就返回TRUE。

要素5：常见错误与正确写法对比

错误1：文字没加引号

错误写法：=IF(B2=在职,"是","否")

Excel会把“在职”当成一个变量名，找不到就报错#NAME?

正确写法：=IF(B2="在职","是","否")

用英文双引号把文字包起来

错误2：IF嵌套层数过多

错误写法：

=IF(A1=1,"一",IF(A1=2,"二",IF(A1=3,"三",IF(A1=4,"四",IF(A1=5,"五",IF(A1=6,"六",IF(A1=7,"七",IF(A1

=8,"八"))))))))

8层嵌套，虽然Excel能识别，但难以维护，出错后很难排查

正确写法：改用IFS函数或VLOOKUP查找表

=IFS(A1=1,"一",A1=2,"二",A1=3,"三",A1=4,"四")

或做一个1到8对应“一”到“八”的查找表，用VLOOKUP查。

要素6：进阶技巧或性能优化建议

进阶技巧：IFS函数替代多层IF嵌套（Excel2019及以上版本可用）。语法为=IFS(条件1,结果1,条件2,结果2,

...)，从左到右依次判断，遇到第一个TRUE就返回对应结果。无需嵌套，可读性大大提升。

性能优化：当IF嵌套超过5层时，建议改用VLOOKUP查找表。把条件和结果做成两列的对照表，用VLOOKUP近似匹配

或INDEX+MATCH精确匹配，速度更快且易于维护。

要素7：版本差异说明

Excel2016及更早版本：不支持IFS函数，只能用IF嵌套。

Excel2019：新增IFS函数，支持多条件判断。

Excel2021/365：IFS和SWITCH均可用。SWITCH语法为=SWITCH(表达式,值1,结果1,值2,结果2,默认

值)，适合等值判断场景。

函数3：COUNTIF——按条件统计数量

要素1：基础信息

语法：

=COUNTIF(统计区域,统计条件)

参数说明：

统计区域：在哪一列/哪个区域里数数。比如B2:B501。

统计条件：数什么。可以是文字（加引号，如"在职"），数字（如100），表达式（加引号，如">=80"），通配

符（如"张*"）。

要素2：完整公式模板

基础模板：

=COUNTIF(B2:B501,"在职")

变体1（数值条件）：

=COUNTIF(C2:C501,">=80")

变体2（通配符，统计姓张的员工）：

=COUNTIF(A2:A501,"张*")

要素3：真实工作场景完整案例

背景：你是某公司HR，需要统计各部门在职人数、离职人数、本科以上学历人数，用于制作月度人力分析报告。共

500行数据。

数据：

B2:B501：员工状态（在职/离职/待入职）

C2:C501：部门名称

D2:D501：学历（高中/大专/本科/硕士/博士）

A2:A501：姓名

零基础操作步骤：

第一步：找一个空白单元格放统计结果

比如在F2单元格开始做统计

第二步：统计在职人数

点击F2，输入：=COUNTIF(B2:B501,"在职")

注意“在职”要加英文引号

按Enter后F2显示在职人数，比如“450”

第三步：统计离职人数

点击F3，输入：=COUNTIF(B2:B501,"离职")

按Enter

第四步：统计技术部人数

点击F4，输入：=COUNTIF(C2:C501,"技术部")

注意这里区域改成了C列（部门列）

按Enter

第五步：统计本科以上人数

点击F5，输入：=COUNTIF(D2:D501,"本科")+COUNTIF(D2:D501,"硕士")+COUNTIF(D2:D501,"博士")

三个COUNTIF相加，分别数本科、硕士、博士人数

按Enter

第六步：统计姓“张”的员工

点击F6，输入：=COUNTIF(A2:A501,"张*")

星号*是通配符，表示任意多个字符

按Enter

预期结果：

F2显示在职人数，如450

F3显示离职人数，如30

F4显示技术部人数，如120

F5显示本科以上人数，如280

F6显示姓张的员工数，如35

如果报错了怎么办：

报错显示原因解决办法

结果总是0条件没加引号或区域选错检查条件是否加了引号，区域是否是条件所在列

#NAME?函数名拼写错误检查是否写成了COUNTIF，而不是countif

结果比预期多用了通配符但没加*检查是否需要通配符匹配

要素4：与其他函数的组合用法

组合1：COUNTIF+SUM（一次统计多个条件）

=SUM(COUNTIF(B2:B501,{"在职","待入职"}))

适用场景：统计“在职”和“待入职”的总人数。花括号{}里列出多个条件，COUNTIF会分别统计再相加。

组合2：COUNTIF+COUNTA（计算在职率）

=COUNTIF(B2:B501,"在职")/COUNTA(B2:B501)

适用场景：计算在职率。COUNTA统计非空单元格数量，作为分母。结果记得设置为百分比格式。

要素5：常见错误与正确写法对比

错误1：条件中的文本未加引号

错误写法：=COUNTIF(B2:B501,在职)

Excel会把“在职”当成变量名，找不到就报错

正确写法：=COUNTIF(B2:B501,"在职")

用英文双引号包裹文字条件

错误2：统计区域和条件不匹配

错误写法：=COUNTIF(B2:B501,"技术部")

B列是员工状态列，不是部门列，数不到技术部

正确写法：=COUNTIF(C2:C501,"技术部")

C列才是部门列，统计区域必须是条件所在的列

要素6：进阶技巧或性能优化建议

进阶技巧：使用数组常量统计多个条件。如=SUM(COUNTIF(B2:B501,{"在职","待入职"}))可一次性统计两种状态

的人数，无需写两个COUNTIF相加。

性能优化：COUNTIF在大数据量（超过10万行）时速度较慢。建议改用数据透视表，或改用COUNTIFS（多条件

时）。COUNTIFS是COUNTIF的升级版，支持多条件同时满足。

要素7：版本差异说明

Excel2016及更早版本：COUNTIF功能完整，支持通配符。

Excel2019：无变化。

Excel2021/365：新增COUNTIFS多条件统计，COUNTIF仍可用。建议多条件时使用COUNTIFS。

函数4：SUMIF——按条件求和

要素1：基础信息

语法：

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

参数说明：

条件区域：用于判断条件的单元格区域，如A2:A301（部门列）

条件：求和的条件，如"技术部"或">=5000"

求和区域：实际求和的数值区域，如C2:C301（费用列）。省略时对条件区域求和。

要素2：完整公式模板

基础模板：

=SUMIF(A2:A301,"技术部",C2:C301)

变体1（数值条件）：

=SUMIF(C2:C301,">=5000",C2:C301)

变体2（省略求和区域）：

=SUMIF(C2:C301,">=5000")

要素3：真实工作场景完整案例

背景：你是某公司HR，需要统计各部门的年度培训费用总额，用于编制下年度培训预算。部门在A列，培训费用在C

列，共300行数据。

数据：

A2:A301为部门名称

C2:C301为培训费用金额

零基础操作步骤：

第一步：点击F2单元格

第二步：统计技术部培训费用

输入：=SUMIF(A2:A301,"技术部",C2:C301)

注意“技术部”要加英文引号

按Enter后F2显示技术部培训费用总额

第三步：统计市场部培训费用

点击F3，输入：=SUMIF(A2:A301,"市场部",C2:C301)

按Enter

第四步：统计所有部门培训费用

点击F4，输入：=SUM(C2:C301)

按Enter

第五步：统计费用超过5000的总额

点击F5，输入：=SUMIF(C2:C301,">=5000")

注意这里省略了第三参数，因为求和区域和条件区域都是C列

按Enter

预期结果：

F2显示技术部培训费用，如25000

F3显示市场部培训费用，如18000

F4显示总培训费用，如80000

F5显示超过5000的总额，如45000

如果报错了怎么办：

报错显示原因解决办法

结果总是0条件没加引号或区域选错检查条件是否加了引号，区域是否是条件所在列

#VALUE!求和区域包含文本检查C列是否都是数值

结果比预期多条件区域和求和区域行数不一致检查两个区域是否都是2:301

要素4：与其他函数的组合用法

组合1：SUMIF+COUNTIF（计算人均培训费用）

=SUMIF(A2:A301,"技术部",C2:C301)/COUNTIF(A2:A301,"技术部")

适用场景：计算技术部人均培训费用。SUMIF求总费用，COUNTIF数人数，相除得人均。

组合2：SUMIF+SUMIF（统计两个部门合计费用）

=SUMIF(A2:A301,"技术部",C2:C301)+SUMIF(A2:A301,"市场部",C2:C301)

适用场景：统计技术部和市场部的合计培训费用。

要素5：常见错误与正确写法对比

错误1：求和区域和条件区域行数不一致

错误写法：=SUMIF(A2:A301,"技术部",C2:C200)

条件区域300行，求和区域只有199行，会导致计算错误

正确写法：=SUMIF(A2:A301,"技术部",C2:C301)

两个区域行数必须一致

错误2：条件中的大于号未加引号

错误写法：=SUMIF(C2:C301,>=5000)

Excel无法识别，会报错

正确写法：=SUMIF(C2:C301,">=5000")

含比较运算符的条件必须加英文引号

要素6：进阶技巧或性能优化建议

进阶技巧：SUMIF的第三参数可以简写。当求和区域和条件区域是同一列时，可以省略第三参数，如

=SUMIF(C2:C301,">=5000")。

性能优化：多条件求和时使用SUMIFS，比多个SUMIF相加更高效。SUMIFS语法为=SUMIFS(求和区域,条件区域1,条

件1,...)。注意SUMIFS的求和区域在第一个参数，与SUMIF不同。

要素7：版本差异说明

Excel2016及更早版本：SUMIF功能完整。

Excel2019：无变化。

Excel2021/365：新增SUMIFS多条件求和。建议多条件时使用SUMIFS。

函数5：DATEDIF——计算两个日期之间的差值

要素1：基础信息

语法：

=DATEDIF(开始日期,结束日期,返回类型)

返回类型：

"Y"：整年数

"M"：整月数

"D"：天数

"YM"：忽略年后的月数

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

"YD"：忽略年后的天数

要素2：完整公式模板

基础模板：

=DATEDIF(A2,TODAY(),"Y")

变体1（计算月数）：

=DATEDIF(A2,TODAY(),"M")

变体2（计算剩余月数）：

=DATEDIF(TODAY(),A2,"YM")

要素3：真实工作场景完整案例

背景：你是某公司HR，需要计算全体员工的入职年限和司龄工资。入职日期在A列，司龄工资规则为：满1年100元，

满2年200元，以此类推，最高1000元。共400名员工。

数据：

A2:A401为入职日期

B列需要填写入职年限，C列需要填写司龄工资

零基础操作步骤：

第一步：点击B2单元格

第二步：输入入职年限公式

输入：=DATEDIF(A2,TODAY(),"Y")

注意引号是英文引号

按Enter后B2显示入职年限，如“3”

第三步：向下填充

双击B2右下角填充柄，自动填充到B401

第四步：点击C2单元格

输入：=MIN(DATEDIF(A2,TODAY(),"Y")*100,1000)

MIN函数取两个值中较小的一个，确保司龄工资不超过1000

按Enter后C2显示司龄工资，如“300”

第五步：向下填充C2到C401

预期结果：

B列显示入职年限（如1、2、3……）

C列显示司龄工资（如100、200、300……最高1000）

每年1月1日打开表格，司龄自动增加1年，工资自动调整

如果报错了怎么办：

报错显示原因解决办法

#NUM!开始日期大于结束日期检查A2是否小于TODAY()

#VALUE!日期格式不正确检查A列是否为日期格式

显示数字而非年份单元格格式不对将单元格格式设为常规或数值

要素4：与其他函数的组合用法

组合1：DATEDIF+TODAY（显示“X年X个月”格式）

=DATEDIF(A2,TODAY(),"Y")&"年"&DATEDIF(A2,TODAY(),"YM")&"个月"

适用场景：显示“3年5个月”格式的司龄。&符号用于连接文本。

组合2：DATEDIF+IF（判断是否已转正）

=IF(DATEDIF(A2,TODAY(),"Y")>=1,"已转正","试用期")

适用场景：判断员工是否已过试用期（满1年）。

要素5：常见错误与正确写法对比

错误1：开始日期大于结束日期

错误写法：=DATEDIF(TODAY(),A2,"Y")

A2是入职日期，TODAY()是今天，开始日期大于结束日期，返回#NUM!错误

正确写法：=DATEDIF(A2,TODAY(),"Y")

开始日期必须小于结束日期

错误2：返回类型用了中文引号

错误写法：=DATEDIF(A2,TODAY(),"Y")（中文引号）

Excel无法识别中文引号，会报错

正确写法：=DATEDIF(A2,TODAY(),"Y")（英文引号）

所有引号必须是英文引号

要素6：进阶技巧或性能优化建议

进阶技巧：DATEDIF是隐藏函数，在Excel函数列表中找不到，但可以直接输入使用。它最早是为Lotus1-2-3兼容而保

留的，功能稳定。

性能优化：DATEDIF计算速度很快，无需优化。但TODAY()是易失性函数，每次打开表格都会重新计算。如果数据量

大，建议将TODAY()替换为固定日期，减少计算量。

要素7：版本差异说明

Excel2016及更早版本：DATEDIF可用。

Excel2019：无变化。

Excel2021/365：DATEDIF仍可用，但微软官方不推荐使用，建议用YEARFRAC或直接日期相减替代。

函数6：TEXT——将数值转换为指定格式文本

要素1：基础信息

语法：

=TEXT(数值,格式代码)

参数说明：

数值：要转换的数值或日期，如A2

格式代码：目标格式，如"yyyy-mm-dd"、"0.00%"、"#,##0"

要素2：完整公式模板

基础模板：

=TEXT(A2,"yyyy-mm-dd")

变体1（百分比）：

=TEXT(B2,"0.00%")

变体2（千分位）：

=TEXT(C2,"#,##0.00")

要素3：真实工作场景完整案例

背景：你是某公司HR，需要制作员工生日提醒表，将日期格式统一为“X月X日”格式，并计算距离生日的天数。生日在

A列，共200名员工。

数据：

A2:A201为员工生日（日期格式）

零基础操作步骤：

第一步：点击B2单元格

第二步：输入生日格式化公式

输入：=TEXT(A2,"m月d日")

按Enter后B2显示“3月15日”格式

第三步：向下填充B2到B201

第四步：点击C2单元格，计算距离生日天数

输入：=DATEDIF(TODAY(),DATE(YEAR(TODAY()),MONTH(A2),DAY(A2)),"D")

按Enter后C2显示距离生日的天数

第五步：点击D2单元格，标注本月生日

输入：=IF(MONTH(A2)=MONTH(TODAY()),"本月生日","")

按Enter后D2显示“本月生日”或空白

预期结果：

B列显示“3月15日”格式的生日

C列显示距离生日的天数

D列标注本月生日的员工

如果报错了怎么办：

报错显示原因解决办法

#VALUE!格式代码错误检查格式代码是否为Excel识别的代码

显示数字而非日期格式代码不对检查是否用了"yyyy-mm-dd"等日期代码

结果与预期不符格式代码中的大小写敏感月用m，分钟用mm；日用d，星期用ddd

要素4：与其他函数的组合用法

组合1：TEXT+CONCATENATE（生成带格式的文本描述）

=CONCATENATE("入职日期：",TEXT(A2,"yyyy年m月d日"))

适用场景：生成“入职日期：2024年3月15日”格式的文本。

组合2：TEXT+SUM（将求和结果转为千分位格式）

=TEXT(SUM(B2:B100),"#,##0.00")

适用场景：将求和结果转为“1,234.56”格式。

要素5：常见错误与正确写法对比

错误1：格式代码用了中文

错误写法：=TEXT(A2,"年月日")

Excel无法识别“年月日”作为格式代码

正确写法：=TEXT(A2,"yyyy年m月d日")

格式代码必须是Excel识别的代码，中文需与yyyy、m、d组合

错误2：TEXT返回的是文本而非数值

错误写法：=TEXT(A2,"0.00")+1

TEXT返回文本，不能直接参与数值计算，会报错

正确写法：=VALUE(TEXT(A2,"0.00"))+1

先用VALUE转数值，再计算

要素6：进阶技巧或性能优化建议

进阶技巧：TEXT的格式代码中，0表示强制显示位数，#表示可选显示位数。如TEXT(1.5,"0.00")返回“1.50”，

TEXT(1.5,"#.##")返回“1.5”。

性能优化：TEXT是文本函数，返回文本结果。如果后续需要数值计算，建议先用TEXT格式化显示，计算时仍用原始数

值。

要素7：版本差异说明

Excel2016及更早版本：TEXT功能完整。

Excel2019：无变化。

Excel2021/365：新增TEXTSPLIT、TEXTBEFORE、TEXTAFTER等文本函数，TEXT仍可用。

函数7：LEFT——从左侧截取指定长度字符

要素1：基础信息

语法：

=LEFT(文本,截取长度)

参数说明：

文本：要截取的文本或单元格引用，如A2

截取长度：从左侧开始截取的字符数。省略时默认为1。

要素2：完整公式模板

基础模板：

=LEFT(A2,3)

变体1（截取到指定字符之前）：

=LEFT(A2,FIND("-",A2)-1)

变体2（与LEN组合，截取去掉末尾N位）：

=LEFT(A2,LEN(A2)-4)

要素3：真实工作场景完整案例

背景：你是某公司HR，需要从员工编号中提取部门代码。员工编号格式为“部门代码-年份-序号”，如“HR-2024-001”。

共300名员工，编号在A列。

数据：

A2:A301为员工编号，格式为“XX-YYYY-NNN”

零基础操作步骤：

第一步：点击B2单元格

第二步：输入提取部门代码公式

输入：=LEFT(A2,FIND("-",A2)-1)

这个公式的意思是：找到第一个“-”的位置，然后从左边截取到“-”之前

FIND("-",A2)返回“-”在A2中的位置，比如A2是“HR-2024-001”，FIND返回3

FIND返回3，减1等于2，LEFT(A2,2)返回“HR”

第三步：按Enter确认

B2显示“HR”

第四步：向下填充

双击B2右下角填充柄，自动填充到B301

第五步：提取年份和序号

C2输入：=MID(A2,FIND("-",A2)+1,4)，返回“2024”

D2输入：=RIGHT(A2,3)，返回“001”

预期结果：

B列显示部门代码（HR、IT、FN等）

C列显示年份（2024、2023等）

D列显示序号（001、002等）

如果报错了怎么办：

报错显示原因解决办法

#VALUE!FIND找不到“-”检查A2中是否真的有“-”

返回空文本截取长度为0检查FIND返回值是否等于1

结果比预期短截取长度不够调整LEFT的第二参数

要素4：与其他函数的组合用法

组合1：LEFT+FIND（截取到指定分隔符之前）

=LEFT(A2,FIND("-",A2)-1)

适用场景：从“部门-年份-序号”格式中提取部门。

组合2：LEFT+LEN（截取去掉末尾N位）

=LEFT(A2,LEN(A2)-4)

适用场景：去掉末尾4位固定后缀。

要素5：常见错误与正确写法对比

错误1：截取长度超过文本长度

错误写法：=LEFT("AB",5)

返回"AB"，不报错但可能不符合预期

正确写法：=IF(LEN(A2)>=5,LEFT(A2,5),A2)

先用LEN判断长度，再决定是否截取

错误2：FIND找不到分隔符

错误写法：=LEFT(A2,FIND("-",A2)-1)

A2中没有“-”时，FIND返回#VALUE!错误

正确写法：=IFERROR(LEFT(A2,FIND("-",A2)-1),A2)

用IFERROR处理找不到的情况，返回原文本

要素6：进阶技巧或性能优化建议

进阶技巧：Excel365用户可使用TEXTBEFORE函数替代LEFT+FIND组合，语法为=TEXTBEFORE(A2,"-")，更简洁。

性能优化：LEFT是文本函数，速度很快。但FIND在大数据量时可能较慢，建议将FIND结果放在辅助列中复用。

要素7：版本差异说明

Excel2016及更早版本：LEFT功能完整。

Excel2019：无变化。

Excel2021/365：新增TEXTBEFORE、TEXTAFTER，拆分文本更方便。建议新版本用户优先使用

TEXTBEFORE。

函数8：RIGHT——从右侧截取指定长度字符

要素1：基础信息

语法：

=RIGHT(文本,截取长度)

参数说明：

文本：要截取的文本或单元格引用

截取长度：从右侧开始截取的字符数。省略时默认为1。

要素2：完整公式模板

基础模板：

=RIGHT(A2,3)

变体1（截取最后一个分隔符之后的文本）：

=RIGHT(A2,LEN(A2)-FIND("-",A2))

变体2（与LEFT组合提取中间位）：

=RIGHT(LEFT(A2,17))

要素3：真实工作场景完整案例

背景：你是某公司HR，需要从18位身份证号码中提取出生日期和性别。身份证号码在A列，共500名员工。身份证第7-

14位是出生日期，第17位是性别（奇数为男，偶数为女）。

数据：

A2:A501为18位身份证号码

零基础操作步骤：

第一步：点击B2单元格，提取出生日期

输入：=MID(A2,7,8)

按Enter后B2显示“19900315”格式的出生日期

第二步：点击C2单元格，转为日期格式

输入：=TEXT(MID(A2,7,8),"0000-00-00")

按Enter后C2显示“1990-03-15”

第三步：点击D2单元格，提取性别

输入：=IF(MOD(RIGHT(LEFT(A2,17)),2)=1,"男","女")

原理：先取前17位，再取最后一位（第17位），用MOD判断奇偶

按Enter后D2显示“男”或“女”

第四步：点击E2单元格，计算年龄

输入：=DATEDIF(TEXT(MID(A2,7,8),"0000-00-00"),TODAY(),"Y")

按Enter后E2显示年龄

第五步：向下填充B2:E2到第501行

预期结果：

B列显示8位出生日期

C列显示标准日期格式

D列显示性别

E列显示年龄

如果报错了怎么办：

报错显示原因解决办法

#VALUE!身份证号码不是18位检查A列数据长度

性别全是“女”MOD判断反了检查是否用了=1判断奇数

年龄不对日期格式转换错误检查MID截取的8位是否正确

要素4：与其他函数的组合用法

组合1：RIGHT+LEFT（提取身份证第17位）

=RIGHT(LEFT(A2,17))

适用场景：提取身份证性别位。先用LEFT取前17位，再用RIGHT取最后一位。

组合2：RIGHT+LEN+FIND（截取最后一个分隔符之后的文本）

=RIGHT(A2,LEN(A2)-FIND("-",A2))

适用场景：从“部门-年份-序号”格式中提取序号。

要素5：常见错误与正确写法对比

错误1：截取长度用了负数

错误写法：=RIGHT(A2,-3)

返回#VALUE!错误

正确写法：=RIGHT(A2,3)

截取长度必须为正整数或0

错误2：与LEFT混淆

错误写法：=RIGHT(A2,FIND("-",A2)-1)

想截取分隔符之前，但RIGHT从右侧截取，结果错误

正确写法：=LEFT(A2,FIND("-",A2)-1)

截取分隔符之前用LEFT，截取之后用RIGHT

要素6：进阶技巧或性能优化建议

进阶技巧：提取身份证性别更直观的写法是=MID(A2,17,1)，直接取第17位，无需RIGHT+LEFT组合。

性能优化：RIGHT是文本函数，速度很快。身份证提取建议用MID替代RIGHT+LEFT组合，更高效。

要素7：版本差异说明

Excel2016及更早版本：RIGHT功能完整。

Excel2019：无变化。

Excel2021/365：新增TEXTAFTER，截取指定字符之后的文本更方便。

函数9：MID——从中间截取指定长度字符

要素1：基础信息

语法：

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

参数说明：

文本：要截取的文本或单元格引用

起始位置：从第几个字符开始截取，从1开始数

截取长度：截取多少个字符

要素2：完整公式模板

基础模板：

=MID(A2,7,8)

变体1（与FIND组合）：

=MID(A2,FIND("-",A2)+1,4)

变体2（提取身份证出生日期）：

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

要素3：真实工作场景完整案例

背景：你是某公司HR，需要从员工邮箱中提取用户名。邮箱格式为“用户名@公司域名”，如“zhangsan@company.co

m”。邮箱在A列，共400名员工。

数据：

A2:A401为员工邮箱

零基础操作步骤：

第一步：点击B2单元格，提取用户名

输入：=LEFT(A2,FIND("@",A2)-1)

按Enter后B2显示“zhangsan”

第二步：点击C2单元格，提取域名

输入：=MID(A2,FIND("@",A2)+1,100)

起始位置是“@”位置+1，截取长度设100足够覆盖所有域名

按Enter后C2显示“”

第三步：点击D2单元格，判断邮箱格式

输入：=IF(ISNUMBER(FIND("@",A2)),"格式正确","格式错误")

按Enter后D2显示格式判断结果

第四步：向下填充B2:D2到第401行

预期结果：

B列显示用户名

C列显示域名

D列显示格式判断

如果报错了怎么办：

报错显示原因解决办法

#VALUE!FIND找不到“@”检查A列邮箱是否含“@”

返回空文本起始位置超出文本长度检查FIND返回值

结果包含“@”起始位置没加1检查FIND后是否加了1

要素4：与其他函数的组合用法

组合1：MID+FIND（截取指定字符之后的文本）

=MID(A2,FIND("@",A2)+1,100)

适用场景：提取邮箱域名。

组合2：MID+TEXT（将8位数字转为日期）

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

适用场景：身份证出生日期提取。

要素5：常见错误与正确写法对比

错误1：起始位置为0

错误写法：=MID(A2,0,5)

返回#VALUE!错误，MID的起始位置从1开始

正确写法：=MID(A2,1,5)

从第1个字符开始截取

错误2：截取长度超过剩余字符数

错误写法：=MID(A2,5,100)

A2只有10个字符时，会返回从第5位到末尾的全部字符，不报错但可能不符合预期

正确写法：=MID(A2,5,6)

精确计算剩余字符数，避免多余内容

要素6：进阶技巧或性能优化建议

进阶技巧：MID是提取固定位置文本的最佳选择。身份证提取出生日期（第7位开始取8位）、提取性别（第17位开始

取1位）都用MID。

性能优化：MID速度很快，无需优化。但FIND在大数据量时可能较慢，建议将FIND结果放在辅助列中复用。

要素7：版本差异说明

Excel2016及更早版本：MID功能完整。

Excel2019：无变化。

Excel2021/365：新增TEXTBEFORE、TEXTAFTER，截取指定字符前后的文本更方便。

函数10：CONCATENATE——合并多个文本

要素1：基础信息

语法：

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

参数说明：

文本1,文本2,...：要合并的文本或单元格引用，最多255个

要素2：完整公式模板

基础模板：

=CONCATENATE(A2,B2,C2)

变体1（与TEXT组合）：

=CONCATENATE(A2,"入职于",TEXT(B2,"yyyy年m月d日"))

变体2（使用&符号替代）：

=A2&"入职于"&TEXT(B2,"yyyy年m月d日")

要素3：真实工作场景完整案例

背景：你是某公司HR，需要制作员工工牌信息，将姓名、部门、工号合并为“姓名-部门-工号”格式。姓名在A列，部门

在B列，工号在C列，共300名员工。

数据：

A2:A301为姓名

B2:B301为部门

C2:C301为工号

零基础操作步骤：

第一步：点击D2单元格

第二步：输入合并公式

输入：=CONCATENATE(A2,"-",B2,"-",C2)

注意分隔符“-”要加英文引号

按Enter后D2显示“张三-技术部-EMP001”

第三步：向下填充D2到D301

第四步：如需换行显