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

domingo, junio 08, 2008

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:

15 comments:

Ernest 11 junio, 2008 05:47  

Como puedo Ordenar un rango de datos si en la columna A tengo los numeros 1,2,3 hasta 32 y en en la columna B los Dias que aplican los servicios D, L, M, W, J, V, S (tengo 32 L,M,W,J,V,S,D) y quiero ordenar que los datos de la siguiente forma
D1, L2, M3, W4, J5, V6, S7, D8 cuando llegue al W32 empiece en J1, V2, S3, D4, L5 y asi sucesivamente

Esto es algo que actualmete me trae loco y no he podido hacer de forma rapida

Jorge L. Dunkelman 11 junio, 2008 18:10  

Ernst
de acuerdo a tu descripción, no puedes hacerlo con el menú Datos-Ordenar. Dado que tienes dos campos (columnas), puedes ordenar los dos en forma ascendente, los dos en descendente o cada uno en un sentido contrario. En ninguna de los caso obtendrás lo que estás buscando. El problema es que las letras que representan los días son consideradas por Excel como tales y en ningun caso obtendrás el resultado buscado.
Habría que buscar una solución distinta a tu problema, no con Ordenar.

Cristian Hernandez 12 junio, 2008 20:52  

Excelente, ni te imaginas como me ayuda a mi, que trabajo en gestiones de delivery, y a veces las BD que envian los bancos vienen con las direcciones tal cual como vienen en la columna A, y se genera un caos, para organizar las rutas de los distribuidores.

te pasastes

fali 12 junio, 2008 20:57  

Hola Jorge, soy Jose A.
Me gusta mucho tu pagina y estoy aprendiendo bastante.
Quisiera preguntarte un ejercicio aunque no tiene que ver con este ejemplo:
Yo tengo un desplegable con tres opciones:opcion1, opcion2 y opcion3 de forma que si eligo opcion1 aparece un grafico de los datos de opcion1, si eligo opcion2 aparece otro grafico distinto y con opcion3 lo mismo.
Yo me he creado tres macros de forma que cada uno hace aparecer un grafico concreto.
Utilizando VBA no tengo problemas y consigo realizarlo.
Mi pregunta es si se puede hacer sin tener que utilizar VBA?
Muchas gracias
Jose A.

Jorge L. Dunkelman 12 junio, 2008 21:40  

Hola Jose A

hay varias posbilidades y algunas las he tratado en mi blog sobre gráficos.
Puedes fijarte en esta nota, y tambien en esta

fali 13 junio, 2008 10:20  

Hola Jorge
Ya habia mirado antes esos articulos que me especificas. Perdona por no explicarme bien antes. Lo que pasa es que lo que yo quiero es que si no eligo ninguna opcion del desplegable no debe aparecer ningun gráfico.
Con VBA lo realice sin problema como te especifique antes.
De todas formas muchas gracias por volverme a recordar esos dos ejemplos que son muy buenos.

fali 13 junio, 2008 19:05  

Hola Jorge
Olvida el último comentario. Ya lo he resuelto gracias a tus ejemplos. He utilizado el ocultar graficos.
Saludos

Anónimo,  03 septiembre, 2008 19:43  

Hola que tal, esta muy bueno su articulo, pero quisiera una ayuda, si no le molesta, tengo una lista de placas de carro como sabe estas contienen tres letras separadas de tres números, ademas incluyo en ella algunas placas de motos (3 letras y dos números; o en algunos casos 3 letras , 2 números y una letra), me gustaría saber si es posible ordenarlas de acuerdo al ultimo digito, osea, si por ejemplo hay un BJY 034 y un MKV 591, Excel organizaria esto, automaticamente BJY 034 y después MKV 591, pero mi intención es ordenarlas de acuerdo al último digito,es posible hacer esto?. Agradezco la ayuda que me puedan brindar

Jorge L. Dunkelman 06 septiembre, 2008 15:07  

Puedes extraer el último dígito en una columna auxiliar y ordenar la tabla de acuerdo a esta columna.

Anónimo,  10 febrero, 2009 19:38  

Hola cómo estás?, quisiera saber si me puedes ayudar con un problema que tengo:
Necesito organizar unas direcciones, digamos que CLL 47 #29-77 (202), CLL 47 #29-77 (302)y
CLL 47 #29-102, por qué cuando le doy ordenar automáticamente ubica en primer lugar la CLL 47 #29-102 y luego las otras dos, y por favor, si puedes dime como hago para que me tome esos números de tres cifras. Muchas gracias

Jorge L. Dunkelman 11 febrero, 2009 21:33  

Como está explicado enla nota, Excel sigue ciertas reglas para ordenar. En tu caso no logro ver cuál seráa la regla

Anónimo,  03 julio, 2010 20:20  

hola tengo un listado de numeros de cedula y necesito ordenarlos por el ultimo digito es decir todas las que terminan en 1 luego las que terminan en 2 etc

Jorge L. Dunkelman 04 julio, 2010 14:18  

Podés crear una columna auxiliar que contenga el último dígito de la cédula y ordenar la tabla según esta columna.
Para extraer el último dígito del número podés usar esta fórmula

=DERECHA(A1;1)

Jose Riu,  19 enero, 2012 19:47  

Hola Jorge.

Estaba revisando este ejemplo, y funciona correctamente si la direccion es solo de la altura de la calle, pero se complica en los casos que tengas datos adicionales como piso y departamento.

Se me ocurre que ahi el problema es casi de imposible resolucion, ya que no ceo que haya una regla que pueda indicarse para ambos casos (digamos que direcciones de casas y de departamentos).

Te dejo la inquietud.

Un fuerte abrazo.

Jorge L. Dunkelman 19 enero, 2012 20:37  

No es imposible, sino más complejo.

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP