Excel常用函数大全：从入门到实战

Excel常用函数大全：从入门到实战适用人群：职场新人、文员、财务、行政、运营、数据整理人员

学习目标：掌握办公场景中最常用的Excel函数，提升数据处理效率一、Excel常用函数概览1.1函数是什么？函数是Excel预置的公式，用于执行特定计算。每个函数都有固定的名称和参数结构。基本语法：=函数名(参数1,参数2,...)例如：=SUM(A1:A10)表示对A1到A10单元格求和。1.2函数分类速查表类别代表函数典型应用场景数学统计SUM、AVERAGE、MAX、MIN销售额汇总、平均分计算条件判断IF、AND、OR、IFS业绩评级、考勤判定查找引用VLOOKUP、XLOOKUP、INDEX+MATCH跨表匹配、信息检索条件统计COUNTIF、SUMIF、COUNTIFS、SUMIFS按条件计数、按条件求和日期时间TODAY、NOW、DATEDIF、EOMONTH合同到期提醒、工龄计算文本处理LEFT、RIGHT、MID、TEXT、CONCAT身份证号提取、格式统一1.3输入函数的三种方式直接输入：在单元格中输入=函数名(函数向导：点击公式栏旁的fx按钮自动补全：输入=和函数首字母，按Tab键自动补全二、SUM/AVERAGE/MAX/MIN基础函数2.1SUM求和函数语法：=SUM(数值1,[数值2],...)常用场景：用法示例说明连续区域=SUM(A1:A10)对A1到A10求和多个不连续区域=SUM(A1:A5,C1:C5)对两个区域分别求和后相加整列求和=SUM(A:A)对A列全部数值求和（注意：会计算整个列）与常量相加=SUM(A1:A10,100)区域总和再加100实战示例：场景：计算Q1季度各月销售额总和

数据：B2=12000,B3=15000,B4=18000

公式：=SUM(B2:B4)

结果：45000注意事项：SUM忽略文本和空单元格，但会把逻辑值TRUE/FALSE转为1/0计算如果区域内有错误值（如#N/A），SUM会返回错误2.2AVERAGE平均值函数语法：=AVERAGE(数值1,[数值2],...)实战示例：场景：计算5位员工的平均业绩

数据：B2:B6分别为80,90,85,70,95

公式：=AVERAGE(B2:B6)

结果：84注意事项：AVERAGE只统计包含数字的单元格，空单元格和文本会被忽略如果需要包含0的平均值，AVERAGE会正常计算；但如果需要排除0，需用=AVERAGEIF(区域,"<>0")2.3MAX/MIN最大最小值语法：=MAX(数值1,[数值2],...)—返回最大值=MIN(数值1,[数值2],...)—返回最小值实战示例：场景：找出本月最高和最低销售额

公式：=MAX(B2:B20)和=MIN(B2:B20)2.4综合案例：月度销售报表项目1月2月3月华东区120001500018000华北区100001100013000华南区90001200015000合计=SUM(B2:B4)=SUM(C2:C4)=SUM(D2:D4)平均=AVERAGE(B2:B4)=AVERAGE(C2:C4)=AVERAGE(D2:D4)最高=MAX(B2:B4)=MAX(C2:C4)=MAX(D2:D4)三、IF/AND/OR条件函数3.1IF单条件判断语法：=IF(条件,条件成立时返回值,条件不成立时返回值)参数说明：条件：逻辑表达式，如A1>60、B2="合格"条件成立时返回值：条件为TRUE时显示的内容条件不成立时返回值：条件为FALSE时显示的内容实战示例：场景1：成绩是否及格（60分及格）

公式：=IF(B2>=60,"及格","不及格")场景2：销售额达标判定（目标10000）

公式：=IF(B2>=10000,"达标","未达标")场景3：计算奖金（达标奖励500，否则0）

公式：=IF(B2>=10000,500,0)3.2IF嵌套（多条件判断）语法：=IF(条件1,结果1,IF(条件2,结果2,结果3))实战示例：场景：成绩等级评定90分及以上：优秀80-89分：良好60-79分：及格60分以下：不及格公式：

=IF(B2>=90,"优秀",IF(B2>=80,"良好",IF(B2>=60,"及格","不及格")))嵌套层数限制：Excel2007及以上版本最多嵌套64层，但建议不超过3-4层，否则可读性差。复杂多条件建议改用IFS函数。3.3AND函数（同时满足多个条件）语法：=AND(条件1,条件2,...)所有条件都为TRUE时，返回TRUE；任一条件为FALSE，返回FALSE。实战示例：场景：判断员工是否同时满足"业绩≥10000"且"出勤率≥95%"

公式：=AND(B2>=10000,C2>=0.95)场景：结合IF使用，判断是否可以晋升

公式：=IF(AND(B2>=10000,C2>=0.95,D2>=2),"可晋升","暂不可晋升")3.4OR函数（满足任一条件即可）语法：=OR(条件1,条件2,...)任一条件为TRUE时，返回TRUE；所有条件为FALSE时，返回FALSE。实战示例：场景：判断是否为"优秀员工"（业绩≥15000或客户满意度≥90%）

公式：=IF(OR(B2>=15000,C2>=0.9),"优秀员工","普通员工")3.5AND+OR组合使用实战示例：场景：判断是否有年终奖（需同时满足：在职满1年，且（业绩前10%或无迟到记录））

公式：

=IF(AND(D2>=1,OR(E2<=10,F2=0)),"有年终奖","无年终奖")3.6IFS函数（Excel2016+/Microsoft365）语法：=IFS(条件1,结果1,条件2,结果2,...)按顺序判断条件，返回第一个满足条件的结果。实战示例（替代IF嵌套）：场景：成绩等级评定

公式：

=IFS(B2>=90,"优秀",B2>=80,"良好",B2>=60,"及格",B2<<60,"不及格")四、VLOOKUP/XLOOKUP查找函数4.1VLOOKUP垂直查找语法：=VLOOKUP(查找值,查找区域,返回列序号,[精确匹配])参数详解：参数说明示例查找值要查找的内容A2（员工工号）查找区域包含查找值和返回值的数据表B2:D20返回列序号返回值在区域中的第几列3（第3列）精确匹配FALSE=精确匹配，TRUE=近似匹配通常用FALSE或0实战示例：场景：根据员工工号查询姓名和部门

数据源（Sheet2）：A列=工号，B列=姓名，C列=部门

查询表（Sheet1）：A2=工号，需要在B2显示姓名，C2显示部门查询姓名公式：=VLOOKUP(A2,Sheet2!A:C,2,FALSE)

查询部门公式：=VLOOKUP(A2,Sheet2!A:C,3,FALSE)VLOOKUP使用要点：查找值必须在区域的第一列返回列序号从查找区域的第一列开始数，不是从工作表A列开始数精确匹配通常用0或FALSE，近似匹配用于数值区间查找绝对引用：区域建议使用$锁定，如Sheet2!$A:$C，方便向下填充VLOOKUP常见问题：问题原因解决方法#N/A错误找不到匹配值检查数据是否存在、是否有空格、格式是否一致#REF!错误返回列序号超出区域列数检查区域选择和列序号返回错误结果近似匹配导致确保第4参数为FALSE4.2XLOOKUP新一代查找函数（Excel2021+/Microsoft365）语法：=XLOOKUP(查找值,查找数组,返回数组,[未找到时返回],[匹配模式],[搜索模式])XLOOKUP相比VLOOKUP的优势：特性VLOOKUPXLOOKUP查找方向只能从左往右支持从左往右、从右往左查找列位置必须在第一列可以在任意位置找不到时返回#N/A可自定义返回值插入/删除列可能破坏公式不受影响默认匹配近似匹配精确匹配实战示例：场景：根据姓名查找工号（反向查找，VLOOKUP难以实现）

数据源：A列=工号，B列=姓名

公式：=XLOOKUP(D2,B:B,A:A,"未找到")场景：根据工号查找信息，找不到显示"查无此人"

公式：=XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C,"查无此人")4.3INDEX+MATCH组合（经典万能查找）语法：=INDEX(返回区域,行号,[列号])—返回区域中指定位置的值=MATCH(查找值,查找数组,[匹配类型])—返回查找值在数组中的位置组合用法：=INDEX(返回列,MATCH(查找值,查找列,0))实战示例：场景：根据产品名称查找价格（产品名称在B列，价格在D列）

公式：=INDEX(D:D,MATCH(A2,B:B,0))INDEX+MATCH的优势：不受插入/删除列影响支持双向查找（同时指定行和列）性能通常优于VLOOKUP五、COUNTIF/SUMIF统计函数5.1COUNTIF单条件计数语法：=COUNTIF(统计区域,条件)实战示例：场景公式说明统计"及格"人数=COUNTIF(B2:B20,"及格")统计区域中等于"及格"的个数统计销售额≥10000的人数=COUNTIF(B2:B20,">=10000")条件需加引号统计某部门人数=COUNTIF(B2:B20,"销售部")文本条件直接写统计非空单元格=COUNTIF(A2:A20,"<>")<>表示非空统计空单元格=COUNTIF(A2:A20,"")空引号表示空值通配符使用：*代表任意多个字符："张*"匹配所有姓张的?代表单个字符："张?"匹配"张三"、"张四"等5.2SUMIF单条件求和语法：=SUMIF(条件区域,条件,[求和区域])参数说明：如果省略"求和区域"，则对"条件区域"本身求和"条件区域"和"求和区域"大小必须一致实战示例：场景1：统计"销售部"的总业绩

公式：=SUMIF(B2:B20,"销售部",C2:C20)

（B列是部门，C列是业绩）场景2：统计业绩≥10000的总金额

公式：=SUMIF(C2:C20,">=10000")场景3：统计某员工（A2单元格中的姓名）的总业绩

公式：=SUMIF(B2:B20,A2,C2:C20)5.3COUNTIFS/SUMIFS多条件统计COUNTIFS语法：=COUNTIFS(条件区域1,条件1,[条件区域2,条件2],...)SUMIFS语法：=SUMIFS(求和区域,条件区域1,条件1,[条件区域2,条件2],...)注意：SUMIFS的参数顺序与SUMIF不同，求和区域在前。实战示例：场景1：统计"销售部"且"业绩≥10000"的人数

公式：=COUNTIFS(B2:B20,"销售部",C2:C20,">=10000")场景2：统计"华东区""销售部"的总业绩

公式：=SUMIFS(D2:D20,B2:B20,"华东区",C2:C20,"销售部")场景3：统计2024年1月的销售额（日期在A列，金额在B列）

公式：=SUMIFS(B:B,A:A,">=2024-1-1",A:A,"<=2024-1-31")5.4条件统计函数速查需求函数示例单条件计数COUNTIF=COUNTIF(A:A,"男")多条件计数COUNTIFS=COUNTIFS(A:A,"男",B:B,">30")单条件求和SUMIF=SUMIF(A:A,"销售部",B:B)多条件求和SUMIFS=SUMIFS(C:C,A:A,"销售部",B:B,">10000")单条件平均AVERAGEIF=AVERAGEIF(B:B,">=60")多条件平均AVERAGEIFS=AVERAGEIFS(C:C,A:A,"男",B:B,">30")单条件最大值MAXIFS=MAXIFS(C:C,A:A,"销售部")单条件最小值MINIFS=MINIFS(C:C,A:A,"销售部")六、日期时间函数6.1TODAY/NOW当前日期时间函数语法结果说明TODAY=TODAY()2026/6/2返回当前日期（不含时间）NOW=NOW()2026/6/28:53返回当前日期和时间特点：每次打开或刷新工作表时会自动更新。6.2YEAR/MONTH/DAY/WEEKDAY提取日期信息函数语法示例结果YEAR=YEAR(日期)=YEAR(A1)2026MONTH=MONTH(日期)=MONTH(A1)6DAY=DAY(日期)=DAY(A1)2WEEKDAY=WEEKDAY(日期,[类型])=WEEKDAY(A1,2)2（表示周二）WEEKDAY返回类型：类型1（默认）：1=周日，7=周六类型2：1=周一，7=周日（中国常用）类型3：0=周一，6=周日实战示例：场景：从入职日期提取入职年份、月份

公式：=YEAR(B2)和=MONTH(B2)场景：判断某天是周几（中文显示）

公式：=TEXT(A1,"aaaa")→返回"星期二"

或=CHOOSE(WEEKDAY(A1,2),"周一","周二","周三","周四","周五","周六","周日")6.3DATEDIF计算日期间隔（隐藏函数）语法：=DATEDIF(开始日期,结束日期,单位)单位代码：代码含义示例Y整年数计算工龄、年龄M整月数计算账期D天数计算间隔天数YM忽略年和日，计算月数差生日倒计时中的月MD忽略年和月，计算天数差生日倒计时中的天YD忽略年，计算天数差今年已过天数实战示例：场景1：计算员工年龄（A2=出生日期）

公式：=DATEDIF(A2,TODAY(),"Y")场景2：计算工龄（A2=入职日期）

公式：=DATEDIF(A2,TODAY(),"Y")&"年"&DATEDIF(A2,TODAY(),"YM")&"个月"场景3：计算合同剩余天数（A2=到期日）

公式：=A2-TODAY()

或=DATEDIF(TODAY(),A2,"D")6.4EOMONTH/EDATE日期推算函数语法说明示例EDATE=EDATE(日期,月数)指定日期前后N个月的日期=EDATE(TODAY(),3)3个月后EOMONTH=EOMONTH(日期,月数)指定日期前后N个月的月末=EOMONTH(TODAY(),0)本月末实战示例：场景1：计算3个月后的转正日期

公式：=EDATE(A2,3)场景2：计算本月最后一天

公式：=EOMONTH(TODAY(),0)场景3：计算某月有多少天

公式：=DAY(EOMONTH(A1,0))6.5TEXT函数格式化日期语法：=TEXT(数值,"格式代码")格式代码效果示例yyyy-mm-dd2026-06-02标准日期yyyy年m月d日2026年6月2日中文日期yyyy/mm/dd2026/06/02斜杠分隔mmmmJune英文月份全称ddddTuesday英文星期全称aaaa星期二中文星期（需配合日期值）实战示例：场景：将日期统一为"2026年06月02日"格式

公式：=TEXT(A1,"yyyy年mm月dd日")七、文本处理函数7.1LEFT/RIGHT/MID截取文本函数语法说明示例LEFT=LEFT(文本,[字符数])从左侧截取=LEFT("ABCDEF",3)→"ABC"RIGHT=RIGHT(文本,[字符数])从右侧截取=RIGHT("ABCDEF",3)→"DEF"MID=MID(文本,起始位置,字符数)从中间截取=MID("ABCDEF",2,3)→"BCD"实战示例：场景1：从身份证号提取出生年份（前6位是地址，第7-10位是年份）

公式：=MID(A2,7,4)场景2：从邮箱提取用户名（@前面的部分）

公式：=LEFT(A2,FIND("@",A2)-1)场景3：提取手机号后4位

公式：=RIGHT(A2,4)7.2LEN/LENB计算文本长度函数说明示例LEN字符数（中文算1个）=LEN("Excel函数")→7LENB字节数（中文算2个）=LENB("Excel函数")→9实战示例：场景：判断手机号是否为11位

公式：=IF(LEN(A2)=11,"正确","位数错误")7.3FIND/SEARCH查找文本位置函数语法区别FIND=FIND(查找文本,原文本,[起始位置])区分大小写，不支持通配符SEARCH=SEARCH(查找文本,原文本,[起始位置])不区分大小写，支持通配符实战示例：场景：提取文件名（去掉路径）

公式：=RIGHT(A2,LEN(A2)-FIND("/",A2,FIND("/",A2,FIND("/",A2)+1)+1))

（假设路径格式为C:/Users/Name/file.xlsx）场景：判断邮箱格式是否包含@

公式：=IF(ISNUMBER(FIND("@",A2)),"格式正确","缺少@")7.4TEXT/VALUE格式转换函数语法说明TEXT=TEXT(数值,"格式")数值转文本VALUE=VALUE(文本)文本转数值实战示例：场景：将数字转为带千分位的文本

公式：=TEXT(A1,"#,##0")场景：将文本型数字转为可计算的数值

公式：=VALUE(A1)或--A1（双负号转换）7.5CONCAT/TEXTJOIN文本合并函数语法说明适用版本CONCAT=CONCAT(文本1,...)合并多个文本Excel2016+TEXTJOIN=TEXTJOIN(分隔符,忽略空值,文本1,...)用分隔符合并Excel2016+&=A1&B1连接符所有版本实战示例：场景1：合并姓名和职位（所有版本）

公式：=A2&"-"&B2场景2：合并多列（忽略空值）

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

结果："华东区、销售部、张三、10000"（空单元格自动跳过）7.6TRIM/CLEAN清理文本函数说明用途TRIM删除首尾空格，中间多余空格转为单个清理姓名、地址中的空格CLEAN删除非打印字符清理从网页/系统导出的数据实战示例：场景：清理导入数据中的多余空格

公式：=TRIM(A2)场景：同时清理空格和非打印字符

公式：=TRIM(CLEAN(A2))八、函数组合实战案例8.1案例一：智能考勤统计需求：根据上下班时间自动判断考勤状态员工上班时间下班时间考勤状态张三8:4518:10=IF(B2>TIME(9,0,0),"迟到",IF(C2<TIME(18,0,0),"早退","正常"))公式解析：TIME(9,0,0)生成9:00的时间值先判断是否迟到（上班时间>9:00）再判断是否早退（下班时间<18:00）否则显示"正常"8.2案例二：销售业绩自动评级需求：根据销售额和回款率综合评级销售额回款率综合评级1500095%=IF(AND(A2>=10000,B2>=0.9),"A级",IF(AND(A2>=8000,B2>=0.8),"B级","C级"))8.3案例三：跨表自动汇总需求：从"销售明细表"按月份汇总到"月度报表"销售明细表结构：A列=日期，B列=销售员，C列=金额月度报表公式：=SUMIFS(销售明细!C:C,销售明细!A:A,">="&DATE(2024,1,1),销售明细!A:A,"<="&EOMONTH(DATE(2024,1,1),0))8.4案例四：合同到期提醒需求：自动标记30天内到期的合同合同编号到期日期剩余天数提醒状态HT0012026-06-25=A2-TODAY()=IF(C2<=0,"已过期",IF(C2<=30,"即将到期("&C2&"天)","正常"))8.5案例五：从身份证号提取信息身份证号性别出生日期年IF(ISODD(MID(A2,17,1)),"男","女")=TEXT(MID(A2,7,8),"0000-00-00")=DATEDIF(C2,TODAY(),"Y")公式解析：性别：第17位奇数为男，偶数为女出生日期：第7-14位，用TEXT格式化年龄：用DATEDIF计算8.6案例六：动态查询面板需求：输入工号，自动显示员工全部信息查询工号1003姓名=XLOOKUP(B1,员工表!A:A,员工表!B:B,"未找到")部门=XLOOKUP(B1,员工表!A:A,员工表!C:C,"未找到")入职日期=XLOOKUP(B1,员工表!A:A,员工表!D:D,"未找到")工龄=DATEDIF(D2,TODAY(),"Y")&"年"九、常见错误与排查9.1错误代码速查表错误代码含义常见原因解决方法#DIV/0!除以零除数为0或空单元格用=IFERROR(公式,0)包裹#N/A找不到值VLOOKUP/XLOOKUP未匹配检查数据是否存在，用IFERROR处理#VALUE!值错误文本参与了数值运算检查数据类型，用VALUE转换#REF!引用错误删除了公式引用的单元格恢复数据或重新设置引用#NAME?名称错误函数名拼写错误检查拼写，如VL00KUP→VLOOKUP#NUM!数字错误数值超出范围检查数值大小#NULL!空值错误区域交集运算符使用错误检查逗号和空格的使用9.2错误处理函数函数语法用途IFERROR=IFERROR(公式,错误时返回值)公式出错时返回指定值IFNA=IFNA(公式,未找到时返回值)仅处理#N/A错误ISERROR=ISERROR(值)判断是否为错误（返回TRUE/FALSE）ISNA=ISNA(值)判断是否#N/A实战示例：场景：VLOOKUP找不到时显示"未录入"

公式：=IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),"未录入")场景：计算平均值，区域为空时显示"暂无数据"

公式：=IFERROR(AVERAGE(A2:A10),"暂无数据")9.3常见问题排查清单VLOOKUP返回#N/A查找值是否在查找区域的第一列？查找值前后是否有空格？（用TRIM清理）数据格式是否一致？（文本型数字vs数值型数字）是否使用了精确匹配（FALSE/0）？公式结果不对检查单元格格式是否为"文本"（应改为"常规"或"数值"）检查是否有循环引用检查公式中的绝对引用$是否正确公式不自动计算检查计算选项是否为"自动"（公式→计算选项→自动）检查单元格格式是否为"文本"十、练习题与答案10.1基础练习练习1：在B2:B10中有一组销售数据，请写出公式：求总和：=SUM(B2:B10)求平均：=AVERAGE(B2:B10)求最高：=MAX(B2:B10)求最低：=MIN(B2:B10)练习2：根据B2的成绩，写出评定公式（90分以上优秀，80-89良好，60-79及格，60以下不及格）：=IF(B2>=90,"优秀",IF(B2>=80,"良好",IF(B2>=60,"及格","不及格")))或使用IFS：=IFS(B2>=90,"优秀",B2>=80,"良好",B2>=60,"及格",B2<<60,"不及格")10.2条件统计练习练习3：A列是部门，B列是性别，C列是业绩。写出公式：统计"销售部"人数：=COUNTIF(A:A,"销售部")统计"销售部"男员工人数：=COUNTIFS(A:A,"销售部",B:B,"男")统计"销售部"总业绩：=SUMIF(A:A,"销售部",C:C)统计"销售部"男员工业绩：=SUMIFS(C:C,A:A,"销售部",B:B,"男")10.3查找练习练习4：Sheet2中A列是工号，B列是姓名，C列是部门。在Sheet1的A2输入工号，在B2显示姓名：VLOOKUP：=VLOOKUP(A2,Sheet2!$A:$C,2,0)XLOOKUP：=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B,"未找到")INDEX+MATCH：=INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))10.4日期练习练习5：A2是员工出生日期（如1990-05-20），写出公式：计算年龄：=DATEDIF(A2,TODAY(),"Y")计算出生年份：=YEAR(A2)