送人玫瑰,手有余香,请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径!
【置顶公众号】或者【设为星标】及时接收更新不迷路
小伙伴们好,今天来和大家分享一道文本题目。今天有三种方法解答这道题目,同时还会对题目稍作变更后引申出额外的彩蛋。有兴趣的小伙伴们可不要走开哦!
原题目是这样子的:
通过公式将左侧的数据结构转变为右侧的数据结构。看到这个题目你第一反应会使用哪个函数?
第一种方法, 海鲜大法。不过我们要借助一列辅助列。在单元格区域B1:B9中输入“:”。
在单元格C2中输入公式“=FILTERXML("<a><b>"&SUBSTITUTE(PHONETIC($A$2:$B$10),":","</b><b>")&"</b></a>","a/b["&6*(ROW(A1)-1)+2*COLUMN(A1)&"]")”,回车后向下向右拖曳即可。
思路:
FILTERXML函数不能处理多单元格区域,因此我们第一步要使用PHONETIC函数把源数据和辅助列合并在一起。当然,如果你可以使用TEXTJOIN函数,就不需要使用辅助列了
利用SUBSTITUTE函数变更数据的结构以符合FILTERXML函数的要求
FILTERXML函数的第二参数"a/b["&6*(ROW(A1)-1)+2*COLUMN(A1)&"]",提取b节点下所有的数据。提取的方法是:6个数据为一组,提取每组的第2、4、6个数据,因此第二参数写成6*(ROW(A1)-1)+2*COLUMN(A1)
INDEX函数。这是一个区域数组函数。
选中单元格区域C2:E4,输入公式“=INDEX(MID(A:A,4,99),ROW(2:4)*3-{4,3,2})”,三键回车即可。
一句话解释:
这条公式整体思路比较简单,只着重讲讲INDEX函数的第二参数。第二参数为“ROW(2:4)*3-{4,3,2}”,其构成了一个3行3列的内存数组,{2,3,4;5,6,7;8,9,10},来分别提取对应的数值。
SUBSTITUTE函数法
在单元格C2中输入公式“=TRIM(RIGHT(SUBSTITUTE(OFFSET($A$1,ROW(A1)*3+COLUMN(A1)-3,),":",REPT(" ",99)),99))”,三键回车并向下向右拖曳即可。
思路:
OFFSET($A$1,ROW(A1)*3+COLUMN(A1)-3,)部分,利用OFFSET函数在源数据中提取不同行的数据
SUBSTITUTE函数部分,使我们经常使用的经典套路。这里不详细介绍了
RIGHT函数右侧提取后去除空格即可
彩蛋。如果我们将源数据中的姓名都改成英文字符,例如AAA、BBB和CCC,这是我们会发现,VLOOKUP函数也可以解决这个问题了!
在单元格C2中输入公式“=VLOOKUP(" *",RIGHTB(INDEX($A$2:$A$10,3*(ROW(A1)-1)+COLUMN(A1)),ROW($1:$20)),1,)”,三键回车并向下向右拖曳即可。
小伙伴们,你们来试着解析一下这条公式吧!如果还不会,去翻翻往期的帖子哦!
本期内容练习文件提取方式:
链接:https://pan.baidu.com/s/1sPrGBYNaJmfwri-IelsCfw?pwd=q9ws
提取码:q9ws
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
我就知道你“在看”
联系客服