Excel實(shí)訓(xùn)2 飲料銷售數(shù)據(jù)分析

1、Excel實(shí)訓(xùn)2飲料店銷售數(shù)據(jù)分析一、問題的提出小李開了個飲料店，為了更好地管理每天的銷售情況，他用Excel制作了 “銷售記錄”流水帳(見 “飲料銷售(素材).xls)，其中表“銷售記錄”表給出了 5月1日到3日三天的飲料銷售記錄，如圖1所 示。“飲料基本信息”表給出了每種飲料的“進(jìn)價”和“售價”(見圖2)?，F(xiàn)在小李要統(tǒng)計(jì)三天的銷售 情況，計(jì)算一下毛利潤有多少?？粗L長的銷售記錄，小李發(fā)愁了。因?yàn)楸怼颁N售記錄”中只記載了飲 料名稱和銷售數(shù)量。ABCDEFGHIJ1日期星期飲料名稱單位售價數(shù)量銷售額進(jìn)價毛利潤毛利率22005-5-1星期日可口可樂(1L)232005-5-1星期日非?？蓸?42

2、005-5-1星期日百事可樂(1L)452005-5-1星期日樂百氏純凈水162005-5-1星期日益力純凈水172005-5-1星期日怡寶純凈水282005-5-1星期日伊利奶茶492005-5-1星期日伊利純牛奶1102005-5-1星期日雪碧(1L)2112005-5-1星期日伊利酸奶51 9早朋R榮生舔加1圖1飲料銷售記錄圖2飲料基本信息為了統(tǒng)計(jì)“毛利潤”，他必須去“飲料基本信息”表中查找每種飲料的“進(jìn)價”和“售價”這個工 作量實(shí)在太大，而且還容易出錯?，F(xiàn)在希望：能否輸入飲料名稱后，讓Excel根據(jù)這個名稱自動查找該飲料的“單位”、“進(jìn)價”、“售 價”等信息，并存放到表“銷售記錄”的相

3、應(yīng)列中。二、解決方案通常情況下，如果不借助其它方法的幫助，要想在Excel中解決這個問題，只能到“飲料基本信息” 表中一條一條地查找各種飲料的“進(jìn)價”和“售價”。如果不想這么做，你有什么更好的辦法嗎？這個實(shí)際需求，開發(fā)Excel的工程師，已經(jīng)為我們想到了。在Excel中有一個函數(shù)，就是專門為解 決這類問題設(shè)計(jì)的，這個函數(shù)就是VLOOKUPo小李這個問題，可利用Excel中的查找函數(shù)VLOOKUP來解決。它的功能是，在數(shù)據(jù)區(qū)域的第一列 中查找指定的數(shù)值，并返回?cái)?shù)據(jù)區(qū)域當(dāng)前行中指定列處的數(shù)值。下面來看看應(yīng)用VLOOKUP函數(shù)是如何 解決上述問題的。三相關(guān)知識點(diǎn)VLOOKUP 函數(shù)名稱的定義絕對引用

4、和相對引用IF函數(shù)和ISERROR函數(shù)分類匯總數(shù)據(jù)透視表四、解決步驟（一）VLOOKUP 函數(shù)VLOOKUP函數(shù)的功能：查找數(shù)據(jù)區(qū)域首列滿足條件的元素，并返回?cái)?shù)據(jù)區(qū)域當(dāng)前行中指定列 處的值。2. VLOOKUP函數(shù)的語法:注意：要查找的對象（參數(shù)）一定要定義在查找數(shù)據(jù)區(qū)域（參數(shù)）的第一列。下面來看看VLOOKUP函數(shù)是怎么用的。（二）如何查找“售價”先創(chuàng)建一個“飲料信息”區(qū)域名稱，后面就可以在VLOOKUP的table_array參數(shù)中使用這個區(qū)域名 稱，由于這個數(shù)據(jù)區(qū)域的第三列存放了 “售價”數(shù)據(jù)，因此可以在這個數(shù)據(jù)區(qū)域中通過查找“飲料名稱” 返回其對應(yīng)的“售價”數(shù)據(jù)。實(shí)現(xiàn)步驟如下：創(chuàng)建“飲

5、料信息”區(qū)域名稱（1）打開“飲料銷售（素材）.xls”，將其另存為“飲料銷售（學(xué)號姓名）.xls”。（2）選擇“飲料信息”工作表，選中飲料名稱、單位、進(jìn)價、售價所在的區(qū)域，如圖3所示。（問 題：區(qū)域的定義可以包含“序號”列嗎？為什么？）。飲料名稱單位進(jìn)價售價可口可樂聽1. 602. 00白事口J樂聽1. 602. 00可口可樂（1L）瓶2. 002. 50白事口J樂UL）瓶2. 002. 50雪碧聽1. 602. 00雪碧（1L）瓶2. 002. 50七喜聽1. 702. 20七喜（1L）瓶2. 002. 60匯源果汁A7. 809. 80美年達(dá)1. 702. 20芬達(dá)聽1. 602. 00醒

6、曰所? nn? Rn序號I1 ?圖3數(shù)據(jù)區(qū)域的選擇（3）選擇菜單“插入” 一 “名稱” 一 “定義”命令，打開“定義名稱”對話框，在名稱框中輸入“飲 料信息”后，單擊“確定”按鈕，“飲料信息”區(qū)域名稱創(chuàng)建完成，如圖4所示（試試看，也可以在名 稱框中直接輸入定義的名稱）。插入（X）格式（jo）工具（X）數(shù)據(jù)工作表也）心|函數(shù)史）.名稱（S）定義更）.圖片崖）I粘貼蜜.|指定Q.圖4名稱的定義問題：如果名稱定義錯誤了，如何將其刪除。創(chuàng)建“售價”的查找公式打開“銷售記錄”工作表，選中E2單元格，選擇“插入一函數(shù)一VLOOKUP”，單擊“確定”， 彈出VLOOKUP函數(shù)的“函數(shù)參數(shù)”對話框。由于要根據(jù)

7、飲料的名稱查找“售價”，所以VLOOKUP函數(shù)的第一個參數(shù)應(yīng)該選擇飲料名稱 “C2”，如圖5所示。圖5函數(shù)參數(shù)對話框單擊“函數(shù)參數(shù)”對話框的第二個文本框右邊的折疊按鈕，接著選擇菜單“插入一名稱一粘貼” 命令，打開“粘貼名稱”對話框，選中名稱“飲料基本信息”，單擊“確定”，區(qū)域名稱“飲料基本信 息”被插入到公式中，VLOOKUP函數(shù)的第二個參數(shù)的位置，如圖6所示。插入(!)格式迫)工具d)數(shù)據(jù)廷)窗口也)幫助QD行|_也成日宋體列改.結(jié)束審閱.-工作表此)幽圖表如FGH回函數(shù)口.數(shù)量銷售額進(jìn)價名稱*定義.匿1片史)*粘貼史).黑超鏈接IX).Ctrl+K指定Q.應(yīng)用兔).圖6粘貼名稱用這種方法在

8、公式中輸入?yún)^(qū)域名稱，即快捷又不容易出錯。（4）“函數(shù)參數(shù)”對話框中的第三個參數(shù)是決定VLOOKUP函數(shù)找到匹配飲料名稱所在行以后， 該行的哪列數(shù)據(jù)被返回，由于“售價”數(shù)據(jù)存放在第四列，所以在這里通過鍵盤輸入數(shù)字“4”。（5）由于要求飲料名稱精確匹配，所以最后一個參數(shù)輸入“ FALSE”，單擊“確定”，可以看到 函數(shù)準(zhǔn)確地返回了 “可口可樂”的“售價”數(shù)據(jù)“ 2.5 ”。（6）復(fù)制公式。注意：在復(fù)制公式時，如果沿著列拖動時，列標(biāo)要用絕對引用，想想看為什么？復(fù)制公式時：可以通過使用填充柄（填充柄：位于選定區(qū)域右下角的小黑方塊。將用鼠標(biāo)指向填充 柄時，鼠標(biāo)的指針更改為黑十字。）1=!將公式復(fù)制到相鄰的單元格中。選擇包含公式的單元格， 再拖動填充柄，使之覆蓋要填充的區(qū)域。按照與上面查找“售價”相同的方法，完