Para resumir los resultados de datos en diferentes hojas de cálculo e informar de ellos, puede consolidar los datos de cada hoja de cálculo independiente en una hoja de cálculo (u hoja de cálculo maestra). Las hojas de cálculo que se consolidan pueden estar en el mismo libro como una hoja de cálculo maestra o en otros libros. Al consolidar datos en una hoja de cálculo, puede actualizarlos y agregarlos más fácilmente de forma regular o puntual.
Por ejemplo, si tiene una hoja de cálculo de cifras de gastos para cada una de las oficinas regionales, podría usar una consolidación para resumir estas cifras en una hoja de cálculo de gastos corporativa. Esta hoja de cálculo maestra podría contener los totales de ventas y promedios, los niveles de inventario actuales y los productos más vendidos de toda la organización.
Hay dos formas principales de consolidar datos:
- Consolide por posición Use este método cuando los datos de varias áreas de origen se organizan en el mismo orden y usan las mismas etiquetas de fila y columna (por ejemplo, cuando tiene una serie de hojas de de cálculo de gastos creadas a partir de la misma plantilla).
- Consolide por categorías Use este método cuando los datos de varias áreas de origen se organizan de forma diferente, pero se usan las mismas etiquetas de fila y columna. Por ejemplo, puede usar este método cuando tiene una serie de hojas de inventario de cada mes en las que se usa el mismo diseño, pero cada hoja de cálculo contiene distintos elementos o un número diferente de elementos.
¿Qué desea hacer?
- En cada hoja de cálculo que contiene los datos que desea consolidar, prepare los datos del siguiente modo:
- Asegúrese de que cada rango de datos está en formato de lista: cada columna tiene una etiqueta en la primera fila, contiene hechos similares y no tiene filas o columnas en blanco.
- Coloque cada rango en una hoja de cálculo diferente, pero no ponga ningún rango en la hoja de cálculo donde vaya a colocar la consolidación.
- Asegúrese de que cada rango tiene el mismo diseño.
- En la hoja de cálculo maestra, haga clic en la celda superior izquierda del área donde desea que aparezcan los datos consolidados.
- En el grupo Herramientas de datos de la ficha Datos, haga clic en Consolidar.
- En el cuadro 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 desea que use Microsoft Excel para consolidar los datos.
- Si la hoja de cálculo que contiene los datos que desea consolidar está en otro libro, haga clic en Examinar para buscar el libro y, a continuación, haga clic en Aceptar para cerrar el cuadro de diálogo Examinar.
- Si la hoja de cálculo que contiene los datos que desea consolidar está en el libro actual, haga lo siguiente:
- En el cuadro Referencia, haga clic en el botón Contraer diálogo para seleccionar los datos de la hoja de cálculo.
- Haga clic en la hoja de cálculo que contiene los datos que desea consolidar, seleccione los datos y, a continuación, haga clic en el botón Expandir diálogo.
- En el cuadro de diálogo Consolidar, haga clic en Agregar y repita los pasos 6 y 7 para agregar todos los rangos que desea.
- Indique cómo desea actualizar la consolidación mediante una de las siguientes acciones:
- Para configurar la consolidación de manera que se actualice automáticamente cuando cambien los datos de origen en otro libro, active la casilla de verificación Crear vínculos con los datos de origen.
- Para configurar la consolidación de modo que pueda actualizarla manualmente cambiando las celdas y los rangos incluidos, desactive la casilla Crear vínculos con los datos de origen.
Consolidar datos por categoría
- En cada hoja de cálculo que contiene los datos que desea consolidar, prepare los datos del siguiente modo:
- Asegúrese de que cada rango de datos está en formato de lista: cada columna tiene una etiqueta en la primera fila, contiene hechos similares y no tiene filas o columnas en blanco.
- Coloque cada rango en una hoja de cálculo diferente, pero no ponga ningún rango en la hoja de cálculo donde vaya a colocar la consolidación.
- Asegúrese de que cada rango tiene el mismo diseño.
- En la hoja de cálculo maestra, haga clic en la celda superior izquierda del área donde desea que aparezcan los datos consolidados.
- En el grupo Herramientas de datos de la ficha Datos, haga clic en Consolidar.
- En el cuadro 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 desea que use Excel para consolidar los datos.
- Si la hoja de cálculo se encuentra en otro libro, haga clic en Examinar para buscar el archivo y, a continuación, haga clic en Aceptar para cerrar el cuadro de diálogo Examinar.
- Si la hoja de cálculo está en el libro actual, haga lo siguiente:
- Haga clic en el botón de referencia a la derecha del cuadro de Referencia para abrir el cuadro de diálogo Consolidar – Referencia.
- Abra la hoja de cálculo que contiene los datos que desea consolidar, seleccione los datos y haga clic en el botón a la derecha del cuadro.
- En el cuadro de diálogo Consolidar, haga clic en Agregar y repita los pasos 6 y 7 para agregar todos los rangos que desea.
- Decida cómo desea actualizar la consolidación. Siga uno de los procedimientos siguientes:
- Para configurar la consolidación de manera que se actualice automáticamente cuando cambien los datos de origen, active la casilla de verificación Crear vínculos con los datos de origen.
- Para configurar la consolidación de modo que puede actualizarla manualmente cambiando las celdas y los rangos incluidos, desactive la casilla de verificación Crear vínculos con los datos de origen.
- Active las casillas de verificación de Usar rótulos en que indican donde se encuentran los rótulos en los rangos de origen, que puede ser: la Fila superior, la Columna izquierda o ambas.
- Los rótulos que no coincidan con los de las otras áreas de origen producirán filas o columnas independientes en la consolidación.
- Asegúrese de que las categorías que no desee consolidar tienen rótulos únicos que aparecen solamente en un rango de origen.
Otras maneras de consolidar datos
Además de consolidar datos por posición o por categoría, también puede hacerlo mediante fórmulas o informes de tabla dinámica.
Usar una fórmula para consolidar datos
- En la hoja de cálculo maestra, copie o escriba los rótulos de columna o fila que desee para los datos de consolidación.
- Haga clic en la celda en que desea incluir los datos de consolidación.
- Escriba una fórmula que incluya una referencia de celda a las celdas de origen de cada hoja de cálculo o una referencia 3D que contenga los datos que desea consolidar. En cuanto a las referencias de celda, siga uno de los procedimientos siguientes:
- Escriba una fórmula con referencias de celda a las otras hojas de cálculo, una por cada hoja de cálculo independiente. Por ejemplo, para consolidar datos de hojas de cálculo denominadas Ventas (en la celda B4), HR (en la celda F5) y Marketing (en la celda B9), en la celda A2 de la hoja de cálculo maestra, tendría que escribir lo siguiente:
Sugerencia Para especificar una referencia de celda como Ventas3!B4 en una fórmula sin escribir, escriba la fórmula hasta el punto en el que necesite la referencia, haga clic en la etiqueta de la hoja de cálculo y, a continuación, haga clic en la celda.
Si los datos que se van a consolidar están en las mismas celdas de otras hojas de cálculo
- Escriba una fórmula con una referencia 3D que use una referencia a un rango de nombres de hojas de cálculo. Por ejemplo, para consolidar los datos de las celdas A2 desde Ventas hasta Marketing (ambos incluidos), tendría que escribir lo siguiente en la celda A2 de la hoja de cálculo maestra:
Nota Si el libro está configurado para calcular fórmulas automáticamente, una consolidación por fórmula siempre se actualiza automáticamente cuando cambian los datos de las hojas de cálculo independientes.
Usar un informe de tabla dinámica para consolidar datos
Puede crear un informe de tabla dinámica (informe de tabla dinámica: informe de Excel interactivo de tablas cruzadas que resume y analiza datos, como registros de una base de datos, de varios orígenes incluidos los externos a Excel.) para varios rangos de consolidación. Este método es similar a la consolidación por categoría, pero ofrece más flexibilidad para reorganizar las categorías. Para obtener más información, vea el tema sobre inicio rápido: creación de informes de tabla dinámica.
No comments:
Post a Comment