外部导入数据不能求和之浅谈-EXCEL“懒惰性”

hai593

    外部导入数据生成透视表，以其强大的功能，号称透视表杀手锏。其可以跨工作表、工作簿生成透视表，完成大量数据分析。

我们在使用数据导入外部数据建立透视表的时候，经常发现，生成后的数据不能求和。

也许，我们会认为这是在数据源中存在文本数字，这种想法是对的，可有时候当我们费尽精力去查看时，数据都是数值格式啊，为什么也不能求和！其实，不是我们查看不细致，不是我们数据有问题，而是EXCEL在偷懒！为什么这么说呢？我们来以一个简单的表揭示这种现象及原因。

一、神秘现象。

请看这个数据，一定看仔细了！

利用SQL 语句SELECT "Sheet1",* FROM [Sheet1$]

生成的透视表如下：

数据源备用金项明明可以求和，而在透视表中却为什么不能求和且不能计数，要是格式不对，起码也应该可以计数啊。这是为什么？

再看一个数据，请仔细看！

利用SQL 语句SELECT "Sheet1",* FROM [Sheet1$]

生成的透视表如下：

是不是很纳闷啊？两个“一摸一样”的数据源，为何生成的透视表却大相径庭！难道是我们操作失误，还是上天戏弄我们？其实这是EXCEL在戏弄我们，因为这里曝露出EXCEL的一个“懒惰性”。

hai593

二、解释原因
其实这是数据透视表缓存造成的原因。数据透视缓存包含字段定义，以及有关该字段中包含的内容的信息。简单的说，数据源再生成透视表之前，数据源先被“吸进缓存”，在缓存的基础上生成数据透视表。在“吸进缓存”的时候，问题就出来了。
我们利用导入外部数据生成数据看一下，生成透视表的缓存数据，到底是什么。
不能求和的数据源，导出缓存数据如下：

是不是发现，备用金字段下面没有数据。正是没有数据才导致，生成的透视表不能求和或者计数。

再看一看可以生成透视表的数据的缓存数据：

是不是发现，备用金字段下面有了数据。正是有数据才使生成的透视表可以求和或者计数。

是不是还在纳闷？为什么呢？

hai593

三、深度揭秘
这两个“一摸一样”的表为什么导致这样的结果。为什么缓存数据大相径庭！而导致生成的透视表天壤之别！
其实这两个表是有微弱差别的，正是这点微弱差别，导致结果不同。正所谓失之毫厘差之千里。
看一下对比的数据源：

看到没有。不能求和的数据源最后一个数值在第10行，能求和的数据源最后一个数值在第9行。EXCEL偷懒的地方就在这。如果，数字的上方有9个空格，则，EXCEL会认为这一列都没有数据；如果，数字的上方有8个空格，则，EXCEL会认为这一列都有数据，再往前走一步，EXCEL就发现数据了，但是，他“太懒”了。

四、解决

解决EXCEL的懒惰，其实也很简单，只要在备用金下的单元格填充一个0即可。这个0就相当于一个路牌，告诉EXCEL。这列数据下面是有数据的。

如图所示：

以上只是个人见解，若有不足之处，望各位指正。

能求和透视表.rar (4.1 KB, 下载次数: 65)

2011-8-19 11:48 上传

点击文件名下载附件

不能求和透视表.rar (3.84 KB, 下载次数: 56)

2011-8-19 11:48 上传

点击文件名下载附件

wuxiang_123

这个扫描行数是用数据源引擎决定的，不是由于EXCEL偷懒。看下图：
打开控制棉板-管理工具-数据源（ODBC）

选择EXCEL FILES，单击右边的配置：

点击里面的选择，可以看到，引擎默认的扫描行数是8行。这个是用于说明数据源字段类型的确认，是通过扫描该字段的前八项来确认。我们可以将此值修改大一些，也可以设置为0。设置为0，意思是对该字段的所有项都扫描来确认字段属性。
但是，要注意的是，这种方法并不能百分百保证可以将字段类型识别为用户想要的字段类型。
除了这种方法，我们还可以使用IMEX=1来将字段强制转换为文本，但是，IMEX=1的结果不可预测，同样无法百分百成功。
所以，我们在录入数据时候，注意字段不能出现混合类型的记录。最好也不要出现大量的NULL。NULL表示未知值，会影响字段类型的判断。
不过，在EXCEL2010中，似乎已经修正了这个BUG，假如是字段除空值外都是同一类型的记录，那么，就能准确识别。有条件的可以在EXCEL2010中测试。

hai593

吴版，研究深啊。
多谢指正{:soso_e181:}

panan123_0

用外部导入数据做的时候SQL语句用字段 *1  /1  -0 +0 啥的就可以了。
像你这个表用     
备用金*1 AS 备用金  
就可以啦。

hai593

panan123_0 发表于 2011-8-19 15:09
用外部导入数据做的时候SQL语句用字段 *1  /1  -0 +0 啥的就可以了。
像你这个表用     
备用金*1 AS 备用 ...

多谢指教。这个解法我试了，不可行。 不能求和透视表.rar (3.65 KB, 下载次数: 35)

2011-8-19 15:54 上传

点击文件名下载附件

wuxiang_123

panan123_0 发表于 2011-8-19 15:09
用外部导入数据做的时候SQL语句用字段 *1  /1  -0 +0 啥的就可以了。
像你这个表用     
备用金*1 AS 备用 ...

需要注意的是，利用SQL构造数据源，其过程可以看作：原始数据源-----利用导入外部数据源对原始数据源进行处理，得出各字段的基本类型、属性，产生新的数据源------SQL根据第二步产生的新数据源作为直接数据源进行再改造。而你的方法，实际上是忽略了第二步，错将原始数据源当作是SQL语句的直接数据源。需知道，假如第二步中，你的原始数据源含有混合型记录，那么，与外部数据源认定的字段类型不符的记录，导入外部数据源将会将其处理为NULL。
举例：某字段含有空值和数字，而外部数据源认为该字段为非数字类型，那么，该字段的数字将会处理为NULL。然后根据你的四则运算进行计算。相当于NULL与四则运算计算。NULL表示未知值，任何与NULL的运算，其结果也是未知的。所以，在这种情况下，利用四则运算并未能改变字段类型。数字已被处理为NULL。
那么，什么时候可以利用四则运算，将文本数字改为常规数字型呢？当导入外部数据源认为你的字段是文本型，且字段只有文本型数字而不含文本时，就可以将文本形数字转化为常规数字。
那么，为什么既有文本数字又有文本时，为什么不能用四则运算转化呢？这是因为，文本并不能参与运算，假如使用四则运算的话，SQL则会返回错误提示：无法从数据库中提取数据。

Tony_Lin

这个原因还是数据库的特性来决定的，因为一个列字段，只能存在一种数据类型。
使用SQL的时候，就相当于把Excel当成一个数据库了。
但是存在混合字段的时候，这就违反了数据库的原则。
对于使用四则运算的时候，应该是可以的转化的。
就比如在Excel中使用函数时候，不也是使用了--这个常见的文本转数字的原理么？

hai593

Tony_Lin 发表于 2011-8-20 00:36
这个原因还是数据库的特性来决定的，因为一个列字段，只能存在一种数据类型。
使用SQL的时候，就相当于把E ...

如果导入的是文本型数字，这个是可以转化。
但是，上面的例子，就是这个方法不能解决的案例，因为它存在空值。
你说的，根据数据库特性，一个列字段存在一种数据模型，个人感觉很对。
但是，有很多时候，数据库导出的数据，是含有空格或者换行符的数据。这时候，任凭怎么处理，只要不把空格或者换行符删除，这些数据是不能进行任何运算的。