Fonction SUMIF dans Excel et somme par plusieurs conditions

Excel est un programme incroyablement fonctionnel. Même l'ensemble de fonctionnalités intégré est suffisant pour effectuer presque toutes les tâches. Et outre les standards, familiers à beaucoup, il y a aussi ceux dont peu de gens ont entendu parler. Mais en même temps, ils ne cessent pas d'être utiles. Ils ont une spécialisation plus étroite et ils ne sont pas toujours nécessaires. Mais si vous les connaissez, ils peuvent être très utiles à un moment critique.

Aujourd'hui, nous allons parler de l'une de ces fonctions - SUMMESLIMN.

Si l'utilisateur est confronté à la tâche de résumer plusieurs valeurs, en se concentrant sur certains critères, il est alors nécessaire d'utiliser la fonction SUMMESLIMN. La formule qui utilise cette fonction prend ces conditions comme arguments, puis additionne les valeurs qui les satisfont, puis la valeur trouvée est entrée dans la cellule dans laquelle elle est écrite. 

Description détaillée de la fonction SUMIFS

Avant de considérer la fonction SUMMESLIMN, vous devez d'abord comprendre ce qu'est une version plus simple de celui-ci - SOMMESLI, puisque c'est sur elle que repose la fonction que nous considérons. Chacun de nous est probablement déjà familier avec deux fonctions souvent utilisées - SUM (effectue la sommation des valeurs) et SI (teste une valeur par rapport à une condition spécifiée).

Si vous les combinez, vous obtenez une autre fonction - SOMMESLI, qui vérifie les données par rapport aux critères spécifiés par l'utilisateur et additionne uniquement les nombres qui répondent à ces critères. Si nous parlons de la version anglaise d'Excel, cette fonction s'appelle SUMIF. En termes simples, le nom en langue est une traduction directe du nom en anglais. Cette fonction peut être utilisée à diverses fins. En particulier, il peut être utilisé comme alternative VPR, c'est-à-dire écrire

La principale différence entre la fonction SUMMESLIMN  de la fonction habituelle SOMMESLI est que plusieurs critères sont utilisés. Sa syntaxe est assez complexe à première vue, mais à y regarder de plus près, il s'avère que la logique de cette fonction est très simple. Vous devez d'abord sélectionner la plage dans laquelle les données seront vérifiées, puis définir les conditions de conformité avec lesquelles l'analyse sera effectuée. Et une telle opération peut être effectuée pour un assez grand nombre de conditions.

La syntaxe elle-même est :

SOMME(plage_somme, plage_condition1, condition1, [plage_condition2, condition2], …)

Dans des endroits appropriés, il est nécessaire de placer des réseaux de cellules adaptés à un cas particulier. 

Examinons les arguments plus en détail :

1. Somme_plage. Cet argument, ainsi que la plage de la condition 1 et de la condition 1, sont obligatoires. C'est un ensemble de cellules qui doivent être sommées.
2. Condition_range1. Il s'agit de la plage où la condition sera vérifiée. Il est associé à l'argument suivant - Condition1. La sommation des valeurs correspondant au critère est effectuée dans les cellules spécifiées dans l'argument précédent.
3. Condition1. Cet argument spécifie les critères à vérifier. Il peut être réglé, par exemple, de cette manière : « > 32 ».
4. Condition range 2, Condition 2… Ici, les conditions suivantes sont définies de la même manière. Si plusieurs conditions doivent être spécifiées, les arguments Condition Range 3 et Condition 3 sont ajoutés. La syntaxe est la même pour les arguments suivants.

La fonction permet de traiter jusqu'à 127 paires de conditions et de plages. 

Vous pouvez l'utiliser dans plusieurs domaines à la fois (nous n'en donnerons que quelques-uns, la liste est en fait encore plus longue) :

1. Comptabilité. Par exemple, il est bon d'utiliser la fonction SUMMESLIMN de créer des rapports de synthèse, par trimestre pour les dépenses supérieures à un certain montant, par exemple. Ou créez un rapport sur un produit d'une certaine catégorie de prix.
2. Gestion des ventes. Ici aussi, la fonction peut être très utile. Par exemple, nous sommes confrontés à la tâche de résumer uniquement le coût des marchandises qui ont été vendues à un certain client à un certain moment. Et dans une telle situation, la fonction SUMMESLIMN peut être très utile.
3. Éducation. Nous allons donner plus d'exemples pratiques de ce domaine aujourd'hui. En particulier, vous pouvez l'utiliser pour obtenir un résumé des notes des étudiants. Vous pouvez choisir pour une seule matière ou pour des notes individuelles. Une personne peut immédiatement définir plusieurs critères selon lesquels l'évaluation sera sélectionnée, ce qui est très pratique et peut faire gagner beaucoup de temps.

Comme vous pouvez le constater, le champ d'application de cette fonction est très large. Mais ce n'est pas son seul mérite. Jetons un coup d'œil à quelques autres avantages de cette fonctionnalité :

1. Possibilité de définir plusieurs critères. Pourquoi est-ce un avantage ? Vous pouvez utiliser la fonction normale SOMMESLI! Et tout cela parce que c'est pratique. Il n'est pas nécessaire d'effectuer des calculs séparés pour chacun des critères. Toutes les actions peuvent être programmées à l'avance, même avant. comment la table de données sera formée. C'est un grand gain de temps.
2. Automatisation. L'ère moderne est l'ère de l'automatisation. Seule la personne qui sait automatiser correctement son travail peut gagner beaucoup. C'est pourquoi la maîtrise d'Excel et la fonction SUMMESLIMN en particulier, est si important pour toute personne qui veut construire une carrière. Connaître une fonction vous permet d'effectuer plusieurs actions à la fois, comme une seule. Et ici, nous passons au prochain avantage de cette fonctionnalité.
3. Gagner du temps. Juste parce qu'une fonction exécute plusieurs tâches à la fois.
4. Simplicité. Malgré le fait que la syntaxe est assez lourde à première vue en raison de son encombrement, en fait, la logique de cette fonction est très simple. Tout d'abord, une plage de données est sélectionnée, puis une plage de valeurs, dont la conformité à une certaine condition sera vérifiée. Et bien sûr, la condition elle-même doit également être spécifiée. Et ainsi plusieurs fois. En fait, cette fonction est basée sur une seule construction logique, ce qui la rend plus simple que la fonction bien connue VPR bien qu'il puisse être utilisé aux mêmes fins, en tenant également compte d'un plus grand nombre de critères. 

Caractéristiques de l'utilisation de la fonction SUMIFS

Il y a plusieurs caractéristiques d'utilisation de cette fonction auxquelles vous devez prêter attention. Tout d'abord, cette fonction ignore les plages avec des chaînes de texte ou des valeurs nulles, car ces types de données ne peuvent pas être additionnés dans un modèle arithmétique, uniquement concaténés comme des chaînes. Cette fonction ne peut pas faire cela. Vous devez également faire attention aux conditions suivantes :

1. Vous pouvez utiliser ces types de valeurs comme conditions de sélection des cellules pour ajouter davantage les valeurs qu'elles contiennent : valeurs numériques, expressions booléennes, références de cellules, etc. 
2. Si du texte, des expressions logiques ou des signes mathématiques sont vérifiés, ces critères sont spécifiés par des guillemets.
3. Impossible d'utiliser des termes de plus de 255 caractères.
4. Il est possible d'utiliser des critères approximatifs pour sélectionner des valeurs à l'aide de caractères génériques. Le point d'interrogation est utilisé pour remplacer un seul caractère et le signe de multiplication (astérisque) est nécessaire pour remplacer plusieurs caractères. 
5. Les valeurs booléennes qui se trouvent dans la plage de sommation sont automatiquement converties en valeurs numériques en fonction de leur type. Ainsi, la valeur "TRUE" se transforme en un et "FALSE" - en zéro. 
6. Si un #VALEUR ! erreur apparaît dans une cellule, cela signifie que le nombre de cellules dans les plages de condition et de sommation est différent. Vous devez vous assurer que les tailles de ces arguments sont les mêmes. 

Exemples d'utilisation de la fonction SUMIFS

Fonction SUMMESLIMN pas aussi compliqué qu'il n'y paraît à première vue, il s'avère. Mais pour plus de clarté, regardons quelques exemples pratiques d'utilisation de la fonction SUMMESLIMN. Cela facilitera grandement l'approfondissement du sujet.

Plage dynamique de sommation des conditions

Commençons donc par le premier exemple. Supposons que nous disposions d'un tableau contenant des informations sur la façon dont les élèves font face au programme dans une matière particulière. Il existe un ensemble de notes, la performance est évaluée sur une échelle de 10 points. La tâche consiste à trouver la note à l'examen des étudiants dont le nom de famille commence par la lettre A et dont la note minimale est de 5.

Le tableau ressemble à ceci.

Afin que nous puissions calculer le score total sur la base des critères décrits ci-dessus, nous devons appliquer la formule suivante.

Décrivons les arguments plus en détail :

1. C3:C14 est notre plage de sommation. Dans notre cas, il coïncide avec la plage de conditions. De celui-ci seront sélectionnés les points utilisés pour calculer le montant, mais uniquement ceux qui relèvent de nos critères.
2. "> 5" est notre première condition.
3. B3:B14 est la deuxième plage de sommation qui est traitée pour correspondre au deuxième critère. On voit qu'il n'y a pas de coïncidence avec la plage de sommation. Nous en concluons que la plage de sommation et la plage de la condition peuvent ou non être identiques. 
4. "A*" est la deuxième plage, qui spécifie la sélection des notes uniquement pour les étudiants dont le nom de famille commence par A. Dans notre cas, un astérisque signifie n'importe quel nombre de caractères. 

Après calculs, on obtient le tableau suivant.

Comme vous pouvez le voir, la formule résumait les valeurs en fonction de la plage dynamique et en fonction des conditions spécifiées par l'utilisateur.

Sommation sélective par condition dans Excel

Supposons maintenant que nous voulions obtenir des informations sur les marchandises expédiées vers quels pays au cours du dernier trimestre. Après cela, trouvez le revenu total des expéditions pour juillet et août.

La table elle-même ressemble à ceci. 

Pour déterminer le résultat final, nous avons besoin d'une telle formule.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

À la suite des calculs effectués par cette formule, nous obtenons le résultat suivant.

Attention! Cette formule semble assez grande même si nous n'avons utilisé que deux critères. Si la plage de données est la même, vous pouvez réduire considérablement la longueur de la formule, comme indiqué ci-dessous.

Fonction SUMIFS pour additionner des valeurs dans plusieurs conditions

Donnons maintenant un autre exemple pour illustrer. Dans ce cas, le tableau reste le même que dans le cas précédent. 

Nous utilisons la formule suivante (mais nous l'écrivons sous forme de formule matricielle, c'est-à-dire que nous la saisissons via la combinaison de touches CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Après la fonction SUMMESLIMN additionnera le tableau de valeurs en fonction des critères spécifiés dans la formule (c'est-à-dire les pays de Chine et de Géorgie), le tableau résultant est additionné par la fonction habituelle SOMME, qui s'écrit sous la forme d'une formule matricielle.

Si les conditions ont été transmises sous forme de constante matricielle pour plus d'une paire, la formule donnera un résultat incorrect.

Examinons maintenant le tableau contenant les totaux.

Comme vous pouvez le voir, nous avons réussi. Vous réussirez certainement aussi. Grand succès dans ce domaine. Il s'agit d'une fonction très simple qu'une personne qui vient de mettre le pied sur le chemin de l'apprentissage d'Excel peut comprendre. Et nous savons déjà que la fonction SUMMESLIMN vous permet d'être efficace dans n'importe quel domaine d'activité, de la comptabilité à même l'éducation. Même si vous construisez une carrière dans un autre domaine qui n'a pas été décrit ci-dessus, cette fonctionnalité vous aidera toujours à gagner de l'argent. C'est pourquoi elle est précieuse.

Surtout, cela vous permet de gagner du temps, qui est malheureusement une ressource limitée. Il semblerait qu'il y ait quelques secondes pour appliquer deux fonctions, mais lorsque vous devez effectuer un grand nombre d'opérations répétitives, ces secondes totalisent des heures qui pourraient être consacrées à autre chose. Nous vous recommandons donc de vous entraîner à utiliser cette fonctionnalité. De plus, c'est incroyablement simple.