Comment fonctionne l'INDEXATION sur Excel et Google Sheets ?

Comprendre l'INDEXATION sur Excel et Google Sheets

L'indexation excel, au sens large, fait référence à la capacité de localiser et d'extraire des données spécifiques à partir d'une plage de cellules ou d'un tableau. Dans ce contexte, nous nous concentrerons sur l'utilisation de la fonction INDEX combinée à d'autres fonctions comme EQUIV ou MATCH pour créer des recherches dynamiques et puissantes.

La fonction INDEX seule renvoie la valeur d'une cellule dans une plage en fonction de son numéro de ligne et de colonne. Combinée à EQUIV, elle devient un outil redoutable pour la recherche de données.

Syntaxe de la fonction INDEX

La fonction INDEX a deux syntaxes principales :

• Forme matricielle : INDEX(matrice, no_ligne, [no_colonne])

  • matrice : La plage de cellules à partir de laquelle vous souhaitez extraire une valeur.
  • no_ligne : Le numéro de la ligne dans la matrice à partir de laquelle la valeur sera extraite.
  • [no_colonne] (facultatif) : Le numéro de la colonne dans la matrice à partir de laquelle la valeur sera extraite. Si omis, la fonction renvoie toute la ligne.

• Forme de référence : INDEX(référence, no_ligne, [no_colonne], [no_zone])

  • référence : Une ou plusieurs plages de cellules.
  • no_ligne : Le numéro de la ligne dans la plage à partir de laquelle la valeur sera extraite.
  • [no_colonne] (facultatif) : Le numéro de la colonne dans la plage à partir de laquelle la valeur sera extraite. Si omis, la fonction renvoie toute la ligne.
  • [no_zone] (facultatif) : Le numéro de la zone dans la référence à partir de laquelle la valeur sera extraite.

Syntaxe de la fonction EQUIV (MATCH en anglais)

La fonction EQUIV recherche une valeur spécifique dans une plage de cellules et renvoie la position relative de cette valeur. Elle est souvent utilisée conjointement avec INDEX pour déterminer le numéro de ligne ou de colonne à utiliser.

• EQUIV(valeur_recherchée, matrice_recherche, [type])
  • valeur_recherchée : La valeur que vous souhaitez rechercher.
  • matrice_recherche : La plage de cellules dans laquelle vous souhaitez rechercher la valeur.
  • [type] (facultatif) : Le type de correspondance souhaité :
    • 0 : Correspondance exacte (le plus courant).
    • 1 : Correspondance la plus grande inférieure ou égale à la valeur recherchée (la matrice doit être triée en ordre croissant).
    • -1 : Correspondance la plus petite supérieure ou égale à la valeur recherchée (la matrice doit être triée en ordre décroissant).

Applications pratiques de l'INDEXATION

L'indexation excel offre une grande flexibilité pour la manipulation de données. Voici quelques exemples concrets :

Exemple 1 : Recherche d'un prix à partir d'un code produit

Imaginez un tableau avec une colonne "Code Produit" et une colonne "Prix". Vous souhaitez trouver le prix d'un produit spécifique en entrant son code.

1. Tableau de données : Créez un tableau avec les colonnes "Code Produit" (A) et "Prix" (B).
2. Cellule de recherche : Créez une cellule (par exemple, D1) où vous entrerez le code produit à rechercher.
3. Formule : Utilisez la formule suivante dans une autre cellule (par exemple, E1) : =INDEX(B1:B10,EQUIV(D1,A1:A10,0)). Cette formule recherche le code produit entré dans D1 dans la plage A1:A10, puis renvoie le prix correspondant dans la plage B1:B10.

Explication de la formule :

• EQUIV(D1,A1:A10,0) : Recherche le code produit de la cellule D1 dans la colonne A et renvoie le numéro de ligne où il est trouvé.
• INDEX(B1:B10, ...) : Utilise le numéro de ligne renvoyé par EQUIV pour extraire la valeur correspondante de la colonne B (la colonne des prix).

Capture d'écran : (Description textuelle : Une capture d'écran d'une feuille Excel. La colonne A contient des codes produits (par exemple, PROD1, PROD2...). La colonne B contient les prix correspondants. La cellule D1 contient le code produit à rechercher. La cellule E1 affiche le prix trouvé grâce à la formule INDEX/EQUIV.)

Exemple 2 : Extraction de données en fonction de plusieurs critères

Supposons que vous ayez un tableau avec les colonnes "Ville", "Produit" et "Ventes". Vous souhaitez extraire les ventes pour une ville et un produit spécifiques.

1. Tableau de données : Créez un tableau avec les colonnes "Ville" (A), "Produit" (B) et "Ventes" (C).
2. Cellules de recherche : Créez deux cellules (par exemple, E1 et E2) où vous entrerez la ville et le produit à rechercher, respectivement.
3. Formule : Utilisez la formule suivante dans une autre cellule (par exemple, F1) : =INDEX(C1:C10,EQUIV(E1&E2,A1:A10&B1:B10,0)). Cette formule combine la ville et le produit pour la recherche.

Explication de la formule :

• E1&E2 : Concatène la ville et le produit entrés dans les cellules E1 et E2.
• A1:A10&B1:B10 : Concatène les colonnes Ville et Produit du tableau de données.
• EQUIV(..., 0) : Recherche la combinaison ville-produit dans la plage concaténée.
• INDEX(C1:C10, ...) : Extrait la valeur correspondante de la colonne C (la colonne des ventes).

Attention : Cette formule est une formule matricielle. Vous devez la valider avec Ctrl+Maj+Entrée (ou Cmd+Maj+Entrée sur Mac).

Capture d'écran : (Description textuelle : Une capture d'écran d'une feuille Excel. Les colonnes A, B et C contiennent respectivement les villes, les produits et les ventes. Les cellules E1 et E2 contiennent la ville et le produit à rechercher. La cellule F1 affiche les ventes trouvées grâce à la formule INDEX/EQUIV.)

Exemple 3 : Utilisation de la fonction CHOISIR pour sélectionner une colonne

La fonction CHOISIR permet de sélectionner une colonne spécifique en fonction d'un numéro d'index. Cela peut être utile en combinaison avec INDEX pour créer des recherches encore plus dynamiques.

Imaginez un tableau avec des informations sur des employés (Nom, Age, Salaire). Vous souhaitez pouvoir sélectionner quelle information afficher en fonction d'un numéro.

1. Tableau de données : Créez un tableau avec les colonnes "Nom" (A), "Age" (B) et "Salaire" (C).
2. Cellule de sélection : Créez une cellule (par exemple, D1) où vous entrerez le numéro de la colonne à afficher (1 pour Nom, 2 pour Age, 3 pour Salaire).
3. Formule : Utilisez la formule suivante : =INDEX(A1:C10,1,CHOISIR(D1,1,2,3)). Cette formule affichera la première ligne du tableau et la colonne sélectionnée via la cellule D1.

Explication de la formule :

• CHOISIR(D1,1,2,3) : Si D1 contient 1, renvoie 1; si D1 contient 2, renvoie 2; si D1 contient 3, renvoie 3.
• INDEX(A1:C10,1, ...) : Utilise le numéro de colonne renvoyé par CHOISIR pour extraire la valeur de la première ligne.

Capture d'écran : (Description textuelle : Une capture d'écran d'une feuille Excel. Les colonnes A, B et C contiennent respectivement le Nom, l'Age et le Salaire des employés. La cellule D1 contient le numéro de la colonne à afficher. La cellule E1 affiche l'information correspondante de la première ligne.)

Bonnes pratiques pour l'utilisation de l'INDEXATION

• Nommez vos plages : Utiliser des noms de plages clairs et descriptifs rend vos formules plus lisibles et plus faciles à maintenir. Par exemple, au lieu d'utiliser A1:A10, nommez la plage "CodesProduits".
• Vérifiez les erreurs : Utilisez les fonctions SIERREUR ou ESTNA pour gérer les erreurs potentielles, comme les valeurs non trouvées. Cela rendra votre feuille de calcul plus robuste.
• Utilisez la validation des données : Pour les cellules de recherche, utilisez la validation des données pour limiter les entrées possibles et éviter les erreurs de frappe.
• Commentez vos formules : Ajoutez des commentaires à vos formules complexes pour expliquer leur fonctionnement. Cela facilitera la compréhension et la maintenance future.

Erreurs courantes à éviter

• Oublier de valider les formules matricielles : Les formules matricielles nécessitent d'être validées avec Ctrl+Maj+Entrée (ou Cmd+Maj+Entrée sur Mac). Si vous oubliez de le faire, vous obtiendrez des résultats incorrects.
• Erreurs de correspondance : Assurez-vous que le type de correspondance (0, 1 ou -1) dans la fonction EQUIV est correct. Une erreur de correspondance peut entraîner des résultats inattendus.
• Plages incorrectes : Vérifiez que les plages de cellules utilisées dans les fonctions INDEX et EQUIV sont correctes et couvrent toutes les données pertinentes.
• Références relatives vs. absolues : Utilisez les références absolues ($A$1) pour verrouiller les plages si nécessaire, afin d'éviter que les références ne se décalent lorsque vous copiez la formule.

INDEXATION sur Google Sheets

L'utilisation de l'indexation excel (fonctions INDEX et EQUIV) est très similaire sur Google Sheets. Les syntaxes sont identiques, et les bonnes pratiques et erreurs à éviter sont les mêmes.

Cependant, Google Sheets offre quelques fonctionnalités supplémentaires qui peuvent être utiles en combinaison avec l'INDEXATION :

• QUERY : La fonction QUERY permet d'extraire et de filtrer des données à partir d'une plage en utilisant une syntaxe SQL. Elle peut être utilisée à la place de INDEX et EQUIV pour des recherches plus complexes.
• FILTER : La fonction FILTER permet de filtrer une plage de données en fonction de critères spécifiques. Elle peut être utilisée pour créer des listes de données dynamiques.

En conclusion, la maîtrise de l'indexation excel (fonctions INDEX et EQUIV) est essentielle pour exploiter pleinement le potentiel d'Excel et Google Sheets. En comprenant les syntaxes, en appliquant les bonnes pratiques et en évitant les erreurs courantes, vous serez en mesure de créer des feuilles de calcul puissantes et efficaces.