|
你好，我是刘卓。欢迎来到我的公号，excel函数解析。常用函数的小伙伴肯定对textjoin比较熟悉，都知道它是文本连接（合并）函数。而在工作表函数中，却没有文本拆分函数split，恰巧filterxml有文本拆分的功能，所以就用filterxml来代替split。 textjoin是文本连接函数，filterxml有文本拆分的功能，所以它们是天然的好搭档。今天要分享的案例就会用到这两个函数。下图A1:G12是数据源，记录的是ABCDE5人购买不同水果的数量。现在的要求是：将每人购买的所有水果种类和数量合并起来，放在相应的单元格中，结果如A14:F14所示。比如E共买了金果2件，火龙果3件。这个问题其实不难，在B14单元格输入下面的公式，按ctrl+shift+enter三键，右拉填充。然后点【开始】-【自动换行】，调整列宽。=TEXTJOIN(CHAR(10),1,IF(B2:B12,$A2:$A12&B2:B12&"件",""))
IF(B2:B12,$A2:$A12&B2:B12&"件","")这部分是if函数的数组用法，先来看第2参数$A2:$A12&B2:B12&"件"，它是把3部分相连，结果如下图G列所示。if的第1参数B2:B12，可能是很多初学者不理解的地方。if的第1参数不应该是条件判断吗，怎么是数字？其实第1参数中的一切非0数字代表条件成立，相当于true；只有0代表条件不成立，相当于false。
所以，IF(B2:B12,$A2:$A12&B2:B12&"件","")的意思是，B2:B12中包含非0数字的单元格是条件成立的，返回第2参数中相应的值；而空单元格相当于0，代表条件不成立，所以返回第3参数的空""。if最后的结果如下图H列所示。最后用textjoin将if函数返回的结果连接起来，并用char(10)作为分隔符，就得到结果了，不要忘记点【自动换行】。其中char(10)就是换行符，是不可见字符。 合并所有水果种类和数量 还是原来的数据源，只不过现在的合并方式有点不一样。之前是将同一人的所有水果种类和数量合并在一个单元格中，现在是将每人的每种水果数量单独合并起来，然后全部罗列出来，结果如H列所示。 其实，这有点像将二维表转换为一维表，还差个分列。那这个效果是怎么做到的？它和第一个案例基本是一样的，只不过由一维数组变为二维数组。 在H2单元格输入下面的公式，按ctrl+shift+enter三键，下拉填充。=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&"件"是好几部分的相连，返回的结果也是一个二维数组，由于这里不方便展示结果，就不截图了，大家自己查看结果。
最后展示一下if函数的结果，如下图蓝色框区域所示。对比上下2图可以看出，B2:F12中包含数字的单元格，在其对应的位置返回合并后的结果；空单元格对应的位置返回空""。然后用textjoin将if函数的结果连接起来，并用"</b><b>"作为分隔符，再在textjoin前后两侧连接上开始和结束标签，构成完整的xml格式的字符串，如下图所示。最后用filterxml函数依次取出每个b标签下的文本内容，也就是<b>和</b>之间的内容。相当于把textjoin合并后的字符串给拆分开。 感想总结： 其实这两个问题都不难，只是把几个函数的用法有机地结合起来。只要你会了单个函数的用法，然后把它们拼凑起来就可以了。就像学车一样，先学单项训练，S弯，坡起，倒车入库……，单项训练学好了，才有利于练组合技。
https://pan.baidu.com/s/15h40EZCoPx3IG4kqbPuKzA
|
