Fonction SI – Utiliser des formules imbriquées et éviter les pièges

Fonction SI – Utiliser des formules imbriquées et éviter les pièges

Important :  Cet article a été traduit automatiquement, voir l’avertissement. Vous pouvez consulter la version en anglais de cet article ici.

La fonction SI permet d’effectuer une comparaison logique entre une valeur et une hypothèse en testant une condition et en renvoyant un résultat Vrai ou Faux.

  • =SI(quelque chose est vrai, effectuer telle action, sinon effectuer telle autre action)

Pour une instruction IF peut avoir deux résultats. Le premier résultat est si votre comparaison est vraie, la seconde si votre comparaison est faux.

Bien que les instructions SI soient extraordinairement puissantes et constituent la base de nombreux modèles de feuille de calcul, elles sont également à l’origine de nombreux problèmes de feuille de calcul. Dans l’idéal, une instruction SI doit s’appliquer à des conditions minimales, telles que Homme/Femme, Oui/Non/Peut-être, mais il arrive que vous deviez évaluer des scénarios plus complexes nécessitant une imbrication* de plus de 3 fonctions.

* Le terme « imbrication » fait référence à la pratique consistant à joindre plusieurs fonctions au sein d’une même formule.

Utilisez la fonction SI, une des fonctions logiques, pour renvoyer une valeur si une condition est vraie et une autre valeur si elle est fausse.

Syntaxe

SI(test_logique; valeur_si_vrai; [valeur_si_faux])

Par exemple :

  • =SI(A2>B2;"Dépasse le budget";"OK")

  • =SI(A2=B2;B4-A4;"")

Argument

Description

test_logique   

(obligatoire)

Condition que vous souhaitez tester.

valeur_si_vrai   

(obligatoire)

Valeur que vous voulez renvoyer si le résultat de test_logique est VRAI.

valeur_si_faux   

(facultatif)

Valeur que vous voulez renvoyer si le résultat de test_logique est FAUX.

Remarques

Tandis que Excel vous permettra d’imbriquer jusqu'à 64 fonctions si différentes, il n’est pas du tout recommandé pour le faire. Pourquoi ?

  • Plusieurs instructions IF requièrent un grand nombre de réflexion pour générer correctement et assurez-vous que leur logique calcule correctement à chaque condition jusqu'à la fin. Si vous ne pas imbriquer votre formule 100 % avec précision, puis elle peut fonctionnent avec 75 % de l’heure, mais retourner des résultats inattendus 25 % du temps. Malheureusement, les chances de vous interception les 25 % sont très faible.

  • Plusieurs instructions IF peuvent devenir très difficiles à mettre à jour, en particulier lorsque vous revenez quelques instants plus tard et essayez d’identifier les éléments, ou pire quelqu'un d’autre, a essayé d’effectuer.

Si vous vous retrouvez avec une instruction SI qui semble croître à l’infini, il est temps de poser votre souris et de repenser votre stratégie.

Nous allons voir comment créer correctement une instruction SI complexe imbriquant plusieurs fonctions SI, et quand reconnaître le moment est venu d’opter pour un autre outil de votre arsenal Excel.

Exemples

Voici un exemple d’instruction SI imbriquée relativement standard destinée à convertir un résultat de test d’étudiant en note alphabétique équivalente.

Instruction SI imbriquée complexe - La formule dans la cellule E2 est =SI(B2>97;"A+";SI(B2>93;"A";SI(B2>89;"A-";SI(B2>87;"B+";SI(B2>83;"B";SI(B2>79;"B-";SI(B2>77;"C+";SI(B2>73;"C";SI(B2>69;"C-";SI(B2>57;"D+";SI(B2>53;"D";SI(B2>49;"D-";"F"))))))))))))
  • =SI(D2>89;"A";SI(D2>79;"B";SI(D2>69;"C";SI(D2>59;"D";"F"))))

    Cette instruction SI imbriquée complexe suit une logique simple :

  1. Si le résultat du test (dans la cellule D2) est supérieur à 89, l’étudiant reçoit la note A

  2. Si le résultat du test est supérieur à 79, l’étudiant reçoit la note B

  3. Si le résultat du test est supérieur à 69, l’étudiant reçoit la note C

  4. Si le résultat du test est supérieur à 59, l’étudiant reçoit la note D

  5. Autrement, l’étudiant reçoit la note F

Cet exemple est relativement sûr car il n’est pas probable que la corrélation entre les tests et notes de lettre changeront, afin qu’il ne nécessite pas beaucoup de maintenance. Mais il y a une idée : que se passe-t-il si vous deviez segmenter les notes entre A + A et A - (et ainsi de suite) ? Votre instruction si quatre condition doit maintenant être réécrits pour avoir 12 conditions ! Voici ce que votre formule donnera maintenant :

  • =SI(B2>97;"A+";SI(B2>93;"A";SI(B2>89;"A-";SI(B2>87;"B+";SI(B2>83;"B";SI(B2>79;"B-";SI(B2>77;"C+";SI(B2>73;"C";SI(B2>69;"C-";SI(B2>57;"D+";SI(B2>53;"D";SI(B2>49;"D-";"F"))))))))))))

Il est toujours fonctionne avec une précision et fonctionne comme prévu, mais prend beaucoup de temps à écrire et plus de temps à tester pour vous assurer que c’est ce que vous voulez. Un autre problème criante est que vous avez obtenu entrer manuellement les scores et les notes de lettre équivalente. Quelles sont les chances que vous aurez accidentellement une faute d’orthographe ? Imaginons que vous essayez d’effectuer cette fois 64 avec des conditions plus complexes ! Bien sûr, il est possible, mais voulez-vous vraiment vous-même soumis à ce type d’effort et les erreurs éventuelles qui sera vraiment difficiles à ton direct ?

Conseil : Toutes les fonctions dans Excel nécessite un les parenthèses () ouvrant et fermant. Excel essaiera d’aide que vous savez où vont les données en couleurs différentes parties de votre formule lorsque vous modifiez celui-ci. Par exemple, si vous deviez modifier la formule ci-dessus, en tant que vous déplacez le curseur au-delà de chacun des parenthèses fin ») », sa parenthèse ouvrante correspondante se transforme la même couleur. Cela peut être particulièrement utile dans les formules imbriquées complexes lorsque vous essayez de déterminer si vous avez suffisamment parenthèses correspondantes.

Exemples supplémentaires

Voici un exemple très courant de calcul de commission de ventes basé sur les niveaux de chiffre d’affaires accompli.

La formule dans la cellule D9 est SI(C9>15000;20%;SI(C9>12500;17,5%;SI(C9>10000;15%;SI(C9>7500;12,5%;SI(C9>5000;10%;0)))))
  • =SI(C9>15000;20%;SI(C9>12500;17,5%;SI(C9>10000;15%;SI(C9>7500;12,5%;SI(C9>5000;10%,0)))))

Cette formule signifie SI (C9 est supérieur à 15 000 retourner 20 %, SI (C9 est supérieur à 12 500 retourner 17,5 %, et ainsi de suite...

Alors qu’il est très similaire à la version antérieure notes exemple, cette formule est un excellent exemple de combien il est difficile à mettre à jour les instructions de grande taille IF – que devez-vous faire si votre organisation a décidé d’ajouter de nouveaux niveaux de rémunération et éventuellement même changer la devise existant ou les valeurs en pourcentage ? Que c’est un grand nombre de travail sur votre mains !

Conseil : Vous pouvez insérer des sauts de ligne dans la barre de formule pour faciliter la lecture des formules de temps. Appuyez sur ALT + entrée avant le texte que vous voulez renvoyer à la ligne dans une nouvelle ligne.

Voici un exemple du scénario de calcul de commission dont la logique est désordonnée :

La formule dans la cellule D9 n’est pas fonctionnelle sous la forme =SI(C9>5000;10%;SI(C9>7500;12,5%;SI(C9>10000;15%;SI(C9>12500;17,5%;SI(C9>15000,20%,0)))))

Vous voyez quel est le problème ? Comparer l’ordre des comparaisons chiffre d’affaires à l’exemple précédent. De quelle façon s’une agit-il ? C’est vrai, il va à partir du bas vers le haut (5 000 $ à 15 000 $), pas le sens inverse. Mais pourquoi qui doivent être importante ? Il est très important parce que la formule ne peut pas passer la première évaluation pour toute valeur supérieure 5 000 $. Supposons que vous avez 12 500 $ de recettes – l’instruction si renverra 10 %, car elle est supérieure à 5 000 € et s’y arrête. Cela peut être très problématique, car dans un grand nombre de situations ces types d’erreurs passer inaperçus jusqu'à ce qu’ils eu un impact négatif. Que faire pour sachant qu’il est certains pièges graves avec des instructions IF imbriquées complexes ? Dans la plupart des cas, vous pouvez utiliser la fonction RECHERCHEV au lieu de créer une formule complexe avec la fonction si. L’utilisation de RECHERCHEV, vous devez d’abord créer une table de référence :

La formule dans la cellule D2 est =RECHERCHEV(C2;C5:D17;2;VRAI)
  • =RECHERCHEV(C2;C5:D17;2;VRAI)

Cette formule indique rechercher la valeur de C2 dans la plage C5:C17. Si la valeur est trouvée, puis renvoyer la valeur correspondante de la même ligne dans la colonne D.

La formule dans la cellule C9 est =RECHERCHEV(B9;B2:C6;2;VRAI)
  • =RECHERCHEV(B9;B2:C6;2;VRAI)

De même, cette formule recherche la valeur dans la cellule B9 dans la plage B2:B22. Si la valeur est trouvée, puis renvoyer la valeur correspondante de la même ligne dans la colonne C.

Remarque : Deux de ces VLOOKUPs utilisent l’argument TRUE à la fin des formules, ce qui signifie que nous souhaitons lui pour rechercher une correspondance approxiate. En d’autres termes, elle donneront les valeurs exactes dans la table de choix, ainsi que toutes les valeurs comprises entre elles. Dans ce cas les tables de recherche doivent être triées par ordre croissant, du plus petit au plus grand.

RECHERCHEV vous trouverez davantage de détails ici, mais il s’agit bien sûr beaucoup plus simple à une instruction si imbriquée au niveau de 12, complexe ! Il existe également d’autres avantages moins évidents :

  • Les tables de références RECHERCHEV sont bien visibles.

  • Vous pouvez aisément mettre à jour leurs valeurs sans jamais devoir modifier la formule en cas de modification des conditions.

  • Si vous ne souhaitez pas que des utilisateurs puissent afficher votre table de référence ou interférer avec celle-ci, placez simplement la table dans une autre feuille de calcul.

Le saviez-vous ?

Il est à présent une fonction IFS pouvant remplacer plusieurs, imbriquée instructions IF avec une seule fonction. Donc au lieu de notre exemple notes initial, qui comporte des fonctions si imbriquées 4 :

  • =SI(D2>89;"A";SI(D2>79;"B";SI(D2>69;"C";SI(D2>59;"D";"F"))))

Nous pouvons simplifier sensiblement la formule en utilisant une seule fonction SI.CONDITIONS comme suit :

  • =SI.CONDITIONS(D2>89;"A";D2>79;"B";D2>69;"C";D2>59;"D";VRAI;"F")

La fonction SI.CONDITIONS est idéale, car elle permet de ne plus se soucier des instructions SI et des parenthèses.

Remarque : Cette fonctionnalité est uniquement disponible si vous avez souscrit un abonnement Office 365. Si vous êtes abonné à Office 365, vérifiez que vous avez la dernière version d’Office.

Essayez Office 365 ou la dernière version d’Excel

Vous avez besoin d’une aide supplémentaire ?

Vous pouvez toujours consulter un expert de la communauté technique Excel, obtenir une assistance dans la communauté Answers ou suggérer une nouvelle fonctionnalité ou une amélioration sur le forum Excel User Voice.

Rubriques connexes

Vidéo : Fonctions si avancées
fonction IFS (Office 365, Excel 2016 et versions ultérieur)
The Nb.Si comptera des valeurs basées sur un critère unique
Nb.Si.ens le comptera des valeurs basées sur multiple critères
la fonction somme.Si additionner des valeurs basées sur un critère unique
somme.Si.ens le additionner les valeurs en fonction de plusieurs critères
fonction et
fonction ou
Fonction RECHERCHEV
vue d’ensemble des formules dans Excel
comment éviter les formules incorrectes
erreurs pour détecter les erreurs dans les formules
fonctions logiques
Fonctions Excel (par ordre alphabétique)
fonctions Excel (par catégorie)

Remarque : Avertissement traduction automatique : cet article a été traduit par un ordinateur, sans intervention humaine. Microsoft propose cette traduction automatique pour offrir aux personnes ne maîtrisant pas l’anglais l’accès au contenu relatif aux produits, services et technologies Microsoft. Comme cet article a été traduit automatiquement, il risque de contenir des erreurs de grammaire, de syntaxe ou de terminologie.

Développez vos compétences
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×