contagem única de valores em uma célula separada por vírgula no excel

Question

Como obtenho a contagem única de valores em uma célula separada por vírgula no excel?

Por exemplo: a célula B13 tem o valor

1,1,2,3,7,1

Usando a fórmula abaixo, obtemos a contagem de valores separados por , como 6.

=1+LEN(B13)-LEN(SUBSTITUTE(B13,";",""))

Mas eu quero contar os valores únicos na célula B13, que é 4. Alguém pode me ajudar a conseguir isso?

Nota: Esta questão é semelhante a Como posso contar valores separados por vírgula exclusivos no Excel 2010 . No entanto, esse é um caso especial e restritivo (os valores são apenas dígitos únicos), o que permite soluções que não se aplicariam à outra pergunta.

    

Answer 1

Instale a seguinte Função definida pelo usuário (UDF) em um módulo padrão:

Public Function CountUnique(r As Range) As Long
    Dim c As Collection
    Set c = New Collection
    ary = Split(r.Text, ",")
    On Error Resume Next
    For Each a In ary
        c.Add a, CStr(a)
        If Err.Number = 0 Then
            CountUnique = CountUnique + 1
        Else
            Err.Number = 0
        End If
    Next a
    On Error GoTo 0
End Function

Funções Definidas pelo Usuário (UDFs) são muito fáceis de instalar e usar:

1. ALT-F11 exibe a janela do VBE
2. ALT-I ALT-M abre um novo módulo
3. cole o material e feche a janela do VBE

Se você salvar a pasta de trabalho, a UDF será salva com ela. Se você estiver usando uma versão do Excel posterior a 2003, deverá salvar o arquivo como .xlsm em vez de .xlsx

Para remover o UDF:

1. abrir a janela do VBE como acima
2. limpe o código
3. feche a janela do VBE

Para usar o UDF do Excel:

=CountUnique(A1)

Para saber mais sobre macros em geral, consulte:

link

e

link

e para detalhes sobre UDFs, consulte:

link

As macros devem estar ativadas para que isso funcione!

Por exemplo:

    

Answer 2

Parece, pela sua fórmula, que sua lista contém apenas números de um único dígito. Aqui está uma maneira de fazer isso:

=10-ISERROR(FIND(0,B13))-ISERROR(FIND(1,B13))-ISERROR(FIND(2,B13))
   -ISERROR(FIND(3,B13))-ISERROR(FIND(4,B13))-ISERROR(FIND(5,B13))
   -ISERROR(FIND(6,B13))-ISERROR(FIND(7,B13))-ISERROR(FIND(8,B13))
   -ISERROR(FIND(9,B13))

Eu dividi a fórmula em linhas separadas para facilitar a leitura e tornar a lógica visível. Se você deseja copiar e colar, você precisará excluir os retornos de carro de formatação e espaços extras.

Isso começa com todos os dez dígitos (0 a 9), como valores únicos possíveis que podem estar presentes. Olha na corda para cada dígito. Se não estiver lá, reduz o número de possíveis valores exclusivos em um (FIND sem êxito retorna um erro, tornando ISERROR true, que é tratado pelo Excel como 1).