目录
17. 统计学相关的函数（相关系数）
1. sumproduct 数乘求和函数
①单条件数乘
在选定区域内，将数组间对应的元素相乘，并返回乘积之和
注：数值相乘只能 行*行 ，或 列*列，不能 行*列
=sumproduct（数组1,数组2,数组3，...）
e.g. 求下列所有产品的库存*金额
=sumproduct （B1:B3,C1:C3）= 35
| A1 | 库存 | 金额 |
| 手表 | 10 | 2 |
| 耳机 | 5 | 3 |
② 多条件求和
=Sumproduct（ (条件区域1=条件1) * (条件区域2=条件2) *.. , 求和区域 ）
=Sumproduct（求和区域 , (条件区域1=条件1) * (条件区域2=条件2) * .. ）
=Sumproduct（（求和区域）* (条件区域1=条件1) * (条件区域2=条件2)*.. ）
e.g. 求下表中性别为男，且学历为本科的工资总和
=sumproduct( ( A1:A13="男") * (C1:C13="本科") , G1:G13)
=sumproduct( G1:G13 , (A1:A13="男") * (C1:C13="本科") )
=sumproduct( （G1:G13） * (A1:A13="男") * (C1:C13="本科") )
③引申-去重后计数
=SUMPRODUCT(1/COUNTIF(A1：A25，A1：A25))
说明：即把A1到A25的单元格去重后，得出去重后的计数数字
2.round 舍入函数
按照指定的小数位，对数值四舍五入
=ROUND(数值or单元格, 取多少位的参数num)
参数num >=0 取小数点后几位or取整
参数num < 0 取小数点左侧前几位
e.g. =round(1234.45,2) =1234
e.g. =round(1234.45,-2) =1200
3.rank 排名函数
①基本用法
- 查找某数字的排名 =rank（待查找单元格or数字，查找范围，排序方式 选填）
- 注：排序方式 0为降序，1为升序
- 如不输入排序方式，默认为降序。即该公式为降序： rank（待查找单元格or数字，查找范围）
②排名引申用法- row函数
- 使用row函数，可求出该单元格所在的行数。
= row （单元格）或 = row（ ） row函数所在单元格不输入内容时，默认计算该单元格的行数
= 行数_num
- 可先将待排序列的第一个单元格选中 → 右键 - 排序 - 升序 →插入【序号】列，输入 = row( ) - 1 即该单元格的所在行数减1，就是它的序号
4.randbetween 随机函数
随机生成范围内的数字，可用于随机抽样
e.g. = randbetween(区间起点，区间终点）
引申用法 - 可用于生成随机字符，如随机生成男or女
=IF(RANDBETWEEN(1,2)=1,"男","女")
5.int 取整函数
向下取整为最接近的整数（舍尾）
= int （ 单元格or具体数字 ）
e.g. =int(4.623) = 4
6.stdev 标准差函数
计算标准差
=STDEV(数组1,数组2, ...)
7.subtotal函数
| 参数 | 对应函数 | 函数意义 | |
| 包括隐藏值 | 忽略隐藏值 | ||
| 1 | 101 | average | 均值 |
| 2 | 102 | count | 对数字单元格计数 |
| 3 | 103 | counta | 对非空单元格计数 |
| 4 | 104 | max | 最大值 |
| 5 | 105 | min | 最小值 |
| 6 | 106 | product | 乘积 |
| 7 | 107 | stdev | 样本标准差 |
| 8 | 108 | stdevp | 总体标准差 |
| 9 | 109 | sum | 对非空单元格求和 |
| 10 | 110 | var | 样本方差 |
| 11 | 111 | varp | 总体方差 |
8. sumif 条件求和函数
对指定条件的值，进行单条件求和
= sumif (包含指定条件的范围, 条件, 实际求和范围）条件如为逻辑表达式or字符型，要加双引号
e.g. 耳机一共有多少库存
= sumif ( A1:B5,A3,B1:B5) = 17
= sumif ( A1:B5,"耳机",B1:B5) = 17
| A1 | 库存 |
| 手表 | 10 |
| 耳机 | 5 |
| 耳机 | 12 |
| 水杯 | 2 |
9. sumifs 多条件求和函数
对指定条件的值，进行多条件求和
= sumifs (求和范围, 条件区域1, 定位点or定位字符1,条件区域2, 定位点or定位字符2, …)
e.g. 既是“耳机”，又大于10的一共有多少库存
=sumifs（B1:B5, A1:A5, A3, B1:B5，">10"） =12
=sumifs（B1:B5, A1:A5, "耳机", B1:B5，">10"）=12
10.count 计数函数
① count
计数 - 只针对（数字）内容的单元格
=COUNT( 待计数区域范围 )
② counta
计数- 针对所有（非空白）内容的单元格
=COUNTA ( 待计数区域范围 )
③ countblank
计数 - 只针对（空白）内容的单元格
=COUNTBLANK ( 待计数区域范围 )
11. countif 条件计数函数
- 在指定区域中,按指定条件,对单元格单条件计数
=COUNTIF（计数条件范围，条件） 条件如为逻辑表达式or字符型，要加双引号
e.g1. =COUNTIF(A2:C8,"福建") 文本内容需加引号
=COUNTIF(A4:C9,">500") 计数，大于500的有多少个
=COUNTIF(A4:C9,">"＆500) 文本与数字中间，用连接符
- 可用于计算某单元格，在某列中出现了几次 如计算B10单元格，在B列中出现的次数
=COUNTIF(B:B,B10)
12. countifs 多条件计数函数
在指定区域中,按指定条件,对单元格 多条件计数
=COUNTIFS（计数条件范围1，定位点1，计数条件范围2，定位点2，...）
e.g. =COUNTIFS(B2:C8,G12，C3:C60,H11) 既是"白色"又是"T恤"的有多少个
13. ABS 绝对值函数
= ABS（单元格or数字） = （数值的绝对值）
e.g. = ABS ( -1111) = 1111
14. MODE 众数函数
得出数组范围中，出现频率最高的数值
= MODE ( 单元格的范围区间 ）
扩展：
- 返回单个众数：=MODE.SNGL（单元格的范围区间） 和mode（）作用效果一样
- 返回多个众数，即以数组形式返回众数的所有值： =MODE.MULT（单元格的范围区间）
扩展说明：如单元格的范围区间包含的数据为10，10，8，8，9，9，7
- MODE和MODE.SNGL返回区间范围内第一个出现的众数：10
- MODE.MULT返回数组{10,8,9}
15. POWER 幂指函数
用于幂乘计数
=power（底数，指数）
形式为 power（单元格 or 数字，数字）
16. datedif 时间差函数
- 注：该函数为隐藏函数，直接在单元格输入公式后回车即可
- 时间参数的字母，不区分大小写
- 所求日期记得加双引号，或直接选中包含了日期的单元格
表现形式 = datedif (开始日期，结束日期，时间参数)
时间参数： 月 "m" 年 "y" 日 "d"
e.g. 求2024/5/30和3月底之间的月份差
=DATEDIF("2022/3/31"，"2024/5/30"，"m")
=DATEDIF("2022/3/31",F5,"M")
17. 统计学相关的函数
1 概率分布函数
2 相关系数
相关系数 - 要点
- 相关系数的范围为(-1,1)，相关系数的绝对值越大，两种数据的相关程度越高。即绝对值大小表相关性，正负表变化方向
- 正相关：如相关系数>0，则两组数据是同方向变化的趋势
- 负相关：如相关系数<0，则两组数据是反方向变化的趋势，即数据a的值越大，数据b的值就越小
- 非线性相关：相关系数=0
用excel求相关系数
- 加载出数据分析功能：选项 - excel选项 - 加载项 → （转到） → 选中 （分析工具库）
- 运用数据分析功能： 数据 - 数据分析 → 相关系数 - 确定 → 选中要对比的数据区域，可包含表头 → 选中 （标志位于第一行） → 确定
- 得出相关系数矩阵，矩阵对角线都是1，对角线两侧的即为相关系数
18. AVERAGEIFS 条件均值函数
AVERAGE(average_range, criteria_range1,criteria1,criteria_range2,criteria2,...)
average_range:计算平均值的区域
criteria_range:判断条件的区域
criteria:指定的条件
484
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
