Quelle fonction utilisez-vous lorsque vous devez faire une somme avec plusieurs conditions? Vous utilisez probablement la fonction SUMIFS ou SOMME.SI.ENS. Mais quelle fonction utiliser pour faire une somme dont seulement l’une ou l’autre des conditions doit être rencontrée? Dans un tel cas, la fonction SUMIFS ou SOMME.SI.ENS n’est pas utile car elle suppose que toutes les conditions sont vraies. Donc, pour contourner ce problème, l’utilisation de la fonction SIGNE d’Excel, imbriquée dans une fonction SUMPRODUCT ou SOMMEPROD, est l’idéal. Cet article vise à démontrer comment.
À relire au besoin
Au besoin, je vous invite à relire les articles suivants:
- Excel: Somme.si.ens et nb.si.ens (sumifs et countifs)
- Excel: Un truc puissant dont vous ne pourrez plus vous passer
Fonction SIGNE dans Excel
Dans l’exemple suivant, il y a des valeurs dans la colonne B et des conditions dans la colonne C. L’objectif est de faire la somme des valeurs pour les conditions qui sont égales à 1, 2, 3, 4, 5, 6, 7, 8, 9, ou 10 (soit les valeurs inscrites dans les cellules D4 à M4). Cet article démontrera deux façons d’arriver au résultat (sans faire l’étendue de toutes les possibilités) et s’arrêtera sur l’utilité de la fonction SIGN ou SIGNE dans un tel contexte.
Utilisation de la fonction SUMIF ou SOMME.SI
Une des façons de résoudre le cas précédent serait d’utiliser une série de SUMIF ou de SOMME.SI, tel qu’illustré ci-bas. En effet, on ne peut pas utiliser la fonction SUMIFS ou SOMME.SI.ENS puisque les conditions sont interchangeables.Pour inclure une valeur dans notre calcul de somme, il faut qu’au moins une des conditions soit respectée.
Utilisation de la fonction SIGNE dans Excel
Une fonction SIGN ou SIGNE imbriquée dans une fonction SUMPRODUCT ou SOMMEPROD, tel qu’illustré ci-bas ramènera la même réponse qu’au point précédent mais en utilisant moins de caractères, ce qui importe beaucoup pour la performance d’un fichier Excel.
Vous aimeriez être un Ninja Excel et devenir la référence ultime de votre bureau, ou tout le moins ne plus avoir l’impression de stagner ? Développez des solutions innovatrices en explorant des fonctions et fonctionnalités souvent méconnues mais fort utiles avec nos formations pour devenir Ninja d’Excel. |
|---|
Comment interpréter la formule ci-dessus?
Il faut voir l’application de la formule ci-dessus comme un tableau de conditions (illustré ci-bas). Attention! Pour utiliser la fonction SIGN ou SIGNE, vous n’avez pas à reproduire ce tableau. Celui-ci explique simplement la mécanique derrière cette fonction. La fonction SIGN($C5:$C21=D$4), ci-bas, ramène un 0 ou un 1 selon que la condition sous-jacente est respectée ou non. Ainsi, pour toutes les conditions égales à 1, on aperçoit un 1 dans la colonne D et pour toutes les autres conditions, on aperçoit un 0. De même, dans la colonne J, pour toutes les conditions égales à 7, on aperçoit un 1 et pour toutes les autres conditions, on aperçoit un 0. Il en va de même pour chacune des colonnes D à M.
À la toute fin, il faut faire la somme des valeurs dont au moins une condition est respectée. Ci-bas, pour représenter la mathématique sous-jacente, j’ai inséré une fonction IF ou SI qui demande à Excel de rapporter un 1 si la somme des éléments de la ligne est supérieur à 0 ou un 0 dans le cas contraire. Ainsi, lorsqu’au moins une condition est respectée, on peut apercevoir un 1 dans la colonne N et un 0 lorsque ce n’est pas le cas.
Ensuite pour obtenir la valeur totale, on pourra simplement multiplier les 1 et les 0 de la colonne N par les valeurs de la colonne B.
Il est possible de résumer toute cette mécanique en une seule formule, qui est la suivante:
Qu’en pensez-vous?
Avez-vous déjà utilsé la fonction SIGN ou SIGNE? Dans quel contexte? Vous pouvez utiliser la zone de commentaires ci-bas pour échanger avec nous.
Fichier d’accompagnement
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Pour apprendre des façons créatives d’utiliser Excel pour créer des solutions surprenantes et régler des problématiques qui semblent parfois sans solution, suivez la formation Excel – Trucs de pro.
Voici quelques commentaires d’apprenants ayant suivi cette formation :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
bonjour, j’opterais pour la formule matricielle {sum(if(c:6c22<=10,b6:b22,0))}, plus direct.
Si nous tenons à voir le résultat pour chaque ligne alors la plage matricielle s'étendrait des lignes 6 à 22 comme suit: {=if(c:6:c22<=,B6:b22.0)} et faire la sommation ensuite des lignes 6 à 22.
Bonjour,
Les formules matricielles sont effectivement une autre façon de résoudre ce genre de problématique. Par contre, elles sont souvent malmenées par les usagers, ce qui peut entraîner d’autres types de problèmes ultérieurement. CHez nous, nous évitons donc de les utiliser lorsque c’est possible.
Merci,
Sophie
La formule Sumproduct fonctionne très bien sans l’utilisation de la formule Sign. Je crois que l’on gagne rien ici à utiliser Sign. Sign permet de convertir un Vrai en 1 et un faux en 0. Cette conversion se produit également si on applique aux vrai/faux une opération mathématique (addition/multiplication) ce qui est le cas ici il y a une addition puis une multiplication. Les vrai/fuax sont donc bien convertie en 1 et 0.
Voici une formule plus simple sous forme matricielle
={SOMME(SI(C5:C21=D4:M4;B5:B21))}
Bonjour,
Vos commentaires sont effectivement appréciés. Comme le Sophie le mentionne, nous ne sommes pas de grands fans des formules matricielles, même si je comprends qu’elles amènent des capacités additionnelles.
Également, notre situation ici montrée est que nous voulons que l’ensemble des conditions retourne VRAI (donc 1) si au moins une des conditions est vraie. En général, des SUMIFS ou SUMPRODUCT suppose que TOUTES les conditions sont vraies, ce qui n’est pas l’objectif poursuivi. La fonction SIGN convertit la somme des conditions (donc une somme de 1 et 0) à 1 si au moins une des conditions est vraie et à 0 si aucune n’est vraie, ce qui est l’objectif poursuivi et ce, sans utiliser de fonctions matricielles.