Exceller

19 conseils pour les formules IF imbriquées

19 Tips Nested If Formulas

Les fonction SI est l'une des fonctions les plus utilisées dans Excel. IF est une fonction simple, et les gens adorent IF car cela leur donne le pouvoir de créer Excel répondre au fur et à mesure que les informations sont saisies dans un tableur. Avec IF, vous pouvez donner vie à votre feuille de calcul.





Mais un SI mène souvent à un autre, et une fois que vous combinez plusieurs SI, vos formules peuvent commencer à ressembler à de petits Frankensteins :)

Les SI imbriqués sont-ils mauvais ? Sont-ils parfois nécessaires ? Quelles sont les alternatives ?





Lisez la suite pour découvrir les réponses à ces questions et plus encore...

1. SI de base

Avant de parler du SI imbriqué, examinons rapidement la structure de base du SI :



 
= IF (test,[true],[false])

La fonction SI exécute un test et effectue différentes actions selon que le résultat est vrai ou faux.

Notez les crochets... cela signifie que les arguments sont facultatifs. Cependant, vous devez fournir Soit une valeur pour vrai ou une valeur pour faux.

Pour illustrer, ici, nous utilisons IF pour vérifier les scores et calculer « Réussite » pour les scores d'au moins 65 :

Fonction SI de base - retour

La cellule D3 de l'exemple contient cette formule :

 
= IF (C3>=65,'Pass')

Ce qui peut se lire ainsi : si le score en C3 est d'au moins 65, retournez 'Pass'.

Notez cependant que si le score est moins que 65, IF renvoie FALSE, car nous n'avons pas fourni de valeur si false. Pour afficher « Echec » pour les scores non réussis, nous pouvons ajouter « Echec » comme faux argument comme ceci :

 
= IF (C3>=65,'Pass','Fail')

Fonction SI de base - avec une valeur ajoutée pour false

Vidéo: Comment construire des formules logiques .

2. Qu'est-ce que l'imbrication signifie

L'imbrication signifie simplement combiner des formules, l'une dans l'autre, de sorte qu'une formule gère le résultat d'une autre. Par exemple, voici une formule où la fonction AUJOURD'HUI est imbriquée dans la fonction MOIS :

 
= MONTH ( TODAY ())

La fonction AUJOURD'HUI renvoie la date actuelle à l'intérieur de la fonction MOIS. La fonction MONTH prend cette date et renvoie le mois en cours. Même les formules modérément complexes utilisent fréquemment l'imbrication, vous verrez donc l'imbrication partout dans les formules plus complexes.

3. Un SI imbriqué simple

Un IF imbriqué n'est que deux instructions IF supplémentaires dans une formule, où une instruction IF apparaît dans l'autre.

Pour illustrer, ci-dessous, j'ai étendu la formule réussite/échec d'origine ci-dessus pour gérer les résultats « incomplets » en ajoutant une fonction SI et en imbriquant un SI dans l'autre :

Un SI imbriqué de base

 
= IF (C3='','Incomplete', IF (C3>=65,'Pass','Fail'))

L'IF externe s'exécute en premier et teste si C3 est vide. Si c'est le cas, le SI externe renvoie « Incomplet » et le SI interne ne s'exécute jamais.

Si le score est pas vide , le SI externe renvoie FALSE et la fonction SI d'origine est exécutée.

Apprenez les FI imbriqués avec formation vidéo claire et concise .

4. Un SI imbriqué pour les échelles

Vous verrez souvent des IF imbriqués configurés pour gérer des « échelles »... par exemple, pour attribuer des notes, des frais d'expédition, des taux de taxe ou d'autres valeurs qui varient sur une échelle avec une entrée numérique. Tant qu'il n'y a pas trop de niveaux dans l'échelle, les FI imbriqués fonctionnent bien ici, mais vous devez garder la formule organisée, ou elle devient difficile à lire.

L'astuce consiste à décider d'une direction (de haut en bas, ou de bas en haut), puis de structurer les conditions en conséquence. Par exemple, pour attribuer des notes dans un ordre « de faible à élevé », nous pouvons représenter la solution requise dans le tableau suivant. Notez qu'il n'y a pas de condition pour 'A', car une fois que nous avons parcouru toutes les autres conditions, nous savons que le score doit être supérieur à 95, et donc un 'A'.

But Classe État
0-63 F <64
64-72 <73
73-84 C <85
85-94 B <95
95-100 À

Avec les conditions bien comprises, nous pouvons entrer la première instruction IF :

moyenne d'une colonne dans Excel
 
= IF (C5<64,'F')

Cela prend en charge 'F'. Maintenant, pour gérer 'D', nous devons ajouter une autre condition :

 
= IF (C5<64,'F', IF (C5<73,'D'))

Notez que j'ai simplement déposé un autre IF dans le premier IF pour le résultat 'faux'. Pour étendre la formule pour gérer « C », nous répétons le processus :

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C')))

Nous continuons sur cette voie jusqu'à ce que nous atteignions la dernière année. Ensuite, au lieu d'ajouter un autre IF, ajoutez simplement la note finale pour false.

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Voici la formule IF imbriquée finale en action :

Exemple d'IF imbriqué terminé pour le calcul des notes

Vidéo: Comment faire un IF imbriqué pour attribuer des notes

5. Les FI imbriqués ont un flux logique

De nombreuses formules sont résolues de l'intérieur vers l'extérieur, car les fonctions ou expressions « internes » doivent d'abord être résolues pour que le reste de la formule continue.

Les FI imbriqués ont leur propre flux logique, puisque les FI « extérieurs » agissent comme une passerelle vers les FI « intérieurs ». Cela signifie que les résultats des IF externes déterminent si les IF internes s'exécutent même. Le diagramme ci-dessous visualise le flux logique de la formule de note ci-dessus.

Le flux logique d'un SI imbriqué

6. Utilisez Évaluer pour observer le flux logique

Sous Windows, vous pouvez utiliser le Évaluer la fonctionnalité pour regarder Excel résoudre vos formules, étape par étape. C'est un excellent moyen de « voir » le flux logique de formules plus complexes et de résoudre les problèmes lorsque les choses ne fonctionnent pas comme prévu. L'écran ci-dessous montre la fenêtre d'évaluation ouverte et prête à fonctionner. Chaque fois que vous cliquez sur le bouton Évaluer, la « prochaine étape » de la formule est résolue. Vous pouvez trouver Évaluer dans l'onglet Formules du ruban (Alt M, V).

Utiliser Évaluer pour parcourir un SI imbriqué qui attribue des notes

Malheureusement, la version Mac d'Excel ne contient pas la fonctionnalité d'évaluation, mais vous pouvez toujours utiliser l'astuce F9 ci-dessous.

7. Utilisez F9 pour vérifier les résultats

Lorsque vous sélectionnez une expression dans la barre de formule et appuyez sur la touche F9, Excel résout uniquement la partie sélectionnée. C'est un moyen puissant de confirmer ce qu'une formule fait vraiment. Dans l'écran ci-dessous, j'utilise les fenêtres d'info-bulles pour sélectionner différentes parties de la formule, puis je clique sur F9 pour voir cette partie résolue :

À l'aide de F9, vérifiez un SI imbriqué qui attribue des notes

Utilisez Ctrl + Z (Commande + Z) sur un Mac pour annuler F9. Vous pouvez également appuyer sur Echap pour quitter l'éditeur de formules sans aucun changement.

Vidéo: Comment déboguer une formule avec F9

8. Connaissez vos limites

Excel a des limites sur la profondeur à laquelle vous pouvez imbriquer des fonctions SI. Jusqu'à Excel 2007, Excel autorisait jusqu'à 7 niveaux de FI imbriqués. Dans Excel 2007+, Excel permet jusqu'à 64 niveaux.

Cependant, juste parce que vous pouvez imbriquer beaucoup de SI, cela ne veut pas dire que vous devrait . Chaque niveau supplémentaire que vous ajoutez rend la formule plus difficile à comprendre et à résoudre. Si vous travaillez avec un SI imbriqué à plus de quelques niveaux de profondeur, vous devriez probablement adopter une approche différente - voir ci-dessous pour des alternatives.

9. Associez les parenthèses comme un pro

L'un des défis avec les FI imbriqués consiste à faire correspondre ou « équilibrer » les parenthèses. Lorsque les parenthèses ne correspondent pas correctement, votre formule est rompue. Heureusement, Excel fournit quelques outils pour vous aider à vous assurer que les parenthèses sont « équilibrées » lors de la modification des formules.

Premièrement, une fois que vous avez plusieurs jeux de parenthèses, les parenthèses sont codées par couleur afin que les parenthèses ouvrantes correspondent aux parenthèses fermantes. Ces couleurs sont sacrément difficiles à voir, mais elles sont là si vous regardez bien :

Les parenthèses des formules sont de couleur assortie mais difficiles à voir

Deuxièmement (et mieux) lorsque vous fermez une parenthèse, Excel mettra brièvement en gras la paire correspondante. Vous pouvez également cliquer dans la formule et utiliser la touche fléchée pour parcourir les parenthèses, et Excel mettra brièvement en gras les deux parenthèses lorsqu'il existe une paire correspondante. S'il n'y a pas de correspondance, vous ne verrez pas de gras.

Malheureusement, la mise en gras est une fonctionnalité réservée à Windows. Si vous utilisez Excel sur un Mac pour modifier des formules complexes, il est parfois judicieux de copier et coller la formule dans un bon éditeur de texte ( Wrangler de texte est gratuit et excellent) pour obtenir de meilleurs outils de correspondance de parenthèses. Text Wrangler clignote lorsque les parenthèses correspondent, et vous pouvez utiliser Commande + B pour sélectionner tout le texte contenu par des parenthèses. Vous pouvez coller la formule dans Excel après avoir réglé les choses.

10. Utilisez la fenêtre d'info-bulle pour naviguer et sélectionner

Lorsqu'il s'agit de naviguer et d'éditer des FI imbriqués, l'info-bulle de fonction est votre meilleur ami. Avec lui, vous pouvez naviguer et sélectionner avec précision tous les arguments dans un SI imbriqué :

Naviguer et sélectionner des arguments de formule avec l'info-bulle

Vous pouvez me voir utiliser beaucoup la fenêtre d'info-bulle dans cette vidéo : Comment construire un SI imbriqué .

11. Faites attention au texte et aux chiffres

Pour rappel, lorsque vous travaillez avec la fonction SI, veillez à bien faire correspondre les nombres et le texte. Je vois souvent des formules SI comme ceci :

 
= IF (A1='100','Pass','Fail')

Le score du test est-il en A1 vraiment du texte et non un numéro ? Non? Alors n'utilisez pas de guillemets autour du nombre. Sinon, le test logique renverra FALSE même si la valeur est une note de passage, car « 100 » n'est pas la même chose que 100. Si la note du test est numérique, utilisez ceci :

 
= IF (A1=100,'Pass','Fail')

12. Ajoutez des sauts de ligne pour faciliter la lecture des SI imbriqués

Lorsque vous travaillez avec une formule qui contient de nombreux niveaux de FI imbriqués, il peut être difficile de garder les choses au clair. Étant donné qu'Excel ne se soucie pas des « espaces blancs » dans les formules (c'est-à-dire des espaces supplémentaires ou des sauts de ligne), vous pouvez considérablement améliorer la lisibilité des ifs imbriqués en ajoutant des sauts de ligne.

Par exemple, l'écran ci-dessous montre un SI imbriqué qui calcule un taux de commission basé sur un nombre de ventes. Ici, vous pouvez voir la structure IF imbriquée typique, qui est difficile à déchiffrer :

Les FI imbriqués sans saut de ligne sont difficiles à lire

Cependant, si j'ajoute des sauts de ligne avant chaque 'valeur si fausse', la logique de la formule saute aux yeux. De plus, la formule est plus facile à modifier :

Les sauts de ligne facilitent la lecture des IF imbriqués

Vous pouvez ajouter des sauts de ligne sous Windows avec Alt + Entrée, sur un Mac, utilisez Ctrl + Option + Retour.

Vidéo: Comment rendre un SI imbriqué plus facile à lire .

13. Limiter les FI avec AND et OR

Les FI imbriqués sont puissants, mais ils se compliquent rapidement à mesure que vous ajoutez plus de niveaux. Une façon d'éviter plus de niveaux est d'utiliser IF en combinaison avec les fonctions ET et OU. Ces fonctions renvoient un simple résultat VRAI/FAUX qui fonctionne parfaitement dans IF, vous pouvez donc les utiliser pour étendre la logique d'un seul IF.

Par exemple, dans le problème ci-dessous, nous voulons mettre un « x » dans la colonne D pour marquer les lignes où la couleur est « rouge » et la taille est « petite ».

IF avec la fonction AND est plus simple que deux IF imbriqués

Nous pourrions écrire la formule avec deux SI imbriqués comme ceci :

 
= IF (B6='red', IF (C6='small','x',''),'')

Cependant, en remplaçant le test par la fonction AND, on peut simplifier la formule :

 
= IF ( AND (B6='red',C6='small'),'x','')

De la même manière, nous pouvons facilement étendre cette formule avec la fonction OU pour vérifier le rouge OU le bleu ET le petit :

 
= IF ( AND ( OR (B4='red',B4='blue'),C4='small'),'x','')

Tout ça pourrait être fait avec des FI imbriqués, mais la formule deviendrait rapidement plus complexe.

Vidéo: SI ceci OU cela

14. Remplacer les FI imbriqués par RECHERCHEV

Lorsqu'un SI imbriqué attribue simplement des valeurs basées sur une seule entrée, il peut être facilement remplacé par le Fonction RECHERCHEV . Par exemple, ce SI imbriqué attribue des nombres à cinq couleurs différentes :

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

Nous pouvons facilement le remplacer par ce VLOOKUP (beaucoup plus simple) :

 
= VLOOKUP (E3,B3:C7,2,0)

IF imbriqué vs RECHERCHEV

En prime, RECHERCHEV conserve les valeurs sur la feuille de calcul (où elles peuvent être facilement modifiées) au lieu de les intégrer dans la formule.

Bien que la formule ci-dessus utilise une correspondance exacte, vous pouvez facilement utiliser RECHERCHEV pour les notes également.

Voir également: 23 choses à savoir sur RECHERCHEV

Vidéo: Comment utiliser RECHERCHEV

Vidéo: Pourquoi RECHERCHEV est meilleur que les SI imbriqués

15. Choisissez CHOISIR

La fonction CHOOSE peut fournir une solution élégante lorsque vous devez mapper des nombres simples et consécutifs (1,2,3, etc.) à des valeurs arbitraires.

Dans l'exemple ci-dessous, CHOOSE est utilisé pour créer des abréviations personnalisées pour les jours de la semaine :

SI imbriqué vs fonction CHOOSE

Bien sûr, vous pourrait utilisez un SI imbriqué long et compliqué pour faire la même chose, mais s'il vous plaît ne le faites pas :)

16. Utilisez IFS au lieu des IF imbriqués

Si vous utilisez Excel 2016 via Office 365, il existe une nouvelle fonction que vous pouvez utiliser à la place des IF imbriqués : la fonction IFS. La fonction IFS fournit une structure spéciale pour évaluer plusieurs conditions sans pour autant imbrication :

La fonction IFS - plusieurs conditions sans imbrication

La formule utilisée ci-dessus ressemble à ceci :

 
= IFS (D5<60,'F',D5<70,'D',D5<80,'C',D5<90,'B',D5>=90,'A')

Notez que nous n'avons qu'une seule paire de parenthèses !

Que se passe-t-il lorsque vous ouvrez une feuille de calcul qui utilise la fonction IFS dans une ancienne version d'Excel ? Dans Excel 2013 et 2010 (et je crois Excel 2007, mais je ne peux pas tester), vous verrez « _xlfn ». ajouté à l'IFS dans la cellule. La valeur calculée précédemment sera toujours là, mais si quelque chose provoque le recalcul de la formule, vous verrez une erreur #NAME. Microsoft a plus d'informations ici .

17. Max out

Parfois, vous pouvez utiliser MAX ou MIN d'une manière très intelligente qui évite une instruction IF. Par exemple, supposons que vous ayez un calcul qui doit aboutir à un nombre positif ou zéro. En d'autres termes, si le calcul renvoie un nombre négatif, vous souhaitez simplement afficher zéro.

La fonction MAX vous offre un moyen astucieux de le faire sans aucun IF en vue :

 
= MAX (calculation,0)

Cette technique renvoie le résultat du calcul s'il est positif, et zéro sinon.

J'aime cette construction parce que c'est juste si simple . Voir cet article pour une écriture complète .

18. Piège les erreurs avec IFERROR

Une utilisation classique de IF consiste à intercepter les erreurs et à fournir un autre résultat lorsqu'une erreur est renvoyée, comme ceci :

 
= IF ( ISERROR (formula),error_result,formula)

C'est moche et redondant, car la même formule entre deux fois et Excel doit calculer le même résultat deux fois lorsqu'il n'y a pas d'erreur.

Dans Excel 2007, la fonction SIERREUR a été introduite, qui vous permet d'intercepter les erreurs de manière beaucoup plus élégante :

 
= IFERROR (formula,error_result)

Désormais, lorsque la formule renvoie une erreur, IFERROR renvoie simplement la valeur que vous fournissez.

19. Utilisez la logique booléenne

Vous pouvez aussi parfois éviter les SI imbriqués en utilisant ce qu'on appelle la « logique booléenne ». Le mot booléen fait référence aux valeurs VRAI/FAUX. Bien qu'Excel affiche les mots VRAI et FAUX dans les cellules, en interne, Excel traite VRAI comme 1 et FAUX comme zéro. Vous pouvez utiliser ce fait pour écrire des formules intelligentes et très rapides. Par exemple, dans l'exemple RECHERCHEV ci-dessus, nous avons une formule SI imbriquée qui ressemble à ceci :

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

En utilisant la logique booléenne, vous pouvez réécrire la formule comme ceci :

 
=(E3='red')*100+(E3='blue')*200+(E3='green')*300+(E3='orange')*400+(E3='purple')*500

Chaque expression effectue un test, puis multiplie le résultat du test par la « valeur si vrai ». Étant donné que les tests renvoient soit VRAI, soit FAUX (1 ou 0), les résultats FAUX s'annulent eux-mêmes de la formule.

Pour les résultats numériques, la logique booléenne est simple et extrêmement rapide, car il n'y a pas de branchement. En revanche, la logique booléenne peut être déroutante pour les personnes qui ne sont pas habituées à la voir. Pourtant, c'est une excellente technique à connaître.

Vidéo: Comment utiliser la logique booléenne dans les formules Excel

Quand avez-vous besoin d'un SI imbriqué ?

Avec toutes ces options pour éviter les SI imbriqués, vous vous demandez peut-être quand est-il judicieux d'utiliser un SI imbriqué ?

Je pense que les SI imbriqués ont du sens lorsque vous devez évaluer plusieurs entrées différentes prendre une décision.

Par exemple, supposons que vous vouliez calculer un statut de facture de 'Payé', 'Ouvert', 'En retard', etc. Cela nécessite que vous examiniez l'âge de la facture et solde impayé :

Calcul du statut de la facture avec un SI imbriqué

Dans ce cas, un SI imbriqué est une solution parfaitement adaptée.

Tes pensées?

Et toi? Êtes-vous un IF-ster? Évitez-vous les FI imbriqués ? Les SI imbriqués sont-ils mauvais ? Partagez vos pensées ci-dessous.

Apprenez rapidement les formules Excel avec formation vidéo concise . Auteur Dave Bruns


^