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

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

Remarque : Nous faisons de notre mieux pour vous fournir le contenu d’aide le plus récent aussi rapidement que possible dans votre langue. Cette page a été traduite automatiquement et peut donc contenir des erreurs grammaticales ou des imprécisions. Notre objectif est de faire en sorte que ce contenu vous soit utile. Pouvez-vous nous indiquer en bas de page si ces informations vous ont aidé ? Voici l’article en anglais à des fins de référence aisée.

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)

Une instruction SI peut donc avoir deux résultats. Le premier résultat est appliqué si la comparaison est vérifiée, sinon le deuxième résultat est appliqué.

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

Si Excel autorise l’imbrication d’un maximum de 64 fonctions SI différentes, nous vous déconseillons de le faire. Pourquoi ?

  • L’utilisation de plusieurs instructions SI nécessite beaucoup de réflexion pour créer celles-ci de façon appropriée et s’assurer que leur logique se calcule correctement, condition après condition. Si vous n’imbriquez pas votre formule avec une précision de 100 %, il se peut qu’elle fonctionne dans 75 % des cas, mais renvoie des résultats inattendus dans les 25 % de cas restants. Malheureusement, les chances que vous déceliez ces 25 % d’occurrences sont minces.

  • Il peut s’avérer très difficile de gérer des instructions SI multiples, en particulier si vous y revenez après un certain temps en essayant de comprendre ce que vous, ou pire une autre personne, avez voulu faire précisément.

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 particulier est relativement sécurisé, car il n'est pas probable que la corrélation entre les notes de test et les notes de lettres changera, de sorte qu'il ne nécessite pas beaucoup de maintenance. Voici une idée: que faire si vous devez segmenter les notes entre A +, A et A-(et ainsi de suite)? À présent, vos quatre conditions si l'instruction doit être réécrite pour avoir 12 conditions. Voici à quoi ressemble votre formule 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"))))))))))))

Elle est toujours fonctionnellement exacte et opère comme prévu, mais il faut beaucoup de temps pour l’écrire et la tester afin de s’assurer qu’elle fait bien ce que vous souhaitez. Un autre problème évident est que vous avez dû entrer les résultats et les notes correspondantes manuellement. Quelles sont les chances que vous fassiez accidentellement une faute de frappe ? Imaginez que vous deviez faire cela 64 fois avec des conditions plus complexes. C’est certainement possible, mais voulez-vous vraiment vous imposer ce type d’effort et vous exposer à des erreurs éventuelles qui seront très difficiles à repérer ?

Conseil : Dans Excel, chaque fonction doit figurer entre parenthèses (). Excel essaie de vous aider à comprendre l’emplacement des différents éléments de votre formule en les colorant à mesure que vous les modifiez. Par exemple, si vous devez modifier la formule ci-dessus, lorsque vous déplacez le curseur au-delà de chacune des parenthèses fermantes « ) », la parenthèse ouvrante correspondante prend la même couleur. Cela est particulièrement utile dans les formules imbriquées complexes, lorsque vous tentez de déterminer si vous avez suffisamment de 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...

Bien qu’elle soit très similaire à la formule de conversion de résultats chiffrés en notes alphabétiques de l’exemple précédent, cette formule montre bien à quel point il peut être difficile de gérer des instructions SI longues. Que faire si votre organisation décide d’ajouter des niveaux de rémunération, voire même de modifier les montants ou les pourcentages existants ? Cela peut nécessiter beaucoup de travail.

Conseil : Vous pouvez insérer des sauts de ligne dans la barre de formule pour faciliter la lecture des formules longues. Appuyez simplement sur ALT+Entrée devant le texte qui doit figurer sur 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)))))

Pouvez-vous voir ce qui est incorrect? Comparez l'ordre des comparaisons de revenus à l'exemple précédent. De quelle façon cela fonctionne-t-il? C'est là, il va de bas ($5 000 à $15 000), et pas l'inverse. Quelle est la raison pour laquelle cela devrait être une véritable affaire? Il s'agit d'une grande affaire, car la formule ne peut pas transmettre la première évaluation pour une valeur supérieure à $5 000. Imaginons que vous obteniez $12 500 en recettes: la déclaration si renvoie 10% car elle est supérieure à $5 000, et elle s'arrête là. Cela peut être extrêmement problématique, car dans de nombreux cas, ces types d'erreurs passent inaperçus jusqu'à ce qu'ils aient un impact négatif. Sachant qu'il y a des pièges sérieux avec des instructions si imbriquées complexes, que pouvez-vous faire? Dans la plupart des cas, vous pouvez utiliser la fonction RECHERCHEV au lieu de créer une formule complexe à l'aide de la fonction si. À l'aide de la fonction RECHERCHEV, vous devez commencer par 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 comment rechercher la valeur de C2 dans la plage C5: C17. Si la valeur est trouvée, renvoyer la valeur correspondante à partir 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 figurant dans la cellule B9 de la plage B2:B22. Si la valeur est trouvée, elle renvoie la valeur correspondante de la même ligne dans la colonne C.

Remarque : Ces deux formules RECHERCHEV utilisent l’argument VRAI à la fin, ce qui signifie que nous souhaitons qu’elles recherchent une correspondance approximative. En d’autres termes, elles trouvent les valeurs exactes dans la table de recherche, ainsi que les valeurs s’inscrivant entre les valeurs exactes. Dans ce cas, les tables de recherche doivent être triées dans l’ordre croissant, soit de la valeur la plus petite à la plus grande.

La fonction RECHERCHEV est décrite de manière beaucoup plus détaillée ici, mais il s’agit assurément d’une solution beaucoup plus simple qu’une instruction SI imbriquée complexe à 12 niveaux. Elle présente é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 existe désormais une fonction IFS qui peut remplacer plusieurs instructions si imbriquées par une fonction unique. Par conséquent, au lieu de notre exemple de notes initial, il inclut 4 fonctions si imbriquées:

  • =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 si. multiple(Office 365, Excel 2016 et versions ultérieures)
la fonction nb.si compte les valeurs sur la base d'un seul critère
la fonction nb.si compte les valeurs sur la base de plusieurs critères
la fonction somme.si additionne des valeurs sur la base d'un seul critère
la fonction ens additionne des valeurs en fonction de différents critères
et fonction
ou fonction

Fonction RECHERCHEV:vue d'ensemble des formules dans Excel
Comment éviter les formules
incorrectes pourdétecter les erreurs dans les formules
.fonctions
logiques Fonctions Excel (par ordre alphabétique)
fonctions Excel (par catégorie)

Développez vos compétences dans Office
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.

×