Supongamos esta lista de nombres. Los nombres repetidos los hemos marcado con un fondo de color usando Formato condicional
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)))))
El archivo con el ejemplo se puede descargar aquí
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)))))
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
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
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))))
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: MS Excel
Hola Jorge:
ResponderBorrarUna 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
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.
ResponderBorrarPara 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.
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
ResponderBorrarCarlos
ResponderBorrar¿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
Hola Jorge
ResponderBorrarSoy 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
Jorge
ResponderBorrarMe 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
Sergio
ResponderBorrarno 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).
Sergio (nuevamente),
ResponderBorrarel 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.
Muy buenas Jorge,
ResponderBorrarComo 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 :)
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?
ResponderBorrarhombre, malo malo no hay nada...
ResponderBorrar...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
Respeta do Jorge,
ResponderBorrarpocas 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.
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.
ResponderBorrarhola 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,
ResponderBorraruna 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
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).
ResponderBorrarNecesito 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!
ResponderBorrarSi 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).
ResponderBorrarExiste 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.
Donde pone Lista es el rango de datos?? donde buscar?? Gracias.
ResponderBorrar"Lista", en el ejemplo, es un nombre definido que se refiere al rango que contiene los nombres (A2:A19 en el ejemplo).
ResponderBorrarSi no estás familiarizado con el tema, puedes darle un vistazo a esta nota.
Hola!
ResponderBorrarMira 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!
Con una tabla dinámica sería la forma más eficiente.
ResponderBorrarHola Jorge
ResponderBorrarEstoy 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!!
Primero seleccionar el rango, luego aplicar la fórmula con ctrl+shift+enter.
ResponderBorrarpuedo usar una formula para que los resultados en numeros auntomaticamente cambien los nombres que se le a asignado? ejem
ResponderBorrarjose 3
carlos 5
pero si carlos es menor que jose que pase arriba pero automaticamente con el nombre... se puede,..?
Creo que te refieres a ordenar listas con fórmulas. Puedes fijarte en esta nota.
ResponderBorrarGracias por este excelente aporte, pero yo tengo una inquietud:
ResponderBorrarTengo 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
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".
ResponderBorrarLo he intentado, pero soy incapaz.
ResponderBorrarQuiero 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
Fijate en la técnica que muestro en esta nota sobre valores únicos en listas desplegables.
ResponderBorrarhOLA
ResponderBorrartENGO 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
Saludos, excelente blog!!!!
ResponderBorrarHe intentado hacerlo para el caso de que la información esté dividida en dos listas... y no he podido. ¿Cómo sería?
Fijate en esta nota.
ResponderBorrarPaco,
ResponderBorrarnadie 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.
Buen post, disculpa tengo una duda si quisiera sumar valores únicos pero con respecto a otra columna que contiene fechas y solo quiero un rango de fechas, como podría hacerlo?
ResponderBorrarHola Manuel,
ResponderBorrarcon SUMAR.SI o con SUMAPRODUCTO pero teniendo en cuenta lo que muestro en este post sobre el uso de fechas como argumentos en esas funciones.
SI.ERROR(INDICE(Tabla6[EQUIPO];COINCIDIR(0;INDICE(CONTAR.SI(Hoja1!$A$2:A2;Tabla6[EQUIPO]);0;0);0));"")
ResponderBorraresta es sencilla
Hola fiajate, que tengo un listado donde tengo que pasara solo los nombres repetidos a otra columna... me podrias ayudar..
ResponderBorrarHay varias formas de hacerlo. Por ejemplo podrías usar la fórmula CONTAR.SI(celda,rango) y filtrar todas las filas donde la fórmula da un resultado mayor 1.
ResponderBorrarMe parecio genial, me ayuda mucho, pero me gustaria q si lo puedes hacer con macros, te lo agradeceria.
ResponderBorrarSe puede. Publicaré un post sobre el tema en breve.
ResponderBorrarHola Jorge
ResponderBorrarEsta Fórmula la haces a partir de 1 sola columna, como se podría hacer si tienes por ejemplo 4 columnas y quieres que te extraiga los elementos repetidos más de 2 veces repetidos en las 4 columnas
Muchas Gracias
Saludos
Hola Jorge
ResponderBorrarHe estado trabajando en la fórmula que hiciste, lo hago como señalas primero selecciono el rango luego ctrlt+shift+enter pero no consigo que funcione, puedo bajar de algún lado la planilla que hiciste
Muchas Gracias
Acabo de agregar el enlace en la nota (inmediatamente abajo de la imagen).
ResponderBorrarRespecto a la consula de las cuatro columnas, hay varias formas de hacerlo dependiendo del tamaño y del diseño del modelo.
ResponderBorrarSi quieres atenerte a la técnica mostrada en esta nota, que es poco eficiente en términos de velocidad de proceso, tendrías que convertir las cuatro columnas en una o aplicar las fórmulas para cada columna.
Hola Jorge
ResponderBorrarMuchisimas Geacias por dejar el Link, no te imaginas cuanto e va a servir tu gran ayuda
Sigue adelante con el Blog que se necesita mucho tu ayuda
Nuevamente
Gracias Totales
Buenas tardes Jorge....
ResponderBorrarComo puedo obtener el registro mas alto de un empleado de acuerdo a lo siguiente
En la columna A capturo el nombre del empleado, este se puede repetir varias veces
En la columna B capturo la fecha de su solicitud.....
Como puedo sacar la fecha mas alta de "X" empleado
Saludos
Hay varias formas de hacerlo. La más sencilla sería usar Autofiltro para filtrar las filas del empleado y ordenar la tabla por fechas de más reciente a menos reciente (de mayor a menor).
ResponderBorrarTambién podrías hacerlo con la función MAX en forma matricial. Sería algo así como =MAX((rango de nombres=nombre)*rango de fechas). Las funciones matriciales se ingresan apretando simultáneamente Ctrl-Mayús.-Enter.
Muchas gracias, me resulto de maravilla
ResponderBorrarSaludos
Buend día, solicitando orientación
ResponderBorrarComo hago para que en una celda al seleccionarla tenga un recuadro de calendario para poder escoger una fecha, como cuando compras un boleto de autobus.
Seleccionas el cuadrito del calendario y al escoger el día lo coloca en la celda determinada.
Gracias
Hola, en la parte superior derecha del blog hay está la ventanilla "Buscar en el blog". Si pones ahí "calendario" podrás ver todos los post sobre el tema.
ResponderBorrarBuenas te agradesco la ayuda que puedas brindarme
ResponderBorrarEn mi trabajo me pidieron revisar una tabla de excel en donde la columna A estan los numeros de celular y en la columna B los totales consumidos.
Mi problema es el sigiente:
Tengo un mismo numero duplicado muchas beses y necesito quitar dichos duplicados pero sumando los totales de mi columna B y que me arroje como resultado solo una vez el numero de celular y la suma del total consumido
Gracias por la ayuda
Mi correo es kiwffel.quintero@gmail.com
Hola, hay varias formas de hacerlo. Te sugiero hacerlo con tablas dinámicas. Dominar el uso de tablas dinámicas te resultará muy útil en tu trabajo.
ResponderBorrarMuchas Gracias por el Posts. Una pregunta hay alguna formula matricial que me permita extrae los valores repetidos de una lista que contiene valoes repetidos para todos los elementos.?? Muchas Gracias
ResponderBorrarLas fórmulas en el post son matriciales, pero ¿por qué con fórmulas matriciales?
ResponderBorrarHay alternativas más eficientes con fórmulas naturales, tablas dinámicas e inclusive con POwer Query.
Hola Jorge,
ResponderBorrarmuchas gracias por tu post, se acerca bastante a lo que busco. Yo necesito una columna de repetidos pero tengo más de una columna como fuente y cada columna tiene valores únicos. Es decir lo que busco son qué valores de la primera columna se repiten en una, algunas o todas las columnas. Casi he conseguido para comparar dos columnas usando la formula que explicas, aunque creo que depende de la fila en la que empiezo hay valores repeditos que no devuelve. En todo caso, necesitaria poder aplicarla a un rango de columnas y no he podido. ¿Me podrías ayudar? Muchas gracias
Dada la complekidad de la tarea te recomiendo dos posibilidades:
ResponderBorrarusar Access tal como muestro en este post (no hace falta ser experto en Access para usar esta técnica);
usar Power Query (imposible oner la explicación en un comentario, irá un post en un futuro próximo).
hola, Soy Blas, este post es algo viejo pero me intereso la lectura
ResponderBorraren mi caso necesito una tercera columna que me muestre todos los valores quitando las duplicaciones, es decir necesito simular la funcion de "quitar duplicados " pero con formulas. asi, si jose aparece dos veces me mostrara solo una y si carlos aparece una vez tambien debe mostrarlo.
Con las soluciones planteadas, la columna de unicos muestra los resultados cuando existe una sola ocurrencia mientras que repetidos cuando ocurren mas de una vez.
Gracias
Hola Blas, hay muchas formas de hacerlo y determinar cual es la más apropiada depende del entorno del modelo y del ojetivo (resultado final, paso intermedio, etc.)
BorrarPor ejemplo, podés copiar la columna de nombres y aplicar Datos-Eliminar duplicados (solución estática); también podrías usar una tabla dinámica ya que al arrastrar la columna de nombres al área de las filas cada nombre aparece una única vez; o usar usar Filtro Avanzado (podés consultar mis posts sobre el tema).