Notre magasin Rue Albert 1er, 7 6810 Pin - Chiny (/fax: 00 32 (0)61/32.00.15

Le cours en ligne EXCEL YBET

FORMATIONS LOGICIELS	FORMATIONS TECHNIQUES	DIVERS
Microsoft PowerPoint	Ordinateurs et périphériques	Le langage MySQL
Tutorial Access	Windows	Installations techniques
Formation Internet	Le matériel réseau

YBET informatique

CONTACT

La formation en ligne

1. Le Tutorial Excel

2. Fonctions de base Excel

3. Fonctions imbriquées

4. Mise en page des feuilles

5. Imprimer avec EXCEL

6. Graphiques

7. Somme conditionnelle

8. Feuilles et classeurs Excel

9. Tris et Filtres

10. Références relatives - absolues, référence des cellules

11. Les tables Excel

12. Emprunts et amortissements

13. Sous-totaux

14. Filtre élaboré

15. RechercheV et RechercheH

16. Macros

17. Macros (suite et exercices)

18. Excel, trucs et astuces

Autres formations en ligne YBET

Cours PowerPoint Un petit tutorial en ligne pour apprendre à utiliser Power Point (présentations).

Base de donnée Access: Formation informatique en ligne sur la base de donnée Access

Formation hardware, formation technique sur le matériel informatique Access

Formation PHP, MySQL, la programmation en PHP, gestion de bases de données MySQL

Microsoft Windows Formation technicien PC/réseaux: installation, dépannage, administration de Windows

Annonces YBET

Ciel Compta 2017 Probablement le meilleur rapport qualité - prix en comptabilité

La Province de Luxembourg, exemple de nos programmations spécifiques en PHP - MySQL, découverte de notre région

Facturation, gestion de stock CIEL CIEL Gescom, gérez votre activité commerciale en toute sécurité.

Notre gamme de caisses numériques pour magasins: Olympia, Acropaq.

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 Excel, sous forme de tri, filtres, ... (en plus de la fonction recherche du menu Edition). Ce chapitre va nous permettre de rechercher des données dans une liste sous forme de fonction.

Ces fonctions vont par exemple nous permettre de taper un code client dans notre facture et de reprendre automatiquement ses coordonnées sur une autre feuille du même classeur (le plus souvent). 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é à 65530 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 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.

A retenir: dans une recherche verticale ou horizontale avec la valeur proche omise si la liste n'est pas triée par ordre croissant, le résultat est généralement faux Si un code n'existe pas dans une liste triée par ordre croissant, Excel reprend automatiquement la ligne avant la valeur recherchée (la plus proche)

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.

Le tutoriel Excel

1. Cours Excel - 2. Fonctions Excel - 3. Fonctions imbriquées - 4. Mise en forme sous Excel - 5. Impressions des feuilles - 6. Graphiques Excel - 7. Fonction Somme.si() - 8. Feuilles Excel - 9. Tri et Filtre Excel - 10 . Référence relative - absolue - 11. Les tables - 12. Fonctions financières - 13. Sous-totaux - 14. Filtres élaborés - 15. Fonctions de recherche - 16. Création de macros- 17. Macros (suite et exercices) - 18. Truc Excell

Votre revendeur informatique de confiance en province de Luxembourg:

YBET informatique
Rue Albert 1er, 7
6810 Pin (Chiny)
Belgique