jueves, 16 de julio de 2009

Resúmenes automáticos - Consolidación

Cuando queremos obtener resultados provenientes de varias hojas de cálculo independientes, podemos consolidarlas de forma automática en una única hoja de cálculo maestra.

De forma genérica, podemos hablar de dos tipos de consolidación: por posición o por categorías.

CONSOLIDAR POR POSICIÓN
Esta modalidad se utiliza cuando todas las hojas a resumir tienen la misma estructura, la misma cantidad de datos y éstos están colocados en el mismo orden. Por ejemplo, supongamos que tenemos tres hojas con nombres de asignaturas y queremos resumirlas en una cuarta, hallando la nota media:


En la hoja en la que deseamos ver el resultado, sólo escribiremos los datos que son comunes a todas las hojas, es decir, el encabezamiento de las columnas y de las filas, de la siguiente forma:


Empezaremos por colocarnos en la celda que debe tener el primer resultado, en nuestro caso, B3. A continuación, en el menú Datos hacemos clic sobre Consolidar, tras lo que aparecerá el siguiente cuadro de diálogo:


En función escogeremos, el tipo de cálculo que queremos aplicar sobre los datos, para este ejemplo: Promedio. A continuación, en Referencia, tendremos que seleccionar por cada página las notas de todos los alumnos y pulsar en Agregar, para que en el cuadro inferior aparezcan los tres rangos que queremos consolidar, y que deberán tener el mismo tamaño. El resultado, deberá ser similar al siguiente:



Ahora sólo nos queda Aceptar, para ver el resultado que Excel nos ofrece:

Este método aunque rápido, tiene como desventaja que los resultados obtenidos son valores; es decir, si cambiamos algún dato en las hojas originales, el resultado no cambia; para actualizar la información, simplemente nos volveríamos a colocar en B2, volvemos a entrar en Consolidar y aceptamos. Pero no todo es negativo, esta forma de consolidar SI puede deshacerse.
Si lo que nos interesa es contar siempre con datos actualizados, en el cuadro de consolidación tendremos que marcar la opción Crear vínculos con los datos de origen antes de aceptar, pero asegúrate de estar correctamente colocado ya que este método NO se puede deshacer. Si nos hemos equivocado, la única opción es eliminar las filas que se generan y volver a escribir los datos iniciales.


CONSOLIDAR POR CATEGORÍAS
Esta modalidad se utiliza cuando las hojas comparten cierta misma estructura, pero la cantidad y orden de los datos puede variar de una a otra. Vamos a modificar el ejemplo anterior, para lo cual, quitamos a Ana de Inglés, en Física cambiamos a Daniela por Bárbara, y en latín añadimos a Esther.


La forma de trabajar es casi idéntica a la del caso anterior, con alguna salvedad. Ahora en la hoja de resumen, sólo figurarán los datos que son idénticos en los tres casos, es decir, la información que aparece en la fila 2, y antes de consolidar nos colocaremos en A3, ya que al cambiar también los nombres, la consolidación deberá resumirlos.
Una vez en el cuadro, volvemos a añadir todos los rangos, que esta vez pueden ser distintos y deberán incluir desde el primer nombre hasta la última nota, el resultado será similar al siguiente:


Ya sólo queda un detalle, como para resumir la información nos guiaremos de los nombres y éstos están colocados en la columna izquierda de cada tabla, deberemos marcar en el cuadro Columna izquierda. Ahora tras aceptar podremos comprobar como el resultado, es el correcto también ahora.

Si deseas el archivo con esta última versión, sólo tienes que hacer click aquí.

2 comentarios:

  1. Mágnifico post acerca de los Resúmenes y Consolidación de datos.
    Espero seguir aprendiendo funciones no tan comunes en las hojas de Excel, pero que tanto trabajo nos quitan a lo hora de hacer cálculos complejos. Gracias por el aporte. Un saludo.

    ResponderEliminar
  2. Bueno bueno, esto constituye un curso mas que un blog, se ve que este tepa lo vives apasionadamente. Tendré que realizar una lectura más reposada para entenderlo todo. Bravo!

    ResponderEliminar