Ordenar texto en Excel con fórmulas.

Excel permite ordenar datos, ya sean texto o número, con facilidad y flexibilidad usando el menú Datos-Ordenar.
Pero hay situaciones en las cuales queremos realizar la tarea con fórmulas, como me consultaba uno de mis lectores hace unos días atrás.
La solución consiste en usar la función CONTAR.SI con un pequeño truco. Empecemos por plantear la situación. Supongamos esta lista de nombres que queremos ordenar alfabéticamente con fórmulas



El primer paso consiste en crear una columna auxiliar con esta fórmula:

=CONTAR.SI($A$2:$A$11;"<="&A2)

que copiamos a todo el rango de la columna B

Es importante notar que el segundo argumento de la función está formado por el texto "<=" concatenado con el operador "&" a la celda correspondiente de la columna A.



Como ven la función CONTAR.SI hace aquí las veces de la función JERARQUIA, que sólo funciona con números, no con texto.

El segundo paso consiste en crear una tabla con dos columnas. Una columna auxiliar donde ponemos los números de posición en forma ordenada y una segunda columna donde aparecerán los nombres. En esta columna ponemos esta fórmula

=INDICE($A$2:$A$11;COINCIDIR(D2;$B$2:$B$11;0))

Usamos INDICE y COINCIDIR ya que los números auxiliares en la tabla original están a la derecha de los nombres. Si estuvieran a la izquierda podríamos usar la función BUSCARV.



Como ven, tenemos nuestra lista ordenada en la tabla D1:E11.

Esta técnica tiene un serio inconveniente. Si un nombre aparece repetido, obtendremos un resultado #N/A



Para superar este problema creamos una segunda columna auxiliar. En esta columna ponemos esta fórmula relacionada a la primera columna auxiliar

=JERARQUIA(B2;$B$2:$B$11;2)+CONTAR.SI($B$2:B2;B2)-1

Ya hemos mostrado esta técnica para lograr "desempates" usando la función JERARQUIA.



Como ven, el primer Daniel recibe el número de orden 5 y el segundo, 6.

Todo lo que nos queda por hacer es aplicar la misma fórmula INDICE y COINCIDIR que usamos más arriba

Technorati Tags: MS Excel