En utilisant une formule matricielle dans Excel, nous pouvons créer une formule de recherche utilisant plusieurs critères pour rechercher des informations dans une base de données ou un tableau de données. La formule matricielle implique l'imbrication de la fonction MATCH dans la fonction INDEX.
Ce didacticiel comprend un exemple pas à pas de création d’une formule de recherche utilisant plusieurs critères pour rechercher un fournisseur deWidgets en titane dans une base de données exemple. Suivez le mouvement pour découvrir à quel point il est facile de créer vos propres formules de recherche personnalisées.
Préparez votre classeur Excel avec des données
Pour suivre les étapes de ce didacticiel, vous devez commencer par saisir les exemples de données dans les cellules suivantes, comme indiqué dans l'image ci-dessus. Les lignes 3 et 4 sont laissées vierges afin de prendre en compte la formule matricielle créée au cours de ce tutoriel.
- Entrez la première plage de données dans les cellules D1 à F2
- Entrez la deuxième plage dans les cellules D5 à F11
Le didacticiel n'inclut pas la mise en forme visible dans l'image, mais cela n'affectera pas le fonctionnement de la formule de recherche. Des informations sur les options de formatage similaires à celles présentées ci-dessus sont disponibles dans ce didacticiel de formatage Excel de base.
02 sur 08Créer une fonction INDEX dans Excel
le INDICE La fonction est l’un des rares à utiliser plusieurs formulaires dans Excel. La fonction a un Forme de tableau et un Formulaire de référence. Le formulaire de tableau renvoie les données réelles d'une base de données ou d'un tableau de données, tandis que le formulaire de référence vous donne la référence de cellule ou l'emplacement des données dans le tableau.
Dans ce didacticiel, nous utiliserons le formulaire Array puisque nous souhaitons connaître le nom du fournisseur pour les widgets en titane plutôt que la référence de cellule à ce fournisseur dans notre base de données. Suivez ces étapes pour créer votre fonction INDEX:
- Cliquez sur la cellule F3 pour en faire la cellule active - c'est ici que nous allons entrer dans la fonction imbriquée.
- Clique sur le Formules onglet du menu du ruban.
- Choisir Recherche et référence dans le ruban pour ouvrir la liste déroulante des fonctions.
- Cliquer sur INDICE dans la liste pour faire apparaître le Sélectionnez les arguments boite de dialogue.
- Choisir la array, row_num, col_num option dans la boîte de dialogue.
- Cliquez sur D'accord ouvrir la boîte de dialogue de la fonction INDEX; cela ouvrira le Générateur de formules dans Excel.
- dans le Formule Builder, clique sur le Tableau ligne.
- Surligner les cellules D6 à F11, en cliquant et en faisant glisser, dans la feuille de calcul pour entrer la plage dans le générateur.
Dans les anciennes versions d'Excel, le Formule Builder sera remplacé par le Arguments de fonction la fenêtre. Appliquez les mêmes étapes que celles mentionnées dans ce tutoriel dans la fenêtre.
03 de 08Démarrer la fonction MATCH imbriquée
Lors de l'imbrication d'une fonction dans une autre, il n'est pas possible d'ouvrir le générateur de formules de la deuxième fonction ou de la fonction imbriquée pour entrer les arguments nécessaires. La fonction imbriquée doit être entrée comme l’un des éléments triés de la première fonction.
Lors de la saisie manuelle de fonctions, les arguments de la fonction sont séparés les uns des autres par une virgule.
La première étape pour entrer la fonction MATCH imbriquée consiste à entrer le Lookup_value argument. La valeur de recherche sera l'emplacement ou la référence de cellule du terme de recherche que nous voulons faire correspondre dans la base de données.
Normalement, la valeur de recherche n'accepte qu'un seul critère ou terme. Pour rechercher plusieurs critères, nous devons étendre la valeur Lookup_value.; tCeci est réalisé en concaténant ou en joignant deux ou plusieurs références de cellules à l’aide du symbole esperluette - &.
- dans le Formule Builder, clique sur le Row_num ligne.
- Tapez le nom de la fonction RENCONTRE suivi d'un crochet rond ouvert.
- Cliquez sur la cellule D3 pour entrer cette référence de cellule dans la boîte de dialogue.
- Tapez une esperluette après la référence de cellule D3 afin d'ajouter une deuxième référence de cellule.
- Cliquez sur la cellule E3 pour entrer cette deuxième référence de cellule.
- Tapez une virgule après la référence de cellule E3 pour compléter la saisie de la fonction MATCH Lookup_value argument.
Dans la dernière étape du tutoriel, notre Lookup_values seront entrés dans les cellules D3 et E3 de la feuille de calcul.
04 de 08Terminez la fonction MATCH imbriquée
Cette étape couvre l'ajout de la Lookup_array argument pour l'imbrication RENCONTRE une fonction. Lookup_array est la plage de cellules dans laquelle la fonction MATCH va rechercher l'argument Lookup_value ajouté à l'étape précédente du didacticiel.
Puisque nous avons identifié deux champs de recherche dans l'argument Lookup_array, nous devons faire la même chose pour Lookup_array. La fonction MATCH ne recherche qu'un tableau pour chaque terme spécifié. Par conséquent, pour entrer plusieurs tableaux, nous utilisons à nouveau l'esperluette pour concaténer les tableaux.
Ces étapes doivent être entrées après la virgule entrée à l’étape précédente sur le Row_num ligne dans le INDICE une fonction.
- Clique sur le Row_num après la virgule, placez le point d’insertion à la fin de l’entrée actuelle.
- Surligner les cellules D6 à D11 dans la feuille de travail pour entrer dans la plage - c’est le premier tableau que la fonction doit rechercher.
- Tapez une esperluette après les références de cellule D6: D11 parce que nous voulons que la fonction recherche deux tableaux.
- Surligner les cellules E6 à E11 dans la feuille de travail pour entrer la plage - c'est le deuxième tableau que la fonction doit rechercher.
- Tapez une virgule après la référence de cellule E3 pour compléter la saisie du RENCONTRE les fonctions Lookup_array argument.
- Quitter le INDICE boîte de dialogue de fonction ouverte pour l’étape suivante du didacticiel.
Ajouter l'argument de type MATCH
Le troisième et dernier argument de la fonction MATCH est le Type de match argument; il indique à Excel comment faire correspondre le Lookup_value avec des valeurs dans Lookup_array - les choix disponibles sont 1, 0 ou -1.
Cet argument est optionnel. Si elle est omise, la fonction utilise la valeur par défaut de 1.
- Si la Match_type = 1 ou est omis: MATCH trouve la plus grande valeur inférieure ou égale à Lookup_value. Les données Lookup_array doivent être triées par ordre croissant.
- Si la Match_type = 0: MATCH trouve la première valeur exactement égale à Lookup_value. Les données Lookup_array peuvent être triées dans n'importe quel ordre.
- Si la 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.
Ces étapes doivent être entrées après la virgule entrée à l’étape précédente sur le Row_num ligne dans le INDICE une fonction.
- Après la virgule sur le Row_num ligne, tapez un zéro - 0 - puisque nous voulons que la fonction imbriquée renvoie les correspondances exactes aux termes que nous avons entrés dans les cellules D3 et E3.
- Tapez un crochet rond de fermeture - ) - compléter le RENCONTRE une fonction.
- Quitter le Formule Builder ouvert pour la prochaine étape du tutoriel.
Finition de la fonction INDEX
Maintenant que le RENCONTRE fonction est terminée, nous allons passer à la troisième ligne du Générateur de formules et entrer le dernier argument de la INDICE une fonction. Ce troisième et dernier argument est le Numéro de colonne argument qui indique à Excel le numéro de colonne dans la plage D6 à F11; C’est à cet endroit qu’il trouvera les informations que nous voulons être renvoyées par la fonction. Dans ce cas, un fournisseur pour widgets en titane.
- Clique sur le Numéro de colonne ligne.
- Entrez le numéro trois - 3 - sur cette ligne puisque nous recherchons des données dans la troisième colonne de la plage D6 à F11.
- Encore une fois, laissez le Formule Builder ouvert pour la prochaine étape du tutoriel.
Création de la formule de tableau
Avant de fermer le Formule Builder, nous devons transformer notre fonction imbriquée en une formule matricielle; c'est ce qui permet à notre fonction de rechercher plusieurs termes dans la table de données. Dans ce tutoriel, nous cherchons à faire correspondre deux termes: Widgets de la colonne 1 et Titane de la colonne 2.
La création d’une formule matricielle dans Excel se fait en appuyant sur la touche CTRL, DÉCALAGE, et ENTRER touches du clavier en même temps. Une fois appuyée, la fonction sera entourée d'accolades indiquant que la fonction est maintenant un tableau.
- Fermer la Formule Builder en cliquant sur le Terminé bouton.
- Ensuite, sélectionnez la cellule F4 et appuyez sur le Entrer touche pour afficher la formule.
- Pour convertir la formule en tableau, appuyez simultanément sur CTRL + SHIFT + ENTRÉE sur votre clavier.
- Si fait correctement un #N / A erreur apparaîtra dans la cellule F3 - la cellule où nous avons entré la fonction.
- le #N / A erreur apparaît dans la cellule F3 parce que les cellules D3 et E3 sont vides. D3 et E3 sont les cellules où nous avons dit à la fonction de trouver le Lookup_values. Une fois les données ajoutées à ces deux cellules, l'erreur sera remplacée par les informations de la base de données.
Ajout des critères de recherche
Enfin, nous ajouterons les termes de recherche à notre feuille de calcul. Comme mentionné dans l'étape précédente, nous cherchons à faire correspondre les termes Widgets de la colonne 1 et Titane de la colonne 2. Si, et seulement si, notre formule trouve une correspondance pour les deux termes dans les colonnes appropriées de la base de données, renverra-t-elle la valeur de la troisième colonne.
- Cliquez sur la cellule D3.
- Type Widgets et appuyez sur le Entrer touche du clavier.
- Cliquez sur la cellule E3.
- Type Titane et appuyez sur le Entrer touche du clavier.
- Le nom du fournisseur Widgets Inc. devrait apparaître dans la cellule F3 - l'emplacement de la fonction, car il s'agit du seul fournisseur répertorié à vendre des widgets Titanium.
- Lorsque vous cliquez sur la cellule F3, la fonction complète apparaît dans la barre de formule au-dessus de la feuille de calcul.
{= INDEX (D6: F11, MATCH (D3 et E3, D6: D11 et E6: E11, 0), 3)}
Dans notre exemple, il n'y avait qu'un seul fournisseur pour les widgets en titane. S'il y a plus d'un fournisseur, le fournisseur répertorié en premier dans la base de données est renvoyé par la fonction.
