千万别用这招整理数据！用过的人都回不去了......
大家好，我是研究数据转换的小爽~
小李是我的同事，他之前设计过一个座位表，当需要查找姓名对应的座位号时，遇到了困难。
小李在查找匹配的数据时，造成难度增加的主要原因是：
设计的表是一个数据展示表，而不是一个标准数据结构表。
如果是个规范的数据源，查找数据将不再是难题。
比如可以直接使用 Vlookup 函数进行查找。(我相信这肯定难不倒我们秋叶 Excel 的粉丝们~)
所以，小爽今天侧重介绍：如何将这个座位数据表，转化为一维表？
文章主要介绍三个方法，一起来看看吧。（最后一招狠简单！）
利用 Power Query
使用 PQ 的第一步，自然是需要先将数据导入到 PQ 编辑器中。
全选数据源区域-在【数据】选项卡下，选择【来自表格/区域】。
由于没有标题，所以取消勾选【表包含标题】，单击【确定】按钮。
此时数据源已经导入到 PQ 编辑器中。
单击 fx 新增步骤。
由于数据是每三行为一组数据，所以我们将表利用 Table.Split 函数进行拆分。
PS. Table.Split 函数能够将指定的行数拆分为多个表。
公式栏中，输入公式:
= Table.Split(源,3)
利用 Table.Transpose 函数将每一个表进行转置操作。
公式栏公式如下 :
= List.Transform(Table.Split(源,3),each Table.Transpose(_))
到这里，差不多已经是我们想要的效果了，现在只需要利用 Table.Combine 函数把表进行合并处理即可。
公式栏公式如下：
= Table.Combine(List.Transform(Table.Split(源,3),each Table.Transpose(_)))
将多余的列删除，鼠标右键需要删除的列-选择【删除】选项。
到这里，数据转换效果已经完成了。是不是很简单鸭~
最后，将表加载到 Excel 中。搞定~
利用 PQ 做法，简单是简单，但是它在导入数据的时候，破坏了原本表格的展示效果。
好丑 !!! 绝对不能忍 !
那有没有不破坏表格结构，对数据进行整理的做法？
接下来，我们介绍函数的做法。
函数整理法
利用函数来做，确定位置时，我们需要用到一点点数学知识~
👉 确定行的规律
观察表中数据，可以发现，姓名与姓名之间依次间隔 3 行, 也就是：1 , 4 , 7 , 10 , 13 , 16。
——由于有 16 列，所以每个数依次重复 16 次。
U2 单元格输入公式 :
=INT((ROW(A1)-1)/16+1)*3-2
👉 确定列的规律
观察表中数据，可以发现，每一行有 16 列,由于一共有 6 排，所以 1 到 16，需要重复 6 次。
V2 单元格输入公式 :
=MOD(ROW(A1)-1,16)+1
👉 索引姓名数据
在前面，我们已经分别确定每一个姓名的行列相对位置。
所以，我们使用 index 函数，返回对应区域的行列数就可以啦。
index 函数的用法，通俗上讲：
=index ( 区域 , 第几行，第几列 )
比如下图，要查找第一行，第一列：
=index(区域,1,1)
也就是小爽。
回到案例中。
W2 单元格直接编写公式：
=INDEX($D$5:S$21,U2,V2)
👉 索引座位数据
因为座位在姓名的向下一行，所以行需要再加上 1。
X2 单元格输入公式：
=INDEX($D$5:$S$21,U2+1,V2)
利用传统函数做法，简单是简单，但是每一次做的时候，都需要事先利用数学知识找规律，再进一步编写函数。好麻烦~
像这种有规律性的结构表设计，在工作中，我们经常见到，比如说制作标签，座位安排等等。
杜绝这种问题主要就是，在设计表格之初，尽量避免。
不过，工作要求，我们不得不设计这种表。
那么对于这种，有结构性规律的重复表格，转换为一维表，我们能不能把它弄为一个自定义函数呢？
我们 Excel 主讲老师——拉登老师就有这个想法💡，于是他制作了一个 P-index 函数，就是专门用来解决这个问题的。
PS. 获取公式 PLUS 的方法见文末。
前面的函数做法，我们用了 n 个函数，对吧？利用 P_INDEX 函数，我们只需要一个函数就可以搞定。走起~
👉 得到姓名
如下图, 在V2 单元格输入公式：
=P_INDEX($D$5,$E$5,$D$8,16,100,U2)
👉 得到座位
W2 单元格输入公式 ：
=P_INDEX($D$5,$E$5,$D$8,16,100,U2,1)
简单解释一下 ：
座位列的公式跟姓名列一样，只不过多了一个偏移的参数，最后的 1 表示向下偏移一个单元格。而座位就在姓名的向下一个单元格处。
姓名列=P_INDEX($D$5,$E$5,$D$8,16,100,U2)座位列=P_INDEX($D$5,$E$5,$D$8,16,100,U2,1)
敲黑板 👉 P_INDEX 函数基础语法
参数 1：第一个单元格
参数2：第二个单元格
参数 3：第三个单元格
参数 4：列标签有几个。
数一数我们知道为 16。
参数 5：行标签有几个。
数一数知道有 6 个，但是我们不知道有多少个的情况下，可以写大一点，比如我这里写了 100。（写大于等于 6 的数就可以）
参数 6：获取第几个值，序号中就是 123……，直接引用就可以。
参数 7：以起始单元格向下偏移几行（座位在姓名的向下一个单元格，所以如果要获取座位的话，写 1）
参数 8：以起始单元格向右偏移几行。
不需要找规律，一个 P_INDEX 函数一下子就搞定这个结构转换的问题。
最后的话
本文深入讲解了同事小李遇到的表格查找问题。查找的主要难点在于表格数据不规范，造成需要写个长公式才能解决，极大的增加了使用难度。
借着这个问题，小爽写了三种转换一维表的方法。
方法一：利用 pq，涉及三个基础 M 函数
拆：利用 Table.Split 函数拆表；
转：利用 Table.Transpose 函数转置；
合：利用 Table.Combine 合并表格；
删：删除不需要的列。
方法二：传统函数定位法
利用 int 和 row 函数构造规律的行数；
利用 mod 和 row 函数构造规律的列数；
通过 index 索引区域行列数，获取姓名和座位。
方法三：公式 PLUS(P_INDEX)
第一到第三参数，确定位置
第四五参数，确定列和行标签个数
第六，返回第几个
第七八参数，确定行列偏移数
当然 P_INDEX 函数除了快速解决上述的表格结构转换，还可以处理其他有规律性结构。后面有机会，我们再聊聊。
做个小调查，上面三种转换一维表的方法，你认为哪个最好用呢？
大家关于工作中，遇到过哪些奇葩表？留言区与我一起聊聊。
对了，如果你想系统性学习 Excel，掌握更多Excel 技能，提升工作效率。
正好，我们家的《秋叶 Excel 3 天集训营》专为职场人准备，全部基于职场真实表格案例设计，还有很多超实用 Excel 技巧教学。
从日常的功能出发，全程演示，一课一练，夯实进阶每一步。
报名即送 【35 个常用函数说明】👇👇👇
*广告
