Excel常用公式函数 按高频办公场景分类整理

作用：纵向精准查找目标数据，同时屏蔽查找失败的#N/A错误，让表格整洁无乱码，兼顾实用性和美观度。

场景示例：根据员工工号查找对应姓名、部门、薪资，无对应工号时提示“信息有误”。假设A列工号、B列姓名、C列部门、D列薪资，查找E2单元格工号对应部门，公式：=IFERROR(VLOOKUP(E2,A2:D20,3,0),"信息有误")。下图仅供参考非此示例图

场景二：单/多条件数据求和

核心函数：SUMIF（单条件）、SUMIFS（多条件）

1. SUMIF函数

语法：=SUMIF(条件区域,条件,求和区域)

作用：满足单个指定条件，即可对对应数值求和，适配简单数据汇总。

场景示例1：统计销售部员工薪资总和，A列部门、B列薪资，公式：=SUMIF(A2:A25,"销售部",B2:B25)。

场 景 示 例 2：SUMIF（C2：C16,“>7”，C2：C16），结果为对C2：C16区域内大于7的单元格进行累加求和。

2. SUMIFS函数

语法：=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2)

作用：满足多个并列条件，再对对应数值求和，适配精细化数据统计。

场景示例：统计3月份销售部的业绩总额，A列部门、B列月份、C列业绩，公式：=SUMIFS(C2:C30,A2:A30,"销售部",B2:B30,"3月")。

场景示例2：求下列不规则销售记录饼干产品在2025年12月6日的销售总额“=SUMIFS(C15:C32,A15:A32,"饼干",B15:B32,"2025/12/6")”可解释为“=SUMIFS(要求和的销售额区，条件区域1，"条件1"，条件区域2，"条件2")”得出了饼干在2025/12/6单品营业额是196元

场景三：数据计数统计

核心函数：COUNTIF（单条件计数）、COUNTA（非空值计数）

1. COUNTIF函数

语法：=COUNTIF(统计区域,条件)

作用：统计区域内满足单个条件的单元格数量，快速核算符合要求的数据条数。

场景示例：统计成绩大于90分的学生人数，A列成绩，公式：=COUNTIF(A2:A20,">90")。

2. COUNTA函数

语法：=COUNTA(统计区域)

作用：统计区域内非空单元格的数量，不管内容是文本、数字还是符号，均可统计。

场景示例：统计员工花名册总人数，D列姓名，公式：=COUNTA(A2:A30)。

场景四：文本提取与拆分

核心函数：MID+TEXT、LEFT+FIND

1. MID+TEXT函数

语法：=TEXT(MID(文本单元格,开始位置,提取位数),"格式代码")

作用：从文本指定位置提取字符，并规范格式，多用于身份证、卡号等信息拆解。

场景示例：提取身份证号出生日期并规范格式，A2单元格为身份证号，公式：=TEXT(MID(A2,7,8),"0000年00月00日")。

2. LEFT+FIND函数

语法：=LEFT(文本单元格,FIND("分隔符",文本单元格)-1)

作用：提取分隔符前的文本内容，快速拆分带符号的混合文本。

场景示例：拆分“姓名-部门”提取姓名，A2单元格为“张三-技术部”，公式：

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

场景五：日期与时间计算

核心函数：DATEDIF+TODAY

语法：=DATEDIF(起始日期,TODAY(),"Y")

作用：计算两个日期的差值，搭配TODAY函数可实现动态计算，无需手动更新日期。

场景示例：根据入职日期计算员工工龄，A2单元格为入职日期，公式：=DATEDIF(A2,TODAY(),"Y")&"年"&DATEDIF(A2,TODAY(),"YM")&"个月"；根据出生日期计算年龄，公式：=DATEDIF(B2,TODAY(),"Y")&"岁"。

场景六：数据判断与逻辑处理

核心函数：IF函数（单/多条件）

语法：=IF(条件,满足条件返回值,不满足返回值)；多条件：=IF(AND(条件1,条件2),真值,假值)

作用：根据设定条件，自动判定并返回对应结果，实现数据自动化分类。

场景示例：判定成绩是否及格，A2单元格为成绩，公式：=IF(A2>=60,"及格","不及格")；判定员工是否达标，业绩≥5000且出勤满勤为达标，公式：=IF(AND(B2>=5000,C2="满勤"),"达标","未达标")。

场景七：筛选后数据汇总

核心函数：SUBTOTAL函数

语法：=SUBTOTAL(109,求和区域)

作用：仅对筛选后的可见单元格求和，忽略隐藏行数据，解决普通SUM函数统计全部数据的弊端。

场景示例：筛选某品类商品后，统计可见商品销售额，C列为销售额，公式：=SUBTOTAL(109,C2:C25)；筛选某部门后，统计员工薪资合计，公式：=SUBTOTAL(109,D2:D20)。

场景八：数据重复值判定

核心函数：COUNTIF+IF函数

语法：=IF(COUNTIF(锁定区域,目标单元格)>1,"重复","唯一")

作用：快速检测单元格内容是否重复，方便数据清洗和去重，避免录入错误。

场景示例：检测员工工号是否重复，A列为工号，公式：=IF(COUNTIF($A$2:$A$20,A2)>1,"重复","唯一")；检测订单号、手机号是否重复，替换对应区域即可直接套用。其中A2单元格是第一个值开始位置，下图是以A1单元格开始的示例