Fórmula para ordenar datos en Excel

Excel nos permite ordenar la información rápidamente utilizando el comando Ordenar y filtrar, pero en ocasiones necesitamos una fórmula para ordenar datos en Excel y en esta ocasión te mostraré una alternativa para hacerlo.

Los datos que utilizaremos en nuestro ejemplo tienen una columna de texto y otra columna de números. En el primer ejemplo ordenaremos los datos por los datos numéricos y en el segundo ejemplo ordenaremos por los valores de texto.

Fórmula para ordenar datos en Excel

Fórmula para ordenar datos numéricos

Para nuestro primer ejemplo haremos un ordenamiento descendente de la columna Calificación. La función que nos permitirá hacer esto de una manera sencilla será la función JERARQUIA.EQV la cual obtiene la posición de un número dentro de un rango. Es importante mencionar que esta función está disponible a partir de Excel 2010, para versiones anteriores debes utilizar la función JERARQUIA. Ahora considera la siguiente fórmula:

=JERARQUIA.EQV(B2,$B$2:$B$9)

Esta fórmula obtiene la posición del valor en B2 dentro del rango B2:B9 que para nuestro ejemplo será la posición 3, observa el resultado:

Fórmula para ordenar números en Excel

La columna Posición asigna el número 1 al valor más alto dentro del rango y a partir de ahí se enumeran todos los registros. Puedes ver que la posición 8 corresponde a la calificación 61 la cual es la más baja de todas las calificaciones. Ahora que ya tenemos las posiciones podemos ordenar los datos utilizando como referencia la columna posición y utilizando la función INDICE y la función COINCIDIR de la siguiente manera:

=INDICE($A$2:$A$9,COINCIDIR(E2,$C$2:$C$9,0))

La función INDICE obtiene un valor dentro de un rango con solo especificar su fila. Con esta fórmula estamos buscando dentro del rango A2:A9 y la fila la obtenemos al hacer coincidir el número 1 de la columna E con el número 1 de la columna C (Posición) que para nuestro ejemplo es Roberto:

Ordenar números con fórmula de Excel

Al copiar la fórmula hacia abajo obtenemos automáticamente el nombre de cada alumno de acuerdo a su posición. Para obtener el dato de la calificación usamos una fórmula idéntica a la anterior con la diferencia de que la función INDICE hará la búsqueda sobre el rango B2:B9:

Fórmula para ordenar números automáticamente en Excel

Con esto hemos ordenado los datos de manera descendente utilizando una columna numérica. Si queremos hacer un ordenamiento ascendente debemos hacer un solo cambio en la función JERARQUIA.EQV de la columna Posición colocando un número 1 como el tercer argumento de la función lo cual hará que los datos se ordenen de manera ascendente. Observa la fórmula de la celda C2 y el resultado del ordenamiento en los datos:

Fórmula para orden ascendente de números

En este caso los datos se ordenan comenzando por la calificación de menor valor hasta la de mayor valor. Así que con solo modificar el tercer argumento de la función JERARQUIA.EQV podrás controlar el tipo de ordenamiento de los datos.

Desempate de valores numéricos

Nuestro ejemplo anterior funcionó a la perfección porque no tenemos calificaciones repetidas entre los alumnos pero observa lo que sucede si Ignacio y Brenda tienen la misma calificación:

Desempatar posiciones en Excel

Al tener dos datos numéricos del mismo valor, la función JERARQUIA.EQV determina un empate entre ambos y les otorga la misma posición, que en nuestro ejemplo es la posición 5, pero la siguiente posición otorgada será la posición 7 saltándose la posición 6 y por lo tanto generando un error en nuestra tabla ordenada.

Para solucionar este problema utilizaré una columna auxiliar donde sumaré a cada calificación el número de fila donde se encuentran los datos. De esta manera, la calificación 77 de Sonia en la columna auxiliar tendrá un valor de 77.000002 porque se encuentra en la fila 2 de la hoja de Excel, la calificación de 72 de Ignacio tendrá un valor de 72.000003. La fórmula utilizada en la columna auxiliar será la siguiente:

=B2+0.000001*FILA()

Otro cambio que debemos hacer es modificar la fórmula de la columna Posición para que la función JERARQUIA.EQV haga el ordenamiento utilizando la columna auxiliar. El resultado es el siguiente:

Desempatar resultado de la función JERARQUIA.EQV

Observa que nuestra tabla de datos ordenada ya no muestra el error ocasionado por la repetición de calificaciones de Ignacio y Brenda.

Fórmula para ordenar texto en Excel

Para ordenar texto en Excel utilizando fórmulas es importante recordar la manera en que la herramienta trabaja con texto especialmente al utilizar operadores de comparación. Observa la siguiente tabla de datos:

Comparación de textos en Excel

Al utilizar el operador Mayor que (>) para comparar dos textos, Excel nos responderá si el primero de ellos tiene una posición mayor dentro del alfabeto. Para la celda C2 el resultado es falso porque la letra A no está después de la letra B. Sin embargo, para la celda C3 el resultado es verdadero porque la letra Z está efectivamente después de la letra A.

Lo mejor de todo es que esta comparación funciona correctamente para palabras y lo puedes ver con los ejemplos de las últimas dos filas. El hecho de que Excel nos devuelva este resultado al comparar dos textos nos facilitará mucho el ordenamiento con fórmulas. Para llegar a la solución de ordenamiento utilizaremos la función CONTAR.SI la cual nos ayudará a contar la cantidad de cadenas de texto que se encuentran ubicadas alfabéticamente antes de otra. Considera la siguiente fórmula:

=CONTAR.SI($A$2:$A$9,"<="&A2)

La función CONTAR.SI contará todos los elementos del rango A2:A9 que sean menores o iguales que el valor de la celda A2 y de esta manera sabremos la cantidad de valores que le anteceden alfabéticamente. Observa el resultado de esta fórmula:

Fórmula para ordenar alfabéticamente en Excel

En nuestro ejemplo Brenda tiene la posición 1 porque la función CONTAR.SI encuentra que solo el texto de la misma celda A8 es menor o igual a Brenda. La posición 2 es para Ignacio porque Brenda e Ignacio son los valores ubicados en una posición alfabética menor o igual dentro del rango. Para generar la tabla con los datos ordenados utilizamos la misa técnica que en el ejemplo anterior con la función INDICE y COINCIDIR:

Ordenar texto en Excel con fórmulas

De esta manera hemos ordenado los datos de manera ascendente por la columna Nombre. Si queremos ordenarlos de manera descendente será suficiente con cambiar el operador de comparación utilizado en la condición de la función CONTAR.SI por un símbolo Mayor qué (>). En la siguiente imagen puedes ver el cambio en la fórmula lo cual ocasiona un cambio en la posición y ordenamiento de los datos:

Cómo ordenar alfabéticamente en Excel

Desempate de posiciones en valores de texto

Al igual que con los números, si tenemos dos celdas con el mismo texto tendremos un error en los datos ordenados así que debemos utilizar un criterio de desempate similar al explicado anteriormente utilizando una columna auxiliar con la siguiente fórmula:

=CONTAR.SI($A$2:$A$9,"<="&A2) + 0.000001*FILA()

Como ya vimos en el ejemplo anterior, la función CONTAR.SI nos devuelve la cantidad de elementos que preceden alfabéticamente a la celda A2 y a ese resultado le sumaremos el número de fila para asegurarnos de que sea un valor único dentro de la columna auxiliar. Para obtener la posición final utilizaremos la función JERARQUIA.EQV de la siguiente manera:

=JERARQUIA.EQV(C2,$C$2:$C$9,1)

Finalmente modificamos las fórmulas en la tabla de datos para que la función COINCIDIR consulte el resultado de la función JERARQUIA.EQV. Observa cómo se ordenan los datos de manera ascendente por la columna de texto y las dos celdas con el mismo valor aparecen juntas:

Ordenar alfabéticamente con fórmula en Excel

En realidad hemos revisado más que una sola fórmula para ordenar datos en Excel, pero las opciones presentadas cubren una buena cantidad de posibilidades de ordenamiento que seguramente te serán de utilidad. Puedes descargar el libro de trabajo con los ejemplos descritos en este artículo para que experimentes por tu cuenta.

41 pensamientos en “Fórmula para ordenar datos en Excel

  1. MarC

    Excelente material.. felicitaciones

    1. BEATRIZ

      HOLA EXCELENTES LOS TEMAS QUE ME ENVIAN SON MUY EXPLICITOS LOS HE APROVECHADO AL MAXIMO
      GRACIAS

      BETTY

  2. Marilú Morales

    Estoy impresionada con el trabajo que nos muestras y lo que nos puedes enseñar del excel día a día.

    Muchas gracias!!

  3. EDUARDO

    Excelente herramienta, en este momento lo estoy aplicando, muchas gracias.

  4. MARCOS

    muchas gracias por tus enseñanzas

  5. Horacio Mamolite

    Agradecimientos por tanta generosidad de aplicar tus conocimientos a gente que quiere aprender.
    Es muy interesante saber como se pueden hacer tantas cosas con este programa.
    Gracias.-Saludos Horacio

  6. SERGIO

    la verdad mis felicitaciones nuevamente por sus aportaciones

  7. Xiomara Castiblanco

    Una manera mas simple de ordenar datos… Muchas gracias por compartirnos tus conocimientos, me han servido mucho…

  8. Ángel

    Que gran ayuda, excelente aporte… Gracias.

  9. Walter

    Muy interesante, de gran ayuda. En verdad gracias por todo esto

  10. ángeles

    Gracias!. pedagogía Util… muchas gracias

  11. Heraclio Cabiedes

    Una manera o forma práctica de clasificar bajo estas características… Gracias por compartir…

  12. Horacio Carmona

    Moy, muchas gracias por compartir tus conocimientos.

  13. Juan Carlos

    Me impresionan todas las formas de combinar fórmulas…generalmente uno cree que la fórmula SOLO arroja resultados según el nombre de la misma o su sintaxis…pero nos demuestras que con un poco más de exploración sumado a una dosis de lógica, se pueden generar datos inpensados…

    Felicitaciones Moisés”!!!!!

  14. Jose

    Excelente, me siento muy agradecido por esta ayuda

  15. Paco

    Se agradecen explicaciones mostradas de forma tan eficaz y sencilla. No pares!!
    Gracias
    Paco

  16. Angélica

    Graciasssss…..mil gracias……

  17. Joel Utate

    esta super importante gracias por las exelentes formulas que nos ayudan en nuestra vida cotidiana

  18. miguel

    Muy bien, cada vez que uno esta enfrente de excel, conoce algo nuevo

    Muchas gracias

  19. Walter Gregorio Toro Lopez

    Excelente lo que he podido aprender con su ayuda… Mil Gracias…

  20. Ricardo

    !! EXCELENTE !!
    Siempre estoy pendiente de cada una de las publicaciones
    Te felicito y muchas gracias por tus enseñanzas

  21. Carlos.

    Gracias, todo esta excelente.

  22. Humberto

    Excelente material ! muchas cosas estoy aprendiendo gracias a sus materiales muy bien explicados.
    Muchas gracias

  23. paul

    Muy bueno lo que das a conocer, en algunos casos otros tienen la oportunidad de aprenderlo en un salón de clases, otros por este medio que me parece nos da la opción de capacitarnos.
    Agradecido por el tiempo que te tomas para publicar estos temas.

  24. LUis

    facinado, que fácil es hacer el trabajo con Excel, gracias compañeros son ustedes unos maestros en este tema.

  25. ezequiel

    muy bueno!! Te felicito y muchas gracias por enseñarnos

  26. Valent

    Muchas GRACIAS, será de mucha utilidad los conocimientos que comparte, estare al pendiente de su próxima publicacion.

  27. Mauricio Jiménez

    Excelente, nunca había intentado ordenar con fórmulas, me es muy útil este artículo. Mil gracias.

  28. Isauro

    Gracias por tus enseñanzas, he aprendido y sigo aprendiendo este mundo fascinante de excel. Ojala sigas aportando mas practicas. GRACIAS

  29. FERNANDO FERNANDEZ

    excelente informacion, agradecemos su trabajo en enviarnos a nuestro buzon cada practica y cuenten que lo recomendaremos pues nos impulsan a ser mejores cada dia.

  30. Gilberto Lindo

    Excelente material, muy bueno y preciso.

  31. Cacho

    Muy buenos los comentarios y agradecido por el envio

  32. Carol

    No puedo dejar de agradecer a cada pagina que ingreso, siempre encuentro la solucion de que necesito. Mil gracias.

  33. Nora

    Gracias por las enseñanzas compartidas con quienes damos seguimiento a sus comentarios.
    Quisiera saber si me pueden sugerir algún método para ordenar mi base de datos con clientes que se repiten en más de dos ocasiones en la hoja. Entre a trabajar a una empresa que tiene clientes que tienen adeudos, realizan los pagos y cuando vuelven a adquirir productos los metían en una nueva cuenta, por lo que se repiten en varias ocasiones. ¿hay algún método para hacer un concentrado de su historial sin tener que desplazarme por toda la hoja? (que por cierto es enorme).Cualquier ayuda o comentario, me serán muy útiles. De antemano les agradezco su atención. Saludos.

    1. Moisés Ortíz Autor

      Hola Nora, todo eso se evita si se diseña adecuadamente la base de datos. Te dejo un artículos que habla sobre los conceptos básicos de diseño de bases de datos:
      http://exceltotal.com/diseno-de-bases-de-datos/

  34. Rodrigo

    Wow! En verdad, mil gracias por esto. Vaya que me sirvió a la perfección.
    Seguiré al pendiente del las publicaciones.

  35. Michael Marcillo

    Muchisisimas gracias mi pana, esto de ordenamiento me servio para mi proyecto de reingenieria de procesos.
    Saludos desde Ecuador – Guayaquil

  36. Jairo

    Es la primera vez que consulto su página y sus explicaciones de uso de Excel, y estoy muy agradecido. No solamente muestra un amplio dominio del programa, si no una excelente pedagogía para transmitir las ideas.
    Definitivamente haré de su página una fuente permanente de consulta.
    Saludos cordiales

  37. edgar

    Excelente trabajo me ayuda bastante gracias aprovecho la oportunidad para desearte que tengas un brillante 2014

  38. carlos cbr

    por mi trabajo, uso mucho las hojas de excel. He consultado varias paginas buscando soluciones y formulas, y EXCELTOTAL es la mejor con diferencia. Las explicaciones son claras y faciles de aplicar. Gracias, pero de verdad, muchas gracias

  39. Eloy

    Justo lo que estaba buscando…
    Muchas gracias desde Maturín, estado Monagas. Venezuela