Comment créer une formule de recherche Excel avec plusieurs conditions

Comment créer une formule de recherche Excel avec plusieurs conditions

ce qu’il faut savoir

• Commencez par créer une fonction INDEX, puis démarrez la fonction MATCH imbriquée en saisissant le paramètre Lookup_value.
• Ensuite, ajoutez le paramètre Lookup_array suivi du Match_type paramètre, puis spécifiez la plage de colonnes.
• Ensuite, convertissez la fonction imbriquée en une formule matricielle en appuyant sur Ctrl+transférer+EntrerEnfin, ajoutez le terme de recherche à la feuille de calcul.

Cet article explique comment créer une formule de recherche qui utilise plusieurs critères dans Excel pour rechercher des informations dans une base de données ou une feuille de données via une formule matricielle. Les formules matricielles impliquent l’imbrication de la fonction MATCH dans la fonction INDEX. Les informations couvrent Excel pour Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 et Excel pour Mac.

suivre le tuto

Pour suivre les étapes de ce didacticiel, entrez des exemples de données dans les cellules suivantes, comme indiqué dans l’image ci-dessous. Les lignes 3 et 4 sont laissées vides pour tenir compte de la formule matricielle créée dans ce didacticiel. (Notez que ce didacticiel ne couvre pas le format indiqué dans l’image.)

• Entrez la plage supérieure de données dans les cellules D1 à F2.
• Entrez la deuxième plage dans les cellules D5 à F11.

Créer une fonction d’index dans Excel

La fonction INDEX est l’une des rares fonctions d’Excel à avoir plusieurs formulaires. La fonction a une forme de tableau et une forme de référence. Renvoie les données d’une base de données ou d’une table de données sous forme de tableau. La table de référence donne la référence de cellule ou l’emplacement des données dans la table.

Dans ce didacticiel, le formulaire Array est utilisé pour trouver le nom du fournisseur du widget en titane, et non la référence de cellule à ce fournisseur dans la base de données.

Suivez ces étapes pour créer une fonction INDEX :

1. sélectionner des cellules F3 Faites-en la cellule active. Cette cellule est l’endroit où la fonction imbriquée est entrée.

2. aller formule.

3. choisir Rechercher et référencer Ouvrez la liste déroulante des fonctions.

4. choisir indice Ouvert sélectionner les paramètres dialogue.

5. choisir tableau, num_ligne, num_colonne.

6. choisir D’ACCORD Ouvert paramètre de fonction dialogue. Dans Excel pour Mac, le générateur de formule s’ouvre.

7. placer le curseur sur en grand nombre zone de texte.

8. cellule de surbrillance D6 passer F11 Entrez la plage dans la boîte de dialogue de la feuille de calcul.

   Laissez la boîte de dialogue Arguments de la fonction ouverte. La formule n’est pas encore terminée. Vous suivrez les instructions ci-dessous pour compléter la formule.

Démarrer la fonction MATCH imbriquée

Lorsqu’une fonction est imbriquée dans une autre fonction, le générateur de formules pour la seconde fonction ou fonction imbriquée ne peut pas être ouvert pour saisir les paramètres nécessaires. Les fonctions imbriquées doivent être saisies comme l’un des paramètres de la première fonction.

Lors de la saisie manuelle d’une fonction, séparez les paramètres de la fonction par des virgules.

La première étape de la fonction MATCH imbriquée consiste à entrer le paramètre Lookup_value. Lookup_value est la position ou la référence de cellule du terme de recherche à rechercher dans la base de données.

Lookup_value n’accepte qu’un seul critère ou terme de recherche. Pour rechercher plusieurs critères, étendez Lookup_value en concaténant avec une esperluette (&) ou en concaténant deux références de cellule ou plus.

1. à l’intérieur paramètre de fonction boîte de dialogue, placez le curseur sur la Lignes zone de texte.

2. Entrer match(.

3. sélectionner des cellules D3 Entrez cette référence de cellule dans la boîte de dialogue.

4. Entrer & (Ampersand) après la référence de cellule D3 Ajoutez une deuxième référence de cellule.

5. sélectionner des cellules E3 Entrez une deuxième référence de cellule.

6. Entrer , (Virgule) Complétez la saisie du paramètre Lookup_value de la fonction MATCH après la référence de cellule E3.

   Dans la dernière étape de ce didacticiel, entrez Lookup_values ​​​​dans les cellules D3 et E3 de la feuille de calcul.

Fonctions MATCH imbriquées complètes

Cette étape inclut l’ajout du paramètre Lookup_array pour la fonction MATCH imbriquée. Lookup_array est la plage de cellules dans laquelle la fonction MATCH recherche le paramètre Lookup_value ajouté à l’étape précédente de ce didacticiel.

Étant donné que les deux champs de recherche sont identifiés dans le paramètre Lookup_array, la même chose doit être faite pour Lookup_array. La fonction MATCH recherche simplement un tableau pour chaque terme spécifié. Pour entrer plusieurs tableaux, utilisez l’esperluette pour joindre les tableaux ensemble.

1. placer le curseur à la fin des données Lignes zone de texte. Le curseur apparaît après la virgule à la fin de l’entrée actuelle.

2. cellule de surbrillance D6 passer D11 Entrez la plage dans la feuille de calcul. Cette plage est le premier tableau que la fonction recherche.

3. Entrer & (et signe) après la référence de cellule D6:D11Ce symbole force la fonction à rechercher deux tableaux.

4. cellule de surbrillance E6 passer E11 Entrez la plage dans la feuille de calcul. La plage est le deuxième tableau que la fonction recherche.

5. Entrer , (virgule) après la référence de cellule E3 Complétez l’entrée pour le paramètre Lookup_array de la fonction MATCH.

6. Laissez la boîte de dialogue ouverte pour l’étape suivante du didacticiel.

Ajouter un paramètre de type MATCH

Le troisième et dernier argument La fonction MATCH est Match_type débat. Ce paramètre indique à Excel comment faire correspondre Lookup_value avec les valeurs de Lookup_array. Les options disponibles sont 1, 0 ou -1.

Ce paramètre est facultatif. Si omis, la fonction utilise la valeur par défaut de 1.

• Si Match_type = 1 ou omis, MATCH recherche la plus grande valeur inférieure ou égale à Lookup_value. Les données Lookup_array doivent être triées par ordre croissant.
• Si Match_type = 0, MATCH trouve la première valeur égale à Lookup_value. Les données Lookup_array peuvent être triées dans n’importe quel ordre.
• Si Match_type = -1, MATCH trouve la plus petite valeur supérieure ou égale à Lookup_value. Les données Lookup_array doivent être triées par ordre décroissant.

Entrez ces étapes après la virgule saisie à l’étape précédente sur la ligne Row_num de la fonction INDEX :

1. Entrer 0 (zéro) après la virgule Lignes zone de texte. Ce nombre amène la fonction imbriquée à renvoyer une correspondance exacte pour les termes saisis dans les cellules D3 et E3.

2. Entrer ) (une parenthèse fermante) pour terminer la fonction MATCH.

3. Laissez la boîte de dialogue ouverte pour l’étape suivante du didacticiel.

Compléter la fonction INDEX

La fonction MATCH est terminée. Il est temps de passer à la zone de texte Column_num de la boîte de dialogue et d’entrer le dernier paramètre de la fonction INDEX. Ce paramètre indique à Excel que le numéro de colonne est compris entre D6 et F11. Cette portée est l’endroit où il trouve les informations renvoyées par la fonction. Dans ce cas, les fournisseurs de widgets en titane.

1. placer le curseur sur numéro de colonne zone de texte.

2. Entrer 3 (Le troisième). Ce nombre indique à la formule de rechercher des données dans la troisième colonne de la plage D6 à F11.

3. Laissez la boîte de dialogue ouverte pour l’étape suivante du didacticiel.

Créer une formule matricielle

Convertissez les fonctions imbriquées en formules matricielles avant de fermer la boîte de dialogue. Ce tableau permet à la fonction de rechercher plusieurs termes dans la table de données. Dans ce didacticiel, deux termes correspondent : widget dans la colonne 1 et Titanium dans la colonne 2.

Pour créer une formule matricielle dans Excel, appuyez sur CTRL, transféreret Entrer touche simultanément. Lorsque vous appuyez dessus, la fonction est entourée d’accolades, indiquant que la fonction est maintenant un tableau.

1. choisir D’ACCORD Fermez la boîte de dialogue.Dans Excel pour Mac, sélectionnez Achevée.

2. sélectionner des cellules F3 Regardez la formule et placez le curseur à la fin de la formule dans la barre de formule.

3. Pour convertir la formule en tableau, appuyez sur CTRL+transférer+Entrer.

4. #N’est pas applicable L’erreur est dans la cellule F3. Il s’agit de la cellule dans laquelle la fonction est saisie.

5. L’erreur #N/A se produit dans la cellule F3 car les cellules D3 et E3 sont vides. D3 et​​ E3 sont les cellules où la fonction recherche Lookup_value. Après avoir ajouté des données à ces deux cellules, les erreurs sont remplacées par les informations de la base de données.

Ajouter des critères de recherche

La dernière étape consiste à ajouter les termes de recherche à la feuille de calcul.Cette étape est liée aux termes Widgets dans la colonne 1 et Titane de la colonne 2.

Si la formule trouve une correspondance pour les deux termes dans les colonnes correspondantes de la base de données, elle renvoie la valeur dans la troisième colonne.

1. sélectionner des cellules D3.

2. Entrer petites pièces.

3. sélectionner des cellules E3.

4. taper titanepuis appuyez Entrer.

5. Le nom du fournisseur Widgets Inc. apparaît dans la cellule F3. Il s’agit du seul fournisseur répertorié qui vend des widgets Titanium.

6. sélectionner des cellules F3La fonction apparaît dans la barre de formule au-dessus de la feuille de calcul.

   {=INDEX(D6:F11,EQUIV(D3&E3,D6:D11&E6:E11,0),3)}

   Dans cet exemple, il n’y a qu’un seul fournisseur de pièces en titane. S’il existe plusieurs fournisseurs, la fonction renvoie le premier fournisseur répertorié dans la base de données.

Merci de nous en informer!

Dites-nous pourquoi !

D’autres détails ne sont pas assez difficiles à comprendre