Contando combinações de pares de várias opções no Excel

Question

Eu tenho um grande conjunto de dados de alunos e as aulas que eles fizeram. Cada aluno levou de 12 a 18 de cerca de 80 aulas disponíveis. Usando o Excel (2013), gostaria de descobrir, para qualquer par de classes, quantos alunos foram os dois. Eu visualizo uma tabela com as 80 classes como linhas e colunas e, em seguida, para cada interseção, eu veria uma contagem de quantos alunos usaram essa combinação.

Os dados chegam como um arquivo do Excel com uma linha por aluno por turma:

Student  Class
Smith    E101
Jones    E101
Parker   E101
Brown    E102
Green    E102
Smith    E201
Jones    E202
Parker   E201
Brown    E202
Green    E203
...

Saída prevista:

      E101  E102  E201  E202  E203  ...
E101        0     2     1     0
E102  0           0     1     1    
E201  2     0           0     0
E202  1     1     0           0
E203  0     1     0     0
...

(Obviamente eu só preciso de uma metade diagonal do acima, como a outra metade espelha.)

Eu usei uma tabela dinâmica para colocar os dados em uma tabela com alunos como linhas e todas as classes possíveis como colunas, mostrando um 1 em que um aluno recebeu uma determinada turma.

        E101  E102  E201  E202  E203  ...
Smith   1           1           
Jones   1                 1         
Parker  1           1          
Brown         1           1     
Green         1                 1
...

Mas, então, fico preso em como proceder, com o mínimo de intervenção manual possível, para o resultado desejado.

Alguém pode sugerir uma maneira de obter a saída de que preciso no Excel? Eu fiz uma pesquisa bastante extensa, mas não encontrei nada.

Ou devo procurar outro software?

    

Answer 1

Isso é bastante simples de fazer no Excel com uma fórmula que opera na tabela dinâmica.

Com as duas tabelas configuradas assim

insiraaseguintefórmulaemJ2ectrl-enter/copy-paste/fill-down&right/autopreenchimentonorestantedascélulasdatabela:

= IF( J$1=$I2, "", COUNTIFS( INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH(J$1,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH(J$1,$A$1:$F$1,0)), 1, INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH($I2,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH($I2,$A$1:$F$1,0)), 1 ) )

Explicação:

O primeiro argumento da função COUNTIFS() é a coluna gerada dinamicamente da tabela dinâmica correspondente ao cabeçalho da coluna da tabela de saída. É um pouco mais fácil de entender, se olharmos para as etapas intermediárias avaliadas (para a célula L2 ):

INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH(L$1,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH(L$1,$A$1:$F$1,0))
→ INDEX($A$1:$F$6,1,MATCH("E201",$A$1:$F$1,0)):INDEX($A$1:$F$6,6,MATCH("E201",$A$1:$F$1,0))
→ INDEX($A$1:$F$6,1,4):INDEX($A$1:$F$6,6,4)
→ $D$1:$D$6

(Observe que os segundos argumentos de cada INDEX() são apenas as linhas iniciais e finais totalmente dinâmicas, respectivamente, da tabela dinâmica.)

Da mesma forma, para o terceiro argumento da função COUNTIFS() , mas desta vez a coluna gerada dinamicamente da tabela dinâmica corresponde ao cabeçalho linha da tabela de saída. Para a célula L2 , ela é avaliada como $B$1:$B$6 .

Assim, a função COUNTIFS() em L2 torna-se

COUNTIFS($D$1:$D$6,1,$B$1:$B$6,1)

qual é o modo padrão de contar o número de linhas (alunos) onde ambas colunas contêm 1 (ou seja, o aluno foi inscrito em ambas as classes).

A função IF() de encapsulamento está lá apenas para garantir que as células diagonais estejam em branco.