EXCEL實(shí)訓(xùn)課件2-飲料店銷售數(shù)據(jù)分析.doc

EXCEL實(shí)訓(xùn)課件二飲料店銷售數(shù)據(jù)分析請(qǐng)采用“文檔結(jié)構(gòu)圖”的方式瀏覽課件（【視圖】【文檔結(jié)構(gòu)圖】）& 課前準(zhǔn)備1在D盤新建個(gè)人文件夾學(xué)號(hào)最后2位+姓名，如“10張三”;2打開IE，進(jìn)入計(jì)算機(jī)基礎(chǔ)教學(xué)網(wǎng) 作業(yè)提交、資料下載【登錄】；將操作素材“EXCEL實(shí)訓(xùn)課件2”下載到D盤的個(gè)人文件夾中，并解壓縮；& 實(shí)訓(xùn)目標(biāo)利用“飲料銷售(素材) .xls”學(xué)會(huì)用圖表、分類匯總、數(shù)據(jù)透視表對(duì)飲料銷售數(shù)據(jù)進(jìn)行分析處理，結(jié)果參見“飲料銷售(樣例) .xls”。任務(wù)一：在銷售記錄工作表里添加飲料的相關(guān)數(shù)據(jù)（如圖1所示）F實(shí)現(xiàn)目標(biāo)：在任務(wù)一中，我們主要完成以下任務(wù)：1查找函數(shù)VLOOKUP的使用。2名稱的定義。3公式的使用。圖1通 過 公 式 計(jì) 算 得 到通 過 函 數(shù) 計(jì) 算 得 到制作過程一、VLOOKUP函數(shù)1VLOOKUP函數(shù)的功能：查找數(shù)據(jù)區(qū)域首列滿足條件的元素，并返回?cái)?shù)據(jù)區(qū)域當(dāng)前行中指定列處的值。2VLOOKUP的語法：VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)到哪個(gè)區(qū)域的第一列查找？要查找的內(nèi)容么？返回區(qū)域第幾列的值？精確查找還是模糊查找？ 注意：要查找的對(duì)象（參數(shù)）一定要定義在查找數(shù)據(jù)區(qū)域（參數(shù)）的第一列。下面來看看VLOOKUP函數(shù)是怎么用的。二、如何查找“單位”先在“飲料價(jià)格”工作表中創(chuàng)建一個(gè)“價(jià)格”區(qū)域名稱，后面就可以在VLOOKUP的table_array參數(shù)中使用這個(gè)區(qū)域名稱，由于這個(gè)數(shù)據(jù)區(qū)域的第2列存放了“單位”數(shù)據(jù)，因此可以在這個(gè)數(shù)據(jù)區(qū)域中通過查找“飲料名稱”得到對(duì)應(yīng)的“單位”數(shù)據(jù)。 實(shí)現(xiàn)步驟如下：（參考教材P185P187）1創(chuàng)建“價(jià)格”區(qū)域名稱（1）打開“飲料銷售(素材).xls”，選擇“飲料價(jià)格”工作表，選中飲料名稱、單位、進(jìn)價(jià)、售價(jià)所在的區(qū)域（參見圖2）。提示：與Shift鍵配合，可實(shí)現(xiàn)對(duì)多個(gè)不間斷單元格的選擇圖2問題：區(qū)域的定義可以包含“序號(hào)”列嗎？為什么？答案：不行。原因：被查找的關(guān)鍵數(shù)據(jù)（飲料名稱）必須排列在被定義區(qū)域的首列。否則，將無法正確實(shí)現(xiàn)查找。（2）選擇菜單“插入名稱定義”命令（見圖3），打開“定義名稱”對(duì)話框，在名稱框中輸入“價(jià)格”后（見圖4），單擊“確定”按鈕，“價(jià)格”區(qū)域名稱創(chuàng)建完成（試試看，也可以在名稱框中直接輸入定義的名稱）。圖3圖4問題：如果名稱定義錯(cuò)誤了，如何將其刪除。2 創(chuàng)建“單位”的查找公式（1）打開“銷售記錄”工作表，選中目標(biāo)單元格F3，選擇“插入函數(shù)VLOOKUP”，單擊“確定”，彈出VLOOKUP函數(shù)的“函數(shù)參數(shù)”對(duì)話框。 （2）由于要根據(jù)飲料的名稱查找“單位”，所以VLOOKUP函數(shù)的第一個(gè)參數(shù)應(yīng)該選擇飲料名稱“D3”（見圖5）。 圖5（3）單擊“函數(shù)參數(shù)”對(duì)話框的第二個(gè)文本框右邊的折疊按鈕，接著選擇菜單“插入名稱粘貼”命令（見圖6），打開“粘貼名稱”對(duì)話框，選中名稱“價(jià)格”（見圖7），單擊“確定”，區(qū)域名稱“價(jià)格”被插入到公式中，VLOOKUP函數(shù)的第二個(gè)參數(shù)的位置。 圖6圖7用這種方法在公式中輸入?yún)^(qū)域名稱，即快捷又不容易出錯(cuò)。（4）“函數(shù)參數(shù)”對(duì)話框中的第三個(gè)參數(shù)是決定VLOOKUP函數(shù)找到匹配飲料名稱所在行以后，該行的哪列數(shù)據(jù)被返回，由于“單位”數(shù)據(jù)存放在第二列，所以在這里通過鍵盤輸入數(shù)字“2”。（5）由于要求飲料名稱精確匹配，所以最后一個(gè)參數(shù)輸入“FALSE”（見圖8），單擊“確定”，可以看到函數(shù)準(zhǔn)確地返回了“統(tǒng)一奶茶”的“單位”是“瓶”。 圖8（6）復(fù)制公式（注意在復(fù)制公式時(shí)，如果沿著列拖動(dòng)時(shí)，列標(biāo)要用絕對(duì)引用，想想看為什么？）復(fù)制公式時(shí)：可以通過使用填充柄（填充柄：位于選定區(qū)域右下角的小黑方塊。將用鼠標(biāo)指向填充柄時(shí)，鼠標(biāo)的指針更改為黑十字。）將公式復(fù)制到相鄰的單元格中。選擇包含公式的單元格，再拖動(dòng)填充柄，使之覆蓋要填充的區(qū)域。F 按照與上面查找“單位”相同的方法，自己完成下面的內(nèi)容：三、 創(chuàng)建 “進(jìn)價(jià)”和“售價(jià)”的查找公式1在G3單元格中建立查找飲料名稱“進(jìn)價(jià)”的公式。2在H3單元格中建立查找飲料名稱“售價(jià)”的公式。四、用公式計(jì)算“銷售額”和“毛利潤(rùn)”2列的值其中：銷售額=售價(jià)數(shù)量毛利潤(rùn)=(售價(jià)-進(jìn)價(jià)) 數(shù)量 設(shè)置結(jié)果：將工作表中的“進(jìn)價(jià)”、“售價(jià)”、“銷售額”和“毛利潤(rùn)”4列的數(shù)據(jù)加“￥”符號(hào)，小數(shù)位數(shù)設(shè)置為“2”。各列設(shè)置為最適合的列寬。五、 數(shù)據(jù)輸出對(duì)“銷售額”和“毛利潤(rùn)”進(jìn)行分類匯總?cè)蝿?wù)二：利用分類匯總統(tǒng)計(jì)“銷售額”和“毛利潤(rùn)”F實(shí)現(xiàn)目標(biāo)：在任務(wù)二中，我們主要完成以下任務(wù)：1用“分類匯總”計(jì)算各個(gè)區(qū)的飲料“銷售額”和“毛利潤(rùn)”（圖9）。2用“分類匯總”統(tǒng)計(jì)每種飲料的“銷售額”和“毛利潤(rùn)”（圖10）。圖9圖10請(qǐng)參見P188P192，完成對(duì)“銷售額”和“毛利潤(rùn)”進(jìn)行分類匯總的操作。什么是分類匯總？現(xiàn)在要統(tǒng)計(jì)出各個(gè)區(qū)飲料的銷售額和毛利潤(rùn)，如何來做呢？這個(gè)問題可以用“分類匯總”來解決?！胺诸悈R總”含有兩個(gè)意思，既按什么分類（各個(gè)區(qū)）和對(duì)什么匯總（銷售額和毛利潤(rùn)）。注意：進(jìn)行“分類匯總”之前，要先對(duì)分類的列（所在區(qū)）進(jìn)行排序（想想為什么？），然后再對(duì)要匯總的列（銷售額和毛利潤(rùn)）進(jìn)行總和。問題：匯總時(shí)，不想計(jì)算總和，能不能計(jì)算平均值？圖12F 按照與上面介紹的“分類匯總”的方法，自己完成下面的內(nèi)容：1建立表“銷售記錄”的三個(gè)副本“銷售記錄(2)”、“銷售記錄(3)”和“銷售記錄(4)”。提示：右單擊“銷售記錄”工作表標(biāo)簽，在彈出的快捷菜單中選擇“移動(dòng)或復(fù)制工作表”并選中“建立副本”復(fù)選框（見圖13）。提示：更簡(jiǎn)單的操作方法是：按住Ctrl鍵，再用鼠標(biāo)拖動(dòng)要復(fù)制的工作表。圖132在表“銷售記錄(2)”中用分類匯總統(tǒng)計(jì)出各個(gè)區(qū)的“銷售額”和“毛利潤(rùn)”，匯總結(jié)果顯示在數(shù)據(jù)下方（結(jié)果見圖14）。提示：先對(duì)“所在區(qū)”進(jìn)行排序（排序的作用是什么？），然后選擇“數(shù)據(jù)”“分類匯總”。匯總后2級(jí)匯總結(jié)果如下所示。隱藏明細(xì)數(shù)據(jù)行圖14方法：1）打開“銷售記錄(2)”工作表2）對(duì)“所在區(qū)”進(jìn)行排序（單擊數(shù)據(jù)清單“所在區(qū)”列中任一單元格，在工具欄中點(diǎn)擊“升序排序”按鈕）3）在菜單欄中選擇“數(shù)據(jù)”“分類匯總”命令，打開“分類匯總”對(duì)話框，（見圖12）4）單擊“確定”按鈕5）點(diǎn)擊數(shù)據(jù)清單左上角的顯示符號(hào)“2”，可看到分級(jí)的效果（見圖14）3在表“銷售記錄(3)”中用分類匯總統(tǒng)計(jì)各種飲料的“銷售額”和“毛利潤(rùn)”，并對(duì)毛利潤(rùn)按降序排序，找出毛利潤(rùn)最大的飲料。（注意：匯總前是否也要先排序？按什么排序？）六、還有方便的辦法嗎？為表“銷售記錄”創(chuàng)建數(shù)據(jù)透視表任務(wù)三：用“數(shù)據(jù)透視表”分析各區(qū)每種飲料銷售情況進(jìn)行“分類匯總”（如圖16所示）F實(shí)現(xiàn)目標(biāo)：在任務(wù)三中，我們主要完成以下任務(wù)：1用“數(shù)據(jù)透視表”進(jìn)行“分類匯總”。2工作表的重命名。3在“數(shù)據(jù)透視表”工作表中創(chuàng)建一個(gè)如右圖16所示的表格。4單元格的引用。5MAX函數(shù)的使用圖16請(qǐng)參見“飲料銷售（樣例）.xls”中的“銷售統(tǒng)計(jì)”工作表，并參閱P192P196，完成以下操作：1用“數(shù)據(jù)透視表”進(jìn)行“分類匯總”上面的問題，如果用“數(shù)據(jù)透視表”則更方便?！皵?shù)據(jù)透視表”可以很方便地對(duì)數(shù)據(jù)進(jìn)行匯總和比較。為表“銷售記錄”建立數(shù)據(jù)透視表，并將數(shù)據(jù)透視表顯示在新工作表中，并將數(shù)據(jù)透視表命名為“銷售統(tǒng)計(jì)”。 數(shù)據(jù)透視表的布局要求：1）行顯示“飲料名稱”，列顯示“所在區(qū)”，“銷售額”顯示在數(shù)據(jù)區(qū)；操作方法：（1）選中“銷售記錄”工作表的任一單元格，選擇“數(shù)據(jù)”“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”；（2）單擊“下一步”，選擇銷售數(shù)據(jù)所在區(qū)域；（3）單擊“下一步”，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視表視圖向?qū)е?”；（4）單擊“布局”按鈕，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視表視圖向?qū)Р季帧睂?duì)話框，將“飲料名稱”拖到左邊圖形的“行”上，所在區(qū)拖到“列”上，銷售額拖到“數(shù)據(jù)”中；（5）單擊“確定”，數(shù)據(jù)透視表顯示位置：“新建工作表”。（6）單擊“完成”， 將數(shù)據(jù)透視表命名為“銷售統(tǒng)計(jì)”。2）對(duì)銷售額按降序排序，找出銷售額最大的飲料。提示：選中“總計(jì)”列的數(shù)值部分（不包含最后一行數(shù)值），然后進(jìn)行排序，如圖17所示。 圖17 問題：怎樣修改數(shù)據(jù)透視表的字段？如果不想求和，如何改變匯總方式？2在“銷售統(tǒng)計(jì)”工作表中創(chuàng)建一個(gè)如圖18所示的表格，然后將“所在區(qū)”行引用到J4N4。圖18提示：同一工作表的單元格引用3用MAX函數(shù)找出各區(qū)“銷售額”最大的飲料“銷售額”。七、用兩軸線柱圖表來比較“銷售額”和“毛利潤(rùn)”任務(wù)四：利用圖表向?qū)?chuàng)建“南山區(qū)”、“福田區(qū)”和“羅湖區(qū)”的“銷售額”和“毛利潤(rùn)”的圖表（如圖20所示）F實(shí)現(xiàn)目標(biāo)：在任務(wù)四中，我們主要完成以下任務(wù)：1用兩軸線柱圖表來比較“銷售額”和“毛利潤(rùn)”2格式化圖表。圖20請(qǐng)完成以下操作：1在表“銷售記錄(2)”分別選擇所需的“所在區(qū)”、“銷售額”和“毛利潤(rùn)”（如圖21所示）。 圖21提示：選擇單元格的同時(shí)按住Ctrl鍵，可對(duì)多個(gè)間斷單元格進(jìn)行選擇2單擊“圖表向?qū)А卑粹o，打開“圖表向?qū)?4-圖表類型”對(duì)話框，選擇“自定義類型”選項(xiàng)卡，如下圖所示。選擇“兩軸線-柱圖”。3單擊“下一步”，打開“圖表向?qū)?4-圖表源數(shù)據(jù)”對(duì)話框；4單擊“下一步”，打開“圖表向?qū)?4-圖表選項(xiàng)”對(duì)話框，按下圖進(jìn)行設(shè)置；5單擊“下一步”，在“圖表位置”對(duì)話框中，選擇“作為其中的對(duì)象插入”，單擊“完成”按鈕。F按照“飲料銷售(樣例).xls”，完成對(duì)圖表區(qū)域的設(shè)置。圖表區(qū)域的填充效果用“麥浪滾滾”；繪圖區(qū)格式的填充效果用“茵茵綠原”圖例格式的填充效果用紋理“紙莎草紙”自我測(cè)試通過本案例的學(xué)習(xí)，你能回答如下問題嗎？1VLOOKUP函數(shù)是干什么的？2如何定義數(shù)據(jù)區(qū)域，如何刪除數(shù)據(jù)區(qū)域？3VLOOKUP函數(shù)中第二個(gè)參數(shù)的含義是什么？在定義第二個(gè)參數(shù)時(shí)要注意什么？4當(dāng)沿著列復(fù)制VLOOKUP函數(shù)時(shí)應(yīng)注意什么？5如何讓VLOOKUP函數(shù)在沒有查找到所查找的內(nèi)容時(shí)不返回錯(cuò)誤值“#N/A”，而顯示“沒找到”呢？6什么是分類匯總？它有什么作用？7在分類匯總之前要注意什么？8利用“圖表向?qū)А眲?chuàng)建圖表共有幾步？如何修改數(shù)據(jù)源？如何美化圖表？9在選擇數(shù)據(jù)區(qū)域時(shí)，要選擇不連續(xù)的數(shù)據(jù)區(qū)域，應(yīng)該如何操作？（答：按住Ctrl鍵，然后用鼠標(biāo)拖動(dòng)來選擇。）常見問題及處理方法下面把大家在本案例的學(xué)習(xí)過程中容易遇到的一些問題及處理方法列于下表：常見問題可能原因處理方法“數(shù)據(jù)區(qū)域”名稱無法重新定義。該“數(shù)據(jù)區(qū)域”名稱已被定義。將其刪除，重新定義，方法：選擇“插入”“名稱”“定義”，打開“定義名稱”對(duì)話框，選擇已定義的名稱，單擊“刪除”命令。VLOOKUP函數(shù)返回錯(cuò)誤值“#N/A”。（1）沒有把要查找的對(duì)象定義在“數(shù)據(jù)區(qū)域”的第一列。重新定義“數(shù)據(jù)區(qū)域”，把要查找的內(nèi)容定義在“數(shù)據(jù)區(qū)域”的第一列。（2）查找的內(nèi)容在定義的“數(shù)據(jù)區(qū)域”中不存在。用ISERROR函數(shù)使錯(cuò)誤值不顯示。使用“填充柄”沿列拖動(dòng)復(fù)制公式時(shí)出現(xiàn)錯(cuò)誤值“#N/A”。要查找的對(duì)象（VLOOKUP函數(shù)的