透视表轻松搞定,一对多统计查询(上)

作者: ladeng6666 分类: Excel 发布时间: 2019-04-14 17:10 阅读: 731

 

作者:Y.SL

秋叶Excel训练营优秀学员投稿

 

以下是正文

前言

领导:小白,请把这份销售数据给我汇总下,我要看每个人产品总数跟明细,我才能一个个对他们有个确切了解。

领导:下面两点要特别注意。

– 最重要是按部门、人员、订单金额、对应的产品大类给我汇总下,产品编码太多了,我只要产品大类。

– 欧,对了,我需要对应每个订单单号的销售数量、金额、成本、利润,都给我显示在同一份表就行!(敲黑板!)

小白:嗯,好的,一张表,我试试

领导:我下午开会要用到,现在刚上班,你来得及吧?

小白:啊?。。。。。可~以~

领导就是这样一种动物

午休刚过,小白睡意未去,拖着腮正发呆,电话像是一挺机关枪,疯狂的刨叫起来!!!!

我:听了电话那边的咆哮,好了,我知道了,

需求分析

我挂完电话,稍微理顺了下问题描述

1- 小白的领导需要的是什么?

1- 小白遇到了什么困难?时间?技巧?数据多?函数复杂?

1- 一张表能不能表现出所有的项目需求,有总又分,又要分部门分月份查看,是不是不可能?

回想本尊也遇过这样的苦恼:领导啦啦啦表达了一堆,好像一份数据什么都想看到,但是又要简单。

最后做出来的,来一句:这个我看不懂,我要的不是这个。

失落~~

除了心慌,你还能干什么?

既然任务来了,就用秋叶训练营学过的毕生功力,拯救下小白!

 

那么我们一般会选择数据透视表,来汇总它的人员销售信息,产品销售信息,利润,金额等,

手动构思下草稿, 嗯,大概就是这样吧,那就开始干活了


一、普通透视表应用

1、剔除无效数据,简化视觉审判

1- 先删除无用数据列,原系统数据导出来,列部分有31个,行数5078。

 

2- 后优化:保留要提取信息的数据列A列-M列,再插入智能表格

是不是清晰了很多

有效的基础数据源,可以让自己事半功倍

 

2、解决汇总要求,提取汇总字段

现在我们尝试下数据汇总,以往思维方式,如图

这样的汇总,领导除了紧皱眉头摇头,还能怎么办?

反之,这是不是也告诉了领导,我没有能力做出来,我的工作还能胜任吗?

转型给大家一个精致透视表的应用设计,请继续往下看!

二、精致透视表格应用

先看下效果

1-领导要的总分明细:一个部门中一个员工的一个订单的分类销售明细,如图:

 

2-领导要的订单总额:在行字段(带三角下拉选项)可以显示对应订单单号的总金额

3-领导要的利润金额

4-领导要的按部门分:切片器筛选按钮

5-领导要的按月份分:切片器筛选按钮

 

精致透视表


做完,发给小白看下效果

秒回。。。。。

小白:这真的是数据透视表做出的效果吗???(惊恐)

小白:我想到的是得用vlookup跟公式去提取汇总信息,然后合并同类项,不然做不出来。

YSL:是的,公式可以做到,只是工作量可想而知,我们还要分月份跟部门去汇总,而且数据一多,函数公式出现误差几率较高。

小白:是啊,怎么做的,来不及了,快教我,领导下午就要数据!

小白:来,思路透漏下


三、思路设计,掌握手段

Step1–字段选择

1- 行字段(需合并同类项):

【销售部门】、【销售人员】、【单号】、【开单总金额】、【产品名称】

2- 值字段(需汇总计算):

【数量】、【成本】、【金额】、【利润】

Step2–增加辅助列

1- 【开单总金额】在行字段(D列):基础数据源增加函数辅助列,实现订单汇总,公式:sumif 。

2- 【利润】在值字段(I列)–字段增加-公式添加

Step3–条件筛选

1- 月份切片器

2- 部门切片器

(可参照前面的手工画图稿理解哦)


现在,附上数据源,给大家感受体验下,大家也可以尝试根据以上思路,试试效果,请动手吧!

 

我是拉小登,一个会设计表格的戏精Excel老师

 

 

 

 

 

 

 

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

2条评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注