Excel | fonctions conditionnelles

Les fonctions conditionnelles permettent de définir des résultats différents ou des calculs adaptés selon le contexte des cellules sources. Nous utiliserons un classeur exemple pour tester les différentes fonctions que nous allons passer en revue dans cet article.

Le classeur d’appui de cette séquence est disponible sur le lien ci-dessous :

Fonctions : SI – ET – OU – SI.CONDITIONS – CHOISIR – SIERREUR -GAUCHE

C’est dans l’onglet « Diapos » que vous trouverez les exemples à utiliser.

Plan :

Si, SI(ET… , Si(OU…

Cas simple (si … alors … Sinon …)

Les arguments* de la fonction sont au nombre de 3 :

• Test logique : c’est ici que nous allons définit nos conditions
• Valeur_si_vrai : (facultatif car entre crochets) nous indiquerons ici ce que la cellule doit afficher si le test réussit (si nos conditions sont remplies). il peut s’agir d’un calcul, d’une valeur, de l’affichage du contenu d’une autre cellule ou d’un renvoi à une autre cellule (par le biais d’un lien hypertexte). Si nous n’indiquons rien, la cellule affichera « 0 » (zéro).
• Valeur_si-faux : nous indiquerons ici ce que la cellule doit afficher si notre test échoue (conditions non remplies). Si nous n’indiquons rien, la cellule affichera « FAUX »

Dans notre exemple, nous testerons si les commerciaux ont réalisé plus de deux ventes. Si le teste réussit, nous afficherons la valeur « 100 » (prime de 100 €), sinon la valeur « 0 » (pas de prime).

• sélectionner les cellules E3 et E4
• Appuyer sur F2 pour activer le mode saisie (fn + F2 sur les portables)
• saisir « =si( » (sans les guillemets)

Pour savoir si nos commerciaux ont effectué plus de 2 ventes, il faut tester l’information contenue dans la colonne C et la comparer au seuil à attendre :

• Cliquer sur C3

la cellule active (celle sur laquelle nous écrivons notre fonction étant en ligne 3, nous allons nous concentrer uniquement sur cette ligne. C’est en recopiant notre calcul à la fin que la fonction s’adaptera aux différentes références (lignes) de ventes du tableau.

• Saisir « >2 » (supérieur à deux)
• Saisir « ; » pour changer d’argument (valeur_si_vrai passe en gras)

• saisir « 100 » (la prime si C3 et supérieure à 2)
• Saisir « ; » (pour changer d’argument)
• Saisir « 0 » (la prime si C3 n’est pas supérieure à 2)

• fermer la parenthèse
• Valider par CTRL + Entrée (pour propager le calcul sur toute la plage résultats)

Revenir au plan

Conditions cumulées Si(Et… ou alternées Si(Ou…

Bien souvent, le nombre de conditions à respecter n’est pas singulier. On peut inclure dans notre test la fonction OU si le test doit respecter une condition ou une autre (ou bien encore une troisième, voire plus) ou la fonction ET si toutes les conditions doivent être respectées.

Attention, les fonctions OU et ET se codent comme les autres fonctions : le nom de la fonction, sauf qu’il ne sera pas précédé du signe égal puisqu’il est imbriqué dans une autre, puis les arguments entre parenthèses séparés par des points-virgules :

ET(argument1; argument2…)

Argument1 ET Argument2 ….

• cliquer sur le plus en face de la ligne 11 pour afficher les lignes 6 à 10
• sélectionner les cellules E8:E9
• F2
• saisir « =si(ou(« 

Nous voulons définir une prime si le nombre de ventes en colonne C est supérieur à 2 ou si le chiffre d’affaires en colonne D est supérieur ou égal à 3000 €.

• cliquer sur C8
• saisir « >2 » (oui, c’est comme tout à l’heure)
• saisir « ; » pour changer d’argument
• cliquer sur D8
• Saisir « >=3000« 
• fermer la parenthèse du OU

• saisir « ; » (pour passer à l’argument valeur_si_vrai)
• saisir « 100 » (c’est la prime si l’une des conditions au moins est réussie)
• saisir « ;« 
• Saisir « 0 » (pas de prime si aucune des conditions n’est atteinte)
• Fermer la parenthèse du SI
• Valider par CTRL + Entrée (pour propager le calcul à toute la plage

revenir au plan

Conditions variables

Vous êtes toujours là ? Bon, alors, compliquons un peu les choses. Imaginons que les conditions soient variables ! Dans cet exemple (développez les lignes 12 à 19), un second tableau indique des données que l’on pourra faire varier et auxquelles les calculs devront s’adapter (en l’occurrence : un nombre de ventes; un seuil pour la prime et un pourcentage de CA à attribuer en prime).

• cliquer sur le plus en face de la ligne 20 pour afficher les lignes 12 à 19
• sélectionner les cellules E13:E14
• F2
• saisir « =si(ou(« 
• cliquer en C13
• Saisir « « >= »
• cliquer en C17
• appuyer sur F4 pour figer la référence de cellule (des $ apparaissent devant la référence de colonne et de ligne)

Figer la référence de cellule signifie bloquer l’implémentation lors de la recopie. Par défaut, une référence à A1 recopiée sur la ligne du dessous devient une référence à B1 puis C1, etc… On parle de référence relative. En bloquant par F4 la référence, on la rend absolue.

• Saisir « ; » pour passer à la 2nde condition du OU
• Cliquer en D13 (le CA réalisé par Léo)
• Saisir « >=« 
• Cliquer en D17 (le CA à atteindre)
• Figer par F4
• fermer la parenthèse du OU

• saisir « ; » pour terminer le test du SI
• cliquer sur C18 pour collecter le montant de la prime de ventes
• Figer par F4
• saisir « + » (on va cumuler avec le % du CA)
• cliquer en D13 pour prendre le montant du CA réalisé par Léo
• Saisir « * » (on va multiplier par le %)
• cliquer en D18 (le fameux %)
• Figer par F4

• saisir « ; » pour fermer l’argument valeur_si_vrai
• saisir « 0 » (pas de prime si le test échoue)
• Fermer la parenthèse du SI
• Valider par Ctrl + Entrée pour propager le calcul

On pourra désormais changer les seuils dans le tableau B16:D18 et les calculs de primes s’adapteront.

Revenir au plan

Conditions multiples

La fonction Si.CONDITIONS permet de définir plusieurs « couples » de SI + valeur_si_vrai. Elle est apparue dans le version 2016 est permet d’éviter les « poupées gigognes » de SI imbriqués dans d’autres SI. Elle fonctionne ainsi :

• Test_logique_x : la condition à tester
• Valeur_si_vrai_x : le résultat à afficher si le test de l’argument précédent fonctionne

Il faudra partir de la valeur la plus haute (4000 et +) en 1er test car si nous partons du test CA >= 1000, la condition étant aussi remplie pour les paliers suivants, Excel n’ira jamais regarder le test 2.

Autre problème, l’étiquette « 4000 ou + » n’est pas une valeur numérique. Nous contournerons la difficulté en utlisant la fonction Gauche qui fonctionne ainsi :

• Texte : la cellule qui contient le texte dont on veut prélever un certain nombre de caractères en partant de la gauche
• [no car] : le nombre de caractères à prélever (1 seul si omis)

pour obtenir la partie « 4000 » dans la cellule « 4000 et + » en C38, on écrira :

=GAUCHE(C38;4)

• cliquer sur le plus en face de la ligne 40 pour afficher les lignes 30 à 39 (si ce n’est déjà fait)
• sélectionner les cellules D31:D32
• saisir « =SI« 
• Sélectionner SI.CONDITIONS dans la liste

• Saisir « GAUCHE(« 
• Cliquer en C38 (4000 et +)
• Figer par F4
• saisir « ;4) » (on prend les 4 premières caractères pour ne garder que le nombre 4000)
• Saisir « ; » pour fermer le test_logique1
• cliquer sur D38 (les 7 %)

• saisir « ; » pour atteindre le test_logique2
• cliquer sur C31 (le CA de Léo)
• Saisir « >=« 
• cliquer sur C37 (on remonte tous les paliers un par un)
• Figer par F4
• Saisir « ;« 

continuer l’opération pour obtenir la formule suivante :

on multiplie à la fin le SI.CONDITIONS par le CA pour obtenir la prime.

Revenir au plan

conditions indexées

Il est possible de conditionner le comportement d’une cellule au numéro d’index figurant dans une autre grâce à la fonction CHOISIR

• no_index : c’est la cellule qui contient la valeur comprise entre 1 et 127 qui permettra à la fonction de choisir quelle valeur adoptée
• Valeur1 : le résultat à afficher dans la cellule si l’index est égal à 1
• [Valeur2] : le résultat à afficher dans la cellule si l’index est égal à 2, etc…

=CHOISIR(D42;$D$46;$D$47;$D$48;$D$49)*C42

Voilà, tous vos avis et commentaires seront appréciés. Bon courage à tous !