Leçon 13 de 23: Les formules INDEX/EQUIV

Les fonctions INDEX et EQUIV ont été créées pour travailler main dans la main. Chacune des fonctions est inutile seule mais en les combinant, on accomplit des miracles. Les formules INDEX/EQUIV sont utilisées pour chercher des valeurs dans des tableaux. Les formules INDEX/MATCH permettent de faire le même travail que les fonctions RECHERCHE, RECHERCHEH et RECHERCHEV. Elle le font mieux, sont moins limitées et peuvent faire plus.

Ainsi, les formules INDEX/EQUIV permettent à l'utilisateur de saisir un nom de client sur une facture et, son adresse, sa ville, son pays et son code postal apparaissent automatiquement dans les cellules voisines. Il inscrit un numéro de produit et, son nom et son prix unitaire apparaissent automatiquement dans les cellules voisines.

Les formules INDEX/EQUIV permettent aussi de compléter l'information d'une base de données avec l'information d'une autre base de données. Supposons que vous ayez une base de données de ventes (numéro de client, montants) et une autre base de données des clients (numéro de client, nom, adresse et ville). Supposons maintenant que vous vouliez faire une analyse des ventes par ville. Dans la base de données des ventes, vous utiliserez des formules INDEX/EQUIV pour ajouter la ville de chacun des clients en recherchant la base de données des clients. Vous pouvez maintenant faire une analyse des ventes par ville.

Dans la partie supérieure de l'image ci-dessus, vous trouvez un courte base de données d'adresses. Dans la partie inférieur de la même image vous trouvez une entête de facture. Quand vous changez le nom dans la cellule B6, vous voulez que l'adresse (B7), le nom de la ville (B8), le nom de la province (B9) et le code postal (B10) se modifient AUTOMATIQUEMENT. Pour les besoins de l'exemple, la base de données et l'entête sont sur la même feuille mais la base de données pourrait être sur une autre feuille nommée "Liste". À la fin de cette leçon vous trouverez les quatre formules si la base de données se trouvait sur une autre feuille.

Voici comment je compose les formules INDEX/EQUIV même après 15 ans d'utilisation. Dans les explications ci-dessous, je compose la formule dans la cellule B7 pour rechercher une adresse.

INDEX

Je compose d'abord une formule avec la fonction INDEX. La syntaxe générale d'une formule utilisant la fonction INDEX est la suivante: =INDEX(dans quelle tableau se fait la recherche, sur quelle ligne, dans quelle colonne). Ma formule de base est donc: =INDEX(B1:B3;1;1) ou en bon français ; "Quelle est la valeur de la cellule de la ligne 1 et de la colonne 1 du tableau B1:B3". Je travaille toujours dans des colonnes uniques et non dans des tableaux.

Dans in deuxième temps, je remplace le deuxième argument (1) par une formule EQUIV pour trouver une ligne en fonction d'un critère.

EQUIV

La fonction EQUIV recherche un valeur dans une colonne et retourne le numéro de ligne dans laquelle se trouve cette valeur. La syntaxe de base est: =EQUIV(Qu'est-ce que je cherche, dans quel tableau, avec quelle précision). Ma formule EQUIV sera donc =EQUIV(B6;A2:A3;0) ou en bon français "Sur quelle ligne de la colonne A2:A3 se trouve le nom inscrit dans la cellule B6 de l'entête. Le troisième argument peut être utilisé afin de préciser si on recherche une valeur exacte (0) ou la plus proche valeur inférieure (-1) ou la plus proche valeur supérieure (1).

INDEX/EQUIV

En combinant les deux formules, nous obtenons la formule finale: =INDEX(B1:B3;EQUIV(B6;A2:A3;0);1) ou en bon français: "quelle est l'adresse dans la colonne B1:B3 et sur la ligne où se trouve la valeur de B6 dans la colonne A1:A3.

Voici donc les quatre formules de l'entête:

B7:=INDEX(B1:B3;EQUIV(B6;A2:A3;0);1)

B8:=INDEX(C1:C3;EQUIV(B6;A2:A3;0);1)

B9:=INDEX(D1:D3;EQUIV(B6;A2:A3;0);1)

B10:=INDEX(E1:E3;EQUIV(B6;A2:A3;0);1)

Remarquez que la section EQUIV de toutes les cellules est la même puisque toutes les recherches sont faites dans la colonne A1:A3. Ce qui change c'est la colonne dans laquelle s'effectue la recherche de l'adresse, la ville, la province et le code postal.

SI la base de données se trouvait sur une autre feuille nommée "Liste", les formules seraient les suivantes:

B7: =INDEX(Liste!B2:B3,EQUIV(B6,Liste!A2:A3,0),1)

B8: =INDEX(Liste!C2:C3,EQUIV(B6,Liste!A2:A3,0),1)

B9: =INDEX(Liste!D2:D3,EQUIV(B6,Liste!A2:A3,0),1)

B10: =INDEX(Liste!E2:E3,EQUIV(B6,Liste!A2:A3,0),1)