4 formules à Somme Top N valeurs dans Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: summing

Si vous souhaitez résumer les 3 valeurs dans Excel, vous pouvez le faire en calculant simplement les 3 premières valeurs à l’aide d’une fonction LARGE et en les additionnant.

Mais ce n’est pas le seul moyen. Vous pouvez additionner les N premières valeurs dans Excel à l’aide de plusieurs formules. Tous utiliseront la fonction LARGE comme fonction clé. Voir l’image ci-dessus. Explorons-les tous.

Somme des 3 premières ou des N premières valeurs en utilisant la fonction SUMIF

Formule générique

=SUMIF(range, »>= »&LARGE(range,n))

Plage: * La plage est la plage à partir de laquelle vous souhaitez additionner les N premières valeurs.

n: Il s’agit du nombre de valeurs principales que vous souhaitez résumer. Si vous voulez additionner les 3 premières valeurs, alors * vaut 3.

Voyons donc cette formule en action.

Exemple 1: Utilisation de SUMIF pour additionner les 3 meilleures ventes. Nous avons des données qui contiennent les ventes réalisées au cours des différents mois. Nous devons résumer les 3 meilleures ventes. Nous utilisons la formule générique ci-dessus.

La plage est C2: C13 et n est 3. Alors la formule sera:

=SUMIF(C2:C13, »>= »&LARGE(C2:C13,3))

Cela revient à 696020 qui est correct. Si vous voulez faire la somme des N valeurs de la variable supérieure et que N est écrit dans D2, la formule sera:

=SUMIF(C2:C13, »>= »&LARGE(C2:C13,D2))

il résumera toutes les valeurs supérieures ou égales à la valeur de la valeur N supérieure.

Comment ça marche? La fonction LARGE renvoie la Nième valeur supérieure de la plage donnée. Dans la formule ci-dessus, la fonction LARGE renvoie la troisième plus grande valeur de rangeC2: C13 * qui est212940. Maintenant la formule est:

=SUMIF(C2:C13, »>= »&212940)

Maintenant, la fonction SUMIF renvoie simplement la SOMME des valeurs supérieures ou égales à212940, soit 696020.

Si vous souhaitez ajouter plusieurs conditions pour additionner les 3 ou N premières valeurs, utilisez la fonction SUMIFS.

Somme des 3 premières ou des N premières valeurs en utilisant la fonction SOMME La méthode ci-dessus est assez facile et rapide à utiliser lorsqu’il s’agit de résumer les 3 ou N premières valeurs de la liste. Mais cela devient difficile lorsqu’il s’agit de sommer les valeurs N arbitraires les plus élevées, par exemple les 2e, 4e et 6e valeurs supérieures d’une plage Excel. Ici, la fonction SUM ou SUMPRODUCT fait le travail.

Formule générique:

=SUM(LARGE(range,\{1,2,3}))

=SUMPRODUCT(LARGE(range,\{1,2,3}))

Plage: * La plage est la plage à partir de laquelle vous souhaitez additionner les N premières valeurs.

\ {1,2,3}: * Ce sont les principales valeurs que vous souhaitez résumer. Ici, c’est 1er, 2e et 3e.

Utilisons donc cette formule dans l’exemple ci-dessus.

Exemple 2: additionnez les plus grandes 1ère, 2ème et 3ème valeurs d’une plage Excel. Nous utilisons donc la même table que celle que nous avons utilisée dans l’exemple ci-dessus. La plage est toujours C2: C13 et les 3 premières valeurs que nous voulons additionner sont les 1ère, 2ème et 3ème valeurs les plus élevées de la plage.

=SUMPRODUCT(LARGE(C2:C13,\{1,2,3}))

Il renvoie le même résultat que précédemment, à savoir 696020. Si vous voulez faire la somme des 2e et 4e valeurs les plus élevées, la formule sera:

=SUMPRODUCT(LARGE(C2:C13,\{2,4}))

Et le résultat sera425360.

Vous pouvez remplacer la fonction SUMPRODUCT par la fonction SUM dans le cas ci-dessus. Mais si vous souhaitez donner une référence aux valeurs supérieures, vous devrez utiliser CTRL + MAJ + ENTRÉE pour en faire une formule matricielle.

\{=SUM(LARGE(C2:C13,E2:E4))}

Comment ça marche?

La fonction LARGE accepte un tableau de valeurs clés et renvoie un tableau de ces valeurs supérieures. Ici, la fonction large renvoie les valeurs \ {241540; 183820; 38740} en tant que valeurs 2, 4 et 6 supérieures. Maintenant, la formule devient:

=SUM(\{241540;183820;38740})

=SUMPRODUCT(\{241540;183820;38740})

Cela résume les valeurs renvoyées par la fonction LARGE.

Addition d’une large plage de valeurs supérieures dans Excel Supposons que vous souhaitiez additionner les valeurs 10 à 2o, tout compris. Dans ce cas, les méthodes mentionnées ci-dessus deviendront difficiles et plus longues. La meilleure façon est d’utiliser la formule générique ci-dessous pour résumer les valeurs les plus élevées:

=SUMPRODUCT(LARGE(range,ROW(INDIRECT(« start:end »))))

range: * La plage est la plage à partir de laquelle vous souhaitez additionner les N premières valeurs.

« start: end »: * C’est le texte de la plage de valeurs supérieures que vous souhaitez résumer. Si vous voulez des valeurs des 3 premières aux 6 premières, alors start: end sera « 3: 6 ».

Regardons cette formule en action:

Exemple 3: Additionner les 3 à 6 premières valeurs des ventes:

Nous avons donc le même tableau que l’exemple ci-dessus. La seule requête est différente. En utilisant la formule générique ci-dessus, nous écrivons cette formule:

=SUMPRODUCT(LARGE(C2:C13,ROW(INDIRECT(« 3:6 »))))

Cela renvoie la valeur 534040. Qui est la somme des 3e, 4e, 5e et 6e plus grandes valeurs de la plage C2: C13.

Comment ça marche?

Tout d’abord, la fonction INDIRECT transforme le texte « 3: 6 » en plage réelle 3: 6.

Ensuite, la fonction ROW renvoie un tableau de numéros de ligne dans la plage 3: 6, qui sera \ {3,4,5,6}. Ensuite, la fonction LARGE renvoie les 3ème, 4ème, 5ème et 6ème plus grandes valeurs de la plage C2: C13. Enfin, la fonction SUMPRODUCT résume ces valeurs et nous obtenons notre résultat.

=SUMPRODUCT(LARGE(C2:C13,ROW(INDIRECT(« 3:6 »))))

=SUMPRODUCT(LARGE(C2:C13,\{3,4,5,6}))*

=SUMPRODUCT(\{212940;183820;98540;38740})

=534040

Bonus: additionnez les N premières valeurs à l’aide de la fonction SEQUENCE La fonction SEQUENCE est introduite dans Excel 2019 et 365. Elle renvoie une séquence de nombres. Nous pouvons l’utiliser pour obtenir les N premières valeurs et les additionner.

Formule générique:

=SUMPRODUCT(LARGE(range,SEQUENCE(num_values,,[start_num], [steps]))))

range: * La plage est la plage à partir de laquelle vous souhaitez additionner les N premières valeurs.

num_values: c’est le nombre de valeurs supérieures que vous souhaitez additionner.

[start_num]: * C’est le numéro de départ de la série. C’est facultatif. Si vous omettez ceci, la série commencera à partir de 1.

[étapes]: C’est la différence entre le numéro suivant et le numéro actuel. Par défaut, il est 1.

Si nous utilisons cette formule générique pour obtenir le même résultat que dans l’exemple précédent, la formule sera:

=SUM(LARGE(C2:C13,SEQUENCE(4,,3)))

Il renverra la valeur534040.

Comment ça marche?

C’est simple. La fonction SEQUENCE renvoie une série de 4 valeurs qui commencent par 3 avec l’intervalle 1. Elle renvoie le tableau \ {3; 4; 5; 6}. La fonction LARGE renvoie maintenant les plus grandes valeurs des nombres correspondants. Enfin, la fonction SUM résume ces valeurs et nous obtenons notre résultat comme 534040.

=SUM(LARGE(C2:C13,SEQUENCE(4,,3)))*

=SUM(LARGE(C2:C13,\{3;4;5;6}))*

=SUM(\{212940;183820;98540;38740})*

=534040

Alors oui les gars, voici comment vous pouvez résumer les 3, 5, 10, … N principales valeurs dans Excel. J’ai essayé d’être explicatif. J’espère que ça t’aide. Si vous avez des doutes concernant ce sujet ou tout autre sujet lié à Excel VBA, demandez dans la section commentaires ci-dessous. Je réponds fréquemment aux questions.

Articles liés:

lien: / somme-somme-si-supérieure-à-0-dans-Excel [Somme si supérieure à 0 dans Excel] | Pour additionner uniquement les valeurs positives, nous devons additionner les valeurs supérieures à 0. Pour ce faire, nous utiliserons la fonction SUMIF d’Excel.

link: / summing-how-to-sum-last-30-days-sales-in-excel [Comment additionner les ventes des 30 derniers jours dans Excel] | Parfois, il est également résumé sur un intervalle standard de 30 jours. Dans ce cas, avoir une formule qui montre dynamiquement la somme des 30 derniers jours sera une bonne idée. Dans cet article, nous allons apprendre à additionner les valeurs des 30 derniers jours dans Excel.

lien: / addition-comment-additionner-les-dernières-n-colonnes-dans-Excel [Comment additionner les n dernières colonnes dans Excel] | Parfois donné une condition, c’est-à-dire

lorsque nous devons obtenir la somme des 5 ou n derniers nombres de la colonne.

Vous pouvez facilement résoudre ce problème en utilisant les fonctions Excel.

lien: / sommation-comment-somme-par-correspondance-ligne-et-colonne [Comment faire la somme en faisant correspondre la ligne et la colonne dans Excel] | Comment additionner les valeurs lorsque nous devons faire correspondre la colonne et la ligne. Dans cet article, nous allons apprendre à faire la somme des lignes et des colonnes correspondantes.

Articles populaires:

lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourcis Excel pour augmenter votre productivité] | Accélérez votre tâche. Ces 50 raccourcis vous permettront de travailler encore plus rapidement sur Excel.

lien: / formules-et-fonctions-introduction-de-vlookup-function [Comment utiliser la fonction RECHERCHEV Excel] | C’est l’une des fonctions les plus utilisées et les plus populaires d’Excel qui est utilisée pour rechercher des valeurs à partir de différentes plages et feuilles. link: / tips-countif-in-microsoft-excel [Comment utiliser le]

lien: / formules-et-fonctions-introduction-de-vlookup-function [Excel]

lien: / tips-countif-in-microsoft-excel [Fonction COUNTIF] | Comptez les valeurs avec des conditions en utilisant cette fonction étonnante.

Vous n’avez pas besoin de filtrer vos données pour compter une valeur spécifique. La fonction Countif est indispensable pour préparer votre tableau de bord.

lien: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel] | C’est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs sur des conditions spécifiques.

Articles populaires:

lien: / formules-et-fonctions-introduction-de-vlookup-function [Excel]

lien: / tips-countif-in-microsoft-excel [Fonction COUNTIF] | Comptez les valeurs avec des conditions en utilisant cette fonction étonnante.

Vous n’avez pas besoin de filtrer vos données pour compter une valeur spécifique. La fonction Countif est indispensable pour préparer votre tableau de bord.