Courantes Statistiques

Calcul par tranche

Temps de lecture 2 minutes

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

La fonction RECHERCHEV nous permet également d'effectuer une recherche comme dans une base de données mais aussi pour effectuer une comparaison entre 2 colonnes. Mais ici, nous allons voir comment récupérer des valeurs "comprises entre ...".

Calcul par tranche dans Excel

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

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

Bien sur la solution fonctionne mais c'est une formule bien trop compliquée à écrire et surtout à maintenir 😒😤😨

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

Exemple d'application - Prime versée

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.

Calcul par tranche

La plupart des valeurs n'existent pas dans le tableau de référence. Mais grâce à la fonction RECHERCHEV nous allons simplifier la formule 😍😍😍

Ecriture de la fonction RECHERCHEV

1. Positionnez-vous en C2 pour y afficher le résultat

2. Indiquer la valeur à rechercher, ici la cellule B2 qui contient la valeur 15.

=RECHERCHEV(B2;

3. Ensuite nous allons indiquer notre tableau de référence ; $E$4:$F$8 (n'oubliez pas de verrouiller les références avec des $ pour la recopie pour les autres cellules)

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

4. Puis 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;

5. Et pour finir, la valeur 1 (ou VRAI) qui signifie valeur approchante.

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

Ainsi, pour toutes ventes comprise entre 10 et 19, la fonction va nous retourner la prime correspondante.

Retourner le résultat entre 2 tranches de valeurs avec la fonction RECHERCHEV

Explication du dernier paramètre

Dans l'article sur la fonction RECHERCHEV, il était indiqué que dans 99,9% des cas, il fallait renseigner le dernier paramètre à 0 pour avoir une recherche exacte.

C'est effectivement vrai car la fonction RECHERCHEV est majoritairement utilisée pour retourner une valeur exacte dans un tableau. Mais il n'y a qu'un seul cas de figure où la fonction RECHERCHEV utilise le paramètre 1 et c'est dans ce cas de figure.

Données triées obligatoirement

Il est indispensable que votre tableau de référence 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é 😱😱😱

Les valeurs du tableau de référence ne sont pas triées et entraînent un résultat faux

Vidéo explicative - plusieurs exemples

Related posts

Arrondir aux dizaines, centaines, milliers

Frédéric LE GUEN

Statistique à 2 variables

Frédéric LE GUEN

NB.SI.ENS plus grand – plus petit

Frédéric LE GUEN

16 commentaires

Belkacem 15/04/2019 at 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

Répondre
FrV 20/09/2018 at 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

Répondre
Moinet 30/04/2018 at 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..

Répondre
Tab florence 24/12/2017 at 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

Répondre
Frédéric LE GUEN 26/12/2017 at 10:02

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

Répondre
Didier CHAVANE 07/11/2017 at 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).

Répondre
Polygos 03/10/2017 at 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

Répondre
Elvira 09/06/2017 at 10:18

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

Répondre
Frédéric LE GUEN 11/06/2017 at 08:50

Merci bien

Répondre
olivia 18/05/2015 at 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 !

Répondre
Nadjet 01/04/2015 at 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

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

super site. Merci pour les bons tuyaux.

Répondre
Marie 18/01/2014 at 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

Répondre
Club Microtel 31/05/2014 at 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

Répondre
Frédéric LE GUEN 01/09/2014 at 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.

Répondre
Frédéric LE GUEN 06/12/2013 at 10:01

:)
De rien

Répondre

Laissez un commentaire

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

Ce site utilise des cookies pour améliorer votre expérience et vos recherches. Nous pensons que vous êtes dʼaccord sur ce principe mais vous pouvez refuser cette option. Accepter Continuer

Privacy & Cookies Policy