Rechercher dans des tableaux Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Rechercher des données dans des tableaux Excel

Dans cette formation Excel, nous abordons deux fonctions qui, utilisées conjointement, permettent de rechercher et d'extraire de l'information de tableaux, selon des critères qui peuvent être des données statistiques. Il s'agit des fonctions Excel Index et Equiv.

La fonction Index permet de retourner la valeur contenue dans une cellule référencée par son indice de ligne et de colonne. Sa syntaxe est la suivante :

=index(tableau_recherche ;num_ligne ;num_colonne)

La fonction Equiv permet de retourner le numéro de ligne ou de colonne de la première occurrence d'une valeur cherchée dans un tableau. Sa syntaxe est la suivante :

=equiv(valeur_cherchee ;tableau_recherche ;0)

Le dernier paramètre fixé à 0 permet d'indiquer que la recherche est bâtie sur une correspondance exacte et non approximative.

Découverte des fonctions Index et Equiv

• Télécharger le classeur extraire-index-equiv.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et activer la feuille Sorties,

Cette feuille est une source de données extraite depuis une base de données Access. Nous l'avions exploitée pour découvrir les fonctions de bases de données ainsi que la puissance des filtres Excel pour rechercher et extraire de l'information de base de données.


Sur ce tableau, nous souhaitons apprendre simplement les fonctions index et equiv, dans un premier temps. Et nous allons débuter par la fonction Equiv afin de comprendre l'intérêt de récupérer des indices de ligne ou colonne, selon l'information recherchée.

• Sélectionner la cellule G4 de la feuille Sorties,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit equiv(,
• Ensuite saisir le terme Restaurant entre guillemets, soit 'Restaurant',
• Taper un point-virgule (;) pour passer à l'argument de la zone de recherche,
• Cliquer sur l'étiquette C pour indiquer la colonne entière comme zone de recherche,
• Taper de nouveau un point-virgule (;) pour passer au dernier argument,
• Saisir 0, fermer la parenthèse et valider le calcul,

Nous obtenons le chiffre 4 en valeur de retour pour avoir saisi la formule suivante :

=EQUIV('Restaurant'; C:C; 0)

Nous demandons à la fonction EQUIV d'Excel de rechercher le texte 'Restaurant', dans la colonne C (C:C), avec une correspondance exacte (0). En colonne C, la première ligne à proposer le texte Restaurant, est en effet la quatrième par rapport au début de la sélection.

Si nous avions exploité la fonction EQUIV d'Excel sur une ligne de recherche et non plus une colonne de recherche, elle aurait retourné l'indice de colonne. Plus précisément, il s'agit de la position de la cellule dans sa rangée. C'est ce que nous allons vérifier :

• Sélectionner la cellule du résultat, soit G4,
• Enfoncer la touche F2 du clavier pour activer la saisie de la formule,
• Remplacer le deuxième argument de la colonne (C:C) par la plage A4:E4,
• Valider le calcul,

Cette fois, la fonction Equiv retourne la valeur 3. La cellule de la valeur concordant avec le critère recherché est en troisième position. Nous avons remplacé une colonne entière par une plage de cellules précise (A4:E4). Nous aurions tout aussi pu indiquer la ligne entière soit 4:4.

Nous parvenons certes bien à exploiter la fonction Equiv d'Excel mais à ce stade, sa pertinence et son potentiel ne sont pas évidents. Nous le découvrirons lorsque nous aurons appris à exploiter la fonction Index, seule dans un premier temps aussi.

La fonction Excel Index permet donc de retourner un élément de recherche, selon des indices de ligne et de colonne spécifiés. Cette recherche s'effectue dans un tableau de données. Pour faciliter la construction de la formule, nous allons attribuer un nom à ce tableau dense.

• Sélectionner la cellule E998 tout en bas de la feuille,
• Remonter tout en haut de la feuille à l'aide de l'ascenseur vertical,
• Puis, tout en maintenant la touche MAJ enfoncée, sélectionner la cellule A1,

Toutes les cellules situées entre ces deux références sont ainsi sélectionnées. C'est une technique notamment enseignée dans la formation sur les trucs et astuces dans Excel. Il suffit désormais d'attribuer un nom à cette plage pour que le tableau soit reconnu par ce nom, dans les formules notamment. L'objectif est d'éviter de devoir le sélectionner à chaque fois qu'il doit être désigné.

• Dans la zone Nom, en haut de la fenêtre et à gauche de la barre de formule, taper tab_sorties puis valider avec la touche Entrée,
• Sélectionner la cellule située en dessous du précédent calcul, soit G5,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit index(,
• Saisir le nom du tableau de recherche, soit tab_sorties,
• Puis, taper un point-virgule pour passer à l'argument suivant,
• Saisir le chiffre 4 suivi d'un point-virgule puis du chiffre 2,
• Fermer la parenthèse et valider la formule avec la touche Entrée,

Le calcul retourne le texte : MAISON PIC - Relais et Chateaux Pic. Il s'agit de l'information de la base de données tab_sorties, située en quatrième ligne et deuxième colonne. Ce sont les trois arguments que nous avons effectivement fournis à la fonction Index, soit tab_sorties;4;2.

Extraire de l'information de base de données
La fonction index a donc bien permis d'extraire de l'information de base de données. Mais dans ce que nous avons vu, elle suppose que nous connaissons précisément l'indice de ligne et de colonne de l'information à extraire. Or l'intérêt d'une extraction est de pouvoir récupérer de l'information répondant à des critères, et non pas à des références de cellules.

La fonction Equiv renvoie justement un indice de ligne ou de colonne, dans une zone de recherche en fonction d'un critère. Cela signifie qu'en combinant les deux, nous pourrions récupérer les données d'un tableau, selon un critère.

• Cliquer sur l'onglet Ventes Trimestre en bas de la fenêtre Excel pour activer sa feuille,

Le tableau de cette feuille présente les chiffres réalisés par les vendeurs d'une entreprise, au cours d'un trimestre. Un petit tableau de bord sur la droite permet de choisir des contraintes par le bais de deux listes déroulantes. Nous avions appris à créer ces listes déroulantes dans une formation Excel.

La première liste située en H7 permet de choisir parmi l'une des dates référencées dans la première colonne du tableau. A l'issue, des formules doivent permettre de récupérer le nom et le chiffre du vendeur pour cette date, en cellules H8 et H9.

La seconde liste située en I7, permet de choisir une opération de synthèse maximum ou minimum. Des formules en I8 et I9 doivent permettre de retourner le nom du vendeur et la date de la vente correspondant soit au chiffre le plus grand, soit au plus petit, en fonction du choix dans la liste.

Comme nous l'avons appris, la fonction Index permet d'extraire une information de base de données, en fonction d'un indice de ligne notamment. Et la fonction Equiv permet d'indiquer précisément l'indice de ligne de l'information recherchée. Nous allons donc imbriquer une fonction Equiv dans une fonction Index, précisément sur l'argument de l'indice de ligne. Ainsi ce numéro sera défini dynamiquement. La combinaison des deux fonctions offre de la puissance pour l'extraction d'informations.

• Sélectionner la date 18/01/2017 avec la première liste déroulante en H7,
• Puis, sélectionner l'opération Maximum avec la seconde liste déroulante en I7,
• Sélectionner la cellule H8 pour débuter la recherche du nom en fonction de la date,
• Taper le symbole = pour initialiser le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit INDEX(,
• Sélectionner ensuite le tableau de recherche, soit A5:E29,
• Taper un point-virgule pour passer à l'argument de la ligne,

C'est précisément cet argument qui doit être calculé dynamiquement par la fonction EQUIV, en fonction de l'élément à rechercher.

• A la suite, taper le nom de la seconde fonction suivi d'une parenthèse ouvrante, soit EQUIV(,
• Cliquer alors sur la cellule de la date H7 pour désigner l'élément à rechercher,
• Taper un point-virgule pour passer à l'argument de la zone de recherche,
• Sélectionner la colonne de recherche, soit A5:A29,
• Taper un point-virgule pour passer à l'argument final,
• Saisir le chiffre 0 pour demander une correspondance exacte,
• Fermer la parenthèse de la fonction EQUIV,

A ce stade, le deuxième argument de la fonction Index, l'indice de ligne, a dû être retourné par la fonction EQUIV. Il reste à indiquer l'indice de colonne. Celui-ci est fixe, il s'agit de la deuxième colonne (2), celle du vendeur.

• Taper un point-virgule suivi du chiffre 2 et fermer la parenthèse.

La formule retourne le nom du vendeur Hamalibou. Nous aurions pu obtenir le même résultat avec la fonction Recherchev que nous avons apprise dans une formation Excel. Mais cette dernière nécessite que l'élément de recherche soit situé dans la première colonne du tableau. Et cette contrainte ne sera plus respectée pour les extractions Maximum et Minimum de la seconde liste déroulante. Donc la combinaison des fonctions Index et Equiv apporte une vraie solution pour l'extraction de données sur critères spécifiques.

En cellule H9, il s'agit d'extraire le chiffre réalisé à la date indiquée dans la liste déroulante. Il suffit de répliquer la formule précédente quasiment à l'identique. Seul l'indice de colonne de l'information à extraire change, soit le troisième argument de la fonction Index. Il ne s'agit plus de la deuxième colonne pour le nom du vendeur mais de la troisième pour le chiffre réalisé. La méthode la plus simple consiste à copier la formule de barre de formule à barre de formule, pour ne pas déplacer les références, puis à modifier cet argument.

• Sélectionner la cellule H8,
• Copier l'intégralité de la formule depuis sa barre de formule,
• Valider par la touche Entrée pour sortir de la barre de formule sans modifier le calcul,
• Sélectionner la cellule H9,
• Coller la formule précédente dans sa barre de formule,
• Modifier le dernier paramètre : 2 par 3 et valider par Entrée,

Le calcul retourne bien le montant réalisé correspondant à la date et au nom du vendeur. Comme les calculs sont dynamiquement liés à leurs cellules, si vous changez la date dans la liste, vous obtenez instantanément les résultats correspondants.

Nous devons maintenant réaliser l'extraction des informations, selon le choix effectué dans la seconde liste déroulante. Il s'agit de retourner le nom du vendeur et la date correspondant à la vente la plus grande (Maximum) ou à la vente la plus petite (Minimum). Comme précédemment, la fonction EQUIV va permettre d'indiquer le numéro de ligne correspondant au chiffre max ou au chiffre min. Ce numéro sera récupéré par la fonction INDEX qui se chargera de retourner l'information correspondante de la ligne, en fonction de la colonne souhaitée (Vendeur ou Réalisé). Les fonctions MAX ou MIN doivent être utilisées pour que la fonction EQUIV puisse retourner l'indice de ligne correspondant au chiffre recherché. Comme ce calcul dépend du choix réalisé dans la liste déroulante, il doit être intégré dans une fonction SI, qui permettra d'enclencher l'un ou l'autre selon le critère.

Littéralement, nous devons exprimer ceci : Si le choix est maximum, alors chercher le nom du vendeur qui a réalisé la meilleure vente, sinon chercher le nom du vendeur qui a réalisé la plus petite vente : =SI(I7= Maximum ; Meilleur_vendeur ; Moins_bon_vendeur).

• Sélectionner la cellule I8, et taper le symbole = pour débuter le calcul,
• Taper le nom de la fonction conditionnelle suivi d'une parenthèse ouvrante, soit SI(,
• Sélectionner la cellule I7 pour poser le critère,
• Saisir ='Maximum' à la suite et taper un point-virgule,

A ce stade, nous avons simplement défini le critère qui permet de savoir si la recherche d'information se base sur le Maximum ou le Minimum : =SI(I7='Maximum';. Nous devons maintenant indiquer quoi faire lorsque ce critère est vérifié, c'est-à-dire retourner le nom du vendeur ayant réalisé la vente maximum.

• Taper le nom de la fonction Index suivi d'une parenthèse ouvrante, soit index(,
• Sélectionner le tableau de recherche soit A5:E29,
• Taper un point-virgule pour passer à l'argument de l'indice de ligne,

C'est la fonction EQUIV qui doit ici indiquer à la fonction INDEX, sur quelle ligne se trouve le chiffre maximum réalisé. Ce chiffre est le critère de recherche à passer à la fonction EQUIV.

• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit EQUIV(,
• Saisir la fonction Max et sélectionner la plage de cellules, soit MAX(C6:C29),
• Taper un point-virgule pour passer à l'argument suivant de la colonne de recherche
• Sélectionner la colonne des chiffres réalisés, soit C5:C29,
• Taper un point-virgule et saisir le chiffre 0 pour demander une correspondance exacte,
• Fermer la parenthèse de la fonction EQUIV,
• Taper un point-virgule pour passer au dernier argument de la fonction INDEX,
• Taper le chiffre 2 pour indiquer que l'information à retourner se trouve dans la colonne des vendeurs,
• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule pour passer au dernier argument de la fonction Si, le sinon,

Le principe est le même, mais l'extraction doit se faire en fonction du numéro de ligne correspondant à la plus petite vente. La méthode la plus simple consiste à copier l'imbrication des fonctions index, equiv et max précédente et de changer la fonction Max en Min.

• Copier et coller l'imbrication précédemment conçue : INDEX(A5:E29; EQUIV(MAX(C6:C29); C5:C29;0); 2),
• Changer le nom de la fonction MAX en MIN pour cette branche sinon de la fonction SI,
• Ne pas oublier de fermer la parenthèse de la fonction SI à la toute fin de la formule,
• Valider le calcul,

Pour le maximum, l'imbrication de fonctions renvoie le vendeur Galls. Pour le minimum, elle renvoie le vendeur Céhef. L'extraction sur critère dynamique par imbrications de fonctions, semble donc fonctionner parfaitement.

La formule que nous avons conçue est la suivante :

=SI(I7='Maximum'; INDEX(A5:E29;EQUIV(MAX(C6:C29); C5:C29;0);2); INDEX(A5:E29; EQUIV(MIN(C6:C29); C5:C29;0);2))

Il s'agit de la répliquer en cellule I9 afin d'extraire, selon les mêmes critères, la date à laquelle ce chiffre a été réalisé. Mais il ne faut pas oublier de modifier le dernier argument de chaque fonction Index imbriquée en remplaçant le chiffre 2 par le chiffre 1. Nous indiquerons ainsi de retourner l'information en première colonne, la date, correspondant aux mêmes critères que précédemment.

• Sélectionner la cellule I8,
• Copier (CTRL + C) l'intégralité de son calcul depuis la barre de formule,
• Valider par Entrée ou Echap afin de sortir du calcul sans l'endommager,
• Sélectionner la cellule I9,
• Coller le calcul dans sa barre de formule,
• Remplacer deux fois le dernier argument de la fonction Index, le 2 devient 1,
• Puis, valider le calcul par la touche Entrée,

La fonction retourne bien une valeur mais étonnement, il s'agit d'un nombre et non d'une date. Tout est question de formatage. La cellule est définie par défaut sur un format Standard.

• Sélectionner de nouveau la cellule I9,
• Dans la section Nombre du ruban Accueil, dérouler la liste des formats,
• Parmi les suggestions, choisir Date courte,

Nous obtenons instantanément une date en bonne et due forme, extraite du tableau source. Nous allons en profiter pour formater le résultat précédent du chiffre d'affaires extrait.

• Sélectionner la cellule H9,
• Réaliser le raccourci clavier CTRL + M,

Cette combinaison est le raccourci du bouton pour appliquer le format monétaire sur une cellule. La donnée retournée par l'extraction s'affiche avec deux décimales, le séparateur de milliers et la devise en Euros. La formation sur les raccourcis claviers Excel enseigne de nombreuses astuces à ce sujet.


Notre petit tableau de bord est désormais tout à fait fonctionnel, comme l'illustre la capture ci-dessus. Le choix d'un critère dynamique par le biais de l'une ou l'autre liste déroulante, conduit à l'extraction instantanée des données correspondantes, issues de la petite base de données Excel. Concernant l'extraction réalisée sur la base du critère défini par la seconde liste, il est intéressant de noter qu'elle dépasse les capacités de la fonction RechercheV. Cette dernière ne retourne un résultat que lorsque l'élément de recherche est bien situé en première colonne du tableau source. Or le chiffre max ou min est situé dans la troisième colonne du tableau pour lequel il s'agit de retourner, tantôt l'information située en deuxième colonne, tantôt en première.

En revanche, la fonction Excel de base de données BDLire, aurait permis de retourner elle aussi ces informations. Mais elle impose préalablement de construire une zone de critères, avec les étiquettes de champs pour trouver l'information correspondante. Nous pouvons donc estimer avoir gagné en souplesse d'un côté et en puissance de l'autre, grâce à l'imbrication des fonction Excel Index et Equiv pour extraire des informations de bases de données.

Cependant, il convient de corriger un problème persistant qui apparaît lorsque qu'aucune information n'est définie pour la recherche.

• Sélectionner la cellule H7 de la première liste déroulante,
• Enfoncer la touche Suppr du clavier pour supprimer son contenu,
• Sélectionner la cellule I7 de la seconde liste déroulante,
• Enfoncer de nouveau la touche Suppr pour supprimer son contenu,

Comme vous le constatez, les calculs en colonne H retournent des erreurs, #N/A. Il s'agit de l'abréviation pour Not Availiable en américain, ce qui signifie Non Disponible. En effet, l'extraction est tentée coûte que coûte mais ne peut produire aucun résultat cohérent, puisque le critère de recherche est vide. En revanche en colonne I, des résultats apparaissent malgré tout, mais ils sont incohérents. Il s'agit de l'extraction correspondant au chiffre minimum. Tel que nous avons conçu la fonction, nous avons indiqué d'extraire les données correspondant au plus grand chiffre si la valeur Maximum était désignée dans la liste et d'extraire les données correspondant au Minimum sinon. Ce sinon signifie dans tous les autres cas, y compris lorsque la critère est vide.



Pour gérer ces exceptions, il convient d'imbriquer chacun de ces calculs dans une fonction SI. L'objectif est de déclencher l'extraction si et seulement si le critère est défini, en d'autres termes, si la cellule de la liste déroulante n'est pas vide (<>'';).

• Modifier les formules en H8, H9, I8 et I9, respectivement comme indiqué ci-dessous :

=SI(H7<>''; INDEX(A5:E29; EQUIV(H7;A5:A29;0);2); '')
=SI(H7<>''; INDEX(A5:E29; EQUIV(H7;A5:A29;0);3); '')
=SI(I7<>''; SI(I7='Maximum'; INDEX(A5:E29; EQUIV(MAX(C6:C29);C5:C29;0);2); INDEX(A5:E29; EQUIV(MIN(C6:C29); C5:C29;0); 2));'')
=SI(I7<>''; SI(I7='Maximum'; INDEX(A5:E29; EQUIV(MAX(C6:C29);C5:C29;0);1); INDEX(A5:E29; EQUIV(MIN(C6:C29); C5:C29;0);1)); '')

Vous constatez que les incohérences et messages d'erreur disparaissent.


En revanche, si vous définissez des critères à l'aide des listes déroulantes, les extractions se déclenchent instantanément, comme le commande la fonction SI par la validation de son critère.


Dans l'exemple ci-dessus, nous en avons profité pour recouper les données à extraire. Dans la liste de droite, nous avons choisi de récupérer les informations sur la vente la plus importante. L'extraction retourne le vendeur Galls pour la date du 22/02/2017. Donc nous avons choisi cette date précisément par le biais de la première liste déroulante. Les données extraites confirment qu'il s'agit bien du vendeur Galls et que le montant est de 31000 Euros, soit la valeur la plus importante du tableau de recherche.