«

»

Calcul par tranche

Réaliser un calcul par tranche dans Excel peut faire peur. En fait, il n’en est rien car ce calcul s’obtient très facilement avec la fonction RECHERCHEV 😃👍

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 …“.

 

Hey ! Mais ça marche 😀

Prenons par exemple le cas de la rémunération des commerciaux d’une société. Leur prime dépend du volume des ventes qu’ils ont réalisés.

Recopiez la première formule (en C2 dans le classeur intégré ci-dessous) pour voir comment la fonction RECHERCHEV retourne les résultats dans la feuille Excel.

Même si le chiffre des ventes que nous recherchons n’est pas exactement dans notre tableau de référence (Bonus Vente), nous pouvons calculer la prime 😎





Calcul par tranche

Un grand nombre d’utilisateurs utilisent des SI imbriqués pour obtenir le même résultat😡. La solution fonctionne c’est certain mais c’est une solution bien trop compliquée à écrire et surtout à maintenir. Préférez l’utilisation de la fonction RECHERCHEV

Le document ci-contre vous présente le pourcentage des primes versées en fonction du volume des ventes réalisées dans le mois.

En fonction du volume des ventes réalisées par nos commerciaux, comment être capable de déterminer le pourcentage de prime à verser ?

 

Pour résoudre ce problème, nous allons nous servir de la fonction RECHERCHEV. Toute l’astuce consiste à utiliser cette fonction avec le paramètre optionnel à VRAI (contrairement à une recherche exacte comme dans une base de données).

Par exemple, dans l’image ci-dessous, nous voulons connaître le pourcentage de commission à verser à un vendeur quand celui-ci a vendu 15 véhicules. Mais dans notre tableau de référence, nous n’avons pas de données correspondant à 15 😱😱😱

Par contre, nous savons qu’entre 10 et 19 voitures vendues, le commercial bénéficie d’une prime de 5%.



Ecriture de la fonction RECHERCHEV

Commençons par écrire la valeur recherchée soit la cellule B2 qui contient la valeur 15.

=RECHERCHEV(B2;

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

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

Ensuite 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.

Ainsi, pour tout résultat compris entre 10 et 19, la fonction va nous retourner le pourcentage de bonus correspondant. La formule pour la cellule C2 s’écrit

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



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 retourné par la fonction serait erroné.

Notez que quand s’il existe une valeur supérieure (ici 32 en ligne 9) à celles contenues dans le tableau de référence, il n’y a pas d’erreur 😃😃😃. C’est la plus grande valeur du tableau de référence qui sera toujours retournée.

Lien Permanent pour cet article : https://www.excel-exercice.com/calcul-par-tranche/

(13 commentaires)

Passer au formulaire de commentaire

  1. Didier CHAVANE

    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).

  2. Polygos

    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

  3. Elvira

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

    1. Frédéric LE GUEN

      Merci bien

  4. Nathalie Maho

    merci pour ces explications très claires et très détaillées

  5. olivia

    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 !

  6. Nadjet

    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

  7. ANN HOUYOUX (cours du soir en secrétariat de direction- Marche-en-Famenne- Belgique)

    super site. Merci pour les bons tuyaux.

  8. Marie

    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

    1. Club Microtel

      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 à [email protected] si vous avez besoin de plus de détails. Bon courage

      1. Frédéric LE GUEN

        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.

  9. joule

    très impressionnant vos publications, vous nous avez rendu un services inoubliable, pour votre information je viens de partager votre site à tout les amis du campus

    1. Frédéric LE GUEN

      🙂
      De rien

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *