Salut les amis. À quelle fréquence devez-vous rechercher une correspondance dans un tableau Excel pour une valeur donnée ? Par exemple, vous devez trouver l'adresse d'une personne dans un annuaire ou dans la liste de prix - le prix d'un article. Si de telles tâches sont rencontrées - ce poste est pour vous !

Je fais ces procédures tous les jours et sans les fonctions décrites ci-dessous, j'aurais vraiment eu du mal. Prenez-en note et appliquez-les dans votre travail !

Recherche de table Excel, fonctions RECHERCHEV et RECHERCHEH

Le rôle de ces fonctions dans la vie d'un utilisateur ordinaire ne peut guère être surestimé. Maintenant, vous pouvez facilement trouver un enregistrement approprié dans la table de données et renvoyer la valeur correspondante.

La syntaxe de la fonction RECHERCHEV est : = RECHERCHEV ( Lookup_value ; table_for_search ; numéro_colonne à afficher ; [Type de match]). Considérez les arguments :

Valeur souhaitée- la valeur à rechercher. C'est un argument requis;
Tableau de recherche- le tableau de cellules dans lequel se fera la recherche. La colonne avec les valeurs souhaitées doit être la première de ce tableau. C'est aussi un argument obligatoire ;
Numéro de colonne affiché- le numéro ordinal de la colonne (en commençant par le premier du tableau), à partir duquel la fonction produira des données si les valeurs requises correspondent. Argument requis ;
Type correspondant- sélectionnez "1" (ou "TRUE") pour un match libre, "0" ("FALSE") pour un match complet. L'argument est facultatif, si vous l'omettez, la recherche sera effectuée match lâche.

Trouver une correspondance exacte à l'aide de RECHERCHEV

Regardons un exemple du fonctionnement de la fonction RECHERCHEV lorsque le type de correspondance est "FAUX", 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 localisation. 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 : = RECHERCHEV (B2; B5: E10; 3; FALSE).

Ici, le premier argument indique que la table doit être recherchée pour la valeur de la cellule B2, c'est-à-dire le mot "Télécopieur". Le deuxième argument indique que la table de recherche est dans la plage B5 : E10, et 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 J5 : J10. Le quatrième argument est FAUX, c'est-à-dire une correspondance complète est requise.

Et ainsi, la fonction recevra la ligne "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 renvoie la valeur correspondante de la colonne D, c'est-à-dire contenu de D8. C'est exactement ce dont nous avions besoin, le problème a été résolu.

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

Trouver une correspondance approximative à l'aide de RECHERCHEV

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

Tableau B5 : C12 indique 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 nous souhaitons recevoir le taux en C2 pour une telle transaction. La tâche est difficile car la somme peut être n'importe quoi et il est peu probable qu'elle coïncide avec celles spécifiées dans le tableau ; la recherche par correspondance exacte ne convient pas :

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

Recherche fluide de RECHERCHEV dans Excel

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

La fonction RECHERCHEH a la même syntaxe que RECHERCHEV, mais recherche le résultat non pas dans les colonnes, mais dans les lignes. C'est-à-dire qu'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 BROWSE

La fonction RECHERCHE fonctionne de manière similaire à RECHERCHEV, mais a une syntaxe différente. Je l'utilise lorsque la table de données contient plusieurs dizaines de colonnes et pour utiliser RECHERCHEV, il est nécessaire de calculer en plus le numéro de la colonne de sortie. Dans de tels cas, la fonction RECHERCHE facilite la tâche. Et donc, la syntaxe est : = RECHERCHE ( valeur_recherche ; Array_for_search ; Array_to_ display) :

Valeur souhaitée- des données ou un lien vers des données à rechercher ;
Tableau de recherche- une ligne ou une colonne dans laquelle nous recherchons une valeur similaire. Nous devons trier ce tableau par ordre croissant ;
Tableau à afficher- une plage contenant des données pour l'affichage des résultats. Naturellement, il doit être de la même taille que le tableau de recherche.

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

Fonction RECHERCHE dans Microsoft Excel

Recherche par coordonnées relatives. Fonctions RECHERCHE et INDEX

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

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

Valeur souhaitée- argument obligatoire
Tableau à parcourir- une ligne ou une colonne dans laquelle nous recherchons une correspondance. Argument requis
Type correspondant- spécifiez "0" pour rechercher une correspondance exacte, "1" - le plus proche inférieur, "-1" - le plus proche supérieur. Étant donné que la fonction recherche du début à la fin de la liste, lors de la recherche du plus petit le plus proche, triez la colonne de recherche par ordre décroissant. Et lorsque vous cherchez plus, triez-les par ordre croissant.

La position de la valeur souhaitée a été trouvée, vous pouvez maintenant l'afficher à l'écran à l'aide de la fonction INDICE( Déployer; Numéro de ligne; [numéro_colonne]) :

Déployer- l'argument indique dans quel tableau de cellules sélectionner une valeur
Numéro de ligne- spécifiez le numéro ordinal 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 du calcul d'une autre fonction. Par exemple, RECHERCHE.
Numéro de colonne- argument optionnel, spécifié si le tableau est constitué de plusieurs colonnes. En cas d'omission, la formule utilise la première colonne du tableau.

Maintenant, combinons ces fonctions pour obtenir le résultat :

Fonctions RECHERCHE et INDEX dans Excel

Ce sont les moyens de rechercher et d'afficher des données dans Excel. De plus, vous pouvez les utiliser dans des calculs, les utiliser dans une présentation, effectuer des opérations avec eux, spécifier d'autres fonctions comme arguments, etc.

Avez-vous l'impression que vos connaissances et vos compétences grandissent et se renforcent ? Alors ne vous arrêtez pas, continuez à lire ! Dans le prochain article, nous considérerons : ce sera difficile et intéressant !

Bon après-midi, chers résidents!

De temps en temps, certains (et peut-être plus que certains) d'entre nous doivent s'occuper des tâches de traitement de petites quantités de données, de l'élaboration et de l'analyse d'un budget domestique à la fin des calculs pour le travail, l'école, etc. L'outil le plus approprié pour cela est peut-être Microsoft Excel (ou peut-être ses autres analogues, mais ils sont moins courants).

La recherche ne m'a donné qu'un seul article sur Habré sur un sujet similaire - "Talmud par formules dans Google SpreadSheet". Il donne une bonne description des éléments de base pour travailler dans Excel (bien qu'il ne s'agisse pas à 100% d'Excel lui-même).

Ainsi, après avoir accumulé un certain pool de demandes/tâches, l'idée est apparue de les taper et de proposer des solutions possibles (quoique pas toutes possibles, mais donnant rapidement des résultats).

Il se concentrera sur la résolution des tâches les plus courantes rencontrées par les utilisateurs.

La description des solutions est structurée comme suit - un cas est donné contenant la tâche initiale, qui devient progressivement plus compliquée, une solution détaillée avec des explications est donnée pour chaque étape. Les noms des fonctions seront donnés en russe, mais le nom original en anglais sera donné entre parenthèses à la première mention (puisque, selon l'expérience, l'écrasante majorité des utilisateurs ont installé la version russe).

Case_1 : Fonctions logiques et de correspondance
"J'ai un ensemble de valeurs dans la plaque et il faut que lorsqu'une certaine condition/ensemble de conditions sont remplies, une certaine valeur s'affiche" (c) Utilisateur

Les données sont généralement présentées sous forme de tableau :

État:

si la valeur de la colonne "Quantité" est supérieure à 5,
alors vous devez afficher dans la colonne "Résultat" la valeur "La commande n'est pas requise",

La formule "SI" (SI) nous y aidera, qui fait référence à des formules logiques et peut donner toutes les valeurs de la solution que nous écrivons à l'avance dans la formule. Veuillez noter que toutes les valeurs de texte sont écrites à l'aide de guillemets.

La syntaxe de la formule est la suivante :
SI (logical_test, [value_if_true], [value_if_false])

Log_expression est une expression qui donne TRUE ou FALSE.
Value_if_true - la valeur qui est sortie si l'expression booléenne est vraie
Value_if_false - la valeur qui est affichée si l'expression booléenne est fausse

Syntaxe de la formule de solution :

= SI (C5> 5; "Aucune commande requise"; "Commande requise")

En sortie, on obtient le résultat :

Il arrive que la condition soit plus complexe, par exemple, la réalisation de 2 conditions ou plus :

si la valeur dans la colonne "Quantité" est supérieure à 5, et la valeur dans la colonne "Type" est "A"

Dans ce cas, on ne peut plus se limiter à n'utiliser qu'une seule formule "SI", il faut ajouter une autre formule à sa syntaxe. Et ce sera une autre formule ET logique.
La syntaxe de la formule est la suivante :
ET (booléen1, [booléen2], ...)

Boolean_value1-2, etc. - une condition vérifiée dont le calcul donne la valeur VRAI ou FAUX

Sortie du résultat dans la cellule D2 :

= SI (ET (C2> 5; B2 = "A"); 1; 0)

Ainsi, en utilisant une combinaison de 2 formules, nous trouvons une solution à notre problème et obtenons le résultat :

Essayons de compliquer la tâche - une nouvelle condition :

si la valeur dans la colonne "Quantité" est 10, et la valeur dans la colonne "Type" est "A"
ou la valeur de la colonne "Quantité" est supérieure ou égale à 5, et la valeur "Type" est "B"
il faut alors afficher dans la colonne "Résultat" la valeur "1", sinon "0".

La syntaxe de la solution sera la suivante :
Sortie du résultat dans la cellule D2 :

= SI (OU (ET (C2 = 10; B2 = "A"); ET (C2> = 5; B2 = "B")); 1; 0)

Comme vous pouvez le voir dans l'entrée, la formule SI comprend une condition OU et deux conditions utilisant la formule ET incluse. Si au moins une des conditions du 2ème niveau a la valeur "VRAI", alors le résultat "1" sera affiché dans la colonne "Résultat", sinon ce sera "0".
Résultat:

Passons maintenant à la situation suivante :
Imaginez que, selon la valeur dans la colonne "Condition", une certaine condition devrait être affichée dans la colonne "Résultat", ci-dessous se trouve la correspondance entre les valeurs et le résultat.
État:

1 = A
2 = B
3 = B
4 = G

Lors de la résolution d'un problème à l'aide de la fonction "SI", la syntaxe sera la suivante :

= SI (A2 = 1; "A"; SI (A2 = 2; "B"; SI (A2 = 3; "C"; SI (A2 = 4; "D"; 0))))

Résultat:

Comme vous pouvez le voir, l'écriture d'une telle formule est non seulement gênante et lourde, mais cela peut prendre un certain temps à un utilisateur inexpérimenté pour la modifier en cas d'erreur.
L'inconvénient de cette approche est qu'elle est applicable pour un petit nombre de conditions, car toutes devront être saisies manuellement et "gonfler" notre formule à de grandes tailles, mais l'approche se distingue par des valeurs "omnivores" complètes et l'universalité d'utilisation.

Solution alternative_1 :
En utilisant la formule CHOISIR,
Syntaxe de la fonction :
CHOIX (index_num, valeur1, [valeur2], ...)

Index_num est le numéro de l'argument de valeur à sélectionner. Le numéro d'index doit être un nombre compris entre 1 et 254, une formule ou une référence de cellule contenant un nombre compris entre 1 et 254.
Valeur1, valeur2,… - une valeur de 1 à 254 arguments de valeur à partir de laquelle la fonction SELECTE sélectionne une valeur ou une action à effectuer à l'aide du numéro d'index. Les arguments peuvent être des nombres, des références de cellules, des noms spécifiques, des formules, des fonctions ou du texte.

Lors de son utilisation, nous entrons immédiatement les résultats des conditions en fonction des valeurs spécifiées.
État:

1 = A
2 = B
3 = B
4 = G

Syntaxe de la formule :

= CHOIX (A2; "A"; "B"; "C"; "D")

Le résultat est le même que la solution de chaînage IF ci-dessus.
Les restrictions suivantes s'appliquent lors de l'application de cette formule :
Seuls des nombres peuvent être saisis dans la cellule "A2" (numéro d'index), et les valeurs de résultat seront affichées dans l'ordre croissant de 1 à 254 valeurs.
En d'autres termes, la fonction ne fonctionnera que si les nombres de 1 à 254 dans l'ordre croissant sont spécifiés dans la cellule "A2" et cela impose certaines restrictions lors de l'utilisation de cette formule.
Ceux. si nous voulons que la valeur de "G" soit affichée lors de la spécification du nombre 5,

1 = A
2 = B
3 = B
5 = G

alors la formule aura la syntaxe suivante :
Sortie du résultat dans la cellule B2 :

= CHOIX (A31; "A"; "B"; "C" ;; "D")

Comme vous pouvez le voir, nous devons laisser la valeur "4" dans la formule vide et transférer le résultat "G" au nombre ordinal "5".

Solution alternative_2 :
Nous arrivons ici à l'une des fonctions Excel les plus populaires, la maîtrise qui transforme automatiquement tout employé de bureau en un "utilisateur Excel expérimenté" / sarcasme /.
Syntaxe de la formule :
RECHERCHEV (lookup_value, table, column_number, [range_view])

Lookup_value est la valeur recherchée par la fonction.
Le tableau est une plage de cellules contenant des données. C'est dans ces cellules que s'effectuera la recherche. Les valeurs peuvent être textuelles, numériques ou logiques.
Column_number est le numéro de la colonne dans l'argument "Table" à partir de laquelle la valeur sera sortie en cas de correspondance. Il est important de comprendre que les colonnes ne sont pas comptées le long de la grille générale de la feuille (A.B, C, D, etc.), mais à l'intérieur du tableau spécifié dans l'argument "Table".
Range_lookup - Détermine si la fonction doit trouver une correspondance exacte ou approximative.

Important: la fonction "RECHERCHEV" recherche une correspondance uniquement par le premier enregistrement unique, si la valeur_désirée est présente plusieurs fois dans l'argument "Table" et a des valeurs différentes, alors la fonction "RECHERCHEV" ne trouvera que la PREMIÈRE correspondance, les résultats pour toutes les autres correspondances ne seront pas affichées. L'utilisation de la formule "RECHERCHEV" (RECHERCHEV) est associée à une autre approche du travail avec les données, à savoir la formation de "livres de référence".
L'essence de l'approche est de créer un "livre de référence" pour faire correspondre l'argument "Seek_value" à un certain résultat, séparément du tableau principal, dans lequel les conditions et leurs valeurs correspondantes sont écrites :

Ensuite, dans la partie de travail du tableau, une formule est déjà écrite avec un lien vers l'ouvrage de référence renseigné précédemment. Ceux. dans l'ouvrage de référence dans la colonne "D", la valeur est recherchée dans la colonne "A" et, lorsqu'une correspondance est trouvée, la valeur de la colonne "E" est affichée dans la colonne "B".
Syntaxe de la formule :
Sortie du résultat dans la cellule B2 :

Résultat:

Imaginons maintenant une situation où il est nécessaire d'extraire des données d'une table à partir d'une autre, alors que les tables ne sont pas identiques. Voir exemple ci-dessous

On peut voir que les lignes des colonnes "Produit" des deux tables ne correspondent pas, cependant, cela n'est pas un obstacle à l'utilisation de la fonction "RECHERCHEV".
Sortie du résultat dans la cellule B2 :

Mais lors de la résolution, nous sommes confrontés à un nouveau problème - lorsque nous "étirons" la formule que nous avons écrite à droite de la colonne "B" à la colonne "E", nous devrons remplacer manuellement l'argument "column_number". C'est une tâche laborieuse et ingrate, par conséquent, une autre fonction vient à notre aide - "COLONNE" (COLONNE).
Syntaxe de la fonction :
COLONNE ([lien])

La référence est la cellule ou la plage de cellules pour laquelle vous souhaitez renvoyer le numéro de colonne.

Si vous utilisez un enregistrement comme :

alors la fonction affichera le numéro de la colonne courante (dans la cellule de laquelle la formule est écrite).
Le résultat est un nombre qui peut être utilisé dans la fonction "RECHERCHEV", que nous utiliserons et obtiendrons l'entrée de formule suivante :
Sortie du résultat dans la cellule B2 :

= RECHERCHEV ($ A3; $ H $ 3: $ M $ 6; COLONNE (); 0)

La fonction COLUMN déterminera le numéro de la colonne actuelle, qui sera utilisé par l'argument Column_num pour déterminer le numéro de la colonne de recherche dans la recherche.
De plus, vous pouvez utiliser la construction :

Au lieu du nombre "1", vous pouvez utiliser n'importe quel nombre (et non seulement le soustraire, mais aussi l'ajouter à la valeur résultante), pour obtenir le résultat souhaité, si vous ne souhaitez pas faire référence à une cellule spécifique dans le colonne avec le nombre dont nous avons besoin.
Le résultat obtenu est :

Nous continuons à développer le sujet et compliquons la condition : imaginons que nous ayons deux répertoires avec des données différentes sur les produits et qu'il soit nécessaire d'afficher les valeurs dans le tableau avec le résultat, selon quel type de répertoire est indiqué dans le Colonne "Répertoire"
État:

Si le numéro 1 est indiqué dans la colonne "Répertoire", les données doivent être extraites de la table "Répertoire_1", si le numéro est 2, puis de la table "Répertoire_2" conformément au mois spécifié

La solution qui vient immédiatement à l'esprit est la suivante :

= SI ($ B3 = 1; RECHERCHEV ($ A3; $ G $ 3: $ I $ 6; COLONNE () - 1; 0); RECHERCHEV ($ A3; $ K $ 3: $ M $ 6; COLONNE () - 1; 0 ))

avantages: le nom de l'ouvrage de référence peut être n'importe quoi (texte, chiffres et leur combinaison), par contre - mal adapté s'il y a plus de 3 options.
Si les numéros d'annuaire sont toujours des numéros, il est logique d'utiliser la solution suivante :
Sortie du résultat dans la cellule C3 :

= RECHERCHEV ($ A3; CHOIX ($ B3; $ G $ 3: $ I $ 6; $ K $ 3: $ M $ 6); COLONNE () - 1; 0)

avantages: la formule peut comprendre jusqu'à 254 noms d'ouvrages de référence, par contre - leur nom doit être strictement numérique.
Le résultat de la formule à l'aide de la fonction SELECT :

Bonus : RECHERCHEV pour deux fonctionnalités ou plus dans l'argument "lookup_value".
État:

Imaginez que nous ayons, comme toujours, un tableau de données sous forme de tableau (sinon, nous y apportons des données), à partir du tableau, selon certains critères, il est nécessaire d'obtenir des valeurs et de les placer dans un autre tableau forme.

Les deux tableaux sont présentés ci-dessous :

Comme le montrent les formulaires tabulaires, chaque position a non seulement un nom (qui n'est pas unique), mais appartient également à une certaine classe et a sa propre option de conditionnement.
En utilisant une combinaison de nom, de classe et d'emballage, nous pouvons créer un nouvel attribut. Pour cela, nous créons une colonne supplémentaire "Attribut supplémentaire" dans le tableau avec les données, que nous remplissons à l'aide de la formule suivante :

En utilisant le symbole "&", nous combinons trois signes en un seul (le séparateur entre les mots peut être n'importe lequel, ou ne pas l'être du tout, l'essentiel est d'utiliser une règle similaire pour la recherche)
Un analogue de la formule peut être la fonction CONCATENATE, dans ce cas elle ressemblera à ceci :

= CONCATENER (H3; "_"; I3; "_"; J3)

Une fois qu'une fonctionnalité supplémentaire est créée pour chaque enregistrement de la table avec des données, nous procédons à l'écriture d'une fonction de recherche pour cette fonctionnalité, qui ressemblera à :
Sortie du résultat dans la cellule D3 :

= IFERREUR (RECHERCHEV (A2 & "_" & B2 & "_" $ G $ 2: $ K $ 6; 5; 0); 0)

Dans la fonction "RECHERCHEV" comme argument "valeur_recherchée", nous utilisons le même groupe de trois attributs (nom_classe_emballage), mais nous le prenons déjà dans la table pour le remplissage et l'entrons directement dans l'argument (en option, on pourrait sélectionner le valeur de l'argument dans une colonne supplémentaire du tableau à remplir, mais cette action serait exagérée).
Permettez-moi de vous rappeler que l'utilisation de la fonction SIERREUR est nécessaire si la valeur souhaitée n'est toujours pas trouvée, et la fonction RECHERCHEV affichera la valeur "# N / A" (plus de détails ci-dessous).
Le résultat est dans l'image ci-dessous :

Cette technique peut être utilisée pour un plus grand nombre de fonctionnalités, la seule condition est l'unicité des combinaisons résultantes, si elle n'est pas respectée, le résultat sera incorrect.

Case_3 Trouver une valeur dans un tableau, ou lorsque RECHERCHEV ne peut pas nous aider

Considérons une situation où il est nécessaire de comprendre si le tableau de cellules contient les valeurs dont nous avons besoin.
Une tâche:

une valeur est indiquée dans la colonne "Condition de recherche" et il faut déterminer si elle est présente dans la colonne "Tableau de recherche"

Visuellement, tout ressemble à ça :

Comme on le voit, la fonction RECHERCHEV est ici impuissante, puisque nous ne recherchons pas une correspondance exacte, mais la présence dans la cellule de la valeur dont nous avons besoin.
Pour résoudre le problème, vous devez utiliser une combinaison de plusieurs fonctions, à savoir :
"SI"
"ESLIOSHIBKA"
"LIGNE"
"TROUVER"

Dans l'ordre à propos de tout, "SI" que nous avons déjà trié plus tôt, passons donc à la fonction "IFERREUR"

SIERREUR (valeur, valeur_sur_erreur)

La valeur est l'argument à vérifier pour les erreurs.
Error_value est la valeur renvoyée en cas d'erreur lors de l'évaluation d'une formule. Les types d'erreurs suivants sont possibles : # N/A, #VALUE !, #REF !, # DIV/0 !, #NUM !, #NAME? et # VIDE !.

Important : cette formule est presque toujours requise lorsque l'on travaille avec des tableaux d'informations et des ouvrages de référence, car il arrive souvent que la valeur requise ne soit pas dans la recherche, et dans ce cas la fonction renvoie une erreur. Si une erreur est affichée dans une cellule et que la cellule est impliquée, par exemple, dans un calcul, elle échouera également avec une erreur. De plus, les cellules où la formule a renvoyé une erreur peuvent se voir attribuer des valeurs différentes, ce qui facilite leur agrégation. De plus, en cas d'erreur, vous pouvez exécuter d'autres fonctions, ce qui est très pratique lorsque vous travaillez avec des tableaux et vous permet de construire des formules en tenant compte de conditions plutôt branchées.

INFÉRIEUR

Texte - texte converti en minuscules.

Important : la fonction LOWER ne remplace pas les caractères non-lettres.
Rôle dans la formule : étant donné que la fonction FIND recherche du texte sensible à la casse, il est nécessaire de ramener le texte entier à une seule casse, sinon "thé" n'équivaudra pas à "thé", et ainsi de suite. Ceci est pertinent si la valeur du registre n'est pas une condition pour la recherche et la sélection de valeurs, sinon la formule "LOWER" ne peut pas être utilisée, donc la recherche sera plus précise.

Maintenant plus sur la syntaxe de la fonction FIND.

FIND (search_text, crawled_text, [start_position])

Search_text est le texte à rechercher.
Searchable_text est le texte dans lequel vous voulez trouver le texte que vous recherchez.
Start_position est le caractère à partir duquel démarrer la recherche. Le premier caractère de "texte_vu" est le numéro 1. Si aucun numéro n'est spécifié, la valeur par défaut est 1.

La syntaxe de la formule de solution sera :
Sortie du résultat dans la cellule B2 :

= SI (SI ERREUR (TROUVEZ (INFÉRIEUR (A2) ; INFÉRIEUR (E2) ; 1 ); 0) = 0 ; « échouer ; « bingo ! »)

Analysons la logique de la formule par actions :

LOWER (A2) - Convertit l'argument "Lookup_text" dans une cellule de A2 en texte minuscule
La fonction FIND commence à rechercher l'argument transformé "Lookup_text" dans le tableau "Look-up_text", qui est converti par la fonction "LOWER (E2)", également en texte minuscule.
Si, la fonction trouve une correspondance, c'est-à-dire renvoie le numéro ordinal du premier caractère du mot / de la valeur correspondant, la condition VRAI dans la formule "SI" est déclenchée, car la valeur résultante n'est pas nulle. En conséquence, la colonne "Résultat" affichera la valeur "Bingo!"
Si, toutefois, la fonction ne trouve pas de correspondance, c'est-à-dire le numéro ordinal du premier caractère du mot / valeur correspondant n'est pas spécifié et au lieu de la valeur une erreur est renvoyée, la condition dans la formule "IFERROR" est déclenchée et la valeur égale à "0" est renvoyée, ce qui correspond à la condition FAUX dans la formule "SI", puisque la valeur résultante est "0". En conséquence, la valeur « échec » sera affichée dans la colonne « Résultat ».

Comme vous pouvez le voir sur l'image ci-dessus, grâce aux fonctions "LOWER" et "FIND", on trouve les valeurs souhaitées quels que soient la casse et l'emplacement dans la cellule, mais il faut faire attention à la ligne 5.
Le terme de recherche est défini sur "111" mais le tableau de recherche indique "1111111 cookies" mais la formule renvoie "Bingo!" Cela se produit parce que la valeur "111" est incluse dans la plage de valeurs "1111111", par conséquent, une correspondance est trouvée. Sinon, cette condition ne fonctionnera pas.

Case_4 Recherche d'une valeur dans un tableau par plusieurs conditions, ou lorsque le VLOOKUP est encore plus incapable de nous aider

Imaginez une situation où vous devez rechercher une valeur du "Tableau avec le résultat" dans le tableau à deux dimensions "Référence" par plusieurs conditions, à savoir, par la valeur "Nom" et "Mois".
La forme tabulaire de la tâche ressemblera à ceci :

État:

Dans le tableau avec le résultat, il est nécessaire de resserrer les données conformément à la coïncidence des conditions "Nom" et "Mois".

Pour résoudre un tel problème, une combinaison des fonctions "INDEX" et "RECHERCHE" convient

Syntaxe de la fonction INDEX

INDEX (tableau, line_num, [column_num])

Tableau - une plage de cellules à partir de laquelle les valeurs seront affichées si leurs conditions de recherche correspondent.
Si le tableau ne contient qu'une seule ligne ou une seule colonne, row_num ou column_num est facultatif, respectivement.
Si le tableau occupe plus d'une ligne et une colonne et qu'un seul des arguments "row_num" et "column_number" est spécifié, la fonction "INDEX" renvoie un tableau composé d'une ligne entière ou d'une colonne entière du "tableau " argument.
Line_number est le numéro de la ligne du tableau à partir de laquelle vous souhaitez renvoyer une valeur.
Column_number est le numéro de la colonne du tableau à partir de laquelle vous souhaitez renvoyer une valeur.

En d'autres termes, la fonction renvoie à partir du tableau spécifié dans l'argument Array la valeur qui se trouve à l'intersection des coordonnées spécifiées dans les arguments Row_num et Column_num.

Syntaxe de la fonction MATCH

MATCH (lookup_value, lookup_array, [match_type])

Lookup_value est la valeur qui correspond aux valeurs de lookup_array. Lookup_value peut être une valeur (nombre, texte ou booléen) ou une référence à une cellule contenant une telle valeur.
Lookup_array est la plage de cellules à rechercher.
Collation_type est un argument facultatif. Le nombre est -1, 0 ou 1.

La fonction MATCH recherche un élément spécifié dans une plage de cellules et renvoie la position relative de cet élément dans la plage.
L'essence de l'utilisation de la combinaison des fonctions "INDEX" et "RECHERCHE" est que nous recherchons les coordonnées des valeurs par leur nom le long des "axes de coordonnées".
L'axe Y sera la colonne Nom et l'axe X sera la ligne Mois.

Une partie de la formule :

RECHERCHE ($ A4; $ I $ 4: $ I $ 7; 0)

renvoie le nombre sur l'axe Y, dans ce cas il sera égal à 1, puisque la valeur "A" est présente dans la plage souhaitée et a une position relative de "1" dans cette plage.
partie de la formule :

RECHERCHE (B $ 3; $ J $ 3: $ L $ 3; 0)

renvoie # N/A car la valeur "1" n'est pas présente dans la plage de visualisation.

Ainsi, nous avons obtenu les coordonnées du point (1; # N / A) que la fonction "INDEX" utilise pour rechercher dans l'argument "Array".
La fonction entièrement écrite pour la cellule B4 ressemblera à ceci :

= INDEX ($ J $ 4: $ L $ 7; RECHERCHE ($ A4; $ I $ 4: $ I $ 7,0); RECHERCHE (B $ 3; $ J $ 3: $ L $ 3,0))

En fait, si nous connaissions les coordonnées de la valeur dont nous avons besoin, la fonction ressemblerait à ceci :

= INDICE ($ J $ 4: $ L $ 7; 1; # N/A))

Étant donné que l'argument Column_num est # N / A, le résultat de la cellule B4 sera approprié.
Comme vous pouvez le voir sur le résultat, toutes les valeurs du tableau avec le résultat ne correspondent pas à la référence, et par conséquent, nous voyons que certaines des valeurs du tableau sont affichées comme "# N / A" , ce qui rend difficile l'utilisation des données pour des calculs ultérieurs.
Résultat:

Pour neutraliser cet effet négatif, nous utilisons la fonction "IFERROR", dont nous avons parlé plus tôt, et remplaçons la valeur renvoyée en cas d'erreur par "0", puis la formule ressemblera à ceci :

= IFERREUR (INDICE ($ J $ 4: $ L $ 7; RECHERCHE ($ A4; $ I $ 4: $ I $ 7,0); RECHERCHE (B $ 3; $ J $ 3: $ L $ 3,0 )); 0)

Démonstration du résultat :

Comme vous pouvez le voir sur l'image, les valeurs "# N/A" n'interfèrent plus avec nos calculs ultérieurs en utilisant les valeurs du tableau avec le résultat.

Case_5 Recherche d'une valeur dans une plage de nombres

Imaginez que nous devions donner un certain signe à des nombres qui se situent dans une certaine plage.
État:
Selon la valeur du produit, une certaine catégorie doit lui être attribuée.
Si la valeur est dans la plage

0 à 1000 = A
1001 à 1500 = B
1501 à 2000 = V
2001 à 2500 = Oui
Plus de 2501 = D

La fonction LOOKUP renvoie une valeur à partir d'une ligne, d'une colonne ou d'un tableau. La fonction a deux formes syntaxiques : vecteur et tableau.

RECHERCHE (lookup_value, lookup_vector, [result_vector])

Lookup_value est la valeur recherchée par LOOKUP dans le premier vecteur. Lookup_value peut être un nombre, un texte, un booléen, un nom ou une référence de valeur.
Lookup_vector est une plage d'une ligne ou d'une colonne. Les valeurs dans lookup_vector peuvent être du texte, des nombres ou des valeurs booléennes.
Les valeurs dans lookup_vector doivent être dans l'ordre croissant : ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE ; sinon, LOOKUP peut renvoyer un résultat incorrect. Les textes en minuscules et en majuscules sont considérés comme équivalents.
Result_Vector est une plage de ligne ou de colonne unique. Le result_vector doit être de la même taille que le view_vector.

= RECHERCHE (E3, $ A $ 3: $ A $ 7, $ B $ 3: $ B $ 7 $)

Les arguments "Viewed_vector" et "Result_Vector" peuvent être écrits sous la forme d'un tableau - dans ce cas, vous n'avez pas besoin de les afficher dans un tableau séparé sur la feuille Excel.
Dans ce cas, la fonction ressemblera à ceci :
Sortie du résultat dans la cellule B3 :
= RECHERCHE (E3; (0; 1001; 1501; 2001; 2501) ; ("A"; "B"; "C"; "D"; "D"))

Case_6 Somme des nombres par attributs

Trois fonctions différentes peuvent être utilisées pour additionner des nombres en fonction de certains critères :
SUMIF - résume un seul attribut
SUMIFS - Sommes sur plusieurs caractéristiques
SUMPRODUCT - Sommes sur plusieurs fonctionnalités
Il existe également une option utilisant SUM et une fonction de formule matricielle, où la formule SUM est élevée en un tableau :
((= SOMME (() * ()))
mais cette approche est plutôt gênante et recouvre complètement la fonctionnalité par la formule "SUMPRODUCT"
Maintenant, plus en détail sur la syntaxe "SUMPRODUCT":

SOMMEPROD (tableau1, [tableau2], [tableau3], ...)

Array1 est le premier tableau dont vous souhaitez multiplier les composants, puis ajouter les résultats.
Array2, array3 ... - de 2 à 255 tableaux dont les composantes doivent être multipliées puis additionnées.

État:

Trouvez le montant total de la valeur des expéditions pour chacun des produits pour une certaine période :

Comme vous pouvez le voir dans le tableau de données, pour calculer le coût, le prix doit être multiplié par la quantité et la valeur résultante, en appliquant les conditions de sélection, doit être transférée dans le tableau avec le résultat.
Cependant, la formule "SUMPROIZ" permet d'effectuer de tels calculs au sein de la formule.
Sortie du résultat dans la cellule B4 :

= SOMMEPROD (($ A4 = $ H $ 3 : $ H $ 11) * ($ K $ 3 : $ K $ 11> = B $ 3) * ($ K $ 3 : $ K $ 11 $
Analysons la formule morceau par morceau :
- définir la condition de sélection dans la colonne "Nom" du tableau avec les données sur la colonne "Nom" dans le tableau avec le résultat
(K$ 3 : K$ 11$> = 3 G$) * (K$ 3 : K$ 11$) - définir la condition de la plage horaire, la date est supérieure ou égale au premier jour du mois en cours, mais inférieure au premier jour du mois suivant. De même, la condition est dans le tableau avec le résultat, le tableau est dans le tableau avec les données.
- nous multiplions les colonnes "Quantité" et "Prix" dans le tableau avec les données.
L'avantage incontestable de cette fonction est l'ordre libre des conditions d'écriture, elles peuvent être écrites dans n'importe quel ordre, cela n'affectera pas le résultat.
Résultat:
Compliquons maintenant la condition et ajoutons l'exigence que la sélection par le nom du "cookie" n'ait lieu que par les classes "petit" et "grand", et par le nom du "roll" tout, sauf le classe "avec confiture":
Sortie du résultat dans la cellule B4 :
= SOMMEPROD (($ A4 = $ H $ 3 : $ H $ 11) * ($ J $ 3 : $ J 11 $> = B $ 3) * ($ J $ 3 : $ J 11 $
Une nouvelle condition a été ajoutée à la formule de sélection des cookies :
(($ I $ 3: $ I $ 11 = "petit") + ($ I $ 3: $ I $ 11 = "grand"))
- comme vous pouvez le voir, deux ou plusieurs conditions dans une colonne sont mises en évidence dans un groupe séparé à l'aide du symbole "+" et en entourant les conditions entre parenthèses supplémentaires.
Une nouvelle condition a également été ajoutée à la formule de sélection des brioches :
= SOMMEPROD (($ A5 = $ H $ 3 : $ H $ 11) * ($ J $ 3 : $ J 11 $> = B $ 3) * ($ J $ 3 : $ J 11 $ "Avec confiture"); ($ L$ 3 : $ L 11$) * (K$ 3 : K$ 11$))

Ce:
($ I $ 3 : $ I $ 11<>"Avec confiture")
- en effet, dans cette formule il était possible d'écrire la condition de sélection ainsi que dans la sélection par cookies, mais alors, il faudrait lister trois conditions dans la formule, dans ce cas, il est plus facile d'écrire une exception - pas égal à "avec confiture" pour cela nous utilisons la valeur "<>».
En général, si des groupes de caractéristiques/classes sont connus à l'avance, il est alors préférable de les combiner dans ces groupes, en créant des ouvrages de référence, que d'écrire toutes les conditions dans une fonction, en la gonflant.
Résultat:
Bon, nous arrivons ici à la fin de notre petit manuel, qui en fait aurait pu être beaucoup plus volumineux, mais le but était quand même de donner une solution aux situations les plus courantes, et non de décrire la solution de cas particuliers (mais bien plus intéressants ).
J'espère que le manuel aidera quelqu'un à résoudre des problèmes avec Excel, car cela signifiera que mon travail n'a pas été vain !
Merci pour votre temps!

Supposons que votre rapport contienne un tableau avec beaucoup de données et de nombreuses colonnes. Il est extrêmement difficile d'effectuer une analyse visuelle de tels tableaux. Et l'une des tâches pour travailler avec le rapport est - l'analyse des données par rapport aux en-têtes des lignes et des colonnes relatives à un mois spécifique. À première vue, c'est une tâche très simple, mais elle ne peut pas être résolue à l'aide d'une fonction standard. Oui, bien sûr vous pouvez utiliser l'outil : "ACCUEIL" - "Edition" - "Rechercher" CTRL + F pour ouvrir la fenêtre de recherche de valeurs dans la feuille Excel. Ou créez une règle de mise en forme conditionnelle pour le tableau. Mais alors il ne sera pas possible d'effectuer d'autres calculs avec les résultats obtenus. Par conséquent, il est nécessaire de créer et d'appliquer correctement la formule appropriée.

Trouver une valeur dans un tableau Excel

Le schéma pour résoudre le problème ressemble à ceci:

dans la cellule B1, nous entrerons les données qui nous intéressent ;

la cellule B2 affichera l'en-tête de colonne qui contient la valeur de la cellule B1

la cellule B3 affichera le nom de la ligne qui contient la valeur de la cellule B1.

En fait, vous devez rechercher des coordonnées dans Excel. Pourquoi est-ce? Assez souvent, nous avons besoin d'obtenir les coordonnées d'un tableau par valeur. Un peu comme l'analyse inverse de la matrice. Un exemple concret en un mot ressemble à ceci. L'objectif en nombre est la valeur initiale, vous devez déterminer qui et quand est le plus proche de cet objectif. À titre d'exemple, nous utiliserons une simple matrice de données indiquant le nombre d'articles vendus pendant les trois quarts, comme le montre la figure ci-dessous. Il est important que tous les chiffres soient cohérents. Si vous ne souhaitez pas créer et remplir manuellement une feuille de calcul Excel à partir de zéro, vous pouvez télécharger un exemple prêt à l'emploi à la fin de l'article.

Nous examinerons séquentiellement des solutions de complexité variable et, à la fin de l'article, le résultat final.

Trouver une valeur dans une colonne Excel

Tout d'abord, apprenons à obtenir les en-têtes des colonnes du tableau par valeur. Pour le faire, suivez ces étapes:

Dans la cellule B1, entrez la valeur tirée du tableau 5277 et surlignez son arrière-plan en bleu pour la lisibilité du champ de saisie (nous entrerons ensuite d'autres nombres dans la cellule B1 pour expérimenter de nouvelles valeurs).

Dans la cellule C2, entrez la formule pour obtenir l'en-tête de la colonne du tableau qui contient cette valeur :

Après avoir entré la formule pour confirmation, appuyez sur la combinaison de touches CTRL + MAJ + Entrée, car la formule doit être exécutée dans un tableau. Si tout est fait correctement, des accolades () apparaîtront sur les bords de la barre de formule.

Trouver une valeur dans une chaîne Excel

Maintenant, nous obtenons le numéro de ligne pour la même valeur (5277). Pour ce faire, entrez la formule suivante dans la cellule C3 :

Après avoir entré la formule pour confirmation, appuyez à nouveau sur la combinaison de touches CTRL + MAJ + Entrée et obtenez le résultat :

La formule a renvoyé le nombre 9 - elle a trouvé le titre de la ligne de feuille par la valeur correspondante dans le tableau. En conséquence, nous avons l'adresse complète de la valeur D9.

Comment obtenir l'en-tête de colonne et le nom de ligne d'un tableau

Apprenons maintenant à obtenir les coordonnées non pas de la feuille entière, mais de la table actuelle par valeur. En un mot, il faut retrouver les entêtes par valeur 5277 au lieu de D9 :

pour une colonne de tableau - mars ;

pour la ligne - Item4.

Pour résoudre ce problème, nous utiliserons une formule avec les valeurs déjà obtenues dans les cellules C2 et C3. Pour ce faire, nous faisons ceci :

En conséquence, les coordonnées internes du tableau sont obtenues par valeur - mars; Article 4 :

Recherche de valeurs en double dans une plage Excel

Pour contrôler la présence de doublons parmi les valeurs du tableau, nous allons créer une formule qui pourra nous informer de la présence de doublons et compter leur nombre. Pour ce faire, saisissez la formule dans la cellule E2 :

De plus, pour la plage de la section tabulaire, créons une règle de mise en forme conditionnelle :

Comme vous pouvez le voir, en présence de doublons, la formule des en-têtes prend l'en-tête du premier doublon horizontalement (de gauche à droite). Et la formule pour obtenir le nom (numéro) de la ligne prend le numéro du premier doublon verticalement (de haut en bas). Il y a 2 façons de corriger cette solution :

Dans ce cas, on change les formules soit l'une soit l'autre, mais pas deux à la fois. Il convient de rappeler que l'ancienne formule doit rester dans la cellule C3 :

Ici les coordonnées du premier duplicata sont affichées verticalement (de haut en bas) - I7 pour la feuille et August ; Produit2 pour la table. Laissons cette option pour le prochain exemple final.

Trouver la valeur la plus proche dans une plage Excel

Ce tableau n'est toujours pas parfait. Après tout, lors de l'analyse, vous devez connaître exactement toutes ses valeurs. Si la formule ne trouve pas le nombre entré dans la cellule B1 du tableau, une erreur est renvoyée - #VALUE ! L'idéal serait que la formule, en l'absence du nombre initial dans le tableau, sélectionne elle-même la valeur la plus proche que contient le tableau. Pour créer un tel programme d'analyse des tableaux dans la cellule F1, entrez une nouvelle formule :

Suit alors dans toutes les autres formules, changez le lien au lieu de B1 devrait être F1 ! Vous devez également modifier le lien dans la mise en forme conditionnelle. Sélectionnez : ACCUEIL - Styles - Mise en forme conditionnelle - Gérer les règles - Modifier la règle. Et ici, dans les paramètres, spécifiez F1 au lieu de B1. Pour vérifier le fonctionnement du programme, entrez dans la cellule B1 un nombre qui n'est pas dans le tableau, par exemple : 8000. Cela conduira au résultat final :

Vous pouvez maintenant entrer n'importe quelle valeur initiale et le programme lui-même sélectionnera le nombre le plus proche que contient la table. Ensuite, il affiche l'en-tête de la colonne et le nom de la ligne pour la valeur actuelle. Par exemple, si vous saisissez le nombre 5000, nous obtenons un nouveau résultat :

Notre programme dans Excel a trouvé la valeur la plus proche de 4965 pour la valeur initiale - 5000. Un tel programme peut être utile pour résoudre automatiquement diverses tâches analytiques dans la planification d'entreprise, la définition d'objectifs, la recherche d'une solution rationnelle, etc. Et les lignes et colonnes résultantes vous permettent d'étendre davantage les capacités de calcul de ce type de rapports à l'aide de nouvelles formules Excel.

Ce tutoriel parle des principaux avantages des fonctionnalités INDICE et CHERCHER dans Excel qui les rendent plus attrayants par rapport à RECHERCHEV... Vous verrez plusieurs exemples de formules qui vous aideront à faire face facilement à de nombreuses tâches complexes que la fonction RECHERCHEV impuissant.
Dans plusieurs articles récents, nous nous sommes efforcés d'éduquer les utilisateurs novices sur les bases de la fonction. RECHERCHEV et montrez des exemples de formules plus complexes pour les utilisateurs avancés. Maintenant, nous allons essayer, sinon vous dissuader d'utiliser RECHERCHEV, puis au moins montrer d'autres moyens d'implémenter la recherche verticale dans Excel.

Pourquoi avons nous besoin de ça? - tu demandes. Oui parce que RECHERCHEV Ce n'est pas la seule fonction de recherche dans Excel, et ses nombreuses limitations peuvent vous empêcher d'obtenir les résultats souhaités dans de nombreuses situations. D'autre part, les fonctions INDICE et CHERCHER- sont plus flexibles et présentent un certain nombre de caractéristiques qui les rendent plus attrayants par rapport à RECHERCHEV.

Informations de base sur INDEX et SEARCH

Puisque le but de ce tutoriel est de montrer les capacités des fonctions INDICE et CHERCHER pour implémenter la recherche verticale dans Excel, nous ne nous attarderons pas sur leur syntaxe et leur application.

Nous donnerons ici le minimum nécessaire pour en comprendre l'essence, puis nous analyserons en détail des exemples de formules qui montrent les avantages d'utiliser INDICE et CHERCHERà la place de RECHERCHEV.

INDEX - syntaxe et application de la fonction

Une fonction INDICE(INDEX) dans Excel renvoie une valeur d'un tableau aux numéros de ligne et de colonne donnés. La fonction a la syntaxe suivante :

Chaque argument a une explication très simple :

déployer(tableau) est la plage de cellules à partir de laquelle extraire la valeur.

num_ligne(line_number) est le numéro de la ligne du tableau à partir de laquelle extraire la valeur. S'il n'est pas spécifié, un argument est requis. num_colonne(numéro_colonne).

num_colonne(column_number) est le numéro de la colonne du tableau à partir de laquelle extraire la valeur. S'il n'est pas spécifié, un argument est requis. num_ligne(numéro de ligne)

Si les deux arguments sont spécifiés, la fonction INDICE renvoie la valeur de la cellule à l'intersection de la ligne et de la colonne spécifiées.
Voici l'exemple le plus simple d'une fonction INDICE(INDICE):

INDICE (A1 : C10,2,3)
= INDICE (A1 : C10 ; 2 ; 3)
La formule recherche la plage A1 : C10 et renvoie la valeur de la cellule dans 2e ligne et 3m colonne, c'est-à-dire à partir d'une cellule C2.

Très simple, non ? Cependant, en pratique, vous ne savez pas toujours de quelle ligne et de quelle colonne vous avez besoin, et vous avez donc besoin de l'aide de la fonction CHERCHER.

MATCH - syntaxe et application de fonction

Une fonction CORRESPONDRE(MATCH) dans Excel recherche une valeur spécifiée dans une plage de cellules et renvoie la position relative de cette valeur dans la plage.

Par exemple, si dans la plage B1 : B3 contient des valeurs New-York, Paris, Londres, alors la formule suivante renverra un chiffre 3 car « Londres » est le troisième élément de la liste.

MATCH ("Londres", B1 : B3,0)
= RECHERCHE ("Londres"; B1: B3; 0)
Une fonction CORRESPONDRE(MATCH) a la syntaxe suivante :

MATCH (lookup_value, lookup_array,)
MATCH (lookup_value ; lookup_array ; [match_type])
valeur_recherche(lookup_value) est le nombre ou le texte que vous recherchez. L'argument peut être une valeur, y compris un booléen, ou une référence de cellule.

tableau_recherche(lookup_array) - la plage de cellules dans laquelle rechercher.

Type de match(match_type) - cet argument indique à la fonction CHERCHER, que vous souhaitiez trouver une correspondance exacte ou approximative :
1 ou alors non précisé- trouve la valeur maximale inférieure ou égale à celle souhaitée. La baie analysée doit être dans l'ordre croissant, c'est-à-dire du plus petit au plus grand.

0 - trouve la première valeur égale à celle souhaitée. Pour combinaison INDICE/CHERCHER une correspondance exacte est toujours nécessaire, donc le troisième argument de la fonction CHERCHER devrait être égal 0 .

-1 - Trouve la plus petite valeur supérieure ou égale à la valeur souhaitée. La baie analysée doit être dans l'ordre décroissant, c'est-à-dire du plus grand au plus petit.

À première vue, les avantages de la fonction CHERCHER est dans le doute. Qui a besoin de connaître la position d'un élément dans une plage ? Nous voulons connaître la signification de cet élément !

Permettez-moi de vous rappeler que la position relative de la valeur souhaitée (c'est-à-dire le numéro de ligne et/ou de colonne) est exactement ce que nous devons spécifier pour les arguments. num_ligne(numéro_ligne) et/ou num_colonne fonction (numéro_colonne) INDICE(INDICE). Comme vous vous en souvenez, la fonction INDICE peut renvoyer une valeur à l'intersection de la ligne et de la colonne données, mais il ne peut pas déterminer la ligne et la colonne qui nous intéressent.

Comment utiliser INDEX et SEARCH dans Excel

Maintenant que vous connaissez les informations de base sur ces deux fonctions, je pense qu'il devient déjà clair comment fonctionne CHERCHER et INDICE peuvent travailler ensemble. CHERCHER détermine la position relative de la valeur souhaitée dans une plage donnée de cellules, et INDICE utilise ce nombre (ou ces nombres) et renvoie le résultat de la cellule correspondante.

Pas encore tout à fait clair ? Introduire les fonctions INDICE et CHERCHER sous cette forme :

INDEX (, (ASSOCIATION ( Valeur souhaitée,la colonne dans laquelle nous cherchons,0))
= INDICE ( la colonne à partir de laquelle extraire; (CHERCHER ( Valeur souhaitée;la colonne dans laquelle nous cherchons;0))
Je pense que ce sera encore plus facile à comprendre avec un exemple. Supposons que vous ayez la liste suivante des capitales d'État :

Trouvons la population d'une des capitales, par exemple le Japon, en utilisant la formule suivante :

INDICE ($ D $ 2: $ D $ 10, MATCH ("Japon", $ B $ 2: $ B $ 10,0))
= INDEX ($ D $ 2: $ D $ 10; RECHERCHE ("Japon"; $ B $ 2: $ B $ 10; 0))
Voyons maintenant à quoi sert chaque élément de cette formule :

Une fonction CORRESPONDRE(MATCH) recherche "Japon" dans une colonne B, en particulier - dans les cellules B2 : B10, et renvoie le nombre 3 parce que "Japon" est en troisième place sur la liste.

Une fonction INDICE(INDICE) utilise 3 pour argumenter num_ligne(line_number), qui spécifie la ligne à partir de laquelle renvoyer la valeur. Ceux. on obtient une formule simple :
INDICE ($ D $ 2 : $ D $ 10,3)
= INDICE ($ D $ 2 : $ D $ 10,3)
La formule dit quelque chose comme ceci : rechercher dans les cellules de D2 avant D10 et extraire la valeur de la troisième ligne, c'est-à-dire de la cellule D4 puisque le comptage commence à partir de la deuxième ligne.

Voici le résultat dans Excel :

Important! Le nombre de lignes et de colonnes dans le tableau que la fonction utilise INDICE(INDEX), doit correspondre aux valeurs des arguments num_ligne(numéro_ligne) et num_colonne fonction (numéro_colonne) CORRESPONDRE(CHERCHER). Sinon, le résultat de la formule sera erroné.
Arrêtez, arrêtez ... pourquoi ne pouvons-nous pas simplement utiliser la fonction RECHERCHEV(RECHERCHEV) ? Est-il utile de perdre du temps à essayer de comprendre les labyrinthes CHERCHER et INDICE?

RECHERCHEV ("Japon", $ B $ 2: $ D $ 2,3)
= RECHERCHEV ("Japon"; $ B $ 2: $ D $ 2; 3)
Dans ce cas, cela n'a aucun sens ! Le but de cet exemple est uniquement à des fins de démonstration, afin que vous puissiez comprendre comment les fonctions CHERCHER et INDICE travailler en équipe de deux. Les exemples suivants vous montreront la véritable puissance du bundle. INDICE et CHERCHER qui gère facilement de nombreuses situations difficiles où RECHERCHEV est perplexe.

Pourquoi INDEX / SEARCH est meilleur(e) que RECHERCHEV ?

Au moment de décider quelle formule utiliser pour la recherche verticale, la plupart des gourous d'Excel pensent que INDICE/CHERCHER beaucoup mieux que RECHERCHEV... Cependant, de nombreux utilisateurs d'Excel utilisent encore RECHERCHEV puisque cette fonction est beaucoup plus simple. En effet, très peu de personnes comprennent pleinement les avantages de passer d'une RECHERCHEV sur un paquet INDICE et CHERCHER, et personne ne veut perdre de temps à étudier une formule plus complexe.

4 principaux avantages de l'utilisation de SEARCH / INDEX dans Excel :

1. Recherchez de droite à gauche. Comme tout utilisateur Excel lettré le sait, RECHERCHEV ne peut pas regarder vers la gauche, ce qui signifie que la valeur souhaitée doit nécessairement se trouver dans la colonne la plus à gauche de la plage étudiée. En cas de CHERCHER/INDICE, la colonne de recherche peut se trouver à gauche ou à droite de la plage de recherche. Exemple : affichera cette fonctionnalité en action.

2. Ajouter ou supprimer des colonnes en toute sécurité. Formules avec fonction RECHERCHEV cesser de fonctionner ou renvoyer des valeurs erronées si vous supprimez ou ajoutez une colonne à la table de recherche. Pour la fonction RECHERCHEV toute colonne insérée ou supprimée changera le résultat de la formule, puisque la syntaxe RECHERCHEV vous oblige à spécifier la plage entière et le numéro de colonne spécifique à partir duquel vous souhaitez extraire les données.

Par exemple, si vous avez une table A1 : C10, et vous souhaitez extraire des données de la colonne B, alors vous devez définir la valeur 2 pour argumenter col_index_num fonction (numéro_colonne) RECHERCHEV, comme ça:

RECHERCHEV ("valeur de recherche", A1 : C10,2)
= RECHERCHEV ("valeur de recherche" ; A1 : C10 ; 2)
Si vous insérez plus tard une nouvelle colonne entre les colonnes UNE et B, alors la valeur de l'argument devra être changée de 2 sur le 3 sinon, la formule renverra le résultat de la colonne que vous venez d'insérer.

En utilisant CHERCHER/INDICE, Vous pouvez supprimer ou ajouter des colonnes à la plage à l'étude sans fausser le résultat, puisque la colonne contenant la valeur souhaitée est directement définie. En effet, c'est un grand avantage, surtout lorsque vous devez travailler avec de grandes quantités de données. Vous pouvez ajouter et supprimer des colonnes sans vous soucier de corriger chaque fonction que vous utilisez. RECHERCHEV.

3. Il n'y a pas de limite sur la taille de la valeur souhaitée. En utilisant RECHERCHEV, rappelez-vous de la limite de longueur de la valeur souhaitée de 255 caractères, sinon vous risquez d'obtenir une erreur #VALEUR!(#VALEUR!). Ainsi, si la table contient de longues chaînes, la seule solution valable est d'utiliser INDICE/CHERCHER.

Supposons que vous utilisiez cette formule avec RECHERCHEV regarder dans les cellules de B5 avant D10 la valeur spécifiée dans la cellule A2:

RECHERCHEV (A2, B5 : D10,3, FAUX)
= RECHERCHEV (A2, B5 : D10, 3, FAUX)
La formule ne fonctionnera pas si la valeur est dans la cellule A2 plus de 255 caractères. Au lieu de cela, vous devez utiliser une formule similaire INDICE/CHERCHER:

INDEX (D5 : D10, CORRESPONDANCE (VRAI, INDEX (B5 : B10 = A2,0), 0))
= INDEX (D5 : D10 ; RECHERCHE (VRAI ; INDEX (B5 : B10 = A2 ; 0 ; 0))
4. Vitesse de travail plus élevée. Si vous travaillez avec de petites feuilles de calcul, la différence de performances Excel sera probablement subtile, en particulier dans les versions récentes. Si vous travaillez avec de grands tableaux contenant des milliers de lignes et des centaines de formules de recherche, Excel fonctionnera beaucoup plus rapidement lors de l'utilisation CHERCHER et INDICEà la place de RECHERCHEV... En général, un tel remplacement augmente la vitesse d'Excel en 13% .

Influence RECHERCHEV Les performances d'Excel sont particulièrement visibles si le classeur contient des centaines de formules matricielles complexes telles que RECHERCHEV + SOMME... Le fait est que la vérification de chaque valeur du tableau nécessite un appel de fonction distinct. RECHERCHEV... Par conséquent, plus le tableau contient de valeurs et plus votre tableau contient de formules matricielles, plus Excel fonctionne lentement.

D'autre part, une formule avec des fonctions CHERCHER et INDICE il recherche et renvoie simplement le résultat, faisant le même travail sensiblement plus rapidement.

INDEX et MATCH - exemples de formules

Maintenant que vous comprenez les raisons pour lesquelles il vaut la peine d'apprendre des fonctions CHERCHER et INDICE, passons au plus intéressant et voyons comment vous pouvez appliquer les connaissances théoriques dans la pratique.

Comment rechercher du côté gauche en utilisant SEARCH et INDEX

Tout tutoriel sur RECHERCHEV insiste sur le fait que cette fonction ne peut pas regarder vers la gauche. Ceux. si la colonne affichée n'est pas la plus à gauche dans la plage de recherche, il n'y a aucune chance d'obtenir de RECHERCHEV résultat désiré.

Les fonctions CHERCHER et INDICE Excel est beaucoup plus flexible et ils ne se soucient pas de l'emplacement de la colonne contenant la valeur à récupérer. Par exemple, revenons au tableau des capitales des États et de la population. Cette fois, écrivons la formule CHERCHER/INDICE, qui montrera quelle place en termes de population est la capitale de la Russie (Moscou).

Comme vous pouvez le voir sur l'image ci-dessous, la formule fait un excellent travail :

INDICE ($ A $ 2: $ A $ 10, MATCH ("Russie", $ B $ 2: $ B $ 10,0))

Maintenant, vous ne devriez avoir aucun problème à comprendre comment cette formule fonctionne :

Tout d'abord, nous utilisons la fonction CORRESPONDRE(MATCH) qui trouve la position de « Russie » dans la liste :
MATCH ("Russie", $ B $ 2: $ B $ 10,0))
= RECHERCHE ("Russie"; $ B$ 2: $ B$ 10; 0))

Ensuite, nous définissons la plage pour la fonction INDICE(INDEX) à partir duquel extraire la valeur. Dans notre cas c'est A2 : A10.

Ensuite, nous connectons les deux parties et obtenons la formule :
INDEX ($ A $ 2: $ A $ 10; MATCH ("Russie"; $ B $ 2: $ B $ 10; 0))
= INDEX ($ A $ 2: $ A $ 10; RECHERCHE ("Russie"; $ B $ 2: $ B $ 10; 0))

Indice: La bonne solution consiste à toujours utiliser des liens absolus pour INDICE et CHERCHER afin que les plages de recherche ne soient pas perdues lors de la copie de la formule dans d'autres cellules.
Calculs utilisant INDEX et SEARCH dans Excel (AVERAGE, MAX, MIN)

Vous pouvez imbriquer d'autres fonctions Excel dans INDICE et CHERCHER, par exemple, pour trouver le minimum, le maximum ou le plus proche de la moyenne. Voici plusieurs options pour les formules appliquées au tableau à partir de :

1. MAXIMUM(MAX). La formule trouve le maximum dans une colonne ré C la même ligne :

INDICE ($ CA 2 : $ CA 10 $, MATCH (MAX ($ D $ 2 : I 10 $), $ D $ 2 : D 10,0) $)
= INDICE ($ CA 2 : $ CA 10 $ ; RECHERCHE (MAX ($ D $ 2 : I $ 10) ; $ D $ 2 : D 10 $ ; 0))
Résultat : Pékin

2. MIN(MIN). La formule trouve le minimum dans une colonne ré et renvoie la valeur de la colonne C la même ligne :

INDICE ($ CA 2 : $ C $ 10, MATCH (MIN ($ D $ 2 : I 10 $), $ D $ 2 : D 10,0) $)
= INDEX ($ C $ 2 : $ C $ 10 ; RECHERCHE (MIN ($ D $ 2 : I $ 10) ; $ D $ 2 : D 10 $ ; 0))
Résultat : Lima

3. MOYENNE(MOYENNE). La formule calcule la moyenne sur une plage J2 : J10, puis trouve le plus proche et renvoie la valeur de la colonne C la même ligne :

INDICE ($ C $ 2 : $ C $ 10, MATCH (MOYENNE ($ D $ 2 : D $ 10), $ D $ 2 : D $ 10,1))
= INDICE ($ CA 2 : $ CA 10 $ ; RECHERCHE (MOYENNE ($ D $ 2 : D 10 $) ; $ D $ 2 : D 10 $ ; 1))
Résultat : Moscou

Points à retenir lors de l'utilisation de la fonction MOYENNE avec INDEX et RECHERCHE

Utilisation de la fonction MOYENNE en combinaison avec INDICE et CHERCHER, comme troisième argument de la fonction CHERCHER le plus souvent, vous devrez indiquer 1 ou alors -1 au cas où vous ne seriez pas sûr que la plage que vous visualisez contienne une valeur égale à la moyenne. Si vous êtes sûr qu'il existe une telle valeur, mettez 0 pour trouver une correspondance exacte.

Si vous spécifiez 1 , les valeurs de la colonne de recherche doivent être triées par ordre croissant et la formule renverra la valeur maximale inférieure ou égale à la moyenne.

Si vous spécifiez -1 , les valeurs de la colonne de recherche doivent être triées par ordre décroissant et la valeur minimale supérieure ou égale à la moyenne est renvoyée.

Dans notre exemple, les valeurs de la colonne ré ordonné par ordre croissant, nous utilisons donc le type correspondant 1 ... Formule INDICE/RECHERCHEPOZ renvoie « Moscou » car la population de la ville de Moscou est la plus proche de la moyenne (12 269 06).

Comment utiliser INDEX et SEARCH pour rechercher une ligne et une colonne connues

Cette formule est équivalente à la recherche bidimensionnelle RECHERCHEV et vous permet de trouver une valeur à l'intersection d'une ligne et d'une colonne spécifiques.
Dans cet exemple, la formule INDICE/CHERCHER sera très similaire aux formules dont nous avons déjà parlé dans ce tutoriel, à une différence près. Devinez lequel ?

Comme vous vous en souvenez, la syntaxe de la fonction INDICE(INDEX) autorise trois arguments :

INDEX (tableau, num_ligne,)
INDEX (tableau ; num_ligne ; [numéro_colonne])
Et je félicite ceux d'entre vous qui l'ont deviné !

Commençons par écrire un modèle de formule. Pour cela, nous prenons la formule déjà familière INDICE/CHERCHER et ajoutez-y une fonction supplémentaire CHERCHER qui renverra le numéro de la colonne.

INDEX (Votre table, (MATCH (, la colonne dans laquelle rechercher, 0)), (ASSOCIATION (, chaîne dans laquelle rechercher,0))
= INDEX (Votre table, (MATCH ( valeur pour la recherche verticale,la colonne dans laquelle rechercher, 0)), (ASSOCIATION ( valeur pour la recherche horizontale,chaîne dans laquelle rechercher,0))
Notez que pour une recherche en deux dimensions, vous devez spécifier la table entière dans l'argument déployer(tableau) fonctions INDICE(INDICE).
Mettons maintenant ce modèle à l'épreuve. Ci-dessous, vous pouvez voir une liste des pays les plus peuplés du monde. Supposons que notre tâche soit de connaître la population américaine en 2015.

Bon, écrivons la formule. Lorsque j'ai besoin de créer une formule complexe dans Excel avec des fonctions imbriquées, j'écris d'abord chacune d'elles séparément.

Commençons donc par deux fonctions CHERCHER qui renverra les numéros de ligne et de colonne pour la fonction INDICE:

RECHERCHER une colonne- nous cherchons dans la colonne B, ou plutôt dans la gamme B2 : B11, la valeur spécifiée dans la cellule H2(ETATS-UNIS). La fonction ressemblera à ceci :
MATCH (H$ 2$, G$ 1$ : G$ 11,0)
= RECHERCHE ($ H $ 2; $ B $ 1 : $ B $ 11 $; 0)
4 car "USA" est le 4ème élément de liste dans la colonne B(y compris le titre).

MATCH pour chaîne- on cherche la valeur de la cellule H3(2015) en ligne 1 , c'est-à-dire dans les cellules A1 : E1:
MATCH (3 $ H $, 1 $ A 1 $ : 1,0 $ E $)
= RECHERCHE ($ H $ 3 ; $ A $ 1 : $ E $ 1 ; 0)

Le résultat de cette formule sera 5 car « 2015 » est dans la 5ème colonne.

Maintenant, nous insérons ces formules dans la fonction INDICE et voila :

INDICE ($ A $ 1 : $ E $ 11, MATCH ($ H $ 2, $ B $ 1 : $ B $ 11,0), MATCH ($ H 3 $, $ A $ 1 : $ E $ 1,0))
= INDICE ($ A $ 1 : $ E $ 11 ; RECHERCHE ($ H $ 2 ; $ B $ 1 : $ B $ 11 ; 0); RECHERCHE ($ H $ 3 ; $ A $ 1 : $ E $ 1 ; 0))
Si on remplace les fonctions CHERCHER sur les valeurs qu'ils renvoient, la formule deviendra simple et directe :

INDICE ($ A $ 1 : $ E $ 11,4,5))
= INDICE ($ A $ 1 : $ E $ 11; 4; 5))
Cette formule renvoie la valeur à l'intersection 4e cordes et 5e colonne dans la plage A1 : E11, c'est-à-dire la valeur de la cellule E4... Juste? Oui!

Recherche multiple avec INDEX et SEARCH

Dans le tutoriel sur RECHERCHEV nous avons montré un exemple de formule avec une fonction RECHERCHEV pour rechercher plusieurs critères. Cependant, une limitation importante de cette solution était la nécessité d'ajouter une colonne auxiliaire. Bonne nouvelle : formule INDICE/CHERCHER peut rechercher par valeurs dans deux colonnes, sans avoir besoin de créer une colonne auxiliaire !

Supposons que nous ayons une liste de commandes et que nous voulions trouver le montant selon deux critères - nom de l'acheteur(Client) et produit(Produit). L'affaire est compliquée par le fait qu'un acheteur peut acheter plusieurs produits différents à la fois, et les noms des acheteurs dans le tableau sur la feuille Table de correspondance sont disposés sans ordre particulier.

C'est la formule INDICE/CHERCHER résout le problème :

(= INDEX ("Lookup table"! $ A $ 2: $ C $ 13, MATCH (1, (A2 = "Lookup table"! $ A $ 2: $ A $ 13) *
(B2 = "Lookup table" ! $ B $ 2 : $ B 13 $), 0), 3))
(= INDEX ("Lookup table"! $ A $ 2: $ C $ 13; MATCH (1; (A2 = "Lookup table"! $ A $ 2: $ A $ 13) *
(B2 = « Table de consultation » ! $ B $ 2 : $ B $ 13 ; 0 ); 3))
Cette formule est plus complexe que les autres dont nous avons parlé plus tôt, mais armée de la connaissance des fonctions. INDICE et CHERCHER Vous la vaincrez. Le plus dur est la fonction CHERCHER Je pense qu'il faut d'abord l'expliquer.

MATCH (1, (A2 = "Table de correspondance"! $ A $ 2: $ A 13 $), 0) * (B2 = "Table de correspondance"! $ B $ 2: $ B $ 13)
MATCH (1 ; (A2 = « Table de consultation » ! $ A 2 $ 2 : $ A 13 $); 0) * (B2 = « Table de consultation » ! $ B $ 2 : $ B $ 13)
Dans la formule ci-dessus, la valeur souhaitée est 1 et le tableau de recherche est le résultat de la multiplication. D'accord, que doit-on multiplier et pourquoi ? Décomposons-le dans l'ordre :

On prend la première valeur de la colonne UNE(Client) par feuille Tableau principal et comparez-le avec tous les noms de clients dans le tableau sur la feuille Table de correspondance(A2 : A13).

Si une correspondance est trouvée, l'équation renvoie 1 (VRAI), et sinon - 0 (FAUX).

Ensuite, nous faisons la même chose pour les valeurs de colonne. B(Produit).

Puis on multiplie les résultats obtenus (1 et 0). Ce n'est que si des correspondances sont trouvées dans les deux colonnes (c'est-à-dire que les deux critères sont vrais), obtiendrez-vous 1 ... Si les deux critères sont faux, ou si un seul d'entre eux est rempli, vous recevrez 0 .

Maintenant vous comprenez pourquoi nous avons demandé 1 , quelle est la valeur souhaitée ? Il est exact que la fonction CHERCHER n'a renvoyé une position que lorsque les deux critères sont remplis.

Noter: Dans ce cas, vous devez utiliser le troisième argument facultatif de la fonction. INDICE... C'est nécessaire car dans le premier argument, nous définissons la table entière et devons indiquer aux fonctions de quelle colonne extraire la valeur. Dans notre cas, c'est la colonne C(Somme) et donc nous sommes entrés 3 .
Et enfin, puisque nous devons vérifier chaque cellule du tableau, cette formule doit être une formule matricielle. Vous pouvez le voir par les accolades qui l'entourent. Ainsi, lorsque vous avez terminé de saisir votre formule, n'oubliez pas d'appuyer sur Ctrl + Maj + Entrée.

Si tout est fait correctement, vous obtiendrez le résultat comme sur l'image ci-dessous :

INDEX et SEARCH en combinaison avec IFERROR dans Excel

Comme vous l'avez probablement déjà remarqué (et plus d'une fois), si vous entrez une valeur incorrecte, par exemple, qui n'est pas dans le tableau affiché, la formule INDICE/CHERCHER signale une erreur # N / A(# N/A) ou #VALEUR!(#VALEUR!). Si vous souhaitez remplacer un tel message par quelque chose de plus compréhensible, vous pouvez insérer la formule avec INDICE et CHERCHER en fonction SIERREUR.

Syntaxe de la fonction SIERREUR très simple:

SIERREUR (valeur, valeur_si_erreur)
SIERREUR (valeur ; valeur_si_erreur)
Où est l'argument valeur(valeur) est la valeur vérifiée pour une erreur (dans notre cas, le résultat de la formule INDICE/CHERCHER); et l'argumentation value_if_error(value_if_error) est la valeur à retourner si la formule renvoie une erreur.

Par exemple, vous pouvez insérer dans la fonction SIERREUR comme ça:

IFERREUR (INDICE ($ A $ 1 : $ E $ 11, MATCH ($ G $ 2, $ B $ 1 : $ B $ 11,0), MATCH ($ G $ 3, $ A 1 $ 1 : $ E $ 1,0)) ,
"Aucune correspondance trouvée. Veuillez réessayer !") = IFERREUR (INDEX ($ A $ 1 : $ E $ 11 ; RECHERCHE ($ G $ 2 ; $ B $ 1 : $ B $ 11 $ ; 0); RECHERCHE ($ G 3 $ ; 1 $ A : 1 $ E $ ; 0));
"Aucune correspondance trouvée. Veuillez réessayer !")
Et maintenant, si quelqu'un entre une mauvaise valeur, la formule renverra ce résultat :

Si vous préférez laisser la cellule vide en cas d'erreur, vous pouvez utiliser des guillemets ("") comme valeur du deuxième argument de la fonction SIERREUR... Comme ça:

SIERREUR (INDEX (tableau, MATCH (valeur_recherche, tableau_recherche, 0), "")
SIERREUR (INDEX (tableau ; RECHERCHE (valeur_recherche ; tableau_recherche ; 0), "")
J'espère que vous avez trouvé au moins une formule utile dans ce tutoriel. Si vous avez rencontré d'autres problèmes de recherche pour lesquels vous n'avez pas pu trouver de solution appropriée parmi les informations de cette leçon, n'hésitez pas à décrire votre problème dans les commentaires, et nous essaierons tous de le résoudre ensemble.
Formatage conditionnel (5)
Listes et gammes (5)
Macros (procédures VBA) (63)
Divers (39)
Bugs et problèmes d'Excel (4)
Comment trouver une valeur dans une autre table ou force VLOOKUP

En fait, dans cet article, je veux parler des possibilités non seulement Fonctions RECHERCHEV, mais je veux aussi aborder CHERCHER, en tant que fonction très étroitement liée à RECHERCHEV. Chacune de ces fonctionnalités a à la fois des avantages et des inconvénients. En un mot, RECHERCHEV recherche une certaine valeur que nous avons spécifiée parmi l'ensemble de valeurs situées dans une colonne. Le plus souvent, le besoin de RECHERCHEV survient lorsque vous devez comparer des données, rechercher des données dans une autre table, ajouter des données d'une table à une autre, en fonction de certains critères, etc.
Pour mieux comprendre le fonctionnement de RECHERCHEV, il est préférable de commencer par un exemple pratique. Il y a un tableau comme ça :
Fig. 1

et à partir du premier tableau, il est nécessaire de substituer dans la deuxième date pour chaque nom de famille. Pour trois enregistrements, ce n'est pas un problème et le faire à la main - tout est évident. Mais dans la vraie vie, ce sont des tables avec des milliers d'enregistrements et les recherches de substitution manuelles peuvent prendre plus d'une heure. De plus, quelques autres éléments sont en jeu : non seulement les noms complets sont classés dans un ordre complètement différent dans les deux tableaux et le nombre d'enregistrements dans les tableaux est différent, de sorte que les tableaux sont également situés sur des feuilles / livres différents. Je crois vous avoir convaincu que la substitution de la main n'est pas du tout une option. Mais RECHERCHEV (RECHERCHEV) ici sera irremplaçable. Dans ce cas, pratiquement rien ne devra être fait - il suffit d'écrire dans la première cellule de la colonne C du deuxième tableau (où vous devez remplacer les dates du premier tableau) cette formule :
= RECHERCHEV ($ A2; Sheet1! $ A $ 2: $ C $ 4; 3; 0)
Vous pouvez écrire la formule soit directement dans la cellule, soit à l'aide du gestionnaire de fonctions, en sélectionnant dans la catégorie Références et tableaux RECHERCHEV et en spécifiant séparément les critères nécessaires. Copiez maintenant ( Ctrl+C) avec la formule, sélectionnez toutes les cellules de la colonne C jusqu'à la fin des données et insérez ( Ctrl+V).

Tout d'abord, le principe de base du fonctionnement : RECHERCHEV recherche dans la première colonne de l'argument Table la valeur spécifiée par l'argument Lookup_value ... Lorsque la valeur requise est trouvée, la fonction renvoie la valeur opposée à la valeur trouvée, mais à partir de la colonne spécifiée par l'argument numéro_colonne ... Nous traiterons de la visualisation par intervalles un peu plus tard. RECHERCHEV ne peut renvoyer qu'une seule valeur - la première qui correspond au critère. Si la valeur souhaitée n'est pas trouvée (pas dans le tableau), alors le résultat de la fonction sera # N / A ... N'ayez pas peur de cela - c'est même utile. Vous saurez exactement quels enregistrements sont manquants et vous pourrez ainsi comparer les deux tables entre elles. Parfois, il s'avère que vous voyez : les données sont dans les deux tables, mais RECHERCHEV donne # N / A. Cela signifie que les données de vos tables ne sont pas identiques. Certains d'entre eux ont des espaces supplémentaires discrets (généralement avant ou après la valeur), ou les caractères cyrilliques sont mélangés avec des caractères latins. Également # N / A sera si les critères sont des nombres et dans le tableau souhaité ils sont écrits sous forme de texte (en règle générale, un triangle vert apparaît dans le coin supérieur gauche d'une telle cellule), et en finale - sous forme de nombres. Ou vice versa.
Description des arguments RECHERCHEV
$ A2 - argument Lookup_value(appelons-le Critèreêtre court). C'est ce que nous recherchons. Ceux. pour le premier enregistrement de la deuxième table ce sera S.A. Petrov. Ici, vous pouvez spécifier soit le texte du critère directement (dans ce cas, il doit être entre guillemets - = RECHERCHEV ("Petrov SA"; Sheet1! $ A $ 2: $ C $ 4; 3; 0), soit un lien vers une cellule avec ce texte (comme dans l'exemple de fonction)... Il y a une petite nuance : vous pouvez également utiliser des caractères génériques : "*" et "?". C'est très pratique si vous avez seulement besoin de trouver des valeurs à partir d'une partie d'une chaîne. Par exemple, vous ne pouvez pas saisir "Petrov S.A" en entier, mais saisir uniquement le nom de famille et un astérisque - "Petrov *". Ensuite, tout enregistrement commençant par "Petrov" sera affiché. Si vous avez besoin de trouver un enregistrement dans lequel le nom de famille "Petrov" apparaît n'importe où dans la ligne, vous pouvez le spécifier comme ceci : "* Petrov *". Si vous voulez trouver le nom de famille Petrov et quelles que soient les initiales du nom et du patronyme (si le nom complet est écrit sous la forme d'Ivanov II), voici simplement ce qui suit : "Ivanov?.?." ... Il est souvent nécessaire que chaque ligne indique sa propre valeur (dans la colonne A les Noms et il faut tous les retrouver). Dans ce cas, les références aux cellules de la colonne A sont toujours indiquées. Par exemple, dans la cellule A1, il est écrit : Ivanov. On sait également qu'Ivanov est dans un autre tableau, mais après le nom de famille, le nom et le patronyme (ou autre chose) peuvent être écrits. Mais nous avons seulement besoin de trouver la ligne qui commence par le nom de famille. Ensuite, il faut écrire comme suit : A1 & "*". Cette entrée sera équivalente à "Ivanov *". A1 dit Ivanov, l'esperluette (&) est utilisée pour combiner deux valeurs de texte en une seule chaîne. Un astérisque entre guillemets (comme devrait l'être le texte à l'intérieur d'une formule). Ainsi, on obtient :
A1 & "*" =>
"Ivanov" & "*" =>
"Ivanov*"
C'est très pratique s'il y a beaucoup de valeurs de recherche.
Si vous devez déterminer s'il y a au moins quelque part un mot dans la chaîne, mettez des astérisques des deux côtés : "*" & A1 & "*"
Feuille 1 ! $ A $ 2 : $ C $ 4 - argument tableau... Spécifie une plage de cellules. Seule la plage doit contenir des données de la première cellule de données à la toute dernière. Il ne doit pas nécessairement s'agir de la plage spécifiée dans l'exemple. S'il y a 100 lignes, alors Sheet1 ! $ A $ 2: $ C $ 100. Il est important de se rappeler trois choses : d'abord, il est tableau devrait toujours commencer par la colonne que vous recherchez Critère ... Et rien d'autre. Sinon, rien ne sera trouvé ou le résultat sera complètement différent de ce à quoi vous vous attendiez. Deuxièmement : argumentation tableau doit être "épinglé" ... Qu'est-ce que ça veut dire. Voir les signes dollar - $? C'est l'ancrage (plus précisément, cela s'appelle une référence de plage absolue)... Comment c'est fait. Mettre le texte du lien en surbrillance (une seule plage - un critère) et appuyez sur F4 jusqu'à ce que des dollars apparaissent avant le nom de la colonne et le numéro de la ligne. Si cela n'est pas fait, lors de la copie de la formule, l'argument Table "se déplacera" et le résultat sera à nouveau incorrect. Et le dernier - la table doit contenir des colonnes de la première (dans laquelle nous cherchons) à la dernière (à partir de laquelle nous devons renvoyer des valeurs). Dans l'exemple Feuille 1 ! $ A $ 2 : $ C $ 4- cela signifie qu'il ne sera pas possible de retourner la valeur de la colonne D (4), car il n'y a que trois colonnes dans le tableau.

3 - numéro_colonne... Ici, nous spécifions simplement le numéro de colonne dans l'argument tableau, les valeurs à partir desquelles nous devons substituer comme résultat. Dans l'exemple, il s'agit de la date d'acceptation - c'est-à-dire colonne numéro 3. Si un département était nécessaire, alors nous indiquerions 2, et si nous avions juste besoin de comparer s'il y a des noms de famille d'une table dans une autre, alors nous pourrions également indiquer 1. Important : l'argument numéro_colonne ne doit pas dépasser le nombre de colonnes dans l'argument tableau ... Sinon, le résultat de la formule sera une erreur. #RELIER!... Par exemple, si la plage $ B $ 2 : $ C $ 4 est spécifiée et que vous devez renvoyer les données de la colonne C, alors 2 est correct. argument tableau($ B $ 2: $ C $ 4) ne contient que deux colonnes - B et C. Si vous essayez de spécifier le numéro de la colonne 3 (quel est le nombre sur la feuille), alors nous obtenons une erreur #RELIER! puisque il n'y a tout simplement pas de troisième colonne dans la plage spécifiée.

Astuce pratique : si l'argument Table a trop de colonnes et que vous devez renvoyer le résultat de la dernière colonne, alors il n'est pas du tout nécessaire de calculer leur nombre. Vous pouvez le spécifier comme ceci : = RECHERCHEV ($ A2; Sheet1! $ A $ 2: $ C $ 4; NUMBERCOLUMN (Sheet1! $ A $ 2: $ C $ 4); 0). Au fait, dans ce cas, Sheet1! peuvent également être supprimés comme inutiles : = RECHERCHEV ($ A2; Sheet1! $ A $ 2: $ C $ 4; NUMBERCOLUMN ($ A $ 2: $ C $ 4); 0).

0 - Interval_view est un argument très intéressant. Il peut être VRAI ou FAUX. La question se pose immédiatement : pourquoi y a-t-il 0 dans ma formule ? Tout est très simple - Excel dans les formules peut percevoir 0 comme FAUX et 1 comme VRAI. Si vous spécifiez ce paramètre égal à 0 ou FALSE dans RECHERCHEV, une correspondance exacte avec le critère spécifié sera recherchée. Cela n'a rien à voir avec les caractères génériques ("*" et "?"). Si vous utilisez 1 ou VRAI (ou ne spécifiez pas du tout le dernier argument, puisqu'il est VRAI par défaut), alors... C'est une très longue histoire. En bref - VLOOKUP recherchera la valeur la plus similaire qui correspond Critère ... Très utile parfois. Cependant, si vous utilisez ce paramètre, la liste dans l'argument Table doit être triée par ordre croissant. J'attire votre attention sur le fait que le tri n'est nécessaire que si l'argument Interval_View est TRUE ou 1. Si 0 ou FALSE, le tri n'est pas nécessaire.

Beaucoup ont probablement remarqué que sur la photo, j'ai foiré les départements pour le nom complet. Ce n'est pas une erreur d'enregistrement. L'exemple joint à l'article montre comment vous pouvez les remplacer, ainsi que les dates, par une seule formule sans modifier manuellement l'argument numéro_colonne... Il m'a semblé qu'un tel exemple pourrait bien être utile.

Comment éviter l'erreur # N / A (# N / A) dans RECHERCHEV ?
Un autre problème courant est que beaucoup de gens ne veulent pas voir le résultat # N / A si aucune correspondance n'est trouvée. Il est facile de se déplacer :
= SI (UND (RECHERCHEV ($ A2; Feuille1! $ A $ 2: $ C $ 4; 3; 0)); ""; RECHERCHEV ($ A2; Feuille1! $ A $ 2: $ C $ 4; 3; 0)))
Maintenant, si RECHERCHEV ne trouve pas de correspondance, la cellule sera vide.
Et les utilisateurs de versions d'Excel 2007 et supérieures peuvent utiliser IFERROR :
= IFERREUR (RECHERCHEV ($ A2; Sheet1! $ A $ 2: $ C $ 4; 3,0); "")

RECHERCHE promise

Cette fonction recherche la valeur spécifiée par le paramètre Lookup_value en dispute Vue_tableau... Et le résultat de la fonction est le numéro de position de la valeur trouvée dans Tableau_visible... C'est le numéro de position, pas la valeur elle-même. En principe, je ne le décrirai pas avec les mêmes détails, car les points principaux sont exactement les mêmes. Si nous voulions l'appliquer au tableau ci-dessus, alors ce serait comme ceci :
= RECHERCHE ($ A2; Feuille1! $ A $ 2: $ A 4 $; 0)
$ A2 - Lookup_value. Ici, tout est exactement le même qu'avec RECHERCHEV. Les caractères génériques sont également autorisés et dans exactement le même design.

Feuille1 ! $ A $ 2: $ A $ 4 - Le tableau à regarder. La principale différence avec RECHERCHEV est que vous pouvez spécifier un tableau avec une seule colonne. Cela devrait être la colonne que nous allons rechercher. Lookup_value ... Si vous essayez de spécifier plusieurs colonnes, la fonction renvoie une erreur.

Match_type (0) - le même que dans RECHERCHEV Interval_view ... Avec les mêmes caractéristiques. Il ne diffère que par la capacité de rechercher le plus petit du désiré ou du plus grand. Mais je ne m'étendrai pas là-dessus dans cet article.

Avec le principal trié. Mais nous devons renvoyer non pas le numéro de position, mais la valeur elle-même. Cela signifie que SEARCH dans sa forme pure ne nous convient pas. Au moins un par lui-même. Mais si vous l'utilisez avec la fonction INDEX, c'est ce dont nous avons besoin et même plus.
= INDEX (Feuille1! $ A $ 2: $ C $ 4; RECHERCHE ($ A2; Sheet1! $ A $ 2: $ A $ 4; 0); 2)
Cette formule renverra le même résultat que RECHERCHEV.

arguments de la fonction INDEX
Feuille1 ! $ A $ 2: $ C $ 4 - Tableau. Comme argument, nous spécifions la plage à partir de laquelle nous voulons obtenir des valeurs. Il peut y avoir une ou plusieurs colonnes. S'il n'y a qu'une seule colonne, le dernier argument de la fonction n'a pas besoin d'être spécifié. Soit dit en passant - cet argument peut ne pas coïncider du tout avec celui que nous spécifions dans l'argument Look-up_array de la fonction MATCH.

Viennent ensuite RowNumber et ColumnNumber. C'est pour Line_Number que nous substituons SEARCH, qui nous renvoie le numéro de position dans le tableau. Tout est construit là-dessus. INDEX renvoie la valeur de Array qui se trouve dans la ligne spécifiée (Row_Number) du Array et dans la colonne spécifiée (Column_number) s'il y a plusieurs colonnes. Il est important de savoir que dans ce bundle, le nombre de lignes dans l'argument Array de la fonction INDEX et le nombre de lignes dans l'argument Look-up_array de la fonction MATCH doivent correspondre. Et commencer sur la même ligne. C'est dans les cas normaux, si vous ne poursuivez pas d'autres objectifs.
Comme dans le cas de RECHERCHEV, INDEX renvoie # N / A si la valeur souhaitée n'est pas trouvée. Et vous pouvez contourner ces erreurs de la même manière :
Pour toutes les versions d'Excel (y compris 2003 et antérieures) :
= SI (UND (RECHERCHE ($ A2; Feuille1! $ A $ 2: $ A $ 4; 0)); ""; INDEX (Feuille1! $ A $ 2: $ C $ 4; RECHERCHE ($ A2; Feuille1! $ A $ 2: $ A 4 $; 0); 2))
Pour les versions 2007 et supérieures :
= IFERREUR (INDEX (Feuille1! $ A $ 2: $ C $ 4; RECHERCHE ($ A2; Sheet1! $ A $ 2: $ A $ 4; 0); 2); "")

Travailler avec des critères de plus de 255 caractères
INDEX-POISKPOS a également un autre avantage sur VLOOKUP. Le fait est que RECHERCHEV ne peut pas rechercher de valeurs dont la longueur de ligne contient plus de 255 caractères... Cela arrive rarement, mais cela arrive. Vous pouvez bien entendu tromper le VLOOKUP et couper le critère :
= RECHERCHEV (MID ($ A2; 1; 255); MID (Feuille1! $ A $ 2: $ C $ 4; 1; 255); 3; 0)
mais c'est une formule matricielle. Et d'ailleurs, une telle formule ne renvoie pas toujours le résultat souhaité. Si les 255 premiers caractères sont identiques aux 255 premiers caractères du tableau, puis que les caractères diffèrent, la formule ne le verra plus. Et la formule ne renvoie que des valeurs de texte, ce qui n'est pas très pratique dans les cas où des nombres doivent être renvoyés.

Par conséquent, il est préférable d'utiliser une formule délicate :
= INDEX (Feuille1! $ A $ 2: $ C $ 4; SOMMEPROD (RECHERCHE (VRAI; Feuille1! $ A $ 2: $ A $ 4 = $ A2; 0)); 2)
Ici, j'ai utilisé les mêmes plages dans les formules pour la lisibilité, mais dans l'exemple de téléchargement, elles sont différentes de celles présentées ici.
La formule elle-même repose sur la capacité de la fonction SOMMEPROD à transformer certaines fonctions à l'intérieur en calculs massifs. Dans ce cas, MATCH recherche la position de la chaîne où le critère est égal à la valeur de la chaîne. Vous ne pourrez plus utiliser de caractères génériques ici.

Dans l'exemple joint à l'article, vous trouverez des exemples d'utilisation de tous les cas décrits et un exemple des raisons pour lesquelles INDEX et MATCH sont parfois préférables à RECHERCHEV.

Télécharger l'exemple
(26,0 Kio, 14 615 téléchargements)
L'article vous a-t-il aidé ? Partagez le lien avec vos amis ! Cours vidéo
("Bottom bar" :( "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easyOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500 textcss " :" affichage : bloc ; remplissage : 12px ; text-align : gauche ; "," textbgcss ":" affichage : bloc ; position : absolue ; haut : 0px ; gauche : 0px ; largeur : 100 % ; hauteur : 100 % ; arrière-plan -couleur : # 333333 ; opacité : 0,6 ; filtre : a lpha (opacité = 60); "," titlecss ":" display: block; position : relative ; police : gras 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial ; color: #fff; "," descriptioncss ":" display: block; position : relative ; police : 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial ; couleur : #fff ; margin-top : 8px ; "," buttoncss ":" affichage : bloc ; position : relative ; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))