《Excel 常用函数公式及技巧搜集（常用）》

《Excel常用函数公式及技巧搜集（常用）》学生&职场新人版·一键复制即用适用人群：大学生（成绩分析、简历数据整理）、职场新人（销售跟进、消费预算、HR简单考勤）。使用建议：新建Excel文件，边看边复制公式到对应单元格，5分钟出结果！目录（快速导航）Excel函数速成基础（10分钟上手，避免90%错误）逻辑与条件函数（自动判断合格、评等级）查找与引用函数（自动拉取数据）文本处理函数（提取身份证、手机号）日期与时间函数（算年龄、倒计时）数学与统计函数（求和、排名、及格率）Excel365新函数与动态数组（一键筛选排序）实用技巧与效率工具（条件格式、数据验证）五大综合实战案例（成绩表、消费预算、求职跟踪、员工信息、简易库存）

附录：常见错误速查表一、Excel函数速成基础（必读，10分钟上手）为什么这个模块最重要？

很多学生和职场新人用Excel时，90%的挫败都来自“公式不生效”“拖动后全乱”“莫名其妙报错”。掌握本节，你就能像搭积木一样，稳稳地搭建后面的所有函数。现在就打开Excel，新建一个空白工作簿，和我一起动手练习吧！1.函数输入的“金科玉律”（30秒掌握）思考题：Excel怎么区分“你输入的是普通文字”还是“要它帮你计算的公式”？核心规则：所有公式必须以英文等号=开头。直接可复制练习（现在就试试）：在任意单元格输入下面内容，回车后观察结果：=1+2+3+4+5←显示15（计算成功）

1+2+3+4+5←只显示文字“1+2+3+4+5”（没有计算）实际场景应用（学生成绩表）：在G2单元格输入=B2+C2+D2+E2+F2→自动算出5门课总分。向下拖动填充（鼠标放在单元格右下角黑点，双击或拉动）。常见新人坑&解决方案：忘了=→显示一堆文字→直接在前面加=即可。输入中文逗号或引号→报错#NAME?→全部改成英文符号,"'2.公式中的常用运算符（像学英语单词一样记住它们）思考题：除了加减乘除，你还知道哪些“运算符”能在办公/学习中发挥大作用？算术运算符：+-*/^（乘方，例如=2^3结果8）连接运算符：&（把文字粘在一起）="张三"&"同学"&"的成绩是"&B2←显示“张三同学的成绩是85”比较运算符（后面IF函数会大量用到）：>>=<<==<>（不等于）实用练习（消费记录场景）：假设B2是实际花费，C2是预算，在D2输入：=B2-C2←正数表示超支，负数表示结余高阶小技巧：用括号改变优先级

=(B2+C2)*0.8（先算总分再打8折）3.单元格引用的三种方式（最容易出错的地方，务必掌握！）思考题：当你把公式从第2行拖到第50行时，为什么有些数字要“跟着变”，有些必须“固定不动”？使用F4键在四种引用间快速切换（选中公式中的地址，反复按F4）。引用方式写法示例拖动时变化典型使用场景（学生/新人）相对引用B2自动变（B3、B4...）每行独立计算总分、平均分绝对引用$B$2完全不变固定及格线60分、全班统一税率、预算标准混合引用（列固定）$B2行变，列不变成绩表中每列用同一门课的及格标准混合引用（行固定）B$2列变，行不变每列用第一行的权重系数直接可复制练习（成绩表场景）：在G2输入总分公式：=SUM(B2:F2)向下拖动→公式自动变成=SUM(B3:F3)（相对引用，完美）。如果想让所有行都参考第1行的“权重”（B1:F1），改成：=SUM(B2:F2*$B$1:$F$1)（必须用绝对引用固定权重行）新人常见错误：拖动后所有行显示同一个结果→忘记用相对引用或用了太多$。解决：选中公式，按F4切换，直到符合你的需求。4.名称管理器（让公式像读文章一样清晰）为什么推荐？学生写长公式时，A2:A100看起来很乱，用名称后一目了然。操作步骤（一步步来）：选中你的成绩数据区域（例如B2:F51）。公式选项卡→定义的名称→定义名称。输入名称“成绩数据”（不能有空格），确定。以后在任意单元格输入=SUM(成绩数据)即可求和。动态名称（进阶）：用=OFFSET(起始单元格,0,0,COUNTA(列),列数)创建会自动扩展的名称，适合不断添加新记录的消费表或求职表。5.公式审核工具（出错时救命神器）思考题：当公式报错时，你是手动检查还是让Excel帮你“画图”定位？显示公式：Ctrl+`（反引号，在Tab键上方）→所有公式显示出来，便于检查。追踪引用：公式选项卡→公式审核→追踪引用单元格（蓝色箭头指向公式依赖哪些格）。追踪从属：看这个单元格被哪些公式使用。错误检查：自动扫描整个工作表的问题。求值公式：逐步看复杂公式每一步的计算结果（超级实用！）。实战练习：故意在公式里写错一个地址，点击“追踪引用”，看箭头指向哪里。6.基础小测试（检验你是否真的上手）如何快速把一列公式批量输入？（选中区域→输入公式→Ctrl+Enter）如何把普通区域转为“智能表格”？（选中数据→Ctrl+T）——后面公式会自动扩展！公式显示#NAME?通常是什么原因？（函数名拼错或名称未定义）恭喜你！完成本节练习后，你已经具备了使用任何Excel函数的基础能力。花了不到10分钟，却避免了以后90%的低级错误。二、逻辑与条件函数（自动评级、判断合格）为什么这个类别超级实用？

学生看成绩单时，最想知道“及格吗？优秀吗？能不能拿奖学金？”；职场新人做消费记录或实习汇报时，最需要“超支了没？任务完成了没？”。逻辑函数就是你的“自动裁判”，帮你瞬间给出判断。现在就打开你的Excel，准备一张成绩表或消费表，和我一起动手练习吧！思考题：如果你要同时判断“语文≥60且数学≥60”，该怎么表达这个“且”的逻辑？（提示：后面AND函数会告诉你）1.IF函数（最基础的条件判断神器）语法：=IF(逻辑条件,条件为真时返回的值,条件为假时返回的值)参数详解（大白话）：逻辑条件：用>=<==等比较，例如B2>=60。真值/假值：可以是文字（“及格”）、数字、甚至另一个公式。典型案例1（成绩表——学生最常用）

假设B2是语文成绩，在G2输入：=IF(B2>=60,"及格","不及格")向下填充（双击右下角填充柄）。

效果：自动显示“及格”或“不及格”。典型案例2（消费记录——职场新人理财）

B列实际花费，C列预算，在D2输入：=IF(B2>C2,"⚠️超支了！","预算正常")小贴士：可以在条件格式中配合使用，让超支单元格自动变红（后面技巧模块会讲）。典型案例3（求职跟踪表）

E列投递日期，F列状态，在G2输入：=IF(E2<>"","已投递","待跟进")高阶嵌套技巧（多条件判断）

老版本常用多层IF：=IF(H2>=90,"优秀",IF(H2>=80,"良好",IF(H2>=70,"中等",IF(H2>=60,"及格","不及格"))))注意：最多嵌套7层，超过就乱。优化建议：用下面IFS函数替代！常见错误&解决方案：显示FALSE→忘记写假值，补上,"其他"。VALUE!→用中文引号或逗号→全部改英文""和,。拖动后全显示同一个结果→检查是否用了过多绝对引用$。2.IFS函数（Excel2016+推荐，多条件神器）语法：=IFS(条件1,值1,条件2,值2,...,TRUE,默认值)参数详解：最后用TRUE,默认值兜底，避免出错。典型案例1（成绩等级评定——最实用）

假设H2是调整后平均分：=IFS(H2>=90,"🏆优秀",H2>=80,"良好",H2>=70,"中等",H2>=60,"及格",TRUE,"需努力")典型案例2（消费分类）：=IFS(B2>2000,"奢侈消费",B2>1000,"中档消费",B2>500,"日常消费",TRUE,"省钱模式")典型案例3（求职阶段判断）：=IFS(F2="已offer","🎉准备入职",F2="已面试","跟进中",TRUE,"继续投递")高阶：结合AND使用更精准判断。常见坑：缺少兜底TRUE→可能返回#N/A→一定要加TRUE,"其他"。3.AND/OR/NOT函数（逻辑组合拳）AND（所有条件都满足才返回TRUE）

语法：=AND(条件1,条件2,...)案例1（奖学金判断）：=IF(AND(B2>=90,C2>=90,D2>=90),"可申请奖学金","暂不符合")案例2（消费）：=IF(AND(B2>C2,B2>1000),"严重超支","正常")OR（任一条件满足就TRUE）

语法：=OR(条件1,条件2,...)案例（有不及格就提醒）：=IF(OR(B2<60,C2<60,D2<60),"⚠️有挂科风险","全部通过")NOT（取反）=IF(NOT(B2>=60),"不及格","及格")XOR（异或，仅一个条件为真）——较少用，但逻辑课很有趣。4.IFERROR/IFNA（错误值美化，必备！）IFERROR（最常用）：

语法：=IFERROR(公式,出错时显示的值)典型案例1：查找失败时不显示#N/A=IFERROR(VLOOKUP(A2,信息表!A:B,2,0),"未找到该学生")典型案例2（成绩表）：=IFERROR(平均分公式,"数据不完整")IFNA（仅处理#N/A错误）：=IFNA(XLOOKUP(...),"暂无信息")高阶：嵌套在复杂公式最外层，让报表永远漂亮。5.SWITCH函数（Excel2016+，多选项判断清爽版）语法：=SWITCH(表达式,值1,结果1,值2,结果2,...,默认值)典型案例（成绩等级）：=SWITCH(TRUE,

H2>=90,"优秀",

H2>=80,"良好",

H2>=70,"中等",

TRUE,"需努力")求职状态案例：=SWITCH(F2,"已offer","准备入职","已面试","跟进","待面试","准备","其他")优势：比多层IF或IFS更清晰。本模块小结思考题：当你有5个等级要判断时，是用IFS还是SWITCH更舒服？为什么？如何把IF+AND组合用于“同时满足多门课优秀才奖励”？如果公式返回错误，你第一个想到的保护函数是哪个？练习建议：现在就在你的Excel里建一张成绩表，把上面所有公式都复制一遍测试。改改分数，看看结果如何变化——这就是“发现式学习”的乐趣！三、查找与引用函数（自动拉数据，省时神器）为什么这个类别值得重点掌握？

学生整理成绩单时，需要从“学生信息表”自动拉取专业、班级；职场新人做求职记录时，需要根据公司名称自动拉取HR电话、投递状态；做消费记录时，需要根据商品代码自动拉单价……手动复制太累了！查找引用函数就是你的“自动搬运工”。现在就准备两张Sheet（一张主表、一张信息表），和我一起练习吧！思考题：如果你有一张“学号-姓名-成绩”表和另一张“学号-专业-班级”表，该如何让Excel根据学号自动填专业？1.VLOOKUP函数（经典入门款，但有局限）语法：=VLOOKUP(查找值,表格区域,返回第几列,0)（最后一个参数0表示精确匹配，推荐永远用0）参数详解（大白话）：查找值：你要找的东西（例如学号）。表格区域：数据源，必须查找列在最左边。返回第几列：从左往右数第几列（包含查找列算1）。0：精确匹配（1或TRUE是模糊匹配，几乎不用）。典型案例1（成绩表——学生最常用）

Sheet1（主表）：A列学号，B列姓名

Sheet2（信息表）：A列学号，B列专业，C列班级在C2输入（主表）：=VLOOKUP(A2,信息表!$A:$C,2,0)效果：自动拉出专业。向下填充即可。典型案例2（求职跟踪）

根据公司名称拉HR电话：=VLOOKUP(A2,公司库!$A:$D,3,0)典型案例3（消费记录）

根据商品代码拉单价：=VLOOKUP(B2,商品库!$A:$E,4,0)高阶：模糊匹配（最后一个参数1）用于成绩区间评级，但新手慎用。常见错误&解决方案：N/A→查找值不存在或数据类型不一致（一个是文本一个是数字）→用TRIM()清理空格，或TEXT()转格式。REF!→返回列数超出区域→检查数字是否正确。数据排序后失效→因为VLOOKUP必须最左列查找。2.XLOOKUP函数（Excel365/2021+强烈推荐，神器升级版）语法：=XLOOKUP(查找值,查找区域,返回区域,[未找到时返回值])与VLOOKUP对比（重点！）：VLOOKUP只能向右查，XLOOKUP左右都能查。支持默认返回值，不再容易#N/A。更直观，不用数列数。典型案例1（成绩表）：反向查找（从右向左）=XLOOKUP(A2,信息表!$B:$B,信息表!$C:$C,"未找到")（根据姓名找班级）典型案例2（求职表）：=XLOOKUP(A2,公司库!$A:$A,公司库!$D:$D,"暂无HR电话")典型案例3（消费表）：多列返回（返回数组）=XLOOKUP(B2,商品库!$A:$A,商品库!$C:$E)（一次拉出单价、库存、供应商）高阶：结合IFERROR或直接用第4个参数兜底。3.INDEX+MATCH（万能组合，老版本最强）语法：=INDEX(返回区域,MATCH(查找值,查找区域,0))为什么强大？不受位置限制，可多条件。典型案例1（成绩表）：=INDEX(信息表!$B:$B,MATCH(A2,信息表!$A:$A,0))典型案例2（求职）：多条件（姓名+投递日期）

用两个MATCH结合（进阶）。典型案例3（消费）：动态列查找。高阶：INDEX+MATCH+IF做多条件查找。4.INDIRECT函数（动态引用神器）语法：=INDIRECT("Sheet名!单元格")典型案例1（多级下拉菜单）：省份→城市

在数据验证里用=INDIRECT(A2)（A2是省份名称，对应Sheet名）。典型案例2（动态汇总）：=SUM(INDIRECT(B2&"!C:C"))（B2是Sheet名称，自动汇总不同月的消费）常见坑：Sheet名变化或删除会导致#REF!→用名称管理器更稳。5.OFFSET函数（动态范围，适合图表）语法：=OFFSET(起始单元格,行偏移,列偏移,高度,宽度)典型案例（消费记录动态汇总）：=SUM(OFFSET(A1,1,1,COUNTA(A:A)-1,1))高阶：配合图表做自动扩展折线图（消费趋势）。6.其他实用函数HLOOKUP：横向查找（不常用）。FORMULATEXT：显示其他单元格的公式（教学/检查用）：=FORMULATEXT(G2)。本模块小结思考题：VLOOKUP和XLOOKUP你更推荐哪一个？为什么？当数据源在不同工作簿时，如何用INDIRECT？如何把查找函数和IF结合，实现“未找到时显示‘请补充信息’”？练习建议：现在就建两张Sheet，把VLOOKUP、XLOOKUP、INDEX+MATCH都试一遍。改一个学号，看看数据是否自动更新——这就是自动化的快乐！四、文本处理函数（身份证、手机号提取）为什么这个类别超级实用？

学生办理入学、社团登记时，常遇到一堆身份证号需要快速提取性别、出生日期、年龄；职场新人整理客户/简历信息时，需要隐藏手机号、清理多余空格、批量生成“尊敬的XXX同学，您好！”的邮件内容。文本函数就是你的“文字手术刀”，几秒钟搞定。现在就准备一张包含身份证号、手机号、姓名的表格，和我一起动手练习吧！思考题：中国18位身份证号中，第7-14位是出生日期，第17位是性别判断依据。你会如何用公式自动提取这些信息？1.LEFT/RIGHT/MID函数（精准截取文字）LEFT（从左边取）：=LEFT(文本,取几位)

RIGHT（从右边取）：=RIGHT(文本,取几位)

MID（从中间取）：=MID(文本,开始位置,取几位)典型案例1（身份证提取出生日期）

假设A2是身份证号（18位）：=MID(A2,7,8)←提取2000-01-01格式的日期

=TEXT(MID(A2,7,8),"0-00-00")←更好看的日期格式典型案例2（手机号隐藏中间四位）=LEFT(A2,3)&"****"&RIGHT(A2,4)效果：1381234（保护隐私）典型案例3（姓名拆分）

假设B2是“张三丰”，想提取姓：=LEFT(B2,1)←“张”

=RIGHT(B2,LEN(B2)-1)←“三丰”高阶：结合LEN动态计算长度。2.LEN/LENB函数（计算长度）LEN（字符数，中文算1）：=LEN(A2)

LENB（字节数，中文算2）：用于全角半角检查。典型案例1（身份证校验）：=IF(LEN(A2)=18,"身份证位数正确","请检查位数！")典型案例2（手机号校验）：=IF(LEN(A2)=11,"手机号格式正确","位数错误")3.FIND/SEARCH函数（查找位置）FIND（区分大小写）：=FIND("查找内容",文本,[开始位置])

SEARCH（不区分大小写，支持通配符*?）典型案例1（提取邮箱域名）：=MID(A2,FIND("@",A2)+1,LEN(A2))典型案例2（身份证籍贯粗判）：

前6位是地区码，可结合VLOOKUP或IF判断省份。4.SUBSTITUTE/REPLACE函数（替换文字）SUBSTITUTE（替换指定内容）：=SUBSTITUTE(文本,旧内容,新内容,[第几次])

REPLACE（按位置替换）：=REPLACE(文本,开始位置,替换几位,新内容)典型案例1（清理多余空格）：=SUBSTITUTE(A2,"","")←去掉所有空格

=TRIM(A2)←推荐！去掉首尾及多余空格典型案例2（银行卡/手机号分段显示）：=LEFT(A2,4)&""&MID(A2,5,4)&""&MID(A2,9,4)&""&RIGHT(A2,4)典型案例3（统一称呼）：=SUBSTITUTE(B2,"先生","同学")←适合学生群发邮件5.TEXT/VALUE函数（格式转换）TEXT（数字转文本并格式化）：=TEXT(MID(A2,7,8),"0000-00-00")←身份证出生日期美化VALUE（文本转数字）：=VALUE(LEFT(A2,4))←用于后续计算

**典型案例**（年龄计算结合日期函数）：

```excel

=DATEDIF(DATE(VALUE(MID(A2,7,4)),VALUE(MID(A2,11,2)),VALUE(MID(A2,13,2))),TODAY(),"Y")6.TEXTJOIN/CONCAT函数（批量连接文字）TEXTJOIN（推荐，有分隔符）：=TEXTJOIN(分隔符,忽略空值,范围)典型案例1（生成邮件正文）：="尊敬的"&B2&"同学：\n您的成绩为"&TEXTJOIN("、",TRUE,C2:F2)典型案例2（地址拼接）：=TEXTJOIN("",TRUE,省份,市,区,详细地址)CONCAT（简单连接，无分隔符）。7.身份证信息提取完整模板（最实用组合）假设A2是身份证号，在不同列输入以下公式：性别（B2）：=IF(MOD(MID(A2,17,1),2)=1,"男","女")出生年月（C2）：=TEXT(MID(A2,7,8),"0000-00-00")年龄（D2）：=DATEDIF(MID(A2,7,8),TODAY(),"Y")完整提取一行（可用TEXTJOIN汇总）：=B2&"，出生于"&C2&"，今年"&D2&"岁"高阶嵌套：把以上公式包在IFERROR里：=IFERROR(上面公式,"身份证格式错误")常见错误&解决方案：VALUE!→MID提取的是文本，用VALUE转数字后再计算。位数不对→先用LEN校验。日期显示为数字→单元格格式设为“日期”。拖动后失效→用绝对引用固定列：$A2。本模块小结思考题：提取身份证性别时，为什么用MOD函数判断奇偶？如何把手机号公式改成“只隐藏中间四位，但支持13位或15位号码”？文本函数和查找函数结合，能实现什么更强大的效果（例如根据姓名自动拉取其他信息）？练习建议：现在就在Excel里输入10条模拟身份证和手机号，把上面所有公式复制一遍。改一个号码，看看性别和年龄是否自动更新——这就是文本处理的强大之处！五、日期与时间函数为什么这个类别值得重点掌握？

今天是2026年5月21日，你的Excel能自动告诉你“还有多少天期末考试”“同学今年多少岁”“实习合同还有多久到期”吗？日期函数就是你的“时间管家”。现在就打开Excel，在A1输入=TODAY()试试，看看它自动显示当前日期——这就是自动化的起点！思考题：如果要计算“从出生日期到今天过了多少完整年”，该用哪个函数？（提示：DATEDIF是隐藏神器）1.TODAY/NOW函数（实时日期时间）TODAY（只返回日期）：=TODAY()

NOW（返回日期+时间）：=NOW()典型案例1（成绩表——自动年龄）：

在C2（出生日期）旁边输入：=TODAY()←当前日期，随时自动更新典型案例2（倒计时）：

期末考试日期在B2：=B2-TODAY()←剩余天数（格式为数字）典型案例3（求职跟踪）：=TODAY()-D2←已投递多少天（D2为投递日期）高阶：结合条件格式，让剩余天数<7时自动变红。常见坑：日期显示为数字→选中单元格→右键设置单元格格式→日期。2.DATE/YEAR/MONTH/DAY函数（构造与拆分日期）DATE（用年月日生成日期）：=DATE(年,月,日)

YEAR/MONTH/DAY（拆分）。典型案例1（生成标准出生日期）：

身份证提取的年月日（文本）转真实日期：=DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))典型案例2（自动生成考勤表头）：

在B1输入（向下/向右填充）：=DATE(2026,5,ROW()-1)←自动生成5月每天日期典型案例3（消费记录）：=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)←下个月1号3.DATEDIF函数（计算时间差，隐藏神器）语法：=DATEDIF(开始日期,结束日期,"单位")单位："Y"=完整年、"M"=完整月、"D"=天、"YM"=忽略年算月等。典型案例1（学生年龄计算）：=DATEDIF(C2,TODAY(),"Y")&"岁"&DATEDIF(C2,TODAY(),"YM")&"个月"典型案例2（职场新人实习工龄）：=DATEDIF(入职日期,TODAY(),"Y")&"年"典型案例3（合同/考试倒计时）：=DATEDIF(TODAY(),截止日期,"D")&"天后到期"高阶：结合IF做预警=IF(DATEDIF(TODAY(),B2,"D")<30,"⚠️即将到期！","正常")4.EDATE/EOMONTH函数（月份计算）EDATE（加减月）：=EDATE(起始日期,月数)

EOMONTH（月底）：=EOMONTH(起始日期,0)典型案例1（合同到期日）：=EDATE(签约日期,6)←6个月后到期典型案例2（自动生成月底还款日）：=EOMONTH(TODAY(),0)典型案例3（项目排期）：

每阶段加30天：=EDATE(上阶段结束,1)5.NETWORKDAYS/WORKDAY函数（工作日计算）NETWORKDAYS（两个日期之间的工作日数，自动排除周末）：=NETWORKDAYS(开始日期,结束日期,[节假日范围])WORKDAY（从某日起加工作日）：=WORKDAY(起始日期,天数,[节假日])典型案例1（成绩表——复习天数）：=NETWORKDAYS(TODAY(),考试日期)典型案例2（求职跟进）：=WORKDAY(投递日期,3)←3个工作日后提醒跟进典型案例3（消费/预算）：=NETWORKDAYS(EOMONTH(TODAY(),-1)+1,EOMONTH(TODAY(),0))6.WEEKDAY/WEEKNUM/TEXT函数（星期与格式化）WEEKDAY（星期几）：=WEEKDAY(日期,2)（1=周一）

TEXT（日期格式化）：=TEXT(日期,"yyyy年mm月dd日星期ddd")典型案例1（自动标周末）：=IF(OR(WEEKDAY(A2,2)=6,WEEKDAY(A2,2)=7),"周末","工作日")典型案例2（美化显示）：=TEXT(TODAY(),"yyyy年mm月dd日")&"星期"&TEXT(TODAY(),"ddd")典型案例3（考勤表头）：=TEXT(DATE(2026,5,1)+COLUMN()-2,"mm月dd日")&"\n"&TEXT(...,"ddd")高阶嵌套：把日期函数和逻辑函数结合做自动提醒。常见错误&解决方案：VALUE!→日期格式不对→用DATE函数转换。显示1900年→单元格格式设为“日期”。包含节假日→把节假日列成范围传给NETWORKDAYS。拖动后日期不变→用相对引用或TODAY()实时函数。本模块小结思考题：DATEDIF和直接用“结束日期-开始日期”有什么区别？什么时候必须用DATEDIF？如何把年龄计算公式改成“精确到月”？日期函数和条件格式结合，能实现什么自动高亮效果（例如7天内到期变红）？练习建议：现在就新建一张“个人信息表”，输入几个同学的出生日期，把年龄、剩余天数、合同到期等公式都复制一遍。改一个日期，看看所有结果是否自动更新——这就是时间智能化的乐趣！六、数学与统计函数为什么这个类别是表格的“灵魂”？

一张成绩表没有总分、平均分、排名，就只是“数据堆”；一张消费记录没有分类求和、超支统计，就只是“记账本”。这些函数能让你几秒钟获得决策信息。现在就打开你的成绩表或消费表，和我一起动手练习吧！思考题：当你想“只统计语文≥60分的学生总分”或“去掉最高最低分后的真实平均分”时，应该用哪个函数？1.SUM/SUMIF/SUMIFS（求和家族，最常用）SUM（简单求和）：=SUM(区域)SUMIF（单条件求和）：=SUMIF(条件区域,条件,求和区域)SUMIFS（多条件求和，推荐）：=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,...)典型案例1（成绩表）：全班语文总分=SUM(B2:B51)典型案例2（单条件）：只算及格的学生语文总分=SUMIF(B2:B51,">=60")典型案例3（多条件——销售/消费场景）：男生且数学≥90的总分=SUMIFS(B2:B51,性别列!$G$2:$G$51,"男",B2:B51,">=90")高阶：结合INDIRECT做动态表求和。常见坑：区域大小不一致→必须行列数匹配；#VALUE!→包含文本→用SUMIFS过滤。2.AVERAGE/AVERAGEIF/AVERAGEIFS（平均分神器）典型案例1（真实水平平均）：去最高最低后的平均（学生最爱）=(SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/3典型案例2（条件平均）：及格学生的平均分=AVERAGEIF(B2:B51,">=60")典型案例3（多条件）：=AVERAGEIFS(成绩列,性别列,"女",年级列,"大一")3.COUNT/COUNTA/COUNTIF/COUNTIFS（计数家族）COUNT（纯数字计数）

COUNTA（非空计数）

COUNTIF/COUNTIFS（条件计数）典型案例1（及格率）：=COUNTIF(B2:B51,">=60")/COUNTA(B2:B51)（格式设为百分比）典型案例2（优秀率）：=COUNTIFS(B2:B51,">=90")/COUNTA(B2:B51)典型案例3（消费分类计数）：超支笔数=COUNTIF(花费列,">预算列")4.MAX/MIN/LARGE/SMALL（极值与排名）MAX/MIN：最高最低分

LARGE/SMALL（第几大/小）：=LARGE(区域,2)（第二高分）典型案例1：全班最高分=MAX(B2:B51)典型案例2：去掉一个最高一个最低后的平均（稳健评分）=(SUM(B2:F2)-LARGE(B2:F2,1)-SMALL(B2:F2,1))/35.RANK函数（自动排名）RANK.EQ（并列排名，推荐）：=RANK.EQ(当前值,整个区域,0)（0=降序）典型案例1（成绩排名）：=RANK.EQ(H2,$H$2:$H$51,0)典型案例2（消费金额排名）：谁花钱最多=RANK.EQ(花费2,$花费列$2:$花费列$51,0)高阶：并列后连续排名用RANK.AVG。6.RAND/RANDBETWEEN（随机函数，抽奖/抽号神器）RAND（0-1随机小数）：=RAND()

RANDBETWEEN（整数随机）：=RANDBETWEEN(最小,最大)典型案例1（随机抽取5名同学）：=RANDBETWEEN(1,50)（配合SORT或手动刷新F9）典型案例2（随机成绩模拟练习）：=RANDBETWEEN(60,100)高阶：用RANDARRAY（365）生成不重复列表。7.ROUND/ROUNDUP/ROUNDDOWN/INT/MOD（四舍五入）ROUND（四舍五入到几位）：=ROUND(数字,2)

银行家舍入（偶数舍入）：用ROUND。典型案例1（消费保留两位小数）：=ROUND(B2,2)典型案例2（提成计算）：=ROUND(销售额*0.15,0)典型案例3（取整与余数）：=INT(总分)←取整数部分

=MOD(总分,10)←取余数（用于奇偶判断）8.SUBTOTAL/AGGREGATE（筛选后汇总，必备！）SUBTOTAL（兼容筛选）：=SUBTOTAL(9,区域)（9=求和）典型案例：筛选后只算可见行总分=SUBTOTAL(9,B2:B51)AGGREGATE（更强大，可忽略错误）：=AGGREGATE(1,6,区域)（1=平均，6=忽略隐藏行和错误）高阶：制作智能仪表盘。本模块小结思考题：SUMIFS和SUBTOTAL有什么区别？什么时候必须用SUBTOTAL？如何把排名公式改成“并列时显示相同名次且下一个跳过”？随机函数+条件格式，能做出什么有趣的抽奖效果？练习建议：现在就在你的成绩表里，把总分、去极值平均、及格率、排名、随机抽号公式全部复制一遍。筛选几行数据，看SUBTOTAL是否只算可见行——这就是智能统计的魅力！七、Excel365新函数与动态数组为什么这个模块是Excel未来的方向？

以前做成绩筛选要用筛选功能+手动复制，现在一个公式就能动态生成符合条件的清单，而且源数据一改，结果立刻更新！学生可以用它一键提取“优秀学生名单”；职场新人可以用它自动生成“本月超支记录”或“投递公司去重列表”。现在就确认你的Excel是365版本（文件→账户→查看产品信息），然后和我一起练习吧！思考题：当一个公式返回多个值时，Excel会自动“溢出”到下方/右侧单元格。如果下方有数据会怎样？（提示：出现#SPILL!错误）1.动态数组溢出原理（必须先懂）一个公式能返回“数组”（多行多列结果）。结果会自动溢出（Spill）到相邻空白单元格。数据源改变→结果自动刷新（无需拖动填充）。老方法（CSE数组公式Ctrl+Shift+Enter）已基本被取代。小测试：在空白区域输入=A1:A10回车，看看是否自动显示一整列。2.FILTER函数（最实用动态筛选神器）语法：=FILTER(数组,筛选条件,[未找到时返回值])典型案例1（成绩表——筛选优秀学生）

假设A2:F51是完整成绩表（A=姓名,F=平均分）：=FILTER(A2:F51,F2:F51>=90,"没有优秀学生")效果：自动溢出一整张只含90分以上学生的表格。典型案例2（消费记录）：筛选本月超支项目=FILTER(A2:D100,(B2:B100>TODAY()-30)*(C2:C100>D2:D100),"本月无超支")典型案例3（求职跟踪）：筛选“已面试但未offer”的公司=FILTER(A2:E50,(D2:D50="已面试")*(E2:E50<>"已offer"))高阶：多条件用*（AND）或+（OR）=FILTER(数据,(条件1)*(条件2)+(条件3))3.SORT/SORTBY函数（自动排序）SORT（按某列排序）：=SORT(数组,[排序索引],[排序方式],[是否按行])典型案例1（成绩排名）：按平均分降序=SORT(A2:F51,6,-1)←第6列（平均分）降序SORTBY（可按多列或不显示的列排序）：=SORTBY(A2:F51,F2:F51,-1,B2:B51,1)典型案例2（消费排序）：按金额降序且日期升序。4.UNIQUE函数（自动去重）语法：=UNIQUE(数组,[按列],[只返回唯一])典型案例1（求职公司去重）：=UNIQUE(A2:A100)典型案例2（成绩表——出现过的科目）：=UNIQUE(科目行)典型案例3（消费类别去重）：自动生成下拉选项来源。高阶：配合FILTER去重筛选结果=UNIQUE(FILTER(A2:A100,B2:B100>=90))5.SEQUENCE函数（生成连续序号/序列）语法：=SEQUENCE(行数,[列数],[起始值],[步长])典型案例1（自动生成序号）：=SEQUENCE(50)←1到50典型案例2（自动生成2026年5月日期）：=SEQUENCE(31,1,DATE(2026,5,1),1)再用TEXT美化格式。典型案例3（考勤打分表）：生成1-100分选项。6.RANDARRAY函数（随机数组）语法：=RANDARRAY(行数,列数,[最小],[最大],[整数?])典型案例1（随机抽号不重复）：=SORTBY(A2:A51,RANDARRAY(50))典型案例2（模拟考试成绩）：=RANDBETWEEN(60,100)（老版）或RANDARRAY版

**高阶**：=UNIQUE(RANDARRAY(10,1,1,50,TRUE))生成不重复随机数。

####7.XMATCH（XLOOKUP的兄弟）

```excel

=XMATCH(查找值,查找区域,[匹配模式])支持通配符、更灵活的查找。本模块小结思考题：FILTER和传统筛选功能有什么本质区别？当出现#SPILL!错误时，最常见的解决办法是什么？（清空下方单元格）如何把FILTER+SORT+UNIQUE组合成“一键生成优秀学生排行榜”？练习建议：在成绩表下方空白处输入FILTER公式筛选优秀学生。改动任意一个分数，看结果是否自动更新。尝试SEQUENCE+TEXT生成一张自动更新的5月考勤日历。八、实用技巧为什么这个模块能直接提升你的职场/学习竞争力？

学会函数只是“会算”，学会这些技巧才是“会用”。学生可以用它们做出漂亮的成绩分析报告；职场新人可以用它们快速完成实习周报、消费记录、求职跟踪表。现在就打开你的Excel，和我一起动手操作吧！思考题：当你想让“不及格成绩自动变红”“超支金额自动提醒”，或者“表格新增一行后公式自动扩展”时，应该用什么技巧？1.快捷键与操作技巧（节省80%时间）核心快捷键（建议打印贴在桌边）：Ctrl+Enter：选中多个单元格，输入公式后一次性全部填充。F4：切换相对/绝对引用（写公式时反复按）。Ctrl+`（反引号）：显示所有公式，便于检查。双击填充柄：公式自动向下填充到数据末尾。Ctrl+T：将数据区域转为智能表格（后面会详讲）。Alt+=：快速在下方插入求和公式。F9：刷新随机函数（RAND/RANDBETWEEN）结果。典型案例（成绩表）：

选中G2:G51→输入=SUM(B2:F2)→Ctrl+Enter→全部自动算出总分。小练习：现在选中一列空单元格，输入一个IF公式，然后Ctrl+Enter，看效果。2.条件格式与函数结合（自动高亮，神奇！）操作路径：选中区域→开始选项卡→条件格式→新建规则→使用公式确定要设置格式的单元格。典型案例1（成绩表——不及格变红）：

选中成绩区域→新建规则→输入公式：=B2<60设置字体红色+填充浅红。典型案例2（消费记录——超支警告）：

公式：=B2>C2设置黄色填充+加粗。典型案例3（求职跟踪——跟进倒计时）：

剩余天数<7天变橙色：=DATEDIF(TODAY(),截止日期,"D")<7高阶：用公式做数据条、色阶、图标集，让排名自动显示小旗子。常见坑：公式用错相对引用→选中第一个单元格写公式，其他格会自动相对调整。3.数据验证与函数结合（防止输入错误）操作路径：选中区域→数据选项卡→数据验证。典型案例1（限制成绩输入0-100）：

数据验证→设置→整数→最小0，最大100。典型案例2（二级下拉菜单——省份→城市）：

用INDIRECT实现：

在“城市”列数据验证来源输入：=INDIRECT(A2)（A2是省份名称，对应Sheet名）。典型案例3（求职表状态下拉）：来源输入：

已投递,已面试,已offer,未通过高阶：结合COUNTIF防止重复输入学号/手机号。4.图表与函数结合（动态图表）推荐方法：使用表格（Ctrl+T）或OFFSET/INDEX做动态数据源。典型案例1（成绩柱状图自动更新）：全选数据→Ctrl+T转为表格。插入柱状图。新增一行数据后，图表自动扩展。典型案例2（消费趋势折线图）：

用FILTER生成动态数据源，再插入图表。典型案例3（排名饼图）：用UNIQUE+SUMIFS生成数据源。5.常见错误排查与处理（救命技巧）错误代码常见原因快速解决办法#N/A查找不到数据用IFNA或XLOOKUP第4参数兜底#DIV/0!除以0（如分母为空）用IFERROR(公式,0)或IF(分母<>0,...)#VALUE!数据类型不匹配（文本+数字）用VALUE()转换或TRIM清理#REF!引用了已删除的单元格检查公式区域，重新引用#NAME?函数名拼错或引号错误检查英文拼写和""#SPILL!动态数组溢出位置被占用清空下方/右侧单元格通用保护公式：=IFERROR(你的公式,"数据有误")6.表格化引用（Ctrl+T智能表格，必学！）操作：选中数据区域→Ctrl+T→确定。优势：公式自动扩展（新增一行自动计算）。使用结构化引用（如=SUM(成绩表[语文])），可读性强。图表、数据验证、条件格式全部自动扩展。典型案例（成绩表）：

转为表格后，在总分列输入=[@语文]+[@数学]+...→新增学生自动计算。本模块小结思考题：条件格式的公式和普通单元格公式有什么区别？Ctrl+T智能表格和普通区域相比，最大优势是什么？如何把条件格式+FILTER组合成“动态仪表盘”？练习建议：拿你的成绩表或消费表做一次条件格式高亮。转为智能表格，新增一行数据，观察公式是否自动填充。设置一个二级下拉菜单，测试是否好用。九、五大综合实战案例（核心价值）使用建议：每个案例新建一个Sheet，按表格结构输入标题和样例数据，然后逐个复制公式。完成后试着改1-2行数据，看结果是否自动更新。案例1：成绩统计分析表（学生最推荐）场景：期末考试后，快速生成全班总分、真实平均分（去最高最低）、等级、排名、及格率、优秀率，并自动高亮。表格结构（A1开始）：A：姓名B-F：语文、数学、英语、物理、化学G：总分H：真实平均分I：等级J：班级排名核心公式（从第2行开始）：G2:=SUM(B2:F2)//总分

H2:=(SUM(B2:F2)-LARGE(B2:F2,1)-SMALL(B2:F2,1))/3//真实平均分（去最高最低）

I2:=IFS(H2>=90,"🏆优秀",H2>=80,"良好",H2>=70,"中等",H2>=60,"及格",TRUE,"需努力")

J2:=RANK.EQ(H2,$H$2:$H$51,0)//排名下方汇总区（第53行）：B53:=COUNTIF(B2:B51,">=60")/COUNTA(B2:B51)//语文及格率（设为百分比）动态优秀名单（用365新函数，放在另一区域）：=FILTER(A2:J51,H2:H51>=90,"暂无优秀学生")美化：转为智能表格（Ctrl+T），条件格式高亮H列<60为红色。改动提示：增加科目只需改SUM和LARGE范围即可。案例2：月度消费预算与理财记录表（职场新人理财入门）场景：记录日常消费，自动分类、统计超支、生成饼图。表格结构：A：日期B：类别（餐饮、交通、学习等）C：金额D：预算E：是否超支F：备注核心公式：E2:=IF(C2>D2,"⚠️超支","正常")

下方汇总：

总支出:=SUM(C2:C100)

餐饮类:=SUMIFS(C2:C100,B2:B100,"餐饮")

超支次数:=COUNTIF(E2:E100,"⚠️超支")动态分类清单：=UNIQUE(B2:B100)//自动去重类别

=SORT(FILTER(A2:C100,C2:C100>D2:D100))//超支明细图表：转为表格后插入饼图，自动更新。高阶：用TEXTJOIN生成月度总结邮件内容。案例3：求职投递跟踪表（职场新人求职必备）场景：记录投递的公司，自动计算跟进天数、状态判断、优秀公司筛选。表格结构：A：公司名称B：投递日期C：状态D：HR联系方式E：跟进天数F：下次跟进日期核心公式：D2:=XLOOKUP(A2,公司库!A:A,公司库!B:B,"暂无")//自动拉HR电话

E2:=TODAY()-B2//已跟进天数

F2:=WORKDAY(B2,5)//建议5个工作日后跟进

C2:=IFS(E2>14,"需重点跟进",E2>7,"跟进中",TRUE,"正常")动态看板：=FILTER(A2:F100,(E2:E100>7)*(C2:C100<>"已offer"))

=UNIQUE(FILTER(A2:A100,C2:C100="已offer"))//已拿offer公司美化：条件格式——跟进天数>10变橙色。案例4：个人/同学信息管理表（身份证提取全套）场景：登记同学或求职者信息，自动提取性别、年龄、籍贯提示、合同/毕业倒计时。表格结构：A：姓名B：身份证号C：性别D：出生日期E：年龄F：手机（隐藏中间）G：状态提醒核心公式：C2:=IF(MOD(MID(B2,17,1),2)=1,"男","女")

D2:=TEXT(MID(B2,7,8),"0000-00-00")

E2:=DATEDIF(D2,TODA