logo
Redian新闻
>公众号
>
千万别用这招整理数据！用过的人都回不去了......

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

公众号新闻


作者：小爽
编辑：卫星酱、竺兰


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

小李是我的同事，他之前设计过一个座位表，当需要查找姓名对应的座位号时，遇到了困难。文章请戳👉 VLOOKUP靠边站，这才是Excel中最牛的查找方法？（建议收藏）。

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

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


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


比如可以直接使用 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 的方法见文末。

VBA 自定义函数


前面的函数做法，我们用了 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 个常用函数说明】
👇👇👇

*广告



❤



点击下方公众号卡片
发送【匹配】
免费领文章配套练习文件！

发送【P】
免费领公式 plus 插件！
👇👇👇


微信扫码关注该文公众号作者

戳这里提交新闻线索和高质量文章给我们。
来源: qq
点击查看作者最近其他文章
相关阅读
头不秃千万别用！2瓶不到50的生姜洗发水，简直是脱发星人的本命救星！试管婴儿与妈妈打过的800多针合影，看过的人都沉默了！！父亲节再读《背影》：看过的人都哭了，看懂的人都老了再想瘦也别用这几种办法减肥，后果太惨烈十四 中苏友好经历弟弟被“富养”出抑郁症，深圳妈幡然醒悟：千万别用养女孩的方法养男孩”！“养天地正气”的圣经解释中东行散记（五）：聊一点点历史千万别用“ChatGPT”做PPT，真的太爽了~华女向国内亲人汇款后，遭美国务院调查，护照被吊销,被困在美国回不去... “恐怕要坐牢了！”纽约6月最不能错过的24大活动！美人鱼游行/草莓节/复古派对都回归啦！太狂妄了！宁德时代暴涨8%，阳光电源涨7%，隆基绿能涨6%！光伏、锂电双双暴起，新能源的至暗时刻过去了吗？股民：回来了，都回来了遇到这种虫，千万别用巴掌解决！有人险些毁容、失明……2块内容的PPT“对比”页，千万别用VS了！震惊，一行 MD5 居然让小伙伴都回不了家！！！没药用了四年的「 神仙面膜」：80%以上精华，用过的人都赞不绝口你读过的书，扛过的事，帮过的人，都是来渡你的澳租客收到$600电费账单后傻眼！用了这招，电费变成$71购药谨慎！安省华人超市“常用药”遭卫生部强制下架，买了的千万别用！34岁被辞退被离婚，如今她靠整理把房间扩大了2倍，逆袭百万年薪：好的人生需要整理！北京什么时候攻打台湾？2025年?千万别用！非法“减肥神药”流入20多个省份，卖了上亿元，原料来源惊人！这种无理数中的无理数，让数学家直呼「根本停不下来」“媳妇，你什么都不要说，我可能回不去了……”十三 三反再也回不去了，大家的澳洲梦已经改变留学生发邮件给教授惨遭批评！这些词语千万别用！震惊，一行MD5居然让小伙伴都回不了家！！！突发！华女寄钱回国,护照被注销,被困在美国回不去... “恐怕要坐牢了！”“书界奥斯卡”：读过的书，走过的路，遇过的事，就是你的人生格局！鸡蛋大的荔枝王，吃过一次就回不去了......遇到这种虫，千万别用手拍！有人因此毁容……房价回不去了！比疫情前涨28%，湾区中间价达$125万当我们开始整理自己的家，实际上是整理自己的人生头不秃千万别用！用这防脱洗发水让你发量收不住！
logo
联系我们隐私协议©2024 redian.news
Redian新闻
Redian.news刊载任何文章，不代表同意其说法或描述，仅为提供更多信息，也不构成任何建议。文章信息的合法性及真实性由其作者负责，与Redian.news及其运营公司无关。欢迎投稿，如发现稿件侵权，或作者不愿在本网发表文章，请版权拥有者通知本网处理。