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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

在企業(yè)的生產(chǎn)與經(jīng)營管理中，經(jīng)常會按照產(chǎn)品訂單的數(shù)量制定單價，本節(jié)將介紹此類問題的解決辦法。61[案例要求]某公司研發(fā)的每種藥品經(jīng)過三個研發(fā)階段。已有每62[案例要求]某公司的產(chǎn)品單價與訂購數(shù)量和付款方式有關(guān)，定價規(guī)則如下：（1）公司為產(chǎn)品A定價，訂購數(shù)量在500件以下（含500件）的訂單單價為25元，訂購數(shù)量在500件以上的訂單單價為20元。（2）公司為產(chǎn)品B定價，訂購數(shù)量在500件以上（不含500件）或一次性付款的訂單單價為15元，否則單價為20元。換句話說，訂購數(shù)量在500件以下（含500件）并且分期支付的訂單單價為20元，否則單價為15元。編寫單價與訂單數(shù)量和付款方式之間的函數(shù)關(guān)系式，并計算貨款總額。案例6-9產(chǎn)品訂單數(shù)量與單價的管理62[案例要求]某公司的產(chǎn)品單價與訂購數(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)營管理中，經(jīng)常會在各類情況表中搜索需要的信息進行匯總。Excel中的LOOKUP、VLOOKUP、HLOOKUP函數(shù)可以解決此類問題，本節(jié)將重點講述這3個函數(shù)在企業(yè)經(jīng)營管理中的應(yīng)用。63[案例要求]某公司有10家連鎖商店，已知連鎖商店的各類產(chǎn)

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

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

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

編制進貨單

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

編制進貨單

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

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

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

ABC單位的工資績效方案

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

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

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

[案例要求]

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

ABC單位基本工資的計算

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

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

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

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

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

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

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

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