Exceller

19 astuces pour les formules IF imbriquées

19 Tips Nested If Formulas

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

Mais un IF en entraîne souvent un autre, et une fois que vous combinez plus de deux IF, vos formules peuvent commencer à ressembler à de petits Frankenstein :)



Les FI imbriquées sont-elles mauvaises? Sont-ils parfois nécessaires? Quelles sont les alternatives?



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

1. IF de base

Avant de parler de IF imbriqué, passons rapidement en revue la structure IF de base:



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

La fonction IF 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 true ou une valeur pour false.

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



Fonction IF de base - retour

La cellule D3 de l'exemple contient cette formule:

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

Ce qui peut se lire comme ceci: si le score en C3 est d'au moins 65, renvoyer «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 'Fail' pour les scores non réussis, nous pouvons ajouter 'Fail' comme argument faux comme ceci:

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

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

Vidéo: Comment créer des formules logiques .

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

Nesting signifie simplement combiner des formules, les unes dans les autres, de sorte qu'une formule gère le résultat d'une autre. Par exemple, voici une formule dans laquelle la fonction TODAY est imbriquée dans la fonction MONTH:

 
= 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, de sorte que vous verrez l'imbrication partout dans des formules plus complexes.

3. Un IF imbriqué simple

Un IF imbriqué est juste deux autres instructions IF dans une formule, où une instruction IF apparaît dans l'autre.

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

Un IF imbriqué de base

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

Le IF externe s'exécute en premier et teste pour voir si C3 est vide. Si tel est le cas, IF externe renvoie «Incomplete» et le IF interne ne s'exécute jamais.

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

Apprenez les IF imbriquées avec formation vidéo claire et concise .

4. Un IF imbriqué pour les échelles

Vous verrez souvent des IF imbriquées configurées 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 IF imbriquées fonctionnent bien ici, mais vous devez garder la formule organisée, sinon elle devient difficile à lire.

L'astuce est de 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 «faible à élevé», nous pouvons représenter la solution nécessaire 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 clairement comprises, nous pouvons entrer la première instruction IF:

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

Cela prend soin de «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 laissé tomber 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 de IF imbriqué terminé pour le calcul des notes

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

5. Les IF imbriquées 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 IF imbriquées ont leur propre flux logique, puisque les IF «externes» agissent comme une passerelle vers les IF «internes». 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 déroulement logique de la formule de note ci-dessus.

Le flux logique d'un IF imbriqué

6. Utilisez Evaluate 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 dépanner lorsque les choses ne fonctionnent pas comme prévu. L'écran ci-dessous montre la fenêtre d'évaluation ouverte et prête à l'emploi. 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 Evaluate pour parcourir un IF imbriqué qui attribue des notes

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

7. Utilisez F9 pour repérer les résultats de la vérification

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 que fait réellement une formule. Dans l'écran ci-dessous, j'utilise les fenêtres d'info-bulle 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 IF 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 formule sans aucune modification.

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

8. Connaissez vos limites

Excel a des limites sur la profondeur de l'imbrication des fonctions IF. Jusqu'à Excel 2007, Excel permettait jusqu'à 7 niveaux de IF imbriqués. Dans Excel 2007+, Excel autorise jusqu'à 64 niveaux.

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

9. Faites correspondre les parenthèses comme un pro

L'un des défis avec les IF imbriqués est la correspondance ou «équilibrer» les parenthèses. Lorsque les parenthèses ne correspondent pas correctement, votre formule est cassée. Heureusement, E xcel fournit quelques outils pour vous aider à vous assurer que les parenthèses sont «équilibrées» lors de l'édition des formules.

Tout d'abord, une fois que vous avez plus d'un jeu 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 vraiment difficiles à voir, mais elles sont là si vous regardez de près:

Les parenthèses de formule 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 vous déplacer entre les parenthèses, et Excel mettra brièvement les deux parenthèses en gras lorsqu'il y a une paire correspondante. S'il n'y a pas de correspondance, vous ne verrez aucun caractère gras.

Malheureusement, le gras est une fonctionnalité uniquement 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 ( Text Wrangler est gratuit et excellent) pour obtenir de meilleurs outils de correspondance des parenthèses. Text Wrangler clignote lorsque les parenthèses correspondent, et vous pouvez utiliser Commande + B pour sélectionner tout le texte contenu entre parenthèses. Vous pouvez recoller 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 de modifier des IF imbriquées, l'info-bulle de la fonction est votre meilleur ami. Avec lui, vous pouvez naviguer et sélectionner précisément tous les arguments dans un IF imbriqué:

Naviguez et sélectionnez 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 créer un IF imbriqué .

11. Faites attention avec le texte et les chiffres

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

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

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

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

12. Ajoutez des sauts de ligne pour faciliter la lecture des IF imbriquées

Lorsque vous travaillez avec une formule qui contient de nombreux niveaux de IF imbriqués, il peut être difficile de garder les choses en ordre. Comme 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 if imbriqués en ajoutant des sauts de ligne.

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

Les IF imbriquées 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 clairement. 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 IF imbriqué plus facile à lire .

13. Limiter les FI avec ET et OU

Les IF imbriquées sont puissantes, mais elles se compliquent rapidement à mesure que vous ajoutez plus de niveaux. Une façon d'éviter plus de niveaux consiste à utiliser IF en combinaison avec les fonctions AND et OR. Ces fonctions renvoient un simple résultat TRUE / FALSE qui fonctionne parfaitement dans IF, vous pouvez donc les utiliser pour étendre la logique d'un IF unique.

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 IF imbriquées comme ceci:

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

Cependant, en remplaçant le test par la fonction ET, nous pouvons 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 pouvait être fait avec des IF imbriquées, mais la formule deviendrait rapidement plus complexe.

Vidéo: SI ceci OU cela

14. Remplacez les IF imbriqués par RECHERCHEV

Lorsqu'un IF imbriqué affecte simplement des valeurs basées sur une seule entrée, il peut être facilement remplacé par Fonction RECHERCHEV . Par exemple, ce IF imbriqué attribue des numéros à 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 ainsi que.

Voir également: 23 choses à savoir sur RECHERCHEV

Vidéo: Comment utiliser RECHERCHEV

Vidéo: Pourquoi VLOOKUP est meilleur que les IF imbriquées

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 de jour de semaine personnalisées:

IF imbriqué vs fonction CHOOSE

Bien sûr, vous pouvait utilisez un IF imbriqué long et compliqué pour faire la même chose, mais 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 emboîtement:

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 qu'Excel 2007, mais je ne peux pas tester), vous verrez «_xlfn». ajouté à l'IFS dans la cellule. La valeur précédemment calculée 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 voulez simplement afficher zéro.

La fonction MAX vous offre un moyen intelligent de le faire sans IF nulle part en vue:

 
= MAX (calculation,0)

Cette technique renvoie le résultat du calcul s'il est positif et zéro dans le cas contraire.

J'adore cette construction car c'est tellement simple . Voir cet article pour une description complète .

18. Piège des 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 en l'absence d'erreur.

Dans Excel 2007, la fonction IFERROR a été introduite, ce qui vous permet de piéger les erreurs beaucoup plus élégamment:

 
= IFERROR (formula,error_result)

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

19. Utilisez la logique booléenne

Vous pouvez aussi parfois éviter les IF imbriqués en utilisant ce qu'on appelle la «logique booléenne». Le mot booléen fait référence aux valeurs TRUE / FALSE. Bien qu'Excel affiche les mots TRUE et FALSE dans les cellules, en interne, Excel traite VRAI comme 1 et FALSE comme zéro. Vous pouvez utiliser ce fait pour écrire des formules intelligentes et très rapides. Par exemple, dans l'exemple VLOOKUP ci-dessus, nous avons une formule IF 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 vraie». Puisque les tests renvoient VRAI ou FAUX (1 ou 0), les résultats FAUX annulent eux-mêmes 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 n'ont pas l'habitude de 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 IF imbriqué?

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

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

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

Calcul du statut de la facture avec un IF imbriqué

Dans ce cas, un IF imbriqué est une solution parfaitement fine.

Tes pensées?

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

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


^