Gestion de plusieurs conditions dans une formule IF. Cet article compare les trois méthodes différentes.
Lorsque vous devez effectuer un calcul conditionnel, la fonction IF est la réponse. Si alors autrement. Dans la figure suivante, un simple IF calcule un bonus si vos ventes étaient de 20 000 $ ou plus.
Mais que se passe-t-il lorsque deux conditions doivent être remplies? La plupart des gens imbriqueront une instruction IF dans une autre, comme ceci:
Mais cela devient incontrôlable si de nombreuses conditions doivent être remplies. La fonction ET raccourcira et simplifiera la formule. = AND (Test, Test, Test, Test) ne sera vrai que si tous les tests logiques sont True. L'exemple suivant montre une formule plus courte avec les mêmes résultats.
Si vous aimez AND, vous pourriez trouver une utilisation pour OR et NOT. = OU (Test, Test, Test, Test) sera Vrai si l'un des tests logiques est Vrai. NOT inversera une réponse. =NOT(True)c'est faux. =NOT(False)est vrai. Si jamais vous devez faire quelque chose de fantaisie comme un NAND, vous pouvez le faire PAS (AND (Test, Test, Test, Test)).
Mise en garde
Bien qu'Excel 2013 ait introduit XOR en tant que Ou Exclusif, il ne fonctionne pas comme prévu par les comptables. =XOR(True,False,True,True)est vrai pour des raisons trop compliquées à expliquer ici. XOR compte vraiment si vous avez un nombre impair de valeurs True. Impair. Vraiment bizarre.
Astuce bonus
Utilisation de la logique booléenne
Je couvre toujours la FI dans mes séminaires. Et je demande toujours comment les gens résoudraient le problème des deux conditions. Les résultats sont souvent les mêmes; 70 à 80% des personnes utilisent les IF imbriquées et 20 à 30% utilisent ET. Une seule fois, en Virginie, une femme de Price Waterhouse a proposé cette formule:
Ça marche. Il donne la même réponse que les autres formules. Calculez le bonus .02 * B4. Mais multipliez ensuite ce bonus par des tests logiques entre parenthèses. Lorsque vous forcez Excel à multiplier un nombre par Vrai ou Faux, le Vrai devient 1 et le Faux devient 0. Tout nombre multiplié par 1 est lui-même. Tout nombre multiplié par 0 est égal à 0. En multipliant le bonus par les conditions, seules les lignes qui remplissent les deux conditions sont payées.
C'est cool. Ça marche. Mais cela semble déroutant lorsque vous le voyez pour la première fois. Ma blague dans le séminaire est toujours: "Si vous quittez votre emploi le mois prochain et que vous détestez vos collègues, commencez à utiliser cette formule."
Regarder la vidéo
- La fonction IF la plus simple est
=IF(Logical Test,Formula if True, Formula if False) - Mais que faites-vous si vous devez tester deux conditions?
- Beaucoup de gens feront
=IF(Test 1, IF(Test 2, Formula if True, False), False) - Cela devient compliqué si 3, 5, 17 conditions!
- Au lieu de cela, utilisez
=IF(AND(t1,t2,t3,t4),Formula if True, Formula if False) - Si vous aimez ET, considérez OU, PAS pour d'autres situations
- NAND peut être fait avec NOT (AND ())
- NOR peut être fait avec NOT (OU ())
- Soyez prudent lorsque vous utilisez XOR car les résultats ne sont pas ceux que vous attendez
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2025 - Conditions multiples dans IF!
Je vais podcaster tout ce livre, le «i» dans le coin supérieur droit vous mènera à la liste de lecture de tous ces podcasts!
D'accord, nous allons commencer par le cas le plus simple au monde de l'IF, le vice-président fictif des ventes vient nous voir et nous dit: «Hé, ce mois-ci, quiconque a plus de 20000 $ de ventes reçoit un bonus de 2%.» D'accord, donc la fonction IF comporte trois parties: Un test logique qui va dire "Est-ce que B4> 20000 $?" Virgule, alors que faire si c'est VRAI? Si c'est VRAI, .02 * B4, virgule, que faire si c'est FAUX? Eh bien, si vous n'avez pas gagné 20000 $, aucun bonus pour vous, c'est zéro, d'accord. Ctrl + Entrée pour copier cela vers le bas, et vous voyez que nous n'avons le bonus que sur les lignes supérieures à 20000 $, celle-ci se ferme, mais toujours pas de bonus, d'accord. Maintenant, il n'y a jamais eu de plan de bonus aussi simple, c'est vrai, il y a toujours plusieurs règles, donc ici, nous devons vérifier si les revenus> 20000 $ ET si le pourcentage de marge brute> 50%.
D'accord, et si vous savez comment résoudre ce problème, réfléchissez à la façon dont vous allez le résoudre, d'accord, et je vais prédire que beaucoup d'entre vous disent: "Eh bien, nous allons commencer par un IF, puis une autre instruction IF comme celle-ci, les IF imbriquées. » Et puis il y a quelques-uns d'entre vous qui disent: "Eh bien, faisons une instruction IF, puis immédiatement ici, entre parenthèses, nous entrons dans une autre fonction appelée ET." Et puis, il y a un moyen de le faire sans aucune FI. D'accord, je vais donc y jeter un coup d'œil, alors voici la première, la plus courante, le SI imbriqué, OK, voici comment la plupart des gens feraient cela.
= SI, faites le premier test, voyez si les revenus dans B4> 20000 $, si c'est le cas, faites un autre IF, voyez si le pourcentage de profit brut> .05? Si c'est VRAI, alors les deux conditions sont VRAIES, nous pouvons faire .02 * B4, sinon pas de bonus. OK, mais nous n'avons pas terminé, fermez les parenthèses intérieures, la virgule, puis si le premier test n'était pas vrai, pas de bonus, Ctrl + Entrée pour copier vers le bas. Vous voyez que seules les lignes supérieures à 20000 $ et au-dessus du pourcentage de profit brut de 50% reçoivent le bonus. Très bien maintenant, à l'époque, il y avait cette horrible limite, où vous ne pouviez pas imbriquer plus de 7 instructions IF. Ce fut une journée douloureuse, en particulier si vous ajoutiez lentement des conditions au fil des mois, et que vous en aviez finalement un qui en avait 7 et que vous deviez ajouter un 8e. Très bien, aujourd'hui tu peux aller à 32, je ne pense pas que tu devrais jamais aller à 32,mais si vous avez désespérément besoin de passer de 7 à 8, alors c'est une bonne chose, d'accord. C'est donc l'approche des instructions IF imbriquées, quand je fais mes séminaires en direct, environ la moitié de la salle le fait, mais il y a une bien meilleure façon de procéder.
= IF et ensuite entrer immédiatement dans une fonction appelée ET, donc à l'intérieur du ET nous mettons tous les tests: Le revenu> 20000, virgule, est un pourcentage de profit brut> 0,5. S'il y avait plus de tests, continuez à mettre des virgules avec les tests supplémentaires, puis fermez la fin, tout à la fin doit être VRAI pour que la fin soit VRAI. Donc si nous arrivons à ce point si la fin est VRAI, 0,02 * revenu, sinon 0, c'est une formule plus courte, c'est plus facile à saisir, vous obtenez les mêmes résultats, la vie est belle.
D'accord, sur tous les séminaires que j'ai organisés au cours des 15 dernières années, une seule fois, quelqu'un est entré et m'a frappé avec cette formule folle. Elle a dit: «Écoutez, nous allons juste faire = 0,02 * le revenu, comme ça.» D'accord, calculez le bonus, je me dis "Oh, attendez, ça va coûter cher, vous allez donner le bonus à tout le monde." elle est comme "Attendez, je n'ai pas fini, fois, puis entre parenthèses, nous allons mettre chaque condition, donc revenu> 20000 fois, entre parenthèses pourcentage de profit brut> 0,5." D'accord, et voici ce qui se passe, c'est que nous calculons le bonus, puis ceux-ci évaluent à VRAI ou FAUX. Et quand on force Excel à multiplier un VRAI ou FAUX fois un nombre, le VRAI devient 1, tout * 1 est lui-même, le FAUX devient 0! D'accord, donc ce que nous avons ici, c'est 2% * les revenus * 1 * 0, n'importe quoi * 0 = 0, à droite,ce qui efface le bonus. C'est la logique booléenne, TRUE * TRUE, 1 * 1 = 1, si l'un ou l'autre est FALSE ou si tous sont FALSE, cela va s'évaluer à 0, et nous obtenons exactement le même résultat. Est-ce que je pense que vous devriez passer à celui-ci? Non, c'est déroutant, à moins que vous ne quittiez votre emploi la semaine prochaine et que vous détestiez vos collègues, alors n'hésitez pas à passer à ça, d'accord.
Si vous aimez la fonction AND, il existe d'autres fonctions ou vérifications pour voir si l'une des conditions est VRAI, donc ceci ou ceci ou cela, il retournera VRAI. NOT va inverser TRUE en FALSE et FALSE en TRUE, ce qui est utile lorsque vous essayez de faire les concepts booléens de NAND ou NOR. NAND signifie non-et, c'est VRAI quand au moins une condition est FAUX, d'accord. Donc, si aucun d'entre eux n'est VRAI, c'est super, si quelques-uns d'entre eux sont VRAI, c'est super, mais dès qu'ils sont tous VRAI, alors nous ne payons pas. NOR signifie non-ou, cela signifie qu'aucune des conditions n'est VRAIE - si cela se produit, ou cela se produit, ou cela se produit, aucun bonus pour vous. Et puis XOR, maintenant faites attention à celui-ci, il a été introduit dans Excel 2013, et il ne fait pas ce que nous, les comptables, pensons qu'il devrait faire.Exclusif - ou signifie qu'un seul des tests est VRAI, et cela fonctionne lorsqu'il y a deux conditions. Mais pour les ingénieurs électriciens, ils le font par paires, donc cela ne donne pas les résultats que vous pourriez penser.
D'accord, voici donc Test 1, Test 2, Test 3, Test 4, trois d'entre eux sont VRAI et XOR dit "Est-ce que l'un de ces tests est VRAI?" Et quand nous faisons ce XOR, il dit "Oui, exactement l'un de ceux qui est VRAI." et c'est parce que la fonction Excel duplique le fonctionnement d'une puce très courante qui est utilisée en génie électrique, je sais, c'est choquant, non? Vous pensez qu'Excel est uniquement destiné aux comptables, mais les ingénieurs utilisent également Excel, et ils ont apparemment ajouté XOR pour eux et non pour les comptables. Donc, la façon dont cela est évalué, comme ils regardent les deux premiers, «Est-ce que l'un de ces 2 est VRAI? Oui!" Très bien, alors nous obtenons que VRAI, puis ils prennent la réponse du XOR de ceci et la comparent à la VRAI, «Est-ce que l'un de ces 2 est VRAI? Non, 2 d'entre eux sont VRAI, alors cela devient FAUX! » Ensuite, ils prennent cette réponse et XOR avec la dernière,alors ils font ça par paires, non? «Est-ce que l'un de ces 2 VRAI? Oui!" D'accord, c'est ainsi que nous l'obtenons. Il s'avère que ce qu'il fait réellement, l'ingénierie électrique, c'est ce comptage si un nombre impair d'entrées est VRAI. Pas nécessairement utile pour les comptables, qui s'attendent à ce qu'il fasse ce que signifie XOR en anglais.
D'accord, beaucoup de bons conseils dans ce livre, des trucs utiles, et même cette horrible discussion sur NAND et XOR et des choses comme ça. Achetez le livre, vous aurez tous ces conseils dans la paume de votre main. Récapitulatif d'aujourd'hui: la fonction IF la plus simple, = test logique IF, que faire si c'est TRUE, que faire si c'est FALSE, mais si vous avez 2 conditions, beaucoup de gens imbriquent les instructions IF, mais imaginez simplement si vous en aviez 3 , 5 ou 17 conditions pour nidifier. AND résoudra cela, raccourcissez-le un peu, donc si vous aimez AND, il y a aussi OU ou PAS, vous pouvez faire NAND, vous pouvez faire NOR, mais soyez prudent lorsque vous utilisez ce nouvel Excel 2013 XOR, les résultats peuvent ne pas être ce que vous attendre.
Très bien, je tiens à vous remercier d'être passé, nous vous verrons la prochaine fois pour une autre diffusion sur le net!
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2025.xlsx
