excel-項目六 超市商品銷售管理.doc

項目六 超市商品銷售管理項目六 超市商品銷售管理項目描述現(xiàn)代超市都會利用計算機來幫助完成商品的銷售、清點、統(tǒng)計分析以及員工的工資發(fā)放。本項目通過制作超市商品清單及收銀單、制作超市銷售日報表、制作超市月銷售額統(tǒng)計表、制作超市員工業(yè)績工資表4個任務，完成簡易的超市商品銷售管理系統(tǒng)的構建，實現(xiàn)超市商品銷售的計算機管理。1 制作超市商品清單及收銀單制作超市商品清單、收銀單的示例如圖6-1、6-2所示。完成該任務除了需要在項目五中已學會的Excel工作表創(chuàng)建、數(shù)據編輯等知識和技能，還要進一步學會Excel工作表格式排版、公式與函數(shù)使用的知識和技能。圖6-1 超市商品清單圖6-2 超市收銀單2 制作超市銷售日報表制作超市銷售日報表的示例如圖6-3、6-4、6-5所示。完成該任務需要學會排序、篩選以及分類匯總的知識與技能。 圖6-3 商品日銷售明細表 圖6-4 商品日銷售明細（分類匯總）圖6-5 商品日銷售明細（圖表分析）3 制作超市月銷售額統(tǒng)計表制作超市月銷售額統(tǒng)計表的示例如圖6-6、6-7、6-8所示。完成該任務需要學會合并計算、跨表引用的知識與技能。圖6-6 商品月銷售統(tǒng)計表（圖表分析）圖6-7商品月銷售統(tǒng)計表（合并計算）圖6-8商品月銷售業(yè)績統(tǒng)計表4 制作超市員工工資表制作超市員工工資表的示例如圖6-9所示。完成該任務需要學會絕對引用、公式與函數(shù)綜合應用的知識與技能。圖6-9 超市員工工資表6.1 制作超市商品清單及收銀單6.1.1 任務分析商品清單就是一個Excel表，表中通常包含商品的編碼、名稱、單價等信息，創(chuàng)建一個新Excel表后，錄入商品的編碼、名稱、單價等信息，即完成商品清單的制作。在超市購物時，可發(fā)現(xiàn)收銀員要做的工作是輸入商品編碼、輸入購買數(shù)量、輸入實付款，商品的名稱、單價、應收款、找零等信息是自動生成的。因此，在收銀單制作時，除了包含商品的編碼、名稱、單價等信息外，還要包含交易時間、交易數(shù)量、應收款、實付款、找零等信息，并且根據收銀員輸入的數(shù)據能自動生成所需的數(shù)據。制作思路：首先，新建一個Excel工作簿，在工作表1中錄入商品信息，進行相應的格式排版，制作出商品清單；然后，在工作表2中，制作出收銀單的雛形；最后，根據輸入的商品編碼自動生成商品名稱及單價，根據輸入的購買數(shù)量自動計算出金額及應收款，根據輸入的實付款自動計算出找零。學習的知識點： 數(shù)據錄入 格式排版 公式函數(shù)6.1.2 制作步驟1. 制作商品清單（1）新建一個Excel工作簿 選擇“開始”“所有程序” “Microsoft Office” “Microsoft Office Excel 2003”菜單命令，新建一個Excel工作簿。 將Excel工作簿命名為“職院超市收銀系統(tǒng)”，將sheet1重命名為“商品清單”、sheet2重命名為“收銀單”，刪除sheet3，如圖6-10所示。圖6-10 新建工作簿（2）錄入商品清單數(shù)據 單擊“商品清單”，在第1行錄入標題“職院超市貨架商品清單”，在第2行的AD字段分別錄入“商品編碼”、“商品名稱”、“商品單價”、“單位”。 根據職院超市的商品信息，從第3行起依次完成各商品信息的錄入，如圖6-11所示。 單擊常用工具欄上的保存按鈕，保存錄入的文字信息。圖 6-11 職院超市貨架商品清單（3）設置標題格式 選中標題行“職院超市貨架商品清單”要合并的單元格區(qū)域“A1:D1”，單擊工具欄上的“合并及居中”按鈕，將標題行合并及居中顯示，如圖6-12所示。圖 6-12 設置“商品清單”標題格式 選中標題文字“職院超市貨架商品清單”，將標題文字設置為“黑體”、“14號”、“加粗”。（4）設置字段名稱格式 選中字段名稱行區(qū)域“A2:D2”，單擊格式工具欄上的“加粗”按鈕，將字段名稱設置為加粗。 接著選擇“格式”“單元格”菜單命令，打開“單元格格式”對話框；單擊“單元格格式”對話框中“圖案”選項卡，單擊“灰色-40%”，如圖6-13所示。 單擊“確定”按鈕，將字段名稱填充為灰色底紋。圖6-13 “單元格格式”對話框的“圖案”選項卡（5）設置數(shù)據區(qū)域格式 選中數(shù)據區(qū)域“A2:D14”，選擇“格式”“單元格”菜單命令，打開“單元格格式”對話框。 單擊“單元格格式”對話框中“邊框”選項卡，將外邊框設置為藍色雙線，內邊框設置為紅色單線，如圖6-14（a）所示。 單擊“確定”按鈕，完成數(shù)據區(qū)域的邊框設置，如圖6-14（b）所示。 （a）“單元格格式”對話框的“邊框”選項卡 （b） 設置效果圖 6-14 設置數(shù)據清單區(qū)域格式（6）商品清單設置 鼠標左鍵單擊左上角行標號和列標號的交叉處，選中整個工作表；然后，選擇“格式”“單元格”菜單命令，打開“單元格格式”對話框；單擊“單元格格式”對話框中的“對齊”選項卡，如圖6-15所示；將“水平對齊”、“垂直對齊”都設置為“居中”后，單擊“確定”按鈕。圖 6-15 “單元格格式對話框”的“對齊”選項卡 將鼠標移動到行標號或列標號之間的交叉處，在鼠標變成雙向箭頭時按住左鍵上下拖動，調整行高；將鼠標移動到列標號之間的交叉處，在鼠標變成雙向箭頭時按住左鍵左右拖動，調整列寬。 選中“C3:C14”區(qū)域，選擇“格式”“單元格”菜單命令，打開“單元格格式”對話框；單擊“單元格格式”對話框中的“數(shù)字”選項卡，在“分類”選項中單擊“貨幣”，在“小數(shù)位數(shù)”文本框中輸入“2”，在“貨幣符號”組合框的下拉選項中單擊人民幣符號“￥”，如圖6-16所示；最后，單擊“確定”按鈕，完成設置。圖 6 -16 “單元格格式”對話框的“數(shù)字”選項卡2. 制作收銀單雛形（1）錄入收銀單中的文字信息 單擊“收銀單”，在第1行錄入標題“職院超市收銀單”，在第2行錄入“交易時間”，在第3行的AE字段分別錄入“商品編碼”、“商品名稱”、“單價”、“數(shù)量”、“金額”。 在A16錄入“應收款：”，在D16錄入“實付款：”，在A17錄入“大寫：”，在A18錄入“找零：”，在A19錄入“貨款請當面點清職院超市歡迎您再次光臨！”，如圖6-17所示。 單擊常用工具欄上的保存按鈕，保存錄入的文字信息。圖 6-17 制作收銀單（2）設置格式 選中標題行“職院超市收銀單”要合并的單元格區(qū)域“A1:E1”，單擊工具欄上的“合并及居中”按鈕，將標題行合并及居中顯示，如圖6-18所示；選中“職院超市收銀單”，將標題行文字設置為“黑體”、“14號”、“加粗”；選中“A19:E19”，單擊工具欄上的“合并及居中”按鈕，將“貨款請當面點清職院超市歡迎您再次光臨！”設置為兩行顯示。圖 6-18 設置“收銀單”標題格式 選中“A2:E3”區(qū)域，打開“單元格格式”對話框中的“邊框”選項卡；在“預置”中單擊“內部”選項，在“樣式”中單擊“虛線”選項，在“邊框”中分別單擊“上橫線”、“中橫線”、“下橫線”選項，如圖6-19所示；單擊“確定”按鈕。圖 6-19 設置橫虛線 選中“B2:E2”區(qū)域，單擊工具欄上的“合并及居中”按鈕，將交易發(fā)生的時間值居中顯示。 選中字段名稱行區(qū)域“A3:E3”，單擊格式工具欄上的“加粗”按鈕；接著單擊工具欄上的“填充顏色”按鈕，將底紋填充為“灰色-40%”。 選中“A3:E15”區(qū)域，打開“單元格格式”對話框中的“邊框”選項卡；在“邊框”中單擊“中豎線”選項，單擊“確定”按鈕。 選中“A15:E15”區(qū)域，打開“單元格格式”對話框中的“邊框”選項卡；在“邊框”中單擊“下橫線”選項，單擊“確定”按鈕。 選中“A16:C17”區(qū)域，打開“單元格格式”對話框中的“邊框”選項卡；在“邊框”中單擊“中橫線”選項，單擊“確定”按鈕。 選中“A17:E18”區(qū)域，打開“單元格格式”對話框中的“邊框”選項卡；在“邊框”中分別單擊“中橫線”、“下橫線”選項，單擊“確定”按鈕。 選中“B16:C16”，單擊工具欄上的“合并及居中”按鈕；選中“B17:C17”，單擊工具欄上的“合并及居中”按鈕；選中“D16:D17”，單擊工具欄上的“合并及居中”按鈕；選中“E16:E17”，單擊工具欄上的“合并及居中”按鈕；選中“B18:E18”，單擊工具欄上的“合并及居中”按鈕。 選中B16，打開“單元格格式”對話框中的“數(shù)字”選項卡，在“分類”選項中單擊“貨幣”，在“小數(shù)位數(shù)”文本框中輸入“2”，在“貨幣符號”組合框的下拉選項中單擊人民幣符號“￥”，單擊“確定”按鈕；選中E16，用同樣的方法設置其貨幣格式。 選中B17，在單元格應用公式“= B16”，并在“分類”選項中單擊“特殊”，在“類型”中單擊“中文大寫數(shù)字”，如圖6-20所示；最后，單擊“確定”按鈕。圖 6-20 設置“中文大寫數(shù)字”3. 在收銀單中使用公式與函數(shù)（1）自動生成商品名稱、單價在A4:A15區(qū)域內任一單元格輸入商品編碼，相對應商品清單表中的商品名稱、單價自動顯示在收銀單中的操作步驟如下。 選中B4單元格，單擊“插入函數(shù)”按鈕，打開“插入函數(shù)”對話框，如圖6-21所示；在“選擇函數(shù)”中找到“VLOOKUP”函數(shù)。 圖 6-21 打開“插入函數(shù)”對話框 單擊“確定”按鈕，打開“選定參數(shù)”對話框；繼續(xù)單擊“確定”按鈕，打開“函數(shù)參數(shù)”對話框，如圖6-22所示；在第一個參數(shù)“Lookup_value”的文本框中輸入“A4”。圖 6-22 “函數(shù)參數(shù)”對話框說明：第一個參數(shù)為在數(shù)據表首列需要搜索的值，即在“商品編碼”列要輸入的商品編碼，當前的參數(shù)應確定為A4。 在第二個參數(shù)“Table_array”的文本框中輸入“商品清單!A3:D14”。說明：第二個參數(shù)為需要在其中搜索數(shù)據的信息表，即為“商品清單”表中的商品信息區(qū)域“A3:D14”。 在第三個參數(shù)“Col_index_num”的文本框中輸入“2”。說明：第三個參數(shù)為滿足條件的單元格在第二個參數(shù)區(qū)域的列序號。此處，需要返回的是商品名稱，為信息表的第二列。所以，參數(shù)值應為“2”。同理，若是返回商品單價，則參數(shù)值應應確定為“3”。 單擊“確定”按鈕；將鼠標移至當前的B4單元格的右下角，鼠標呈現(xiàn)十字架實心的形狀，按住鼠標左鍵并拖動至B15單元格，松開鼠標，如圖6-23所示。圖 6-23 函數(shù)使用報錯說明：此時，由于沒有在A列輸入商品編碼，所以會出現(xiàn)圖6-23中所示的提示符號，表示此時函數(shù)不可用?？梢栽凇癆4:A15”的任意單元格內輸入一商品編碼，商品名稱即可顯示。同理，在單價列應用此函數(shù)，即可返回商品編碼對應的單價，如圖6-24所示。6-24 函數(shù)使用正確情況（4）自動生成購買時間選中B2單元格，在單元格內輸入“=NOW()”，按下回車鍵“Enter”，即可返回當前的系統(tǒng)時間。（3）自動計算應收款根據輸入商品的編碼、購買的數(shù)量，自動計算應收款的操作步驟如下。 選中E4單元格，在單元格內輸入“=C4*D4”，完成后按下回車鍵“Enter”。 將鼠標移至當前的E4單元格的右下角，鼠標呈現(xiàn)十字架實心的形狀，按住鼠標左鍵并拖動至E15單元格，松開鼠標。 輸入顧客購買的一種商品編碼和數(shù)量，例如“A004”、“3”，計算出相應的金額，如圖6-25所示。圖 6-25 自動計算購買一種商品的金額 選中B16單元格，插入使用“SUM”函數(shù)，“函數(shù)參數(shù)”設置為“E4:E15”，即可自動計算出應付款。例如，輸入某顧客購買的商品編碼、購買數(shù)量后計算出的商品應付款，如圖6-26所示。應付款的大寫金額顯示在B17單元格中。圖 6-26 自動計算應付款（3）自動計算找零根據輸入的實付款，自動計算找零的操作步驟為：選中B18單元格，在單元格內輸入“=E16-B16”，按下回車鍵“Enter”，即可。至此，收銀單制作完畢。為了提醒收銀員，可使用條件格式設置找零。找零為負數(shù)時，以紅色底紋顯示，提示收銀員實付款金額不足；找零為正數(shù)時，以綠色底紋顯示，提示收銀員付款成功，按數(shù)找零。具體操作步驟如下。 選中B18單元格后，單擊“格式”菜單的“條件格式”菜單項，打開“條件格式”對話框。 在“條件格式”對話框中，設置條件“單元格數(shù)值”、“小于”、“0”；單擊“格式”按鈕，在“單元格格式”對話框中單擊“圖案”選項卡，設置紅色底紋，如圖6-27（a）所示。 單擊“添加”按鈕，設置條件2“單元格數(shù)值”、“大于等于”、“0”；單擊“格式”按鈕，在“單元格格式”對話框中單擊“圖案”選項卡，設置綠色底紋，如圖6-27（b）所示。 （a） 設置條件1 （b）設置條件2圖 6-27 設置“條件格式” 單擊“確定”按鈕，完成設置。此時，輸入某顧客實付款“40”元后，收銀單如圖6-28所示。圖 6-28 收銀單效果圖6.1.3 知識學習1. Excel的功能Excel具有制作表格、數(shù)據計算處理、數(shù)據庫、圖表4種功能，其中制作表格、數(shù)據計算處理為常用的基本功能。在利用Excel進行數(shù)據計算處理時，有幾百種函數(shù)可供使用。當將Excel中某個數(shù)據區(qū)域作為數(shù)據清單（字段+記錄）時，可以實現(xiàn)記錄的增刪改、排序、篩選、分類匯總等數(shù)據庫的功能。使用Excel中提供的各類圖表，可以直觀地呈現(xiàn)Excel中的數(shù)據。2IF函數(shù)的使用（1）語法規(guī)則IF函數(shù)執(zhí)行真假值判斷，根據邏輯測試的真假值返回不同的結果，可以使用函數(shù)IF對數(shù)值和公式進行條件檢測。其語法規(guī)則為：IF(Logical_test,Value_if_true,Value_if_false)（2）參數(shù)說明 Logical_test。 表示計算結果為“true”或“false”的任意值或表達式。例如“A10=100”就是一個邏輯表達式，如果單元格A10中的值等于100，表達式即為“true”，否則為“false”，本參數(shù)可使用任何比較運算符。 Value_if_true?！發(fā)ogical_test”為“true”時返回的值，可以是其他公式。 Value_if_false。logical_test為“false”時返回的值，當然也可以是其他公式。（3）使用舉例根據學生的成績打分，判斷其評定結果，小于60分評定為“不及格”，大于等于60評定為“及格”。具體操作步驟如下。 選中“C2”，打開“函數(shù)參數(shù)”對話框；設置參數(shù)1為條件表達式“B2=60”，設置條件2的參數(shù)為“及格”，設置條件3的參數(shù)為“不及格”，如圖6-29所示。 單擊“確定”按鈕；使用填充柄，將“C2”復制至“C9”單元格，即可完成8位同學的成績評定。圖 6-29 IF函數(shù)參數(shù)對話框說明：圖6.1.21所示“函數(shù)參數(shù)”設置的3個條件中，條件1成立時，返回參數(shù)2“及格”（返回值為字符時需加上雙引號）；若條件1不成立，則返回參數(shù)3“不及格”。需要指出，IF函數(shù)不僅可以對一個條件的判斷作出兩種選擇，也可以利用嵌套應用于更復雜的情況。IF函數(shù)可以嵌套七層，用 Value_if_false 及 Value_if_true 參數(shù)可以構造復雜的檢測條件。例如，給應用舉例再增加一個條件，即成績達到85及以上，評定為優(yōu)秀。這樣就等于有兩個條件，會出現(xiàn)三個結果?！昂瘮?shù)參數(shù)”的設置，如圖6-30所示。圖 6-30 IF函數(shù)嵌套3VLOOKUP函數(shù)的使用（1）語法規(guī)則VLOOKUP是一個縱向查找函數(shù)。其語法規(guī)則為： VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup) （2）參數(shù)說明 Lookup_value。 Lookup_value為需要在數(shù)據表第一列中進行查找的數(shù)值。Lookup_value可以為數(shù)值、引用或文本字符串。 Table_array。 Table_array為需要在其中查找數(shù)據的數(shù)據表。使用對區(qū)域或區(qū)域名稱的引用。 Col_index_num。 Col_index_num為Table_array中待返回的匹配值的列序號。Col_index_num為1時，返回Table_array第一列的數(shù)值，Col_index_num為2時，返回table_array第二列的數(shù)值，以此類推。如果Col_index_num小于1，函數(shù) VLOOKUP返回錯誤值#VALUE!；如果Col_index_num大于Table_array的列數(shù)，函數(shù)VLOOKUP返回錯誤值#REF!。 Range_lookup。 Range_lookup為一邏輯值，指明函數(shù)VLOOKUP查找時是精確匹配，還是近似匹配。如果為true或省略，則返回近似匹配值。也就是說，如果找不到精確匹配值，則返回小于Lookup_value 的最大數(shù)值。如果Lookup_value 為false，函數(shù)VLOOKUP將查找精確匹配值，如果找不到，則返回錯誤值 #N/A。 （3）使用舉例如圖6-31所示，在“A2:F12”區(qū)域中提取100003、100004、100005、100007、1000105人的全年總計銷量，并對應輸入到“I4:I8”中。圖 6-31 VLOOKUP函數(shù)示例數(shù)據查找時，可以采用一個一個的手動查找操作。但在數(shù)據量大的時候將十分繁瑣。因此，使用VLOOKUP函數(shù)查找將十分便捷。具體操作步驟如下。 選中“I4”單元格，在單元格內輸入“=Vlookup(”，Excel提示4個參數(shù)。 第一個參數(shù)設置為“H4,”，對應100003；第二個參數(shù)設置為“$A$2:$F$12,”，為要查找的區(qū)域(絕對引用)；第三個參數(shù)設置“6”，為“全年總計”，是區(qū)域的第六列。第4個參數(shù)不設置，因為要精確的查找工號。 補全最后的右括號“)”，得到公式“=VLOOKUP(H4,$A$2:$F$12,6)”；使用填充柄，將“I4”單元格復制“I8”即可完成查找操作。查找結果如圖6-32所示。圖 6-32 VLOOKUP函數(shù)查找結果6.2 制作超市銷售日報表6.2.1 任務分析超市銷售日報表是對超市一天銷售的商品做一個統(tǒng)計分析。報表通常包含商品編碼、商品名稱、銷售時間、銷售數(shù)量、單價、（銷售）金額等信息，數(shù)據來自收銀單。為便于掌握顧客的購買習慣、商品的受歡迎程度，需要對銷售數(shù)據按商品編碼、銷售時間進行排序，并分類匯總、制成圖表。超市的管理者根據這些信息提示，可以合理地調配人員，補充適銷商品，撤換滯銷商品。制作思路：首先，新建一個Excel工作簿，在工作表中錄入當日的商品銷售數(shù)據，制作“日商品銷售明細”表；然后，對表中數(shù)據按商品編碼、銷售時間進行排序；最后，對表中數(shù)據進行分類匯總、篩選查看，并制作成圖表。學習的知識點： 數(shù)據排序 分類匯總 篩選查看 制作圖表6.2.2 制作步驟1. 制作日商品銷售明細表（1）新建一個Excel工作簿打開Excel，將新工作簿命名為“職院超市銷售日報表”，重命名sheet1為“日商品銷售明細”，刪除sheet2、sheet3。（2）錄入報表數(shù)據根據任務一中的收銀單，完成一天超市商品銷售信息的錄入，如圖6-33所示。錄入完畢后，單擊常用工具欄上的保存按鈕，保存錄入的數(shù)據。圖 6-33 職院超市銷售日報表（3）編排格式將標題行合并居中、文字加粗，給數(shù)據清單區(qū)域添加邊框；根據數(shù)據類型，將數(shù)據設置成相應的時間型或貨幣型；調整對齊方式、字體、字號，調整單元格的寬度、高度。編排完成后，單擊常用工具欄上的保存按鈕，保存編排的格式。編排后的效果，如圖6-34所示。圖 6-34 編排后的職院超市銷售日報表2. 排序“日商品銷售明細”表（1）選中排序操作的區(qū)域鼠標指向A2單元格，按下鼠標左鍵拖動到F40單元格，松開鼠標左鍵，選中排序操作的區(qū)域“A2:F40”。說明：排序是對數(shù)據表中數(shù)據清單的操作，數(shù)據清單是由字段名和每一條記錄組成的。在日商品銷售明細表中，排序的選定區(qū)域應該是“A2:F40”。（2）打開排序對話框選擇“數(shù)據”“排序”菜單命令，打開“排序”對話框，如圖6-35所示。圖 6-35 “排序”對話框（3）設置排序關鍵字 單擊“主要關鍵字”組合框，在下拉列表中選擇“商品編碼”選項，將主要關鍵字設置為“商品編碼”；再單擊“升序”前的單選按鈕，將排序方式設置為“升序”。 單擊“次要關鍵字”組合框，在下拉列表中選擇“銷售時間”選項，在主要關鍵字相同的情況下將“次要關鍵字”設置為“銷售時間”；再單擊“升序”前的單選按鈕，將排序方式設置為“升序”，如圖6-36所示。圖 6-36 確定排序關鍵字說明：為了方便查看數(shù)據，掌握銷售情況，根據任務分析，對表中的數(shù)據應該按照商品編碼、銷售時間進行排序操作。這樣，同一種商品的銷售信息就在一起了。（4）完成排序設置好排序關鍵字后，單擊“排序”對話框中的“確定”按鈕，完成排序操作。排序后的日商品銷售明細表，如圖6-37所示。圖 6-37排序后的日商品銷售明細表3. 分類匯總“日商品銷售明細”表（1）選中分類匯總操作的區(qū)域鼠標指向A2單元格，按下鼠標左鍵拖動到F40單元格，松開鼠標左鍵，選中分類匯總操作的區(qū)域“A2:F40”。說明：分類匯總也是對整個數(shù)據清單的操作。（2）打開分類匯總對話框選擇“數(shù)據”“分類匯總”菜單命令，打開“分類匯總”對話框，如圖6-38所示。圖 6-38 “分類匯總”對話框（3）設置分類匯總選項 單擊“分類字段”組合框的下拉按鈕，在下拉選項中選擇“商品名稱”，將分類字段設為商品名稱，方便管理人員查看。 單擊“匯總方式”組合框的下拉按鈕，在下拉選項中選擇“求和”，將匯總方式設置為“求和”。 在“選定匯總項”列表中，選中“銷售數(shù)量”和“金額”前的復選框；接著分別選中“替換當前分類匯總”、“匯總結果顯示在數(shù)據下方”前的復選框。（4）完成分類匯總單擊“分類匯總”對話框中的“確定”按鈕，完成分類匯總操作。分類匯總后的“日商品銷售明細”表，如圖6-39所示。圖 6-39 分類匯總效果圖（5）分級查看匯總結果 單擊數(shù)據表左上方的“2”，查看每種商品的銷售總量，如圖6-40所示。圖 6-40 每種商品的銷售匯總 單擊數(shù)據表左上方的“1”，查看商品銷售總量，如圖6-41所示。圖 6-41 商品銷售總量匯總4. 篩選查看“日商品銷售明細”表（1）設置篩選方式選擇“數(shù)據”“篩選”“自動篩選”菜單命令，設置篩選方式為“自動篩選”，如圖6-42所示。圖 6-42 設置“自動篩選”（2）查看自動篩選結果單擊字段名稱后的下拉按鈕，例如“商品名稱”后的下拉按鈕；在下拉選項中選擇要查看的選項，例如“康師傅老壇酸菜面”；查看“康師傅老壇酸菜面”的銷售情況，如圖6-43所示。圖 6-43 查看“康師傅老壇酸菜面”的銷售情況（3）查看自定義篩選結果 單擊字段名稱后的下拉按鈕，例如“銷售時間”的下拉按鈕，打開下拉選項，如圖6-44所示。圖 6-44 打開“銷售時間”下拉選項 在下拉選項中，選擇“自定義”選項，打開“自定義自動篩選方式”對話框，如圖6-45所示。圖 6-45 “自定義自動篩選方式”對話框 在“銷售時間”選項中，單擊第一行左邊的下拉按鈕，在下拉選項中選擇“大于或等于”；再單擊第一行右邊的組合框，輸入時間，例如“9:30”。 選中“與”單選按鈕；單擊第二行左邊的下拉按鈕，在下拉選項中選擇“小于或等于”；再單擊第二行右邊的組合框，輸入時間，例如“9:50”。 單擊“確定”按鈕，查看到“9:309:50”時間段的銷售情況如圖6-46所示。圖 6-46 自定義篩選結果5. 制作商品銷售日報圖表將“日商品銷售明細”表分類匯總后，就已經完成了“超市銷售日報表”的制作。為了更直觀地顯示“日商品銷售”情況，還可以利用Excel的圖表功能制作商品銷售日報圖表。具體操作步驟如下。（1）打開圖表向導 單擊“超市銷售日報表”左上方的數(shù)字“2”，選中“A2:F47”區(qū)域，單擊菜單欄上的“插入”菜單，如圖6-47所示。圖 6-47 打開“圖表”菜單命令 單擊“圖表”菜單命令，打開“圖表向導”對話框，如圖2-48所示。圖 6-48“圖表向導”對話框（2）創(chuàng)建圖表 單擊“標準類型”選項卡，在“圖表類型”列表中單擊“柱形圖”，單擊“下一步”按鈕，如圖6-49所示。圖 6-49“圖表向導”之二 單擊“系列”選項卡，如圖6-50所示；選中“系列”列表中“銷售時間”，單擊“刪除”按鈕；再選中“單價”，單擊“刪除”按鈕。圖 6-50 “系列”選項卡 單擊“下一步”按鈕，再單擊“下一步”按鈕，如圖6-51所示。圖 6-51 “圖表向導”之4 單擊“完成”按鈕，完成“商品銷售日報圖表”制作，如圖6-52所示。圖 6-52 商品銷售日報圖表6.2.3 知識學習1. Excel數(shù)據表建立準則在Excel中，可以把數(shù)據表（或稱數(shù)據清單）作為數(shù)據庫來處理，實現(xiàn)排序、分類匯總、篩選等數(shù)據庫功能。為了使Excel能容易地對數(shù)據表進行處理，建立數(shù)據表時應注意以下準則。 一張工作表只建立一張數(shù)據表。 工作表的第一行建立各列標題，列標題使用的字體、格式應和下面的數(shù)據相區(qū)別。 同一列數(shù)據的類型應一致。 工作中的數(shù)據區(qū)不出現(xiàn)空白行和列。2. 數(shù)據排序對某些數(shù)據表，有時需要按字段的大小進行排序。排序的依據字段稱為“關鍵字”，例如，對“日商品銷售明細”表排序時的“商品編碼”、“銷售時間”等。Excel默認依據列字段排序，支持單個關鍵字排序（即簡單排序），也支持多個關鍵字排序（即復雜排序），但最多支持三個關鍵字排序。排序時，英文字符按字母順序排序，而漢字默認按拼音順序排序。（1）簡單排序實現(xiàn)簡單排序的方法是：單擊排序所依據列中的任一單元格，根據需要單擊“常用”工具欄中的“升序”或“降序”按鈕。簡單排序也可以用“排序”對話框完成。（2）復雜排序復雜排序是根據多字段的內容對數(shù)據清單進行排序，即排序所依據的第一關鍵字（主要關鍵字）內容相同時，再按第二關鍵字（次要關鍵字）進行排序，第二關鍵字也相同時，再按第三關鍵字進行排序。復雜排序要使用“排序”對話框完成。如果數(shù)據表需按行排序或漢字筆畫排序，要修改Excel的默認設置。修改方法是：單擊“排序”對話框中的“選項”按鈕，打開“排序選項”對話框，如圖6-53所示；在“排序選項”對話框中，修改排序方向和方法，然后單擊“確定”按鈕即可。圖 6-53 “排序選項”對話框2. 分類匯總分類匯總就是按數(shù)據清單的某列字段對記錄進行分類，將列字段值相同的連續(xù)記錄分為一組，并可以對各組數(shù)據進行求和、計數(shù)、求平均值、求最大值等匯總計算。（1）插入分類匯總在分類匯總前，應按分類所依據的列進行排序。這樣才能確保列字段值相同的記錄是連續(xù)的，匯總才能成功。插入分類匯總的方法如下。首先，單擊“數(shù)據”菜單，在下拉選項中單擊“分類匯總”菜單命令，打開“分類匯總”對話框。然后，在對話框中，選擇“分類字段”，例如“日商品銷售明細”表中的“商品名稱”；選擇“匯總方式”，例如“日商品銷售明細”表中的“求和”；設置“選定匯總項”，例如“日商品銷售明細”表中的“銷售數(shù)量”、“金額”。最后，單擊“確定”按鈕，分類匯總的結果隨之顯示出來，完成分類匯總。（2）刪除分類匯總插入分類匯總后，再次單擊“數(shù)據”菜單中“分類匯總”菜單命令，打開“分類匯總”對話框中；在“分類匯總”對話框中單擊“全部刪除”命令，即可刪除分類匯總，使數(shù)據清單恢復原狀。3. 數(shù)據篩選在實際應用中，經常需要在一個數(shù)據清單中查找滿足特定條件的記錄，而篩選就是查找的一種快捷有效的方法。篩選可以顯示滿足條件的行，而不滿足條件的會被暫時隱藏。Excel提供兩類篩選數(shù)據清單的方式，分別是自動篩選和高級篩選。（1） 自動篩選單擊數(shù)據清單中任意單元格，選擇“數(shù)據”“篩選”“自動篩選”菜單命令，進入自動篩選狀態(tài)。此時，每列的標題名右端都會有“全部”、“前10個”、“自定義”和當前列中所出現(xiàn)的值等選項。在這些選項中，單擊所需的選項，即可進行自動篩選查看。（2） 高級篩選使用高級篩選，可以應用較復雜的條件來篩選數(shù)據清單。與自動篩選不同的是，使用高級篩選需要在數(shù)據清單之外另建一個條件區(qū)域。條件區(qū)域可以建在數(shù)據清單的上方、下方、左側或右側，但與數(shù)據清單間必須至少要保留一個空行或空列。例如，篩選英語、數(shù)學和計算機成績都及格的學生，操作步驟如下。 建立條件區(qū)域。在與數(shù)據清單空一行或一列的空白區(qū)域輸入英語、數(shù)學和計算機字段名，在對應的字段名下一單元格輸入條件“=60”，如圖6-54所示。圖 6-54 創(chuàng)建條件區(qū)域 單擊數(shù)據清單中任意單元格，然后選擇“數(shù)據”“篩選”“高級篩選”菜單命令，打開“高級篩選”對話框，如圖6-55所示。圖 6-55 “高級篩選”對話框 在對話框中，根據要求單擊“方式”下的單選按鈕，選中所需的方式。在“條件區(qū)域”中，輸入條件區(qū)域的引用；或單擊右端的拾取器按鈕，隱藏對話框，再在工作表中使用鼠標拖動選中條件區(qū)域。最后，單擊“確定”按鈕，完成高級篩選。篩選結果，如圖6-56所示。圖 6-56 高級篩選效果圖說明：在“列表區(qū)域”中，Excel會自動識別出數(shù)據區(qū)域。如果單擊了“方式”下的“將篩選結果復制到其他位置”單選按鈕，則“復制到”激活變黑，用“條件區(qū)域”的類似操作確定復制到的位置，然后單擊“確定”按鈕完成篩選。高級篩選后，選擇“數(shù)據”“篩選”“全部顯示”命令，可顯示高級篩選隱藏的數(shù)據。4. 圖表Excel中，可以用圖表的方式顯示工作表中數(shù)據。圖表具有較好的視覺效果，可方便用戶查看數(shù)據之間的差異和趨勢。Excel為用戶提供了豐富的圖表類型，如柱形圖、折線圖、餅圖等。（1）創(chuàng)建圖表創(chuàng)建圖表最常用的方法是使用圖表向導。例如，在“學生成績表”中創(chuàng)建顯示“姓名”、“計算機成績”圖表的操作步驟如下。 在“學生成績表”中，拖動鼠標選中“B2:B8”；按下“Ctrl”鍵不要松開，再拖動鼠標選中“E2:E8”。選中的“B2:B8”和“E2:E8”不連續(xù)區(qū)域作為要創(chuàng)建圖表的數(shù)據源。 單擊“常用”工具欄中的“圖標向導”按鈕或選擇“插入”“圖表”菜單命令，打開圖表向導對話框，如圖6-57所示。圖 6-57 “圖表向導”對話框 在“標準類型”選項卡中，選擇圖表的類型，例如“柱形圖”“簇狀柱形圖”；然后，單擊“下一步”按鈕，打開“圖表向導”步驟2對話框，如圖6-58所示。 圖 6-58 “圖表向導”步驟2對話框在“標準類型”選項卡中，有多種圖表類型可供選擇，如“柱形圖”、“餅圖”等。每一種圖表類型還細分出若干子類型，可根據需要進行選擇它們。如果對提供的圖表類型不滿意，還可以在“自定義類型”選項卡中自行選擇所需的圖表類型。 在圖表向導第2步中，定義圖表的數(shù)據源；在“系列”選項卡中，設置圖表中顯示的分類軸標志；設置完成后，單擊“下一步”按鈕，打開“圖表向導”步驟3對話框，如圖6-59所示。 圖 6-59 “圖表向導”步驟3對話框一般情況下，Excel會自動識別出圖表的數(shù)據源。如果有誤，可以單擊“數(shù)據區(qū)域”框右端的拾取器按鈕，隱藏對話框；然后，在工作表中選擇數(shù)據源區(qū)域，并拖到隱藏的對話框。 在圖表向導第3步中，設置圖表的“標題”、“數(shù)值軸”、“網格線”、“圖例”、“數(shù)據表”等選項，例如在“標題”選項卡的“圖表標題”中輸入“學生計算機成績圖表”、“數(shù)值軸”中輸入“成績”，在“圖例”選項卡中取消“顯示圖例”的復選；設置完成后，單擊“下一步”按鈕，打開“圖表向導”步驟4對話框，如圖6-60所示。圖 6-60 “圖表向導”步驟4對話框 在圖表向導第4步中，選擇圖表插入位置，例如選中“作為其中的對象插入”；然后，單擊“完成”按鈕，完成圖表創(chuàng)建，如圖6-61所示。圖 6-61 圖表效果圖（2）更改圖表設置圖表創(chuàng)建后，還可以根據需要更改圖表的類型、數(shù)據源、圖表選項和位置設置。更改的方法是：在圖表上單擊鼠標右鍵，在快捷菜單中選擇“圖表類型”、“圖表選項”等選項進行更改；或者單擊圖表選中后，選擇“插入”“圖表”菜單命令，在打開的對話框中進行更改。例如，將“學生成績表”圖表類型更改為“折線圖“、圖例靠右顯示的操作步驟如下。 在如圖6-61所示的圖表上單擊鼠標右鍵，打開快捷菜單，如圖6-62所示；單擊“圖表類型”，在打開的對話框中選擇“折線圖”。 圖 6-62 圖表向導步驟4對話框 再在圖表上單擊鼠標右鍵，單擊“圖表選項”，在打開的對話框中選擇“圖例”選項卡，選中“顯示圖例”，并選中“靠右”；單擊“完成”按鈕，完成圖表更改。（3）格式化圖表Excel圖表是由圖表區(qū)、圖表標題、圖例、繪圖區(qū)、系列和數(shù)據標志等對象組成。可以通過鼠標右擊圖表區(qū)，在打開的快捷菜單中單擊“圖表區(qū)格式”菜單命令，打開“圖表區(qū)格式”對話框，如圖6-63所示；在對話框中，設置字體、邊框、填充色等格式化圖表。圖 6-63 “圖表區(qū)格式”對話框6.3 制作超市月銷售額統(tǒng)計表6.3.1 任務分析超市以多分店方便顧客贏得了市場。為及時核算成本，高效地調度資金，需要對各個分店的月銷售額進行統(tǒng)計、分析。統(tǒng)計時，通常每個分店制作一張統(tǒng)計表，然后將各分店的銷售數(shù)據合并，完成整個超市系統(tǒng)當月商品銷售情況的統(tǒng)計、分析，并根據各分店的銷售業(yè)績核算出獎金額。為避免月底集中統(tǒng)計造成工作量的不平衡，或每天統(tǒng)計造成工作效率低下，可分上、中、下三旬對每個月各分店的銷售情況進行統(tǒng)計。制作思路：首先，創(chuàng)建各類工作表，如“一分店”月銷售額統(tǒng)計表、“合并計算表”、“各店銷售業(yè)績統(tǒng)計表”等；然后，對各個分店的月銷售額進行統(tǒng)計、分析，制成圖表；最后，將各分店的月銷售情況進行合并計算，并核算出各分店的獎金額。學習的知識點： 格式排版 合并計算 跨表引用6.3.2 制作步驟1. 創(chuàng)建各類工作表（1）創(chuàng)建各分店月銷售額統(tǒng)計表 新建工作簿，命名為“職院超市月銷售額統(tǒng)計表”；分別將Sheet1、Sheet2、Sheet3重命名為：“一分店”、“二分店”、“三分店”。 單擊“一分店”，參照圖6-64所示，完成一分店月銷售額統(tǒng)計表的創(chuàng)建。圖 6-64 一分店月銷售額統(tǒng)計表 選中“A1:L16”區(qū)域，將“職院一分店月銷售額統(tǒng)計表”的內容復制到“二分店”、“三分店”，并分別將標題中的“一分店”修改為“二分店”、“三分店”。 單擊“保存”按鈕，完成各分店月銷售額統(tǒng)計表的創(chuàng)建。（2）創(chuàng)建合并計算表 鼠標右鍵單擊“一分店”工作表，在快捷菜單中單擊“移到或復制工作表”，打開“移到或復制工作表”對話框，如圖6-65所示。圖 6-65 “移到或復制工作表”對話框 單擊“（移至最后）”；選中 “建立副本”復選框，再單擊“確定”按鈕。 重命名“一分店2”為“合并計算表”，將標題修改為“職院超市各分店月銷售額統(tǒng)計總表”，如圖6-66所示。圖 6-66 合并計算表 單擊“保存”按鈕，完成合并計算表的創(chuàng)建。（3）創(chuàng)建各店銷售業(yè)績統(tǒng)計表 單擊“合并計算表”，選擇“插入”“工作表”菜單命令，在“合并計算表”插入一張新工作表；將新工作表重命名為“各店銷售業(yè)績統(tǒng)計表”，鼠標左鍵按住“各店銷售業(yè)績統(tǒng)計表”拖動到最后，松開鼠標左鍵。 參照圖6-67所示，完成“各店銷售業(yè)績統(tǒng)計表”創(chuàng)建后，單擊“保存”按鈕，保存創(chuàng)建的工作表。圖 6-67 各分店銷售業(yè)績統(tǒng)計表2. 統(tǒng)計各分店月銷售額（1）統(tǒng)計一分店月銷售額 根據一分店提交的商品銷售日報表，分上、中、下三旬完成當月各商品銷售數(shù)量的錄入，如圖6-68所示。圖 6-68 錄入好銷售數(shù)量的工作表 利用Excel中的公式、函數(shù)，計算“娃哈哈礦泉水”上旬銷售金額，如圖6-69所示；利用填充柄復制公式到其他單元格，計算出上旬所有商品的銷售金額。中、下旬銷售金額用同樣的方法計算出。圖 6-69 計算“娃哈哈礦泉水”上旬銷售金額 計算“娃哈哈礦泉水”的月銷售總數(shù)，如圖6-70所示；利用填充柄復制公式到其他單元格，計算出所有商品的月銷售總數(shù)。圖 6-70 計算“娃哈哈礦泉水”月銷售總數(shù) 計算“娃哈哈礦泉水”的月銷售總額，如圖6-71所示；利用填充柄復制公式到其他單元格，計算出所有商品的月銷售總額。圖 6-71 計算“娃哈哈礦泉水”月銷售總額 計算上旬銷售金額合計值，如圖6-72所示；用同樣的方法，計算出中、下旬銷售金額合計值及銷售總額合計值，如圖6-73所示。圖 6-72 計算上旬銷售金額合計值圖 6-73 計算銷售總額合計值 選中“商品名稱”、“銷售總數(shù)”兩列，打開“圖表向導”對話框，如圖6-74所示；在向導指引下，制作“商品月銷售總數(shù)”圖表，如圖6-75所示。圖 6-74 創(chuàng)建銷售總數(shù)圖表圖 6-75 一分店商品月銷售總數(shù)圖表 選中“商品名稱”、“銷售總額”兩列，打開“圖表向導”對話框，如圖6-76所示；在向導指引下，制作“商品月銷售總額”圖表，如圖6-77所示。圖 6-76 創(chuàng)建銷售總額圖表圖 6-77 一分店商品月銷售總額圖表（2）統(tǒng)計二、三分店月銷售額按照一分店月銷售額統(tǒng)計及分析的方法，完成二、三分店月銷售額的統(tǒng)計及分析。3. 統(tǒng)計超市月銷售額（1）合并計算 單擊“合并計算表”，選中“E4:L16”區(qū)域，如圖6-78所示。圖 6-78 打開“合并計算表” 選擇“數(shù)據”“合并計算”菜單命令，打開“合并計算”對話框，如圖6-79所示。圖 6-79 “合并計算”對話框 單擊“一分店”工作表，選中一分店月銷售額統(tǒng)計表中的“E4:L16”區(qū)域，如圖6-80所示。圖 6-80 選擇“引用位置” 單擊“合并計算”對話框中的“添加”按鈕，將一分店的“E4:L16”區(qū)域添加到引用位置；用同樣的方法，添加二、三分店月銷售額統(tǒng)計表中的“E4:L16”區(qū)域；選中 “標簽位置”的“創(chuàng)建連至源數(shù)據的鏈接”復選框，如圖6-81所示。圖 6-81 添加所有引用位置 單擊“確定”按鈕，完成三個分店月銷售的合并計算，如圖6-82所示。圖 6-82 合并計算后結果 單擊左側的“+”號，查看每個分店的銷售數(shù)據，如圖6-83所示。圖 6-83 查看各分店數(shù)據（2）創(chuàng)建超市月銷售圖表 在合并計算表中，選中“商品名稱”、“銷售總數(shù)”兩列，打開“圖表向導”對話框，在向導指引下，制作“超市商品月銷售總數(shù)”圖表，如圖6-84所示。 選中“商品名稱”、“銷售總額”兩列，打開“圖表向導”對話框，在向導指引下，制作“超市商品月銷售總額”圖表，如圖6-84所示。圖 6-84 超市系統(tǒng)月銷售圖表4. 核算獎金基數(shù)（1）引用月銷售額 單擊“各店銷售業(yè)績統(tǒng)計表”，選中D3單元格，在D3單元格內輸入“=一分店!L16”，如圖6-85（a）所示；按下“Enter”鍵，將“一分店”表中L16單元格的月銷售總額引用到D3單元格，如圖6-85（b）所示。 （a） 數(shù)據跨表引用 （b） 引用結果圖 6-85 引用一分店月銷售額 同理，選中D4單元格，在D4單元格內輸入“=二分店!L16”，將“二分店”表中L16單元格的月銷售總額引用到D4單元格；選中D5單元格，在D5單元格內輸入“=三分店!L16”，將“三分店”表中L16單元格的月銷售總額引用到D5單元格。完成引用后如圖6-86所示。圖 6-86 引用各分店月銷售額（2）計算獎金基數(shù) 選中E3單元格，在E3單元格內輸入“D3*2%”， 按下“Enter”鍵，計算出一分店獎金基數(shù)。 利用填充柄復制公式到E4、E5單元格，計算出二、三分店獎金基數(shù)，如圖6-87所示。圖 6-87 獎金核算結果6.3.3 知識學習1. 合并計算一個公司內可能有很多的銷售地區(qū)或者分公司，各個分公司具有各自的銷售報表和會計報表，為了對整個公司的所有情況進行全面的了解，就要將這些分散的數(shù)據進行合并，從而得到一份完整的銷售統(tǒng)計報表或者會計報表。利用Excel提供的合并計算功能，可快捷地完成這些數(shù)據匯總工作。（1）基本概念合并計算是指，將多個源區(qū)中的數(shù)據匯總為一個數(shù)據的計算方法。Excel 提供了兩種合并計算的方法。一是通過位置合并計算，適用于源區(qū)中的數(shù)據源具有相同的對應位置數(shù)據的匯總。二是通過分類合并計算，適用于源區(qū)中的數(shù)據沒有相同的布局時數(shù)據的匯總。 要想合并計算，必須先為匯總信息定義一個目的區(qū)，用來顯示摘錄的信息。此目標區(qū)域可位于與源相同的工作表上，也可以在另一個工作表上或工作簿內。然后，選擇要合并計算的源。此源可以來自單個工作表、多個工作表或多重工作簿中。 在Excel中，可以最多指定255個源區(qū)域來進行合并計算。在合并計算時，不需要打開包含源區(qū)域的工作簿。（2） 通過位置合并計算 通過位置來合并計算非常適用于處理相同表格的數(shù)據匯總。例如，將超市的多家分店數(shù)據合并形成一個完整的超市報表，將不同地區(qū)的稅務報表合并形成一個市的總稅務報表等。用這種方式合并計算時，要求：同一類數(shù)據在所有源區(qū)域的數(shù)據表中處于相同的位置。（3）通過分類合并計算設分類合并計算前的原始數(shù)據，位于Sheet2工作表中，如圖6-88所示。匯總各個產品總銷售量的操作如下。圖 6-88某公司銷售表 在Sheet2工作表中，單擊“A16”單元格，將光標定位于A16，存放匯總的第一個數(shù)據；然后，選擇“數(shù)據”“合并計算”菜單命令，打開“合并計算”對話框。 單擊“引用位置”，選中原始數(shù)據表中的“B1:C1