【原】活学活用FILTERXML函数，难题也会变通途！

EXCEL应用之家 2023-06-02 发布于上海

展开全文

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

【置顶公众号】或者【设为星标】及时接收更新不迷路

小伙伴们好，今天来和大家分享一道文本题目。这道题目要求我们给单元格里的多行数据添加序列号。原题目是这样子的：

在源数据中，数据间甚至还有空行出现，这给公式的书写带来了极大的困难。

高版本环境下，这道题目相对比较简单。如果不能使用高版本函数，那么可以考虑使用SUBSTITUTE函数或者是FILTERXML函数。今天我们着重来介绍一下如何使用FILTERXML函数来解决这道问题。

使用海鲜大法我们可以将这道题目中单元格里的每一个数据都提取出来。之后再统计文本的数量，添加序列。

在单元格C2中输入公式“=IFERROR(ROW(INDIRECT("1:"&COUNTA(FILTERXML("<a>"&SUBSTITUTE(A2,CHAR(10),"")&"</a>","a/b"))))&"-"&CHAR(SMALL(IFERROR(CODE(FILTERXML("<a>"&SUBSTITUTE(A2,CHAR(10),"")&"</a>","a/b")),""),ROW($1:$3))),"")”，三键回车并向下拖曳即可。

由于此公式还需要在最外侧嵌套TEXTJOIN函数，而此函数在2016版中也不能使用。因此公式就写到这里了。此公式的运行结果如单元格D2中所示。

思路：

FILTERXML("<a>"&SUBSTITUTE(A2,CHAR(10),"")&"</a>","a/b")部分，利用FILTERXML函数提取源数据中的每一个数据。由于存在多行，因此利用了换行符来进行数据分割和提取，这部分的结果是{"A";"B";"C"}
接下来要考虑如何统计出文本的数量。COUNTA函数解决这个问题
ROW(INDIRECT("1:"&COUNTA()部分，使我们常用的一个技巧。它返回了一组自然数序列。序列中的每一个数都对应一个源数据中的文本
由于有空行的存在，FILTERXML函数的提取结果中会夹杂的错误值。我们需要把所有的文本都按顺序排列在一起，将错误值都放到最后。但由于文本数据不能直接排序，因此需要借助CODE函数和CHAR函数来处理。
CODE函数将文本转换为字符集中的数字代码，之后就可以排序了。将错误值转换为空值后，再按从小到大的顺序来排列。完成后再利用CHAR函数将数字代码转换回文本字符
最后，将两部分用“-”链接起来就可以了

本期内容练习文件提取方式：

链接：https://pan.baidu.com/s/11zVs4EYI9fkLYyeuT9k0xQ?pwd=gdkt

提取码：gdkt

好了朋友们，今天和大家分享的内容就是这些了！喜欢我的文章请分享、转发、点赞和收藏吧！如有任何问题可以随时私信我哦！

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

我就知道你“在看”