数据透视表,大家可能都比较熟悉,功能非常强大,特别常用的是分类汇总功能,可以多方位、多角度地对数据进行汇总,分析。 通常,我们做明细数据表的时候,首行作为标题行,在做数据透视表引用数据源的时候,直接从明细表的A列,拖到最后一列来完成数据透视表的制作。 后续增加记录的时候,可以直接登记在最后的记录下面,汇总的时候都能包括在内。 然而,实际工作中,情况会比较复杂,也许会增加列,如果是在最后一列前增加新列,刷新数据透视表的时候会包括进去,但在最后一列之后添加,就不能直接包括在汇总范围之内了。 这时,通常是点击“数据透视表向导”来重新选定数据范围,偶尔为之,无伤大雅,常常这样,就不胜其烦了,这是一; 还有一种情况,标题行不在首行,首行有表格的名称,比如“XXX明细表”之类的,那么数据源选择那就不能直接选从A列到最后一列了。 通常是从标题行首列开始,选择到最后一列,然后再向下选择若干行,形成一个区域,只要区域行数足够大,后续增加记录是没有问题的。 虽然以上做法也勉强可行,但是总觉得不是那么professional,有没有更好的方法呢?答案当然是肯定的,那就是利用“名称”建立一个动态的数据引用区域,作为数据透视表的数据源。下面结合本人实际工作中的一个例子来说明一下。 本人在某贷款公司从事财务工作,有一项工作是登记“贷款明细表”,记录放款信息,及时提供相关报表。明细表样如下(表名称是“放款明细表”): 当然,这只是一个简化了的演示表,实际包括很多字段,还有其他相关表格,这里不多说,只是用来说明动态引用区域的设置方式。 一、建立名称,具体叫什么,各人自己随便定义,以直观明了为好。这里是“明细表数据源”,公式是=OFFSET(放款明细表!$A$3,0,0,COUNTA(放款明细表!$A:$A)-2,COUNTA(放款明细表!$3:$3)) 公式的基本含义可以描述为:利用OFFSET函数,从A3开始,建立一个区域,高度(height)是所有行的数量,宽度(width)是所有列的数量,并且,在增加行、列的时候,自动扩展。 二、创建数据透视表 在表/区域里,填上刚建立的名称,然后选择汇总显示的字段,大功告成! 怎么样?想不想去试试看? 不想折腾的同学,把表格的字段改改就能用到其他地方,是不是很方便啊?! |
|