6165金沙总站 24

商品销售情况统计,Excel数据统计

问题:请问大神,有没有函数能把工作表“鸿意A”里面“分公司”(例如:郑州)对应的重量自动统计出来填入工作表“2017分公司统计”C3单元格处,体积数据以此类推。图一数据每天会增加,图二也能自动计算出来,谢谢。

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

回答:

步骤:

这个为题可以用sumif函数解决

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

如图,我们要将明细表里的数据填入统计表中

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

6165金沙总站 1

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

6165金沙总站 2

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

步骤如下:

6165金沙总站 3

6165金沙总站 4

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

在目标单元格中输入公式“=SUMIF(销售登记!B:B,A4,销售登记!E:E)”往下填充就行,这个公式,B列是条件区域,如我们是按地区汇总B列就是我们明细表里地区那列,A4就是我们求和的条件,即将区域为A4单元格内容的区域的销量进行求和,我们的E列就是我们的求和列。

6165金沙总站 5

提问示例中,日报表地区就是我们的B列,体积和重量就是我们的E列。

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

回答:

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

这个问题起码有两种处理方法:

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

  • 使用sumif统计重量和体积;

  • 使用透视表统计重量和体积。

6165金沙总站 6

sumif统计重量和体积

用法:sumif(地区列, 地区, 重量列)

6165金沙总站 7

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

透视表统计

使用透视表可以轻松统计重量和体积

6165金沙总站 8

右键透视表选择刷新,即可更新到最新数据

6165金沙总站 9

6165金沙总站 10

问题扩充

这里就再多介绍几个常用的统计用法

  • 条件求和sumifs

6165金沙总站 11

  • sum跨表求和

6165金沙总站 12

  • count计数

6165金沙总站 13

  • 透视表

6165金沙总站 14

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

更多实用技巧,记得关注我哦!

回答:

用数据透视表

1、选中数据所在区域的整列,一定要全选,不是只选中数据区域哦

2、插入-插入数据透视表-在新工作表

3、在新工作表中,勾选城市为行标签,勾选吨位、体积为数值

4、在数据区域右击,选择值汇总依据为求和(有的时候会默认为求和,就不用修改了)

此时现有表格的数据就求和完成了,如增加新数据,直接刷新数据透视表即可,动图可见每步操作。

6165金沙总站 15

回答:

透视表的使用,下面的答主已经说得非常清楚了。

我补充一点,选中表格区域——点插入——表格——包括标题

这个步骤是把你的区域变成智能表格,然后再去做透视表

每次新增数据的时候,透视表数据源会自动扩展,自动刷新数据

欢迎关注我的头条号,如果有excel方面的问题,可以私信交流,为你答疑解惑。

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

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

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

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

6165金沙总站 16

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

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

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

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

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

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

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)设置商品类别,从商品代码的前两位表示商品类别 ,如图

6165金沙总站 17

单击单元格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列,设置居中显示。单击保存。

  1. 分析汇总销售情况

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

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

6165金沙总站 18

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

6165金沙总站 19

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.

6165金沙总站 20

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

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

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

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

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

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

6165金沙总站 21

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

6 .插入数据透视图

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

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

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

6165金沙总站 22

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

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

6165金沙总站 23

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

6165金沙总站 24