Excel在經(jīng)濟(jì)管理中的應(yīng)用課件(中)

第五章Excel在數(shù)據(jù)統(tǒng)計(jì)與分析中的應(yīng)用

01了解數(shù)組及數(shù)組公式掌握常用數(shù)學(xué)和統(tǒng)計(jì)函數(shù)的基本用法學(xué)會(huì)在實(shí)際事務(wù)中靈活使用數(shù)學(xué)和統(tǒng)計(jì)函數(shù)本章學(xué)習(xí)目標(biāo)1了解數(shù)組及數(shù)組公式本章學(xué)習(xí)目標(biāo)2Excel的數(shù)組公式很有用，尤其在需要一次性獲得多個(gè)運(yùn)算結(jié)果時(shí)，數(shù)組公式就顯得特別重要，了解Excel數(shù)組及數(shù)組公式可以為實(shí)際數(shù)據(jù)處理問題提供更多的解決方案。5.1.1認(rèn)識(shí)數(shù)組和數(shù)組公式Excel數(shù)組是以常量數(shù)據(jù)為元素的集合，其元素的數(shù)據(jù)類型可以是數(shù)值、文本和邏輯型。特別的，數(shù)組中也可以僅有一個(gè)元素，即為我們之前熟悉的單值常量。引入數(shù)組概念后，Excel中輸入的公式當(dāng)以Ctrl+Shift+Enter組合鍵確認(rèn)時(shí)即為數(shù)組公式，其中參與運(yùn)算的數(shù)組可以是常量數(shù)組或區(qū)域數(shù)組。5.1數(shù)組公式及數(shù)據(jù)處理應(yīng)用2Excel的數(shù)組公式很有用，尤其在需要一次性獲得多個(gè)運(yùn)算3進(jìn)行數(shù)組公式運(yùn)算時(shí)，要先選擇用來(lái)存放結(jié)果的單元格區(qū)域（可以是一個(gè)單元格），然后輸入數(shù)組公式，按Ctrl+Shift+Enter組合鍵結(jié)束，Excel將在公式兩邊自動(dòng)加上花括號(hào)“{}”。5.1.2數(shù)組的常見形式1.常量數(shù)組用一對(duì)花括號(hào)“{}”把構(gòu)成數(shù)組的常量數(shù)據(jù)括起來(lái)，就是常量數(shù)組。同行數(shù)據(jù)之間用逗號(hào)“，”分割，同列數(shù)據(jù)之間用分號(hào)“；”分割。2.區(qū)域數(shù)組區(qū)域數(shù)組就是通過對(duì)一組連續(xù)的單元格形成的矩形區(qū)域進(jìn)行引用而得到的數(shù)組。3.內(nèi)存數(shù)組內(nèi)存數(shù)組是指在數(shù)組公式計(jì)算過程中生成的中間結(jié)果值。3進(jìn)行數(shù)組公式運(yùn)算時(shí)，要先選擇用來(lái)存放結(jié)果的單元格區(qū)域（可以45.1.3數(shù)組間的運(yùn)算1.二維數(shù)組間的運(yùn)算其結(jié)果由對(duì)各數(shù)組相同位置的元素進(jìn)行相同的運(yùn)算而獲得，若參與運(yùn)算的數(shù)組的行列數(shù)不匹配，結(jié)果數(shù)組的大小應(yīng)該和最大的行列數(shù)匹配，但有效數(shù)據(jù)區(qū)域和最小的行列數(shù)匹配。2.二維數(shù)組與一維數(shù)組的運(yùn)算若一維數(shù)組是行數(shù)組，其運(yùn)算結(jié)果相當(dāng)于原值擴(kuò)展其行與二維數(shù)組行相同后，再與二維數(shù)組進(jìn)行的運(yùn)算；若一維數(shù)組是列數(shù)組，其運(yùn)算結(jié)果相當(dāng)于原值擴(kuò)展其列與二維數(shù)組列相同后，再與二維數(shù)組進(jìn)行的運(yùn)算，實(shí)質(zhì)歸結(jié)為二維數(shù)組間的運(yùn)算。3.一維數(shù)組間的運(yùn)算45.1.3數(shù)組間的運(yùn)算5若同是行數(shù)組或同是列數(shù)組運(yùn)算時(shí)，其運(yùn)算規(guī)則同二維數(shù)組間的運(yùn)算。若是行數(shù)組和列數(shù)組運(yùn)算時(shí)，其運(yùn)算結(jié)果相當(dāng)于原值擴(kuò)展行數(shù)組的行與列數(shù)組的行相同，同時(shí)原值擴(kuò)展列數(shù)組的列與行數(shù)組的列相同后，再進(jìn)行的二維數(shù)組間的運(yùn)算。4.數(shù)組與單一數(shù)據(jù)的運(yùn)算其運(yùn)算結(jié)果相當(dāng)于原值擴(kuò)展單一數(shù)據(jù)為與其運(yùn)算的數(shù)組的行列數(shù)一樣的數(shù)組后，再進(jìn)行的運(yùn)算。以上數(shù)組運(yùn)算全部以加法示例，其他如減法、乘法及比較等運(yùn)算雷同。統(tǒng)計(jì)銷售額[案例要求]打開“案例5-1.xlsx”工作簿，“銷售表”中記錄了各產(chǎn)品的銷售數(shù)量和銷售單價(jià)。根據(jù)已提供數(shù)據(jù)，分別用非數(shù)組公式數(shù)組的概念案例5-15若同是行數(shù)組或同是列數(shù)組運(yùn)算時(shí)，其運(yùn)算規(guī)則同二維數(shù)組間的運(yùn)6和數(shù)組公式兩種方法，計(jì)算每種產(chǎn)品的銷售金額以及所有產(chǎn)品的銷售總額。本案例實(shí)現(xiàn)效果如圖數(shù)組的聲明6和數(shù)組公式兩種方法，計(jì)算每種產(chǎn)品的銷售金額以及所有產(chǎn)品的銷7在Excel環(huán)境下，對(duì)數(shù)據(jù)進(jìn)行基本處理和分析時(shí)，諸如求和、求平均值、求最大最小值、計(jì)數(shù)等是最常見的要求，可以由Excel提供的常用數(shù)學(xué)和統(tǒng)計(jì)函數(shù)實(shí)現(xiàn)。5.2.1企業(yè)產(chǎn)品總量統(tǒng)計(jì)常用實(shí)現(xiàn)求和函數(shù)如下：1.SUM格式：SUM(number1,[number2],...)功能：計(jì)算參數(shù)列表中數(shù)值的和。說(shuō)明：?jiǎn)卧褚脜^(qū)域中的空值、文本值和邏輯值都被按數(shù)值0處理。2.SUMIF格式：SUMIF(range,criteria,[sum_range])5.2常用數(shù)學(xué)統(tǒng)計(jì)函數(shù)及數(shù)據(jù)處理應(yīng)用7在Excel環(huán)境下，對(duì)數(shù)據(jù)進(jìn)行基本處理和分析時(shí)，諸如求和、8功能：計(jì)算區(qū)域中滿足單一條件的單元格數(shù)值的和。說(shuō)明：range必需。根據(jù)條件進(jìn)行計(jì)算的單元格區(qū)域。Criteria必需。對(duì)range指定區(qū)域的限定條件，其形式可以為數(shù)值、表達(dá)式、單元格引用、文本或函數(shù)，任何文本條件或任何含有邏輯或數(shù)學(xué)符號(hào)的條件都必須使用雙引號(hào)“””括起來(lái)。如果條件為數(shù)值，則無(wú)需使用雙引號(hào)。sum_range可選。要求和的單元格區(qū)域，空值和文本值將被忽略。如果缺省，Excel會(huì)對(duì)在range參數(shù)中指定的單元格（即滿足條件的單元格）求和。3.SUMIFS格式：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)功能：計(jì)算區(qū)域中滿足多個(gè)條件的單元格數(shù)值的和。8功能：計(jì)算區(qū)域中滿足單一條件的單元格數(shù)值的和。9說(shuō)明：Sum_range必需。要求和的單元格區(qū)域。Criteria_range1必需。應(yīng)用Criteria1條件測(cè)試的區(qū)域。Criteria1必需。測(cè)試條件。Criteria_range1和Criteria1設(shè)置用于搜索某個(gè)區(qū)域是否符合特定條件的搜索對(duì)。一旦在該區(qū)域中找到了滿足條件的項(xiàng)，將計(jì)算sum_range中相應(yīng)值的和。Criteria_range2,criteria2,…可選。附加的區(qū)域及其關(guān)聯(lián)條件，最多可以輸入127個(gè)區(qū)域/條件對(duì)。全部缺省時(shí)，功能同SUMIF。僅在與sum_range參數(shù)中的單元格同一位置的各條件區(qū)域的值滿足各自相應(yīng)的指定條件時(shí)，才將該單元格數(shù)值計(jì)入總和中。4.SUMPRODUCT9說(shuō)明：Sum_range必需。要求和的單元格區(qū)域。10格式：SUMPRODUCT(array1,[array2],[array3],...)功能：在給定的幾組數(shù)組中，先進(jìn)行數(shù)組間的乘法運(yùn)算，最后把乘積結(jié)果集的元素相加。這是SUMPRODUCT函數(shù)的基本用法。SUMPRODUCT函數(shù)功能結(jié)合“5.1.3數(shù)組間的運(yùn)算”一節(jié)的內(nèi)容更易于理解。說(shuō)明：Array1必需。Array2,array3,...可選。2到255個(gè)數(shù)組參數(shù)。全部缺省時(shí)，SUMPRODUCT函數(shù)的使用同SUM函數(shù)。數(shù)組參量間的分隔符為逗號(hào)“，”時(shí)，函數(shù)SUMPRODUCT將文本、邏輯型的數(shù)組元素作為0處理，并且要求各個(gè)數(shù)組參數(shù)的行列數(shù)必須匹配，否則，函數(shù)SUMPRODUCT將返回#VALUE!。如果希望處理行列數(shù)不匹配的數(shù)組參數(shù)的運(yùn)算，需要把數(shù)10格式：SUMPRODUCT(array1,[array11組間的分隔符由逗號(hào)“，”換為星號(hào)“*”。數(shù)組間的乘法運(yùn)算規(guī)則同“5.1.3數(shù)組間的運(yùn)算”一節(jié)的講解，若乘積運(yùn)算的結(jié)果集中有錯(cuò)誤值#N/A時(shí)，SUMPRODUCT將返回#N/A。參與運(yùn)算的數(shù)據(jù)區(qū)域若有文本數(shù)據(jù)，會(huì)按0處理；若有邏輯數(shù)據(jù)時(shí)，F(xiàn)ALSE按0處理，TREU按1處理。SUMPRODUCT函數(shù)還有兩種擴(kuò)展用法，介紹如下：（1）格式：SUMPRODUCT((criteria_array1)*[(criteria_array2)]*[(criteria_array3)]*...)功能：實(shí)現(xiàn)多條件計(jì)數(shù)。說(shuō)明：criteria_array1必需。是條件，其實(shí)質(zhì)是數(shù)組的比較運(yùn)算，結(jié)果值是以TREU或FALSE為元素的數(shù)組。(criteria_array2),(criteria_array3),...可選。需要滿足的更多條件。若全部缺省，SUMPRODUCT函數(shù)的結(jié)果值為0。11組間的分隔符由逗號(hào)“，”換為星號(hào)“*”。數(shù)組間的乘法運(yùn)算12（2）格式：SUMPRODUCT((criteria_array1)*[(criteria_array2)]*[(criteria_array3)]*...*sum_array)功能：實(shí)現(xiàn)條件求和。說(shuō)明：sum_array必需。是求和區(qū)域。運(yùn)算流程和條件計(jì)數(shù)雷同，只是增加了求和區(qū)域。最后的求和結(jié)果相當(dāng)于把求和區(qū)域中滿足這樣條件的數(shù)計(jì)入總和，即比較運(yùn)算符前的各數(shù)組中與該數(shù)相同位置上的元素同時(shí)滿足各自對(duì)應(yīng)的條件。運(yùn)算中若有數(shù)組行列數(shù)不匹配的問題，運(yùn)算規(guī)則與前面講過的算術(shù)運(yùn)算一樣。統(tǒng)計(jì)企業(yè)產(chǎn)品組裝量[案例要求]打開“案例5-2.xlsx”工作簿，“sheet1”表中按月記錄了某企業(yè)組裝車間員工一季度組裝某產(chǎn)品的數(shù)量，如圖5-14所示，案例5-212（2）格式：SUMPRODUCT((criteria_a13根據(jù)已提供數(shù)據(jù)，完成以下操作：（1）“Sheet1”表中統(tǒng)計(jì)每位員工的一季度組裝量。（2）“Sheet2”表中按組統(tǒng)計(jì)每月和一季度組裝總量。（3）分別在“Sheet3”表、“Sheet4”表和“Sheet5”表中用不同的公式按組統(tǒng)計(jì)男女員工的一季度組裝總量。本案例實(shí)現(xiàn)主要效果圖如下：13根據(jù)已提供數(shù)據(jù)，完成以下操作：145.2.2企業(yè)人數(shù)分段統(tǒng)計(jì)常用實(shí)現(xiàn)計(jì)數(shù)函數(shù)如下：1.COUNT格式：COUNT(value1,[value2],...)功能：計(jì)算參數(shù)列表中數(shù)值的個(gè)數(shù)。說(shuō)明：value1必需。要計(jì)算其中數(shù)值的個(gè)數(shù)的第一項(xiàng)，可以是常量或單元格引用。145.2.2企業(yè)人數(shù)分段統(tǒng)計(jì)15value2,...可選。要計(jì)算其中數(shù)值的個(gè)數(shù)的其他項(xiàng)，最多可包含255個(gè)。如果參數(shù)為數(shù)值、邏輯值、日期或者代表數(shù)字的文本（例如，用引號(hào)引起的數(shù)字，如“1”），則將被計(jì)算在內(nèi)。錯(cuò)誤值或不能轉(zhuǎn)換為數(shù)值的文本，則不會(huì)被計(jì)算在內(nèi)。如果參數(shù)是一個(gè)數(shù)組或引用，則只計(jì)算其中的數(shù)值，空白單元格、邏輯值、文本或錯(cuò)誤值將不計(jì)算在內(nèi)。2.COUNTA格式：COUNTA(value1,[value2],...)功能：計(jì)算區(qū)域中非空單元格的個(gè)數(shù)。說(shuō)明：COUNTA函數(shù)計(jì)算包含任何類型信息（包括錯(cuò)誤值和空文本(“”)）的單元格。例如，如果參數(shù)區(qū)域中包含由公式計(jì)算返回的空字符串，COUNTA函數(shù)計(jì)算該值。15value2,...可選。要計(jì)算其中數(shù)值的個(gè)數(shù)的其他項(xiàng)16COUNTA函數(shù)不會(huì)對(duì)空單元格進(jìn)行計(jì)數(shù)。3.COUNTIF格式：COUNTIF(range,criteria)功能：計(jì)算區(qū)域中滿足指定條件的單元格的個(gè)數(shù)。說(shuō)明：range必需。要進(jìn)行計(jì)數(shù)的單元格區(qū)域，空值將被忽略。criteria必需。用于確定對(duì)哪些單元格計(jì)數(shù)的條件。4.COUNTIFS格式：COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],…)功能：計(jì)算多個(gè)區(qū)域同一位置上同時(shí)滿足各自條件的單元格的個(gè)數(shù)。說(shuō)明：criteria_range1,criteria1必需。在其中計(jì)算關(guān)聯(lián)條件16COUNTA函數(shù)不會(huì)對(duì)空單元格進(jìn)行計(jì)數(shù)。17的第一個(gè)區(qū)域和關(guān)聯(lián)條件。criteria_range2,criteria2,...可選。附加的區(qū)域及其關(guān)聯(lián)條件。若全部缺省，COUNTIFS函數(shù)的使用同COUNTIF函數(shù)。每一個(gè)附加的區(qū)域都必須與參數(shù)criteria_range1具有相同的行數(shù)和列數(shù)，這些區(qū)域無(wú)需彼此相鄰。每個(gè)區(qū)域的條件一次應(yīng)用于一個(gè)單元格。如果所有區(qū)域的第一個(gè)單元格都滿足其關(guān)聯(lián)條件，則計(jì)數(shù)增加1。如果所有區(qū)域的第二個(gè)單元格都滿足其關(guān)聯(lián)條件，則計(jì)數(shù)再增加1，依此類推，直到計(jì)算完所有單元格。5.FREQUENCY格式：FREQUENCY(data_array,bins_array)功能：以一個(gè)垂直數(shù)組返回一組數(shù)據(jù)的頻率分布。說(shuō)明：Data_array必需。要計(jì)算頻率的一組數(shù)值或?qū)@組17的第一個(gè)區(qū)域和關(guān)聯(lián)條件。18數(shù)值的引用。如果data_array中不包含任何數(shù)值，則FREQUENCY返回一個(gè)零數(shù)組。Bins_array必需。對(duì)data_array進(jìn)行頻率計(jì)算的一組間隔值。由于FREQUENCY返回一個(gè)數(shù)組，所以它必須以數(shù)組公式的形式輸入。返回的數(shù)組中的元素比bins_array中的元素多一個(gè)。這額外元素返回最大的間隔值以上的值的計(jì)數(shù)。例如，在對(duì)輸入到三個(gè)單元格中的三個(gè)值范圍（間隔）進(jìn)行計(jì)數(shù)時(shí)，要選中四個(gè)單元格返回FREQUENCY函數(shù)的計(jì)算結(jié)果。額外的單元格將返回data_array中大于第三個(gè)間隔值的值的數(shù)量。函數(shù)FREQUENCY將忽略空白單元格和文本。分段統(tǒng)計(jì)員工人數(shù)[案例要求]打開“案例5-3.xlsx”工作簿，“sheet1”表中按月案例5-318數(shù)值的引用。如果data_array中不包含任何數(shù)值，則19記錄了某企業(yè)組裝車間員工一季度組裝某產(chǎn)品的數(shù)量，根據(jù)已提供數(shù)據(jù)，完成以下操作：（1）統(tǒng)計(jì)組裝量分別在0-49、50-59、60-69、70-79、80-89、90-99、100及以上段的員工人數(shù)。（2）分別統(tǒng)計(jì)各月完成和未完成任務(wù)的員工人數(shù)，若組裝量小于60，屬于未完成，否則完成。本案例實(shí)現(xiàn)效果如圖19記錄了某企業(yè)組裝車間員工一季度組裝某產(chǎn)品的數(shù)量，根據(jù)已提2020215.2.3企業(yè)人均產(chǎn)量統(tǒng)計(jì)常用實(shí)現(xiàn)求平均值函數(shù)如下：1.AVERAGE格式：AVERAGE(number1,[number2],...)功能：計(jì)算參數(shù)列表中數(shù)值的平均值（算術(shù)平均值）。說(shuō)明：Number1必需。Number2,...可選。邏輯值（TRUE轉(zhuǎn)為1，F(xiàn)ALSE轉(zhuǎn)為0）和直接鍵入到參數(shù)列表中代表數(shù)字的文本被計(jì)算在內(nèi)，而錯(cuò)誤值或不能轉(zhuǎn)換為數(shù)值的文本，將會(huì)導(dǎo)致錯(cuò)誤。如果區(qū)域或單元格引用參數(shù)包含文本、邏輯值或空單元格，則這些值將被忽略；但包含零值的單元格將被計(jì)算在內(nèi)。2.AVERAGEIF格式：AVERAGEIF(range,criteria,[average_range])215.2.3企業(yè)人均產(chǎn)量統(tǒng)計(jì)22功能：計(jì)算區(qū)域中滿足指定條件的單元格數(shù)值的平均值（算術(shù)平均值）。說(shuō)明：Range必需。根據(jù)條件進(jìn)行計(jì)算的單元格區(qū)域。Criteria必需。用于確定對(duì)哪些單元格數(shù)值求平均值的條件。Average_range可選。計(jì)算平均值的實(shí)際單元格區(qū)域。如果省略，Excel會(huì)對(duì)range參數(shù)中符合指定條件的單元格數(shù)值（即應(yīng)用條件的單元格）求平均值。如果average_range中的單元格為空單元格，AVERAGEIF將忽略它。3.AVERAGEIFS格式：AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)功能：計(jì)算區(qū)域中滿足多個(gè)條件的單元格數(shù)值的平均值22功能：計(jì)算區(qū)域中滿足指定條件的單元格數(shù)值的平均值（算術(shù)平23（算術(shù)平均值）。說(shuō)明：Average_range必需。要計(jì)算平均值的單元格區(qū)域。當(dāng)有空值時(shí)，函數(shù)會(huì)把它去掉，不參與計(jì)算。4.ROUND格式：ROUND(number,num_digits)功能：將數(shù)值四舍五入到指定的位數(shù)。說(shuō)明：number必需。要四舍五入的數(shù)值。num_digits必需。進(jìn)行四舍五入運(yùn)算時(shí)采用的位數(shù)。如果num_digits大于0（零），則將數(shù)值四舍五入到指定的小數(shù)位數(shù)。如果num_digits等于0，則將數(shù)值四舍五入到最接近的整數(shù)。如果num_digits小于0，則將數(shù)值四舍五入到小數(shù)點(diǎn)左邊的相應(yīng)位數(shù)。統(tǒng)計(jì)平均組裝量案例5-423（算術(shù)平均值）。案例5-424[案例要求]打開“案例5-4.xlsx”工作簿，“sheet1”表中按月記錄了某企業(yè)組裝車間員工一季度組裝某產(chǎn)品的數(shù)量，根據(jù)已提供數(shù)據(jù)，完成以下操作：（1）“sheet1”表中統(tǒng)計(jì)每位員工一季度的組裝量和月平均組裝量。（2）“sheet2”表中按組統(tǒng)計(jì)每月人均組裝量和一季度人均組裝量。（3）“sheet3”表中按組統(tǒng)計(jì)男女員工一季度人均組裝量。（4）所有結(jié)果為整數(shù)。本案例實(shí)現(xiàn)效果如下圖24[案例要求]打開“案例5-4.xlsx”工作簿，“s25252626275.2.4企業(yè)產(chǎn)量最值及排位統(tǒng)計(jì)常用實(shí)現(xiàn)求大小位次的函數(shù)如下：1.MAX格式：MAX(number1,[number2],...)功能：返回參數(shù)列表中的最大值。說(shuō)明：number1必需，number2,...可選。邏輯值和直接鍵入到參數(shù)列表中代表數(shù)字的文本被計(jì)算在內(nèi)。如果參數(shù)是一個(gè)數(shù)組或引用，則只計(jì)算其中的數(shù)值，空白單元格、邏輯值或文本將被忽略。如果參數(shù)不包含任何數(shù)值，則MAX函數(shù)返回0。2.MIN格式：MIN(number1,[number2],...)功能：返回參數(shù)列表中的最小值。275.2.4企業(yè)產(chǎn)量最值及排位統(tǒng)計(jì)283.RANK格式：RANK(number,ref,[order])功能：返回某數(shù)值在一個(gè)數(shù)據(jù)集的大小排名。如果多個(gè)值具有相同的排位，則返回該組值的最高排位。說(shuō)明：Number必需。要找到其排位的數(shù)值。Ref必需。要在其中查找排名的數(shù)據(jù)集。Ref中的非數(shù)字值會(huì)被忽略。Order可選。一個(gè)指定數(shù)值排位方式的數(shù)字。如果order為0或省略，按照降序返回排名結(jié)果；如果order為1，按照升序返回排名結(jié)果。該函數(shù)與Excel早期版本兼容，在未來(lái)版本中可能不再使用，替代函數(shù)為以下兩個(gè)函數(shù)。4.RANK.EQ283.RANK29格式：RANK.EQ(number,ref,[order])功能：同上。5.RANK.AVG格式：RANK.AVG(number,ref,[order])功能：。返回某數(shù)值在一個(gè)數(shù)據(jù)集的大小排名。如果多個(gè)值具有相同的排位，則將返回平均排位。6.PERCENTRANK格式：PERCENTRANK(array,x,[significance])功能：返回某個(gè)數(shù)值在一個(gè)數(shù)據(jù)集中的百分比排位，此處的百分比值范圍為0到1(包含0和1)。說(shuō)明：Array必需。定義相對(duì)位置的數(shù)值數(shù)據(jù)區(qū)域。X必需。需要得到排位的數(shù)值。significance可選。指定返回的百分比值的有效位數(shù)。如果省略，則保留3位小數(shù)。29格式：RANK.EQ(number,ref,[order30省略，則保留3位小數(shù)。如果數(shù)組里沒有與x相匹配的值，函數(shù)PERCENTRANK將進(jìn)行插值以返回正確的百分比排位。函數(shù)返回值在0到1(包含0和1)之間變化，最大的數(shù)排位是1，最小的數(shù)排位是0。該函數(shù)與Excel早期版本兼容，在未來(lái)版本中可能不再使用，替代函數(shù)為以下兩個(gè)函數(shù)。7.PERCENTRANK.INC格式：PERCENTRANK.INC(array,x,[significance])功能：同上。8.PERCENTRANK.EXC格式：PERCENTRANK.EXC(array,x,[significance])功能：返回某個(gè)數(shù)值在一個(gè)數(shù)據(jù)集中的百分比排位,此處的百分比值范圍為0到1(不包含0和1)。30省略，則保留3位小數(shù)。31說(shuō)明：函數(shù)返回值在0到1(不包含0和1)之間變化。9.LARGE格式：LARGE(array,k)功能：返回?cái)?shù)據(jù)集中第k個(gè)最大值。說(shuō)明：Array必需。需要確定第k個(gè)最大值的數(shù)據(jù)區(qū)域。K必需。返回值在數(shù)據(jù)區(qū)域中的排位（從大到小排）。例如：在有N個(gè)數(shù)的數(shù)據(jù)集中，LARGE(array,1)返回最大值，LARGE(array,N)返回最小值10.SMALL格式：SMALL(array,k)功能：返回?cái)?shù)據(jù)集中第k個(gè)最小值。說(shuō)明：Array必需。需要確定第k個(gè)最小值的數(shù)據(jù)區(qū)域。K必需。返回值在數(shù)據(jù)區(qū)域中的排位（從小到大）。31說(shuō)明：函數(shù)返回值在0到1(不包含0和1)之間變化。32例如：在有N個(gè)數(shù)的數(shù)據(jù)集中，SMALL(array,1)返回最小值，SMALL(array,N)返回最大值統(tǒng)計(jì)組裝量最值和排量案例5-5[案例要求]打開“案例5-5.xlsx”工作簿，“sheet1”表中按月記錄了某企業(yè)組裝車間員工一季度組裝某產(chǎn)品的數(shù)量，根據(jù)已提供數(shù)據(jù)，在其中完成以下操作：（1）統(tǒng)計(jì)每位員工的一季度組裝量、一季度組裝量降序排名和一季度組裝量百分比排名，百分比排名結(jié)果按百分比樣式顯示。（2）統(tǒng)計(jì)每月和一季度的最高最低組裝量。在“sheet2”表中完成排名前五位的一季度組裝量和末五位的一季度組裝量的統(tǒng)計(jì)。本案例實(shí)現(xiàn)效果如下圖32例如：在有N個(gè)數(shù)的數(shù)據(jù)集中，SMALL(array,1333334企業(yè)車間分組按性別的各項(xiàng)統(tǒng)計(jì)[案例要求]打開“案例5-6.xlsx”工作簿，“sheet1”表中按月記錄了某企業(yè)組裝車間員工一季度組裝某產(chǎn)品的數(shù)量，根據(jù)已提供數(shù)據(jù)，在“sheet2”表中完成以下操作：案例5-634案例5-635（1）按組分性別統(tǒng)計(jì)一季度組裝量。（2）按組分性別統(tǒng)計(jì)人數(shù)。（3）按組分性別統(tǒng)計(jì)一季度人均組裝量。（4）所有結(jié)果為整數(shù)。本案例實(shí)現(xiàn)效果如下圖35（1）按組分性別統(tǒng)計(jì)一季度組裝量。36由多表給出基礎(chǔ)數(shù)據(jù)的統(tǒng)計(jì)[案例要求]打開“案例5-7.xlsx”工作簿，“sheet1”、“sheet2”和“sheet3”表中分別記錄了某企業(yè)組裝車間員工一季度各月組裝某產(chǎn)品的數(shù)量。根據(jù)已提供數(shù)據(jù)，在“sheet4”表中統(tǒng)計(jì)每位員工的一季度組裝量、月平均組裝量和一季度組裝量降序排名；在“sheet5”表中完成以下操作：（1）按組統(tǒng)計(jì)每月的組裝量及一季度組裝量。（2）按組統(tǒng)計(jì)月平均組裝量。（3）所有結(jié)果為整數(shù)。本案例實(shí)現(xiàn)效果如下圖案例5-736案例5-737373838395.3.1企業(yè)車間組裝量分析打開“課外實(shí)驗(yàn)5-1.xlsx”工作簿，“sheet1”表中按月記錄了某企業(yè)組裝車間員工一季度組裝某產(chǎn)品的數(shù)量。根據(jù)已提供數(shù)據(jù)，在“Sheet2”中完成以下操作：（1）統(tǒng)計(jì)組裝量分別在0-39、40-49、50-59、60-69、70-79、80-89、90-99、100及以上分段的員工人數(shù)。（2）統(tǒng)計(jì)達(dá)標(biāo)和不達(dá)標(biāo)人數(shù)，組裝量小于60為不達(dá)標(biāo)，否則達(dá)標(biāo)。（3）統(tǒng)計(jì)各月最高、最低量和人均組裝量，平均值保留兩位小數(shù)。（4）統(tǒng)計(jì)達(dá)標(biāo)率，用百分比形式顯示。最終效果如圖5.3本章課外實(shí)驗(yàn)395.3.1企業(yè)車間組裝量分析5.3本章課外實(shí)驗(yàn)4040415.3.2企業(yè)車間組裝量匯總打開“課外實(shí)驗(yàn)5-2.xlsx”工作簿，“sheet1”表中按月記錄了某企業(yè)組裝車間員工一季度組裝某產(chǎn)品的數(shù)量。根據(jù)已提供數(shù)據(jù)，在“Sheet2”表中完成以下操作：（1）按組分性別統(tǒng)計(jì)每月的組裝量。（2）按組統(tǒng)計(jì)一季度組裝量。在“Sheet3”表中按組分性別統(tǒng)計(jì)每月的人均組裝量，保留兩位小數(shù)。最終效果如圖415.3.2企業(yè)車間組裝量匯總42424343

第六章Excel在企業(yè)生產(chǎn)和經(jīng)營(yíng)管理中的應(yīng)用

4445掌握企業(yè)生產(chǎn)管理中常用表格的建立方法掌握日期時(shí)間函數(shù)的基本用法，能夠解決生產(chǎn)周期、工時(shí)數(shù)的計(jì)算問題熟練使用IF函數(shù)解決企業(yè)生產(chǎn)與經(jīng)營(yíng)過程中的常見問題掌握OFFSET函數(shù)的用法，能夠核算生產(chǎn)成本本章學(xué)習(xí)目標(biāo)45掌握企業(yè)生產(chǎn)管理中常用表格的建立方法本章學(xué)習(xí)目標(biāo)46

企業(yè)生產(chǎn)和經(jīng)營(yíng)管理中會(huì)用到大量表格，表格的制作水平直接關(guān)系到企業(yè)的生產(chǎn)和管理效率。本節(jié)將介紹使用EXCEL如何設(shè)計(jì)和制作出符合企業(yè)生產(chǎn)經(jīng)營(yíng)管理規(guī)律的表格。6.1設(shè)計(jì)企業(yè)生產(chǎn)管理常用表格6.1.1生產(chǎn)部門業(yè)務(wù)能力分析表

對(duì)于生產(chǎn)部門來(lái)說(shuō)，業(yè)務(wù)能力的強(qiáng)弱是非常重要的，所以對(duì)生產(chǎn)部門業(yè)務(wù)能力的分析也是非常必要和必須的。通過對(duì)業(yè)務(wù)能力的分析，幫助決策者盡快做出調(diào)整，最大限度地保障企業(yè)生產(chǎn)效率。46企業(yè)生產(chǎn)和經(jīng)營(yíng)管理中會(huì)用到大量表格，表格的制作水47[案例要求]某企業(yè)將設(shè)計(jì)一款生產(chǎn)部門業(yè)務(wù)能力分析表，用于對(duì)生產(chǎn)部門業(yè)務(wù)開展情況進(jìn)行打分，從而起到激勵(lì)或整改的目的。案例6-1生產(chǎn)部門業(yè)務(wù)能力分析表47[案例要求]某企業(yè)將設(shè)計(jì)一款生產(chǎn)部門業(yè)務(wù)能力分析表，用于486.1.2設(shè)計(jì)產(chǎn)品成本核算系統(tǒng)

生產(chǎn)成本的分析和計(jì)劃對(duì)于企業(yè)的經(jīng)營(yíng)決策起著極其重要的作用，本節(jié)主要講解如何設(shè)計(jì)產(chǎn)品成本核算系統(tǒng)。設(shè)計(jì)過程中會(huì)用到TODAY、YEAR、MONTH、DAY和CONCATENATE函數(shù)，這5個(gè)函數(shù)的語(yǔ)法及用法介紹如下。1．TODAY格式：TODAY()功能：TODAY()函數(shù)用于返回當(dāng)前日期2．YEAR格式：YEAR(serial_number)功能：該函數(shù)用于取日期中的年。說(shuō)明：參數(shù)serial_number為日期。486.1.2設(shè)計(jì)產(chǎn)品成本核算系統(tǒng)生產(chǎn)成493．MONTH格式：MONTH(serial_number)功能：該函數(shù)用于取日期中的月。4．DAY格式：DAY(serial_number)功能：該函數(shù)用于取日期中的日。5．CONCATENATE格式：concatenate(text1,[text2],...）功能：把多個(gè)字符文本或數(shù)值連接在一起，實(shí)現(xiàn)合并的功能。說(shuō)明：參數(shù)Text1，Text2可以是文本或者數(shù)值，最多為255項(xiàng)，但是項(xiàng)與項(xiàng)之間必須用逗號(hào)隔開。493．MONTH4．DAY5．CONCATENATE[案例要求]某公司計(jì)劃設(shè)計(jì)一個(gè)產(chǎn)品成本核算系統(tǒng)，用于分析生產(chǎn)成本，從而對(duì)企業(yè)經(jīng)營(yíng)進(jìn)行決策。案例6-2設(shè)計(jì)產(chǎn)品成本核算系統(tǒng)[案例要求]某公司計(jì)劃設(shè)計(jì)一個(gè)產(chǎn)品成本核算系統(tǒng)，用于分析生產(chǎn)516.1.3設(shè)計(jì)新產(chǎn)品開發(fā)測(cè)試情況登記表

要想使企業(yè)在市場(chǎng)上能夠立于不敗之地，就必須對(duì)產(chǎn)品經(jīng)常進(jìn)行更新?lián)Q代。新產(chǎn)品開發(fā)系統(tǒng)一般包括兩個(gè)表格：新產(chǎn)品開發(fā)測(cè)試情況登記表、新產(chǎn)品開發(fā)測(cè)試統(tǒng)計(jì)表。[案例要求]一般企業(yè)在新產(chǎn)品研究成功之前都要進(jìn)行大量的測(cè)試，每一次的結(jié)果都是非常重要的，本節(jié)就來(lái)學(xué)習(xí)如何設(shè)計(jì)一張表格對(duì)開發(fā)測(cè)試情況進(jìn)行記錄。案例6-3設(shè)計(jì)新產(chǎn)品開發(fā)測(cè)試情況登記表516.1.3設(shè)計(jì)新產(chǎn)品開發(fā)測(cè)試情況登記表要想使企526.1.3設(shè)計(jì)新產(chǎn)品開發(fā)測(cè)試統(tǒng)計(jì)表

上節(jié)課介紹的開發(fā)測(cè)試情況登記表，可以記錄信息。本節(jié)將對(duì)如何快速高效地統(tǒng)計(jì)這些結(jié)果進(jìn)行介紹。[案例要求]公司要求設(shè)計(jì)新產(chǎn)品開發(fā)測(cè)試統(tǒng)計(jì)表，對(duì)已經(jīng)收集上來(lái)的記錄進(jìn)行統(tǒng)計(jì)。案例6-4設(shè)計(jì)新產(chǎn)品開發(fā)測(cè)試統(tǒng)計(jì)表526.1.3設(shè)計(jì)新產(chǎn)品開發(fā)測(cè)試統(tǒng)計(jì)表上節(jié)課介紹的536.2日期時(shí)間函數(shù)的應(yīng)用

日期時(shí)間函數(shù)是企業(yè)經(jīng)營(yíng)管理過程中經(jīng)常使用的函數(shù)，正確使用日期時(shí)間函數(shù)才會(huì)使經(jīng)濟(jì)業(yè)務(wù)處理得更準(zhǔn)確，通過對(duì)本節(jié)的學(xué)習(xí)可以掌握日期時(shí)間函數(shù)的用法，以及在企業(yè)生產(chǎn)和經(jīng)營(yíng)管理中的應(yīng)用。6.2.1產(chǎn)品交貨日期計(jì)算

在企業(yè)生產(chǎn)中，有時(shí)知道生產(chǎn)任務(wù)的開始日期和完成生產(chǎn)所需的工作日，需要了解完成任務(wù)的日期，可以用WORKDAY()函數(shù)實(shí)現(xiàn)，該函數(shù)的語(yǔ)法及用法介紹如下。536.2日期時(shí)間函數(shù)的應(yīng)用日期時(shí)間函數(shù)是企業(yè)經(jīng)營(yíng)54WORKDAY格式：WORKDAY(start_date,days,holidays)功能：WORKDAY()函數(shù)用于返回某日期（起始日期）之前或之后相隔指定工作日的某一日期的日期值，工作日不包括周末和專門指定的假日，在計(jì)算發(fā)票到期日、預(yù)期交貨時(shí)間或工作天數(shù)時(shí)，可以使用函數(shù)WORKDAY來(lái)扣除周末或假日。說(shuō)明：參數(shù)start_date表示開始日期，參數(shù)days表示在參數(shù)start_date之前或之后指定的工作日天數(shù)，參數(shù)holidays表示需要排除在外的節(jié)假日。54WORKDAY55[案例要求]某企業(yè)接到若干訂單，從接到訂單開始，要求若干個(gè)工作日后必須交貨，現(xiàn)需要根據(jù)開始日期和完成任務(wù)所需工作日，計(jì)算出完成任務(wù)的日期。案例6-5產(chǎn)品交貨日期計(jì)算6.2.2生產(chǎn)任務(wù)工作日計(jì)算

在企業(yè)生產(chǎn)中，有時(shí)知道生產(chǎn)任務(wù)的開始日期和計(jì)劃完成日期，需要對(duì)生產(chǎn)任務(wù)的工作日進(jìn)行管理?？梢杂肗ETWORKDAYS()、TODAY()和WORKDAY()函數(shù)編寫“開始日期”、“結(jié)束日期”和“工作日”及“當(dāng)前日期”的函數(shù)關(guān)系式。下面簡(jiǎn)單介紹NETWORKDAYS()和TODAY()函數(shù)的語(yǔ)法及用法。55[案例要求]某企業(yè)接到若干訂單，從接到訂單開始，要求若干561、NETWORKDAYS格式：NETWORKDAYS(start_date,end_date,holidays)功能：NETWORKDAYS函數(shù)用于返回參數(shù)start_date和end_date之間完整的工作日數(shù)值，工作日不包括周末和專門指定的假期，可以使用函數(shù)NETWORKDAYS，根據(jù)某一特定時(shí)期內(nèi)雇員的工作天數(shù)，計(jì)算其應(yīng)計(jì)的報(bào)酬。說(shuō)明：參數(shù)start_date表示開始日期，參數(shù)end_date表示結(jié)束日期，參數(shù)holidays在工作日中排除的特定日期。2、TODAYTODAY函數(shù)用于返回當(dāng)前日期。該值在打開工作簿或重新參與運(yùn)算時(shí)隨著系統(tǒng)日期的變化而變化。561、NETWORKDAYS2、TODAY57[案例要求]某企業(yè)接到若干訂單，從接到訂單開始，要求若干個(gè)工作日后必須交貨，現(xiàn)已知開始日期和完成日期，需要計(jì)算出完成任務(wù)所需工作日。案例6-6生產(chǎn)任務(wù)工作日計(jì)算6.2.3工時(shí)數(shù)計(jì)算

在企業(yè)生產(chǎn)中，經(jīng)常會(huì)根據(jù)工作開始和結(jié)束的時(shí)間，來(lái)計(jì)算工作時(shí)數(shù)，但如果上夜班，工作結(jié)束時(shí)間會(huì)小于開始時(shí)間，簡(jiǎn)單地加減運(yùn)算往往不能得到正確結(jié)果。本節(jié)將介紹如何解決此類問題。57[案例要求]某企業(yè)接到若干訂單，從接到訂單開始，要求若干58[案例要求]某企業(yè)每天要統(tǒng)計(jì)員工的工作開始時(shí)間和結(jié)束時(shí)間，并計(jì)算出每天的工時(shí)數(shù)。案例6-7工時(shí)數(shù)計(jì)算58[案例要求]某企業(yè)每天要統(tǒng)計(jì)員工的工作開始時(shí)間和結(jié)束時(shí)間596.3企業(yè)經(jīng)營(yíng)管理

除了前面介紹的日期時(shí)間函數(shù)外，還有很多EXCEL函數(shù)經(jīng)常應(yīng)用到企業(yè)的生產(chǎn)和經(jīng)營(yíng)管理中，本節(jié)將通過案例對(duì)幾個(gè)常用函數(shù)進(jìn)行介紹。6.3.1藥品研發(fā)階段成本計(jì)算

在企業(yè)的經(jīng)營(yíng)管理中，經(jīng)常會(huì)根據(jù)產(chǎn)品的月成本、產(chǎn)品開發(fā)周期，計(jì)算產(chǎn)品在不同開發(fā)階段的總成本，從而進(jìn)行調(diào)控。要解決此類問題，需要用到OFFSET函數(shù)，該函數(shù)語(yǔ)法及用法如下。596.3企業(yè)經(jīng)營(yíng)管理除了前面介紹的日期時(shí)間函數(shù)外60OFFSET格式：OFFSET(reference,rows,cols,height,width)功能：OFFSET函數(shù)以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個(gè)單元格或單元格區(qū)域。說(shuō)明：參數(shù)Reference作為偏移量參照系的引用區(qū)域，可以是單元格或區(qū)域。參數(shù)Rows表示相對(duì)于偏移量參照系的左上角單元格，進(jìn)行上下偏移的行數(shù)，參數(shù)Cols則表示左（右）偏移的列數(shù)。參數(shù)Height表示所要返回的引用區(qū)域的行數(shù)，參數(shù)width表示所要返回的列數(shù)。60OFFSET61[案例要求]某公司研發(fā)的每種藥品經(jīng)過三個(gè)研發(fā)階段。已有每種藥品按月列出的成本表，已知每個(gè)研發(fā)階段的時(shí)長(zhǎng)，如何計(jì)算每種藥品在每個(gè)研發(fā)階段的總成本。案例6-8藥品研發(fā)階段成本計(jì)算6.3.2產(chǎn)品訂單數(shù)量與單價(jià)的管理

在企業(yè)的生產(chǎn)與經(jīng)營(yíng)管理中，經(jīng)常會(huì)按照產(chǎn)品訂單的數(shù)量制定單價(jià)，本節(jié)將介紹此類問題的解決辦法。61[案例要求]某公司研發(fā)的每種藥品經(jīng)過三個(gè)研發(fā)階段。已有每62[案例要求]某公司的產(chǎn)品單價(jià)與訂購(gòu)數(shù)量和付款方式有關(guān)，定價(jià)規(guī)則如下：（1）公司為產(chǎn)品A定價(jià)，訂購(gòu)數(shù)量在500件以下（含500件）的訂單單價(jià)為25元，訂購(gòu)數(shù)量在500件以上的訂單單價(jià)為20元。（2）公司為產(chǎn)品B定價(jià)，訂購(gòu)數(shù)量在500件以上（不含500件）或一次性付款的訂單單價(jià)為15元，否則單價(jià)為20元。換句話說(shuō)，訂購(gòu)數(shù)量在500件以下（含500件）并且分期支付的訂單單價(jià)為20元，否則單價(jià)為15元。編寫單價(jià)與訂單數(shù)量和付款方式之間的函數(shù)關(guān)系式，并計(jì)算貨款總額。案例6-9產(chǎn)品訂單數(shù)量與單價(jià)的管理62[案例要求]某公司的產(chǎn)品單價(jià)與訂購(gòu)數(shù)量和付款方式有關(guān)，定63[案例要求]某公司有10家連鎖商店，已知連鎖商店的各類產(chǎn)品的銷售總額。打開案例6-10.xlsx工作簿，有“銷售總額”、“商店銷售總額”和“產(chǎn)品銷售總額”工作表，查詢各商店的銷售總額，查詢各類產(chǎn)品的銷售總額。案例6-10連鎖店與各類產(chǎn)品銷售情況匯總表6.3.3連鎖店與各類產(chǎn)品銷售情況匯總表

在企業(yè)的經(jīng)營(yíng)管理中，經(jīng)常會(huì)在各類情況表中搜索需要的信息進(jìn)行匯總。Excel中的LOOKUP、VLOOKUP、HLOOKUP函數(shù)可以解決此類問題，本節(jié)將重點(diǎn)講述這3個(gè)函數(shù)在企業(yè)經(jīng)營(yíng)管理中的應(yīng)用。63[案例要求]某公司有10家連鎖商店，已知連鎖商店的各類產(chǎn)

第七章Excel在市場(chǎng)銷售管理中的應(yīng)用

6465員工銷售量查詢庫(kù)存產(chǎn)品查詢產(chǎn)品批發(fā)零售銷售額及利潤(rùn)計(jì)算銷售金額等級(jí)評(píng)價(jià)與獎(jiǎng)金員工業(yè)績(jī)考核管理編制進(jìn)貨單本章學(xué)習(xí)目標(biāo)65員工銷售量查詢本章學(xué)習(xí)目標(biāo)66員工銷售量查詢會(huì)用到MATCH、ADDRESS和INDIRECT函數(shù)，這3個(gè)函數(shù)介紹如下：（1）函數(shù)MATCH可在單元格區(qū)域中搜索指定項(xiàng)，然后返回該項(xiàng)在單元格區(qū)域中的相對(duì)位置。語(yǔ)法如下：MATCH(lookup_value,lookup_array,[match_type])（2）函數(shù)ADDRESS可以根據(jù)指定行號(hào)和列號(hào)獲得工作表中的某個(gè)單元格的地址。語(yǔ)法如下：ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])7.1員工銷售量查詢66員工銷售量查詢會(huì)用到MATCH、ADDRESS和INDI67（3）INDIRECT函數(shù)返回由文本字符串指定的引用。語(yǔ)法如下：INDIRECT(ref_text,[a1])

員工銷售量查詢[案例要求]打開案例7-1.xlsx工作簿，進(jìn)行如下操作：（1）在“員工銷售量”工作表中，有10位員工的產(chǎn)品銷售數(shù)據(jù)，根據(jù)該數(shù)據(jù)在“員工銷售量查詢”工作表中，利用數(shù)據(jù)驗(yàn)證設(shè)置“月份”與“姓名”的下拉選擇列表，從而可以選擇員工的姓名及月份。案例7-167（3）INDIRECT函數(shù)返回由文本字符串指定的引用。語(yǔ)68（2）在“員工銷售量查詢”工作表中，選定員工的姓名及銷售月份后，通過ADDRESS和MATCH函數(shù)找到所要查詢信息在工作簿中單元格的位置；根據(jù)查詢信息所在位置的單元格，通過INDIRECT函數(shù)查詢出本月銷售量。7.2庫(kù)存產(chǎn)品查詢對(duì)庫(kù)存產(chǎn)品進(jìn)行查詢需要用到一些函數(shù)，如ADDRESS、MATCH、ROW、COLUMN、INDIRECT和INDEX函數(shù)等，其中ROW、COLUMN、INDIRECT和INDEX函數(shù)的語(yǔ)法和用法如下。68（2）在“員工銷售量查詢”工作表中，選定員工的姓名及銷售69（1）函數(shù)ROW返回引用的行號(hào)。語(yǔ)法如下：ROW([reference])（2）函數(shù)COLUMN返回引用的列標(biāo)。語(yǔ)法如下：COLUMN([reference])（3）函數(shù)INDIRECT返回由文本字符串指定的引用，該函數(shù)立即對(duì)引用進(jìn)行計(jì)算，并顯示其內(nèi)容。語(yǔ)法如下：INDIRECT(ref_text,[a1])（4）函數(shù)INDEX返回指定的行與列交叉處的單元格引用。語(yǔ)法如下：INDEX(reference,row_num,[column_num],[area_num])69（1）函數(shù)ROW返回引用的行號(hào)。語(yǔ)法如下：70庫(kù)存產(chǎn)品查詢[案例要求]打開案例7-2.xlsx工作簿，進(jìn)行如下操作：（1）在“庫(kù)存表”工作表中給出了產(chǎn)品信息，根據(jù)該信息在“產(chǎn)品庫(kù)存查詢”工作表中輸入抽查編號(hào)，通過ADDRESS和MATCH函數(shù)找到所要抽查的編號(hào)在工作簿中單元格的位置。（2）根據(jù)B2單元格中查詢出的結(jié)果，通過ROW和COLUMN函數(shù)顯示所查詢出的單元格所在的行和列。（3）通過INDEX函數(shù)找到與抽查編號(hào)所對(duì)應(yīng)的產(chǎn)品名稱和庫(kù)存量。案例7-270案例7-2717.3產(chǎn)品批發(fā)零售銷售額及利潤(rùn)計(jì)算統(tǒng)計(jì)產(chǎn)品的銷售額和銷售利潤(rùn)，會(huì)用到兩個(gè)查找函數(shù)：VLOOKUP（按列查找）和HLOOKUP（按行查找）。這兩個(gè)函數(shù)的語(yǔ)法如下。VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])717.3產(chǎn)品批發(fā)零售銷售額及利潤(rùn)計(jì)算72產(chǎn)品批發(fā)零售銷售額及利潤(rùn)計(jì)算[案例要求]打開案例7-3.xlsx工作簿，在“價(jià)格表”工作表中已經(jīng)給出了家電產(chǎn)品的進(jìn)貨、批發(fā)和零售價(jià)格。進(jìn)行如下操作：（1）在“銷售額統(tǒng)計(jì)”工作表中，完成批發(fā)價(jià)格、零售價(jià)格、批發(fā)金額、零售金額和進(jìn)貨金額的計(jì)算。（2）在“銷售利潤(rùn)”工作表中，完成進(jìn)貨金額、批發(fā)金額、零售金額和利潤(rùn)的計(jì)算。案例7-372案例7-3737.4銷售金額等級(jí)評(píng)價(jià)與獎(jiǎng)金對(duì)銷售金額等級(jí)進(jìn)行評(píng)價(jià)和獎(jiǎng)金管理，需要用到查找函數(shù)VLOOKUP或LOOKUP，選擇函數(shù)CHOOSE。簡(jiǎn)單介紹LOOKUP和CHOOSE函數(shù)如下。（1）LOOKUP函數(shù)從單行或單列區(qū)域（區(qū)域:工作表上的兩個(gè)或多個(gè)單元格。區(qū)域中的單元格可以相鄰或不相鄰。）或數(shù)組（數(shù)組:用于建立可生成多個(gè)結(jié)果或可對(duì)在行和列中排列的一組參數(shù)進(jìn)行運(yùn)算的單個(gè)公式。數(shù)組區(qū)域共用一個(gè)公式；數(shù)組常量是用作參數(shù)的一組常量。）返回值。它有兩種語(yǔ)法形式：向量形式和數(shù)組形式。737.4銷售金額等級(jí)評(píng)價(jià)與獎(jiǎng)金747.4銷售金額等級(jí)評(píng)價(jià)與獎(jiǎng)金向量形式：在單行區(qū)域或單列區(qū)域（稱為“向量”）中查找值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的值。當(dāng)要查詢的值列表較大或者值可能會(huì)隨時(shí)間而改變時(shí)，使用該形式。語(yǔ)法如下。LOOKUP(lookup_value,lookup_vector,[result_vector])注意：result_vector參數(shù)必須與lookup_vector參數(shù)大小相同。數(shù)組形式：在區(qū)域（稱為“數(shù)組”）的第一行或第一列中查找指定的值，然后返回區(qū)域的最后一行或最后一列中相同位置的值；當(dāng)要查詢的值列表較小或者值在一段時(shí)間內(nèi)保持不變時(shí)，使用該形式。語(yǔ)法如下。LOOKUP(lookup_value,array)747.4銷售金額等級(jí)評(píng)價(jià)與獎(jiǎng)金757.4銷售金額等級(jí)評(píng)價(jià)與獎(jiǎng)金（2）選擇函數(shù)CHOOSE，語(yǔ)法如下。CHOOSE(index_num,value1,[value2],...)參數(shù)index_num是必需的，必須是介于1到254之間的數(shù)字，或是包含1到254之間的數(shù)字的公式或單元格引用。如果index_num為1，則CHOOSE返回value1；如果為2，則CHOOSE返回value2，以此類推；如果index_num小于1或大于列表中最后一個(gè)值的索引號(hào)，則CHOOSE返回錯(cuò)誤值；如果index_num為小數(shù)，則截尾取整。757.4銷售金額等級(jí)評(píng)價(jià)與獎(jiǎng)金76銷售金額等級(jí)評(píng)價(jià)與獎(jiǎng)金[案例要求]打開案例7-4.xlsx工作簿，進(jìn)行如下操作：（1）在“月銷售金額統(tǒng)計(jì)”表中，計(jì)算出銷售金額。（2）在“評(píng)價(jià)等級(jí)”工作表中，給出了不同銷售金額范圍的評(píng)分和等級(jí)標(biāo)準(zhǔn)，計(jì)算“銷售金額評(píng)價(jià)與獎(jiǎng)金”工作表中的評(píng)分、等級(jí)和獎(jiǎng)金；其中評(píng)分用LOOKUP函數(shù)來(lái)計(jì)算；等級(jí)和獎(jiǎng)金的計(jì)算用CHOOSE函數(shù)來(lái)完成。獎(jiǎng)金的發(fā)放標(biāo)準(zhǔn)為：優(yōu)+5000元、優(yōu)3000元、良1000元、中500元、差0元。（3）根據(jù)“月銷售金額統(tǒng)計(jì)”表中的銷售金額數(shù)據(jù)，填充“銷售金額評(píng)價(jià)與獎(jiǎng)金”工作表中的銷售金額。案例7-476銷售金額等級(jí)評(píng)777.5員工業(yè)績(jī)考核管理對(duì)員工業(yè)績(jī)進(jìn)行考核管理，會(huì)涉及到一些計(jì)算的公式，說(shuō)明如下：（1）單價(jià)列數(shù)據(jù)通過VLOOKUP函數(shù)從“單價(jià)表”中獲取。（2）銷售金額=數(shù)量*單價(jià)。（3）總銷售額列的數(shù)據(jù)用SUMIF函數(shù)按銷售員在“月銷售明細(xì)表”中匯總。（4）提成比例列數(shù)據(jù)通過HLOOKUP函數(shù)從“提成標(biāo)準(zhǔn)”表中獲取。（5）獎(jiǎng)金=提成比例*總銷售額。777.5員工業(yè)績(jī)考核管理78員工業(yè)績(jī)考核管理[案例要求]打開案例7-5.xlsx工作簿，進(jìn)行如下操作：（1）在“月銷售明細(xì)表”工作表中計(jì)算單價(jià)和銷售金額。（2）在“月銷售業(yè)績(jī)獎(jiǎng)勵(lì)表”工作表中計(jì)算總銷售額、提成比例和獎(jiǎng)金。案例7-578案例7-579員工業(yè)績(jī)?cè)u(píng)星考核[案例要求]打開案例7-6.xlsx工作簿，進(jìn)行如下操作：（1）在“評(píng)星考核”工作表中，分別用LOOKUP和VLOOKUP計(jì)算員工的星級(jí)標(biāo)準(zhǔn)。（2）思考：兩個(gè)函數(shù)在使用上有什么區(qū)別？案例7-679案例7-6807.6編制進(jìn)貨單

編制進(jìn)貨單

[案例要求]打開“案例7-7.xlsx”工作簿，在“電腦配件分類表”工作表中，給出了各類配件的信息，要求完成進(jìn)貨單未完成部分的設(shè)計(jì)和計(jì)算。其中：（1）單價(jià)、合計(jì)金額、定金及余款設(shè)定為“會(huì)計(jì)專用”顯示格式。（2）利用數(shù)據(jù)驗(yàn)證定義下拉列表，輸入商品編號(hào)。（3）利用IF和VLOOKUP函數(shù)設(shè)置依據(jù)商品的編號(hào)自動(dòng)顯示單價(jià)數(shù)據(jù)。案例7-7807.6編制進(jìn)貨單案例7-7817.6編制進(jìn)貨單

編制進(jìn)貨單

[案例要求]（4）利用IF和OR函數(shù)設(shè)置自動(dòng)計(jì)算合計(jì)金額（5）用“自動(dòng)求和”工具，計(jì)算所有商品的總金額。（6）總價(jià)金額設(shè)定為大寫數(shù)字顯示方式。（7）預(yù)定交貨日期設(shè)定為日期格式顯示。（8）付款方式通過數(shù)據(jù)驗(yàn)證定義下拉列表選擇輸入，付款方式有現(xiàn)金、本票、支票和信用卡4種。（9）報(bào)表編號(hào)格式通過自定義方式設(shè)定，制表日期通過日期函數(shù)TODAY（）實(shí)現(xiàn)。案例7-7817.6編制進(jìn)貨單案例7-7

第八章Excel在工資績(jī)效管理中的應(yīng)用

8283建立工資表基本工資計(jì)算績(jī)效工資計(jì)算工資報(bào)表的編制工資的統(tǒng)計(jì)分析本章學(xué)習(xí)目標(biāo)83建立工資表本章學(xué)習(xí)目標(biāo)84進(jìn)行工資績(jī)效管理時(shí)，首先要根據(jù)單位的實(shí)際工資績(jī)效制度進(jìn)行需求分析，建立實(shí)體-聯(lián)系圖，確定工資表，確定各個(gè)工資表的工資項(xiàng)目。8.1.1建立實(shí)體-關(guān)系圖實(shí)體-聯(lián)系圖(EntityRelationshipDiagram)，簡(jiǎn)稱E-R圖，是用來(lái)描述現(xiàn)實(shí)實(shí)體的方法。根據(jù)實(shí)際問題，首先要抽象提取出可能的實(shí)體，確定實(shí)體的類型和屬性。其次，分析定義各實(shí)體間的聯(lián)系。完整的E-R圖能夠準(zhǔn)確地反映出現(xiàn)實(shí)問題，是現(xiàn)實(shí)問題的一種概念模型。8.1工資表的建立84進(jìn)行工資績(jī)效管理時(shí)，首先要根據(jù)單位的實(shí)際工85

ABC單位的工資績(jī)效方案

[案例要求]根據(jù)ABC單位的具體業(yè)務(wù)情況，結(jié)合工資改革的主要精神，按照國(guó)家有關(guān)政策，核算制定該單位的工資績(jī)效方案。案例8-185案例8-186案例8-2建立實(shí)體-聯(lián)系圖[案例要求]根據(jù)ABC單位的工資績(jī)效分配制度，分析建立對(duì)應(yīng)的工資實(shí)體-聯(lián)系圖。86案例8-2878.1.2工資表的建立根據(jù)所建立的實(shí)體-聯(lián)系圖，在Excel中建立各個(gè)表格，一般每個(gè)實(shí)體對(duì)應(yīng)一個(gè)基本的表格。

ABC單位的工資表的建立[案例要求]在“ABC單位工資表”工作薄中，建立各個(gè)工資表。案例8-3878.1.2工資表的建立案例8-3888.2基本工資的計(jì)算

ABC單位的各月份工資表的編制

[案例要求]

根據(jù)績(jī)效工資信息，按照月份編制ABC單位的工資表。案例8-4888.2基本工資的計(jì)算案89

ABC單位基本工資的計(jì)算

[案例要求]計(jì)算各職工的基本工資部分?；竟べY包括兩項(xiàng)：崗位工資和薪級(jí)工資。案例8-589ABC單位基本工資的計(jì)算案例8-5908.3績(jī)效工資的計(jì)算ABC單位的績(jī)效工資計(jì)算[案例要求]根據(jù)職工每月工作量的具體完成情況，計(jì)算職工的績(jī)效工資部分?？?jī)效工資包括兩項(xiàng)：基礎(chǔ)績(jī)效和崗位業(yè)績(jī)津貼。案例8-6908.3績(jī)效工資的計(jì)算案例8-6918.4工資報(bào)表的編制計(jì)算ABC單位的職工應(yīng)發(fā)工資。[案例要求]根據(jù)“ABC單位2017年度工作量表”的計(jì)算結(jié)果，計(jì)算ABC單位的應(yīng)發(fā)工資。案例8-7918.4工資報(bào)表的編制案例8-792ABC單位的工資報(bào)表的編制[案例要求]按照稅收、保險(xiǎn)等有關(guān)政策規(guī)定，完成工資報(bào)表的全部數(shù)據(jù)計(jì)算，得到一月份的最終工資報(bào)表。案例8-892ABC單位的工資報(bào)938.5工資的統(tǒng)計(jì)分析

統(tǒng)計(jì)ABC單位的一月份工資情況[案例要求]統(tǒng)計(jì)ABC單位的一月份工資的總和以及平均值，并利用圖形工具進(jìn)行分析。案例8-9938.5工資的統(tǒng)計(jì)分析案例8-994

分析ABC單位的各部門一月份工資情況[案例要求]按部門統(tǒng)計(jì)ABC單位的一月份的工作量及工資情況，并利用圖形工具進(jìn)行分析。案例8-1094分析ABC單位的各部門一月份工958.6年度工資績(jī)效計(jì)算分析8.6.1編制二月份、三月份工資表

本節(jié)會(huì)用到REPLACE,FIND和CELL函數(shù)，這3個(gè)函數(shù)的語(yǔ)法及用法介紹如下。

1．REPLACE格式：REPLACE(old_text,start_num,num_chars,new_text)功能：返回用新內(nèi)容替換原有內(nèi)容后的值。說(shuō)明：Old_text：欲被替換其部分內(nèi)容的文本。Start_num：開始位置。Num_chars：字符個(gè)數(shù)。Num_bytes：字節(jié)個(gè)數(shù)。New_text：欲替換的文本。REPLACE函數(shù)示例：(1)設(shè)A2的值為“同學(xué)們?cè)缟虾谩?則=REPLACE(A2,4,2,"下午")的結(jié)果為“同學(xué)們下午好”。（2）設(shè)A2的值為2017=REPLACE(A2,3,2,"20")的值為2020。958.6年度工資績(jī)效計(jì)算分析962.FIND格式：find(find_text,within_text,start_num)功能：返回一個(gè)內(nèi)容在另一個(gè)內(nèi)容中所在的位置。說(shuō)明：find_text：要查找的內(nèi)容。within_text：要查找的單元格。start_num：起始位置?？墒÷?，默認(rèn)為1。FIND函數(shù)示例：設(shè)A2的值為“abcdefg”，則=FIND("d",A2,2)的結(jié)果為4。962.FIND97選項(xiàng)含義"address"單元格的名稱。"col"單元格的列標(biāo)。"color"單元格的負(fù)值是否以不同顏色顯示，是為1，否為0。"contents"單元格的值。"filename"當(dāng)前工作表的名稱全名。"format"單元格的格式。"parentheses"是否加括號(hào)，是為1，否為0。"prefix"前綴。"protect"是否鎖定，是為1，否為0。"row"單元格的行號(hào)。"type"數(shù)據(jù)類型。"width"單元格的列寬。3.CELL格式：CELL(info_type,[reference])功能：返回指定單元的信息，包括格式、位置或值等。說(shuō)明：Reference：欲取得其信息的單元?？墒÷裕J(rèn)為最后被更改的單元格。info_type：欲獲得信息的類型，可為12種選項(xiàng)，如表8-7所示。表8-7參數(shù)info_type的選項(xiàng)CELL函數(shù)示例：（1）=CELL("address",A10:B15)的值為$A$10。（2）=CELL("row",A10:B15)的值為10。97選項(xiàng)含義"address"單元格的名稱。"col"單元格988-11

編制計(jì)算ABC單位的二月份、三月份的工資表[案例要求]根據(jù)二月份、三月份職工具體完成的工作量情況(如圖8-21,、8-22所示)，編制）二月份、三月份的工資表。988-11998.6.2一季度工作量、工資情況匯總分析案例8-12ABC單位一季度工作量、工資情況匯總分析[案例要求]要求：根據(jù)一月份、二月份、三月份的工資表，統(tǒng)計(jì)分析一季度ABC單位及其各部門工作量完成以及工資分配情況。998.6.2一季度工作量、工資情況匯總分析案例8-121008.6.3各季度工作量、工資情況變化趨勢(shì)分析案例8-13ABC單位各季度工作量、工資情況變化趨勢(shì)分析[案例要求]根據(jù)四月份至十二月份的工資表，統(tǒng)計(jì)匯總二、三、四季度的工作量、工資情況，分析四個(gè)季度的變化趨勢(shì)。1008.6.3各季度工作量、工資情況變化趨勢(shì)分析案例8-11018.6.4全年績(jī)效工資改革情況分析案例8-14ABC單位全年績(jī)效工資改革情況分析[案例要求]根據(jù)全年共12個(gè)月的工資表，分析ABC單位績(jī)效工資改革情況。1018.6.4全年績(jī)效工資改革情況分析案例8-141028.6.5各部門全年實(shí)際工作量、工資情況分析案例8-15ABC單位全年各部門具體情況分析[案例要求]根據(jù)全年的工資表，分析ABC單位的各部門的工資績(jī)效情況。1028.6.5各部門全年實(shí)際工作量、工資情況分析案例8-1

第五章Excel在數(shù)據(jù)統(tǒng)計(jì)與分析中的應(yīng)用

103104了解數(shù)組及數(shù)組公式掌握常用數(shù)學(xué)和統(tǒng)計(jì)函數(shù)的基本用法學(xué)會(huì)在實(shí)際事務(wù)中靈活使用數(shù)學(xué)和統(tǒng)計(jì)函數(shù)本章學(xué)習(xí)目標(biāo)1了解數(shù)組及數(shù)組公式本章學(xué)習(xí)目標(biāo)105Excel的數(shù)組公式很有用，尤其在需要一次性獲得多個(gè)運(yùn)算結(jié)果時(shí)，數(shù)組公式就顯得特別重要，了解Excel數(shù)組及數(shù)組公式可以為實(shí)際數(shù)據(jù)處理問題提供更多的解決方案。5.1.1認(rèn)識(shí)數(shù)組和數(shù)組公式Excel數(shù)組是以常量數(shù)據(jù)為元素的集合，其元素的數(shù)據(jù)類型可以是數(shù)值、文本和邏輯型。特別的，數(shù)組中也可以僅有一個(gè)元素，即為我們之前熟悉的單值常量。引入數(shù)組概念后，Excel中輸入的公式當(dāng)以Ctrl+Shift+Enter組合鍵確認(rèn)時(shí)即為數(shù)組公式，其中參與運(yùn)算的數(shù)組可以是常量數(shù)組或區(qū)域數(shù)組。5.1數(shù)組公式及數(shù)據(jù)處理應(yīng)用2Excel的數(shù)組公式很有用，尤其在需要一次性獲得多個(gè)運(yùn)算106進(jìn)行數(shù)組公式運(yùn)算時(shí)，要先選擇用來(lái)存放結(jié)果的單元格區(qū)域（可以是一個(gè)單元格），然后輸入數(shù)組公式，按Ctrl+Shift+Enter組合鍵結(jié)束，Excel將在公式兩邊自動(dòng)加上花括號(hào)“{}”。5.1.2數(shù)組的常見形式1.常量數(shù)組用一對(duì)花括號(hào)“{}”把構(gòu)成數(shù)組的常量數(shù)據(jù)括起來(lái)，就是常量數(shù)組。同行數(shù)據(jù)之間用逗號(hào)“，”分割，同列數(shù)據(jù)之間用分號(hào)“；”分割。2.區(qū)域數(shù)組區(qū)域數(shù)組就是通過對(duì)一組連續(xù)的單元格形成的矩形區(qū)域進(jìn)行引用而得到的數(shù)組。3.內(nèi)存數(shù)組內(nèi)存數(shù)組是指在數(shù)組公式計(jì)算過程中生成的中間結(jié)果值。3進(jìn)行數(shù)組公式運(yùn)算時(shí)，要先選擇用來(lái)存放結(jié)果的單元格區(qū)域（可以1075.1.3數(shù)組間的運(yùn)算1.二維數(shù)組間的運(yùn)算其結(jié)果由對(duì)各數(shù)組相同位置的元素進(jìn)行相同的運(yùn)算而獲得，若參與運(yùn)算的數(shù)組的行列數(shù)不匹配，結(jié)果數(shù)組的大小應(yīng)該和最大的行列數(shù)匹配，但有效數(shù)據(jù)區(qū)域和最小的行列數(shù)匹配。2.二維數(shù)組與一維數(shù)組的運(yùn)算若一維數(shù)組是行數(shù)組，其運(yùn)算結(jié)果相當(dāng)于原值擴(kuò)展其行與二維數(shù)組行相同后，再與二維數(shù)組進(jìn)行的運(yùn)算；若一維數(shù)組是列數(shù)組，其運(yùn)算結(jié)果相當(dāng)于原值擴(kuò)展其列與二維數(shù)組列相同后，再與二維數(shù)組進(jìn)行的運(yùn)算，實(shí)質(zhì)歸結(jié)為二維數(shù)組間的運(yùn)算。3.一維數(shù)組間的運(yùn)算45.1.3數(shù)組間的運(yùn)算108若同是行數(shù)組或同是列數(shù)組運(yùn)算時(shí)，其運(yùn)算規(guī)則同二維數(shù)組間的運(yùn)算。若是行數(shù)組和列數(shù)組運(yùn)算時(shí)，其運(yùn)算結(jié)果相當(dāng)于原值擴(kuò)展行數(shù)組的行與列數(shù)組的行相同，同時(shí)原值擴(kuò)展列數(shù)組的列與行數(shù)組的列相同后，再進(jìn)行的二維數(shù)組間的運(yùn)算。4.數(shù)組與單一數(shù)據(jù)的運(yùn)算其運(yùn)算結(jié)果相當(dāng)于原值擴(kuò)展單一數(shù)據(jù)為與其運(yùn)算的數(shù)組的行列數(shù)一樣的數(shù)組后，再進(jìn)行的運(yùn)算。以上數(shù)組運(yùn)算全部以加法示例，其他如減法、乘法及比較等運(yùn)算雷同。統(tǒng)計(jì)銷售額[案例要求]打開“案例5-1.xlsx”工作簿，“銷售表”中記錄了各產(chǎn)品的銷售數(shù)量和銷售單價(jià)。根據(jù)已提供數(shù)據(jù)，分別用非數(shù)組公式數(shù)組的概念案例5-15若同是行數(shù)組或同是列數(shù)組運(yùn)算時(shí)，其運(yùn)算規(guī)則同二維數(shù)組間的運(yùn)109和數(shù)組公式兩種方法，計(jì)算每種產(chǎn)品的銷售金額以及所有產(chǎn)品的銷售總額。本案例實(shí)現(xiàn)效果如圖數(shù)組的聲明6和數(shù)組公式兩種方法，計(jì)算每種產(chǎn)品的銷售金額以及所有產(chǎn)品的銷110在Excel環(huán)境下，對(duì)數(shù)據(jù)進(jìn)行基本處理和分析時(shí)，諸如求和、求平均值、求最大最小值、計(jì)數(shù)等是最常見的要求，可以由Excel提供的常用數(shù)學(xué)和統(tǒng)計(jì)函數(shù)實(shí)現(xiàn)。5.2.1企業(yè)產(chǎn)品總量統(tǒng)計(jì)常用實(shí)現(xiàn)求和函數(shù)如下：1.SUM格式：SUM(number1,[number2],...)功能：計(jì)算參數(shù)列表中數(shù)值的和。說(shuō)明：?jiǎn)卧褚脜^(qū)域中的空值、文本值和邏輯值都被按數(shù)值0處理。2.SUMIF格式：SUMIF(range,criteria,[sum_range])5.2常用數(shù)學(xué)統(tǒng)計(jì)函數(shù)及數(shù)據(jù)處理應(yīng)用7在Excel環(huán)境下，對(duì)數(shù)據(jù)進(jìn)行基本處理和分析時(shí)，諸如求和、111功能：計(jì)算區(qū)域中滿足單一條件的單元格數(shù)值的和。說(shuō)明：range必需。根據(jù)條件進(jìn)行計(jì)算的單元格區(qū)域。Criteria必需。對(duì)range指定區(qū)域的限定條件，其形式可以為數(shù)值、表達(dá)式、單元格引用、文本或函數(shù)，任何文本條件或任何含有邏輯或數(shù)學(xué)符號(hào)的條件都必須使用雙引號(hào)“””括起來(lái)。如果條件為數(shù)值，則無(wú)需使用雙引號(hào)。sum_range可選。要求和的單元格區(qū)域，空值和文本值將被忽略。如果缺省，Excel會(huì)對(duì)在range參數(shù)中指定的單元格（即滿足條件的單元格）求和。3.SUMIFS格式：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)功能：計(jì)算區(qū)域中滿足多個(gè)條件的單元格數(shù)值的和。8功能：計(jì)算區(qū)域中滿足單一條件的單元格數(shù)值的和。112說(shuō)明：Sum_range必需。要求和的單元格區(qū)域。Criteria_range1必需。應(yīng)用Criteria1條件測(cè)試的區(qū)域。Criteria1必需。測(cè)試條件。Criteria_range1和Criteria1設(shè)置用于搜索某個(gè)區(qū)域是否符合特定條件的搜索對(duì)。一旦在該區(qū)域中找到了滿足條件的項(xiàng)，將計(jì)算sum_range中相應(yīng)值的和。Criteria_range2,criteria2,…可選。附加的區(qū)域及其關(guān)聯(lián)條件，最多可以輸入127個(gè)區(qū)域/條件對(duì)。全部缺省時(shí)，功能同SUMIF。僅在與sum_range參數(shù)中的單元格同一位置的各條件區(qū)域的值滿足各自相應(yīng)的指定條件時(shí)，才將該單元格數(shù)值計(jì)入總和中。4.SUMPRODUCT9說(shuō)明：Sum_range必需。要求和的單元格區(qū)域。113格式：SUMPRODUCT(array1,[array2],[array3],...)功能：在給定的幾組數(shù)組中，先進(jìn)行數(shù)組間的乘法運(yùn)算，最后把乘積結(jié)果集的元素相加。這是SUMPRODUCT函數(shù)的基本用法。SUMPRODUCT函數(shù)功能結(jié)合“5.1.3數(shù)組間的運(yùn)算”一節(jié)的內(nèi)容更易于理解。說(shuō)明：Array1必需。Array2,array3,...可選。2到255個(gè)數(shù)組參數(shù)。全部缺省時(shí)，SUMPRODUCT函數(shù)的使用同SUM函數(shù)。數(shù)組參量間的分隔符為逗號(hào)“，”時(shí)，函數(shù)SUMPRODUCT將文本、邏輯型的數(shù)組元素作為0處理，并且要求各個(gè)數(shù)組參數(shù)的行列數(shù)必須匹配，否則，函數(shù)SUMPRODUCT將返回#VALUE!。如果希望處理行列數(shù)不匹配的數(shù)組參數(shù)的運(yùn)算，需要把數(shù)10格式：SUMPRODUCT(array1,[array114組間的分隔符由逗號(hào)“，”換為星號(hào)“*”。數(shù)組間的乘法運(yùn)算規(guī)則同“5.1.3數(shù)組間的運(yùn)算”一節(jié)的講解，若乘積運(yùn)算的結(jié)果集中有錯(cuò)誤值#N/A時(shí)，SUMPRODUCT將返回#N/A。參與運(yùn)算的數(shù)據(jù)區(qū)域若有文本數(shù)據(jù)，會(huì)按0處理；若有邏輯數(shù)據(jù)時(shí)，F(xiàn)ALSE按0處理，TREU按1處理。SUMPRODUCT函數(shù)還有兩種擴(kuò)展用法，介紹如下：（1）格式：SUMPRODUCT((criteria_array1)*[(criteria_array2)]*[(criteria_array3)]*...)功能：實(shí)現(xiàn)多條件計(jì)數(shù)。說(shuō)明：criteria_array1必需。是條件，其實(shí)質(zhì)是數(shù)組的比較運(yùn)算，結(jié)果值是以TREU或FALSE為元素的數(shù)組。(criteria_array2),(criteria_array3),...可選。需要滿足的更多條件。若全部缺省，SUMPRODUCT函數(shù)的結(jié)果值為0。11組間的分隔符由逗號(hào)“，”換為星號(hào)“*”。數(shù)組間的乘法運(yùn)算115（2）格式：SUMPRODUCT((criteria_array1)*[(criteria_array2)]*[(criteria_array3)]*...*sum_array)功能：實(shí)現(xiàn)條件求和。說(shuō)明：sum_array必需。是求和區(qū)域。運(yùn)算流程和條件計(jì)數(shù)雷同，只是增加了求和區(qū)域。最后的求和結(jié)果相當(dāng)于把求和區(qū)域中滿足這樣條件的數(shù)計(jì)入總和，即比較運(yùn)算符前的各數(shù)組中與該數(shù)相同位置上的元素同時(shí)滿足各自對(duì)應(yīng)的條件。運(yùn)算中若有數(shù)組行列數(shù)不匹配的問題，運(yùn)算規(guī)則與前面講過的算術(shù)運(yùn)算一樣。統(tǒng)計(jì)企業(yè)產(chǎn)品組裝量[案例要求]打開“案例5-2.xlsx”工作簿，“sheet1”表中按月記錄了某企業(yè)組裝車間員工一季度組裝某產(chǎn)品的數(shù)量，如圖5-14所示，案例5-212（2）格式：SUMPRODUCT((criteria_a116根據(jù)已提供數(shù)據(jù)，完成以下操作：（1）“Sheet1”表中統(tǒng)計(jì)每位員工的一季度組裝量。（2）“Sheet2”表中按組統(tǒng)計(jì)每月和一季度組裝總量。（3）分別在“Sheet3”表、“Sheet4”表和“Sheet5”表中用不同的公式按組統(tǒng)計(jì)男女員工的一季度組裝總量。本案例實(shí)現(xiàn)主要效果圖如下：13根據(jù)已提供數(shù)據(jù)，完成以下操作：1175.2.2企業(yè)人數(shù)分段統(tǒng)計(jì)常用實(shí)現(xiàn)計(jì)數(shù)函數(shù)如下：1.COUNT格式：COUNT(value1,[value2],...)功能：計(jì)算參數(shù)列表中數(shù)值的個(gè)數(shù)。說(shuō)明：value1必需。要計(jì)算其中數(shù)值的個(gè)數(shù)的第一項(xiàng)，可以是常量或單元格引用。145.2.2企業(yè)人數(shù)分段統(tǒng)計(jì)118value2,...可選。要計(jì)算其中數(shù)值的個(gè)數(shù)的其他項(xiàng)，最多可包含255個(gè)。如果參數(shù)為數(shù)值、邏輯值、日期或者代表數(shù)字的文本（例如，用引號(hào)引起的數(shù)字，如“1”），則將被計(jì)算在內(nèi)。錯(cuò)誤值或不能轉(zhuǎn)換為數(shù)值的文本，則不會(huì)被計(jì)算在內(nèi)。如果參數(shù)是一個(gè)數(shù)組或引用，則只計(jì)算其中的數(shù)值，空白單元格、邏輯值、文本或錯(cuò)誤值將不計(jì)算在內(nèi)。2.COUNTA格式：COUNTA(value1,[value2],...)功能：計(jì)算區(qū)域中非空