Comment Somme Haut ou Bas valeurs N avec critères
Dans l’article précédent, nous avons appris comment additionner les N valeurs supérieures ou inférieures.
Dans cet article, nous essayons de résumer les N valeurs supérieures ou inférieures avec un critère.
Somme des valeurs TOP N avec critères
Comment résoudre le problème?
Pour cet article, nous devrons utiliser la fonction link: / summing-excel-sumproduct-function [fonction SUMPRODUCT]. Nous allons maintenant faire une formule à partir de ces fonctions. Ici, on nous donne une fourchette et un critère. Nous devons obtenir les 5 premières valeurs de la plage et obtenir la somme des valeurs en fonction des critères donnés.
_Formule générique: _
= SUMPRODUCT ( LARGE ( (list = criteria) * (range), { 1 , 2 , .... ,n } } )
list: liste de critères Critères: critères à associer range: plage de valeurs valeurs: nombres séparés par des virgules comme si vous souhaitez trouver les 3 premières valeurs, utilisez \ {1, 2, 3}.
Exemple:
Ici, nous avons les valeurs de l’ensemble de données de A1: D50.
Tout d’abord, nous devons trouver les cinq premières valeurs à l’aide de la fonction LARGE qui correspond à la ville « Boston », puis effectuer l’opération de somme sur ces 5 valeurs. Nous allons maintenant utiliser la formule suivante pour obtenir la somme _Utilisez la formule: _
= SUMPRODUCT ( LARGE ( ( City = "Boston" ) * (quantity) , { 1 , 2 , 3 , 4 , 5 } ) )
_Explication: _
-
« Boston » de City correspond à la gamme City mentionnée. Cela renvoie un tableau de vrai et faux.
-
lien: / formule-logique-excel-large-fonction [La fonction LARGE]retourne les 5 premières valeurs numériques de la plage de quantité et renvoie le tableau à la fonction SOMMEPROD.
SUMPRODUCT \ {193, 149, 138, 134, 123} * La fonction SUMPRODUCT obtient un tableau des 5 premières valeurs, avec un tableau des 5 premiers nombres renvoyant la SOMME de ces nombres.
Ici, la plage Ville et quantité est donnée comme plage nommée. Appuyez sur Entrée pour obtenir la somme des 5 premiers nombres.
Comme vous pouvez le voir dans l’instantané ci-dessus, cette somme est de 737. La somme des valeurs 193 + 149 + 138 + 134 + 123 = 737.
Vous pouvez vérifier les valeurs ci-dessus dans l’ensemble de données à l’aide de l’option de filtre Excel.
Appliquez le filtre à l’en-tête Ville et quantité et cliquez sur le bouton fléché de l’en-tête de ville qui apparaît. Suivez les étapes ci-dessous.
Étapes:
-
Sélectionnez la cellule d’en-tête Ville. Appliquer le filtre à l’aide du raccourci Ctrl + Maj + L. Cliquez sur la flèche qui apparaît comme une option de filtre.
-
Sélectionnez l’option (Tout sélectionner).
-
Sélectionnez uniquement la ville de Boston.
-
Sélectionnez maintenant l’en-tête de quantité.
-
Triez la liste de la plus grande à la plus petite et vous pouvez afficher toutes les 5 premières valeurs que nous avons calculées à l’aide de la formule.
Comme vous pouvez le voir dans le gif ci-dessus, toutes les 5 valeurs qui correspondent aux critères donnés. Cela signifie également que la formule fonctionne très bien pour obtenir le nombre de ces valeurs
GRANDS N nombres
Le processus ci-dessus est utilisé pour calculer la somme de quelques nombres à partir du haut. Mais pour calculer pour n (grand) nombre de valeurs dans une longue plage.
Utilisez la formule:
= SUMPRODUCT ( LARGE ( ( City = "Boston" ) * (quantity), ROW ( INDIRECT ( "1:10" ) )
Ici, nous générons la somme des 10 premières valeurs en obtenant un tableau de 1 à 10 \ {1; 2; 3; 4; 5; 6; 7 ; 8; 9; 10} à l’aide des fonctions Excel ROW & INDIRECT.
Ici, nous avons la somme des 10 premiers nombres qui donne 1147.
Somme des N dernières valeurs avec critères
Comment résoudre le problème?
Pour cet article, nous devrons utiliser la fonction link: / summing-excel-sumproduct-function [fonction SUMPRODUCT]. Nous allons maintenant faire une formule à partir de ces fonctions. Ici, on nous donne une plage et nous devons réduire les 5 valeurs de la plage et obtenir la somme des valeurs.
Formule générique:
{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , { 1 , 2 , 3 , 4 , 5 } ) ) }
Plage: plage de valeurs Valeurs: nombres séparés par des virgules comme si vous souhaitez trouver les 3 dernières valeurs, utilisez \ {1, 2, 3}.
Exemple:
Tout cela peut être difficile à comprendre. Alors, testons cette formule en l’exécutant sur l’exemple ci-dessous.
Ici, nous avons une plage de valeurs de A1: D50.
Ici, la gamme Ville et quantité est donnée comme utilisant l’outil Excel de gamme nommée.
Tout d’abord, nous devons trouver les cinq dernières valeurs à l’aide de la fonction SMALL qui correspond aux critères, puis l’opération de somme doit être effectuée sur ces 5 valeurs. Maintenant, nous allons utiliser la formule suivante pour obtenir la somme Utilisez la formule:
{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , { 1 , 2 , 3 , 4 , 5 } ) ) }
N’utilisez PAS d’accolades manuellement. Accolades appliquées à l’aide de Ctrl Maj + Entrée au lieu de simplement Entrée.
Explication:
-
lien: / formule-statistique-excel-petite-fonction [PETITE]
La fonction avec la fonction IF renvoie les 5 dernières valeurs numériques qui correspondent à la ville « Boston » et renvoie le tableau à la fonction SOMME.
SOMME (\ {23, 27, 28, 28, 30}))
-
La fonction SOMME obtient le tableau des 5 dernières valeurs, qui a un tableau des 5 derniers nombres renvoie la SOMME de ces nombres utilisés avec CTRL + MAJ + ENTRÉE.
Ici, la plage Ville et quantité est donnée comme plage nommée. Appuyez sur Ctrl + Maj + Entrée pour obtenir la somme des 5 derniers nombres car il s’agit d’une formule matricielle.
Comme vous pouvez le voir dans l’instantané ci-dessus, cette somme est de 136.
Le processus ci-dessus est utilisé pour calculer la somme de quelques nombres à partir du bas. Mais pour calculer pour n (grand) nombre de valeurs dans une longue plage.
Utilisez la formule:
{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , ROW ( INDIRECT ("1:10") ) ) ) }
N’utilisez PAS les accolades manuellement. Utilisez les touches Ctrl + Maj + Entrée au lieu d’utiliser la touche Entrée.
Ici, nous générons la somme des 10 dernières valeurs en obtenant un tableau de 1 à 10 \ {1; 2; 3; 4; 5; 6; 7 ; 8; 9; 10} à l’aide des fonctions Excel ROW & INDIRECT.
Ici, nous avons la somme des 10 derniers nombres qui donneront 155.
Voici quelques notes d’observation ci-dessous.
Remarques:
-
La formule ne fonctionne qu’avec des nombres.
-
La formule ne fonctionne que lorsqu’il n’y a pas de doublons dans la table de recherche. La fonction SOMMEPROD considère les valeurs non numériques (comme le texte abc) et les valeurs d’erreur (comme #NOMBRE!, #NULL!) Comme des valeurs nulles.
-
La fonction SUMPRODUCT considère la valeur logique TRUE comme 1 et False comme 0.
-
Le tableau d’arguments doit être de la même longueur que la fonction.
J’espère que cet article sur la façon de renvoyer la somme des 5 premières valeurs ou des 5 dernières valeurs avec des critères dans Excel est explicatif. Trouvez plus d’articles sur les fonctions SUMPRODUCT ici. Veuillez partager votre requête ci-dessous dans la zone de commentaire. Nous vous assisterons.
Si vous avez aimé nos blogs, partagez-les avec vos amis sur Facebook. Et vous pouvez également nous suivre sur Twitter et Facebook. Nous serions ravis de vous entendre, faites-nous savoir comment nous pouvons améliorer, compléter ou innover notre travail et le rendre meilleur pour vous. Écrivez-nous à [email protected]
Articles liés
link: / summing-excel-sumproduct-function [Comment utiliser la fonction SUMPRODUCT dans Excel]: Renvoie la SOMME après multiplication des valeurs dans plusieurs tableaux dans Excel.
link: / summing-sum-if-date-is-between [SUM if date is between]: Renvoie la somme des valeurs entre des dates ou une période données dans Excel.
lien: / somme-somme-si-la-date-est-supérieure-à-la-date [Somme si la date est supérieure à la date donnée]: * Renvoie la somme des valeurs après la date ou la période donnée dans Excel.
link: / summing-2-ways-to-sum-by-month-in-excel [2 Ways to Sum by Month in Excel]: * Renvoie la somme des valeurs dans un mois spécifique donné dans Excel.
link: / summing-how-to-sum-multiple-columns-with-condition [Comment additionner plusieurs colonnes avec condition]: Renvoie la somme des valeurs sur plusieurs colonnes ayant une condition dans Excel `link: / tips-excel- wildcards [Comment utiliser les jokers dans Excel *] `: Compter les cellules correspondant aux phrases en utilisant les jokers dans Excel
Articles populaires
lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourci Excel pour augmenter votre productivité]
link: / excel-generals-how-to-edit-a-dropdown-list-in-microsoft-excel [Edit a dropdown list]
lien: / excel-range-name-absolu-reference-in-excel [Référence absolue dans Excel]
lien: / conseils-formatage-conditionnel-avec-instruction-if [Si avec formatage conditionnel]
lien: / formules-logiques-si-fonction-avec-jokers [Si avec jokers]
lien: / lookup-formulas-vlookup-by-date-in-excel [Vlookup by date]
link: / tips-inches-to-feet [Convertir les pouces en pieds et pouces dans Excel 2016]
lien: / excel-édition-de-texte-et-format-join-prénom-et-nom-dans-excel [Joindre le nom et le prénom dans Excel]
link: / counting-count-cells-qui-correspondent-soit-a-ou-b [Compter les cellules qui correspondent à A ou B]