送人玫瑰，手有余香，请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径！
【置顶公众号】或者【设为星标】及时接收更新不迷路
小伙伴们好，今天来和大家分享一道文本处理题目。这道题目有一定的难度，朋友们一起来做做看吧！
原题是这样子的：
要求用公式将左侧的结构转变为右侧的结构。今天准备用两种方法来解决这个问题。第一种方法我们需要写三条公式；第二种方法则是一步到位，一条公式完成。
观察一下这道题目的源数据，我们发现，结构转换后，部门名称需要重复显示，重复的次数等于名单中人名的数量。而人数又比分隔符多一个。根据这些特点，我们开始书写公式吧！
拆分部门。
在单元格D2中输入公式“=INDEX($A$2:$A$5,SMALL(IF((LEN($B$2:$B$5)-LEN(SUBSTITUTE($B$2:$B$5,"、",""))+1)<COLUMN(A:D),100,ROW($A$2:$A$5)),ROW(A1))-1)”，三键回车并向下拖曳即可。
思路：
LEN(SUBSTITUTE($B$2:$B$5,"、","")部分，用空值替代源数据中的分隔符“、”，再用LEN函数求得替换后的字符串长度
(LEN($B$2:$B$5)-LEN(SUBSTITUTE($B$2:$B$5,"、",""))+1)，再利用LEN函数求得替换前的字符串长度，两项相减，再加上1，就得到了人数
用(LEN($B$2:$B$5)-LEN(SUBSTITUTE($B$2:$B$5,"、",""))+1)这部分和COLUMN(A:D)相比，得到一个4行4列的一个内存数组。再利用IF函数根据逻辑判断的结果，返回100或者对应的行号。这部分的结果是：{2,2,2,100;3,100,100,100;4,4,100,100;5,5,100,100}。用一个矩阵的形式看得更直观
接下来利用SMALL函数，依次来返回第1、2、3..、9小值。随着公式向下拖曳，SMALL函数的第二参数会由ROW(A1)一直变到ROW(A8)。ROW(A1)到ROW(A3)时SMALL会返回2，ROW(A4)时返回3，后面依次类推
最后由INDEX函数来返回对应的部门名称
提取名单。
接下来就要在D列的基础上来提取名单。这时候就要用到SUBSTITUTE函数的经典应用了。
在单元格E2中输入公式“=MID(TRIM(MID(SUBSTITUTE(VLOOKUP($D2,$A$2:$B$5,2,),"、",REPT(" ",99)),COUNTIF($D$2:$D2,$D2)*99-98,99)),1,
LEN(TRIM(MID(SUBSTITUTE(VLOOKUP($D2,$A$2:$B$5,2,),"、",REPT(" ",99)),COUNTIF($D$2:$D2,$D2)*99-98,99)))-3)”，并向下拖曳即可。
思路：
VLOOKUP($D2,$A$2:$B$5,2,)部分，确保在D列的部门相同时（例如“财务部”）都能够提取到单元格B2的内容。下面的也是一样的
SUBSTITUTE(VLOOKUP($D2,$A$2:$B$5,2,),"、",REPT(" ",99))部分，用重复了99次的空格“ ”来替代源数据中的分隔符“、”
MID(SUBSTITUTE(VLOOKUP($D2,$A$2:$B$5,2,),"、",REPT(" ",99)),COUNTIF($D$2:$D2,$D2)*99-98,99)部分，利用MID函数来从第1、197、296..个位置上提取长度为99的字符串
COUNTIF($D$2:$D2,$D2)*99-98,99部分，是一个小亮点。通常使用这个公式时，COUNTIF($D$2:$D2,$D2)是会写成类似于COLUMN(A:D)这种形式的，但今天在这里不行。因为D列中有多个部门，且每个部门出现的次数也是不一样的。而部门出现次数又代表这个部门有多少人，需要用SUBSTITUTE函数做多少次分割。因此这里就写成了COUNTIF($D$2:$D2,$D2)这种形式。随着公式向下拖曳，COUTIF函数会字符返回不同部门出现的次数
最后利用TRIM函数去除多余的空格。
到这里为止，名单提取仅仅完成了一半，因为公式返回的是类似于"张二-18"这样的结果。我们还需要把姓名单独提取出来。方法也很简单，定位“-”的位置后用字符串总长度再减去3，就是名字的长度。利用MID函数提取就好了。
提取年龄
在单元格F2中输入公式“=RIGHT(TRIM(MID(SUBSTITUTE(VLOOKUP($D2,$A$2:$B$5,2,),"、",REPT(" ",99)),COUNTIF($D$2:$D2,$D2)*99-98,99)),2)”，并向下拖曳即可。
这个很简单，只要用RIGHT函数提取就行，不再过多介绍了。
一道题写3条公式在效率上不好。如果你不能使用高版本函数，就可以考虑用海鲜大法写一条公式。
在单元格D2中输入公式“=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PHONETIC($A$2:$C$5),"部","部部门、"),"-","名单、年龄"),D$1,"<c/>"),"、","</b><b>")&"</b></a>",IF(COLUMN()=4,"a/b[c][.='"&INDEX($A$2:$A$5,SMALL(IF((LEN($B$2:$B$5)-LEN(SUBSTITUTE($B$2:$B$5,"、",""))+1)<COLUMN($A:$D),100,ROW($A$2:$A$5)),ROW(A1))-1)&"']","a/b[c][position()="&ROW(A1)&"]"))”，三键回车后向右向下拖曳即可。
思路：
多次利用SUBSTITUTE函数整理源数据结构，使其符合FILTERXML函数的要求
重点讲一下它的第二参数。第二参使用了IF函数，根据列标的不同返回不同的内容。大家注意看，INDEX($A$2:$A$5,SMALL(IF((LEN($B$2:$B$5)-LEN(SUBSTITUTE($B$2:$B$5,"、",""))+1)<COLUMN($A:$D),100,ROW($A$2:$A$5)),ROW(A1))-1)这部分其实就是我们上面介绍的如何重复显示部门的那一段公式。由于返回的结果是文本，因此需要把它放在’“&&”’的中间，整体上符合[.='"&公式&"']的要求，和[c]同样作为筛选条件。其含义是，源数据中等于INDEX函数返回结果的那个数据
再来看看a/b[c][position()="&ROW(A1)&"]这部分，当公式拖曳到E列和F列时，FILTERXML函数就分别构成了姓名的内存数组和年龄的内存数组，如下
{"张二";"李四";"唐一心";"曾贤";"欧阳小虎";"帅小火";"林小木";"阿七"}
{18;29;24;36;28;24;24;34}
而position()表示数据的位置信息，position()="&ROW(A1)&"表示取第一个位置上的数据，随着公式向下拖曳，position()="&ROW(A1)&"会依次提取后面的数据信息
-END-
联系客服
