Il y a longtemps, j'ai travaillé pour le vice-président des ventes dans une entreprise. 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 montré dans cette astuce 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 sont supérieures à 250 000 $, alors la remise est de 15%; sinon,…."
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.
Si vous utilisez Excel 2003, votre formule approche déjà de la limite. Avec cette version, vous ne pouvez 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 en nicher autant, mais il est bon de savoir qu'il n'y a pas de problème pour emboîter 8 ou 9.
Plutôt que d'utiliser la fonction IF imbriquée, essayez d'utiliser 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 remise 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 doit être triée uniquement pour 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. Utilisez le curseur pour sélectionner le deuxième argument entier: $ E $ 13: $ F $ 18.
Appuyez sur la touche F9. Excel incorpore 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. Voir Comprendre les constantes de tableau.
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.
