Découvrir la fonction SI() par l’exemple sur Excel (5 exemples à la difficulté progressive)
Dans plusieurs tutoriels précédents, nous avons déjà eu l’occasion de découvrir la fonction SI(). À la suite de ces tutoriels, vous avez été plusieurs à me demander de revenir sur celle-ci afin d’illustrer son fonctionnement avec d’autres exemples.
C’est ce que je vais vous montrer à présent avec cinq exemples d’utilisation de la fonction SI() à la difficulté progressive.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. La fonction SI
Aujourd’hui, nous allons revenir sur l’une des fonctions les plus utiles d’Excel, mais qui reste pourtant crainte par un grand nombre d’utilisateurs : la fonction SI().
Nous avions déjà consacré tout un tutoriel à cette dernière, il y a maintenant pas mal de temps, et nous avions alors vu que celle-ci permet d’effectuer un test logique afin de retourner un résultat différent en fonction de ce dernier.
Elle attend ainsi trois arguments :
SI(Test_logique;[Valeur_si_vrai];[Valeur_si_faux])
Où :
- Test_logique est le test qu’Excel effectue afin de déterminer la valeur à retourner. Ce test retourne soit la valeur « VRAI », soit la valeur « FAUX ». Il peut s’agir soit d’un test effectué à l’aide d’un opérateur logique (exemples : « 1=2 » retourne « FAUX », alors que « 1<2 » retourne « VRAI »), soit d’un test effectué à l’aide d’une fonction prévue pour retourner l’une de ces deux valeurs (exemple : « ESTERREUR(#N/A) » retourne « VRAI », « ESTVIDE(A1) » retourne « VRAI » ou « FAUX » en fonction de la cellule A1).
- Valeur_si_vrai est la valeur qui doit être retournée par Excel lorsque le premier paramètre de la fonction (Test_logique) est « VRAI »
- Valeur_si_faux est la valeur qui doit être retournée par Excel lorsque le premier paramètre de la fonction (Test_logique) est « FAUX »
Après ce rapide retour sur la fonction SI(), découvrons quelques exemples qui vont nous permettre d’appréhender celle-ci de manière simple.
2. Exemples
2.1. Calcul de prime
Pour ce premier exemple, nous allons nous intéresser aux primes versées par une entreprise.
Nous considérons en effet qu’une entreprise consent à verser une prime à ces employés commerciaux ayant obtenu de bons résultats de ventes au cours de l’année 2021.
Le montant de cette prime est de 2000€, et elle sera versé à tous les employés ayant écoulé au moins 100k€ de produits auprès de leurs clients :
Comme nous l’avons vu un peu plus tôt, l’utilisation de la fonction SI() repose sur la réalisation d’un test qui pourra aboutir sur un résultat de type booléen, c’est-à-dire dont les valeurs seront soit VRAI, soit FAUX.
La manière la plus simple de réaliser un test consiste à utiliser les opérateurs de comparaison (=, <, >, <=, >=).
Ici, avant de déterminer le montant de la prime nous allons souhaiter réaliser ce test, en regardant si la valeur des ventes réalisées par Paul sont supérieures ou égales au montant générant le déclenchement du versement de la prime :
=B11>=B12
Ici, comme nous pouvions nous en douter, Paul n’a pas réalisé suffisamment de ventes pour prétendre au versement de la prime, la formule nous retourne donc le résultat FAUX.
Mais maintenant, imaginons que Paul ait réalisé 120k€ de ventes :
Le résultat sera alors égal à VRAI.
Maintenant que nous avons mis en place ce test, nous allons pouvoir calculer de manière automatique le montant de la prime perçue par Paul.
En effet, lorsque la valeur de la cellule B15 est égale à VRAI, alors nous pourrons récupérer la valeur de la cellule B13, alors qu’à l’inverse, lorsque ce résultat sera égal à FAUX, nous retournerons zéro :
=SI(B15;B13;0)
Dans cette hypothèse, Paul touchera bien la prime promise.
Évidemment, le test permettant de déterminer le montant de la prime peut être inséré directement dans la fonction SI(), ce qui permettra alors de ne pas avoir à utiliser une cellule intermédiaire pour effectuer ce calcul, et ce qui se traduira par un gain de temps :
=SI(B11>=B12;B13;0)
Le résultat sera ici strictement identique !
2.2. Obtenir un texte différent avec la fonction SI() d'Excel
Pour ce deuxième exemple, nous allons voir comment retourner un texte différent avec la fonction SI(), en fonction du résultat d’une cellule.
Pour cela, nous allons partir du bulletin de notes d’élèves, pour lesquels nous souhaiterons proposer soit le passage dans la classe supérieure, lorsque celui-ci a obtenu au moins 10/20 de moyenne générale, soit le redoublement dans le cas contraire :
Comme pour le premier exemple, nous allons tout d’abord regarder si l’élève a obtenu la moyenne en comparant sa moyenne générale avec 10 :
=B12>=10
Ensuite, lorsque la valeur de ce test est égale à VRAI, nous afficherons le mot « Passage » et « Redoublement » dans le cas contraire, en saisissant chacun de ces termes entre guillemets :
=SI(C12;"Passage";"Redoublement")
Ici aussi, nous préfèrerons n’utiliser qu’une seule formule pour réaliser cette opération :
=SI(B12>=10;"Passage";"Redoublement")
2.3. Cumuler les tests
Dans les exemples précédents, nous avons étudié des situations amenant des tests simples.
Mais évidemment, dans les faits, nous pouvons être confrontés à des analyses bien plus poussées, nécessitant la mise en place de plusieurs tests.
Nous revenons ici sur un exemple proche du tout premier, avec un calcul de prime.
Sauf qu’ici la règle de détermination est bien complexe. En effet, la règle d’attribution prévoit que la prime sera accordée aux commerciaux en fonction des critères suivants :
- L'employé doit avoir réalisé 150k€ de ventes au cours de la dernière année (2021)
- Ou l'employé doit avoir réalisé 120k€ de ventes en moyenne au cours des trois dernières années (2019 à 2021), dont 50k€ de ventes moyennes de produit A
Nous pouvons schématiser ce scénario de la manière suivante :
Pour construire cette formule, nous allons donc devoir réaliser plusieurs tests les uns à la suite des autres, en incorporant des fonctions SI() en tant que paramètres d’une fonction SI() principale :
=SI(B14>=150000;"Versement de la prime";SI(MOYENNE(B14:B16)>=120000;SI(MOYENNE(C14:C16)>=50000;"Versement de la prime";"Pas de prime");"Pas de prime"))
2.4. Utiliser des fonctions
La formule que nous avons créée dans la partie précédente permet effectivement d’obtenir le résultat attendu, mais comme vous pouvez le constater, celle-ci n’est pas simple à relire, ni à comprendre.
Pour simplifier à la fois sa création et sa relecture, nous pouvons utiliser les fonctions logiques :
- La fonction ET() permet de vérifier que plusieurs conditions sont remplies
- La fonction OU() permet de vérifier qu’au moins une condition est remplie
- La fonction OUX() permet de vérifier qu’un nombre impair de conditions est ou sont remplies
=SI(OU(B14>=150000;ET(MOYENNE(B14:B16)>=120000;MOYENNE(C14:C16)>=50000));"Versement de la prime";"Pas de prime")
Vous trouverez plus d’informations sur les fonctions logiques ici.
2.5. Utiliser une fonction SI() pour réaliser un calcul
Dans les exemples précédents, nous avons utilisé la fonction SI() pour déterminer si l’employé pouvait prétendre à une prime ou non.
Mais nous pouvons également l’utiliser pour déterminer le montant de cette prime.
En effet, dans ce dernier exemple le montant de la prime est composé de deux parties :
- Une partie fixe de 2000€
- Ainsi qu’une partie variable égale à 3% des ventes réalisées au-dessus de 100k€
Cette partie variable ne sera à calculer que si l’employé a réalisé plus de 100k€ de ventes pour éviter de fausser les calculs.
Le montant de la prime est donc déterminé de la manière suivante :
= 2000+SI(B14>100000;(B14-100000)*3%;0)
Que nous pouvons ensuite intégrer dans le calcul global de la prime :
=SI(OU(B14>=150000;ET(MOYENNE(B14:B16)>=120000;MOYENNE(C14:C16)>=50000));2000+SI(B14>100000;(B14-100000)*3%;0);"Pas de prime")
