欢迎转发和点一下“在看”，文末留言互动!
置顶公众号或设为星标及时接收更新不迷路
小伙伴们好，今天来和大家分享一道烧脑的题目。这道题目我也是思考了很长时间，终于在某一时刻灵机一动，写下了今天将向大家分享的公式。
原题目是这样的：
题目要求统计一下源数据中有多少个整数。
观察一下源数据，既有“3-4”这样的的形式，也有单独的数字。这里ROW(INDIRECT())的技巧是不适用的。是不是有一种老虎吃天，无从下口的感觉？
慢着，这几天一直在学习FILTERXML函数，里面有一个功能是提取含有特殊字符的数据，刚好就和我们今天这道题目非常契合，走起，做题！
01
FILTERXML函数提取含特殊字符的数据
高版本环境下，这道题目非常简单。在低版本环境下解决这道题目着实要费一番气力。
在单元格D2中输入下列公式，三键确认后向下拖曳即可。
=IFERROR(NPV(-2,IMREAL(IMDIV(FILTERXML("<a><b>"&SUBSTITUTE(A2&"i",",","i</b><b>")&"</b></a>","a/b[contains(.,'-')]"),{"1","-i"})))+LEN(A2)-LEN(SUBSTITUTE(A2,"-","")),0)+COUNT(FILTERXML("<a><b>"&SUBSTITUTE(A2,",","</b><b>")&"</b></a>","a/b[contains(.,'-')=0]"))这道题目的主要思路是：先把“3-4”这种形式的数据提取出来，计算它们的数字个数；再把单独数组提取出来统计个数。最后汇总。
SUBSTITUTE(A2&"i",",","i</b><b>")由于类似“3-4”这样的数据被提取后系统会默认为日期型数据，会自动给转换为一串数字，因此这里把他们转换成复数的形式，也方便后面提取实数和虚数进行计算。
FILTERXML("<a><b>"&SUBSTITUTE(A2&"i",",","i</b><b>")&"</b></a>","a/b[contains(.,'-')]")利用FILTERXML函数提取所有含有“-”的数据。以第三行为例，这部分的结果是{"1-3i";"26-28i"}。
FILTERXML函数的第二参数"a/b[contains(.,'-')]"，指定提取那些含有“-”的数据。
IMDIV(FILTERXML("<a><b>"&SUBSTITUTE(A2&"i",",","i</b><b>")&"</b></a>","a/b[contains(.,'-')]"),{"1","-i"})利用IMDIV函数，让上一步的结果去除以一个数。这里是数组形式{"1","-i"}。得到的结果是{"1-3i","3+i";"26-28i","28+26i"}。
你看，在实数位置上的数字分别就是“1-3”、“26-28”这种文本的前后端数字。
IMREAL(IMDIV(FILTERXML("<a><b>"&SUBSTITUTE(A2&"i",",","i</b><b>")&"</b></a>","a/b[contains(.,'-')]"),{"1","-i"}))利用IMREAL函数提取实数部分。结果为{1,3;26,28}。
NPV(-2,IMREAL(IMDIV(FILTERXML("<a><b>"&SUBSTITUTE(A2&"i",",","i</b><b>")&"</b></a>","a/b[contains(.,'-')]"),{"1","-i"})))利用NPV函数来求和，得到的结果是4。这个还不是正确答案，因为这种前后端相减，结果还要再加上一个1，才是正确答案。
LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))但是这里不能直接加上1，因为在源数据中可能有多个“1-3”这种类型的数据。那么我们可以转变一下思路。有几个“-”，就需要加上几个1。
上面的这组公式就是实现了这个目的。
IFERROR(NPV(-2,IMREAL(IMDIV(FILTERXML("<a><b>"&SUBSTITUTE(A2&"i",",","i</b><b>")&"</b></a>","a/b[contains(.,'-')]"),{"1","-i"})))+LEN(A2)-LEN(SUBSTITUTE(A2,"-","")),0)最后IFERROR函数将错误值转换为0。
到此为止，“1-3”这种形式的数据就处理完了。接下来需要处理单独的那些数字了。
COUNT(FILTERXML("<a><b>"&SUBSTITUTE(A2,",","</b><b>")&"</b></a>","a/b[contains(.,'-')=0]"))同样，利用FILTERXML函数来提取那些不含有“-”的数据后，再用COUNT函数统计数字个数就好了。
IFERROR(NPV(-2,IMREAL(IMDIV(FILTERXML("<a><b>"&SUBSTITUTE(A2&"i",",","i</b><b>")&"</b></a>","a/b[contains(.,'-')]"),{"1","-i"})))+LEN(A2)-LEN(SUBSTITUTE(A2,"-","")),0)+COUNT(FILTERXML("<a><b>"&SUBSTITUTE(A2,",","</b><b>")&"</b></a>","a/b[contains(.,'-')=0]"))最后，将这几部分相加，就得到正确答案了！
02
VBA代码更简单
上面的公式太复杂了，如果利用VBA来写一个自定义函数，则此题目将非常简单。
完成代码如下：
Function Nocount(rng)Dim aFor Each a In Split(rng, ",")If InStr(a, "-") = 0 ThenNocount = Nocount + 1ElseNocount = Nocount + Range(Replace(a, "-", ":")).Rows.countEnd IfNextEnd Function
简单介绍一下这段代码的含义。
将源数据按分隔符来拆分。拆分后在每个字符串中查找符号“-”。如果不能找到，则记录一次累计记录；如果能找到，则把字符串转换成range对象变量，统计行数并累计记录。
03
工作表中直接使用自定义函数
来看一下自定义函数的计算结果吧。
本期内容练习文件提取方式：
链接: https://pan.baidu.com/s/1SRL_z5SjR7rgKN2qOSK6fw?pwd=grnc 提取码: grnc
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
我就知道你“在看”
