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

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