VLOOKUP в Excel, част 2 Използване на VLOOKUP без база данни
В скорошна статия въведехме функцията Excel, наречена VLOOKUP и обясни как може да се използва за извличане на информация от база данни в клетка в локален работен лист. В тази статия споменахме, че има две употреби за VLOOKUP и само един от тях се занимава с търсене на бази данни. В тази статия, втората и последна в серията VLOOKUP, разглеждаме тази друга, по-малко известна употреба за функцията VLOOKUP.
Ако все още не сте го направили, моля, прочетете първата статия на VLOOKUP - тази статия ще приеме, че много от понятията, обяснени в тази статия, вече са известни на читателя.
Когато се работи с бази данни, VLOOKUP се предава “уникален идентификатор”, който служи за идентифициране на записа на данни, който желаем да намерим в базата данни (например продуктов код или клиентски идентификатор). Този уникален идентификатор трябва да съществуват в базата данни, в противен случай VLOOKUP ни връща грешка. В тази статия ще разгледаме начина на използване на VLOOKUP, където идентификаторът изобщо не трябва да съществува в базата данни. Това е почти така, сякаш VLOOKUP може да възприеме „достатъчно близо е достатъчно добър“ подход за връщане на данните, които търсим. При определени обстоятелства това е така точно това, от което се нуждаем.
Ще илюстрираме тази статия с пример от реалния свят - изчисляване на комисионните, генерирани на база данни за продажбите. Ще започнем с много прост сценарий и след това постепенно ще го направим по-сложен, докато единственото рационално решение на проблема е да се използва VLOOKUP. Първоначалният сценарий в нашата фиктивна компания работи по следния начин: Ако продавачът създаде повече от 30 000 долара продажби на стойност за дадена година, комисионната, която печелят от тези продажби, е 30%. В противен случай тяхната комисионна е само 20%. Досега това е доста прост работен лист:
За да използва този работен лист, продавачът вписва данните си за продажби в клетка B1, а формулата в клетка B2 изчислява правилната ставка на комисионната, която те имат право да получат, която се използва в клетка B3 за изчисляване на общата комисиона, която продавачът дължи ( е просто умножение на В1 и В2).
Клетката В2 съдържа единствената интересна част от този работен лист - формулата за вземане на решение коя комисионна да се използва: тази По-долу праг от 30 000 долара, или този по-горе прага. Тази формула използва функцията Excel, наречена АКО. За тези читатели, които не са запознати с IF, тя работи по следния начин:
IF (условие, стойност ако е вярно, стойност ако е невярна)
Където състояние е израз, който се оценява и за двете вярно или фалшив. В примера по-горе, състояние е изразът B1
Както можете да видите, използването на обща сума от $ 20,000 ни дава комисионна от 20% в клетка B2. Ако въведем стойност от $ 40,000, ще получим различен процент на комисиона:
Така че нашата електронна таблица работи.
Нека го направим по-сложен. Да въведем втори праг: Ако продавачът спечели повече от $ 40,000, тогава техният процент на комисионната се увеличава до 40%:
Достатъчно лесно да се разбере в реалния свят, но в клетка B2 нашата формула става все по-сложна. Ако се вгледате внимателно във формулата, ще видите, че третият параметър на оригиналната IF функция ( стойност, ако е невярна) сега е цялостна функция на ИФ сама по себе си. Това се нарича a вложена функция (функция във функция). Той е напълно валиден в Excel (дори работи!), Но е по-трудно да се чете и разбират.
Няма да влезем в орехите и начините за това как и защо това работи, нито ще разгледаме нюансите на вложените функции. Това е урок за VLOOKUP, а не за Excel като цяло.
Както и да е, става още по-лошо! Какво ще кажете, когато решим, че ако спечелят повече от $ 50,000, тогава те имат право на 50% комисиона, а ако печелят повече от $ 60,000, тогава имат право на 60% комисиона.?
Сега формулата в клетка B2, макар и правилна, е станала практически нечетлива. Никой не трябва да пише формули, където функциите са вложени четири нива! Със сигурност трябва да има по-прост начин?
Със сигурност има. VLOOKUP на помощ!
Нека малко да променим работния лист. Ще запазим всички същите цифри, но ще ги организираме по нов начин, още повече плосък по следния начин:
Отделете малко време и проверете за себе си, че новият Таблица на тарифите работи точно по същия начин като поредицата от по-горе.
Концептуално, това, което ще направим, е да използваме VLOOKUP, за да разгледаме общите продажби на продавача (от B1) в таблицата за тарифите и да ни върнем съответната ставка на комисионната. Имайте предвид, че продавачът наистина може да е създал продажби не една от петте стойности в таблицата за тарифите ($ 0, $ 30,000, $ 40,000, $ 50,000 или $ 60,000). Те може да са създали продажби от $ 34,988. Важно е да се отбележи, че $ 34,988 го правят не се появи в таблицата с тарифи. Да видим дали VLOOKUP все пак може да реши нашия проблем…
Ние избираме клетка B2 (мястото, което искаме да поставим нашата формула) и след това вмъкваме функцията VLOOKUP от Формули раздел:
Най- Функция Аргументи се появява полето за VLOOKUP. Попълваме аргументите (параметрите) един по един, започвайки с Lookup_value, което в този случай е общата сума на продажбите от клетка B1. Поставяме курсора в полето Lookup_value и след това щракнете веднъж върху клетка B1:
След това трябва да зададем на VLOOKUP каква таблица да потърси тези данни. В този пример, разбира се, това е таблицата за скоростта. Поставяме курсора в полето Table_array и маркирайте цялата таблица на скоростта - с изключение на заглавията:
След това трябва да посочим коя колона в таблицата съдържа информацията, която искаме формулата ни да ни върне. В този случай искаме комисионата, която се намира във втората колона на таблицата, затова въвеждаме a 2 в Col_index_num поле:
Накрая въвеждаме стойност в Range_lookup поле.
Важно: Това е използването на това поле, което отличава двата начина на използване на VLOOKUP. За да използвате VLOOKUP с база данни, този краен параметър, Range_lookup, трябва винаги да е настроено на FALSE, но с това използване на VLOOKUP трябва или да го оставим празно, или да въведем стойност от ВЯРНО. Когато използвате VLOOKUP, е изключително важно да направите правилния избор за този краен параметър.
За да бъдем ясни, ще въведем стойност от вярно в Range_lookup област. Също така би било добре да го оставите празно, тъй като това е стойността по подразбиране:
Изпълнихме всички параметри. Сега кликнете върху Добре и Excel изгражда формулата на VLOOKUP за нас:
Ако експериментираме с няколко различни общи продажби, можем да се убедим, че формулата работи.
заключение
В "базата данни" версия на VLOOKUP, където Range_lookup параметър FALSE, стойността, подадена в първия параметър (Lookup_value) трябва да присъства в базата данни. С други думи, търсим точно съвпадение.
Но при тази друга употреба на VLOOKUP не е задължително да търсим точно съвпадение. В този случай „достатъчно близо е достатъчно”. Но какво имаме предвид под „достатъчно близо“? Нека използваме един пример: Когато търсим комисионна върху общата сума от продажби от $ 34,988, нашата формула VLOOKUP ще ни върне стойност от 30%, което е правилният отговор. Защо избра ред в таблицата, съдържащ 30%? Какво всъщност означава „достатъчно близо“ в този случай? Нека бъдем точни:
Кога Range_lookup е настроено на ВЯРНО (или пропуснати), VLOOKUP ще изглежда в колона 1 и съвпада най-високата стойност, която не е по-голяма от на Lookup_value параметър.
Също така е важно да се отбележи, че тази система работи, таблицата трябва да бъде сортирана във възходящ ред в колона 1!
Ако искате да практикувате с VLOOKUP, примерният файл, илюстриран в тази статия, може да бъде изтеглен от тук.
