王佩丰数据透视表（一到五讲）

创建数据透视表

选中数据区域，然后插入数据透视表即可。

自定义字段到透视表中，双击值字段中的数据，可以跳转到该数据的数据源。

避免源数据泄漏：复制粘贴时仅粘贴表格中的数据。

如果删除掉字段中的某个项目，下拉框中还会显示出来。

比如以部门为行字段，删除掉源数据中的四科，

下拉框中还有，此问题可以在数据透视表选项中解决。

创建数据透视表后默认有行列的总计，也可以通过数据透视表选项除去。

嫌筛选字段竖着排列不好看？

通过数据透视表选项可以修改成水平排列。

当行字段有两个以上时，可以通过工具栏的设计——报表布局来调整布局。

——

通过组合可以对字段进行组合。

举个例子，行字段是月份，通过组合可以将每四个月份组合成一个季度。

——行字段就变成日期了。

也可以对数值进行组合。

如将金额作为行字段，设置区间为0到320000，步长为40000，值字段是金额

在值字段放入多个金额字段可以实现不同的计算结果。

在值字段设置处可以设置值汇总方式和值显示方式。

文本字段也可以进行组合

——

选中要分类的类别，然后右键分组，再改个名字就行了。

创建一个辅助列

例子，先在源数据处根据日期创建旬辅助列

然后用这个旬列作为列字段即可。

有时候统计数据，可能不是从每月1号开始，比如要求统计每月5号到下个月5号的金额数据，此时也可以通过建立辅助列来实现。

统计的就是每月5号开始的数据。

如果想处理不等步长的数据，也要通过辅助列，比如统计满意度评分的分布的个数，0-60一档，60-80一档，80-90一档，90-100一档。

如果数据中有空白值，会导致该数据无法分组。

如果有这种情况，可以通过定位空白值选中空白然后向上填充。

如果日期里有文本型日期会导致无法组合，可以使用辅助列转化为正常的日期。

再使用辅助日期就可以进行分组了。

还可以通过工具栏的分列工具给文本型日期列分一下列就可以转为正常的日期，操作更简单。

如果金额列中的数据都是文本型的，会导致值字段无法求和，办法也是用辅助列。

值字段用金额辅助即可。

值字段显示方式可以改变数据的显示方式。

通过值显示方式的设置可以对比两年的数据变化。

设置方式为

如果要显示具体差值

计算字段和计算项

值字段有金额和成本，可以据此通过计算字段搞一个利润和利润率列出来。

如果计算字段的结果中有错误值的话可以通过数据透视表选项来设置不显示。

列字段有预算和实际两个属性，值字段是金额，通过计算项可以得出两者的差值

还可以在计算字段中插入函数

例如，根据金额和成本计算出了利润，根据利润的大小有不同的返点。

=IF(利润<1000000,利润*0.1,IF(利润<2000000,利润*0.15,利润*0.2))

求解次序是由下到上

先算下面的，再算上面的。

排序方法，除了常规的升序降序，还可以手动拖动字段标签进行顺序变化。还可以在自定义序列中自行设置排序方法。

xlsx格式的文件才能在数据透视表中使用筛选，xls的不能。左xls，右xlsx。

使用报表筛选页字段可以制作动态图。

先将月份放入行字段，金额和成本放入值字段，再将区域放入页字段。

选中数据透视表制作折线图。

此后修改所属区域，折线图会动态调整为该区域的数据。

使用切片器（只有xlsx格式才能用，xls格式不能用）

先创建数据透视表，工具栏的分析处有插入切片器选项。

选择指定字段即可创建切片器。

根据数据透视表中的数据创建折线图。

此后可以通过选择产品类别中的标签展示不同标签的数据。

工具栏的切片器工具可以对切片器的参数进行调整。

还有一种玩法是通过一个切片器控制多张图表，图表要来源于同一个数据源。

同一个数据源，创建两个不同的透视表和图，将图移植一个新工作表中并根据产品类别创建切片器。

右键切片器选择报表连接，选中两个报表，即可同时操控两张图。

数据透视表中有0值时，可以通过数字格式的自定义进行处理。

或者条件格式

在数据透视表中使用条件格式。

比如通过数据条描绘数值大小

通过条件格式达到这种效果

先根据数据插入一个数据透视表

值字段再引入一个金额，并调整列顺序

第二个值字段设置为差异，基于2009年的数据，然后将第二个金额字段的2009年列设置为隐藏，通过自定义格式将2010年改为差异。

最后通过条件格式标记最后一列。

条件格式还有其他用法，比如箭头