如何统计带分隔符的字符串中不重复的子字符串数?

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

图1

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)),0)=ROW(INDIRECT("1:"& LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))))

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

数据分析实战

作者:(美)托马兹·卓巴斯(Tomasz Drabas)

当当

公式解析

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

上面的公式可以简化为:

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

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

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

其原理详见《Excel实用公式4:从分隔符连接的字符串中提取子字符串》中的讲解。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函数将上述布尔值数组转换成由0和1组成的数组:

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

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

(0)

相关推荐

  • 新增函数concat和老函数concatenate的用法

    很多时候我们都会用文本连接符&进行连接运算,今天就说2个关于文本连接的函数,一个是concatenate,一个是concat.虽然它们写法上有点相似,但是差别还是挺大的.concatenate ...

  • 史上最强文本连接函数textjoin的用法

    我们昨天学了2个文本连接函数,今天来学一个功能更强大的文本连接函数--textjoin.此函数也是新增函数,要office2016版以上才可能有这个函数,它的功能是使用分隔符连接文本字符串区域. -0 ...

  • 如何批量的去计算单元格当中的人员总数?

    如何批量的去计算单元格当中的人员总数?

  • 从分隔符连接的字符串中提取子字符串

    有时候,在工作表单元格中有一些以某分隔符连接的字符串,如图1中的单元格A1,其内容是以逗号连接城市名. 图1 如果我们想要提取其中的某个城市,例如第8个子字符串表示的城市名,则可以使用下面的公式: = ...

  • 统计带空白单元格的区域中不重复值的个数

    下表记录的是一些人的信息,包含姓名,年龄和职务.其中有些人没有职务,就是空白单元格.求不重复职务的个数.通过动图的演示,可以看到不重复的职务有5个.如何通过函数计算出来呢? 假如没有空白单元格,很多小 ...

  • 按分隔符将字符串中的数据分列放置

    原创作者 | 李锐 微信公众号 | Excel函数与公式(ID:ExcelLiRui) 个人微信号 | (ID:ExcelLiRui520) 按分隔符将字符串中的数据分列放置 今天我来结合一个实际案例 ...

  • 在Excel中提取单元格混合字符串中的数字与英文的函数介绍

    如何将某个单元格中的由英文与数字混合组成的字符串中的英文与中文分别提取到其它单元格? 首先说明,在Excel中没有满足这种功能的现成的函数,要想使用这种函数,就必须使用自定义的函数. 下面我们先看效果 ...

  • 菜鸟记302-巧用查找替换统计带字母的数值,这个思路您想到了吗?

    万一您身边的朋友用得着呢? 关键词:EXCEL2016:查找替换:SUMIF函数:操作难度* 一个学期下来,小菜面对这样一张累计18周实验学时统计表: 图1:数据源示意图 表格里面按不同分类记录为B和 ...

  • ​菜鸟记468-制作1000行不带分隔符的获奖名单,您需要加班吗?

    万一您身边的朋友用得着呢? 各位朋友早上好,小菜继续和您分享经验之谈,截止今日小菜已分享450+篇经验之谈,可以文章编号或关键词进行搜索 以下才是今天的正式内容-- 摘要:本文介绍利用COUNTIF函 ...

  • 沈阳绝版老照片,带你回到记忆中的童年

    1973年沈阳扒掉有轨电车铁轨的义务劳动场面 1973年庆祝有轨电车扒掉,沈阳站前 1979年的南湖鲁园地区 1979年和平大街 1980年沈阳钟厂 1981年沈阳机车车辆厂女民兵高炮连 1982年沈 ...

  • Python|字符串中第二大的数字

    问题描述给你一个混合字符串s,请你返回s中第二大的数字,如果不存在第二大的数字,请你返回-1.混合字符串由小写英文字母和数字组成.示例:输入:s = 'dfa12321afd'输出:2解决方案这是一道 ...

  • 汝城话||带我们回到记忆中的乡村夜晚

     小时候在农村里长大的我们,对于那些熟悉的场景,一经想起,便是记忆犹新中.那时的乡民,讲究的是勤劳致富.他们从早忙到晚,忙里忙外,很少有闲谈下来的闲心.只有当夜幕来临,在家里安顿好一切后,才会借着吃夜 ...