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 插件!
👇👇👇


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

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