miércoles, 20 de marzo de 2013

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: 


lunes, 4 de marzo de 2013

Máximos, Mínimos y Promedios por Columnas


"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.