Excel表格函数公式大全

Excel表格函数公式大全

一、基础运算与数值处理（共 10 个）

1.SUM

◦用途：计算指定区域数值总和

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

◦示例：=SUM(A1:A10,B1:B5)（计算 A1-A10 和 B1-B5 的和）

2.AVERAGE

◦用途：计算数值区域的算术平均值

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

◦示例：=AVERAGE(C2:C20)（求 C 列 2-20 行的平均值）

3.MAX

◦用途：返回指定区域中的最大值

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

◦示例：=MAX(D1:D30)（提取 D 列 1-30 行的最大值）

4.MIN

◦用途：返回指定区域中的最小值

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

◦示例：=MIN(E1:E50)（提取 E 列 1-50 行的最小值）

5.ABS

◦用途：返回数值的绝对值

◦语法：=ABS(数值)

◦示例：=ABS(-15)（结果为 15）

6.ROUND

◦用途：按指定小数位数四舍五入数值

◦语法：=ROUND(数值, 小数位数)

◦示例：=ROUND(3.1415, 2)（结果为 3.14）

7.ROUNDUP

•用途：向上舍入数值（无论小数位大小，均进 1）

•语法：=ROUNDUP(数值, 小数位数)

•示例：=ROUNDUP(3.141, 2)（结果为 3.15）；=ROUNDUP(5.1, 0)（结果为 6）

•注意：小数位数为负数时，向上舍入到整数位（如ROUNDUP(123.4, -1)=130）

8.ROUNDDOWN

•用途：向下舍入数值（无论小数位大小，均舍去）

•语法：=ROUNDDOWN(数值, 小数位数)

•示例：=ROUNDDOWN(3.999, 2)（结果为 3.99）；=ROUNDDOWN(78.9, -1)（结果为 70）

9.INT

•用途：取数值的整数部分（直接舍去小数，不四舍五入）

•语法：=INT(数值)

•示例：=INT(15.9)（结果为 15）；=INT(-3.2)（结果为 - 4，负数向更小整数取整）

10.MOD

•用途：计算两个数相除的余数（余数符号与除数一致）

•语法：=MOD(被除数, 除数)

•示例：=MOD(10, 3)（结果为 1）；=MOD(-10, 3)（结果为 2，除数 3 为正，余数为正）

二、文本处理（共 13 个）

1.LEFT

◦用途：从文本左侧提取指定长度字符

◦语法：=LEFT(文本, [提取长度])

◦示例：=LEFT("Excel函数", 2)（结果为“Ex”）

2.RIGHT

◦用途：从文本右侧提取指定长度字符

◦语法：=RIGHT(文本, [提取长度])

◦示例：=RIGHT("数据分析", 3)（结果为“据分析”）

3.MID

◦用途：从文本指定位置提取指定长度字符

◦语法：=MID(文本, 起始位置, 提取长度)

◦示例：=MID("20251020", 5, 4)（从第 5 位提取 4 个字符，结果为 “1020”）

4.CONCAT

◦用途：合并多个文本或单元格内容（替代旧版 CONCATENATE）

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

◦示例：=CONCAT(A2, "-", B2)（合并 A2、“-” 和 B2，如 “A001 - 张三”）

5.LEN

◦用途：计算文本的字符个数（含中文，每个字符计 1）

◦语法：=LEN(文本)

◦示例：=LEN("Excel表格")（结果为 7，“Excel” 5 个字母 +“表格” 2 个中文）

6.TRIM

◦用途：去除文本前后多余空格（保留中间单个空格）

◦语法：=TRIM(文本)

◦示例：=TRIM("  数据  处理  ")（结果为“数据  处理”）

7.TEXT

◦用途：将数值按指定格式转换为文本

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

◦示例：=TEXT(12345, "#,##0")（结果为“12,345”）

8.FIND

◦用途：查找字符在文本中的起始位置（区分大小写）

◦语法：=FIND(查找字符, 文本, [起始位置])

◦示例：=FIND("c", "Excel Column")（结果为 8，“Column” 中 “c” 在第 8 位）

9.PROPER

◦用途：将文本中每个单词的首字母大写，其余小写

◦语法：=PROPER(文本)

◦示例：=PROPER("excel FUNCTION")（结果为“Excel Function”）；=PROPER("zhang san")（结果为“Zhang San”）

10.UPPER

◦用途：将文本全部转换为大写

◦语法：=UPPER(文本)

◦示例：=UPPER("Excel公式")（结果为“EXCEL 公式”）；=UPPER("abc123")（结果为“ABC123”）

11.LOWER

◦用途：将文本全部转换为小写

◦语法：=LOWER(文本)

◦示例：=LOWER("DATA ANALYSIS")（结果为“data analysis”）

12.REPLACE

◦用途：按指定位置替换文本中的字符（固定位置替换）

◦语法：=REPLACE(原文本, 起始位置, 替换长度, 新文本)

◦示例：=REPLACE("20251020", 5, 4, "1231")（将第 5-8 位 “1020” 替换为 “1231”，结果为 “20251231”）

13.SUBSTITUTE

◦用途：按指定字符替换文本（按内容替换，可指定替换次数）

◦语法：=SUBSTITUTE(原文本, 旧字符, 新字符, [替换次数])

◦示例：=SUBSTITUTE("a-b-c-d", "-", "|", 2)（将前 2 个 “-” 替换为 “|”，结果为 “a|b|c-d”）

三、日期时间（共 11 个）

1.TODAY

◦用途：返回当前系统日期（无参数，自动更新）

◦语法：=TODAY()

◦示例：=TODAY()（2025 年 10 月 20 日返回 “2025/10/20”）

2.NOW

◦用途：返回当前系统日期和时间（自动更新）

◦语法：=NOW()

◦示例：=NOW()（返回“2025/10/20 14:30:25”）

3.DATEDIF

◦用途：计算两个日期的差值（年 / 月 / 日）

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

◦单位："Y"（年）、"M"（月）、"D"（日）、"YM"（忽略年算月）

◦示例：=DATEDIF("2023/1/1", TODAY(), "Y")（计算 2023 年 1 月 1 日到今天的年数）

4.YEAR

◦用途：提取日期中的年份

◦语法：=YEAR(日期)

◦示例：=YEAR("2025/10/20")（结果为 2025）

5.MONTH

◦用途：提取日期中的月份

◦语法：=MONTH(日期)

◦示例：=MONTH(TODAY())（10 月返回 10）

6.DAY

◦用途：提取日期中的日

◦语法：=DAY(日期)

◦示例：=DAY("2025/10/20")（结果为 20）

7.EDATE

◦用途：计算指定日期前后 N 个月的日期

◦语法：=EDATE(起始日期, 月份数)

◦示例：=EDATE(TODAY(), 3)（返回 3 个月后的今天日期）

8.DATE

◦用途：将年、月、日数值组合成日期格式

◦语法：=DATE(年份, 月份, 日期)

◦示例：=DATE(2025, 12, 31)（结果为“2025/12/31”）；=DATE(2025, 13, 1)（自动调整为“2026/1/1”）

9.TIME

◦用途：将时、分、秒数值组合成时间格式

◦语法：=TIME(小时, 分钟, 秒)

◦示例：=TIME(14, 30, 45)（结果为“14:30:45”）；=TIME(25, 10, 0)（自动调整为“1:10:00”）

10.HOUR/MINUTE/SECOND

◦用途：分别提取时间中的小时、分钟、秒

◦语法：=HOUR(时间)/=MINUTE(时间)/=SECOND(时间)

◦示例：=HOUR("18:45:30")（结果为 18）；=SECOND(NOW())（返回当前时间的秒数）

11.NETWORKDAYS

◦用途：计算两个日期之间的工作日天数（自动排除周末，可自定义节假日）

◦语法：=NETWORKDAYS(开始日期, 结束日期, [节假日区域])

◦示例：=NETWORKDAYS("2025/10/20", "2025/10/30")（计算 10.20-10.30 的工作日，排除周末）；若 E1:E3 为节假日，公式为=NETWORKDAYS("2025/10/20", "2025/10/30", E1:E3)

四、逻辑判断（共 8 个）

1.IF

◦用途：根据条件返回不同结果（单条件判断）

◦语法：=IF(条件, 满足条件结果, 不满足条件结果)

◦示例：=IF(A2>=60, "及格", "不及格")（A2 分数≥60 显示 “及格”，否则 “不及格”）

2.AND

◦用途：所有条件都满足时返回 TRUE，否则 FALSE

◦语法：=AND(条件1, [条件2,...])

◦示例：=AND(B2>=80, C2>=80)（B2 和 C2 都≥80 返回 TRUE）

3.OR

◦用途：任意一个条件满足时返回 TRUE，否则 FALSE

◦语法：=OR(条件1, [条件2,...])

◦示例：=OR(D2>90, E2>90)（D2 或 E2 任一 > 90 返回 TRUE）

4.IFERROR

◦用途：捕获公式错误，返回指定值

◦语法：=IFERROR(原公式, 错误时返回值)

◦示例：=IFERROR(VLOOKUP(A2,B:C,2,0), "未找到")（VLOOKUP 出错时显示 “未找到”）

5.IFNA

◦用途：仅捕获 #N/A 错误，其他错误正常显示

◦语法：=IFNA(原公式, #N/A时返回值)

◦示例：=IFNA(MATCH(A2,B:B,0), "无匹配")（匹配不到时显示“无匹配”）

6.SWITCH

◦用途：多条件匹配（替代多层 IF，更简洁），匹配到第一个符合条件的值即返回

◦语法：=SWITCH(判断值, 条件1, 结果1, 条件2, 结果2,..., [默认结果])

◦示例：=SWITCH(A2, 1, "优秀", 2, "良好", 3, "合格", "不及格")（A2 为 1 返回 “优秀”，2 返回 “良好”，以此类推）

7.NOT

◦用途：反转逻辑值（TRUE 变 FALSE，FALSE 变 TRUE）

◦语法：=NOT(逻辑值/条件)

◦示例：=NOT(AND(B2>=60, C2>=60))（若 B2、C2 均≥60，AND 返回 TRUE，NOT 后为 FALSE）

8.IFS

◦用途：多条件判断（按顺序检查，满足第一个条件即返回对应结果）

◦语法：=IFS(条件1, 结果1, 条件2, 结果2,..., [条件N, 结果N])

◦示例：=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", A2>=60, "D", TRUE, "F")（按分数段返回等级）

五、数据查询与提取（共 10 个）

1.VLOOKUP

◦用途：按列查找，返回指定列数据（左查右）

◦语法：=VLOOKUP(查找值, 数据表, 返回列序数, 匹配类型)

◦匹配类型：0（精确匹配，推荐）、1（近似匹配）

◦示例：=VLOOKUP("张三", A:C, 3, 0)（在 A 列找 “张三”，返回 C 列对应值）

2.HLOOKUP

◦用途：按行查找，返回指定行数据（上查下）

◦语法：=HLOOKUP(查找值, 数据表, 返回行序数, 匹配类型)

◦示例：=HLOOKUP("销售额", A:E, 4, 0)（在第 1 行找 “销售额”，返回第 4 行对应值）

3.INDEX

◦用途：按行号列号定位单元格值（可单独用或配 MATCH）

◦语法 1（区域）：=INDEX(数据区域, 行号, [列号])

◦语法 2（数组）：=INDEX(数组, 位置)

◦示例：=INDEX(A1:C10, 5, 2)（返回 A1:C10 区域第 5 行第 2 列的值）

4.MATCH

◦用途：返回查找值在区域中的位置（行号 / 列号）

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

◦示例：=MATCH("李四", B:B, 0)（返回“李四” 在 B 列的行号）

5.INDEX+MATCH（组合）

◦用途：替代 VLOOKUP，支持左右查找、反向查找

◦语法：=INDEX(返回区域, MATCH(查找值, 查找区域, 0))

◦示例：=INDEX(A:A, MATCH(100, C:C, 0))（在 C 列找 100，返回 A 列对应值，实现 “右查左”）

6.XLOOKUP

◦用途：Excel 365 新增，替代 VLOOKUP/HLOOKUP，支持双向查找

◦语法：=XLOOKUP(查找值, 查找区域, 返回区域, [未找到值], [匹配类型])

◦示例：=XLOOKUP(A2, B:B, D:D, "无数据", 0)（B 列找 A2，返回 D 列值，无匹配显示 “无数据”）

7.LOOKUP（向量形式）

◦用途：单条件查找（适用于已排序的查找区域，返回对应值）

◦语法：=LOOKUP(查找值, 查找向量, [结果向量])

◦示例：=LOOKUP(100, A2:A10, B2:B10)（在 A2:A10 中找≤100 的最大值对应的 B 列值，需 A 列升序）

8.FILTER（Excel 365+）

◦用途：按条件筛选数据（返回动态数组）

◦语法：=FILTER(数据区域, 筛选条件, [无匹配时返回值])

◦示例：=FILTER(A2:C20, B2:B20="销售部", "无销售部数据")（筛选 B 列为 “销售部” 的 A-C 列数据）

9.CHOOSEROWS/CHOOSECOLS

◦用途：分别按行号、列号提取指定行 / 列数据

◦语法：=CHOOSEROWS(数据区域, 行号1, [行号2,...])/=CHOOSECOLS(数据区域, 列号1, [列号2,...])

◦示例：=CHOOSEROWS(A1:C10, 2, 5)（提取第 2 行和第 5 行数据）；=CHOOSECOLS(A1:E10, 1, 3)（提取第 1 列和第 3 列数据）

10.XMATCH（Excel 365+）

◦用途：替代 MATCH，支持反向查找、精确匹配 / 模糊匹配

◦语法：=XMATCH(查找值, 查找区域, [匹配类型], [搜索方向])

◦示例：=XMATCH("李四", B2:B20, 0, -1)（从 B 列末尾向前找 “李四”，精确匹配，返回行号）

六、统计分析（共 14 个）

1.COUNT

◦用途：计算区域中数值型数据的个数（忽略文本、空值）

◦语法：=COUNT(值1, [值2,...])

◦示例：=COUNT(A1:A20)（统计 A1-A20 中数值的个数）

2.COUNTA

◦用途：计算区域中非空单元格的个数（含文本、数值）

◦语法：=COUNTA(值1, [值2,...])

◦示例：=COUNTA(B1:B30)（统计 B1-B30 中非空单元格数）

3.COUNTBLANK

◦用途：计算区域中空单元格的个数

◦语法：=COUNTBLANK(区域)

◦示例：=COUNTBLANK(C1:C50)（统计 C 列 1-50 行空单元格数）

4.COUNTIF

◦用途：单条件统计单元格个数

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

◦示例：=COUNTIF(D:D, ">90")（统计 D 列中 > 90 的单元格数）

5.COUNTIFS

◦用途：多条件统计单元格个数（条件同时满足）

◦语法：=COUNTIFS(区域1, 条件1, [区域2, 条件2,...])

◦示例：=COUNTIFS(E:E, "男", F:F, ">25")（统计 E 列 “男” 且 F 列 > 25 的单元格数）

6.SUMIF

◦用途：单条件求和

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

◦示例：=SUMIF(G:G, "销售部", H:H)（G 列 “销售部” 对应的 H 列数值求和）

7.SUMIFS

◦用途：多条件求和（条件同时满足）

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

◦示例：=SUMIFS(I:I, J:J, "2025", K:K, "华东")（J 列 “2025” 且 K 列 “华东” 对应的 I 列求和）

8.RANK.EQ

◦用途：计算数值在区域中的排名（相同值排名相同，后续排名跳过）

◦语法：=RANK.EQ(数值, 排名区域, [排序方式])

◦排序方式：0（降序，默认）、1（升序）

◦示例：=RANK.EQ(L2, L:L, 0)（L2 在 L 列的降序排名）

9.MODE.SNGL

◦用途：返回区域中出现频率最高的数值（单峰）

◦语法：=MODE.SNGL(数值区域)

◦示例：=MODE.SNGL(M1:M20)（返回 M1-M20 中出现次数最多的数值）

10.STDEV.S

◦用途：计算样本数据的标准差（反映数据离散程度）

◦语法：=STDEV.S(数值区域)

◦示例：=STDEV.S(N1:N30)（计算 N 列 1-30 行样本数据的标准差）

11.AVERAGEIF/AVERAGEIFS

◦用途：单条件 / 多条件计算平均值

◦语法：=AVERAGEIF(条件区域, 条件, [平均区域])/=AVERAGEIFS(平均区域, 区域1, 条件1, [区域2, 条件2,...])

◦示例：=AVERAGEIF(C:C, "男", D:D)（C 列为 “男” 的 D 列平均值）；=AVERAGEIFS(D:D, C:C, "男", E:E>30)（C 列 “男” 且 E 列 > 30 的 D 列平均值）

12.MEDIAN

◦用途：返回数据区域的中位数（将数据排序后，中间位置的数值）

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

◦示例：=MEDIAN(1, 3, 5, 7, 9)（结果为 5）；=MEDIAN(2, 4, 6, 8)（结果为 5，中间两个数 4 和 6 的平均值）

13.PERCENTILE.EXC

◦用途：计算数据区域中指定百分位的值（排除 0 和 100 百分位，适用于样本数据）

◦语法：=PERCENTILE.EXC(数据区域, 百分位值)（百分位值范围：0<P<1）

◦示例：=PERCENTILE.EXC(A2:A20, 0.75)（返回 A 列数据 75% 分位值）

14.COUNTUNIQUE（Excel 365+）

◦用途：统计数据区域中唯一值的个数

◦语法：=COUNTUNIQUE(值1, [值2,...])

◦示例：=COUNTUNIQUE(B2:B50)（统计 B 列不重复值的数量）；=COUNTUNIQUE(A2:A10, C2:C10)（统计 A 列和 C 列组合后的唯一值数量）

七、财务与特殊函数（共 12 个）

1.PMT

◦用途：计算贷款每期还款额（等额本息）

◦语法：=PMT(月利率, 总期数, 贷款本金, [未来值], [付款类型])

◦示例：=PMT(5%/12, 24, -10000)（月利率 5%/12，24 期，本金 10000，每期还款额≈438.71）

2.PV

◦用途：计算投资的现值（未来资金的当前价值）

◦语法：=PV(利率, 期数, 每期支付额, [未来值], [付款类型])

◦示例：=PV(6%, 5, -2000)（年利率 6%，5 年，每年付 2000，现值≈8424.73）

3.FV

◦用途：计算投资的未来值（当前资金的未来价值）

◦语法：=FV(利率, 期数, 每期支付额, [现值], [付款类型])

◦示例：=FV(4%/12, 36, -500, -10000)（月利率 4%/12，36 期，月存 500，初始 10000，未来值≈31733.68）

4.ROW

◦用途：返回当前单元格的行号（无参数时）或指定单元格行号

◦语法：=ROW([单元格])

◦示例：=ROW()（当前在第 10 行，返回 10）；=ROW(C5)（返回 5）

5.COLUMN

◦用途：返回当前单元格的列号（无参数时）或指定单元格列号

◦语法：=COLUMN([单元格])

◦示例：=COLUMN()（当前在 D 列，返回 4）；=COLUMN(F8)（返回 6）

6.INDIRECT

◦用途：将文本格式的单元格地址转换为实际地址并返回值

◦语法：=INDIRECT(文本地址, [引用类型])

◦示例：=INDIRECT("A"&5)（将“A5” 转换为地址，返回 A5 的值）

7.OFFSET

◦用途：以指定单元格为起点，偏移指定行 / 列后返回区域值

◦语法：=OFFSET(起点单元格, 行偏移, 列偏移, [高度], [宽度])

◦示例：=OFFSET(A1, 2, 3, 1, 1)（从 A1 向下 2 行、向右 3 列，返回 1 行 1 列的值，即 D3）

8.SUMPRODUCT

◦用途：多区域对应元素相乘后求和（支持条件筛选）

◦语法：=SUMPRODUCT(数组1, [数组2,...])

◦示例 1（普通求和）：=SUMPRODUCT(A1:A5, B1:B5)（A1B1 + A2B2 +... + A5*B5）

◦示例 2（条件求和）：=SUMPRODUCT((C1:C10="A")*(D1:D10>100)*D1:D10)（C 列 “A” 且 D 列 > 100 的 D 列数值求和）

9.IPMT

◦用途：计算贷款指定期数的利息部分（等额本息还款）

◦语法：=IPMT(利率, 期数, 总期数, 贷款本金, [未来值], [付款类型])

◦示例：=IPMT(5%/12, 1, 24, -10000)（月利率 5%/12，第 1 期，总 24 期，本金 10000，第 1 期利息≈41.67）

10.PPMT

◦用途：计算贷款指定期数的本金部分（等额本息还款）

◦语法：=PPMT(利率, 期数, 总期数, 贷款本金, [未来值], [付款类型])

◦示例：=PPMT(5%/12, 1, 24, -10000)（第 1 期本金≈397.04，与 IPMT 相加为 PMT 结果≈438.71）

11.RATE

◦用途：计算贷款或投资的利率（已知期数、每期支付额、本金）

◦语法：=RATE(总期数, 每期支付额, 本金, [未来值], [付款类型], [估计值])

◦示例：=RATE(24, -438.71, 10000)*12（总 24 期，月付 438.71，本金 10000，计算年利率≈5%）

12.NPV

◦用途：计算投资的净现值（按指定贴现率，将未来现金流折算为现值，判断投资可行性）

◦语法：=NPV(贴现率, 现金流1, [现金流2,...])（现金流从第 1 期开始，初始投资需单独减去）

◦示例：=NPV(8%, -10000, 3000, 4000, 5000)（贴现率 8%，初始投资 10000，后续 3 年现金流 3000、4000、5000，净现值≈672.71，为正则可行）

八、数据处理新函数（Excel 365+，共 3 个）

1.UNIQUE

◦用途：提取数据区域的唯一值（返回动态数组，自动去重）

◦语法：=UNIQUE(数据区域, [按行/按列])（TRUE 按行去重，默认 FALSE 按列）

◦示例：=UNIQUE(B2:B50)（提取 B 列所有不重复值）；=UNIQUE(A2:C20, TRUE)（提取 A-C 列组合后的唯一行）

2.SORT

◦用途：按指定条件排序数据（返回动态数组，不改变原数据）

◦语法：=SORT(数据区域, [排序列], [排序方式], [按行/按列])（排序方式：1 升序，-1 降序）

◦示例：=SORT(A2:C20, 3, -1)（按第 3 列降序排序 A-C 列数据）

3.SORTBY

◦用途：按指定区域排序目标数据（按外部区域规则排序，更灵活）

◦语法：=SORTBY(目标区域, 排序区域1, [排序方式1], [排序区域2, 排序方式2,...])

◦示例：=SORTBY(A2:A20, B2:B20, -1)（按 B 列降序排序 A 列数据）