Excel: contando frases únicas, separadas por barras

Question

Eu tenho alguns dados do Excel que parecem um pouco com isto:

John / Tim      Upheld
Sam             Upheld
Tim / Dave      Upheld
Sam / John      Not Upheld
Tim             Not Upheld
Dave / Tim      Upheld

Como posso fazer o seguinte no Excel, se é que posso?

1. Conte cada nome único separado por barras na coluna A e classifique-os por ordem de ocorrências (assim Tim [4], Dave [2], Sam [2], John [1]).
2. Conte o número de vezes que cada nome exclusivo aparece com 'Confirmado' na coluna B (então Tim [3], Dave [2], John [1], Sam [1]).

Isso seria melhor feito usando um script Python ou semelhante?

    

Answer 1

Algo certamente poderia ser combinado usando fórmulas de matriz, mas provavelmente seria bastante confuso. Eu não gostaria de ter uma punhalada sem saber quantas linhas existem, este número é fixo, são os nomes conhecidos de antemão, os nomes vão mudar ao longo do tempo, etc.

Eu teria pensado que seria melhor fazer isso em um script de alguma descrição, porém, o VBA provavelmente seria a escolha lógica.

    

Answer 2

Colocando seus dados no intervalo A1: B6, e os nomes são o intervalo C1: C4 a fórmula do array (entrar com Ctrl + Deslocar + Enter em vez de apenas kbd > Enter) em D1, copiando a coluna

=SUM(IF(ISNUMBER(FIND(C1,$A$1:$A$6)),1,0))

dá as respostas corretas para a primeira parte.

A segunda parte se torna mais complicada ...

Em vez de usar formulas de matriz, você poderia usar apenas uma carga de colunas extras, duas para cada nome.

Assim, as duas colunas para John começariam:

=IF(ISNUMBER(FIND("John",$A1)),1,0)

e

=IF(AND(ISNUMBER(FIND("John",$A1)),B1="Upheld"),1,0)

Suas respostas são apenas os totais das colunas (que, por questões de ordem, podem ser ocultadas e os totais usados em uma boa tabela de resumo).