Excel最常用30个函数

一、基础计算函数（5个）。

这类函数是Excel的“基本功”，日常求和、算平均、统计数量全靠它们，难度极低，记熟语法就能直接用。

1. SUM（求和）

核心作用：快速计算一组数值的总和，是最常用的函数没有之一。

语法：=SUM(数值1, [数值2], ...)

实操示例：

=SUM(A2:A10)    // 计算A2到A10单元格所有数字的和（比如月度总支出）
=SUM(A2, A5, A8) // 对离散单元格求和（比如3个项目的费用合计）

实际场景：计算月度总销售额、部门总开支、学生总分。

避坑点：会自动忽略文本、逻辑值（比如“未填写”“TRUE”），只计算纯数字。

2. AVERAGE（求平均）

核心作用：计算一组数值的平均值，适合统计均值类数据。

语法：=AVERAGE(数值1, [数值2], ...)

实操示例：=AVERAGE(B2:B20) // 计算B2到B20区域的平均分（比如学生成绩）

实际场景：统计员工平均月薪、班级平均成绩、产品平均销量。

避坑点：和SUM一样，忽略非数值单元格（比如空值、文本），不会把文本当0计算。

难度：★☆☆☆☆

3. MAX / MIN（求最大/最小值）

核心作用：快速筛选出一组数值中的最大值或最小值，不用手动对比。

语法：=MAX(数值范围) / =MIN(数值范围)

实操示例：

=MAX(C2:C100) // 找出C列的最高分、最高销售额
=MIN(C2:C100) // 找出C列的最低分、最低成本

实际场景：找出业绩TOP1的员工、比赛中的最低分、产品的最高定价。

避坑点：如果数值范围中包含0，MIN可能会返回0（比如统计最低销售额时，注意排除空单的0值）。

难度：★☆☆☆☆

4. COUNT（统计数字个数）

核心作用：统计指定范围中，包含数字的单元格个数（只认数字）。

语法：=COUNT(数值范围)

实操示例：=COUNT(D2:D200) // 统计D列有多少个单元格填了数字（比如有效订单数）

实际场景：统计有效成绩单数量、已录入的客户数、非空的数值数据。

避坑点：只统计数字，文本、空值、逻辑值都不算；如果想统计所有非空单元格（包括文本），用COUNTA函数。

难度：★☆☆☆☆

5. COUNTIF（条件统计个数）

核心作用：计算满足“单个条件”的单元格个数，比COUNT更灵活。

语法：=COUNTIF(统计范围, 条件)

实操示例：=COUNTIF(E2:E500, ">60") // 统计E列分数大于60的人数（及格人数）

实际场景：统计及格人数、某个部门的员工数、某产品的出现次数、销售额大于1000的订单数。

避坑点：条件必须用英文引号括起来，比如 "销售部"、">=100"；模糊匹配可用通配符*（比如"张*"统计所有姓张的人）。

难度：★★☆☆☆

二、核心逻辑与匹配函数（10个）—— 高效处理数据的核心

这类函数是Excel的“灵魂”，能实现条件判断、多维度求和、数据查找，解决80%的复杂数据处理场景，职场人必吃透！

6. IF（条件判断）

核心作用：根据一个条件判断，返回两种不同的结果，简单又实用。

语法：=IF(判断条件, 条件成立时返回的值, 条件不成立时返回的值)

实操示例：=IF(F2>=60, "及格", "不及格") // 判断F2单元格分数是否及格

实际场景：绩效评级（比如“>=90为优秀，否则为合格”）、数据筛选标记（比如“销售额>=5000标记为重点客户”）。

避坑点：可以多层嵌套（比如IF嵌套IF），但建议不超过3层，否则公式会变复杂，可用后面的IFS函数替代。

难度：★★☆☆☆

7. IFS（多条件判断）

核心作用：检查多个条件，返回第一个为TRUE的条件对应的值，替代多层IF嵌套，更简洁。

语法：=IFS(条件1, 值1, 条件2, 值2, ...)

实操示例：

=IFS(G2>=90, "优", G2>=80, "良", G2>=60, "中", TRUE, "差")
// 多级绩效评级，满足一个条件就返回对应结果

实际场景：多级评分、区间判断（比如“销售额分级”“成绩分级”），比嵌套IF更清晰，不易出错。

避坑点：条件要按“从高到低”排序（比如先判断>=90，再判断>=80）；最后一个条件可用TRUE作为“兜底”（比如所有条件都不满足时，返回“差”）。

难度：★★☆☆☆

8. SUMIF（条件求和）

核心作用：对满足“单个条件”的单元格求和，比SUM更精准。

语法：=SUMIF(条件判断范围, 条件, [求和范围])

实操示例：=SUMIF(A:A, "张三", C:C) // 在A列找“张三”，并累加其对应的C列数值（张三的总业绩）

实际场景：计算某个销售员的业绩总和、某个产品的总销量、某个部门的总开支。

避坑点：如果省略“求和范围”，则对“条件判断范围”本身求和（比如=SUMIF(A:A, "张三")，就是求A列中“张三”对应的数字和）。

难度：★★☆☆☆

9. SUMIFS（多条件求和）

核心作用：对满足“多个条件”的单元格求和，是数据分析的核心函数，职场高频使用！

语法：=SUMIFS(求和范围, 条件范围1, 条件1, [条件范围2, 条件2], ...)

实操示例：=SUMIFS(销售额, 销售员, "张三", 产品, "A", 月份, ">2023-6") // 求张三6月后销售A产品的总业绩

实际场景：多维度数据汇总（比如“某部门、某月份、某产品的总销售额”）、精准统计目标数据。

避坑点：重点！求和范围在第一个参数，顺序和SUMIF相反，别记混；多个条件是“同时满足”（逻辑与）。

难度：★★★☆☆

10. VLOOKUP（垂直查找）

核心作用：垂直查找——在表格的首列查找指定的值，返回该行指定列的值，使用频率极高！

语法：=VLOOKUP(要找谁, 在哪找, 返回第几列, 精确找[FALSE]还是近似找[TRUE])

实操示例：=VLOOKUP(H2, $A$2:$D$100, 4, FALSE) // 按H2的工号，在A2:D100中找对应行，返回第4列的姓名

实际场景：根据工号查姓名、根据产品编号查价格、根据身份证号查信息，是日常数据查询的“神器”。

避坑点：① 查找值必须在查找区域的第一列（比如查姓名，工号必须在查找区域的第一列）；② 推荐用FALSE（精确匹配），避免近似匹配出错；③ 查找区域建议用$锁定（比如$A$2:$D$100），防止下拉公式时区域偏移。

难度：★★★☆☆

11. XLOOKUP（VLOOKUP升级版）

核心作用：VLOOKUP的现代替代品，功能更强大、更灵活，解决VLOOKUP的所有痛点。

语法：=XLOOKUP(查找值, 查找数组, 返回数组, [未找到时], [匹配模式], [搜索模式])

实操示例：=XLOOKUP(I2, 工号列, 姓名列) // 按工号查姓名，比VLOOKUP更简单

实际场景：替代VLOOKUP，支持反向查找（比如从右往左查）、多条件查找，不用纠结列顺序。

避坑点：需要Excel 365/2021及以上版本支持；无需指定列序号，查找列和返回列可以是任意位置，如果版本支持，请优先使用。

难度：★★★☆☆

12. INDEX（定位单元格值）

核心作用：返回给定区域中，特定行和列交叉处单元格的值（相当于“定位坐标”）。

语法：=INDEX(区域, 行号, [列号])

实操示例：=INDEX(A1:F100, 5, 3) // 返回A1:F100区域中第5行第3列（C5）的值

实际场景：单独使用较少，通常和MATCH函数组合，实现更灵活的查找（克服VLOOKUP的限制）。

难度：★★★☆☆

13. MATCH（查找位置）

核心作用：在指定区域中查找指定项，返回该项的“相对位置”（第几个），而不是值本身。

语法：=MATCH(查找值, 查找区域, [匹配类型])

实操示例：=MATCH("苹果", B1:B10, 0) // 在B1:B10中精确查找“苹果”，返回其位置（比如第3行就返回3）

实际场景：为INDEX函数提供行号/列号，实现精准定位；也可单独用于查找某个值的位置。

避坑点：匹配类型中，0为精确匹配（最常用），1为近似匹配（需排序），-1为反向近似匹配。

难度：★★★☆☆

14. INDEX + MATCH 组合（万能查找）

核心作用：Excel查找的“天花板”，实现任意方向的精确查找，克服VLOOKUP只能从左向右查的限制。

语法：=INDEX(返回区域, MATCH(找谁, 行查找区域, 0), MATCH(找哪列, 列查找区域, 0))

实操示例：

=INDEX($C$2:$G$100, MATCH(H2, $A$2:$A$100, 0), MATCH(I2, $C$1:$G$1, 0))
// 按H2的姓名、I2的月份，查找对应业绩

实际场景：二维表查询（比如查某人在某月的业绩、某产品在某区域的销量），功能比VLOOKUP更强大、更灵活，是进阶必备组合。

避坑点：两个MATCH函数分别对应“行位置”和“列位置”，查找区域建议锁定，避免公式偏移。

难度：★★★★☆

15. FILTER（动态筛选）

核心作用：根据条件，动态筛选出一个区域或数组，结果会自动“溢出”到相邻单元格，不用手动筛选。

语法：=FILTER(要筛选的区域, 筛选条件, [无结果时返回的值])

实操示例：

=FILTER(A2:D100, (C2:C100="销售部")*(D2:D100>10000))
// 筛选出销售部、销售额大于10000的所有记录

实际场景：动态提取满足多个条件的记录（比如“销售部+3月份+销售额>5000的订单”），数据更新后，筛选结果会自动同步。

避坑点：动态数组函数，需Excel 365/2021及以上版本；多个条件用*连接（表示“同时满足”），用+连接（表示“满足一个即可”）。

难度：★★★★☆

三、文本与日期处理函数（8个）—— 数据清洗必备

日常处理数据时，难免遇到文本混乱、日期格式不统一的问题，这类函数能快速清洗数据，让数据更规范。

16. LEFT / RIGHT / MID（提取文本）

核心作用：从文本中提取指定位置的字符，Left从左取、Right从右取、MID从中间取，灵活应对各种文本提取场景。

语法：

• =LEFT(文本, [字符数]) // 从左提取指定个数的字符
• =RIGHT(文本, [字符数]) // 从右提取指定个数的字符
• =MID(文本, 开始位置, 字符数) // 从指定位置开始，提取指定个数的字符

实操示例：

=LEFT(J2, 4)     // 提取J2前4位（比如身份证的年份：1998XXXX→1998）
=MID(J2, 6, 2)   // 从J2第6位取2位（比如日期2023-10-05→10）

实际场景：分离地址（比如“北京市朝阳区XXX”提取“北京市”）、提取身份证出生日期、拆分姓名和工号。

避坑点：每个中文字符、英文字符、数字，都算一个字符（比如“你好123”，Left取2位就是“你好”）。

难度：★★☆☆☆

17. TEXT（格式转换）

核心作用：将数值、日期，转换为指定格式的文本（比如把数字改成货币格式、把日期改成“年月日”格式）。

语法：=TEXT(数值, "格式代码")

实操示例：

=TEXT(K2, "¥#,##0.00")          // 格式化为货币，如“¥12,345.67”
=TEXT(TODAY(), "yyyy年mm月dd日") // 格式化今天日期，如“2024年05月20日”

实际场景：规范数据展示格式（比如财务报表的货币格式）、将日期和数字拼接进报告（比如“报告生成于2024年05月20日”）。

避坑点：转换后结果是“文本格式”，无法直接用于计算（比如把123改成“¥123.00”后，不能再用SUM求和）。

难度：★★★☆☆

18. CONCAT（合并文本）

核心作用：将多个文本项、单元格内容，合并为一个文本项，简单高效。

语法：=CONCAT(文本1, [文本2], ...)

实操示例：=CONCAT(L2, "的", M2, "成绩是", N2, "分") // 合并为“张三的数学成绩是90分”

实际场景：生成报告摘要、合并多列信息（比如合并姓名+工号+部门）、拼接文本备注。

避坑点：也可用 & 符号连接，更灵活（比如L2 & "的" & M2 & "成绩是" & N2 & "分"），两种方法都能实现合并。

难度：★☆☆☆☆

19. TRIM（清理空格）

核心作用：删除文本中除“单词间单空格”外的所有空格（比如开头、结尾的空格，多个连续空格），数据清洗必备。

语法：=TRIM(文本)

实操示例：=TRIM(O2) // 清理O2单元格中多余的空格（比如“ 张三 ”→“张三”）

实际场景：清理从系统导出的、含不规则空格的数据（比如导出的客户姓名、产品名称，常带多余空格），避免VLOOKUP匹配失败。

避坑点：不会删除单词之间的单空格（比如“张三 李四”，清理后还是“张三 李四”），只清理多余空格。

难度：★☆☆☆☆

20. DATE（组合日期）

核心作用：将独立的年、月、日数值，组合成一个有效的日期格式（比如把“2024”“5”“20”组合成“2024-05-20”）。

语法：=DATE(年, 月, 日)

实操示例：=DATE(P2, Q2, R2) // 将P列（年）、Q列（月）、R列（日）的数值组合成日期

实际场景：将分开记录的年月日数据标准化（比如表格中分别记录年、月、日，组合后方便排序、计算）。

难度：★★☆☆☆

21. DATEDIF（计算日期差）

核心作用：计算两个日期之间的天数、月数或年数，隐藏函数但超实用！

语法：=DATEDIF(开始日期, 结束日期, "单位代码")

实操示例：

=DATEDIF(S2, TODAY(), "Y") // 计算年龄（从S2的出生日期到今天的整年数）
=DATEDIF(T2, U2, "M")      // 计算相隔整月数（比如合同开始到结束的月数）

实际场景：计算年龄、工龄、合同剩余天数、项目周期（月数/天数）。

避坑点：① 此函数在Excel函数列表中找不到，但可直接输入使用；② 单位代码常用 "Y"（年）、"M"（月）、"D"（天），还有 "YM"（忽略年，算月差）、"YD"（忽略年，算天差）。

难度：★★★☆☆

22. YEAR / MONTH / DAY（提取日期部分）

核心作用：从一个完整的日期中，分别提取年、月、日部分，方便按时间分组汇总。

语法：=YEAR(日期) / =MONTH(日期) / =DAY(日期)

实操示例：=YEAR(V2) // 提取V2日期的年份（比如2024-05-20→2024）

实际场景：按年份、月份汇总数据（比如“2024年各月份销售额”“各年份员工入职人数”）。

难度：★☆☆☆☆

23. TODAY / NOW（获取当前日期/时间）

核心作用：自动返回当前日期（TODAY）或当前日期+时间（NOW），无需手动输入，自动更新。

语法：=TODAY() / =NOW()

实操示例：

=TODAY()                // 返回今天日期（比如2024-05-20）
="报告生成于：" & TEXT(NOW(), "yyyy-mm-dd hh:mm") // 生成带时间的报告标题

实际场景：制作自动更新日期的表格标题、计算“距离今天还有多少天”（比如合同到期倒计时）。

避坑点：属于“易失性函数”，每次打开表格、重算公式时，会自动更新为当前日期/时间，无法保留历史记录。

难度：★☆☆☆☆

四、进阶计算分析函数（7个）—— 深度数据处理

这类函数适合需要深度分析数据的场景（比如财务分析、业绩排名、动态排序），学会后能轻松搞定复杂数据任务，提升专业度。

24. RANK.EQ（排名）

核心作用：返回一个数字在数字列表中的排位（比如成绩排名、业绩排名），相同数值排名相同。

语法：=RANK.EQ(要排位的数字, 排位区域, [排序方式])

实操示例：=RANK.EQ(W2, $W$2:$W$100, 0) // 降序排列，计算W2在列中的名次（1为最高）

实际场景：员工业绩排名、学生成绩排名、产品销量排名。

避坑点：0表示降序（从高到低），非0表示升序（从低到高）；相同数值会占用相同排名（比如两个90分，都排第1，下一个89分排第3）。

难度：★★☆☆☆

25. SUBTOTAL（可见单元格汇总）

核心作用：对“可见单元格”进行分类汇总（求和、平均、计数等），忽略被筛选或隐藏的行，动态更新。

语法：=SUBTOTAL(功能代码, 区域1, [区域2], ...)

实操示例：=SUBTOTAL(109, X2:X1000) // 对X列可见单元格求和（筛选后，只汇总显示的数据）

实际场景：筛选数据后，快速汇总可见数据（比如筛选“销售部”后，汇总销售部的总业绩）；隐藏部分行后，只计算显示的数值。

避坑点：常用功能代码（记熟这几个就够了）：109（求和）、101（平均）、103（计数）、104（最大值）、105（最小值）；代码前加“10”，表示忽略隐藏行。

难度：★★★☆☆

26. ROUND / ROUNDUP / ROUNDDOWN（四舍五入）

核心作用：将数字按指定小数位数舍入，ROUND四舍五入、ROUNDUP向上舍入、ROUNDDOWN向下舍入，按需选择。

语法：=ROUND(数字, 小数位数)（ROUNDUP、ROUNDDOWN语法相同）

实操示例：

=ROUND(Y2, 2)        // 四舍五入保留两位小数（3.141→3.14）
=ROUNDUP(3.141, 2)   // 向上舍入保留两位小数（3.141→3.15）
=ROUNDDOWN(9.99, 0)  // 向下舍入保留0位小数（9.99→9）

实际场景：财务计算（比如金额保留两位小数）、规范数据精度（比如百分比保留一位小数）。

避坑点：小数位数为负数时，表示对小数点前的数字舍入（比如ROUND(1234, -2)→1200）。

难度：★★☆☆☆

27. NPV（净现值计算）

核心作用：基于一系列未来的现金流和贴现率，计算一项投资的净现值（判断投资是否可行），财务分析常用。

语法：=NPV(贴现率, 现金流1, [现金流2], ...)

实操示例：=NPV(0.1, B2, B3, B4, B5) // 假设贴现率10%，计算4期现金流的净现值

实际场景：项目投资评估、财务可行性分析（净现值为正，说明投资可行）。

避坑点：现金流须按时间顺序排列（第一期、第二期……），且假定发生在各期期末；贴现率用小数表示（比如10%写成0.1）。

难度：★★★★☆

28. NPER（期数计算）

核心作用：基于固定利率和等额分期付款，计算某项投资或贷款的期数（比如还清贷款需要多少个月）。

语法：=NPER(利率, 每期付款额, 现值, [未来值], [类型])

实操示例：=NPER(0.05/12, -2000, 100000) // 月利率5%÷12，每月还款2000元，贷款10万需还的月数

实际场景：计算还清贷款所需的月数、达到储蓄目标所需的期数（比如每月存5000，存够10万需要多久）。

避坑点：支出（比如还款、存款）通常用负数表示（比如每月还款2000，写成-2000）；利率要和期数对应（月还款用月利率，年还款用年利率）。

难度：★★★★☆

29. UNIQUE（提取唯一值）

核心作用：快速提取指定区域中的唯一值，去除重复项，生成不重复的列表。

语法：=UNIQUE(区域, [按列/行], [仅出现一次])

实操示例：=UNIQUE(Z2:Z1000) // 提取Z列所有不重复的客户名称、部门名称

实际场景：快速生成不重复的项目清单、部门列表、客户名单，避免重复统计。

避坑点：动态数组函数，需Excel 365/2021及以上版本；结果会自动溢出到相邻单元格，无需手动下拉。

难度：★★★☆☆

30. SORT（动态排序）

核心作用：对某个区域或数组的内容进行排序，生成一个新的排序视图，不改变原数据的顺序。

语法：=SORT(要排序的区域, [排序列], [升序1降序-1], [按行排序])

实操示例：=SORT(A2:F100, 3, -1) // 对A2:F100区域，按第3列（比如销售额）降序排序

实际场景：动态生成排序后的报表（比如“按销售额降序排列的产品清单”），原数据不变，方便对比查看；可与FILTER、UNIQUE组合使用（比如先筛选，再排序）。

避坑点：动态数组函数，结果自动溢出；1表示升序，-1表示降序，默认升序。

难度：★★★☆☆

快速检索指南

不用翻全文，按需求快速找函数：

• 求和/平均/统计：SUM、AVERAGE、MAX/MIN、COUNT、COUNTIF
• 条件判断/求和：IF、IFS、SUMIF、SUMIFS
• 数据查找：VLOOKUP、XLOOKUP、INDEX+MATCH
• 文本处理：LEFT/RIGHT/MID、TEXT、CONCAT、TRIM
• 日期处理：DATE、DATEDIF、YEAR/MONTH/DAY、TODAY/NOW
• 进阶分析：RANK.EQ、SUBTOTAL、ROUND、NPV、NPER、UNIQUE、SORT