excel 数据透视表 数据源,透视表如何更改数据源

EXCEL数据透视表可以说是EXCEL数据分析的利器，简单几下拖拽就可以实现从多维度的透视数据。可以说EXCEL数据透视表大大简化了我们的工作，也降低了数据分析的门槛。

回想不会EXCEL透视表的日子里，我们要先手工建立汇总的表格，行上放什么，列上什么，然后再通过各种统计函数（sum，sumif，count，countif，vlookup等）汇总数据。

这样的方式统计工作巨大，一旦领导看数据的角度有变，又得重新设计表格，重复上面的步骤。另外，很多人一看到那些公式就头痛。

自从有了EXCEL透视表，一切都变得简单。简单拖拽，即可生成透视表和图表。再配合数据源，即可实现数据的动态更新。当新数据添加进数据源，一键即可刷新透视表，再也不用重复做表格了。

因此，EXCEL透视表的数据源非常重要，非常重要，非常重要（重要的事情说三遍）。

总体来说，数据源就是类似一个数据库的数据表。有过数据库使用经历的同学可能知道，我们在设计一个数据库的表的时候，有很多的规则和约束。例如，字段名，字段数据类型，是否为空，索引等等。

不了解数据库也没关系，接下来我们就来探讨一个合格的EXCEL数据透视表的数据源需要达到什么标准，以及其中的原理是什么。我尽可能以案例的形式给大家展示，便于加深印象。

强烈建议收藏本文，避免以前都踩过的坑，也可以分享给其他人。

一、不允许多层表头

我们很多时候设计了比较复杂的多层表头，但是在作为EXCEL透视表的数据源是不允许的。因为很多多层表头进行了横向单元格合并，使得EXCEL无法识别标题行（更确切的说是字段）。因此，只能允许单层标题行。

那么如何处理比较好呢？自然是单行标题行。改进后的效果如下：

二、标题行下的数据数据类型要统一

虽然EXCEL中未明确定义数据类型，或者强制要求单元格数据类型。但是，作为EXCEL透视表的数据源，每个标题内容（字段）下面的数据要做到统一，否则在透视数据的时候会出问题。

为什么要统一呢？其实这里面涉及对EXCEL数据类型对象的深入理解。简单来说，EXCEL数据类型分为两类：文本型和数值型。

可以用于加减乘除等数学运算即为数值型，其它为文本型。文本型主要是由于定义和描述事物的名词或者形容词等。例如，身份证号看似是数字构成的，但其实是文本。因为，你用身份证号加减乘除好像没有什么意义。

而数值型又可以细分为日期、时间、逻辑和单纯数值。具体区别可以参见我的另外一篇文章《闻香识女人，EXCEL你需要洞察的数据类型》。

1.数值文本化会导致计算错误

订单数量就是应该是数值型，因为可以计算。但上图中，在每个单元格左上角有个绿色的三角形表示的是这个是个文本。会出现什么错误呢？

可以明显的看到，我们无法对文本型的数值进行汇总，即订单数量汇总居然为0。因为，你告诉EXCEL这是个文本，那EXCEL就不汇总了呗，只能是计数。

如何调整呢，首先我们要改变其数据类型，可以通过选择性粘贴予以快速处理。

可以看到数据可以正常汇总了。

2.数据中多行标题也是不允许的，也会破坏数据类型的不统一的原则。例如：

这种要去除多余标题行，留第一行即可。多余的标题行处于数据行中，都是文本，会破坏一列上的数据统一，从而导致汇总的数据为0。例如一列中又有数值型，又有文本型，然后EXCEL透视表是无法将不同类型的数据汇总。

3.数据区域中不能有空行，需要提前做处理

要通过筛选，删除空行数据。

4.不能有重复的记录

这一点很好理解，有重复记录会影响最终的透视结果，导致结果虚增。

三、数据行间相互独立，不能有联系

1.数据源中不能有小计或者汇总行存在

类似于以下这种小计或者汇总是不被允许的。这里要说另外一个概念。其实每一行叫一行记录，行与行及即记录与记录之间应该是单独存在的，便于后续在汇总的时候不出差。而小计或者其它汇总行的出现破坏了这一原则，即某一行记录依赖于其它行的记录。

2.数据行汇总不允许有合并单元格的情况出现

这个也违背了数据行间需要保持独立性的原则。合并单元格的实质就是在视觉上貌似进行了合并，在数据层面是将很多单元格进行了隐藏。对于被隐藏单元格的数据行而言，这个隐藏的数据就是空值，对后续的透视汇总产生影响，导致得不到正确数据。

A区合并单元格是横跨D2到D7的单元格，但是当选中的时候被显示成了D2，即D3到D7都被隐藏了，形成了实质性的空行，透视会报错。

透视得到了一个空白行的汇总数据，A区、B区的汇总都是错的。

如何处置呢，自然要将合并的单元格恢复原状，即取消单元格合并，并填充正确的值。

操作方式是选中要取消合并的单元格，点击合并单元格，F5键调出定位菜单，选择空值，在当前单元格输入等号，然后选择上方单元格，CTRL Shift Enter三键齐发，复制当前单元格的公式到其它空值单元格，最后选择性粘贴进行选择数值进行公式去除工作。

四、字段名不能重复，最好不为空

这个不再赘述。字段名重复会导致EXCEL搞不清楚哪个是哪个。空的字段又代表什么呢。所以说尽量避免。

五、能放一个表格的数据不要放多个工作表

例如我们有很多销售明细表，一个月一个工作表。这样的话就会导致我们只能透视一个月的数据，无法透视全年的数据。

正确的做法就是，将数据整合成一个表，按照上面说的规则。每当我们需要新增销售数据的时候，就在这个表下面继续添加新的数据即可。这才是正确数据存储之道。类似于数据库的表设计。

应该是这样：

好了，今天暂时分享到这里。

很多时候，我们需要养成良好的习惯，才能使我们的工作事半功倍。同时，我们不能学表面功夫，还需要理解问题的本质。只有知其然并知其所以然，我们才能够更为精进，后续遇到问题才能举一反三，快速处置。

我是华哥。每日精进，不负光阴韶华。

赠人玫瑰，手留余香。欢迎评论，转发。