EXCEL实训课件2-饮料店销售数据分析

实训课件二饮料店销售数据分析EXCEL课前准备

1.在I）盘新建个人文件夹学号最后2位+姓名，如“10张三”；

2.打开正，进入计算机基础教学网jsjjc.szpt.edu.cn作也提交、资料下载【登录】;将操作素材“EXCEL实训课件2”下我到D盘的个人文件夹中，并解压缩;危实皿目标利用“饮料销传（素材）.xls”学会用图表、分类汇总、数据透视表对饮料销售数据进行分析处理，结果参见“饮料销碍（样例）.X1S”o任务一在《销售记录》工作表里添加饮料的相关数据（如图1所示）寸实现目标:在任务一中，我们主要完成以下任务:

1.查找函数VLOOKUP的使用

六、花有方便的办法吗？一为表飞肖售记苴创建数据透视我任务三:用“数据透视表”分析各区每种饮料销售情况进行“分类汇总”（如图16所示）―实现目标在任务三中，我们主要完成以下任务:

1.用“数据透视表“进行“分类汇总〃A4I C J V I bI f；Ji XJ Xr，・库和触布W木法正R，」1供肝本▼百生赠m访3相南山

1.5i41m T:1以％

11.福T C1C1R4•八A ircc:c-々I廿o可口可乐（口\F5t911叱吱可漫■氏一冰红藻・11CtsW

5227.7431T.19rr2c114〜7774耳王誓〃Q*•••TF7迎Q杂一八S

32.d

2.工作表的重命名图16请参见“饮料销售（样例）.xls”中的“销售统计〃工作表，并参阅P192〜P196,I_L邺苹果什商以下操作\\711ilW1riA♦C，r scli»«IBSft.91Z9，，

1.用“数据透视表”进行“分类汇总”上面的问题，如果用“数据透视表”则更方便“数据透视表”订以很方便地对数据进行汇总和比较为表“销倬记录”建立数据透视表，并将数据透视表显示在新工作表中，并将数据透视表命名为“销倬统计二数据透视表的布局要求1）•行显示“饮料名称”，列显示“所在区”，“销售额”显示在数据区I操作方法

（1）选中“^宵记录”工作表的任一单元格.选择“数据”-“数据透视表和数据透视图“；莪据透视表和数据透视图向导一步熟之1111I-J［〈上一步道）］匚就叫」I彳布局便、）...］选项口取销

（4）单击“布局”按钮，打开“数据透视表和数据透视表视图向『布局”文寸话框，请指瑾竺匝驾裂悠歪裨关竺，.@LMicrc»£oft OfficeExcel彼抿刿蓑巍数提库箪将“饮料名称”拖到左边图形的“行”

⑥致据透视表和数据趋视图向导一步馨之|T|IE［眼消I.下一步国）＞完成制・选择销传数据所在区域;

（3）单击“下一步”•打开“数据透视表和数据透视表视图向导之3”；数据透视表显示位直:新建工作表如O现有工作表重）*1I单击“完成”按钮创连数拷透视表，5单击“确定”，数据透视表显示位置“新建工作表”6单击“完成”，将数据透视表命名为“销竹统计”2•对销售额按降序排序，找出销售额最大的饮料提示选中“总计”列的数值部份不包含最后一行数值，然后进行排序.如图17所示B C在区E P京利喧奥园34伙料名祢宝安*拙龙PI m1附T汇醐汁I21B.21龄.4JTB.

61087.

81773.

87849.£黄蜀汁IC8615361830117615«7停苹果汁

1008.

81918.

81315.

61877.2105S.6717T虹11261跄

2763.

21230.

62448.fi7123二可口可乐

90.

4.4M

2637.

2056.

6686.446C

4.910果辉_87314766845西978_453S96050231199E711醐多=720二饷12，相茱

671.

62600.3位22T.

74317.1641513七喜50Z皈.81408759902二14脱

832530.

4975642.

2777.437516王挫13711648】旦

763.

4400.

410.63源i云

636.

3852.66M.

603.535717七如

813.812S

8.

4444.66冶

361.4S53e图17问题怎样修改数据透视表的字段？如果不想求和，如何改变汇总方式

2.在“销件统计”工作表中创建一个如图18所示的表格，然后将“所在区”行引用到J4sMoK M■T*T3招用龙岗罗湖南山匡玄15昂工绡筐额图]8提示同一工作表的单元格引用

3.用MAX函数找出各区额”最大的饮料“销传额、

七、用两轴线柱图表来比较“销售额”和“毛利润”任务四利用图表向导创建“南山区”、“福田区”和“罗湖区的和“销售额”“毛利润”的图表（如图20所示）,实现目标:在任务四中,我们主要完成以下任务”等忆录*h曲好丫瓦¥25J

11522.小¥B

7.»0・1¥F,»T,5¥耻，“期|子”:0hi上士心L rrnm.sY27JCD.00«yPDOf!.M“¥6ecntin当V2B^D00湎-500¥25pQ10amsiuoa制/I XWMui4-0的立区请完成以下操作:在表“销传记荥（2广分别选择所需的“所任区”.“销售额”和“毛利润”（如图21所示）O

1.WArtr*dfi«舫刍记录表值单位改二竭看毛利安田囱湖H r.一.30¥与

522.2山一¥9£7一T.76麻宝x-I,934-¥

542.9福龙1总¥

25.

517.90¥6,

479.2罗川1267电总¥

27.

632.10厂线D54¥135,

1268232.50¥2^

585.7图21提示选择单元格的同时按住Ctrl摧，可对多个间断单元格进行选择

2.单击“图表向导”按钮，打开“图表向导4图表类型”对话框，选择“自定义类型”选项卡，如卜图所示选择“两轴线-柱图）图森向导・4■饕之1•图来类里TJ JI口定义美”LQ行架的伊图vififtrrn.v«rnfF日’百TdOCUIOX—7**i雷r-nI1I二Q王白埔国GM U.U.-V[■\/I jIWLW■黑白辆四ra.rco.3o___.黑白折铳田一时间到亲vr.r»r-i IHIU界自柱地图\rsxo.Do■..弓卜...oQ M牙合纨阳扇龈温:匕・Jir,r-r.11,.T I3jrJ洗白r目后暇型的射2图表.广技七步并分.，枝制在西个，阿欷:E，标衽，■■■r24,WLuU■MFfEiFrf.

3.单击“下一步”•打开“图表向导耳-图表源数据”对话框;U-SI〈山T1仞#丁〉I融xFT“冲,平访“Y群醇W印申并实取i,基软•申事理河“慕剽滓图，，32,“名一.」，，平审•）Q维I（脂一JL HI与叫邮好331Teal03313»:⑴联以）即擀0001VIccrxcBzI■cog OOOOtfg_L±弑弩■00阉.■J co（力聘茉专*:g I㈤麟亟蝎（6JU龄曲笠苦数也手上叫昌隹I I IIS39同拳0茎净骤笑翎存矜圈奔崇网啊烯区［T£Z婆基囹一擀垢卜翼祝,巢UN誓圆副斡“囱土举国十畚回举国,，丑n、屏一殳，平审十

①平一下特，〃邮i2EWU8$*鲤晶奠E:向列田醐“人U0U0nnnrzqjiOOOCCSA Q0U-C4AOOOCSSCZ心门00a SOXGGooces.i山.人加s廿的，00OOOCOz7ADC6ClOOOC09JI1*nnnr^jOOOC/91QOOCCSZAIK弓近尝孵习亲腰按照“饮料销仍（样例）・X1S”，完成对图表区域的设置图表区域的填充效果用“麦浪滚滚”绘图区格式的填充效果用“茵茵绿原”图例格式的填充效果用纹理“纸莎草纸”自我测试通过本案例的学习，你能回答如下问题吗？

1.VLOOKUP函数是干什么的？

2.如何定义数据区域，如何删除数据区域？

3.VLOOKUP函数中第二个参数的含义是什么？在定义第二个参数时要注意什么？

4.当沿着列复制VLOOKUP函数时应注意什么？

5.如何让VLOOKUP函数在没仃查找到所查找的内容时不返回错误值“荆WA”，而显示“没找到”呢？

6.什么是分类汇总？它有什么作用？

7.在分类汇总之前要注意什么？

8.利用“图表向导”创建图表共有几步？如何修改数据源？如何美化图表？

9.在选择数据区域时，要选择不连续的数据区域，应该如何操作？（答按住Ctrl键，然后用鼠标拖动来选择）常见问题及处理方法卜而把大家在本案例的学习过程中容易遇到的•些问题及处理方法列于下表常见问题可能原因处理方法将其删除，重新定义，方法选持“数据区域”名称无法重该“数据区域”名称己被定义“插入”-“名称新定义“定义〃，打开〃定义名称〃对话框，选择己定义的名称，单击“删除”命令（）1没有把要查找的对象定义在“数重新定义“数据区域”，把要查找的内VLOOKUP函数返回据区域”的第一列容定义在“数据区域”的第一列错误值

（2）查找的内容在定义的“数据区用ISERROR函数使错误值不显示O域”中不存在使用“填充柄”沿列拖动复要查找的对象（VLOOKUP函数的第在使用“填充柄”沿列拖动前先将第•制公式时出现错误值个参数的列标绝对引用一个参数）列标没实用绝对引用H#N/A\（）1在“分类汇总”之前没先按要先按要分类的字段排序，然后再进行“分类”的字段排序“分类汇总”分类汇总结果不正确（）2虽然己先按要“分类”的字段在“分类汇总”时，“分类〃字段选择排序.但在“分类汇总”己排序的字段时，分类字段选持不正确（没有选择己排序的字段）

1、打开正，进入

2、.登录，上传本次课的Excel文档______I工单位店店占—2022店店占ft统蛆茶打牛TO lira茂90收

69.00r

327.Y3SJ00w雨店杏唐店税lelWr吨4c2¥

25.60¥

78.CO南占店店右非冷可任有事W59»523BR Is〈gx5c24,W2022M2022闵《店宙店乐C I,好哈哈N6脂4«忻1成盒122¥¥¥¥¥

15.0o通¥

3.am4AG八CC南店占店丽果扔菊荀汁唯62ora豆ri

47.50L¥

29.502022南丽丽、、*王害声〃宝叶S1C L41留,si2022肉27〈〈ZCOZ闵Fnfn足丽净水n汞六h果■9〈2C2K V

516.00〜

29.00电怀明丽被怪可口可任凋8C6C¥

105.60JX¥2,002C0727施I1M闵朋丽丽祈1L结一幺豆拉仲Y

37.40¥

3.50SC22022南丽丽丽丽姓哈哈幺屯泠水30ls CNFY

74.40-fb Y

18.CO i•南G£L乐百氏珥冷水1D12022ST丽In匹西・112C5CJ.20*加〈广,.1E西西E西七直Z172022南西西西西七m LL可口可・1C%I Vq.811南2022■Fr供醉目人一库、¥

13.50山山山山山2022算山山山FggguT土日R卅泳灯苏2022L小南闵rlrnr」+白氏果奶83ten90g¥

16.802022南南南T

2.

2.

2.f9¥

24.go2C07南与尸23际2022一一一口刎¥

38.00l«l»r2022一12到¥

13.80二・I1YS.CO LfT OHOO247CH7L1公¥

39.5o二一二一96Uta1111“；§»KD•L0,

2.01D-7•oo•//11rz•6Ds.722560402OooDV4Y¥¥V¥Y3DX23167ISS96制作过程

一、VLOOKUP函数

1.VLOOKUP函数的功能查找数据区域首列满足条件的元素，并返回数据区域当前行中指定列处的值

2.VLOOKUP的语法VLOOKUP（lookup_value.table,anav彳

③返回区域

④精确查找还第几列的值？是含糊查找？

①要查找的内容I

②到哪个区域的第一列查找？注意要查找的对象（参数

①）一定要定义在查找数据区域（参数

②）的第一列卜面来看看VLOOKUP函数是怎么用的

二、如何查找“单位”先在“饮料价格”工作表中创建一个“价格”区域名称，后而就可以在VLOOKUP的table_airay参数中使用这个区域名称，由于这个数据区域的第2列存放了“单位”数据•因此可以在这个数据区域中通过查找“饮料名称”得到对应的“单位”数据实现步骤如R（参考教竹？典，二坦§7）1-创建“价格”…区域名称

（1）打开“饮料销售（素材）.xls”,选择“饮料价格”工作表.选中饮料名称、单位、进价、售价所在的区域（参见图2）o提示与Shift键配金，可实现对多个不间断单元格的选择代料件格表—可口可乐日〜

1.

702.1L1幺百事可乐L

602.J可口可乐知b□

2.

102.60百享可乐淅

642.

002.

50751.

702.2086寺碧（1L）7®

2.

002.50七尊昕

91.

702.20108七事（1L）

2.

002.60_g汇源果汁.合

7./9•金口听

1.

702.20—

1.

602.0C—

2.

002.50问题区域的定义可以包含“序号”列图2答案不行原因被查找的关键数据（饮料名称）必须罗列在被定义区域的首列否则，将无法正确实现查找

（2）选择菜单“插入一位称一定义”命令（见图3），打开“定义名称”对话框，在名称框中输入“价格”后（见图4）,单击“确定”按钮，“价格”区域名称创建完成（试试看，也可以在名称框中直接输入定义的名称）插入（X）|格式立工具

①数据如1工作表呢A函数（D...I名祢

⑤A定义如..囹片（！）A1粘贝占口…指定…图4问题如果名称定义错误了，如何将其删除也一挈I瘀式

（1）打开“箱侣记录”工作表选中目标单元格F3,选择“插入-*函数fVLOOKUP”,单击“确定”，弹出VL00KUP函数的“函数参数”对话框

（2）由于要根据饮料的名称查找“单.位〃，所以VLOOKUP函数的第一个参数应该选择饮料名称“D3”（见图5%

（3）单击“函数参数”对话框的第二个文本框右边的折叠按钮，接着选择菜单“插入一位称-粘贴”命令（见图6）,打开“粘贴名称”对话框.选中名称“价格〃（见图7）.单击“确定”，区域名称“价格”数的第二个参数的位置插被拒I入到公式中，VLOOKUP函U IBSSS入

①I格式9工具D数据

①窗口鉴［行每特殊符号⑻.…定义也…饮料名称数量粘贴E…指定©…应用A—标签707QD...图6图7—统一奶茶•行士用这种方法在公式中输入区域名称，即快捷又不容易出错4“函数参数”对话框中的第三个参数是决定VLOOKUP函数找到匹配饮料名称所在行以后，该行的哪列数据被返回，由于“单位”数据存放在第二列，所以在这里通过键盘输入数字“2”5由于要求饮料名称精确匹配，所以最后一个参数输入“FALSER见图8,单击“确定”，可以看到函数准确地返回了“统一奶茶”的“单位”是“瓶定待•检素单元格左区城甲的行序号，再进一方返回逢定单元格的•ange lookup指定在叠他区娱求褶跳匕出还是大致倪c如果为FALSL大致匹旦,如果为TRUE或怒区椿嫌工配计耸结果二瓶有关该函教的号助H束消

（6）夏制公式（注意在复制公式时，如果沿着列拖动时.列标要用绝对引用，想想看为什么？;复制公式时可以通过使用填充柄（填充柄位于选定区域右下角的小黑方块将用鼠标指向填充柄时，鼠标的指针更改为黑十字摄1将公式复制到相邻的单元格中选择包含公式的单元格，再拖动境充柄，使之覆盖要填充的区域按照与上面查找“单位”相同的方法，自己完成卜面的内容

三、创建“进价”和“售价〃的查找公式L在G3单元格中建立查找饮料名称“进价”的公式

2.在H3单元格中建立查找饮料名称“传价”的公式

四、用公式计算“销售额”和“毛利润”2列的值其中销哲额二侈价X数量毛利润二（齿价-进价）X数量设置结果将工作表中的“进价”、“售价”、“销侔额”和“毛利润”4列的数据加符号，小数位数设置为“2气各列设置为最适合的列宽

五、数据输出一对“销售额”和“毛利润”进行分类汇总任务二利用分类汇总统计“销售额”和“毛利润〃b实现目标在任务二中，我们主要完成以下任务:

1.用“分类汇总”计算各个区的饮料“销售额和“毛利润”（图9XB C|D EF|G H I1123A jrTft+削E字安+〃50T棚山汇总龙岗汇总+16T毛利润±W罗湖汇金¥25,

488.CO vfi,522,20¥27,

629.南山1S¥5,

987.50¥29,

984.炎¥6,

542.¥25,

547.190¥27632,10¥5,卯

3.20¥

136.

232.50¥29,

586.

701.23A BC DE IF GHI钠售记录表]2所在区快料店被料名葡萄汁K红牛¥七

194.00¥1,

798.scJL总汇源果汁汇¥7,

123.20¥1,t

6.OCY

7849.+〃9T总革果汁it总七f80Y1,

632.OC¥

7.

176.00+126昌汇邑统一冰¥1,

212.00+商统茶总果U橙Y4,

158.00寸…，I汇乏可口且孙fas¥4,

317.10¥4,¥

938.50±L））0-6群情多

539.00¥

1604.¥

907.如汇总T5060Y33F.Ti¥$

5.5CT8T¥Sf

757.00¥86T.60212¥S

57.OC图10请参见P188-P192,完成对“销售”和”毛IJ润”进行分类汇总的操作什么是分类汇总现在要统计出各个区饮料的销估额和毛利润，如何来做呢？这个问题可以用“分类汇总”来解决“分类汇总”含有两个意思，既按什么分类（各个区）和对什么汇总（销传额和毛利润）注意进行“分类汇总”之前，要先对分类的列（所在区）进行排序（想想为什么？），然后再对要汇总的列（销售额和毛利润）进行总和问题汇总时，不想计算总和，能不能计算平均值？图12孑按照与上面介绍的“分类汇总”的方法，自己完成下面的内容

1.建立表“销售记录”的三个副本“销售记录

（2）”、“销售记录⑶”和“销售记录

（4）”提示右单击“销侔记录”工作表标签，在弹出的快捷菜单中选择“挪移或者复制工作表”并选中“建立副本”复选框（见图13）o提示更简单的操作方法是按住CtrHit,再用鼠标拖动要复制的工作表图

132.在表“销碍记录

（2）”中用分类汇总统计出各个区的“销倡额”和“毛利润”，汇总结果显示在数据下方（结果见图14）提示先对“所在区”进行排序（排序的作用是什么？），然后选择“数据”一“分类汇总）汇总后2级汇总结果如卜所示n隐藏明细数据行听番记亲表or单位1^25,

488.00¥5,

522.2汇汇汇¥27,

629.60¥5,

987.50M岗湖山计福¥29,9灵.90¥25,¥6,

542.90北罗南5d

7.ȴ27,

632.¥5,2010¥

136.

232.50¥6,

059.90¥29,

565.70图14方法1）打开“销售记录⑵”工作表2）对“所在区”进行排序（单击数据清单“所在区”列中任一单元格,在工具栏中点击“升序排序”按钮）3）在菜单栏中选择“数据”-“分类汇总”命令，打开“分类汇总〃对话框，（见图12）4）单击“确定”按钮5）点击数据消单左上角的显示符号“2”，订看到分级的效果（见图14）

3.在表“销售记录⑶”中用分类汇总统计各种饮料的“销传额”和“毛利润”，并对毛利润按降序排序，找出毛利润最大的饮料-（注意；汇总前是否也要先排序？按什么排序？）A4。