一列用逗号隔开的姓名，怎么统计每个姓名出现的个数，并在另外一列反映

静听溪流

loelaine 发表于 2021-6-26 22:15
大神，这两天在单位太忙，没来得及实验，但是我在家里的电脑试了，这种只适合人员不超过三个的，假如人员 ...

那就需要通过indirect等函数来  来限定范围了

不过这种不规则的东西   还是通过VBA自定义函数或者直接vba代码来得方便

loelaine

静听溪流 发表于 2021-6-26 22:51
那就需要通过indirect等函数来  来限定范围了

不过这种不规则的东西   还是通过VBA自定义函数或者直接 ...

那更难了

静听溪流

Function TongJi(Trng As Range, Rng As Range)
   Dim StrX, ArrStore() As String, SourceStr As String
   Dim X As Range
   For Each X In Trng
      SourceStr = SourceStr & "，" & X
   Next
      SourceStr = SourceStr & "，"
   If Rng = "" Then
        TongJi = ""
   Else
       ArrStore = Split(Rng, "，")
       For Each StrX In ArrStore
           TongJi = TongJi & "," & UBound(Split(SourceStr, "，" & StrX & "，"))
       Next
       TongJi = Mid(TongJi, 2)
   End If
End Function

zpy2

=filterxml(webservice("http://e.anyoupin.cn/?s=" & encodeurl("select 姓名||','||group_concat(日期) from 合并姓名明细表 group by 姓名"),"//td")

loelaine

静听溪流 发表于 2021-6-26 23:19
Function TongJi(Trng As Range, Rng As Range)
   Dim StrX, ArrStore() As String, SourceStr As String ...

膜拜大神！

lelewa0505

名字分列黏贴一列，数据透视，再加countif公式。

祝洪忠-

loelaine 发表于 2021-6-26 22:15
大神，这两天在单位太忙，没来得及实验，但是我在家里的电脑试了，这种只适合人员不超过三个的，假如人员 ...

1. =SUBSTITUTE(TRIM(TEXT(SUBSTITUTE(SUM(MMULT(1^TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(A$2:A$99)))),ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(A2,"，",REPT(" ",99)),TRANSPOSE(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"，",))+1)))*99-98,99)),A$2:A$6))*1)*10^(10-TRANSPOSE(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"，",))+1))))),0,),"# # 0"))," ","，")

复制代码

看看公式在03版本里能不能运行。