Как да използвам VLOOKUP в Excel

VLOOKUP е една от най-полезните функции на Excel и освен това е една от най-малко разбираемите. В тази статия ние демистифицираме VLOOKUP като пример от реалния живот. Ще създадем използваем шаблон за фактура за фиктивна компания.

VLOOKUP е функция на Excel . Тази статия ще приеме, че четецът вече има кратко разбиране за функциите на Excel и може да използва основни функции като SUM, AVERAGE и TODAY. В най-често срещаната си употреба VLOOKUP е функция на база данни , което означава, че работи с таблици на базата данни - или по-просто, списъци с неща в работен лист на Excel. Какви неща? Е, всякакви неща. Може да имате работен лист, който съдържа списък със служители, или продукти, или клиенти, или компактдискове във вашата CD колекция, или звезди на нощното небе. Всъщност няма значение.

Ето пример за списък или база данни. В този случай това е списък с продукти, които нашата фиктивна компания продава:

Обикновено списъци като този имат някакъв уникален идентификатор за всеки елемент в списъка. В този случай уникалният идентификатор е в колоната „Код на артикула“. Забележка: За да работи функцията VLOOKUP с база данни / списък, този списък трябва да има колона, съдържаща уникалния идентификатор (или „ключ“, или „ID“), и тази колона трябва да бъде първата колона в таблицата . Нашата примерна база данни по-горе отговаря на този критерий.

Най-трудната част от използването на VLOOKUP е разбирането точно за какво служи. Така че нека видим дали можем първо да разберем това:

VLOOKUP извлича информация от база данни / списък въз основа на предоставен екземпляр на уникалния идентификатор.

В горния пример бихте вмъкнали функцията VLOOKUP в друга електронна таблица с код на артикул и тя ще ви върне или съответното описание на артикула, цената му или наличността му (количеството „На склад“), както е описано във вашия оригинал списък. Коя от тези части информация ще ви предаде обратно? Е, вие трябва да решите това, когато създавате формулата.

Ако всичко, от което се нуждаете, е една информация от базата данни, би било много проблем да отидете, за да изградите формула с функция VLOOKUP в нея. Обикновено бихте използвали този вид функционалност в електронна таблица за многократна употреба, като шаблон. Всеки път, когато някой въведе валиден код на артикула, системата извлича цялата необходима информация за съответния елемент.

Нека създадем пример за това: Шаблон за фактура, който можем да използваме отново и отново в нашата фиктивна компания.

Първо стартираме Excel и си създаваме празна фактура:

Ето как ще работи: Човекът, който използва шаблона за фактура, ще попълни поредица кодове на артикули в колона „А“ и системата ще извлече описанието и цената на всеки артикул от нашата база данни с продукти. Тази информация ще се използва за изчисляване на сумата на редовете за всеки елемент (ако приемем, че въвеждаме валидно количество).

За да улесним този пример, ще намерим продуктовата база данни на отделен лист в същата работна книга:

В действителност е по-вероятно продуктовата база данни да се намира в отделна работна книга. Това няма голяма разлика за функцията VLOOKUP, която всъщност не се интересува дали базата данни се намира на един и същ лист, различен лист или съвсем различна работна книга.

И така, ние създадохме нашата база данни за продукти, която изглежда така:

За да тестваме формулата VLOOKUP, която предстои да напишем, първо въвеждаме валиден код на артикул в клетка A11 на нашата празна фактура:

След това преместваме активната клетка в клетката, в която искаме да се съхранява информация, извлечена от базата данни от VLOOKUP. Интересното е, че това е стъпката, която повечето хора сбъркват. За да обясним допълнително: Предстои да създадем формула VLOOKUP, която ще извлече описанието, което съответства на кода на артикула в клетка A11. Къде искаме това описание да бъде поставено, когато го получим? В клетка B11, разбира се. Така че там записваме формулата VLOOKUP: в клетка B11. Изберете клетка B11 ​​сега.

Трябва да намерим списъка с всички налични функции, които Excel може да предложи, за да можем да изберем VLOOKUP и да получим някаква помощ при попълването на формулата. Това се намира, като първо щракнете върху раздела Формули и след това щракнете върху Вмъкване на функция :

Появява се поле, което ни позволява да изберем някоя от функциите, налични в Excel.

За да намерим този, който търсим, бихме могли да напишем дума за търсене като „lookup“ (защото функцията, която ни интересува, е функция за търсене ). Системата ще ни върне списък с всички функции, свързани с търсенето в Excel.  VLOOKUP е вторият в списъка. Изберете го, щракнете върху OK .

Появява се полето Аргументи на функции , което ни подканва за всички аргументи (или параметри ), необходими за завършване на функцията VLOOKUP. Можете да мислите за това поле като за функцията, която ни задава следните въпроси:

  1. Какъв уникален идентификатор търсите в базата данни?
  2. Къде е базата данни?
  3. Коя информация от базата данни, свързана с уникалния идентификатор, искате да бъде извлечена за вас?

Първите три аргумента са показани с удебелен шрифт , което показва, че те са задължителни аргументи (функцията VLOOKUP е непълна без тях и няма да върне валидна стойност). Четвъртият аргумент не е удебелен, което означава, че е по избор:

Ще попълним аргументите по ред, отгоре надолу.

Първият аргумент, който трябва да попълним, е аргументът Lookup_value . Функцията се нуждае от нея, за да й кажем къде да намери уникалния идентификатор ( в този случай кода на артикула ), на който трябва да връща описанието. Трябва да изберем кода на артикула, който сме въвели по-рано (в A11).

Щракнете върху иконата на селектора вдясно от първия аргумент:

След това щракнете веднъж върху клетката, съдържаща кода на артикула (A11), и натиснете Enter :

Стойността на „A11“ се вмъква в първия аргумент.

Сега трябва да въведем стойност за аргумента Table_array . С други думи, трябва да кажем на VLOOKUP къде да намери базата данни / списък. Кликнете върху иконата на селектора до втория аргумент:

Сега намерете базата данни / списъка и изберете целия списък - без да включва заглавния ред. В нашия пример базата данни се намира на отделен работен лист, така че първо кликваме върху раздела на този работен лист:

След това избираме цялата база данни, без да включва заглавния ред:

... и натиснете Enter . Диапазонът от клетки, който представлява базата данни (в случая „База данни на продукта! A2: D7“), се въвежда автоматично за нас във втория аргумент.

Сега трябва да въведем третия аргумент, Col_index_num . Използваме този аргумент, за да посочим на VLOOKUP коя информация от базата данни, асоциирана с кода на елемента ни в A11, бихме искали да ни е върната. В този конкретен пример искаме да ни се върне описанието на артикула . Ако погледнете работния лист на базата данни, ще забележите, че колоната „Описание“ е втората колона в базата данни. Това означава, че трябва да въведем стойност „2“ в полето Col_index_num :

Важно е да се отбележи, че тук не въвеждаме „2“, тъй като колоната „Описание“ е в колоната Б на този работен лист. Ако случайно базата данни започне в колона K на работния лист, все пак ще въведем „2“ в това поле, тъй като колоната „Описание“ е втората колона в набор от клетки, които сме избрали при задаване на „масив_маси“.

И накрая, трябва да решим дали да въведем стойност в последния аргумент VLOOKUP, Range_lookup . Този аргумент изисква или вярна, или невярна стойност, или трябва да се остави празно. Когато използвате VLOOKUP с бази данни (както е вярно в 90% от случаите), начинът да решите какво да поставите в този аргумент може да се мисли по следния начин:

Ако първата колона на базата данни (колоната, която съдържа уникалните идентификатори) е сортирана по азбучен ред / число във възходящ ред, тогава е възможно да се въведе стойност true в този аргумент или да се остави празно.

Ако първата колона на базата данни не е сортирана или е сортирана в низходящ ред, тогава трябва да въведете стойност false в този аргумент

Тъй като първата колона от нашата база данни не е сортирана, ние въвеждаме false в този аргумент:

Това е! Въведохме цялата информация, необходима на VLOOKUP, за да върне необходимата ни стойност. Щракнете върху бутона OK и забележете, че описанието, съответстващо на код на артикул „R99245“, е въведено правилно в клетка B11:

Формулата, която беше създадена за нас, изглежда така:

Ако въведем различен код на артикул в клетка A11, ще започнем да виждаме силата на функцията VLOOKUP: Клетката с описание се променя, за да съответства на новия код на елемента:

Можем да извършим подобен набор от стъпки, за да върнем цената на артикула в клетка E11. Обърнете внимание, че новата формула трябва да бъде създадена в клетка E11. Резултатът ще изглежда така:

... и формулата ще изглежда така:

Имайте предвид, че единствената разлика между двете формули е третият аргумент ( Col_index_num ) се е променил от „2“ на „3“ (защото искаме данните да бъдат извлечени от третата колона в базата данни).

Ако решихме да закупим 2 от тези артикули, щяхме да въведем „2“ в клетка D11. След това щяхме да въведем проста формула в клетка F11, за да получим общия ред:

= D11 * E1

... което изглежда така ...

Попълване на шаблона за фактура

Досега научихме много за VLOOKUP. Всъщност научихме всичко, което ще научим в тази статия. Важно е да се отбележи, че VLOOKUP може да се използва и при други обстоятелства освен бази данни. Това е по-рядко и може да бъде отразено в бъдещи статии с инструкции за Geek.

Шаблонът ни за фактура все още не е завършен. За да го завършим, бихме направили следното:

  1. Ще премахнем примерния код на елемент от клетка A11 и „2“ от клетка D11. Това ще накара новосъздадените формули VLOOKUP да показват съобщения за грешка:



    Можем да коригираме това чрез разумно използване на функциите на Excel IF () и ISBLANK () . Променяме формулата си от тази ...      = VLOOKUP (A11, 'База данни на продукта'! A2: D7,2, FALSE) ... на тази ... = IF (ISBLANK (A11), ””, VLOOKUP (A11, 'База данни на продукта'! A2 : D7,2, FALSE))


  2. Бихме копирали формулите в клетки B11, E11 и F11 до останалата част от редовете на артикулите във фактурата. Имайте предвид, че ако направим това, получените формули вече няма да се отнасят правилно към таблицата на базата данни. Можем да поправим това, като променим препратките към клетките за базата данни на абсолютни препратки към клетки. Като алтернатива - и дори по-добре - бихме могли да създадем име на диапазон за цялата продуктова база данни (като „Продукти“) и да използваме това име на диапазон вместо препратките към клетките. Формулата ще се промени от това ...      = IF (ISBLANK (A11), ””, VLOOKUP (A11, 'База данни на продуктите! A2: D7,2, FALSE)) ... до това ...       = IF (ISBLANK (A11), ”” , VLOOKUP (A11, Продукти, 2, FALSE)) ... и след това копирайте формулите до останалите редове на елемента на фактурата.
  3. Бихме вероятно "заключване" на клетките, които съдържат нашите формули (или по-скоро отключване на други клетки), а след това се защити лист, за да се гарантира, че нашите внимателно построени формули не са случайно презаписани, когато някой идва да запълни във фактурата.
  4. Бихме запазили файла като шаблон , за да може той да бъде използван повторно от всички в нашата компания

Ако се чувствахме много умно, щяхме да създадем база данни на всички наши клиенти в друг работен лист и след това да използваме идентификатора на клиента, въведен в клетка F5, за да попълним автоматично името и адреса на клиента в клетки B6, B7 и B8.

Ако искате да практикувате с VLOOKUP или просто да видите резултата от нашия шаблон за фактура, той може да бъде изтеглен от тук.