*歐陽光明*歐陽光明*創編2023.03.07*歐陽光明*歐陽光明*創編2023.03.07EXCEL實訓練習三歐陽光明〔2023.03.07〕商場銷售數據的分析處理問題的提出小李在深圳市開了假設干家飲料連鎖店，為了提高治理水平，他打算用Excel工作表來治理銷售數據。以下圖是他制作的各飲料店的銷售記錄流水帳表。為了統計“毛利潤”，他必需去“飲料根本信息”表中查找每種飲料的“進價”和“售價”。這個工作量實在太大，而且還簡潔出錯。現在期望：能否輸入飲料名稱后，讓Excel依據這個名稱自動去查找該飲料的“單位”、“進價”、“售價”等信息，并存放到表“銷售記錄”的相應列中。解決方案通常狀況下，假設不借助其它方法的幫助，要想在Excel中解決這個問題，只能到“飲料根本信息”表中一條一條地查找各種飲料的“進價”和“售價”。假設不想這么做，你有什么更好的方法嗎？這個實際需求，開發Excel的工程師，已經為我們想到了。在Excel中有一個函數，就是特地為解決這類問題設計的，這個函數VLOOKUP。小李這個問題，可利用Excel中的查找函數VLOOKUP來解決。它的功能是，在數據區域的第一列中查找指定的數值，并返回數據區域當前行中指定列處的數值。下面來看看應用VLOOKUP函數是如何解決上述問題的。實現方法本案例要解決如下幾個問題：在“銷售記錄”工作表中用VLOOKUP函數計算飲料的“單位”、“進價”和“售價”等信息，并計算出工作表中的“銷售額”和“毛利潤”等信息。用“分類匯總”統計出各連鎖店和各個區中各種飲料的“銷售額”、“毛利潤”。用“數據透視表”分析各個區中每種飲料的銷售狀況和各個區中銷售狀況最好的飲料。另外，為了提高效率、避開出錯，小李還想制作一張可以從下拉列表中選擇飲料名稱，并能自動計算出顧客應交款及應找回款的“銷售記錄”工作表。制作過程STEP1—VLOOKUP函數的使用設計目標參照以下圖，依據列，利用VLOOKUP函數在“飲料根本信息”表中查找其他列〔單位、進價和售價〕的值。*歐陽光明*創編 2023.03.07依據以上兩個表中的數據得到以下圖中的結果額、毛利潤、毛利率*歐陽光明*創編 2023.03.07*歐陽光明*歐陽光明*創編2023.03.07*歐陽光明*歐陽光明*創編2023.03.07VLOOKUP函數是干什么用的VLOOKUP函數的功能：查找數據區域首列滿足條件的元素，并返回數據區域當前行中指定列處的值。VLOOKUP的語法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)①查找什么？②到哪個區域查找？③查找區域第幾列的值？④準確查找還是模糊查找？留意：要查找的對象〔參數①〕肯定要定義在查找數據區域〔參數②〕的第一列。VLOOKUP函數是怎么用的。如何查找“單位”？翻開文件“飲料銷售(素材).xls”，將文件另存為“姓名_飲料銷售.xls”。在“銷售記錄”工作表中，選中F3單元格→選擇“插入→函數→VLOOKUP”→單擊“確定”。由于要依據飲料的名稱查找“單位”，所以VLOOKUP函數的第一個參數應中選擇飲料名稱“D3”。在“Table_array區域中選擇飲料價格工作表中的B2:E44。由于“單位”數據存放在“飲料信息”2列，所以輸入數字“2”。由于要求飲料名稱準確匹配，所以最終一個參數輸入“FALSE”。單擊“確定”，可以看到函數準確地返回了“統一奶茶”的單位“瓶”。復制公式,消滅了什么問題為什么？如何解決呢？提示：留意在復制公式時，假設沿著列拖動時，列標要用確定引用，想想看為什么？修改公式后，重復制公式。創立區域名稱選擇“飲料名稱”工作表。選中飲料名稱、單位、進價、售價所在的區域，即單B2:E44。在名稱框中輸入“自己的姓名＋A”，肯定要按回車確溫馨溫馨提示：插入→名稱→定義A認。思考問題：區域的定義可以包含“序號”列嗎？為什么？假設名稱定義錯誤了，如何將其刪除。利用區域名查找“進價”和“售價”選擇“銷售記錄”工作表→選中G3單元格→輸入等號“=”→選擇函數“VLOOKUP”→單擊“確定”。由于要依據飲料的名稱查找“售價”，所以VLOOKUP函數的第一個參數應中選擇飲料名稱“D3”。在“Table_array”區域中輸入“A”。由于“進價”3列，所以輸入數字“3”。由于要求飲料名稱準確匹配，所以最終一個參數輸入“FALSE”單擊“確定”，可以看到函數準確地返回了“統一奶茶”的“進價”數據“1.9”。

VLOOKUP函數你是復制公式，看一看有什么不同 否有了肯定的生疏？假設還不會，H3多遍慢”式。計算銷售額、毛利潤溫馨溫馨提示：表格中數據之間的關系為：＝*數量＝(售價－進價)×數量2—分類匯總首先，*歐陽光明*創編 2023.03.07建立表“銷售記錄”的二個副本“銷售記錄(2)”、“銷售記錄(3)”和“銷售記錄(4)”。溫馨提示：Ctrl鍵拖開工作表標簽依據“所在區”進展分類匯總在表“銷售記錄(2)”中按“所在區”對銷售額和毛利潤進展分類溫馨提示：選擇“所在區”列中的任意單元格單擊“升序”按鈕 進展排序〔排序的作用是什么？〕然后選擇“數據”→“分類匯總”。匯總，匯總結果顯示在數據下方〔結果見樣例飲料銷售.xls”〕。將銷售記錄(2)改名為“所在區匯總”結果參見樣例中的工作表——“所在區匯總”依據“飲料名稱”進展分類匯總在表“銷售記錄(3)”中按“飲料名稱”對銷售額和毛利潤進展分類匯總〔匯總方式為求和〕對匯總結果中的毛利潤列按降序排序，找出毛利潤最大的飲*歐陽光明*創編 2023.03.07*歐陽光明*創編 2023.03.07料。將銷售記錄(3)改名為“飲料名稱匯總”結果參見樣例中的工作表——“飲料名稱匯總”溫馨溫馨提示：首先依據“飲料名稱”對“銷售額”和“毛利潤”進展分類匯總；然后，對“毛利潤”進展降序排序。用“”統計各個區和各飲料店的飲料“銷售額”和“毛利潤”在“(4)”“”選擇“所在區”，“次要關鍵字”選擇“飲料店”排序。進展第一次“分類匯總”〔分類字段為“所在區”〕。進展其次次“分類匯總”〔分類字段為“飲料店”〕。將銷售記錄(4)改名為“嵌套匯總”結果參見樣例中的工作表——“嵌套匯總”溫馨提示：首先，選擇“數據”→“排序”命令，在主要關鍵字中選擇，在次要關鍵字中選擇； 然后，先依據“所在區”進展分類匯總； 飲料店”進展匯總，但是其次次匯總時，必需取消“替換當前分類匯總”。3—利用數據透視表實現統計分析設計目標*歐陽光明*創編 2023.03.07*歐陽光明*創編 2023.03.07如以下圖所示，利用數據透視表生成三維數據統計報表。利用排序，找出銷售額最大的飲料為“銷售記錄”表創立數據透視表要求：為表“銷售記錄”建立數據透視表，將數據透視表顯示在工作表中，并將數據透視表命名為“銷售統計”行顯示“”“所在區”，“銷售額”顯示在數據區。結果參見樣例中的“銷售統計”工作表。*歐陽光明*創編 2023.03.07*歐陽光明*創編 2023.03.07溫馨提示： 選中“銷售記錄”工作表的任意單元格，選擇“數據數據透視表和數據透視圖”； 單擊“下一步”，選擇銷售數據所在區域； 單擊“下一步”，翻開“數據透視表和數據透視表視圖向導之3”； 單擊“布局”按鈕，翻開“數據透視表和數據透視表視圖向導—布局”對話框，將“飲料名稱”拖到左邊圖形的“行”上，“所在區”拖到“列”上，銷售額拖到“數據”中，如以下圖所示； 單擊“確定”，數據透視表顯示位置：“建工作表”。 單擊“完成*歐陽光明*創編 2023.03.07*歐陽光明*歐陽光明*創編2023.03.07*歐陽光明*歐陽光明*創編2023.03.07依據數據透視表找出銷售額最大的飲料對毛利潤按降序排序，找出銷售額最大的三種飲料。結果參見樣例中的“銷售統計”工作表溫馨溫馨提示：選中“總計”列的數值局部〔不包含最終一行數值〕然后單擊工具欄中的排序按鈕進展排序。在“銷售統計”工作表中，找出各個區“銷售額”最大的飲料在“銷售統計”工作表中，用MAX函數找出每個區“銷售額”最大的飲料的“銷售額”。在“銷售統計”工作表中，用VLOOKUP函數找出各區“最大銷售額”所對應的“飲料名稱”。STEP4—用“兩軸線-柱圖”比較“銷售額”和“毛利潤”設計目標現在，小李想用“兩軸線-柱圖”比較“南山區”、“福田區”和“羅湖區”的銷售額和毛利潤之間的關系。如以下圖所示，用兩軸線柱圖表來比較銷售額和毛利潤：制作“兩軸線圖”在表“所在區匯總”分別選擇“所在區”、“銷售額”和“毛利潤”三列〔如以下圖〕所示；單擊“圖表向導”按鈕，翻開“圖表向導-4-圖表類型”對話框，選擇“自定義類型”選項卡，選擇“兩軸線-柱圖”。單擊“下一步”，翻開“圖表向導-4-圖表源數據”對話框；單擊“下一步”，翻開“圖表向導-4-圖表選項”對話框，按以下圖進展設置；單擊“下一步”，在“圖表位置”對話框中，選擇“作為其中的對象插入”，單擊“完成”按鈕。美化圖表參照“飲料銷售(樣例).xls”，完成對圖表格式的設置〔格式可以自定義〕。STEP5—利用數據有效性制作更便利、更有用的“銷售記錄”工作表設計目標在“飲料銷售.xls”中制作一張“銷售記錄表”，并應用數據有效性設置，使得在填寫了銷售“數量”和選取了“飲料名稱”后，可以自動計算出“銷售額”、“毛利潤”和“毛利率”。制作“銷售記錄”表副本在“飲料銷售.xls”中建立一個“銷售記錄”表的副本，并將其重命名為“銷售記錄”，然后將其前五列的內容刪除〔只保存標題行〕，如以下圖所示。3個列“實收”、“應收”和“找回”對“飲料名稱”應用數據有效性設置選中“飲料價格”表中的“飲料名稱”區域，并將其定義為“飲料名稱”，如以下圖所示。選中“銷售記錄”的第4列〔“飲料名稱”列〕，然后再選擇“數據”→“有效性”翻開數據有效性對話框。在有效性條件中選擇“序列”。在“來源”中填寫“=”〔“飲料名稱”是定義的“飲料名稱”區域〕，如圖下所示。數據有效性的使用制作完成，如以下圖所示，試試看，是不是在填寫了銷售“數量”和選取了“飲料名稱”后，可以自動計算出“銷售額”、“毛利潤”和“毛利率”，很便利吧！“飲料店”列進展數據有效性設置參照“飲料名稱”的設置對“飲料店”進展設置創立“應收”和“找回”列的公式，算法如下：應收=銷售額，找回=實收－應收。STEP6—讓查找公式更完善隱蔽列在“銷售記錄”工作表中將不需要顯示的列〔“單位”、“進價”、“銷售額”〕隱蔽起來。前面的結果有缺陷在上面應用了查找函數VLOOKUP的“銷售記錄”表中，假設把D3單元格中的飲料名稱刪去以后，可以看到F3、G3、H3單元格〔即：單位、售價和進價〕中均返回錯誤值“#N/A”。大家可以試一下，當VLOOKUP函數在“飲料價格”表中沒有找D3單元格中的飲料名稱時都要返回錯誤值“#N/A”。能不能讓當VLOOKUP函數在“飲料信息”表中沒有找到D3單元格中的飲料名稱時不返回錯誤值“#N/A，而只什么都不顯示〔即顯示空格〕呢？問題：利用IF函數和ISERROR函數，使“銷售額”、“毛收入”和“毛利潤”三列的值，在沒有輸入飲料名稱時，不顯示“#VALUE!”錯誤值。〔2〕解決方法利用IF和ISERROR函數可以解決上面的問題。試試看，你能解決這個問題嗎？上面的問題變成兩種狀況：“VLOOKUP(D3,ylmc,2,FALSE)局部返回錯誤值F3中顯示空字符串。假設“VLOOKUP(D3,ylmc,2,FALSE)局部工作正常，則在F3 單元格中顯示飲料對應的“售價”〔即VLOOKUP(D3,ylmc,2,FALSE)的值〕。*歐陽光明*創編 2023.03.07溫馨提示：ISERROR(value)當變量value是錯誤值“#N/A”時，返回規律真〔TRUE〕，因此，當它與函數IF結合在一起使用時，可以用于在公式中查出錯誤值。IFISERRORIF(ISERROR(VALUE),””,VALUE)STEP7—凍結窗口保存標題行在“銷售記錄”工作表中，當銷售記錄增多后，為了保存標題行A1單元格，執行“窗口”→“凍結窗口”命令，當滾動屏幕時，可以把第一行〔即表頭行〕的內容始終保存在窗口中。4.重點難點〔1〕

重點VLOOKUP函數的使用。區域名稱的定義分類匯總數據透視表*歐陽光明*創編 2023.03.07*歐陽光明*歐陽光明*創編2023.03.07*歐陽光明*歐陽光明*創編2023.03.07〔2〕

難點VLOOKUP函數的參數的選擇數據透視表案例總結與常見問題本案例通過對學生信息表數據的處理，介紹了查找與引用類函VLOOKUP的用法及分類匯總和數據透視表的用法。〔一〕你知道了嗎？通過本案例的學習，你能答復如下問題嗎？VLOOKUP函數是干什么的？用VLOOKUP函數進展查找時，所要查找的元素為什么肯定要在所定義的數據區域的第一列？VLOOKUP函數中其次個參數的含義是什么？在定義其次個參數時要留意什么？在對VLOOKPU函數沿列進展復制時，一般狀況下，為什么列標要用確定地址？如何定義數據區域，如何刪除數據區域？VLOOKPU函數時，可以不定義數據區域嗎？什么是分類匯總？它有什么作用？在分類匯總之前要留意什么？〔二〕常見問題及處理方法下面把大家在本案例的學習過程中簡潔遇到的一些問題及處理方法列于下表：常見問題可能緣由處理方法將其刪除，重定義，方法：名稱無該數據區域名稱已被定選擇插入名稱定法重定義。義。義””對話框，選擇已定義的名稱，單擊“刪除”命令。VLOOKUP函數返回錯誤值“#N/A”。使用沿列拖動復制公式時消滅錯誤值“#N/A”。分類匯總結果不正確。

沒有把要查找的對象定義在“數據區域”的第一列。〕查找的內容在定義的“數據區域”中不存在。要查找的對象〔VLOOKUP函數的第一個參數〕列標沒有用確定引用。在“分類匯總”之前沒先按要“分類”的字段排序。雖然已先按要“分類”的字段排序，但在“”時，分類字段選擇不正確〔段〕。

重定義“數據區域”，把要查找的內容定義在“數據區域”的第一列。用ISERROR函數使錯誤值不顯示。在使用“填充柄”沿列拖動前先將第一個參數的列標確定引用。先按要分類的字段排序，然后再進展“分類匯總”。在“分類匯總”時，“分類”字段選擇已