公众号平台最新的推送规则对技术类文章不太友善,如果不想错过干货,请务必 “设为星标”哦!!!
点击上方蓝字 --> 点击“...”--> 选择“设为星标”
不知道大家注意过没有,用数据表透视表计算的时候,无论是求和、求平均值,还是最大最小值等等,都只是对可见数据进行计算。
也就是说,如果有数据透视表中有筛选项,那么隐藏的数值是不会纳入计算结果的。
有没有一个选项,可以让数据透视表对隐藏项也进行计算?
案例:
用下图 1 的数据表制作数据透视表,要求:筛选数据透视表的时候,求和结果包括隐藏项的值。
效果如下图 2 所示。
解决方案:
既然可以实现,就说明 Excel 中有这个功能,就按大家平时的方法做数据透视表,先看下是否可以达成目的。
1. 选中数据表的任意区域 --> 选择菜单栏的“插入”-->“数据透视表”
2. 在弹出的对话框中选择“现有工作表”及其位置 --> 点击“确定”
3. 在右侧的“数据透视表字段”区域,将“月份”和“姓名”字段拖动到“行”区域,“订单数”字段拖动到“值”区域
4. 点击“行标签”旁的筛选箭头 --> 在弹出的菜单中勾选“1”--> 点击“确定”
筛选后的总计结果成了 1 月的总计,也就是自动忽略了隐藏行求和。那我们看一下是否能设置成包含隐藏行求和。
5. 选中数据透视表的任意区域 --> 选择菜单栏的“设计”-->“分类汇总”
最下方有一个“汇总中包含筛选项”选项,这正是我们需要的功能,但此选项却是灰的,无法用,这又是为何?
其实很简单,只要在最开始创建数据透视表的时候,加个勾选项就可以了。
接下来我们重头来过。
1. 选中数据表的任意区域 --> 选择菜单栏的“插入”-->“数据透视表”
2. 在弹出的对话框中选择“现有工作表”及其位置 --> 勾选“将此数据添加到数据模型”--> 点击“确定”
3. 在右侧的“数据透视表字段”区域,仍然是将“月份”和“姓名”字段拖动到“行”区域,“订单数”字段拖动到“值”区域
4. 选中数据透视表的任意区域 --> 选择菜单栏的“设计”-->“分类汇总”
现在“汇总中包含筛选项”不再是灰色的了,选它!选它!选它!
5. 点击“行标签”旁的筛选箭头 --> 在弹出的菜单中勾选“1”--> 点击“确定”
成功了!总计的数值是整个数据透视表的求和数,包括筛选后隐藏了的两个月数据。
6. 选中数据透视表的任意区域 --> 选择菜单栏的“设计”-->“分类汇总”-->“在组的底部显示所有分类汇总”
7. 选择“设计”-->“报表布局”-->“以表格形式显示”
8. 点击“姓名”旁的筛选箭头 --> 任意勾选几个人名 --> 点击“确定”
这就是我们想要实现的效果:无论是总计,还是分类汇总,都是包含隐藏行求和。
转发、点赞、在看也是爱!