函数类型统计函数字符串函数数值函数逻辑函数日期函数选择查找函数

统计函数

一、计数类函数

1. COUNT

功能：统计参数中包含数字的单元格个数（文本、空值、逻辑值不计入）。

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

案例：

A1:A5 分别为: 10, "苹果", 20, "", TRUE=COUNT(A1:A5)  →  结果：2  （只统计10和20）

2. COUNTA

功能：统计非空单元格个数（数字、文本、逻辑值都算，空值不算）。

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

案例：

A1:A5 分别为: 10, "苹果", 20, "", TRUE=COUNTA(A1:A5)  →  结果：4  （""为空，不计入）

3. COUNTBLANK

功能：统计空白单元格个数。

语法：COUNTBLANK(区域)

案例：

A1:A5 分别为: 10, "", 20, "", ""=COUNTBLANK(A1:A5)  →  结果：3

4. COUNTIFS

功能：按多条件统计满足条件的单元格个数。

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

案例：

A列: 部门    B列: 销售额    C列: 月份销售部  8000  1月市场部  5000  1月销售部  9000  2月=COUNTIFS(A2:A4, "销售部", C2:C4, "1月")  →  结果：1

二、求和与平均类函数

5. SUM

功能：对数字求和。

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

案例：

=SUM(10, 20, 30)  →  60=SUM(A1:A10)  →  A1到A10的总和

6. SUMIFS

功能：按多条件求和。

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

案例：

=SUMIFS(B2:B10, A2:A10, "销售部", C2:C10, ">5000")→  统计A列为"销售部"且C列大于5000的对应B列之和

7. AVERAGE

功能：计算算术平均值。

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

案例：

=SUM(10, 20, 30)  →  20

8. AVERAGEIFS

功能：按多条件计算平均值。

语法：AVERAGEIFS(求平均区域, 条件区域1, 条件1, ...)

案例：

=AVERAGEIFS(B2:B10, A2:A10, "销售部", C2:C10, ">=1月")

三、极值与数据库函数

9. MAX

功能：返回一组数据中的最大值。

语法：MAX(数字1, [数字2], ...)

案例：

=MAX(10, 50, 30, 80)  →  80

10. DMAX

功能：从数据库/列表中按条件返回最大值。

语法：DMAX(数据库区域, 字段, 条件区域)

案例：

数据库区域 A1:C10（含标题行）条件区域 E1:E2（E1写"部门"，E2写"销售部"）=DMAX(A1:C10, "销售额", E1:E2)→  返回销售部中的最大销售额

11. MIN

功能：返回一组数据中的最小值。

语法：MIN(数字1, [数字2], ...)

案例：

=MIN(10, 50, 30, 80)  →  10

12. DMIN

功能：从数据库中按条件返回最小值。

语法：DMIN(数据库区域, 字段, 条件区域)

案例：

=DMIN(A1:C10, "销售额", E1:E2)→  返回销售部中的最小销售额

四、中位数与乘积和

13. MEDIAN

功能：返回一组数据的中位数（排序后中间值，偶数个时取中间两数平均）。

语法：MEDIAN(数字1, [数字2], ...)

案例：

=MEDIAN(1, 3, 5, 7, 9)  →  5=MEDIAN(1, 3, 5, 7)     →  4  （(3+5)/2）

14. SUMPRODUCT

功能：先乘积后求和，常用于加权平均或多条件统计。

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

案例：

价格: A1:A3 = {10, 20, 30}数量: B1:B3 = {5, 3, 2}=SUMPRODUCT(A1:A3, B1:B3)  →  10×5 + 20×3 + 30×2 = 50+60+60 = 170多条件用法：=SUMPRODUCT((A2:A10="销售部")*(B2:B10>5000)*C2:C10)→  统计销售部且销售额>5000的C列总和

五、统计分布函数

15. VAR.S

功能：计算样本方差（基于样本，分母为 n-1）。

语法：VAR.S(数字1, [数字2], ...)

案例：

=VAR.S(2, 4, 6, 8)  →  结果约 6.67

16. SKEW

功能：返回数据分布的偏度（衡量不对称性），正数右偏，负数左偏。

语法：SKEW(数字1, [数字2], ...)

案例：

=SKEW(1, 2, 3, 4, 100)  →  结果约 1.93（明显右偏，因为100拉高了右侧）

17. NORM.DIST

功能：计算正态分布的概率密度或累积概率。

语法：NORM.DIST(x, 均值, 标准差, 累积)

• • 累积 = TRUE：返回累积分布函数 P(X ≤ x)
• • 累积 = FALSE：返回概率密度函数 f(x)

案例：

均值=50，标准差=10=NORM.DIST(60, 50, 10, TRUE)  →  约 0.8413（60分及以下约占84.13%）=NORM.DIST(60, 50, 10, FALSE) →  约 0.0242（60分处的概率密度值）

字符串函数

一、长度计算

1. LEN

功能：返回字符串的字符数（中英文每个字符都算1个）。

语法：LEN(文本)

案例：

=LEN("Hello")      →  5=LEN("你好世界")    →  4=LEN(A1)           →  A1单元格的字符数

2. LENB

功能：返回字符串的字节数（中文双字节算2，英文单字节算1）。

语法：LENB(文本)

案例：

=LENB("Hello")      →  5=LENB("你好")       →  4  （每个汉字2字节）=LENB("Hi你好")     →  6  （2+4）

技巧：提取中文姓名（假设A1为"张三123"）=LEFTB(A1, LENB(A1)-LEN(A1)) → 返回"张三"

二、截取提取

3. LEFT

功能：从字符串左侧截取指定个数的字符。

语法：LEFT(文本, [截取个数])

案例：

=LEFT("Excel函数", 3)   →  "Excel"=LEFT("2024年", 4)      →  "2024"

4. RIGHT

功能：从字符串右侧截取指定个数的字符。

语法：RIGHT(文本, [截取个数])

案例：

=RIGHT("Excel函数", 2)   →  "函数"=RIGHT("订单号A001", 4)  →  "A001"

5. MID

功能：从字符串中间指定位置截取指定长度的字符。

语法：MID(文本, 开始位置, 截取个数)

案例：

=MID("身份证号110101199001011234", 7, 8)  →  "19900101"  （提取出生日期）=MID("ABC-DEF-GHI", 5, 3)  →  "DEF"

三、大小写转换

6. UPPER

功能：将字符串全部转为大写。

语法：UPPER(文本)

案例：

=UPPER("excel")    →  "EXCEL"=UPPER("Hello123") →  "HELLO123"

7. LOWER

功能：将字符串全部转为小写。

语法：LOWER(文本)

案例：

=LOWER("EXCEL")    →  "excel"=LOWER("Hello World") → "hello world"

补充：PROPER("hello world") → "Hello World"（首字母大写）

四、查找定位

8. FIND

功能：查找某字符串在文本中的起始位置，区分大小写，不支持通配符。找不到返回错误。

语法：FIND(查找文本, 所在文本, [开始位置])

案例：

=FIND("e", "Excel")       →  2=FIND("E", "Excel")       →  1=FIND("函数", "Excel函数") →  6=FIND("-", "2024-07-16")  →  5

9. SEARCH

功能：查找某字符串在文本中的起始位置，不区分大小写，支持通配符（?代表单个字符，*代表任意字符）。

语法：SEARCH(查找文本, 所在文本, [开始位置])

案例：

=SEARCH("e", "Excel")        →  1  （不区分大小写）=SEARCH("Ex*", "Excel函数")  →  1=SEARCH("??函数", "Excel函数") →  3  （??匹配"ce"）

FIND vs SEARCH 对比：

• • FIND：区分大小写，不支持通配符
• • SEARCH：不区分大小写，支持通配符

五、替换函数

10. SUBSTITUTE

功能：将字符串中指定的旧文本替换为新文本，可指定替换第几个。

语法：SUBSTITUTE(文本, 旧文本, 新文本, [替换第几个])

案例：

=SUBSTITUTE("苹果-苹果-香蕉", "苹果", "橙子")  →  "橙子-橙子-香蕉"=SUBSTITUTE("苹果-苹果-香蕉", "苹果", "橙子", 2)  →  "苹果-橙子-香蕉"  （只替换第2个）=SUBSTITUTE("2024/07/16", "/", "-")  →  "2024-07-16"

11. REPLACE

功能：根据位置替换字符串中的部分内容。

语法：REPLACE(旧文本, 开始位置, 替换字符数, 新文本)

案例：

=REPLACE("110101199001011234", 7, 8, "********")  →  "110101********1234"  （身份证号打码）=REPLACE("Hello World", 7, 5, "Excel")  →  "Hello Excel"

SUBSTITUTE vs REPLACE 对比：

• • SUBSTITUTE：按内容替换
• • REPLACE：按位置替换

六、连接与比较

12. CONCATENATE

功能：将多个字符串连接成一个。Excel 2016+ 推荐使用 CONCAT 或 TEXTJOIN。

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

案例：

=CONCATENATE("姓", "名")           →  "姓名"=CONCATENATE(A1, "-", B1)          →  A1和B1用"-"连接更简洁写法（推荐）：=A1 & "-" & B1

TEXTJOIN（Excel 2016+）：=TEXTJOIN("-", TRUE, A1:A5)用"-"连接A1:A5，忽略空值。

13. EXACT

功能：比较两个字符串是否完全相同（区分大小写）。

语法：EXACT(文本1, 文本2)

案例：

=EXACT("Excel", "excel")  →  FALSE=EXACT("Excel", "Excel")  →  TRUE=A1=B1                    →  不区分大小写=EXACT(A1, B1)            →  区分大小写

七、清理函数

14. TRIM

功能：删除字符串中多余的空格（保留单词间单个空格，删除首尾空格）。

语法：TRIM(文本)

案例：

=TRIM("  Hello   World  ")   →  "Hello World"=TRIM("  Excel  函数  ")     →  "Excel 函数"

补充清理函数：

• • CLEAN(文本)：删除非打印字符
• • TRIM(CLEAN(A1))：组合使用，彻底清理

综合案例

案例1：从邮箱提取用户名

邮箱: "zhangsan@company.com"=LEFT(A1, FIND("@", A1)-1)  →  "zhangsan"

案例2：隐藏手机号中间4位

手机号: "13812345678"=REPLACE(A1, 4, 4, "****")  →  "138****5678"

案例3：提取文件名（不含扩展名）

文件名: "report.xlsx"=LEFT(A1, FIND(".", A1)-1)  →  "report"

案例4：统一日期格式

日期: "2024/07/16"=SUBSTITUTE(A1, "/", "-")   →  "2024-07-16"

案例5：判断是否为纯数字

=IF(ISNUMBER(--A1), "是数字", "非数字")结合LEN: =IF(LEN(A1)=LENB(A1), "纯英文/数字", "含中文")

快速对比表

数值函数

一、随机数函数

1. RAND

功能：返回一个大于等于0且小于1的随机小数。每次工作表重新计算时都会变化。

语法：RAND()

案例：

=RAND()           →  0.372891...（每次计算都不同）=RAND()*100       →  0~100之间的随机小数=RAND()*50+50     →  50~100之间的随机小数

固定随机值：选中单元格 → Ctrl+C → Ctrl+Alt+V → 选择"值"粘贴。

2. RANDBETWEEN

功能：返回指定范围内的随机整数（包含上下界）。

语法：RANDBETWEEN(最小值, 最大值)

案例：

=RANDBETWEEN(1, 100)     →  1到100之间的随机整数=RANDBETWEEN(50, 200)    →  50到200之间的随机整数=RANDBETWEEN(-10, 10)    →  -10到10之间的随机整数

生成随机日期：=RANDBETWEEN(DATE(2024,1,1), DATE(2024,12,31))

二、绝对值与取余

3. ABS

功能：返回数字的绝对值（去掉正负号）。

语法：ABS(数字)

案例：

=ABS(-10)       →  10=ABS(10)        →  10=ABS(A1-B1)     →  两数差的绝对值（常用于计算误差）

4. MOD

功能：返回两数相除后的余数（取模运算）。

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

案例：

=MOD(17, 5)     →  2  （17÷5=3余2）=MOD(20, 4)     →  0  （整除余数为0）判断奇偶：=IF(MOD(A1, 2)=0, "偶数", "奇数")

注意：MOD 的结果符号与除数相同。=MOD(-17, 5) → 3（Excel 中）；=MOD(17, -5) → -3。

三、幂运算与乘积

5. POWER

功能：返回某数的乘幂（即指数运算）。

语法：POWER(底数, 指数)

案例：

=POWER(2, 3)     →  8  （2的3次方）=POWER(10, 2)    →  100=POWER(9, 0.5)   →  3  （开平方，等同于=SQRT(9)）

也可用 ^ 运算符代替：2^3 = 8

6. PRODUCT

功能：返回所有参数的乘积。

语法：PRODUCT(数字1, [数字2], ...)

案例：

=PRODUCT(2, 3, 4)     →  24=PRODUCT(A1:A5)       →  A1到A5的乘积=PRODUCT(A1, 1.1)     →  A1的值乘以1.1（常用于涨价10%）

四、向上/向下取整

7. CEILING

功能：将数字向上舍入到最接近的指定基数的倍数。

语法：CEILING(数字, 基数)

案例：

=CEILING(17, 5)       →  20  （向上舍入到5的倍数）=CEILING(23, 10)      →  30=CEILING(4.2, 1)      →  5=CEILING(100, 50)     →  100计算包装箱数（每箱装6个）：=CEILING(25, 6)       →  30  （需要30个位置，即5箱）

8. FLOOR

功能：将数字向下舍入到最接近的指定基数的倍数。

语法：FLOOR(数字, 基数)

案例：

=FLOOR(17, 5)         →  15  （向下舍入到5的倍数）=FLOOR(23, 10)        →  20=FLOOR(4.9, 1)        →  4计算完整组数（每组5人）：=FLOOR(23, 5)         →  20  （可组成4个完整组）

CEILING vs FLOOR 对比：

• • CEILING：向上取整（往大取）
• • FLOOR：向下取整（往小取）
• • 两者都要求基数为正数（Excel 中）。

五、四舍五入类函数

9. ROUND

功能：按指定小数位数进行四舍五入。

语法：ROUND(数字, 小数位数)

案例：

=ROUND(3.14159, 2)     →  3.14=ROUND(1234.567, 0)    →  1235=ROUND(1234.567, -1)   →  1230  （负数表示对整数位四舍五入）=ROUND(1234.567, -2)   →  1200

10. ROUNDUP

功能：按指定小数位数向上舍入（远离零）。

语法：ROUNDUP(数字, 小数位数)

案例：

=ROUNDUP(3.14159, 2)    →  3.15=ROUNDUP(3.1, 0)        →  4=ROUNDUP(-3.1, 0)       →  -4  （远离零，即更负）=ROUNDUP(1234, -2)      →  1300

11. ROUNDDOWN

功能：按指定小数位数向下舍入（靠近零）。

语法：ROUNDDOWN(数字, 小数位数)

案例：

=ROUNDDOWN(3.999, 2)    →  3.99=ROUNDDOWN(3.9, 0)      →  3=ROUNDDOWN(-3.9, 0)     →  -3  （靠近零）=ROUNDDOWN(1234, -2)    →  1200

ROUND / ROUNDUP / ROUNDDOWN 对比：

函数	3.6 → 0位	-3.6 → 0位
ROUND	4	-4
ROUNDUP	4	-4
ROUNDDOWN	3	-3

六、补充常用数值函数

综合案例

案例1：生成随机测试数据

=RANDBETWEEN(1000, 9999) & "-" & RANDBETWEEN(1000, 9999)→  "4821-7392"（随机订单号格式）

案例2：计算含税价格（向上取整到分）

原价: 99.99，税率: 6.5%=CEILING(99.99 * 1.065, 0.01)  →  106.49

案例3：计算快递费（不足1kg按1kg算）

重量: 2.3kg，单价: 10元/kg=ROUNDUP(2.3, 0) * 10  →  30

案例4：提取金额整数部分

金额: 1234.56=ROUNDDOWN(1234.56, 0)  →  1234或 =INT(1234.56)        →  1234

案例5：判断是否为3的倍数

=IF(MOD(A1, 3)=0, "是", "否")

案例6：批量生成随机日期

=DATE(2024, RANDBETWEEN(1, 12), RANDBETWEEN(1, 28))→  2024年随机一天

快速对比表

逻辑函数以下是逻辑函数和日期时间函数的详细用法与案例：

第一部分：逻辑函数

一、基础逻辑运算

1. AND

功能：所有条件都为**真（TRUE）**时返回 TRUE，任一条件为假则返回 FALSE。

语法：AND(逻辑1, [逻辑2], ...)

案例：

=AND(5>3, 10>8)              →  TRUE=AND(A1>60, B1>60, C1>60)    →  三科都及格才返回 TRUE=AND(A1>=DATE(2024,1,1), A1<=DATE(2024,12,31))  →  判断日期是否在2024年内

2. OR

功能：任一条件为**真（TRUE）**时返回 TRUE，全部条件为假才返回 FALSE。

语法：OR(逻辑1, [逻辑2], ...)

案例：

=OR(5>10, 8>3)               →  TRUE=OR(A1="优秀", A1="良好")     →  任一满足即 TRUE=OR(A1>90, B1>90, C1>90)     →  至少有一科超过90分

3. NOT

功能：对逻辑值取反。

语法：NOT(逻辑)

案例：

=NOT(TRUE)                   →  FALSE=NOT(5>10)                   →  TRUE=NOT(ISBLANK(A1))            →  A1非空时返回 TRUE

二、条件判断

4. IF

功能：根据条件判断返回不同结果。

语法：IF(条件, 条件为真时的值, 条件为假时的值)

案例：

=IF(A1>=60, "及格", "不及格")=IF(A1>=90, "优秀", IF(A1>=80, "良好", IF(A1>=60, "及格", "不及格")))=IF(AND(A1>0, A1<100), "有效", "无效")=IF(OR(A1="男", A1="女"), "性别有效", "请重新输入")

三、错误处理

5. IFERROR

功能：公式计算出错时返回指定值，否则返回公式正常结果。

语法：IFERROR(值, 错误时的值)

案例：

=IFERROR(A1/B1, "除数不能为0")=IFERROR(VLOOKUP(A1, B:C, 2, 0), "未找到")=IFERROR(DATEDIF(A1, B1, "D"), "日期错误")