Le 21 septembre dernier, je vous ai présenté un webinaire sur les secrets bien gardés de la fonction INDEX dans Excel. À mon avis, la fonction INDEX représente un réel couteau suisse dans Excel, capable de vous venir en aide dans pratiquement toutes les situations. Cet article vise donc à vous présenter un sommaire de ce qui a été démontré lors de ce webinaire. Pour ceux qui n’ont pas pu y assister, vous pouvez écouter l’enregistrement en devenant membre VIP du CFO masqué.
1. INDEX sur une plage de données (recherche exacte)
L’usage le plus fréquent de la fonction INDEX est sans contredit celui basé sur une plage de données unique. Dans ce contexte, la fonction INDEX, sert à identifier la valeur qui se trouve sur une ligne spécifique et dans une colonne spécifique, dans cette plage de données. Autrement dit, Excel retourne la valeur à la croisée de cette ligne et de cette colonne. Pour identifier la ligne et la colonne de façon dynamique, on utilise la fonction EQUIV. Ce faisant, on obtient une fonction comme la suivante :
2. INDEX sur une plage de données (recherche approximative)
Dans l’exemple précédent, on cherche le coût d’impression d’un magazine selon un nombre de pages et un nombre de copies qui se trouvent dans les étiquettes de lignes et de colonnes de la matrice. Mais on peut aussi utiliser la fonction INDEX avec un nombre de pages et un nombre de copies qui ne font pas partie des étiquettes de lignes et des étiquettes de colonnes de la matrice. Par exemple, quel serait le coût d’un magazine de 57 pages et de 17,000 copies ? Pour cela, il faut utiliser des recherches approximatives comme 3ième paramètre de la fonction EQUIV(). Le tout est expliqué en détails dans l’article suivant : Excel : Vous pensez tout connaître de la fonction INDEX ?
3. INDEX sur plusieurs plages de données
Il est également possible d’utiliser la fonction INDEX sur plusieurs plages de données, en y ajoutant un 4ième paramètre. Là aussi, on va rechercher une valeur à la croisée d’une ligne et d’une colonne précise mais cette fois-ci, la ligne et la colonne appartiennent à une plage de données, parmi un ensemble de plages disponibles. Il faut donc également indiquer dans laquelle de ces plages effectuer la recherche.
Dans une telle situation, le 1er paramètre de la fonction spécifiera l’ensemble des plages disponibles et le 4ième paramètre indiquera dans laquelle de ces plages faire la recherche. Plus spécifiquement, le 4ième paramètre précisera s’il faut rechercher la valeur dans la première, deuxième ou xième plage. Ce paramètre peut être spécifié dans une cellule, comme dans l’exemple ci-dessous, mais il pourrait également être identifié par le biais d’une 3ième fonction EQUIV. Ceci a été démontré lors du webinaire.
4. INDEX pour remplacer un RECHERCHEV ou un RECHERCHEH
Comme la fonction INDEX sert à identifier une valeur à la croisée d’une ligne et d’une colonne, il est aussi possible de l’utiliser en remplacement d’une fonction RECHERCHEV ou d’une fonction RECHERCHEH, ou même d’un RECHERCHEX, comme dans l’exemple ci-dessous, où l’on recherche le nom du meilleur vendeur. On cherche dans la colonne de vendeurs, celui qui se trouve sur la ligne du maximum de ventes identifié en F7 (max de la colonne C).
Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière. |
|---|
5. INDEX pour remplacer un DECALER
La fonction DECALER peut s’avérer utile, notamment lorsque vient le temps d’effectuer des calculs d’amortissement d’actifs dynamiques dans un modèle financier. Elle peut aussi s’avérer utile lors d’un calcul dynamique de valeurs cumulées année-à-date (YTD). Toutefois, comme la fonction DECALER fait partie des fonctions volatiles d’Excel, il est toujours préférable de la remplacer, lorsque possible, par une fonction non volatile. En effet, les fonctions volatiles forcent le recalcul du fichier à chaque intervention et peut entraîner de l’instabilité. Dans un tel contexte, la fonction INDEX peut venir en renfort, tel que démontré dans l’exemple suivant.
Ici, on utilise la fonction DECALER pour calculer les ventes cumulées de mai 2020 (ou de toute autre combinaison de mois et d’année).
Et ici, on obtient le même résultat avec deux fonctions INDEX. Écoutez notre webinaire en différé pour bien comprendre le fonctionnement de cette combinaison.
6. INDEX pour remplacer des SI IMBRIQUÉS
Les fonctions SI imbriquées sont très prisées par les usagers d’Excel et c’est malheureux car ces fonctions sont très lourdes et peuvent entraîner des problèmes de performance. Lorsque c’est possible, il est recommandé de les remplacer par d’autres fonctions. La plupart du temps, une fonction INDEX parviendra à remplacer une série de SI imbriqués et offrira beaucoup plus de performance. De plus, une telle formule sera beaucoup plus simple à écrire.
Par exemple, ici, on utilise une combinaison de SI imbriqués pour trouver le taux de bonus du vendeur. À noter qu’on aurait également pu utiliser une fonction SI.CONDITIONS.
Mais il serait également possible de remplacer le tout par une fonction INDEX, ce qui serait beaucoup plus performant. Écoutez notre webinaire en différé pour découvrir comment faire.
7. INDEX en mode matriciel dynamique
Depuis l’arrivée des fonctions matricielles dynamiques dans Excel, vous avez peut-être remarqué l’apparition de @ devant vos anciennes fonctions INDEX. C’est que les fonctions INDEX peuvent désormais être utilisées également en mode matriciel dynamique, comme dans l’exemple suivant.
Ici, comme on utilise 0 (ou vide) comme numéro de référence de ligne, Excel retourne toute la colonne A sous forme de matrice. Pour obtenir uniquement le total de cette formule, on peut simplement imbriquer le tout dans une fonction SOMME.
La fonction INDEX vs la fonction RECHERCHEX
Dans bien des situations (mais pas dans toutes), la fonction RECHERCHEX peut également être employée. C’est le cas dans un RECHECHEV inversé par exemple (illustré par la recherche du meilleur vendeur plus haut dans cet article). Qu’est-ce qui justifierait alors d’utiliser INDEX/EQUIV plutôt que RECHERCHEX? Et bien la première raison serait parce que vous n’avez pas accès à cette fonction, disponible uniquement pour les usagers de O365. Une autre raison serait parce que ce ne sont pas tous vos collègues qui ont accès à cette fonction et par conséquent, ce ne serait pas tous vos collègues qui pourraient consulter vos fichiers. De plus, il faut quand même savoir maîtriser la fonction INDEX puisque la très grande majorité des fichiers Excel qui seront échangés avec vous auront été conçus avant l’arrivée de la fonction RECHERCHEX. Il y a donc une probabilité assez grande que ces fichiers aient reposé sur des fonctions INDEX. Et finalement, les fonctions INDEX/EQUIV et RECHERCHEX sont interchangeables dans certaines situations (obtention du même résultat) mais pas dans toutes les situations. La fonction RECHERCHEX, bien que fort intéressante, n’est pas le couteau suisse que représente la fonction INDEX :).
Écoutez notre webinaire en différé
Pour écouter ce webinaire en différé devenez membre VIP du CFO masqué.Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Afin de découvrir des fonctions Excel similaires à la fonction INDEX, nous vous recommandons notre formation Excel – Modélisation financière (niveau 1).
Voici quelques commentaires d’apprenants ayant suivi cette formation :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
