二维码 购物车
部落窝在线教育欢迎您!

光涨肉价,不涨工资?用excel做张老板最爱的自动化表格,让你的工资翻一番!

 

作者:刘宏玲来源:部落窝教育发布时间:2019-09-18 15:03:22点击:1826

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

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

 

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

 

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

 

数据源:

 

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

 

 

*说明:

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

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

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

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

 

目标样式:

 

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

 


分析思路:

 

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

 

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

 

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

 

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

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

 

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

 

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

2)数据有效性+公式

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

 

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

 

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

 

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

 

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

 

1excel自带的筛选

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

3)数据有效性

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

5)高级筛选等

 

关于“动态”:

 

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

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

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

 

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

 

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

 

具体操作:


※步骤一:处理数据源

 

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

 

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

 

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

 

操作请见如下GIF动图

 

 

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

 

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

 

 

结果如下:

 

 

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

 

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

 

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

 

 

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

 

 

效果如下图所示:

 

 

b. 取消分类汇总

 

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

 

 

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

 

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

 

 

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

 

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

 

 

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

 

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

 

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

 

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

 

 

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

 

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

 

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

 

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

 

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

 

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

 

具体操作请见下方动图:

 

 

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

 

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

 

本文配套的练习课件请加入QQ群:264539405下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

超级表中切片器的应用学会了Ctrl+E,你还需要一个Ctrl+T表格宝宝!(下)

OFFSET函数的应用(下)《Excel进阶之路必学函数:动态统计之王——OFFSET(下篇)》

简单的动态图表《Excel教程:一看就会的excel动态图表入门篇》