《Excel數(shù)據處理與可視化》教案10-統(tǒng)計函數(shù)

《Excel數(shù)據處理與可視化》課程教案授課題目統(tǒng)計函數(shù)授課類型理實一體教學目標1、掌握MODE.MULT函數(shù)、COUNTIF函數(shù)、“IFS”結尾、COUNTIFS函數(shù)、TRIMMEAN函數(shù)等統(tǒng)計函數(shù)的用法；2、會靈活選用統(tǒng)計函數(shù)重點1、統(tǒng)計函數(shù)的選用常識2、多個函數(shù)的組合難點1、多個函數(shù)的組合教學過程一利用MODE.MULT函數(shù)統(tǒng)計出現(xiàn)最多的數(shù)字（一）公式“=MODE.MULT($A$2:$G$12)”，按Enter鍵執(zhí)行計算，即可得出現(xiàn)次數(shù)最多的數(shù)值。二、利用MODE.MULT函數(shù)統(tǒng)計出現(xiàn)最多的數(shù)字（二）公式“=IFERROR(INDEX(MODE.MULT($A$1:$E$10),ROW(A1)),"")”，按Enter鍵執(zhí)行計算，即得出現(xiàn)次數(shù)最多的數(shù)值，三、利用COUNTIF函數(shù)給眾多班級中相同班級的學生編號公式“=COUNTIF($B$2:B2,B2)”，按Enter鍵執(zhí)行計算，再將公式向下填充就得到所有班級的學生編號。四、5個常用的“IFS”結尾的多條件統(tǒng)計函數(shù)1）COUNTIFS函數(shù)：多條件計數(shù)語法：COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],…)中文語法：COUNTIFS(條件區(qū)域1,條件1,[條件區(qū)域2,條件2],…)●條件區(qū)域1：必需，在其中計算關聯(lián)條件的第1個區(qū)域?！駰l件1：必需，條件的形式為數(shù)字、表達式、單元格引用或文本。它定義了要計數(shù)的單元格范圍。例如，條件可以表示為32、">32"、B4、"apples"或"32"?！裨诒臼纠?，要統(tǒng)計市場1部業(yè)績分高于10的女高級工程師人數(shù)，有以下4個條件對?！駰l件區(qū)域1：市場部；條件1：市場1部?！駰l件區(qū)域2：業(yè)績分；條件2：高于10?！駰l件區(qū)域3：性別；條件3：女。●條件區(qū)域4：職稱；條件4：高級工程師。所以，公式為“=COUNTIFS(A2:A21,"市場1部",E2:E21,">=10",C2:C21,"女",D2:D21,"高級工程師")”。2）SUMIFS函數(shù)：多條件統(tǒng)計和語法：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)中文語法：SUMIFS(統(tǒng)計和的數(shù)值區(qū)域,條件區(qū)域1,條件1,[條件區(qū)域2,條件2],…)●統(tǒng)計和的數(shù)值區(qū)域：必需，要計算和的一個或多個單元格，其中包含數(shù)字或包含數(shù)字的名稱、數(shù)組、引用。●條件區(qū)域1、條件區(qū)域2等：條件區(qū)域1必需，后續(xù)條件區(qū)域是可選的。在其中計算關聯(lián)條件的1～127個區(qū)域?！駰l件1、條件2等：條件1必需，后續(xù)條件是可選的。形式為數(shù)字、表達式、單元格引用或文本的1～127個條件，用來定義要統(tǒng)計和的單元格。例如，條件可以表示為32、"32"、">32"、"蘋果"或B4。在本示例中，要統(tǒng)計市場1部女高級工程師業(yè)績總分，有以下3個條件對?！駰l件區(qū)域1：市場部；條件1：市場1部。●條件區(qū)域2：性別；條件2：女?！駰l件區(qū)域3：職稱；條件3：高級工程師。3）AVERAGEIFS函數(shù)：多條件統(tǒng)計平均值語法：AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)中文語法：AVERAGEIFS(統(tǒng)計平均值區(qū)域,條件區(qū)域1,條件1,[條件區(qū)域2,條件2],…)●統(tǒng)計平均值區(qū)域：必需，要計算平均值的一個或多個單元格，其中包含數(shù)字或數(shù)字的名稱、數(shù)組、引用?！駰l件區(qū)域1、條件區(qū)域2等：條件區(qū)域1必需，后續(xù)條件區(qū)域是可選的。在其中計算關聯(lián)條件的1～127個區(qū)域?！駰l件1、條件2等：條件1必需，后續(xù)條件是可選的。形式為數(shù)字、表達式、單元格引用或文本的1～127個條件，用來定義要計算平均值的單元格。例如，條件可以表示為32、"32"、">32"、"蘋果"或B4。在本示例中，要統(tǒng)計市場1部女高級工程師平均業(yè)績分，有以下3個條件對。●條件區(qū)域1：市場部；條件1：市場1部。●條件區(qū)域2：性別；條件2：女?！駰l件區(qū)域3：職稱；條件3：高級工程師。所以，公式為“=AVERAGEIFS(E2:E21,A2:A21,"市場1部",C2:C21,"女",D2:D21,"高級工程師")”。4）MAXIFS函數(shù)：多條件統(tǒng)計最大值語法：MAXIFS(max_range，criteria_range1，criteria1，[criteria_range2，criteria2],…)中文語法：MAXIFS(取最大值的單元格區(qū)域,條件區(qū)域1,條件1,[條件區(qū)域2,條件2],…)●取最大值的單元格區(qū)域：必需，要取最大值的一個或多個單元格，其中包含數(shù)字或數(shù)字的名稱、數(shù)組、引用。●條件區(qū)域1、條件區(qū)域2等：條件區(qū)域1必需，后續(xù)條件區(qū)域是可選的。在其中計算關聯(lián)條件的1～126個區(qū)域?！駰l件1、條件2等：條件1必需，后續(xù)條件是可選的。形式為數(shù)字、表達式、單元格引用或文本的1～126個條件，用來定義取最大值的單元格。例如，條件可以表示為32、"32"、">32"、"蘋果"或B4。在本示例中，要統(tǒng)計市場1部女高級工程師最高業(yè)績得分，有以下3個條件對。●條件區(qū)域1：市場部；條件1：市場1部?！駰l件區(qū)域2：性別；條件2：女。●條件區(qū)域3：職稱；條件3：高級工程師。5）MINIFS函數(shù)：多條件統(tǒng)計最小值語法：MINIFS(min_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)中文語法：MINIFS(取最小值的單元格區(qū)域,條件區(qū)域1,條件1,[條件區(qū)域2,條件2],…)●取最小值的單元格區(qū)域：必需，要取最小值的一個或多個單元格，包含數(shù)字或數(shù)字的名稱、數(shù)組、引用?！駰l件區(qū)域1、條件區(qū)域2等：條件區(qū)域1必需，后續(xù)條件區(qū)域是可選的。在其中計算關聯(lián)條件的1～126個區(qū)域?！駰l件1、條件2等：條件1必需，后續(xù)條件是可選的。形式為數(shù)字、表達式、單元格引用或文本的1～126個條件，用來定義取最小值的單元格。例如，條件可以表示為32、"32"、">32"、"蘋果"或B4。在本示例中，要統(tǒng)計市場1部女高級工程師最低業(yè)績得分，有以下3個條件對?！駰l件區(qū)域1：市場部；條件1：市場1部?！駰l件區(qū)域2：性別；條件2：女?！駰l件區(qū)域3：職稱；條件3：高級工程師。五、利用SUM+COUNTIF函數(shù)組合統(tǒng)計不重復值的個數(shù)在E2單元格中輸入公式“=SUM(1/COUNTIF(B2:B16,B2:B16))”，按Ctrl+Shift+Enter組合鍵執(zhí)行計算，即可統(tǒng)計出銷售的商品種數(shù)所示。六、利用FREQUENCY函數(shù)分段計數(shù)輸入公式“=FREQUENCY(B2:B16,{60,70,80,90}-0.1)”，按Ctrl+Shift+Enter組合鍵執(zhí)行計算，即可計算每個分數(shù)段的人數(shù)。七、利用COUNTIFS函數(shù)統(tǒng)計滿足多個條件的單元格數(shù)量在F2單元格中輸入公式“=COUNTIFS($A$2:$A$135,G$1,$C$2:$C$135,">="&$F2)”，按Enter鍵執(zhí)行計算，再將公式向下、向右填充，即可得到每個班級各分數(shù)段的人數(shù)。八、利用TRIMMEAN函數(shù)計算去掉最高分和最低分后的平均分，左側公式為“TRIMMEAN（B3:B8,2/9）”，表示在9個裁判員打分中，去掉一個最高分和一個最低分后計算平均分；右側公式為“TRIMMEAN(F3:F8,4/9)”，表示在9個裁判員打分中，去掉兩個最高分和兩個最低分后計算平均分。作業(yè)練習統(tǒng)計函數(shù)的使用教學小結本節(jié)知識：利用MODE.MULT函數(shù)統(tǒng)計出現(xiàn)最多的數(shù)字（