Il s'agit des fonctions incontournables pour permettre aux formules de s'adapter selon différentes conditions.
Un article sur des notions de bases mais qui manquait sur ce site.
La fonction SI : La base
Une fonction SI permet de faire un choix entre 2 possibilités seulement.
Syntaxe
=SI(Test_logique ; Valeur_si_vrai ; Valeur_si_faux)- Test_logique : Valeur ou expression correspondante à une valeur booléenne (valeur VRAI ou FAUX).
- On peut utiliser les 5 opérateurs suivant <, <=, >, >=, =, <> (différent).
- On peut utiliser toute fonctions renvoyant des valeurs booléennes (ET, OU, EST.PAIR...).
- Valeur_si_vrai : Valeur ou expression renvoyée si Test_logique est égale à VRAI.
- Valeur_si_faux : Valeur ou expression renvoyée si Test_logique est égale à FAUX.
Cas simple sans imbrications
Exemple 1
Afficher "Admis" si une valeur (A2) est égale ou supérieur à 10, sinon ne rien afficher.
=SI(A2 > = 10;"Admis";"")Exemple 2
Si la valeur de la cellule A2 est égale à la lettre R (réduction), Excel fera le calcul B2*D2 sinon Excel fera le calcul C2*D2.
=SI(A2="R";B2;C2)*D2Cas complexe avec imbrications
La fonction SI permettant de faire seulement un choix entre 2 possibilités, comment faire avec 3, 4... possibilités ?
On va utiliser la méthode de dichotomie. En faisant une succession de choix simple (2 possibilités), on va parvenir à sélectionner le résultat désiré.
Principe
Par exemple si l'on a 4 choix possibles, on va devoir utiliser 3 fonctions SI imbriqués, une de moins que le nombre de cas. Chaque fonction SI imbriquée permettra, dans ce cas, "d'éliminer"/"choisir" une des valeurs de la liste, la dernière fonction SI s'occupera des 2 dernières valeurs restantes.
Exemple avec 4 possibilités
- Valeur inférieure 2 => on affiche "A".
- Valeur entre 2 et 3 => on affiche "B".
- Valeur entre 3 et 4 => on affiche "C".
- Valeur supérieure à 4 => on affiche "D".
Syntaxe 1
Ce "découpage" est celui que je conseille, on part d'une "extrémité" et on se décale vers l'autre en éliminant un cas toujours du côté du départ.
Avantages de cette structure logique
- Facilité de lecture
- Toutes les parenthèses se ferme à la fin (même nombre que de fonction SI).
=SI(A2<2;"A";SI(A2<3;"B";SI(A2<4;"C";"D")))Syntaxe 2
Plusieurs autres solutions sont possibles, par exemple en utilisant cette logique
Cette logique nécessite une profondeur d'imbrication de fonction plus faible que la logique précédente et pouvait être intéressante dans d'anciennes versions d'Excel ou l'on était plus limité à ce niveau.
- Format xls : Limité à 7 niveaux d'imbrication, soit 8 fonctions SI, soit
- 9 choix avec la 1ère syntaxe.
- 256 choix avec la 2ème syntaxe (Cette 2ème syntaxe semble plus avantageuse mais au-delà de 4/5 imbrications, d'autres solutions devraient être envisagées).
- Format moderne (xlsx...) : Limité à 64 niveaux d'imbrication, donc ce sera plus la longueur de la formule (nombre de caractères) qui sera limitant que par le nombre d'imbrication.
=SI(A2<3;SI(A2<2;"A";"B");SI(A2<4;"C";"D"))Inconvénients de cette structure logique
- Formule longue (rédaction longue, maintenance/correction plus complexe).
- Fait peur aux débutants qui confondent "formule longue" et "formule complexe".
Remarques :
- On trouve souvent des expressions inutilement complexes pour l'argument Test_logique comme "A2 = VRAI". Dans ce cas spécifique, un simple "SI( A2 ; ..." est suffisant vu que A2 contient déjà une valeur booléenne, inutile donc de chercher à en générer une nouvelle avec un test d'égalité).
- Une erreur classique que l'on rencontre dans ce type de formule est de vouloir tester les 2 bornes de chaque plage en imbriquant une fonction ET pour chaque test.
Exemple : =SI(A2<2;"A";SI( ET(A2>=2;A2<3) ;"B"; SI( ET(A2>=3;A2<4) ;"C";"D"))) ce qui complexifie inutilement la formule. - Une autre erreur classique est d'ajouter une fonction SI de trop à la fin ainsi =SI(A2<2;"A";SI(A2<3;"B";SI(A2<4;"C";SI A2>4;"D";???)))) (Que mettre à la place des "???" ?).
- Les fonctions ET et OU sont souvent utilisées dans l'argument Test_logique.
Exemple : On affiche le mot "Prime" pour toutes personnes dont l'ancienneté est supérieure à 20 ans sans aucune autre considération mais aussi pour les personnes ayant fait au moins +5 en objectif sans avoir plus de 9 jours d'absence (ici utilisation de cellules nommées).
=SI(OU(Ancienneté>20;ET(Objectif>4;Absence<10));"Prime";"")La fonction SI.CONDITIONS (version >=2019)
Permet de simplifier la formule avec SI imbriqués en évitant la réécriture des "SI(" et de la parenthèse finale correspondante pour les fonctions SI imbriqués.
Syntaxe
=SI.CONDITIONS(Test_logique1;Valeur_si_vrai1;[Test_logique2;Valeur_si_vrai2];…)- Test_logique1 : Valeur ou expression correspondante à une valeur booléen (valeur VRAI ou FAUX).
- Valeur_si_vrai1 : Valeur ou expression renvoyée si Test_logique1 est égale à VRAI.
Facultatif : - Test_logique2 à 127 : Valeur ou expression correspondante à une valeur booléen (valeur VRAI ou FAUX).
- Valeur_si_vrai2 à 127 : Valeur ou expression renvoyée si Test_logique2 à 127est égale à VRAI.
Si aucune condition test_logique n'est vérifiée la fonction renvoie la valeur #N/A.
Exemple avec le cas à 4 possibilités
=SI(A2<2;"A";SI(A2<3;"B";SI(A2<4;"C";"D")))
devient
=SI.CONDITIONS(A2<2;"A";A2<3;"B";A2<4;"C";VRAI;"D")Les arguments Test_logique sont évalués selon l'ordre de leur saisie, ainsi le dernier Test_logique est souvent la simple valeur VRAI renvoyant un Valeur_si_vrai qui correspondant ainsi à la valeur par défaut (évite le #N/A si aucune condition Test_logique n'est vérifiée).
Autre exemple
Dans un tableau structuré (voir A la découverte des tableaux structurés 1/2), on doit calculer une valeur de réduction de prix basée sur cette logique.
- Une personne handicapée bénéficie d'une réduction de 10%.
- Une personne de moins de 18 ans bénéficie d'une réduction de 5%.
=SI.CONDITIONS([@Handicapé];10%;[@Age]<18;5%;VRAI;0)La fonction SI.MULTIPLE (version >=2019)
Permet de simplifier la formule avec SI imbriqués et SI.CONDITIONS dans le cas particulier ou tous les tests logiques portent sur la même expression et que celle-ci est une simple égalité (pas de conditions complexes avec les opérateurs >, >=, <, <=, <>).
Syntaxe
=SI.MULTIPLE(Expression;Valeur1;Résultat1;[Défaut ou Valeur2; Résultat2];…)- Expression : Expression renvoyant une valeur qui sera comparée à Valeur1 à 126.
- Valeur1 à Valeur126 : Expression renvoyant une valeur qui sera comparée à Expression.
- Résultat1 à Résultat126 : Valeur à renvoyer lorsque l’argument ValeurN correspondant est conforme à l’expression
- Défaut : Dernier argument n'ayant pas d'argument Valeur lui correspondant, sert à renvoyer une valeur si aucune correspondance n’est trouvée dans les expressions précédente ValeurN.
Exemple
Afficher la tranche d'âge en fonction de la catégorie sportive (indiqué en colonne Catégorie dans un tableau structuré).
Version SI
=SI([@Catégorie]="Poussin";"9-10";SI([@Catégorie]="Benjamin";"11-12";
SI([@Catégorie]="Minime";"13-14";SI([@Catégorie]="Cadet";"15-16";
SI([@Catégorie]="Junior";"17-18";SI([@Catégorie]="Senior";"19-34";
SI([@Catégorie]="Vétéran";"35 et +";"")))))))
Version SI.CONDITIONS
=SI.CONDITIONS(
[@Catégorie]="Poussin";"9-10";[@Catégorie]="Benjamin";"11-12";
[@Catégorie]="Minime";"13-14";[@Catégorie]="Cadet";"15-16";
[@Catégorie]="Junior";"17-18";[@Catégorie]="Senior";"19-34";
[@Catégorie]="Vétéran";"35 et +";VRAI;"")
Version SI.MULTIPLE
=SI.MULTIPLE(
[@Catégorie];"Poussin";"9-10";"Benjamin";"11-12";
"Minime";"13-14";"Cadet";"15-16";"Junior";"17-18";
"Senior";"19-34";"Vétéran";"35 et +";"")La fonction CHOISIR
Ressemble à la fonction SI.MULTIPLE en plus "simple" et "limité.
Syntaxe
=CHOISIR(no_index;valeur1[;valeur2];...)- No_index : Argument correspondant à l'argument Expression de la fonction SI.MULTIPLE. N'accepte que des valeurs numériques entre 1 et 254.
- Valeur1 à Valeur254 : Valeur correspondantes au 254 valeurs possibles pour No_index.(exemple : Avec No_index = 3, la fonction CHOISIR renvoie la valeur Valeur3).
Comparaison à la fonction SI.MULTIPLE :
- Expression peut être numérique ou non => dans la fonction CHOISIR ce n'est que des valeurs numériques entre 1 et 254.
- Valeur1 à Valeur126 => dans la fonction CHOISIR c'est inutile vu que l'on sait que ce ne peut être que des valeurs entre 1 et 245.
- Résultat1 à Résultat126 => dans la fonction CHOISIR ce sont les arguments Valeur1 à Valeur254.
Exemple
Sur un ordinateur configuré Français, on veut afficher le jour de la semaine en anglais correspondante à la date saisie en A2.
=CHOISIR(JOURSEM(A2;2);"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday";"Sunday")Remarque, pour la version Française, il y a plus simple mais ça n'illustre pas bien l'utilisation de la fonction CHOISIR 😁.
=TEXTE(A2;"jjjj")La fonction SIERREUR
Permet d'"intercepter" une erreur dans une formule, traiter un cas spécifique/effet de bord qui provoque une erreur.
Syntaxe
=SIERREUR(Valeur;Valeur_si_erreur)- Valeur : Valeur ou expression à renvoyer.
- Valeur_si_erreur : Valeur ou expression à renvoyer si l'évaluation de l'argument Valeur génère une erreur (#N/A, #VALEUR!, #REF!, #DIV/0!, #NOMBRE!, #NOM?, #NUL!).
Exemple
On veut faire une simple division mais la valeur du diviseur, pouvant être égale à 0 dans certains cas ,rendant ainsi la division impossible, doit être "contrôlé" afin d'éviter une formule en erreur. Dans ce cas spécifique (division impossible) on veut afficher un texte d'alerte.
Solution avec la fonction SI :
=SI(A2=0;"A2 vide !";10/A2) ou =SI(A2;10/A2;"A2 vide !") car 0 <=> FAUX
Solution avec la fonction SIERREUR :
=SIERREUR(10/A2;"A2 vide !")Remarque :
- Les fonctions SI.NON.DISP joue le même rôle mais seulement avec le message 'information #N/A (= information non trouvée) renoyé par les fonctions de recherche comme RECHERCHEX, EQUIV, RCHERCHEV...
Les fonctions de recherche de valeurs
Dans certain cas il est tout à fait possible d'utiliser des fonctions comme INDEX, EQUIV, FILTRE, RECHERCHEX... (voir Les fonctions de recherche de valeur) pour remplacer avantageusement de fonctions conditionnelles.
Exemple
Afficher la tranche d'âge en fonction de la catégorie sportive (indiqué en colonne Catégorie dans un tableau structuré).
=INDEX({"9-10";"11-12";"13-14";"15-16";"17-18";"19-34";"35 et +";""};
SI.NON.DISP(EQUIV([@Catégorie];{"Poussin";"Benjamin";"Minime";"Cadet";"Junior";"Senior";"Vétéran"};0);
8))
ou
=SIERREUR(INDEX({"9-10";"11-12";"13-14";"15-16";"17-18";"19-34";"35 et +";""};
EQUIV([@Catégorie];{"Poussin";"Benjamin";"Minime";"Cadet";"Junior";"Senior";"Vétéran"};0));
"")Ici 2 constantes matricielles sont utilisées mais elles pourraient être remplacées par une plage/table annexe permettant de faire la correspondance entre le résultat du test/recherche et le résultat attendu.
Quand utiliser quoi !
- SI : cas simple quand il y a 3/4 choix au maximum.
- SI.CONDITION : quand il y a une dizaine de choix au maximum.
- SI.MULTIPLE : quand il y a une dizaine de choix au maximum utilisant le même test logique.
- CHOISIR : quand il y a une dizaine de choix au maximum utilisant le même test logique renvoyant des valeurs numériques de 1 à ...
- SIERREUR, SI.NON.DIS : que pour "intercepter" une erreur dans une formule (traiter un cas spécifique/ effet de bord).
- Les fonctions de recherche de valeurs : Permet de traiter un très grand nombre de choix mais nécessite la réalisation d'une table/tableau annexe pour faire la correspondance.
J'aurais pu ajouter les fonctions se terminant par ".SI" et ".SI.ENS" mais je vous renvoie à Une famille de fonctions incontournable, les ... .SI.ENS pour plus d'informations les concernant.
Merci pour votre attention bienveillante.
Un commentaire