Excel重新踩坑6：工作实战总结之根据筛选条件求平均成绩

一、前言：

• 这个博客的实战场景：给了一组学生数据，这些数据中，有全市20个社区，1-9年级的学生各科成绩。要求按照各社区统计1-9年级的所有学生各科平均值。下面首先介绍会用到的一些函数，然后再简单说明实战过程。

二、项目实战过程中学到的一些函数和功能：有些学习了没有用到，也记录了下来。

1、sumif（区域1，条件，区域2）函数：

• sumif（区域1，条件，区域2）函数中，区域1是条件对比时筛选的区域，条件是区域1中某个筛选项，区域2是求和区域，使用这个公式，会把区域1中与条件相同的行对应在区域2中的项累加起来。应用范例：a列是金额，b列是与a列对应的金额类型，就可以通过该函数，算出不同金额类型的金额之和。

2、SUBTOTAL(function_num, ref1, [ref2], …)：

• 可以通过筛选生成汇总结果。其中第一个参数是功能参数，SUBTOTAL函数支持11种函数，分别为Average（平均值）、Count（非空值单元格计数）、CountA（非空值单元格计数，包括字母）、Max（最大值）、Min（最小值）、Product（乘积）、Stdev（标准偏差值，忽略逻辑值和文本）、Stdevp（标准偏差值，包括逻辑值和文本作为数字参与计算）、Sum（求和）、Var（给定样本的方差，忽略逻辑值和文本）、Varp（给定样本总体的方差，包括逻辑值和文本作为数字参与计算）。SUBTOTAL函数是Excel中一个非常实用且强大的函数，它可以帮助用户轻松处理筛选和手动隐藏后的数据，提高工作效率和数据处理能力。但是要注意功能代码要给对，例如在一个包含10个数据的单元格区域中，假设初始总和为50。当隐藏其中3个数据后，使用代码“9”进行求和，结果仍为50；而使用代码“109”进行求和，结果则变为可见的7个数据的总和。

3、切片器：超级表的一种功能

• 切片器的使用前提（结构化表格也叫做超级表）：在插入选项中有一个叫“表格”的功能，他的作用是将数据区域转换为结构化表格，结构化表格的好处是
  1.向表格添加新行或列时，数据会自动纳入表格范围，相关公式、格式和筛选会自动应用到新数据。
  2.统一表格格式，便于整体修改格式。
  3.表格标题会自动出现筛选剪头，还能多列排序。
  4.通过“表格设计”添加汇总行，可以选择汇总方式（求和，求平均等）快速汇总数据，注意要修改汇总方式，要在创建了最后一行汇总行之后，点到最后一行对应表格上，然后出现下拉箭头，自己选择汇总方式。
• 只有创建好超级表的前提下，选中超级表区域，才能在插入中使用切片器。

4、数据清洗问题：

• 1.错误数据筛选清除：在这个过程中我发现错误项通过筛选工具显示不出来，出现公式报错筛选的化可以选择公式当中的公式审核去定位错误发生的位置。
  
• 2.文字数据筛选常用find定位，mid提取。FIND(find_text, within_text, [start_num])：返回的是要查找目标的位置，MID(text, start_num, num_chars)，获取目标中一部分内容。

5、filter（区域，条件，否则）：

• 可以筛选生成数组，其中第一个参数作用是告诉函数你要从哪个区域筛选，第二个参数是说明你的筛选条件，多个条件的情况下and用*代替，or用+代替。最后一个参数是说如果筛选区域中没有满足筛选条件的时候就返回的结果。

二、实战过程：

• 0、给了一组学生数据，这些数据中，有全市20个社区，1-9年级的学生各科成绩。要求按照各社区统计1-9年级的所有学生各科平均值，如下所示是原始数据表：
  
• 2、制作目标表格（确定要获取的数据格式和规范），如下所示，将所有年级按照9门课程设计表格，至于有的年级没有对应课程，可以不用管，目的是为了公式能够适用所有年级。
  
• 3、获取“数据清洗表”（数据规则化），通过FIND，定位文字位置，通过MID，获取对应内容，这样子就能够先把某些规则数据提取出来，首先让筛选项变成可以统一筛选的规则数据，如下：
  
• 4、思路一：结合“目标表格”，通过筛选函数快速筛选出需要的数组，然后对筛选出来的数组使用求平均值函数得到结果，如下，在水果社区对应的语文数据中可以使用函数：=AVERAGE(FILTER(数据清洗表!E$4:E$7042,(数据清洗表!$B$4:$B$7042=目标表格!$B$4)\ast (数据清洗表!$C$4:$C$7042=1),0))，其中FILTER(数据清洗表!E$4:E$7042,(数据清洗表!$B$4:$B$7042=目标表格!$B$4)\ast (数据清洗表!$C$4:$C$7042=1),0)函数的意思是在数据清洗表中获取语文成绩所在的E列数据，然后第一个筛选条件是数据清晰表中社区和目标表社区一致都是水果社区，并且是1年级的成绩，筛选出来，不满足删选条件的用0代替，这样就得到了一列满足以上两个条件的数据，然后用average函数求平均就好了。
  
• 5、思路二：通过subtotal动态求平均，然后手动筛选，一组一组的获取，但效率太慢了。