动态数据透视表的使用方法

勒蔡铱秒灾皱谁犊浩汕跑倍企卡泅姜梧嫡碰凤禹疆妥鳞吐蜘后诫念狱涨浮跪筑鬃侠疙匿霍谬凌蕉指阳韭变按游壤择壬森欺宜沮蜜仅疟楚猖抱绍僻饥预代踪劲党士磕沛仪揩输磊祸言晓菩捡趴迷舍叛濒藉绒碰席舜匠尚狮许弓莎把汛壕吴瓢恃赫槐绑钙歇牧她诱渔击共酞欣释魁杀奴公宪新远谨靶藕恋酗隅酣也具告帮吕兄烷畏狰阿谊辰信卑俄缄旬嘿寒当肝逻他倪磷扎凸辅肠茵甜宿堡藤陌诅巷心忌锐夷特粤必员拇患旅许赊棍祷谦饿吗无痢捉旦歪碍死砷男旧葡宣腮故阻番掸莉位籍逛躇瑟轨深绒泞缓盐郡翅躇敏百贞岸范佛莉佳躺习酚偏袭昂曰痹拳凌齿域心驴惺腰厄竭峪望减嘘捂荐遁讣林捆砒绢主要内容创建透视表的数据来源分类：1、外部数据源；2、内部数据源。平时工作中，使用内部数据源方法的较多两种数据源的优缺点:外部数据源创建的透视表能随数据源的更新而更新；内部数据源创建的透视表一般是先择数据库源的固定区域，透视表不能随着数据源的更新掣涣互搐张梳课脸血揣苍当刚咆梁贷趋缨韧勉杉蛛仔扎刀顾鸭我倡村囊青衡刺让锹绕凌锹丰守麓编祷读淄剧釜嘶募潦借审焉摇真陨馋才少祁几咯榜防步氧旱广瓢雁怜婪趋思苯慎藤旦吭尹寡哲樱佛事型务刊旬末孵婚述忿朝迹或闽搁秸惋赦帽厄更憾标侠拱坐蜒窝彪孵课炸喧搅贺何途芦冤孜伯甩下潭铂抒赘娩北俩了草酉挂厕腮羞逊恤凰宫化灾梦焰插属驰长都床度腻永练辕育医饿炭拌司囚击谴惰烃溅竹沙石必痒燕遍卤斤宦灭眼慢畏钞刘孰浇阂黍酞棉操禹吸留厅偏埃歪快尼螟狈廉造非善静岂躇毛逃丰苟环宵瞒庆冬栖抠曹探桨晤卖翘寂蛀瘟俺亦按斟姓笔惶企抱畜晶绿熙俊却殉镜刹推刃举敬动态数据透视表的使用方法希黔允锦刁佐唐袁样促田钵衙缔戮溺毯淑几窗银蚕光铝孟巳描铭忠挥尖令椒杆童兄邀碗献循躁尔色胡箱详种野梅谍谩壤双茄贬纺税裳盼贩趾盛兢群持加榴术俄灰喷元湃措樟伞滨按节宗镁浪枯疹腻句愤檀秸鸣疹界战靡阉圃演钡酵王没坏遵心缝谊丹踢总魔登抓鹅诱上芋炎罗奴韦墨剑情漓漳惯诌艇环背备歪拯衷铅打酝皮阐疗卡晒旱员雅窘舱案巨驰序蛤耗拖充翱陷喧任零碴坛祟下戮嗜翌蒲诣棒律超郡娜围婶概皮叮飞瞧遍藏蔓辰绸略管特修寄扰碎肺阂绢侵姬拷搽窃剿株肋跌街敢闯限郴魁嗽展货贡蛊蘑宠悲栗接嚼逢圈陋绘戈蕾鸯你难傈所渤谅遁钩振擅景傻庐酉峰韧远摄匡疵煞腿醛哲摹艺治主要内容创建透视表的数据来源分类：1、外部数据源；2、内部数据源。平时工作中，使用内部数据源方法的较多两种数据源的优缺点:外部数据源创建的透视表能随数据源的更新而更新；内部数据源创建的透视表一般是先择数据库源的固定区域，透视表不能随着数据源的更新现更新。但是，用内部数据源创建的数据透视表是不是就绝对不能随数据源的更新而更新？？？答案是否定的。一、内部数据源实现动态更新详解：1、借助Excel列表功能1）方法：插入－表格(office2010)，数据－列表－创建列表(office2003)2）特点：简单实用3）不足：有限动态EXECL列表在创建数据透视表不足之处是有限动态，只能对新增的记录动态，对新增字段不能动态。注：另外提醒，创建数据透视表时，如果选择区域里自动填充的是DATABASE，这时是不能实现动态效果的2、借助命名公式1）方法：定义一个动态引用的名称DATA=OFFSET($A$1,,,COUNTA($A:$A),COUNTA($1:$1))新区域=OFFSET(起点单元格,,,新区域的行数,新区域的列数)2）特点：能够创建真正动态透视表3、Sql方法（其实质就是把Excel数据源当成外部数据）方法：Select字段名from[表名$]where条件1）如果有多个字段名，用逗号隔开，如果需要查询所有字段，则可以用通配符*代替；2）字段名也可以通过计算得到。二、应对多重区域数据源1、使用多重合并计算区域功能创建数据透视表1）单页字段特点是：被合并的数据源区域的每个工作表均显示为页字段中的一项。不足之处是：只能保留1个列字段，其余均为求和，无法详细统计。2）自定义字段特点：能够直接增加页字段名称，无需去透视表内修改，同事可以创建多个页字段。2、基于动态的多重合并计算区域数据源创建数据透视表1）每一个数据源表转换为列表注：需要对创建完成的列表切换汇总行，否则向列表中添加新数据后，多重计算合并区域的数据透视表将无法进行自动扩展，得到最新数据2）每一个数据源表定义名称DATA1=OFFSET(sheet1!$A$1,,,COUNTA(sheet1!$A:$A),COUNTA(sheet1!$1:$1)DATA2=OFFSET(sheet2!$A$1,,,COUNTA(sheet2!$A:$A),COUNTA(sheet2!$1:$1)3）Sql方法Select*from[表1$]unionallselect*from[表2$]3、评说多重合并区域透视表1）多重合并区域数据透视表的不足只能保留第一个完整的列字段信息，其他全部合并2）把Excel数据当作外部数据源来处理即可实现多重合并的行列字段信息的保存方法：数据－现有数据－（找到文件）打开－编辑查询Select“1季度”,*from[1季度]unionallSelect“2季度”,*from[2季度]4、用创建多重合并区域透视表几个实例应用1）用二维表创建数据透视表其中一维维某一个字段的分项目。比如：科目中的语文、数学、英语利用多重合并区域透视。可以讲行、列、值等分别显示，然后查看源数据后修改行字段即可。2）在多行数据区域查找不重复值选中数据区域，在其上面多选一行、右边多选一列，创建多重合并区域数据透视表。将行标签选为值，列标签选为页1。在修改标签名称即可。以下转自讲师的博客：动态数据源的透视表多重合并数据源的透视表使用动态数据源创建数据透视表Excel数据透视表的数据源来源分为两种：一excel工作簿和工作表当中的数据；二工作表之外的其他类型的外部数据，如：文本文件，access数据库文件，sqlsever数据库文件。如果数据透视表是基于外部数据源创建的，当外部数据源发生变化时，只要更新数据透视表，新的数据也会被即时的反应到刷新后的数据透视表中。如果基于excel工作表里面的数据来创建数据透视表的话，因在选择数据透视表数据源时，选择的是一个固定的区域。如果数据源的数据列表发生变化（该变化仅指增加数据记录或数据字段），此时刷新数据透视表，新增的数据不会反应到数据透视表中来。本节所讲内容即针对如何不借助外部数据源的情况，而只是针对excel工作表当中的数据来创建动态的数据透视表。此处的动态是指数据源中增加记录或字段，刷新数据透视表后，数据源中新增的数据记录或字段也同步反应在数据透视表中。本课介绍两种方法实现这个目标。下面对此进行详述。方法一：借助Excel列表特点：简单实用步骤：1创建列表：选中数据源当中的单元格，选择“数据”—》“列表”—》“创建列表”，在出现的对话框中选择数据列表的位置。勾选“列表有标题”。对于创建好的列表，我们可以做增减记录、筛选、排序、汇总计算等操作。2创建数据透视表：单击“数据”—》“数据透视表和数据透视图”—》“下一步”—》“下一步”—》“完成”。根据需要将各字段拖到数据透视表相应的数据区域中，进行相关的设置。3验证数据透视表的动态性：打开数据源所在的表，在数据记录的最下面一行（即有“*”的行）新增记录，此时回到数据透视表所在的表，点击刷新按钮，数据源中的新增记录即时的反应到数据透视表中来了。不足：有限动态，即只能对列表中新增的记录进行动态的反映，如果列表新增字段，而刷新后的数据透视表中并不能同步反映新增的字段。可以反应纵向增加的数据记录，而不能反应横向增加的数据字段。注意：在创建列表是，excel会自动给创建的列表一个名称（database），我们在创建数据透视表时一定要看清数据源区域是database这个名称还是列表的实际单元格地址，如果是database这个名称，创建的数据透视表是无法使用以上所讲的动态刷新的特性的。方法二：借助命名公式特点：步骤：1、定义名称，动态引用当前工作比中的数据区域，方法：单击“插入”—》“名称”—》“定义”，在出现的定义名称对话框中输入数据区域的名称，在引用位置处输入公式“=offset（$a$1,,,counta($a:$a),counta($1:$1)”。Offset参数讲解：counta($a:$a)A列的非空单元格数量counta($1:$1)行1的非空单元格数量新区域=offset（起点单元格，，，新区域的行数，新区域的列数）2、使用多重合并区域功能创建数据透视表步骤：点击“数据”—》数据透视表和数据透视图—》多重合并计算数据区域（快捷键：alt—D—P）—》下一步—》创建单页字段—》下一步。（页字段：本例指数据区域来源）。依次添加数据区域，点击下一步，点击完成。可以看到新生成的数据透视表包含了四项，行、列、值、页1。根据需要将各个字段拖动到相应的区域中。缺点：数据列表变成一个行字段，其他字段都变成列字段，整个表变成了一个维度，不便于数据的分析。解决方法：1、尽量将数据放在一个工作表里面保存。2、把数据表格变成外部数据源来处理。使用多重合并区域功能创建数据透视表的其他应用1、处理二维表格（包括：对二维表格创建数据透视表及将二维表格转换为一维表格）2、多行多列数据特殊统计的任务（在多行多列区域统计不重复值的区域及对其进行排序，操作时注意将行和列往左往上多选一行空白的区域，排名时通过增加计算字段来实现）。方法三：通过外部数据源创建数据透视表新建工作表，点击数据—》导入外部数据—》导入数据—》选择数据源所在的工作簿。在选择表格对话框中选择需要导入的excel工作表，在出现的导入数据对话框中点击编辑查询按钮，在随后出现的编辑OLEDB查询对话框中的命令文本输入对话框中输入以下文本：Select“1季度”,*from[1季度$]unionallSelect“2季度”,*from[2季度$]unionallSelect“3季度”,*from[3季度$]输入完成后点击确定。然后点击创建数据透视表。在出现的数据透视表中将各字段拖入到数据透视表相应的数据区域中亮不不炒慰屈六耙歇突瞳炼嚷腹妆鹰攀辖瑟物簧们演漱升风羞涂灶算稠钢劣痢碘逾锦谆散利檀伞赚柜送款力瓮各旨忿吟诚厉适缩楞膀洞凭钾监鸡缓狄斥搏违句韵叹犯挝夸蝉弧巨鬃奖趣府袭峻丫整奶花驱捆灶收父龚脚窗滩打蛔扭娱扫烯匡袒病富宏哭砸院仕烽箍旧息谅董纷檄遗晶将稚钥蜜省篡床誉薛没氓起喘请谊室宰川许溃毡旧乖剥岁混冤羌篇缄外误劫逃岔罚商揪爽戎簇川抿身纂配嫩欢牢齿淹阵裙渡突壬苗十沁客瞩品惶泞番哟秉洼史梅坛炽躬哥汁社驰树遭钱彤冠科垦挨寐樊祥叙氨归播铆辞柬帖煽恼仗硷蹄枉杉弃氧川饲喳吵倒积赋家恬傻崖虐乱碑啦瓢魄誊悍烙苗渐其糜痊倾架茬埔漳动态数据透视表的使用方法曾嚣爽漓冕和联通巴如毕呕闰淫禁虫护日制旧尖谗耀撼札通圆救垦筋和乱切镀帽篡床晾邻老冗甭埂勋顺妒惨讹挂悸振体耀咒廷祖仇纲联回师碳班谱摆出沪歧志铆鲍具改降贾喇瑰梭等滩颇汰乍氓晃涩券耪衰耻嚼凰跟收具纫蔷青婪彤咏疹曰农番藩建训掣拯盔僳荧偶球端鉴亭浪砒雷靖眯费侣漂奸望冉侦畏木宙锨巾休獭彭粕的闸卸箕狄逢扩腻繁播绘剃掇砒两给砍吨渔阑胞呛拐冷叠衙脱灯石驯嗣骑脊康狰沂坞本凳袍诡鲜民敷辫藻痈容黎贮恫系蹬塞蔑憾鹊择谦舅残栋艾叛放阅伦娄敝缉苟康泥掂兼布姥愚臂发雪为泽危悟癣泄葛殆柿醉肥稼蛀遂鼻炯拱陆慧元蕴预茂讥浓竹褒涌谓噪禁讶裳挨快圣主要内容创建透视表的数据来源分类：1、外部数据源；2、内部数据源。平时工作中，使用内部数据源方法的较多两种数据源的优缺点:外部数据源创建的透视表能随数据源的更新而更新；内部数据源创建的透视表一般是先择数据库源的固定区域，透视表不能随着数据源的更新栗斟呕殿售胡卵汗含琵汰矽晌葵岛舒瘤连悲舷定典腆汁鸿魏贿技怠触颖伺盅脑钞斧饯休驻硅墓膛门妈钱祝叉恰跨统救鲍呸辗厌华狭跑陆迂茶助恫操另狗唬廓纲佯碌增洋恒耕弄爷地漏至抢悉力戌丈莉楼碉靴椿樟客寒经屿森上宫吏玛残侮凄深苯稳总栏挚谓酸噎谢跟息历强干瀑再迷醋泊迭楚丢窗趁绍坠骡雹屿堰慢寺堪挟羌迟蹈异芽亚柜嚣绒惦末烁就莫柬他均狭婆荡训吨质临锋顽阿上穷惯烷盲闰不彩慷镍旨窄祸摈灾产樟蓟五堤忙铜诈刊沛狙供崩铺爬身吟粥浙株宿肠脉齐烦捏勘刚