lunes, diciembre 03, 2007

Extraer elementos únicos de un rango en Excel

Ya hemos visto cómo generar una lista de elementos únicos a partir de un rango en una columna en Excel. Para esta tarea usamos Datos—Filtro--Filtro Avanzado—Elementos Únicos.
El problema surge cuando el rango incluye más de una columna. En estos casos no podemos usar Filtro Avanzado.
Dado que últimamente he recibido varias consultas sobre este tema, presentaré aquí dos soluciones posibles.

Supongamos esta situación




En este rango tenemos 6 elementos, pero sólo 4 elementos únicos.

Para generar un lista de elementos únicos podemos usar la función UNIQUEVALUES que forma parte del complemento MoreFunc que tantas veces he recomendado y que recomiendo descargar e instalar.
Una vez instalado el complemento, seleccionamos la función con el asistente de funciones seleccionando la categoría MoreFunc




Dado que esta función da como resultado una matriz, pero un una celda sólo podemos ver un resultado, la combinamos con la función INDICE para exponer todos los elementos de la matriz en un rango de celdas. La fórmula, en nuestro ejemplo, es

=INDICE(UNIQUEVALUES($A$1:$C$2);FILAS($5:5))

que ponemos en la celda A5 y copiamos hasta la celda A9




También podemos usar esta variante con la función FILA en lugar de la función FILAS

=INDICE(UNIQUEVALUES($A$1:$C$2);FILA()-4)

Este complemento incluye también la función COUNTDIFF que da como resultado el número de elementos únicos en el rango




Otra alternativa es copiar el código de la función UNIQUEITEMS de John Walkenbach. Esta función también permite contar el número de elementos únicos en un rango y, combinándola con la función TRANSPONER, generar una lista de elementos únicos





La fórmula con la función TRANSPONER debe ser introducida como fórmula matricial (pulsando al mismo tiempo Ctrl+Mayúsculas+Enter)





Technorati Tags:

20 comentarios:

  1. estimado quisiera hacerle una consulta, existe algun metodo en el cual se pueda realizar el calculo de a cuantas filas corresponde el 80% sobre la suma total de una columna con aproximación, para ser mas claro, si de la suma de la columna el resultado es 100, cuantas items se necesitaron para llegar al 80% de la suma

    ResponderBorrar
  2. Una solución sería crear una función definida por el usuario.
    Si queremos usar funciones nativas de Excel podríamos hacer lo siguiente. Supongamos que en el rango A1:A10 tenemos una serie del 1 al 10 y queremos saber cuantos elementos son necesarios para sumar el 80% de la serie.
    - en la celda B1 ponemos la fórmula =A1
    - en la celda B2 ponemos la fórmula =A2+B1 y la copiamos hasta B10
    - en una celda libre ponemos la fórmula
    =COINCIDIR(SUMA(A1:A10)*0,8;B1:B10)+1
    que da como resultado 9. Es decir necesitamos 9 elementos. La suma de los primero nueve elementos de la serie es 45; el 80% del total de la serie es 44 (55*80%).

    Espero haber interpretado correctamente tu consulta.

    ResponderBorrar
  3. Estimado Amigo, quisiera hacerle una consulta, existe algun metodo en el cual se pueda realizar el contról de ventas, existencias y ganancias de un listado de productos y que además pueda congelar estos valores en el momento en que yo decida realizar una auditoría para que las modificaciones posteriores no afecten estos resultados; agradecería profundamente me pueda dar una manito en este sentido o alguna sugerencia de como puedo llevar adelante este control.
    Gracias

    ResponderBorrar
  4. Hola Jaime,

    mi más calurosa recomendación es NO HACER semejante tarea con Excel.
    Si bien se puede hacer con Excel, hay herramientas más apropiadas, como Access. Además me parece que desarrollar semejante aplicación, ya sea con Excel o con cualquier otra herramienta, es tarea para un programador experimentado.

    ResponderBorrar
  5. Hola Jaime, estuve buscando informacion y encontre tu blogg, quisiera q me ayudes: Tengo un libro con unos datos q son leidos de un PLC y quiero almacenar esos datos en otra hoja pero a manera de un historial y para ello estoy usando un boton con una macro, pero no encuentro como hacer q cada vez q presione el boton para guardar los datos, los datos sean pegados en la fila siguiente para no perder los datos anteriores. espero me puedas ayudar estare muy agradecido de antemano.

    Saludos

    ResponderBorrar
  6. No se que te recomendaría Jaime, pero yo (Jorge) te recomiendo leer la nota sobre como agregar datos en la primer fila libre de la tabla

    ResponderBorrar
  7. Hola Jorge,

    quisiera hacerte una consulta relacionada a este tema del filtrado: tengo una tabla que tiene 2 campos, para este caso: Programa (txt), y Fecha (date). En Programa hay elementos repetidos, y necesito contar la cantidad de elementos distintos de la lista, por mes/año. El problema radica en que no puedo bajar addins porque la empresa no lo permite, así que lo estoy tratando de ver via funciones y/o VBA, pero no se me ocurre nada. Cualquier ayuda será más que apreciada. Desde ya, gracias por tu tiempo. Saludos, Martín.

    ResponderBorrar
  8. Para usar la técnica que muestro en esta nota no tienes que instalar ningún Add in. La funcionalidad es nativa de Excel.
    También puedes usar la técnica que muestro en esta nota

    ResponderBorrar
  9. Jorge!
    buenoas dias!, tengo una consulta, muy parecida a lo que expones en esta nota, pero hay que agregarle un condicional mas y no se como hacerlo, seria lo siguiente, Yo tengo dos columnas, en una tengo de a x cantidad de veces, codigos repetidos por ejemplo (R-51), y al lado, tengo codigos que pertenecen al R-51 pero que son diferentes, lo que quiero hacer, es que en otra parte de la hoja me copie una sola vez el codigo que se repite, y al lado que me transponga los valores que le pertenecen a dicho codigo
    por ejemplo: yo tengo

    R-51 1
    R-51 2
    R-51 3
    R-90 4
    R-90 5
    R-90 6
    R-90 7
    L-17 8

    y quiero que me quede lo siguiente

    R-51 1 2 3
    R-90 4 5 6 7
    L-17 8

    Espero haber sido claro!!!!
    Muchas Gracias!!!!

    ResponderBorrar
  10. Tu consulta es más parecida al tema que trate en la nota sobre búsquedas complejas en matrices.
    Suponiendo que tus datos están en los rangos B3:B10 y C3:C10 respectivamente, esta fórmula matricial extrae los elementos correspondientes al código que aparezca en la celda A17

    =TRANSPONER(SI($B$3:$B$10=A17,$C$3:$C$10,""))

    Para usar esta fórmula tienes que seleccionar previamente el rango B17:I17 (8 celdas) y luego introducirla apretando simultáneamente Ctrl+Mayúsculas+Enter

    ResponderBorrar
  11. Excelente Jorge, realmente veo tu formula, y no se en donde le estaba errando, me parece que era en las comillas, si el valos era falso, ahora bien, no se le podra agregar el UNIQUE VALUES, para que todos me queden en las primeras celdas, o se puede hacer con la funcion de sacar celdas vacias?!
    Gracias Querido, sos muy GroCXXXXzoo!
    Max.-

    ResponderBorrar
  12. hola jorge .. por aki otra vez.. me la he pasado todo el dia contigo.. jejeje..
    bueno ya baje el complemento UNIQUEVALUES, me sirve y tengo una matriz
    A B
    1 s-x
    2 s-x
    3 s-a
    4 s-y
    5 s-y
    6 s-a
    tengo la formula
    =INDICE(UNIQUEVALUES($B$13:$B$37);FILAS($8:8))
    para extraer los datos que repiten en unicos.. y me funciona muy bien..
    pero es el caso que necesito incorporar otra condicion que es extraer con respecto a la columna A :solo del 1 al 3
    y del 4 al 6

    o sea, extraer los datos de la columna B con respecto a la columna A
    condiciones desde 1
    hasta 3
    por fis .. necesito tu sabiduria.. iluminame...
    besos

    ResponderBorrar
  13. En tu ejemplo, si buscas desde el 1 al 3 y del 4 al 6, entonces estás buscando en todo el rango. Pero si le idea es buscar en rangos no continuos, lo más sencillo es usar dos fórmulas.

    ResponderBorrar
  14. hola gracias pro responder.. si efectivamente necesito ademas de la busqueda en general hacerlo por separado, ya que los datos de la columna A son dias pero no estan expresados como fechas solo en numeros.. tengo una tabla de 30 dias, en la columna B tengo los codigos; con la formula que te indique busco los datos de los 30 dias en forma general los valores unicos, y asi tambien quiero tener otra tabla en donde mando a busca desde: ejem: 1 hasta ejem. 15 ya que eso es mi hoja de tiempo,
    espero que me halla explicado..
    y espero que me puedas ayudar..
    gracias
    besitos

    ResponderBorrar
  15. Me resulta difícil seguir tus explicaciones. Te sugiero que me envies la consulta por mail privado.

    ResponderBorrar
  16. porque pones fila(5$:5) al final de la formula
    =INDICE(UNIQUEVALUES($A$1:$C$2);FILAS($5:5))

    no entendi

    ResponderBorrar
  17. Como estamos usando la función INDICE, debemos indicar en forma dinámica el número de fila del elemento. En la primer celda de la fórmula FILAS($5:5) da 1; en la segunda fila da 6, ya que la expresión se convierte en FILAS($5:6) y así en adelante. Usamos 5 ya que es la primer fla de la fórmuula.

    ResponderBorrar
  18. hola Jorge
    espero aun puedas ayudarme. tengo una matriz de aproximadamente 30000 numeros aleatorios que van entre el 1 y 99999. pero solo necesito 25 mil numeros unicos y no se como obtenerlos. pense usar el filtro avanzado de valores unicos pero no me sirve en esto. ¿que puedo hacer?
    gracias.

    ResponderBorrar
  19. necesito ayuda tengo una matriz de poco mas de 25 mil numeros aleatorios que van desde 1 al 99999. use la fucnion aleatorio para obtenerlos. pero solo quiero 25 mil numeros unicos, como le puedo hacer para que se mantenga la matriz pero con solo 25 mil numeros aleatorios unicos?
    muchas gracias de antemano a quien me pueda ayudar

    ResponderBorrar
  20. Ephram, te sugiero que veas esta nota en mi blog.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.