Fórmula de Excel para encontrar los 3, 5, 10 valores principales en una columna o fila

Fórmula de Excel para encontrar los 3, 5, 10, etc. valores principales

Para obtener los N valores más altos en una lista, use las funciones GRANDE y FILAS juntas de esta manera:

GRANDE(valoresFILAS(A$2:A2))

Por ejemplo, para encontrar los N valores principales en B2:B12, ingrese la siguiente fórmula en la celda superior donde desea que aparezcan los resultados (D2) y luego arrástrela a través de tantas celdas como sea necesario.

=LARGE($B$2:$B$12, ROWS(A$2:A2))

tirar 3 valores principalescopie la fórmula en 3 celdas.

Llegar 5 valores principalescopia la misma fórmula en 5 celdas.

Encontrar 10 valores principales en una columna, copie la fórmula en 10 celdas.

Cómo funciona esta fórmula:

La función GRANDE compara todos los valores numéricos en un rango proporcionado para el 1^calle argumento (formación) y devuelve el valor más grande según la posición especificada en el 2^{Dakota del Norte} argumento (k).

La función FILAS con una referencia de rango en expansión como FILAS(A$2:A2) genera el k valores automáticamente. Para crear dicha referencia, bloqueamos la coordenada de la fila en la primera celda con una referencia absoluta (A$2) y usamos una referencia relativa para la última celda (A2).

En la celda superior donde ingresa la fórmula (D2 en este ejemplo), FILAS(A$2:A2) genera 1 para k, diciéndole a LARGE que devuelva el valor máximo. Cuando se copia en las celdas siguientes, la referencia del rango se expande en 1 fila, lo que provoca que k argumento para incrementar en 1. Por ejemplo, en D3, la referencia cambia a A$2:A3. La función FILAS cuenta el número de filas en A$2:A3 y devuelve 2 para kpor lo que la función GRANDE genera el 2^{Dakota del Norte} mayor valor.

Tenga en cuenta que usamos A$2:A2 solo por conveniencia porque nuestros datos comienzan en la fila 2. Puede usar cualquier letra de columna y cualquier número de fila para la referencia del rango en expansión, digamos A$1:A1 o C$1:C1.

Fórmula de Excel para obtener los valores inferiores 3, 5, 10, etc. en Excel

Para encontrar los N valores más bajos en una lista, la fórmula genérica es:

PEQUEÑO(valoresFILAS(A$2:A2))

En este caso, usamos la función PEQUEÑA para extraer el k-ésimo valor más pequeño y la función FILAS con una referencia de rango en expansión para generar el k número.

Por ejemplo, para encontrar los N valores inferiores en la siguiente tabla, use esta fórmula:

=SMALL($B$2:$B$12, ROWS(A$2:A2))

Ingréselo en la celda superior y luego arrastre hacia abajo tantas celdas como valores desee obtener.

Fórmula de Excel para encontrar los N valores principales seguidos

Si sus datos están organizados horizontalmente en filas, puede utilizar las siguientes fórmulas genéricas para encontrar los valores más altos o más bajos:

Obtenga los mejores valores seguidos:

GRANDE(valoresCOLUMNAS(A$2:A2))

Obtenga los valores inferiores en una fila:

PEQUEÑO(valoresCOLUMNAS(A$2:A2))

La lógica de las fórmulas es la misma que en el ejemplo anterior con la diferencia de que se utiliza la función COLUMNAS en lugar de FILAS para “alimentar” el k valor a GRANDE y PEQUEÑO.

Digamos que su tabla enumera los resultados de 5 rondas para cada participante, como se muestra en la imagen a continuación. Su objetivo es encontrar los 3 valores principales en cada fila.

Para hacerlo, ingrese la siguiente fórmula en la celda superior derecha (B10 en nuestro caso) y luego arrástrela hacia abajo y hacia la derecha:

=LARGE($B2:$F2, COLUMNS($A1:A1))

Para que la fórmula se copie correctamente, utilizamos una referencia mixta para la formación argumento de GRANDE que bloquea solo las coordenadas de la columna ($B2:$F2).

Para encontrar los 3 valores inferiores en cada fila, puede usar una fórmula PEQUEÑA análoga:

=SMALL($B$2:$H$2, COLUMNS($A2:A2))

Cómo obtener coincidencias de los valores N más grandes

En una situación en la que desee recuperar datos relacionados con los valores principales, utilice la fórmula clásica INDEX MATCH junto con GRANDE como valor de búsqueda:

ÍNDICE(matriz_retornoCOINCIDIR(GRANDE(matriz_buscada, k), matriz_buscada0))

Dónde:

• matriz_devolución es un rango del cual extraer datos asociados (coincidencias).
• matriz_de_búsqueda es un rango donde buscar los valores más grandes.
• k Es la posición de mayor valor a buscar.

En la siguiente tabla, puede encontrar los 3 valores principales utilizando el siguiente enfoque.

Para extraer los 3 mejores resultados, la fórmula en E3 es:

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

Debido a que los rangos se escriben en celdas separadas, la función FILAS no es necesaria en este caso; simplemente hacemos referencia a la celda que contiene el k valor (D3).

Para recuperar los nombres, la fórmula en F3 es:

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

Donde A2:A12 son los nombres (return_array), B2:B12 son los resultados (lookup_array) y D3 es la posición desde mayor (k).

De manera similar, puedes obtener coincidencias de valores N inferiores. Para esto, simplemente use la función PEQUEÑA en lugar de GRANDE.

Para obtener los 3 últimos resultados, la fórmula en E3 es:

=SMALL($B$2:$B$12, D3)

Para extraer los nombres, la fórmula en F3 es:

=INDEX($A$2:$A$12, MATCH(SMALL($B$2:$B$12, D3), $B$2:$B$12, 0))

Cómo funciona esta fórmula:

La función GRANDE obtiene el k-ésimo valor más grande y lo pasa al valor de búsqueda argumento de PARTIDO.

Por ejemplo, en F3, estamos buscando el 1^calle valor más grande, que es 5,57. Entonces, después de reemplazar la función GRANDE con su salida, la fórmula se reduce a:

=INDEX($A$2:$A$12, MATCH(5.57, $B$2:$B$12, 0))

La función COINCIDIR determina la posición relativa de 5,57 en B2:B12, que es 9. Este número va al número_fila argumento de ÍNDICE, simplificando aún más la fórmula:

=INDEX($A$2:$A$10, 9)

Finalmente, la función ÍNDICE devuelve el valor del 9^th fila en el rango A2:A12, que es “Nick”.

Cómo encontrar valores superiores con duplicados

El enfoque utilizado en el ejemplo anterior funciona bien para un conjunto de datos que solo tiene números únicos en la columna de búsqueda. Los duplicados pueden dar lugar a resultados erróneos. Por ejemplo, si el 1^calle y 2^{Dakota del Norte} Los números más grandes resultan ser iguales, la función GRANDE devolverá el mismo valor para cada uno, lo que se espera por diseño. Pero debido a que COINCIDIR devuelve la posición de la primera coincidencia encontrada, la fórmula generará la primera coincidencia dos veces, como se muestra en la imagen a continuación:

Para “romper empates” y solucionar el problema, necesitamos una función COINCIDIR más sofisticada:

=INDEX($A$2:$A$12, MATCH(1, ($B$2:$B$12=LARGE($B$2:$B$12, D2)) * (COUNTIF(F$1:F1, $A$2:$A$12)=0), 0))

En todas las versiones excepto Excel 365, solo funciona como fórmula matricial. Entonces, recuerde presionar Ctrl + Mayús + Intro para completar la fórmula correctamente.

Cómo funciona esta fórmula:

Aquí, la función COINCIDIR está configurada para buscar el número 1, que es el valor de búsqueda. La matriz de búsqueda se construye utilizando la siguiente lógica:

En primer lugar, todos los números se comparan con el valor devuelto por GRANDE:

$B$2:$B$12=LARGE($B$2:$B$12, D2)

En segundo lugar, la función CONTAR.SI con una referencia de rango en expansión verifica si un elemento determinado ya está en la lista superior. Preste atención a que el rango en expansión comienza en la fila anterior (F1) para evitar una referencia circular.

COUNTIF(F$1:F1, $A$2:$A$12)=0

El resultado de las operaciones anteriores son dos matrices de valores VERDADEROS y FALDOS, que se multiplican entre sí. Por ejemplo, en F3 tenemos las siguientes matrices:

{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE} * {TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

En la primera matriz, hay dos valores VERDADEROS que corresponden a 5,57 (clasificación 1 y 2): elementos 3 y 9. Pero en la segunda matriz, el elemento 3 es FALSO porque este nombre (Brian) ya está en la lista. La operación de multiplicación cambia los valores lógicos VERDADERO y FALSO a 1 y 0, respectivamente. Y como multiplicar por cero siempre da cero, sólo el elemento 9 “sobrevive”:

{0;0;0;0;0;0;0;0;1;0;0}

La función COINCIDIR busca “1” en esta matriz y devuelve su posición relativa (9), que es Nick.

Cómo encontrar valores superiores en Excel con criterios

Para obtener los valores principales que coincidan con cierta condición, exprese sus criterios con la ayuda de la función SI y anídelos dentro de las fórmulas analizadas en las secciones anteriores.

Como ejemplo, busquemos los 3 primeros resultados de un grupo determinado. Para hacerlo, ingresamos el grupo objetivo en F1 y escribimos los rangos 1 a 3 en E5:E7 (consulte la imagen a continuación).

Extraer 3 mejores resultadosingrese la siguiente fórmula en F5 y arrástrela a través de F7:

=LARGE(IF($B$2:$B$12=$F$1, $C$2:$C$12), E5)

Para garantizar que la función GRANDE solo procese los resultados dentro del grupo objetivo, creamos una declaración IF que compara una lista de grupos con F1.

Para obtener el nombrescopie esta fórmula en G5 a G7:

=INDEX($A$2:$A$12, MATCH(LARGE(IF($B$2:$B$12=$F$1, $C$2:$C$12), E5), IF($B$2:$B$12=$F$1, $C$2:$C$12), 0))

Aquí utilizamos la ya familiar combinación INDEX MATCH LARGE pero la ampliamos con dos pruebas lógicas:

GRANDE(IF($B$2:$B$12=$F$1, $C$2:$C$12), E5) – IF comprueba si un grupo coincide con el objetivo en F1. Las coincidencias entran en la matriz de la cual la función GRANDE elige el valor más alto según el rango en E5. El resultado se convierte en el valor de búsqueda para COINCIDIR.

IF($B$2:$B$12=$F$1, $C$2:$C$12) – IF filtra nuevamente los grupos irrelevantes, por lo que solo los resultados que pertenecen al grupo objetivo llegan a la matriz de búsqueda, donde MATCH busca para el valor de búsqueda.

Estos son fórmulas de matriz que se debe ingresar presionando Ctrl + Mayús + Intro simultáneamente. Debido a la capacidad de Excel 365 para manejar matrices de forma nativa, es suficiente presionar la tecla Enter en esta versión.

En Excel 365, puede realizar las mismas pruebas lógicas dentro de BUSCARX y obtener resultados idénticos:

=XLOOKUP(LARGE(IF($B$2:$B$12=$F$1, $C$2:$C$12), E5), IF($B$2:$B$12=$F$1, $C$2:$C$12), $A$2:$A$12)

Cómo FILTRAR valores superiores o inferiores en Excel

En Excel 365, existe una forma más sencilla de encontrar los valores N más grandes mediante el uso de nuevas funciones de matriz dinámica, como ORDENAR y FILTRAR.

FILTRO DE CLASIFICACIÓN(datos, números>=GRANDE(números, norte)), índice_clasificación-1)

Dónde:

• Datos es la tabla de origen excluyendo los encabezados de columna.
• Números son los valores numéricos a clasificar.
• norte es el número de entradas principales que se extraerán.
• índice_clasificación el número de la columna por la que se va a ordenar.

Por ejemplo, para filtrar los 3 registros principales de nuestro conjunto de datos, la fórmula es la siguiente:

=SORT(FILTER(A2:B12, B2:B12>=LARGE(B2:B12, 3)), 2, -1)

En este caso, configuramos norte a 3 porque estamos extrayendo los 3 mejores resultados y índice_clasificación a 2 ya que los números están en la segunda columna.

Lo bueno de esta fórmula es que solo necesita ingresarla en una celda y Excel automáticamente distribuye los resultados en tantas celdas como sea necesario (este concepto se llama rango de distribución).

Para extraer los 3 resultados más bajos, la fórmula es:

=SORT(FILTER(A2:B12, B2:B12<=SMALL(B2:B12, 3)), 2, 1)

Cómo funcionan estas fórmulas:

Aquí, utilizamos la función FILTRO para filtrar los datos de origen según los criterios incluidos en el 2^{Dakota del Norte} argumento.

Para obtener los valores superiores, construimos la expresión lógica que verifica si un número dado es mayor o igual que el enésimo número más alto de la lista (el tercer número más grande en nuestro caso): B2:B12>=LARGE(B2:B12, 3).

Para obtener los valores inferiores, verificamos si un número es menor o igual que el tercer número más bajo: B2:B12<=SMALL(B2:B12, 3).

El resultado de la prueba lógica es una matriz de valores VERDADERO y FALSO, que se utiliza para filtrar; solo las entradas correspondientes a VERDADERO entran en la matriz final:

=SORT({"Aiden",5.51;"Brian",5.57;"Nick",5.57}, 2, -1)

Como puede ver arriba, la función FILTRO genera los elementos en el mismo orden en que aparecen en el rango de origen. Para ordenar los resultados, colocamos la fórmula FILTRO dentro de la función ORDENAR y configuramos el Orden de clasificación argumento para -1 (descendente) o 1 (ascendente).

Libro de práctica para descargar.

Fórmulas de Excel para encontrar valores superiores o inferiores (archivo .xlsx)