Lista Desplegable con Rango Dinámico

"Necesito realizar una lista desplegable que vaya incorporando automáticamente los nombres que voy introduciendo en una tabla (pero sin que aparezcan espacios en blanco en dicha lista)".

Para solucionar este problema utilizaremos dos funciones, a saber, DESREF y CONTARA y la herramienta de Validación de Datos. Partimos del siguiente ejemplo:

Si utilizamos directamente la herramienta de Validación y seleccionamos como lista el rango E3:E20 entonces nos aparecerá un desplegable con 13 opciones en blanco:

Para evitar este problema vamos a crear un rango dinámico. Empezamos por crear el nombre del rango de los participantes, esto es, seleccionamos E3:E20 y en el cuadro de nombres (a la izquierda de la barra de fórmulas) escribimos el nombre Listado. A continuación vamos a la ficha Datos / Validación de datos y seleccionamos Lista. En Origen escribimos la fórmula:

=DESREF(E2;1;;CONTARA(listado))

De esta manera, el contenido de la lista desplegable se ajustará estrictamente a las entradas que se produzcan en el rango Listado (E3:E20). Con la función CONTARA calculamos el número de celdas no vacías del rango Listado. Dicho resultado será el argumento Alto de la función DESREF y crecerá o disminuirá en función de que añadamos o eliminemos registros del listado, como se puede ver en las siguientes imágenes: 

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:

Ordenar Automáticamente una Lista con Valores Repetidos

"Tengo una tabla con cifras de ventas de distintas zonas y me gustaría generar otra tabla idéntica que me ordenara automáticamente de mayor a menor dichas zonas en función de las ventas logradas".

Este problema lo resolvimos en el post Ordenar Automáticamente una Lista . Pero surgió una nueva cuestión:

¿Cómo resolvemos el orden cuando hay cifras de ventas repetidas? 

La solución es utilizar la función JERARQUIA para establecer un orden dentro de los valores repetidos, como sugiere Jorge Dunkelman en su artículo Función JERARQUIA en Excel. A saber:

 Lo primero es crear el nombre Zona para el rango B3:B14 y el nombre Ventas para el rango C3:C14. En la celda E3 escribimos la fórmula: =JERARQUIA(C3;ventas)  y copiamos hasta E14. De esta manera estamos estableciendo el orden que ocupa cada cifra de ventas. El problema es que hay cifras que se repiten y que debemos "reordenar":

Para ello nos situamos en la celda F3 y escribimos la siguiente fórmula:

=JERARQUIA(C3;Ventas)+CONTAR.SI($C$3:C3;C3)-1

El resultado de dicha es fórmula es el siguiente:

Utilizando la columna F (Jerarquia2) ya podemos aplicar nuestra solución del caso original y resolver el problema. Preparamos el siguiente cuadro (tal y como hicimos en el caso original):

Nos ponemos en la celda I3 y escribimos la siguiente fórmula:

=INDICE(Zona;COINCIDIR($H3;$F$3:$F$14;0))

Nos ponemos en la celda J3 y escribimos la siguiente fórmula:

=INDICE(Ventas;COINCIDIR($H3;$F$3:$F$14;0))

Finalmente seleccionamos el rango I3:J3 y copiamos hacia abajo hasta I14:J14 , y trabajo terminado:

Evitar Duplicados en Varias Columnas

Descargar el Archivo

"Tengo una hoja en la que cada 4 columnas tengo que introducir los códigos de recibos (en total en tres columnas). Estos códigos no pueden repetirse y, en consecuencia, necesito que no permita introducir los códigos que ya se hayan introducido previamente".

Este problema es una derivada un poco más complicada del que ya traté en su día en el artículo "Evitar Datos Repetidos con Validación de Datos Personalizada". En dicho post veíamos como evitar introducir dos veces el mismo código pero dentro de una misma columna. En este nuevo caso, además de comprobar cada columna individualmente, debemos cotejar las distintas columnas entre si para cerciorarnos de que no estamos introduciendo datos repetidos.

 Partimos del siguiente ejemplo:

Y lo que queremos conseguir es que cuando introduzcamos un código que ya se haya utilizado, como se ve en la siguiente imagen, nos aparezca un mensaje de error que no nos permita utilizar dicho código:

Vamos a necesitar generar una matriz de comprobaciones para resolver este problema, matriz que situaré en el rango M2:R10. Antes de empezar a formular vamos a crear tres nombres. Para ello seleccionamos el rango discontinuo A1:A10 E1:E10 y I1:I10 y en la ficha Fórmulas pinchamos en Crear desde la selección. En la ventana que se abre seleccionamos Crear nombres a partir de los valores de la: Fila superior. De esta manera hemos creado los nombres Codigo1, Codigo2 y Codigo3 que se corresponden a cada una de las columnas de la entrada de datos.

Vamos a la celda M2 y escribimos la fórmula =CONTAR.SI(codigo1;A2)  y copiamos la fórmula hasta M10. De esta manera estamos contando cuántas veces está cada código introducido en la primera columna.

Hacemos lo mismo para el resto. Nos situamos en N2 y escribimos: =CONTAR.SI(codigo2;E2)  y copiamos hasta N10. Finalmente nos situamos en O2 y escribimos: =CONTAR.SI(codigo3;I2)  y copiamos hasta O10.

Procedemos ahora a comprobar si hay duplicados de unas columnas en otras. Para ello nos situamos en la celda P2 y escribimos la fórmula: =SI(ESERROR(BUSCARV(A2;codigo2;1;));0;1)   y la copiamos hasta la celda P10. Esta fórmula verifica si algún elemento de la primera columna se encuentra en la segunda columna. Si no se encuentra generará un error tipo N/A que lo convertimos en valor cero con el uso del condicional de la fórmula. En caso contrario, es decir, si está repetido, entonces el condicional le otorga un valor 1, como se puede ver al final de la fórmula.
Hacemos ahora la misma comprobación pero en la tercera columna. Nos situamos en Q2 y escribimos: =SI(ESERROR(BUSCARV(A2;codigo3;1;));0;1)   Finalmente comprobamos si hay valores repetidos de la columna 2 en la columna 3 con la siguiente fórmula en la celda R2: =SI(ESERROR(BUSCARV(E2;codigo3;1;));0;1)

El resultado obtenido es la siguiente matriz de comprobación:

Sólo nos queda analizar que resultados podemos permitir en esta matriz que garanticen que no se ha introducido el mismo código 2 veces:
En el caso de las columnas M, N, O los resultados permitidos serán cero o uno (cero en el caso de que todavía no hayamos introducido un código y uno en el caso de que lo hayamos introducido y no esté repetido (en cuyo caso sumaría 2).
En el caso de las columnas P, Q, R  el resultado permitido será cero, ya que si aparece un valor uno, por ejemplo, significará que hay una repetición de código de una columna en otra.

Seleccionamos el rango discontinuo A1:A10 E1:E10 y I1:I10 y vamos a la ficha Datos y pinchamos en Validación e datos. Dentro de la ventana que se abre elegimos Permitir Personalizada. En el recuadro Fórmula escribimos la fórmula que cumple con las condiciones analizadas, a saber:

=SUMAR.SI(M2:M10;">1")+SUMAR.SI(N2:N10;">1")+SUMAR.SI(O2:O10;">1")+
SUMA(P2:P10;Q2:Q10;R2:R10)=0

Y problema resuelto!