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

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