如何统计带分隔符的字符串中不重复的子字符串数?
时间:2022-02-15 01:47:51
Q:某些情况下,我们可能要统计带有分隔符的字符串中不重复的子字符串数。如下所示,我想知道单元格A1中不重复的数字有几个,应该怎么编写公式?
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}
数组中元素之和即为不重复的值的个数。
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
在Word中整理笔记页中的手写内容的方法
![](https://img.aspxhome.com/file/2023/3/17953_0s.jpg)
最新屏蔽 iOS 16 更新描述文件已发布!附上安装和使用方法
![](https://img.aspxhome.com/file/2023/8/45598_0s.png)
一个excel表格公式填写金额收据
![](https://img.aspxhome.com/file/2023/0/a142210_0s.png)
Word 常用使用小技巧详解
Word 2010文档中输入键盘没有的符号
今年母亲节是五月几日?给你excel图表三种算法
![](https://img.aspxhome.com/file/2023/3/a142053_0s.png)
Word2007怎么制作数据图表
![](https://img.aspxhome.com/file/2023/2/19622_0s.jpg)
Win10系统打开组策略提示“命名空间已被定义”怎么解决?
![](https://img.aspxhome.com/file/2023/2/50182_0s.jpg)
Win10 KB4524244补丁有哪些存在的问题?Win10 KB4524244存在的问题详解
![](https://img.aspxhome.com/file/2023/8/52058_0s.jpg)
Win11清理临时文件教程
![](https://img.aspxhome.com/file/2023/1/53321_0s.jpg)
excel表格中的图片怎么缩放?
![](https://img.aspxhome.com/file/2023/8/40188_0s.jpg )
win10启动慢怎么优化?win10启动慢优化教程
![](https://img.aspxhome.com/file/2023/7/48387_0s.jpg)
如何双面打印word文档?
![](https://img.aspxhome.com/file/2023/6/33606_0s.png)
Win10玩红警黑屏有声音和鼠标怎么解决?
![](https://img.aspxhome.com/file/2023/4/49574_0s.jpg)
Excel 2019如何设置默认视图
![](https://img.aspxhome.com/file/2023/7/a141097_0s.jpg)
标记出Word2007文档格式不一致的地方
![](https://img.aspxhome.com/file/2023/8/21158_0s.jpg)
Excel总是弹出循环引用警告怎么办?
word2013怎样自动生成目录 word目录自动生成的设置方法
Word文档如何使用求和功能?Word文档使用求和教程
![](https://img.aspxhome.com/file/2023/4/31514_0s.jpg)