Comparar listas en Excel

viernes, octubre 06, 2006

Una de los temas más corrientes en Excel es el de la comparación de listas, y su "hermano gemelo" la eliminación o prevención de duplicados.
Ya he tratado el tema en diversas oportunidades, por ejemplo
como comparar dos listas usando formato condicional o como evitar duplicados usando validación de datos. También he mostrado como usar consolidación de datos para comparar listas


En esta nota haremos una revisión ordenada del tema. Empecemos por plantearnos un ejemplo. En una misma hoja tenemos dos listas de productos





Para facilitar nuestro trabajo hemos definido dos nombres

lista1 =Hoja1!$A$2:$A$11
lista2 =Hoja1!$D$2:$D$11

1 - Encontrar duplicados usando la función COINCIDIR

En el rango B2:B11 ponemos la fórmula =COINCIDIR(A2,lista2,0); en el rango E2:E11 ponemos la fórmula =COINCIDIR(D2,lista1,0). El resultado es




Los resultados N/A identifican los valores de la lista 1 que no se encuentran en la lista 2, y viceversa. Podemos lograr un resultado más "elegante" combinando COINCIDIR con SI y ESERROR en esta fórmula

=SI(ESERROR(COINCIDIR(A2,lista2,0)),"","duplicado")

El resultado es




Podemos mejorar aún más la presentación usando formato condicional para dar un fondo de color a los duplicados. Por ejemplo, para la lista2 definimos esta condición



La dirección de la celda de referencia debe ser relativa (sin los signos $)

2 – Señalar duplicados usando formato condicional.

Como explicamos en la nota sobre el tema, usamos la función CONTAR.SI en el menú de formato condicional para comprobar si valores de la lista 1 también figuran en la lista 2. Usamos una fórmula distinta para cada lista.
En la lista 1 usamos la fórmula =CONTAR.SI(lista2,A2)>0





En la lista 2 usamos la fórmula =CONTAR.SI(lista1,A2)>0



El resultado es idéntico al método anterior



3 – Generar una lista de valores comunes a ambas listas

Digamos que ahora queremos generar una lista de valores que aparecen en ambas listas.
En nuestro ejemplo, escribimos esta fórmula en la columna F:

=SI(CONTAR.SI(lista2,A2)>0,A2,"")




Alternativamente podemos usar la fórmula =SI(CONTAR.SI(lista1,D2)>0,D2,""), que dará el mismo resultado aunque en distinto orden.

Como podemos ver en la lista de valores comunes aparecen celdas en blanco. Una alternativa para eliminar las celdas en blanco es copiar los valores de la lista a otro rango y usar la función Ir A--Especial--Celdas en Blanco, como explicamos en
esta nota.

También podemos hacerlo usando fórmulas. Para esto necesitaremos crear dos rangos de fórmulas auxiliares.
Siguiendo con nuestro ejemplo, escribimos esta fórmula en la celda H2 y la copiamos a lo largo del rango

=SI(CELDA("contenido",F2)="",0,FILA())




Esta fórmula nos permite dar un número único si en la columna F aparece un producto común a ambas listas o 0 (cero) si el resultado en F es blanco. Usamos la función CELDA y no ESBLANCO, ya ninguna de las celdas en la columna F está en blanco (todas contienen fórmulas).

El próximo paso es usar la función JERARQUIA en la columna I

=JERARQUIA(H2,$H$2:$H$11)

En esta fórmula hemos omitido el argumento opcional "orden", por lo tanto el resultado aparece en forma descendiente.




Podemos ver que para las celda con resultado 0 en la columna H, obtenemos el mismo número de orden.

El resultado en la columna I nos sirve de variable en la fórmula que escribimos en la columna J,

=INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0))

que nos da esta lista




El problema con esta fórmula es que aparecen resultados N/A. Para evitar esto agregamos una condición a nuestra fórmula, resultando

=SI(ESERROR(INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0))),"",INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0)))

es decir, primero evaluamos la fórmula y si el resultado da error, la función SI da como resultado "blanco", si no se aplica la función INDICE.




4 – Generar una lista de valores únicos

Para generar una lista de valores únicos, usamos el mismo método con la diferencia que la fórmula en la columna F pasa a ser

=SI(CONTAR.SI(lista2,A2)=0,A2,SI(CONTAR.SI(lista1,D2)=0,D2,""))

donde combinamos dos condiciones para obtener los productos que sólo aparecen en una de las listas.


Por supuesto, existen otros métodos para comparar listas, incluyendo el uso de macros. Esto será tema de una futura nota.


Categorías: Funciones&Formulas_, Manejo de Datos_

Technorati Tags: ,

37 comments:

Montanito 13 octubre, 2006 16:41  

Oye tengo una duda como puede comparar un vector con otro y que me devuelva que que parte se asemejan, ejemplo vectorA 1 2 3 4 5 6 7 y vectorB 4 3 2 1 5 6 7, quiero que me diga que posicion del vectorsB ocupan las semejanzas del 1, me entendes?

bye

Jorge L. Dunkelman 13 octubre, 2006 20:55  

Para comparar vectores, y calcular la posicion de un miembro de un vector en otro vector, utilizas la funcion CONCIDIR, como indico en esta entrada.
Puedes descargar un archivo con tu ejemplo aqui.
En este ejemplo uso Validacion de Datos para generar la lista desplegable en la celda D2.
La formula en la celda E2 es =COINCIDIR(D2,B2:B8,0)

Espero haber entendido

Chau

Madrynense 14 octubre, 2006 03:58  

Mil gracias por tan valiosa informacion!!! no sabes como necesitaba esta informacion, sos un grande.!!

Pablo Díaz 18 abril, 2007 00:21  

Hola Jorge, fuera del tema de esta entrada... Estoy tratando de que me muestre los datos que estén duplicados dentro de una misma columna, sin borrarlos, pero destacándolos de la misma forma que lo haces tu acá... con un formato condicional. Por favor, me puedes ayudar.

Jorge L. Dunkelman 18 abril, 2007 06:37  

Hola Pablo,
no tan fuera del tema. Suponiendo que el rango de datos a comparar empieza en A1, escribís en la ventanilla Fórmula de Formato Condicional
=CONTAR.SI($A$1:A1;A1)>1
Fijate que el el primer A1 es una dirección absoluta ($A$1, con el signo $).

Anónimo,  18 julio, 2007 19:00  

(Alexander Beltran Colombia)
hola jorge tengo una duda en
ejemplo q expones de comparar listas, al principio muestras como nombras a cada una de las lista con un rango especifico "
lista1 =Hoja1!$A$2:$A$11
lista2 =Hoja1!$D$2:$D$11
cuando ingreso la formula de
=COINCIDIR(A2,lista2,0); el sistema no me reconoce el nombre del campo q en este caso es lista 1 o lista 2, yo manejo el excel 2003 del paquete de office en español. mil gracias jorege espero me puedas colaborar con esta consulta estoy atento a tu respuesta gracias

Jorge L. Dunkelman 18 julio, 2007 23:05  

Lo mejor sería que me mandes el archivo así para revisarlo (jorgedun@gmail.com).
Si estás copiando las fórmulas de la página, ten en cuenta que dependiendo del sistema el separador de los argumentos puede ser ";" y no ",".
Otro problema podría ser algún error en el nombre que pones en la fórmula. Para pegar los nombres conviene usar la tecla F3.

Tecno 07 septiembre, 2007 19:16  

En el punto 3 cuando dices: "Una alternativa para eliminar las celdas en blanco es copiar los valores de la lista a otro rango y usar la función Ir A--Especial--Celdas en Blanco, "

No acaba de salir, una vez copiados solos los valores y ejecutando esta función el sistema responde que no hay celdas en blanco. ¿es posible que al colocar los "" la celda ya no este en blanco ?

gracias

Tecno 24 septiembre, 2007 00:25  

hola,

si usamos esta formula para generar una lista de valores unicos:
=SI(CONTAR.SI(lista2,A2)=0,A2,SI(CONTAR.SI(lista1,D2)=0,D2,""))

que sucede si por coincidencia los valores unicos estan en la misma fila? No es cierto que el valor unico de la segunda lista no seria tenido en cuenta.
Ciertamente se cumpliria la primera parte de la condición y este otro valor se pasaria por alto.

Como se podria solucionar?

Jorge L. Dunkelman 04 octubre, 2007 12:11  

Hola Tecno,
se trata de copiar los valores de las celdas a otro rango. De esta manera, cuando el resultado de la fórmula en el rango original es "", al copiar se obtiene una celda en blanco.
La celda original que contiene la formula, aunque no muestra ningún valor, no es una celda en blanco.

Jorge L. Dunkelman 04 octubre, 2007 18:57  

La fórmula =SI(CONTAR.SI(lista2,A2)=0,A2,SI(CONTAR.SI(lista1,D2)=0,D2,"")) prioriza los valores que se encuentran en la columna A. Sólo si no son unicos busca en la D.

Yashvé ♥ 11 diciembre, 2008 11:30  

Hola Jorge:
Me encantá tu blog... Tengo una pregunta:

Habrá una manera de comparar textos parecidos: Ejemplo; Si tengo una compañía que se llama
yashve C.A. y la otra Yahve CA.. el me podra decir que porcentaje de coincidencia hay entre esos dos nombres??

Gracias

Jorge L. Dunkelman 12 diciembre, 2008 09:44  

Hola
en principio se puede hacer pero hay que definir ciertas condiciones. Por ejemplo, podemos calcular que "Yashve" es muy similar a "Yashve CA". Pero que pasa con "Yashve" y "Veyash"? O con
"Yashve" y "Yavesh" cuando Yavesh es realmente un texto distinto aunque parecido?

Eugenio 17 febrero, 2009 20:29  

Hola Jorge, tengo el problema casi resuelto, mediante la siguiente formula "=BUSCARV(Alcaudete!B2;España!B2:B2051;1;FALSO)", pero necesito que en la busqueda me de el registro completo, o sea, la linea donde coincide el dato ¿me explico?.

Te agradezco la ayuda de antemano.

Jorge L. Dunkelman 18 febrero, 2009 20:09  

Eugenio
las fórmulas dan resultados en una celda, no en una fila. Para producir un registro completo a partir del valor de una celda, tienes que usar una macro.

uak 21 junio, 2009 21:05  

Buenas tardes, Jorge. llevo bastante tiempo pensando como sacar adelante una fórmula ayudandome de la página, pero no doy con la tecla:Tengo una hoja con datos de una cuenta corriente donde se abonan y cargan importes en distintas divisas y por distintos conceptos.Cuando creo subtotales por conceptos se suman todos los importes del mismo concepto: p.e. Ingreso en efectivo, recibos...pero la suma no distingue entre divisas y me lo suma todas las cantidades en euros.He solucionado la obtención de las cotizaciones diarias de divisas mediante actualización por internet y he añadido una columna "tipo de cambio". Mi interés está en que cuando exista un movimiento de cuenta en divisas, p.e: Dolar americano (en siglas sería USD), Libras esterlinas (GBP)poder pasarlo a euros para que al sumar con subtotales la cantidad sea real en euros. Para ello intento obtener una fórmula que cuando encuentre siglas distintas al euro (EUR) como GBP o USD me introduzca en la columna de tipo de cambio el valor obtenido por internet (que esta en una tabla de otra hoja), de modo que simplemente divida la cantidad en divisas por el tipo de cambio, me de el valor en euros y, por lo tanto, el subtotal en euros. GRACIAS.

Jorge L. Dunkelman 22 junio, 2009 19:01  

Estimado
los comentarios tienen que estar relacionados con el tema de la nota. Te sugiero que consultes en alguno de los muchos, y buenos, foros de ayuda de Excel que hay en la WEB.
De todas maneras, y a partir de tu descripción: creas una tabla con las tasas de conversión de las divisas; en una columna auxiliar traduces todo a euros con la función BUSCARV.

Anónimo,  20 octubre, 2009 12:05  

Hola Jorge:

Aunque esta entrada sea antigua, creo que conviene comentar una cosa para que no haya lugar a error.
En el punto 4-Generar una lista de valores únicos, pienso que esa fórmula no responde al título del punto (lista de valores únicos) porque podría haber valores únicos en la misma fila, tal como decía el usuario Tecno.
Si queremos la lista completa de valores únicos de las 2 columnas habría que añadir algo más.
Una solución posible, aunque no del todo satisfactoria, sería:
=SI(Y(CONTAR.SI(lista2;A2)=0;CONTAR.SI(lista1;D2)=0);A2&" - "&D2;SI(CONTAR.SI(lista2;A2)=0;A2;SI(CONTAR.SI(lista1;D2)=0;D2;"")))
De esta forma, cuando hubiera coincidencia en la misma fila de 2 valores únicos (en tu ejemplo en la fila 7), aparecería algo así como Producto 7 - Producto 6.
Supongo que lo ideal sería desarrollar una matriz donde cada valor encajara en una celda separada, pero esto no sé cómo se haría.

Por lo demás, esta nota me parece muy buena.
Un saludo,
SL

Anónimo,  04 marzo, 2010 22:57  

buen dia, quisiera saber como realizar en excel 2003 el conteo de celdas por el color.

Jorge L. Dunkelman 05 marzo, 2010 00:17  

Fijate en esta nota.
En esta otra nota muestro funciones definidas por el usuario para contar o sumar por colores.

jorge esgardo 25 mayo, 2010 00:18  

Cordial Saludo

Desearia conocer si es posible comparar una fila de una matriz de 5000 datos con una fila que se encuentra en la matriz de datos de 60000 datos y me señale la coincidencia.

Muchas Gracias por tu colaboracion

Jorge L. Dunkelman 25 mayo, 2010 18:00  

Hola Joge,
¿la matriz está en Excel? Si los datos están en filas supongo que hay un dato por cada celda de la fila. En Excel 2007 disponemos de 16384 columnas, por lo que no podríamos poner 60000 datos en una fila.

Anónimo,  19 agosto, 2010 22:42  

Estimado Jorge, mi pregunta es la siguiente:
Tengo dos bases de datos, con descripciones de productos cada una de ellas, relacionadas a un código. Una descripción textual por celda por cada producto. Las descripciones de una base y la otra son muy parecidas pero no iguales(respecto del mismo producto).Esto por un cambio de sistemas. Existe alguna forma de poder relacionarlas de acuerdo a la mayor similitud que puedan presentar, una referencia de una lista, vs la otra lista.
Saludos. Gary.

Jorge L. Dunkelman 20 agosto, 2010 07:56  

Gary,
se puede hacer pero los resultados pueden ser erróneos. Los productos, ¿no tienen el mismo número de catálogo en ambas tablas?

Anónimo,  20 agosto, 2010 17:21  

Estimado Jorge, lastimosamente NO tienen el mismo número de catálogo o código, por lo que resulta complicado justamente relacionar un producto con su similar de la otra base de datos. Lo único muy parecido que tuviesen una tabla con la otra sería la descripción, que por los formatos del sistema, tampoco es exactamente igual, pero si presenta mucha similitud en la mayoría de los casos.
Gracias x la respuesta. Gary.

Anónimo,  11 mayo, 2011 19:01  

Jorge como comparo dos columnas pero con diferente cantidad de columnas, es decir en una columna tengo 450 y en la otra 2034, en excel 2003

Jorge L. Dunkelman 11 mayo, 2011 21:20  

La cantidad de columnas es la misma, una, lo que difiere es la cantidad de filas.
Enel blog hay varias notas sobre comparación de listas. Fijate en la etiqueta "comparar listas".

Sandra de los Reyes 04 junio, 2012 21:26  

hola jorge!
retomando las preguntas de Anónimo y Yashvé, y es que tengo una hoja (PRODUCTOS!)con una lista de productos (p.e. clavos de 2"; clavo para contreto 3"; tornillo de 3/4...)que tengo que asociar con una categoría, el criterio para asignar las categorías está en la hoja CATALOGO!, donde la columna A tiene una lista genérica de productos (p.e. clavo; tornillo) y la columna B la categoría que quiero relacionar.

quisiera saber entonces como puedo poner en PRODUCTOS!B, la categoría de CATALOGO!B.

la coincidencia no es exacta, pero el catálogo de productos es bastante general y enlista además los errores de dedo más comunes, como podría ser (viático, viatico, viáticos, viaticos).

cualquier sugerencia es bien recibida, porque hablamos de listados semanales de 5000 productos, así que con que me resuelva "algunos" productos me daría por bien servida...

(o bien alguna sugerencia para cumplir mi objetivo)

Jorge L. Dunkelman 08 junio, 2012 11:57  

Hola Sandra,

podrías crear una tabla de "coincidencias" donde a cada posibilidad se le asigne el valor único correcto (por ejemplo, en la columna A de la tabla aparecería "viaticos", "viatico", "viático", etc. y al en la columna B el valor correcto, "viáticos"). Basándose en esta tabla podrías crear una columna axuiliar con el valor correcto para registro. Luego podrías usar SUMAPRODUCTO con resultados exactos y sin mayores complicaciones.

jcarloszamudio @ hotmail.com,  22 agosto, 2012 23:35  

hola Jorge,
Muiy parecida mi duda a ellos, si tengo un listado de proveedores con nombres similares por ejemplo:
Jorge Asociados
Jorge Asociados SRL
Como puedo hacer para qeu me busque esos parecidos? porque son más de 3000 líneas...
Saludos y gracias

Jorge L. Dunkelman 23 agosto, 2012 11:04  

Como ya puse en el pasado, no hay una forma exacta de hacerlo ya que nombres similares pueden pertenecer a proveedores distintos.

jose zamudio 24 agosto, 2012 16:19  

=(,
pero alguna forma que encuentre esos similares y evitarme revisar uno por uno las 3000 líneas, así nomas revisar 150 o las que aparezcan...

Jorge L. Dunkelman 26 agosto, 2012 15:07  

Se puede hacer usando un método recursivo. Por ejemplo, se podría programar una macro que revise todo los nombres que coinciden en los primeros 10 caracteres, agruparlos y revisarlos. Luego indentificarlos como "corregidos" y volver al proceso con coincidencias de 8 caracteres, y así sucesivamente.

Anónimo,  20 noviembre, 2013 18:07  

Hola me gustaría ver si me pueden ayudar.
Estor utilizando esta formula:
=INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0))

Como en el ejemplo de arriba, sol que la lista ordenada solo me da los datos que están seguidos, cuando se brinca un espacio en blanco ya no me da los siguientes datos.
que estoy haciendo mal??

Jorge Dunkelman 20 noviembre, 2013 20:58  

Tendrías que decir que hay en los rangos F2:F11 e I2:11

Angelina Figueroa 16 octubre, 2015 00:52  

Hola me gustaría ver si me pueden ayudar.
Estoy utilizando esta formula:
=CONTAR.SI(K2:AA2,">0")
pero el tema es que solo necesito que lo haga en las columnas intermedias
K2, M2, O2, Q2 etc...
te estaria muy agradecida

Jorge Dunkelman 17 octubre, 2015 11:48  

Si bien se puede hacer (la explicación excede el marco de un comentario), la pregunta es si la forma más eficiente de hacerlo es con CONTAR.SI.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP