Notre magasin

Rue Albert 1er, 7

6810 Pin - Chiny

(/fax: 00 32 (0)61/32.00.15

Le cours EXCEL d'YBET Informatique

Gérez facilement vos factures, stocks, clients et fournisseurs:  CIEL Gestion commerciale

FORMATION informatique

Le MAGASIN YBET

PRODUITS et SERVICES

Formation INTERNET

Activités et présentation

Formation Word - Tutorial Access

Plan d'accès à Chiny

15. Fonctions de rechercheV, rechercheH Excel

1. Introduction - 2. Préparation des listes - 3. La fonction rechercheV() - 4. Valeur proche

Dans les précédents chapitres, nous avons abordé les recherches sous Excel, sous forme de tri, filtres, ... (en plus de la fonction recherche du menu Edition). Ce chapitre de la formation Excel en ligne va nous permettre de rechercher des données dans une liste sous forme de fonction.

Les applications sont nombreuses. Ces fonctions de recherche vont par exemple nous permettre de taper un code client dans notre facture Excel et de reprendre automatiquement ses coordonnées. Ceci est également valable en tapant le code  d'un produit, la recherche va automatiquement récupérer la description, le prix et le taux de TVA.

2. Préparation des listes

Reprenons notre fichier facture Excel. Cette version inclus déjà quelques petites fonctionnalités comme le non affichage des calculs dont le résultat est zéro et le calcul des différentes bases de TVA. Nous allons modifier cette facture pour reprendre en premier les coordonnées du client et en deuxième, rajouter une colonne dans les lignes de produits que nous appellerons "Code". N'oubliez pas d'enlever la protection des cellules si nécessaire. Rajoutons les 2 cases pour pouvoir encoder le code client en A11 et rajoutons une colonne à gauche de Quantité pour insérer le code du produit par ligne.

La deuxième étape va être de créer une lise de clients reprenant le code du client, son nom, adresse, code postal, ville et numéro de TVA. Pour cela, utilisons une autre feuille du classeur (même si la liste des clients peut être dans un classeur différent).

Sur la feuille (que nous appellerons clients), tapons dans la première ligne les différents renseignements comme en-tête, les lignes suivantes nous permettrons de taper les coordonnées effectives des clients. Nous pouvons éventuellement reprendre d'autres renseignements, mais à ce stade, ce n'est pas nécessaire.

Sur une autre feuille (que nous appellerons produits), nous allons reprendre les différentes caractéristiques de nos produits:

Jusqu'ici, rien de compliqué. Les fonctions de recherche vont reprendre dans les différentes listes les renseignements souhaités pour notre facture.

Cette solution a des limites et ne permet pas de reprendre une liste trop longue de produits, Excel est limité à 65535 lignes maximum.

3. La fonction rechercheV()

Commençons par les produits. Le but est donc de taper le code en A14 dans la feuille facture et que Excel recherche ce code dans la feuille produits, puis insère dans notre facture les descriptions, prix et taux de TVA correspondants. Positionnons le curseur en C14 et dans insertions fonction sélectionnons rechercheV. Pour les versions Excel XP et 2003, Microsoft nous a compliqué la tâche: il faut quasiment passer par une recherche de fonction et la fonction recherche n'est plus utilisée (compatibilité avec les versions antérieures).

RechercheV recherche une valeur dans la première colonne à gauche du tableau, puis renvoie une valeur dans la même ligne à partir d'une colonne spécifié. Par défaut, le tableau doit être trié par ordre croissant. Cette petite phrase en rouge va déjà nous poser quelques problèmes, même si l'utilisation des tris Excel n'a plus de secret pour nous. La fonction rechercheh()  est identique sauf que les listes sont données en lignes au lien d'en colonnes.

• La valeur recherchée est la cellule dans laquelle nous tapons le code de notre produit sur la feuille facture (soit A14)
• La table de matrice est la liste de produits que nous avons créé, soit le contenu de la feuille produits (sans la première ligne qui reprend les en-têtes). Premier problème de cette fonction, si vous créez de nouveaux produits, ils ne seront pas repris dans la liste. Une solution est de sélectionner plus de lignes que vous n'avez de produits.

• No_index_col est le numéro de colonne dans la table de matrice (liste des produits). Comme dans notre facture, notre colonne reprend les descriptions, le numéro est 2 (le numéro correspondant dans notre liste de produits pour la description)
• Valeur_proche (facultatif) propose de rechercher la valeur exacte ou non. Par défaut, elle ne recherche pas la valeur exacte, mais la valeur la plus proche dans la liste. Nous allons voire ci-dessous les conséquences de l'utilisation de cette valeur proche. Pour l'instant, omettons cette valeur

Et voici notre fonction rechercheV() remplie. Essayons en A14 de taper le code PC (repris dans notre liste de codes). Excel nous donne:

Ce n'est pas franchement ce que nous recherchons. Dans notre feuille produits, trions notre liste en ordre croissant. Le résultat dans notre feuille facture est maintenant correcte.

Dans notre feuille Produits, tapons les produits suivants:

Ces produits vont nous permettre d'y voire un peu plus claire. Trions ensuite l'ensemble de la liste par ordre croissant. Dans notre cellule A14 de la feuille facture, tapons a comme code, effectivement la description est correcte. Tapons maintenant aa comme code.

La valeur reprise est la description du produit dont le code est a et non un message d'erreur nous signalant que le produit n'existe pas.

4. Valeur proche

Dans la cellule C14, modifions notre formule de recherche

Comme le code aa n'est pas repris dans la liste, la description affichée est #N/A, un message d'erreur Excel. Cette solution est déjà beaucoup plus pratique. En fin de liste, ajoutons justement ce code aa avec comme description produit aa (ne faites pas de tri, mais modifiez la table de matrice dans la fonction pour reprendre tous les produits si nécessaires). Le résultat devient correct

Insérons de nouveau la fonction rechercheV() dans la cellule D1' pour rechercher le prix unitaire. Je vous laisse le soin de remplir la fonction pour récupérer le code TVA du produit.

Il ne nous reste plus qu'à recopier les fonctions dans les différentes colonnes en utilisant le petit carré en bas à droite.

Malheureusement, cette manière de travailler ne fonctionne pas. Excel va adapter les références de cellules lignes par lignes. Il nous faut utiliser des références de cellules absolues dans les cellules pour la table de matrice. La fonction devient dans la table de matrice:

Truc: utilisez la touche F4 dans la formule pour passer de relatif en absolu.

Il nous reste un dernier petit problème. Si nous ne tapons pas de code, notre facture ressemble à ceci:

Les différents #N/A ne sont pas très esthétiques. Nous allons utiliser la fonction Si() pour corriger ce problème d'affichage en l'imbriquant avec notre rechercheV:

  =SI(A14<>"";RECHERCHEV(A14;produits!$A$2:$D$9;2;0);""). De cette manière, si aucun code n'est rentré, rien n'est affiché. Vous pouvez directement modifier votre fonction dans la barre de formule:

Modifiez les autres fonctions de la même manière sur la première ligne de produits et recopiez les cellules vers le bas.

Il nous reste maintenant à récupérer les coordonnées du client. Ceci ne va pas poser de problèmes particulier. Le code du client est tapé en A10. Le nom du client est affiché en D6. Dans cette cellule, la fonction Excel de recherche devient: =RECHERCHEV(A11;'adresse client'!A2:J17;2;0), et ainsi de suite pour les autres coordonnées. Remarquez que le code de recherche dans notre feuille facture peut être positionné n'importe où, pas seulement dans la même ligne.

YBET Informatique: formations débutants ou avancés

dans notre salle de cours à Chiny ou en entreprise: Windows, office, création Internet et gestion

Renseignements, tarifs et inscriptions sur formation informatique privée