Excel数据分析与应用-店铺销售情况统计与分析

店铺销售情况统计与分析目

录项目概述任务2.1对销售情况进行排序任务2.2分类汇总各区域销售信息任务任务2.3筛选特定店铺的销售情况任务2.4使用数据透视表快速分析销售情况任务2.5各店铺销售数据统计与分析任务2.6条件格式设置任务2.7绘制图表拓展延伸：常见图表类型及适用场景课后技能训练

项目概述项目背景

“和美家”连锁超市总部收集了各个分店的销售情况，现在想对这些分店的销售情况进行统计与对比分析，以便推广优秀经验，提升店铺整体销售水平。对“和美家”连锁超市各个分店的销售情况统计与分析，反映店铺总体销售情况，完成后效果如图所示。项目演示思维导图思政聚焦：两个大数据起源的故事故事一：啤酒与尿布故事二：看不见的弹痕最致命思政聚焦：两个大数据起源的故事正如维克托·迈尔－舍恩伯格在《大数据时代：生活、工作与思维的大变革》一书中指出，大数据时代最大的转变就是思维方式的转变：从抽样、精确、因果关系转变为全样本、效率、相关关系。大数据，不仅是一次技术革命，同时也是一次思维革命。人类解决问题的思维方式，正在朝着“以数据为中心”的方式迈进，大数据从业者必须具有敢于质疑、大胆推理的创新精神、及时转化思维模式，以及踏实肯干、认真细致的工匠精神，具有技术技术服务人类的服务精神。思考与讨论：你还知道关于数据思维的其他故事吗？你知道大数据的发展历程吗？你知道大数据技术和物联网、云计算、人工智能之间的关系吗？任务2.1对销售情况进行排序实施步骤：1.选择操作对象。在“排序1”工作表中，单击选择店铺编号列的任意单元格。2.排序。在【数据】选项卡的【排序和筛选】命令组中，单击命令

。2.1.1按照店铺编号升序排序按照店铺编号升序排序的表格将各个区域的销售额进行降序排列，有两个排序关键字分别是区域和第一季度销售额，在按照第一关键字升序（或降序）排序的基础上，再按照第一季度销售额进行降序排序。实施步骤：1.选择操作对象。单击选择区域列的任意非空白单元格。2.排序。在【数据】选项卡的【排序和筛选】命令组中，单击【排序】命令，弹出【排序】对话框，进行设置。2.1.2按各个区域第一季度销售额降序排列排序对话框设置完成后效果如图所示。2.1.2按各个区域第一季度销售额降序排列按照“升序”及“第一季度销售额”降序排序可能需要按照特定的序列进行排序，例如按照城市大小“广州、深圳、佛山、中山、珠海”的顺序进行排序，这就用到了自定义序列排序。实施步骤：1.选择操作对象。单击选择区域列的任意非空白单元格。2.排序。在【数据】选项卡的【排序和筛选】命令组中，单击【排序】命令，弹出【排序】对话框。在【排序】对话框的【主要关键字】栏中选择“自定义序列”选项，弹出【自定义序列】对话框，输入序列,点击【添加】。2.1.3按自定义序列排序输入自定义排序回到【排序】对话框，选择刚才创建的序列,效果如图所示。2.1.3按自定义序列排序选择自定义排序完成后效果如下图所示。自定义序列排序结果任务2.2分类汇总各区域销售信息分类汇总顾名思义就是先使用某个关键字进行分类，然后在分类后的数据范围内进行汇总。实施步骤：1.根据区域升序排序。2.分类汇总各区域店铺数量。在【数据】选项卡的【分级显示】命令组中，单击【分类汇总】命令，弹出【分类汇总】对话框。2.2.1汇总各区域的店铺个数分类汇总对话框完成分类汇总，效果如图所示。2.2.1汇总各区域的店铺个数分类汇总结果求各区域每个季度的销售额平均值，首先按照区域进行排序，再求各区域所有店铺的每个季度销售额的平均值。实施步骤：1.按照区域排序。2.使用分类汇总求各区域每个季度销售额的平均值。2.2.2汇总各区域每个季度的销售额平均值分类汇总设置完成分类汇总，单击查看，2级数据效果如图所示。2.2.2汇总各区域每个季度的销售额平均值各区域各季度销售额平均值任务2.3筛选特定店铺的销售情况实施步骤：1.打开【筛选】命令。在“筛选1”工作表中，选择任一非空单元格。在【数据】选项卡的【排序和筛选】命令组中，单击【筛选】命令。2.设置筛选条件。单击“区域”列的下拉三角符号，在下拉列表中，选择【中山】，如右图所示。完成后效果如图下所示。2.3.1查找所有中山店铺的销售信息筛选设置中山店铺销售情况实施步骤：1.打开【筛选】命令。2.筛选第一季度销售额大于等于90万的店铺。单击【第一季度销售额（万元）】列右侧的下拉三角，依次选择【数字筛选】、【大于或等于】，弹出【自定义自动筛选方式】对话框，在大于或等于后的文本框中输入90，如图下所示。2.3.2查找第一、四季度销售额都在90万以上的店铺自定义自动筛选设置3.筛选第四季度销售额大于等于90万的店铺。单击【第四季度销售额（万元）】列右侧的下拉三角，依次选择【数字筛选】、【大于或等于】，弹出【自定义自动筛选方式】对话框，在大于或等于后的文本框中输入90，单击【确定】,完成后效果如下图所示。2.3.2查找第一、四季度销售额都在90万以上的店铺第一、四季度销售额均大于等于90万的店铺方法一：先找出第二季度销售额在70万以下的店铺，再找出第三季度销售额在70万以下的店铺。方法二：使用高级筛选。高级筛选：1.适用于筛选条件比较复杂的环境，使用高级筛选时需要设置一个条件区域。2.在条件区域中，当多个条件之间是并且关系时，条件写在同一行。3.当多个条件之间是或者关系时，条件写在不同的行。4.条件区域与数据之间应至少留有一个空白行或者一个空白列。5.因为手动输入时容易多输空格，影响筛选结果，建议构建条件区域时列名从数据表复制。2.3.3

查找第二、三季度销售额都在70万以下的店铺实施步骤：1.构建筛选条件区域。将E2:F2单元格内容复制到I2:J2单元格，在I3和J3单元格内输入“<70”。2.使用高级筛选完成任务。单击数据区任一非空单元格，在【数据】选项卡的【排序和筛选】命令组中，单击【高级】命令，弹出【高级筛选】对话框。选择【在原有区域侠士筛选结果】，【列表区域】选择$A$2:$G$24，【条件区域】选择$I$2:$J$3，单击【确定】。2.3.3

查找第二、三季度销售额都在70万以下的店铺条件区域高级筛选设置2.3.3

查找第二、三季度销售额都在70万以下的店铺条件区域第二、三季度销售额均在70万以下的店铺销售信息完成后效果如下图所示。实施步骤：1.构建筛选条件区域。因两个筛选条件是或者关系，在I2到J4单元格构建筛选条件如右图所示。2.使用高级筛选完成任务。完成后效果如下图所示。2.3.4

查找第二或者第三季度销售额在70万以下的店铺条件区域第二或者第三季度销售额在70万以下的店铺销售情况任务2.4使用数据透视表快速分析销售情况数据透视表功能强大，综合了数据排序、筛选、分类汇总等常用数据分析方法的优点，可方便地调整分类汇总的方式，灵活地展示数据的特征。一张数据透视表仅靠鼠标移动字段位置，即可变换出各种类型的报表。

实施步骤：1.新建数据透视表。打开“数据透视表-数据”工作表，单击数据区域内任一单元格，在【插入】选项卡的【表格】命令组中，单击【数据透视表】命令，弹出【创建数据透视表】对话框，保持默认选项，在新工作表中创建数据透视表。2.4.1统计各区域各季度销售额总和创建数据透视表2.计算各区域各季度销售额总和。在【数据透视表字段】设置面板中，将【区域】拖动到【行】位置，将【第一季度销售额（万元）】……【第四季度销售额（万元）】拖动到【值】位置，如右图。完成后如下图。3.格式化数据。套表格样式，去掉筛选，设置行高。2.4.1统计各区域各季度销售额总和数据透视表字段设置各区域各季度销售额总和”数据透视表各区域各季度销售额总和实施步骤：1.新建数据透视表。打开“数据透视表-数据”工作表，单击数据区域内任一单元格，在新工作表中创建数据透视表。2.计算各区域第一季度销售额平均值。在【数据透视表字段】设置面板中，将【区域】拖动到【行】位置，将【第一季度销售额（万元）】拖动到【值】位置。单击【求和项：第一季度销售额（万元）】，选择【值字段设置】，弹出【值字段设置】对话框，在【值字段汇总方式】下选择【平均值】。2.4.2

统计各区域第一季度销售额平均值值字段设置完成后数据透视表字段设置如右图所示，得到的数据透视表和格式化后的表格如下图所示。2.4.2统计各区域第一季度销售额平均值值字段设置“各区域第一季度销售额平均值”数据透视表各区域第一季度销售额平均值任务2.5各店铺销售数据统计与分析1.什么是相对地址和绝对地址？绝对地址和相对地址有时又会被称作绝对引用和相对引用。相对引用：随着公式或函数的位置变化，所引用单元格位置也在变化的是相对引用（相对地址）；绝对引用：而随着公式或函数位置的变化所引用单元格位置不变化的就是绝对引用（绝对地址）。2.怎么样表达相对地址和绝对地址？直接写单元格的地址，如A1、B2，称为相对地址。如果在相对地址的行号与列号前加上$符号，如$A$1、$B$2，相对地址就转换成了绝对地址。与上述两种写法相应的，如果仅在行号或者列号前加上$符号，例如$A1、A$1，则为混合地址。3.相对地址和绝对地址怎样转换？在公式中，如果需要使用绝对地址，在行号与列号前手工输入$符号，也可以选中单元格名称，再按F4键，Excel表格就会在四种地址模式中循环变化。知识准备实施步骤：1.使用公式计算单个店铺全年总销售额。打开“函数”工作表，单击H3单元格，在编辑栏输入公式“=D3+E3+F3+G3”，如图所示。单击输入按钮或按【Enter】键，完成计算。2.5.1计算各店铺一年的总销售额罗湖总店全年总销售额计算公式罗湖总店全年总销售额2.计算其它店铺总销售额。单击H3单元格，鼠标放置在H3单元格右下角绿色实心方块上，鼠标变成十字型，此时按住鼠标左键向下拖动到H24单元格复制公式，完成其余店铺总销售额的计算。2.5.1计算各店铺一年的总销售额各店铺全年销售总额实施步骤：1.计算各季度所有店铺销售额的最高值。（1）计算第一季度销售额最大值。打开”函数”工作表，单击D26单元格，在【公式】选项卡的【函数库】命令组中，单击自动求和函数

下面的下拉三角，单击

，看到编辑栏和D26单元格中都有了公式“=MAX(D3:D25)”，将函数改为“=MAX(D3:D24)”，按【Enter】即可。2.5.2

计算各季度所有店铺销售额的最高值、最低值、平均值第一季度最高销售额计算公式（2）计算其他季度最高销售额。单击D26单元格，并利用拖动手柄向右拖动，计算其他季度销售额最高值。2.5.2计算各季度所有店铺销售额的最高值、最低值、平均值各季度所有店铺销售额最高值2.各季度所有店铺销售额的最低值。（1）计算第一季度各店铺销售额最小值。求解函数为“=MIN(D3:D24)”。（2）再使用拖动手柄快速计算其他季度销售额最小值。3.各季度所有店铺销售额的平均值（1）计算第一季度各店铺销售额平均值。求解函数为“=AVERAGE(D3:D24)”。（2）再使用拖动手柄快速计算其他季度销售额平均值。2.5.2

计算各季度所有店铺销售额的最高值、最低值、平均值各季度所有店铺销售额最高值、最低值、平均值求各店铺总销售额排名，就是求某店铺销售额的数字在所有店铺销售额数字中的排名，使用Rank函数。实施步骤：1.使用函数计算罗湖总店总销售额排名。（1）选择函数。打开”函数”工作表，单击I3单元格，在【公式】选项卡的【函数库】命令组中单击【插入函数】按钮，弹出【插入函数】对话框，如图所示，在【选择类别】下拉列表中选择【统计】，在下方【选择函数】列表框中选择【RANK.EQ】后单击【确定】按钮。弹出【函数参数】设置对话框。2.5.3各店铺总销售额排名各季度所有店铺销售额最高值、最低值、平均值（2）设置参数。在函数中不直接写数字，而是写引用地址。第一个参数Number指要被排序的数字H3；第二个参数Ref是所有参与排序的数字$H$3:$H$24（这里必须绝对地址，因为使用拖动手柄计算其他排名时，相对地址会变化，导致结果错误）；第三个参数Order填0，按照降序。2.5.3各店铺总销售额排名RANK.EQ函数参数设置（3）计算其它店铺总销售额排名。使用拖动手柄即可。2.5.3各店铺总销售额排名各店铺总销售额排名判断某家店铺业绩是否合格可以使用IF函数，如果总销售额大于等于200，则输出“是”合格，否则输出“否”。实施步骤：（1）选择函数。选择【常用函数】中的【IF】。（2）参数设置。第一个参数是判断条件，设置条件为H3>=200；第二个参数是满足条件时的显示，设置为“是”；第三个参数是不满足条件时的显示，设置为“否”。完成后编辑栏公式为=IF(H3>=200，"是"，"否")2.5.4判断各店铺的总销售额是否合格公式参数设置（3）判断其它店铺总销售额是否合格。使用拖动手柄即可。2.5.4判断各店铺的总销售额是否合格各店铺总销售额合格情况判断某家店铺总销售额等级，320万以上为优秀，260万到320万之间为良好，200万到260万之间为合格，200万以下为不合格，共四个等级。问题分析：判断某家店铺总销售额等级使用IF函数，从2.5.4中可以看到，在函数“=IF(H3>=200，"是"，"否")”中，一个判断H3>=200，可以有是、否两个结果，即一个条件可以得到结果的两个分支。本例中有四个等级，相当于有四个结果分支，需要使用三个IF函数嵌套。2.5.5划分各店销售额等级实施步骤：（1）输入函数。因为有嵌套，所以直接在K3单元格输入函数IF(H3<260，"合格"，IF(H3<320，"良好"，"优秀")))即可。（2）判断其它店铺总销售额等级。使用拖动手柄即可。2.5.5划分各店销售额等级各店铺总销售额等级COUNTA函数计算区域中非空单元格的个数，因为数据区域中没有空白，所以只要选中任意一列数据，COUNTA函数就能统计出店铺总数。实施步骤：1.选择函数。选择【统计】类中的【COUNTA】函数。2.统计店铺个数。单元格区域C3:C24中的内容为所有的店铺名称，选择C3到C24单元格作为value1的参数。2.5.6统计店铺总数参数设置完成后效果如图所示。2.5.6统计店铺总数店铺总数统计COUNTIF函数计算区域中满足给定条件的单元格个数。实施步骤：1.选择函数。选择【统计】类中的【COUNTIF】函数。2.统计一季度销售额上百万店铺个数。COUNTIF函数的第一个参数Range是参与统计的数据区域，第一季度的销售额为区域D3:D24，第二个参数Criteria是条件的意思，输入“>=100”。2.5.7统计第一季度销售额上百万店铺数参数设置完成后效果如图所示。2.5.7统计第一季度销售额上百万店铺数第一季度销售额超百万的店铺数统计任务2.6条件格式设置实施步骤：1.突出显示总销售额不合格店铺在”函数”工作表中，选中J3:J24单元格，在【开始】选项卡的【样式】命令组中单击命令

选择【突出显示单元格规则】选项，单击【等于】，弹出【等于】对话框，如图所示，在条件框中输入“否”，选择【浅红填充色深红色文本】，单击【确定】，完成后效果如右图。条件格式设置不合格店铺格式设置2.突出显示总销售额优秀等级店铺。在”函数”工作表中，选中K3:K24单元格，同上节操作，在【等于】对话框中的【为等于以下值的单元格设置格式】下的单元格中输入“优秀”，在【设置为】后的下拉列表中选择【绿填充深绿色文本】，单击【确定】，完成后效果如右图。条件格式设置优秀等级店铺格式设置3．突出显示总销售额前五店铺。在”函数”工作表中，选中I3:I24单元格，在【开始】选项卡的【样式】命令组中，单击【条件格式】命令，选择【突出显示单元格规则】选项，单击【小于】，弹出【小于】对话框，在【为小于以下值的单元格设置格式】下的单元格中输入“6”，在【设置为】后的下拉列表中选择【自定义格式】。条件格式设置总销售额前五设置-1弹出【设置单元格格式】对话框，如下图，在【字体】选项卡中，将【字形】设置为【加粗倾斜】，将【颜色】设置为红色，单击【确定】按钮，完成单元格格式设置。回到【小于】对话框中，单击【确定】，完成总销售额前五店铺格式设置。条件格式设置总销售额前五设置-24．以数据条形式显示所有店铺总销售额。在“函数”工作表中，选中H3:H24单元格，在【开始】选项卡的【样式】命令组中，单击【条件格式】命令，选择【数据条】选项，在【渐变填充】组中选择第一行第一列【蓝色数据条】，完成以数据条形式显示的总销售额。所有条件格式设置完成后效果如下图。条件格式设置完成店铺销售情况统计与分析任务2.7绘制图表2.7.1知识准备图表界面组成框1为图表区，框2为绘图区，框3为水平（类别）轴，框4为垂直（值）轴，框5为图例，6为数据系列实施步骤：1.选择数据。选择“数据透视表1”工作表A13:E18区域，如图示。2.绘制簇状柱形图。在【插入】选项卡的【图表】命令组中单击按钮

，弹出【插入图表】对话框，看到【推荐的图表】选项卡中，已经有推荐的簇状柱形图，如右图，单击【确定】按钮，绘制簇状柱形图。2.7.1绘制各季度各区域销售额总和对比柱形图【插入图表】对话框选择各季度各区域销售额数据绘制的簇状柱形图，效果如下图所示。3.添加并设置数据标签。单击图表中任意位置，单击右上角

，在【图表元素】快捷菜单中勾选【数据标签】前面的复选框，即可添加数据标签，如图所示，数据有重叠现象。2.7.1绘制各季度各区域销售额总和对比柱形图添加数据标签选择各季度各区域销售额数据效果如下图所示。2.7.1绘制各季度各区域销售额总和对比柱形图设置数据标签格式修改数据显示位数。单击图表中任意位置，单击右上角

，在【图表元素】快捷菜单中，单击【数据标签】后的向左小箭头，选择【更多选项】，弹出【设计数据标签格式】对话框，在【标签选项】选项卡中，单击【数字】，在【类别】下选择【数字】，然后把小数位数调整为“0”，如左图，完成后如下图。修改数据显示位数2.7.1绘制各季度各区域销售额总和对比柱形图4.修改图表标题。单击激活图表标题文本框，更改图表标题为“各季度各区域销售情况”，完成后效果如下图所示。各季度各区域销售情况实施步骤：（1）绘制饼图。选择2.4.2节得到的数据，在【插入】选项卡的【图标】命令组中单击按钮，弹出【插入图表】对话框，切换至【所有图表】选项卡，单击【饼图】选项，单击【确定】按钮，绘制饼图，如图所示。2.7.2绘制各区域第一季度销售额平均值饼图选择各季度各区域销售额数据（2）添加数据标签。调出【设置数据标签格式】窗格，在窗格中的【标签选项】

选项卡中的【标签包括】中勾选【百分比】、【显示引导线】复选框，去掉【值】复选框，在【标签位置】中勾选【数据标签外】复选框，如右图所示。（3）修改图例位置。单击选中“绘图区”，单击右上角

在【图表元素】快捷菜单中，选中【图例】，单击其右侧小三角，在弹出的快捷菜单中选择【设置图例格式】命令，弹出【设置图例格式】窗格，在【图例位置】栏中勾选【靠右】，如右图所示。2.7.2绘制各区域第一季度销售额平均值饼图设置数据标签格式设置图例格式2.7.2绘制各区域第一季度销售额平均值饼图完成后效果如下图所示。第一季度销售额平均值拓展延伸常见图表类型及适用场景1.柱形图--表示比较柱形图是一种以长方形的长度来表达数据的统计报告图，由一系列高度不等的纵向条纹表示数据分布的情况。柱形图表达简单直观，很容易根据柱形的长短看出值的大小，易于比较各组数据之间的差别，但当数据集过大时柱子会变得非常稠密，因此