Ordenar tablas sin usar macros [Excel]

Tabla excel con los datos de la Prima de Riesgo
Datos Prima de Riesgo [mayo 2014]
Ú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 => 102
    • 2 dígitos representan el número de elementos => 102
  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

Documento Excel ilustrativo para el ordenado automático de filas de una tabla.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *