Excel講解及練習(xí)制作銷售利潤(rùn)提成表

1、項(xiàng)目四 制作銷售利潤(rùn)提成表（替換原項(xiàng)目四）項(xiàng)目說明銷售利潤(rùn)提成表是企業(yè)記錄銷售利潤(rùn)的表單，對(duì)銷售利潤(rùn)進(jìn)行記錄是企業(yè)進(jìn)行銷售管理的重要組成部分，本項(xiàng)目完成銷售利潤(rùn)提成表的制作并作相關(guān)計(jì)算。項(xiàng)目分析本項(xiàng)目要求建立銷售利潤(rùn)提成表，通過使用合并計(jì)算功能計(jì)算提成工資。為了更好地了解銷售利潤(rùn)情況，利用單變量求解實(shí)現(xiàn)利潤(rùn)最大化，最后利用篩選功能對(duì)銷售利潤(rùn)表進(jìn)行比較與分析。任務(wù)1 建立銷售利潤(rùn)提成表和成員分配表分析或說明：本任務(wù)是建立如圖3-4-1所示的銷售利潤(rùn)提成表和成員分配表。步驟： 1、 啟動(dòng)Excel應(yīng)用程序，單擊“文件”“另存為”菜單項(xiàng)，在彈出的“另存為”對(duì)話框中，輸入文件名“銷售利潤(rùn)提成表”，文件

2、類型為“.xls”。圖3-4-1 銷售利潤(rùn)提成表2、 雙擊標(biāo)簽“Sheet1”，將工作表重命名為“銷售利潤(rùn)提成表”。3、 在A1單元格輸入“銷售利潤(rùn)提成表”，選擇A1：K1數(shù)據(jù)區(qū)域，單擊工具欄上的“居中并合并”按鈕，使標(biāo)題居中顯示。4、 單擊“格式”“單元格”菜單項(xiàng)，在彈出的對(duì)話框中選擇“字體”選項(xiàng)卡，把標(biāo)題文字設(shè)為宋體、16號(hào)。5、 參照?qǐng)D3-4-1在相應(yīng)單元格中輸入“姓名”、“售出數(shù)量”、“單價(jià)”等其余數(shù)據(jù)。6、 選擇C4：I14數(shù)據(jù)區(qū)域，單擊“格式”“單元格”菜單項(xiàng)，在彈出的對(duì)話框中選擇“數(shù)字”選項(xiàng)卡，在“分類”中選擇“貨幣”項(xiàng)，設(shè)置小數(shù)位數(shù)為1。7、 選擇A3：K3數(shù)據(jù)區(qū)域，單擊“格式

3、”“單元格”菜單項(xiàng)，在彈出的對(duì)話框中選擇“圖案”選項(xiàng)卡，選擇一種底紋顏色。8、 在對(duì)話框中單擊“邊框”選項(xiàng)卡，在“線條樣式”中選擇粗實(shí)線，單擊“預(yù)置”中的“外邊框”按鈕，給表格加上外邊框。9、 再在“線條樣式”中選細(xì)虛線，單擊“預(yù)置”中的“內(nèi)部”按鈕，給表格加上內(nèi)邊框，單擊“確定”按鈕。10、 單擊“工具”“選項(xiàng)”菜單項(xiàng)，在彈出的“選項(xiàng)”對(duì)話框中單擊“視圖”選項(xiàng)卡，單擊“網(wǎng)格線”復(fù)選框使其不選中，單擊“確定”按鈕。11、 雙擊標(biāo)簽“Sheet2”，將工作表重命名為“成員分配表”。12、 參照?qǐng)D3-4-2輸入成員分配表的數(shù)據(jù)。圖3-4-2 成員分配表任務(wù)2 合并計(jì)算提成工資分析或說明：合并計(jì)算功

4、能可以將每個(gè)單獨(dú)的工作表中的數(shù)據(jù)合并計(jì)算到一個(gè)工作表中，也可以合并到工作簿中，以便能夠更容易地對(duì)數(shù)據(jù)進(jìn)行定期或不定期的更新匯總。本任務(wù)利用合并計(jì)算功能計(jì)算銷售利潤(rùn)提成表中的提成工資。步驟： 1、 單擊工作表標(biāo)簽切換到“銷售利潤(rùn)提成表”工作表。2、 光標(biāo)放在D4單元格，輸入公式：“=C4*B4”，按回車鍵確認(rèn)。3、 用鼠標(biāo)左鍵按鈕住D4單元格右下角的填充柄向下拖拉，計(jì)算其余人員的銷售額。4、 光標(biāo)放在K4單元格，輸入公式：“=VLOOKUP(A4,成員分配表!$A$2:$C$11,2)”，按回車鍵確認(rèn)。5、 用鼠標(biāo)左鍵按鈕住K4單元格右下角的填充柄向下拖拉，填入其余人員的職位。6、 單擊工作表標(biāo)

5、簽切換到“成員分配表”工作表。7、 光標(biāo)放在C2中，輸入公式：“=IF(B2="組長(zhǎng)",7.5%,6%)”，按回車鍵確認(rèn)。8、 用鼠標(biāo)左鍵按鈕住C4單元格右下角的填充柄向下拖拉，計(jì)算其余人員的提成利潤(rùn)分配。9、 單擊工作表標(biāo)簽切換到“銷售利潤(rùn)提成表”工作表。10、 光標(biāo)放在E4中，輸入公式：“=J4*VLOOKUP(A4,成員分配表!$A$2:$C$11,3)”，按回車鍵確認(rèn)。11、 用鼠標(biāo)左鍵按鈕住E4單元格右下角的填充柄向下拖拉，計(jì)算其余人員的提成利潤(rùn)。12、 選擇F4：F13數(shù)據(jù)區(qū)域，單擊“數(shù)據(jù)”“合并計(jì)算”菜單項(xiàng)。13、 在彈出的“合并計(jì)算”對(duì)話框中“函數(shù)”下拉列表

6、中選擇“求和”選項(xiàng)，然后單擊“引用位置”文本框右側(cè)的按鈕。14、 用鼠標(biāo)選擇H4：H13數(shù)據(jù)區(qū)域后返回“合并計(jì)算”對(duì)話框，單擊“添加”按鈕，將H4：H13數(shù)據(jù)區(qū)域添加到“所有引用位置”列表框中。15、 在“合并計(jì)算”對(duì)話框中再次單擊“引用位置”文本框右側(cè)的按鈕，用鼠標(biāo)選擇E4：E13數(shù)據(jù)區(qū)域，單擊“添加”按鈕，將E4：E13數(shù)據(jù)區(qū)域添加到“所有引用位置”列表框中，如圖3-4-3所示。16、 單擊“確定”按鈕，即可求出提成額，如圖3-4-4所示。圖3-4-3 合并計(jì)算圖3-4-4 計(jì)算提成額任務(wù)3 用單變量求解利潤(rùn)最大化分析或說明：利用單變量求解功能可以在給定公式的前提下，通過調(diào)整可變單元格中的

7、數(shù)值來尋求目標(biāo)單元格中的目標(biāo)值。本任務(wù)使用單變量求解來實(shí)現(xiàn)提成利潤(rùn)的最大化，即當(dāng)A產(chǎn)品的利潤(rùn)達(dá)到10000時(shí)售出的數(shù)量是多少。步驟： 1、 用鼠標(biāo)右鍵單擊工作表標(biāo)簽，插入一個(gè)新工作，重命名為“銷售利潤(rùn)表”。2、 參照?qǐng)D3-4-5輸入標(biāo)題和相關(guān)數(shù)據(jù)，設(shè)置單元格的格式，隱藏工作表中的網(wǎng)格線。3、 光標(biāo)放在E5單元格，輸入公式：“=C5*D5”，按回車鍵確認(rèn)。4、 用鼠標(biāo)左鍵按住E5單元格的填充柄把公式復(fù)制到E6和E7單元格。5、 光標(biāo)放在G5單元格，輸入公式：“=F5*D5”，按回車鍵確認(rèn)。6、 用鼠標(biāo)左鍵按住G5單元格的填充柄把公式復(fù)制到G6和G7單元格。7、 光標(biāo)放在H5單元格，輸入公式：“=

8、E5-G5”，按回車鍵確認(rèn)。圖3-4-5 銷售利潤(rùn)表8、 用鼠標(biāo)左鍵按住H5單元格的填充柄把公式復(fù)制到H6和H7單元格。9、 光標(biāo)放在H5，單擊“工具”“單變量求解”菜單項(xiàng)。10、 在彈出的“單變量求解”對(duì)話框的“目標(biāo)單元格”文本框中輸入“$H$5”，在“目標(biāo)值”文本框中輸入“10000”，在“可變單元格”文本框中輸入“$D$5”，如圖3-4-6所示。11、 單擊“確定”按鈕后彈出對(duì)話框，顯示對(duì)單元格H5進(jìn)行單變量求解的狀態(tài)信息，如圖3-4-7所示。圖3-4-6 單變量求解圖3-4-7 單變量求解狀態(tài)圖3-4-8 單變量求解結(jié)果12、 單擊“確定”按鈕后得到求解結(jié)果，如圖3-4-8所示。任務(wù)4

9、 篩選銷售利潤(rùn)表分析或說明：為了更好地了解利潤(rùn)情況，還需要對(duì)銷售利潤(rùn)表進(jìn)行比較與分析。本任務(wù)用自定義篩選的方法篩選提成額為100150元的記錄。用高級(jí)篩選“銷售數(shù)量”大于15、“銷售額”大于500，“提成額”大于100的銷售記錄。步驟： 1、 單擊工作表標(biāo)簽切換到“銷售利潤(rùn)提成表”工作表。2、 光標(biāo)放在I4中，輸入公式：“=D4-F4”，按回鍵計(jì)算“存入總計(jì)”。3、 用鼠標(biāo)左鍵按住I5單元格的填充柄把公式復(fù)制到I6和I7單元格。4、 光標(biāo)放在B14中，單擊工具欄上的“自動(dòng)求和”按鈕，系統(tǒng)自動(dòng)對(duì)數(shù)據(jù)區(qū)域“B4：B13”求和，按回車鍵確認(rèn)。5、 用鼠標(biāo)左鍵按住B13右下角的填充柄向右拖拉，計(jì)算其余

10、單元格的總計(jì)值。6、 選擇數(shù)據(jù)區(qū)域“A3：K3”，單擊“數(shù)據(jù)”“篩選”“自動(dòng)篩選”菜單項(xiàng)，此時(shí)在每個(gè)列標(biāo)題的右下側(cè)都會(huì)出現(xiàn)一個(gè)下拉箭頭。7、 單擊“提成額”右側(cè)的箭頭，在彈出的下拉列表中選擇“自定義”選項(xiàng)，彈出“自定義自動(dòng)篩選方式”對(duì)話框，如圖3-4-9所示。圖3-4-9 自定義自動(dòng)篩選方式8、 在對(duì)話框中按圖3-4-9所示進(jìn)行設(shè)置，單擊“確定”按鈕，即可篩選出滿足條件的記錄，如圖圖3-4-10所示。圖3-4-10 篩選結(jié)果9、 單擊“數(shù)據(jù)”“篩選”“自動(dòng)篩選”菜單項(xiàng)，取消自動(dòng)篩選。10、 在第3行行號(hào)上單擊鼠標(biāo)右鍵，選擇“插入”菜單項(xiàng)，在標(biāo)題行上面插入一行空行。11、 選擇數(shù)據(jù)區(qū)域“A4：K

11、4”，單擊“格式”“單元格”菜單項(xiàng)，在彈出的對(duì)話框中選擇“對(duì)齊”選項(xiàng)卡。12、 在“對(duì)齊”選項(xiàng)卡中的“文本控制”中去掉“自動(dòng)換行”復(fù)選框前的勾號(hào)，單擊“確定”按鈕返回工作表中，將表格的各列列寬調(diào)整到合適的大小。13、 在數(shù)據(jù)區(qū)域“C18：E19”中輸入要查詢的條件，如圖3-4-11所示。14、 把光標(biāo)放在要進(jìn)行篩選的數(shù)據(jù)區(qū)域中的任一單元格中，單擊“數(shù)據(jù)”“篩選”“高級(jí)篩選”菜單項(xiàng)。圖3-4-11設(shè)置篩選條件15、 在彈出的“高級(jí)篩選”對(duì)話框中按圖3-4-12進(jìn)行設(shè)置。16、 單擊“確定”按鈕后即在數(shù)據(jù)區(qū)域“A21：K26”顯示篩選結(jié)果。圖3-4-12高級(jí)篩選項(xiàng)目總結(jié)Excel具有強(qiáng)大的數(shù)據(jù)處理

12、與分析功能。本項(xiàng)目完成銷售利潤(rùn)提成表的制作，通過使用合并計(jì)算功能計(jì)算提成工資。同時(shí)，為了更好地了解銷售利潤(rùn)情況，利用單變量求解實(shí)現(xiàn)利潤(rùn)最大化，最后利用篩選功能對(duì)銷售利潤(rùn)表進(jìn)行比較與分析。拓展延伸1、vlookup函數(shù)的使用函數(shù)格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)其中：Lookup_value ：需要在表格第一列中查找的數(shù)值Table_array：兩列或多列數(shù)據(jù)，可以使用對(duì)區(qū)域的引用或區(qū)域名稱。 Col_index_num：為 table_array 中待返回的匹配值的列序號(hào)。Col_index_

13、num 為 1 時(shí)，返回 table_array 第一列中的數(shù)值；col_index_num 為 2，返回 table_array 第二列中的數(shù)值，以此類推。Range_lookup ：為邏輯值，指定希望 VLOOKUP 查找精確的匹配值還是近似匹配值：如果為 TRUE 或省略，則返回精確匹配值或近似匹配值。此時(shí)第一列中的值必須以升序排序；否則 VLOOKUP 可能無法返回正確的值（可以事先對(duì)工作表按升序進(jìn)行排序）。如果為 FALSE，VLOOKUP 將只尋找精確匹配值。在此情況下，第一列的值不需要排序。例如，在工作表“銷售記錄”中已有各人的銷售金額，如圖3-4-13所示?，F(xiàn)想在另一工作表“Sheet1”中輸入相同的銷售金額，并且要求該表的銷售金額會(huì)根據(jù)“銷售記錄”中的數(shù)據(jù)自動(dòng)更新，而且兩個(gè)工作表中人員的次序并不完全不一致（最后兩人位置不同），如圖3-4-14所示。圖3-4-13銷售記錄表圖3-4-14Sheet1表則只要在工作表“Sheet1”的D3單元格中輸入公式：“=VLOOKUP(A3,銷售記錄!$A$3:$D$8,4,FA