第05讲（第5章）-1Excel数据统计分析函数

模块一：EXCEL基础第五章统计函数与数据分析知识回顾数据筛选：三种数据筛选方式排序与分类汇总：按字段分类，并统计汇总单元格引用：相对地址、绝对地址、混合地址、跨表引用、跨工作簿引用名称引用：名称定义、修改和引用常用函数：数值、日期、字符串目录页CONTENTSPAGE1.条件计数2.条件求和3.条件均值4.排名统计Countif/countifsSum/sumif/sumifsAverageif/averageifsRoundRankMax/MinLarge/SmallEXCEL知识点：常用统计函数统计函数与数据分析分段统计四舍五入函数最值函数排名统计条件计数1统计函数——计数指定区域中符合指定条件的单元格计数。countif（range，criteria）参数：range

要计算其中非空单元格数目的区域；参数：criteria

以数字、表达式或文本形式定义的条件。例1：计算语数英成绩在60分以下的学生人数。函数表达式：=COUNTIF(D2:D101,"<60")区域条件表达式1.条件计数函数：countif函数统计函数——计数统计多个区域中满足给定条件的单元格的个数。COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,…)criteria_range1为第一个需要计算其中满足某个条件的单元格数目的单元格区域（简称条件区域）criteria1为第一个区域中将被计算在内的条件（简称条件），其形式可以为数字、表达式或文本同理，criteria_range2为第二个条件区域，criteria2为第二个条件，依次类推。最终结果为多个区域中满足所有条件的单元格个数。例2：计算语数英成绩在60-69分之间的学生人数。=COUNTIFS(D2:D101,">=60",D2:D101,"<=69")区域1条件1条件2区域22.计数函数：Countifs函数统计函数——计数实训任务一：条件计数函数打开工作簿“01：统计分析应用.xlsx”；完成工作表“1.1”中的计算。5-1统计分析应用”8分钟统计函数——最值指定区域中最小数：MIN（range）指定区域中最大数：MAX（range）参数：range要计算最值的区域。例3：查找语数英成绩最高分，最低分。函数表达式：=MAX(D2:D101)函数表达式：=MIN(D2:D101)区域3.最值函数：Min/Max函数统计函数——四舍五入返回某个数字按指定位数取整后的数字。ROUND(number，num_digits)Number：需要进行四舍五入的数字。Num_digits：指定的位数，按此位数进行四舍五入。例4：计算语数英成绩平均分，并保留小数点1位。函数表达式：=ROUND(AVERAGE(D2:D101),1)取舍的数字位数4.取舍函数：Round函数统计函数——实训任务实训任务二：最值、均值、取舍打开工作簿“01：统计分析应用.xlsx”；完成工作表“1.1”中的计算。8分钟条件求和2统计函数——汇总根据指定条件对若干单元格求和。SUMIF(range,criteria,sum_range)Range：为用于条件判断的单元格区域。Criteria：为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。sum_range：是需要求和的实际单元格。例5：求各班学生语数英总和。函数表达式：=SUMIF($A$2:$A$101,"信息1“/$I13,E2:E101)=SUMIF($A$2:$A$101,$I13,$E$2:$E$101)判断区域条件实际区域1.单条件求和函数：Sumif函数统计函数——汇总根据多个指定条件对若干单元格求和。SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)1）criteria_range1为计算关联条件的第一个区域。2）criteria1为条件1，条件的形式为数字、表达式、单元格引用或者文本，可用来定义将对criteria_range1参数中的哪些单元格求和。3）criteria_range2为用于条件2判断的单元格区域。4）criteria2为条件2和3）均成对出现。最多允许127个区域、条件对，即参数总数不超255个。5）sum_range是需要求和的实际单元格。=SUMIFS($E:$E,$A:$A,$I19,$D:$D,"男")例6：求各班男女生语文分数的总和。实际区域条件1区域条件2区域条件1条件22.多条件求和函数：Sumifs函数统计函数——汇总实训任务三：单条件求和函数打开工作表“01：统计分析应用.xlsx”；完成工作表“2.1-2.2”中的计算。12分钟条件平均值3平均值计算AverageifAverageifs条件平均值函数平均值计算1.单条件平均值函数：Aaverageif函数根据条件指定的单元格求平均值。AVERAGEIF(range,criteria,average_range)Range：为用于条件判断的单元格区域。Criteria：为确定哪些单元格将求平均值的条件，其形式可以为数字、表达式或文本。average_range：是需要求平均值的实际单元格。例7：求各班语数英的平均分，并保留1位小数。=ROUND(AVERAGEIF($A:$A,$I25,E:E),1)条件区域条件实际区域平均值计算2.多条件平均值函数：Aaverageifs函数利用多重标准对满足条件的单元格求平均值。AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)同sumifs函数例8：求各班男女生语文分数的平均分，并保留1位小数。=AVERAGEIFS($E:$E,$A:$A,$I31,$D:$D,"男")=ROUND(AVERAGEIFS($E:$E,$A:$A,$I31,$D:$D,"男"),1)实际区域条件区域1条件区域2条件1条件2平均值计算实训任务四：单条件平均值函数打开工作簿“01：统计分析应用.xlsx”；完成工作表“3.1-3.2”中的计算。15分钟4排名统计排名统计Rank函数Large函数Small函数排名统计求某一个数值在某一区域内的排名。RANK(number，ref，[order])number为需要求排名的那个数值或者单元格名称（单元格内必须为数字）；ref为排名的参照数值区域；order的为0和1，默认不用输入，得到的就是从大到小的排名，若是想求倒数第几，order的值请使用1。例：求每个学生的排名。函数表达式：=RANK(D2,D:D)排名的数字参照数值区域1.排名函数：Rank函数排名统计返回数据集中的第K个最大值。LARGE(array,k)array为需要找到第k个最大值的数组或数字型数据区域。k为返回的数据在数组或数据区域里的位置(从大到小)。说明：LARGE函数计算最大值时忽略逻辑值TRUE和FALSE以及文本型数字例10：求十个最高分。=LARGE(排名函数!D:D,最值!A2)找的区域排名2.第K个最大值函数：Large函数排名统计返回数据集中的第K个最小值。SMALL(array,k)array为需要找到第k个最小值的数组或数字型数据区域。k为返回的数据在数组或数据区域里的位置(从小到大)。说明：函数计算最小值时忽略逻辑值TRUE和FALSE以及文本型数字。例：求十个最低分。=SMALL(排名函数!D:D,最值!A2)找的区域排名3.第K个最小值函数：Small函数排名统计实训任务五：排名第K个最值函数打开工作簿“01：统计分析应用.xlsx”；完成工作表“4.1-4.2”中的计算。10