《Excel數據處理與可視化》教案10-統計函數

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