Le tutoriel explique comment utiliser la fonction IF nichée dans Excel pour vérifier plusieurs conditions. Vous apprendrez également quelques autres fonctions qui pourraient être de bonnes alternatives à l'utilisation d'une formule imbriquée dans Excel.

Comment implémentez-vous habituellement une logique de prise de décision dans vos feuilles de calcul Excel? Dans la plupart des cas, vous utiliseriez une formule IF pour tester votre condition et renvoyez une valeur si la condition est remplie, une autre valeur si la condition n'est pas remplie. Pour évaluer plus d'une condition et renvoyer différentes valeurs en fonction des résultats, vous nichez plusieurs IF dans les autres.

Bien que très populaire, l'instruction IF nichée n'est pas le seul moyen de vérifier plusieurs conditions dans Excel. Dans ce tutoriel, vous trouverez une poignée d'alternatives qui valent vraiment la peine d'être explorées.

Excel Nested If Déclaration

Voici la formule classique Excel IF sous une forme générique:

If ( condition1 , result1 , if ( condition2 , result2 , if ( condition3 , result3 , result4 ))))

Vous pouvez voir que chaque fonction si ultérieure est intégrée dans l'argument Value_IF_False de la fonction précédente. Chacun si la fonction est enfermée dans son propre ensemble de parenthèses, mais toutes les parenthèses de cl?ture sont à la fin de la formule.

Notre formule générique sifflée évalue 3 conditions et renvoie 4 résultats différents (le résultat 4 est retourné si aucune des conditions n'est vraie). Traduit dans un langage humain, cette déclaration IFS nichée indique à Excel de faire ce qui suit:

Condition de test1 , si true - return result1 , si false -test condition2 , si true - return r esult2 , if false - Test condition3 , if true - return result3 , if false -raturn result4

Par exemple, découvrons des commissions pour un certain nombre de vendeurs en fonction du montant des ventes qu'ils ont réalisés:

Commission	Ventes
3%	1 $ - 50 $
5%	51 $ - 100 $
7%	101 $ - 150 $
10%	Plus de 150 $

En mathématiques, la modification de l'ordre des addeds ne change pas la somme. Dans Excel, la modification de l'ordre des fonctions IF modifie le résultat. Pourquoi? Parce qu'une formule imbriquée renvoie une valeur correspondant à la première vraie condition . Par conséquent, dans vos déclarations imbriquées, il est très important d'organiser les conditions dans la bonne direction - élevée à faible ou faible à élevée, selon la logique de votre formule. Dans notre cas, nous vérifions d'abord la condition "la plus élevée", puis le "deuxième plus haut", et ainsi de suite:

=IF(B2>150, 10%, IF(B2>=101, 7%, IF(B2>=51, 5%, IF(B2>=1, 3%, ""))))

Si nous pla?ons les conditions dans l'ordre inverse, de bas en haut, les résultats seraient faux parce que notre formule s'arrêterait après le premier test logique (B2> = 1) pour toute valeur supérieure à 1. Disons que nous avons des ventes de 100 $ - il est supérieur à 1, donc la formule ne vérifiera pas d'autres conditions et reviendrons 3% en conséquence.

Si vous préférez organiser les conditions de faible à haut, utilisez l'opérateur "moins que" et évaluez d'abord la condition "la plus basse", alors le "deuxième plus bas", etc.:

=IF($B2

Comme vous le voyez, il faut beaucoup de réflexion pour construire correctement la logique d'une déclaration imbriquée. Et bien que Microsoft Excel permet à Nesting jusqu'à 64 si les fonctions dans une formule, ce n'est pas quelque chose que vous voudriez vraiment faire dans vos feuilles de calcul. Donc, si vous (ou quelqu'un d'autre) regardez votre formule imbriquée Excel en essayant de comprendre ce qu'elle fait, il est temps de reconsidérer votre stratégie et probablement de choisir un autre outil dans votre arsenal.

Pour plus d'informations, veuillez consulter Excel Nested If.

Imbriqué si ou / et conditions

Dans le cas où vous devez évaluer quelques ensembles de conditions différentes, vous pouvez exprimer ces conditions en utilisant ou ainsi que la fonction, nidifiez les fonctions à l'intérieur des instructions IF, puis niditez les instructions IF.

Imbriqué si en excel avec ou déclarations

En utilisant la fonction OR, vous pouvez vérifier deux ou plusieurs conditions différentes dans le test logique de chacun si la fonction et le retour vrai si quelque chose (au moins un) des arguments OR évalue à true. Pour voir comment cela fonctionne réellement, veuillez considérer l'exemple suivant.

En supposant que vous avez deux colonnes de ventes, disons les ventes de janvier dans les ventes de la colonne B et de février dans la colonne C. Vous souhaitez vérifier les chiffres dans les deux colonnes et calculer la Commission en fonction d'un nombre plus élevé. En d'autres termes, vous construisez une formule avec la logique suivante: si les ventes de janvier ou de février sont supérieures à 150 $, le vendeur obtient une commission de 10%, si les ventes de janvier ou de février sont supérieures ou égales à 101 $, le vendeur obtient une commission de 7%, etc.

Pour le faire, écrivez quelques instructions comme ou (B2> 150, C2> 150) et les nid dans les tests logiques des fonctions IF discutées ci-dessus. En conséquence, vous obtenez cette formule:

=IF(OR(B2>150, C2>150), 10%, IF(OR(B2>=101, C2>=101),7%, IF(OR(B2>=51, C2>=51), 5%, IF(OR(B2>=1, C2>=1), 3%, ""))))

Et la commission a été assignée en fonction du montant des ventes plus élevé:

Pour plus d'exemples de formules, veuillez consulter Excel si ou déclaration.

Imbriqué si en excel avec et déclarations

Si vos tests logiques incluent plusieurs conditions et que toutes ces conditions doivent évaluer à vrai, exprimez-les en utilisant la fonction et.

Par exemple, pour attribuer les commissions en fonction d'un nombre plus faible de ventes, prenez la formule ci-dessus et remplacez ou par des instructions. Pour le dire différemment, vous dites à Excel de retourner 10% uniquement si les ventes de janvier et de février sont supérieures à 150 $, 7% si les ventes de janvier et de février sont supérieures ou égales à 101 $, etc.

=IF(AND(B2>150, C2>150), 10%, IF(AND(B2>=101, C2>=101), 7%, IF(AND(B2>=51, C2>=51), 5%, IF(AND(B2>=1, C2>=1), 3%, ""))))

En conséquence, notre formule imbriquée calcule la commission en fonction du nombre inférieur dans les colonnes B et C. Si l'une ou l'autre colonne est vide, il n'y a pas de commission du tout car aucune des conditions n'est remplie:

Si vous souhaitez retourner 0% au lieu de cellules vierges, remplacez une cha?ne vide ('' ") dans le dernier argument avec 0%:

=IF(AND(B2>150, C2>150), 10%, IF(AND(B2>=101, C2>=101), 7%, IF(AND(B2>=51, C2>=51), 5%, IF(AND(B2>=1, C2>=1), 3%, 0%))))

Plus d'informations peuvent être trouvées ici: Excel si avec plusieurs et / ou des conditions.

Vlookup au lieu de imbriqué si excel

Lorsque vous traitez avec des "échelles", c'est-à-dire des plages continues de valeurs numériques qui couvrent ensemble toute la plage, dans la plupart des cas, vous pouvez utiliser la fonction VLookup au lieu de IFS imbriqués.

Pour commencer, faites une table de référence comme indiqué dans la capture d'écran ci-dessous. Et puis, créez une formule Vlookup avec une correspondance approximative , c'est-à-dire avec l'argument Range_lookup défini sur true.

En supposant que la valeur de recherche est en B2 et que le tableau de référence est F2: G5, la formule se déroule comme suit:

=VLOOKUP(B2,$F$2:$G$5,2,TRUE)

Veuillez noter que nous fixons la table_array avec des références absolues ($ f 2 $: $ g 5 $) pour que la formule puisse copier correctement dans d'autres cellules:

En définissant le dernier argument de votre formule VLookup sur true, vous dites à Excel de rechercher la correspondance la plus proche - si une correspondance exacte n'est pas trouvée, renvoyez la plus grande valeur la plus importante qui est plus petite que la valeur de recherche. En conséquence, votre formule correspondra non seulement aux valeurs exactes de la table de recherche, mais aussi à toutes les valeurs qui se situent entre les deux.

Par exemple, la valeur de recherche en B3 est de 95 $. Ce numéro n'existe pas dans la table de recherche, et Vlookup avec une correspondance exacte renverrait une erreur # n / a dans ce cas. Mais Vlookup avec une correspondance approximative continue de rechercher jusqu'à ce qu'il trouve la valeur la plus proche qui est inférieure à la valeur de recherche (qui est de 50 $ dans notre exemple) et renvoie une valeur de la deuxième colonne de la même ligne (qui est de 5%).

Mais que se passe-t-il si la valeur de recherche est inférieure au plus petit nombre de la table de recherche ou si la cellule de recherche est vide? Dans ce cas, une formule VLookup renvoie l'erreur # n / a. Si ce n'est pas ce que vous voulez réellement, nid vlookup à l'intérieur de IFERROR et fournit la valeur à la sortie lorsque la valeur de recherche n'est pas trouvée. Par exemple:

=IFERROR(VLOOKUP(B2, $F$2:$G$5, 2, TRUE), "Outside range")

Remarque importante! Pour une formule VLookup avec une correspondance approximative pour fonctionner correctement, la première colonne de la table de recherche doit être triée en ordre ascendante , du plus petit au plus grand.

Pour plus d'informations, veuillez consulter Exact Match Vlookup par rapport à la correspondance approximative Vlookup.

énoncé IFS comme alternative à la fonction nichée

Dans les versions Excel 2016 et ultérieures, Microsoft a introduit une fonction spéciale pour évaluer plusieurs conditions - la fonction IFS.

Une formule IFS peut gérer jusqu'à 127 paires logical_test / value_if_true , et le premier test logique qui évalue à vrai "gagne":

Ifs (logical_test1, value_if_true1, [logical_test2, value_if_true2] ...)

Conformément à la syntaxe ci-dessus, notre formule imbriquée peut être reconstruite de cette manière:

=IFS(B2>150, 10%, B2>=101, 7%, B2>=51, 5%, B2>0, 3%)

Veuillez faire attention que la fonction IFS renvoie l'erreur # n / a si aucune des conditions spécifiées n'est remplie. Pour éviter cela, vous pouvez ajouter un autre logical_test / value_if_true à la fin de votre formule qui renverra 0 ou une cha?ne vide ("") ou quelle que soit la valeur que vous voulez si aucun des tests logiques précédents n'est vrai:

=IFS(B2>150, 10%, B2>=101, 7%, B2>=51, 5%, B2>0, 3%, TRUE, "")

En conséquence, notre formule renverra une cha?ne vide (cellule vide) au lieu de l'erreur # n / a si une cellule correspondante dans la colonne B est vide ou contient du texte ou un nombre négatif.

Note. Comme Nested If, la fonction IFS d'Excel renvoie une valeur correspondant à la première condition qui évalue TRUE, c'est pourquoi l'ordre des tests logiques dans une formule IFS compte.

Pour plus d'informations, veuillez consulter la fonction Excel IFS au lieu d'être imbriqué si.

Choisissez au lieu de Nested si la formule dans Excel

Une autre fa?on de tester plusieurs conditions dans une seule formule dans Excel consiste à utiliser la fonction Choose, qui est con?ue pour renvoyer une valeur de la liste en fonction d'une position de cette valeur.

Appliqué à notre exemple de données, la formule prend la forme suivante:

=CHOOSE((B2>=1) (B2>=51) (B2>=101) (B2>150), 3%, 5%, 7%, 10%)

Dans le premier argument ( index_num ), vous évaluez toutes les conditions et additionnez les résultats. étant donné que True équivaut à 1 et faux à 0, de cette fa?on, vous calculez la position de la valeur à retourner.

Par exemple, la valeur en B2 est de 150 $. Pour cette valeur, les 3 premières conditions sont vraies et la dernière (B2> 150) est fausse. Ainsi, index_num équivaut à 3, ce qui signifie que la 3e valeur est retournée, ce qui est de 7%.

Conseil. Si aucun des tests logiques n'est vrai, index_num est égal à 0 et que la formule renvoie le #Value! erreur. Un correctif facile consiste à emballer le choix dans la fonction IFERROR comme celle-ci:

=IFERROR(CHOOSE((B2>=1) (B2>=51) (B2>=101) (B2>150), 3%, 5%, 7%, 10%), "")

Pour plus d'informations, veuillez consulter Excel Choisissez une fonction avec des exemples de formule.

La fonction de commutation comme une forme concise de imbriqué si dans Excel

Dans les situations lorsque vous traitez avec un ensemble fixe de valeurs prédéfinies, et non des échelles, la fonction de commutation peut être une alternative compacte à des instructions IF complexes:

Switch (Expression, Value1, result1, valeur2, result2,…, [par défaut])

La fonction de commutation évalue l'expression par rapport à une liste de valeurs et renvoie le résultat correspondant à la première correspondance trouvée.

Dans le cas, vous souhaitez calculer la Commission en fonction des notes suivantes, plut?t que des montants de vente, vous pouvez utiliser cette version compacte de Formule If If Excel:

=SWITCH(C2, "A", 10%, "B", 7%, "C", 5%, "D", 3%, "")

Ou, vous pouvez faire un tableau de référence comme indiqué dans la capture d'écran ci-dessous et utiliser des références de cellules au lieu de valeurs codées en dur:

=SWITCH(C2, $F$2, $G$2, $F$3, $G$3, $F$4, $G$4, $F$5, $G$5, "")

Veuillez remarquer que nous verrouillons toutes les références, sauf la première avec le signe $ pour les empêcher de changer lors de la copie de la formule en autres cellules:

Note. La fonction de commutation est uniquement disponible dans Excel 2016 et plus.

Pour plus d'informations, veuillez consulter la fonction Switch - la forme compacte de l'instruction if imbriquée.

Concaténer plusieurs fonctions IF dans Excel

Comme mentionné dans l'exemple précédent, la fonction de commutation n'a été introduite que dans Excel 2016. Pour gérer des taches similaires dans les versions Excel plus anciennes, vous pouvez combiner deux ou plusieurs instructions IF en utilisant l'opérateur de concaténate (&) ou la fonction concaténée.

Par exemple:

=(IF(C2="a", 10%, "") & IF(C2="b", 7%, "") & IF(C2="c", 5%, "") & IF(C2="d", 3%, ""))*1

Ou

=CONCATENATE(IF(C2="a", 10%, ""), IF(C2="b", 7%, ""), IF(C2="c", 5%, "") & IF(C2="d", 3%, ""))*1

Comme vous l'avez peut-être remarqué, nous multiplions le résultat par 1 dans les deux formules. Il est fait pour convertir une cha?ne renvoyée par la formule du concaténate en nombre. Si votre sortie attendue est du texte, l'opération de multiplication n'est pas nécessaire.

Pour plus d'informations, veuillez consulter la fonction du concaténate dans Excel.

Vous pouvez voir que Microsoft Excel fournit une poignée de bonnes alternatives aux formules imbriquées, et j'espère que ce tutoriel vous a donné quelques indices sur la fa?on de les exploiter dans vos feuilles de travail. Pour examiner de plus près les exemples discutés dans ce tutoriel, vous êtes invités à télécharger notre exemple de classeur ci-dessous. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Classeur de pratique pour télécharger

Excel Nested If Instruction - Exemples (fichier .xlsx)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!