Cómo armar en Excel un listado con los 5 datos mayores y 5 menores a partir de una lista desordenada.

NIVEL: INTERMEDIO

DESCARGAR Archivo de ejemplo.  Recomiendo descargar el archivo para poder seguir mejor el ejemplo y entender más fácilmente las distintas funciones.

Las funciones MAX y MIN nos permiten encontrar cuál son los valores Máximos y Mínimos dentro de un rango de datos.

Son muy útiles cuando necesitamos justamente encontrar los valores extremos: el mayor de todos los números entre los seleccionados y el menor de todos.

Pero, ¿qué pasa cuando necesitamos encontrar por ejemplo los 5 primeros (ver imagen)? ¿Hay alguna forma de hacerlo fácilmente en Excel?

Afortunadamente Excel nos brinda un par de funciones muy útiles (aunque poco conocidas):

• K.ESIMO.MAYOR (LARGE en inglés)
• K.ESIMO.MENOR (SMALL en inglés)

Como es usual, vamos a ver cómo utilizar estas funciones a través de un ejemplo.

En el archivo de ejemplo (el link para descargar está al inicio de esta nota), tenemos una lista alfabética de las provincias de Argentina y su población estimada.

1. Encontrar la mayor y menor población

Repasando las funciones MAX y MIN, la fórmula es:

MAX(rango de datos)

MIN(rango de datos)

Esta es la forma más habitual.  Pero en lugar de rangos de datos podemos tener valores separados con “;”

Ejemplo:

MAX(1;8;3;4) devuelve 8

Por lo tanto, econtrar cuales son las mayores y menores poblaciones en nuestro ejemplo, se resuelve fácilmente:

MAX(B3:B26)

MIN(B3:B26)

Nos falta ahora identificar a cuáles provincias pertenecen éste valor.  Esto es un poco más complicado.  Lo dejo para el final.

2. Encontrar las 5 Mayores y 5 Menores poblaciones.

Para este caso utilizaremos las funciones K.ESIMO.MAYOR y K.ESIMO.MENOR

Ambas funciones tienen la misma estructura.  Por ejemplo:

K.ESIMO.MAYOR(Rango;k)

Donde:

• Rango: es el rango de datos
• k: es la posición a partir de la más alta (en caso de K.ESIMO.MAYOR) que se desea buscar.  1 dará el valor más alto (igual a MAX).  2 dará el segundo valor más alto.  3, el tercer valor más alto, etc.

En el caso de K.ESIMO.MENOR, si k=1 la fórmula devolverá el valor más bajo (igual a MIN), 2 es segundo más bajo y así sucesivamente.

Es para esto que en la imagen de la derecha vemos los números del 1 al 5 al lado de cada nombre de la provincia.  Utilizaremos estos valores para indicar qué posición necesitamos.

En las celdas E8 vamos a identificar la población de la provincia con más habitantes, en la E9 la segunda de mayor cantidad, etc., según los números que tenemos en las columna C.

En la celda E8 ingresaremos la fórmula:

=K.ESIMO.MAYOR($B$3:$B$26;C8)

Donde:

• $B$3:$B$26 es el rango donde se encuentran las poblaciones.  Dejamos estas celdas fijas (con signos $ o presionando F4) porque copiaremos la fórmula a las celdas inferiores.
• C8 contiene el número de la posición a buscar.

Para los casos de las provincias con menor cantidad de habitantes, el proceso es equivalente:

=K.ESIMO.MENOR($B$3:$B$26;G8)

3. Encontrar el nombre de la provincia a la cual pertenece la población.

Ya tenemos identificadas cuáles son las poblaciones que necesitábamos buscar: la mayor, la menor, las 5 mayores y las 5 menores.

Ahora, debemos ir para atrás e identificar a cuál es el nombre de la provi ncia a la c ual corresponde.

¿Cómo lo haríamos manualmente? Revisando la columna de las poblaciones (columna B) y cuando encontramos la cifra que coincide, buscamos el valor que se encuentra en la misma fila pero en la columna A.

Ejemplo: Para identificar a qué ciudad corresponde la población 3.058.309 (encerrada en rojo en la imagen), buscaremos en la columna B el valor hasta encontrarlo.  Como este es el quinto valor de la lista desde arriba, la ciudad a la cual corresponde es también el quinto valor de la lista de ciudades (columna A).

De igual forma lo haremos en Excel ayudándonos de dos fórmulas:

• COINCIDIR: A través de esta función podemos identificar la posición relativa de un valor buscado dentro de un rango de datos (hacer click en el nombre para repasar el tutorial).
• INDICE: Esta función nos permite traer el valor de una matriz de datos al indicarle la posición de la fila y columna donde se encuentra el dato que necesitamos.  Puedes repasar cómo utilizar esta función haciendo click en el nombre.

Si no recuerdas cómo utilizar estas funciones, te recomiendo que las repases antes de seguir (haciendo click en los nombres te lleva al tutorial correspondiente).

Paso 1: Encontrar en qué fila de la lista de provincias-poblaciones se encuentra.

Vamos a armar el caso de las 5 mayores.  El caso de ciudades de menor población es igual.

Debemos encontrar entonces en qué fila se encuentra la población que coincida con la el valor de población buscado.

La primera parte de la fórmula es:

COINCIDIR(E8;$B$3:$B$26;0)

Donde:

• E8: Referencia con la cifra de población que queremos buscar.
• $B$3:$B$26: Rango donde debemos buscar el valor anterior.

El resultado de esta fórmula será la posición en el rango que ocupa el número buscado en el rango B3:B26.

Paso 2: Buscar en el rango de nombres.

Volviendo al caso de la figura, cuando buscamos la posición (con Coincidir) correspondiente a 3.058.309, nos devolverá la posición 5 ya que este es el quinto valor de arriba hacia abajo del rango B3:B26.

Ahora, debemos encontrar la quinta posición, pero de la columna A.  Para esto usamos la función INDICE.

Para el caso de la celda D8, la función queda:

=INDICE($A$3:$A$26;COINCIDIR(E8;$B$3:$B$26;0))

Ya podemos copiar la fórmula al resto de las celdas de la columna D y tendremos el listado con los 5 mayores completo.

De forma equivalente se completa el listado para las cinco ciudades de menor población.

Buscá más trucos en: http://www.trucosdeexcel.com/
Seguime por Twitter, usuario “trucosdeexcel”.
Mandá tus sugerencias o preguntas a: excelman@trucosdeexcel.com