Използване на функцията VLOOKUP за заместване на стойности

Който го мързи или няма време да чете – гледайте видеото. Подробности и нюанси в текста по-долу.

Формулиране на проблема

И така, имаме две маси – маса за поръчки и ценова листа:

Задачата е автоматично да замените цените от ценовата листа в таблицата с поръчки, като се фокусирате върху името на продукта, за да можете по-късно да изчислите цената.

Решение

В набора от функции на Excel, под категорията Препратки и масиви (Търсене и справка) има функция ВПР (VLOOKUP).Тази функция търси дадена стойност (в нашия пример това е думата „Ябълки“) в най-лявата колона на посочената таблица (ценова листа), движейки се отгоре надолу и след като я намери, показва съдържанието на съседната клетка (23 рубли) . Схематично работата на тази функция може да бъде представена така:

За по-лесно по-нататъшно използване на функцията направете едно нещо наведнъж – дайте собствено име на диапазона от клетки в ценовата листа. За да направите това, изберете всички клетки от ценовата листа, с изключение на „заглавката“ (G3: H19), изберете от менюто Вмъкване – Име – Присвояване (Вмъкване — Име — Дефиниране) или натиснете CTRL + F3 и въведете произволно име (без интервали) като Цена… Сега, в бъдеще, можете да използвате това име за връзка към ценовата листа.

Сега използваме функцията ВПР… Изберете клетката, в която ще бъде въведен (D3) и отворете раздела Формули – вмъкване на функция (Формули — Вмъкване на функция)… В категорията Препратки и масиви (Търсене и справка) намерете функцията ВПР (VLOOKUP) и натиснете OK… Ще се появи прозорец за въвеждане на аргументи за функцията:

Използване на функцията VLOOKUP за заместване на стойности

Попълваме ги на свой ред:

  • Желана стойност (Търсена стойност) – името на продукта, който функцията трябва да намери в най-лявата колона на ценовата листа. В нашия случай думата „Ябълки“ от клетка B3.
  • Маса (Табличен масив) – таблица, от която са взети желаните стойности uXNUMXbuXNUMXbar, тоест нашата ценова листа. За справка използваме нашето собствено име „Цена“, дадено по-рано. Ако не сте дали име, можете просто да изберете таблицата, но не забравяйте да натиснете бутона F4за да закачите връзката със знаци за долар, защото в противен случай тя ще се плъзне надолу, когато копираме нашата формула надолу към останалите клетки в колона D3:D30.
  • Номер_на_колона (Индексен номер на колона) – пореден номер (не буква!) на колоната в ценовата листа, от която ще вземем ценовите стойности. Първата колона от ценовата листа с имената е номерирана с 1, следователно ни трябва цената от колона с номер 2.
  • интервал_търсене (Търсене на обхват) – само две стойности могат да бъдат въведени в това поле: FALSE или TRUE:
      • Ако е въведена стойност 0 or ЛЪЖА (НЕВЯРНО), тогава всъщност това означава, че е разрешено само търсене точно съвпадение, т.е. ако функцията не намери нестандартния артикул, посочен в таблицата за поръчки в ценовата листа (ако е въведено например „Кокос“), ще генерира грешка #N/A (няма данни).
      • Ако е въведена стойност 1 or TRUE (ВЯРНО), то това означава, че разрешавате търсенето не на точното, а на приблизително съвпадение, т.е. в случай на „кокос“, функцията ще се опита да намери продукт с име, което е възможно най-близко до „кокос“ и ще върне цената за това име. В повечето случаи такава приблизителна замяна може да изиграе номер на потребителя, като замени стойността на грешния продукт, който всъщност е бил там! Така че за повечето реални бизнес проблеми е по-добре да не допускате приблизително търсене. Изключение е, когато търсим числа, а не текст – например при изчисляване на стъпкови отстъпки.

Всичко! Остава да натиснете OK и копирайте въведената функция в цялата колона.

# N / A грешки и тяхното потискане

функция ВПР (VLOOKUP) връща грешка #N/A (#N/A) ако:

  • Активирано точно търсене (аргумент Интервален изглед = 0) и желаното име не е вътре Маса.
  • Включено грубо търсене (Интервален изглед = 1), но Маса, в който се извършва търсенето, не е сортиран във възходящ ред на имената.
  • Форматът на клетката, от която идва необходимата стойност на името (например B3 в нашия случай) и форматът на клетките от първата колона (F3: F19) на таблицата са различни (например числови и текстови ). Този случай е особено типичен при използване на цифрови кодове (номера на сметки, идентификатори, дати и др.) вместо текстови имена. В този случай можете да използвате функциите Ч и ТЕКСТ за конвертиране на формати на данни. Ще изглежда нещо подобно:

    =VLOOKUP(ТЕКСТ(B3),цена,0)

    Можете да прочетете повече за това тук.

  • Функцията не може да намери необходимата стойност, тъй като кодът съдържа интервали или невидими непечатаеми знаци (нови редове и т.н.). В този случай можете да използвате текстови функции TRIM (ОТРИМ) и PRINT(ЧИСТ) за да ги премахнете:

    =VLOOKUP(ОТКРАЙНИ ПРОСТРАНСТВА(CLEAN(B3)),цена,0)

    =VLOOKUP(ПОДРЯЗВАНЕ(ИЗЧИСТВАНЕ(B3));цена;0)

За да потиснете съобщението за грешка # N / A (#N/A) в случаите, когато функцията не може да намери точно съвпадение, можете да използвате функцията АКО ГРЕШКА (ГРЕШКА)… Така, например, тази конструкция прихваща всички грешки, генерирани от VLOOKUP, и ги замества с нули:

= IFERROR (VLOOKUP (B3, цена, 2, 0), 0)

= IFERROR (VLOOKUP (B3; цена; 2; 0); 0)

PS

Ако трябва да извлечете не една стойност, а целия набор наведнъж (ако има няколко различни), тогава ще трябва да шаманизирате с формулата на масива. или използвайте новата функция XLOOKUP от Office 365.

 

  • Подобрена версия на функцията VLOOKUP (VLOOKUP 2).
  • Бързо изчисляване на отстъпките за стъпки (диапазон) с помощта на функцията VLOOKUP.
  • Как да направите „ляв VLOOKUP“ с помощта на функциите INDEX и MATCH
  • Как да използвате функцията VLOOKUP за попълване на формулярите с данни от списъка
  • Как да извадите не първите, а всички стойности от таблицата наведнъж
  • Функции VLOOKUP2 и VLOOKUP3 от добавката PLEX

 

Оставете коментар