Préparez votre certification
Microsoft Office Specialist

Rechercher des données

Recevez ce cours au format PDF par email, en envoyant un message ici

Avant de voir les fonctions de recherche, vous n'êtes pas sans savoir qu'un tableau peut être présenté dans le sens horizontal ou dans le sens vertical, comme le montre l'illustration suivante.

Dans les deux tableaux nous avons exactement les mêmes informations à une différence près. Le premier tableau présente les numéros des examens en colonnes et les noms des jours en lignes, alors que dans le deuxième tableau on trouve les noms des jours en colonnes et les numéros des examens en lignes. Autrement dit, les colonnes du premier tableau forment les lignes du deuxième tableau et les lignes du premier forment les colonnes du deuxième. On dit que le deuxième tableau est une Transposition du premier.

Pour permuter les lignes et les colonnes d'un tableau, il y a une première méthode, déjà vue dans un chapitre précédent, qui consiste à copier le tableau et de le coller dans un autre emplacement en effectuant un Collage spécial et en choisissant l'option Transposé.

Une deuxième méthode pour inverser les lignes et les colonnes ou transposer un tableau est d'utiliser la fonction qui porte le même nom : TRANSPOSE.

La fonction TRANSPOSE()

TRANSPOSE() est une fonction matricielle, c'est à dire qu'elle agit sur un tableau (une matrice) tout entier. On la trouve dans la catégorie Recherche et référence dans le groupe Bibliothèque de Fonctions sous l'onglet FORMULES.

On peut utiliser l'assistant pour l'insérer ou bien l'écrire manuellement. La syntaxe est simple, elle exige un seul argument qui est le tableau à transposer.

=TRANSPOSE(tableau)

Les étapes à suivre sont :

1. On sélectionne une plage de cellules telle que le nombre de colonnes égale le nombre de lignes du tableau source et le nombre de lignes égale le nombre de colonnes du tableau source
   
2. On écrit l'expression =TRANSPOSE(, cette écriture apparait seulement dans la cellule active
3. On sélectionne le tableau source, que l'on veut transposer, pour renseigner l'argument de la fonction, puis on ferme la parenthèse
4. ATTENTION, la validation est un peu particulière puisqu'il s'agit d'une fonction matricielle ; on valide par Ctrl + Maj + Entrée

Voir l'animation

La fonction RECHERCHE()

La fonction RECHERCHE(), à ne pas confondre avec la fonction CHERCHE(), permet de chercher une donnée dans une plage de cellules et renvoie une valeur lui correspondant dans une autre plage de cellules. Cette fonction se décline en deux variantes : RECHERCHE(Vecteur) et RECHERCHE(Matrice).

RECHERCHE(Vecteur)

Un Vecteur signifie une plage de cellules contiguës, étalée sur une seule ligne ou bien sur une seule colonne. La variante RECHERCHE(Vecteur) exige trois arguments :

• Valeur_recherchée : argument obligatoire
• Vecteur_de _recherche : obligatoire, c'est la liste (verticale ou horizontale) où la fonction va vérifier l'existence de la valeur recherchée
• Vecteur_résultat : facultatif, c'est où la fonction va extraire la valeur correspondante à la valeur recherchée.

La syntaxe est la suivante :

=RECHERCHE(Valeur_recherchée;Vecteur_de_recherche;[Vecteur_resultat])

Par exemple, on voudrait chercher dans la plage de cellules A3:A13 un nom qui sera saisi dans la cellule D3 puis extraire la date de naissance qui lui correspond dans la plage de cellule (le vecteur) B3:B13 et l'afficher dans la cellule E3.

RECHERCHE(Matrice)

Par le mot Matrice on comprend une plage de cellules contiguës étalées sur au moins deux lignes et deux colonnes, sinon ça reste un vecteur.

Cette variante de la fonction RECHERCHE() nécessite deux paramètres (tous deux obligatoires) qui sont : Valeur_recherchée et la Matrice_de_recherche. Elle permet de chercher une valeur dans la première colonne de la matrice passée en paramètre et renvoie son correspondant situé sur la même ligne dans la dernière colonne de la plage sélectionnée.

La syntaxe est la suivante :

=RECHERCHE(Valeur_recherchée;Matrice_de_recherche)

Étant donné qu'on cherche la population d'un continent saisi dans la cellule A14, la fonction a eu comme deuxième paramètre la matrice A3:B10, comme ça la colonne de renvoi sera celle de la population (la plus à droite). Pour obtenir le pourcentage de la population on doit lui passer la matrice A3:C10.

Au lieu de saisir la syntaxe manuellement, vous pouvez insérer la fonction en utilisant l'assistant.

La fonction RECHERCHEV()

La fonction RECHERCHEV() est très pratique pour extraire des informations à partir d'un tableau de données. Sa syntaxe est assez claire dans la mesure où il suffit de répondre aux quatre questions suivantes :

1. Que cherche-t-on ?
2. Où va-t-on chercher ?
3. Quelle est l'information à extraire ?
4. Cherche-t-on une valeur exacte ou bien approchée ?

=RECHERCHEV(Valeur_recherchée;Ou_chercher;Valeur_a_extraire;Rapprochée/exacte)

Par Exemple, on aimerait extraire à partir de la base ci-dessous, le nom de l'article dont le code est 5.

En réponse aux questions précédentes on a

1. On cherche le code ayant la valeur 5
2. Dans la plage de cellules A2:E15
3. On veut l'information qui se trouve dans la colonne C (c'est la troisième (3) colonne à partir de la colonne du code)
4. On cherche la valeur de code exacte

D'où l'expression :

À savoir :

• Le premier argument peut être une valeur ou bien la référence de la cellule contenant la valeur à rechercher.
• Le dernier argument peut prendre la valeur FAUX ou 0 pour une valeur exacte, ou bien VRAI ou 1 pour une valeur approchée.
• Le dernier argument est facultatif, s'il est omis, il prend par défaut la valeur VRAI

Pour mettre en pratique cette fonction, on va créer une facture qui se remplit automatiquement en puisant les informations à partir d'une base de données située sur une autre feuille Excel. Pour ce faire, je vous propose de télécharger le fichier de travail ici. C'est un classeur Excel formé de deux feuilles : la feuille Facture sur laquelle on va travailler, et la feuille etatStock qui va servir comme base de données.

L'idée est, pour remplir la facture, l'utilisateur n'aura pas à saisir les noms des articles (Désignation), ni leurs prix unitaires. Ces informations seront extraites automatiquement à partir de la feuille etatStock. La même chose pour le prix Total. Le prix TTC, le Total HT et la Somme TTC, seront calculés automatiquement grâce à des formules qui sont déjà saisies. Les seules données que l'utilisateur devra saisir sont le Code de l'article et la Quantité.

Pour la première ligne de la facture, essayons de répondre aux quatre questions de tout à l'heure. Je vous les rappelle.

1. Que cherche-t-on ?
2. Où va-t-on chercher ?
3. Quelle est l'information à extraire ?
4. Cherche-t-on une valeur exacte ou bien approchée ?

Réponses concernant la première ligne de la facture (la ligne 6 de la feuille)

1. On cherche l'article qui porte le code que l'utilisateur aura saisi dans la cellule A6 de la feuille Facture
2. On va chercher ce Code dans la première colonne de la plage A2:E15 qui se trouve dans la feuille etatStock
3. On veut extraire la Désignation de ce produit qui se trouve dans la colonne (3)
   
4. On cherche la valeur exacte du code

L'écriture de la fonction sera saisie dans la cellule C6 de la feuille Facture et en se basant sur ces réponses, son expression sera comme c'est indiqué dans la capture d'écran ci-dessous. Il ne reste que la valider pour obtenir le résultat.

Le déroulement complet de l'opération est montré par l'animation suivante :

Apparemment la fonction marche bien, mais un message d'erreur #N/A apparait si on ne saisit pas un code d'article. Ce message est illustré dans la capture suivante.

L'erreur #N/A est générée dans le cas où aucune saisie du code n'a été faite ou bien si la valeur saisie n'est pas trouvée dans la base de données. On doit donc gérer ce cas pour améliorer le rendu de la fonction. Pour ceci, on va utiliser le fonction SIERREUR() pour vérifier si la fonction RECHERCHEV() renvoie une erreur, dans ce cas, la cellule C6 ne doit pas afficher l'erreur mais plutôt rester vide, sinon elle affiche le résultat de la fonction RECHERCHEV().

L'expression améliorée devient alors une écriture qui a l'air complexe mais qui ne l'est pas.

L'animation suivante montre que la fonction RECHERCHEV() combinée à la fonction SIERREUR() marche à merveille. Elle ne renvoie plus le message d'erreur si le code de l'article n'existe pas dans la base ou bien si l'utilisateur n'a pas saisi une valeur.

Maintenant que la fonction marche bien, il est temps de la recopier vers les lignes d'en bas pour pouvoir remplir les autres lignes de la facture. Rien de plus simple, puisqu'il suffit de cliquer dans la cellule C6 qui contient l'expression de la formule puis de la prendre par le petit carré qui se trouve dans son coin inférieur droit et de faire glisser la souris vers les cellules au-dessous dans la même colonne.

En essayant de remplir les lignes de la facture, on remarque que pour la première ligne ça marche, mais pour les autres rien ne s'affiche??!!

En examinant les expressions des cellules de la colonne C, on remarque qu'elles utilisent pour le deuxième argument une Référence relative, c'est à dire que la zone d'où elles puisent les informations, change d'une ligne à l'autre, etatStock!A2:E15 - etatStock!A3:E16 - etatStock!A4:E17 - etc. À chaque fois, la zone de recherche est décalée vers le bas, ce qui a pour effet d'exclure les premières lignes de la base et rajouter des lignes vides en bas, et par la suite de fausser les rendus de la fonction.

L'animation suivante montre la zone de recherche correspondant à l'expression écrite dans chaque ligne.

Pour chacune des lignes de la facture, l'extraction de l'information doit toujours se faire à partir de la même zone. Pour cela, avant de recopier l'expression vers les autres cellules, on doit fixer la zone d'extraction des données en utilisant une Référence absolue pour le deuxième argument de la fonction RechercheV(). Pour ce faire, deux méthodes sont possibles :

• La première façon de faire est d'utiliser les symboles $ : On insère un symbole $ devant les colonnes et les lignes, dans la formule. On peut le faire manuellement ou bien en utilisant la touche F4.
• La deuxième façon consiste à transformer la zone d'extraction en un tableau en lui affectant un nom simple, significatif et sans espace : Sélectionner toute la zone puis cliquer sur le bouton Mettre sous forme de tableau sous l'onglet ACCUEIL

C'est ce nom donné à la zone d'extraction qui sera utilisé comme deuxième argument de la fonction RechercheV et l'expression finale devient alors :

Cette deuxième méthode, qui consiste à utiliser le nom de la zone, est à préférer pour deux raisons :

1. L'écriture de l'expression est plus claire
2. Elle s'adapte automatiquement à la plage des cellules en cas d'ajout ou de suppression de produits dans la base.

Une fois la rectification faite, on peut recopier la formule de la cellule C6 vers les cellules de dessous dans la même colonne et tout sera mis en ordre.

La fonction RECHERCHEH()

La fonction RECHERCHEH() fait exactement la même chose que la fonction RECHERCHEV() sauf qu'elle fait le travail à l'horizontal. C'est à dire qu'elle cherche une valeur dans la première ligne d'un tableau et renvoie une valeur située un peu plus bas dans la même colonne.

La syntaxe est semblable à celle de la fonction Recherchev(), il n'y a que le nom de la fonction qui change.

=RECHERCHEH(Valeur_recherchée;Ou_chercher;Valeur_a_extraire;Rapprochée/exacte)

Pour le troisième argument, Valeur_a_extraire, on passe le numéro de la ligne où l'on veut extraire l'information. On peut écrire l'expression manuellement ou bien l'insérer à l'aide de l'assistant.

Recevez ce cours au format PDF par email, en envoyant un message ici