一、定义名称法:
说白了,就是在创建数据透视表,选择数据源的时候,用一个自定义的名称来代替数据源;而这个自定义的名称包含的,就是一个动态的数据源;假如数据源在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右图)
在“引用位置(R)”处输入公式 =OFFSET(汇总!$A$1,,,COUNTA(汇总!$A:$A),COUNTA(汇总!$1:$1))
在名称处输入“汇总表源数据”;点击“添加”,“确定”按钮(下图4.2为2003版本)
STEP2:按照传统方法,鼠标点击到“汇总”表内任意单元格,数据—数据透视表和数据透视图—点击下一步,直到进入步骤之2界面(下图4.3为2003版本)
STEP3:将“选定区域(R)”内的内容删除,输入“汇总表源数据”,点击“完成”按钮【注:输入的时候,不用加引号】(下图4.4为2003版本)
数据透视表创建OK。将“物料编码”拖到行区域,显示有852条记录;
验证:1、将数据源区域的数据人工删除部分行和列字段,再刷新透视表,可见的确是最新数据;
2、再将数据源区域的数据人工增加部分行和列字段,刷新透视表,可见也的确是最新数据;
二、列表法
选中数据源中的任意一个有内容的单元格-数据-列表-创建列表(下图4.5为2003);通过列表工具栏设计页面布局;
(2007)选中数据源中的任意一个有内容的单元格-插入-表; 再到 设计菜单进行页面布局
不过从最终效果图来看,该方法创建的透视表只支持行记录增加或/减少,列记录减少/在现有区域内插入新增列 后的动态更新。而且行记录貌似只能一行一行的增加,列记录要增加的话,必须在现有的列区域内(也就是图中A~H列)插入列。如果在I列新增,是无法实现自动更新的。
三、VBA创建透视表
这个……虽然自学过VBA,但毕竟有大半年没见它老人家了。再加上,貌似普通法+列表法+自定义法+SQL四个方法对我来说已经足够,所以……呃,我暂时不研究啦~~~
四、三种创建动态透视表的方法优劣对比:
本想做一个对比表格,从多方面来对比展示。但考虑到我也是初学,对这几种方法的理解可能会有偏颇之处,最终还是放弃了。在这里将我认为需要注意的事项做一个简要说明吧。
对于单表透视:
如果只需要添加行信息,或者对新增的列字段位置要求不高;且单表数据量不大的话,就用列表法,毕竟快速而且适用;但一个包含了列表法创建透视表的工作簿不能设置共享;
如果行和列都可能需要添加并且位置要求较高,或者单表数据量非常大,推荐使用定义名称法,虽然公式长,不过不复杂,毕竟只有两个函数套用。用该方法创建透视表后可以支持设置共享;【注意:只是支持你把工作簿设置成共享,但在共享状态下,透视表是不能使用的!】
如果要进行多表透视,推荐使用SQL。因为SQL不但可以应用在普通的透视表创建,也可以创建复杂的透视表,跨工作簿透视,有条件选择性透视等,功能是三者中最强大的;
建议大家在工作中尽量套用这几种方法试试,这样更容易掌握
评论