En este artículo, aprenderemos cómo encontrar el n-ésimo más pequeño con los criterios y el n-ésimo más grande con los criterios de la tabla dada en Excel.
Guión:
En palabras simples, mientras se trabaja con números en números de datos, a veces se da una condición, es decir, cuando necesitamos buscar el quinto valor más alto dado. Puede realizar la solución a este problema fácilmente utilizando las funciones de Excel como se explica a continuación.
Nth más grande con criterios
¿Como resolver el problema?
Para este artículo, se nos pedirá que usemos la función GRANDE. Ahora haremos una fórmula con estas funciones. Aquí se nos da una tabla y necesitamos encontrar el enésimo valor más grande en el rango con los criterios dados.
Fórmula genérica:
{ = GRANDE (SI (c_range = "valor", rango), norte ) }
c_range : Rango de criterio
valor : valor de coincidencia de criterios
distancia : matriz de resultados
norte : nth más grande
Nota : No ponga llaves de forma manual. Esta es una fórmula de matriz, debe usar Ctrl + Mayús + Entrar en lugar de simplemente Enter donde devuelve #NUM! error.
Ejemplo:
Todos estos pueden resultar confusos de entender. Entonces, probemos esta fórmula ejecutándola en el ejemplo que se muestra a continuación.
Aquí tenemos los detalles del pedido con la fecha del pedido, la región y el precio del pedido.
Necesitamos averiguar el quinto precio de pedido más grande de la región este. Aquí, el rango se da como si se usara la herramienta de Excel de rango con nombre.
región (C3: C16)
precio (D3: D16)
En primer lugar, necesitamos encontrar el quinto valor más grande usando la función GRANDE y luego realizar la operación de suma sobre esos 5 valores. Ahora usaremos la siguiente fórmula para obtener la suma
Usa la fórmula:
{ = GRANDE (SI (región = G5, precio), F5)}
Explicación:
- La función SI verifica los criterios que coinciden con todos los valores en la región con el valor Este dado en la celda G5 y devuelve los valores VERDADEROS correspondientes del rango de precios.
= GRANDE ({58.41; 303.63; FALSO; 153.34; 82.84; FALSO; 520.01; FALSO; 177; FALSO; FALSO; 57.97; 97.72; FALSO}), F5)
- La función GRANDE toma la matriz que tiene todo el rango de precios como se muestra arriba y devuelve el quinto valor más grande de la matriz como se muestra en la siguiente instantánea.
Puede ver la fórmula en el cuadro de fórmula como se muestra en la instantánea anterior. Use Ctrl + Shift + Enter para obtener el resultado.
Como puede ver, la fórmula de matriz devuelve el quinto precio más grande $ 97.72 con la región Este.
También puede alimentar una matriz como una matriz, criterios entre comillas y un valor n directamente a la función en lugar de usar la referencia de celda o el rango con nombre.
Usa la fórmula:
{ = GRANDE (SI (C3: C16 = "Oeste", D3: D16), 3)}
Usar Ctrl + Mayús + Entrar
Puede ver que la fórmula funciona bien donde encuentra el enésimo valor más grande con criterios en la tabla.
Nth más bajo con criterio
¿Como resolver el problema?
Para este artículo, se nos pedirá que utilicemos la función PEQUEÑO. Ahora haremos una fórmula con estas funciones. Aquí se nos da una tabla y necesitamos encontrar el enésimo valor más pequeño en el rango con los criterios dados.
Fórmula genérica:
{ = PEQUEÑO (SI (c_range = "valor", rango), norte ) }
c_range : Rango de criterio
valor : valor de coincidencia de criterios
distancia : matriz de resultados
norte : nth más grande
Nota : No ponga llaves de forma manual. Esta es una fórmula de matriz, debe usar Ctrl + Mayús + Entrar en lugar de simplemente Enter donde devuelve #NUM! error.
Ejemplo:
Todos estos pueden resultar confusos de entender. Entonces, probemos esta fórmula ejecutándola en el ejemplo que se muestra a continuación.
Aquí tenemos los detalles del pedido con la fecha del pedido, la región y el precio del pedido.
Necesitamos averiguar el enésimo precio de pedido más pequeño de la región este. Aquí, el rango se da como si se usara la herramienta de Excel de rango con nombre.
región (C3: C16)
precio (D3: D16)
En primer lugar, necesitamos encontrar el quinto valor más pequeño o más bajo usando la función PEQUEÑO y luego buscar la operación que se realizará sobre los valores. Ahora usaremos la siguiente fórmula para obtener la suma
Usa la fórmula:
{ = PEQUEÑO (SI (región = G5, precio), F5)}
Explicación:
- La función SI verifica los criterios que coinciden con todos los valores en la región con el valor Este dado en la celda G5 y devuelve los valores VERDADEROS correspondientes del rango de precios.
= GRANDE ({58.41; 303.63; FALSO; 153.34; 82.84; FALSO; 520.01; FALSO; 177; FALSO; FALSO; 57.97; 97.72; FALSO}), F5)
- La función PEQUEÑA toma la matriz que tiene todo el rango de precios como se muestra arriba y devuelve el quinto valor más grande de la matriz como se muestra en la siguiente instantánea.
Puede ver la fórmula en el cuadro de fórmula como se muestra en la instantánea anterior. Usar Ctrl + Mayús + Entrar para obtener el resultado.
Como puede ver, la fórmula de matriz devuelve el quinto precio más pequeño $ 153.34 con la región Este.
También puede alimentar una matriz como una matriz, criterios entre comillas y un valor n directamente a la función en lugar de usar la referencia de celda o el rango con nombre.
Usa la fórmula:
{ = PEQUEÑO (SI (C3: C16 = "oeste", D3: D16), 3)}
Usar Ctrl + Mayús + Entrar
Puede ver que la fórmula funciona bien donde encuentra el enésimo valor más grande que tiene criterios en la tabla.
Aquí hay algunas notas de observación que se muestran a continuación.
Notas:
- La fórmula solo funciona con números.
- No ponga llaves de forma manual. Esta es una fórmula de matriz, debe usar Ctrl + Mayús + Entrar en lugar de simplemente Enter donde devuelve #NUM! error.
- ¡El valor de no puede ser mayor que el valor del número de criterios o devuelve # NUM! Error.
- SI los criterios no coinciden con la fórmula, devuelve un error.
- La matriz de argumentos debe tener la misma longitud que la función.
Espero que este artículo sobre cómo encontrar el n-ésimo más grande con criterios y el n-ésimo más pequeño con criterios en Excel sea explicativo. Explore más artículos sobre fórmulas de búsqueda en Excel aquí. Si te gustaron nuestros blogs, compártelo con tus amigos en Facebook. Y también puedes seguirnos en Twitter y Facebook. Nos encantaría saber de usted, háganos saber cómo podemos mejorar, complementar o innovar nuestro trabajo y hacerlo mejor para usted. Escríbanos al sitio de correo electrónico
Cómo utilizar la función SUMPRODUCT en Excel: Devuelve la SUMA después de la multiplicación de valores en varias matrices en Excel.
SUM si la fecha está entre : Devuelve la SUMA de valores entre fechas o períodos dados en Excel.
Suma si la fecha es mayor que la fecha dada: Devuelve la SUMA de los valores posteriores a la fecha o período especificado en Excel.
2 formas de sumar por mes en Excel: Devuelve la SUMA de valores dentro de un mes específico dado en Excel.
Cómo sumar varias columnas con condición: Devuelve la SUMA de valores en varias columnas con condición en Excel
Cómo usar comodines en Excel : Cuente las celdas que coinciden con las frases usando los comodines en Excel
articulos populares
50 atajos de Excel para aumentar su productividad : Acelere su tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel.
Cómo usar tLa función BUSCARV en Excel : Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.
Cómo usar la función CONTAR.SI en Excel : Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.
Cómo usar la función SUMIF en Excel : Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.
