Función GRANDE de Excel para obtener el valor n-ésimo más alto

Este artículo lo guiará sobre cómo usar la función GRANDE en Excel con muchos ejemplos de fórmulas.

Al analizar un conjunto de números, a menudo tiene sentido encontrar los más grandes. Obtener el valor más alto es muy fácil con la función MAX. Cuando se trata de apuntar a un valor más grande específico, digamos el segundo o el tercer número más grande en un conjunto de datos, la función GRANDE es útil.

Función GRANDE de Excel

La función GRANDE en Excel se usa para devolver el valor n-ésimo más grande de un conjunto de datos numéricos. Por ejemplo, puede calcular la puntuación más alta, el segundo pedido más grande, el resultado del tercer lugar, etc.

La sintaxis consta de dos argumentos, ambos obligatorios:

GRANDE(matriz, k)

Dónde:

• Formación – un rango o una matriz donde buscar el valor más grande.
• k – la posición desde la más alta para devolver, es decir, el k-ésimo valor más grande en un conjunto de datos.

GRANDE se clasifica en Funciones estadísticas. Está disponible en todas las versiones de Excel para Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 y versiones anteriores.

3 cosas que debe saber sobre la función GRANDE

Antes de llegar a cosas más prácticas y comenzar a construir nuestras propias fórmulas, preste atención a 3 hechos simples que explican lo esencial:

1. La función GRANDE procesa solo valores numéricos. Celdas en blanco, textoy lógico los valores son ignorados.
2. Si la matriz contiene alguna erroresse devuelve un error.
3. En caso de que la matriz contenga n valores, LARGE(array,1) devolverá el valor maximin y LARGE(array,n) devolverá el valor mínimo.

Fórmula GRANDE básica

En su forma básica, una fórmula GRANDE en Excel es muy fácil de construir. Para el primer argumento, proporciona un rango de valores numéricos. En el segundo argumento, define la posición desde la más grande para devolver.

En la tabla de muestra a continuación, supongamos que desea conocer la segunda puntuación más alta. Esto se puede hacer con la siguiente fórmula:

=LARGE(B2:B10, 2)

Cómo usar la fórmula GRANDE en Excel – ejemplos

Y ahora, veamos casos de uso más específicos y veamos cómo la función GRANDE podría ser útil.

Cómo obtener los mejores valores de N en Excel

Para obtener los valores más grandes de 3, 5, 10, etc. con una sola fórmula, siga estos pasos:

1. Escriba las posiciones de interés en celdas separadas. Estos números se utilizarán como valores de k.
2. Cree una fórmula GRANDE aplicando una referencia de rango absoluto para la matriz ($ B $ 2: $ B $ 10 en nuestro caso) y una referencia de celda relativa para k (D3).
3. Ingrese la fórmula en la celda superior y luego arrástrela hacia las celdas inferiores. ¡Hecho!

Como ejemplo, vamos a encontrar los 3 mejores puntajes en la tabla a continuación. Para ello digitamos los números 1, 2 y 3 en D3, D4 y D5, respectivamente, e ingresamos la siguiente fórmula en E3:

=LARGE($B$2:$B$10, D3)

Arrástrelo a través de E4 y obtendrá este resultado:

En lugar de escribir las posiciones en la hoja, puede usar la función FILAS con un rango de expansión referencia para generar los valores k automáticamente como se explica en la fórmula de Excel para encontrar los valores N principales en una lista.

=LARGE($B$2:$B$10, ROWS(B$2:B2))

Cómo sumar o promediar los valores N más grandes

A suma valores n principales en un conjunto de datos, puede usar LARGE junto con SUMPRODUCT o SUM de esta manera:

SUMAPRODUCTO(GRANDE(matriz, {1, …, n }))

O

SUMA(GRANDE(matriz, {1, …, n}))

A promedio los valores n más altos, combine las funciones PROMEDIO y GRANDE:

PROMEDIO(GRANDE(matriz, {1, …, n}))

Para ver cómo funciona en la práctica, busquemos un promedio de las 3 puntuaciones más altas en nuestra tabla de muestra. Para ello, estamos utilizando esta fórmula:

=AVERAGE(LARGE(B2:B10, {1,2,3}))

Para sumar los 3 puntajes más altos, la fórmula es:

=SUM(LARGE(B2:B10, {1,2,3}))

Nota. En caso de que use un rango en lugar de una constante de matriz para k, debe presionar Ctrl + Shift + Enter para convertirlo en una fórmula de matriz. En Excel 365 que admite matrices dinámicas, todas las fórmulas se pueden completar como de costumbre presionando la tecla Intro.

Cómo funcionan estas fórmulas:

Normalmente, la función GRANDE devuelve un único valor basado en el número k. En estas fórmulas, proporcionamos una constante de matriz como {1,2,3} para el argumento k que lo obliga a devolver una matriz de valores. Esa matriz va a la función externa para sumar o promediar.

Obtener datos asociados con el valor n-ésimo más grande

Para recuperar información relacionada con los valores más grandes, simplemente anide la función GRANDE en la fórmula canónica ÍNDICE COINCIDIR:

ÍNDICE(return_array, COINCIDIR(LARGE(lookup_array, n), lookup_array, 0))

Dónde:

• Retorno_matriz es un rango desde el cual extraer coincidencias.
• Lookup_array es un rango de números para clasificar desde el más alto.
• norte es la posición del mayor valor a buscar.

Por ejemplo, para obtener el nombre de un estudiante al que le fue mejor en los exámenes, ingrese esta fórmula en F3:

=INDEX($A$2:$A$10, MATCH(LARGE($B$2:$B$10, $D3), $B$2:$B$10, 0))

Donde A2:A10 es la matriz de retorno (nombres), B2:B10 es la matriz de búsqueda (puntuaciones) y D3 es la posición desde la más grande.

Para saber quién tiene el segundo y tercer puntaje más alto, copie el formulario en F4 y F5:

Notas:

• Esta solución funciona bien para valores únicos. Si su conjunto de datos contiene números duplicados, pueden ocurrir «empates» en la clasificación, lo que producirá resultados incorrectos. Para evitar que esto suceda, utilice una fórmula más compleja para manejar los empates.
• En Excel 365, puede usar nuevas funciones de matrices dinámicas para encontrar una solución mucho más simple que resuelva un problema de vínculos automáticamente. Para obtener detalles completos, consulte Cómo filtrar los valores N principales en Excel.

Fórmula GRANDE para ordenar números de forma descendente

Para ordenar rápidamente una lista de números en Excel 365, puede usar la nueva función ORDENAR. En Excel 2019, 2016 y versiones anteriores que no admiten matrices dinámicas, tenemos que confiar en la función GRANDE para ordenar de manera descendente y PEQUEÑA para ordenar de manera ascendente.

Para este ejemplo, ordenaremos los números en A2:A10 de mayor a menor. Para hacerlo, nuevamente necesitaremos la función FILAS con una referencia de rango en expansión para incrementar el argumento k en 1 con cada fila:

=LARGE($A$2:$A$10, ROWS(A$2:A2))

La fórmula anterior va a la celda superior (C2). Y luego, lo arrastra a través de tantas celdas como números haya en la lista original (C2: C10 en nuestro caso):

GRAN fórmula para fechas y horas

Como sabe, las fechas y horas en Excel son valores numéricos: las fechas se almacenan como números enteros y las horas como decimales. ¿Qué significa eso para nosotros? La función GRANDE calcula los valores de fecha y hora exactamente de la misma manera que lo hace con los números. En otras palabras, ¡la fórmula que usaste para los números también funcionará para fechas y horas!

Suponiendo que tiene una lista de fechas en B2:B10, la siguiente fórmula devolverá el N fechas más recientesdependiendo de en cuántas celdas lo copie:

=LARGE($B$2:$B$10, ROWS(B$2:B2))

La misma fórmula también puede encontrar el las 3 veces mas largas:

Obtenga una fecha futura más cercana a hoy o una fecha especificada

Este ejemplo muestra un uso más específico de la función GRANDE de Excel con fechas.

De una lista de fechas en B2:B10, suponga que desea devolver una fecha futura más cercano a hoy. Para realizar la tarea, usaremos las funciones CONTAR.SI y HOY juntas para calcular un valor para el argumento k de GRANDE:

=LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&TODAY()))

Para encontrar la penúltima fecha, la fórmula es:

=LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&TODAY())-1)

Para buscar una fecha que viene justo después de una fecha dadaingrese la fecha objetivo en alguna celda (E3 en este ejemplo) y concatene esa referencia de celda en los criterios de COUNTIF:

=LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&E1))

En una situación en la que no se encuentra una fecha que coincida con sus criterios, puede usar la función IFERROR como un «envoltorio» para detectar un error y reemplazarlo con el texto que considere adecuado:

=IFERROR(LARGE($B$2:$B$10, COUNTIF($B$2:$B$10, ">"&E1)), "Not found")

Cómo funciona esta fórmula:

En esencia, utiliza la función CONTAR.SI para contar cuántas fechas en la lista son mayores que hoy o una fecha específica. Ese recuento es el valor k que necesita la función GRANDE.

Al momento de escribir, la fecha de hoy era el 7 de octubre de 2020. La función CONTAR.SI con «>»&HOY() para criterios determinó que en B2:B10 hay 4 fechas posteriores al 7 de octubre de 2020. Es decir, la cuarta fecha más grande de la lista es la fecha futura más cercana que estamos buscando. Entonces, conectamos COUNTIF en el segundo argumento de LARGE y obtenemos el resultado deseado:

=LARGE($B$2:$B$10, 4)

La siguiente fecha es la tercera fecha más grande en nuestro caso. Para obtenerlo, restamos 1 del resultado de COUNTIF.

La función GRANDE de Excel no funciona

¡Una fórmula GRANDE puede arrojar un #NUM! error debido a las siguientes razones:

• La matriz proporcionada está vacía o no contiene un solo valor numérico.
• El valor de k es un número negativo.
• El valor k es mayor que el número de valores en la matriz.

Así es como se usa la función GRANDE en Excel para encontrar los valores más altos en un conjunto de datos. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!

Cuaderno de ejercicios para descargar

(archivo .xlsx)