Ordenar tablas sin usar macros [Excel]

Publicado por Alejandro Escario en

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

Prima de riesgo
Datos Prima de Riesgo [mayo 2014]

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ísPrima 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:

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

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

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

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

Ordenado automático de registros.xslx (534 descargas)
Categorías: Uncategorized

0 comentarios

Deja una respuesta

Marcador de posición del avatar

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