Bottom line: Apprenez à calculer les commissions dans Excel pour un plan et une table de taux à plusieurs niveaux de base à l’aide des fonctions IF, VLOOKUP ou XLOOKUP.
Niveau de compétence: Intermédiaire
Tutoriel vidéo
Le calcul des commissions dans Excel peut être une tâche très délicate. Cela est particulièrement vrai si vous avez essayé d’utiliser plusieurs instructions IF pour calculer les commissions pour chaque niveau d’une table de taux.
Cet article explique comment utiliser la fonction VLOOKUP pour faciliter ce processus. Le secret consiste à définir le dernier argument du vlookup sur TRUE, pour trouver la correspondance la plus proche.
Veuillez consulter mon article sur l’utilisation de VLOOKUP pour trouver la correspondance la plus proche pour une explication détaillée sur la définition du dernier argument sur TRUE.
Mise à jour: Le message a été mis à jour pour inclure des instructions de calcul des commissions avec la nouvelle fonction XLOOKUP. Voici un article & vidéo sur XLOOKUP.
Télécharger le fichier
Télécharger le fichier à suivre.
Calculs de commissions – SI Vs VLOOKUP Vs XLOOKUP.xlsx (31.5 KB)
Calcul des commissions avec VLOOKUP
Avec un plan de commission simple, vous avez généralement une table de taux qui répertorie le taux de paiement à chaque niveau de vente. Comme le représentant des ventes fait plus de ventes, son taux de paiement augmentera généralement.
Le taux de paiement peut être un montant fixe en dollars, un pourcentage des revenus, un pourcentage du quota, etc. Le travail du VLOOKUP consiste à trouver le montant des ventes du représentant dans le tableau des taux et à renvoyer le taux de paiement correspondant.
Pour cet exemple, notre plan de commissions ressemble à ce qui suit:
- Rep vend $0-$50,000, ils gagnent 5%
- Rep vend $51 000$et 100 000 habitants, ils gagnent 7%
- Rep vend $100,001 de 150 000$, ils gagnent 10%
- Rep vend plus de 150 001, ils gagnent 15%
Le représentant se fera un certain montant de chiffre d’affaires au cours du mois. Il / elle gagnera un certain taux de paiement en fonction du niveau de ventes réalisé.
Nous pouvons utiliser une formule VLOOKUP pour calculer le taux de paiement pour un montant de vente donné (valeur de recherche). Pour que cela fonctionne, nous devons définir le dernier argument du vlookup sur TRUE.
Avec le dernier argument défini sur TRUE, vlookup trouvera la correspondance la plus proche de la valeur de recherche qui est inférieure ou égale au montant de la recherche. Cela nous permet essentiellement de trouver une valeur entre des plages de deux nombres (niveaux).
C’est pourquoi le dernier argument de VLOOKUP est nommé range_lookup. Lorsque cet argument est TRUE, VLOOKUP recherche entre des plages de valeurs dans la colonne tier minimum pour trouver une correspondance exacte ou la valeur inférieure à la valeur de recherche.
Comment configurer la table de taux
Lorsque vous placez cette table de taux dans Excel, il vous suffit de lister le niveau minimum pour la plage de recherche. Encore une fois, le VLOOKUP recherchera une « correspondance la plus proche » inférieure ou égale à la valeur de recherche. S’il trouve une valeur supérieure à la valeur de recherche, il renverra la ligne précédente.
Dans cet exemple pour une table de taux de commissions, la première ligne de la plage de recherche doit être nulle. En effet, le représentant des ventes pourrait potentiellement avoir des ventes de 0 $ et la valeur de recherche serait nulle. Si la valeur de recherche (montant des ventes) était un nombre négatif, le vlookup renverrait une erreur.
Il est important de le savoir et de configurer votre table de taux pour toutes les valeurs de recherche possibles.
Si le montant des ventes est supérieur à la dernière ligne de la plage de recherche, le vlookup renverra la dernière ligne. Par exemple, si le représentant réalisait des ventes de 175 000 $, vlookup rapporterait 15%.
Calculez les commissions pour Retourner une valeur en dollars
Le paiement peut également être retourné sous forme de valeur en dollars, au lieu d’un pourcentage. Avec cette configuration, le paiement sera forfaitaire.
Cela signifie que le paiement sera le même, quel que soit le montant des ventes dans le niveau. Dans l’exemple ci-dessous, si le paiement sera de 1 000 $ si le montant des ventes est de 55 000 $ ou de 95 000 $.
Le paiement n’est PAS sur une échelle mobile. C’est un tarif forfaitaire pour chaque niveau.
Si vous recherchez un calcul à échelle mobile, consultez mon article sur le calcul de la commission avec une structure de taux à plusieurs niveaux à l’aide de SUMPRODUCT.
Calculs de commission avec XLOOKUP
Nous pouvons également utiliser la nouvelle fonction XLOOKUP pour ce calcul. Cela fonctionne très similaire à VLOOKUP lors de la recherche de la correspondance la plus proche.
Avantages avec XLOOKUP
Il existe deux principales différences et avantages avec XLOOKUP:
- Avec XLOOKUP, nous spécifions le tableau de recherche et le tableau de retour en tant que plages distinctes. Il s’agit de B4: B7 et D4: D7 dans l’image ci-dessus. C’est un avantage par rapport à VLOOKUP car nous pouvons insérer ou supprimer des lignes entre les colonnes B et D, et la formule fonctionnera toujours.
Lorsque nous ajoutons / supprimons des colonnes dans le tableau de table de VLOOKUP, nous devons modifier manuellement le numéro d’index de colonne ou utiliser une formule pour le calculer dynamiquement. - XLOOKUP n’exige PAS que les données soient triées lors de l’utilisation de la correspondance exacte ou du prochain élément plus petit / plus grand pour le mode de correspondance. XLOOKUP recherchera en fait l’élément plus petit / plus grand suivant et renverra le résultat de cette ligne / colonne.
VLOOKUP nécessite que les données soient triées lorsque le dernier argument est VRAI. En règle générale, vous voudrez de toute façon trier vos données pour ces types de tables, mais il est bon de savoir que vous pouvez simplement placer de nouveaux niveaux de taux en bas de la table et XLOOKUP fonctionnera.
Inconvénient de XLOOKUP
Le principal inconvénient de XLOOKUP est la compatibilité. Vous et tous les utilisateurs de votre fichier devez utiliser une version de Microsoft/Office 365 dotée de XLOOKUP. Il n’est pas rétrocompatible ou disponible sur les anciennes versions d’Excel.
Par conséquent, VLOOKUP fonctionne très bien dans ce scénario et est beaucoup plus facile à écrire qu’une formule IF imbriquée.
Évitez les formules IF imbriquées
Une approche courante pour calculer les commissions utilise des instructions IF. Avec une table de taux comme celle-ci, vous devrez écrire plusieurs instructions IF. Vous devez essentiellement écrire une instruction IF pour chaque niveau (ligne) de la table.
Vous devez ensuite joindre toutes les instructions IF en une formule longue et laide. Celles-ci sont appelées instructions IF imbriquées.
J’essaie toujours d’éviter lesFs imbriqués lorsque cela est possible car ils sont difficiles à lire et à comprendre, et ils peuvent être plus lents à calculer pour Excel. Si vous avez des milliers de formules IF imbriquées dans votre classeur, votre temps de calcul pourrait ralentir.
La formule IF est beaucoup plus difficile à maintenir. Il nécessite une modification lorsque des lignes sont ajoutées / supprimées de la table. VLOOKUP et XLOOKUP ne nécessitent PAS ce type de maintenance.
Si vous utilisez IF, assurez-vous de faire de chaque référence de cellule à la table de taux une référence absolue (F4 sur le clavier). Sinon, vous obtiendrez des résultats incorrects lors de la copie de la formule.
Utiliser VLOOKUP est beaucoup plus facile et plus propre que d’utiliser des formules IF imbriquées. Comme vous pouvez le voir avec cet exemple, vlookup vous permet d’utiliser une formule pour calculer le taux de paiement de la commission pour un montant de vente donné.
Ressources supplémentaires
- Comment Utiliser VLOOKUP pour Trouver la Correspondance la Plus proche – Le Dernier Argument Est Égal à TRUE
- Comment Calculer les Commissions Cumulatives à Plusieurs Niveaux avec SUMPRODUCT
- VLOOKUP Expliqué en termes simples chez Starbucks
- VLOOKUP & MATCH – Un Duo dynamique
- La nouvelle Fonction XLOOKUP pour Excel + Tutoriel Vidéo
- La Fonction IF Expliquée: Comment Écrire une Formule d’instruction IF dans Excel
Comment Utilisez-Vous VLOOKUP avec la Correspondance la plus proche?
Le VLOOKUP avec la technique de correspondance la plus proche peut également être utilisé pour les calculs de tranches d’imposition, l’appariement des prix, etc. Qu’utilisez-vous cette technique pour calculer?