Disons que vous devez trouver le poste téléphonique d'un employé à l'aide de son numéro et également estimer correctement le ratio de commission pour le montant des ventes. Vous recherchez des données pour trouver rapidement et efficacement des données spécifiques dans une liste et vérifier automatiquement que les données sont utilisées correctement. Après avoir visualisé les données, vous pouvez effectuer des calculs et afficher les résultats, en indiquant les valeurs de retour. Il existe plusieurs façons de rechercher des valeurs dans une liste de données et d'afficher les résultats.

Dans cet article

Rechercher des valeurs dans une liste verticalement par correspondance exacte

Vous pouvez utiliser la fonction RECHERCHEV ou une combinaison des fonctions INDEX et MATCH pour effectuer cette tâche.

Exemples d'utilisation de la fonction RECHERCHEV

Fonction RECHERCHEV.

Exemples d'INDEX et de MATCHES

Qu'est-ce que ça veut dire:

=INDEX(vous devez renvoyer la valeur de C2:C10, qui correspondra à MATCH(la première valeur "Chou" dans le tableau B2:B10))

La formule recherche la première valeur de la cellule C2:C10 correspondant à chou(en B7), et renvoie la valeur en C7 ( 100 ) - la première valeur correspondant chou.

Pour plus d’informations, voir Fonction INDEX et Fonction MATCH.

Rechercher des valeurs dans une liste verticalement par correspondance approximative

Pour ce faire, utilisez la fonction RECHERCHEV.

Important:

Dans l'exemple ci-dessus, la fonction RECHERCHEV recherche le nom d'un élève qui a 6 retards dans la plage A2:B7. Il n'y a aucune entrée dans le tableau pour 6 tardives, donc la fonction RECHERCHEV recherche la prochaine correspondance la plus élevée en dessous de 6 et trouve la valeur 5 associée au prénom Dave, et renvoie donc Dave.

Pour plus d’informations, consultez la fonction RECHERCHEV.

Trouver des valeurs verticales dans une liste de taille inconnue avec une correspondance exacte

Pour effectuer cette tâche, utilisez les fonctions OFFSET et MATCH.

Note: Cette approche est utilisée si les données se trouvent dans une plage de données externe que vous mettez à jour quotidiennement. Vous savez que la colonne B contient le prix, mais vous ne savez pas combien de lignes de données le serveur renvoie et la première colonne n'est pas triée par ordre alphabétique.

C1 est la cellule supérieure gauche de la plage (également appelée cellule de départ).

Correspondance("oranges"; C2 : C7; 0) recherche la couleur orange dans la gamme C2:C7. Vous ne devez pas inclure la cellule de départ dans la plage.

1 - le nombre de colonnes à droite de la cellule de départ pour lesquelles la valeur de retour doit être renvoyée. Dans notre exemple, la valeur de retour est dans la colonne D, Ventes.

Rechercher des valeurs dans une liste horizontalement par correspondance exacte

Pour effectuer cette tâche, la fonction GLOOKUP est utilisée. Ci-dessous un exemple.

La fonction LOOKUP recherche une colonne Ventes et renvoie la valeur de la ligne 5 dans la plage spécifiée.

Pour plus d’informations, consultez Fonctions RECHERCHE.

Rechercher des valeurs dans une liste horizontalement en utilisant une correspondance approximative

Pour effectuer cette tâche, la fonction GLOOKUP est utilisée.

Important: Assurez-vous que les valeurs de la première ligne sont triées par ordre croissant.

Dans l'exemple ci-dessus, la fonction GLOOKUP recherche la valeur 11 000 dans la ligne 3 dans la plage spécifiée. Il ne trouve pas 11 000 et recherche donc la valeur la plus élevée suivante, inférieure à 1 100, et renvoie le nombre 10 543.

Pour plus d’informations, consultez Fonctions RECHERCHE.

Créez une formule de recherche à l'aide de l'Assistant de recherche (Excel 2007 uniquement)

Note: Le complément Assistant de recherche a été abandonné dans Excel 2010. Cette fonctionnalité a été remplacée par l'Assistant de fonction et les fonctionnalités de recherche et de référence (référence) disponibles.

Dans Excel 2007, l'Assistant de recherche crée une formule de recherche basée sur les données d'une feuille de calcul comportant des en-têtes de lignes et de colonnes. L'assistant de recherche vous aide à rechercher d'autres valeurs dans une ligne lorsque vous connaissez la valeur dans une colonne, et vice versa. L'assistant de recherche utilise index et MATCH dans les formules qu'il crée.

Bonjour les amis. À quelle fréquence devez-vous rechercher une correspondance dans une feuille de calcul Excel pour une valeur ? Par exemple, vous devez rechercher l’adresse d’une personne dans un annuaire, ou le prix d’un produit dans une liste de prix. Si de telles tâches se produisent, cet article est fait pour vous !

J'effectue des procédures similaires tous les jours et sans les fonctions décrites ci-dessous, j'aurais vraiment du mal. Prenez-en note et appliquez-les dans votre travail !

Recherche dans le tableau Excel, fonctions VLOOKUP et GLOOKUP

Le rôle de ces fonctions dans la vie d'un utilisateur ordinaire ne peut guère être surestimé. Vous pouvez désormais facilement trouver une entrée appropriée dans le tableau de données et renvoyer la valeur correspondante.

La syntaxe de la fonction RECHERCHEV est : =RECHERCHEV( valeur_recherche ; recherche_table ; numéro_colonne_sortie ; [type_mapping]). Regardons les arguments :

• Valeur de recherche– la valeur que l’on va rechercher. C'est un argument requis;
• Tableau de recherche– le tableau de cellules dans lequel la recherche aura lieu. La colonne contenant les valeurs recherchées doit être la première de ce tableau. C'est également un argument obligatoire ;
• Numéro de colonne à afficher– le numéro de série de la colonne (en commençant par la première du tableau), à partir de laquelle la fonction affichera les données si les valeurs requises correspondent. Argument requis ;
• Type correspondant– sélectionnez « 1 » (ou « TRUE ») pour une correspondance faible, « 0 » (« FALSE ») pour une correspondance complète. L'argument est facultatif ; s'il est omis, une recherche sera effectuée correspondance non stricte.

Trouver une correspondance exacte à l'aide de RECHERCHEV

Regardons un exemple du fonctionnement de la fonction RECHERCHEV lorsque le type de correspondance est FALSE, recherchant une correspondance exacte. Le tableau B5:E10 indique les immobilisations d'une certaine entreprise, leur valeur comptable, leur numéro d'inventaire et leur emplacement. La cellule B2 contient le nom pour lequel vous devez trouver le numéro d'inventaire dans le tableau et le placer dans la cellule C2.

Fonction RECHERCHEV dans Excel

Écrivons la formule : =VLOOKUP(B2,B5:E10,3,FALSE) .

Ici, le premier argument indique que dans le tableau, vous devez rechercher la valeur de la cellule B2, c'est-à-dire le mot « Fax ». Le deuxième argument indique que le tableau dans lequel rechercher est dans la plage B5:E10 et que vous devez rechercher le mot « Fax » dans la première colonne, c'est-à-dire dans le tableau B5:B10. Le troisième argument indique au programme que le résultat du calcul est contenu dans la troisième colonne du tableau, c'est-à-dire D5:D10. Le quatrième argument est FAUX, c'est-à-dire une correspondance complète est requise.

Ainsi, la fonction recevra la chaîne « Fax » de la cellule B2 et la recherchera dans le tableau B5:B10 de haut en bas. Une fois qu'une correspondance est trouvée (ligne 8), la fonction renverra la valeur correspondante de la colonne D, c'est-à-dire Contenu du D8. C'est exactement ce dont nous avions besoin, le problème est résolu.

Si la valeur recherchée n'est pas trouvée, la fonction retournera .

Trouver une correspondance imprécise à l'aide de RECHERCHEV

Grâce à cette option dans RECHERCHEV, nous pouvons éviter les formules complexes pour trouver le résultat souhaité.

Le tableau B5 : C12 montre les taux d’intérêt sur les prêts en fonction du montant du prêt. Dans la cellule B2, nous indiquons le montant du prêt et souhaitons recevoir le taux pour une telle transaction en C2. La tâche est difficile dans la mesure où le montant peut être n'importe quoi et est peu susceptible de coïncider avec ceux spécifiés dans le tableau ; la recherche d'une correspondance exacte ne convient pas :

Ensuite, nous écrivons la formule d'une recherche non stricte : =RECHERCHEV(B2,B5:C12,2,VRAI). Désormais, parmi toutes les données présentées dans la colonne B, le programme recherchera la plus petite la plus proche. C'est-à-dire que pour le montant de 8 000, la valeur de 5 000 sera sélectionnée et le pourcentage correspondant sera affiché.

Recherche RECHERCHEV non stricte dans Excel

Pour que la fonction fonctionne correctement, vous devez trier la première colonne du tableau par ordre croissant. Sinon, cela pourrait donner un résultat erroné.

La fonction GLOOKUP a la même syntaxe que VLOOKUP, mais recherche le résultat en lignes plutôt qu'en colonnes. Autrement dit, il analyse les tableaux non pas de haut en bas, mais de gauche à droite et affiche le numéro de ligne spécifié, pas la colonne.

Recherche de données à l'aide de la fonction VIEW

La fonction LOOKUP fonctionne de manière similaire à VLOOKUP, mais a une syntaxe différente. Je l'utilise lorsque la table de données contient plusieurs dizaines de colonnes et que pour utiliser RECHERCHEV, vous devez en outre calculer le numéro de la colonne de sortie. Dans de tels cas, la fonction VIEW facilite la tâche. Et donc la syntaxe : =VOIR( valeur_recherche ; array_to_search; Tableau_à_affichage) :

• Valeur de recherche– des données ou un lien vers les données à rechercher ;
• Tableau à rechercher– une ligne ou une colonne dans laquelle on recherche une valeur similaire. Nous devons trier ce tableau par ordre croissant ;
• Tableau à afficher– une plage contenant des données pour afficher les résultats. Naturellement, il doit avoir la même taille que le tableau de recherche.

En écrivant de cette façon, vous donnez une référence non relative au tableau de résultats. Et vous le montrez directement, c'est-à-dire il n'est pas nécessaire de calculer au préalable le numéro de la colonne de sortie. Nous utilisons la fonction VIEW dans le premier exemple pour la fonction RECHERCHEV (immobilisations, numéros d'inventaire) : =VUE(B2,B5:B10,D5:D10). Le problème a été résolu avec succès !

Fonction VOIR dans Microsoft Excel

Recherche par coordonnées relatives. Fonctions MATCH et INDEX

Une autre façon de rechercher des données consiste à combiner les fonctions MATCH et INDEX.

Le premier d'entre eux permet de rechercher une valeur dans un tableau et d'obtenir son numéro de série : SEARCH( valeur_recherche ; tableau_vu ; [Type correspondant] ). Arguments de la fonction :

• Valeur de recherche– argument requis
• Tableau à visualiser– une ligne ou une colonne dans laquelle nous recherchons une correspondance. Argument requis
• Type correspondant– spécifiez « 0 » pour rechercher une correspondance exacte, « 1 » pour la plus petite correspondance la plus proche, « -1 » pour la plus grande correspondance la plus proche. Étant donné que la fonction recherche du début à la fin de la liste, lorsque vous recherchez le plus petit le plus proche, triez la colonne de recherche par ordre décroissant. Et lorsque vous en cherchez plus, triez-les par ordre croissant.

La position de la valeur recherchée a été trouvée, vous pouvez désormais l'afficher à l'écran grâce à la fonction INDICE( Tableau; numéro de ligne; [Numéro_colonne]) :

• Tableau– l'argument spécifie dans quel tableau de cellules la valeur doit être sélectionnée
• Numéro de ligne– indiquez le numéro de série de la ligne (à partir de la première cellule du tableau) que vous souhaitez afficher. Ici, vous pouvez écrire la valeur manuellement ou utiliser le résultat d'un autre calcul de fonction. Par exemple, RECHERCHE.
• Numéro de colonne– argument facultatif, spécifié si le tableau est composé de plusieurs colonnes. Si un argument est omis, la formule utilise la première colonne du tableau.

Combinons maintenant ces fonctions pour obtenir le résultat :

Fonctions MATCH et INDEX dans Excel

Ce sont les méthodes de recherche et d’affichage des données qui existent dans Excel. De plus, vous pouvez les utiliser dans des calculs, les utiliser dans des présentations, effectuer des opérations avec eux, les spécifier comme arguments pour d'autres fonctions, etc.

Sentez-vous comment vos connaissances et vos compétences se développent et se renforcent ? Alors ne vous arrêtez pas, continuez à lire ! Dans le prochain article, nous réfléchirons : ce sera difficile et intéressant !

Date : 3 janvier 2016 Catégorie :

La recherche de caractères dans une chaîne est la tâche la plus importante qui vous permet d'automatiser le travail avec du texte. Par exemple, cela facilite les choses lorsque la position de la ligne cible est inconnue.

J'ai évoqué l'outil de recherche Excel dans , mais nous examinerons ici les fonctions qui recherchent du texte dans une chaîne et renvoient sa position de départ.

Il existe deux fonctions pour rechercher du texte dans Microsoft Excel :

1. TROUVER() . Cette fonction a 2 arguments obligatoires et un facultatif :
   • Rechercher du texte– la séquence de caractères à trouver. C'est un argument requis
   • Texte original– la chaîne dans laquelle la recherche est effectuée. Argument requis
   • Position de départ– le numéro d'ordre du caractère du texte source à partir duquel la recherche commence. Argument facultatif. S'il n'est pas précisé, la recherche démarre à partir du premier caractère

Fonction TROUVER prend en compte Et ne prend pas en charge l'utilisation de caractères génériques(lisez-les plus loin).

Fonction TROUVER dans Excel

1. RECHERCHE(Rechercher du texte ; Texte original; [La position de départ]) a une syntaxe et des arguments similaires. Cela diffère en ce que insensible à la casse Et prend en charge l'utilisation de caractères génériques.

Fonction RECHERCHE dans Excel

Comme vous pouvez le constater, cette fonction a donné un résultat différent car la recherche n'était pas sensible à la casse.

Que sont les jokers, demandez-vous ? Il s'agit de caractères spéciaux qui vous permettent de sauter une partie inconnue du texte lors de la recherche :

• ? – n'importe quel symbole
• * – n'importe quelle séquence de caractères

Si le point d'interrogation ou l'astérisque fait partie de la chaîne de recherche et n'est pas un caractère générique, faites-le précéder d'un tilde (~).

Écrivons la formule suivante : =SEARCH(“??chn”,A1,1). La fonction recherchera une chaîne de deux caractères quelconques suivie des lettres « chn ». La recherche correspond à une partie du mot « Tabulaire » dans l'exemple de texte, c'est-à-dire : combinaison de caractères "personnels". Le résultat de la fonction sera la valeur "4".

Caractères génériques dans Excel

Écrivons maintenant la formule =SEARCH("p*s",A2,1) . La fonction recherche la combinaison « p » + n'importe quel nombre de caractères + « s ». Dans l'exemple, cela fait partie du mot « processeur ». Le résultat de la fonction est 11.

Caractère générique astérisque dans Excel

Les fonctions de recherche vous permettent de suivre plus facilement la position du texte souhaité, il est donc plus facile de le remplacer. Assurez-vous de vous entraîner à les utiliser. Mon post-tutoriel sur les fonctions de texte vous y aidera.

Et le prochain article y sera consacré. Un sujet important qui doit absolument être étudié !

Posez des questions dans les commentaires et abonnez-vous aux mises à jour pour ne rien manquer !

Nous avons un tableau qui enregistre les volumes de ventes de certains produits au cours de différents mois. Vous devez rechercher des données dans le tableau et les critères de recherche seront les en-têtes de lignes et de colonnes. Mais la recherche doit être effectuée séparément par plage de lignes ou de colonnes. Autrement dit, un seul des critères sera utilisé. Par conséquent, la fonction INDEX ne peut pas être utilisée ici, mais une formule spéciale est nécessaire.

Trouver des valeurs dans un tableau Excel

Pour résoudre ce problème, nous illustrons un exemple sur un tableau schématique qui correspond aux conditions décrites ci-dessus.

Feuille avec un tableau pour rechercher des valeurs verticalement et horizontalement :

Au-dessus du tableau lui-même, il y a une ligne avec les résultats. Dans la cellule B1, nous saisissons le critère de la requête de recherche, c'est-à-dire l'en-tête de colonne ou le nom de ligne. Et dans la cellule D1, la formule de recherche doit renvoyer le résultat du calcul de la valeur correspondante. Après cela, la deuxième formule fonctionnera dans la cellule F1, qui utilisera déjà les valeurs des cellules B1 et D1 comme critères de recherche du mois correspondant.

Trouver une valeur dans une chaîne Excel

Nous découvrons maintenant quel est le volume maximum et au cours de quel mois a eu lieu la vente maximale du produit 4.

Pour effectuer une recherche par colonnes :

Trouvé au cours de quel mois et quelle a été la plus grande vente du produit 4 sur deux trimestres.

Le principe de la formule pour trouver une valeur dans une ligne Excel :

Le premier argument de la fonction RECHERCHEV (Vertical View) précise un lien vers la cellule où se trouve le critère de recherche. Le deuxième argument spécifie la plage de cellules à afficher lors de la recherche. Le troisième argument de la fonction RECHERCHEV doit indiquer le numéro de la colonne à partir de laquelle la valeur doit être prise par rapport à la ligne portant le nom Produit 4. Mais comme nous ne connaissons pas ce numéro à l'avance, nous utilisons la fonction COLONNE pour créer un tableau de numéros de colonnes pour la plage B4:G15.

Cela permet à la fonction RECHERCHEV de collecter un tableau complet de valeurs. De ce fait, toutes les valeurs correspondantes pour chaque colonne de la ligne Produit 4 sont stockées en mémoire (à savoir : 360 ; 958 ; 201 ; 605 ; 462 ; 832). Après cela, la fonction MAX ne peut prendre que le nombre maximum de ce tableau et le renvoyer comme valeur de la cellule D1, suite au calcul de la formule.

Comme vous pouvez le constater, la conception de la formule est simple et concise. Sur cette base, vous pouvez trouver d'autres indicateurs pour un produit spécifique de la même manière. Par exemple, la valeur minimale ou moyenne du volume des ventes en utilisant les fonctions MIN ou MOYENNE. Rien ne vous empêche d'appliquer cette formule squelette en utilisant des fonctions plus complexes pour mettre en œuvre l'analyse la plus confortable du rapport de ventes.

Comment obtenir des en-têtes de colonnes basés sur le début d’une cellule ?

Par exemple, avec quelle efficacité nous avons affiché le mois au cours duquel il y a eu une vente maximale en utilisant la deuxième formule. Il n’est pas difficile de remarquer que dans la deuxième formule nous avons utilisé le squelette de la première formule sans la fonction MAX. La structure principale de la formule est : RECHERCHEV(B1,A5:G14,COLUMN(B5:G14),0). Nous avons remplacé la fonction MAX par MATCH, qui utilise la valeur obtenue par la formule précédente comme premier argument. Il fait désormais office de critère de recherche du mois. Et par conséquent, la fonction RECHERCHE nous renvoie le numéro de la colonne 2, où se trouve la valeur maximale du volume des ventes du produit 4. Après quoi la fonction INDEX est incluse dans le travail, qui renvoie la valeur par date et numéro de colonne. dans la plage spécifiée dans ses arguments. Puisque nous avons un numéro de colonne de 2 et que le numéro de ligne dans la plage où sont stockés les noms des mois sera dans tous les cas 1. Il suffit ensuite d'utiliser la fonction INDEX pour obtenir la valeur correspondante de la plage B4 : G4 - février (deuxième mois).



Trouver une valeur dans une colonne Excel

La deuxième version de la tâche consisterait à effectuer une recherche dans le tableau en utilisant le nom du mois comme critère. Dans de tels cas, nous devons changer le squelette de notre formule : remplacer la fonction RECHERCHEV par RECHERCHEH, et remplacer la fonction COLONNE par ROW.

Cela nous permettra de savoir quel volume et quel produit ont été les ventes maximales au cours d'un mois donné.

Pour savoir quel produit a enregistré le volume de ventes maximum au cours d'un mois donné, vous devez :

Le principe de la formule pour trouver une valeur dans une colonne Excel :

Dans le premier argument de la fonction HLOO (Horizontal View), on indique un lien vers la cellule avec le critère de recherche. Le deuxième argument spécifie une référence à la plage de table affichée. Le troisième argument est généré par la fonction ROW, qui crée un tableau de 10 éléments de numéros de ligne en mémoire. Puisque nous avons 10 lignes dans la partie tabulaire.

Ensuite, la fonction GLOOKUP, en utilisant tour à tour chaque numéro de ligne, crée un tableau de valeurs de ventes correspondantes à partir du tableau pour un mois spécifique (juin). Ensuite, la fonction MAX n'a ​​besoin que de sélectionner la valeur maximale de ce tableau.

ATTENTION! Lorsque vous utilisez la formule squelette pour d'autres problèmes, faites toujours attention aux deuxième et troisième arguments de la fonction de recherche GLOOKUP. Le nombre de lignes couvertes dans la plage spécifiée dans l'argument doit correspondre au nombre de lignes du tableau. Et aussi la numérotation doit commencer à partir de la deuxième ligne !

Parmi des milliers de lignes et des dizaines de colonnes de données, il est presque impossible de trouver manuellement quelque chose dans un tableau Excel. La seule option consiste à utiliser une sorte de fonction de recherche, et nous verrons ensuite comment effectuer une recherche dans un tableau Excel.

Pour rechercher des données dans un tableau Excel, vous devez utiliser l'élément de menu "Rechercher et sélectionner" sur l'onglet "Maison", dans lequel vous devez sélectionner une option "Trouver" ou utilisez la combinaison de touches pour appeler "Ctrl + F".

Par exemple, essayons de trouver le nombre recherché parmi les données de notre tableau, puisque c'est lors de la recherche de nombres qu'il faut prendre en compte certaines subtilités de la recherche. Cherchons un nombre dans le tableau Excel "dix".

Après avoir sélectionné l'élément de menu requis, entrez la valeur souhaitée dans la zone de recherche qui apparaît. Nous avons deux options pour rechercher des valeurs dans un tableau Excel, celle-ci consiste à trouver toutes les correspondances à la fois en cliquant sur le bouton "Trouver tout" ou visualisez immédiatement chaque cellule trouvée en appuyant à chaque fois sur le bouton "Trouver le suivant". Lorsque vous utilisez le bouton "Trouver le suivant" vous devez également prendre en compte l'emplacement actuel de la cellule active, puisque la recherche commencera à partir de cette position.

Essayons de trouver toutes les valeurs à la fois, et tout ce qui est trouvé sera répertorié dans la fenêtre sous les paramètres de recherche. Si nous laissons tous les paramètres par défaut, le résultat de la recherche ne sera pas exactement celui attendu.

Pour rechercher correctement des données dans un tableau Excel, cliquez sur le bouton "Options" et configurez la zone de recherche. Désormais, la valeur souhaitée est recherchée même dans les formules utilisées dans les cellules de calcul. Nous devons spécifier la recherche uniquement en valeurs et, si nous le souhaitons, nous pouvons également spécifier le format des données recherchées.

Lors de la recherche de mots dans un tableau Excel, vous devez également prendre en compte toutes ces subtilités et, par exemple, vous pouvez même prendre en compte la casse des lettres.

Et enfin, voyons comment rechercher des données dans Excel uniquement dans la zone requise de la feuille. Comme le montre notre exemple, la valeur souhaitée "dix" se produit dans toutes les colonnes de données à la fois. Si vous avez besoin de trouver cette valeur, par exemple uniquement dans la première colonne, vous devez sélectionner cette colonne ou toute zone de valeurs dans laquelle vous devez rechercher, puis lancer la recherche.