微信号:cntongji

介绍:中国统计网(www.itongji.cn),国内最大的数据分析门户网站.提供数据分析行业资讯,统计百科知识、数据分析、商业智能(BI)、数据挖掘技术,Excel、SPSS、SAS、R等数据分析软件等在线学习平台.

技能提升Excel数据透视,让数据分析更轻松!

2018-03-14 17:00 中国统计网


透视表“值显示方式”


透视表中的值显示方式是很常用的功能,但是由于种类较多,很多同学容易搞混,今天就给大家整理出来所有值显示方式的用法。(Excel数据透视课程福利,在文末)




下表是我们要用到的基础数据。


1总计的百分比

如果求每个品类的产品在每个区域的销售额占总销售额的比例。


把产品类别这个字段拖到行区域,把区域字段拖到列区域,总金额拖到值区域,对总金额进行值字段设置,在值显示方式中选择“总计的百分比”。


2列汇总百分比

求每个品类的产品在各自区域的销售额所占的比例(也就是每个区域占比总计为100%,看每个品类的占比为多少)。


前面的操作和上一个示例一样,就是在值显示方式中选择“列汇总的百分比”就行了。


3行汇总百分比

每个品类的产品销售额合计为100%,看每个区域的销售额所占的比例是多少。


在值显示方式中选择“行汇总的百分比”就行了。


4百分比(以电脑为基本项)

如果想看每个品类的产品销售额与电脑销售额的比较,可以在值显示方式中选择“百分比”,基本字段选择“产品类别”,基本项选择“电脑”,这样就以电脑的销售额为100%了,看其他产品的相对销售额。


5百分比(以东区为基本项)

如果想看每个区域与东区销售额比较的话,可以在值显示方式中选择“百分比”,基本字段选择“区域”,基本项选择“东区”,这样就以东区的销售额就为100%了,可以看其他区域的相对销售额。


6父行汇总百分比

父行汇总百分比是求每个单元格的数值占自己上一级行类别的百分比(行区域的的字段级别自上往下越来越低)


7父列汇总百分比

父列汇总百分比是求每个单元格的数值占各自上一级列类别的百分比(列区域的的字段级别自上往下越来越低)


8父级汇总百分比

父级汇总百分比是指定某一个字段为父级(也就是以它的总计为100%),来看占比情况。


在上面的例子中,如果基本字段选“产品大类”,则每个大类的合计是100%;如果基本字段选“区域”,则每个区域的合计是100%。


9差异与差异百分比(同比分析)

工作中做月报分析的时候经常会用到同比分析,就是这个月与去年这个月的比较,一个是看值的差异,另一个是值差异再除以去年的当月的值,算出差异百分比。


这里需要提醒大家的是,上面演示的版本是2016,如果你的Office是早期版本,需要对日期进行分组才能进行下一步的操作。


10按某一字段汇总/按某一字段汇总百分比

这个翻译的不太好理解,其实就是按某一字段累计和累计百分比。如果想看16年每月的累积情况,可以按下面的演示操作。(Excel数据透视课程福利,在文末)


11升序降序排列

按中国的习惯计算排名,如果有相同排名的情况,用公式的方法就会比较麻烦,需要用到数组,而用透视表的升序降序排列就比较简单了。


12分区降序排名

在进行中国分区排名时,使用公式就要用到更为复杂的数组,而用透视表却极为简单,只需要在行区域新增一个字段就行了。


产生透视图时不产生透视表


在普通Excel表中插入透视图,会自动带透视表,如果再把透视表删除,则透视图会变成普通图表。


Office13版开始,新增了Power Pivot功能(10专业增强版可以到官网免费下载),可以单独插入透视图,具体操作如下。


■ 操作步骤1:将数据加载到Power Pivot

选择任一有数据的单元格,点击“添加到数据模型”


勾选“我的表具有标题”,确定。


■ 操作步骤2:插入透视图

下拉“数据透视表”,点击“数据透视图”。


点击确定。


将“产品名称”拖至轴区域,将“数量”拖到值区域即可。


现在,没有产生透视表,只有透视图。

再对图表进行美化,插入切片器就是一个动态图表了。


避开GETPIVOTDATA


1案例 场景举例

当我们引用数据透视表中的数据时,会自动出现数据透视表函数GETPIVOTDATA向下复制单元格时单元格引用不会相应变动(Excel数据透视课程福利,在文末)



此时应该怎么处理?有两种方法。


2手动输入解决

这时,可以采用手动输入的方法,在D3单元格中输入“=B3+C3”,向下复制就可以了。


3Excel选项中设置

如果你的工作中还用不到透视表函数GETPIVOTDATA,可以在Excel选项中设置。


End.

作者:白永乾

来源: Excel实务



没错

3月15日20:00

以上技能储君老师

全部分享给你!



点击进入直播间

 
中国统计网 更多文章 校招生开出年薪30万+,这个岗位最吃香! Python难懂?买一次西瓜就懂了! AI将重塑TMT行业,从业者分别应该关注什么? “五险一金”政策新变化!2018年《政府工作报告》用数据说话! 如何进行用户数据挖掘?数据分析下的受众画像
猜您喜欢 人工智能的价值一定在于进攻,而非防御 雅虎开源的Kafka集群管理器(Kafka Manager) 编程新手:看懂很多示例,却依然写不好一个程序 从4行代码看右值引用 009 | 快速入门Web前端开发的正确姿势