Consolidar datos en Excel
Consolidar datos consiste en combinar los valores de varios rangos de datos en uno solo. Por ejemplo, si tienes una hoja de cálculo de cifras de gastos para cada una de sus oficinas regionales, puedes utilizar una consolidación para reunir estas cifras en una hoja de cálculo de gastos para toda la organización; o si tienes las ventas de cada uno de los vendedores puedes agruparlas en una sola hoja para calcular el total de ventas de toda la compañía.
Para utilizar la herramienta de consolidación de Excel, los rangos a consolidar deben tener una estructura muy similar, o sea, los mismos rótulos de filas y columnas o estar en el mismo orden.
Por ejemplo: tenemos las ventas del primer semestre para cada uno de los vendedores de la compañía y deseamos calcular las ventas totales de la compañía.
es un proceso por el cual se analiza información obtenida de sistemas heredados o lícitamente intercambiada con otras entidades, para obtener una organización de datos legible e interpretable a los efectos de su uso o evaluación desde un punto de vista determinado.
Con la función Consolidación del Menú Datos puedes:
- Reunir información de hasta 255 hojas de cálculo.
- Vincular los datos consolidados a los datos fuente, de tal forma que una vez modificados estos últimos, se alteren los resultados de la consolidación.
- Consolidar por posición o por categoría.
- Consolidar hojas de cálculo utilizando funciones como Promedio, Máx, Mín, Producto, Cuenta, Desvest, etc.
- Consolidar hojas de cálculo en libros abiertos o almacenados en disco.
¿COMO CONSOLIDAR LOS DATOS?
Imagina que después de recopilar la información sobre las ventas de nuestros productos en diferentes países tenemos un libro de Excel donde cada hoja tiene la información de un mes en específico.
Necesito consolidar la información en una sola hoja para poder crear mi reporte, pero ¿cómo puedo hacerlo de una manera sencilla? Para consolidar datos en Excel tenemos el comando Consolidar que nos ayuda a obtener el resultado deseado de una manera fácil. Supongamos que la información de las 3 hojas de Excel es la siguiente.
Comando Consolidar en Excel
Para consolidar los datos crearé una nueva hoja y haré clic en el botón Consolidar que se encuentra en la ficha Datos dentro del grupo Herramientas de datos.
Se mostrará el cuadro de diálogo Consolidar en donde se deberá especificar cada uno de los rangos de las diferentes hojas. Puedes hacer clic en el botón de selección de referencia para seleccionar los rangos fácilmente.
Una vez especificada la referencia se debe pulsar el botón Agregar. De la misma manera se deben agregar cada una de las hojas hasta tener todas las referencias enlistadas. Finalmente debo marcar los cuadros de selección de rótulos en la Fila superior y en Columna Izquierda de manera que los datos sean presentados adecuadamente.
Al pulsar el botón Aceptar, Excel realizará la consolidación de los datos y los colocará en la nueva hoja de Excel.
De esta manera la información que antes estaba en diferentes hojas de Excel ha sido consolidada correctamente en una sola hoja.
TIPOS DE CONSOLIDACION DE DATOS
- 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; por ejemplo, si cuenta con datos de una serie de hojas de cálculo creadas desde la misma plantilla, puede consolidar los datos por posición.
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.
- Configure los datos que va a consolidar.
- 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 por categorías
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. 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.
CONSOLIDACIÓN DE TABLAS DINÁMICAS
Las tablas dinámicas son una buena opción para analizar e informar sobre los datos. Y si los datos son relacionales (es decir, están almacenados en tablas independientes que puede juntar en valores comunes), puede crear una tabla dinámica como esta en cuestión de minutos:
¿Qué tiene de particular esta tabla dinámica? Observe que la lista de campos de la derecha muestra una colección de tablas en lugar de solo una. Estas tablas contienen campos, que el usuario puede combinar en una sola tabla dinámica para segmentar los datos de varias formas. No es necesario preparar los datos ni aplicar formato de forma manual. En cuanto importe los datos podrá crear, de forma inmediata, una tabla dinámica basada en las tablas relacionadas.
Para obtener varias tablas en la lista de campos de la tabla dinámica
- Importe varias tablas desde otros orígenes de datos, incluidos archivos de texto, fuentes de distribución de datos, datos de hojas de cálculo de Excel, etc. Puede agregar estas tablas a Modelo de datos en Excel, crear relaciones entre ellas y luego usar el modelo de datos para crear la tabla dinámica.
A continuación se muestra la manera de importar varias tablas desde una base de datos de SQL Server.
¿COMO CONSOLIDAR TABLAS DINÁMICAS?
- Asegúrese de conocer los nombres del servidor y de la base datos, así como las credenciales que debe usar para conectarse a SQL Server. El administrador de la base de datos puede facilitarle la información que necesite.
- Haga clic en Datos > Obtener datos externos > De otras fuentes > Desde SQL Server.
- En el cuadro Nombre del servidor, escriba el nombre del equipo de red del equipo que ejecuta SQL Server.
- En el cuadro Credenciales de conexión, haga clic en Usar autenticación de Windows para conectarse como usted mismo. De no ser así, escriba el nombre de usuario y la contraseña que le haya proporcionado el administrador de la base de datos.
- Presione Entrar y, en el cuadro Seleccionar base de datos y tabla, elija la base de datos que desee y haga clic en Habilitar selección de varias tablas.
- Si sabe exactamente con qué tablas desea trabajar, elíjalas de forma manual. De lo contrario, seleccione una o dos tablas y luego haga clic en Seleccionar tablas relacionadas para seleccionar de forma automática tablas relacionadas con las que ha seleccionado.
- Si el cuadro Importar relaciones entre tablas seleccionadas está activado, manténgalo así para que Excel pueda crear de nuevo en el libro las relaciones de tablas equivalentes.
- Haga clic en Finalizar.
- En el cuadro de diálogo Importar datos, elija Informe de tabla dinámica.
- Para iniciar la importación y rellenar la lista de campos, haga clic en Aceptar.
Observe que la lista de campos contiene varias tablas. Se trata de todas las tablas que seleccionó durante la importación. Para ver los campos que contienen, puede expandirlas y contraerlas de forma individual. Siempre que las tablas estén relacionadas, puede crear su tabla dinámica arrastrando los campos desde cualquiera de estas tablas a las áreas VALORES, FILAS o COLUMNAS.
- Arrastre los campos numéricos al área VALORES. Por ejemplo, si usa una base de datos de ejemplo de Adventure Works, arrastre ImporteVentas desde la tabla VentasInternetEfectuadas.
- Para analizar las ventas por fecha o zona, arrastre los campos de fecha o zona a las áreas FILAS o COLUMNAS.
- En ocasiones puede ocurrir que, para usar dos tablas en una tabla dinámica, deba crear una relación entre ellas. Si un mensaje le indica que debe crear una relación, en primer lugar haga clic en Crear.
Si está trabajando con otros tipos de bases de datos:- Para usar otras bases de datos relacionales (por ejemplo, Oracle), debe instalar el software cliente adicional. Consulte al administrador de la base de datos para averiguar si esta instalación resulta necesaria.
- También puede importar varias tablas desde Access. Para obtener más detalles, vea Tutorial: Análisis de datos de tabla dinámica con un modelo de datos en Excel.
- Usar el modelo de datos para crear una nueva tabla dinámica
Quizás ha creado relaciones entre tablas en el modelo de datos y ahora está listo para usar estos datos en su análisis. A continuación se explica cómo crear una tabla dinámica o un gráfico dinámico nuevos mediante el modelo de datos en el libro.
- Haga clic en cualquier celda de la hoja de cálculo.
- Haga clic en Insertar > Tabla dinámica.
- En el cuadro de diálogo Crear tabla dinámica, en Seleccione los datos que desea analizar, haga clic en Usar un origen de datos externo.
- Haga clic en Elegir conexión.
- En la pestaña Tablas, en Este Modelo de datos de libro, seleccione Tablas en el Modelo de datos de libro.
- Haga clic en Abrir y luego en Aceptar para mostrar una lista de campos que contenga todas las tablas del modelo de datos.
