Últimamente me paso muchas horas delante del Excel por temas de trabajo y me he dado cuenta de que, a pesar de ser una herramienta muy potente pocos saben utilizarla bien de verdad. Yo, por desgracia no me encuentro entre ellos.

El secreto de la productividad utilizando en esta herramienta no es otra que conseguir maximizar la automatización de tareas. Por ello voy a intentar describir paso a paso como ordenar una lista sin tener que hacer ni un solo clic.

Si bien es cierto que esto se puede hacer “fácilmente” haciendo uso de una macro, vamos a ver los pasos a seguir para hacer lo mismo mezclando dos funciones de Excel: RANK y VLOOKUP.

En primer lugar vamos a crear una tabla con información, por ejemplo los valores de la prima de riesgo de distintos países frente a Alemania (datos del 19 de mayo de 2014):

País	Prima de Riesgo
España	164,00
Reino Unido	123,00
Francia	46,00
Italia	175,00
Portugal	238,00
Estados Unidos	119,00
Suiza	–       59,00
Japón	–       74,00
Australia	237,00
Austria	23,00
Finlandia	31,00
Grecia	538,00
Holanda	31,00
India	751,00
Irlanda	132,00
Nueva Zelanda	297,00
Suecia	49,00
Canadá	93,00

Ahora vamos a establecer el ranking de cada país teniendo en cuenta su prima de riesgo. Para ello, en la columna de la izquierda (es importante que sea en la de la izquierda, ya que posteriormente utilizaremos la función VLOOKUP) de la primera fila escribiremos la siguiente fórmula:

=RANK.EQ(G7;$G$7:$G$24;1)

RANK.EQ es una función que nos dará el orden de un valor dentro de un listado de valores. La coletilla EQ hará que en caso de empate se muestre el mismo número dos veces; en este caso Finlandia y Holanda tienen la misma Prima de Riesgo y están en cuarto lugar, por lo que asignará a ambas el valor de empate a 4 (En caso de querer ver la media de los valores de la posición, en este caso 4,5 en lugar de 4, deberemos hacer uso de RANK.AVG). Este empate más adelante tendremos que resolverlo para poder ordenar correctamente la lista.

Para la función RANK.EQ:

• El primer parámetro (G7) hace referencia al valor para el que deseamos buscar una posición.
• El segundo parámetro ($G$7:$G$24) indica el vector sobre el que se hará la clasificación de los valores. En este caso después de seleccionar la columna G entre la fila 7 y la 24, hemos añadido los símbolos $ (pulsando f4) para que al arrastrar la fórmula a otras filas, no cambie el vector sobre el que se va a buscar la posición.
• El tercer valor determina que el orden que se quiere seguir es ascendente. Si hubiésemos escrito un 0, entonces sería descendente.

Finalmente, para obtener las posiciones asociadas a todos los valores aplicamos la fórmula a cada fila.

Tras completar estos pasos, nuestra tabla debería ser parecida a la siguiente:

Ranking	País	Prima de Riesgo
12	España	164,00
10	Reino Unido	123,00
6	Francia	46,00
13	Italia	175,00
15	Portugal	238,00
9	Estados Unidos	119,00
2	Suiza	–     59,00
1	Japón	–     74,00
14	Australia	237,00
3	Austria	23,00
4	Finlandia	31,00
17	Grecia	538,00
4	Holanda	31,00
18	India	751,00
11	Irlanda	132,00
16	Nueva Zelanda	297,00
7	Suecia	49,00
8	Canadá	93,00

Ahora sólo nos queda ordenar la tabla. Para lo que haremos uso de la función VLOOKUP.

Para ello, lo primero que vamos a hacer es escribir en la columna de la izquierda de nuestra nueva tabla el orden en el que deseamos que aparezca nuestra tabla. En nuestro caso vamos a ordenarlo en orden ascendente, por lo que en la primera columna deberemos escribir una enumeración de 1 a 18, marcando así cada una de las filas.

Para cada fila escribiremos en la columna contigua a la enumeración:

=VLOOKUP($I7;$E$7:$G$24;2;FALSE)

De manera que:

• El primer parámetro indica la columna en la que indicamos el valor que deseamos buscar, en nuestro caso la enumeración.
• En el segundo parámetro indicamos la tabla para la cual:
  • En la primera columna de la misma se buscará el valor que hemos indicado en el primer parámetro.
  • En el resto de columnas tenemos información que queramos extraer de la tabla una vez encontrado el valor a buscar.
• Como tercer parámetro indicamos que una vez encontrado ese valor, mostraremos en la celda el valor de la segunda columna.
• Finalmente, el cuarto parámetro indica que se desea encontrar el valor buscado coincida exactamente con el valor que deseamos buscar.

Repetimos el proceso con la tercera columna para tener el valor de la prima de riesgo asociada al país y listo. Ya tenemos nuestra lista ordenada.

Si no tenemos valores repetidos, ya hemos terminado, pero este no es nuestro caso, nos vamos a encontrar con errores al aplicarle a la tabla la función VLOOKUP porque tenemos dos países con la misma prima de Riesgo.

Ranking	País	Prima de Riesgo
1	Japón	–     74,00
2	Suiza	–     59,00
3	Austria	23,00
4	Finlandia	31,00
5	#N/A	#N/A
6	Francia	46,00
7	Suecia	49,00
8	Canadá	93,00
9	Estados Unidos	119,00
10	Reino Unido	123,00
11	Irlanda	132,00
12	España	164,00
13	Italia	175,00
14	Australia	237,00
15	Portugal	238,00
16	Nueva Zelanda	297,00
17	Grecia	538,00
18	India	751,00

Llegados a este punto tenemos varias opciones:

• Arreglar el error a mano: en este caso tenemos pocos registros por lo que no debería suponer un problema corregir el registro que nos falta.
• Modificar los datos originales de tal modo que no afecten a los datos que mostramos en la hoja pero que evite los empates.

Vamos a proceder a explicar el segundo método aun a sabiendas de que no siempre será posible seguir estos pasos antes de aplicar la función RANK.EQ.

*NOTA: lo que voy a contar a continuación podría calificarse de chapuza (útil y resolutiva, eso sí). Es posible que existan otras formas mejores de hacerlo sin usar macros.

1. Observar la precisión de los datos. En este caso, estamos tratando con la prima de riesgo, que son valores enteros, es decir, en este caso tenemos una precisión de unidades, pero en la tabla queremos mostrar hasta las centésimas.
2. Contamos con 18 registros que queremos mostrar ordenados, es decir, necesitamos dos dígitos.
3. Es decir:
   • 2 decimales => 10²
   • 2 dígitos representan el número de elementos => 10²
4. Ahora ponderamos cada uno de los países dándoles un orden manualmente para que, en caso de empate se haga uso de éste.
5. Modificamos el valor de la prima de riesgo de tal forma que quede aumentado su valor en 0,0001 veces el valor que le hemos asignado manualmente. Por ejemplo:

=C7+0,0001*E7

De este modo, deberíamos obtener un resultado final satisfactorio como el que se puede observar en la siguiente tabla:

Ranking	País	Prima de Riesgo
1	Japón	–     74,00
2	Suiza	–     59,00
3	Austria	23,00
4	Finlandia	31,00
5	Holanda	31,00
6	Francia	46,00
7	Suecia	49,00
8	Canadá	93,00
9	Estados Unidos	119,00
10	Reino Unido	123,00
11	Irlanda	132,00
12	España	164,00
13	Italia	175,00
14	Australia	237,00
15	Portugal	238,00
16	Nueva Zelanda	297,00
17	Grecia	538,00
18	India	751,00

Finalmente, podéis descargar el archivo Excel que se ha utilizado para realizar este artículo.

Ordenado automático de registros.xslx (844 descargas )