返回

实务交流

发帖

数据透视表,小白的救星

Excel不加班楼主

2018-05-18 16:25:57 495 2

数据透视表不仅可以求和,还能求最大值和最小值等。而统计数据又是数据透视表的强项。下面看一下,数据透视表如何完成各种统计?


1.拖拉几下,条件求和全搞定。


某公司领用用品明细表,现在要对这张表进行4种情况的汇总。

                  

01 统计科室的数量。


Step 01 单击单元格A1,切换到“插入”选项卡,单击“数据透视表”图标,弹出“创建数据透视表”对话框,这时数据透视表会自动帮你选择好区域,保持默认不变,单击“确定”按钮即可。


Step 02 将科室拉到行,数量拉到值。


你没看错,拉两个字段就完成了统计,就这么简单。


02 统计科室和领用用品的数量。


领用用品这个字段可以放到行,也可以放到列,分别拖到行和列看一下效果。


将领用用品拉到行,就瞬间完成统计。如果这里用公式统计,更改公式都需要好久。善于借助工具,可以大大提升效率。


同理,将领用用品拉到列。数据透视表在改变布局上有非常明显的优势,如果对现有的布局不满意,只需拖拉一下就可以解决。


以上两种布局,各有优势,看自己实际的需求而定。


03 统计每个月份的数量。


现在不需要科室和领用用品,将字段取消勾选。取消勾选后,数据透视表就只剩下数量的统计。


返回数据源,再次确认一下,只有具体日期,没有月份,是不是需要用MONTH函数提取月份再统计?


Excel2016对日期的处理能力超级强大,直接将日期拉到行,不做任何处理,直接就按月份统计。


低版本将日期拉到行同样是具体日期。


选择任意日期,右键组合。组合这个功能,有的版本叫创建组,名字不同,用法一样。


默认情况下就是按月组合,直接单击“确定”按钮即可。


就是按月组合的效果。


也就是说,不管什么版本,数据透视表在进行按月份组合的时候都无需借助公式,这样操作起来就更简单。


04 统计每个科室各年份的数量。


现在需要按年份统计,选择月份任意单元格,右键取消组合。


选择任意日期,右键组合。


单击月,就取消选择月。


单击年,再单击“确定”按钮。


因为这里只有一年数据,所以组合后只有一个2016年。


假设将最后一个日期改成2017-5-9。


数据透视表不像公式那样可以自动统计,需要右键刷新,才可以统计。


刷新以后,2017年就出来了。


将日期拉到列,科室拉行,就完成了最终统计。


这是不是比公式简单很多?只需点点鼠标就完成了各种统计。


2.除了求和,还可以求最大值和最小值。


前面卢子采用MAX+IF和MIN+IF数组公式,获取物料代码的最高单价和最低单价。其实,最方便的方法还是数据透视表。工作上60%的问题都可以靠数据透视表解决。


Step 01 单击单元格A1,切换到“插入”选项卡,单击“数据透视表”图标,弹出“创建数据透视表”对话框,这时数据透视表会自动帮你选择好区域,保持默认不变,单击“确定”按钮即可。

Step 02 将物料代码拉到行,单价拉2次到值。


Step 03 单击“求和项:单价”这个单元格,右键,选择“值汇总依据”→“最大值”。


Step 04 单击“求和项:单价2”这个单元格,右键,选择“值汇总依据”→“最小值”。


Step 05 修改标题就完成了最终效果。这个在以后的章节会详细说明如何操作。


3.涨见识,数据透视表居然连不重复计数也可以。


统计每个业务单元对应的不重复公司个数。前面采用了超级复杂的公式才搞定,现在来看看数据透视表是如何实现的。


Step 01 单击单元格A1,切换到“插入”选项卡,单击“数据透视表”图标,弹出“创建数据透视表”对话框,这时数据透视表会自动帮你选择好区域,保持默认不变,再勾选“将此数据添加到数据模型”,单击“确定”按钮即可。


“将此数据添加到数据模型”是Excel2013以上版本的新功能,只有勾选了才有不重复计数的功能,否则就跟普通的数据透视表一样。


Step 02 将业务单元拉到行,公司拉到值。


Step 03 单击行标签总计这个单元格,右键,“删除总计”。


Step 04 单击“以下项目的计数:公司”这一列任意单元格,右键,选择“值汇总依据”→“其他选项”。


Step 05 计算类型选择“非重复计数”,单击“确定”按钮。


这样就完成了最终统计,这可比公式简单好多倍。


再次强调,一定要勾选“将此数据添加到数据模型”,否则是找不到“非重复计数”这个新功能,切记!


没有对比,就没有伤害。卢子这里用了三个很平常的案例,分别用公式法和用数据透视表法。用公式要完成各种效果,是比较困难的,需要有很高的水平才可以。而借助数据透视表,只需拖拉几下就搞定,普通人也很容易上手。选择合适的工具,你会事半功倍。


数据透视表,小白的救星。


你想跟着卢子一起学习数据透视表,摆脱这种苦不堪言的日子吗?

作者:卢子,清华畅销书作者;个人公众号:Excel不加班(ID:Excelbujiaban)

飞翎恋雪1楼

2018-05-23 13:40:44