sábado, 2 de febrero de 2013

Lista de Valores Únicos (con Fórmulas)

"Tengo un listado de más de 500 registros donde uno de los campos es un código. Estos códigos están repetidos en los distintos registros y necesito generar una lista utilizando fórmulas que me muestre los códigos únicos que existen". 

Este problema ya lo resolvimos haciendo uso de filtros avanzados y tablas dinámicas en artículos anteriores. Vamos a ver ahora cómo solucionarlo mediante fórmulas. A continuación muestro de dónde partimos y a dónde queremos llegar: 
Empezamos generando una columna de procesos en I3 para obtener los valores únicos. Para ello nos situamos en dicha celda y escribimos la siguiente fórmula y la copiamos hasta la celda I27:

=SI(N(CONTAR.SI($C$3:C3;C3)=1);C3;"")

Desgranemos esta fórmula:  CONTAR.SI($C$3:C3;C3)=1 verifica cada valor empezando por C3, y devuelve el valor VERDADERO cuando el código aparece por primera vez dentro del "rango dinámico" que generamos ($C$3:C3). Para convertir en 1 y 0 los valores VERDADERO ó FALSO  que devuelve esta parte de la fórmula utilizamos la función N, ya vista en otros artículos de este blog. Finalmente hacemos uso del condicional para transformar los valores 1 en el código que le corresponde y los valores 0 convertirlos en "". El resultado es el siguiente:
A continuación nos situamos en la celda H3 y escribimos la fórmula:  =SI(I3="";"";B3)
De esta manera colocamos el número que le corresponde en el listado original a cada código. Obtenemos lo siguiente:
Procedemos ahora a ordenar los datos para dejar los valores únicos al principio de la lista y los valores "en blanco" al final. Para ello nos situamos en la celda H3 y escribimos la siguiente fórmula que debemos copiar hasta H27:

=SI.ERROR(K.ESIMO.MENOR($H$3:$H$27;B3);"")

K.ESIMO.MENOR ordena la lista de menor a mayor. En las celdas que tengamos "" nos devolverá el error #¡NUM!. Para evitar este mensaje de error y conseguir que la celda se quede en blanco, usamos la función SI.ERROR (disponible a partir de la versión 2010 de excel). Esta función ejecuta el primer argumento, esto es, K.ESIMO.MENOR($H$3:$H$27;B3) y si el resultado de esta parte de la fórmula es un error entonces aplica el segundo argumento, es decir, "". Si no es un error simplemente devuelve el resultado del primer argumento. Obtenemos lo siguiente:
Tan sólo nos queda ahora buscar los códigos correspondientes a dichos números y problema resuelto. Nos situamos en la celda F3 y escribimos la siguiente fórmula que copiamos hasta la celda F27:

=SI.ERROR(BUSCARV(E3;$B$3:$C$27;2;FALSO);"")
Para concluir el modelo, podemos hacer que aparezcan bordes en las celdas con valores únicos de manera automática utilizando Formato Condicional. A saber:

1. Seleccionamos el rango E3:F27 y vamos a Formato Condicional / Nueva regla.
2. Seleccionamos "Utilice una fórmula que determine las celdas para aplicar formato".
3. En "Editar una descripción de regla" escribimos la siguiente: =E3<>""
4. Pulsamos el botón Formato y marcamos los bordes de la celda que queremos que aparezca (u otro formato que deseemos).
5. Terminamos pulsando Aplicar y Aceptar. Y trabajo concluido:

14 comentarios:

  1. Otra opción algo más rápida y más 'casera', al menos siempre que los datos esten como en el ejemplo, sería:

    1- Copiar todo el rango de códigos en F2 (pegando valores).
    2- Usar la opción quitar duplicados (pestaña datos)
    3- Copiar y pegar todo el rango de Nº en C1, por ejemplo.
    4 - En E2, utilizar la función BUSCARV. En el ejemplo quedaría =BUSCARV(F3;$B$3:$C$27;2;FALSO). Y arrastrarla hacia abajo hasta el último valor de la columna F.

    Saludos,
    Javi

    ResponderEliminar
    Respuestas
    1. Muchas gracias Javi por tu aportación.

      Eliminar
  2. Hola Kiko, interesantisimo tu blog, pero aca tengo la pregunta: Supongamos que tengo dos listas en distintas hojas; una hoja contiene una columna de datos A y la segunda hoja tiene la Columna de datos A + una columna de indices (ejm: codigo de alumno, nombre de alumno), la idea es que en una tercera hoja se presenten la lista de alumnos "no duplicados" con sus respectivos codigos... he venido tratando de combinar algunas de las soluciones que has propuesto, pero no logro dar con la solucion.
    Espero puedas darme una mano con este asunto que seguramente te lo han pedido un millon de veces.
    Muchas gracias!

    ResponderEliminar
    Respuestas
    1. Por favor mándame un mail a kiko.rial@gmail con un ejemplo en excel para que pueda entender exactamente lo que quieres resolver. Gracias y un saludo.

      Eliminar
  3. Hola Kico, leo con mucha atención tus comentarios en la REBOTICA y la verdad que me ayudan bastante.
    Si no te es molestia te comento un pequeño problema con una formula que me funciona pero no termino de entender una cosa
    Te explico, la formula es la siguiente:

    =INDICE($F$660:$F$698;ALEATORIO.ENTRE(1;CONTARA($F$660:$F$698)))

    Es referente a formulas aleatorias, te marco en negrita y rojo el numero, que es el 1, que viene luego CONTARA, la pregunta
    Es que ocurre si en vez de poner 1 le pongo 2-3-4-5 o x, cuando me lo recalcula no le veo la diferencia, pero ¿cambia algo?

    Muchas gracias por todo.

    Recibe un cordial saludo

    Lucas Deyá

    ResponderEliminar
    Respuestas
    1. Hola Lucas. El valor 1 dentro de la función ALEATORIO.ENTRE es el valor inferior desde el que genera un número aleatorio. Es decir, si escribimos =ALEATORIO.ENTRE(1;100) generará un valor aleatorio entre 1 y 100. Si escribes 2, 3 ó 4 generará un aleatorio desde dicho valor.

      Eliminar
    2. Muchas Gracias Kiko, me ha despejado todas las dudas.

      Cordiales saludos

      Lucas

      Eliminar
  4. Estimado, ud sabe como es posible obtener valores unicos por estrato?

    ResponderEliminar
  5. Necesito que me concretes más tu pregunta, por favor.

    ResponderEliminar
  6. Tengo las siguientes 4 variables:
    Me explico un poco mejor:

    Numero de instalacion: van del 1 al 100.000, y corresponden a una instalacion productiva

    Número de negocio: van del 1 al 200, dependiendo del número del negocio, cada instalacion productiva está ligada a un negocio

    Nombre de la empresa ligada a la instalacion

    Rut de la empresa ligada a la instalacion.

    1) Un primer ejercicio fue dejar separada solo las empresas, y eso lo hice con lo explicado más arriba separando por rut

    2) Ahora, necesito obtener dentro de cada negocio, el total de empresas distintas que hay. Entonces, quisiera saber si hay alguna forma de obtener los valores unicos pero por cada uno de los 200 estratos.

    Gracias Kiko

    ResponderEliminar
    Respuestas
    1. Por favor, mándame un ejemplo a kiko.rial@gmail.com e intento ayudarte. Gracias

      Eliminar
  7. Hola
    Kiko, necesito encontrar valores únicos en una tabla y explico lo de únicos
    Un valor es UNICO sin parece una y solo una vez en el rango seleccionado.
    Si un valor aparece N VECES no es único y no se considera, debe borrarse

    Todo lo que he visto, borra las N-1 veces que se rpeite y mantiene 1. Eso no es lo que necesito

    A ver si puedes ayudarme o dar alguna idea.

    Saludos y gracias!!



    ResponderEliminar
    Respuestas
    1. Te contesto en mi post de hoy. Un saludo

      Eliminar
  8. Hola Kiko, buenísimo tu blog, me ha salvado de mucho trabajo, estoy generando un calendario de publicaciones en el que cada columna es un día de año y en las filas se coloca la publicación hay días en las que tenemos 3 o 5 publicaciones diferentes y me gustaría enlistar estas tareas en una columna para llevar un mejor control, y de ser posible que se automatizara el numero de filas necesarias... se te ocurre algo?

    Saludos y gracias!

    ResponderEliminar