送人玫瑰,手有余香,请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径!
【置顶公众号】或者【设为星标】及时接收更新不迷路
小伙伴们好,今天来和大家分享一道变更数据结构的题目。这道题目我们使用多维引用的技术。但同时呢,我们通过INDEX+MATCH函数,也可以完成这个题目。
原题是这样子的:
需要用公式将左侧的数据结构转变为右侧的结构。第一反应应该是可以利用INDEX函数来解决问题的。
话不多说,我们一起来看看如何解决这道问题。
在单元格G3中输入公式“=IF(AND(ROW()=4,OR(COLUMN()={13,14,15})),"",INDEX($B$3:$D$10,MATCH($F3,$A$3:$A$10,)+INT((COLUMN(A1)-1)/3),MOD(COLUMN(A1)-1,3)+1))”,三键回车并向下向右拖曳即可。
思路:
INT((COLUMN(A1)-1)/3)部分,返回0,0,0,1,1,1,2,2,2这样的序列MOD(COLUMN(A1)-1,3)+1部分,返回1,2,3,1,2,3,1,2,3这样的序列
MATCH($F3,$A$3:$A$10,)部分,发挥灭一个ID的在A列中的第一个位置。然后再加上INT((COLUMN(A1)-1)/3)部分的值,实际上就是逐行向下移动
利用INDEX函数,就可以返回对应的数据了
但是这里有一个问题。由于ID2只有两行记录,而INT()部分是以3作为周期循环的,因此单元格区域M4:O4不为空。因此需要对这部分进行处理。利用IF函数,配合OR函数和AND函数,进行判断,当活动单元格处在第4行,同时,也处在第13、14或15列中任意一列时,返回空值。完美解决问题!
下面使用多维引用计数来解决这个问题。
在单元格G3中输入公式“=IFERROR(INDIRECT(TEXT(SMALL(IF($A$3:$A$10=$F3,ROW($3:$10)/1%+{2,3,4}),COLUMN(A1)),"r0c00"),),"")”,三键回车并向下向右拖曳即可。
思路:
IF($A$3:$A$10=$F3,ROW($3:$10)部分,返回ID等于1的所有的行号
上述结果扩大100倍后再分别加上列号(2、3、4)
SMALL函数一次提取最值,TEXT函数将其转换为“r0c00”的格式
INDEXT函数将其转换为单元格中的数据
最后利用IFERROR函数屏蔽错误值
本期内容练习文件提取方式:
链接:https://pan.baidu.com/s/1_QEtqZtkC1Jg7nBrAM3MJA?pwd=kf8y
提取码:kf8y
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
我就知道你“在看”
联系客服