注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

宁夏城市印象

宁夏

 
 
 

日志

 
 

自定义名称法及列表法制作动态数据透视表  

2012-11-20 16:58:35|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
 自从加入Excel Home数透中级培训班,才知道原来数据透视表有那么多的学问!今天看到wyf22006在群里说,创建动态区域的透视表有四种方法:一、定义名称法;二、列表法;三、SQL引用法;四、VBA创建。除了昨天新学的SQL引用法,其它三种根本没听说过啊!这好奇心马上就被勾起来了。三下两下把工作弄完,立马上网百度,整理出来跟大家分享~

一、定义名称法:

       说白了,就是在创建数据透视表,选择数据源的时候,用一个自定义的名称来代替数据源;而这个自定义的名称包含的,就是一个动态的数据源;假如数据源在sheet1中,那么自定义名称的公式=OFFSET(sheet1!$A$1,,,COUNTA(sheet1!$A:$A),COUNTA(sheet1!$1:$1))

      这里的:COUNTA(sheet1!$A:$A) 得到的就是sheet1工作表中A列的非空单元格的数量;

COUNTA(sheet1!$1:$1) 得到的就是sheet1工作表中第一行的非空单元格的数量;

即:区域=OFFSET(工作表中起点单元格,, 工作表连续非空行数, 工作表连续非空列数);也就是说:这个公式是定义一个代号来代表一个区域,不管这个区域的范围如何改变,代号名称不变,那么当区域变化的时候,直接刷新透视表,得到的总能是最新数据。


       听起来蛮不错的,马上试验一把~ 把“汇总”这个sheet表里面的数据做一个透视,然后随意增加或减少表内数据的行数列数,看看透视表能不能通过右键刷新自动选择最新的区域。

STEP1:首先自定义一个名称;(2003:插入—名称---定义 4.1左图) 进入以下界面(2007:公式,自定义名称  4.1右图)

4.1.jpg


2012-10-30 21:52:16 上传
下载附件 (29.64 KB)
                              

在“引用位置(R)”处输入公式 =OFFSET(汇总!$A$1,,,COUNTA(汇总!$A:$A),COUNTA(汇总!$1:$1))

在名称处输入“汇总表源数据”;点击“添加”,“确定”按钮(下图4.2为2003版本)

4.2.jpg


2012-10-30 21:52:17 上传
下载附件 (22.42 KB)

STEP2:按照传统方法,鼠标点击到“汇总”表内任意单元格,数据—数据透视表和数据透视图—点击下一步,直到进入步骤之2界面(下图4.3为2003版本)

4.3.jpg


2012-10-30 21:52:18 上传
下载附件 (20.16 KB)

STEP3:将“选定区域(R)”内的内容删除,输入“汇总表源数据”,点击“完成”按钮【注:输入的时候,不用加引号】(下图4.4为2003版本)

4.4.jpg


2012-10-30 21:52:19 上传
下载附件 (19.97 KB)

数据透视表创建OK。将“物料编码”拖到行区域,显示有852条记录;

验证:1、将数据源区域的数据人工删除部分行和列字段,再刷新透视表,可见的确是最新数据;

         2、再将数据源区域的数据人工增加部分行和列字段,刷新透视表,可见也的确是最新数据;


二、列表法

      选中数据源中的任意一个有内容的单元格-数据-列表-创建列表(下图4.5为2003);通过列表工具栏设计页面布局;

(2007)选中数据源中的任意一个有内容的单元格-插入-表; 再到 设计菜单进行页面布局

4.5.jpg


2012-10-30 21:52:21 上传
下载附件 (66.16 KB)


      不过从最终效果图来看,该方法创建的透视表只支持行记录增加或/减少,列记录减少/在现有区域内插入新增列 后的动态更新。而且行记录貌似只能一行一行的增加,列记录要增加的话,必须在现有的列区域内(也就是图中A~H列)插入列。如果在I列新增,是无法实现自动更新的。


三、VBA创建透视表

        这个……虽然自学过VBA,但毕竟有大半年没见它老人家了。再加上,貌似普通法+列表法+自定义法+SQL四个方法对我来说已经足够,所以……呃,我暂时不研究啦~~~


四、三种创建动态透视表的方法优劣对比:

      本想做一个对比表格,从多方面来对比展示。但考虑到我也是初学,对这几种方法的理解可能会有偏颇之处,最终还是放弃了。在这里将我认为需要注意的事项做一个简要说明吧。

对于单表透视:

       如果只需要添加行信息,或者对新增的列字段位置要求不高;且单表数据量不大的话,就用列表法,毕竟快速而且适用;但一个包含了列表法创建透视表的工作簿不能设置共享;

       如果行和列都可能需要添加并且位置要求较高,或者单表数据量非常大,推荐使用定义名称法,虽然公式长,不过不复杂,毕竟只有两个函数套用。用该方法创建透视表后可以支持设置共享;【注意:只是支持你把工作簿设置成共享,但在共享状态下,透视表是不能使用的!】

       如果要进行多表透视,推荐使用SQL。因为SQL不但可以应用在普通的透视表创建,也可以创建复杂的透视表,跨工作簿透视,有条件选择性透视等,功能是三者中最强大的;

      建议大家在工作中尽量套用这几种方法试试,这样更容易掌握

  评论这张
 
阅读(349)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017