【辅助列解决实际问题_系列10】辅助透视表

cleverzhzhf

透视表可以说是Excel中的最大神器！处理数据量大，速度快，操作简便。
如果用函数来做，需要多重嵌套，多重计算，甚至可能直接电脑搞死机。

可是也有它的缺点：
1、对数据的规范性有一定的苛求：
    1）第一行上，需要每一个字段都有名称，不能有空值；（是缺点也是优点，正所谓无规矩不成方圆）
    2）每一列的数据要尽量做到格式统一，否则后续操作会有麻烦；（这就需要养成一个良好的表格习惯，不能太随心所欲）
    3）常规引用数据源方式，在增加新数据的时候不能自动扩展；（使用OFFSET+COUNTA这种最常用的动态数据引用组合）
2、出来的数据，可以说是一种制式，不能达到想要的最终效果
（没关系，让透视表完成量大恼人的计算即可，之后我可以Vlookup引用出来需要的数据，就达到效果啦。）

又一年的三国销售大会，这次卖的是电器，罗贯中给老板汉献帝做了个分析报告。
这次他不再只局限于使用函数公式的辅助列，而是神奇的用上了辅助透视表！
辅助透视表.zip (114.33 KB, 下载次数: 204)

2014-10-27 11:22 上传

点击文件名下载附件

数据区域：

辅助透视表：

定义动态名称的公式：

每次更新，“全部刷新”的神奇功效：

cleverzhzhf

一、定义名称data：
    =OFFSET(销售清单!$A$1,,,COUNTA(销售清单!$A:$A),COUNTA(销售清单!$A$1:$F$1))
    COUNTA：计算区域内用多少个非空单元格。
    用COUNTA数出来数据区域有多少行多少列，然后通过OFFSET的第4、5参数扩展到相应的数据区域，这样就把全部数据都包含进来了。
    有新的数据增加，COUNTA计算的数据就会相应的变化，然后OFFSET引用的数据区域也跟着变，达到动态的目的。

二、插入透视表：
    在数据区域那里，输入data。
   

三、对透视表进行操作，添加相应的字段，以及进行“排序”：
    因为我们在做分析报告，引用数据的时候，常常需要查看诸如“前三名”，“销量冠军”，“后三名”之类的；
    在一次对透视表排序后，以后每次刷新透视表，都会按照此规则进行排序计算。所以每次做，“前三名”等都会在“固定的单元格”。

四、=INDEX(辅助透视表!A:A,ROW(4:4))：
    用公式从透视表中引用相应的数据，也可以把公式更复杂一些，以增加容错性。
    根据个人喜好和水平而定，也可以直接简单的单元格引用：=辅助透视表!A4

五、美化我们引用的数据：
    这一步纯为了给老板看，大多老板还是喜欢看一些五彩斑斓的东西；
    同时可以在引用出来的数据旁增加简单的评论，使你的分析报告更加的专业，高大上。

看见星光

先赞，再读。

cmxxccxx

抢个位，慢慢看

lfspecter

Mark一下，搬个板凳用心学习

jeems

这个太好了，刚在学习透视表