DECALER, EQUIV, INDEX et INDIRECT dans Excel
Retrouver la position d’une valeur, extraire le contenu d’une cellule à l’intersection d’une ligne et d’une colonne, construire une plage qui s’adapte toute seule ou changer de feuille à partir d’une liste déroulante : ces quatre fonctions sont la boîte à outils des tableaux de bord Excel.
EQUIV : trouver la position d’une valeur
=EQUIV(valeur_cherchée;plage;type) ne renvoie pas la valeur mais son rang dans une ligne ou une colonne : 1 pour la première cellule, 2 pour la deuxième, etc.
| Type | Recherche | Condition sur la plage |
|---|---|---|
0 | Valeur exacte (jokers * et ? acceptés pour le texte) | Aucune, ordre quelconque |
1 ou omis | Plus grande valeur inférieure ou égale | Triée par ordre croissant |
-1 | Plus petite valeur supérieure ou égale | Triée par ordre décroissant |
;0)."Power*" trouve la première cellule qui commence par Power.INDEX : lire la cellule à une intersection
=INDEX(plage;n°ligne;n°colonne) renvoie le contenu de la cellule située à la ligne et à la colonne indiquées, en comptant à partir du coin supérieur gauche de la plage et non de la feuille.
| Formule (feuille Nord) | Lit | Résultat |
|---|---|---|
=INDEX(B2:G7;1;3) | 1re ligne, 3e colonne de la plage : D2 | 3 360 € |
=INDEX(A2:A7;4) | 4e cellule d’une colonne | PowerPoint |
=INDEX(B2:G7;0;3) | Colonne entière n° 3 (mars) | D2:D7, à additionner : 11 290 € |
=SOMME(INDEX(B2:G7;0;3)) additionne mars.Le duo INDEX + EQUIV
EQUIV calcule les numéros de ligne et de colonne, INDEX lit la cellule : la formule devient une recherche à double entrée qui suit les listes déroulantes.
| Besoin | Formule |
|---|---|
| Recherche à double entrée | =INDEX(B2:G7;EQUIV(K1;A2:A7;0);EQUIV(K2;B1:G1;0)) |
| Recherche vers la gauche (impossible avec RECHERCHEV) | =INDEX(A2:A7;EQUIV(MAX(G2:G7);G2:G7;0)) : formation au meilleur CA de juin → Excel |
| Barème par tranche (EQUIV type 1) | =INDEX(J2:J5;EQUIV(8;I2:I5;1)) → remise de 5 % pour 8 stagiaires |
=RECHERCHEX(K1;A2:A7;G2:G7) fait la même chose en une fonction, mais INDEX + EQUIV fonctionne dans toutes les versions et reste demandé au TOSA.DECALER : une plage qui se déplace et se redimensionne
=DECALER(réf;lignes;colonnes;[hauteur];[largeur]) part de réf, se décale du nombre de lignes (vers le bas si positif) et de colonnes (vers la droite), puis renvoie une plage de la hauteur et de la largeur demandées. On l’utilise à l’intérieur d’une autre fonction : SOMME, MOYENNE, NBVAL…
=DECALER(Nord!$A$2;0;0;NBVAL(Nord!$A:$A)-1;1). La liste déroulante qui utilise =Formations s’agrandit dès qu’on ajoute une ligne.INDIRECT : transformer un texte en référence
=INDIRECT(texte_réf) lit une adresse écrite sous forme de texte ("C3", "'Nord'!C3", un nom de plage…) et renvoie la cellule correspondante. Le texte peut être construit avec & à partir d’une liste déroulante : c’est ainsi qu’on change de feuille sans modifier la formule.
| Usage | Formule |
|---|---|
| Total de la feuille choisie en B1 | =SOMME(INDIRECT("'"&B1&"'!B2:G7")) |
| Recherche double entrée sur la feuille choisie | =INDEX(INDIRECT("'"&B1&"'!B2:G7");EQUIV(B2;A2:A7;0);EQUIV(B3;B1:G1;0)) |
| Listes déroulantes en cascade | Plages nommées Bureautique, PAO… puis validation de données : =INDIRECT(A2) |
'Power BI'!C3. Ajoutez-les systématiquement dans la concaténation.Exercice de synthèse : tableau de bord régional
Télécharger le fichier d’exercice (Énoncé et Corrigé)
- Feuille Tableau de bord : B1 = Nord, B2 = PowerPoint, B3 = avr. Calculez avec EQUIV la position de la formation (4) et du mois (4).
- INDEX + EQUIV : CA de PowerPoint en avril dans la feuille Nord → 960 €.
- INDIRECT : même calcul sur la feuille choisie en B1. Passez B1 à Sud → 1 360 €.
- DECALER : cumul de janvier au mois choisi (Nord, PowerPoint, avr. → 6 000 €) et total des 3 derniers mois (4 140 €).
- Barème : remise pour 8 stagiaires avec INDEX + EQUIV type 1 → 5 %. Total de la région avec INDIRECT (Nord : 61 290 €).
- Créez le nom dynamique Formations avec DECALER et utilisez-le comme source de la liste déroulante de B2.