求助单元格内重复与否

细雨带风

求助单元格内重复与否

求助单元格内重复与否.zip (7.94 KB, 下载次数: 103)

2021-10-21 12:43 上传

点击文件名下载附件
求助单元格内重复与否.zip

仕宾

=COUNT(0/ISTEXT(FILTERXML("<a><b>"&SUBSTITUTE(A3,",","</b><b>")&"</b></a>","a/b[not(following::*=.)]")))

=COUNT(0/ISTEXT(FILTERXML("<a><b>"&SUBSTITUTE(A3,",","</b><b>")&"</b></a>","a/b[not(preceding::* =.)][following::* =.]")))

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

扬眉吐气

不重复个数
=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))

黄小毅

这个是正则表达式吗

呼伦贝尔博涵二手工程机械

filterxml函数，较为特殊的一个函数可参考大福将版主原创作品附件，学习一下。
【原创】函鲜-FILTERXML-2019-9-17.zip (261.08 KB, 下载次数: 207)

2021-10-21 12:43 上传

点击文件名下载附件
【原创】函鲜-FILTERXML-2019-9-17.zip

喜欢淘气

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

陆鸿博

自定义函数，第一参数指定要计算的单元格，第二参数指定分隔符，第三参数为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

无所谓天黑

不重复个数=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

星河漫步

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

疯疯的骆驼

不重复的数组公式
=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