Hola Paula, ¿se pueden comparar listas en Excel?
o
¿Cuál es la mejor manera de comparar dos conjuntos de datos en Excel?
Muy a menudo se requiere en Excel comparar dos listas, o dos conjuntos de datos para encontrar elementos que faltan o que coinciden. Como se trata de Excel, siempre hay más de una manera de hacer las cosas, incluyendo la comparación de datos. Desde las fórmulas y el Formato Condicional hasta Power Query. En este artículo vamos a ver varias formas de comparar dos listas en Excel y también veremos la comparación de filas enteras de un conjunto de datos.
Puede que tu forma preferida no esté incluida a continuación. Si no es así por qué no dejas un comentario abajo y compartes con nosotros cómo te gusta comparar dos listas o conjuntos de datos en Excel. Espero leer tus comentarios.
Deseamos comparar la Lista 1 con la Lista 2.
Contenidos
Formato condicional rápido para comparar dos columnas de datos
Limpiar el formato condicional
Comparar datos en Excel utilizando la función MATCH
Comparar 2 listas en Excel 365 con MATCH o XMATCH como Función de matrices dinámicas
MATCH y matrices dinámicas para comparar 2 listas
XMATCH Excel 365 para comparar dos listas
Tablas – Comparación de listas en Excel donde los tamaños de los rangos pueden cambiar
Subrayar las diferencias en las listas utilizando Formato condicional personalizado
Copiar fórmula a Formato condicional personalizado
Otras fórmulas utilizadas para comparar dos listas en Excel
VLOOKUP para comparar dos listas en Excel
XLOOKUP para comparar dos listas en Excel
COUNTIF para comparar dos listas en Excel
Cómo comparar 2 conjuntos de datos en Excel
Cómo comparar listas o conjuntos de datos con Power Query
Formato condicional rápido para comparar dos columnas de datos
El formato condicional te permitirá resaltar una celda o rango en base a criterios predefinidos. La forma más rápida y sencilla de comparar visualmente estas dos columnas rápidamente es utilizar la regla predefinida de resaltar el valor duplicado.
Comience seleccionando las dos columnas de datos.
Desde la pestaña Inicio, seleccione el desplegable Formato condicional. A continuación, seleccione Reglas de resaltado de celdas. A continuación, seleccione Duplicar valores.
Se abrirá un cuadro de configuración de Duplicar valores en el que podrá definir el formato y seleccionar entre Duplicar o Valores únicos.
Al seleccionar Duplicar, todas las entradas recurrentes se ajustarán al formato seleccionado. Ahora puede ver rápidamente los elementos de la Lista 1 que están en la Lista 2, ya que estos son los elementos formateados. También puede ver rápidamente los elementos de la lista 2 que no están en la lista 1 ya que estos no tienen el formato aplicado.
Sin embargo, también puede formatear los elementos Únicos. Esto se puede conseguir seleccionando Único en el cuadro de configuración Valores duplicados.
En este caso, hemos aplicado dos formatos condicionales diferentes. El rojo indicando los duplicados y el verde indicando los elementos únicos.
Nótese que no sólo he cogido las celdas con datos, he cogido todas las columnas de la A a la C. La columna B no tiene datos, por lo que no puede afectar a los resultados. Sin embargo, estas celdas sí contienen el formato condicional aplicado, por lo que sería una mejor práctica seleccionar sólo las celdas que necesitas.
Borrar el formato condicional
Para borrar todo el formato condicional, primero selecciona la celda, o el rango. A continuación, seleccione el desplegable de formato condicional en la cinta de inicio. A continuación, seleccione Borrar reglas. Por último, seleccione Borrar reglas de las celdas seleccionadas.
Si tiene más de un formato condicional aplicado a la vez y sólo quiere eliminar uno de ellos, seleccione Administrar reglas en el desplegable de formato condicional. Seleccione la regla que desea eliminar y, a continuación, seleccione Eliminar regla.
Al pulsar Aceptar, la regla se eliminará del Administrador de reglas y las celdas ya no contendrán el formato.
Así que esta es la forma más básica de comparar dos listas en Excel. Es rápido, sencillo y eficaz. También puedes aplicar un formato condicional basado en fórmulas, que veremos más adelante en este artículo.
Comparar datos en Excel usando la función MATCH
Hay muchas fórmulas de búsqueda que puedes usar para comparar dos rangos o listas en Excel. La primera que veremos es la función MATCH.
La función MATCH devuelve la posición relativa en una lista. Un número basado en su posición, si se encuentra, en la matriz de búsqueda.
La sintaxis de MATCH es
=MATCH (lookup value, Lookup array, Match type)
Donde lookup value es el valor para el que se quiere encontrar una coincidencia. Lookup array es la lista en la que se busca una coincidencia. Y Tipo de coincidencia le permite seleccionar entre una coincidencia exacta o aproximada.
Queremos escribir una fórmula de coincidencia para ver si los elementos de la Lista 2 están en la Lista 1.
En la celda E3 podemos introducir la fórmula
=MATCH(C2, $A$2:$A$21,0)
Al rellenar esta fórmula, para los valores en los que Excel encuentre una coincidencia, se devolverá la posición de dicha coincidencia. Cuando no haya coincidencia el valor de retorno será un #N/A.
Muy a menudo, la posición relativa o el #N/A no tiene ningún valor para nosotros y necesitamos convertir estos valores en verdadero o falso. Para ello podemos ampliar fácilmente nuestra fórmula Match utilizando una función lógica. Como Match devuelve un número, podemos utilizar la función ISNUMBER
=ISNUMBER(MATCH(C2, $A$2:$A$21,0))
Comparar 2 listas en Excel 365 con MATCH o XMATCH como función de Matriz Dinámica
Si estás utilizando Excel 365 tienes más alternativas a la hora de utilizar MATCH para comparar listas o datos. Como Excel 365 piensa en arrays, ahora podemos pasar un array como valor de búsqueda de MATCH y nuestros resultados se derramarán por nosotros. Esto elimina la necesidad de copiar la fórmula hacia abajo y con sólo 1 fórmula, su hoja de cálculo será menos propensa a errores y más compacta.
MATCH y Arrays dinámicos para comparar 2 listas
Si aún no está familiarizado con los Arrays dinámicos, le sugiero que lea este artículo: Excel Dynamic Arrays – A new way to model your Excel Spreadsheets: para entender mejor su funcionamiento y los rangos de derrame.
El único cambio en la fórmula de coincidencia es que en lugar de seleccionar la celda C2 como nuestro valor de búsqueda, seleccionaremos el rango C2:12
=ISNUMBER(MATCH (C2:C12,$A$2:$A$21,0))
Como puedes ver, la fórmula única derrama los resultados por la columna E.
XMATCH Excel 365 para comparar dos listas
Excel 365 también introduce la nueva función XMATCH. Al igual que la función MATCH, XMATCH devuelve una posición relativa en una lista. Ahora que estás familiarizado con XLOOKUP, que sustituye a la antigua función VLOOKUP, sabes que XLOOKUP viene con un poder adicional. Esto viene en forma de nuevas condiciones en la sintaxis de la fórmula como el modo de búsqueda y los tipos de coincidencia. Pues bien, XMATCH también como este poder adicional sobre su predecesor MATCH.
La sintaxis de XMATCH es
XMATCH (Lookup Value, Lookup Array, ,)
Donde
Lookup Value es el valor que se busca para encontrar la posición relativa
Lookup Array es la fila o columna que contiene el Lookup Value
El modo de coincidencia es opcional. A diferencia de la antigua función MATCH, el valor por defecto es una coincidencia exacta. También puede seleccionar entre
- Coincidencia exacta o la siguiente más pequeña
- Coincidencia exacta o la siguiente más grande
- Coincidencia con tarjeta comodín
El modo de búsqueda también es opcional. El modo por defecto (y única opción en la antigua función MATCH) es buscar de arriba a abajo. También puede seleccionar la búsqueda de último a primero y la búsqueda binaria. Si trabaja con búsquedas binarias. La opción de coincidencia con comodines no funciona.
Con XMATCH podemos utilizar tanto matrices dinámicas como referencias de celdas para crear la fórmula, al igual que hemos visto con MATCH. Para este ejemplo, utilizaremos Arrays dinámicos. La fórmula es muy similar a la que usamos con MATCH; excepto que no tenemos que seleccionar 0 para una coincidencia exacta ya que en XMATCH esta es la configuración por defecto. Vamos a mezclar las cosas un poco esta vez un vistazo a la búsqueda de elementos en la lista 2 no en la lista uno.
En este caso podemos utilizar la fórmula
=NOT(ISNUMBER(XMATCH(C2:C12,A2:A21)))
Donde no convertirá los verdaderos en falsos y falsos en verdaderos.
Tablas – Comparación de listas en Excel donde el tamaño de los rangos puede cambiar
En cada una de las fórmulas que hemos visto hasta ahora, hemos seleccionado un rango de celdas en nuestras funciones Match que no es dinámico. Eso significa que si añadimos nuevos datos a una de las listas, tenemos un paso manual para actualizar nuestra fórmula para incluir los nuevos datos.
Para convertir las listas en tablas, seleccione una de las listas y pulse CTRL. Este es el atajo de teclado para convertir a una tabla. Si ha seleccionado la cabecera en el rango de celdas, asegúrese de marcar la casilla para confirmar que su tabla tiene cabeceras.
Las tablas, por su naturaleza, utilizan nombres estructurados. Por lo tanto, cuando esté escribiendo una fórmula y seleccione una columna de una tabla, no mostrará las referencias de las celdas, sino el nombre de la columna.
Viendo nuestra fórmula anterior usando XMATCH para encontrar elementos en la lista 2 que no están en la lista 1, podemos reescribir esta función ahora usando nuestras referencias a la tabla
=NOT (ISNUMBER(XMATCH(list2,list1)))
Ahora, como hemos utilizado tablas, si añadimos una nueva fila a cualquiera de las tablas, nuestro rango de derrame también aumentará para incluir los nuevos datos.
Resaltar las diferencias en las Listas utilizando el Formato Condicional Personalizado
Al principio de este artículo vimos una forma muy rápida de comparar estas dos listas utilizando una regla predefinida para los duplicados. Sin embargo, también podemos utilizar el Formato condicional personalizado. Si no estás familiarizado con el Formato Condicional Personalizado, te sugiero que consultes este artículo: Trucos de Formato Condicional Dinámico de Excel:
Necesitamos ver dos enfoques diferentes aquí para resaltar las diferencias. Cuando no estamos utilizando tablas y hemos creado una fórmula verdadero/falso para identificar las diferencias, podemos tomar una copia de la fórmula y añadirla a nuestro Formato personalizado. Sin embargo, con las Tablas necesitamos forzar el uso de las referencias a las celdas.
Copiar la fórmula al Formato condicional personalizado
Empezamos por tomar una copia de la fórmula. Como hemos probado la fórmula en la hoja de cálculo, podemos ver que funciona antes de utilizarla en el formato condicional. Esta es la mejor práctica, ya que muy a menudo con las referencias de celdas relativas y absolutas puede ser difícil conseguir que la fórmula sea correcta.
Selecciona las celdas a las que quieres aplicar el formato personalizado. A continuación, desde la cinta de inicio, seleccione el menú desplegable de formato condicional y seleccione Nueva regla
Se abrirá el cuadro de configuración de la Nueva regla de formato y seleccione Usar fórmula para determinar las celdas a las que aplicar el formato. A continuación, pegue la fórmula y establezca el tipo de formato
Esto hará que todas las celdas que se encuentran en ambas listas tengan el formato elegido.
Tenga en cuenta que hemos seleccionado un rango de celdas para aplicar este formato condicional y que no es dinámico. Si utilizáramos tablas, esto se actualizaría sin necesidad de cambiar nada
Otras fórmulas utilizadas para comparar dos listas en Excel
Hay muchas fórmulas que puedes utilizar para comparar dos listas en Excel. Ya hemos visto MATCH y XMATCH, pero ahora vamos a ver algunas más. Cualquiera de las funciones de búsqueda funcionará realmente junto con algunas otras!
VLOOKUP para comparar dos listas en Excel
Si no está familiarizado con VLOOKUP, puede leer sobre él aquí. En pocas palabras VLOOKUP devolverá un valor correspondiente de una celda, si no hay un valor correspondiente se devolverá un error #N/A. En nuestro ejemplo estamos trabajando con texto. Por lo tanto, podemos realizar un VLOOKUP y comprobar si devuelve texto. Si estuviéramos utilizando números, podríamos sustituir ISTEXT por ISNUMBER.
Podríamos utilizar la función =ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))
O si estuviéramos utilizando matrices dinámicas en Excel 365, podríamos utilizar la función
=ISTEXT(VLOOKUP (C2:C12,$A$2:$A$21,1,FALSE))
XLOOKUP para comparar dos listas en Excel
XLOOKUP se introdujo en Excel 365 y puedes encontrar más información sobre él aquí. Muy parecido a VLOOKUP, XLOOKUP devolverá un valor correspondiente de una celda, y puede definir un resultado si el valor no se encuentra. Utilizando arrays dinámicos, la función sería
=ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))
COUNTIF para comparar dos listas en Excel
La función COUNTIF contará el número de veces que un valor, o texto, está contenido en un rango. Si el valor no se encuentra, se devuelve 0. Podemos combinar esto con una sentencia IF para devolver nuestros valores verdaderos y falsos.
=IF(COUNTIF (A2:A21,C2:C12)<>0, «True», «False»)
Cómo comparar 2 conjuntos de datos en Excel
Comparar dos listas es bastante fácil y ya hemos visto varias formas de hacerlo. Pero comparar dos conjuntos de datos puede ser un poco más difícil.
Veamos un ejemplo. Tenemos dos tablas de datos, cada una con las mismas cabeceras de columna. Observando la imagen, podemos ver que para hacer coincidir estas dos tablas sería necesario mirar más de una columna.
Cuando se necesita mirar más de una columna, la solución sería crear una columna compuesta combinando los datos en una sola columna. Esto creará una columna única para cada fila que podremos utilizar como columna coincidente
En este ejemplo podríamos combinar el Nombre y el DoB para dar a cada tabla un identificador único
Hay muchas formas de unir contenidos de una celda, en este caso haremos una simple concatenación. Como estamos usando tablas, la fórmula calzará el formato de nomenclatura de la tabla.
= &»-«&
Repite los pasos en la segunda tabla.
Ahora podemos utilizar cualquiera de los ejemplos anteriores para comparar estas dos nuevas columnas de datos. Cuando coinciden, sabemos que coinciden con toda la fila.
Comparar listas o conjuntos de datos utilizando Power Query
También puedes comparar listas y conjuntos de datos utilizando Excels Power Query. Conectándose a las tablas y luego fusionando las tablas, utilizando diferentes tipos de join podemos comparar ambas listas.
En este vídeo aprenderás a comparar o conciliar dos conjuntos de datos diferentes utilizando Excels Power query
Hay un conjunto de datos para acompañar este vídeo y practicar que puedes coger de este artículo.