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 SIGN ou SIGNE, imbriquée dans une fonction SUMPRODUCT ou SOMMEPROD, estl’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 SIGN ou SIGNE
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 SIGN ou SIGNE
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.
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:
Télécharger le fichier Excel
Vous pouvez télécharger ce fichier Excel.
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.
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.
Consulter la liste des formations offertes
Pour info: 514-605-7112 ou [email protected]
Jeu de briques
Snake
Pacman
Bubble