Rechercher des données sur Excel (1)

Tout d’abord, il faut dire que rechercher des données sur Excel est une tâche importante pour les professionnels du chiffre. En fait, les comptables et financiers en entreprise sont  amenés à rechercher des informations pertinentes dans des tableaux de données chiffrées. A ce titre, Excel permet de répondre à cette problématique en permettant à l’utilisateur de sélectionner des critères de recherche.

Ainsi, la combinaison de la fonction INDEX et EQUIV permet de trouver immédiatement n’importe quelle valeur dans un tableau.

Rechercher des données sur Excel : principes généraux

Contrairement aux fonctions RECHERCHEV ou RECHERCHEH, il n’est pas nécessaire de savoir dans quelle ligne ou dans quelle colonne se trouve l’information recherchée.

1) la fonction index

Pour rechercher des données sur Excel, on utilise la fonction INDEX.

La fonction INDEX se définit par la syntaxe suivante : =INDEX (plage_de_cellules; no_ligne; no_colonne)

Prenons l’exemple suivant :

Tout d’abord, on recherche la quantité vendue pour le produit B
Ensuite, a plage de cellules est l’ensemble de notre tableau : A1-F4
Le numéro de ligne de mon tableau correspondant à quantités vendues est la 3ème ligne
Le numéro de colonne de mon tableau correspondant au produit B est la 3ème colonne

Aussi, on a : INDEX (A1-F4 ;3 ;3) = 11700

2) la fonction EQUIV

Pour rechercher des données sur Excel, on peut également utiliser la fonction EQUIV.

D’abord, la fonction EQUIV se définit par la syntaxe suivante :  =EQUIV (valeur_recherchée ; plage ; type)
L’objectif ici est de situer la position dans le tableau où se trouve la valeur recherchée.

Prenons l’exemple suivant : la valeur recherchée = 11 700
– valeur recherchée est 11 700, soit ici la cellule C10
– plage de cellules correspond à la colonne C1 :C4
Type : on recherche la valeur exacte, soit la valeur 0

Ensuite, on a la formule suivante dans la cellule C10 :  EQUIV (C10 ; C1 :C4 ;0) = 3

Rechercher des données sur Excel : combinaison des fonctions INDEX-EQUIV

Finalement, pour rechercher des données sur Excel, l’utilisation conjointe de la fonction INDEX et de la fonction EQUIV permet d’obtenir le résultat directement.

Prenons le cas où l’objectif est le suivant  :  valeur recherchée = 11 700

Tout d’abord, on commence par la fonction INDEX. Aussi, on sélectionne l’ensemble de son tableau
= INDEX (A1 : F4 …)

Ensuite, on veut déterminer le numéro de ligne dans la fonction INDEX. Pour ce faire, on utilise la fonction EQUIV.
– la valeur recherchée se situe en C11 i.e. 11 700
– la plage de cellules où se trouve la valeur recherchée dans mon tableau est C1 :C4
– la valeur recherchée doit être exacte i.e. la valeur 0
Donc, on a la fonction suivante pour déterminer le numéro de ligne c’est-à-dire :
=EQUIV (C11 ; C1 :C4 ;0)

Enfin, ce résultat est intégré dans ma fonction INDEX pour déterminer le numéro de ligne :
= INDEX (A1:F4; EQUIV (C11; C1:C4;0); ….)

Alors maintenant, il reste à déterminer le numéro de colonne dans la fonction INDEX. Si, on veut connaître les quantités vendues : on tape 1 pour 1ère colonne
=INDEX (A1:F4; EQUIV (C11; C1:C4;0);1)

Rechercher des données sur Excel : fonctionnement du modèle

L’intérêt ici est de rechercher n’importe quelle valeur qui se trouve en colonne (produits) ou en ligne (prix de vente, quantités vendues, chiffre d’affaires)

Par exemple, on recherche la valeur 11 700 à partir du produit B en colonne et quantités vendues en ligne.
– On commence par la fonction INDEX : on sélectionne l’ensemble de son tableau A1 : F4
– Puis on veut déterminer le numéro de ligne (ici quantités vendues en A11) et on utilise la fonction EQUIV
=INDEX (A1:F4; EQUIV (A11; $A$1: $A$4;0).)
– Enfin on veut déterminer le numéro de colonne (ici produit B en C10) et on utilise la fonction EQUIV

=INDEX (A1:F4; EQUIV (A11; $A$1: $A$4;0); EQUIV (C10; $A$1: $F$1;0))

Le résultat est 11700

Puis, l’objectif est d’automatiser la recherche dans ce tableau
On positionne alors son curseur en A11, puis on va dans Données/validation des données/validation des données …

Dans l’onglet Options, autoriser, on sélectionne liste et en source, on sélectionne A2 :A4 qui correspond aux informations en ligne

De même, la même opération est réalisée pour les informations en colonne. On positionne ainsi son curseur en C10, produit B. Puis, dans l’onglet Options, autoriser, on sélectionne liste et en source, on sélectionne B1 :E1 qui correspond aux informations en colonne.

Prenons l’exemple suivant : chiffre d’affaires pour le produit C = 798000

Conclusion

Désormais, on peut sélectionner n’importe quelle valeur en ligne ou en colonne. On peut alors trouver la valeur recherchée immédiatement.

Pour approfondir la recherche de données sur Excel : voir : Les fonctions de recherche sur Excel