Comment utiliser la fonction SOMME et SI à la place pour la fonction SUMPRODUCT ou SUMIFS dans Excel
Dans cet article, nous allons apprendre à utiliser la fonction IF au lieu de la fonction SUMPRODUCT et SUMIFS dans Excel.
Scénario:
En termes simples, lorsque vous travaillez avec un long ensemble de données dispersées, nous devons parfois trouver la somme des nombres avec quelques critères dessus. Par exemple, trouver la somme des salaires dans un département particulier ou avoir plusieurs critères sur la date, les noms, le département ou même numéroter des données telles que les salaires inférieurs à la valeur ou la quantité supérieure à la valeur. Pour cela, vous utilisez généralement la fonction SUMPRODUCT ou SUMIFS. Mais vous ne le croiriez pas, vous exécutez la même fonction avec la fonction IF de la fonction de base Excel.
Comment résoudre le problème?
Vous devez vous demander comment est-ce possible, pour effectuer des opérations logiques sur des tableaux de tables en utilisant la fonction IF. Si la fonction dans Excel est très utile, elle vous permettra de réaliser certaines tâches difficiles dans Excel ou dans tout autre langage de codage. La fonction IF teste les conditions sur le tableau correspondant aux valeurs requises et renvoie le résultat sous forme de tableau correspondant aux conditions True comme 1 et False comme 0.
Pour ce problème, nous utiliserons les fonctions suivantes:
-
lien: / math-and-trig-excel-sum-function [fonction SUM] -
lien: / conseils-si-condition-dans-excel [fonction IF]
Nous aurons besoin de ces fonctions ci-dessus et d’un certain sens de base du fonctionnement des données. les conditions logiques sur les tableaux peuvent être appliquées à l’aide d’opérateurs logiques. Ces opérateurs logiques fonctionnent à la fois sur le texte et les nombres. Voici la formule générique ci-dessous. \ {} * accolades est l’outil magique pour effectuer des formules matricielles avec la fonction IF.
Formule générique:
|
\{* =
(logical_1) (logical_2) … (logical_n) , sum_array ) ) }* |
Remarque: pour les accolades (\ {}) Utilisez Ctrl + Maj + Entrée * lorsque vous travaillez avec des tableaux ou des plages dans Excel. Cela générera des accolades sur la formule par défaut. N’essayez PAS de coder en dur les caractères entre accolades.
Logique 1: teste la condition 1 sur le tableau 1 Logique 2: teste la condition 2 sur le tableau 2 et ainsi de suite sum_array: tableau, l’opération somme est effectuée
Exemple:
Tout cela peut être difficile à comprendre. Alors, testons cette formule en l’exécutant sur l’exemple ci-dessous. Ici, nous avons des données sur les produits livrés dans différentes villes ainsi que les champs de catégorie et les quantités correspondants. Ici, nous avons les données et nous devons trouver la quantité de cookies envoyés à Boston où la quantité est supérieure à 40.
Le tableau de données et le tableau des critères sont présentés dans l’image ci-dessus. Pour comprendre, nous avons utilisé des plages nommées pour les tableaux utilisés. Les plages nommées sont répertoriées ci-dessous.
Ici:
Ville définie pour le tableau A2: A17.
Catégorie définie pour le tableau B2: A17.
Quantité définie pour le tableau C2: C17.
Vous êtes maintenant prêt à obtenir le résultat souhaité en utilisant la formule ci-dessous.
Utilisez la formule:
|
\{ =
(City= »Boston ») (Category= »Cookies ») (Quantity>40) , Quantity)) }* |
Explication:
-
City = « Boston »: vérifie que les valeurs de la plage de villes correspondent à « Boston ».
-
Category = « Cookies »: vérifie que les valeurs de la plage de catégories correspondent à celles de « Cookies ».
-
Quantité> 40: vérifie les valeurs de la plage de quantité à ma. Quantity be array où la somme est requise.
-
La fonction IF vérifie tous les critères et l’astérisque char (*) multiplie tous les résultats du tableau.
SOMME (SI (\ {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, \ {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))
-
Maintenant, la fonction IF ne renvoie que les quantités correspondant aux 1 et le reste est ignoré.
-
La fonction SOMME renvoie la SOMME.
Désormais, la quantité correspondant à 1 ne fait que s’additionner pour obtenir le résultat.
Comme vous pouvez le voir, la quantité 43 est retournée mais il y a trois commandes de cookies livrées à « Boston » avec les quantités 38, 36 et 43. Nous avions besoin d’une somme de quantité où la quantité est supérieure à 40. La formule renvoie donc 43 seulement. Maintenant, utilisez d’autres critères pour obtenir le SUM Quantity for City: « Los Angeles » & Category: « Bars » & Quantity be less than 50.
Utilisez la formule *
|
\{* =
« Los Angeles ») (Category= »Bars ») (Quantity < 50), Quantity ) ) } |
Comme vous pouvez le voir, la formule renvoie les valeurs 86 comme résultat. Qui est la somme de 2 commandes satisfaisant aux conditions ayant la quantité 44 et 42.
Cet article illustre comment remplacer un lien: / formules-logiques-excel-nested-if-function [formule IF imbriquée] par un IF unique dans une formule matricielle. Cela peut être utilisé pour réduire la complexité des formules complexes. Cependant, ce problème particulier pourrait être facilement résolu avec link: / summing-excel-sumifs-function [SUMIFS] ʻou link: / summing-excel-sumproduct-function [SUMPRODUCT] `function.
Utilisation de la fonction SUMPRODUCT:
La fonction SUMPRODUCT renvoie la somme des valeurs correspondantes dans le tableau. Nous allons donc obtenir les tableaux pour renvoyer 1s aux valeurs de l’instruction True et 0s aux valeurs de l’instruction False. Donc, la dernière somme correspondra là où toutes les déclarations sont vraies.
Utilisez la formule:
|
= ( — (City = « Boston ») , — (Category = « Cookies ») , — (Quantity > 40) , Quantity ) |
-: opération utilisée pour convertir tous les TRUE en 1 et False en 0.
La fonction SUMPRODUCT revérifie la somme de la quantité retournée par la fonction SUM et IF expliquée ci-dessus.
De même pour le deuxième exemple, le résultat est le même.
Comme vous pouvez le voir, la fonction SUMPRODUCT peut effectuer la même tâche.
Voici toutes les notes d’observation concernant l’utilisation de la formule.
Remarques:
-
Le sum_array dans la formule ne fonctionne qu’avec des nombres.
-
Si la formule renvoie une erreur #VALUE, vérifiez que les accolades doivent être présentes dans la formule, comme indiqué dans les exemples de l’article.
-
Negation (-) char change les valeurs, TRUEs ou 1s en FALSEs ou 0s et FALSEs ou 0s en TRUEs ou 1s.
-
Les opérations telles que égal à (=), inférieur à égal à (⇐), supérieur à (>) ou non égal à (<> *) peuvent être effectués dans une formule appliquée, avec des nombres uniquement.
J’espère que cet article sur la façon d’utiliser la fonction IF au lieu de la fonction SUMPRODUCT et SUMIFS dans Excel est explicatif. Trouvez plus d’articles sur les formules de sommation ici. Si vous avez aimé nos blogs, partagez-les avec vos premiers 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 une condition]: * Renvoie la SOMME des valeurs sur plusieurs colonnes ayant une condition dans Excel.
Articles populaires:
lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourci Excel pour augmenter votre productivité]: Accélérez votre tâche. Ces 50 raccourcis vous permettront de travailler encore plus rapidement sur Excel.
link: / formulas-and-functions-introduction-of-vlookup-function [Comment utiliser la fonction RECHERCHEV dans 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 dans différentes plages et feuilles. link: / tips-countif-in-microsoft-excel [Comment utiliser la fonction COUNTIF dans Excel]: Comptez les valeurs avec des conditions en utilisant cette fonction étonnante. Vous n’avez pas besoin de filtrer vos données pour compter des valeurs spécifiques. 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]: Ceci est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs sur des conditions spécifiques.