商品销售情况统计

本案例采用数据透视表这一分析工具的强大功能，数据透视表有效的综合了数据排序、筛选、分类汇总、图表等多种数据分析方法的优势，能够灵活的采用多种手段展示，汇总，分析数据。还可以代替excel函数或公式轻松解决很多复杂的问题。

步骤：

1.  自网页导入商品品列表

1) 双击案例文本夹中的“品名表.htm"，在浏览其中打开该网页，复制地址栏中的网址。

2）打开“家电销售统计表.xls”,插入一个空白工作表，并重命名为“品名”

3）单击工作表”品名”中的A1单元格，定位光标。在“数据”选项卡上的“获取外部数据”组中，单击“自网站”按钮打开"新建web查询"窗口。，将步骤1）中的网址复制上去，然后点击“转到”按钮。在浏览窗口中单击“商品代码”左侧的黄色箭头，使其变为绿色选中标记，同时品名列表被选中。单击右下角的“导入“按钮，弹出”导入数据对话框“。单击“确定”，完成数据导入。

4）在“数据”选项卡上的“连接”组中，单击“连接”按钮，打开“工作簿连接”对话框。选中“连接”，单击右侧的“删除”按钮，在随后的弹出对话框中单击“确定”按钮，将导入的数据表与源数据的连接切断。

2. 通过条件格式查找重复项。数据列表中存在的重复项，需要找出并删除。

1）选择“品名”表中的A、B两列数据，在“开始”选项卡上的"样式"组中，单击“条件格式”按钮，从下拉的列表中选择“突出显示单元格规则”中的“重复值”命令，打开“重复值”对话框，设置重复值格式为“浅红色填充”。单击“确定”。

2）将颜色标出的重复数据排列在最上方。 仍然保持选中“品名”表中的A，B两列数据。在“数据”选项卡上的“排序和筛选”组中，单击“排序”按钮，打开“排序”对话框，单击选中右上角的复选框“数据包含标题”，表示标题行不参与排序。指定“商品名称”为主要关键字，排序依据为“单元格颜色”，次序为刚才标出的重复值使用的浅红色且选中“在顶端”。单击“添加条件”按钮，增加一行排序条件，分别指定“商品代码”为次要关键字且按数值升序排列。单击"确定"完成排序。

3）删除重复项。在“商品名称"列中任意单元格单击鼠标定位，在“数据”选项卡的“数据工具”组中，单击“删除重复项”按钮，打开“删除重复项”对话框，勾选“数据包含标题”，然后单击“确定”。弹出提示删除的对话框，继续点击"确定".

删除完成后，若发现还有个别重复的浅红色填充项，可手动删除。直到表格中不再有浅红色填充项。

4）商品名称中西文下划线"_"左边的文本代表了商品的品牌，我们可以依照"_"对其分列。首先，将单元格B2的标题改为"品牌_商品名称”，然后选择数据区域B1：B151，在“数据”选项卡上的“数据工具”组中，单击“分列”按钮，进入文本分列向导第1步 .在指定原始数据的文件类型为“分隔符号”，点击“”下一步”按钮。进入文本分列向导第2步，单击选中“其他”复选框，在其右侧文本框中输入西文下划线"_",单击“下一步”。进入文本分列向导第3步。单击‘完成’按钮，指定的列数据被分到相邻列中。

5）调整工作表的格式。 选择B列数据，在“开始”选项卡上的“样式”组中单击“条件格式”按钮，从下拉列表中选择“清除规则”-->"清除所选单元格规则",从而删除之前设置的重复项浅红色填充。同时选中A、B、C三列，用鼠标双击其中一列列标的右边线，自动调整到合适列宽。

6）选择除标题行外的A2：C151数据区域，从“数据”选项卡上的“排序和筛选”组中单击“自定义排序”按钮，将数据列表按“商品代码”升序排列。

7）导入的外部数据列表excel自动定义名称为sheet001，且其应用范围只在当前工作表中，不适合后续的引用，需要将其删除。在“公式”选项卡上的“定义的名称”组中，单击“名称管理器”按钮，打开“名称管理器”对话框。

在名称列表中选择“sheet001”，单击”删除”按钮，将其删除。

8）重新选中工作表“品名”中的数据区域A1：C151，点击“定义名称”按钮，弹出“新建名称”对话框，在“名称"框中国你输入“品名”后点击"确定"。

2.  合并商品的销售价格和进货价格

1）打开案例文档“价格表.xlsx”,单击“插入工作表”，插入一个空白工作表，并重命名为“价格”。 然后单击新工作表“价格”的单元格A1，在“数据”选项卡上的“数据工具”组中，单击“合并计算”按钮，打开"合并计算"对话框。在函数下拉框中选择“求和”函数，在“引用位置”框中单击鼠标，然后在工作表“单价”中选择单元格区域A1：B152，单击“添加”，继续在工作表“进阶”中选择单元格区域A1:B151,单击“添加”，在“标签位置”下单击选中“首行”和“最左列”两个复选框。单击“确定”按钮，完成数据合并。然后在A1单元格，输入“商品代码”。同时调整各列列宽。

2) 在工作表标签“价格”上单击鼠标右键，从弹出的快捷菜单中选择“移动或复制”命令，打开"移动或复制工作表"对话框。在"工作簿"下拉列表中选择前述案例文档“家电销售统计表.xlsx”,在”下列选定工作表之前“列表框中单击”（移至最后）“，勾选”建立副本“，点击确定。在移动后的工作表”价格“中选择数据区域A1：C152，在”名称框“中输入文本”价格“后，点击”确认“。对案例文档”家电销售统计表.xlsx“进行保存，关闭文档”价格表.xlsx“.

3. 完善商品销售统计表数据。

1）单击表标签“年度销售汇总”，将其切换为当前工作表。单击单元格A3，按下ctrl+A组合键选择整个数据列表A3：L374.在“插入”选项卡上的“表格”组中，单击“表格”按钮，打开“创建表”对话框。单击”确定”按钮，将选定区域创建为“表”，同时套用一个表格格式。在“表格工具|设计”选项卡上的“属性”组中，将“表名称”更改为“销售列表”。 在“数据”选项卡上的“排序和筛选”组中，单击“筛选”按钮退出自动筛选状态。

2）在C4单元格单击，输入公式 "=VLOOKUP(B4,品名!$A$1:$C$151,2,FALSE) ",按回车确定。在单元格D4中输入公式"=VLOOKUP(B4,品名!$A$1:$C$151,3,FALSE) ",按回车确定。然后选中D列，在右侧边界处双击鼠标，D4列自动调整列宽，显示单元格全部内容。

3）在J4单元格单击，输入公式"=VLOOKUP(B4,价格!$A$1:$C$152,2,FALSE) ",按回车确定。选中J列，设置单元格属性为货币类型。在L4单元格单击，输入公式"=VLOOKUP(B4,价格!$A$1:$C$152,3,FALSE) ",按回车确定。同样选中J列设置单元格属性为货币类型。

4）设置商品类别，从商品代码的前两位表示商品类别 ,如图

单击单元格E4，输入下面公式  “=IF(LEFT(B4,2)="TV","电视",IF(LEFT(B4,2)="AC","空调",IF(LEFT(B4,2)="RF","冰箱",IF(LEFT(B4,2)="WH","热水器",IF(LEFT(B4,2)="WM","洗衣机","计算器")))))" . 这是一个IF与LEFT的嵌套。  点击K4单元格，输入公式"=I4 * J4",回车。 综上所述，将区域设置为表格的好处，最直观的就是自动填充了。

5）选中A1：L1区域，设置“跨列居中”，然后套用"标题1"样式.  选中C列，设置居中显示。选中E列，设置居中显示。单击保存。

4. 分析汇总销售情况

1）插入一张空白工作表，并重命名为“迷你图分析”，在单元格B2、C2中分别输入文本"商品类别"，“分部”。单击单元格B4，在“数据”选项卡上的“排序和筛选”组中，单击“高级”按钮，打开“高级筛选”对话框。将“列表区域”指定为工作表“年度销售汇总”的“商品类别”列数据区域E3：E374(注意所选区域要包括标题行）."条件区域”保持为空。勾选“选择不重复的记录”，选中“将筛选结果复制到其他位置”，复制到设置为“迷你图分析!$B$2”,点击“确定”。

2）采用同样的方法，从工作表“年度销售汇总”的“分部”列中筛选出所有的分部名称，放置到工作表“迷你图分析”的单元区域C2位置。

3）选择分部名称所在的单元格区域C3:C9,按下ctrl+C组合键进行复制。点击C2单元格定位光标，在“开始”选项卡上的“剪贴板”组，单击“粘贴”按钮下方的黑色三角箭头，打开粘贴选项列表，选择“转置”按钮，分部名称自单元格C2开始向右填充。如图，删除C3：C9中的内容。调整C，D,E,F,G,H,I 等列的列宽。加粗标题的字体。在B1单元格输入标题“各分部各类商品全年销售额汇总”，设置“跨列居中”，设置为标题1样式。在J1单元格输入“单位：万元”。

4）在单元格C3中输入"=SUMIFS(年度销售汇总!$K$4:$K$374,年度销售汇总!$E$4:$E$374,$B3,年度销售汇总!$G$4:$G$374,C$2)", 同样的办法，在单元格C4中输入公式“=SUMIFS(年度销售汇总!$K$4:$K$374,年度销售汇总!$E$4:$E$374,$B4,年度销售汇总!$G$4:$G$374,C$2)” ； 在单元格C5中输入公式“=SUMIFS(年度销售汇总!$K$4:$K$374,年度销售汇总!$E$4:$E$374,$B5,年度销售汇总!$G$4:$G$374,C$2)”；在单元格C6中输入公式“=SUMIFS(年度销售汇总!$K$4:$K$374,年度销售汇总!$E$4:$E$374,$B6,年度销售汇总!$G$4:$G$374,C$2)”；在单元格C7中输入公式“=SUMIFS(年度销售汇总!$K$4:$K$374,年度销售汇总!$E$4:$E$374,$B7,年度销售汇总!$G$4:$G$374,C$2)”；在单元格C8中输入公式“=SUMIFS(年度销售汇总!$K$4:$K$374,年度销售汇总!$E$4:$E$374,$B8,年度销售汇总!$G$4:$G$374,C$2)”.

5）在单元格B9中输入“合计”，选中C3：I9，设置单元格格式为 自定义，自定义类型为"0!.0," ，点击确定。

（“0,!.0,”中的“！”是强制插入符号，“！”后面有一个".",表示这里强制插入小数点，通过千分位符“，”可知，强制插入小数点的位置是千分位的左边一位，也就是万位）。

6）点击C9单元格，输入公式“=SUM(C3:C8)”,拖动C9单元格右下角的填充柄，向右直到 i9单元格。

7）在J2单元格中输入标题文本“迷你图”，加粗并居中对齐，单击J3单元格，在“插入”选项卡上的“迷你图”组中，单击“折线图”按钮，打开“创建迷你图”对话框。指定“数据范围”为单元格区域C3：i3，点击“确定”。拖动J3单元格右下角的填充柄直到单元格J9.

8）在“迷你图工具|设计”选项卡上的“显示”组，勾选“高点”和“低点”，令迷你图中显示最高点和最低点标记。在“迷你图工具|设计”选项卡上的“分组”组中，单击“取消组合”按钮，使每个迷你图独立出来。然后在“样式”组中，分别设置每个迷你图的样式。

5. 利用数据透视表统计数据

数据透视表是最常用的，功能最全的excel数据分析工具之一。它有效的结合了数据排序，筛选，分类汇总等多种数据统计、分析方法的优势，是一种方便、快捷而灵活的数据分析手段。

1）在工作表“年度销售汇总”中，任意数据位置单击，定位光标，在“插入”选项卡上的“表格”组中，单击“数据透视表”按钮。打开“创建数据透视表”对话框。数据源自动取自当前工作表的当前区域，默认生成位置为“新工作表”，点击“确定”。excel将插入一个新工作表，并自该工作表的单元格A3开始创建一个空的数据透视表。将改工作表命名为“数据透视表”。在“数据透视表字段列表”窗格的字段列表区中，单击选中“品牌”将其添加至“行标签”中，将字段“销售日期”直接拖动到“行标签”中“品牌”字段的下方。

2）继续单击选中“销售额”字段作为列标签，并自动对数值进行求和计算。将字段“分部”拖动到“报表筛选”区中最为筛选字段。

3）在数据透视表的“行标签”下，用右键单击任意一个日期值，如右击单元格A6，弹出快捷菜单，从快捷菜单中选择“创建组”命令，打开“分组”对话框。在对话框的“步长”列表中单击取消对项目“月”的选择，再单击选中“季度”，单击”确定”

4）在透视表中增加字段，计算各个品牌的毛利和毛利率。在数据透视表上单击任意单元格，出现其上下文选项卡。在“数据透视表工具|选项”选项卡上的“计算”组中，单击“域、项目和集”按钮，从弹出的下拉列表中选择“计算字段”命令，打开“插入计算字段”对话框。在“名称”文本框中输入“毛利”，在“字段”列表框中双击“销售单价”字段，然后输入“-”，再双击“进货成本”字段。然后输入“*”，在双击“销量“，得到计算毛利的公式。公式为：毛利 = (销售单价 - 进货成本 )* 销量；单击“添加”按钮，将新定义的字段添加到数据透视表。同样的办法，再添加“毛利率”，毛利率=毛利/销售额.双击A3单元格，进入编辑状态，输入“季度”替换掉原来的“行标签”。同样将B3改为“销售额（万元）”，C3改为“毛利（元）”，D3改为“毛利率%”。

6 .插入数据透视图

1）在数据透视表中的任意位置点击鼠标。 将字段“品牌”从行标签中拖回字段列表，将”商品类别”拖动到报表筛选栏，放在“分组”的下面。

2）在数据透视表中的任意位置点击鼠标。在“数据透视表工具|选项”选项卡上的“显示”组中，单击“字段列表”按钮，暂时隐藏“数据透视表字段列表”窗格。

3）在“数据透视表工具|选项”选项卡上的“工具”组中，单击“数据透视图”按钮，打开"插入图表”对话框。选择“簇状柱形图”，点击“确定”。

由于毛利率与销售额和毛利没有可比性，想要在图中显示出来的话，就需要进行特殊设置。

4）在“数据透视图工具|布局”选项卡上的“当前所选内容”组中，打开“图标元素”下拉条，选择“系列毛利率%”，然后单“设置所选内容格式”按钮，打开“设置数据系列格式”对话框。在"系列选项"中，单击选中“系列绘制在”区域下的“次坐标轴”，单击"关闭".

5）依旧保持选中图标元素”系列毛利率%”，在“数据透视图工具|设计”选项卡上的“类型”组中，单击“更改图表类型”按钮，从打开的“更改图标类型“对话框中选择”带数据标记的折线图“，并单击”确定“