excel数据分析 - 10个数据透视表的优化技巧

目录

数据透视表的注意事项

1 优化切片器

2 自定义透视表和切片器排序

3 同一透视表的刷新设置

4 透视表单元格的进度条形式

5 自定义透视表的计算字段/项

6 使筛选类别并排显示

7 多重合并计算数据区域

8 用数据透视表作非重复计数

9 设置万元单位的数据格式

10 加减按钮 隐藏/添加

---

数据透视表的注意事项

所用的数据源应该是一维表，即每列字段都仅包含一种数据信息；

不能包含空行和空列，数据源表的首行单元格应都有文本内容

1 优化切片器

① 隐藏无数据的选项

添加切片器 → 切片器设置 → 项目排序和筛选 - 隐藏没有数据的项

② 设置切片器列数

可在切片器设置界面，设置列数、按钮高宽度、外部高宽度

③ 报表连接

将不同的数据透视表or数据透视图，通过切片器连接，实现视觉动态的多个透视表联动

注：切片器的报表连接功能，需连接的数据透视表的数据源 完全相同

 ④  固定切片器的位置

 右键切片器 - 大小和属性 → 锁定纵横比 → 选中 不随单元格改变位置和大小

2 自定义透视表和切片器排序

• 文件 - 选项 → 高级 → 常规 - 编辑自定义列表

  

• 输入自定义排序，以东南西北为例 → 可用enter键隔开，或者用英文的逗号隔开 →确定

•  回到数据透视表 - 选中表头单元格 → 左键 - 排序 → 其他排序选项

•  选择  其他选项  → 自动排序 - 取消勾选  每次自动更新报表时自动排序
•  主关键字排序顺序 - 选择之前新增的“东区，南区，北区，西区”

•  刷新透视表，此时切片器和透视表都已经按照区域排序

3 同一透视表的刷新设置

① 透视表刷新后，首行的行高保持不变

• 选中透视表任意单元格 - 右键 - 数据透视表选项 → 布局和格式  - 取消勾选（自动调整列宽） ＆ 选中更新时保留单元格格式 

•  选中透视表首行所有单元格 → 设置自动换行 → 用空格键填充首行单元格 

② 设置透视表自动刷新

• 选中透视表任意单元格 - 右键 → 数据透视表选项 → 数据 - 打开文件时刷新数据

③  利用定义名称 自动更新透视表的数据源

• 公式 - 名称管理器 → 新建名称 → 名称输入自己想设定的表名，以下表为例， 输入“石榴卖出记录” 
• 引用位置 输入公式=OFFSET(工作簿!$A$1,,,COUNTA(工作簿!$A:$A),COUNTA(工作簿!$1:$1)) 
• 此处即 =OFFSET(CCC!$A$1,,,COUNTA(CCC!$A:$A),COUNTA(CCC!$1:$1))
• 即从A1个单元格开始，$1:$1即为第一行，如需要从A2单元格、第二行开始，则该对应的内容即可=OFFSET(CCC!$A$2,,,COUNTA(CCC!$A:$A),COUNTA(CCC!$2:$2))

• 随后往下输入新的行，刷新之前建立好的透视表 → 透视表会自动刷新数据，无需重新选择数据源

注：该定义的表是从第一列开始，所以新增的行的第一个单元格，即第一列的位置不能为空

4 透视表单元格的进度条形式

• 选中对应数据 → 开始 - 条件格式 → 数据条 - 渐变填充

• 另： 同比增长率 -  与上一年同期对比（去年同一个月）         
•         环比增长率 - 与上期对比  （上个月）

5 自定义透视表的计算字段/项

① 在透视表新增自定义公式字段/项

a.新增计算字段：

• 透视表中，可添加自由算式的列值，运用目前已在透视表中的列项数值，进行自由运算。以某月的产品的库存天数为例：
• 库存天数 =库存月*30

                =（当月的期末库存 ÷ 近三月的平均销量）*30

先透视表中，已经包含了各个产品的当月期末库存列数据，近三月的平均销量。透视表如下：

产品	求和项:期末库存	求和项:近3月平均销量
aaaa	453049	715033.3333
cccc	637169.8	98413.7
bbbb	687535	165224
eeee	536528.8	433857.8667
dddd	55368	52319.33333
fffff	130672	154896
gggg	97032	31786.66667
hhhh	28460.7	57251.63333
总计	2625815.3	1708782.533

• 点击 数据透视表分析 → 计算 - 字段、项目和集 → 计算字段

  

• 在 名称 输入 库存天数 → 在公式框输入库存天数的计算公式，可直接点击（字段）处的“期末库存”和“近3月平均销量”，添加对应符号，补充完整公式 → 点击确定后，透视表就已经新增了库存天数列

  

• 设置单元格格式，把小数点去掉 → 设置居中，优化呈现效果 →去掉表头里多余的字符，如“求和项：”，如出现“已有相同数据透视表字段名存在”的提示框，在表头的单元格适当添加空格即可。
• 最后结果如下：

产品	期末库存	近3月平均销量	库存天数
aaaa	453049	715033	19
cccc	637170	98414	194
bbbb	687535	165224	125
eeee	536529	433858	37
dddd	55368	52319	32
fffff	130672	154896	25
gggg	97032	31787	92
hhhh	28461	57252	15
总计	2625815	1708783	46

b.新增计算项：操作同上，不过首先要点击数据透视表中【列标签】这一单元格，计算项才可以往下操作

② 查看已存在的自定义公式列/项

• 选中透视表的点击 数据透视表分析 → 计算 - 字段、项目和集 → 列出公式

• 得出结果如下：

6 使筛选类别并排显示

• 右键筛选处单元格 → 数据透视表选项 →  布局和格式 → 在报表筛选区域显示字段 - 水平并排

•  选中后筛选区域字段，可同时在数据透视表的首行显示，呈现如下

7 多重合并计算数据区域

需求：在同个工作簿中有多个页签，需要计算同个种类字段的同种数据，如不同页签有不同产品的数据，需要计算各个产品的销量，输出在同一个数据透视表里。

注：不同页签的首行字段需要一致，即选中的每列标题一致，否则透视表会输出多列数据

步骤：

• 按 Alt + D + P  呼出（数据透视表和数据透视图向导）→ 多重合并计算数据区域 - 数据透视表 →
• 创建单页字段 → 选定区域 - 分别选中各个页签的源数据的两列 → 完成

•  输出结果如下，可在（页1）选择不同项查看数据，每项代表一个页签：

         

8 用数据透视表作非重复计数

• 新建透视表 - 勾选 “将此数据添加到数据模型” → 值字段设置 → 下拉 在计算类型找到非重复计数

注：未添加到数据模型的透视表，不会出现（非重复计数）的计算类型，原本的计数为重复计数

       

9 设置万元单位的数据格式

• 选中单元格- 右键 → 设置单元格格式 - 数字 - 自定义 →在【类型】种输入0!.0,万
• 即可显示以万元为单位   结果如下

销售月
2022年1月	2022年2月	2022年3月	总计
100.7万	50.1万	79.1万	229.9万
18.9万	1.8万	6.0万	26.7万
	36.7万	49.2万	85.9万
		0.0万	0.0万
1.1万	0.6万	1.0万	2.7万

10 加减按钮 隐藏/添加

要把透视表中的行 加减号去掉，取消选中  “+/-按钮” 即可