La fonction VLOOKUP d'Excel permet de rechercher et de renvoyer des informations à partir d'une table de données en fonction d'une valeur de recherche choisie.
En règle générale, VLOOKUP requiert que la valeur de recherche se trouve dans la colonne la plus à gauche de la table de données et la fonction renvoie un autre champ de données situé dans la même ligne à droite de cette valeur.
Cependant, en combinant VLOOKUP avec la fonction CHOOSE, vous pouvez créer une formule de recherche à gauche qui:
- Permet à la valeur de recherche d'être de n'importe quelle colonne du tableau de données
- Renvoie des informations situées dans n'importe quelle colonne à gauche de la valeur de recherche.
Remarque: Ces instructions s'appliquent aux versions Excel 2019, 2016, 2013, 2010 et Excel pour Office 365.
01 de 05Didacticiel: Utilisation des fonctions VLOOKUP et CHOISIR dans une formule de recherche à gauche
Pour créer la formule de recherche de gauche vue dans l'exemple d'image, utilisez la formule:
= VLOOKUP ($ D $ 2, CHOISISSEZ ({1,2}, $ F: $ F, $ D: $ D), 2, FAUX)
Dans cet exemple, la formule permet de trouver la pièce fournie par les différentes sociétés répertoriées dans la colonne 3 du tableau de données.
Le travail de la fonction CHOISIR dans la formule consiste à faire croire à VLOOKUP que la colonne 3 est la colonne 1. Le nom de la société peut donc être utilisé comme valeur de recherche pour trouver le nom de la pièce fournie par chaque société.
Entrez les données du didacticiel
- Entrez le titre Fournisseur dans la cellule D1.
- Entrez le titre Partie en cellule E1.
- Entrez le tableau de données vu dans l'image ci-dessus dans les cellules D4 à F9
- Rangées 2 et 3 sont laissés en blanc pour répondre aux critères de recherche et à la formule de recherche de gauche créée au cours de ce tutoriel
Ouvrir la boîte de dialogue VLOOKUP
Bien qu'il soit possible de taper la formule directement dans la cellule F1 de la feuille de calcul, de nombreuses personnes ont des difficultés avec la syntaxe de la formule.
Dans ce cas, il est préférable d'utiliser la boîte de dialogue VLOOKUP. Presque toutes les fonctions d'Excel ont une boîte de dialogue qui vous permet de saisir chacun des arguments de la fonction sur une ligne séparée.
- Cliquez sur la cellule E2 de la feuille de calcul. E2 est l'emplacement où seront affichés les résultats de la formule de recherche de gauche.
- Clique sur le Formules onglet du ruban.
- Clique sur le Recherche et référence option dans le ruban pour ouvrir la liste déroulante des fonctions.
- Cliquer sur VLOOKUP dans la liste pour faire apparaître la boîte de dialogue de la fonction.
Saisie d'arguments dans la boîte de dialogue VLOOKUP
Les arguments d'une fonction sont les valeurs utilisées par la fonction pour calculer un résultat.
Dans la boîte de dialogue d'une fonction, le nom de chaque argument est placé sur une ligne séparée, suivie d'un champ dans lequel entrer une valeur.
Entrez les valeurs suivantes pour chacun des arguments de VLOOKUP sur la bonne ligne de la boîte de dialogue, comme indiqué dans l'image d'accompagnement.
La valeur de recherche
La valeur de recherche est le champ d'information utilisé pour rechercher le tableau de table. VLOOKUP renvoie un autre champ de données à partir de la même ligne que la valeur de recherche.
Cet exemple utilise une référence de cellule à l'emplacement où le nom de la société sera entré dans la feuille de calcul. L'avantage de cela est qu'il est facile de changer le nom de la société sans modifier la formule.
- Clique sur le lookup_value ligne dans la boîte de dialogue.
- Cliquez sur la cellule D2 d'ajouter cette référence de cellule à la lookup_value ligne.
- appuyez sur la F4 touche du clavier pour rendre la référence de cellule absolue - $ D $ 2.
Références de cellules absolues
Les références de cellules absolues sont utilisées pour les arguments de valeur de recherche et de tableau afin d'éviter les erreurs si la formule de recherche est copiée dans d'autres cellules de la feuille de calcul.
Entrer dans la fonction CHOISIR
L'argument de tableau est le bloc de données contiguës à partir duquel des informations spécifiques sont extraites.
En règle générale, VLOOKUP ne recherche qu'à la droite de l'argument de valeur de recherche pour rechercher des données dans le tableau. Pour obtenir une apparence gauche, VLOOKUP doit être trompé en réorganisant les colonnes du tableau à l'aide de la fonction CHOOSE.
Dans cette formule, la fonction CHOISIR accomplit deux tâches:
- Il crée un tableau dont la largeur ne dépasse pas deux colonnes (colonnes D et F).
- Il change l'ordre des colonnes du tableau de droite à gauche, de sorte que la colonne F arrive en premier et la colonne D en deuxième.
Fonctions de saisie
Lors de la saisie manuelle de fonctions, chaque argument de la fonction doit être séparé par une virgule.
- Dans la boîte de dialogue Fonction VLOOKUP, cliquez sur le bouton Tableau_Array ligne.
- Entrez ce qui suit CHOISIR une fonction:CHOISIR ({1,2}, $ F: $ F, $ D: $ D)
Numéro d'index de la colonne
Normalement, le numéro d'index de colonne indique quelle colonne du tableau contient les données que vous recherchez. Cependant, dans cette formule, il fait référence à l'ordre des colonnes défini par la fonction CHOOSE.
La fonction CHOOSE crée un tableau de la largeur de deux colonnes, la colonne F étant suivie de la colonne D. Etant donné que les informations recherchées (nom de la pièce) se trouvent dans la colonne D, la valeur de l'argument d'index de colonne doit être définie sur 2.
- Clique sur le Col_index_num ligne dans la boîte de dialogue.
- Type A 2 dans cette ligne.
Recherche de plage
L'argument Range_lookup de VLOOKUP est une valeur logique (TRUE ou FALSE uniquement) qui indique si vous voulez que VLOOKUP recherche une correspondance exacte ou approximative à la valeur de recherche.
- Si TRUE ou si cet argument est omis, VLOOKUP renvoie soit une correspondance exacte à Lookup_value ou, si aucune correspondance exacte n’est trouvée, VLOOKUP renvoie la plus grande valeur suivante. Pour que la formule puisse faire cela, les données de la première colonne de Table_array doivent être triées par ordre croissant.
- Si FALSE, VLOOKUP utilise uniquement une correspondance exacte avec Lookup_value. Si la première colonne de Table_array contient deux valeurs ou plus qui correspondent à la valeur de recherche, la première valeur trouvée est utilisée. Si aucune correspondance exacte n’est trouvée, une erreur # N / A est renvoyée.
Dans ce tutoriel, étant donné que nous recherchons un nom de pièce particulier, Range_lookup est défini sur Faux de sorte que seules les correspondances exactes sont retournées par la formule.
- Clique sur le Range_lookup ligne dans la boîte de dialogue.
- Entrez le mot Faux dans cette ligne pour indiquer que nous voulons que VLOOKUP renvoie une correspondance exacte pour les données recherchées.
- Cliquez sur D'accord pour compléter la formule de recherche de gauche et fermer la boîte de dialogue.
- Comme nous n'avons pas encore entré le nom de la société dans la cellule D2, une erreur # N / A apparaît dans la cellule E2.
Renvoi de données avec la formule de recherche de gauche
Pour trouver quelles entreprises fournissent quelles pièces, tapez le nom de l'entreprise dans la cellule D2 et appuyez sur le ENTRER touche du clavier.
Le nom de la pièce est affiché dans la cellule E2.
- Cliquez sur la cellule D2 dans votre feuille de calcul.
- Type Gadgets Plus dans la cellule D2 et appuyez sur le ENTRER touche du clavier.
- Le texte "Gadgets" - la pièce fournie par la société Gadgets Plus - doit être affiché dans la cellule E2.
Testez encore la formule de recherche en tapant d'autres noms de société dans la cellule D2. Le nom de la pièce correspondante doit apparaître dans la cellule E2.
Si un message d'erreur tel que # N / A apparaît dans la cellule E2, recherchez les erreurs d'orthographe dans la cellule D2.
04 de 05Création d'un tableau de table à deux colonnes
La syntaxe de la fonction CHOISIR est la suivante:
= CHOISIR (numéro_index, valeur1, valeur2, … valeur254)
La fonction CHOISIR renvoie généralement une valeur de la liste des valeurs (Valeur1 à Valeur254) en fonction du numéro d'index entré.
Si le numéro d'index est 1, la fonction renvoie Value1 à partir de la liste. si le numéro d'index est 2, la fonction renvoie Value2 à partir de la liste, etc.
Lorsque plusieurs numéros d'index sont entrés, la fonction renvoie plusieurs valeurs dans l'ordre souhaité. Obtenir CHOISIR de renvoyer plusieurs valeurs se fait en créant un tableau.
Pour entrer un tableau, entourez les nombres entrés avec des accolades ou des crochets. Deux numéros sont entrés pour le numéro d'index:{ 1,2 }.
Il convient de noter que CHOOSE n’est pas limité à la création d’un tableau à deux colonnes. En incluant un nombre supplémentaire dans le tableau, tel que {1,2,3}, et une plage supplémentaire dans l'argument de valeur, CHOOSE crée une table à trois colonnes.
Des colonnes supplémentaires vous permettent de retourner différentes informations avec la formule de recherche de gauche en modifiant l'argument numéro d'index de colonne de VLOOKUP en numéro de la colonne contenant les informations souhaitées.
05 de 05Modification de l'ordre des colonnes avec la fonction CHOISIR
Dans la fonction CHOISIR utilisée dans cette formule:
CHOISIR ({1,2}, $ F: $ F, $ D: $ D)
l'intervalle pour la colonne F est indiqué avant la colonne D.
Puisque la fonction CHOOSE définit le tableau de VLOOKUP (la source de données pour cette fonction), l'ordre de commutation des colonnes de la fonction CHOOSE est transmis à VLOOKUP.
En ce qui concerne VLOOKUP, le tableau de table ne comporte que deux colonnes de large, la colonne F à gauche et la colonne D à droite. Étant donné que la colonne F contient le nom de la société à rechercher et que la colonne D contient les noms des pièces, VLOOKUP peut effectuer ses tâches de recherche habituelles en recherchant des données situées à gauche de la valeur de recherche.
En conséquence, VLOOKUP peut utiliser le nom de la société pour rechercher la pièce fournie.
