miércoles, 22 de febrero de 2012

Una matricial para obtener una lista de valores únicos en Excel.

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:

Una matricial para obtener una lista de valores únicos en Excel.


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:

Una matricial para obtener una lista de valores únicos en Excel.


Por otro lado numeramos las filas, empleando la función FILAS, la fórmula matricial que usaríamos es:
{=FILA(INDIRECTO("1:"&FILAS(Equipo)))}

Una matricial para obtener una lista de valores únicos en Excel.


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.

33 comentarios:

  1. Hola ExcelForo

    Muy 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

    ResponderEliminar
    Respuestas
    1. Hola Lázaro,
      podrí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

      Eliminar
  2. Hola ExcelForo:
    Tengo 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.

    ResponderEliminar
    Respuestas
    1. Hola,
      mejor 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

      Eliminar
  3. hola, cuando utilizo la formula matricial {=FILA(INDIRECTO("1:"&FILAS(Equipo)))} en toda mi lista solo me salen 1 estaré haciendo algo mal

    ResponderEliminar
    Respuestas
    1. Hola Milton,
      supongo 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

      Eliminar
  4. Hola,

    A mi me pasa lo mismo que a Milton ¿Como se puede solucionar?

    Gracias y un saludo.

    ResponderEliminar
    Respuestas
    1. Hola,
      Verifica 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

      Eliminar
  5. 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).
    Gracias por tu aporte y la ayuda que me puedas proporcionar: jmqplus@gmail.com

    ResponderEliminar
    Respuestas
    1. Hola Yac Mar Kyn,
      pues, 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

      Eliminar
    2. Este comentario ha sido eliminado por el autor.

      Eliminar
    3. Muchas 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:

      PEDIDOS=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.

      Eliminar
    4. Gracias a ti Yav Mar Kyn!!...
      es 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

      Eliminar
    5. ... 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.

      muchísimas gracias por tu atención y colaboración.

      ... un abrazo. Yav

      Eliminar
  6. 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)))

    Ingreso de manera matricial y el rango Hoja1!$A$2:$A$20.

    Agradeciendo con antelación vuestra atención a la presente.

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola Eduardo,
      no 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

      Eliminar
  7. 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.

    Desde ya muchas gracias.

    https://rapidshare.com/files/934140272/Matrices valores únicos.xlsx

    Saludos

    ResponderEliminar
    Respuestas
    1. Ok, Eduardo, visto.
      cuando 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

      Eliminar
  8. Gracias ExcelForo, muchas gracias. Simplemente magistral la solución y el blog.

    Y los usuarios anteriores estaban en el mismo error que yo. Ahora todo queda aclarado.

    Desde Lima-Perú, un cordial saludo.

    ResponderEliminar
  9. 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.
    CA 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

    ResponderEliminar
    Respuestas
    1. Hola Carlos,
      podrí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

      Eliminar
  10. GRACIAS POR LA FORMULA EL RESULTADO ES PERFECTO.

    ResponderEliminar
  11. Tengo un cuadro en Excel de cédulas y necesito que estén ordenadas, no tengan espacios en blanco "" . Ejemplo.

    CÉDULA CÉDULA
    V26454598 / V26454598
    V26523318 / V26523318
    V26530624 / V26530624
    V26683840 / V26683840
    V26683960 / V26683960
    * / V28472426
    * / V29674785
    * /
    V28472426 /
    V29674785 /
    De antemano Gracias por la ayuda.

    ResponderEliminar
    Respuestas
    1. Hola Omar,
      bien, 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

      Eliminar
  12. a mi me sale #¿NOMBRE?

    ResponderEliminar
    Respuestas
    1. Hola, que tal
      espero 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

      Eliminar
  13. 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?
    Pudieras ayudarme?

    ResponderEliminar
    Respuestas
    1. Hola Vanessa,
      no 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

      Eliminar
  14. 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?

    ResponderEliminar
    Respuestas
    1. Cómo estás!?, me alegra igualmente saludarte.
      si 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

      Eliminar
    2. 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í:
      =DESREF(B2, 0, 0, CONTARA($B$:$B$)+1))

      Eliminar
    3. Hola!,
      si 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

      Eliminar
  15. Eduardo Piñaabril 02, 2014

    Te pasaste !!!

    Me funcionó perfect, sin macros ni nada
    Gracias master !!!

    ResponderEliminar