Comment utiliser les fonctions de recherche dans Excel ?

exemples de fichiers

1. Fonction ADRESSE

La fonction ADDRESS renvoie une référence de cellule valide selon l’adresse de colonne et de ligne. En termes simples, vous pouvez créer l’adresse d’une cellule en utilisant son numéro de ligne et son numéro de colonne.

Syntaxe

ADRESSE(num_ligne,num_colonne,num_abs,A1,texte_feuille)

Arguments

• row_num : un nombre pour spécifier le numéro de ligne.
• column_num : un nombre pour spécifier le numéro de colonne.
• [abs_num] : Type de référence.
• [A1] : style de référence.
• [sheet_text] : une valeur de texte en tant que nom de feuille.

Remarques

• Par défaut, la fonction ADDRESS renvoie une référence absolue dans le résultat.

Exemple

Dans l’exemple ci-dessous, nous avons utilisé différents arguments pour obtenir tous les types de résultats.

Avec le style de référence R1C1 :

• Référence relative.
• Référence de ligne relative et référence de colonne absolue.
• Référence de ligne absolue et de colonne relative.
• Référence absolue.

Avec le style de référence A1 :

• Référence relative.
• Référence de ligne relative et référence de colonne absolue.
• Référence de ligne absolue et de colonne relative.
• Référence absolue.

2. Fonction ZONES

La fonction ZONES renvoie un nombre qui représente le nombre de plages dans la référence que vous avez spécifiée. En termes simples, il compte en fait les différentes zones de feuille de calcul auxquelles vous avez fait référence à la fonction.

syntaxe

DOMAINES(référence)

Arguments

• reference : une référence à une cellule ou à une plage de cellules.

Remarques

• La référence peut être une cellule, une plage de cellules ou une plage nommée.
• Si vous souhaitez faire référence à plusieurs références de cellule, vous devez placer toutes ces références dans plusieurs ensembles de parenthèses et utiliser des virgules pour séparer chaque référence des autres.

Exemple

Dans l’exemple ci-dessous, nous avons utilisé la fonction zones pour obtenir la référence numérique dans une plage nommée.

Comme vous pouvez le voir, il y a trois colonnes dans la plage et il a renvoyé 3 dans le résultat.

3. CHOISISSEZ la fonction

La fonction CHOISIR renvoie une valeur de la liste de valeurs en fonction du numéro de position spécifié. En termes simples, il recherche une valeur dans une liste en fonction de sa position et la renvoie dans le résultat.

Syntaxe

CHOISIR(num_index,valeur1,valeur2,…)

Arguments

• index_num : nombre permettant de spécifier la position de la valeur dans la liste.
• valeur1 : une plage de cellules ou une valeur d’entrée à partir de laquelle vous pouvez choisir.
• [valeur2] : une plage de cellules ou une valeur d’entrée à partir de laquelle vous pouvez choisir.

Remarques

• Vous pouvez faire référence à une cellule ou vous pouvez également insérer des valeurs directement dans la fonction.

Exemple

Dans l’exemple ci-dessous, nous avons utilisé la fonction CHOISIR avec une liste déroulante pour calculer quatre choses différentes (somme, moyenne, max et mélange). Nous avons donc utilisé la formule ci-dessous pour calculer les quatre choses :

=CHOOSE(VLOOKUP(K2,Q1:R4,2,FALSE),SUM(O2:O9),AVERAGE(O2:O9),MAX(O2:O9),MIN(O2:O9))

Nous avons ce petit tableau avec le nom des quatre calculs que nous voulons et un numéro de série pour chacun dans la cellule correspondante.

Après cela, nous avons une liste déroulante pour les quatre calculs. Maintenant, pour obtenir le numéro d’index dans la fonction de choix à partir de cette petite table, nous avons une formule de recherche qui renverra le numéro de série selon la valeur sélectionnée dans la liste déroulante.

Et au lieu de valeurs, nous avons utilisé quatre formules pour 4 calculs différents.

4. Fonction COLONNE

La fonction COLUMN renvoie le numéro de colonne pour la référence de cellule donnée. Comme vous le savez, chaque référence de cellule est composée d’un numéro de colonne et d’un numéro de ligne. Il prend donc le numéro de colonne et le renvoie dans le résultat.

Syntaxe

COLONNE([référence])

Arguments

• reference : une référence de cellule pour laquelle vous souhaitez obtenir le numéro de colonne.

Remarques

• Vous ne pouvez pas faire référence à plusieurs références.
• Si vous faites référence à un tableau, la fonction de colonne renverra également les numéros de colonne dans un tableau.
• Si vous vous référez à une plage de plusieurs cellules, il renverra le numéro de colonne de la cellule la plus à gauche. Par exemple, si vous vous référez à la plage A1:C10, il renverra le numéro de colonne de la cellule A1.
• Si vous ignorez la spécification d’une référence, il renverra le numéro de colonne de la cellule actuelle.

Exemple

Dans l’exemple ci-dessous, nous avons utilisé COLUMN pour obtenir le numéro de colonne de la cellule A1.

Comme je l’ai déjà mentionné, si vous ignorez la référence de cellule, le numéro de colonne de la cellule actuelle sera renvoyé. Dans l’exemple ci-dessous, nous avons utilisé COLUMN pour créer un en-tête avec des numéros de série.

5. Fonction COLONNES

La fonction COLUMNS renvoie le nombre de colonnes référencées dans la référence donnée. En termes simples, il compte le nombre de colonnes dans la plage fournie et renvoie ce nombre.

Syntaxe

COLONNES(tableau)

Arguments

• tableau : un tableau ou une plage de cellules à partir de laquelle vous souhaitez obtenir le nombre de colonnes.

Remarques

• Vous pouvez également utiliser une plage nommée.
• La fonction COLUMNS n’est pas concernée par les valeurs dans les cellules, elle renverra simplement le nombre de colonnes dans une référence.

Exemple

Dans l’exemple ci-dessous, nous avons utilisé COLUMN pour obtenir le nombre de colonnes de la plage A1:F1.

6. Fonction FORMULATEXT

La fonction FORMULATEXT renvoie la formule de la cellule référencée. Et s’il n’y a pas de formule dans la cellule référencée, une valeur ou un blanc, il renverra un #N/A.

Syntaxe

TEXTEFORMULE(référence)

Arguments

• reference : la référence de cellule à partir de laquelle vous souhaitez obtenir la formule sous forme de texte.

Remarques

• Si vous faites référence à un autre classeur, ce classeur doit être ouvert, sinon il n’affichera pas la formule.
• Si vous vous référez à une plage de plus d’une seule cellule, il renverra la formule de la cellule supérieure gauche de la plage donnée.
• Il renverra une valeur d’erreur « #N/A » si la cellule que vous utilisez comme référence ne contient aucune formule, a une formule avec plus de 8192 caractères, une cellule est protégée ou un classeur externe n’est pas ouvert.
• Si vous faites référence à deux cellules dans une référence circulaire, les résultats des deux seront renvoyés.

Exemple

Dans l’exemple ci-dessous, nous avons utilisé un texte de formule avec différents types de références. Lorsque vous vous référez à une cellule qui n’a pas de formule, elle renverra la valeur d’erreur « #N/A ».

7. Fonction RECHERCHEH

La fonction RECHERCHEH recherche une valeur dans la ligne supérieure d’une table et renvoie la valeur de la même colonne de la valeur correspondante à l’aide du numéro d’index. En termes simples, il effectue une recherche horizontale.

Syntaxe

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Arguments

• lookup_value : la valeur que vous souhaitez rechercher.
• table_array : la table de données ou un tableau à partir duquel vous souhaitez rechercher la valeur.
• row_index_num : une valeur numérique représentant un nombre de lignes inférieures à partir de la ligne supérieure à partir de laquelle vous souhaitez obtenir la valeur. Par exemple, si vous spécifiez 2 et que votre valeur de recherche est dans A10 dans la table de données, elle renverra la valeur de la cellule B10.
• [range_lookup] : une valeur logique pour spécifier le type de recherche. Si vous souhaitez effectuer une recherche de correspondance exacte, utilisez FALSE et si vous souhaitez effectuer une recherche de correspondance non exacte, utilisez TRUE (par défaut).

Remarques

• Vous pouvez utiliser des caractères génériques.
• Vous pouvez effectuer une correspondance exacte et une correspondance approximative.
• Lors de l’exécution d’une correspondance approximative, assurez-vous de trier les données dans l’ordre croissant de gauche à droite, et si les données ne sont pas dans l’ordre croissant, cela renverrait un résultat inexact.
• Si range_lookup est vrai ou omis, il effectuera une correspondance non exacte mais renverra une correspondance exacte si la valeur de recherche existe dans la plage de recherche.
• Si range_lookup est vrai ou omis, et que la valeur de recherche n’est pas dans la plage de recherche, il renverra la valeur la plus proche qui est inférieure à la valeur de recherche.
• Si range_lookup est false, il n’est pas nécessaire de trier la plage de données.

Exemple

Dans l’exemple ci-dessous, nous avons utilisé la fonction RECHERCHEH avec MATCH pour créer une formule dynamique, puis nous avons utilisé une liste déroulante pour modifier la valeur de recherche de la cellule.

Le nom de zone de la cellule C7 est utilisé comme valeur de recherche. Plage B1 : F5 comme tableau de table et pour row_index_num, nous avons utilisé la fonction match pour obtenir le numéro de ligne.

Chaque fois que vous modifiez la valeur dans la cellule C9, elle renverra le numéro de ligne du tableau de table. Vous n’avez pas à changer votre formule encore et encore. Modifiez simplement les valeurs avec la liste déroulante et vous obtiendrez une valeur pour cela.

8. Fonction HYPERLIEN

La fonction HYPERLINK renvoie une chaîne avec un lien hypertexte qui lui est attaché. En termes simples, comme l’option HYPERLINK que vous avez dans Excel, la fonction HYPERLINK vous aide à créer un lien hypertexte.

Syntaxe

HYPERLINK(link_location,[friendly_name])

Arguments

• Link_Location : l’emplacement pour lequel vous souhaitez ajouter un HYPERLINK. Il peut encore être divisé en deux termes.
  1. lien : il peut s’agir de l’adresse d’une cellule ou d’une plage de cellules dans la même feuille de calcul ou dans toute autre feuille de calcul ou dans tout autre classeur. Nous pouvons également lier un signet à partir d’un document Word.
  2. emplacement : il peut s’agir d’un lien vers un disque dur, un serveur utilisant le chemin UNC ou toute URL provenant d’Internet ou d’un intranet. (Dans Excel en ligne, vous ne pouvez utiliser l’adresse Web que pour la fonction HYPERLINK). Vous pouvez insérer un lien vers la fonction en l’insérant sous forme de texte avec des guillemets ou en faisant référence à une cellule contenant le lien sous forme de texte. Assurez-vous d’utiliser « HTTPS:// » avant une adresse Web.
• [friendly_name] : C’est une partie facultative de cette fonction. Il agit comme la face du lien de connexion.
  1. Vous pouvez utiliser n’importe quel type de texte, de nombre ou les deux.
  2. Vous faites également référence à une cellule qui contient le friendly_name.
  3. Si vous l’ignorez, la fonction utilisera l’adresse du lien pour s’afficher.
  4. Si friendly_name renvoie une erreur, la fonction affichera une erreur.

Remarques

• Lier un fichier enregistré sur une adresse Web : vous pouvez utiliser un fichier enregistré sur une adresse Web. Cela nous aide à partager le fichier de manière efficace.
• Lier un fichier enregistré sur un disque dur : vous pouvez également utiliser cette fonction lorsque vous travaillez hors ligne. Vous pouvez lier un fichier stocké sur votre disque dur et y accéder via votre feuille Excel unique, pas besoin d’aller dans chaque dossier pour les ouvrir.
• Lier un fichier de document Word : il s’agit également d’une fonctionnalité impressionnante de la fonction HYPERLINK. Vous pouvez lier un fichier de document Word ou un emplacement spécifique dans un fichier de document Word à l’aide d’un signet.
• Lier un fichier sans utiliser de nom convivial : si vous souhaitez afficher le lien réel vers le fichier ou l’emplacement pour l’utilisateur. Dans cette situation, il vous suffit d’ignorer la déclaration de nom convivial dans la fonction HYPERLINK.

9. Fonction INDIRECTE

La fonction INDIRECT renvoie une référence valide à partir d’une chaîne de texte qui représente une référence de cellule. En termes simples, vous pouvez faire référence à une plage de cellules en utilisant l’adresse de cellule comme valeur de texte.

Syntaxe

INDIRECT(ref_text, [a1])

Arguments

• ref_text : un texte qui représente l’adresse d’une cellule, l’adresse d’une plage de cellules, une plage nommée ou un nom de table. Par exemple, A1, B10:B20 ou MyRange.
• [a1] : un nombre ou une valeur booléenne pour représenter le type de référence de cellule que vous spécifiez dans ref_text. Par exemple, si vous souhaitez utiliser le style de référence A1, utilisez VRAI ou 1 et si vous souhaitez utiliser le style de référence R1C1, utilisez FAUX ou 0 pour le style de référence R1C. Et si vous omettez de spécifier le type de référence de cellule, il utilisera le style A1 par défaut.

Remarques

• Lorsque vous avez fait référence à un autre classeur, ce classeur doit être ouvert.
• Si vous insérez une ligne ou une colonne dans la plage que vous avez référencée, INDIRECT ne mettra pas à jour cette référence.
• Si vous souhaitez insérer du texte directement dans la fonction, vous devez le mettre entre guillemets doubles ou vous pouvez également faire référence à une cellule contenant le texte que vous souhaitez utiliser comme référence.

Exemple

1. Référence à une autre feuille de travail

Vous pouvez également faire référence à une autre feuille de calcul en utilisant INDIRECT et vous devez y insérer le nom de la feuille de calcul. Dans l’exemple ci-dessous, nous avons utilisé la fonction indirecte pour faire référence à une autre feuille de calcul et avoir le nom de la feuille dans la cellule A2 et la référence de cellule dans la cellule B2.

Dans la cellule C2, nous avons utilisé la formule suivante pour combiner le texte.

=INDIRECT(“‘”&A2&”‘!”&B2)

Cette combinaison crée un texte qui est utilisé par la fonction INDIRECT pour faire référence à la cellule A1 dans la feuille1 et la meilleure partie est que lorsque vous modifiez le nom de la feuille de calcul ou l’adresse de la cellule, la référence changera automatiquement.

La cellule A1 dans « Sheet1 » a la valeur « Oui » et c’est pourquoi indirect renvoie la valeur « Oui ».

2. Référence à un autre classeur

Vous pouvez également vous référer à un autre classeur, de la même manière que nous l’avons fait pour une autre feuille de calcul. Tout ce que vous avez à faire est simplement d’ajouter un nom de classeur dans votre texte que vous utilisez comme référence.

Dans l’exemple ci-dessus, nous avons utilisé la formule suivante pour obtenir la valeur de la cellule A1 du classeur « Book1 ».

=INDIRECT(“[“&A2&”]”&B2&”!”&C2)

Comme nous avons le nom du classeur dans la cellule « A2 », le nom de la feuille de calcul dans la cellule « B2 » et le nom de la cellule dans la cellule « C2 ». Nous les avons combinés pour les utiliser comme texte d’entrée dans une fonction indirecte.

Remarque : Lorsque vous combinez une référence de cellule en tant que texte, assurez-vous de suivre la bonne structure de référence.

3. Utilisation de plages nommées

Oui, vous pouvez également faire référence à une plage nommée à l’aide de la fonction indirecte. C’est simple. Une fois que vous avez créé une plage nommée, vous devez entrer cette plage nommée sous forme de texte dans INDIRECT.

Dans l’exemple ci-dessus, nous avons une liste déroulante dans la cellule E1 qui contient une liste de plages nommées, et dans la cellule E2, nous avons utilisé ce nom. Comme la plage B2:B5 est nommée « Quantité » et la plage C2:C5 est nommée « Montant ».

Lorsque vous sélectionnez une quantité dans la liste déroulante, la fonction indirecte fait instantanément référence à la plage nommée. Et lorsque vous sélectionnez le montant dans le menu déroulant, vous aurez la somme de la plage de cellules C2 : C5.

11. Fonction RECHERCHE

La fonction RECHERCHE renvoie une valeur (que vous recherchez) à partir d’une ligne, d’une colonne ou d’ un tableau. En termes simples, vous pouvez rechercher une valeur et RECHERCHE renverra cette valeur si elle se trouve dans cette ligne, cette colonne ou ce tableau.

Syntaxe

RECHERCHE(valeur, plage_recherche, [plage_résultat])

Il existe deux types de fonctions RECHERCHE.

• Forme vectorielle
• Forme de tableau

Arguments

• valeur : la valeur que vous souhaitez rechercher dans une colonne ou une ligne.
• lookup_range : la colonne ou la ligne à partir de laquelle vous souhaitez rechercher la valeur.
• [result_range] : la colonne ou la ligne à partir de laquelle vous souhaitez renvoyer une valeur. Il s’agit d’un argument facultatif.

Remarques

• Au lieu d’utiliser la forme de tableau, il est préférable d’utiliser VLOOKUP ou HLOOKUP.

12. Fonction CORRESPONDANCE

La fonction MATCH renvoie le numéro d’index de la valeur d’un tableau. En termes simples, la fonction MATCH recherche une valeur dans la liste et renvoie le numéro de position de cette valeur dans la liste.

Syntaxe

MATCH (lookup_value, lookup_array, [match_type])

Arguments

• lookup_value : La valeur dont vous voulez obtenir la position à partir d’une liste de valeurs.
• lookup_array : la plage de cellules ou un tableau contient des valeurs.
• [match_type] : Le nombre (-1, 0 et 1) pour spécifier comment Excel recherche la valeur dans la liste de valeurs.
  1. Si vous utilisez 1, il renverra la plus grande valeur égale ou inférieure à la valeur de recherche. Les valeurs de la liste doivent être triées par ordre croissant.
  2. Si vous utilisez -1, il renverra la plus petite valeur égale ou supérieure à la valeur de recherche. Les valeurs de la liste doivent être triées par ordre croissant.
  3. Si vous utilisez 0, il renverra la correspondance exacte de la liste.

Remarques

• Vous pouvez utiliser des caractères génériques .
• S’il n’y a pas de valeur correspondante dans la liste, if renverra #N/A.
• La fonction de correspondance n’est pas sensible à la casse.

Exemple

Dans l’exemple ci-dessous, nous avons utilisé 1 comme type de correspondance et nous recherchons la valeur 5.

Comme je l’ai déjà mentionné, si vous utilisez 1 dans le type de correspondance, il renvoie la plus grande valeur égale ou inférieure à la valeur de recherche. Dans toute la liste, il y a 3 valeurs inférieures à 5 et 4 est la plus élevée.

C’est pourquoi dans le résultat, il a renvoyé 3 qui est la position de la valeur 4.

13. Fonction de décalage

La fonction DECALER renvoie une référence à une plage qui est à un nombre spécifique de lignes et de colonnes d’une cellule ou d’une plage de cellules. En termes simples, vous pouvez faire référence à une cellule ou à une plage de cellules en utilisant des lignes et des colonnes à partir d’une cellule de départ.

Syntaxe

DECALAGE(référence, lignes, colonnes, [hauteur], [largeur])

Arguments

• reference : La référence à partir de laquelle vous voulez décaler pour commencer. Il peut s’agir d’une cellule ou d’une plage de cellules adjacentes.
• rows : Le nombre de lignes qui indiquent à OFFSET de se déplacer vers le haut ou vers le bas à partir de la référence. Pour descendre, il faut un nombre positif et pour monter, il faut un nombre négatif.
• cols : Le nombre de colonnes indique à OFFSET de se déplacer vers la gauche ou la droite à partir de la référence. Pour aller à droite, vous avez besoin d’un nombre positif et pour aller à gauche, vous avez besoin d’un nombre négatif.
• [hauteur] : un nombre pour spécifier les lignes à inclure dans la référence.
• [width] : un nombre pour spécifier les colonnes à inclure dans la référence.

Remarques

• OFFSET est une fonction « volatile », elle recalcule chaque fois qu’il y a un changement dans une feuille de calcul.
• Il affiche le #REF! valeur d’erreur si le décalage est en dehors du bord de la feuille de calcul.
• Si la hauteur ou la largeur est omise, la hauteur et la largeur de référence sont utilisées.

Exemple

Dans l’exemple ci-dessous, nous avons utilisé SUM avec OFFSET pour créer une plage dynamique qui additionne les valeurs de tous les mois pour un produit particulier.

14. Fonction LIGNE

La fonction ROW renvoie le numéro de ligne de la cellule référencée. En termes simples, avec la fonction ROW, vous pouvez obtenir le numéro de ligne d’une cellule et si vous ne faites référence à aucune cellule, elle renvoie le numéro de ligne de la cellule où vous l’insérez.

Syntaxe

LIGNE([référence])

Arguments

• référence : une référence de cellule ou une plage de cellules dont vous souhaitez vérifier le numéro de ligne.

Remarques

• Il comprendra tous les types de feuilles (feuille de graphique, feuille de travail ou feuille de macro).
• Vous pouvez faire référence à des folios même s’ils sont visibles, cachés ou très cachés.
• Si vous ne spécifiez aucune valeur dans la fonction, cela vous donnera le numéro de feuille de la feuille dans laquelle vous avez appliqué la fonction.
• Si vous spécifiez un nom de feuille non valide, il renverra un #N/A.
• Si vous spécifiez une référence de feuille invalide, elle renverra un #REF!.

Exemple

Dans l’exemple ci-dessous, nous avons utilisé la fonction de ligne pour vérifier le numéro de ligne de la même cellule où nous avons utilisé la fonction.

Dans l’exemple ci-dessous, nous avons fait référence à une autre cellule pour obtenir le numéro de ligne de cette cellule.

Vous pouvez utiliser la fonction de ligne pour créer une liste de numéros de série dans votre feuille de calcul. Tout ce que vous avez à faire est simplement d’entrer des fonctions de ligne dans une cellule et de la faire glisser jusqu’à la cellule dans laquelle vous souhaitez ajouter des numéros de série.

15. Fonction LIGNES

La fonction ROWS renvoie le nombre de lignes de la plage référencée. En termes simples, avec la fonction ROWS, vous pouvez compter le nombre de lignes dans la plage à laquelle vous vous êtes référé.

Syntaxe

LIGNES (tableau)

Arguments

• array : une référence de cellule ou un tableau pour vérifier le nombre de lignes.

Remarques

• Vous pouvez également utiliser une plage nommée.
• Il n’est pas concerné par les valeurs dans les cellules, il renverra simplement le nombre de lignes dans une référence.

Exemple

Dans l’exemple ci-dessous, nous avons fait référence à une plage verticale de 10 cellules et elle a renvoyé 10 dans le résultat car la plage comprend 10 lignes.

16. Fonction TRANSPOSER

La fonction TRANSPOSE modifie l’orientation d’une plage. En termes simples, en utilisant cette fonction, vous pouvez modifier les données d’une ligne dans une colonne et d’une colonne dans une ligne.

Syntaxe

TRANSPOSER (tableau)

Arguments

• array : un tableau ou une plage que vous souhaitez transposer.

Remarques

• Vous devez appliquer TRANSPOSE en tant que fonction de tableau, en utilisant le même nombre de cellules que vous avez dans votre plage source en appuyant sur Ctrl + Maj + Entrée.
• Si vous sélectionnez des cellules inférieures à la plage source, les données ne seront transposées que pour ces cellules.

Exemple

Ici, nous devons transposer les données de la plage B2: D4 à la plage G2 à I4 :

Pour cela, nous devons d’abord aller à la cellule G2 et sélectionner la plage de cellules jusqu’à I4.

Ensuite, entrez (=TRANSPOSER(B2:D4)) dans la cellule G2 et appuyez sur Ctrl+Maj+Entrée.

TRANSPOSE convertira les données des lignes en colonnes, et la formule que nous avons appliquée est une formule matricielle, vous ne pouvez pas en modifier une seule cellule.