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

EXCEL實訓練習三

商場銷售數(shù)據(jù)的分析處理

1.問題的提出

小李在深圳市開了若干家飲料連鎖店，為了提高管理水平，他打算用Excel工作

表來管理銷售數(shù)據(jù)。下圖是他制作的各飲料店的銷售記錄流水帳表。

ABCDIEIFGHIT

銷售年錄表

日期所在區(qū)飲料店飲料名稱數(shù)量單位進價售價精售頡毛利潤

2007-1-1南山統(tǒng)一奶茶70

2007-1-1南山紅牛18

2007-1-1南山菠蘿啤:

2007-1-1南山非常可樂

2007-1-1南山百事可樂（1L）

2007-1-1南山娃哈哈果奶23

2007-1-1南山?81汁86

102007-1-148

2007-1-1南山王老吉

11怡寶最爭水17

122007-1-1南山

jj2007-1-1南山脈動27

142007-1-1南山果粒橙18

152007-1-1南山可口可樂（1L）27

162007-1-1南山統(tǒng)一綠茶94

172007-1-1南山56

182007-1-1南山西麗店樂百氏純凈水83

序號飲料名稱單位

1可口可樂聽1.702.10

2百事可樂聽1.602.00

3可口可樂（1L）瓶2.102.60

4百事可樂（1L）瓶2.002.50

5雪碧聽1.702.20

6雪碧（1L）瓶2.002.50

1七喜聽1.702.20

8七喜（1L）瓶2.002.60

9匯源果汁合7.809.80

扇

10美年達1.702.20

11芬達聽1.602.00

12醒目聽2.002.50

13健力寶聽1.702.20

14王老吉合1.702.20

15鮮橙多S2.403.00

16鮮桃多瓶2.403.00

17統(tǒng)一奶茶瓶1.902.40

18統(tǒng)一冰紅茶瓶1.802.30

19統(tǒng)一綠茶瓶1.802.30

20旭日升冰紅茶瓶1.702.20

為了統(tǒng)計“毛利潤”，他必需去“飲料基本信息”表中查找每種飲料的“進價”

和“售價”。這個工作量實在太大，而且還簡潔出錯。

現(xiàn)在希望：能否輸入飲料名稱后，讓Excel依據(jù)這個名稱自動去查找該飲料的

“單位”、“進價”、“售價”等信息，并存放到表“銷售記錄”的相應列中。

2.解決方案

通常狀況下，假如不借助其它方法的幫助，要想在Excel中解決這個問題，只

能到“飲料基本信息”表中一條一條地查找各種飲料的“進價”和“售價”。假如不

想這么做，你有什么更好的方法嗎？

這個實際需求，開發(fā)Excel的工程師，已經(jīng)為我們想到了。在Excel中有一個

函數(shù),就是特地為解決這類問題設計的，這個函數(shù)就是VLOOKUP。

小李這個問題，可利用Excel中的查找函數(shù)VLOOKUP來解決。它的功能是，

在數(shù)據(jù)區(qū)域的第一列中查找指定的數(shù)值，并返回數(shù)據(jù)區(qū)域當前行中指定列處的數(shù)值。

下面來看看應用VLOOKUP函數(shù)是如何解決上述問題的。

3.實現(xiàn)方法

本案例要解決如下幾個問題：

1.在“銷售記錄”工作表中用VLOOKUP函數(shù)計算飲料的“單位”、“進價”

和“售價”等信息，并計算出工作表中的“銷售額”和“毛利潤”等信息。

2.用“分類匯總”統(tǒng)計出各連鎖店和各個區(qū)中各種飲料的“銷售額”、“毛利

潤”。

3.用“數(shù)據(jù)透視表”分析各個區(qū)中每種飲料的銷售狀況和各個區(qū)中銷售狀況最

好的飲料。

4.另外，為了提高效率、避開出錯，小李還想制作一張可以從下拉列表中選擇

飲料名稱，并能自動計算出顧客應交款與應找回款的“新銷售記錄”工作表。

4.制作過程

STEP1—VLOOKUP函數(shù)的運用

設計目標

參照下圖，依據(jù)“銷售記錄”表中的“飲料名稱”歹L利用VLOOKUP函數(shù)在

“飲料基本信息”表中查找其他列（單位、進價和售價）的值。

日期所在區(qū)■飲料電1飲料名稱?塞量單位進價售價銷售頡毛利澗1

2007-1-1南山西麗店統(tǒng)一奶茶70

2007-1-1南山西麗店紅牛78

2007-1-1南山西麗店菠蘿啤16

2007-1-1南山西麗店非?？蓸?

2007-1-1南山西麗店百事可樂（1L）59

2007-1-1南山西麗店娃哈哈果奶23

2007-1-1南山西麗店葡萄汁86

2007-1-1南山西麗店雪碧48

2007-1-1南山西麗店王老吉17

2007-1-1南山西麗店怡寶死凈水62

2007-1-1南山西麗店脈動27

2007-1-1南山西麗店果粒橙18

2007-1-1南山西麗店可口可樂（1L）27

2007-1-1南山西麗店統(tǒng)一綠茶94

2007-1-1南山西麗店娃哈哈比凈水56—

序號飲料名稱單位進價售價

1可口可樂聽1.702.10

2百事可樂聽1.602.00依據(jù)以上兩個表

3可口可樂（1L）瓶2.102.60中的數(shù)據(jù)得到下

4百事可樂（1L）瓶2.002.50圖中的結(jié)果

5雪碧聽1.702.20

6雪碧（1L）瓶2.002.50

7七喜聽1.702.20

8七喜（1L）瓶2.002.60

9匯源果汁合7.809.80

藐

10美年達1.702.20

11芬達聽1.602.00

12醒目聽2.002.50

13健力寶聽1.702.20

14王老吉合1.702.20

MS年ZA

日期所在區(qū)飲料店飲料名稱數(shù)量羊it精售項毛利潤

2007-1-1南山西麗店統(tǒng)一奶茶711.92.4

2007-1-1南山西麗店紅牛753.24.2

2007-1-1南山西麗店菠蘿啤If1.21.6

2007-1-1南山西麗店非常可樂1.62

2007-1-1南山西麗店百事可樂（1L）5!22.5

2007-1-1南山西麗店娃哈哈果奶2：1.41.8

2007-1-1南山西麗店葡萄汁814.56

2007-1-1南山西麗店雪碧4(1.72.2

2007-1-1南山西麗店王老吉r1.72.2

2007-1-1南山西麗店怡寶燉凈水6：0.91.2

2007-1-1南山西麗店脈動2,22.6或終計算銷售額

2007-1-1南山西麗店果粒橙152.43毛利誨

2007-1-1南山西麗店可口可樂（1L）2,2.12.6百利俱1、

2007-1-1南山西麗店統(tǒng)一綠茶9,1.82.3___1

2007-1-1南山西麗店娃哈哈兜凈水510.91.2nni—

2007-1-1南山西麗店樂百氏婉凈水8：11.3

2007-1-1南山西麗店七喜7<1.72.2

2007-1-1南山西麗店七喜（1L）2：22.6------1

2007-1-1南山西麗店可口可樂P1.72.1\

2007-1-1南山西麗店醒目7!22.5

2007-1-1南山西麗店旭日升冰紅茶1!1.72.2

2007-1-1南山西麗店樂百氏果奶9：1.31.7

2007-1-1南山西麗店芬達2：1.62

日期所在區(qū)飲料店飲料名稱數(shù)量單位進價售價精售蒸毛利潤1

2007-1-1南山西麗店統(tǒng)一奶茶701.92.4168.0035.00

2007-1-1南山西麗店紅牛783.24.2327.6078.00

2007-1-1南山西麗店菠蘿啤161.21.625.606.40

2007-1-1南山西麗店非?？蓸?1.6216.003.20

2007-1-1南山西麗店百事可樂（1L）59瓶22.5147.5029.50

2007-1-1南山西麗店娃哈哈果奶231.41.841.409.20

2007-1-1南山西麗店葡萄汁864.56516.00129.00

2007-1-1南山西麗店雪碧481.72.2105.6024.00

2007-1-1南山西麗店王老吉17合1.72.237.408.50

2007-1-1南山西麗店怡寶純凈水62瓶0.91.274.4018.60

2007-1-1南山西麗店脈動2722.670.2016.20

2007-1-1南山西麗店果粒橙182.4354.0010.80

2007-1-1南山西麗店可口可樂（1L）272.12.670.2013.50

2007-1-1南山西麗店統(tǒng)一綠茶941.82.3216.2047.00

2007-1-1南山西麗店娃哈哈燉凈水560.91.267.2016.80

2007-1-1南山西麗店樂百氏純凈水83瓶11.3107.9024.90

2007-1-1南山西麗店七喜761.72.2167.2038.00

2007-1-1南山西麗店七喜（1L）2322.659.8013.80

2007-1-1南山西麗店可口可樂141.72.129.405.60

2007-1-1南山西麗店醒目7922.5197.5039.50

2007-1-1南山西麗店旭日升冰紅茶151.72.233.007.50

2007-1-1南山西麗店樂百氏果奶981.31.7166.6039.20

2007-1-1南山西麗店芬達221.6244.008.80

（1）VLOOKUP函數(shù)是干什么用的

★VLOOKUP函數(shù)的功能:

查找數(shù)據(jù)區(qū)域首列滿足條件的元素，并返回數(shù)據(jù)區(qū)域當前行中指定列處的值。

★VLOOKUP的語法:

留意:

要查找的對象（參數(shù)①）確定要定義在查找數(shù)據(jù)區(qū)域（參數(shù)②）的第一列。

下面來看看VLOOKUP函數(shù)是怎么用的。

(2)如何查找“單位”？

★打開文件“飲料銷售(素材).xls”，將文件另存為“姓名—飲料銷售.X1S”。

★在“銷售記錄”工作表中，選中F3單元格一選擇“插入一函數(shù)一

VLOOKUP”一單擊“確定”。

★由于要依據(jù)飲料的名稱查找“單位”，所以VLOOKUP函數(shù)的第一個參數(shù)

應當選擇飲料名稱“D3”o

★在“Table_array”區(qū)域中選擇“飲料價格”工作表中的B2:E44。

★由于“單位”數(shù)據(jù)存放在“飲料信息”數(shù)據(jù)區(qū)域的第2歹L所以輸入數(shù)字

“2”。

★由于要求飲料名稱精確匹配，所以最終一個參數(shù)輸入“FALSE”。

★單擊“確定”，可以看到函數(shù)精確地返回了“統(tǒng)一奶茶”的單位“瓶”。

★復制公式，出現(xiàn)了什么問題？？？為什么？如何解決呢？

提示：留意在復制公式時，假如沿著列拖動時，列標要用確定引用，想想

看為什么?

★修改公式后，重新復制公式。

(3)創(chuàng)建區(qū)域名稱

★選擇“飲料名稱”工作表。

★選中飲料名稱、單位、進價、售價所在的區(qū)域，即單元格區(qū)域B2:E44。

★在名稱框中輸入“自己的姓名+A”，確定要按回車確認。

域的定義

可以包含“序號”列嗎？為什么?

(2)假如名稱定義錯誤了，如何將其刪除。

(4)利用區(qū)域名查找“進價”和“售價”

★選擇“銷售記錄”工作表-選中G3單元格->輸入等號“="一選擇函數(shù)

“VLOOKUP”一單擊“確定”。

★由于要依據(jù)飲料的名稱查找“售價”，所以VLOOKUP函數(shù)的第一個參數(shù)應當

選擇飲料名稱“D3”。

★在“Table_array”區(qū)域中輸入“姓名A”。

★由于“進價”數(shù)據(jù)存放在第3歹L所以輸入數(shù)字“3"o

★由于要求飲料名稱精確匹配，所以最終一個參數(shù)輸入“FALSE”

★單擊“確定”，可以看到函數(shù)精確地返回了“統(tǒng)一奶茶”的“進價”數(shù)據(jù)“L9”o

函數(shù)參數(shù)

怎么樣，對于VLOOKUP函數(shù)你是

VLOOKUP

Lookup_valueD3否有了確定的相識？假如還不會，

Table_array唐錚A

請多做幾遍，慢慢去體會。

Col_index_num3

Range_lookupFALSE

搜索表區(qū)域首列滿足條件,確定待檢索單元格在區(qū)域行序號，再進一步返

回選定單元格的值。默認，表是以升序排序的

Range_lookup指定在查找時是要求精確匹配J還是大致匹配。如果為FALSE,大

致匹配。如果為TRUE或忽略，精確匹配

計算結(jié)果:1.9

有關(guān)該函數(shù)的幫助(H)[確定]|取消J

★復制公式，看一看有什么不同？？？

★同樣道理在H3單元格中建立查找飲料名稱“售價”的公式。

(5)計算銷售額、毛利潤

STEP2一分類匯總

首先，建立表“銷售記錄''的二個副本“銷售記錄(2)”、“銷售記錄(3)”和“銷

售記錄⑷”O(jiān)

(1)依據(jù)“所在區(qū)”進行分類匯總

★在表“銷售記錄(2)”中按“所在區(qū)”對銷售額和毛利潤進行分類匯總，匯

總結(jié)果

顯示在

數(shù)據(jù)下

★

方(結(jié)果

見“(樣

★

例)飲料

銷售.X1S”)O

★將銷售記錄⑵改名為“所在區(qū)匯總“

★結(jié)果參見樣例中的工作表——“所在區(qū)匯總“

(2)依據(jù)“飲料名稱”進行分類匯總

★在表“銷售記錄(3)”中按“飲料名稱”對銷售額和毛利潤進行分類匯總(匯

總方式為求和)

★對匯總結(jié)果中的毛利潤列按降序排序，找出毛利潤最大的飲料。

★將銷售記錄⑶改名為“飲料名稱匯總”

★結(jié)果參見樣例中的工作表——“飲料名稱匯總”

★

★

(3)用“嵌套分類匯總”統(tǒng)計各個區(qū)和各飲料店的飲料“銷售額”和“毛利潤”

★在“銷售記錄(4)”工作表中對“主要關(guān)鍵字”選擇“所在區(qū)”，“次要關(guān)

鍵字”選擇“飲料店”排序。

★進行第一次“分類匯總”(分類字段為“所在區(qū)”)。

★進行其次次“分類匯總”(分類字段為“飲料店”)。

★將銷售記錄⑷改名為“嵌套匯總”

★結(jié)果參見樣例中的工作表——“嵌套匯總”

★

★

STEP3—利用數(shù)據(jù)透視表實現(xiàn)統(tǒng)計分析

設計目標

如下圖所示，利用數(shù)據(jù)透視表生成三維數(shù)據(jù)統(tǒng)計報表。

求和項:銷售額所在區(qū)2數(shù)據(jù)透視表字段列表Hx

寶安福田龍南羅湖南山總計

百事可樂5324624243867442548將項目拖至數(shù)據(jù)透視表

XI

期

百事可樂（1L）542.5850785737.5502.53417.5日

菠蘿啤256508.8460.8360257.61843.2

在

所

非?？蓸?403062284062961676

槌

芬達6424204805383202400飲

料

光明純牛奶503.2647.7678.3156.4683.42669飲

光明酸奶476.9376.2547.2744.8317.32462.4

數(shù)量

果粒橙87314766845289784539

單

紅茶693636.3852.6684.6703.53570位

進

紅牛1125.61558.2760.21230.62448.67123.2價

售

匯源果汁1215.21989.41783.61087.81773.87849.8價

健力寶644.6184.8594534.61108.83066.8

銷

售

可口可樂556.5663.6504730.8420

毛

可口可樂（1L）790.4434.26372056.6686.44604.6利

樂百氏純凈水230.1101.4322.4425.1243.11322.1

樂百氏果奶525.3537.2397.8180.2443.72084.2

脈動832530.4975642.2777.43757

美年達440387.2664.4393.8565.42450.81添加到1行區(qū)域5

蒙牛純牛郎3044004283144581904

求和項:銷售額

所在區(qū)M1、

飲料名稱寶安福田龍岡羅湖南山總計

匯源果汁1215.21989.41783.61087.81773.87849.8

葡萄汁108615361830117615667194

蘋果汁1008.81918.81315.61877.21055.67176

1125.61558.2760.21230.62448.67123.2

利用排序，790.4434.26372056.6686.44604.6

87314766845289784539

96058211199577204338

由山睡田碗

356.5671.62509.3552227.74317.1

七喜卜508.2580.814087599024158

\832530.4975642.2777.43757

N1370.6481.8763.4400.4710.63726.8

IIII_(693636.3852.6684.6703.53570

七喜（IL）L813.81258.4444.6657.8361.43536

Y/

醒目96069。6407554053450

百事可樂（1L）542.5850785737.5502.53417.5

旭日升冰紅茶774.4737294.8673.2662.23141.6

健力寶644.6184.8594534.61108.83066.8

鮮橙多5555527683098493033

伊利純牛奶530.1533.9915.8638.4345.82964

(1)為“銷售記錄”表創(chuàng)建數(shù)據(jù)透視表

要求:

為表“銷售記錄”建立數(shù)據(jù)透視表，將數(shù)據(jù)透視表顯示在新工作表中，并將數(shù)

據(jù)透視表命名為“銷售統(tǒng)計”

行顯示“飲料名稱”，列顯示“所在區(qū)”，“銷售額”顯示在數(shù)據(jù)區(qū)

結(jié)果參見樣例中的“銷售統(tǒng)計”工作表。

I數(shù)據(jù)■■數(shù)據(jù)透視表和數(shù)據(jù)透視圖

數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?-布局岡

將右邊的字段按鈕拖到左邊的圖上

g即可構(gòu)造數(shù)據(jù)透視表.

所在區(qū)|列?'

I頁9I

歐料名稱

行?數(shù)據(jù)@)

[幫助?][確定][取消]

(2)依據(jù)數(shù)據(jù)透視表找出銷售額最大的飲料

★對毛利潤按降序排序，找出銷售額最大的三種飲料。

★結(jié)果參見樣例中的“銷售統(tǒng)計”工作表

(3)在“銷售統(tǒng)計”工作表中，找出各個區(qū)“銷售額”最大的飲料

★在“銷售統(tǒng)計”工作表中，用MAX函數(shù)找出每個區(qū)“銷售額”最大的飲料

的“銷售額

★在“銷售統(tǒng)計”工作表中，用VLOOKUP函數(shù)找出各區(qū)“最大銷售額”所

對應的“飲料名稱

STEP4一用“兩軸線-柱圖”比較“銷售額”和“毛利潤”

設計目標

現(xiàn)在，小李想用“兩軸線-柱圖''比較"南山區(qū)”、“福田區(qū)”和“羅湖區(qū)”的銷

售額和毛利潤之間的關(guān)系。

如下圖所示，用兩軸線柱圖表來比較銷售額和毛利潤:

銷售頷與毛利潤關(guān)系圖

￥28,000,00)￥6.200.00)

￥27,500.00)￥6.000.00)

￥27,000.00)

￥5,800.00)

桶￥26,500,00)

￥5,600.00)急口銷售額

懣￥26,000,00)沖?毛利潤

￥5.400.00)

￥25,500.00)

￥25,000.00)￥5,200.00)

￥24,500.00)￥5,000,00)

福田匯總羅湖匯總南山匯總

飲料店

（1）制作“兩軸線圖”

★在表“所在區(qū)匯總”分別選擇“所在區(qū)”、“銷售額”和“毛利潤”三列（如

下圖）所示；

銷售記錄表

日期所在區(qū)飲料店飲料名稱數(shù)量單位進價售價銷售頻毛利海

25488.005522.20

27629.605987.50

2554L90I5479.20

27632.106054.90

總計II136232.5029586.70

★單擊“圖表向?qū)А卑粹o，打開“圖表向?qū)?4-圖表類型”對話框，選擇“自定義

類型”選項卡，選擇“兩軸線-柱圖”。

圖表向?qū)?4步驟之1-圖表類型

［取消|「一步量）＞］［完成（￡）］

★單擊“下一步”，打開“圖表向?qū)?4-圖表源數(shù)據(jù)”對話框;

圖表向?qū)?4步束定2-圖表源數(shù)據(jù)陽區(qū)）

數(shù)據(jù)區(qū)域系列

數(shù)據(jù)區(qū)域也）：福刎;i工i

系列產(chǎn)生在。行6）

◎列

［取消Ik上一步⑥］［下一步OP＞］［完成更）］

★單擊“下一步”，打開“圖表向?qū)?4-圖表選項”對話框，按下圖進行設置;

圖表向?qū)?4步翼之3-圖表選項

標題坐標軸網(wǎng)格線圖例數(shù)據(jù)標志數(shù)據(jù)表

圖表標題五萬

“牝W4稗W皋m

銷售額與利潤關(guān)系m

他

分類00軸C）：28000JOO

斷在區(qū)27SDOJOO既nmo

270008梵m

察m

數(shù)值（Y）軸9：-26600JOO

在m

銷售額廠26000JDD黑

次分類（X）軸Q0：25900JD0皿JDmO

25000JOO通m

24500JDD

次數(shù)值W）軸9：JOO

毛利潤

|取消][＜上一步|下一步國）＞|[完成9]

★單擊“下一步”，在“圖表位置”對話框中，選擇“作為其中的對象插入”，單

擊“完成”按鈕。

圖表向?qū)?4步驟之4-圖表位置?區(qū)

將圖表：

叵O作為新工作表插入（§）：

Chartl

②作為其中的對象插入地）：施在區(qū)匯/

|取消|[＜上一步?）]|完成電）|

（2）美化圖表

★參照“飲料銷售（樣例）.xls”，完成對圖表格式的設置（格式可以自定義）。

STEP5一利用數(shù)據(jù)有效性制作更便利、更好用的“新銷售記錄”工作表

設計目標

在“飲料銷售.xls”中制作一張“新銷售記錄表”，并應用數(shù)據(jù)有效性設置，使得

在填寫了銷售“數(shù)量”和選取了“飲料名稱”后，可以自動計算出“銷售額”、“毛

利潤”和“毛利率”。

日期1星期1飲料名稱單￡售價數(shù)量糖售頡進價毛利潤毛利率|

雷碧A￥2.002￥4.00￥1.60￥0.8020.00%

?5

4

七喜(1L)

匯源果汁1

美年達1

V2

(1)制作“銷售記錄”表副本

★在“飲料銷售.Xis”中建立一個“銷售記錄”表的副本，并將其重命名為“新銷

售記錄”，然后將其前五列的內(nèi)容刪除(只保留標題行)，如下圖所示。

銷售記錄表

銷售頡毛利#潤A

N/#N/A#A

N/

#N/A#A

N/#N/AN/

N/#N/A

N/#N/AN#/A

N/#N/A#N/A

#N/AN#/A

#N/AN#/A

#N/AN/

N#/A

#N/AN#/A

#N/AN#/A

#N/AN#/A

#N/AN#/A

N/

#WA#N/A#A

#N/AN/

#AN#/A

N#/A#N/AN#/A

N#/A#N/AN#/A

N#/A#N/AN#/A

N/

#A#N/AN#/A

N/N/

N#/A#N/A#A

#N/AN#/A

N#/AN/

N#/A#N/AN#/A