Métodos para comparar listas en Excel

La importancia de comparar listas en Excel de forma automática

Aprender a comparar listas en Excel es muy importante para saber si existen fallos o huecos en nuestro conjunto de datos. Compararlas de forma automática en Excel te permite una mayor eficiencia en el trabajo y mucha más seguridad para no cometer errores. En este post os queremos enseñar todas las formas de comparar listas en Excel para que podáis tener todas las herramientas en vuestras manos.

1. El uso de la fórmula COINCIDIR (MATCH)

Para explicar el procedimiento a seguir hemos usado dos tablas en Excel. La tabla principal (azul) equivale a todos los clientes que han venido a nuestra tienda A y la segunda tabla a todos los clientes que han venido a nuestra tienda B. Queremos comparar los nombres de la lista A con los de la lista B y ver las personas que solamente han venido a una de nuestras tiendas. En las dos tablas tenemos: una fila con el ID del cliente, el nombre del cliente y una columna vacía.

Lo primero de todo es poner la función COINCIDIR en la primera celda de nuestra columna vacía. Con esta función podemos buscar el valor que nos interesa entre otros valores. Usaremos el ID del primer cliente de la tabla principal como valor. Después la función nos pide la “matriz buscada” que simplemente se refiere a que seleccionemos la matriz dónde queremos buscar el valor previamente seleccionado. Es muy importante en este segundo paso fijar la referencia con F4 para que la matriz buscada sea la misma para todos los valores. Para finalizar pondremos “0” en “tipo de coincidencia porqué queremos una coincidencia exacta.

Con la función aplicada nos aparecerá un 17. Este 17 nos indica la posición de nuestro valor dentro del rango indicado de la segunda tabla. Es decir: el primer cliente también está en la segunda tabla y lo encontramos a la 17ª posición. Ahora solo queda copiar la fórmula hacia abajo pulsando la pequeña cruz en la esquina inferior derecha de la celda.

2. El uso de la fórmula CONTAR.SI (COUNTIF)

El error que aparece como N/A son los clientes que no aparecen en la segunda tabla y por lo tanto no están repetidos. Ahora podríamos repetir la misma función en la segunda tabla para ver aquellos clientes que no se encuentran en la tabla principal. Aun así, utilizaremos otra función llamada CONTAR.SI, que cuenta el número de celdas dentro del rango que cumplen el criterio especificado. El rango serán todos los ID de la tabla principal y el criterio el primer ID de la segunda tabla. Una vez ejecutada la función nos aparecerá un “1” en la celda, que nos indica la cantidad de veces que aparece el ID de la segunda tabla en la tabla principal. En las filas que aparezca un “0” significará que ese cliente no aparece en la otra lista.

3. COINCIDIR y CONTAR.SI desde el formato condicional

Usar el formato condicional nos permite resaltar aquellos datos que queremos marcar. Si aplicamos el formato condicional a las fórmulas COINCIDIR y CONTAR.SI, los resultados que no salgan en las dos tablas quedarán resaltados. Primero seleccionamos las dos columnas de la tabla principal y buscamos la opción de “nueva regla” en el formato condicional. En la ventana emergente que se abre aparecen todas las opciones para fijar condiciones. El tipo de regla que nos interesa está ubicado en la última posición y es la que nos permite poner una función cómo regla. En la descripción de la regla es dónde tenemos que poner la función COINCIDIR.

Esta fórmula todavía no está lista ya que el formato condicional requiere que la fórmula devuelva el valor verdadero para aquellas filas dónde se aplicará el formato, sin embargo, la función COINCIDIR nos devuelve un error N/A para aquellas celdas que no encuentra. Como nos interesa resaltar justamente estas celdas donde la función COINCIDIR nos devuelve el error N/A , tenemos que usar una función llamada ESNOD. Esta función nos permite obtener el valor verdadero en esos casos en que la función COINCIDIR nos da error.

Es importante fijar a fórmula para que todas las celdas sean evaluadas respecto a la columna de ID. Ahora solo nos falta definir el formato y para eso tenemos que ir a “formato” en la pestaña de “relleno” y elegir el color que queramos para resaltar las celdas.

Usar otras herramientas en lugar de fórmulas

4. Comparación de listas con la herramienta Consolidar

Nos dirigimos a la lista principal (verde) y en la primera fila, justo en la última columna ponemos el número “1” y copiamos el valor hacia abajo. Lo mismo hacemos en la segunda lista, pero con el número “2”.

De esta manera tenemos identificados los valores de la primera lista con el número uno y los valores de la segunda lista con el número dos. Ahora tenemos que ir a la pestaña de “datos” y dentro del grupo herramientas de datos debemos pulsar el comando “consolidar”. Nos aparecerá un cuadro de diálogo.

En la primera opción desplegable tenemos que dejar activada la función de suma. Luego pulsamos el botón de selección de rango y seleccionamos las columnas de nombre y número de la primera lista. Volvemos a pulsar el botón de selección de rango y agregamos la selección. De nuevo pulsamos selección de rango, seleccionamos las columnas de la segunda lista y agregamos. El comando “consolidar” se encargará de agrupar los nombres de la columna cliente y sumará las celdas de la columna de los números. Es Importante activar en el cuadro de diálogo la opción de fila superior y columna izquierda para hacerle saber a Excel que tenemos los rótulos en esas posiciones.

Al pulsar el botón “aceptar” nos aparecen nuestros datos consolidados. Ahora podemos ver los valores sumados. El número 3 indica que el cliente aparece en ambas listas, el número 2 quiere decir que solo aparece en la lista verde y el número 1 en la lista azul.

Cuando dominas las posibilidades de Excel, es mucho más fácil y rápido hacer tu trabajo

Si te habías encontrado en una situación en que querías pedir información determinada y proteger la hoja, podrías haber mejorado procesos aplicando Validación de datos en Excel. Si quieres conocer y dominar todo el potencial que te ofrece Excel para ser más eficiente en tu trabajo, infórmate de nuestro curso práctico de Excel.

¿No conoces Aglaia? Somos tus consultores especializados en herramientas Microsoft. Te acompañamos en cada paso de tu formación para garantizar que sacas el máximo provecho de tus datos.