Como posso contar valores separados por vírgulas exclusivos no Excel 2010

Question

Eu preciso criar uma fórmula para percorrer uma coluna de valores separados por vírgulas e contar o número de valores exclusivos visualizados. Estou usando o Excel 2010. Tenho uma captura de tela de alguns dados de exemplo ...

Asaídadafórmulanessecasodeveser5.Emoutraspalavras,afórmuladevecontarosseguintesvalores:2.3.0,2.4.1,2.4.2,2.4.3,2.4.4

Eudescobricomopossosimplesmentecontaronúmerobrutodevaloresseparadosporvírgulascomoeste...

=SUMPRODUCT(--(M123:M127<>""),LEN(M123:M127)-LEN(SUBSTITUTE(M123:M127,",",""))+1)

No entanto, essa fórmula gera 7 , porque conta 2.4.3 e 2.4.4 duas vezes.

Não sei como rejeitar valores repetidos separados por vírgulas em células diferentes da mesma coluna.

Devido aos outros sistemas que devem interagir com a planilha, as respostas só podem usar uma fórmula; eles não devem usar o VBA ou algum tipo de filtro.

    

Answer 1

Se você puder adicionar algumas fórmulas intermediárias à sua planilha, aqui está uma possível solução.

Com base nos intervalos no seu exemplo:

Coloque na célula N123 Isso retorna a parte à esquerda de , ou a célula inteira se não houver ,

=IFERROR(LEFT($M123,FIND(",",$M123)-1),M123)

Coloque na célula O123 Isso retorna a parte à direita de , ou - se não houver ,

=IFERROR(TRIM(MID($M123,FIND(",",$M123)+1,999)),"-")

Coloque na célula P123 Isso retorna 1 se a célula N123 for única na lista, mais 1 se a célula O123 for exclusiva

=AND(ROW()+1=ROW($N$123:$N$127)+MATCH($N123,$N$123:$N$127,0),$N123<>"-")
+AND(ROW()=IFERROR(MATCH($O123,$N$123:$N$127,0),""),$O123<>"-")
+AND(ISNA(MATCH($N123,$N$123:$N$127,0)),ROW()+1=IFERROR(ROW($N$123:$N$127) 
    +MATCH($N123,$O$123:$O$127,0),FALSE),$N123<>"-")
+AND(ISNA(MATCH($O123,$N$123:$N$127,0)),ROW()+1=ROW($N$123:$N$127)
    +MATCH($O123,$O$123:$O$127,0),O123<>"-")

Copie as células N123..P123 para N123..P127

O número de itens exclusivos é =SUM(P123:Q127)

Isso pressupõe que haja no máximo 2 itens em cada célula de dados. Se esse não for o caso, você precisará adicionar mais colunas intermediárias e estender a fórmula em O123 de acordo. Se houver muitos mais de dois, isso rapidamente sairá do controle.

FWIW, você diz que uma solução VBA não é possível, mas é uma função definida pelo usuário VBA ok?

Seria parecido com =CountUnique(M123:M127)

    

Answer 2

Aqui está outra solução usando as funções do VBA. Cole o seguinte em um módulo.

Function ListCount(list As String, delimiter As String) As Long
Dim arr As Variant
arr = Split(list, delimiter)
ListCount = UBound(arr) - LBound(arr) + 1
End Function

Function RemoveDuplicates(list As String, delimiter As String) As String
Dim arrSplit As Variant, i As Long, tmpDict As New Dictionary, tmpOutput As String
arrSplit = Split(list, delimiter)
For i = LBound(arrSplit) To UBound(arrSplit)
    If Not tmpDict.Exists(arrSplit(i)) Then
        tmpDict.Add arrSplit(i), arrSplit(i)
        tmpOutput = tmpOutput & arrSplit(i) & delimiter
    End If
Next i
If tmpOutput <> "" Then tmpOutput = Left(tmpOutput, Len(tmpOutput) - Len(delimiter))
RemoveDuplicates = tmpOutput
'housekeeping
Set tmpDict = New Dictionary
End Function

Em sua planilha, você pode usar a seguinte fórmula:

=ListCount(RemoveDuplicates(A1,", "),", ")

em que A1 é sua lista separada por vírgulas.

NOTA: Você terá que adicionar a referência Microsoft Scripting Runtime ao seu projeto VBA (em Tools > References... no Projeto VBA Janela do editor).

    

Answer 3

Com base na opinião de Chris Neilsen, vou tentar modificar nossos sistemas e usar isso ... Fonte: Mr Fóruns do Excel - Post de 1 de janeiro de 2010 às 10h04

Function UNIQUECOUNTIF(ByRef SR As Range, _
                        ByRef RR As Range, _
                        Optional ByVal Crit As Variant, _
                        Optional NCOUNT As Boolean = False, _
                        Optional POSTCODE As Boolean = False) As Long
Dim K1, K2, i As Long, c As Long, x, n As Long
K1 = SR: K2 = RR
With CreateObject("scripting.dictionary")
    For i = 1 To UBound(K1, 1)
        If Not IsMissing(Crit) Then
            If LCase$(K1(i, 1)) = LCase$(Crit) Then
                If POSTCODE Then
                    x = Split(Replace(LCase$(K2(i, 1)), ",", " "), " ")
                Else
                    x = Split(LCase$(K2(i, 1)), ",")
                End If
                For c = 0 To UBound(x)
                    If POSTCODE Then
                        If IsNumeric(x(c)) Then
                            If Not .exists(x(c)) Then
                                .Add x(c), 1
                            ElseIf NCOUNT Then
                                .Item(x(c)) = .Item(x(c)) + 1
                            End If
                        End If
                    Else
                        If Not .exists(x(c)) Then
                            .Add x(c), 1
                        ElseIf NCOUNT Then
                            .Item(x(c)) = .Item(x(c)) + 1
                        End If
                    End If
                Next
            End If
        Else
            If POSTCODE Then
                x = Split(Replace(LCase$(K2(i, 1)), ",", " "), " ")
            Else
                x = Split(LCase$(K2(i, 1)), ",")
            End If
            For c = 0 To UBound(x)
                If POSTCODE Then
                    If IsNumeric(x(c)) Then
                        If Not .exists(x(c)) Then
                            .Add x(c), 1
                        ElseIf NCOUNT Then
                            .Item(x(c)) = .Item(x(c)) + 1
                        End If
                    End If
                Else
                    If Not .exists(x(c)) Then
                        .Add x(c), 1
                    ElseIf NCOUNT Then
                        .Item(x(c)) = .Item(x(c)) + 1
                    End If
                End If
            Next
        End If
    Next
    If .Count > 0 Then UNIQUECOUNTIF = Application.Sum(.items)
End With
End Function