Excel数据分析：函数公式之统计计算类函数

公式和函数在Excel数据分析中是数据清洗中必不可少的一环，用于汇总统计、文本处理、时间计算、查找匹配等

说明：本文仅做个人笔记记录，如有疑问欢迎戳我~

1、公式使用技巧

单元格地址引用时的锁定

在锁定位置前加$符号

功能强大的公式审核

位置：“公式”选项卡——公式审核模块

1、追踪引用单元格：在公式选项卡的公式审核中，点击追踪引用单元格，用箭头标识出当前公式引用了哪些单元格

2、追踪从属单元格：在公式选项卡的公式审核中，点击追踪引用单元格，用箭头标识出哪些单元格引用了当前单元格

3、显示公式：显示公式内容，而不是计算结果，并标识关联的单元格

4、公式求值：在公式选项卡的公式审核中，点击公式求值，【求值】按钮逐步显示公式计算结果，帮助快速理解复杂的公式

5、错误检查：在公式选项卡的公式审核中，点击错误检查，可以查看公式错误的原因

2、统计计算类函数

有以下单元格以及其值，其中A6的值为空：

A1，1

A2，2

A3，3

A4，a

A5，c

A6，

AVERAGE

含义：返回一组的算术平均值

表达式：=AVERAGE(number1,number2,…)

参数说明：value可以为某些单元格，也可以是具体某个数值型的值

示例：

计算A1-A3的平均值：=AVERAGE(A1:A3) —— 结果返回：2

COUNT

含义：统计数组或单元格区域中含有数字的单元格个数

表达式：=COUNT(value1,value2,…)

参数说明：value为包含或引用各类型数字的参数

示例：

统计A1-A5中数字个数：=COUNT(A1:A5) —— 结果返回：3

COUNTA

含义：计算区域内非空单元格个数（任何类型的内容计数）

表达式：=COUNTA(value1,value2,...)

参数说明：value为包含或引用各种类型数字的参数

示例：

统计A1-A6不为空的单元格个数：=COUNTA(A1:A6) —— 结果返回：5

COUNTIF

含义：计算区域中满足指定条件的单元格个数（单条件计数）

表达式：=COUNTIF(range,criteria)

参数说明：

range：单元格区域

criteria：条件，逻辑表达式或字符型需加双引号

示例：

统计A1-A3中大于1的数值个数：=COUNTIF(A1:A3,">1") —— 结果返回：2

COUNTIFS

含义：统计多个区域中满足给定条件的单元格的个数（多条件计数）

表达式：=COUNTIFS(criteira_range,criteria,…)

参数说明：

criteira_range：范围区域

criteria：条件，逻辑表达式或字符型需加双引号

示例：

统计A1-A3中大于1且小于3的数值个数：=COUNTIFS(A1:A3,">1",A1:A3,"<3") —— 结果返回：1

COUNTBLANK

含义：统计指定区域内空白单元格的个数

表达式：=COUNTBLANK(range)

参数说明：range为范围区域，只有一个

示例：

计算A1-A6中空值个数：=COUNTBLANK(A1:A6) —— 结果返回：1

FREQUENCY

含义：频数统计，以一列垂直数组返回一组数据的频率分布

表达式：=FREQUENCY(data_array,bins_array)

参数说明：

data_array：需要计算频数的数据区域

bins_array：计算频率的间隔点，可以为一组单元格，也可以为一组数值

示例：

统计A1-A3中小于等于2的个数和大于2的个数：=FREQUENCY(A1:A3,2) —— 结果返回：2,1

注意：函数为区间计数，如上式中划分为了2个区间，需要2-1个分割点，需选中2个输出的区间单元格（几个区间选中几个单元格），输入公式，按下ctrl + Shift + Enter实现数组计算，即可返回区间个数的数组（直接回车只有一个数值哦~）

SUBTOTAL

含义：可以根据源数据的筛选进行变换，返回一个数据列表或数据库的分类汇总

表达式：=SUBTOTAL(function_num,ref1,...)

参数说明：

function_num：一个数字，用于指定计算总和时要使用的函数，可以为以下值

1：AVERAGE，计算均值

2：COUNT，计算计数

3：COUNTA，计算非空单元格的计数

4：MAX，计算最大值

5：MIN，计算最小值

6：PRODUCT，计算乘积

7：STDEV，计算标准偏差

8：STDEVP，计算总体标准偏差

9：SUM，计算总和

10：VAR，计算方差

11：VARP，计算总体方差

ref：要计算的数据范围

示例：

计算A1-A3的和：=SUBTOTAL(9,A1:A3) —— 结果返回：6

注意：这里与SUM不同的是，SUBTOTAL是将筛选出来的可见部分求和

SUM

含义：对指定区域内的数值求和

表达式：=SUM(number1,number2,...)

参数说明：number为求和的数字参数或范围

示例：

计算A1-A3的和：=SUM(A1:A3) —— 结果返回：6

SUMIF

含义：对指定条件的值求和（单条件求和）

表达式：=SUMIF(range,criteria,sum_range)

参数说明：

range：包含有指定条件的区域

criteria：求和条件

sum_range：实际求和区域

示例：

① 计算A1-A3中大于1的值的和：=SUMIF(A1:A3,">1") —— 结果返回：5

② 设置数据有效性：当G列产品总额大于B列时，不可提交

思路：将F列的同类数值相加，包括自己，限制为小于B列库存值

在F（F3）产品列制作下拉列表，选项范围为A列G（G3）列 - 数据有效性 - 自定义 - 公式【=sumif(F:F,F3,G:G)≤sumif(A:A,F3,B:B)】

SUMIFS

含义：统计多个区域中满足给定条件数据之和（多条件求和）

表达式：=SUMIFS(sum_range,criteria_range,cirteria,…)

参数说明：

sum_range：实际求和范围

criteria_range：范围

cirteria：条件

省略号后面不用再添加实际求和范围，只需要添加范围2、条件2……

示例：

计算A1-A3中大于1且小于3的值的和：=SUMIFS(A1:A3,A1:A3,">1",A1:A3,"<3") —— 结果返回：2

SUMPRODUCT

含义：返回数组或区域的乘积之和

表达式：=SUMPRODUCT(array1,array2,array3,…)

参数说明：array为条件数组

示例1：

计算A1-A3的平方之和：=SUMPRODUCT(A1:A3,A1:A3) —— 结果返回14

注意：N个部分数组的元素个数必须相同，且行/列要统一

示例2：

有单元格B1-B3，名字分别为：张三、张三、李四

求不重复名字个数：=SUMPRODUCT(1/COUNTIF(B1:B3,B1:B3)) —— 结果返回：2

解析：COUNTIF(B1:B3,B1:B3)可以计算出每个不同名字的个数

用1/后，1,1,1变成0.5,0.5,1（即：1/2,1/2,1），这样无论某个名字重复多少次，用1/后再相加的结果都是1

LARGE

含义：返回某一数据集的某个最大值

表达式：=LARGE(array,k)

参数说明：

array：查询第k个最大值的数组或数据区域

k：返回值在数组或数据区域里的名次

示例：

查看A1-A3中最大的值：=LARGE(A1:A3,1) —— 结果返回：3

查看A1-A3中第二大的值：=LARGE(A1:A3,2) —— 结果返回：2

SMALL

含义：返回某一数据集的某个最小值

表达式：=SMALL(array,k)

参数说明：

array：查询第k个最小值的数组或数据区域

k：返回值在数组或数据区域里的名次

示例：

查看A1-A3中最小的值：=SMALL(A1:A3,1) —— 结果返回：1

查看A1-A3中第二小的值：=SMALL(A1:A3,2) —— 结果返回：2

MAX

含义：返回一组数据中的最大值（忽略逻辑值和文本）

表达式：=MAX(number1,number2,…)

参数说明：number可以是区域，也可以是具体的数值型数字（可以选择多个范围）

示例：

查看A1-A3中最大的值：=MAX(A1:A3) —— 结果返回：3

MIN

含义：返回一组数据中的最小值（忽略逻辑值和文本）

表达式：=MIN(number1,number2,…)

参数说明：number可以是区域，也可以是具体的数值型数字（可以选择多个范围）

示例：

查看A1-A3中最小的值：=MAX(A1:A3) —— 结果返回：1

RANK

含义：返回一个数值在一组数值中的数字排位（跳跃排序）

表达式：=RANK(number,ref,order)

参数说明：

number：需要排名的数字，为某个单元格的值

ref：数据区域

order：0为降序，可省略，1为升序

示例：

输出A1-A3的升序排位：=RANK(A1,A$1:A$3,1) —— 结果返回：1,2,3

输出A1-A3的降序排位：=RANK(A1,A$1:A$3,0) —— 结果返回：3,2,1

ABS

含义：取绝对值

表达式：=ABS(number)

示例：

输出-1的绝对值：=ABS(-1) —— 结果返回：1

MOD

含义：求余数，能被整除返回0，不能被整出返回余数值

表达式：=MOD(number,divisor)

参数说明：

number：被除数，可以为单元格也可以为具体某数值

divisor：除数，可以为单元格也可以为具体某数值

示例：

计算A2/A1的余数（整除）：=MOD(A2,A1) —— 结果返回：0

计算A3/A2的余数（不整除）：=MOD(A3,A2) —— 结果返回：1

INT

含义：将单元格内数值向下取整的函数（取整）

表达式：=INT(number)

参数说明：number可以为单元格，也可以为具体数值

示例：

将1.5向下取整：=INT(1.5) —— 结果返回：1

将-2.5向下取整得到的结果：=INT(-2.5) —— 结果返回：-3

ROUND

含义：返回按指定的小数位数四舍五入向上取整的结果

表达式：=ROUND(number,num_digits)

参数说明：

number：数值，可以为单元格也可以为具体数字

num_digits：小数点后的位数

示例：

将1.565四舍五入取整，保留两位小数：=ROUND(1.565,2) —— 结果输出1.57

FLOOR

含义：将数字向下舍入到最接近的整数，或最接近的指定基数的倍数，若number为正数，significance为负数，则返回错误值#NUM!

表达式：=FLOOR(number,significance)

参数说明：

number可以为单元格，也可以为具体数字

significance为基数，要求取舍数字为其的倍数

示例：

将3.6向下取舍，使其等于2的倍数 =FLOOR(3.6,2) —— 结果返回：2

将3.6向下取舍，使其等于1的倍数 =FLOOR(3.6,1) —— 结果返回：3

将3.6向下取舍，使其等于-1的倍数 =FLOOR(3.6,-1) —— 结果返回：#NUM!

ROUNDUP

含义：向上舍入数字

表达式：=ROUNDUP()

ROUNDDOWN

含义：向下舍入数字

表达式：=ROUNDDOWN()

RAND

含义：返回0~1之间的随机数，左闭右开，满足正态分布

表达式：=RAND()

RANDBETWEEN

含义：返回左闭右开值之间的一个随机整数

表达式：=RANDBETWEEN(bottom,top)

参数说明：bottom最小值和top最大值可以为单元格的值，也可以为具体数值

示例：

返回10~20之间随机数：=RANDBETWEEN(10,20) —— 结果返回：12

VAR

含义：估算基于给定样本的方差

表达式：=VAR(number1,number2,...)

示例：

计算A1-A3的方差：=VAR(A1:A3) —— 结果返回：1

STDEV

含义：估算基于给定样本的标准偏差

表达式：=STDEV(number1,number2,...)

参数说明：对应于总体中的样本的数字参数

示例：

计算A1-A3的标准差：=STDEV(A1:A3) —— 结果返回：1

计算1,3,5,7,8的标准差：=STDEV(1,3,5,7,8) —— 结果返回：2.863564

SQRT

含义：返回数值的算数平方根

表达式：=SQRT(number)

示例：

计算数字9的算数平方根：=SQRT(9) —— 结果返回：3

计算数字2的算数平方根：=SQRT(2) —— 结果返回：1.414214

SKEW

含义：偏度，用来体现某一分布相对其平均值的不对称程度

表达式：=SKEW(number1,number2,...)

示例：

查看A1-A3的偏度：=SKEW(A1:A3) —— 结果返回：0

查看1,3,5,7,8的偏度：=SKEW(1,3,5,7,8) —— 结果返回：-0.30663

KURT

含义：返回一组数据的峰值

表达式：=KURT(number1,number2,...)

示例：

查看1,3,5,7,8的峰值：=KURT(1,3,5,7,8) —— 结果返回：-1.54372

QUARTILE

含义：返回一组数据的四分位点

表达式：=QUARTILE(array,quart)

参数说明：

array：需要求得四分位数值的数组或数字型单元格区域

quart：决定返回的四分位值，可以为以下值

0：最小值（0%）

1：第一个四分位数（第25个百分点值）

2：中分位数（第50个百分点值）

3：第三个四分位数（第75个百分点值）

4：最大值（100%）

示例：

查看A1-A3的0：=QUARTILE(A1:A3,0) —— 结果返回：1

查看A1-A3的1：=QUARTILE(A1:A3,1) —— 结果返回：1.5

查看A1-A3的2：=QUARTILE(A1:A3,2) —— 结果返回：2

查看A1-A3的3：=QUARTILE(A1:A3,3) —— 结果返回：2.5

查看A1-A3的4：=QUARTILE(A1:A3,4) —— 结果返回：3