textjoin和filterxml合合分分,分分合合!
你好,我是刘卓。欢迎来到我的公号,excel函数解析。常用函数的小伙伴肯定对textjoin比较熟悉,都知道它是文本连接(合并)函数。而在工作表函数中,却没有文本拆分函数split,恰巧filterxml有文本拆分的功能,所以就用filterxml来代替split。
=TEXTJOIN(CHAR(10),1,IF(B2:B12,$A2:$A12&B2:B12&"件",""))
if的第1参数B2:B12,可能是很多初学者不理解的地方。if的第1参数不应该是条件判断吗,怎么是数字?其实第1参数中的一切非0数字代表条件成立,相当于true;只有0代表条件不成立,相当于false。
最后用textjoin将if函数返回的结果连接起来,并用char(10)作为分隔符,就得到结果了,不要忘记点【自动换行】。其中char(10)就是换行符,是不可见字符。
合并所有水果种类和数量
还是原来的数据源,只不过现在的合并方式有点不一样。之前是将同一人的所有水果种类和数量合并在一个单元格中,现在是将每人的每种水果数量单独合并起来,然后全部罗列出来,结果如H列所示。
其实,这有点像将二维表转换为一维表,还差个分列。那这个效果是怎么做到的?它和第一个案例基本是一样的,只不过由一维数组变为二维数组。
=FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,IF(B$2:F$12,B$1:F$1&":"&A$2:A$12&B$2:F$12&"件",""))&"</b></a>","a/b["&ROW(A1)&"]")
IF(B$2:F$12,B$1:F$1&":"&A$2:A$12&B$2:F$12&"件","")这部分是if函数二维数组的用法,第1参数B$2:F$12是二维区域,包含数字的单元格是条件成立的,返回第2参数相应的值;空单元格为0,是条件不成立的,返回第3参数的空""。
第2参数B$1:F$1&":"&A$2:A$12&B$2:F$12&"件"是好几部分的相连,返回的结果也是一个二维数组,由于这里不方便展示结果,就不截图了,大家自己查看结果。
最后用filterxml函数依次取出每个b标签下的文本内容,也就是<b>和</b>之间的内容。相当于把textjoin合并后的字符串给拆分开。
感想总结:
https://pan.baidu.com/s/15h40EZCoPx3IG4kqbPuKzA