Excel par l'exemple
Débuter et progresser sur Microsoft Excel à l'aide d'exercices aidés et corrigés, de guides pratiques, de trucs et astuces, d'un forum utilisateurs, etc.

 

Téléchargez le livret des formules Excel


  Accueil
Exercices aidés et corrigés
Guides Excel et Internet Facile
 Téléchargement
Trucs et Astuces
Formules de calcul
Démonstrations Vidéos
 Applications Excel
 Touches de raccourci
Liens
 Forum utilisateurs
 Livre d'or
 Contactez moi

A propos de cet espace

 

=RECHERCHEV()
=INDEX()
=CHOISIR()

La fonction de calcul =RECHERCHEV()

Cette fonction requiert 4 arguments et se présente ainsi :

=RECHERCHEV(valeur_cherchée;table_matrice;no_index_col;valeur_proche)

La fonction va rechercher, dans une table_matrice, la valeur_cherchée que l’on va lui indiquer. Quand elle aura trouvé cette valeur (nombre ou texte), elle va renvoyer le contenu de la cellule se trouvant sur la même ligne que la valeur trouvée et dans la colonne désignée par no_index_col.

Si la valeur_cherchée n’existe pas dans la table_matrice, la fonction affichera :

  • un message d’erreur, si on choisi le nombre 0 pour l’argument valeur_proche

  • le contenu de la colonne, déterminée par no_index_col, correspondant à la valeur précédente la plus proche de la valeur_cherchée, si on choisi le nombre 1 pour l’argument valeur_proche

 Dans notre exemple :

Pour élaborer une facture, nous allons utiliser la fonction =RECHERCHEV().

Tout d’abord, nous construisons 3 tableaux, un sur chaque feuille du classeur, un modèle de facture, un tableau des articles (nous nommerons la zone " articles " pour la réutiliser dans les formules) et un tableau de remises (que nous nommerons " remise ").

 

La première formule à créer permettra d’afficher automatiquement la désignation de l’article (dans la colonne " désignation ") dont le code aura été saisi en A2 (colonne " code article). Nous allons construire cette formule dans la colonne " Désignation " (en B2)

La formule, que l’on va construire dans la cellule B2, sera donc la suivante :

  • La valeur_cherchée sera le code de l’article, préalablement saisi en A2.

  • La table_matrice est la plage de cellules que l’on a préalablement nommé " articles ".

  • no_index_col sera ici le chiffre 2 qui correspond à la deuxième colonne de notre zone " articles ", et qui contient le nom des articles.

  • valeur_proche sera ici le chiffre 0 qui permettra d’afficher un message d’erreur si le code article saisi n’existe pas.

la formule sera donc la suivante :

=RECHERCHEV(A2;articles;2;0)

Le résultat sera le suivant :

La fonction construite en B2 renvoie la valeur " Disque dur ".

En effet :

  • la fonction recherche la valeur 1, saisie en A2, dans la zone " articles " en se déplaçant VERTICALEMENT dans la première colonne de la zone.

  • une fois la valeur_cherchée trouvée, la fonction lit la ligne correspondante dans la zone jusqu’à la colonne choisie dans no_index_col.

  • la fonction renvoie la valeur de cette cellule.

Pour obtenir le prix de l’article, la fonction est la même que la précédente, mis à part le no_index_col qui est ici le chiffre 3 (troisième colonne de la zone " articles "). La fonction construite en D2 est la suivante :

=RECHERCHEV(A2;articles;3;0)

 

Pour obtenir le montant total, on multiplie la quantité en C2 par le prix obtenu en D2. la formule est la suivante :

=C2*D2

Ces formules de la ligne 2 sont à recopier sur les lignes suivantes.

Le total hors taxes de la facture s’obtient en faisant la somme des lignes de la facture :

=SOMME(E2:E8)

Le montant de la remise sera fonction du total hors taxes de la facture situé en E9. On applique ici un barème par tranches. Par exemple, entre 5000 € et 7000 €, on applique 5% de remise. Dès que le montant hors taxes dépasse 7000 € et jusqu’à ce qu’il atteigne 10000 €, le taux de remise sera de 7%, etc.

Dans notre tableau des remises, tous les cas de figure ne sont pas prévus. Seuls les seuils sont représentés. la fonction de recherche se basera donc sur ces seuils pour renvoyer le taux de remise à appliquer. Si le total de la facture est différent du montant d’un des seuils (cas général), la fonction renverra le taux de remise correspondant au seuil inférieur le plus proche.

La fonction (construite dans notre exemple en E10) sera donc la suivante :

=RECHERCHEV(E9;remise;2;1)*E9

  •  F11 est la cellule contenant le total hors taxes qui est la valeur_cherchée dans la table_matrice.

  •  remise est le nom de la table_matrice (ou plage de cellules) contenant le barème des remises où la recherche sera effectuée.

  •  2 est le numéro de la colonne de la table_matrice " remise " qui contient le taux de remise à appliquer.

  •  1 signifie qu’en l’absence de la valeur_cherchée dans la table_matrice, la valeur inférieure la plus proche sera renvoyée.

  •  Le résultat de la recherche est multiplié par le montant hors taxes en E9 (*E9) pour obtenir le montant de la remise.

Pour un total hors taxes de 6325 € le taux de remise applicable sera donc 5%

La fonction =CHOISIR()

Exemple avec des taux de remise variables en fonction d'un code (1,2 ou 3) affichant des remises de 5%, 10% ou 15 % :

La cellule A1 contient le code d'escompte (1, 2 ou 3),
La cellule en B1 s'écrit :

=CHOISIR(A1;5%;10%;15%)

où Si le code de remise est "1" la valeur de B1 sera 5%, si le code de remise est "2" la valeur de B1 sera 10% et si le code d'escompte est "3", la valeur de B1 sera 15%.

La formule peut contenir autant de variables que vous le désirez.

 

 

  
 


Membre de ClickFR, Reseau francophone Paie-Par-Click