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

kevinseagal

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

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

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

amazeyeli

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

amazeyeli

如果某个字段允许为空的话,可以使用以下函数

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

kevinseagal

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

kevinseagal

kevinseagal 发表于 2012-11-27 14:53
接上文。通过进一步摸索，基本上可以判定是工作表合并后部分数据的格式发生了变化导致了问题的出现。选择了 ...

都是格式惹的祸。

kevinseagal

kevinseagal 发表于 2012-11-29 17:27
都是格式惹的祸。

想骂人！！！

jadyjjk

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不太理解

口口相传

MARK 学习中