Récupérer la nᵉ occurrence avec INDEX
Pour trouver la nᵉ occurrence d’une valeur dans une plage et récupérer les données correspondantes, utilisez une formule combinant les fonctions INDEX, LIGNE, PETITE.VALEUR et SI.
Comment récupérer les informations associées à la nᵉ occurrence d’une valeur avec INDEX ?
Pour obtenir les informations concernant la 3ᵉ occurrence d’Usain Bolt dans le tableau ci-dessus, vous pouvez combiner les fonctions LIGNE et SI afin de générer un tableau contenant les numéros de ligne de toutes les occurrences de la valeur « Usain Bolt ». Ensuite, utilisez la fonction PETITE.VALEUR pour extraire le numéro de ligne de la nᵉ occurrence souhaitée. Enfin, transmettez ce résultat à la fonction INDEX pour récupérer les informations correspondantes situées sur cette même ligne.
Syntaxe générique
=INDEX()return_range,SMALL(IF())lookup_array=lookup_value,ROW()lookup_array-ROW(INDEX())lookup_array,1,1))+1),n))
√ Remarque : Il s’agit d’une formule matricielle que vous devez valider en appuyant sur Ctrl+Maj+Entrée.
- plage_de_retour : La plage à partir de laquelle renvoyer les informations correspondant à la nᵉ occurrence. Il s’agit ici de l’année ou de la plage horaire.
- plage_recherche : La plage dans laquelle rechercher la position de la nᵉ occurrence. Il s’agit ici de la plage des gagnants.
- valeur_recherchée : La valeur que vous avez spécifiée pour rechercher sa nᵉ occurrence. Ici, il s’agit d’Usain Bolt.
- n : Indique la nᵉ occurrence. Pour trouver la 1ʳᵉ occurrence d’une valeur, définissez n sur 1 ; pour trouver la 2ᵉ occurrence, définissez n sur 2.
Pour obtenir les informations concernant la 3ᵉ occurrence d’Usain Bolt, veuillez copier ou saisir les formules ci-dessous dans les cellules G8 et G9, puis appuyer sur Ctrl+Maj+Entréepour obtenir les résultats :
Année (cellule G8)
=INDEX()B6:B12;PETITE.VALEUR(SI())C6:C12=G5;LIGNE()C6:C12-LIGNE(INDEX())C6:C12;1,1))+1);G6))
Temps (cellule G9)
=INDEX()D6:D12;PETITE.VALEUR(SI())C6:C12=G5;LIGNE()C6:C12-LIGNE(INDEX())C6:C12;1,1))+1);G6))
Explication de la formule
Nous utilisons ici la formule suivante comme exemple :
=INDEX(B6:B12,)SMALL()IF(C6:C12=G5,)ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6))
- LIGNE(C6:C12):La fonction LIGNE renvoie le numéro de ligne de chaque cellule de la plage C6:C12 sous forme de tableau comme suit : {6;7;8;9;10;11;12}.
- LIGNE(INDEX(C6:C12;1,1)):Les fonctions INDEX et LIGNE renvoient le numéro de la première ligne de la plage de recherche C6:C12 dans cette feuille Excel, soit 6.
- SI(C6:C12=G5;)LIGNE(C6:C12)-LIGNE(INDEX(C6:C12;1,1))+1)=SI(C6:C12=G5;){6;7;8;9;10;11;12}-6+1):La fonction SI vérifie si chaque valeur de la plage C6:C12 correspond à la valeur de la cellule G5, Usain Bolt. Si c’est le cas, elle renvoie « numéro de ligne correspondant – 6 + 1 » ; sinon, elle renvoie FAUX. Le résultat obtenu est donc un tableau comme suit : {FAUX;2;3;4;FAUX;FAUX;FAUX}.
- PETITE.VALEUR()SI(C6:C12=G5;)LIGNE(C6:C12)-LIGNE(INDEX(C6:C12;1,1))+1);G6)=PETITE.VALEUR(){FAUX;2;3;4;FAUX;FAUX;FAUX};G6):Cette formule renvoie la 3ᵉ plus petite valeur du tableau (correspondant à la valeur située en)G6), soit 4.
- INDEX(B6:B12;)PETITE.VALEUR()SI(C6:C12=G5;)LIGNE(C6:C12)-LIGNE(INDEX(C6:C12;1,1))+1);G6)) = INDEX(B6:B12;4)La fonction INDEX renvoie la 4e valeur de la plage des années B6:B12, soit Pékin 2008.
Fonctions associées
La fonction INDEX d’Excel renvoie la valeur affichée à une position donnée dans une plage ou un tableau.
La fonction SI est l’une des fonctions les plus simples et les plus utiles d’Excel. Elle effectue un test logique simple et renvoie une valeur si le résultat est VRAI, ou une autre valeur si le résultat est FAUX.
La fonction LIGNE d’Excel renvoie le numéro de ligne correspondant à une référence donnée.
Formules associées
Récupérer la nᵉ occurrence avec RECHERCHEV
Supposons que vous disposiez d’une liste de valeurs accompagnées de leurs informations associées. Pour récupérer les données liées à la nᵉ occurrence d’une valeur, utilisez une formule fondée sur la fonction RECHERCHEV.
Récupérer les informations associées aux n plus petites valeurs
Pour récupérer les données correspondant à la plus petite, à la 2ᵉ plus petite ou à la nᵉ plus petite valeur d’une liste, d’un tableau ou d’une ligne dans Excel, combinez les fonctions INDEX et PETITE.VALEUR.
Obtenir les informations correspondant à Valeur maximale
Pour récupérer les données correspondant à la Valeur maximale dans une liste, un tableau ou une ligne dans Excel, vous pouvez combiner les fonctions INDEX et Distinguer les formules avec la fonction MAX.
Obtenir les informations correspondant à Valeur minimale
Pour récupérer les données correspondant à la Valeur minimale dans un tableau, vous pouvez combiner les fonctions INDEX et Distinguer les formules avec la fonction MIN.
Les meilleurs outils de productivité Office
Kutools pour Excel – Vous permet de vous démarquer de la foule
Kutools pour Excel propose plus de 300 fonctionnalités,garantissant que ce dont vous avez besoin est toujours à portée de clic...
Office Tab – Active la lecture et l’édition par onglets dans Microsoft Office (y compris Excel)
- Passez d’un document à l’autre en une seconde, même avec des dizaines de documents ouverts !
- Réduisez de centaines vos clics de souris chaque jour et dites adieu au syndrome de la main de la souris.
- Augmentez votre productivité de 50 % en consultant et en modifiant simultanément plusieurs documents.
- Apporte à Office (y compris Excel) une efficacité comparable à celle des navigateurs tels que Chrome, Edge et Firefox.