千万别用这招整理数据！用过的人都回不去了......
点击蓝字发送【福利】
免费领办公模板、软件工具！
本文作者：小爽
本文编辑：卫星酱、竺兰
大家好，我是研究数据转换的小爽~
小李是我的同事，他之前设计过一个座位表，当需要查找姓名对应的座位号时，遇到了困难。文章请戳 。
小李在查找匹配的数据时，造成难度增加的主要原因是：
设计的表是一个数据展示表，而不是一个标准数据结构表。
如果是个规范的数据源，查找数据将不再是难题。
比如可以直接使用 Vlookup 函数进行查找。 (我相信这肯定难不倒我们秋叶 Excel 的粉丝们~)
所以，小爽今天侧重介绍：如何将这个座位数据表，转化为一维表？
文章主要介绍三个方法，一起来看看吧。 （最后一招狠简单！）
利用 Power Query
使用 PQ 的第一步，自然是需要先将数据导入到 PQ 编辑器中。
全选数据源区域-在【数据】选项卡下，选择【来自表格/区域】。
由于没有标题，所以取消勾选【表包含标题】，单击【确定】按钮。
此时数据源已经导入到 PQ 编辑器中。
单击 fx 新增步骤。
由于数据是每三行为一组数据，所以我们将表利用Table.Split函数进行拆分。
PS. Table.Split 函数能够将指定的行数拆分为多个表。
公式栏中，输入公式:
利用Table.Transpose 函数将每一个表进行转置操作。
公式栏公式如下 :
到这里，差不多已经是我们想要的效果了，现在只需要利用Table.Combine 函数把表进行合并处理即可。
公式栏公式如下：
将多余的列删除，鼠标右键需要删除的列-选择【删除】选项。
到这里，数据转换效果已经完成了。是不是很简单鸭~
最后，将表加载到 Excel 中。搞定~
利用 PQ 做法，简单是简单，但是它在导入数据的时候，破坏了原本表格的展示效果。
好丑 !!! 绝对不能忍 !
那有没有不破坏表格结构，对数据进行整理的做法？
接下来，我们介绍函数的做法。
函数整理法
利用函数来做，确定位置时，我们需要用到一点点数学知识~
确定行的规律
观察表中数据，可以发现，姓名与姓名之间依次间隔 3 行, 也就是： 1 , 4 , 7 , 10 , 13 , 16。
——由于有 16 列，所以每个数依次重复 16 次。
U2 单元格输入公式 :
确定列的规律
观察表中数据，可以发现，每一行有 16 列, 由于一共有 6 排，所以 1 到 16，需要重复 6 次。
V2 单元格输入公式 :
索引姓名数据
在前面，我们已经分别确定每一个姓名的行列相对位置。
所以，我们使用 index 函数，返回对应区域的行列数就可以啦。
index 函数的用法，通俗上讲：
=index ( 区域 , 第几行，第几列 )
比如下图，要查找第一行，第一列：
=index(区域,1,1)
也就是小爽。
回到案例中。
W2 单元格直接编写公式：
索引座位数据
因为座位在姓名的向下一行，所以行需要再加上 1。
X2 单元格输入公式：
利用传统函数做法，简单是简单，但是每一次做的时候，都需要事先利用数学知识找规律，再进一步编写函数。好麻烦~
像这种有规律性的结构表设计，在工作中，我们经常见到，比如说制作标签，座位安排等等。
杜绝这种问题主要就是，在设计表格之初，尽量避免。
不过，工作要求，我们不得不设计这种表。
那么对于这种，有结构性规律的重复表格，转换为一维表，我们能不能把它弄为一个自定义函数呢？
我们 Excel 主讲老师——拉登老师就有这个想法，于是他制作了一个P-index函数，就是专门用来解决这个问题的。
PS. 获取公式 PLUS 的方法见文末。
VBA 自定义函数
前面的函数做法，我们用了 n 个函数，对吧？利用 P_INDEX 函数，我们只需要一个函数就可以搞定。走起~
得到姓名
如下图, 在 V2 单元格输入公式：
得到座位
W2 单元格输入公式 ：
简单解释一下 ：
座位列的公式跟姓名列一样，只不过多了一个偏移的参数，最后的 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 个常用函数说明】
最后，感谢你阅读今天这篇文章，下面是 1 个抽奖链接，7 月 12 日中午 12:00 自动开奖，奖品是包邮赠送的一本《秒懂 Excel》图书。
中奖的小伙伴请在5 天内根据小程序给出的二维码添加我的微信，发送收货地址及信息，我们会安排寄送。
*以上内容包含广告
发送【匹配】
免费领文章配套练习文件！
发送【P】
免费领公式 plus 插件！
为「查找之王」
分享给朋友
一起学习进步