ECEL中統(tǒng)計與計算類函數(shù)

商務(wù)數(shù)據(jù)分析基礎(chǔ)EXCEL中統(tǒng)計與計算類函數(shù)3.2.1統(tǒng)計類函數(shù)平均值函數(shù)1算術(shù)平均值：返回參數(shù)的平均值A(chǔ)VERAGE(number1,number2,...)2幾何平均值：返回正數(shù)數(shù)組或數(shù)值區(qū)域的幾何平均數(shù)GEOMEAN(number1,[number2],...)3調(diào)和平均值：返回一組正數(shù)的調(diào)和平均數(shù)HARMEAN(number1,[number2],...)1．平均值函數(shù)平均值是表示一組數(shù)據(jù)集中趨勢的量數(shù)，是反映數(shù)據(jù)集中趨勢的一項指標(biāo)3.2.1統(tǒng)計類函數(shù)1．平均值函數(shù)【例3-1】某企業(yè)2018年上半年每個月的成本如圖3-11所示，現(xiàn)要求計算出該企業(yè)2018年上半年的平均值、幾何平均值和調(diào)和平均值，操作步驟如下。圖3-11某企業(yè)2018年上半年每個月成本明細3.2.1統(tǒng)計類函數(shù)1．平均值函數(shù)光標(biāo)定位于D4單元格，鼠標(biāo)單擊“插入函數(shù)”按鈕，彈出如圖3-12所示的“插入函數(shù)”對話框，選擇類別“統(tǒng)計”—“GEOMEAN”函數(shù)，單擊“確定”按鈕，彈出如圖3-13所示的“函數(shù)參數(shù)”對話框。光標(biāo)定位于number1右側(cè)的輸入框中，按住鼠標(biāo)左鍵選取EXCEL表中的B2:B7區(qū)域，再單擊“確定”按鈕，即計算得出“上半年成本幾何平均值”。同理，分別將光標(biāo)定位于D2單元格和D6單元格計算“上半年成本平均值”和“上半年成本調(diào)和平均值”圖3-12“插入函數(shù)”對話框圖3-13“函數(shù)參數(shù)”對話框3.2.1統(tǒng)計類函數(shù)2．計數(shù)函數(shù)在數(shù)據(jù)分析過程中，經(jīng)常需要統(tǒng)計選定區(qū)域內(nèi)數(shù)值型單元格的數(shù)目、空白單元格的數(shù)目、非空單元格的數(shù)目以及滿足某條件的單元格的數(shù)目03020104COUNT功能：返回包含數(shù)字以及包含參數(shù)列表中的數(shù)字的單元格的個數(shù)COUNT(value1,value2,...)COUNTA功能：計算范圍中不為空的單元格的個數(shù)COUNTA(value1,[value2],...)COUNTBLANK功能:用于計算單元格區(qū)域中的空單元格的個數(shù)COUNTBLANK（range）COUNTIF功能：計算區(qū)域中滿足給定條件的單元格的個數(shù)COUNTIF(range,criteria)3.2.1統(tǒng)計類函數(shù)2．計數(shù)函數(shù)【例3-2】請根據(jù)如圖3-14所示的學(xué)生成績明細，統(tǒng)計學(xué)生人數(shù)、語文科目參加考試人數(shù)及數(shù)學(xué)科目參加考試人數(shù)。操作步驟如下。圖3-14學(xué)生成績明細數(shù)據(jù)表3.2.1統(tǒng)計類函數(shù)2．計數(shù)函數(shù)光標(biāo)定位于F4單元格，鼠標(biāo)單擊“插入函數(shù)”按鈕，彈出如圖315所示的“插入函數(shù)”對話框，選擇類別“統(tǒng)計”—“COUNTA”函數(shù)，單擊“確定”按鈕，彈出如圖316所示的“函數(shù)參數(shù)”對話框。光標(biāo)定位于value1右側(cè)的輸入框中，鼠標(biāo)選取A2:A11單元格區(qū)域，單擊“確定”按鈕即可。光標(biāo)定位于F5單元格，鼠標(biāo)單擊“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，選擇類別“統(tǒng)計”—“COUNT”函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框，光標(biāo)定位于value1右側(cè)的輸入框中，鼠標(biāo)選取B2:B11單元格區(qū)域，單擊“確定”按鈕即可。圖3-15“插入函數(shù)”對話框圖3-16“函數(shù)參數(shù)”對話框3.2.1統(tǒng)計類函數(shù)2．計數(shù)函數(shù)【例3-3】如圖3-17所示是會員數(shù)據(jù)，一般數(shù)據(jù)庫中的數(shù)據(jù)均有一個主鍵，即不允許重復(fù)的鍵，計算主鍵的重復(fù)次數(shù)如果大于1即為重復(fù)。操作步驟如下。圖3-17會員數(shù)據(jù)明細3.2.1統(tǒng)計類函數(shù)2．計數(shù)函數(shù)在A列與B列中插入一列，名稱為“重復(fù)次數(shù)”。將光標(biāo)定位于B2單元格，鼠標(biāo)單擊“插入函數(shù)”按鈕，彈出如圖3-18所示的“插入函數(shù)”對話框，選擇類別“統(tǒng)計”—“COUNTIF”函數(shù)，單擊“確定”按鈕，彈出如圖3-19示的“函數(shù)參數(shù)”對話框。光標(biāo)定位于range右側(cè)的輸入框中，鼠標(biāo)選取A2:A11單元格區(qū)域，由于此區(qū)域在公式復(fù)制過程中不變，故行號后添加$符號，將相對地址改為混合地址。將光標(biāo)定位于Criteria右側(cè)的輸入框中，選取A2單元格，單擊“確定”按鈕，B2單元格中的數(shù)值顯示3。單擊B2單元格，將鼠標(biāo)移動到B2單元格右下角，此時光標(biāo)變?yōu)橐粋€+，雙擊+，后續(xù)的單元格自動按此公式進行計算。圖3-18“插入函數(shù)”對話框圖3-19“函數(shù)參數(shù)”對話框3.2.1統(tǒng)計類函數(shù)2．計數(shù)函數(shù)【例3-4】如圖3-20所示是某單位2018年1月的銷售明細數(shù)據(jù)，現(xiàn)要求統(tǒng)計教學(xué)部男員工的銷售記錄個數(shù)。操作步驟如下。圖3-20某單位2018年1月銷售明細數(shù)據(jù)3.2.1統(tǒng)計類函數(shù)2．計數(shù)函數(shù)光標(biāo)定位于工作表的空白單元格，鼠標(biāo)單擊“插入函數(shù)”按鈕，彈出如圖3-21所示的“插入函數(shù)”對話框，選擇類別“統(tǒng)計”—“COUNTIFS”函數(shù)，單擊“確定”按鈕，彈出如圖3-22所示的“函數(shù)參數(shù)”對話框。光標(biāo)定位于criteria_range1右側(cè)的輸入框中，鼠標(biāo)選取第一個條件區(qū)域C3:C14，光標(biāo)定位于criteria1右側(cè)的輸入框中，輸入第一個條件“教學(xué)部”，光標(biāo)定位于criteria_range2右側(cè)的輸入框中，鼠標(biāo)選取第二個條件區(qū)域B3:B14，光標(biāo)定位于criteria2，輸入第二個條件“男”，單擊“確定”按鈕即可。圖3-21“插入函數(shù)”對話框圖3-22“函數(shù)參數(shù)”對話框3.2.1統(tǒng)計類函數(shù)3．其他統(tǒng)計類函數(shù)其他統(tǒng)計函數(shù)0102030405最大值函數(shù)最小值函數(shù)Frequency函數(shù)Rank函數(shù)MEDIAN函數(shù)MAX(number1,number2,...)功能：返回一組值中的最大值MIN(number1,number2,...)功能：返回一組值中的最小值MEDIAN(number1,number2,...)功能：返回給定數(shù)值集合的中值.中值是在一組數(shù)據(jù)中居于中間的數(shù),即在這組數(shù)據(jù)中，有一半的數(shù)據(jù)比它大,有一半的數(shù)據(jù)比它小RANK(number,ref,order)功能：返回一個數(shù)字在數(shù)字列表中的排位。數(shù)字的排位是其大小與列表中其他值的比值（如果列表已排過序，則數(shù)字的排位就是它當(dāng)前的位置）Frequency(data_array,bins_array)功能：以一列垂直數(shù)組返回一組數(shù)據(jù)的頻率分布3.2.1統(tǒng)計類函數(shù)3．其他統(tǒng)計類函數(shù)【例3-5】計算如圖3-17所示會員明細數(shù)據(jù)表中，購買金額的最大值與最小值。操作步驟如下。圖3-17會員數(shù)據(jù)明細3.2.1統(tǒng)計類函數(shù)3．其他統(tǒng)計類函數(shù)鼠標(biāo)單擊“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，選擇類別“統(tǒng)計”—“MAX”函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框。光標(biāo)定位于number1右側(cè)的輸入框中，鼠標(biāo)選取G2:G11單元格區(qū)域，單擊“確定”按鈕即可求出購買金額的最大值?！纠?-6】計算如圖3-17所示會員明細數(shù)據(jù)表中，購買金額的中位數(shù)。操作步驟如下。鼠標(biāo)單擊“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，選擇類別“統(tǒng)計”—“MEDIAN”函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框。光標(biāo)定位于number1右側(cè)的輸入框中，鼠標(biāo)選取G2:G11單元格區(qū)域，單擊“確定”按鈕即可求出購買金額的中位數(shù)。3.2.1統(tǒng)計類函數(shù)3．其他統(tǒng)計類函數(shù)【例3-7】計算如圖3-23所示學(xué)生成績明細表中每位同學(xué)成績在年級中的排名。操作步驟如下。圖3-23學(xué)生成績明細表3.2.1統(tǒng)計類函數(shù)3．其他統(tǒng)計類函數(shù)光標(biāo)定位于工作表的E2單元格，鼠標(biāo)單擊“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，選擇類別“統(tǒng)計”—“RANK”函數(shù)，單擊“確定”按鈕，彈出圖324“函數(shù)參數(shù)”對話框。光標(biāo)定位于number右側(cè)的輸入框中，鼠標(biāo)選取B2單元格。光標(biāo)定位于Ref右側(cè)的輸入框中，按住Ctrl的同時，鼠標(biāo)選取B2:B11單元格區(qū)域與D2:D11單元格區(qū)域，為保證復(fù)制公式時，此區(qū)域地址不變化，需要使用絕對地址，因此在行列號前加上$，并在區(qū)域外添加英文狀態(tài)下的括號。光標(biāo)定位于Order右側(cè)的輸入框中，輸入0或省略，默認情況下為降序。鼠標(biāo)單擊“確定”按鈕即可。圖3-24RANK“函數(shù)參數(shù)”對話框3.2.1統(tǒng)計類函數(shù)3．其他統(tǒng)計類函數(shù)【例3-8】請分別統(tǒng)計圖3-23所示學(xué)生成績明細表中學(xué)生成績的分段人數(shù)，結(jié)果如圖3-26所示。操作步驟如下。圖3-23學(xué)生成績明細表圖3-26分段統(tǒng)計結(jié)果3.2.1統(tǒng)計類函數(shù)3．其他統(tǒng)計類函數(shù)鼠標(biāo)單擊“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，選擇類別“統(tǒng)計”—“FREQUENCY”函數(shù)，單擊“確定”按鈕，彈出如圖3-25所示“函數(shù)參數(shù)”對話框。光標(biāo)定位于Data_array右側(cè)的輸入框中，按住Ctrl鍵的同時，鼠標(biāo)選取B2:B11單元格區(qū)域和D2:D11單元格區(qū)域。為保證復(fù)制公式時，此區(qū)域地址不變化，需要使用絕對地址，因此在行列號前加上$，并在區(qū)域外添加英文狀態(tài)下的括號。光標(biāo)定位于Bins_array右側(cè)的輸入框中，鼠標(biāo)選取C14:C17單元格區(qū)域，接收區(qū)間的結(jié)束單元格C17在復(fù)制公式時不能變化，需要使用混合地址，因此在C17行號前加上$。鼠標(biāo)單擊“確定”按鈕即可。圖3-25Frequency“函數(shù)參數(shù)”對話框3.2.2數(shù)學(xué)計算類函數(shù)1．求和函數(shù)在數(shù)據(jù)分析過程中，有時需要對數(shù)值型數(shù)據(jù)進行求和或根據(jù)指定條件求和等03020104SUMSUM(number1,number2,...)功能：返回某一單元格區(qū)域中所有數(shù)字之和。參數(shù)說明：Number1,number2,...為1到30個需要求和的參數(shù),可以是一個具體的數(shù)值、一個單元格或單元格區(qū)域SUMIFSSUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)功能：用于計算其滿足多個條件的全部參數(shù)的總量。SUMIFSUMIF(range,criteria,sum_range)功能：對范圍中符合指定條件的數(shù)值求和。SUMPRODUCTSUMPRODUCT(array1,[array2],[array3],...)功能：在給定的幾組數(shù)組中,將數(shù)組間對應(yīng)的元素相乘,并返回乘積之和3.2.2數(shù)學(xué)計算類函數(shù)1．求和函數(shù)【例3-9】要求計算如圖3-27所示的信息表中指定部門的總成績。操作步驟如下。圖3-27成績信息表3.2.1統(tǒng)計類函數(shù)1．求和函數(shù)光標(biāo)定位于工作表的B14單元格，鼠標(biāo)單擊“插入函數(shù)”按鈕，彈出如圖3-28所示的“插入函數(shù)”對話框，選擇類別“數(shù)學(xué)與三角函數(shù)”—“SUMIF”函數(shù)，單擊“確定”按鈕，彈出如圖3-29所示的“函數(shù)參數(shù)”對話框。光標(biāo)定位于range右側(cè)的輸入框中，鼠標(biāo)選取條件區(qū)域A2:A10，為確保向下復(fù)制公式時，條件區(qū)域永遠定位于A2:A10，因此需要將該地址改為混合地址A$2:A$10，光標(biāo)定位于criteria右側(cè)的輸入框中，選取條件單元格A14，光標(biāo)定位于Sum_range右側(cè)的輸入框中，鼠標(biāo)選取求各區(qū)域B3:B14，同樣，需要將該區(qū)域地址修改為混合地址B$3:B$14，單擊“確定”按鈕即可。圖3-28“插入函數(shù)”對話框圖3-29SUMIF“函數(shù)參數(shù)”對話框3.2.2數(shù)學(xué)計算類函數(shù)1．求和函數(shù)【例3-10】要求計算如圖3-30所示的信息表中各部門男女員工的銷售總額。操作步驟如下。圖3-30各部門員工銷售明細表3.2.1統(tǒng)計類函數(shù)1．求和函數(shù)光標(biāo)定位于工作表的D18單元格，鼠標(biāo)單擊“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，選擇類別“數(shù)學(xué)與三角函數(shù)”—“SUMIFS”函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框。光標(biāo)定位于Sum_range右側(cè)的輸入框中，鼠標(biāo)選取計算區(qū)域D3:D14，為確保向下復(fù)制公式時，條件區(qū)域永遠定位于D3:D14，因此需要將該地址改為絕對地址$D$3:$D$14。光標(biāo)定位于criteria_Range1右側(cè)的輸入框中，鼠標(biāo)選取計算區(qū)域C3:C14，為確保向下復(fù)制公式時，條件區(qū)域永遠定位于C3:C14，因此需要將該地址改為絕對地址$C$3:$C$14，光標(biāo)定位于criteria1右側(cè)的輸入框中，鼠標(biāo)選取條件單元格B18。光標(biāo)定位于criteria_Range2右側(cè)的輸入框中，鼠標(biāo)選取計算區(qū)域B3:B14，為確保向下復(fù)制公式時，條件區(qū)域永遠定位于B3:B14，因此需要將該地址改為絕對地址$B$3:$B$14，光標(biāo)定位于criteria2右側(cè)的輸入框中，鼠標(biāo)選取條件單元格C18，單擊“確定”按鈕即可。3.2.2數(shù)學(xué)計算類函數(shù)1．求和函數(shù)【例3-11】要求使用SUMPRODUCT計算如圖330所示的信息表中各部門男女員工的人數(shù)及銷售總額。操作步驟如下。圖3-30各部門員工銷售明細表3.2.1統(tǒng)計類函數(shù)1．求和函數(shù)光標(biāo)定位于工作表的D18單元格，鼠標(biāo)單擊“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，選擇類別“數(shù)學(xué)與三角函數(shù)”—“SUMPRODUCT”函數(shù)，單擊“確定”按鈕，彈出如圖3-32所示的“函數(shù)參數(shù)”對話框。光標(biāo)定位于array1右側(cè)的輸入框中，輸入公式($C$3:$C$14=D18)*($B$3:$B$14=C18)光標(biāo)定位于array2右側(cè)的輸入框中，鼠標(biāo)選取計算區(qū)域$D$3:$D$14，單擊“確定”按鈕即可計算各部門男女員工的銷售總額計算各部門男女員工的人數(shù)，只需要在array1右側(cè)的輸入框中輸入公式($C$3:$C$14=D18)*($B$3:$B$14=C18)，單擊“確定”按鈕即可。圖3-32SUMPRODUCT“函數(shù)參數(shù)”對話框3.2.2數(shù)學(xué)計算類函數(shù)2．其他數(shù)學(xué)函數(shù)（1）INT(number)功能：將number數(shù)值向下舍入為最接近