Comment utiliser RECHERCHEV, INDEX, ... dans Excel, vous devez savoir

Les fonctions RECHERCHEV, INDEX, MATCH, ... sont des fonctions assez complexes mais sont efficaces dans l'exploration de données sous Excel . Dans cet article, je vais vous guider en détail comment utiliser VLOOKUP et les fonctions associées efficacement.

1. Fonction RECHERCHEV

Utilisation : recherche une valeur spécifiée dans la première colonne et extrait les données correspondantes de la même ligne dans une autre colonne. C'est l'une des fonctions les plus populaires et fournit un excellent support pour les tâches de bureau complexes.

Formule : = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Dans lequel :

+ Lookup_value : La valeur à rechercher.

+ Table_array : deux colonnes ou plus de données.

+ Col_index_num : Nombre de colonnes pour extraire les données.

+ Range_lookup : détermine si la recherche est complètement précise (FALSE) ou simplement relativement précise (TRUE ou omise).

Exemple : Nous utilisons la fonction RECHERCHEV pour trouver le Bonus de chaque employé. La valeur à rechercher est la position de cellule C2; Le tableau à détecter est le tableau F1: G3. Lorsque vous entrez la formule = RECHERCHEV (C2, $ F $ 1: $ G $ 3,2,0) (1), le résultat est STAFF 2000 et ROOMER 5000 (2).

2. Fonction INDEX

Utilisation : renvoie une référence à une cellule à l'intérieur d'un tableau en fonction du nombre de lignes et de colonnes que vous spécifiez.

Formule : = INDEX (tableau, numéro_ligne, [numéro_colonne])

Dont : Tableau (obligatoire): Une plage de cellules ou une constante de tableau.

Exemple : Lorsque nous avons besoin d'obtenir la valeur de la première ligne, la deuxième colonne du tableau, nous utilisons la fonction INDEX . Array est le tableau que nous devons détecter est B2: C5; Row_num est la ligne 1; Column_num est la colonne 2. Lorsque vous entrez la formule INDEX (B2: C5,1,2,1) (1), le résultat est "HEAD OF ROOM" (2).

3. Fonction MATCH

Utilisation : recherche une valeur spécifique dans une plage de cellules et affiche la position relative de cette valeur.

Formule : = MATCH (lookup_value, lookup_array, [match_type])

Dans lequel :

+ lookup_value (obligatoire) : la valeur que vous souhaitez mettre en correspondance dans le tableau de recherche. Modifier le numéro de valeur de recherche peut être une valeur (nombre, texte ou valeur logique) ou une référence de cellule à un nombre, un texte ou une valeur logique.

+ lookup_array (obligatoire) : La plage de cellules à rechercher.

+ match_type (facultatif) : nombre -1, 0 ou 1. L'argument de type de correspondance spécifie comment Excel fait correspondre la valeur de recherche aux valeurs du tableau de recherche. La valeur par défaut de cet argument est 1.

Par exemple : lorsque nous avons besoin de savoir dans quelle position se trouve CLOTH, nous utilisons la fonction MATCH . Lorsque vous entrez la formule = MATCH (FF2 & "*", "B2: B7", 0) (1), le résultat est 3 (2).

3. Fonction INDIRECT

Utilisation : renvoie une référence de cellule ou une plage spécifiée par une chaîne de texte. Utilisez la fonction INDIRECT lorsque vous souhaitez modifier la référence à une cellule dans une formule sans modifier la formule elle-même.

Formule : = INDIRECT (ref_text, [a1])

Dans lequel :

+ Ref_text (obligatoire) : référence à une cellule contenant le style de référence A1, le style de référence R1C1 ou une référence à la cellule sous forme de chaîne de texte.

+ Si le texte de référence n'est pas une référence de cellule valide, INDIRECT renvoie la valeur d'erreur #REF! .

+ Si ref_text fait référence à un autre classeur (une référence externe), ce classeur doit être ouvert. Si le classeur source n'est pas ouvert, INDIRECT renvoie la valeur d'erreur #REF !. .

Par exemple : lorsque vous avez seulement besoin de calculer la commission et que vous souhaitez faire glisser, déposer ou déplacer la zone de résultat vers une autre position sans changer la formule monétaire, nous utilisons INDIRECT . Lors de la saisie de la formule

= INDIRECT («B2», TRUE) * INDIRECT («C2», TRUE) (1) donnera le résultat 700 * 100 = 70000 (2).

4. Fonction OFFSET

Utilisation : renvoie la référence à une plage par un nombre spécifié de lignes et un nombre spécifié de colonnes à partir d'une cellule ou d'une plage de cellules. La référence renvoyée peut être une seule cellule ou une plage de cellules. Vous pouvez spécifier le nombre de lignes et de colonnes à renvoyer.

Formule : = OFFSET (référence, lignes, cols, [hauteur], [largeur])

Dans lequel :

+ Référence (obligatoire) : la zone de référence sur laquelle vous souhaitez baser la distance de référence. La plage de référence doit faire référence à une cellule ou à une plage de cellules adjacentes; sinon, OFFSET renvoie la valeur d'erreur #VALUE !. .

+ Rows (obligatoire) : le nombre de lignes, vers le haut ou vers le bas, auxquelles la cellule supérieure gauche doit faire référence. Les lignes peuvent être positives (ce qui signifie en dessous de la référence de départ) ou négatives (ce qui signifie au-dessus de la référence de départ).

+ Cols (obligatoire) : Nombre de colonnes, à gauche ou à droite, auxquelles la cellule dans le coin supérieur gauche du résultat doit faire référence. Les colonnes peuvent être positives ou négatives.

+ Hauteur (facultatif) : La hauteur, en nombre de lignes, que vous souhaitez avoir pour la référence renvoyée. La hauteur doit être un nombre positif.

+ Largeur (facultatif) : La largeur, en nombre de colonnes, que vous souhaitez avoir pour la référence renvoyée. La largeur doit être un nombre positif.

Exemple : nous utilisons OFFSET pour localiser les valeurs, puis nous utilisons la fonction SOMME pour calculer la somme. La cellule à laquelle nous nous référons est A1; par rapport à A1, les lignes de la plage de référence sont inférieures à 1 cellule, les lignes sont donc 1; par rapport à A1, le Cols gauche est 1 cellule, donc Cols est 1; Hauteur nous prenons 3 lignes; Largeur, nous obtenons 2 colonnes. Ainsi, le résultat en additionnant toutes les valeurs de la région est 2025.

5. Fonction TRANSPOSE

Utilisation : convertit une plage horizontale de cellules en une plage verticale et vice versa, c'est-à-dire convertit les lignes en colonnes et les colonnes en lignes.

Formule : = TRANSPOSE (tableau)

Faire

+ Étape 1 : Sélectionnez des cellules vides.

+ Étape 2 : Entrez = TRANSPOSE (B2: C4).

+ Étape 3 : Entrez la plage de cellules d'origine.

+ Étape 4 : Enfin, appuyez sur CTRL + SHIFT + ENTRÉE.

Exemple : Lorsque nous avons besoin de convertir la liste de prix des produits entière de la verticale à l'horizontale, nous utilisons la fonction TRANSPOSE avec le tableau de valeurs B2 à C4. En entrant la formule = TRANSPOSE (B2: C4) (1) donnera le résultat comme indiqué (2).

6. Fonction HYPERLINK

Utilisations : crée un lien hypertexte vers un document stocké sur l'intranet ou Internet.

Formule : = HYPERLINK (emplacement_lien, [nom_amical]).

Dans lequel :

Link_location peut faire référence à un emplacement dans un document, comme une cellule spécifique ou une plage nommée dans une feuille de calcul ou dans un classeur Excel, ou à un signet dans un document Microsoft Word.

Le chemin peut être vers un fichier stocké sur le disque dur. Le chemin peut également être un chemin UNC (Universal Naming Convention) sur le serveur (dans Microsoft Excel pour Windows).

Exemple : les liens longs et déroutants seront ennuyeux pour l'utilisateur. Veuillez donc utiliser HYPERLINK pour rendre l'interface plus facile à utiliser. La saisie de la formule = HYPERLINK (A1, «LINK GOOGLE») (1) entraînera LINK GOOGLE (2).

L'article ci-dessus a guidé RECHERCHEV et les fonctions associées. J'espère que l'article ci-dessus vous sera utile.