Valores Únicos No Repetidos

"Necesito encontrar valores únicos en una tabla (entendiendo por únicos aquellos valores que aparecen una y sólo una vez en el listado) y obtener un nuevo listado donde sólo se consideren los valores nunca repetidos (y que el resto de valores desaparezcan)".

Partimos del siguiente ejemplo:

Nos situamos en la celda D4 y escribimos la siguiente fórmula:
=SI(CONTAR.SI($B$4:$B$18;B4)>1;"";B4)  y la copiamos hasta D18:

Si queremos que excel añada un borde a las celdas que contienen números, podemos hacer uso del Formato Condicional. Para ello seleccionamos D4:D18 y vamos a Formato Condicional y seguimos los pasos que se muestran la siguiente imagen:

Tras escribir la fórmula, pulsamos el botón Formato... y en Bordes elegimos Contorno en color, por ejemplo, granate:

Pulsamos Aceptar y problema resuelto:

Especificar Tipo de Formato de una Celda

"Regularmente me envían un listado con diferentes entradas en una columna y necesito detectar, por medio de fórmulas, cuáles de dichas entradas son fechas".

Para solucionar este problema haremos uso de la función CELDA. Partimos del siguiente ejemplo:

La función CELDA devuelve información acerca del formato, la ubicación o el contenido de una celda. La sintaxis de esta función es CELDA( tipo_de_info;referencia), tal y como muestra la ayuda de excel, y tiene los siguientes argumentos:

tipo_de_info: Es un valor de texto que especifica el tipo de información de la celda que se desea obtener. La siguiente lista muestra los posibles valores del argumento de tipo_de_info y los correspondientes resultados:

tipo_de_info	Devuelve
"DIRECCION"	la referencia, en forma de texto, de la primera celda del argumento ref.
"COLUMNA"	El número de columna de la celda del argumento ref.
"COLOR"	Valor 1 si la celda tiene formato de color para los valores negativos; de lo contrario, devuelve 0 (cero).
"CONTENIDO"	Valor de la celda superior izquierda de la referencia, no una fórmula.
"ARCHIVO"	Nombre del archivo (incluida la ruta de acceso completa) que contiene la referencia, en forma de texto. Devuelve texto vacío ("") si todavía no se ha guardado la hoja de cálculo que contiene la referencia.
"FORMATO"	Un valor de texto correspondiente al formato numérico de la celda. Los valores de texto para los distintos formatos se muestran en la siguiente tabla. Si la celda tiene formato de color para los números negativos, devuelve "-" al final del valor de texto. Si la celda está definida para mostrar todos los valores o los valores positivos entre paréntesis, devuelve "()" al final del valor de texto.
"PARENTESIS"	Valor 1 si la celda tiene formato con paréntesis para los valores positivos o para todos los valores; de lo contrario, devuelve 0 (cero).
"PREFIJO"	Un valor de texto que corresponde al "prefijo de rótulo" de la celda. Devuelve un apóstrofo (') si la celda contiene texto alineado a la izquierda, comillas (") si la celda contiene texto alineado a la derecha, un acento circunflejo (^) si el texto de la celda está centrado, una barra inversa (\) si la celda contiene texto con alineación de relleno y devolverá texto vacío ("") si la celda contiene otro valor.
"PROTEGER"	Valor 0 (cero) si la celda no está bloqueada; de lo contrario, devuelve 1 si la celda está bloqueada.
"FILA"	El número de fila de la celda del argumento ref.
"TIPO"	Un valor de texto que corresponde al tipo de datos de la celda. Devolverá "b" (para blanco) si la celda está vacía, "r" (para rótulo) si la celda contiene una constante de texto y "v" (para valor) si la celda contiene otro valor.
"ANCHO"	El ancho de columna de la celda redondeado a un entero. Cada unidad del ancho de columna es igual al ancho de un carácter en el tamaño de fuente predeterminado.

referencia: (argumento opcional) La celda sobre la que desea información. Si se omite, se devuelve la información especificada en el argumento tipo_de_info para la última celda cambiada. Si el argumento de referencia es un rango de celdas, la función CELDA devuelve la información sólo para la celda superior izquierda del rango.

He destacado en naranja "formato" porque es el tipo_de_info con el que vamos a trabajar. Para ello nos situamos por ejemplo en la celda E3 y escribimos la fórmula:

=CELDA("formato";B3)   y copiamos hasta E9:

Como se puede ver, en aquellas celdas que tenemos formato de fecha obtenemos la referencia D1. Haciendo uso de la ayuda de Excel, la siguiente lista describe los valores de texto que devuelve la función CELDA cuando el argumento tipo_de_info es "formato":

Si el formato de Excel es	La función CELDA devuelve
Estándar	"G"
0	"F0"
#.##0	".0"
0,00	"F2"
#.##0,00	".2"
$#,##0_);($#,##0)	"C0"
$#.##0;(rojo)-$#.##0	"-M0"
$#.##0,00_);($#.##0,00)	"C2"
$#.##0,00;(rojo)-$#.##0,00	"-M2"
0%	"P0"
0,00%	"P2"
0,00E+00	"C2"
# ?/? o # ??/??	"G"
d/m/aa o d/m/aa h:mm o dd/mm/aa	"D4"
d-mmm-aa o dd-mm-aa	"D1"
d-mmm	"D2"
mmm-aa	"D3"
mm/dd	"D5"
h:mm a.m./p.m.	"D7"
h:mm:ss a.m./p.m.	"D6"
h:mm	"D9"
h:mm:ss	"D8"

Como se puede comprobar, todos los formatos de fecha comienzan por la letra D. Por ello hacemos ahora la siguiente fórmula en la celda D3:

=SI(IZQUIERDA(E3;1)="D";"Sí";"No")   y copiamos hasta la celda D9:

Evidentemente, podríamos resolver el modelo con una única fórmula en D3 que nos evitaría la columna E, a saber:

=SI(IZQUIERDA(CELDA("formato";B3);1)="D";"Sí";"No")

IMPORTANTE: Si el argumento tipo_de_info de la función CELDA es "formato", como en nuestro caso, y procedemos a asignar un formato diferente al inicial a la celda a la que se hace referencia, es necesario volver a calcular la hoja de cálculo (o pulsar F9) para poder actualizar los resultados de dicha función.

Si queremos destacar en otro color aquellas entradas que son fechas entonces tenemos que hacer uso de la herramienta de Formato condicional. Para ello seleccionamos el rango B3:B9 y vamos a Formato condicional y formulamos como se detalla en la imagen a continuación:

Transformar una Matriz a Sistema de Numeración Binario

"Necesito transformar una matriz numérica en una matriz binaria (con valores 0 ó 1), es decir, que los valores que superen un cierto número se conviertan en uno y el resto en cero".

El pasado lunes 30 de junio les prometí a mis alumnos del Master in Management del IE Business School que les dedicaría el próximo post que publicase. Vaya pues por delante la dedicatoria y mi agradecimiento a una clase maravillosa!

Vamos a resolver el problema planteado con una sencilla fórmula utilizando la función lógica SI y acabando con un Ctrl + Enter. Partimos del siguiente ejemplo:

Se trata de una matriz de 8x10 (8 columnas y 10 filas) y lo que buscamos es transformar los números que aparecen en 1 y 0. Para ello necesitamos un criterio, es decir, un valor, por ejemplo, a partir del cuál los valores inferiores se conviertan en uno y, por contra, los valores superiores se conviertan en cero. Dicho criterio lo tenemos en la celda C2. Lo que hacemos a continuación es seleccionar una matriz de la misma dimensión, es decir, seleccionar un rango de 8 columnas por 10 filas. Lo hacemos en B19:I28

Con dicho rango seleccionado escribimos la fórmula:  =SI(B5<$C$2;1;0) y finalizamos pulsando Ctrl + Enter. De esta manera rellenamos de una sola vez toda la matriz resultante:

Si además queremos que, por ejemplo, los 1 se destaquen en negrita y cambie el color de fondo, podemos aplicar Formato condicional. Para ello dentro de la ficha Inicio seleccionamos Formato condicional. En el menú que se abre seleccionamos Resaltar reglas de celdas y, en el nuevo menú, Es igual a...  Aparecerá la siguiente ventana:

Escribimos un 1, dejamos el formato que aparece (si queremos aplicar cualquier otro abrimos la lista y marcamos Personalizado) y pulsamos Aceptar. El resultado será el deseado:

Resaltar Duplicados Concatenados

"Necesito una fórmula que localice códigos duplicados en la columna B y, si los encuentra, que los coloree sólo si en la columna C los nombres coinciden también, pero no puedo añadir columnas adicionales en la hoja."

Necesitamos concatenar la columna B y la C para comprobar si hay entradas duplicadas y, en tal caso, resaltar dichas celdas pero sin utilizar columnas adicionales en la hoja. Para ello formularemos directamente en la herramienta de Formato Condicional. Lo solucionaremos con la ayuda de la función SUMAPRODUCTO. Empezaremos formulando en la hoja para que se entienda mejor y luego pasaremos dicha formulación a la herramienta. Partimos del ejemplo de la primera imagen y queremos conseguir el resultado de la segunda imagen:

Para ello nos situamos en la celda E3 y escribimos la siguiente fórmula que copiaremos hasta la celda E10: 

SUMAPRODUCTO es una función que suma el producto de dos rangos (rangos que deben tener la misma dimensión). Si la fórmula fuese =SUMAPRODUCTO(B3:B10;C3:C10) excel ejecutaría (B3*C3)+(B4*C4)+(B5*C5)... Al introducir un criterio en la función (en nuestro caso el criterio es que el primer rango sea =$B3 y que el segundo sea =$C3) , excel genera una matriz de resultados tipo VERDADERO/FALSO que al multiplicarlo por 1 se convierte en una matriz del tipo 1/0. De esta manera estamos consiguiendo valores 1 para el rango B3:B10 en aquellos casos en los que un código esté repetido y valores cero para los que no lo estén.Y lo mismo en el rango C3:C10. Al combinar ambos resultados obtendremos valores mayores de 1 para aquellas combinaciones repetidas. Para aplicar esta formulación directamente en la herramienta de Formato Condicional debemos especificar una condición que, nuevamente, genere un resultado tipo VERDADERO/FALSO. Es por ello que introducimos el >1 del final de la fórmula. El resultado es el siguiente:

Como no podemos utilizar columnas adicionales en la hoja, seleccionamos ahora el rango B3:C10 y vamos a Formato condicional. Elegimos la opción de introducir una fórmula y escribimos (o copiamos y pegamos directamente) nuestra fórmula. En el botón Formato... damos la apariencia de relleno de color que deseemos y aceptamos:

Evidentemente, procedemos a borrar la formulación realizada en la columna E. Para finalizar correctamente el modelo debemos considerar que si dejamos celdas en blanco excel las rellenará con el formato que le hayamos asignado:

  Para evitar ésto, ampliamos la fórmula de la siguiente manera:

Destacar Datos Repetidos más de n-veces

"En una lista de datos, los cuales se repiten varias veces, necesito resaltar aquellos datos que se repitan mas de dos veces, pero que las dos primeras veces que aparezcan no se marquen". 

Partimos del siguiente ejemplo:

 Y queremos conseguir lo siguiente:

Para ello nos seleccionamos el rango B5:B24. Vamos a Formato Condicional y elegimos "Utilice una fórmula que determine las celdas para aplicar formato". En "Editar una descripción de regla" escribimos la siguiente fórmula:

=CONTAR.SI($B$5:B5;B5)>$C$2

Pulsamos el botón Formato... y seleccionamos el aspecto que queremos que tomen las celdas a destacar. Acabamos pulsando Aceptar y trabajo terminado.

Lista de Valores no Repetidos

"Necesito comparar dos columnas y crear una tercera columna en la que aparezcan los datos que no están repetidos. Es decir, si la columna A contiene números del 1 al 12 y la columna B contiene números del 1 al 10, necesito que en la columna C me aparezcan el 11 y el 12, ya que son los únicos dos valores que no están repetidos".

Partimos del siguiente ejemplo:

Vamos a generar una lista con los valores que no estén repetidos. Por otro lado, vamos a ordenar dichos valores de mayor a menor y, finalmente, vamos a resaltar con color de relleno cuáles son estos valores. Para ello trabajaremos con las funciones CONTAR.SI, SI.ERROR, SI, y K.ESIMO.MAYOR, y con la herramienta Formato Condicional.

Lo primero que hacemos es darle nombre al rango B3:C14 para lo que seleccionamos dicho rango y hacemos clic en el cuadro de nombres (a la izquierda de la barra de fórmulas) y escribimos Valores y pulsamos Enter. A continuación preparamos el rango donde aparecerán los valores no repetidos. Habilitamos 24 filas ya que en nuestro ejemplo partimos de 2 columnas con 12 datos cada una y, por lo tanto, podríamos llegar a tener 24 valores no repetidos. Le añadimos a la derecha un número de orden que utilizaremos posteriormente:

Nos situamos en la celda G3 y escribimos la fórmula:

=SI(CONTAR.SI(valores;B3)=1;B3;"") y la copiamos hasta la celda G14. 

De esta manera lo que estamos haciendo es pedirle que cuente en el rango llamado Valores cuántas veces se repite cada uno de los valores de la columna B. Si se repite sólo una vez que lo escriba y si se repite más veces que no ponga nada ("").

Nos situamos ahora en G15 y escribimos una fórmula casi idéntica:

=SI(CONTAR.SI(valores;C3)=1;C3;"") y la copiamos hasta la celda G26. Estamos haciendo lo mismo que antes pero ahora con los valores de la columna C. El resultado será el siguiente:

Como puede comprobar, ya hemos generado la lista de valores no repetidos. Para ordenarla de mayor a menor nos situamos en la celda E3 y escribimos:

=SI.ERROR(K.ESIMO.MAYOR($G$3:$G$26;H3);"")  y copiamos hasta la celda E26.

Con la función K.ESIMO.MAYOR ordenamos los valores de mayor a menor. En los valores que se encuentre en blanco nos devolverá el error N#A y por eso utilizamos la función SI.ERROR para que cuando aparezca dicho error simplemente lo mantenga como celda en blanco (para ser más correctos celda con ""). Aplicamos formato condicional a aquellas celdas distintas de "" (puede consultar cómo hacerlo en el post Lista de valores Únicos): 

Finalmente vamos a destacar en nuestra entrada de datos aquellos valores que no están repetidos. Seleccionamos el rango B3:C14 y vamos a Formato Condicional. Seleccionamos la opción Utilice una fórmula que determine las celdas para aplicar formato  y escribimos la fórmula  =CONTAR.SI($B$3:$C$14;B3)=1  Pulsamos el botón formato y elegimos que colores u otros formatos deseamos utilizar y acabamos pulsando Aceptar. El resultado final es el que se puede observar a continuación:

Máximos, Mínimos y Promedios por Columnas

Descargar Archivo

"He leído el post de "Resaltar Máximos, Mínimo  y Promedios con Formato Condicional" y me gustaría saber si se puede realizar el mismo cálculo pero por columnas".

La solución es muy sencilla. Partimos del siguiente ejemplo:

Nos situamos en la celda C3 y escribimos:

=MAX(C$8:C$19)  y copiamos hasta la celda G3

En C4 escribimos:

=MIN(C$8:C$19)    y copiamos hasta la celda G4

En C5 escribimos:

=PROMEDIO(C$8:C$19)   y copiamos hasta la celda G5

Seleccionamos ahora el rango C8:G19 y vamos a Formato Condicional / Utilice una fórmula que determine las celdas para aplicar formato. En Editar una descripción de regla escribimos la siguiente fórmula:

=C8=C$3  y pulsamos el botón Formato. Ahora seleccionamos el Relleno de color naranja y la fuente negrita (por ejemplo) y aceptamos.

Realizamos la misma operación de nuevo pero escribiendo ahora la fórmula:

=C8=C$4  y en el Formato seleccionamos el color verde y fuente negrita.

Finalmente vamos a resaltar aquellas zonas cuyo promedio se encuentre por encima del promedio total. Para ello seleccionamos el rango C7:G7 y vamos a Formato Condicional / Utilice una fórmula que determine las celdas para aplicar formato. En Editar una descripción de regla escribimos la siguiente fórmula:

=C$5>=PROMEDIO($C$5:$G$5)   y en el botón Formato seleccionamos, por ejemplo, relleno rosa y fuente negrita. 

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:

Resaltar Duplicados por Colores

"Necesitaría destacar con distintos colores los valores repetidos dentro de una lista".

Supongamos que partimos del siguiente ejemplo:

Lo que buscamos es que un determinado valor, por ejemplo el 9, que se repite en varias ocasiones, se coloree del mismo color en aquellas celdas en las que se repite. Para ello vamos a aplicar una solución bastante sencilla utilizando el Formato Condicional.

Para ello seleccionamos el rango B3:B18 y vamos a Formato Condicional / Resaltar reglas de celdas / Duplicar valores:

En la ventana que se abre elegimos Único y Formato personalizado (dentro de las dos listas desplegables que aparecen en la ventana). Finalmente elegimos rellenar con el color blanco y aceptamos. De esta manera lo que hemos hecho es que los valores únicos (los no duplicados) mantengan el fondo blanco en nuestra hoja.

A continuación, y manteniendo seleccionado el rango B3:B18, volvemos a Formato Condicional y abrimos la opción Escalas de color y elegimos, por ejemplo, la primera opción. El resultado será el mostrado en la siguiente imagen donde, como se puede observar, cada valor repetido tiene asignado un color determinado, lo que facilita su localización visual:

Asignación Aleatoria por Filas

Descargar Archivo

"Quiero realizar la asignación aleatoria de un número de anuncios determinado de distintas empresas en una parrilla dispuesta por bloques como aparece en la imagen:

De tal manera que la suma de los 5 bloques de cada anunciante debe resultar el número de anuncios contratados por cada empresa".

Pues nos ponemos manos a la obra. Empezamos creando la correspondiente tabla en nuestra hoja, a saber:

A continuación seleccionamos el rango E17:I23 y con dicho rango seleccionado escribimos la fórmula:

=ALEATORIO() y pulsamos Ctrl + Enter (así rellenamos todo el rango de una sola vez):

Nos situamos ahora en E5. Para que se entienda mejor vamos a realizar una primera fórmula que no es la definitiva. Escribimos (en E5) la fórmula:

=JERARQUIA(E17;$E17:$I17)

Copiamos dicha fórmula para toda la tabla (rango E5:I11). De esta manera conseguimos ordenar del 1 al 5 los resultados aleatoriamente obtenidos:

Ahora ya sólo nos queda aprovechar aquellos valores de orden que sean igual o inferiores al número de anuncios contratados por cada empresa. Por ejemplo, para el Balneario de Mondariz, que ha contratado 3, nos interesarán los valores 1, 2 y 3. A partir de aquí utilizaremos el formato condicional y la función SI para dar el formato final. Nos situamos nuevamente en la celda E5 y escribimos la fórmula:

=SI(JERARQUIA(E17;$E17:$I17)>$C5;"";1)

Nuevamente copiamos esta fórmula para el rango E5:I11. Al añadir el condicional si el número aleatorio es superior al número de anuncios, excel dejará la celda correspondiente al bloque en blanco. Pero si el número aleatorio obtenido es igual o inferior al número de anuncios contratados entonces escribirá un 1 en la celda del bloque correspondiente:

Ya sólo queda aplicar formato condicional para rematar la tarea. Seleccionamos el rango E5:I11. Dentro de la ficha Inicio vamos al módulo de Estilos y pulsamos el icono de Formato Condicional. De la lista que se abre seleccionamos nueva regla. En la ventana que se abre seleccionamos Utilice una fórmula que determine las celdas para aplicar formato (la última opción de la lista). Nos situamos en Dar formato a los valores donde esta fórmula sea verdadera y escribimos la siguiente fórmula: =1

Acabamos pulsando el Botón Formato. Ahora seleccionaremos el formato con el que queremos que excel resalte los bloques en los que sí hay anuncio. En nuestro ejemplo le pediremos relleno azul y color de fuente blanco y negrita. Aceptamos y...

Cada vez que se recalcule la hoja o que pulsemos la tecla F9 excel generará nuevos números aleatorios y, en consecuencia, un nuevo mapa como se puede ver a continuación en varios ejemplos:

Evidentemente también podemos modificar el número de anuncios contratados por cada empresa y el modelo seguirá funcionando correctamente (obviamente con un máximo, en este ejemplo, de 5 anuncios por empresa).