Avez-vous une formule Excel qui imbrique plusieurs fonctions IF? Lorsque vous arrivez au point avec trop d'instructions IF imbriquées, vous devez voir si tout deviendrait plus simple avec une simple fonction RECHERCHEV. J'ai vu des formules de 1000 caractères être simplifiées en une formule RECHERCHEV à 30 caractères. Il est facile à gérer lorsque vous devez ajouter de nouvelles valeurs ultérieurement.
Il y a longtemps, j'ai travaillé pour le vice-président des ventes au travail. Je modélisais toujours un nouveau programme de bonus ou un plan de commission. Je suis devenu assez habitué à commander des plans avec toutes sortes de conditions. Celui ci-dessous est assez apprivoisé.
L'approche normale consiste à commencer à créer une formule IF imbriquée. Vous commencez toujours par le haut ou le bas de la plage. «Si les ventes dépassent 500 000 $, la remise est de 20%; autrement… ." Le troisième argument de la fonction IF est une toute nouvelle fonction IF qui teste le deuxième niveau: si les ventes dépassent 250 000 $, la remise est de 15%; autrement,… "
Ces formules s'allongent de plus en plus car il y a plus de niveaux. La partie la plus difficile d'une telle formule est de se souvenir du nombre de parenthèses fermantes à mettre à la fin de la formule.
Mini conseil bonus
Faire correspondre les parenthèses
Excel parcourt une variété de couleurs pour chaque nouveau niveau de parenthèses. Bien qu'Excel réutilise les couleurs, il utilise le noir uniquement pour la parenthèse ouvrante et pour la parenthèse fermante correspondante. Lorsque vous terminez la formule ci-dessous, continuez à taper des parenthèses fermantes jusqu'à ce que vous tapiez une parenthèse noire.
Retour à l'astuce de formule imbriquée
Si vous utilisez Excel 2003, votre formule approche déjà de la limite. À l'époque, vous ne pouviez pas imbriquer plus de 7 fonctions IF. C'était un jour affreux où les pouvoirs qui devaient changer le plan de commission et vous aviez besoin d'un moyen d'ajouter une huitième fonction IF. Aujourd'hui, vous pouvez imbriquer 64 fonctions IF. Vous ne devriez jamais faire cela, mais il est bon de savoir qu'il n'y a pas de problème d'imbrication 8 ou 9.
Plutôt que d'utiliser la fonction IF imbriquée, essayez d'utiliser l'utilisation inhabituelle de la fonction RECHERCHEV. Lorsque le quatrième argument de VLOOKUP passe de False à True, la fonction ne recherche plus une correspondance exacte.
Eh bien, VLOOKUP essaie d'abord de trouver une correspondance exacte. Mais si aucune correspondance exacte n'est trouvée, Excel s'installe dans la ligne un peu moins que ce que vous recherchez.
Considérez le tableau ci-dessous. Dans la cellule C13, Excel recherchera une correspondance pour 28 355 $ dans le tableau. Lorsqu'il ne trouve pas 28355, Excel renvoie la remise associée à la valeur qui est juste inférieure. Dans ce cas, la réduction de 1% pour le niveau de 10 000 $.
Lorsque vous convertissez les règles dans la table dans E13: F18, vous devez commencer par le niveau le plus petit et passer au niveau le plus élevé. Bien que cela ne soit pas indiqué dans les règles, si quelqu'un a moins de 10 000 $ de ventes, la remise sera de 0%. Vous devez l'ajouter comme première ligne du tableau.
Mise en garde
Lorsque vous utilisez la version «True» de RECHERCHEV, votre table doit être triée par ordre croissant. Beaucoup de gens pensent que toutes les tables de recherche doivent être triées. Mais une table n'a besoin d'être triée que dans le cas d'une correspondance approximative.
Que faire si votre manager veut une formule complètement autonome et ne veut pas voir le tableau des bonus sur la droite? Après avoir créé la formule, vous pouvez intégrer le tableau directement dans la formule.
Mettez la formule en mode Edition en double-cliquant sur la cellule ou en sélectionnant la cellule et en appuyant sur F2.
À l'aide du curseur, sélectionnez le deuxième argument entier: $ E $ 13: $ F $ 18.
Appuyez sur la touche F9. Excel incorporera la table de recherche en tant que constante de tableau. Dans la constante de tableau, un point-virgule indique une nouvelle ligne et une virgule indique une nouvelle colonne.
Appuyez sur Entrée. Copiez la formule dans les autres cellules.
Vous pouvez maintenant supprimer la table. La formule finale est présentée ci-dessous.
Merci à Mike Girvin de m'avoir appris les parenthèses correspondantes. La technique VLOOKUP a été suggérée par Danny Mac, Boriana Petrova, Andreas Thehos et @mvmcos.
Regarder la vidéo
- Avec un programme de commission, de bonus ou de remise à plusieurs niveaux, vous devez souvent imbriquer vos fonctions IF
- La limite d'Excel 2003 était de 7 instructions IF imbriquées.
- Vous pouvez maintenant nicher 32, mais je ne pense pas que vous devriez jamais nicher 32
- Quand utiliseriez-vous la version de correspondance approximative de RECHERCHEV? Il est temps.
- Traduire le programme de remise en table de consultation
- RECHERCHEV ne trouvera pas la réponse dans la plupart des cas.
- Mettre, True à la fin dira VLOOKUP de trouver la valeur un peu moins.
- C'est la seule fois où la table RECHERCHEV doit être triée.
- Vous ne voulez pas que la table RECHERCHEV soit sur le côté? Intégrez-le dans la formule.
- F2 pour modifier la formule. Sélectionnez la table de recherche. Appuyez sur F9. Entrer.
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2030 - Remplacement de l'IF imbriqué par RECHERCHEV!
Je vais diffuser tout ce livre en podcast, cliquez sur le «i» dans le coin supérieur droit pour accéder à la playlist!
Salut, bon retour sur le netcast, je suis Bill Jelen. D'accord, c'est vraiment courant avec un programme de commissions, ou un programme de rabais, ou un programme de bonus, où nous avons les niveaux, les différents niveaux, non? Si vous êtes supérieur à 10000 $, vous obtenez 1% de réduction, 50000 $ 5% de réduction. Vous devez être prudent lorsque vous créez cette instruction IF imbriquée, vous la démarrez en haut si vous recherchez plus de, ou en bas si vous recherchez moins de. Donc B10> 50000, 20%, sinon un autre IF, B10> 250000, 25%, et ainsi de suite. C'est juste une formule longue et compliquée, et si votre table est plus grande, dans Excel 2003, vous ne pouviez pas imbriquer plus de 7 instructions IF, nous sommes presque proches de la limite ici. Vous pouvez aller à 32 maintenant, je ne pense pas que vous devriez jamais aller à 32, et même si vous criez "Hé, attendez,qu'en est-il de la nouvelle fonction qui vient de sortir dans Excel 2016, la version de février 2016, avec la fonction IFS? » Oui, bien sûr, il y a moins de parenthèses ici, et 87 caractères au lieu de 97 caractères, c'est toujours un gâchis, débarrassons-nous de cela et faisons-le avec une RECHERCHEV!
D'accord, voici les étapes, vous prenez ces règles et vous les mettez à l'envers. La première chose que nous devons dire est que si vous aviez 0 $ de ventes, vous bénéficiez d'une remise de 0%, si vous aviez 10000 $ de ventes, vous bénéficiez d'une remise de 1%, si vous avez 50000 $ de ventes, vous obtenez une remise de 5% . 100000 $, 10% de réduction, 250000 $, 15% de réduction, et enfin, tout ce qui> 500000 $ obtient la réduction de 20%, d'accord. Maintenant, à chaque fois que je parle de RECHERCHEV, je dis que chaque RECHERCHEV que vous créez se terminera par FALSE, et les gens diront "Eh bien, attendez une seconde, quelle est la VRAIE version là-bas? C'est pour ce cas précis où nous recherchons une plage, donc nous recherchons cette valeur, un VLOOKUP régulier, bien sûr,, 2, FALSE ne trouvera pas 550000, va renvoyer le # N / A!Donc, dans ce cas très spécial, nous allons changer ce FALSE en TRUE, et cela indiquera à Excel "Allez chercher le 550000, si vous ne pouvez pas le trouver, donnez-nous la valeur qui est juste inférieure." Donc ça nous donne les 20%, c'est ce que ça fait, d'accord? Et c'est la seule fois où votre table RECHERCHEV doit être triée, toutes les autres fois avec, FALSE, cela peut être comme vous le souhaitez. Et oui, vous pouvez laisser TRUE désactivé, mais je l'ai toujours mis là juste pour me rappeler que c'est l'un de ces VLOOKUP étranges incroyablement dangereux, et je ne veux pas copier cette formule ailleurs. D'accord, nous allons donc copier et coller des formules spéciales, d'accord.bien? Et c'est la seule fois où votre table RECHERCHEV doit être triée, toutes les autres fois avec, FALSE, cela peut être comme vous le souhaitez. Et oui, vous pouvez laisser TRUE désactivé, mais je l'ai toujours mis là juste pour me rappeler que c'est l'un de ces VLOOKUP étranges incroyablement dangereux, et je ne veux pas copier cette formule ailleurs. D'accord, nous allons donc copier et coller des formules spéciales, d'accord.bien? Et c'est la seule fois où votre table RECHERCHEV doit être triée, toutes les autres fois avec, FALSE, cela peut être comme vous le souhaitez. Et oui, vous pouvez laisser TRUE désactivé, mais je l'ai toujours mis là juste pour me rappeler que c'est l'un de ces VLOOKUP étranges incroyablement dangereux, et je ne veux pas copier cette formule ailleurs. D'accord, nous allons donc copier et coller des formules spéciales, d'accord.
Prochaine plainte: votre responsable ne veut pas voir la table de consultation, il la veut "Débarrassez-vous simplement de cette table de consultation." D'accord, nous pouvons le faire en intégrant la table de recherche, vérifiez ceci, une excellente astuce que j'ai obtenue de Mike Girvin chez ExcelIsFun. F2 pour mettre la formule en mode Edition, puis sélectionnez très soigneusement uniquement la plage de la table de recherche. Appuyez sur F9, et il prend cette table et la met dans la nomenclature du tableau, puis j'appuie simplement sur Entrée comme ça. Copiez cela, collez les formules spéciales, puis je suis libre de me débarrasser de la table de recherche, qui continue tout simplement de fonctionner dans la rangée. C'est un énorme problème si vous devez revenir et éditer ceci, vous devez vraiment le regarder avec beaucoup de clarté. La virgule signifie que nous allons à la colonne suivante, le point-virgule signifie que nous allons à la ligne suivante, d'accord,mais vous pourriez en théorie revenir là-dedans et changer cette formule si l'un des numéros de chaîne change.
Très bien, donc utiliser une RECHERCHEV au lieu d'une instruction IF imbriquée est le conseil n ° 32 sur notre chemin vers 40, «40 meilleurs conseils Excel de tous les temps», vous pouvez avoir tout ce livre. Cliquez sur ce «i» dans le coin supérieur droit, 10 $ pour un livre électronique, 25 $ pour le livre imprimé, d'accord. Donc, aujourd'hui, nous essayons de résoudre un bonus de commission à plusieurs niveaux, ou un programme de remise. Les IF imbriquées sont vraiment courantes, attention, 7 est tout ce que vous pouvez avoir dans Excel 2003, aujourd'hui vous pouvez en avoir 32, mais vous ne devriez jamais en avoir 32. Alors, quand utiliser la version MATCH approximative de VLOOKUP, c'est tout. Prenez ce programme de réduction, mettez-le à l'envers, transformez-le en table de recherche en commençant par le plus petit nombre et en allant au plus grand nombre. VLOOKUP, bien sûr, ne trouvera pas la réponse, mais en changeant VLOOKUP en, TRUE à la fin, il lui dira de trouver la valeur un peu moins,c'est le seul moment où la table doit être triée. Si vous ne voulez pas voir ce tableau là-bas, vous pouvez l'intégrer dans la formule à l'aide de l'astuce Mike Girvin, F2 pour modifier la formule, sélectionnez la table de recherche, appuyez sur F9, puis sur Entrée.
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: Podcast2030.xlsx
