Listas ordenadas en validación de datos de Excel

La situación es la siguiente: en una hoja de Excel tenemos una lista que nos sirve como base para crear una lista desplegable con validación de datos. Esta lista cambia de tanto en tanto. Si queremos mantener la lista actualizada sin necesidad de realizar cambios en forma manual, definimos un rango dinámico como ya hemos mostrado en el pasado.
Por comodidad queremos que la lista desplegable aparezca ordenada alfabéticamente. El problema reside en que a lista que nos sirve de base no sigue ningún orden preestablecido. Una posibilidad, obviamente, es ordenar la lista manualmente cada vez que ésta cambie. Pero, como ya habrán supuesto, queremos que esto suceda en forma automática.
Tenemos dos posibilidades: programar un evento o utilizar fórmulas y columnas auxiliares.
Empezaremos por la segunda opción. Supongamos esta lista (la misma que usamos en la nota sobre cómo agregar valores únicos en la lista desplegable de validación de datos)



Definimos un nombre con un rango dinámico



y creamos una lista desplegable con validación de datos



Al abrir la lista desplegable, veremos los miembros de la lista en el mismo orden que figuran en el rango original



Para ordenarlos con fórmulas empezamos por crear una columna auxiliar, donde le daremos a cada miembro un número de orden con la técnica que mostramos en la nota sobre cómo ordenar texto con fórmula en Excel. Creamos la columna auxiliar "No. de orden" y usamos esta fórmula:

=CONTAR.SI(frutas,"<="&A2)

Ahora creamos una segunda columna auxiliar, donde ordenamos la lista usando las funciones INDICE y COINCIDIR

=INDICE(frutas,COINCIDIR(FILA()-1,orden,0))



Hemos creado un segundo nombre, "orden", que es también un rango dinámico que contiene los números de orden de la columna auxiliar. En la fórmula que usamos en la columna auxiliar "Lista ordenada", usamos la función FILA para obtener el número de orden deseado sin necesidad de cambiarlo manualmente en cada celda.

Todo lo que nos queda por hacer ahora es crear una nombre que contenga el rango de los valores ordenados, es decir, de la columna "Lista ordenada"



Ahora reemplazamos el nombre "Frutas" en la definición de validación de datos, por el nombre "lista_ordenada"



La lista desplegable aparecerá ahora ordenada alfabéticamente



Podemos hacer lo mismo sin columnas auxiliares, programando un evento. Este código hará que con cada cambio nuestra lista se reordene alfabéticamente.

En el módulo Vba de la hoja correspondiente (en nuestro caso, la hoja "evento"), ponemos este código



Como pueden ver, usamos el nombre que define el rango dinámico en nuestro código. La sentencia On error resume next, nos permite borrar todas la lista en la hoja sin que aparezca un mensaje de error.

Ahora al agregar un nuevo valor, por ejemplo "Melón", este aparecerá automáticamente en el lugar deseado.



El archivo con el ejemplo se puede descargar aquí.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Suma y promedio de los valores mayores de una lista.

Supongamos esta lista de personas (Pedro, Rubén y Marcos) con valores arbitrarios para los meses de enero hasta octubre

Definimos dos nombres, uno que contiene el rango A2:A31 (Personas) y otro que contiene el rango C2:C30 (Valores)



Ahora podemos calcular con facilidad el total y el promedio de los valores para cada una de las personas

La función para SUMA es

=SUMAR.SI(personas;$E2;valores)

y para PROMEDIO

=SUMAPRODUCTO((personas=E2)*valores)/CONTAR.SI(personas;E2)

Para el promedio también pueden usar esta fórmula matricial:

={PROMEDIO(SI(((personas=E2)*valores)<>0;valores;FALSO))}

Un lector me consultaba hace unos días como calcular la suma (o el promedio) de los cinco mayores valores de cada persona.

Para esta tarea usamos esta fórmula matricial, que a continuación explicaré


={SUMA(K.ESIMO.MAYOR(((personas=$E10)*valores);FILA(INDIRECTO("1:"&$G$6))))}

La clave de esta fórmula es la función K.ESIMO.MAYOR. Esta función tiene dos argumentos: una lista de valores y el número de orden del valor que queremos obtener. Al usar esta función en una fórmula matricial obtenemos una matriz de valores de acuerdo al criterio que fijamos con la función.
La expresión (personas=$E10)*valores) genera una matriz con los valores del rango "valores" que corresponden a Pedro o cero si corresponde a un nombre distinto.
Para indicar que queremos, por ejemplo, los cinco valores mayores, tenemos que poner como segundo argumento de K.ESIMO.MAYOR una matriz como {1,2,3,4,5}. En lugar de esto usamos FILA con INDIRECTO, poniendo en la celda G6 la cota superior de nuestra matriz. En nuestro ejemplo

FILA(INDIRECTO("1:"&$G$6))

dentro de a fórmula matricial genera la matriz {1,2,3,4,5}. La ventaja de este método es que con cambiar el valor en G6, cambiamos la cantidad de elementos a ser tenidos en cuenta, sin necesidad de reescribir la fórmula.

Si queremos hacer las operaciones para los cinco menores valores de cada persona, por ejemplo, tenemos que complicar un poco nuestra fórmula (que ya dista de ser sencilla!)

={SUMA(K.ESIMO.MENOR(SI((personas=$E17)*valores<>0;(personas=$E17)*valores;FALSO);FILA(INDIRECTO("1:"&$G$6))))}



En esta fórmula tenemos que agregar la función SI para eliminar los ceros que aparecen en la matriz (personas=$E17)*valores
En la fórmula para calcular los valores mayores estos ceros no "molestan" ya que de por si son los menores. Pero cuando queremos calcular los valores menores de cada persona, tenemos que eliminarlos.
Para ver este problema más claramente podemos usar el botón Evaluar de la barra de herramientas Auditoría de fórmulas



Como ven la matriz generada contiene ceros en los valores que no corresponden al criterio elegido ("Pedro" en nuestro ejemplo). Al usar la función SI

SI((personas=$E17)*valores<>0;(personas=$E17)*valores;FALSO)

dentro de la fórmula matricial, obtenemos FALSO en lugar de ceros




Esto nos permite calcular correctamente nuestra suma y promedio para los valores menores del rango.



El cuaderno con el ejemplo se puede descargar aquí.

Buen fin de semana y mis disculpas por la demora en responder a las muchas consultas que llegan a mi mail. Lamentablemente algunas de ellas quedarán sin responder.

Technorati Tags: MS Excel

Como determinar fechas de iniciación o fin de tareas con Excel

Cálculos de fechas es una de los temas sobre los cuales recibo muchas consultas. He tratado el tema en varias notas. Si pulsan el enlace Fechas y Hora podrán acceder a todas la notas sobre el tema en el blog.

Uno de los cálculos más triviales es determinar la fecha de conclusión de una tarea sabiendo la fecha de inicio y la cantidad de días requeridos. Esto se hace con una simple operación de suma. Si ponemos en la celda B1 la fecha de inicio (03/01/2008) y en la celda B2 los días requeridos para completar la tarea (35), en la celda B3 ponemos =B1+B2




Si queremos hacer el cálculo tomando en cuento sólo los días hábiles, tenemos que usar la función DIA.LAB (WORKDAY en la versión inglesa). Esta función está disponible sólo si hemos instalado el complemento Analysis Toolpak



Como pueden ver, la tarea será concluida en 49 días, dentro los cuales hay 7 sábados y 7 domingos.


Un lector me consultaba como hacer la cuenta a la inversa. Es decir, dados los días requeridos y la fecha de cierre, cuando debemos comenzar nuestra tarea?

En el caso de tratarse de días corridos (sin tomar en cuenta los feriados), sencillamente restamos los días requeridos de la fecha de cierra



Si queremos hacer el cálculo sólo con días hábiles, la solución también es sencilla, pero no tan intuitiva. Usamos DIA.LAB, donde el primer argumento es la fecha de cierre y el segundo los días hábiles requeridos pero expresados como número negativo



Nótese el signo "-" delante de B17.

Como habrán notado, esta función tiene un tercer argumento, opcional, que permite introducir una lista de días festivos.

Technorati Tags: MS Excel