数据透视表应用技巧

第一章数据透视表应用技巧

1、什么是数据透视表和数据透视图

数据透视是一种可以快速汇总大量数据的交互方式。使用数据透视表可以深入分析数值数据，并且可以回答一些预计不到的数据问题。数据透视表是针对以下用途特别设计的：

(1)以多种友好方式查询大量数据；（PS：友好方式即不破坏数据源）

(2)对数值数据进行分类汇总，按分类和子分类对数据进行汇总，创建自定义计算和公式；

(3)展开或折叠要关注结果的级别数据，查看感兴趣区域的数据明细；

(4)通过行列交互移动，以查看不同的数据源汇总；

(5)对有用的数据子集进行进一步操作。

2、数据透视表和数据透视图的创建：

（1）我们打开某个需要进行数据汇总计算的EXCEL表格，我们在红色框所标注的功能区标签栏中，选中插入系列命令标签，那么在首位置的图标即我们所需要的数据透视表和数据透视图创建图标，然后我们点击选取数据透视表

在创建原始表格的时候，需要注意以下几点：

①整个数据表的第一行，也就是被成为表头的一行，其单元格不可以为空，因为这一行

将作为数据透视表的数据字头使用。

②数据透视表的数据选择时，尽量使用CTRL+SHIFT+方向键的快捷组合选择，如果单纯的

点击行标或者列标或者左上角的全选标记的话，那么在你数据透视表的最后输出中会有“空白”一项出现，空白项所汇总的即为你选择的无效区域；当然你如果不需要二次加工的话，那么可以保留空白项。

③完成数据透视表后如果数据源需要改动的，在改动完之后要在数据透视表输出工作片上

所出现的专有命令框中点击“刷新”选项，具体位置见后图。

（2）选取之后就会弹出向导对话框要求对数据的选择范围和新建输出区域的定义（当然在你对数据透视表操作很熟悉了之后，我们完全可以跳过向导框进行数据透视表的操作），其中红色框部分表示你要选择的区域参数设定，而绿色框部分表示新建输出区域的参数设定，

你可以直接点击向导框对应的输入区域后进行选择，也可以点击图标后进行选择。

数据选择向导框

3、我们可以快速建立向导模块：

（1）第一种方式：

点选左上角的常用命令图标，点击EXCEL选项，如下图：

在选项表里点选自定义，并在命令位置选择框里点选“不在功能区中的命令”或者“所有命令”，找到数据透视表和数据透视图向导后添加。

添加后，这个命令会在快捷命令栏中出现：

那么点选快捷命令就可以了。

（2）第二种方法：按ALT+D+P直接调用。

3、数据透视表和数据透视图的模块介绍

选择好区域之后我们就进入了数据透视表的操作界面，整个界面的布局如图所示，各个模块的基本功能如下：

1)新增命令标签区域：EXCEL自动搜索数据透视表模块下的命令并整理成命令标签，

用于各种数据透视表操作命令的查询与操作。

2)字段显示区域：显示数据源的字段情况，你可以点选复选框进行字段计算，前提是

你要设置好正确的安排方式，安排方式在右上角选择，当然我不推荐这种方式。

3)字段操作区域：直接拖拽字段显示区域的字段到你想放的位置就好了。

4)数据输出区域：查看结果的区域，也可以进行拖拽和修改操作，集合了字段操作区

域的功能。

其中新增命令标签区域会罗列你可能用到的仅仅是关于“数据透视表”功能的相关命令，当你点击数据透视表操作区域外任意位置时，这个命令标签会被隐藏，同样你可以在使用诸如图标、绘图都功能时看到它。

新增的命令标签如下所示：

“选项”标签所罗列的是你平常会用到的修改数据透视表标签或数据的行为命令，其中大部分命令你可以在熟练操作后直接在输出区域上做出修改，而“设计”标签所罗列的命令

数据输出区域

新增命令标签区域

字段显示区域

字段操作区域

则是关于输出表格的布局和美化功能，你实现相应功能的时候，则必须在命令标签区域中操作。

具体模块命令作用如下：

“选项”栏：

1)数据透视表名称：你可以直接修改名称，同时可以在选项中设置本表的一些默认值。

2)活动字段：即输出区域中的字段添加或删除，字段值属性修改和设置，我们完全可

以通过拖拽完成，所以这个命令区域你现在可以不去关注它。

3)分组：你可以将数据透视表计算后的任意连续区域设定成一个数据组进行标记，以

方便查询，当你不需要这个数据组之后，你点选好组名称后点击取消就可以了；当

然这个命令标签在你没有任何数据的时候是无法被激活的。

4)排序：常用功能之一。是对已经汇总完的数据进行排序，但请注意的是，排序的依

据除了简单的数据因素外，还关联着标签的逻辑因素，也就是说，当你存在多个行

标签且逻辑排列顺序不一致的时候（如商品编码和商品名称，一个按数字排，一个

按字母牌），排序功能就无法在数据输出区域体现，除非你把整个数据区域复制并

选择性粘帖到新的区域后，再进行该操作。

5)刷新：即数据源发生变动后，需要点击刷新完成输出结果的更新，当然在默认设置

下，你重新打开工作表也会起到刷新的作用。

6)操作：基本操作，不再赘述。

7)工具：你可以通过数据透视图完成图表创建，具体功能跟图表向导是完全一致的；

公式选项可以让你对某行或某列单元格的计算方式做出修改，当基本的算法无法满

足你透视需要的时候，你可以在这个命令标签里修改它，有兴趣的同事可以在了解

函数操作后自己尝试下。

8)显示/隐藏：基本操作，可以直接通过在单元格上单击右键完成。

“设计”栏：

1)分类汇总：选择是否自动汇总，一般不使用。

2)总计：选择是否对行和/或列进行总计，一般不使用。

3)报表布局：常用命令，用于修改报表的输出方式，一般阅读习惯上会选择表格形式。

4)空行：常用命令，最简单的加入或删除空行的命令。

5)数据透视表样式：美化系列命令，可自行探索。

2.4数据透视表和数据透视图的基本应用

下面我们就来学习数据透视表的基本应用，也就是其数据汇总计算功能的应用，如下图数据，我们从系统当中检索出了1-6月内配门店的所有销售清单数据共305607条，针对如此大的数据源，我们如何处理出我们想要的数据呢？

看到这里，先不要考虑傻不傻的问题，有人说你要这个数据做什么呢？看销售拉个报表不就行了？这里先卖个关子，因为你会发现，最原始的数据是最有价值的，其他的应用我们将在第四个模块中介绍。

让人崩溃的数据量

好了，现在我们要完成一个要求，就是按门店把其1-6月的销售额统计出来，我们应该如何做呢？

最直接的办法就是，把我们想要的数据“放到它本应该在的筐子里”，你可以通过拖拽完成这个放鸡蛋的过程，比如为了完成上述要求，我们把销售额放入述职区域，把网店放入行标签当中就可以了，结果如下图。

这时你应该注意到以下几点：

1)数据的输出时，在其标签上都会显示算法，如图中的算法就是计数。

2)如果要更改数据算法，你可以在任意一个该类数据的单元格上单击右键，从命令菜

单中选择值字段设置来完成更改，也可以通过左键单击字段操作区域的字段名称后选择值字段设置来完成更改。

3)新增命令标签模块中的绝大多数命令，你都可以在任意一个有效区域内的单元格上

单击右键来获得命令列表完成，右键点击的同时，会出现字体修改命令集，当然你也可以在EXCEL选项中修改这种设置。

4)当你选中字段操作区域中的推迟数据更新时，旁边的更新命令会被激活。因为在

EXCEL的默认操作中，每当有字段布局调整时，就会自动更新数据输出，当你的机器计算能力相对较弱的时候，这种更新会重复消耗计算时间，所以你可以在完成全部布局的时候进行一次性更新，前提是你对布局把握要准确。

5)如果报表计算量较大，你会在最下面的提示栏中看到诸如“正在计算单元格”的提

示（如现在是“就绪”，表示没有计算进行），当提示没有结束的时候，不要认为是死机了或者挂掉了，请耐心等待计算完成。

好了，上面就是我们的初步输出结果，现在我需要的是计算汇总金额，那么我们就来更改下字段的计算方式，更改的方式我们前面已经说过了。

单元格上点击右键后出

现的命令菜单和字体修

改命令菜单

这是我们更改为求和后的结果：

点击值字段设置后出现的设置内容，你可以按照想法和汇总要求选择

当我们想看某一个数据的明细内容的时候，我们在对应的数据单元格上双击鼠标左键，就会出现一个新的工作片，并筛选中你所需要的所有数据。

我需要查看401的交易

明细，那么就在401对应

的数据单元格双击鼠标

左键

双击后出现新的工作片，

明细就在这张新的工作

片上，如果需要看输出表

格，我们只要选择回

SHEET2这个工作片即可

EXCEL提供了强大的数据计算支持，你可以在任意一个标签的下拉箭头处（倒三角）进行复选式筛选，这个功能不再抓图，请各位同事自己验证。同样图表的绘制功能跟图表模块完全一致，具体操作请参见图表功能介绍。

2.5 数据透视表和数据透视图的扩展应用

没人会否认EXCEL是一个强大的工具，它可以用来做打卡考勤机、可以用来做数据的人机交互、甚至可以做动画！所以工具的强大不在于本身，而在于人如何去运用它，作为EXCEL 的功能来说，没有过多介绍的必要，因为它本身就跟炒锅、漏勺一样，在此我将分享几个案例，启发大家通过解析逻辑，运用工具，将复杂的命题变简单，从而体验到创新并不是一部分人的专利和乐趣的道理。

在扩展应用里，我将例举出两个普通案例跟一个命题案例来说明。

2.5.1 普通案例一：信息管理及整合。

案例内容：工装订制统计及发放记录。

如下，假如这是我们的数据源：

现在我们要完成以下工作：

1)工装按部门，分性别进行统计

2)对发放的情况和不符情况进行反馈

首先我们假设在统计阶段，即没有完成发放阶段，完成统计的话，我们如下图所示把相应字段放置相应位置即可：

其中列标签所显示的就是部门，同时为方便阅读，我们把性别和号码项放置到行标签中，数据区域中放置件数即可。

这样就可以显示出男女工装每个型号需要做多少件，其中部分分配为后续工作，为方便逻辑理解，我们就放在列标签中，仅为阅读使用。

好了，订做完成了，然后我们选择了在数据源中记录发放与不符，现在需要进行反馈了，反馈结果如下：

现在我们要统计少发和调换的数量，其中调换的要显示出号码和对应的数量，我们就可以按下图进行数据透视：

同样为了方便阅读，我们在反馈后的倒三角除把复选框中“无”的汇总分类删除，形成下表：

这就形成了我们需要的结果，当然这个表这个动态调整，以方便你进行各种查询。

2.5.2普通案例二：多个数据区域的数据透视。

案例内容：多个月份的销售记录整合透视，汇总计算。

假设下面是门店口腔某小类的三个月的销售记录

我们现在需要对三个月的销售总记录进行汇总，当然基于多重数据区域的特点，EXCEL 会默认首列项为汇总项，即在上图的情况下，我们能准确计算的是单店的销售总额这一数据。

我们同样使用数据透视表向导完成，向导要点如下：

步骤1选择多重合并计算数据区域。

步骤二创建单页字段，当然你也可以自定义不同筛选范围的自定义字段。

第三步我们选择每一个区域，每当选择完毕后点击添加，然后选择第二个，如此往复后我们把三个区域都选择完毕了，如下图。

点击完成后就出现结果了。

这个结果的被汇总区域是联动的，也就是说，原来有效的二级汇总项变成了现在的三级汇总项，所以无法进行单独透视了，因此在上表结果中规格、商品编号和全称等项目已经变得没有意义了。

这个案例只是带你了解下多重数据区域的合并透视，它是快速计算多个工作表中的某一数据的好办法，同样它也只是运用了SUMIF和COUNTIF这样两个函数的计算逻辑。

2.5.3命题案例一：商品的共性计算或商品的分销计算。

案例内容：通过准确的库存记录或销售记录，完成分销或共性商品统计。

我们现在接到一个课题，就是统计一下成人牙膏在佳乐家系统的分销情况，因为我们没有库存数据，我们做一个假设前提，就是假设1-6月内有销售数据的认为即有分销，那么我们就用到了前面例子中庞大的数据（利用库存统计的思路是一样的过程）。

我们的数据源如下：

数据透视表的优势在于不破坏数据源，所以我们就没有必要对非成人牙膏的销售数据进行删除了，这也是数据透视表最优秀的功能之一。

我们按上图进行了一次数据透视，然后我们把现有的结果复制并选择性粘帖到另一个工作片上，作为二次数据源进行处理。

在这里，为了二次数据透视的需要，我们要把每一条商品信息填写完整，所以我们用IF 函数来完成填充，具体解释可以参看函数介绍相关模块。

经过对公式内容的复制和选择性粘帖，以及简单的布局调整，我们很容易得到上面的结果，为了避免赠品的影响，我们通过筛选把金额为空和为0的条目都删除。以放置影响分销

的统计结果。

然后我们进行如下二次数据透视。

在网点的复选框中，我们只勾选目标分析门店的标号，为了在下面的结果中可以让大家看到全图，我只勾选5个门店，并在后面的整理中只保留10个单品，实际操作中多门店是一样的。

这样我们就得到了一个如上图所示的结果，就是每个单品每个目标门店在分析期内的销售金额，这个金额我们在假设中已经视同有分销了，下面需要处理的就是把单品的分销店数、单店的分销品数和共性商品的比率计算出来就可以了。

这是我们对二次数据透视结束后的整理数据，因为我只保留了10个单品，所以合计数肯定是错误的，下步我们的思路是让所有的销售数据变为数字1，这样我们就可以简单相加得出汇总结果了。

我们选定目标替换区域，然后在开始标签中找到替换命令。

我们只需要在查找内容中输入“*”号，在替换为中输入“1”就可以了，其中“*”号代表的是任意长度的字符串，它被称作通配符，同样“？”也是通配符，但是它只能代表任一字符。

然后我们把总计中的数字删除，通过求和完成公式汇总，得出如下结果，这个结果也会切合到分销标准或分销指导书的样式：

PS：作为严格的共性商品，也被认为是连锁经营当中的基本商品，也就是要求每个门店都要经营的产品，这个比率一般被业内认为在50%-60%的标准最佳，这样就可以通过集采优势带来利润的提升，很显然上图的共性商品严格的讲为0。当然作为共性商品的计算这一做法，还希望通过抛砖引玉的方式激发出大家的想法，以之完善商品管理的理论基础。