千万别用这招整理数据！用过的人都回不去了......

　　点击蓝字发送【福利】

　　免费领办公模板、软件工具！

　　本文作者：小爽

　　本文编辑：卫星酱、竺兰

　　大家好，我是研究数据转换的小爽~

　　小李是我的同事，他之前设计过一个座位表，当需要查找姓名对应的座位号时，遇到了困难。文章请戳 。

　　小李在查找匹配的数据时，造成难度增加的主要原因是：

　　设计的表是一个数据展示表，而不是一个标准数据结构表。

　　

　　如果是个规范的数据源，查找数据将不再是难题。

　　

　　比如可以直接使用 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 插件！

　　为「查找之王」

　　分享给朋友

　　一起学习进步