Pour faire un calcul entre deux tranches de valeurs, Excel dispose de 2 fonctions pour faire ce travail très facilement
- Déjà, ne pas utiliser la fonction SI
Beaucoup de personnes font l'erreur de faire des SI imbriqués pour écrire chaque tranche à traiter. C'est inutile avec la méthode décrite ici
- La fonction RECHERCHEV
La fonction RECHERCHEV est essentiellement utilisée pour faire des recherches commee dans une base de données. Mais en changeant un paramètre, vous pouvez retourner une valeur entre 2 tranches
- La fonction RECHERCHEX
La fonction RECHERCHEX améliore les lacunes de la fonction RECHERCHEV, surtout dans le domaine du calcul par tranche.
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.
- D'abord, 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;
- 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.
- Le tri du tableau de référence n'est pas obligatoire.
- La recherche peut se faire sur la borne inférieure, ou sur la borne supérieure d'une tranche 😀👍
Pour construire une recherche par tranche avec RECHERCHEX :
- Ecrire 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).
- Le 4ème paramètre peut être laissé vide, pas d'erreur à gérer.
- 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.
- Supposons que nous ayons 60 invités et un tableau de prix unitaire selon le nombre d'invités.
- Si nous voulons récupérer la valeur supérieure de l'intervale (ici pour 80 personnes), nous écrirons la formule suivante
=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
- Comprendre la fonction RECHERCHEV avec un quiz
- Pourquoi la fonction RECHERCHEV retourne #N/A ?
- RECHERCHEX, le remplaçant de la fonction RECHERCHEV
- Exercice RECHERCHEV, choisir la bonne réponse
- Exercice RECHERCHEV, remplir les trous
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
Elvira
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