Tengo un gran conjunto de datos de estudiantes y las clases que han tomado. Cada estudiante ha tomado de 12 a 18 de alrededor de 80 clases disponibles. Usando Excel (2013), me gustaría saber, para cualquier par de clases, cuántos estudiantes tomaron ambas. Imagino una tabla con las 80 clases en filas y columnas, y luego, para cada intersección, vería un recuento de cuántos estudiantes tomaron esa combinación.
Los datos llegan como un archivo Excel con una fila por estudiante por clase:
Student Class
Smith E101
Jones E101
Parker E101
Brown E102
Green E102
Smith E201
Jones E202
Parker E201
Brown E202
Green E203
...
Resultados previstos:
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 solo necesito la mitad diagonal de lo anterior, ya que la otra mitad lo refleja).
Utilicé una tabla dinámica para obtener los datos en una tabla con los estudiantes como filas y todas las clases posibles como columnas, mostrando un 1 donde un estudiante tomó una clase determinada.
E101 E102 E201 E202 E203 ...
Smith 1 1
Jones 1 1
Parker 1 1
Brown 1 1
Green 1 1
...
Pero luego no sé cómo proceder, con la menor intervención manual posible, para obtener el resultado deseado.
¿Alguien puede sugerir una forma de lograr el resultado que necesito en Excel? He hecho una búsqueda bastante exhaustiva pero no he encontrado nada.
¿O debería buscar otro software?
Respuesta1
Esto es bastante sencillo de hacer en Excel con una fórmula que opera en su tabla dinámica.
Con las dos mesas dispuestas así.
ingrese la siguiente fórmula J2y presione Ctrl-Intro/copiar-pegar/rellenar&derecha/autocompletar en el resto de las celdas de la tabla:
=
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
)
)
Explicación:
El primer argumento de la COUNTIFS()función es la columna generada dinámicamente de la tabla dinámica correspondiente al encabezado de la columna de la tabla de salida. Es un poco más fácil de entender si nos fijamos en los pasos intermedios evaluados (para la celda 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
(Tenga en cuenta que los segundos argumentos de cada uno INDEX()son solo las filas inicial y final completamente dinámicas, respectivamente, de la tabla dinámica).
Lo mismo ocurre con el tercer argumento de la COUNTIFS()función, pero esta vez la columna generada dinámicamente de la tabla dinámica corresponde a lafilaencabezado de la tabla de salida. Para la celda L2se evalúa como $B$1:$B$6.
Así la COUNTIFS()función en L2se convierte en
COUNTIFS($D$1:$D$6,1,$B$1:$B$6,1)
que es la forma estándar de contar el número de filas (estudiantes) dondeambosLas columnas contienen a 1(es decir, el estudiante estaba matriculado en ambas clases).
La IF()función de encapsulación solo está ahí para garantizar que las celdas diagonales estén en blanco.