Guide XP / 2003 - Niveau 3

 

  1. Les Fonctions de Recherche
  2. L'utilisation des listes de données
  3. L'automatisation des documents
  4. Les tableaux croisés dynamiques

Téléchargez le manuel complet au format pdf


La fonction
 RECHERCHE

Pour illustrer l’utilisation de la fonction RECHERCHE d’Excel, vous allons travailler sur un exemple. Il s’agira d’élaborer une facture.

 Pour cela vous allez commencer par créer un classeur  composé de trois feuilles : 

Sur la feuille  « Tarifs », saisissez le tableau suivant :

 

Sur la feuille « Remises », saisissez le tableau suivant :

 

 

Sur la feuille  « Facture », saisissez le corps de la facture :

 

Nommer des plages de cellules (voir la vidéo sur la façon de nommer des cellules)

Pour construire votre facture, vous allez utiliser les fonctions de recherche d’Excel. Ces fonctions vont faire référence à des plages de cellules contenant les informations nécessaires. Pour plus de confort, vous allez donner un nom à ces plages de cellules.

 La première plage de cellules qui nous intéresse, est celle contenant les prestations. 

  1. Cliquez sur l’onglet « Prestations »

  2.  Sélectionnez la plage de cellules A1:C13

  3.  Cliquez sur le menu Insertion - Nom - Définir

  4.  Ecrivez « tarifs »

  5.  Validez en cliquant sur OK

 La plage de cellules porte maintenant le nom « tarifs »

 

La deuxième plage de cellules qui vous intéresse, est celle contenant les remises. 

  1. Cliquez sur l’onglet « Barème des remises »

  2. Sélectionnez la plage de cellules A1:F2

  3. Cliquez sur le menu Insertion - Nom - Définir

  4. Ecrivez «remises»

  5. Validez en cliquant sur OK

 La plage de cellules porte maintenant le nom « remises »

 

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 fonctionva rechercher, dans une table_matrice, la valeur_cherchée que l’on va lui indiquer. Quand elle aura trouvé cette valeur (nombreou 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 l’exemple :

 Pour élaborer votre facture, vous allez utiliser la fonction =RECHERCHEV(). Vous allez construire cette formuledans la colonne « Désignation » afin d’afficher automatiquement le nom de la prestation en fonction du code prestation qui sera saisi dans la colonne « Code ».

la formule sera donc la suivante :

 =RECHERCHEV(A2;tarifs;2;0)

 Le résultat sera le suivant :

 

 

La fonction construite en B2 renvoie la valeur « Chambre Double ».

 En effet :

  1.  la fonction recherchela valeur 1, saisie en B5, dans la zone « articles » en se déplaçant VERTICALEMENT dans la première colonnede la zone.

  2. une fois la valeur_cherchée trouvée, la fonctionlit la ligne correspondante dans la zone jusqu’à la colonne choisie dans no_index_col.

  3. la fonctionenvoie la valeur de cette cellule.

 Pour obtenir le prix de l’article, la fonctionest 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 E5 est la suivante :

 

=RECHERCHEV(A2;tarifs;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 formulessont à recopiersur les lignes  suivantes.

 Le total de la facture s’obtient en faisant la sommedes lignesde la facture :

=SOMME(E2:E10)

 

 

Le montant de la remise sera fonctiondu total hors taxes de la facture situé en E11. On applique ici un barème par tranches. Par exemple, entre 100 € et 300 €, on applique 1% de remise. Dès que le montant hors taxes dépasse 300 € et jusqu’à ce qu’il atteigne 500 €, le taux de remise sera de 2%, etc.

Dans votre tableau des remises, tous les cas de figure ne sont pas prévus. Seuls les seuils sont représentés. La fonctionde recherchese 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. 

NB : ici le tableau des remises se présente de telle sorte que le critère recherché (le montant de la facture) se trouve sur la première ligne et non pas dans la première colonne. Ceci implique qu’il faudra utiliser la fonction =RECHERCHEH()

 

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

 =RECHERCHEH(E11;remises;2;1)*E11

 

Pour un total hors taxes de 450 € le taux de remise applicable sera donc 2%