EXCEL Function Part II Chapter 3 —— 用函数统计和汇总数据

---

typora-copy-images-to: EXCEL FUNCTION PART II

Chapter 3 用函数统计和汇总数据

---

SUMPRODUCT 函数，按条件汇总数据

• SUM IF 只能设置一个求和条件,COUNTIF也只能设置1个计数条件
• 2007版本以上可以使用SUMIFS & COUNTIFS 函数解决
• 如果只为SUMPRODUCT 函数设置一个参数，SUMPRODUCT(A2:A6)则其功能等同于SUM

=  SUMPRODUCT(| 参数1引用区域 |，| 参数2引用区域 |)
# 可以多个引用区域
# 必须保证每个参数包含的数据个数相同切行列数相等
# 先求积，再求和
= SUMPRODUCT((条件1区域=条件)+0，(条件2区域=条件)+0，-----，求和区域)
# 按条件求和，可以有多个条件
# 加0的目的是为了转化逻辑值为数值，参与计算
= SUMPRODUCT(A2:A11=E2)+0,(B2:B11=F2)+0,C2:C11)
# 对参数的顺序没有要求

• 使用运算符合并多个求和条件

  = SUMPRODUCT((A2:A11=E2)*(B2:B11=F2),C2:C11)

• 条件计数也可以用SUMPRODUCT函数解决

= SUMPRODUCT((B2:B11>=60)+0)

• 多条件的计数

` = SUMPRODUCT((B2:B11>=60)*(C2:C11=“女”))

• SUMIFS 与 COUNTIFS 函数的对应参数只能是单元格区域，而不能是公式以及数据常量

# 依据考试分数为成绩评定名次
= SUMPRODUCT(($H$2:$H17>H2)+0)+1
# 先计算不等式取得逻辑值
# 转化为数值
# H2是相对引用，随着下拉填充而改变

• 多条件计数的案例：求计算同班成绩排名

FREQUENCY函数，分区间统计数值个数

• 解决区间统计数值个数的问题可以用COUNTIF函数

= COUNTIF(B2:B10,">60") - COUNTIF(B2:B10,">80")
OR = COUNTIFS(B2:B10,">60",B2:B10,"<=80")
OR = SUMPRODUCT((B2:B10>60)*(B2:B10<=80))

• 如果要统计的数据区间不止一个，则需要使用FREQUENCY函数

= FREQUENCY(B2:B10,E2:E5)
# 需要先选中目标返回单元格区域
# 输入公式后按<Ctrl+Shift+Enter>组合输入
= FREQUENCY(| 数据源 |，| 分段点 |)
# 分段点中间区段，函数将统计大于前1个段点，且小于或等于当前段点的数值个数
# FREQUENCY函数对参数的行列没有限制，不过该函数只返回一列垂直的数组
# FREQUENCY函数对参数的数据排序方式也没有要求，分段点的排列也没有要求
# 建议对分段点数值进行升序的排列

• FREQUENCY函数会自动忽略非数值的数据，不可统计非数值类型的数据

• 段点可以修正，以符合统计需求

• 多行列的分段点排列规律： 先行后列
  

• FREQUENCY 函数允许重复的分段点出现

• FREQUENCY 函数的其他应用

# 将FREQUENCY函数的结果显示在一行中
= TRANSPOSE(FREQUENCY(A2:A12,C2:C5))
# TRANSPOSE 是专门用于行列转换的函数，效果等同于选择性粘贴中的转置命令

# 求单独某个区间的数据个数
= INDEX(FREQUENCY(A2:A12,{60;80}),2)
# 此公式按照{60;80}两个分段点来计算，得出3个区间的数据个数
# 3个数据中，第2个区间个数是要完成的统计，用INDEX返回数值

• 统计不重复的“数值”个数，数据区域升序排列后，同时设置为FREQUENCY函数的两个参数

= SUMPRODUCT(--(FREQUENCY(A2:A12,A2:A12)>0))
# 先用FREQUENCY函数从数据中找出不为零的频率返回值
# 返回值与0进行比较，返回一系列True、False的数组
# --把返回值转变为数值格式
# 用SUMPRODUCT函数公式进行条件求和

• 统计不重复的“数据”个数
  

= SUMPRODUCT(--FREQUENCY(MATCH(A2:A11,A2:A11,0),MATCH(A2:A11,A2:A11),0)>0)
# MATCH函数返回1个数据第一次出现的位置，所以重复的数据，返回值也是重复的
# MATCH函数的第3参数0，意味着完全等于第1参数的值
# 后面的顺序和上一个例子一致

• 求足球队连续赢球的最多场次
  

= MAX(FREQUENCY(IF(B2:B13=3,ROW(2:13),""),IF(B2:B3<>3,ROW(2:13),"")))
# IF 函数是为了得到两个辅助列
# 以C列为FREQUENCY第1参数，D列为分段点，求频率
# 得到的返回值，求最大值，则为所求函数

• 求连续一段时间不是晴天的最大天数
  

• 按中国式排名规则计算名次（同名次只计数1次）

• 重要规律，如下图：分段点中不重复的数值有多少个，FREQUENCY函数返回的结果中就有多少个大于0的数值
  

= SUM(--FREQUENCY($B$W:$B$17,IF($B$2:$B$17>=B2,$B$2:$B$17))>0)
# 步骤一：逐步比对锁定单元区域与B2数值的比较，如果大于，返回该数值，否则返回False，得到一个数组
# 将锁定单元区域和上一步计算得到的数组作为第1、第2参数，用FREQUENCY函数返回一个数组
# 逐步比对返回TRUE、FALSE数组
# --转变为数值格式
# 求和得到名次

TRIMMEAN函数，去极值之后求平均值

• 当去掉的极值不是一个的时候，普通方法会比较繁琐

= TRIMMEAN(B2:M2),1/3
= TRIMMEAN(| 数据区域 |，| 去除极值个数/COUNT(数据区域) |)
# 如果第2参数为单数，则，函数会自动舍去小数部分

SUBTOTAL函数，汇总筛选货隐藏状态下的数据

• 分类汇总可以执行什么计算，SUBTOTAL 函数就能执行什么计算，包括标准差、总体标准差、方差、总体方差等等
  

• SUBTOTAL函数的第一参数

• SUBTOTAL 和 SUM、AVERAGE等函数的区别，如果对数据执行了筛选命令，则SUBTOTAL函数只对筛选后得到的数据进行汇总和计算。

• SUBTOTAL函数第一参数设置为"9" or "109"不同点在于计算时，是否让隐藏行中的数据参与计算，1**的参数会忽略，而1到11的设置不会忽略。
  

• 注意点：SUBTOTAL函数金支持行方向的隐藏统计，列区域隐藏不会忽略

= SUBTOTAL(| 汇总方式 |，| 数据区域1 |，| 数据区域2 |......)

# 使用SUBTOTAL函数生成不间断的序号
= SUBTOTAL(103,B$2:B2)*1
# 第1参数103会将隐藏行忽略，重新计数
"*1"的作用在于，使得EXCEL把最后1行当做普通行，而不是汇总行，也可以使用+0，-0