CONSOLIDACIÓN DE DATOS

Para consolidar datos, se combinan los valores de varios rangos de datos. Por ejemplo, si tiene una hoja de cálculo de cifras de gastos para cada una de sus oficinas regionales, puede utilizar una consolidación para reunir estas cifras en una hoja de cálculo de gastos para toda la organización.

Excel permite consolidar datos de varios modos. El método más flexible es crear fórmulas que hagan referencia a las celdas de cada rango de datos que se vaya a combinar. Las fórmulas que hacen referencia a las celdas de varias hojas de cálculo se denominan fórmulas 3D.

Consolidar por posición.

Consolide los datos por posición cuando los datos de todas las áreas de origen se organicen en un orden y una ubicación idénticos. Puede configurar la consolidación de modo que se actualice automáticamente cuando cambien los datos de origen, pero no podrá modificar qué celdas y rangos se incluyen en la consolidación

.

Consolidar por categorias.

Consolide los datos por categorías si desea resumir un conjunto de hojas de cálculo que tienen los mismos rótulos de filas y columnas pero organizan los datos de forma diferente. Este método combina los datos que tienen rótulos coincidentes en cada hoja de cálculo. Puede configurar la consolidación de modo que se actualice automáticamente cuando cambien los datos de origen, pero no podrá modificar qué celdas y rangos se incluyen en la consolidación.

Para utilizar esta herramienta, nos dirigimos al menu Datos y despues a Consolidar, se abrira el cuadro de dialogo Consolidar.

En Funcion elegimos aquella que se adapte a nuestras necesidades, en Referencia seleccionamos el rango de celdas de la hoja actual y posteriormente damos click en Agregar, si los otros datos se encuentran en la misma posicion con eso sera suficiente, en caso contrario tendremos que repetir esta operacion varias veces. Tambien marcamos donde se usaran rotulos e indicamos si se crearan vinculos con los datos de origen, esta opcion permitira actualizar los datos cuando se cambie la informacion de origen.

Ejemplo:

Se consolidaran los datos de las siguientes empresas para conocer la suma de sus Ingresos, Egresos y Utilidad registrados durante un periodo de cuatro años.

Empresa 1

Para consolidarlos, en una hoja nueva abrimos el cuadro de dialogo Consolidar, elegimos la funcion Suma, seleccionamos el rango de datos completo de las empresas, y marcamos las tres casillas de la parte inferior. El resultado es el siguiente:



Las dos listas con los datos de cada empresa se agruparon en una, en la que se muestra la suma correspondiente por año de Ingresos, Egresos, Utilidad. Tambien se visualiza el nombre del libro al que pertenecen los datos y del lado izquierdo unos botones con los que podemos disminuir el tamaño de nuestra lista para solo ver los resultados anuales.

Reflexión.


Consolidar es un conjuntos de datos con iguales encabezados, que se encuentran en otros libros o en el mismo en el que estamos trabajando. Ademas podemos utilizar funciones que nos muestren subtotales por cada subconjunto de datos. Esta herramienta proporciona al usuario mayor comodidad y facilidad en la comprensión para resumir datos que tienes en varios sitios, pero con una estructura parecida. Por ejemplo, si tienes unas tablas con anotaciones por personas y meses (por ejemplo horas trabajadas) una tabla por cada trabajo de la información mostrada, también ahorra tiempo y evita errores en el manejo y entendimiento de los datos. A partir de una consolidación es posible realizar otras operaciones, como lo es la creación de gráficos.

SUBTOTALES

Microsoft Excel puede calcular automáticamente valores de subtotales y de totales generales en una lista. Cuando se insertan subtotales automáticos, Excel esquematiza la lista para que se puedan mostrar y ocultar las filas de detalle de cada subtotal.

Para insertar subtotales, primero se ordena la lista para agrupar las filas cuyos subtotales se desea calcular. Después pueden calcularse los subtotales de cualquier columna que contenga números.

Si los datos no están organizados en una lista, o si sólo necesita un total, puede utilizar Auto suma en lugar de subtotales automáticos.

Cómo se calculan los subtotales Excel calcula los subtotales con una función de resumen, como Suma o Promedio. Puede mostrar subtotales en una lista con más de un tipo de cálculo a la vez.

Totales generales

Los valores del total general se obtienen a partir de los datos de detalle y no de los valores de las filas de subtotales. Por ejemplo, si se utiliza la función de resumen Promedio, la fila Total general mostrará el promedio de todas las filas de detalle de la lista y no el promedio de los valores de las filas de subtotales.

Actualización automática: Excel actualizará automáticamente los subtotales y el total general cuando se modifiquen los datos de detalle.

Subtotales anidadosPuede insertar subtotales de grupos más pequeños en los grupos de subtotales existentes. En el ejemplo a continuación, los subtotales de cada deporte están en una lista que ya tiene subtotales para cada región.

Subtotales exterioresAntes de insertar los subtotales anidados, compruebe que ha ordenado la lista por todas las columnas para las que desea los valores subtotales de manera que las filas cuyos subtotales desea estén agrupadas.

Insertar subtotales individuales

1. Asegúrese de que los datos cuyos subtotales desea calcular están en el siguiente formato: cada columna tiene un rótulo en la primera fila, contiene hechos similares y no hay filas ni columnas en blanco en el rango

1. Haga clic en una celda de la columna cuyos subtotales se desea calcular. En el ejemplo anterior, haría clic en una celda de la columna Deporte, columna B.
2. Haga clic en Orden ascendente o en Orden descendente.
3. En el menú Datos, haga clic en Subtotales.

1. En el cuadro Para cada cambio en, haga clic en la columna cuyos subtotales desee calcular. En el ejemplo anterior, haría clic en la columna Deporte.
2. En el cuadro Usar función, haga clic en la función de resumen (función de resumen: tipo de cálculo que combina datos de origen en un informe de tabla dinámica o una tabla de consolidación, o cuando se insertan subtotales automáticos en una lista o base de datos. Algunos ejemplos de funciones de resumen son: Sumar, Contar y Promedio.) que desee utilizar para calcular los subtotales.
3. En el cuadro Agregar subtotal a, active la casilla de verificación de cada columna que contenga valores cuyos subtotales desee calcular. En el ejemplo anterior, seleccionaría la columna Ventas.
4. Si desea un salto de página automático después de cada subtotal, active la casilla de verificación Salto de página entre grupos.
5. Si desea que los subtotales aparezcan encima de las filas cuyos subtotales se han calculado en lugar de que aparezcan debajo, desactive la casilla de verificación Resumen debajo de los datos.
6. Haga clic en Aceptar.
7.   Puede utilizar de nuevo el comando Subtotales para agregar más subtotales con diferentes funciones de resumen. Para evitar que se sobrescriban los subtotales existentes, desactive la casilla de verificación Reemplazar subtotales actuales.

Sugerencia

Para mostrar un resumen solamente de los subtotales y de los totales generales, haga clic en los símbolos de esquema que aparecen junto a los números de fila. Utilice los símbolos y par a mostrar u ocultar las filas de detalle de los subtotales individuales.

Insertar subtotales anidados

1. Subtotales exteriores

1. Asegúrese de que los datos cuyos subtotales desea calcular están en el siguiente formato: cada columna tiene un rótulo en la primera fila, contiene hechos similares y no hay filas ni columnas en blanco en el rango.
2. Ordene el rango por varias columnas, ordenando primero por la columna de subtotales exteriores, después por la siguiente columna interior de los subtotales anidados, etc. En el ejemplo anterior, ordenaría el rango primero por la columna Región y después por la columna Deporte.¿Cómo?Para obtener resultados óptimos, el rango que se ordene deberá tener rótulos de columna.
   1. Haga clic en el rango que desee ordenar.
   2. En el menú Datos, haga clic en Ordenar.
   3. En los cuadros Ordenar por y Luego por, haga clic en las columnas que desee ordenar.
   4. Seleccione otras opciones de ordenación que desee y, a continuación, haga clic en Aceptar.
3. Inserte los subtotales exteriores.¿Cómo?
   1. En el menú Datos, haga clic en Subtotales.
   2. En el cuadro Para cada cambio en, haga clic en la columna de los subtotales exteriores. En el ejemplo anterior, haría clic en Región.
   3. En el cuadro Usar función, haga clic en la función de resumen (función de resumen: tipo de cálculo que combina datos de origen en un informe de tabla dinámica o una tabla de consolidación, o cuando se insertan subtotales automáticos en una lista o base de datos. Algunos ejemplos de funciones de resumen son: Sumar, Contar y Promedio.) que desee utilizar para calcular los subtotales.
   4. En el cuadro Agregar subtotal a, active la casilla de verificación correspondiente a cada columna que contenga valores cuyos subtotales desee calcular. En el ejemplo anterior, esa columna sería Ventas.
   5. Si desea un salto de página automático después de cada subtotal, active la casilla de verificación Salto de página entre grupos.
   6. Si desea que los subtotales aparezcan encima de las filas cuyos subtotales se han calculado en lugar de que aparezcan debajo, desactive la casilla de verificación Resumen debajo de los datos.
4. Inserte los subtotales anidados.¿Cómo?
1. En el menú Datos, haga clic en Subtotales.
2. En el cuadro Para cada cambio en, haga clic en la columna de subtotales anidados. En el ejemplo anterior, esa columna sería Deporte.
3. Seleccione la función de resumen (función de resumen: tipo de cálculo que combina datos de origen en un informe de tabla dinámica o una tabla de consolidación, o cuando se insertan subtotales automáticos en una lista o base de datos. Algunos ejemplos de funciones de resumen son: Sumar, Contar y Promedio.) y otras opciones.
4. Desactive la casilla de verificación Reemplazar subtotales actuales.

1. Repita el paso anterior para más subtotales anidados, empezando desde los más exteriores.

REFLEXIÓN:

Los subtotales muestran resultados preliminares de una lista de datos que contenga caracteristicas generales o similitudes en su contenido.   EXCEL crea la fórmula, inserta las filas correspondientes al subtotal y al total y efectúa un esquema de los datos, automáticamente. Así, los datos resultantes son fáciles de formatear, colocar en un gráfico e imprimir. En resumen, añade líneas de subtotal a columnas de la lista.Cuando configuramos la creación de un subtotal nos podemos dar cuenta de que es posible crear y deshacer varios subtotales diferentes