domingo, junio 08, 2008

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:

20 comentarios:

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

    ResponderBorrar
  2. 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.

    ResponderBorrar
  3. 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

    ResponderBorrar
  4. 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.

    ResponderBorrar
  5. 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

    ResponderBorrar
  6. 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.

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

    ResponderBorrar
  8. 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

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

    ResponderBorrar
  10. 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

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

    ResponderBorrar
  12. 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

    ResponderBorrar
  13. 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)

    ResponderBorrar
  14. 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.

    ResponderBorrar
  15. No es imposible, sino más complejo.

    ResponderBorrar
  16. Muchas gracias por la ayuda, era justo lo que quería. Gracias!

    ResponderBorrar
  17. Buenas noches, necesitos ordenar un listado de alumnos por números de cédula, pero en la misma columna debo colocarle la letra V- si es venezolano y la E- si es extranjero: Ejemplo: V-11210147, V-456145, V-1378412. Se puede ordenar?

    ResponderBorrar
  18. Por supuesto. Como se trata de texto al ordenar en forma ascendiente los valores que empiezan con E aparecerán en primer lugar.

    ResponderBorrar
  19. hola quisiera ordenar estas placas de carros asi MAB-4555 MGN-3655 como se puede ordenar primero por el numero que quede asi MGN-3655 Y LUEGO MAB-4555 EN EXCEL

    ResponderBorrar
  20. EN tu caso, sencillamente usando el menú de Excel. Tus valores son texto, así que Excel los ordena de izquierda a derecha. En tu ejemplo tendrás que hacerlo en forma descendiente.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.