VLOOKUP е една от най-полезните функции на Excel и също е една от най-слабо разбитите. В тази статия ние демотитираме VLOOKUP чрез пример за реалния живот. Ще създадем използваем Шаблон за фактури за фиктивна компания.
VLOOKUP е Excel функция, Тази статия ще приеме, че читателят вече има превъзходно разбиране на функциите на Excel и може да използва основни функции като SUM, AVERAGE и TODAY. В най-честата си употреба VLOOKUP е a база данни функция, което означава, че тя работи с таблици на базата данни - или по-просто, списъци от нещата в работен лист на Excel. Какви неща? Добре, който и да е нещо като нещо. Може да имате работен лист, който съдържа списък на служители, продукти, клиенти или компактдискове в компактдиска или звезди на нощното небе. Няма значение.
Ето един пример за списък или база данни. В този случай това е списък на продуктите, които нашата фиктивна компания продава:
Обикновено списъците като този имат някакъв уникален идентификатор за всеки елемент в списъка. В този случай уникалният идентификатор е в графата "Код на елемента". Забележка: За да може функцията VLOOKUP да работи с база данни / списък, този списък трябва да има колона, съдържаща уникалния идентификатор (или "ключ" или "ID") и тази колона трябва да бъде първата колона в таблицата, Нашата примерна база данни по-горе удовлетворява този критерий.
Най-трудната част от използването на VLOOKUP е да разберете точно какво е за него. Затова нека видим дали първо можем да получим това ясно:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
В горния пример ще вмъкнете функцията VLOOKUP в друга електронна таблица с код на елемента и ще ви върне или описанието на съответния елемент, неговата цена или наличността му (неговата "наличност"), както е описано в оригинала списък. Коя от тези данни ще ви върне обратно? Е, можете да решите това, когато създавате формулата.
Ако всичко, от което се нуждаете, е една част от информацията от базата данни, би било много трудно да отидете, за да създадете формула с функция VLOOKUP в нея. Обикновено бихте използвали този вид функционалност в електронна таблица за многократна употреба, като шаблон. Всеки път, когато някой въведе валиден код на артикула, системата ще извлече цялата необходима информация за съответния елемент.
Нека да създадем пример за това: An Шаблон за фактури че можем да използваме отново и отново в нашата фиктивна компания.
Първо стартираме Excel и създаваме празна фактура:
Ето как ще работи: Лицето, което използва шаблона за фактури, ще попълни поредица от кодове на елементи в колона "А" и системата ще извлече описание и цена на всеки елемент от нашата продуктова база данни. Тази информация ще се използва за изчисляване на общия брой линии за всеки елемент (ако приемем, че въвеждаме валидно количество).
За да поддържаме този пример прост, ще намерим базата данни на продукта на отделен лист в същата работна книга:
Така че, ние сме създали нашата продуктова база данни, която изглежда така:
Трябва да намерим списъка с всички налични функции, които Excel може да предложи, така че да можем да изберем VLOOKUP и да получим помощ при попълването на формулата. Това се установява, като първо щракнете върху Формули, след което щракнете върху Вмъкване на функция:
За да намерите този, който търсите, бихме могли да въведем дума за търсене като "търсене" (защото функцията, от която се интересуваме, е погледни нагоре функция). Системата ще ни върне списък с всички функции, свързани с търсенето в Excel. VLOOKUP е втората в списъка. Изберете го с едно кликване Добре.
Най- Функции на аргументите се появява, което ни подсказва за всички аргументи (или параметри), необходими за завършване на функцията VLOOKUP. Можете да мислите за тази кутия като функцията, която ни задава следните въпроси:
- Какъв уникален идентификатор търсите в базата данни?
- Къде е базата данни?
- Коя информация от базата данни, свързана с уникалния идентификатор, искате ли да бъдете извлечени за вас?
Показани са първите три аргумента с удебелен, което показва, че те са задължителен аргументи (функцията VLOOKUP е непълна без тях и няма да върне валидна стойност). Четвъртият аргумент не е смел, което означава, че е по избор:
Първият аргумент, който трябва да завършим, е Lookup_value аргумент. Функцията ни изисква да кажем къде да намери уникалния идентификатор ( кода на елемента в този случай), че трябва да върне описанието на. Трябва да изберем кода на елемента, който въведохме по-рано (в A11).
Кликнете върху иконата на селектора вдясно от първия аргумент:
След това кликнете веднъж върху клетката, съдържаща кода на елемента (A11), и натиснете Въведете:
Сега трябва да въведем стойност за Table_array аргумент. С други думи, трябва да кажем на VLOOKUP къде да намери базата данни / списъка. Кликнете върху иконата на селектора до втория аргумент:
… и натиснете Въведете, Обхватът на клетките, които представляват базата данни (в този случай "Продуктова база данни"! A2: D7 ") се въвежда автоматично за нас във втория аргумент.
Сега трябва да въведете третия аргумент, Col_index_num, Използваме този аргумент, за да уточним на VLOOKUP каква част от информацията от базата данни, свързана с кода на елемента в A11, искаме да се върнем при нас. В този конкретен пример искаме да разполагаме с елементите описание се върна при нас. Ако погледнете в работния лист на базата данни, ще забележите, че колоната "Описание" е втори в базата данни. Това означава, че трябва да въведем стойност "2" в Col_index_num кутия:
Важно е да отбележим, че тук не влизаме в "2", защото колоната "Описание" е в B в този работен лист. Ако базата данни стартира в колона K от работния лист все пак ще въведем "2" в това поле, защото колоната "Описание" е втората колона в набор от клетки, които сме избрали при посочването на "Table_array".
И накрая, трябва да решим дали да въведем стойност в крайния аргумент VLOOKUP, Range_lookup, Този аргумент изисква или a вярно или фалшив стойност или тя трябва да остане празна. При използването на VLOOKUP с бази данни (както е вярно 90% от времето), начинът да се реши какво да се постави в този аргумент може да се мисли, както следва:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
Първата колона на нашата база данни е не сортирани, влизаме фалшив в този аргумент:
Това е! Въвели сме цялата информация, необходима за VLOOKUP, за да върнем необходимата ни стойност. Кликнете върху Добре и забележете, че описанието, съответстващо на кода на елемента "R99245", е въведено правилно в клетка B11:
Ако влезем в различен кода на елемента в клетка A11, ще започнем да виждаме силата на функцията VLOOKUP: клетката за описание се променя, за да съответства на новия код на елемента:
Можем да изпълним подобен набор от стъпки, за да получим елементите цена връща се в клетка Е11. Обърнете внимание, че новата формула трябва да бъде създадена в клетка E11. Резултатът ще изглежда така:
… и формулата ще изглежда така:
Имайте предвид, че единствената разлика между двете формули е третият аргумент (Col_index_num) се е променило от "2" на "3" (защото искаме данните да бъдат извлечени от 3-та колона в базата данни).
Ако решихме да купим 2 от тези елементи, щяхме да въведем "2" в клетка D11. След това ще въведем проста формула в клетка F11, за да получим общия брой линии:
=D11*E11
… което изглежда така …
Завършване на шаблона за фактури
Научихме много за VLOOKUP досега. Всъщност научихме всичко, което ще научим в тази статия. Важно е да се отбележи, че VLOOKUP може да се използва при други обстоятелства освен бази данни. Това е по-рядко срещано и може да бъде обхванато в бъдещите статии "Как да".
Нашият шаблон за фактури все още не е завършен. За да го завършим, ще направим следното:
-
Ще премахнем примерния код на елемента от клетка А11 и "2" от клетка D11. Това ще накара новосъздадените формули VLOOKUP да показват съобщения за грешки:
Image Можем да отстраним това чрез разумно използване на Excel IF () и ISBLANK () функции. Променяме формулата си от това … = VLOOKUP (A11, "Продуктова база данни"! A2: D7,2, FALSE) …до това… = IF (ISBLANK (A11), "", VLOOKUP (A11, "Продуктова база данни"! A2: D7,2, FALSE))
- Бихме копирали формулите в клетките B11, E11 и F11 до остатъка от редовете на елементите на фактурата. Имайте предвид, че ако го направим, получените формули вече няма да се отнасят правилно до таблицата на базата данни. Можем да решим това, като променим референтните клетки за базата данни абсолютен референтни клетки. Алтернативно - и още по-добре - бихме могли да създадем име на диапазона за цялата продуктова база данни (например "Продукти") и използвайте това име на диапазона вместо референтните клетки. Формулата ще се промени от това … = IF (ISBLANK (A11), "", VLOOKUP (A11, "Продуктова база данни"! A2: D7,2, FALSE)) …до това… = IF (ISBLANK (A11), "", VLOOKUP (A11, продукти, 2, FALSE)) …и тогава копирайте формулите до останалите редове на елементите на фактурата.
- Вероятно ще заключим клетките, които съдържат нашите формули (или по-скоро отключвам на друг клетки) и след това да защитим работния лист, за да гарантираме, че внимателно изработените ни формули не случайно ще бъдат презаписани, когато някой попълни фактурата.
- Ще запазим файла като a шаблон, така че да може да се използва отново от всички в нашата компания
Ако се чувствахме наистина ли бихме създали база данни на всички наши клиенти в друг работен лист и след това използваме идентификационния номер на клиента, въведен в клетка F5, за да попълним автоматично името и адреса на клиента в клетките B6, B7 и B8.
