Site icon Excel Exercice

Calcul par tranche dans Excel

Calcul par tranche Youtube

Exemple d'application - Calcul de la Prime à verser aux commerciaux

Pour illustrer notre article, nous allons chercher à calculer les primes à verser à nos commerciaux en fonction du volume des ventes. Nous avons à notre disposition le tableau des primes à verser selon le volume des ventes.

Fonction SI à éviter ⛔

Pour réaliser un calcul par tranche, un grand nombre d'utilisateurs d'Excel créent des SI imbriqués. Bien sûr, cette solution fonctionne mais c'est une formule bien trop compliquée à écrire et surtout impossible à maintenir

=SI(C2>=50;500;SI(C2>=25;200;SI(C2>=20;100; ....

Solution avec RECHERCHEV

Les 3 premiers paramètres reprennent la même logique que la fonction RECHERCHEV pour faire une recherche exacte.

  1. D'abord, la valeur à rechercher (ici la cellule B2 qui contient la valeur 15).
  2. Puis, les références du tableau contenant les tranches ( $E$4:$F$8 (n'oubliez pas de verrouiller les références avec des $ pour la recopie pour les autres cellules).
  3. Et nous indiquons le numéro de colonne de notre tableau de référence que nous voulons retourner (ici c'est la deuxième).

=RECHERCHEV(B2;$E$4:$F$8;2;

  1. Pour finir, la valeur 1 (ou VRAI) qui signifie valeur approchante.

=RECHERCHEV(B2;$E$4:$F$8;2;1)

MAIS CE N'EST PAS TOUT. Il est indispensable que votre tableau contenant les tranches soit trié en ordre croissant !!!!!!!!!!!

Si les données de votre tableau de référence ne sont pas triées, le résultat sera erroné ??

Solution avec RECHERCHEX

La version d'Excel de Microsoft 365 contient la fonction RECHERCHEX qui corrige les points cités avec RECHERCHEV.

Pour construire une recherche par tranche avec RECHERCHEX :

  1. Ecrire la valeur à rechercher (la cellule B2).
  2. Ensuite, la colonne contenant uniquement les tranches (seulement la colonne E).
  3. Puis la colonne contenant les valeurs à retourner (ici, la colonne F).
  4. Le 4ème paramètre peut être laissé vide, pas d'erreur à gérer.
  5. Enfin, le 5ème paramètre positionné à -1 (recherche par tranche à seuil atteint)

Explications et différences sur l'utilisation du dernier paramètre

Pour comprendre l'utilité du 5e argument de la fonction RECHERCHEX, nous allons prendre l'exemple de création d'un devis en fonction du nombre d'invités.

=RECHERCHEX($B$3;D4:D8;E4:E8;;1)

A l'inverse, si nous voulons récupérer la valeur inférieure de l'intervale (ici pour 50 personnes), la formule s'écrit de la façon suivante :

=RECHERCHEX($B$3;D4:D8;E4:E8;;-1)

Articles liées

Vous trouverez des informations complémentaires sur la fonction RECHERCHEV sur le site de Microsoft.

Quitter la version mobile