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

Excel函数大全——HR/财务/运营必会的50个公式标签：Excel函数·HR工具·财务工具·运营工具日期：2026年9月24日一、查找与引用类（8个）1.VLOOKUP—纵向查找语法：=VLOOKUP(查找值,查找区域,返回列序号,匹配方式)HR场景：根据员工工号从花名册中查找部门、职级、入职日期。工资表中引用“全员信息表”的数据，最常用的就是VLOOKUP函数。财务场景：根据供应商编码查供应商名称、根据产品编码查单价。示例：=VLOOKUP(A2,员工信息表!$A:$F,4,FALSE)—用工号在A列查找，返回第4列（部门）。必坑点：查找值必须在查找区域的第一列，否则返回#N/A。第4参数必须设为FALSE（精确匹配），90%的VLOOKUPbug都出在这里。2.XLOOKUP—万能查找（推荐）语法：=XLOOKUP(查找值,查找数组,返回数组,[找不到时返回],[匹配模式])HR场景：用工号反查姓名（工号在B列、姓名在A列），XLOOKUP可以直接从右向左查询，无需调整列顺序。财务场景：在动态月报中查找数据，中间插入新字段时公式不会因列序号错位而报错。示例：=XLOOKUP(A2,员工信息表!B:B,员工信息表!A:A,"未找到")版本要求：Excel2021及以上或Microsoft365。3.INDEX+MATCH—反向查找黄金组合语法：=INDEX(返回列,MATCH(查找值,查找列,0))HR场景：根据姓名查找员工工号（姓名在B列、工号在A列）。财务场景：在多维数据表中精确定位某个科目的金额。示例：=INDEX(A:A,MATCH("张三",B:B,0))适用场景：老版本Excel（2019及以前）+需要反向查找。4.XLOOKUPvsVLOOKUPvsINDEX/MATCH选择指南场景推荐函数Excel2021+/Microsoft365XLOOKUP老版本+需要反向查找INDEX/MATCH老版本+单向查找VLOOKUP（注意第4参数）5.FILTER—条件筛选提取语法：=FILTER(数据区域,条件,[无结果时返回])运营场景：提取所有“华东”区域且金额>10000的销售记录。示例：=FILTER(A2:C10,(B2:B10="华东")*(C2:C10>10000))版本要求：Excel2021及以上。6.UNIQUE—去重提取语法：=UNIQUE(数据区域)运营场景：从产品名称列表中提取唯一的产品清单。HR场景：从员工名单中提取唯一的部门列表。示例：=UNIQUE(A2:A100)7.ROW/COLUMN—返回行号/列号语法：=ROW([单元格])/=COLUMN([单元格])运营场景：生成自动序号，=ROW()-1在删除行后自动更新。8.INDIRECT—间接引用语法：=INDIRECT(文本形式的单元格地址)HR场景：跨表引用不同月份的工资表，月份名放在单元格中，用INDIRECT动态构建引用。示例：=INDIRECT("'"&A1&"'!B2")—A1中存放月份名（如“9月工资表”）。二、条件统计类（10个）9.SUM—求和语法：=SUM(数值1,[数值2],...)通用场景：对一列数值求和。所有岗位最基础的函数。10.AVERAGE—平均值语法：=AVERAGE(数值1,[数值2],...)HR场景：计算部门平均工资。=AVERAGE(C2:C20)运营场景：计算各渠道的平均获客成本。11.COUNT—计数（数字）语法：=COUNT(区域)运营场景：统计某列中有多少个数字（忽略文本和空值）。用于生成序号：=COUNT(A$1:A1)+1。12.COUNTA—非空计数语法：=COUNTA(区域)HR场景：统计已提交考勤记录的人数（包含文本和数字）。13.COUNTIF—单条件计数语法：=COUNTIF(区域,条件)HR场景：统计全公司迟到人次。=COUNTIF(考勤列,"迟到")。运营场景：统计某产品的订单数量。14.COUNTIFS—多条件计数语法：=COUNTIFS(范围1,条件1,[范围2,条件2],...)HR场景：统计销售部的迟到人次。=COUNTIFS(考勤列,"迟到",部门列,"销售部")。必坑点：多个条件区域的行数必须一致，否则返回#VALUE!错误。15.SUMIF—单条件求和语法：=SUMIF(条件区域,条件,[求和区域])财务场景：按部门统计工资总额、按供应商统计采购额、按月份汇总费用。示例：=SUMIF(部门列,"销售部",工资列)16.SUMIFS—多条件求和语法：=SUMIFS(求和区域,条件区域1,条件1,[条件区域2,条件2],...)HR场景：统计人事部主管级别的工资总额。=SUMIFS(工资列,部门列,"人事部",职级列,"主管")。财务场景：统计某月某科目的费用合计。必坑点：求和区域放在第一个参数位置，与SUMIF不同。17.AVERAGEIFS—多条件平均值语法：=AVERAGEIFS(平均值区域,条件区域1,条件1,...)HR场景：计算技术部3年以上员工的平均工资。18.SUMPRODUCT—数组乘积求和语法：=SUMPRODUCT(数组1,[数组2],...)运营场景：多条件求和（不需要数组公式语法）。=SUMPRODUCT((C:C="华东")*(D:D>10000)*D:D)。财务场景：加权平均计算。=SUMPRODUCT(B:B,C:C)/SUM(C:C)。注意：参与相乘的数组中不能含有文本值，否则返回#VALUE!错误。三、逻辑判断类（5个）19.IF—条件判断语法：=IF(判断条件,符合时返回,不符合时返回)财务场景：判断工资是否超过5000需缴个税。=IF(E2>1500,"达标","不达标")。HR场景：判断员工是否通过试用期。20.IFS—多条件判断（替代嵌套IF）语法：=IFS(条件1,结果1,条件2,结果2,...)HR场景：绩效等级判定。=IFS(A2>=90,"优秀",A2>=80,"良好",A2>=70,"合格",TRUE,"需改进")优势：比多层嵌套IF更易读、易维护。21.IFERROR—错误处理语法：=IFERROR(原公式,出错时返回的值)通用场景：VLOOKUP找不到值时返回“未找到”而不是#N/A。示例：=IFERROR(VLOOKUP(A2,表!A:B,2,FALSE),"未找到")22.AND/OR—逻辑与/或语法：=AND(条件1,条件2)/=OR(条件1,条件2)HR场景：判断员工是否同时满足“工龄≥3年”和“绩效≥良好”。23.IF+AND/OR嵌套HR场景：年终奖资格判定——工龄≥1年且绩效≥合格且无重大违纪。示例：=IF(AND(工龄>=1,绩效>="合格",违纪="无"),"有资格","无资格")四、文本处理类（5个）24.LEFT—从左截取语法：=LEFT(文本,截取字符数)财务场景：从发票号码中提取前3位识别发票类型。示例：=LEFT(A2,3)25.RIGHT—从右截取语法：=RIGHT(文本,截取字符数)HR场景：从身份证号提取最后4位。=RIGHT(A2,4)26.MID—从中间截取语法：=MID(文本,起始位置,截取字符数)HR场景：从身份证号中提取出生日期。=MID(A2,7,8)27.TEXTJOIN—按分隔符合并文本语法：=TEXTJOIN(分隔符,是否忽略空值,文本1,[文本2],...)HR场景：将多个部门的员工姓名合并成一列，用顿号分隔。示例：=TEXTJOIN("、",TRUE,A2:A10)28.CONCAT—合并文本语法：=CONCAT(文本1,[文本2],...)HR场景：将“姓”和“名”合并为全名。五、日期与时间类（7个）29.TODAY/NOW—当前日期/时间语法：=TODAY()/=NOW()HR场景：计算员工工龄，以当前日期为终点。=DATEDIF(入职日期,TODAY(),"Y")30.DATEDIF—日期间隔计算语法：=DATEDIF(开始日期,结束日期,返回类型)（返回类型："Y"年、"M"月、"D"天）HR场景：计算员工工龄和年龄。=DATEDIF(B2,TODAY(),"Y")。财务场景：计算账龄（从发票日期到今天的月数）。注意：DATEDIF是Excel中隐藏函数，不会出现在函数提示列表中，但可以直接输入使用。31.YEARFRAC—年分数（精确工龄）语法：=YEARFRAC(开始日期,结束日期,[基础天数])HR场景：计算带小数的工龄，用于薪酬系数折算、绩效权重分配等需要精确到小数位的场景。示例：=YEARFRAC(B2,TODAY(),1)返回如3.75年。32.EDATE—月份偏移语法：=EDATE(开始日期,月份数)HR场景：计算试用期结束日期。=EDATE(入职日期,3)返回入职后3个月的对应日期。财务场景：计算合同续签提醒日期。33.EOMONTH—月末日期语法：=EOMONTH(开始日期,月份数)财务场景：计算月末结账日期、下月最后一天。=EOMONTH(TODAY(),1)返回下个月最后一天。HR场景：计算工资结算周期截止日。34.WORKDAY—工作日计算语法：=WORKDAY(开始日期,工作日数,[节假日])HR场景：计算N个工作日后的日期（跳过周末和法定假日）。=WORKDAY(TODAY(),10)返回10个工作日后的日期。运营场景：计算服务承诺的完成截止日。35.NETWORKDAYS—工作日天数语法：=NETWORKDAYS(开始日期,结束日期,[节假日])HR场景：计算某段期间的实际工作天数，用于考勤核算。运营场景：计算项目实际工期（排除周末）。六、财务专用函数（5个）36.SLN—直线折旧语法：=SLN(原值,残值,使用年限)财务场景：固定资产按直线法计提折旧。=SLN(100000,5000,5)返回年折旧额19000元。适用：固定资产均匀损耗的情况。37.DB—固定余额递减折旧语法：=DB(原值,残值,使用年限,折旧期数,[首年月份数])财务场景：按固定余额递减法计算折旧，前期折旧额较大。38.SYD—年数总和法折旧语法：=SYD(原值,残值,使用年限,折旧期数)财务场景：按年数总和法加速折旧。39.NPV—净现值语法：=NPV(折现率,现金流1,[现金流2],...)财务场景：评估项目投资价值，计算未来现金流的净现值。40.IRR—内部收益率语法：=IRR(现金流序列,[猜测值])财务场景：计算投资项目的内部收益率，与基准收益率对比判断项目是否可行。七、排名与百分位类（4个）41.RANK—排名语法：=RANK(数值,排名区域,[排序方式])运营场景：计算当日收入的排名。=RANK(B2,B:B)。HR场景：对员工绩效分数进行排名。42.RANK.EQ—排名（并列同名）语法：=RANK.EQ(数值,排名区域,[排序方式])说明：RANK的升级版，并列时返回相同排名。43.PERCENTRANK—百分位排名语法：=PERCENTRANK(数据区域,数值,[有效位数])运营场景：判断某员工绩效在团队中的百分位位置。=PERCENTRANK($B$5:$B$9,B5,2)返回该员工在团队中的百分位（0%到100%）。44.QUARTILE—四分位数语法：=QUARTILE(数据区域,四分位值)运营场景：分析销售数据的分布情况，判断业绩的集中区间。八、数据清洗与转换类（3个）45.TRIM—去除多余空格语法：=TRIM(文本)HR场景：清理从系统导出的员工姓名前后的多余空格，避免VLOOKUP匹配失败。46.TEXT—格式化数字语法：=TEXT(数值,格式代码)财务场景：将数字格式化为金额显示。=TEXT(12345.6,"¥#,##0.00")返回“¥12,345.60”。HR场景：将日期格式化为“2026年9月”的格式。47.VALUE—文本转数值语法：=VALUE(文本)财务场景：将从系统导出的文本格式金额转换为可计算的数值。九、错误处理与调试类（3个）48.ISERROR/ISNA—错误判断语法：=ISERROR(值)/=ISNA(值)场景：配合IF使用，判断公式是否返回错误，再做相应处理。49.IFERROR嵌套应用场景：VLOOKUP返回#N/A时显示“未找到”，SUMIFS返回#VALUE!时显示0。示例：=IFERROR(VLOOKUP(A2,表!A:C,3,FALSE),"未找到")50.AGGREGATE—忽略错误的聚合语法：=AGGREGATE(功能编号,忽略选项,数据区域)场景：计算一列中包含错误值（如#DIV/0!、#N/A）的平均值时，忽略错误值只计算有效数据。=AGGREGATE(1,8,A1:A6)忽略错误值返回有效数字的平均值。功能编号：1=AVERAGE，2=COUNT，4=M