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 :
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.
- La valeur à rechercher (ici la cellule B2 qui contient la valeur 15).
- 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).
- 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;
- 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.
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 :
- La valeur à rechercher (la cellule B2).
- Ensuite, la colonne contenant uniquement les tranches (seulement la colonne E).
- Puis la colonne contenant les valeurs à retourner (ici, la colonne F).
- 4ème paramètre peut être laissé vide.
- 5ème paramètre positionné à -1.
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.
Et quand le paramètre est positionné à 1, le résultat sera celui quand la valeur a déjà été atteinte.
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.
Meyna
Gaelle Tirelli
sylvain phaneuf
Ludovic
Frédéric LE GUEN
Etoto
Belkacem
FrV
Moinet
Tab florence
Didier CHAVANE
Polygos
olivia
Nadjet
ANN HOUYOUX (cours du soir en secrétariat de direction- Marche-en-Famenne- Belgique)
Marie
Club Microtel
15/09/2022 @ 18:11
Bonjour,
Je cherche un moyen de calculer les RFA pour plusieurs clients mais qui ont chacun plusieurs conditions. Parfois par palier, par progression et aussi en fonction de l'évolution par rapport à N-1. Actuellement, j'ai une fiche par client mais cela devient épuisant quand il faut ouvrir les 300 fiches plusieurs fois dans l'année (pour des acomptes). Auriez-vous une idée svp?
Merci beaucoup,
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.
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
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
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
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
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
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
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
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..
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
26/12/2017 @ 10:02
Bonjour,
Non, la fonction atteint toujours la prochaine valeur de votre tableau de référence
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).
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
09/06/2017 @ 10:18
Tellement clairement expliqué et facile à appliquer !! Merci, vous venez de me sauver un projet !
11/06/2017 @ 08:50
Merci bien
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 !
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
15/10/2014 @ 11:06
super site. Merci pour les bons tuyaux.
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
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
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.
06/12/2013 @ 10:01
🙂
De rien