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