Elementos únicos y repetidos en dos listas

sábado, octubre 24, 2009

En la nota de ayer vimos cómo extraer elementos únicos o repetidos en una lista usando fórmulas matriciales. En el ejemplo de ayer la condición era que los elementos estaban ubicados en una única lista, es decir, en una sola columna.

Usando fórmulas similares podemos también extraer elementos repetidos o únicos de dos listas.

En este caso supondremos dos listas de 9 nombres



Excel elementos únicos y repetidos

Hemos creado dos nombres para contener los rango de las listas


lista1 = A2:A10
lista2 = B2:B10

Para extraer los nombres que repetidos, aquellos que aparecen en ambas listas usamos esta fórmula matricial “multicelular”


={SI(CONTAR.SI(lista1,lista2)>0,lista2,"")}

Excel elementos únicos y repetidos

Recordemos que para usar esta fórmulas seleccionamos previamente el rango (en nuestro caso C2:C10) y luego introducimos la fórmula apretando simultáneamente Ctrl+Mayúsculas+Enter.

Por supuesto queremos que la lista de nombres repetidos aparezca ordenado, para lo cual usamos la función INDICE en forma matricial de la siguiente manera

=INDICE(lista1,K.ESIMO.MAYOR(SI(CONTAR.SI(lista2,lista1)>0,FILA()-1,""),FILA()-1))



Excel elementos únicos y repetidos

Al rango D2:D10 aplicamos formato condicional para ocultar los resultados #NUM! que aparecen.
La fórmula funciona de la siguiente manera:

+ la expresión SI(CONTAR.SI(lista2,lista1)>0,FILA()-1,"") genera un vector de números o blancos. Cuando un nombre de la lista 1 aparece en la lista 2, la fórmula produce un número equivalente al número de fila menos 1; en caso contrario produce un valor en blanco.

Excel elementos únicos y repetidos

+ Este vector los ordenamos con la función K.ESIMO.MAYOR


K.ESIMO.MAYOR(SI(CONTAR.SI(lista2,lista1)>0,FILA()-1,""),FILA()-1)

Excel elementos únicos y repetidos

+ Este vector ordenado nos sirve como argumento en la función INDICE para extraer los elementos de las filas correspondientes en la lista 1.

Para extraer elementos únicos, debemos definir previamente el orden de comparación. Es decir, nombres de la lista 1 que no aparecen en la lista 2 o nombres de la lista 2 que no aparecen en la lista 1.

Para extraer los nombres de la lista 1 que no aparecen en la lista 2 usamos la fórmula matricial


=INDICE(lista1,K.ESIMO.MAYOR(SI(CONTAR.SI(lista2,lista1)=0,FILA()-1,""),FILA()-1))

Excel elementos únicos y repetidos

En las fórmulas que hemos mostrado podemos usar también K.ESIMO.MENOR para cambiar el orden de aparición de los nombres:


=INDICE(lista1,K.ESIMO.MENOR(SI(CONTAR.SI(lista2,lista1)=0,FILA()-1,""),FILA()-1))



Excel elementos únicos y repetidos





Technorati Tags:

7 comments:

Verónica 22 enero, 2010 13:58  

Hola Jorge,

Primeramente felicitarte por tu blog, es de gran ayuda! Enhorabuena!

Necesito sacar elementos únicos de diferentes listas que están en diferentes hojas, de un mismo documento, a una hoja nueva en el mismo documento. Y no se como puedo hacerlo.

A ver si tengo suerte y me puedes echar una mano.

Gracias y un saludo!

Verónica

Jorge L. Dunkelman 22 enero, 2010 14:28  

Con la misma técnica que muestro aquí. Solamente tenés que definir el nombre de cada lista en la hoja correspondiente. En la entrada ambas listas están en la misma hoja, pero también pueden estar en hojas distintas.

Anónimo,  21 abril, 2010 23:44  

Hola Jorge,

Estuve leyendo el ejemplo último, lo copie a un Excel y aplique las formulas pero no me da la columna "Únicos Lista 1", es decir, no me aparecen los nombres (Carlos,Juana, Rosario, Sara, Luis) sino que me aparece #¡VALOR! #¡NUM!
#¡NUM! #¡NUM! #¡VALOR! #¡VALOR!#¡NUM!#¡VALOR!#¡NUM!
¿Qué estoy haciendo mal?
Gracias!!
Muy bueno el blog!
Juan T

Jorge L. Dunkelman 22 abril, 2010 07:22  

Parece ser que hay un problema con alguna de las referencias en la fórmula. Tenés que revisar las fórmulas. También podés mandarme el archivo para que lo revise.

mkda,  03 junio, 2010 01:05  

HOLA, esta formula tambien funciona con valores numericos??

Yair José Guzmán Brieva 31 julio, 2011 16:34  

Buenos días, saludos desde Colombia, tengo una consulta: Tengo un libro en excel 2010, el libro tiene varias hojas de cálculo, en cada hoja de cálculo hay por lo menos dos columnas, una con un nombre, otra con el número de cédula de esa persona (cédula es como el dni de ustedes), a su vez, cada hoja tiene un nombre, la idea es encontrar las cédulas duplicadas, escribirlas en una columna y que al lado escriba el nombre de las hojas donde están, cual es el objetivo de esto, cada hoja representa a un candidato a un cargo público, y los nombres en las hojas son las personas que dicen que votarán por ellos, a veces un ciudadano lo promete a varios candidatos que votarán por el y quiero saber como encontrar esas personas. Gracias por la atención prestada

Jorge L. Dunkelman 31 julio, 2011 18:54  

Podrías ahcerlo con unpoco de trabajo manual:
1 - copias todas las listas a una única hoja agregando un campo que identifique la hoja (el candidato)
2 - agregas una segunda columna auxiliar para identificar si una cédula aparece más de una vez. Digamos que las cédulas están en la columna A y la columna auxiliar en D, sería =CONTAR.SI($A$1:$A$1000,A1). Si queremos ver sólo los duplicados, no la primer ocurrencia, usamos CONTAR.SI($A$1:A1,A1)
3 - ahora podés filtrar usando como criterio al columna D>1.

Me pica la curiosidad, ¿es legal usar el número de cédula en este tipo de estudios?

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP