细雨带风 发表于 2021-10-21 12:43:00

求助单元格内重复与否

求助单元格内重复与否

仕宾 发表于 2021-11-5 23:24:48

=COUNT(0/ISTEXT(FILTERXML("<a><b>"&SUBSTITUTE(A3,",","</b><b>")&"</b></a>","a/b")))

=COUNT(0/ISTEXT(FILTERXML("<a><b>"&SUBSTITUTE(A3,",","</b><b>")&"</b></a>","a/b")))

都是数组，2013以上支持
大影影指点修正，这样更好些。
=COUNT(N(FILTERXML("<a><b>"&SUBSTITUTE(A3,",","</b><b>")&"</b></a>","a/b")))


扬眉吐气 发表于 2021-11-21 16:45:00

不重复个数
=COUNT(0/(FREQUENCY(MATCH(TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A3)-LEN(SUBSTITUTE(A3,",",))+1))*99-98,99)),TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A3)-LEN(SUBSTITUTE(A3,",",))+1))*99-98,99)),),MATCH(TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A3)-LEN(SUBSTITUTE(A3,",",))+1))*99-98,99)),TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A3)-LEN(SUBSTITUTE(A3,",",))+1))*99-98,99)),))=1))
重复个数
=COUNT(0/(FREQUENCY(MATCH(TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A3)-LEN(SUBSTITUTE(A3,",",))+1))*99-98,99)),TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A3)-LEN(SUBSTITUTE(A3,",",))+1))*99-98,99)),),MATCH(TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A3)-LEN(SUBSTITUTE(A3,",",))+1))*99-98,99)),TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A3)-LEN(SUBSTITUTE(A3,",",))+1))*99-98,99)),))>1))

黄小毅 发表于 2021-11-22 08:03:34

这个是正则表达式吗

呼伦贝尔博涵二手工程机械 发表于 2021-11-28 23:30:36

filterxml函数，较为特殊的一个函数可参考大福将版主原创作品附件，学习一下。

喜欢淘气 发表于 2021-12-19 10:21:43

感谢,看了一下看都看不懂没接触过

陆鸿博 发表于 2021-12-19 16:31:53

自定义函数，第一参数指定要计算的单元格，第二参数指定分隔符，第三参数为0时，计算不重复数量，非0为重复数量。
Function demo(rng As Range, s As String, tf As Byte)
Dim arr, dic As Object, i%, brr
Set dic = CreateObject("scripting.dictionary")
arr = Split(rng, ",")
For i = LBound(arr) To UBound(arr)
dic(arr(i)) = dic(arr(i)) + 1
Next i
brr = dic.items
For i = LBound(brr) To UBound(brr)
If brr(i) = 1 Then k = k + 1
Next i
If tf = 0 Then
demo = k
Else
demo = dic.Count - k
End If

End Function

无所谓天黑 发表于 2022-1-4 08:02:07

不重复个数=COUNTA(UNIQUE(FILTERXML("<a><b>"&SUBSTITUTE(A3,",","</b><b>")&"</b></a>","a/b")))，重复个数=COUNTA(FILTERXML("<a><b>"&SUBSTITUTE(A3,",","</b><b>")&"</b></a>","a/b"))-B3

星河漫步 发表于 2022-1-8 12:13:35

B3{=SUM(N(MMULT(N(TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),ROW($1:$9)*99-98,99))=TRANSPOSE(TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),ROW($1:$9)*99-98,99)))),ROW(1:9)^0)=1))
C3{=SUM(N(MATCH(TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),ROW($1:$9)*99-98,99)),TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),ROW($1:$9)*99-98,99)),)=ROW($1:$9)))-1-B3

疯疯的骆驼 发表于 2022-1-21 04:22:26

不重复的数组公式
=COUNT(0/(LEN(A3)-LEN(SUBSTITUTE(A3,MID(A3,4*ROW(1:9)-3,3),))=3))
重复的
=COUNT(0/IF(MATCH(MID(A3,4*ROW(1:9)-3,3),MID(A3,4*ROW(1:9)-3,3),)=ROW(1:9),ROW(1:9)))-B3-1
页: [1]
查看完整版本: 求助单元格内重复与否