Veremos hoy cómo con una función matricial en Excel podremos obtener un listado ordenado de elementos únicos, sin repetir:
Supongamos un listado de con diferentes Equipos repetidos, sobre una Tabla de Excel, que previamente ha quedado ordenada:
Como se ve en la imagen, el objetivo es lograr un listado como el que se aprecia en B2:B5; listado que se consigue aplicando en el rango B2:B20 la siguiente función matricial (ejecutada presionando Ctrl+Mayusc+Enter):
{=SI.ERROR(INDICE(Equipo;K.ESIMO.MENOR(SI(COINCIDIR(Equipo;Equipo;0)=FILA(INDIRECTO("1:"&FILAS(Equipo)));COINCIDIR(Equipo;Equipo;0);"");FILA(INDIRECTO("1:"&FILAS(Equipo)))));"")}
una función un poco larga, pero que vamos a analizar paso a paso.
Antes de comenzar explicar que hemos asignado un nombre al rango A2:A20 de la Hoja 'Matricial'
Equipo =Matricial!$A$2:$A$20
En primer lugar en lo más profundo de nuestra fórmula nos encontramos con
=COINCIDIR(Equipo;Equipo;0)
que tiene el efecto de numerar los diferentes elementos según su agrupación, como vemos en la imagen:
Por otro lado numeramos las filas, empleando la función FILAS, la fórmula matricial que usaríamos es:
{=FILA(INDIRECTO("1:"&FILAS(Equipo)))}
El siguiente paso es sencillo, los elementos únicos son aquellos en los que coincida la primera con la segunda columna:
Aplicando un condicional obtendríamos el orden del elemento buscado:
{=SI(COINCIDIR(Equipo;Equipo;0)=FILA(INDIRECTO("1:"&FILAS(Equipo)));COINCIDIR(Equipo;Equipo;0);"")}
Se puede observar como sólo obtenemos los valores de la ordenación para los primeros elementos de cada grupo, esto es, para los elementos únicos.
El siguiente paso consiste en ordenar este resultado obtenido, de tal forma que aparezcan arriba en nuestra futura lista, para ello aplicamos una función K.ESIMO.MENOR, anidando dentro de esta todo lo anterior:
{=K.ESIMO.MENOR(SI(COINCIDIR(Equipo;Equipo;0)=FILA(INDIRECTO("1:"&FILAS(Equipo)));COINCIDIR(Equipo;Equipo;0);"");FILA(INDIRECTO("1:"&FILAS(Equipo))))}
El penúltimo paso, sería aplicar la función INDICE sobre los valores anteriores en la matriz de datos 'Equipo', con el fin de obtener el elemento en concreto, que corresponda a esas ordenaciones:
{=INDICE(Equipo;K.ESIMO.MENOR(SI(COINCIDIR(Equipo;Equipo;0)=FILA(INDIRECTO("1:"&FILAS(Equipo)));COINCIDIR(Equipo;Equipo;0);"");FILA(INDIRECTO("1:"&FILAS(Equipo)))))}
Finalmente, para evitar el error #NUM! anidamos nuestro resultado, el ya esperado, en una función SI.ERROR:
{=SI.ERROR(INDICE(Equipo;K.ESIMO.MENOR(SI(COINCIDIR(Equipo;Equipo;0)=FILA(INDIRECTO("1:"&FILAS(Equipo)));COINCIDIR(Equipo;Equipo;0);"");FILA(INDIRECTO("1:"&FILAS(Equipo)))));"")}
Con lo que lograremos nuestro listado de elementos únicos en nuestra hoja de Excel.
Supongamos un listado de con diferentes Equipos repetidos, sobre una Tabla de Excel, que previamente ha quedado ordenada:
Como se ve en la imagen, el objetivo es lograr un listado como el que se aprecia en B2:B5; listado que se consigue aplicando en el rango B2:B20 la siguiente función matricial (ejecutada presionando Ctrl+Mayusc+Enter):
{=SI.ERROR(INDICE(Equipo;K.ESIMO.MENOR(SI(COINCIDIR(Equipo;Equipo;0)=FILA(INDIRECTO("1:"&FILAS(Equipo)));COINCIDIR(Equipo;Equipo;0);"");FILA(INDIRECTO("1:"&FILAS(Equipo)))));"")}
una función un poco larga, pero que vamos a analizar paso a paso.
Antes de comenzar explicar que hemos asignado un nombre al rango A2:A20 de la Hoja 'Matricial'
Equipo =Matricial!$A$2:$A$20
En primer lugar en lo más profundo de nuestra fórmula nos encontramos con
=COINCIDIR(Equipo;Equipo;0)
que tiene el efecto de numerar los diferentes elementos según su agrupación, como vemos en la imagen:
Por otro lado numeramos las filas, empleando la función FILAS, la fórmula matricial que usaríamos es:
{=FILA(INDIRECTO("1:"&FILAS(Equipo)))}
El siguiente paso es sencillo, los elementos únicos son aquellos en los que coincida la primera con la segunda columna:
Aplicando un condicional obtendríamos el orden del elemento buscado:
{=SI(COINCIDIR(Equipo;Equipo;0)=FILA(INDIRECTO("1:"&FILAS(Equipo)));COINCIDIR(Equipo;Equipo;0);"")}
Se puede observar como sólo obtenemos los valores de la ordenación para los primeros elementos de cada grupo, esto es, para los elementos únicos.
El siguiente paso consiste en ordenar este resultado obtenido, de tal forma que aparezcan arriba en nuestra futura lista, para ello aplicamos una función K.ESIMO.MENOR, anidando dentro de esta todo lo anterior:
{=K.ESIMO.MENOR(SI(COINCIDIR(Equipo;Equipo;0)=FILA(INDIRECTO("1:"&FILAS(Equipo)));COINCIDIR(Equipo;Equipo;0);"");FILA(INDIRECTO("1:"&FILAS(Equipo))))}
El penúltimo paso, sería aplicar la función INDICE sobre los valores anteriores en la matriz de datos 'Equipo', con el fin de obtener el elemento en concreto, que corresponda a esas ordenaciones:
{=INDICE(Equipo;K.ESIMO.MENOR(SI(COINCIDIR(Equipo;Equipo;0)=FILA(INDIRECTO("1:"&FILAS(Equipo)));COINCIDIR(Equipo;Equipo;0);"");FILA(INDIRECTO("1:"&FILAS(Equipo)))))}
Finalmente, para evitar el error #NUM! anidamos nuestro resultado, el ya esperado, en una función SI.ERROR:
{=SI.ERROR(INDICE(Equipo;K.ESIMO.MENOR(SI(COINCIDIR(Equipo;Equipo;0)=FILA(INDIRECTO("1:"&FILAS(Equipo)));COINCIDIR(Equipo;Equipo;0);"");FILA(INDIRECTO("1:"&FILAS(Equipo)))));"")}
Con lo que lograremos nuestro listado de elementos únicos en nuestra hoja de Excel.
Hola ExcelForo
ResponderEliminarMuy buen desglose y explicación de esta Matricial, ya la guardare a buen recaudo, yo estoy buscando también lo mismo para copiar los duplicados y que me los ordene de dos columnas o más, si tienes alguna sugerencia te lo agradecería.
Saludos Cordiales
Lázaro
Hola Lázaro,
Eliminarpodrías echar un vistazo a esta entrada, quizá te de la pista para lo que necesitas
http://excelforo.blogspot.com/2010/03/elementos-duplicados-en-campos.html
Espero te oriente
Slds
Hola ExcelForo:
ResponderEliminarTengo un catálogo de clientes B4:B165, que escribiendo en la celda $B$2 cualquier nombre me seleccione de color amarillo en formato condicional, Ahora lo que quiero es que las celda de color amarillo que son nombre me aparezcan en la celda G3,G14 ;de forma dinámica así correlativamente según las cantidad de nombre que hay con esa letra inicial.
Hola,
Eliminarmejor envíame un ejemplo de ese archivo con el que trabajas a
excelforo@gmail.com
ya que no acabo de enteder la estructura de datos de la tabla con la que trabajas.
Slds
hola, cuando utilizo la formula matricial {=FILA(INDIRECTO("1:"&FILAS(Equipo)))} en toda mi lista solo me salen 1 estaré haciendo algo mal
ResponderEliminarHola Milton,
Eliminarsupongo has verificado que has asignado a un rango de datos el nombre 'Equipo', y este rango está compuesto de varias celdas, por ejemplo, en el post, Equipo = $A$2:$A$20;
además entiendo que la estás ejecutando matricialmente.
Si todo esto es así, y te sigue dando tod valores 1, envíamelo a
excelforo@gmail.com
y le echo un vistazo.
Slds
Hola,
ResponderEliminarA mi me pasa lo mismo que a Milton ¿Como se puede solucionar?
Gracias y un saludo.
Hola,
EliminarVerifica que lo estás ejecutando matricialmente, es decir, presionando Ctrl+mayusc+Enter, para validar la fórmula.
Igualmente que has definido o asignado nombre al rango de trabajo.
Slds
Excelente!, muy inteligentemente logrado. Lo necesitaba y me funcionó. Excepto por un detalle: falla cuando el rango tiene celdas vacias. Me Explico: Mi rango de repetidos es de 30 celdas como máximo, pero rara vez están todas llenas, casi siempre en mi plantilla las últimas están vacias. Cómo lo arreglo? (en mi prueba con tu fórmula daba una lista vacia, sospeché que la razón era la expuesta y llene la totalidad de celdas y entonces funcionó a la perfección).
ResponderEliminarGracias por tu aporte y la ayuda que me puedas proporcionar: jmqplus@gmail.com
Hola Yac Mar Kyn,
Eliminarpues, sinceramente, no había probado esa posibilidad, tan inesperada por otra parte. La solución pasaría por añadir una nueva condición, se me ocurre la siguiente:
=SI.ERROR(INDICE(Equipo;K.ESIMO.MENOR(SI(COINCIDIR(SI(Equipo="";" ";Equipo);SI(Equipo="";" ";Equipo);0)=FILA(INDIRECTO("1:"&FILAS(Equipo)));COINCIDIR(SI(Equipo="";" ";Equipo);SI(Equipo="";" ";Equipo);0);"");FILA(INDIRECTO("1:"&FILAS(Equipo)))));"")
recuerda ejecutarla, por supuesto, matricialmente.
con esa modificación conseguimos que aparezcan todos los valores, también los vacíos...eso sí, los vacíos aparecen como un cero.
Espero te sirva, saludos
Este comentario ha sido eliminado por el autor.
EliminarMuchas gracias por tu pronta y acertada respuesta. De hecho a mi se me ocurrió otra que me funciona muy bien y omite el problema de los vacios: hice mi rango de repetidos (EQUIPO en tu ejemplo, PEDIDOS en mi caso) un rango dinámico con DESREF:
EliminarPEDIDOS=DESREF('IMPORTACIONES'!$N$8;0;0;CONTAR('IMPORTACIONES'!$N$8:$N$37);1), como puedes ver la función CONTAR limita el rango a celdas con dato. Funciona cuando las celdas vacias están al final, como en mi caso, pero requeriría otra solución en casos donde se presenten lista con vacios intercalados.
Gracias a ti Yav Mar Kyn!!...
Eliminares cierto que con DESREF consigues el rango dinámico, pero como bien dices se queda algo cojo cuando los vacíos no están al final.
Un coridal saludo
... lo importante y muy rescatable de esta solución es que en gran número de veces (sino la mayoría de éllas!) la lista se crea línea a línea, sin dejar "huecos", pero con un amplio límite final o sin él, para lo cual un rango dínamico es perfecto.
Eliminarmuchísimas gracias por tu atención y colaboración.
... un abrazo. Yav
Gracias por el aplicativo. Estuve buscando este ejemplo pero se me presenta el mismo problema que los usuarios anteriores ¿Como soluciono el problema del número 1 repetido en el rango al usar la siguiente fórmula? FILA(INDIRECTO("1:"&FILAS(Equipo)))
ResponderEliminarIngreso de manera matricial y el rango Hoja1!$A$2:$A$20.
Agradeciendo con antelación vuestra atención a la presente.
Gracias
Hola Eduardo,
Eliminarno te puedo decir otra cosa distinta a los demás...
la fórmula del post está máss que probada; y si escribes
=FILA(INDIRECTO("1:"&FILAS($A$2:$A$20)))
y la ejecutas matricialmente, te deberán aparecer números correlativos del 1 al 19.
Asegúrate que efectivamente está ejecutada matricialmente, tu fórmula debe tener este aspecto en la barra de fórmulas
{=FILA(INDIRECTO("1:"&FILAS($A$2:$A$20)))}
La función sólo devuelve todos los valores uno cuando no está ejecutada matricialmente, esto es, presionando Ctrl+Mayus+Enter en lugar de Enter solo.
Slds
Hola Excel Foro, gracias por vuestro comentario. Mucho agradeceré la gentileza de ver el archivo adjunto. Sería genial si nos echan una ayudita para solucionar nuestra dificultad.
ResponderEliminarDesde ya muchas gracias.
https://rapidshare.com/files/934140272/Matrices valores únicos.xlsx
Saludos
Ok, Eduardo, visto.
Eliminarcuando vayas a aplicar una fórmula matricial sobre un rango, debes seleccionar todo el rango.
En el ejemplo que adjuntas prueba seleccionado el rango D2:D20 con la celda activa D2, introduce la fórmula matricial o edítala, si ya la tienes escrita, y entonces ejecuta matricialmente.
Lo mismo para el rango B2:B20, selecciona todo el rango B2:B20 y con la celda B2 activa, introduce o edita la fórmula y ejecutala matricialmente.
Parece que tu tenías creado 19 fórmulas, en vez de una sóla que cubra el rango.
Espero haberme explicado con claridad.
Un cordial saludo
Gracias ExcelForo, muchas gracias. Simplemente magistral la solución y el blog.
ResponderEliminarY los usuarios anteriores estaban en el mismo error que yo. Ahora todo queda aclarado.
Desde Lima-Perú, un cordial saludo.
Tengo un cuadro en Excel con 11 Materias y cada una tiene 3Tres Lapsos (L1,L2,L3),necesito calcular el Promedio de L1,L2;l3 de cada materia. Cuando coloco la formula Promedio el resultado dice #Div/0 , esto pasa cuando no tiene notas, el alumno se retiro o no asistió mas. Y necesito que cuando no tenga notas en las materias el resultado sea "",o "*" Ejemplo.
ResponderEliminarCA IN MA Promedio
L1!L2!L3!DFN! !L1!L2!L3!DFN! !L1!L2!L3!DFN! !L1!L2!L3!
!13!14!14 ! !14!12!13!13 ! !10!12!13!12 ! !12!12!13!
! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! !
09!12! !11 ! !12!11!13!12 ! !08!10!11!11 ! !10!11!12
Hola Carlos,
Eliminarpodrías anidar la función que tengas definida para el cálculo del promedio (supongo que la que tengas funcionará), dentro de un SI.ERROR
=SI.ERROR(promedio(...);"")
Slds
GRACIAS POR LA FORMULA EL RESULTADO ES PERFECTO.
ResponderEliminarTengo un cuadro en Excel de cédulas y necesito que estén ordenadas, no tengan espacios en blanco "" . Ejemplo.
ResponderEliminarCÉDULA CÉDULA
V26454598 / V26454598
V26523318 / V26523318
V26530624 / V26530624
V26683840 / V26683840
V26683960 / V26683960
* / V28472426
* / V29674785
* /
V28472426 /
V29674785 /
De antemano Gracias por la ayuda.
Hola Omar,
Eliminarbien, en primer lugar tendríamos que tener claro cómo actuar para aquellos casos en los que no hay datos, ya que para una posterior ordenación, será necesario.
Quizá podrías enviarme el ejemplo con la explicación correcta y completa de la casuística a
excelforo@gmail.com
Slds
a mi me sale #¿NOMBRE?
ResponderEliminarHola, que tal
Eliminarespero te encuentres bien.
Quizá estés trabajando con Excel 2003, la función SI.ERROR no existía en esa versión; o tal vez no hayas ejecutado matricialmente...
Slds cordiales
Hola Ismael, he estado trabajando con la formula y me ha resultado perfecta, pero cómo puedo hacer si quiero agregar una condición? por ejemplo, si tuviera 2 columnas, una con el equipo y otra con nombres de personas y quisiera obtener una lista de valores únicos (nombres) sí y solo sí pertenecieran al equipo A. Es esto posible ajustando la formula matricial de alguna manera?
ResponderEliminarPudieras ayudarme?
Hola Vanessa,
Eliminarno digo que no sea posible, pero si para algo 'sencillo' para un listado simple, para algo con dos columnas se podría complicar bastante.
En esencia consistiría en trabajar en lugar de con el rango 'equipo' con un rango nuevo del que obtendríamos los nombres correspondientes a ese EquipoA.
Supongamos en A los diferentes equipos (repetidos) y en B varios nombres para cada Equipo.
Crearemos los siguientes nombres definidos:=
equipo =Hoja1!$A$2:$A$12
nombres =Hoja1!$B$2:$B$12
Names =SI(equipo="EquipoA";nombres;"")
Nuestra fórmula matricial para conseguir los Nombres únicos que pretenecen al Equipo A será:
=SI.ERROR(INDICE(Names;K.ESIMO.MENOR(SI(COINCIDIR(Names;Names;0)=FILA(INDIRECTO("1:"&FILAS(Names)));COINCIDIR(Names;Names;0);"");FILA(INDIRECTO("1:"&FILAS(Names)))));"")
Espero te sirva.
Slds
La fórmula fila ya la tengo ejecutada como matricial pero no me da más allá de 1 en todas mis filas. ¿qué podría estar haciendo mal?
ResponderEliminarCómo estás!?, me alegra igualmente saludarte.
Eliminarsi revisas comentarios anteriores, probablemente tu error será el mismo que comentado.
Para ejecutar matricialmente selecciona todo el rango (columna) B2:B20 y con todo seleccionado, presiona Ctrl+Mayusc+Enter
Un cordial saludo y muchas gracias
Hola, un cordial saludo. Disculpa la rudeza de mis exigencias :P ¿qué tal si mi rango está dado por una fórmula en la asignación de nombre, más o menos así:
Eliminar=DESREF(B2, 0, 0, CONTARA($B$:$B$)+1))
Hola!,
Eliminarsi suponemos tu listado en el rango B2:B... y en la celda B1 un rótulo, valdría igualmente, mientras tu Nombre definido 'equipo' tuviera esta forma:
=DESREF(Hoja1!$B$2;;;CONTARA(Hoja1!$B:$B)-1)
Un cordial saludo
Te pasaste !!!
ResponderEliminarMe funcionó perfect, sin macros ni nada
Gracias master !!!
Hola, es posible obtener una lista de valores únicos (ordenados en una columna) pero a partir de una matriz en lugar de una sola columna?
ResponderEliminarHola,
Eliminarte recomendaría, para este caso, aplicarás algo de programación:
http://excelforo.blogspot.com.es/2012/03/vba-objeto-collection-para-lograr.html
Saludos
Hola... muy agradecido por el post, me ha sido de mucha ayuda pero tengo una duda.
ResponderEliminarTengo una matriz de 3x20 donde se encuentran almacenadas diferentes fechas algunas repetidas entre ellas, quisiera extraer las fechas UNICAS en una sola lista, como podria lograrlo?
Gracias de antemano!
Hola Galeanix,
Eliminarpara ese caso te recomiendo emplees la siguiente macro:
http://excelforo.blogspot.com.es/2012/03/vba-objeto-collection-para-lograr.html
Saludos