Tenho um grande conjunto de dados de alunos e as aulas que eles frequentaram. Cada aluno assistiu de 12 a 18 das cerca de 80 aulas disponíveis. Usando o Excel (2013), gostaria de saber, para qualquer par de aulas, quantos alunos fizeram as duas. Imagino uma tabela com as 80 classes em linhas e colunas e, então, para cada interseção, veria uma contagem de quantos alunos fizeram aquela combinação.
Os dados chegam como um arquivo 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
...
Resultado previsto:
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, preciso apenas de uma metade diagonal do item acima, pois a outra metade reflete.)
Usei uma tabela dinâmica para colocar os dados em uma tabela com os alunos como linhas e todas as aulas possíveis como colunas, mostrando um 1 onde um aluno fez uma determinada aula.
E101 E102 E201 E202 E203 ...
Smith 1 1
Jones 1 1
Parker 1 1
Brown 1 1
Green 1 1
...
Mas não sei como proceder, com o mínimo de intervenção manual possível, para obter o resultado desejado.
Alguém pode sugerir uma maneira de obter o resultado que preciso no Excel? Fiz uma pesquisa bastante extensa, mas não encontrei nada.
Ou devo procurar outro software?
Responder1
Isso é bastante simples de fazer no Excel com uma fórmula que opera em sua tabela dinâmica.
Com as duas mesas configuradas assim
insira a seguinte fórmula J2e pressione Ctrl-enter/copiar-colar/preencher-para baixo e para a direita/preencher automaticamente no restante das células da tabela:
=
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 COUNTIFS()função é a coluna gerada dinamicamente da tabela dinâmica correspondente ao cabeçalho da coluna da tabela de saída. Fica um pouco mais fácil de entender se observarmos as etapas intermediárias avaliadas (para cell 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 um INDEX()são apenas as linhas inicial e final totalmente dinâmicas, respectivamente, da tabela dinâmica.)
Da mesma forma para o terceiro argumento da COUNTIFS()função, mas desta vez a coluna gerada dinamicamente da tabela dinâmica corresponde aolinhacabeçalho da tabela de saída. Para célula, L2é avaliado como $B$1:$B$6.
Assim, a COUNTIFS()função em L2torna-se
COUNTIFS($D$1:$D$6,1,$B$1:$B$6,1)
que é a forma padrão de contar o número de linhas (alunos) ondeambosas colunas contêm um 1(ou seja, o aluno estava matriculado em ambas as turmas).
A IF()função de encapsulamento existe apenas para garantir que as células diagonais fiquem em branco.