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

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