Extraer elementos únicos y repetidos con fórmulas.

jueves, octubre 22, 2009

Si tenemos una lista de valores ordenados en una única columna podemos obtener una lista de los valores repetidos o de los valores únicos usando fórmulas.

Supongamos esta lista de nombres. Los nombres repetidos los hemos marcado con un fondo de color usando Formato condicional



Excel elementos únicos y repetidos

Si queremos crear una lista de valores únicos en el rango B2:B19, usamos esta fórmula matricial:


=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=1)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))





Dos observaciones importantes en relación a esta fórmula:


1 – ésta es una fórmula matricial y debe ser introducida apretando simultáneamente Ctrl+Mayúsculas+Enter


2 – ésta es una fórmula matricial “multicelular”, es decir, la misma fórmula da un resultado distinto en cada celda. Por esto primero debemos seleccionar el rango que va a ocupar la fórmula y luego introducirla.


Si queremos obtener una lista de los elementos repetidos, modificamos levemente la fórmula


=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)<>1)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))

Excel elementos únicos y repetidos

De la misma manera, si queremos extraer los elementos que se repiten 3 veces (no los hay en el ejemplo), usaríamos


=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=3)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))


Es decir, si queremos extraer los elementos que se repitan n veces, usamos

=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=n)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))

Si queremos que los resultados #¡VALOR! no aparezcan podemos usar Formato condicional

Excel elementos únicos y repetidos


Excel elementos únicos y repetidos

Una breve explicación de las fórmulas:

La fórmula


=(CONTAR.SI(Lista,Lista)=1)


genera un vector de valores VERDADERO o FALSO que multiplicamos por el número de fila para generar una serie ordenada

=(CONTAR.SI(Lista,Lista)=1)*FILA()-1

Excel elementos únicos y repetidos

Restamos 1 para dado que la primer fila en el rango es 2.


Los nombres repetidos dan como resultado -1; los valores únicos dan el número de fila menos 1.
Ahora tenemos que ordenar este vector, para lo que usamos K.ESIMO.MAYOR

=K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=1)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista))))

Excel elementos únicos y repetidos

Este vector nos sirve de argumento en la función INDICE para encontrar el elemento indicado.

¿Cómo haríamos para obtener los mismos resultados si los nombres estuvieran divididos en dos listas?





Technorati Tags:

33 comments:

Anónimo,  23 octubre, 2009 11:57  

Hola Jorge:

Una pregunta. ¿Por qué no repites en el segundo argumento del K.ESIMO.MAYOR la expresión FILA()-1 en lugar de FILA(INDIRECTO("1:"&FILAS(Lista))) que ya habías puesto antes en la fórmula al multiplicar por el CONTAR.SI? Creo que sería lo mismo pero más corto.

Gracias por tu labor. Un saludo

Jorge L. Dunkelman 23 octubre, 2009 13:12  

Efectivamente, la fórmula es más corta. El problema de usar FILA()-1 es que al mover el rango, por ejemplo si insertamos filas, los resultados son incorrectos. En cambio FILA(INDIRECTO("1:"&FILAS(Lista))) da siempre una serie de números que siempre empieza en 1 y termina en el número de filas de la matriz.
Para que la fórmula funcione en toda situación tendría que usar
FILA(INDIRECTO("1:"&FILAS(Lista)))
en ambos casos.
Gracias por todas las observaciones de los últimos días que siempre aportan.

Carlos Armando 24 octubre, 2009 12:18  

no me sale ya lo pase a ingles y no me funciona me da como resultado los mismos valores que estan en la lista no se que tengo mal esta es la fomula tal como la tengo: {=INDEX(Lista1,LARGE((COUNTIF(Lista1,Lista1)=1)*ROW()-1,ROW(INDIRECT("1:"&ROWS(Lista1)))))}, se podria subir algun archivo para una mejor ayuda

Jorge L. Dunkelman 24 octubre, 2009 14:38  

Carlos
¿cómo introducís la fórmula en el rango? Como indico en la nota, primero tenés que seleccionar el rango, en el ejemplo B2:B19, y luego introducir la fórmula apretando simultáneamente Ctrl+Mayúsculas+Enter

Anónimo,  27 octubre, 2009 09:59  

Hola Jorge

Soy el primer anónimo de esta nota (Sergio).
Sigo pensando en buscar algo más sencillo para generar la serie numérica 1,2,3....
Probé con FILA(1:$65536) para Excel2003 y en general funciona bien. Lo probé a reemplazar en algunos de tus ejemplos donde ponés FILA()-n y va bastante bien.
En el ejemplo de este ejercicio, reemplacé FILA(INDIRECTO("1:"&FILAS(Lista))) por FILA(1:$65536) en la última parte de la fórmula y funcionó. Pero en la primera parte de la fórmula (donde ponés FILA()-1) no lo hizo bien. Pero ahí tampoco funcionó bien la expresión FILA(INDIRECTO.....), aunque no tan mal como la que te estoy planteando.
Para el caso de serie numérica horizontal, también ví que va bien COLUMNA(A:$IV) en Excel2003.
Un saludo desde Madrid,
Sergio

Anónimo,  27 octubre, 2009 19:23  

Jorge
Me di cuenta de algo: en realidad, mejor que FILA(1:$65536) se puede poner directamente FILA(1:1) para obtener la sucesión numérica 1, 2, 3....
Lo que observo es que cuando está en el contexto de una fórmula matricial es cuando no funciona bien.
Saludos,
Sergio

Jorge L. Dunkelman 27 octubre, 2009 20:37  

Sergio
no es que no funcione bien. Cuando ponés FILA(1:1) en forma no matricial y copiás la fórmula, obtenés FILA(2:2), FILA(3:3) y así sucesivamente. Cuando la usás en el marco de una fórmula maricial, la fórmula calcula siempre FILA(1:1).

Jorge L. Dunkelman 27 octubre, 2009 20:59  

Sergio (nuevamente),
el uso de FILA(INDIRECTO("1:"&FILAS(Lista))) en la primer parte de la fórmula genera un problema con la función INDICE usada en forma matricial. Por eso no la puedes usar FILA()-1 es la mejor opción.

diego,  03 septiembre, 2010 12:20  

Muy buenas Jorge,
Como siempre, cuando me surge una duda y busco ideas siempre aparece tu blog :)

Como quiero que me salgan los valores únicos, pero también uno de referencia de los múltiples (lo que viene saliendo con el filtro avanzado y copiar valores únicos), pues el post me venía corto... aunque con el k.esimo.mayor he visto la luz!!

suponiendo que la lista de valores es a1:a14 en la columna B obtengo la posición de la fila de los valores únicos y uno de referencia:
=SI(CONTAR.SI(A1:$A$14;A1)=1;FILA();"")
la "gracia" está en dejar el A1 flotante para que se reduzca la matriz de búsqueda, así los números repetidos además de N en el contador siempre tendrán un 1.

y luego en la C, obtengo la matriz
=+INDICE($A$1:$A$14;K.ESIMO.MAYOR($B$1:$B$14;CONTARA($B$1:$B$14)-CONTARA(B1:$B$14)+1);1)
como veras, mi K la obtengo de una manera un tanto rústica (contara fijo - contara variable y sumando 1 para evitar el 0)

No es muy limpia, pero soluciona el problema de no poder tener ese listado variable mediante el filtro avanzado :D

Ahora viene el rizado de la cuestión:
He intentado que todo este rollo en vez de utilizar la columna B como intermedia, funcionara de manera matricial (sustituyendo la referencia a la columna B y aplicandola directametne en la función k.esimo) para tenerlo en una sóla fórmula y columna, pero no veo que sea posible... ¿estoy en lo cierto y no me queda otra que usar una columna de mas?

pues eso, que como siempre muchas gracias, y supongo que google me devolverá a tu blog en la siguiente duda :)

Jorge L. Dunkelman 03 septiembre, 2010 16:37  

Hmmm... No sé; tal vez se pueda hacer, no puedo decirlo con seguridad a primera vista. Pero, ¿que hay de malo con usar columnas auxiliares?

Anónimo,  06 septiembre, 2010 10:11  

hombre, malo malo no hay nada...
...mas que nada por tema de limpieza (si lo consigo poner con una sola celda suelo evitar intermedias), y como siempre, por saber "si se puede hacer" :D

es lo que tiene el excel, que siempre se busca más

pibfer 19 enero, 2011 18:06  

Respeta do Jorge,
pocas palabras para agradecerte todo este conocimiento que compartes y que perdure!!!

Una inquietud: alguna forma de resolverlo sin usar funciones matriciales?

Otra inquietud:
Cómo podría obtener en la columna B de este ejemplo el listado de todos los nombres sin importar cuántas veces se repitan, es decir, el nombre puede aparecer desde una (1) hasta N veces? Y en la columna C generar el listado de los nombres que se repiten más de una vez a partir de la columna B?

Éxitos y muchísimas gracias por tu tiempo.

Jorge L. Dunkelman 19 enero, 2011 21:56  

Tal vez las dos inquietudes estén relacionadas. Paa obtener una lista de todos los nombres que aparecen en la columna (es decir, los valores únicos) podés usar Filtro Avanzado como muestro en esta nota.

Hada Heavy sin cuento 25 noviembre, 2011 08:46  

hola Jorge kerido.. muchas gracias pro tu dulce sabiduria... pero por mas q sigo tu formula para extraer los repetidos no sale el resultado q tu obtienes, me da un error con eso de la evaluacion : INDICE(C13:C33;32); y no doy que el lo que pasa,
una pregunta, tengo necesariamente que hacer las dos columnas para primero ver los "unico" y luego con esas dos busco los "repetidos" o no es necesario... por fis ayudame... gracias
besitos

Jorge L. Dunkelman 25 noviembre, 2011 14:13  

No se a que te refieres con me da un error con eso de la evaluacion : INDICE(C13:C33;32), pero si sigues mi fórmula te sugiero que revises si la estás introduciendo como fórmula matricial (Ctrl+Mayúsculas+Enter).

eRiCkZiiN 08 febrero, 2012 22:13  

Necesito ayuda para extraer datos iguales; Tratare de explicarme.. Tengo una lista de equipos con nombre, modelo, serie, y otros datos, inclui una colmna llamada "Etatus" donde pongo "disponible, obsoleto, vendido, robado" segun sea el caso... Lo que quiero es copiar en otra colmna los Disponibles" en otra los Obsoletos" en otra los vendidos" y en otra los Robados" sera que puedes ayudarme... de antemano gracias!

Jorge L. Dunkelman 09 febrero, 2012 21:36  

Si se trata de una tarea a realizar una única vez puedes usar autofiltro y copiar los resultados manualmente. También puedes usar filtro avanzado con la opción de copiar a otro lugar (esta opción sería semi-manual).
Existe la posibilidad de hacerlo con fórmulas, pero no es trivial.
Finalmente se puede hacer con macros, si queremos que el proceso sea automático.

Anónimo,  22 marzo, 2012 15:19  

Donde pone Lista es el rango de datos?? donde buscar?? Gracias.

Jorge L. Dunkelman 23 marzo, 2012 19:18  

"Lista", en el ejemplo, es un nombre definido que se refiere al rango que contiene los nombres (A2:A19 en el ejemplo).
Si no estás familiarizado con el tema, puedes darle un vistazo a esta nota.

Leidy Rodriguez,  31 mayo, 2012 20:29  

Hola!
Mira estoy haciendo un archivo en donde en una hoja ingreso datos que puede que se repitan... y en otra hoja llamo los datos pero sin repetirlos. No se si sepas una formula que me pueda servir.

Muchas gracias!

Jorge L. Dunkelman 01 junio, 2012 17:31  

Con una tabla dinámica sería la forma más eficiente.

Jorge Ramirez,  08 junio, 2012 20:49  

Hola Jorge
Estoy realizando una busqueda matricial multicelular. En una ho0ja selecciono un valor de una lista y lo busco en otra hoja en una matriz (desordenada) pero solo me devuelve el primer valor. LA formula que uso es
=SI(FILAS($H$64:H64)<=AR$59;INDICE('Plantilla previa'!$F$3:$F$122;SI(AR$59>1;MIN(SI('Plantilla previa'!$E$3:$E$122=$V$59;FILA('Plantilla previa'!$E$3:$E$122)+FILA('Plantilla previa'!$E3)+1);FILAS($H$64:H64));COINCIDIR(V$59;'Plantilla previa'!$E$3:$E$122;0)));"")
Cuando la pongo con ctrl+shift+enter y la copia hacia abajo solo me despliega el primer dato. Que me recomiendas. De antemano muchas gracias!!

Jorge L. Dunkelman 17 junio, 2012 18:59  

Primero seleccionar el rango, luego aplicar la fórmula con ctrl+shift+enter.

Anónimo,  01 agosto, 2012 16:12  

puedo usar una formula para que los resultados en numeros auntomaticamente cambien los nombres que se le a asignado? ejem

jose 3
carlos 5

pero si carlos es menor que jose que pase arriba pero automaticamente con el nombre... se puede,..?

Jorge L. Dunkelman 02 agosto, 2012 07:16  

Creo que te refieres a ordenar listas con fórmulas. Puedes fijarte en esta nota.

Alianza Simon Bolivar 14 febrero, 2013 20:52  

Gracias por este excelente aporte, pero yo tengo una inquietud:
Tengo una base de datos "tablas" con los campos de Estado, municipo y parroquia "entre otros". Lo que deseo es seleccionar con una lista desplegable un estado y con ello me deje disponible los municipios correspondientes al estado en un listado desplegable y asi sucesivamente para llegar a las parroquias... Agradeceria su aporte

Jorge L. Dunkelman 15 febrero, 2013 07:49  

En la nube de etiquetas (en la parte superior del blog) puedes apretar la etiqueta "listas desplegables" para ver todas las notas sobre el tema. Fijate en las notas sobre "listas desplegables dependientes".

mathieu journet 26 abril, 2013 12:25  

Lo he intentado, pero soy incapaz.
Quiero listar codigos pero sin REPETIR, y estos codigos son resultados de una busqueda.

Tengo mi columna a de clientes : se llama routinga=DESREF(DATABASE!$A$2;;;CONTARA(DATABASE!$A:$A)-1)
y mi columna b de articulos : se llama
routingb==DESREF(DATABASE!$C$2;;;CONTARA(DATABASE!$C:$C)-1)

En mi otra hoja en función del cliente que se elige en una celda (c15) segun una lista ;quiero que me liste todos los articulos de este cliente pero que no se repitan.

=SI(FILA(A1)<=CONTAR.SI(routinga;$C$15);INDICE(routingb;K.ESIMO.MENOR(SI(routinga=$C$15;FILA(routinga)-1);FILA(A1)));"")

Me salen todos los articulos pero se repiten ; que tengo que hacer?

Gracias por su ayuda

Jorge Dunkelman 26 abril, 2013 14:43  

Fijate en la técnica que muestro en esta nota sobre valores únicos en listas desplegables.

Anónimo,  26 junio, 2013 00:04  

hOLA
tENGO UN VIEJITO EXCEL 2000.
Me siento un torpe, cuando leo vuestros post!
porfavor, no os rias demasiado de mi ignorancia ecellina......... ;-)
Primero que es una tabla matricial?

Segunda, si uso la función CONTAR SI? es correcto?

Necesito contar digitos repetidos de varias columnas.
por ejemplo, de A, B, C, D, E, F, G, H
en donde cada columna tiene digitos, no me importa el valor.
Solo necesito saber, cuantos digitos repetidos.
por ejemplo, el digito 6 aparece, 3 veces en la columna A
2 veces en la columna C, etc...
Como planteo esto?
Gracia, gracias, gracias por vuestra ayuda,
Paco, de Valencia


Anónimo,  11 julio, 2013 20:01  

Saludos, excelente blog!!!!

He intentado hacerlo para el caso de que la información esté dividida en dos listas... y no he podido. ¿Cómo sería?

Jorge Dunkelman 14 julio, 2013 09:55  

Paco,

nadie se ríe, todos fuimos principiantes alguna vez. Dejando el tema de las fórmulas matriciales de lado, si todo lo que quieres es contar cuantas veces veces aparece cada valor en una lista, CONTAR.SI es la función indicada.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP