Table des matières
Qui est trop paresseux ou n'a pas le temps de lire - regardez la vidéo. Les détails et les nuances sont dans le texte ci-dessous.
Formulation du problème
Donc, nous avons deux tables - tableau de commande и liste de prix:
La tâche consiste à substituer automatiquement les prix de la liste de prix dans le tableau des commandes, en se concentrant sur le nom du produit afin que vous puissiez ensuite calculer le coût.
Solution
Dans le jeu de fonctions Excel, sous la catégorie Références et tableaux (Recherche et référence) il y a une fonction VPR (VLOOKUP).Cette fonction recherche une valeur donnée (dans notre exemple, il s'agit du mot "Pommes") dans la colonne la plus à gauche du tableau spécifié (liste de prix) en se déplaçant de haut en bas et, après l'avoir trouvée, affiche le contenu de la cellule adjacente (23 roubles) .Schématiquement, le fonctionnement de cette fonction peut être représenté Ainsi :
Pour faciliter l'utilisation ultérieure de la fonction, faites une chose à la fois - donnez votre propre nom à la plage de cellules de la liste de prix. Pour cela, sélectionnez toutes les cellules de la liste de prix sauf le « header » (G3 : H19), sélectionnez dans le menu Insérer – Nom – Attribuer (Insérer — Nom — Définir) ou appuyez sur CTRL + F3 et entrez n'importe quel nom (sans espaces) comme Prix… Maintenant, à l'avenir, vous pourrez utiliser ce nom pour créer un lien vers la liste de prix.
Maintenant, nous utilisons la fonction VPR… Sélectionnez la cellule où il sera saisi (D3) et ouvrez l'onglet Formules – Insertion de fonctions (Formules — Insérer une fonction)… Dans la catégorie Références et tableaux (Recherche et référence) trouver la fonction VPR (VLOOKUP) et appuyez sur OK… Une fenêtre de saisie des arguments de la fonction apparaît :
Nous les remplissons tour à tour :
- Valeur souhaitée (Valeur de recherche) – le nom du produit que la fonction doit trouver dans la colonne la plus à gauche de la liste de prix. Dans notre cas, le mot "Pommes" de la cellule B3.
- lampe de table (tableau) - un tableau à partir duquel les valeurs souhaitées uXNUMXbuXNUMX sont extraites, c'est-à-dire notre liste de prix. Pour référence, nous utilisons notre propre nom "Prix" donné plus tôt. Si vous n'avez pas donné de nom, vous pouvez simplement sélectionner la table, mais n'oubliez pas d'appuyer sur le bouton F4épingler le lien avec des signes dollar, car sinon, il glissera vers le bas lors de la copie de notre formule vers le reste des cellules de la colonne D3: D30.
- Numéro_colonne (Numéro d'index de la colonne) – numéro de série (pas une lettre!) De la colonne de la liste de prix à partir de laquelle nous prendrons les valeurs de prix. La première colonne de la liste de prix avec les noms est numérotée 1, nous avons donc besoin du prix de la colonne numérotée 2.
- recherche_intervalle (Recherche de plage) – seules deux valeurs peuvent être saisies dans ce champ : FALSE ou TRUE :
- Si une valeur est entrée 0 or COUCHÉ (FAUX), cela signifie en fait que seule la recherche est autorisée correspondance exacte, c'est-à-dire que si la fonction ne trouve pas l'article non standard spécifié dans la table de commande dans la liste de prix (si « Noix de coco » est renseigné, par exemple), elle générera l'erreur #N/A (pas de données).
- Si une valeur est entrée 1 or VRAI (VRAI), cela signifie que vous autorisez la recherche non pas pour l'exact, mais correspondance approximative, c'est-à-dire dans le cas de « noix de coco », la fonction va essayer de trouver un produit avec un nom qui se rapproche le plus possible de « noix de coco » et retourner le prix pour ce nom. Dans la plupart des cas, une telle substitution approximative peut jouer un tour à l'utilisateur en substituant la valeur du mauvais produit qui s'y trouvait en réalité ! Ainsi, pour la plupart des problèmes commerciaux réels, il est préférable de ne pas autoriser la recherche approximative. L'exception est lorsque nous recherchons des nombres et non du texte - par exemple, lors du calcul des remises d'étape.
Tout! Il reste à appuyer OK et copiez la fonction saisie dans la colonne entière.
# Erreurs N/A et leur suppression
Fonction VPR (VLOOKUP) renvoie l'erreur #N/A (#N / A) si un:
- Recherche exacte activée (argument Vue d'intervalle = 0) et le nom souhaité n'est pas dans lampe de table.
- Recherche grossière incluse (Vue d'intervalle = 1), Mais lampe de table, dans lequel la recherche a lieu n'est pas triée par ordre croissant des noms.
- Le format de la cellule d'où provient la valeur requise du nom (par exemple, B3 dans notre cas) et le format des cellules de la première colonne (F3 : F19) du tableau sont différents (par exemple, numérique et texte ). Ce cas est particulièrement typique lors de l'utilisation de codes numériques (numéros de compte, identifiants, dates, etc.) au lieu de noms textuels. Dans ce cas, vous pouvez utiliser les fonctions Ч и TEXTE pour convertir les formats de données. Cela ressemblera à ceci :
=RECHERCHEV(TEXTE(B3),prix,0)
Vous pouvez en savoir plus à ce sujet ici.
- La fonction ne trouve pas la valeur requise car le code contient des espaces ou des caractères invisibles non imprimables (sauts de ligne, etc.). Dans ce cas, vous pouvez utiliser les fonctions de texte TRIM (GARNITURE) и Print(NETTOYER) pour les supprimer :
=VLOOKUP(TRIMSPACES(CLEAN(B3)),prix,0)
=VLOOKUP(TRIM(CLEAN(B3));prix;0)
Pour supprimer le message d'erreur #N / A (#N / A) dans les cas où la fonction ne peut pas trouver une correspondance exacte, vous pouvez utiliser la fonction IFERREUR (SIERREUR)… Ainsi, par exemple, cette construction intercepte toutes les erreurs générées par VLOOKUP et les remplace par des zéros :
= SIERREUR (RECHERCHEV (B3, prix, 2, 0), 0)
= SIERREUR (RECHERCHEV (B3; prix; 2; 0); 0)
PS
Si vous avez besoin d'extraire non pas une valeur, mais l'ensemble complet à la fois (s'il y en a plusieurs différentes), alors vous devrez chamaniser avec la formule matricielle. ou utilisez la nouvelle fonctionnalité XLOOKUP d'Office 365.
- Une version améliorée de la fonction VLOOKUP (VLOOKUP 2).
- Calcul rapide des remises d'étape (gamme) à l'aide de la fonction VLOOKUP.
- Comment faire une « RECHERCHEV gauche » en utilisant les fonctions INDEX et MATCH
- Comment utiliser la fonction VLOOKUP pour remplir les formulaires avec les données de la liste
- Comment retirer non pas la première, mais toutes les valeurs du tableau à la fois
- Fonctions VLOOKUP2 et VLOOKUP3 du module complémentaire PLEX