Excel 答疑，已经成为了我的日常工作，各种奇葩问题早已习以为常，司空见惯。

但是，前两天被一个同学问到了一个问题，真的有点不想再搞 Excel 了。

文章开始之前，让我先吐槽一句：

心里舒服多了，我们继续。

01问题描述

这位同学的问题是这样的：

问题表格来了。

表 1

表 2

这是很典型的把表 A 做成表 B的问题，能这么提问的同学，通常具体的原因、目标、细节都是一问三不知。

所以我没有详细的过问，脑子开始条件反射筛选方法。

方法❶ ：复制粘贴。效率太低了，放弃。

方法❷ ：使用函数公式。

首先，要使用 ROW 函数，结合简单的数学计算，根据行号获取对应的列号。

然后，使用 INDEX 函数、获取行号和列号里对应的施工日期。

要特别注意行列的锁定，因为，公式向下拖动的时候，单元格引用会发生变化。

函数公式思路很快就梳理出来了，但是我还是放弃回答这个问题。

因为我知道，即便花 1~2 个小时写好公式，都不能算真正的解决问题，因为，后面还会有很高的「公式维护」成本。

❶ 领导看到表格，可能会追问函数公式是什么意思？

❷ 同事 Copy 了表格之后，修改了一下数据，公式就不好使了。

这位同学肯定会用更多我想不到的问题，来蹂躏我。

要一劳永逸的解决这个问题，写再多再复杂的函数公式，也只是隔靴搔痒。

关键是要解决数据明细规范化，和数据表格设计的问题。

02问题分析

分析表 1 里的内容，不难总结出规律，表格横向列标题记录的是，不同项目开始和结束的日期。

但是，在查询数据的时候，需要左右滚动表格，查询和筛选的时候也很不方便。

所以这位同学，整理成了表 2 的样子，把原有的横向列表标题，整理成了纵向的行标题，如下图所示：

这样来看，这应该是属于行列转换的问题，对吧，右键转置粘贴就好了吧？

千万不要这么想，因为行列转换这 4 个字会限制住你的思维。

或许制作表 2 的人，根本就没有考虑过行列转换，只是一拍脑袋觉得这样好看，就这么做了。

下个星期、下个月他再做一张表格，可能就是另外一种样式了。

更好的方式，是把这个问题归结为，数据明细和表格呈现的问题。

这样我们只要遵循下面两条，从一开始把表格设计好，就可以更高效的统计数据：

❶ 数据明细，用一维数据表记录数据。

❷ 表格呈现，尽量使用智能表格、或者数据透视表，实现数据统计、查询、分析，提高工作效率。

03解决方案

解决问题的过程，我们分成两个步骤：

❶ 数据明细的整理；

❷ 数据的查询表格呈现。

◆ 数据明细整理◆

这个表格的问题还是蛮多的，主要是标题字段缺失、混乱，我们来仔细看一下。

对应图示，主要问题有：

❶ 标题字段出现空白；

字段空白的数据列含义不清楚。按照完成后的表格来看，空白的这一列，应该指的是结束日期。

❷ 字段数量不匹配；

有的字段包含两列：开始日期和结束日期；有的只包含一列，需要补全缺失的字段。

首先，我尝试着把明细表的字段补全，让每个字段的数量匹配起来，形成规律。

❶ 不同的项目内容形成父标题。比如基坑、基础、检验、墙身。

❷ 每个项目里都包含对应的开始日期和结束日期。

明细表的字段名称规范后，我们再把它整理成一个一维表，像下面这个样子：

整理一维表的要点是：

❶ 数据归类。

把基坑、基础、检验、墙身等项目内容，全部都归类成「项目」字段。

❷ 记录要从上往下纵向延伸。

原本的数据，需要根据行列标题交叉查询，和更新数据。更新后的一维表是一条条的记录，从上往下依次追加数据即可，更加方便。

数据明细整理完成之后，接下来就完成对数据的查询。

◆ 数据查询表格◆

我们再看一眼原始的查询表格，需求大概是，根据桩号查询不同部位（即项目）的施工日期。

前面我们整理过后的明细表，已经基本符合了这个表格的需求。

所以，我们只需要把相应桩号的记录筛选出来就好了。

智能表格+切片器 ↑

效果图中，我将明细表转成了智能表格，使用智能表格的切片器功能，完成了交互是查询的效果。

如果想要完全的还原表 2 里的效果，可以是使用数据透视表+切片器，实现快速的查询。

透视表 + 切片器 ↑

04知识点汇总

这篇文章的重点，不在于最后的智能表格，也不在于透视表，重点在于数据明细表的整理和规范化。

包括：

❶ 补全缺失的字段

❷ 列字段名称归类

❸ 二维表转一维表

拉登解说：

这在动作数据分析领域有一个专业的名字叫做：数据清洗。

你可以不会，但是请一定要了解、知晓。

这样，才能拓展自己的视野，而不是整天被困在那几个函数公式上。

数据清洗的内容主要包括下面这些，有空的时候，可以百度深入了解。

❶ 选择子集：选择出我们要分析的数据列；

❷ 列名重命名：确保列明容易理解，方便数据分析；

❸ 删除重复项：删除重复的记录；

❹ 缺失值处理：处理数据中缺失的数据，方式计算统计错误；

❺ 数据类型转换：文本转数字，去掉数字中的单位等等；

❻ 数据排序：对日期序列的分析，要考虑到数据的排序；

❼ 异常值处理：数据类型不对，不是常规内容，需要特殊处理。

这些看似复杂的操作，并不需要专业的软件来操作，在 Excel 里就可以进行。

怎么样，听完拉登老师的分享，你有没有 Get 到，数据的整理和规范化有多么重要！