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: