excel銷售數(shù)據(jù)分析-實(shí)訓(xùn)練習(xí)

1、excel 銷售數(shù)據(jù)分析-實(shí)訓(xùn)練習(xí)excel 實(shí)訓(xùn)練習(xí)三商場銷售數(shù)據(jù)的分析處理1. 問題的提出小李在深圳市開了若干家飲料連鎖店，為了提高管理 水平，他打算用 excel 工作表來管理銷售數(shù)據(jù)。下圖是 他制作的各飲料店的銷售記錄流水帳表。2為了統(tǒng)計(jì)“毛利潤”，他必須去 “飲料基本信息”表 中查找每種飲料的“進(jìn)價(jià)”和“售價(jià)”。這個(gè)工作量實(shí)在 太大，而且還容易出錯(cuò)?，F(xiàn)在希望：能否輸入飲料名稱后，讓 excel 根據(jù)這個(gè) 名稱自動去查找該飲料的“單位”、“進(jìn)價(jià)”、“售價(jià)”等 信息，并存放到表“銷售記錄”的相應(yīng)列中。32解決方案通常情況下，如果不借助其它方法的幫助，要想在 excel 中解決這個(gè)問題，只

2、能到“飲料基本信息”表中 一條一條地查找各種飲料的“進(jìn)價(jià)”和“售價(jià)”。如果不 想這么做，你有什么更好的辦法嗎？這個(gè)實(shí)際需求，開發(fā) excel 的工程師，已經(jīng)為我們想 到了。在 excel 中有一個(gè)函數(shù)，就是專門為解決這類問 題設(shè)計(jì)的，這個(gè)函數(shù)就是 vlookup。小 李 這 個(gè) 問 題 ， 可 利 用 excel 中 的 查 找 函 數(shù) vlookup 來解決。它的功能是，在數(shù)據(jù)區(qū)域的第一列 中查找指定的數(shù)值，并返回?cái)?shù)據(jù)區(qū)域當(dāng)前行中指定列處 的數(shù)值。下面來看看應(yīng)用 vlookup 函數(shù)是如何解決 上述問題的。43.實(shí)現(xiàn)方法本案例要解決如下幾個(gè)問題：1在“銷售記錄”工作表中用 vlookup 函

3、數(shù)計(jì)算飲料的“單位”、“進(jìn)價(jià)”和“售價(jià)”等信息，并計(jì) 算出工作表中的“銷售額”和“毛利潤”等信息。2 用“分類匯總”統(tǒng)計(jì)出各連鎖店和各個(gè)區(qū)中各種 飲料的“銷售額”、“毛利潤”。3 用“數(shù)據(jù)透視表”分析各個(gè)區(qū)中每種飲料的銷售 情況和各個(gè)區(qū)中銷售情況最好的飲料。4 另外，為了提高效率、避免出錯(cuò)，小李還想制作 一張可以從下拉列表中選擇飲料名稱，并能自動計(jì)算出 顧客應(yīng)交款及應(yīng)找回款的“新銷售記錄”工作表。54制作過程step 1vlookup函數(shù)的使用設(shè)計(jì)目標(biāo)參照下圖，根據(jù)“銷售記錄”表中的“ 飲料名稱 ”列， 利用 vlookup 函數(shù)在“飲料基本信息”表中查找其 他列（ 單位、進(jìn)價(jià)和售價(jià) ）的值。

4、根 據(jù) 以 上 兩 個(gè) 表 中 的 數(shù) 據(jù) 得 到 下 圖中的結(jié)果6最后計(jì)算銷售額、 毛利潤、毛利率7vlookup 函數(shù)（1）vlookup 函數(shù)是干什么用的vlookup 函數(shù)的功能：查找數(shù)據(jù)區(qū)域首列滿足條件的元素，并返回?cái)?shù)據(jù)區(qū)域當(dāng)前行中指定列處的 值。vlookup 的語法：vlookup(lookup_value,table_arr查找什么？注意：到哪個(gè)區(qū)域查找？ 查找區(qū)域第幾列的值？精確查找 還是模糊查找？要查找的對象（參數(shù)）一定要定義在查找數(shù)據(jù)區(qū) 域（參數(shù)）的第一列。下面來看看 是怎么用的。8（2）如何查找“單位”？打開文件“飲料銷售 (素材 ).xls ”，將文件另存為“ 姓名_

5、飲料銷售 .xls ”。在“銷售記錄”工作表 中，選中 f3 單元格 選擇“插入函數(shù) vlookup ” 單擊“確定”。由 于 要 根 據(jù) 飲 料 的 名 稱 查 找 “ 單 位 ” ， 所 以vlookup 函數(shù)的第一個(gè)參數(shù)應(yīng)該選擇飲料名稱 “d3”。在“ table_array ”區(qū)域中選擇“ 飲料價(jià)格 ”工作表中的 b2:e44。由于“單位”數(shù)據(jù)存放在“飲料信息”數(shù)據(jù)區(qū)域的第 2 列，所以輸入數(shù)字“ 2”。由于要求飲料名稱精確匹配，所以最后一個(gè)參數(shù)輸入“ false”。9單擊“確定”，可以看到函數(shù)準(zhǔn)確地返回了“統(tǒng)一奶茶”的單位“瓶”。復(fù)制公式 , 出現(xiàn)了什么問題？為什么？如何解決呢？提示

6、：注意在復(fù)制公式時(shí)，如果沿著列拖動 時(shí)，列標(biāo)要用絕對引用，想想看為什么？修改公式后，重新復(fù)制公式。10f溫馨（3）創(chuàng)建區(qū)域名稱選擇“飲料名稱”工作表。選中飲料名稱、單位、進(jìn)價(jià)、售價(jià)所在的區(qū)域，即單元格區(qū)域 b2:e44 。在名稱框中輸入“自己的姓名 a”，一定要按回提示：也可以采用以下方法定義區(qū)域名稱： 選 擇菜單“ 插入名稱定義 ”命令， 打開“定義名稱”對話框，問題：（1） 區(qū)域的定義可以包含“序號”列嗎？為什么？（2） 如果名稱定義錯(cuò)誤了，如何將其刪除。車確認(rèn)。思 考11（4）利用區(qū)域名查找“進(jìn)價(jià)”和 “售價(jià)”選擇“銷售記錄” 工作表 選中 g3 單元格 輸入等號“=”選擇函數(shù)“ vlo

7、okup ” 單擊“確 定”。由于要根據(jù) 飲料的名稱 查找“售價(jià)”，所以 vlookup函數(shù)的第一個(gè)參數(shù)應(yīng)該選擇飲料名稱“ d3”。在“ table_array ”區(qū)域中輸入“ 姓名 a”。由于“進(jìn)價(jià)”數(shù)據(jù)存放在第 3 列，所以輸入數(shù)字“3”。 由于要求飲料名稱精確匹配，所以最后一個(gè)參數(shù)輸入“ false”單擊“確定 ”，可以看到函數(shù)準(zhǔn)確地返回了“統(tǒng)一奶茶”的“進(jìn)價(jià)”數(shù)據(jù)“ 1.9 ”。怎 么 樣 ， 對 于 vlookup 函數(shù)你是 復(fù)制公式，看一看有什么不同？ 同樣道理在 h3 單元格中建立查找飲料名稱“售價(jià)”12的公式。13（5）計(jì)算銷售額、毛利潤溫馨提示 ：表格中數(shù)據(jù)之間的關(guān)系為： 銷

8、售額 售價(jià) *數(shù)量 毛利潤 ( 售價(jià)進(jìn)價(jià) ) 數(shù)量14step 2分類匯總首先，建立表“銷售記錄”的二個(gè)副本“ 銷售記錄 (2)”、 “銷售記錄 (3) ”和“ 銷售記錄 (4)”。f溫馨提示 ：按住 ctrl 鍵拖動工作表標(biāo)簽15（1）按照“所在區(qū)”進(jìn)行分類匯總在表“銷售記錄 (2) ”中按“所在區(qū)”對銷售額和毛利f溫馨提示 ：潤 選擇“所在區(qū)”列中的任意單元格進(jìn)行 單擊“升序”按鈕 作用是什么？）進(jìn)行排序（排序的分類匯總，匯總結(jié)果顯示在數(shù)據(jù)下方（結(jié)果見“ ( 樣例 ) 飲料銷售 .xls ”）。將銷售記錄 (2) 改名為“所在區(qū)匯總” 結(jié)果參見樣例中的工作表“ 所在區(qū)匯總 ”16（2）按照

9、“飲料名稱”進(jìn)行分類匯總在表“銷售記錄 (3) ”中按“飲料名稱”對銷售額 和毛利潤 進(jìn)行分類匯總（匯總方式為 求和 ）對匯總結(jié)果中的毛利潤列按 降序 排序，找出毛利潤最大的飲料。將銷售記錄 (3) 改名為“飲料名稱匯總”結(jié)果參見樣例中的工作表“ 飲料名稱匯總 ”f溫馨提示 ：首先按照“飲料名稱”對“銷售額” 和“毛利潤”進(jìn)行分類匯總；17（3）用 “嵌套分類匯總”統(tǒng)計(jì)各個(gè)區(qū)和各飲料店的飲料“銷售額 ”和“毛利潤 ”在 “銷售記錄 (4)”工作表中對 “主要關(guān)鍵字 ”選擇 “所在區(qū) ”，“次要關(guān)鍵字 ”選擇“飲料店 ”排序。進(jìn)行第一次 “分類匯總 ”（分類字段為 “所在區(qū) ”）。 進(jìn)行第二次

10、“分類匯總 ”（分類字段為 “飲料店 ”）。 將銷售記錄 (4) 改名為“嵌套匯總”結(jié)果參見樣例中的工作表“ 嵌套匯總 ”f溫馨提示 ：首先，選擇“數(shù)據(jù)”“排序” 命 令，在主要關(guān)鍵字中選擇 “所在 區(qū)” ，在次要關(guān)鍵字中選擇 “飲 料店” ；18step 3利用數(shù)據(jù)透視表實(shí)現(xiàn)統(tǒng)計(jì)分析設(shè)計(jì)目標(biāo)如下圖所示，利用數(shù)據(jù)透視表生成三維數(shù)據(jù)統(tǒng)計(jì)報(bào) 表。利用排序，找 出 銷 售 額 最 大的飲料19（1）為 “銷售記錄”表創(chuàng)建數(shù)據(jù)透視表要求：為表“銷售記錄 ”建立數(shù)據(jù)透視表，將數(shù)據(jù)透視表顯 示在新工作表中，并將數(shù)據(jù)透視表命名為“ 銷售統(tǒng)計(jì) ”行顯示“飲料名稱 ”，列顯示“所在區(qū) ”，“銷售額 ” 顯示在數(shù)

11、據(jù)區(qū)。結(jié)果參見樣例中的“ 銷售統(tǒng)計(jì)”工作表。溫馨提示 ： 選中“銷售記錄”工作表的任意單元格， 選擇“ 數(shù)據(jù) ”“ 數(shù)據(jù)透視表和數(shù)據(jù)透 視圖 ”； 單擊“ 下一步 ”，選擇銷售數(shù)據(jù)所在區(qū) 域； 單擊“ 下一步 ”，打開“數(shù)據(jù)透視表和 數(shù)據(jù)透視表視圖向?qū)е?3 ”； 單擊“ 布局 ”按鈕，打開“數(shù)據(jù)透視表 和數(shù)據(jù)透視表視圖向?qū)Р季帧睂υ捒颍?0（2）根據(jù)數(shù)據(jù)透視表找出銷售額最大的飲料對毛利潤按降序排序，找出銷售額最大的三種飲料。結(jié)果參見樣例中的“ 銷售統(tǒng)計(jì) ”工作表f溫馨提示 ：選中“總計(jì)”列的數(shù)值部分（不包含 最后一行數(shù)值）21（3）在“銷售統(tǒng)計(jì)”工作表中，找出各個(gè)區(qū)“銷售額”最大的飲料在“銷

12、售統(tǒng)計(jì)”工作表中，用 max 函數(shù)找出每個(gè)區(qū)“銷售額”最大的飲料的“銷售額”。在“銷售統(tǒng)計(jì)”工作表中，用 vlookup 函數(shù)找出各區(qū)“最大銷售額”所對應(yīng)的“飲料名稱”。22step 4用“兩軸線-柱圖”比較“銷售額”和“毛利潤”設(shè)計(jì)目標(biāo)現(xiàn)在，小李想用“兩軸線-柱圖”比較“南山區(qū)”、“福 田區(qū)”和“羅湖區(qū)”的 銷售額 和毛利潤 之間的關(guān)系。如下圖所示，用兩軸線柱圖表來比較銷售額和毛利 潤：23（1）制作“兩軸線圖”在表“所在區(qū)匯總”分別選擇“所在區(qū)”、“銷售額”和“毛利潤”三列（如下圖）所示；單擊“圖表向?qū)А卑粹o，打開“圖表向?qū)?-4-圖表類型”對話框，選擇“ 自定義類型 ”選項(xiàng)卡，選擇“兩軸

13、線 - 柱圖”。單擊“下一步”，打開“圖表向?qū)?-4-圖表源數(shù)據(jù)”對話框；24單擊“下一步”，打開“圖表向?qū)?-4-圖表選項(xiàng)”對話框，按下圖進(jìn)行設(shè)置；單擊“下一步”，在“圖表位置”對話框中，選擇“作為其中的對象插入”，單擊“完成”按鈕。25（2）美化圖表參照“飲料銷售 ( 樣例 ).xls ”，完成對圖表格式的設(shè)置 （格式可以自定義）。26step 5利用數(shù)據(jù)有效性制作更方便、更實(shí)用的“新銷售記錄”工 作表設(shè)計(jì)目標(biāo)在“飲料銷售 .xls”中制作一張“新銷售記錄表”，并 應(yīng)用數(shù)據(jù)有效性設(shè)置，使得在填寫了銷售“數(shù)量”和選 取了“飲料名稱”后，可以自動計(jì)算出“銷售額”、“毛 利潤”和“毛利率”。27

14、（1）制作“銷售記錄”表副本在“飲料銷售 .xls ”中建立一個(gè)“銷售記錄”表的副本，并將其重命名為“新銷售記錄”，然后將其前五 列的內(nèi)容刪除（只保留標(biāo)題行），如下圖所示。并增加 3 個(gè)新列“實(shí)收”、“應(yīng)收”和“找回”28（2）對“飲料名稱”應(yīng)用數(shù)據(jù)有效性設(shè)置選中“飲料價(jià)格”表中的“飲料名稱”區(qū)域，并將其定義為“飲料名稱”，如下圖所示。選中“新銷售記錄”的第 4 列（“飲料名稱”列），然后再選擇“數(shù)據(jù)”“有效性”打開數(shù)據(jù)有效性對 話框。在有效性條件中選擇“序列”。在“來源”中填寫“ =飲料名稱”（注意：“飲料名稱”是定義的“飲料名稱”區(qū)域），如圖下所示。2930（3）數(shù)據(jù)有效性的使用制作完成，

15、如下圖所示，試試看，是不是在填寫了銷 售“數(shù)量”和選取了“飲料名稱”后，可以自動計(jì)算出 “銷售額”、“毛利潤”和“毛利率”，很方便吧！31（4）對 “飲料店”列進(jìn)行數(shù)據(jù)有效性設(shè)置參照“飲料名稱”的設(shè)置對“飲料店”進(jìn)行設(shè)置（5）創(chuàng)建 “應(yīng)收”和“找回”列的公式，算法如下：應(yīng)收 =銷售額，找回 =實(shí)收應(yīng)收。32step 6讓查找公式更完美（1）隱藏列在“新銷售記錄”工作表中將不需要顯示的列（ “ 單 位”、“進(jìn)價(jià) ”、“銷售額 ”）隱藏起來。（2）前面的結(jié)果有缺陷在上面應(yīng)用了查找函數(shù) vlookup 的“銷售記錄” 表中，如果把 d3 單元格中的飲料名稱刪去以后，可以 看到 f3、g3、h3 單元

16、格（即：單位、售價(jià)和進(jìn)價(jià)）中 均返回錯(cuò)誤值“ #n/a”。大家可以試一下，當(dāng) vlookup 函數(shù)在“飲料價(jià)格” 表中沒有找到 d3 單元格中的飲料名稱時(shí)都要返回錯(cuò)誤 值“ #n/a”。能不能讓當(dāng) vlookup 函數(shù)在“飲料信息”表中沒 有 找 到 d3 單 元 格 中 的 飲 料 名 稱 時(shí) 不 返 回 錯(cuò) 誤 值 “#n/a”，而只什么都不顯示（即顯示空格）呢？ 問題：33利用 if 函數(shù)和 iserror 函數(shù)，使“銷售額”、“毛收入”和“毛利潤”三列的值，在沒有 輸入飲料名稱時(shí)，不顯示“ #value! ”錯(cuò)誤值。34iserror（？）（2）解決辦法f利用 if 和 iserror

17、 函數(shù)可以解決上面的問題。試試看，你能解決這個(gè)問題嗎？ 上面的問題變成兩種情況：如果當(dāng)“ vlookup(d3,ylmc,2,false) ”部分 返回錯(cuò)誤值的話，則在 f3 中顯示 空字符串 。 如果“ vlookup(d3,ylmc,2,false) ”部分工 作正常，則在 f3 單元格中顯示飲料對應(yīng)的“售f溫馨 提示：iserror(value) 函數(shù)：當(dāng)變量 value 是錯(cuò)誤值“#n/a ”時(shí)，返回 邏輯真（true ），因此，當(dāng)它與函數(shù) if 結(jié) 合在一起使用時(shí)，可以用于在公式中查出錯(cuò) 誤值。if 函數(shù)與 iserror 函數(shù)聯(lián)合使用的通用 表示法 :if(iserror( val

18、ue ),”,value )出錯(cuò)嗎？d3,ylmc,2,false) 的值）。否是價(jià)”（即vlookup(35空字符串vlookup(c2,姓名 a,3,false)step 7凍結(jié)窗口保留標(biāo)題行在“銷售記錄”工作表中，當(dāng)銷售記錄增多后，為 了保留標(biāo)題行的內(nèi)容，可單擊 a1 單元格，執(zhí)行“窗口” “凍結(jié)窗口”命令，當(dāng)滾動屏幕時(shí)，可以把第一行（即 表頭行）的內(nèi)容一直保留在窗口中。364. 重點(diǎn)難點(diǎn)（1） 重點(diǎn)l vlookup 函數(shù)的使用。 l 區(qū)域名稱的定義l 分類匯總l 數(shù)據(jù)透視表（2） 難點(diǎn)l vlookup 函數(shù)的參數(shù)的選擇 l 數(shù)據(jù)透視表375. 案例總結(jié)與常見問題本案例通過對學(xué)生信息

19、表數(shù)據(jù)的處理，介紹了查找與 引用類函數(shù) vlookup 的用法及分類匯總和數(shù)據(jù)透視 表的用法。（一）你知道了嗎？通過本案例的學(xué)習(xí)，你能回答如下問題嗎？1 vlookup 函數(shù)是干什么的？2 用 vlookup 函數(shù)進(jìn)行查找時(shí)，所要查找的元素為什么一定要在所定義的 數(shù)據(jù)區(qū)域的第一列？3 vlookup 函數(shù)中第二個(gè)參數(shù)的含義是什么？在定義第二個(gè)參數(shù)時(shí)要注意 什么？4 在對 vlookpu 函數(shù)沿列進(jìn)行復(fù)制時(shí)，一般情況下，為什么列標(biāo)要用絕對 地址？5 如何定義數(shù)據(jù)區(qū)域，如何刪除數(shù)據(jù)區(qū)域？6 用 vlookpu 函數(shù)時(shí)，可以不定義數(shù)據(jù)區(qū)域嗎？7 什么是分類匯總？它有什么作用？8 在分類匯總之前要注意

20、什么？（二）常見問題及處理方法下面把大家在本案例的學(xué)習(xí)過程中容易遇到的一些 問題及處理方法列于下表：常見問題可能原因處理方法38將其刪除，重 新定義，方法： 選擇“插入”“ 數(shù) 據(jù) 區(qū) “名稱” 域”名稱無 該“數(shù)據(jù)區(qū)域” “定義”，打 法 重 新 定 名稱已被定義。 開“定義名稱”義。（ 1）沒有把要 查 找 的 對 象 定vlookup 義 在 “ 數(shù) 據(jù) 區(qū) 函數(shù)返回錯(cuò) 域”的第一列。 誤 值（ 2）查找的內(nèi) “#n/a”。容在定義的“數(shù) 據(jù)區(qū)域”中不存 在。對話框，選擇 已 定 義 的 名 稱，單擊“刪 除”命令。 重新定義“數(shù) 據(jù)區(qū)域”，把要 查找的內(nèi)容定 義在“數(shù)據(jù)區(qū) 域”的第一列

21、。用 iserror 函數(shù)使錯(cuò)誤值 不顯示。使用“填充 要 查 找 的 對 象 在使用“填充 柄”沿列拖 （ vlookup 柄”沿列拖動 動復(fù)制公式 函 數(shù) 的 第 一 個(gè) 前先將第一個(gè) 時(shí)出現(xiàn)錯(cuò)誤 參數(shù)）列標(biāo)沒有 參數(shù)的列標(biāo)絕39值“#n/a”。用絕對引用。對引用。（ 1）在“分類 先按要分類的 匯總”之前沒先 字段排序，然 按要“分類”的 后再進(jìn)行“分字段排序。 （ 2）雖然已先類匯總”。分類匯總結(jié) 按要“分類”的果不正確。 字段排序，但在 在“分類匯總” “ 分 類 匯 總 ” 時(shí)，“分類” 時(shí)，分類字段選 字段選擇已排 擇不正確（沒有 序的字段。 選 擇 已 排 序 的字段）。406. 課后作業(yè)：學(xué)生成績的查詢和統(tǒng)計(jì)請大家用查找函數(shù) vlookup 完成下面的案例。 （1） 問題的提出：新學(xué)期剛開始，