Calcul par tranche

Temps de lecture : 3 minutes

Réaliser un calcul par tranche est assez simple dans Excel à condition d'utiliser la bonne fonction.

Fonction SI à éviter

Pour réaliser un calcul par tranche, un grand nombre d'utilisateurs d'Excel créent des SI imbriqués .

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

Bien sûr, cette solution fonctionne mais c'est une formule bien trop compliquée à écrire et surtout impossible à maintenir 😒😤😨

Préférez l'utilisation de la fonction RECHERCHEV ou RECHERCHEX pour vos calculs par tranche.

Exemple d'application - Calcul de la Prime à verser ?

Nous cherchons dans cet exemple à calculer les primes à verser à nos commerciaux en fonction du volume des ventes en se basant sur un tableau de primes suivant :

Comment trouver un resultat compris entre 2 valeurs

Quelle formule utiliser pour retourner le montant de la prime en fonction des quantités vendues ?

Solution avec RECHERCHEV

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

  1. 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. Et pour finir, la valeur 1 (ou VRAI) qui signifie valeur approchante.

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

Même si le 4ème paramètre est facultatif, c'est le plus important dans ce cas de figure. C'est uniquement dans le cadre d'une recherche par tranche que vous devez renseigner le 4ème paramètre de la fonction RECHERCHEV à 1.

Retourner la valeur entre 10 et 20

MAIS CE N'EST PAS TOUT. Il est indispensable que votre tableau de 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. Cette fonction corrige l'obligation d'avoir les données triées mais aussi permet de faire une recherche à seuil atteint ou bien seuil à atteindre.

Pour construire une recherche par tranche avec RECHERCHEX :

  1. 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. 4ème paramètre peut être laissé vide.
  5. 5ème paramètre positionné à -1.
Calcul par tranche avec la fonction RECHERCHEX
Ici les données du tableau des tranches ne sont pas triées mais ça fonctionne toujours.

Avec RECHERCHEX, vous pouvez spécifier si la recherche se fait une fois que la limite a été atteinte ou bien quand la limite va être atteinte.

Avec le paramètre positionné à -1, cela signifie que le résultat sera celui à limite à atteindre.

RECHERCHEX Valeur a atteindre 1

Et quand le paramètre est positionné à 1, le résultat sera celui quand la valeur a déjà été atteinte.

RECHERCHEX Valeur atteinte 1

Dans ce dernier exemple, pour une vente de 55 unités, la formule retourne une erreur (#N/A) car la valeur maximale de notre tableau de référence c'est 40.

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

22 Comments

  1. Gaelle Tirelli
    07/10/2021 @ 08:47

    bonjour, je sollicite votre aide pour résoudre un souci...
    J'ai un fichier excel, avec plusieurs colonnes : Locaux (colonne A), Références doc (colonne B), Titres doc (colonne C), date docs (colonne D).
    Je voudrais générer un genre de registre qui fait état de la liste des documents présent dans tel ou tel local.
    un registre par local.
    Par exemple, je veux connaitre la liste de documents présents dans le Local A, le résultat doit afficher la référence du document (qui est un lien hypertexte), ainsi qui son titre et sa date.

    Merci infiniment pour votre aide et le temps que vous prendrez pour me répondre.

    NB : Je n'ai pas réussi à concaténer en gardant le format date.

    Reply

  2. sylvain phaneuf
    30/04/2021 @ 00:11

    Bonjour, comment faire ce type de calcul: MERCI
    Manipulez la « colonne C – Nuit(s) » pour déterminer le nombre de nuits.
    1) Lorsque le total parcouru de la colonne B est de 750 kilomètre et plus dans la même journée, il est temps
    pour l’employé de s’arrêter pour dormir. C’est le maximum permis dans une journée.
    2) À chaque tranche de 750 kilomètre maximum, l’employé devra s’arrêter dormir.
    3) Attention, si le 750 kilomètre est atteint avec le total du déplacement suivant, il doit
    s’arrêter dormir avant de dépasser ce 750 kilomètre.
    4) Pour mentionner que l’employé doit arrêter dormir, faites inscrire automatiquement
    « 1 » sous un format numérique dans la colonne « Nuits » ou C.
    QUÉBECJonquière 220 KM
    JonquièreSt-Georges 320 KM
    St-GeorgesSherbrooke 150 KM, ici il faut mettre 1 dans la colonne C
    SherbrookeMontréal 160 KM
    MontréalSt-Georges 300 KM
    St-GeorgesSherbrooke 150 KM, ici 1... etc
    SherbrookeJonquière 450 KM
    JonquièreMontréal 480 KM
    MontréalQuébec 255 KM
    QuébecSt-Georges 108 KM
    St-GeorgesQuébec 108 KM

    Reply

  3. Ludovic
    23/04/2021 @ 13:59

    Bonjour à vous,
    j'ai un tableau excel où on retrouve les matricules (arrivées et départs) et salaires des personnes appartenant à une entreprise s'il vous plait j'aimerais connaitre une formule permettant de retrouver le premier salaire ainsi que le dernier

    Merci
    Ludovic

    Reply

    • Frédéric LE GUEN
      23/04/2021 @ 14:55

      Ecoutez, déjà je ne comprends pas la question mais même si j'avais compris, il n'y a pas une réponse ou une formule à donner. En fait, tout dépend du contexte (la présentation des données sources) et de l'utilisation que vous voulez en faire. C'est de toute façon plusieurs de travail pour comprendre tout cela

      Reply

  4. Etoto
    11/04/2021 @ 14:13

    Bonjour,

    J'aimerais faire une conversion d'unité de longueur avec des unités comme le pétamètre, l'année lumière ou le parsec grâce à un tableau Excel. Ce que je voudrait ce que je place le nombre en question dans une cellule de tableau, l'unité de longueur dans une autre cellule et après Excel me donne le nombre exact pour chaque unité. J'ai aussi fait un autre tableau regroupant toutes les unités converties en mètre. Mais maintenant je sais pas si je doit faire un SI imbriqué qui sera très long ou faire un RECHERCHEV sur l'autre tableau pour faire la conversion.

    Merci de votre aide

    Reply

  5. MBE Ludovic
    10/03/2021 @ 14:44

    bonjour
    s'il vous plait puis-je avoir un exemple de calcul de la recherchev avec un si à l'intérireur???

    Merci

    Reply

  6. Belkacem
    15/04/2019 @ 22:31

    Bonjour;
    sincèrement c'est très instructif. Votre façon de cibler à chaque fois l'objectif, est exceptionnel. Je ne trouve aucune difficulté a assimiler les astuces et messages véhiculés.
    Merci beaucoup pour ce que vous faites, vous nous rendez de précieux services. Je continuerai de vous suivre sachant que cela me sera d'une grande utilité.

    Belkacem

    Reply

  7. FrV
    20/09/2018 @ 22:22

    Bonsoir je cherche à formaliser sous excel une formule le calcul de rémunération cumulée par palier
    Prenons l'exemple ci-après
    Tranche % Palier Entrée Palier Sortie
    Tranche 1 65% 0 € 41 000 €
    Tranche 2 70% 41 001 € 61 000 €
    Tranche 3 75% 61 001 € 77 000 €
    Tranche 4 80% 77 001 € 90 000 €
    Tranche 5 85% 90 001 € 160 000 €
    Tranche 6 90% 160 001 € 199 000 €
    Tranche 7 95% 199 001 €

    Exemple CA en cours de mois le CA est de 65000 € (il a donc déjà donner lieu à rémunération sur la base des paliers précédents), une nouvelle facture intervient par ex. de 13000€, soit un montant total cumulé de 78000€.
    Dans ce cas la rémunération calculée sera la suivante : (1000€ x 80%)+(12000 x 75%)= 9800 €

    Merci de votre aide

    Reply

  8. Moinet
    30/04/2018 @ 10:58

    Bonjour,
    Merci pour vos informations très compréhensibles et facilement applicables!!!
    puis-je vous soumettre un problème ?
    Je souhaite intégrer une formule qui calcule des primes par palier, si des conditions sont remplies.
    je m'explique pour attribuer le pourcentage correspondant à la prime, je dois me référer à un tableau. Les vendeurs ont deux types de produits à vendre Rav ou Men; et le taux change en fonction de leur CA réalisé s'il est inférieur ou égal à 34999.99€ ou supérieur ou égal à 35000.00€
    de façon littéraire ma formule donnerai si C5 = "Rav" et que G22 est < ou égal à 34999.99€ alors J5 = (ligne correspondant au palier du tableau de marge) et si C5 = "Men" et que G22 est ou égal à 35000€ alors J5 = (ligne correspondant au palier du tableau de marge) et si C5 = "Men" et que G22 est > ou égal à 35000€ alors J5 = (ligne correspondant au palier du tableau de marge).

    je pensais donc utiliser une une formule comprenant SI CONDITIONS et RECHERCHV, mais je n'y arrive pas peut être qu'il existe une autre formule ?
    Merci de votre aide et de tous les outils publiés..

    Reply

  9. Tab florence
    24/12/2017 @ 11:53

    Bonjour,

    Pour la valeur approchante, y-aurait-il un paramètrage pour faire en sorte qu'elle se rapproche de la valeur supérieure , par exemple dans le cas des exercices de la vidéo pour le devis de la feuille 2 : pour 60 invités il faudrait prévoir 700 tentes (préconisées pour 75 personnes) et pas 600 ? Merci

    Reply

    • Frédéric LE GUEN
      26/12/2017 @ 10:02

      Bonjour,
      Non, la fonction atteint toujours la prochaine valeur de votre tableau de référence

      Reply

  10. Didier CHAVANE
    07/11/2017 @ 16:36

    Bonjour,
    Je viens juste de faire la connaissance de votre site, et ma première impression est d'en apprécier son contenu très pédagogique. Merci
    J'ai juste remplacé la plage de cellules $E$4:$F$8 par le nom de ce tableau (nommé TABLEAU).

    Reply

  11. Polygos
    03/10/2017 @ 10:47

    Bonjour
    c'est vraiment très propre et donc agréable. Par contre, quelle version d'Excel est concernée ? Je ne connaissais pour ma part que le jeu EQUIV/INDEX pour résoudre ce tyoe de recherche ?
    Merci

    Reply

  12. Elvira
    09/06/2017 @ 10:18

    Tellement clairement expliqué et facile à appliquer !! Merci, vous venez de me sauver un projet !

    Reply

    • Frédéric LE GUEN
      11/06/2017 @ 08:50

      Merci bien

      Reply

  13. olivia
    18/05/2015 @ 11:17

    Bonjour Frédéric. J'utilise un tableau croisé dynamique pour obtenir le CA par article par client (base de données énorme). J'ai besoin de récupérer pour un client (c'est la donnée variable) ces données sur un autre onglet pour faire un Dashboard de vente qui doit se mettre à jour en fonction du code client saisi. Pour capturer toutes les lignes du client, j'utilise la recherchev en valeur exacte en créant une clé de recherche (N° du client + &+ N° ligne). La recherchev ralentit énormément le calcul. La clé créée de recherche ne semble pas fonctionner pour une recherche proche. Auriez-vous une astuce pour booster le calcul? Merci encore pour vos vidéos très instructives !

    Reply

  14. Nadjet
    01/04/2015 @ 18:16

    Bonjour
    Après bien des recherches... la formule RECHERCHEV pour associer les notes aux étudiants ne fonctionne pas.
    La raison ? les moyennes sont notées avec des points et non des VIRGULES

    Merci pour votre site accessible à tous et très complet

    Reply

  15. ANN HOUYOUX (cours du soir en secrétariat de direction- Marche-en-Famenne- Belgique)
    15/10/2014 @ 11:06

    super site. Merci pour les bons tuyaux.

    Reply

  16. Marie
    18/01/2014 @ 16:03

    s'il vous plait, aidez moi à faire cet exercice, moi j'y arrive pas. je sais pas la fonction à utiliser si c'est SI ou RECHERCHEV. je vous remercie d'avance.

    Exercice 1

    Sexe Taille Catégorie
    Alain M 1,52 m
    Maurice M 1,73 m
    José M 1,81 m
    Lucie F 1,68 m
    Pierre M 1,85 m
    Théodore M 1,65 m
    Jacques M 1,56 m
    Marie F 1,45 m
    Josette F 1,85 m
    Jean-Pierre M 1,87 m

    A faire : Dans la colonne "Catégorie": Indiquer la catégorie correspondante selon les données du tableau ci-dessous

    Femme Homme
    Petite taille <1,6m <1,7m
    Taille moyenne <1,75m 1,75m >1,85m

    Reply

    • Club Microtel
      31/05/2014 @ 15:07

      Bonjour, désolée de répondre tardivement, la formule est =SI(C12="M";RECHERCHEV(D12;$G$6:$I$8;3;VRAI);RECHERCHEV(D12;$H$6:$I$8;2;VRAI))

      Hypothèse
      Homme Femme Catégorie
      0,00 0,00 Petite taille
      1,70 1,60 Taille Moyenne
      1,85 1,75 Grande taille

      Résultat
      Nom Sexe Taille Catégorie
      Alain M 1,52 Petite taille
      Maurice M 1,73 Taille Moyenne
      José M 1,81 Taille Moyenne
      Lucie F 1,68 Taille Moyenne
      Pierre M 1,85 Grande taille
      Théodore M 1,65 Petite taille
      Jacques M 1,56 Petite taille
      Marie F 1,45 Petite taille
      Josette F 1,85 Grande taille
      Jean-Pierre M 1,87 Grande taille

      Envoyez-nous un mail à microtel.78500@orange.fr si vous avez besoin de plus de détails. Bon courage

      Reply

      • Frédéric LE GUEN
        01/09/2014 @ 20:29

        Bonjour
        C'est étonnant que ça ne fonctionne pas car la formule me semble correct. Je suppose qu'en colonne D vous avez les tailles des personnes. Il n'y a pas de raison que la formule ne fonctionne pas. Peut-être que la décimale est le soucis dans votre problème, auquel cas, convertissez les tailles en centimètre.

  17. Frédéric LE GUEN
    06/12/2013 @ 10:01

    :)
    De rien

    Reply

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

MICROSOFT MVP

RECHERCHE D’EMPLOI AVEC

Back to top