ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 10789|回复: 8

利用select union all合并工作表并生成透视表的问题

[复制链接]

TA的精华主题

TA的得分主题

发表于 2012-11-26 16:06 | 显示全部楼层 |阅读模式
本帖最后由 kevinseagal 于 2012-11-27 15:46 编辑

在一个工作薄中有6个工作表。每个工作表中都有一一对应且相同的字段。欲由所有工作表共同生成一个数据透视表。由于“多重合并计算数据区域”所产生的透视表有很多限制,于是采用,select union all语句。问题:合并后的透视表中,有三列数据中的两列或一列数据不正常。默认字段格式为计数项,改为求和项后所有值为0.
如果分别将每个工作表单独生成透视表,则所有数据都正常。尝试将6个工作表合并生成一个表后发现,在三列数据列中,总有1列或2列的数据格式不正确,且在生成的表中无法批量修改数据列的格式。回头逐个工作表,逐个数据列,逐个数据单元格检查,所有单元格的格式均为数字格式,但一旦合并成一个表后,就不正常了。请问有没有高手能够解答?谢谢!

同时还有一个怪现象,如果select语句采用完整表述,则在合并6个工作表时可以合并成功。如采用通配符“*”时,则无法合并6个工作表。逐一尝试,发现从合并2个工作表开始,逐渐增加,从2个工作表到5个工作表合并都正常(其中一个工作表合并后数据格式显示异常),但一旦添加上最后一个工作表时就无法并了。并出现如下提示 捕获66.PNG

本人已摸索出一些替代性的解决方法,请看4楼下文。

入库清单.rar

85.56 KB, 下载次数: 190

问题工作薄及语句

TA的精华主题

TA的得分主题

发表于 2012-11-26 16:16 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
sql 中 count 只对有数据的记录计数而忽略空值

count(1,2,3,5)=4,count(1,2,3,null)=3

但是sum不会忽略空值

sum(1,2,3)=6,sum(1,2,null)=null

TA的精华主题

TA的得分主题

发表于 2012-11-26 16:23 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
如果某个字段允许为空的话,可以使用以下函数

sum(iif(col is null,0,col)
sum(nz(col,0))

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-11-27 14:53 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
接上文。通过进一步摸索,基本上可以判定是工作表合并后部分数据的格式发生了变化导致了问题的出现。选择了两个内容较少的工作表,重建并重新输入内容后,合并且直接生成透视表,正常。但对于工作表较多或内功较多时,此方法毫无意义,且如果是由别人已做好的数据源制作透视表也不可能采用这样重做的方法,于是继续尝试相对快捷的方式。
       由于判断问题是格式的问题,于是将原数据源中各个工作表逐一用 开始-编辑-清除-清除格式 后重新逐列设置格式,但问题依旧存在。之后发现一个非常奇怪的现象,尝试将6月和11月合并生成透视表成功,但以6月分别组合7、8、9、10月工作表时,生成的透视表,入库量、出库量和库存量中总有至少一个量是不正确的。且透视表中数值不正确和合成表中数据列格式不正确是对应的。
      由于无法找出产生这样现象的原因,只好设法找到变通的解决方法。具体如下:1、采用select union all 语句将6个工作表合并成一个表。(合并时只能用完整字段名语句,使用通配符*语句无法全部合并,同前述)。2、在生成的表中,选择格式不正常的列,点击 数据-分列,分列后可能修改整列的格式。根据数据列的类型调整格式。3、全部数据列格式调整完后,选择整个工作表,复制,新建一个工作表,点击选择性粘贴-值和数字格式。4、最后在新建并粘贴的工作表的基础上建立透视表。之所以需要在第3步中进行复制粘贴,是因为在最初合并的表中,虽然用分列及修给格式可以得到正确的数据列格式,但一旦在表中刷新,或在透视表中点击全部刷新,那么修给过的数据列格式又会不正确。因此只有用此方法过渡。
      上述方法虽然解决了由多个工作表生成一个透视表的问题,但如果需要经常刷新数据的话,还是很麻烦。本人已尝试了将原工作表中所有数据列进行了格式清除-格式重设-分列。但仍无法解决问题,希望能有高手分析、指点。

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-11-29 17:27 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
kevinseagal 发表于 2012-11-27 14:53
接上文。通过进一步摸索,基本上可以判定是工作表合并后部分数据的格式发生了变化导致了问题的出现。选择了 ...

都是格式惹的祸。

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-11-30 12:31 | 显示全部楼层
kevinseagal 发表于 2012-11-29 17:27
都是格式惹的祸。

想骂人!!!

TA的精华主题

TA的得分主题

发表于 2015-4-2 11:47 | 显示全部楼层
select *,"A"  as 合同  from [E:\2011\业绩\A`.`合同$A5:CF130]
union all
select *,"B"  as 合同  from [E:\2011\业绩\B`.`合同$A5:CF130]
union all
select *,"C"  as 合同  from [E:\2011\业绩\C`.`合同$A5:CF130]
union all
select *,"D"  as 合同  from [E:\2011\业绩\D`.`合同$A5:CF130]

这个是我的 透视表的,非常好,A BCD 是部门 或文件名,但是其中的  `.`合同$A5:CF130不太理解

TA的精华主题

TA的得分主题

发表于 2021-9-13 09:54 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-12-25 22:10 , Processed in 0.037930 second(s), 11 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表