Excel講解及練習制作銷售利潤提成表

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

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

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

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

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

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

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

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

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

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

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

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

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