私信回复关键词【2020】,获取100+套高颜值图表模板!
我们在做 Excel 时遇到的问题,多半是表格没有设计好。
那些各种复杂的公式,都是在弥补表格设计上的错误。
好的表格是设计出来的。
01
问题描述
年终了,很多年终的数据对比,接踵而来,我收到一位网友的提问:
拉登老师,下面是我们公司的年度财务数据,要核对数据,并分析各部门、各费用类别的状况,怎么样做才能更直观啊?
![年底要核对几千条数据?!教你5分钟就搞定](http://p2.ttnews.xyz/loading.gif)
想一想,如果是你,你会怎么做?
————-思考时间————-
————-思考时间————-
————-思考时间————-
下面是我给出的解决方案。
首先是按照费用类别的数据对比,图中绿色表示 2016 年的数据,红色表示 2017 年的数据。
![年底要核对几千条数据?!教你5分钟就搞定](http://p2.ttnews.xyz/loading.gif)
然后是部门的消费数据对比。
实际上这个和上面的图表是同一个,我只是拖动了一个选项,就快速切换了数据源。
另外,我们还可以根据「工资」、报销等费用项目,动态查看各个部门的费用状况。
这么复杂的数据,是怎么做出对应的图表的呢?接着往下看。
02
问题分析
这个表格有一个非常显著的问题,它是二维的数据表(纵向是费用类别,横向是部门)。
这种数据结构,让人很容易陷入这样的思考:如何使用 VLOOKUP,把需要的数据提取出来,然后再做核对比较。
<code>=VLOOKUP(B3,’2016 年’!B2:N52,MATCH(‘2017 年’!C2,’2016 年’!B2:N2,0),0)/<code>
▲左右滑动查看完整公式
公式写起来非常复杂。
而且,二维的数据结构,很难使用图表进行呈现,即便做出来,也看不到对比。
所以我们首先要做的是:二维数据表转一维数据表。
接下来,看看具体的解决步骤。
03
解决步骤
整个问题的解决步骤,大致可以分为下面 3 个部分:
❶ 二维数据表转一维数据表
❷ 创建数据透视表
❸ 创建数据透视图
◆ 二维表转一维表 ◆
首先你需要明白什么是二维表,什么是一维表,看看下面两个图表的对比,你会更加清晰。
二维数据表,和一维数据表一个明显区别就是:
二维数据表的标题在行方向和列方向都有,而一维数据表的标题,只存在于行方向。
明白了二者的区别,接下来就是如何转换了。
二维转一维数据表的方法有很多,可以用插件、数据透视表,我自己常用的方法是自己写一段 VBA。
这些方法,听起来都很复杂、很难,不过如果你是 Office 2016 及以上版本用户,转换的方法就简单多了,我们使用 Excel 内置的功能,就可以完成。
接下来跟我一起操作。
❶ 选择【数据】选项卡,点击【新建查询】,选择【从文件】-【从工作簿】,选择我们的案例文件。
❷ 在对话框里,选择文件夹,点击【编辑】。
❸ 删除右边 3 个无用的数据列。
❹ 点击【Data】右边的按钮,展开所有的数据,点击【确定】。
❺ 点击【删除行】【删除最前面几行数据】,删除第 1 行的数据。
❻ 点击【将第 1 行用作标题】。
❼ 按住 shift 选择第 4 列到最后 1 列,选择【转换】选项卡,点击【逆透视列】,把二维表,转换为以为表。
❽ 选择【开始】选项卡,点击【关闭并上载】。
这样我就把二维数据表,成功转换成了一维数据表。
◆ 创建数据透视表 ◆
有了一维数据表之后,我们就可以很轻松地创建出数据透视表了。
❶ 选择【插入】选项卡,点击【数据透视表】。
❷ 然后按照下面的图片,设置数据透视表的字段。
❸ 创建数据透视图。
设置好数据透视表之后,创建数据透视图,只是点击几下按钮的事情。
选择数据透视表中的任意单元格;
选择【分析】选项卡,点击【数据透视图】,选择条形图。
③ 选择【分析】选项卡,点击【字段按钮】,隐藏所有的字段按钮。图表就创建好了。
04
改善输出
因为图表的数据源,是数据透视表,所以我们只要修改数据透视表的结构,就可以更新图表中的数据。
比如我们把「费用类别」删除,把「部门」拖动到【轴】区域,就可以得到「部门」的费用对比。
另外,通过插入【切片器】,我们还可以添加一个「动态列表」,轻切换不同的费用项目。
05
知识点汇总
重点是把二维表转一维表的操作,转换成一维表之后,我们才有更多数据可视化的选择。
除此之外,本节的知识点还包括:
❶ 调整数据透视表达结构,完成数据分组统计。
❷ 创建数据透视图,调整图表元素。
❸ 使用切片器,实现动态的数据图表。
怎么样,上面的内容都学会了吗?
私信回复关键词【2020】,获取100+套高颜值图表模板!
閱讀更多 秋葉Excel 的文章