¿Alguna vez has tenido que resolver alguno de los siguientes problemas?
-
Recibes varios libros de Excel con la misma información pero de diferentes áreas, regiones, productos, etc, dentro de tu compañía.
-
Debes integrar la información para que posteriormente la puedas analizar, es decir, se te ha solicitado integrarla en un único libro maestro.
-
Para resolver el problema, copias las tablas en un libro nuevo y posteriormente a partir de fórmulas lo unificas todo en una sola tabla, siendo este un procedimiento un poco tedioso y que tarda un tiempo considerable.
Afortunadamente existe otra manera de realizar este procedimiento, seguramente (y si no lo conocías) te va ayudar mucho, así que descubramos de qué se trata con un ejemplo práctico.
Análisis de Ventas
Supongamos que nos encontramos en una fábrica de Bicicletas, la cual tiene 3 líneas diferentes de productos denominados así: Playera, Doméstica y Carrera, cada línea de bicicletas maneja la misma variedad de colores.
Para el mes de Julio y a fin de mejorar el desempeño de la compañía en el segundo semestre, queremos analizar el total de unidades vendidas mes a mes en la primera mitad del año, dependiendo del color de los productos, el problema radica en que la información solicitada llega en diferentes archivos que debemos unificar en uno solo.
Solución
Lo primero es abrir el archivo donde se encuentra los datos a consolidar, a continuación se muestran las tablas para cada línea de producto:
- Nuestro objetivo: unir o consolidar los datos de las tres tablas presentadas arriba en una única tabla
Crear un libro de Excel en blanco en el cual haremos la consolidación de datos, para esto: ARCHIVO -> NUEVO -> LIBRO EN BLANCO
Consolidación de Datos
En el nuevo archivo ubicando la celda activa donde queremos que aparezca la tabla de consolidados, nos dirigimos a la pestaña Datos y en el grupo Herramientas de Datos presionamos el botón Consolidar.
Automáticamente aparece el siguiente cuadro de dialogo:
Y ahora agregamos uno a uno los rangos donde se encuentran las tablas, para ello damos clic izquierdo en la caja de texto de Referencia y nos dirigimos al primer Libro, allí seleccionamos toda la tabla, incluyendo las etiquetas de columna.
En la caja de texto Referencia, se muestra el nombre del libro, la hoja dentro del libro y el rango donde se encuentra la tabla, para finalizar presionamos el botón agregar.
Y repetimos el procedimiento para los otros dos libros sin cerrar el cuadro de dialogo.
Podemos apreciar que las tres tablas han sido agregadas exitosamente a la caja de texto Todas las Referencias, ahora vamos a elegir con que función vamos a consolidar los datos.
Funciones
Existen 11 funciones que podemos implementar en la consolidación de datos:
-
SUMA: Suma los productos de las tres líneas por cada color.
-
CUENTA: Calcula el número de entradas para cada mes y por color
-
PROMEDIO: Calcula el promedio entre las tres líneas por cada color.
-
MAX: Arroja el valor máximo para cada color en cada mes.
-
MIN: Arroja el valor mínimo para cada color en cada mes.
-
PRODUCTO: Multiplica los valores entre sí, dependiendo el color y el mes.
-
CONTAR NÚMEROS: Cuenta las celdas con Números.
-
DESVEST: Desviación Estandar de una Muestra.
-
DESVESTP: Desviación Estandar Poblacional.
-
VAR: Varianza de una muestra.
-
VARP: Varianza Poblacional.
La función que debemos elegir, depende completamente de nuestras necesidades, sin embargo, para este caso la más adecuada es la función Suma.
Nota: ¡IMPORTANTE!
Si dejamos las opciones Fila Superior y Columna Izquierda habilitadas, se copiará en la tabla de consolidado estas etiquetas, Además, la opción Crear Vínculos con los Datos de Origen, permite que las tablas queden vinculadas es decir, si hay algún cambio en alguna de las tablas esto se verá reflejado en la tabla de consolidados automáticamente, por lo anterior, es recomendable habilitar las tres opciones.
Por último, presionamos el botón aceptar.
Y posterior a ello se crea la siguiente tabla:
Como podemos ver en las etiquetas de fila y columna aparecen botones para revisar en detalle la consolidación de los datos, para mostrarnos como se calculó la tabla.
Para finalizar el análisis, modificamos el diseño de la tabla.
Si se quiere hacer un análisis más profundo podemos implementar gráficos y filtros todo depende del escenario en el que nos encontremos, además, con esta tabla existe la posibilidad de encontrar conclusiones significativas, como el color más vendido, el color menos vendido, el mes con menores ventas, etc.
Eso es todo por ahora, la herramienta Consolidar es altamente recomendada, imaginemos el tiempo que podemos llegar a ahorrar si en lugar de 3 archivos son 10 o más.
Ahora coméntanos, qué tan oscura y oculta es para ti esta opción en Excel, la conocías, las utilizas con frecuencia o sugieras algún otro método para consolidar datos
Hasta la Próxima publicación.