Ordenar listas en Excel por día se semana o por mes.

En los últimos días he recibido varias consultas sobre el tema de ordenar rangos o listas en Excel, como pueden juzgar por las notas publicadas recientemente.
Una de las consultas se refiere a cómo ordenar una lista siguiendo el orden de los días de la semana. El asunto no es trivial en Excel, ya que los nombres de los días son considerados texto y ordenados como tales.
Por ejemplo si tenemos esta lista de días de la semana sin ordenar



y lo ordenamos en forma corriente



veremos que el orden no cambia. Esto se debe a que, para Excel, la lista ya está ordenada alfabéticamente.
Excel tiene un método que nos permite definir listas personalizadas. Una vez definida una lista personalizada, podemos utilizarla para ordenar un rango de valores.
En nuestro ejemplo, necesitamos definir una lista personalizada que contenga los días de la semana en el orden deseado. Esto lo hacemos accediendo a la pestaña Listas Personalizadas en el menú Opciones y agregando la lista



Una vez agregada la lista abrimos el menú Ordenar, pulsamos el botón Opciones y elegimos la lista personalizada en la ventana Primer criterio de ordenación



Ahora obtenemos el resultado deseado

Technorati Tags: MS Excel

Ordenar rangos de valores mixtos (alfanuméricos) en Excel

Ordenar listas e valores es una tarea muy corriente en Excel. Para ordenar un rango de valores, sencillamente usamos el menú Datos-Ordenar que nos permite ordenar los valores del rango en forma ascendente o descendente usando hasta tres criterios

Los problemas empiezan cuando los valores a ordenar son valores mixtos, es decir que contienen número y letras, como en el caso de direcciones.

En este ejemplo tenemos una lista de direcciones en la columna A y la misma lista ordenada en forma ascendente usando el menú Ordenar de Excel



Como pueden ver, Excel considera cada valor de izquierda a derecha y pone en primer lugar los números, en nuestro caso de mayor a menor, y luego las letras.
Es por este motivo que las direcciones en la calle 25 de Mayo aparecen antes que las de la calle Av. Mitre.
Ahora se preguntarán por qué 25 de Mayo aparece antes que 9 de Julio, dado que 25 es mayor que 9? Cuando se trata de valores mixtos, alfanuméricos, Excel considera todos los componentes del valor como texto. Por lo tanto, al considerar el primer elemento del texto, el 2 es menor que el 9.
Hasta aquí esto no parece ser un grave problema. Pero si nos fijamos en el resto de la lista veremos que las direcciones de calle tampoco están ordenadas como hubiéramos querido. Por ejemplo, 25 de Mayo 541 aparece después de 25 de Mayo 3290!
Si estuviéramos organizando el recorrido de cartero, por ejemplo, estaríamos dificultando enormemente su tarea.

Excel no tiene un método incorporado para este tipo de ordenamiento, pero podemos hacerlo creando dos columnas auxiliares, en base a las cuales ordenaremos la lista.

La idea es separar el nombre de la calle del número de la casa (o edificio). Basándonos en la norma que en la dirección siempre aparece el nombre de la calle al principio y el número de la casa al final, usaremos la técnica que mostré en la nota sobre cómo extraer el ultimo elemento de un texto en una celda en Excel.

Empezamos por crear dos columnas auxiliares (aux1, aux2). En la primera aparecerá el nombre de la calle y en la segunda el número de casa



La fórmula en la columna B es

=IZQUIERDA(A2,LARGO(A2)-(LARGO(A2)-ENCONTRAR("*",SUSTITUIR(A2," ","*",LARGO(A2)-LARGO(SUSTITUIR(A2," ",""))))))

La fórmula en la columna C:

=VALOR(DERECHA(A2,LARGO(A2)-ENCONTRAR("*",SUSTITUIR(A2," ","*",LARGO(A2)-LARGO(SUSTITUIR(A2," ",""))))))

En esta última usamos la función VALOR, que queremos que Excel ordene los números como tales y no como texto.

La explicación sobre cómo funcionan estas fórmulas las encontrarán en la nota mencionada.

Ahora todo lo que nos queda por hacer es ordenar la tabla usando la columna Aux1 como primer criterio y la columna Aux2 como segundo

Technorati Tags: MS Excel

Listas ordenadas en validación de datos de Excel - segunda nota

Esta es una ampliación a mi nota anterior sobre el tema. A partir del comentario de un lector he notado la posibilidad de usar una función matricial en lugar de columnas auxiliares.
Básicamente usaremos las mismas funciones, pero combinándolas en una única fórmula matricial.
Volviendo a nuestro ejemplo, tenemos esta lista



donde hemos creado dos columnas: B para obtener el número de orden de los miembros de la lista y en C la lista ordenada alfabéticamente para crear nuestra validación de datos ordenada.

Para mostrar la solución con fórmula matricial empezamos por crear una nueva hoja, "matricial", y poniendo un rango dinámico para la lista de valores de la columna A en el nombre "frutasm". En la columna B obtendremos la lista ordenada, usando una fórmula matricial



Para el rango de la columna B creamos nombre con rango dinámico, "ordenm"



La fórmula en la columna B es una combinación de las fórmulas que usamos en la solución no matricial (en la hoja "con formulas"), pero utilizando rangos de celdas (matrices) en lugar de una celda por fila

={INDICE(frutasm,COINCIDIR(FILA()-1,CONTAR.SI(frutasm,"<="&frutasm),0))} Como en toda fórmula matricial, la introducimos en el rango de celdas relevante (en nuestro ejemplo B2:B10) usando la combinación de teclas Ctrl+Mayúsculas+Enter.


Para evitar ver los resultados #N/A debidos a las celdas vacías, podemos aplicar formato condicional, pero esto no evitará que los valores #N/A aparezcan en la lista desplegable.



Ahora tenemos que corregir la definición de la lista de validación de datos usando el nombre "ordenm"



Ahora podemos agregar nuevos valores a la lista en la columna A, y estos aparecerán ordenados en la columna B y en la lista desplegable






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.