获取VIP特权
首页 > 模板攻略 > Excel教程 > excel筛选技巧:如何做一个动态筛选自动化图表?

2019-09-19 10:11:05

6已点赞

excel筛选技巧:如何做一个动态筛选自动化图表?



编按:哈喽,大家好!最近猪肉价格疯涨,很多小伙伴们都开始抱怨,要是再不涨薪,估计连肉都吃不上了。的确如此,这光涨肉价,不涨工资,搁谁也受不住啊!所以今天给大家分享一个老板最爱的excel自动化表格,搞定老板,升职加薪,不再是梦!

​*********

最近两个多月,猪肉价格成为很多人关注的热点话题。

根据新闻报道,在今年8月的第二周,猪肉价格同比上涨了46.8%,部分地区达到了32.61元/公斤的高价,没想到今年,这位“二师兄”肉的宝贵程度及知名度,竟然丝毫不亚于当年它“师父”的唐僧肉。让我们具体来看看它到底有多贵吧。


数据源:

小玲老师从“中国养猪网”随机选取了五个省份的猪肉价格,制成下表。(仅作excel演示使用)


excel筛选技巧:如何做一个动态筛选自动化图表?(1)


*说明:

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

外三元:全部选用外来品种杂交而成;

内三元:三个品种中有一到两个我国的品种,则称为内三元;

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

目标样式:

现在我们需要利用excel,将上表做成动态可筛选的模式,变成一个可汇报的动态数据表,数据表内容会随被筛选字段的变化而变化,如下图所示:


excel筛选技巧:如何做一个动态筛选自动化图表?(2)


分析思路:

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

我一直信奉 “思路比方法更为重要”,学会分析,就学会了举一反三的能力,遇到问题时,总能想到解决办法,这是最重要的,也是自己竞争力的体现。

首先,我们来对比一下“目标数据表”与“数据源表”的区别,你会发现:

(1)功能上有区别:静态表 vs 动态表,目标表多了筛选功能。

(2)样式上有区别:目标表较数据源表,新增了三个筛选按钮,且目标表没有合并单元格。

提到“动态筛选”,其实是有固定套路的,给大家普及一下三种常用的套路:

(1)数据透视表/超级表+切片器

(2)数据有效性+公式

(3)开发工具-窗体控件-单元格关联+公式+高级筛选

这三种套路,希望大家可以熟记于心,并形成条件反射,日后一旦遇到动态表格制作,就直接拿来用,脑中想到这几种固定搭配即可。

*本着让大家深入理解的精神,给大家解释一下,这几个套路的形成思路(同类问题均可按此方法进行分析):

1、“动态筛选”,可以拆分为:动态+筛选

2、涉及筛选部分,需要想到excel具有“筛选”功能的操作或按键有哪些:

(1)excel自带的筛选

(2)数据透视表本身的筛选功能,外加切片器

(3)数据有效性

(4)开发工具中的窗体控件

(5)高级筛选等

关于“动态”:

(1)数据透视表本身就是动态的;

(2)目标值通过公式链接到具体单元格,随着单元格内容变化,目标值发生变化,也是动态的;

(3)宏、VBA也可以实现目标的动态化

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

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

具体操作:

※步骤一:处理数据源

取消数据源表中的合并单元格,并快速填充空白单元格。

*提示:数据源非常重要,是一切excel操作的来源,一定要保证数据源的准确性和规范性(无隐藏行或列、无合并单元格等)。关于这一点我们在之前的文章中详细介绍过《函数技巧千千万,如何制表才关键!(上篇)》

具体操作:取消合并单元格→按Ctrl+G打开定位条件→选择“空值”→在编辑栏输入公式“=B3”(=上方单元格)→按Ctrl+Enter组合键完成公式录入→最后粘贴为数值。

操作请见如下GIF动图


excel筛选技巧:如何做一个动态筛选自动化图表?(3)


※步骤二:制作数据透视表

这个步骤会涉及到一些细节上的操作,需要保证最后呈现出的数据透视表与数据源表的内容、字段排列一致。所以在制作透视表时,需先将所有的字段均移至透视表字段的“行”字段下:


excel筛选技巧:如何做一个动态筛选自动化图表?(4)


结果如下:


excel筛选技巧:如何做一个动态筛选自动化图表?(5)


移动完成后,你会发现透视表的整体格式不符合我们初始的要求,需要进行以下操作步骤:

a. 更改数据透视表的布局

点击透视表,单击鼠标右键→选择“数据透视表选项”→在“显示”栏中勾选“经典数据透视表布局(启用网格中的字段拖放)”。


excel筛选技巧:如何做一个动态筛选自动化图表?(6)


或者也可以直接在“数据透视表工具”栏中,点击“设计”→“报表布局”→选择“以表格形式显示”。


excel筛选技巧:如何做一个动态筛选自动化图表?(7)


效果如下图所示:


excel筛选技巧:如何做一个动态筛选自动化图表?(8)


b. 取消分类汇总

点击透视表,在“数据透视表工具”栏中 ,点击“设计”→“分类汇总”→选择“不显示分类汇总”。效果如下图所示:


excel筛选技巧:如何做一个动态筛选自动化图表?(9)


c. 将省份列的空白处填充

点击透视表,在“数据透视表工具”栏中,点击“设计”→“报表布局”→选择“重复所有项目标签”。


excel筛选技巧:如何做一个动态筛选自动化图表?(10)


d. 取消“展开与折叠”按钮

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


excel筛选技巧:如何做一个动态筛选自动化图表?(11)


设置完成后,是不是发现与源数据表的格式差不多了呢?

※步骤三:添加切片器,实现“傻瓜式”动态筛选

数据透视表有自带的手动筛选功能,将需要筛选的字段挪至“筛选”字段下即可实现筛选。

但如果本例按此法操作,将作为筛选字段的“省份、类型及较全国均价”,移至“筛选”字段下,会发现这三列数据会在表格中消失,仅作为筛选按钮出现在表格的上方。


excel筛选技巧:如何做一个动态筛选自动化图表?(12)


导致这一现象的原因是由于数据透视表的筛选字段与行字段,是二选一,有我无他的关系。

若想解决,可以在数据源中添加辅助列(将需要筛选的字段列重新复制一列),刷新数据透视表,然后将其中一份字段放入筛选字段下,另一份字段放入行字段下,即可。

但此方法较为繁琐,且展示起来不够简洁明了。

此时,“切片器”的优势就体现出来了,利用切片器,可以直观的进行数据的筛选。

哪怕不懂数据透视表,在看到切片器后,就知道该如何使用了。

切片器的添加方式:点击透视表,在“数据透视表工具”栏中,点击“分析”- “插入切片器”。然后根据需要,选择作为筛选栏的字段即可。

具体操作请见下方动图:


excel筛选技巧:如何做一个动态筛选自动化图表?(13)


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

除以上excel技能外,小玲老师更多的是希望小伙伴们可以学会分析的思路。考虑到数据透视表的普及度,以及不受版本限制的特点,所以本例,老师采用透视表来做讲解。若小伙伴们的excel是2013版及以上的,可以直接使用超级表,两步就能完成。操作用法:点击源数据表,使用快捷键Ctrl+T,调用出超级表,然后增加切片器功能即可。是不是很简单,小伙伴快来试试吧!

本站已授权转载

原创:刘宏玲/部落窝教育(未经同意,请勿转载)

更多教程:部落窝教育(itblw.com


如果帮到您!点个赞吧!

相关模板

3秒登陆,即可下载

注册即同意《我拉网服务使用协议》&《我拉网隐私政策》

QQ

QQ

邮箱

电话

提交

手机绑定

绑定手机号,账户更安全

中国 +86

手机号格式错误

按住滑块,拖拽到最右边
>>

验证码错误

温馨提示:微信绑定手机号,手机端也可以登录,我拉网将对用户隐私信息给予严格保密。

取消