http://www.pptjcw.com

wps页眉页脚怎么全部删除:excel怎么统计分隔符的字符串

    excel怎么统计分隔符的字符串

    Q某些情况下,我们可能要统计带有分隔符的字符串中不重复的子字符串数。如下所示,我想知道单元格A1中不重复的数字有几个,应该怎么编写公式?

    wps页眉页脚怎么全部删除:excel怎么统计分隔符的字符串

    A:下面的数组公式可以完成单元格A1的字符串不重复值的统计:

    =SUM(N(MATCH(TRIM(MID(SUBSTITUTE(A1,”,”,REPT(“”,999)),ROW(INDIRECT(“1:” &LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1))*999-998,999)),TRIM(MID(SUBSTITUTE(A1,”,”,REPT(“”,999)),ROW(INDIRECT(“1:” &LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1))*999-998,999)), )=ROW(INDIRECT(“1:”& LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1))))

    结果为6。注意,公式输入完成后要按下Ctrl+Shift+Enter组合键。

    公式解析

    这么长的公式,一看到可能被吓着了,让我们来看看这个复杂的公式是怎么得来的。

    上面的公式可以简化为:

    =SUM(N(MATCH(单元格中的子字符串组成的数组, 单元格中的子字符串组成的数组, )=连续数字组成的数组))

    其中,生成单元格中的子字符串组成的数组的公式:

    TRIM(MID(SUBSTITUTE(A1,”,”,REPT(“”,999)),ROW(INDIRECT(“1:” &LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1))*999-998,999))

    ROW(INDIRECT(“1:”& LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1))获得从1至子字符串个数的连续数字。本例中,单元格中的子字符串组成的数组为:

    {“1″;”2″;”3″;”1″;”2″;”3″;”4″;”5″;”6”}

    上述数组作为MATCH函数的参数,查找每个子字符串在上面数组中出现的位置,得到下面的数组:

    {1;2;3;1;2;3;7;8;9}

    公式中的:

    ROW(INDIRECT(“1:”& LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1))

    生成由连续的数字组成的数组:

    {1;2;3;4;5;6;7;8;9}

    上面生成的两个数组进行比较:

    {1;2;3;1;2;3;7;8;9}={1;2;3;4;5;6;7;8;9}

    得到由布尔值组成的数组:

    {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}

    其中的TRUE表明是不重复的值,FALSE表明值出现的重复。因为不重复值出现的位置应该与其在子字符串中的位置一致,也就是说比较的结果为TRUE;如果位置不一致,则前面已经出现过该子字符串,即为重复值,比较的结果为FALSE。

    N函数将上述布尔值数组转换成由和1组成的数组:

    {1;1;1; ; ; ;1;1;1}

    数组中元素之和即为不重复的值的个数。

    提示:如果您觉得本文不错,请点击分享给您的好友!谢谢

    上一篇:wps动画制作教程:IFERROR函数怎么剔除不需要的值 下一篇:COUNTIFS函数处理数组

    郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。