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.
Utilizar fórmulas para combinar datos
En el siguiente ejemplo, la fórmula de la celda A2 agrega tres números de tres posiciones diferentes en tres hojas de cálculo distintas.
Agregar datos a una consolidación con referencias 3D
Cuando todas las hojas de cálculo de origen tengan el mismo diseño, podrá utilizar un rango de nombres de hojas en fórmulas 3D. Para agregar otra hoja de cálculo a la consolidación, sólo tendrá que mover la hoja al rango al que haga referencia la fórmula.
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. También puede actualizar la consolidación manualmente, lo que le permitirá cambiar las celdas y los rangos incluidos en ella.
Consolidar por categoríasConsolide 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. Como alternativa, puede actualizar la consolidación manualmente, lo que le permitirá cambiar las celdas y los rangos incluidos en ella.
Cómo 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.) a partir de varios rangos de consolidación. Este método es similar al de consolidación por categorías, pero ofrece una mayor flexibilidad para reorganizar las categorías.
Examine los datos y decida si desea consolidarlos utilizando referencias 3D (referencia 3D: referencia a un rango que ocupa dos o más hojas de cálculo en un libro.) en fórmulas, por posición o por categoría.
Posición Si va a combinar datos que están en la misma celda en varios rangos, puede consolidar por posición.
Categoría: Si tiene varios rangos con diseños diferentes y va a combinar datos de filas o columnas que tengan rótulos coincidentes, puede consolidar por categoría.
Consolidar los datos con referencias 3D o fórmulas
- En la hoja de cálculo de consolidación, copie o introduzca los rótulos que desee para los datos de consolidación.
- Haga clic en la celda en que desea incluir los datos de consolidación.
- Introduzca una fórmula que incluya referencias a las celdas de origen en cada hoja de cálculo que contenga los datos que se desea consolidar.Por ejemplo, para combinar los datos de la celda B3 de las hojas de cálculo Hoja 2 a Hoja 7 ambas inclusive, podría escribir=SUMA(Hoja2:Hoja7!B3). Si los datos que va a consolidar están en celdas diferentes de hojas de cálculo distintas, escriba una fórmula como esta: =SUMA(Hoja3!B4, Hoja4!A7, Hoja5!C5). Para introducir una referencia como Hoja3!B4 en una fórmula sin escribir, escriba la fórmula hasta el punto en el que necesita la referencia, haga clic en la etiqueta de la hoja de cálculo y, a continuación, haga clic en la celda.
Consolidar por posición o categoría
Configure los datos que va a consolidar.
¿Cómo?
- Asegúrese de que cada rango de datos está en formato de lista (lista: serie de filas que contienen datos relacionados o serie de filas que designa para que funcionen como hojas de datos mediante el comando Crear lista.): cada columna tiene un rótulo 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. No ponga ningún rango en la hoja de cálculo donde vaya a colocar la consolidación.
- Si realiza la consolidación por posición, asegúrese de que cada rango tiene el mismo diseño.Si realiza la consolidación por categoría, asegúrese de que los rótulos de las columnas o filas que desea combinar tienen idéntica ortografía y coincidencia de mayúsculas y minúsculas.
- Asigne un nombre a cada rango: seleccione todo el rango, elija Nombre en el menú Insertar, haga clic en Definir y escriba un nombre para el rango.
- Haga clic en la celda superior izquierda del área donde desee que aparezcan los datos consolidados.
- En el menú 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 utilice Microsoft Excel para consolidar los datos.
- Haga clic en el cuadro Referencia, elija la etiqueta de hoja del primer rango que va a consolidar, escriba el nombre que asignó al rango y, a continuación, haga clic en Agregar. Repita este paso para cada rango.
- Si desea actualizar la tabla de consolidación automáticamente cada vez que cambien los datos en cualquiera de los rangos de origen y no está seguro de si más tarde deseará incluir rangos diferentes o adicionales en la consolidación, active la casilla de verificación Crear vínculos con los datos de origen.
- Si realiza la consolidación por posición, deje en blanco las casillas bajo Usar rótulos en. Microsoft Excel no copia los rótulos de fila o columna de los rangos de origen a la consolidación. Si desea rótulos para los datos consolidados, cópielos de uno de los rangos de origen o introdúzcalos manualmente.
Si realiza la consolidación por categoría, active las casillas de verificación bajo Usar rótulos en que indican dónde están localizados los rótulos en los rangos de origen: en 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.
Consolidar varias hojas de cálculo en un informe de tabla dinámica
Para resumir resultados de rangos de hojas de cálculo independientes y elaborar informes con ellos, puede consolidar los datos de cada rango de las hojas de cálculo independientes en un informe de tabla dinámica en una hoja de cálculo principal. Los rangos de las hojas de cálculo independientes pueden encontrarse en el mismo libro que la hoja de cálculo principal o en uno distinto. Cuando consolida datos, está ensamblándolos para poder actualizar y agregar datos con más facilidad regularmente o según sus necesidades.
El informe de tabla dinámica consolidado resultante puede tener los siguientes campos en la Lista de campos de tabla dinámica, que se puede agregar al informe de tabla dinámica: Fila, Columna y Valor. Además, puede tener hasta cuatro campos de filtro de página denominados Página1, Página2, Página3 y Página4.
NOTA El Asistente para tablas y gráficos dinámicos utiliza el término campo de página en la interfaz de usuario. En Microsoft Office Excel 2007, un campo de página ahora se denomina campo de filtro de informe en otras parte de la interfaz de usuario de tabla dinámica.
CONFIGURAR EL ORIGEN DE DATOS
Cada rango de datos debe organizarse en formato de tabla de referencias cruzadas, con los nombres coincidentes de fila y columna de los elementos que desea resumir. No incluya ninguna fila o columna de total del origen de datos al especificar los datos del informe. En el siguiente ejemplo se muestran cuatro rangos en formato de tabla de referencias cruzadas.
CAMPOS DE PÁGINA EN CONSOLIDACIONES DE DATOS
La consolidación de datos puede emplear campos de página que contengan elementos que representen uno o más de los rangos de origen. Por ejemplo, si está consolidando datos presupuestarios de los departamentos de marketing, ventas y producción, un campo de página puede incluir un elemento que muestre los datos de cada departamento más un elemento que muestre los datos combinados. En el siguiente ejemplo se muestra un informe de tabla dinámica consolidado con un campo de página y varios elementos seleccionados.
UTILIZAR RANGOS CON NOMBRE
Si es probable que el rango de datos cambie la siguiente vez que consolide los datos (es decir, que el número de filas puede cambiar), considere la posibilidad de definir un nombre para cada rango de origen de las hojas de cálculo independientes. A continuación, utilice esos nombres a la hora de consolidar los rangos en la hoja de cálculo principal. Si un rango de datos se amplía, puede actualizarlo en la hoja de cálculo independiente de modo que incluya los nuevos datos antes de actualizar el informe de tabla dinámica.
OTRAS MANERAS DE CONSOLIDAR DATOS
Excel también ofrece otras maneras de consolidar datos con varios formatos y diseños. Por ejemplo, puede crear fórmulas con referencias 3D o utilizar el comando Consolidar (en la ficha Datos, en el grupo Herramientas de datos).
Consolidar varios rangos
Puede utilizar el Asistente para tablas y gráficos dinámicos para consolidar varios rangos. En este asistente puede optar por no utilizar campos de página, por emplear un único campo de página o varios.
CONSOLIDAR DATOS SIN UTILIZAR CAMPOS DE PÁGINA
Para combinar los datos de todos los rangos y crear una consolidación sin campos de página, siga este procedimiento:
- Haga clic en una celda en blanco en el libro que no forma parte de un informe de tabla dinámica.
- Para iniciar el Asistente para tablas y gráficos dinámicos, presione ALT+D+P.
- En la página Paso 1 del asistente, haga clic en Rangos de consolidación múltiples y, a continuación, en Siguiente.
- En la página Paso 2a del asistente, haga clic en Campos de página personalizados y, a continuación, en Siguiente.
- En la página Paso 2b del asistente, haga lo siguiente:
- Para cada rango de celdas, haga clic en Contraer diálogo
a fin de ocultar el cuadro de diálogo de forma temporal, seleccione el rango de celdas en la hoja de cálculo, presione Expandir diálogo
y, a continuación, haga clic en Agregar.
- En ¿Cuántos campos de página desea?, haga clic en 0 y, a continuación, en Siguiente.
dinámica y, a continuación, haga clic en Finalizar.
CONSOLIDAR DATOS MEDIANTE UN ÚNICO CAMPO DE PÁGINA
Para incluir un único campo de página que tenga un elemento para cada rango de origen más un elemento que consolide todos los rangos, siga este procedimiento:
- Haga clic en una celda en blanco en el libro que no forma parte de un informe de tabla dinámica.
- Para iniciar el Asistente para tablas y gráficos dinámicos, presione ALT+D+P.
- En la página Paso 1 del asistente, haga clic en Rangos de consolidación múltiples y, a continuación, en Siguiente.
- En la página Paso 2a del asistente, haga clic en Crear un solo campo de página y, a continuación, haga clic en Siguiente.
- En la página Paso 2b del asistente, haga lo siguiente:
- Para cada rango de celdas, haga clic en Contraer diálogo a fin de ocultar el cuadro de diálogo de forma temporal, seleccione el rango de celdas en la hoja de cálculo y, a continuación, presioneExpandir diálogo.
- Haga clic en Siguiente.
- En la página Paso 3 del asistente, seleccione una ubicación para el informe de tabla dinámica y, a continuación, haga clic en Finalizar.
CONSOLIDAR DATOS MEDIANTE VARIOS CAMPOS DE PÁGINA
Puede crear varios campos de página y asignar sus propios nombres de elemento a cada rango de origen. Eso le permitirá crear consolidaciones parciales o totales; por ejemplo, un campo de página que consolide marketing y ventas independientemente de producción, y otro campo de página que consolide los tres departamentos. Para crear una consolidación que use varios campos de página, siga este procedimiento:
- Haga clic en una celda en blanco en el libro que no forma parte de un informe de tabla dinámica.
- Para iniciar el Asistente para tablas y gráficos dinámicos, presione ALT+D+P.
- En la página Paso 1 del asistente, haga clic en Rangos de consolidación múltiples y, a continuación, en Siguiente.
- En la página Paso 2a del asistente, haga clic en Campos de página personalizados y, a continuación, en Siguiente.
- En la página Paso 2b del asistente, haga lo siguiente:
- Para cada rango de celdas, haga clic en Contraer diálogo a fin de ocultar el cuadro de diálogo de forma temporal, seleccione el rango de celdas en la hoja de cálculo, presione Expandir diálogoy, a continuación, haga clic en Agregar.
- En ¿Cuántos campos de página desea?, haga clic en el número de campos de página que desea emplear.
- En ¿Qué rótulos de elemento desea que use cada campo de página para identificar el rango de datos seleccionado?, seleccione el rango de celdas de cada campo de página y, a continuación, escriba un nombre para ese rango.
- Haga clic en Siguiente.
- En la página Paso 3 del asistente, seleccione una ubicación para el informe de tabla dinámica y, a continuación, haga clic en Finalizar.
REFLEXIÓN
Excel nos permite consolidar datos de diferentes modos. El método mas flexible de consolidar datos es creando formulas que hagan referencia a las celdas de cada rango de datos que se vaya a combinar. Las formulas que hacen referencia a las celdas de varias hojas de calculo. Para consolidar datos, se combinan de varios rangos de datos. Por ejemplo si nosotros tenemos una hoja de calculo con los gastos de una empresa para cada una de las oficinas, podemos utilizar la consolidación para reunir todas estas cifras en una hoja de calculo de gastos para toda la organización.
No hay comentarios:
Publicar un comentario