Fonction INDEX : les techniques secrètes et sous-estimées qui rendent vos tableaux Excel vraiment dynamiques

Dans ce tutoriel, je vais vous montrer comment une seule fonction Excel peut vous éviter des formules interminables, des recherches qui cassent dès qu’on ajoute une ligne, et des tableaux de bord impossibles à maintenir.

Nous allons partir d’un tableau de ventes, très simple à comprendre, puis nous allons voir comment récupérer automatiquement une valeur à partir d’une région, d’un produit et d’un mois.

Ensuite, nous irons plus loin : nous allons utiliser INDEX pour renvoyer une ligne complète, extraire une colonne entière, additionner une période variable, comparer deux mois non consécutifs, et même choisir entre deux tableaux différents.

L’idée, c’est qu’à la fin de cette vidéo, nous ne verrons plus INDEX comme une petite fonction de recherche, mais comme une vraie brique de construction pour créer des fichiers Excel propres, dynamiques et beaucoup plus faciles à faire évoluer.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons les ventes mensuelles d’une entreprise qui commercialise du matériel informatique.

Pour commencer, nous allons transformer ce simple bloc de données en tableau structuré.

Un tableau structuré, c’est un tableau qu’Excel reconnaît comme une vraie base de données, avec des en-têtes, des filtres automatiques, un nom, et surtout des références beaucoup plus lisibles dans les formules.

L’avantage, c’est que notre tableau pourra s’agrandir automatiquement si nous ajoutons de nouvelles lignes.

Et comme nous allons utiliser la fonction INDEX avec des formules dynamiques, c’est exactement ce dont nous avons besoin.

Pour cela, nous sélectionnons n’importe quelle cellule du tableau, puis nous utilisons le raccourci [Ctrl]+[L].

Dans la fenêtre qui s’affiche, nous vérifions que la plage commence bien en A6 et que la case « Mon tableau comporte des en-têtes » est cochée.

C’est une étape importante, car un tableau structuré s’agrandit automatiquement quand nous ajoutons de nouvelles lignes.

Ensuite, nous allons dans l’onglet « Création de tableau », puis nous nommons le tableau :

  Ventes

Ce nom va nous permettre d’écrire des formules plus lisibles, car au lieu d’écrire une plage comme C7:H18, nous pourrons utiliser directement le nom affecté.

C’est plus clair, plus robuste, et surtout beaucoup plus simple à relire.

2. Récupérer une valeur précise avec INDEX

Commençons par le cas le plus classique.

Nous voulons trouver le chiffre d’affaires des « Écrans » dans la région « Sud » pour le mois d’« Avril ».

La fonction INDEX fonctionne comme un GPS dans un tableau.

Nous lui donnons une plage, un numéro de ligne, un numéro de colonne, et elle nous renvoie la valeur située au croisement.

La structure générale est la suivante :

  =INDEX(plage;numéro_ligne;numéro_colonne)

Dans notre cas, la plage de chiffres correspond aux colonnes de janvier à juin.

Nous pourrions écrire une formule fixe comme celle-ci :

  =INDEX(Ventes[[Janvier]:[Juin]];5;4)

Ici, Excel va chercher dans la 5e ligne de la plage de ventes mensuelles, puis dans la 4e colonne.

Mais cette formule n’est pas assez souple.

Si nous changeons la région, le produit ou le mois, le résultat ne changera pas automatiquement.

Nous allons donc remplacer les numéros fixes par la fonction EQUIV.

EQUIV permet de trouver la position d’une valeur dans une liste.

Pour trouver la ligne, nous devons chercher la combinaison Région + Produit, car chaque région apparaît plusieurs fois.

La partie qui cherche la ligne est donc :

  =EQUIV(K6&K7;Ventes[Région]&Ventes[Produit];0)

Le & permet concaténer, c’est-à-dire accoler des textes les uns à la suite des autres.

Excel cherche donc, par exemple, « SudÉcrans » dans les combinaisons des colonnes Région et Produit.

Le dernier argument, 0, indique que nous voulons une correspondance exacte.

Pour trouver la colonne du mois, nous utilisons :

  =EQUIV(K8;Ventes[[#En-têtes];[Janvier]:[Juin]];0)
  

Cette formule cherche le mois saisi en K8 dans les en-têtes Janvier à Juin.

Nous pouvons maintenant assembler le tout dans la cellule K9 :

  =SIERREUR(INDEX(Ventes[[Janvier]:[Juin]];EQUIV(K6&K7;Ventes[Région]&Ventes[Produit];0);EQUIV(K8;Ventes[[#En-têtes];[Janvier]:[Juin]];0));"Aucun  résultat")

La fonction SIERREUR évite d’afficher une erreur si une valeur est mal saisie.

Par exemple, si nous écrivons « Ecran » sans accent alors que le tableau contient « Écrans », Excel ne trouve pas la correspondance.

Au lieu d’afficher #N/A, la formule affiche « Aucun résultat ».

Petite astuce : pour éviter les erreurs de saisie, nous pouvons créer des listes déroulantes.

Nous sélectionnons K6, puis nous allons dans « Données » > « Validation des données ».

Dans « Autoriser », nous choisissons « Liste », puis nous indiquons les régions.

Nous faisons la même chose pour K7 avec les produits, et pour K8 avec les mois.

C’est une petite amélioration, mais elle rend le fichier beaucoup plus fiable.

3. Renvoyer une ligne ou une colonne complète

INDEX peut renvoyer une seule valeur, mais ce n’est pas tout.

Avec Excel 365, INDEX peut aussi renvoyer plusieurs cellules d’un coup.

C’est ce qu’on appelle un résultat dynamique, ou un déversement.

Nous allons maintenant afficher toute la ligne mensuelle correspondant au produit choisi.

Par exemple, si nous choisissons « Ouest » et « Ordinateurs », nous voulons récupérer toutes les ventes de janvier à juin.

Nous nous plaçons en J12, puis nous écrivons les en-têtes :

Janvier Février Mars Avril Mai Juin

Ensuite, en J13, nous saisissons :

  =INDEX(Ventes[[Janvier]:[Juin]];EQUIV(K6&K7;Ventes[Région]&Ventes[Produit];0);0)
  

Le point important ici, c’est le dernier argument.

Nous mettons 0 à la place du numéro de colonne.

Cela signifie : « renvoie-moi toutes les colonnes de la ligne trouvée ».

Excel va donc afficher automatiquement les six mois sur la ligne.

Si rien ne s’affiche, vérifions que les cellules à droite de J13 sont vides.

Cette technique est très utile pour construire rapidement un graphique dynamique.

Par exemple, nous pouvons afficher les ventes d’un mois choisi, puis créer un graphique à partir de cette colonne déversée.

Pour additionner directement le mois sélectionné, nous pouvons intégrer INDEX dans SOMME :

  =SOMME(INDEX(Ventes[[Janvier]:[Juin]];0;EQUIV(K8;Ventes[[#En-têtes];[Janvier]:[Juin]];0)))
  

Ici, INDEX renvoie toute la colonne du mois, puis SOMME additionne cette colonne.

Nous obtenons donc le total mensuel sans avoir besoin de créer une formule différente pour chaque mois.

C’est beaucoup plus propre qu’une longue formule avec plusieurs SI imbriqués.

4. Créer une plage dynamique entre deux mois

Nous allons maintenant utiliser INDEX d’une manière un peu plus avancée.

L’idée est de calculer automatiquement le total des ventes entre deux mois choisis.

Par exemple, nous voulons additionner toutes les ventes de « Février » à « Mai », sans avoir à sélectionner manuellement les colonnes dans notre tableau.

Nous préparons donc une nouvelle zone de paramètres.

Les mois de début et de fin sont saisis en K16 et K17 et nous souhaitons obtenir le total de la période en K18.

Ici, la formule est un peu plus complexe à élaborer, car nous ne voulons pas seulement récupérer une valeur dans le tableau.

Nous voulons construire automatiquement une plage complète, qui commence au premier mois choisi et qui se termine au second mois choisi.

Pour gagner du temps, nous allons donc utiliser mon outil « IA Excelformation.fr ».

Cet outil permet notamment de demander une formule Excel en français, directement depuis Excel, puis de récupérer une formule prête à l’emploi.

Pour cela, nous commençons par sélectionner la plage qui englobe les deux tableaux.

Ensuite, nous nous rendons dans l’onglet « IA Excelformation.fr », puis nous cliquons sur le bouton « Créer une formule » :

Dans la fenêtre qui s’affiche, nous allons décrire notre besoin le plus clairement possible.

Et c’est important : plus nous donnons de contexte à l’IA, plus la formule proposée a de chances d’être directement exploitable.

Nous pouvons par exemple saisir la demande suivante :

« Dans le tableau structuré nommé "Ventes" (A6:H18), additionne toutes les valeurs comprises entre le mois indiqué en K16 et le mois indiqué en K17. La formule doit utiliser INDEX pour créer une plage dynamique entre les deux mois et les noms dynamiques du tableau structuré "Ventes". Si le mois n’est pas trouvé, affiche "Vérifiez les mois". »

L’idée ici est de fournir un maximum de contexte pour que l’IA comprenne exactement la structure de notre fichier.

Nous lui indiquons le nom du tableau, les cellules de paramètres, les colonnes concernées, le type de formule attendu, et même le message à afficher en cas d’erreur.

Nous évitons ainsi une réponse trop vague, ou une formule qui utiliserait des plages classiques comme C7:H18 au lieu de nos références de tableau structuré.

Après validation, l’outil nous propose une formule que nous pouvons insérer dans notre feuille.

Voici la formule obtenue :

=SIERREUR(SOMME(INDEX(Ventes[[Janvier]:[Juin]];1;EQUIV(K16;Ventes[[#En-têtes];[Janvier]:[Juin]];0)):INDEX(Ventes[[Janvier]:[Juin]];LIGNES(Ventes[[Janvier]:[Juin]]);EQUIV(K17;Ventes[[#En-têtes];[Janvier]:[Juin]];0)));"Vérifiez  les mois")

Nous allons maintenant décortiquer cette formule, parce que même si l’IA nous aide à la générer, il est essentiel de comprendre ce qu’elle fait.

La fonction SIERREUR entoure toute la formule.

Son rôle est simple : si Excel rencontre une erreur, par exemple parce que le mois saisi en K16 ou K17 n’existe pas dans les en-têtes du tableau, alors la formule affiche « Vérifiez les mois ».

Sans SIERREUR, nous aurions probablement une erreur du type #N/A ou #VALEUR!, ce qui serait beaucoup moins propre pour l’utilisateur.

Ensuite, nous retrouvons la fonction SOMME.

C’est elle qui va additionner toutes les cellules de la plage dynamique.

Mais la partie la plus intéressante se trouve à l’intérieur de cette fonction SOMME.

Nous avons un premier INDEX avant le symbole « : », puis un second INDEX après le symbole « : ».

Dans Excel, le symbole « : » permet de créer une plage.

Par exemple, C7:F18 signifie que nous prenons toutes les cellules situées entre C7 et F18.

Ici, au lieu d’écrire manuellement C7:F18, nous demandons à INDEX de trouver automatiquement la cellule de départ et la cellule d’arrivée.

Le premier INDEX renvoie la première cellule du mois de départ :

INDEX(Ventes[[Janvier]:[Juin]];1;EQUIV(K16;Ventes[[#En-têtes];[Janvier]:[Juin]];0))

Dans cette partie, le chiffre 1 signifie que nous voulons commencer à la première ligne de la zone de ventes.

La fonction EQUIV cherche le mois indiqué en K16 dans les en-têtes Janvier à Juin.

Si K16 contient « Février », EQUIV renvoie la position de la colonne Février.

Le premier INDEX renvoie donc la première cellule de la colonne Février.

Ensuite, le second INDEX renvoie la dernière cellule du mois de fin :

INDEX(Ventes[[Janvier]:[Juin]];LIGNES(Ventes[[Janvier]:[Juin]]);EQUIV(K17;Ventes[[#En-têtes];[Janvier]:[Juin]];0))

ette fois, au lieu d’utiliser directement un numéro de ligne fixe, nous utilisons la fonction LIGNES.

=LIGNES(Ventes[[Janvier]:[Juin]])

Cette fonction compte automatiquement le nombre de lignes de notre tableau.

C’est très pratique, parce que si nous ajoutons une nouvelle région, un nouveau produit ou une nouvelle ligne de vente dans le tableau structuré, la formule continue de fonctionner sans modification.

Le second INDEX renvoie donc la dernière cellule de la colonne du mois de fin.

Enfin, le symbole « : » entre les deux INDEX construit la plage complète.

Si K16 contient « Février » et K17 contient « Mai », Excel crée automatiquement une plage allant de la première cellule de Février jusqu’à la dernière cellule de Mai.

Puis SOMME additionne toutes les valeurs de cette plage.

Nous obtenons donc un total dynamique, piloté uniquement par les deux cellules K16 et K17.

C’est exactement le genre de formule qui devient très pratique dans un tableau de bord, parce que nous pouvons changer la période d’analyse sans toucher à la formule.

Nous venons de voir que la fonction INDEX est beaucoup plus souple qu’une simple fonction de recherche.

Nous l’avons utilisée pour récupérer une valeur précise, afficher une ligne complète, extraire une colonne entière, construire une plage dynamique entre deux mois, additionner deux mois séparés et choisir entre deux tableaux.

Le point à retenir, c’est que INDEX devient vraiment intéressant lorsqu’elle est combinée avec EQUIV, SOMME, SIERREUR et les tableaux structurés.

Avec ces quelques techniques, nous pouvons construire des fichiers Excel beaucoup plus dynamiques, plus propres et plus faciles à maintenir.