这样做表格,活该天天加班
拉小登Excel
好的表格是设计出来的
关注
Excel答疑已经成为了我的日常工作,各种奇葩问题早已习以为常,司空见惯。
但是前两天被一个同学问到了一个问题,真的有点不想再搞Excel了。文章开始之前让我先吐槽1句:
心里舒服多了,我们继续上课。
/ 1 /
问题描述
这位同学的问题是这样的:
问题表格来了。
表1
表2
这是很典型的从把表A做成表B的问题,能这么提问的同学,通常具体的原因、目标、细节都是一问三不知。
所以我没有详细的过问,脑子里开始条件反射式的筛选方法。
方法1:复制粘贴。但是效率太低了,放弃。
方法2:使用函数公式。
首先要使用ROW函数,结合简单的数学计算,根据行号获取对应的列号。
然后使用INDEX函数、获取行号和列号里对应的施工日期。
要特别注意行列的锁定,因为公式向下拖动的时候,单元格引用会发生变化。
函数公式思路很快就梳理出来了,但是我还是放弃回答这个问题。
因为我知道,即便花1~2个小时写好公式,不能算真正的解决问题,后面还会有很高的「公式维护」成本。
1- 领导看到表格,可能会追问函数公式是什么意思?
2- 同事copy了表格之后,修改了一下数据,公式就不好使了。
这位同学肯定会用更多我想不到的问题,来蹂躏我。
要一劳永逸的解决这个问题,写再多再复杂的函数公式,也只是隔靴搔痒。关键是要解决数据明细规范化,和数据表格设计的问题。
/ 2 /
问题分析
分析表1里的内容,不难总结出规律,表格横向列标题记录的是,不同项目开始和结束的日期。
但是在查询数据的时候,需要左右滚动表格,查询和筛选的时候也很不方便。
所以这位同学,整理成了表2的样子,把原有的横向列表标题,整理成了纵向的行标题,如下图所示。
这样来看,这应该是属于行列转换的问题,对吧?右键转置粘贴就好了吧?
千万不要这么想,因为行列转换这4个字会限制住你的思维。
或许制作表2的人,根本就没有考虑过行列转换,只是一拍脑袋觉得这样好看,就这么做了。下个星期、下个月他再做一张表格,可能就是另外一种样式了。
▼
更好的方式,是把这个问题归结为,数据明细和表格呈现的问题。
这样我们只要遵循下面两条,从一开始把表格设计好,就可以更高效的统计数据:
1- 数据明细,用一维数据表记录数据。
2- 表格呈现,尽量使用智能表格、或者数据透视表,实现数据统计、查询、分析,提高工作效率。
/ 3 /
解决方案
解决问题的过程,我们分成两个步骤:
1- 数据明细的整理
2- 数据的查询表格呈现
1- 数据明细整理
这个表格的问题还是蛮多的,主要是标题字段缺失、混乱,我们来仔细看一下。
对应图示,主要问题有:
1- 标题字段出现空白
字段空白的数据列含义不清楚。按照完成后的表格来看,空白的这一列,应该指的是结束日期。
2- 字段数量不匹配
有的字段包含两列:开始日期和结束日期;有的只包含一列,需要补全缺失的字段。
首先,我尝试着把明细表的字段补全,让每个字段的数量匹配起来,形成规律。
1- 不同的项目内容形成父标题。比如基坑、基础、检验、墙身。
2- 每个项目里都包含对应的开始日期和结束日期。
明细表的字段名称规范后,我们再把它整理成一个一维表,像下面这个样子:
整理一维表的要点是:
1-数据归类。
把基坑、基础、检验、墙身等项目内容,全部都归类成「项目」字段。
2-记录要从上往下纵向延伸。
原本的数据,需要根据行列标题交叉查询,和更新数据。更新后的一维表是一条条的记录,从上往下依次追加数据即可,更加方便。
数据明细整理完成之后,接下来就完成对数据的查询。
2- 数据查询表格
我们再看一眼原始的查询表格,需求大概是,根据桩号查询不同部位(即项目)的施工日期。
前面我们整理过后的明细表,已经基本符合了这个表格的需求。所以,我们只需要把相应桩号的记录筛选出来就好了。
智能表格+切片器 ↑
效果图中,我将明细表转成了智能表格,使用智能表格的切片器功能,完成了交互是查询的效果。
如果想要完全的还原表2里的效果,可以是使用数据透视表+切片器,实现快速的查询。
透视表 + 切片器 ↑
/ 4 /
知识点汇总
这篇文章的重点,不在于最后的智能表格,也不在于透视表,重点在于数据明细表的整理和规范化。包括:
1- 补全缺失的字段
2- 列字段名称归类
3- 二维表转一维表
拉登解说
这在动作数据分析领域有一个专业的名字叫做:数据清洗。
你可以不会,但是请一定要了解、知晓。这样才能拓展自己的视野,而不是整天被困在那几个函数公式上。
数据清洗的内容主要包括下面这些,有空的时候,可以百度深入了解。
(1)选择子集:选择出我们要分析的数据列
(2)列名重命名:确保列明容易理解,方便数据分析
(3)删除重复项:删除重复的记录
(4)缺失值处理:处理数据中缺失的数据,方式计算统计错误
(5)数据类型转换:文本转数字,去掉数字中的单位等等
(6)数据排序:对日期序列的分析,要考虑到数据的排序
(7)异常值处理:数据类型不对,不是常规内容,需要特殊处理
这些看似复杂的操作,并不需要专业的软件来操作,在Excel里就可以进行。
这就是同样做表格,不同人之间的差距。
有的人做表格,可以做成了Office培训师、数据分析师,年收入过100万。而有的人,同样是做表格,却还在天天加班,整理报表和数据。
我在知乎看到了一篇不错的文章,作者用Excel做了一个“数据分析师”求职的分析,深入浅出的讲解了「数据分析」的步骤。
点击【阅读全文】了解更多内容,今天就是这样。
我是拉小登,源于Excel,不只是Excel