Excel: À quoi pourrait bien vous servir la fonction SIGN ou SIGNE?

Publié le 14 septembre 2015
par Sophie Marchand M.Sc., CPA, CGA, MVP
Excel fonction SIGN

Excel: À quoi pourrait bien vous servir la fonction SIGN ou SIGNE?

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, est l’idéal. Cet article vise à démontrer comment.

 

À relire au besoin

Au besoin, je vous invite à relire les articles suivants:

 

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.
 
Excel fonction SIGN
 
 

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.
 
Excel fonction SIGN
 
 

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.

Excel fonction SIGN
 
 

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.
 
Excel fonction SIGN
 
 

À 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.
 
Excel fonction SIGN
 
 

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.
 
Excel fonction SIGN
 
 

Il est possible de résumer toute cette mécanique en une seule formule, qui est la suivante:
 
Excel fonction SIGN
 
 

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 :

 

Mélissa Paquin
Écrit il y a 4 semaines
Certains sujets aideront à améliorer les rapports actuels

Formation intéressante. Certains sujets aideront à améliorer les rapports actuels que j'utilise en tant qu'analyste financier.

Roger Martin Nguyen
Écrit il y a 5 mois
Exemples bien expliquées

Exemples bien expliquées et surtout, les exercices sont détaillées avec la démarche à suivre pour arriver au résultat.

Nathalie Tardif
Écrit il y a 5 mois
J'ai été cherché de nouvelles façons de faire qui vont optimiser mon travail.

Excellent! J'ai été cherché de nouvelles façons de faire qui vont optimiser mon travail. Merci beaucoup!

Lucie Dupuis
Écrit il y a 5 mois
La formation en ligne est idéale

Excellente formation qui donne des trucs pour des situations que nous pouvons et/ou avons rencontré dans notre travail. La formation en ligne est idéale car ça nous permet de réécouter/revoir les explications que nous n'avons pas bien saisi. La durée des modules n'est jamais trop longue.


CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

5 réflexions sur “Excel: À quoi pourrait bien vous servir la fonction SIGN ou SIGNE?”

  1. 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.

    1. 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

  2. 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.

  3. Francis Paquet, M.Sc., ing., EEE

    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.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top