Excel教程：数据透视表和切片器结合运用，制作动态数据表

4lqedz · 发表于 2024-10-1 09:45:28

★

编按

★

Hello大众好！在做工作报告时比起密密麻麻的数字，老板可能更爱看一目了然的图表，比起静态的图表，能够动起来的图表似乎更加“吸睛”。今天就让咱们来学习一下怎样做一张老板爱都看的动态数据表。

01

数据源

小玲老师从“中国养猪网”随机选择了五个省份的猪肉价格，制成下表。（仅作excel演示运用）

*说明：

由三个品种杂交生产的猪叫三元猪；

外三元：所有选择外来品种杂交而成；

内三元：三个品种中有一到两个我国的品种，则叫作为内三元；

土杂猪：指的是良种猪与本地猪的杂交品种。

02

目的样式

此刻咱们需要利用excel，将上表做成动态可筛选的模式，变成一个可报告的动态数据表，数据表内容会随被筛选字段的变化而变化，如下图所示：

03

分析思路

在动手操作之前呢，咱们先来分析一下“这道题”。

我始终信奉 “思路比办法更为重要”，学会分析，就学会了举一反三的能力，遇到问题时，总能想到处理办法，这是最重要的，亦是自己竞争力的表现。

首要，咱们来对比一下“目的数据表”与“数据源表”的区别，你会发掘：

（1）功能上有区别：静态表 vs 动态表，目的表多了筛选功能。

（2）样式上有区别：目的表较数据源表，新增了三个筛选按钮，且目的表无合并单元格。

说到“动态筛选”，其实是有固定招数的，给大众普及一下三种常用的招数：

（1）数据透视表/超级表+切片器

（2）数据有效性+公式

（3）研发工具-窗体控件-单元格相关+公式+高级筛选

这三种招数，希望大众能够熟记于心，并形成要求反射，日后一旦遇到动态表格制作，就直接拿来用，脑中想到这几种固定搭配就可。

*本着让大众深入理解的精神，给大众解释一下，这几个招数的形成思路（同类问题均可按此办法进行分析）：

1、“动态筛选”，能够拆分为：动态+筛选

2、触及筛选部分，需想要到excel拥有“筛选”功能的操作或按键有那些：

（1）excel自带的筛选

（2）数据透视表本身的筛选功能，外加切片器

（3）数据有效性

（4）研发工具中的窗体控件

（5）高级筛选等

关于“动态”：

（1）数据透视表本身便是动态的；

（2）目的值经过公式链接到详细单元格，随着单元格内容变化，目的值出现变化，亦是动态的；

（3）宏、VBA亦能够实现目标的动态化

3、再将筛选与动态结合起来，就形成为了以上说到的三种常用固定搭配。

在本例中很显著是运用数据透视表+切片器的搭配模式，实用性最高，操作起来最简单。

↑免费领取Excel模板、视频教程学习↑

04

详细操作

※过程一：处理数据源

取消数据源表中的合并单元格，并快速填充空白单元格。*提示：数据源非常重要，是一切excel操作的源自，必定要保准数据源的准确性和规范性（无隐匿行或列、无合并单元格等）。关于这一点咱们在之前的文案中仔细介绍过《函数技巧千千万，怎样制表才关键！（上篇）》详细操作：取消合并单元格→按Ctrl+G打开定位要求→选取“空值”→在编辑栏输入公式“=B3”（=上方单元格）→按Ctrl+Enter组合键完成公式录入→最后粘贴为数值。操作请见如下GIF动图

※过程二：制作数据透视表

这个过程会触及到有些细节上的操作，需要保准最后呈现出的数据透视表与数据源表的内容、字段摆列一致。因此在制作透视表时，需先将所有的字段均移至透视表字段的“行”字段下：

结果如下：

移动完成后，你会发掘透视表的整体格式不符合咱们初始的需求，需要进行以下操作过程：a. 更改数据透视表的布局点击透视表，单击鼠标右键→选取“数据透视表选项”→在“表示”栏中勾选“经典数据透视表布局（启用网格中的字段拖放）”。

或亦能够直接在“数据透视表工具”栏中，点击“设计”→“报表布局”→选取“以表格形式表示”。

效果如下图所示：

b. 取消归类汇总点击透视表，在“数据透视表工具”栏中，点击“设计”→“归类汇总”→选取“不表示归类汇总”。效果如下图所示：

c. 将省份列的空白处填充点击透视表，在“数据透视表工具”栏中，点击“设计”→“报表布局”→选取“重复所有项目的签”。

d. 取消“展开与折叠”按钮点击透视表，单击鼠标右键→选取“数据透视表选项”→在“表示”栏中取消勾选“表示展开/折叠按钮”。或，在“数据透视表工具”栏中，点击“分析”→点击“+/-按钮”。效果如图所示：

设置完成后，是不是发掘与源数据表的格式差不多了呢？

※过程三：添加切片器，实现“傻瓜式”动态筛选

数据透视表有自带的手动筛选功能，将需要筛选的字段挪至“筛选”字段下就可实现筛选。但倘若本例按此法操作，将做为筛选字段的“省份、类型及较全国均价”，移至“筛选”字段下，会发掘这三列数据会在表格中消失，仅做为筛选按钮出此刻表格的上方。

引起这一现象的原由是因为数据透视表的筛选字段与行字段，是二选一，有我无他的关系。若想处理，能够在数据源中添加辅助列（将需要筛选的字段列重新复制一列），刷新数据透视表，而后将其中一份字段放入筛选字段下，另一份字段放入行字段下，就可。但此办法较为繁琐，且展示起来不足简洁明了。此时，“切片器”的优良就表现出来了，利用切片器，能够直观的进行数据的筛选。哪怕不懂数据透视表，在看到切片器后，就晓得该怎样运用了。切片器的添加方式：点击透视表，在“数据透视表工具”栏中，点击“分析”- “插进切片器”。而后按照需要，选取做为筛选栏的字段就可。详细操作请见下方动图：

最后再对切片器和数据透视表进行美化，就能够完成目的样式中的动态图表啦~

说在最后：

除以上excel技能外，小玲老师更加多的是期盼小伙伴们能够学会分析的思路。思虑到数据透视表的普及度，以及不受版本限制的特点，因此本例，老师采用透视表来做讲解。若小伙伴们的excel是2013版及以上的，能够直接运用超级表，两步就能完成。操功效法：点击源数据表，运用快捷键Ctrl+T，调用出超级表，而后增多切片器功能就可。是不是很简单，小伙伴快来试试吧！扫一扫添加老师微X

在线咨询Excel课程

举荐阅读

关注咱们，发掘更加多Excel优秀教程

求职大厂，却被面试官“毒打”：不会这五个技能的人，正在失去职场竞争力

大厂HR：有这种Excel思维的实习生，我真的想高薪留下来

靠一只“猪”一秒拆分上千个工作表？！同事的骚操作看呆我......

比VBA好用100倍！拆分工作表，用数据透视表5秒就搞定！

课程举荐

抱怨身处阴暗，不如提灯前行

您的“点赞”、“在看”和 “分享”是咱们连续产出的动力。