Des formes et syntaxes peu connues de la fonction INDEX

Cette fonction aux nombreuses utilisations a certaines syntaxes et fonctionnalités peu connues que je vous propose de découvrir.

La forme classique dite Matricielle

Rôle

Récupérer l'élément d'une matrice en fonction de ses coordonnées Ligne/Colonne.

Syntaxe

= INDEX( Matrice ; [No_lig] [; No_col] )

• Matrice : Matrice ou plage.
• No_lig : Numéro de la ligne (scalaire ou matrice, voir Excel et les matrices pour les versions récentes supportant les tables dynamiques), facultatif si No_col, valeur par défaut 0 (voir plus bas).
• No_col : Numéro de la colonne (scalaire ou matrice ...), facultatif si No_lig, valeur par défaut 0 (voir plus bas).

Exemple 1

Utilisation classique

Remplacer les fonctions RECHERCHEV et RECHERCHEH pour pourvoir rechercher aussi bien à gauche ou au-dessus de la colonne/ligne de recherche (chose impossible avec les fonctions RECHERCHEV et RECHERCHEH).

Exemple 2

Rechercher à partir du Nom (Colonne2), le matricule (colonne1, à gauche de Nom).

On utilise en association la fonction EQUIV qui est l'inverse de INDEX. EQUIV renvoie la position d'une valeur dans une matrice 1D (1 colonne ou 1 ligne).

Remarque : La nouvelle fonction RECHERCHEX est faite pour ça !

La forme dite Vectorielle

Rôle

Forme moins courante. C'est la même chose mais avec la possibilité de choisir dynamiquement la matrice.

Syntaxe

= INDEX( Référence; No_lig ; [ No_col] [; No_zone] )

• Référence : Plages séparées par un point-virgule, le tout entre parenthèse. Pas de matrice. Plages sur la même feuille.
• No_lig : Numéro de la ligne (scalaire ou matrice ...), facultatif si No_col, valeur par défaut 0 (voir plus bas).
• No_col : Numéro de la colonne (scalaire ou matrice ...), facultatif si No_lig, valeur par défaut 0 (voir plus bas).
• No_zone : N° de la plage à utiliser (1 par défaut, scalaire ou matrice).

Exemple 3

Rechercher une valeur correspondant à un mois dans le tableau correspondant à une année précise.

Solution fonctionnant entre feuilles : (Feuilles s'appelant 2022 et 2023)

On utilise la forme Matricielle.

= INDEX( INDIRECT(B4 & "!C4:C6") ; C4 )
= INDEX( CHOISIR(B4-2021;'2022'!$C$4:$C$6;'2023'!$C$4:$C$6);C4 )

Le cas particulier avec No_Lig ou No_col=0

Si No_Lig ou No_col = 0 alors INDEX retourne la référence pour la ligne entière ou la colonne entière.

Exemple 4

Somme des valeurs du Mois

=SOMME( INDEX(C3:E5;0;G3) )

Cas particulier du cas particulier !

• Dans ce cas (No_Lig ou No_col=0) on ne peut pas utiliser de matrice pour l'autre arguments No_lig et No_col (sinon l'argument à 0 sera remplacé par 1).

Exemple : On veut additionner par ligne un tableau/matrice de 2 x 2

On utilise ici la constante matricielle { 1 . 2 ; 11 . 22 } (Voir Excel et les matrices (pas celles du cours de mathématiques)

=BYROW(SEQUENCE(2) ; LAMBDA(i ; SOMME(INDEX({1.2;11.22} ; i ; 0)))) => {1;11} et non {3;33}

Ici SEQUENCE renvoie successivement les matrices {1} et {2} ce qui génère INDEX( {1.2;11.22} ; {1} ; 0 ) puis INDEX( {1.2;11.22} ; {2} ; 0 ) correspondant à l'exception citée plus haut.

Solutions :

• Intervenir au niveau de SEQUENCE en la plaçant dans une cellule (ex : A2) : = BYROW( A2# ; LAMBDA( i ; SOMME(INDEX( {1.2;11.22} ; i ; 0 )))).
• Intervenir au niveau de la matrice en la convertissant :
  • Utiliser l'opérateur d'intersection : = BYROW( SEQUENCE(2) ; LAMBDA( i ; SOMME(INDEX( {1.2;11.22} ; @i )))).
  • Utiliser une fonction : = BYROW( SEQUENCE(2) ; LAMBDA( i ; SOMME(INDEX( {1.2;11.22} ; MAX(i) ; 0)))).

Utilisation du résultat d'INDEX comme une référence de cellule !

La fonction renvoie en réalité une référence à une cellule et non la valeur !

• Dans l'exemple 1, la fonction INDEX renvoie F5 qui a pour valeur H.
• Dans l'exemple 4, la fonction INDEX renvoie la plage D2 : D5.

Exemple 5

Faire la somme des valeurs entre telle et telle ligne.

=SOMME( INDEX(B2:B8;E2) : INDEX(B2:B8;E3) )
=SOMME( INDEX(B2:B8;E2) : INDEX(B2:B8;E3) )

Remarque :

• La formule =SOMME(DECALER(B2:B8;E2-1;0;E3-E2+1)) renvoie aussi 232 mais est moins intéressante car DECALER est une fonction volatile, c'est à dire qu'elle est recalculée à chaque mise à jour ce qui peut être pénalisant en terme de performance.

Merci pour votre attention bienveillante.