Cómo sumar N superiores o inferiores valores con criterios
En el artículo anterior aprendimos cómo sumar los valores N superiores o inferiores.
En este artículo intentamos sumar los valores N superiores o inferiores con un criterio.
Suma de valores TOP N con criterios
¿Cómo solucionar el problema?
Para este artículo, se nos pedirá que usemos el enlace: / suma-excel-sumproducto-función [función SUMPRODUCTO]. Ahora haremos una fórmula con estas funciones. Aquí se nos da un rango y un criterio. Necesitamos obtener los 5 valores principales del rango y obtener la suma de los valores según los criterios dados.
_Fórmula genérica: _
= SUMPRODUCT ( LARGE ( (list = criteria) * (range), { 1 , 2 , .... ,n } } )
lista: lista de criterios Criterios: criterios para hacer coincidir rango: rango de valores valores: números separados con comas como si desea encontrar los 3 valores principales, use \ {1, 2, 3}.
Ejemplo:
Aquí tenemos los valores del conjunto de datos de A1: D50.
En primer lugar, necesitamos encontrar los cinco valores principales usando la función LARGE que coincide con la ciudad «Boston» y luego realizar la operación de suma sobre esos 5 valores. Ahora usaremos la siguiente fórmula para obtener la suma _Utilice la fórmula: _
= SUMPRODUCT ( LARGE ( ( City = "Boston" ) * (quantity) , { 1 , 2 , 3 , 4 , 5 } ) )
_Explicación: _
-
El «Boston» de la ciudad coincide con el rango de ciudades mencionado. Esto devuelve una matriz de verdadero y falso.
-
vínculo: / fórmulas-lógicas-excel-función-grande [La función GRANDE]devuelve los 5 primeros valores numéricos del rango de cantidades y devuelve la matriz a la función SUMPRODUCTO.
SUMPRODUCTO \ {193, 149, 138, 134, 123} * La función SUMPRODUCTO obtiene una matriz de los 5 valores principales, con una matriz de los 5 números principales que devuelven la SUMA de esos números.
Aquí, el rango de ciudad y cantidad se da como el rango con nombre. Presione Entrar para obtener la SUMA de los 5 números principales.
Como puede ver en la instantánea anterior, esa suma es 737. La suma de los valores 193 + 149 + 138 + 134 + 123 = 737.
Puede verificar los valores anteriores en el conjunto de datos usando la opción de filtro de Excel.
Aplique el filtro al encabezado Ciudad y cantidad y haga clic en el botón de flecha en el encabezado de la ciudad que aparece. Siga los pasos que se muestran a continuación.
Pasos:
-
Seleccione la celda del encabezado Ciudad. Aplicar filtro usando el atajo Ctrl + Shift + L. Haga clic en la flecha que aparece como una opción de filtro.
-
Seleccione la opción (Seleccionar todo).
-
Seleccione solo la ciudad de Boston.
-
Seleccione el encabezado de cantidad ahora.
-
Ordene la lista de mayor a menor y podrá ver los 5 valores principales que calculamos mediante la fórmula.
Como puede ver en el gif anterior, los 5 valores que coinciden con los criterios dados. Esto también significa que la fórmula funciona bien para obtener el recuento de estos valores
N números GRANDES
El proceso anterior se utiliza para calcular la suma de algunos números desde la parte superior. Pero para calcular n (gran) número de valores en un rango largo.
Usa la fórmula:
= SUMPRODUCT ( LARGE ( ( City = "Boston" ) * (quantity), ROW ( INDIRECT ( "1:10" ) )
Aquí generamos la suma de los 10 valores principales obteniendo una matriz de 1 a 10 \ {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} usando las funciones de Excel ROW & INDIRECT.
Aquí tenemos la suma de los 10 números principales que da como resultado 1147.
Suma de los valores N inferiores con criterios
¿Cómo solucionar el problema?
Para este artículo, se nos pedirá que usemos el enlace: / suma-excel-sumproducto-función [función SUMPRODUCTO]. Ahora haremos una fórmula con estas funciones. Aquí se nos da un rango y necesitamos 5 valores inferiores en el rango y obtener la suma de los valores.
Fórmula genérica:
{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , { 1 , 2 , 3 , 4 , 5 } ) ) }
Rango: rango de valores Valores: números separados por comas como si desea encontrar los 3 valores inferiores, use \ {1, 2, 3}.
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 un rango de valores de A1: D50.
Aquí, el rango de ciudad y cantidad se da usando la herramienta de Excel de rango con nombre.
En primer lugar, necesitamos encontrar los cinco valores inferiores utilizando la función PEQUEÑA que coincide con los criterios y luego realizar la operación de suma sobre esos 5 valores. Ahora usaremos la siguiente fórmula para obtener la suma Use la fórmula:
{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , { 1 , 2 , 3 , 4 , 5 } ) ) }
NO use llaves manualmente. Las llaves se aplican usando Ctrl Shift + Enter en lugar de solo Enter.
Explicación:
-
enlace: / fórmulas-estadísticas-excel-pequeña-función [PEQUEÑA]
La función con función SI devuelve los 5 valores numéricos inferiores que coinciden con la ciudad «Boston» y devuelve la matriz a la función SUMA.
SUMA (\ {23, 27, 28, 28, 30}))
-
La función SUMA obtiene la matriz de los 5 valores inferiores, que tiene una matriz de los 5 números inferiores devuelve la SUMA de los números utilizados con CTRL + MAYÚS + ENTRAR.
Aquí, el rango de ciudad y cantidad se da como el rango con nombre. Presione Ctrl + Shift + Enter para obtener la SUMA de los 5 números inferiores, ya que esta es una fórmula de matriz.
Como puede ver en la instantánea anterior, esa suma es 136.
El proceso anterior se usa para calcular la suma de algunos números desde la parte inferior. Pero para calcular n (gran) número de valores en un rango largo.
Usa la fórmula:
{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , ROW ( INDIRECT ("1:10") ) ) ) }
NO use las llaves manualmente. Use Ctrl + Shift + Enter en lugar de usar Enter.
Aquí generamos la suma de los 10 valores inferiores obteniendo una matriz de 1 a 10 \ {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} usando las funciones de Excel ROW & INDIRECT.
Aquí tenemos la suma de los 10 números inferiores que resultarán en 155.
Aquí hay algunas notas de observación que se muestran a continuación.
Notas:
-
La fórmula solo funciona con números.
-
La fórmula solo funciona cuando no hay duplicados en la tabla de búsqueda. La función SUMPRODUCTO considera valores no numéricos (como texto abc) y valores de error (como # ¡NUM!, # ¡NULO!) Como valores nulos.
-
La función SUMPRODUCTO considera el valor lógico VERDADERO como 1 y Falso como 0.
-
La matriz de argumentos debe tener la misma longitud que la función.
Espero que este artículo sobre cómo devolver la suma de los 5 valores principales o los 5 valores inferiores con criterios en Excel sea explicativo. Encuentre más artículos sobre las funciones SUMPRODUCT aquí. Comparta su consulta a continuación en el cuadro de comentarios. Te atenderemos.
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 a [email protected]
Artículos relacionados
link: / summing-excel-sumproduct-function [Cómo usar la función SUMPRODUCT en Excel]: Devuelve la SUMA después de la multiplicación de valores en múltiples matrices en Excel.
link: / summing-sum-if-date-is-between [SUM si la fecha está entre]: Devuelve la SUMA de los valores entre fechas o períodos dados en Excel.
link: / sumando-suma-si-la-fecha-es-mayor-que-la-fecha-dada [Suma si la fecha es mayor que la fecha dada]: * Devuelve la SUMA de los valores después de la fecha o período dado en Excel.
link: / summing-2-way-to-sum-by-month-in-excel [2 formas de sumar por mes en Excel]: * Devuelve la SUMA de valores dentro de un mes específico dado en Excel.
link: / summing-how-to-sum-multiple-columns-with-condition [Cómo sumar múltiples columnas con condición]: Devuelve la SUMA de valores en múltiples columnas que tienen condición en Excel `link: / tips-excel- comodines [Cómo usar comodines en excel *] `: Cuente las celdas que coinciden con las frases usando los comodines en excel
Artículos populares
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-incrementa-your-productividad [50 Excel Shortcut to Increase Your Productivity]
link: / excel-generals-how-to-edit-a-dropdown-list-in-microsoft-excel [Editar una lista desplegable]
link: / excel-range-name-absolute-reference-in-excel [Referencia absoluta en Excel]
link: / tips-conditional-formatting-with-if-statement [Si con formato condicional]
enlace: / fórmulas-lógicas-si-función-con-comodines [Si con comodines]
enlace: / lookup-formulas-vlookup-by-date-in-excel [Vlookup por fecha]
link: / tips-pulgadas-a-pies [Convertir pulgadas a pies y pulgadas en Excel 2016]
link: / excel-text-edit-and-format-join-first-and-last-name-in-excel [Unir nombre y apellido en excel]
enlace: / contando-recuento-celdas-que-coinciden-con-a-o-b [Cuenta celdas que coinciden con A o B]