Fonction INDEX Excel - Exemples de formules + Vidéo GRATUITE

Fonction Excel INDEX (Exemples + Vidéo)

Quand utiliser la fonction Excel INDEX

La fonction Excel INDEX peut être utilisée lorsque vous souhaitez extraire la valeur d'une donnée tabulaire et que vous avez le numéro de ligne et le numéro de colonne du point de données. Par exemple, dans l'exemple ci-dessous, vous pouvez utiliser la fonction INDEX pour obtenir les notes de « Tom » en physique lorsque vous connaissez le numéro de ligne et le numéro de colonne dans l'ensemble de données.

Ce que ça renvoie

Il renvoie la valeur d'une table pour le numéro de ligne et le numéro de colonne spécifiés.

Syntaxe

=INDEX (tableau, num_ligne, [num_col])
=INDEX (tableau, row_num, [col_num], [area_num])

La fonction INDEX a 2 syntaxes. Le premier est utilisé dans la plupart des cas, cependant, dans le cas de recherches à trois voies, le second est utilisé (couvert dans l'exemple 5).

Arguments d'entrée

• déployer - une plage de cellules ou une constante matricielle.
• num_ligne - le numéro de ligne à partir duquel la valeur doit être récupérée.
• [num_col] - le numéro de la colonne à partir de laquelle la valeur doit être récupérée. Bien qu'il s'agisse d'un argument facultatif, mais si row_num n'est pas fourni, il doit l'être.
• [zone_num] - (Facultatif) Si l'argument du tableau est composé de plusieurs plages, ce nombre serait utilisé pour sélectionner la référence parmi toutes les plages.

Notes supplémentaires (trucs ennuyeux… mais importants à savoir)

• Si le numéro de ligne ou le numéro de colonne est 0, il renvoie respectivement les valeurs de la ligne ou de la colonne entière.
• Si la fonction INDEX est utilisée devant une référence de cellule (comme A1:), elle renvoie une référence de cellule au lieu d'une valeur (voir les exemples ci-dessous).
• Le plus largement utilisé avec la fonction MATCH.
• Contrairement à RECHERCHEV, la fonction INDEX peut renvoyer une valeur à partir de la gauche de la valeur de recherche.
• La fonction INDEX a deux formes - la forme tableau et la forme référence
  • La « forme de tableau » est l'endroit où vous récupérez une valeur basée sur le numéro de ligne et de colonne d'une table donnée.
  • Le « formulaire de référence » est l'endroit où il y a plusieurs tables, et vous utilisez l'argument area_num pour sélectionner la table, puis extraire une valeur à l'intérieur de celle-ci en utilisant le numéro de ligne et de colonne (voir l'exemple en direct ci-dessous).

Fonction Excel INDEX - Exemples

Voici six exemples d'utilisation de la fonction Excel INDEX.

Exemple 1 - Trouver les marques de Tom en physique (une recherche bidirectionnelle)

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :

Pour trouver les notes de Tom en physique, utilisez la formule ci-dessous :

=INDICE($B$3:$E$10,3,2)

Cette formule INDEX spécifie le tableau comme $B$3:$E$10 qui a les marques pour tous les sujets. Ensuite, il utilise le numéro de ligne (3) et le numéro de colonne (2) pour récupérer les notes de Tom en physique.

Exemple 2 - Rendre la valeur LOOKUP dynamique à l'aide de la fonction MATCH

Il n'est pas toujours possible de spécifier manuellement le numéro de ligne et le numéro de colonne. Vous pouvez avoir un énorme ensemble de données, ou vous pouvez vouloir le rendre dynamique afin qu'il identifie automatiquement le nom et/ou le sujet spécifié dans les cellules et donne le résultat correct.

Quelque chose comme indiqué ci-dessous :

Cela peut être fait en utilisant une combinaison de la fonction INDEX et MATCH.

Voici la formule qui rendra les valeurs de recherche dynamiques :

=INDICE($B$3:$E$10,MATCH($G$5,$A$3:$A$10,0),MATCH($H$4,$B$2:$E$2,0))

Dans la formule ci-dessus, au lieu de coder en dur le numéro de ligne et le numéro de colonne, la fonction MATCH est utilisée pour le rendre dynamique.

• Le numéro de ligne dynamique est donné par la partie suivante de la formule - MATCH($G$5,$A$3:$A$10,0). Il scanne le nom des étudiants et identifie la valeur de recherche (5 $ G$ dans ce cas). Il renvoie ensuite le numéro de ligne de la valeur de recherche dans l'ensemble de données. Par exemple, si la valeur de recherche est Matt, elle renverra 1, si c'est Bob, elle renverra 2 et ainsi de suite.
• Le numéro de colonne dynamique est donné par la partie suivante de la formule - MATCH($H$4,$B$2:$E$2,0). Il scanne les noms des sujets et identifie la valeur de recherche ($H$4 dans ce cas). Il renvoie ensuite le numéro de colonne de la valeur de recherche dans l'ensemble de données. Par exemple, si la valeur de recherche est Math, elle renverra 1, si c'est Physique, elle renverra 2 et ainsi de suite.

Exemple 3 - Utilisation de listes déroulantes comme valeurs de recherche

Dans l'exemple ci-dessus, nous devons saisir manuellement les données. Cela peut prendre du temps et être source d'erreurs, surtout si vous avez une longue liste de valeurs de recherche.

Une bonne idée dans de tels cas est de créer une liste déroulante des valeurs de recherche (dans ce cas, il peut s'agir des noms et des matières des étudiants), puis de simplement choisir dans la liste. En fonction de la sélection, la formule mettrait automatiquement à jour le résultat.

Quelque chose comme indiqué ci-dessous :

Cela fait un bon composant de tableau de bord car vous pouvez avoir un énorme ensemble de données avec des centaines d'étudiants en arrière-plan, mais l'utilisateur final (disons un enseignant) peut rapidement obtenir les notes d'un étudiant dans un sujet en effectuant simplement les sélections à partir de la descente.

Comment faire ceci :

La formule utilisée dans ce cas est la même que celle utilisée dans l'exemple 2.

=INDICE($B$3:$E$10,MATCH($G$5,$A$3:$A$10,0),MATCH($H$4,$B$2:$E$2,0))

Les valeurs de recherche ont été converties en listes déroulantes.

Voici les étapes pour créer la liste déroulante Excel :

• Sélectionnez la cellule dans laquelle vous voulez la liste déroulante. Dans cet exemple, en G4, nous voulons les noms des étudiants.
• Accédez à Données -> Outils de données -> Validation des données.
• Dans la boîte de dialogue de validation des données, dans l'onglet Paramètres, sélectionnez Liste dans la liste déroulante Autoriser.
• Dans la source, sélectionnez $A$3 :$A$10
• Cliquez sur OK.

Vous aurez maintenant la liste déroulante dans la cellule G5. De même, vous pouvez en créer un dans H4 pour les sujets.

Exemple 4 - Valeurs de retour d'une ligne/colonne entière

Dans les exemples ci-dessus, nous avons utilisé la fonction Excel INDEX pour effectuer une recherche bidirectionnelle et obtenir une valeur unique.

Maintenant, que faire si vous voulez obtenir toutes les notes d'un étudiant. Cela peut vous permettre de trouver le score maximum/minimum de cet élève, ou le total des notes obtenues dans toutes les matières.

En anglais simple, vous voulez d'abord obtenir la ligne entière des scores d'un élève (disons Bob), puis dans ces valeurs, identifier le score le plus élevé ou le total de tous les scores.

Voici l'astuce.

Dans la fonction Excel INDEX, lorsque vous entrez le numéro de colonne comme 0, il renverra les valeurs de cette ligne entière.

La formule pour cela serait donc :

=INDICE($B$3:$E$10,MATCH($G$5,$A$3:$A$10,0),0)

Maintenant cette formule. si utilisé tel quel, renverrait la #VALUE ! Erreur. Pendant qu'il affiche l'erreur, dans le backend, il renvoie un tableau qui a tous les scores pour Tom - {57,77,91,91}.

Si vous sélectionnez la formule en mode édition et appuyez sur F9, vous pourrez voir le tableau qu'elle renvoie (comme indiqué ci-dessous) :

De même, en fonction de la valeur de recherche, lorsque le numéro de colonne est spécifié comme 0 (ou est laissé vide), il renvoie toutes les valeurs de la ligne pour la valeur de recherche

Maintenant, pour calculer le score total obtenu par Tom, nous pouvons simplement utiliser la formule ci-dessus dans la fonction SOMME.

=SOMME(INDICE($B$3:$E$10,MATCH($G$5,$A$3:$A$10,0),0))

Sur des lignes similaires, pour calculer le score le plus élevé, nous pouvons utiliser MAX/LARGE et pour calculer le minimum, nous pouvons utiliser MIN/SMALL.

Exemple 5 - Recherche à trois voies utilisant INDEX/MATCH

La fonction Excel INDEX est conçue pour gérer les recherches à trois voies.

Qu'est-ce qu'une recherche à trois ?

Dans les exemples ci-dessus, nous avons utilisé un tableau avec les scores des étudiants dans différentes matières. Ceci est un exemple de recherche bidirectionnelle car nous utilisons deux variables pour récupérer le score (le nom de l'étudiant et le sujet).

Maintenant, supposons qu'en un an, un étudiant ait trois niveaux d'examens différents, un test unitaire, un examen intermédiaire et un examen final (c'est ce que j'avais quand j'étais étudiant).

Une recherche à trois voies serait la possibilité d'obtenir les notes d'un étudiant pour un sujet spécifié à partir du niveau d'examen spécifié. Cela en ferait une recherche à trois voies car il y a trois variables (le nom de l'étudiant, le nom du sujet et le niveau d'examen).

Voici un exemple de recherche à trois voies :

Dans l'exemple ci-dessus, en plus de sélectionner le nom de l'étudiant et le nom de la matière, vous pouvez également sélectionner le niveau de l'examen. En fonction du niveau de l'examen, il renvoie la valeur correspondante de l'une des trois tables.

Voici la formule utilisée dans la cellule H4 :

=INDICE(($B$3:$E$7,$B$11:$E$15,$B$19:$E$23),MATCH($G$4,$A$3:$A$7,0),MATCH($H $3,$B$2:$E$2,0),IF($H$2="Test unitaire",1,IF($H$2="Moyen terme",2,3)))

Décomposons cette formule pour comprendre comment elle fonctionne.

Cette formule prend quatre arguments. INDEX est l'une de ces fonctions dans Excel qui a plus d'une syntaxe.

=INDEX (tableau, num_ligne, [num_col])
=INDEX (tableau, row_num, [col_num], [area_num])

Jusqu'à présent, dans tous les exemples ci-dessus, nous avons utilisé la première syntaxe, mais pour effectuer une recherche à trois voies, nous devons utiliser la deuxième syntaxe.

Voyons maintenant chaque partie de la formule basée sur la deuxième syntaxe.

• array - ($B$3:$E$7,$B$11:$E$15,$B$19:$E$23): Au lieu d'utiliser un seul tableau, dans ce cas, nous avons utilisé trois tableaux entre parenthèses.
• row_num - MATCH($G$4,$A$3:$A$7,0) : la fonction MATCH est utilisée pour trouver la position du nom de l'étudiant dans la cellule $G$4 dans la liste des noms de l'étudiant.
• col_num - MATCH($H$3,$B$2:$E$2,0) : la fonction MATCH est utilisée pour trouver la position du nom du sujet dans la cellule $H$3 dans la liste des noms de sujet.
• [area_num] - IF($H$2=”Unit Test”,1,IF($H$2=”Midterm”,2,3)) : La valeur du numéro de zone indique à la fonction INDEX quel tableau sélectionner. Dans cet exemple, nous avons trois tableaux dans le premier argument. Si vous sélectionnez Test unitaire dans la liste déroulante, la fonction SI renvoie 1 et les fonctions INDEX sélectionnent le 1er tableau parmi les trois tableaux (soit $B$3:$E$7).

Exemple 6 - Création d'une référence à l'aide de la fonction INDEX (plages nommées dynamiques)

Il s'agit d'une utilisation sauvage de la fonction Excel INDEX.

Prenons un exemple simple.

J'ai une liste de noms comme indiqué ci-dessous:

Maintenant, je peux utiliser une simple fonction INDEX pour obtenir le nom de famille sur la liste.

Voici la formule :

=INDICE($A$2:$A$9,COUNTA($A$2:$A$9))

Cette fonction compte simplement le nombre de cellules qui ne sont pas vides et renvoie le dernier élément de cette liste (elle ne fonctionne que lorsqu'il n'y a pas de blancs dans la liste).

Maintenant, qu'est-ce qui vient de la magie.

Si vous placez la formule devant une référence de cellule, la formule renverra une référence de cellule de la valeur correspondante (au lieu de la valeur elle-même).

=A2:INDICE($A$2:$A$9,COUNTA($A$2:$A$9))

Vous vous attendriez à ce que la formule ci-dessus renvoie =A2:"Josh" (où Josh est la dernière valeur de la liste). Cependant, il renvoie =A2:A9 et vous obtenez donc un tableau de noms comme indiqué ci-dessous :

Un exemple pratique où cette technique peut être utile est la création de plages nommées dynamiques.

C'est tout dans ce tutoriel. J'ai essayé de couvrir les principaux exemples d'utilisation de la fonction Excel INDEX. Si vous souhaitez voir plus d'exemples ajoutés à cette liste, faites-le moi savoir dans la section commentaires.

Remarque: j'ai fait de mon mieux pour relire ce tutoriel, mais si vous trouvez des erreurs ou des fautes d'orthographe, veuillez me le faire savoir

Fonction Excel INDEX - Tutoriel vidéo

• Fonction RECHERCHEV Excel.
• Fonction RECHERCHEH Excel.
• Excel Fonction INDIRECT.
• Fonction MATCH Excel.
• Fonction de décalage Excel.

Vous aimerez peut-être aussi les didacticiels Excel suivants :

• RECHERCHEV vs. INDEX/MATCH
• Correspondance d'index Excel
• Valeurs de recherche et de retour dans une ligne/colonne entière.