《Excel數(shù)據(jù)處理與可視化》教案21-數(shù)據(jù)統(tǒng)計（二）

《Excel數(shù)據(jù)處理與可視化》課程教案授課題目數(shù)據(jù)統(tǒng)計(二)授課類型理實一體教學(xué)目標(biāo)1、掌握常用的數(shù)據(jù)統(tǒng)計方法2、會根據(jù)應(yīng)用場景選擇合適的函數(shù)解決具體問題重點1、常用數(shù)據(jù)統(tǒng)計的使用2、根據(jù)場景靈活選用函數(shù)難點1、根據(jù)場景靈活選用函數(shù)教學(xué)過程一同一單元格中有多個姓名，統(tǒng)計總?cè)藬?shù)入公式“=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1”，按Enter鍵執(zhí)行計算二、員工姓名和業(yè)績在同一單元格中，統(tǒng)計業(yè)績最大值公式“=MAX((SUBSTITUTE(B2,ROW($1:$100),)<>B2)*ROW($1:$100))”，按Ctrl+Shift+Enter組合鍵執(zhí)行計算三巧用ROW函數(shù)統(tǒng)計前N名數(shù)據(jù)求前3名業(yè)績和的公式為“=SUM(LARGE(B2:B37,{1,2,3}))”。前10名業(yè)績和的公式為“=SUM(LARGE(B2:B37,ROW(1:10)))”，按Ctrl+Shift+Enter組合鍵執(zhí)行計算。前10名平均業(yè)績的公式為“=AVERAGE(LARGE(B2:B37,ROW(1:10)))”，按Ctrl+Shift+Enter組合鍵執(zhí)行計算。四排除重復(fù)項統(tǒng)計每月缺勤人數(shù))-2))，按Ctrl+Shift+Enter組合鍵執(zhí)行計算五巧用動態(tài)區(qū)域統(tǒng)計累計情況輸入“=C2/B2”，按Enter鍵執(zhí)行計算，再將公式向下填充，即可計算每個月的完成計劃情況（2）累積計劃。在E2單元格中輸入公式“=SUM($B$2:B2)”，按Enter鍵執(zhí)行計算，再將公式向下填充，即得本月及之前月的累積計劃累積銷量。累積完成計劃情況。累積完成計劃情況，方法同“當(dāng)月完成計劃情況”，在G2單元格中輸入公式“=F2/E2”，按Enter鍵執(zhí)行計算，再將公式向下填充，即得累積完成計劃情況。六根據(jù)銷售額分段提成標(biāo)準(zhǔn)計算累進提成（1）添加輔助列。增加“區(qū)間最低值”與“區(qū)間提成”兩個輔助列，區(qū)間提成是用1000乘以提成率計算而來的。（2）公式計算。按Enter鍵執(zhí)行計算，再將公式向下填充，可計算出所有銷售額的累進提成，如圖2129所示。七統(tǒng)計代碼含有指定數(shù)字的商品銷售總量公式“=SUMPRODUCT(ISNUMBER(SEARCH("4????",A2:A12))*B2:B12)”，按Enter鍵執(zhí)行計算，即可統(tǒng)計出首位為4的商品銷售總量。八利用LEN+SUBSTITUTE函數(shù)組合計算員工參與項目數(shù)公式“=(LEN(B2)-LEN(SUBSTITUTE(B2,"、",))+1)*(B2<>"")，按Enter鍵執(zhí)行計算，得第1位員工參與項目數(shù)量，再將公式向下填充，得所有員工參與的項目數(shù)九按不同字體或背景顏色統(tǒng)計數(shù)值【實現(xiàn)方法】1）不用顏色數(shù)值統(tǒng)計（1）建立名稱。單擊“公式”→“定義名稱”，在彈出的“新建名稱”對話框的“名稱”文本框中輸入“ztcolor”，“引用位置”文本框中輸入公式“=GET.CELL(24,B2)&T(NOW())”，單擊“確定”按鈕。（2）提取字體顏色。在C2單元格中輸入公式“=ztcolor”，按Enter鍵執(zhí)行計算，再將公式向下填充，即得B列數(shù)字顏色值。定義名稱提取字體顏色把不同顏色的數(shù)字各取其一，粘貼到E列，同時在F2單元格中輸入公式“=ztcolor”，按Enter鍵執(zhí)行計算，再將公式向下填充，即得E列數(shù)字顏色值。取顏色值（3）統(tǒng)計。在G2單元格中輸入公式“=SUMIF($C$2:$C$9,F2,$B$2:$B$9)”，按Enter鍵執(zhí)行計算，公式向下填充，即得不同顏色數(shù)值的和；在H2單元格中輸入公式“=AVERAGEIF($C$2:$C$9,F2,$B$2:$B$9)”，按Enter鍵執(zhí)行計算，公式向下填充，即得不同顏色數(shù)值的平均值。不同顏色平均值2）不同背景數(shù)值統(tǒng)計（1）建立名稱。單擊“公式”→“定義名稱”，在彈出的“新建名稱”對話框的“名稱”文本框中輸入“bjcolor”，“引用位置”文本框中輸入公式“=GET.CELL(63,B2)&T(NOW())”，單擊“確定”按鈕。（2）提取背景顏色。在C2單元格中輸入公式“=bjcolor”，按Enter鍵執(zhí)行計算，再將公式往下填充，即得B列背景顏色值。（3）統(tǒng)計。把不同背景顏色各取其一，粘貼到E列，同時在F2單元格中輸入公式“=bjcolor”，按Enter鍵執(zhí)行計算，再將公式向下填充，即得E列背景顏色值。在G2單元格中輸入公式“=SUMIF($C$2:$C$9,F2,$B$2:$B$9)”，按Enter鍵執(zhí)行計算，公式向下填充，即得不同背景顏色數(shù)據(jù)的和；在H2單元格中輸入公式“=AVERAGEIF定義背景顏色名稱得背景顏色值($C$2:$C$9,F2,$B$2:$B$9)”，按Enter鍵執(zhí)行計算，再將公式向下填充，即得不同背景顏色數(shù)值的平均值。作業(yè)數(shù)據(jù)統(tǒng)計練習(xí)教學(xué)小結(jié)本節(jié)知識：同一單元格中有多個姓名，統(tǒng)計總?cè)藬?shù)員工姓名和業(yè)績在同一單元格中，統(tǒng)計業(yè)績最大值巧用ROW函數(shù)