8- Fonction =RECHERCHE()

Imprimer l’énoncé | Télécharger l’exercice | Conseils | Corrigé

Enoncé

6. Renommez l’onglet de la feuil3 « Catalogue »
7. Nommer le tableau contenant la liste des clients « clients« , le tableau des frais de port « port« , le tableau des articles « catalogue« 
8. Insérez une nouvelle feuille de calcul (menu « Insertion/ feuille »)
9. Reproduisez sur la feuil4 (la nouvelle feuille) la facture (modèle en ANNEXE 2)
10. Renommez l’onglet de la feuil4 « Modèle facture« 
11. concevez les formules de la facture de la façon suivante :

    • A la place de « NOM » : saisir le nom d’un client
    • A la place de « ADRESSE » : créez une fonction de recherche sur le NOM à partir de la liste des clients
    • idem pour le code postal et la ville
    • Colonne « désignation » : créez une fonction de recherche sur le code article à partir du catalogue produits
    • Colonne « quantité » : saisie directe
    • Colonne « prix unitaire » : créez une fonction de recherche sur le code article à partir du catalogue produits
    • Colonne « montants » : produit des prix par les quantités
    • Ligne « remise » : créez une fonction recherche sur le total des marchandises à partir du barème de remises
    • Pour élaborer les factures relatives aux bons de commandes ci-dessous, dupliquez la feuille « Modèle facture » autant de fois que nécessaire de façon à préserver votre modèle.

Pratique : Télécharger le fichier de l’exercice

ANNEXE 1

ANNEXE 2

La fonction =RECHERCHEV() permet d’afficher n’importe quelle valeur contenue dans un tableau à partir d’un code contenu dans la première colonne de celui-ci.

Exemple : Le tableau suivant contient des informations relatives au nombre d’habitants par ville ainsi que leur classement dans l’ordre des villes les plus peuplées.

La première colonne contient le nom de la ville. Pour l’exemple, la formule de calcul sera construite dans une autre feuille de calcul (sachant qu’elle pourrait l’être n’importe où).

 

 

Dans la cellule G3, on saisi le nom de la ville recherchée

Dans la cellule G4, on construit la fonction =RECHERCHEV() suivante :

=RECHERCHEV(B2;B:D;2;0)

  • G3 : valeur_cherchée. Référence de la cellule où la valeur_cherchée a été saisie. (ici le nom de la ville).
  • A:D : table_matrice. zone contenant les données. (ici la liste des villes avec leur nombre d’habitants et leur rang).
  • 2: no_index_col. numéro de la colonne du tableau (et non de la feuille Excel) contenant la donnée désirée (ici la colonne 2 du fichier salarié : « nombre d’habitants »).
  • 0 : valeur_proche. le zéro, choisi ici, indique que la valeur_cherchée doit se trouver dans la table_matrice. choisir 1 pour afficher la valeur proche (la précédente).

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

valeur_cherchée est la valeur que l’on demande à Excel de rechercher dans la table_matrice. Cette recherche va s’effectuer verticalement dans la première colonne de cette table_matrice.

table_matrice est un tableau, ou une table de données, dans laquelle s’exécute la fonction de recherche.

no_index_col est le numéro de la colonne où se trouve la donnée à afficher à l’aide de la fonction de recherche

valeur_proche indique si l’on veut trouver exactement la valeur_cherchée (choisir zéro) ou une valeur proche (choisir 1). Si l’on choisi 0 (zéro), et que la valeur_cherchée n’existe pas dans table_matrice, un message d’erreur apparaît. Dans le même cas, si l’on choisi 1, Excel renverra la valeur la plus proche dans la colonne de recherche.

NB : dans RECHERCHEH() la recherche s’effectue horizontalement dans la première ligne de la table_matrice

commentaire 1 : les formules de la ligne 8 sont à recopier sur les lignes suivantes

commentaire 2 : En D25, il s’agit de la formule =RECHERCHEH() et non =RECHERCHEV(). Le tableau des frais de port est conçu avec les critères de recherche en première ligne et non en première colonne comme pour les autres tableaux. Le mot « port » employé dans la formule est le nom donné à la plage de cellule A2:F3 de l’onglet « Frais de port ». Le dernier argument de la fonction est 1 ce qui permet d’afficher la valeur la plus proche correspondant au total en E13.

 

Astuce : pour éviter des messages d’erreur, sur les lignes où le code article n’est pas renseigné, ajoutez, dans le catalogue des articles un nouvel article dont le code sera 0 (zéro) et pour lequel la désignation et le prix seront laissés vides.

En effet, Excel considère les cellules vides comme contenant un zéro. Les fonctions de recherche iront chercher ce code dans le catalogue et afficherons un message d’erreur si elles ne le trouvent pas.

Supprimez les zéros en allant sur le menu « Fichier » / Options Excel / Options avancées et décochez « Afficher un zéro dans les cellules qui ont une valeur nulle »

Télécharger le corrigé