FILTERXML函数巧妙统计单元格有效行数和总人数

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

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

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

小伙伴们好，今天来和大家分享一道文本处理题目。通过这道题目，再次提醒大家数据录入时的规范性和有效性，否则对后面的工作带来务必巨大的麻烦。

原题目是这样子的：

题目要求统计出每个单元格内有效的行数以及总人数。如果没有空行，这个题目就非常简单。但由于有空行存在，处理起来就会麻烦一些。两个思路，一是用SUBSTITUTE函数按换行符提取，然后再统计文本数据的个数，就是有效行数了。求总人数也是用同样的思路。二是使用FILTERXML函数，总体上的思路和SUBSTITUTE函数是类似的。

今天我们就着重介绍一下如何使用FILTERXML函数俩解决这个问题。

在开始前，在单元格E1和F1中分别输入“有效数据行数”和“,”，并对F1单元格做自定义处理，让他显示成“实际总人数,”。

在单元格E2中输入公式“=COUNT(0/ISTEXT(FILTERXML("<a>"&SUBSTITUTE(SUBSTITUTE($A2,CHAR(10),E$1),E$1,"")&"</a>","a/b")))”，三键回车并向下拖曳即可。

思路：

FILTERXML("<a>"&SUBSTITUTE(SUBSTITUTE($A2,CHAR(10),E$1),E$1,"")&"</a>","a/b")部分，利用海鲜大法将源数据中的换行符替换为单元格E1中的内容。结果有文本，也有错误值{"张鑫,徐浩";"张振杰";#VALUE!;"陈晨,李然,陶兴涛";#VALUE!;#VALUE!;"甄秀珍,徐伟";#VALUE!;#VALUE!;#VALUE!;"马杰,徐杰,张喆"}
利用ISTEXT函数做一个判断，所有文本都返回TRUE，错误值都返回FALSE
接下来COUNT(0/())这个常用操作，0/()将所有TRUE都转换为0，所有FALSE都转换为错误值。COUNT统计出0的个数，也就是有效行数了
当单元格向右拖曳时，单元格E$1就会变成F$1，而单元格F1中实际存在的数据是“,”，因此它的含义是用“,”来替换换行符，然后再用FILTERXML函数按“,”来提取数据
剩下来的过程和上面的是一样的，这里不在细说了。

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

链接：https://pan.baidu.com/s/1oZZ_A9JzRcmO8ifgoCY0QQ?pwd=bu25

提取码：bu25

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

-END-

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

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

我就知道你“在看”