El otro día un amigo profesor me comentaba que estaba creando una tabla excel para llevar la contabilidad de las notas de sus alumnos. Para ello durante el curso realizaba distintas pruebas (exámenes al fin y al cabo) que aportaban un porcentaje de la nota final, para el resto debía utilizar indicadores de comportamiento del alumno, el había tenido la idea de hacer uso de las puntuaciones de las pruebas para automáticamente rellenar los indicadores de comportamiento, he aquí donde entra la pregunta, ¿como hacer una hoja excel genérica en la que no tenga que estar haciendo AVERAGE(A1;B1...) etc si no que automáticamente coja las columnas que yo le diga)?
La solución que se me ocurrió en ese momento fue crear una nueva hoja donde rellenaríamos las relaciones que deben tenerse en cuenta para hacer la media, veamos pues:
Tenemos una tabla con notas por alumnos y exámenes:
Y la tabla de indicadores que deseamos rellenar automáticamente:
La idea que me propuso era básicamente hacer que la competencia número 1 se basara en la media del listening, vocabulario y examen T1, la del 2 en otras 4 distintas, tal que así. En estático sería simplemente en la columna B39=AVERAGE(B5;C5;D5), en la C39=AVERAGE(de otras cuatro)
Hasta aquí bien, pero si tengo otra clase con diferentes asignaturas y las relaciones cambian debo editar una por una todas las celdas de mi tabla de indicadores, ¿como hacer esta edición mas intuitiva?
Para ello usaremos una tabla de relaciones, abrimos una nueva página donde creamos una tabla que diga para el indicador uno usarás B,C y D, para el 2: C,D,F y H, etc:
Ahora queremos que en la celda B39 de la hoja Alumnos se evalúe esta relación, para ello haremos uso de una formula que obtenga de la hoja Referencias el numero de indicador según la columna en la que nos encontramos.
Un poco de teoría:
La función COLUMN() nos dice el valor numérico de la columna actual en el caso B39, COLUMN() = 2
La funcion ROW() nos devuelve el valor numérico de la fila en la que estamos B39, ROW() = 39
La función OFFSET nos permite obtener el contenido de una celda en relación a la que le indiquemos, por ej: OFFSET(B39;1;1) = contenido de la celda C40.
De esta forma vamos a ir construyendo la formula poco a poco:
=OFFSET(Referencias!$B$4;COLUMN()-1;1)
Como nos encontramos en la columna B, COLUMN() devuelve 2, siendo el indicador 1, le restamos 1 y cogemos el OFFSET respecto a Referencias!$B$4, esto nos devolverá el contenido de Referencias!C5 = "B"
Para seleccionar la fila debemos posicionarnos en la primera tabla de notas, esto lo haremos restandole a nuestra fila la distancia con la fila de la primera tabla:
=(ROW()-39+5)
Al estar en la fila 39 la operación nos devolverá "5".
Concatenando:
=OFFSET(Referencias!$B$4;COLUMN()-1;1) & (ROW()-39+5)
Resultado: B5
Ahora introducimos la función INDIRECT que permite hacer referencia al numero de celda que escribamos en otra, por ej: Si A1="B1" entonces INDIRECT(A1) nos devolverá el contenido de la celda B1.
=INDIRECT( OFFSET(Referencias!$B$4;COLUMN()-1;1) & (ROW()-39+5) )
Nos devolverá el contenido de B5 = 6
Haciendo esta misma operación con la media de los 6 indicadores sería tal que así:
=AVERAGE(
INDIRECT( OFFSET(Referencias!$B$4;COLUMN()-1;1) & (ROW()-39+5) );
INDIRECT( OFFSET(Referencias!$B$4;COLUMN()-1;2) & (ROW()-39+5) );
INDIRECT( OFFSET(Referencias!$B$4;COLUMN()-1;3) & (ROW()-39+5) );
INDIRECT( OFFSET(Referencias!$B$4;COLUMN()-1;4) & (ROW()-39+5) );
INDIRECT( OFFSET(Referencias!$B$4;COLUMN()-1;5) & (ROW()-39+5) );
INDIRECT( OFFSET(Referencias!$B$4;COLUMN()-1;6) & (ROW()-39+5) )
)
Cuidado con olvidarse un punto y coma en el último pues eso haría que excel creyera que hay otro valor más e hiciera media con cero.
Ya solo queda copiar el contenido de B39 en todas las demás:
