Index Excel Fonction et fonction match

INDEX et MATCH Présentation

  • La fonction INDEX peut renvoyer un élément d'une position spécifique dans une liste.
  • La fonction MATCH peut retourner la position d'une valeur dans une liste.
  • Les INDEX / fonctions MATCH peuvent être utilisés ensemble, comme un outil flexible et puissant pour extraire des données d'une table.

Vidéo: INDEX Fonction

Dans cette vidéo, vous verrez comment utiliser la fonction INDEX pour:







  • Trouver le montant des ventes pour le mois sélectionné
  • Obtenir référence à la ligne spécifiée, la colonne, la zone
  • Créer une dynamique en fonction du nombre
  • colonne de tri de texte dans l'ordre alphabétique

Il existe d'autres exemples INDEX ci-dessous la vidéo. Pour des instructions écrites pour cette vidéo, consultez l'article Blog Contextures: 30 Fonctions Excel en 30 jours: 24 - INDEX

INDEX Fonction - Arguments

La fonction INDEX a trois arguments:

  1. tableau. Où se trouve la liste? Si vous utilisez une référence absolue ($ A $ 2: $ B $ 4), au lieu d'une référence relative (A2: B4), il sera plus facile de copier la formule à d'autres cellules. Ou, nommez la table de consultation, et reportez-vous par son nom.
  2. no_lig. Quelle ligne a la valeur souhaitée de retour? Dans cet exemple, l'élément de la troisième ligne sera retournée.
  3. [Num_colonne]. Quelle colonne a la valeur souhaitée de retour? Dans cet exemple, l'élément dans la deuxième colonne sera retourné.

Index Excel Fonction et fonction match

INDEX Fonction - Exemple

La fonction d'index peut renvoyer un article à partir d'une position spécifique dans une colonne spécifique dans une liste. Par exemple, quel est le 3ème élément dans la 2ème colonne dans la liste ci-dessous?

Index Excel Fonction et fonction match

  1. Sélectionnez la cellule dans laquelle vous voulez que le résultat
  2. Tapez un signe égal, le nom de la fonction INDEX, et une parenthèse d'ouverture:
    = INDEX (
  3. Sélectionnez les cellules qui contiennent la liste - cellules A2: B4 dans cet exemple
  4. Appuyez sur la touche F4 sur le clavier, pour modifier la référence à une référence absolue: $ A $ 2: $ B $ 4. Ensuite, si vous copiez la formule dans une autre cellule, il continuera de se référer à la liste des articles.
  5. Tapez une virgule, pour séparer les arguments
  6. Tapez le numéro de l'élément que vous souhaitez revenir - 3 dans cet exemple
  7. Tapez une virgule, pour séparer les arguments
  8. Tapez le numéro de la colonne que vous voulez revenir - 2 dans cet exemple
  9. Terminer avec une parenthèse de fermeture. La formule est terminée:
    = INDEX ($ A $ 2: B $ $ 4,3,2)
  10. Appuyez sur la touche Entrée pour voir le résultat.

Interactive INDEX Exemple

Dans le fichier Excel intégré indiqué ci-dessous, vous pouvez voir l'exemple de la fonction INDEX. Sur la feuille 1, les numéros de ligne et de colonne sont saisis dans la formule - codé en dur.

REMARQUE: Le fichier interactif peut ne pas être visible sur tous les appareils ou les navigateurs.

Sur la feuille 2, les numéros de ligne et de colonne sont dans les cellules de feuille de calcul. Modifier le numéro de ligne ou de colonne pour modifier le résultat de la formule.

MATCH Fonction - Arguments

La fonction MATCH a trois arguments:

  1. valeur_cherchée. Quelle valeur voulez-vous trouver dans la liste? Vous pouvez saisir la valeur, ou faire référence à une cellule qui contient la valeur.
  2. tableau_recherche. Où se trouve la liste?
  3. [Type de match]. Match_type peut être -1, 0 ou 1. Il indique à Excel comment faire correspondre la valeur_cherchée aux valeurs dans le tableau_recherche.
    1 - trouver la plus grande valeur inférieure ou égale à l'argument valeur_cherchée
    (La liste doit être dans l'ordre croissant)
    0 - trouver la première valeur exactement égale à l'argument valeur_cherchée. tableau_recherche
    (La liste peut être dans un ordre quelconque)
    -1 - trouver la plus petite valeur supérieure ou égale à l'argument valeur_cherchée.
    (La liste doit être dans l'ordre décroissant)
    Remarque: Si match_type est omis, il est supposé être 1.

MATCH Fonction - Exemple

La fonction MATCH peut trouver une valeur dans une liste, et retourner sa position. Par exemple, où est « veste » dans la liste ci-dessous?

Index Excel Fonction et fonction match

  1. Sélectionnez la cellule dans laquelle vous voulez que le résultat
  2. Tapez un signe égal, le nom de la fonction MATCH, et une parenthèse d'ouverture:
    = MATCH (
  3. Tapez la valeur à trouver, ou cliquez sur la cellule qui contient la valeur - A5 cellulaire dans cet exemple
  4. Tapez une virgule, pour séparer les arguments
  5. Sélectionnez la plage qui contient la liste - une plage nommée ItemList dans cet exemple
  6. Tapez une virgule, pour séparer les arguments
  7. Tapez le numéro pour le type de match que vous souhaitez utiliser - 0 a été utilisé dans cet exemple, car on exige une correspondance exacte.
  8. Terminer avec une parenthèse de fermeture. La formule est terminée:
    = MATCH (A5, ItemList, 0)
  9. Appuyez sur la touche Entrée pour voir le résultat.






INDEX / MATCH - Exemple 1

Un avantage des fonctions INDEX / MATCH est que la valeur de recherche peut être dans l'une des colonnes de la matrice, contrairement à la fonction VLOOKUP, dans lequel la valeur de recherche doit être dans la première colonne.

Dans cet exemple INDEX / MATCH, la fonction MATCH se trouve la position de « veste » dans la colonne B, et la fonction INDEX renvoie le code de la même position dans la colonne A.

Index Excel Fonction et fonction match

  1. Mettre en place la feuille de calcul comme indiqué à droite
  2. Entrez la formule suivante dans la cellule B6:
    INDEX = ($ A $ 2: $ A $ 4, MATCH (A6, $ B $ 2: $ B $ 4,0))
  3. Appuyez sur la touche Entrée pour voir le résultat.

La fonction MATCH, MATCH (A6, $ B $ 2: $ B $ 4,0). renvoie 2, qui est la position de « veste » dans la liste.

Ensuite, la fonction INDEX, INDEX ($ A $ 2: $ A 4,2 $). renvoie "JK002", qui constitue le deuxième point dans la plage $ A $ 2: $ A $ 4.

INDEX / MATCH - Exemple 2

La fonction MATCH peut être utilisée pour renvoyer des valeurs pour les arguments de no_lig et num_colonne dans la fonction INDEX.

La première fonction de match, match (B7, $ A $ 2: $ A 4,0 $). renvoie 3, qui est la position de « Pants » dans la liste des articles.

La seconde fonction de match, match (A7, B $ 1: $ D 1,0 $). retourne 2, qui est la position de « Med » dans la liste Taille.

Ensuite, la fonction INDEX, INDEX ($ B $ 2: $ D $ 4,3,2). retours « 30 », qui est le troisième élément de la seconde colonne dans la plage $ B $ 2: $ D $ 4.

INDEX / MATCH - Exemple 3

Pour l'exemple précédent encore plus flexible, vous pouvez utiliser la fonction INDEX dans la fonction MATCH, de rechercher des valeurs dans la première ligne ou colonne d'une table nommée. (Cet exemple INDEX / MATCH est d'un newsgroup par Peo Sjoblom)

Index Excel Fonction et fonction match

Mettre en place la feuille de calcul comme indiqué ci-dessus

  1. Les cellules vertes sont une plage nommée tableau). (Instructions sur la désignation d'une gamme)
  2. Entrez la formule suivante dans la cellule C7:
    = INDEX (tableau, MATCH (B7, INDEX (tableau ,, 1), 0), MATCH (A7, INDEX (tableau 1,), 0))
  3. Appuyez sur la touche Entrée pour voir le résultat.

La première fonction de match, match (B7, INDEX (tableau ,, 1), 0). cherche des « pantalon » dans la première colonne de la plage de la table (A1: A4), et les retours 4.

La seconde fonction de match, match (A7, INDEX (tableau 1,), 0). cherche des « Med » dans la première ligne de la plage de la table (A1: D1), et renvoie 3.

Ensuite, la fonction INDEX, INDEX (tableau, 4,3). retours « 30 », qui est dans la quatrième ligne de la troisième colonne dans la plage nommée tableau.

INDEX / MATCH - Exemple 4

Index Excel Fonction et fonction match

Vidéo: Recherche avec des critères multiples

Votre navigateur ne peut pas montrer ce cadre. Voici un lien vers la page

Dans ce INDEX / MATCH exemple, au lieu de colonnes rubriques de petites, Med et grande, la taille est stockée dans la colonne C.

Vous devez trouver le prix de la colonne D, lorsque veste est dans la colonne B, et Med est dans la colonne C.

  1. Mettre en place la feuille de calcul comme indiqué à droite
  2. Entrez la formule suivante dans la cellule C13:
    INDEX = ($ D $ 2: $ D $ 10, MATCH (1, (A13 = $ B $ 2: 10 $ B $) * (B13 = $ C $ 2: $ C $ 10), 0))
  3. Ceci est une formule de tableau, donc maintenez les touches Ctrl + Maj, puis appuyez sur la touche Entrée pour voir le résultat.

Les accolades seront automatiquement ajoutés à la formule, de sorte que le résultat final ressemblera à ceci (ne les tapez pas vous!):

Ensuite, créez une formule INDEX / MATCH similaire en D13, pour obtenir le code correct de la colonne A.

Pour plus de détails sur la façon dont fonctionne cette formule, vous pouvez lire l'explication sur le blog Contextures: vérification des critères multiples avec Excel INDEX et MATCH

Vidéo: Trouver le meilleur prix avec INDEX, MATCH, MIN

En combinant la fonction INDEX avec MATCH et MIN, vous pouvez afficher le nom du magasin qui a le plus bas prix. MIN calcule le prix le plus bas, et MATCH localise que le prix de la ligne. INDEX retourne le nom du magasin pour la colonne sélectionnée.

Pour obtenir des instructions écrites pour ce tutoriel Excel fonction INDEX, consultez l'article Blog Contextures: Trouver le meilleur prix avec Excel INDEX et MATCH

Vidéo: Trouver Distance entre les villes avec INDEX / MATCH

À partir d'une table de correspondance avec des distances entre les villes, vous pouvez utiliser les fonctions INDEX et MATCH pour afficher le kilométrage entre deux villes.

Regardez cette vidéo pour voir les étapes, et vous pouvez télécharger le fichier exemple de la vidéo pour voir comment cela fonctionne.

Résoudre la formule MATCH

Texte par rapport au nombre

Si possible, convertir le texte en chiffres, en utilisant l'une des méthodes présentées ici:
Convertir le texte en chiffres

Si vous ne pouvez pas convertir les données, vous pouvez convertir la valeur de recherche de la formule de MATCH:

valeurs de recherche sont texte, et la table contient les numéros

Si le tableau de recherche contient des numéros, et la valeur pour rechercher du texte, utiliser une formule similaire à ce qui suit:

Le double unaire (-) convertit le texte à un certain nombre, et fonctionnera correctement même si les valeurs de recherche sont des nombres.

valeurs de recherche sont des nombres, et la table contient du texte

Si le tableau de recherche contient du texte et la valeur à rechercher est numérique, utiliser une formule similaire à ce qui suit:

= MATCH (A5 - ""), ItemList, 0)
OU
= MATCH (TEXT (A5, "00000"), ItemList, 0)

La fonction TEXTE convertit un nombre en texte, et fonctionne correctement, même si les valeurs de recherche sont le texte. Dans le premier exemple, l'opérateur - crée une chaîne de texte à partir d'un numéro non formaté. Dans le second exemple, un certain nombre formaté avec zéros (par exemple 00123) correspondrait un texte « nombre » par des zéros.

Les espaces dans une valeur, et non l'

Si possible, supprimer les espaces inutiles, et la formule MATCH devrait fonctionner correctement. Si vous ne pouvez pas supprimer les espaces, utilisez la fonction TRIM dans le match, pour enlever de premier plan, de fuite ou de dupliquer des espaces. Par exemple:
= MATCH (TRIM (A5), ItemList, 0)

caractères HTML dans une valeur, et non l'

Télécharger le fichier d'échantillon







Articles Liés