Contenido
Tenemos dos tablas (por ejemplo, la versión antigua y la nueva de la lista de precios), que necesitamos comparar y encontrar rápidamente las diferencias:
Inmediatamente queda claro que algo se ha añadido a la nueva lista de precios (dátiles, ajo...), algo ha desaparecido (moras, frambuesas...), los precios de algunos productos han cambiado (higos, melones...). Necesita encontrar y mostrar rápidamente todos estos cambios.
Para cualquier tarea en Excel, casi siempre hay más de una solución (generalmente 4-5). Para nuestro problema, se pueden utilizar muchos enfoques diferentes:
- función VPR (VLOOKUP) — busque los nombres de los productos de la nueva lista de precios en la anterior y muestre el precio anterior junto al nuevo, y luego detecte las diferencias
- combine dos listas en una y luego cree una tabla dinámica basada en ella, donde las diferencias serán claramente visibles
- use el complemento Power Query para Excel
Vamos a tomarlos todos en orden.
Método 1. Comparación de tablas con la función BUSCARV
Si no está completamente familiarizado con esta maravillosa función, primero mire aquí y lea o mire un video tutorial al respecto: ahórrese un par de años de vida.
Por lo general, esta función se usa para extraer datos de una tabla a otra haciendo coincidir algún parámetro común. En este caso, lo usaremos para pasar los precios antiguos al nuevo precio:
Esos productos, contra los cuales resultó el error #N/A, no están en la lista anterior, es decir, fueron agregados. Los cambios de precios también son claramente visibles.
Para Agencias y Operadores este método: simple y claro, “clásico del género”, como dicen. Funciona en cualquier versión de Excel.
Desventajas también está allí. Para buscar productos agregados a la nueva lista de precios, deberá realizar el mismo procedimiento en la dirección opuesta, es decir, extraer nuevos precios al precio anterior con la ayuda de BUSCARV. Si los tamaños de las tablas cambian mañana, habrá que ajustar las fórmulas. Bueno, y en tablas realmente grandes (> 100 mil filas), toda esta felicidad se ralentizará decentemente.
Método 2: Comparar tablas usando un pivote
Copiemos nuestras tablas una debajo de la otra, agregando una columna con el nombre de la lista de precios, para que luego pueda entender de qué lista en qué fila:
Ahora, en base a la tabla creada, crearemos un resumen a través de Insertar – Tabla dinámica (Insertar: tabla dinámica). Vamos a tirar un campo Producto a la zona de lineas, campo Precio al área de la columna y al campo Цena en el rango:
Como puede ver, la tabla dinámica generará automáticamente una lista general de todos los productos de las listas de precios antiguas y nuevas (¡sin repeticiones!) y ordenará los productos alfabéticamente. Puedes ver claramente los productos añadidos (no tienen el precio anterior), los productos eliminados (no tienen el precio nuevo) y los cambios de precio, si los hubiera.
Los totales generales en una tabla de este tipo no tienen sentido y se pueden deshabilitar en la pestaña Constructor – Grandes totales – Deshabilitar para filas y columnas (Diseño — Totales generales).
Si los precios cambian (¡pero no la cantidad de bienes!), basta con simplemente actualizar el resumen creado haciendo clic derecho sobre él: Refrescar.
Para Agencias y Operadores: este enfoque es un orden de magnitud más rápido con tablas grandes que BUSCARV.
Desventajas: debe copiar manualmente los datos uno debajo del otro y agregar una columna con el nombre de la lista de precios. Si los tamaños de las tablas cambian, entonces tienes que hacer todo de nuevo.
Método 3: Comparación de tablas con Power Query
Power Query es un complemento gratuito para Microsoft Excel que le permite cargar datos en Excel desde casi cualquier fuente y luego transformar estos datos de la forma deseada. En Excel 2016, este complemento ya está integrado de forma predeterminada en la pestaña Datos (Datos), y para Excel 2010-2013, debe descargarlo por separado del sitio web de Microsoft e instalarlo; obtenga una nueva pestaña Power Query.
Antes de cargar nuestras listas de precios en Power Query, primero deben convertirse en tablas inteligentes. Para hacer esto, seleccione el rango con datos y presione la combinación en el teclado Ctrl+T o seleccione la pestaña en la cinta Inicio – Formato como tabla (Inicio — Formatear como tabla). Los nombres de las tablas creadas se pueden corregir en la pestaña Constructor (Dejaré el estándar Tabla 1 и Tabla 2, que se obtienen por defecto).
Cargue el precio anterior en Power Query usando el botón Desde tabla/rango (De tabla/rango) de la pestaña Datos (Fecha) o desde la pestaña Power Query (dependiendo de la versión de Excel). Después de cargar, volveremos a Excel desde Power Query con el comando Cerrar y cargar – Cerrar y cargar en… (Cerrar y cargar — Cerrar y cargar a…):
… y en la ventana que aparece luego seleccione Solo crea una conexión (Solo conexión).
Repita lo mismo con la nueva lista de precios.
Ahora vamos a crear una tercera consulta que combinará y comparará los datos de las dos anteriores. Para hacer esto, seleccione en Excel en la pestaña Datos – Obtener datos – Combinar solicitudes – Combinar (Datos — Obtener datos — Fusionar consultas — Fusionar) o presione el botón Combinar (Unir) de la pestaña. Power Query.
En la ventana de unión, seleccione nuestras tablas en las listas desplegables, seleccione las columnas con los nombres de los productos en ellas y, en la parte inferior, configure el método de unión: Completo externo (Exterior completo):
Después de hacer clic en OK Debería aparecer una tabla de tres columnas, donde en la tercera columna debe expandir el contenido de las tablas anidadas usando la flecha doble en el encabezado:
Como resultado, obtenemos la fusión de datos de ambas tablas:
Por supuesto, es mejor cambiar el nombre de las columnas en el encabezado haciendo doble clic en las más comprensibles:
Y ahora lo más interesante. ir a la pestaña Añadir columna (Añadir columna) y haga clic en el botón columna condicional (Columna condicional). Y luego, en la ventana que se abre, ingrese varias condiciones de prueba con sus valores de salida correspondientes:
Queda por hacer clic en OK y cargue el informe resultante a Excel usando el mismo botón cerrar y descargar (Cerrar y Cargar) de la pestaña. Inicio (Home):
Belleza.
Además, si se producen cambios en las listas de precios en el futuro (se agregan o eliminan líneas, cambian los precios, etc.), bastará con actualizar nuestras solicitudes con un atajo de teclado. Ctrl+otro+F5 o por botón Refrescar todo (Refrescar todo) de la pestaña. Datos (Fecha).
Para Agencias y Operadores: Quizás la forma más hermosa y conveniente de todas. Funciona inteligentemente con mesas grandes. No requiere ediciones manuales al cambiar el tamaño de las tablas.
Desventajas: Requiere la instalación del complemento Power Query (en Excel 2010-2013) o Excel 2016. Los nombres de las columnas en los datos de origen no deben cambiarse; de lo contrario, obtendremos el error "¡No se encontró la columna tal y tal!" al intentar actualizar la consulta.
- Cómo recopilar datos de todos los archivos de Excel en una carpeta determinada usando Power Query
- Cómo encontrar coincidencias entre dos listas en Excel
- Fusionar dos listas sin duplicados