В скорошна статия ние представихме функцията Excel, наречена VLOOKUP и обясни как може да се използва за извличане на информация от база данни в клетка в локален работен лист. В тази статия споменахме, че има две приложения за VLOOKUP и само един от тях се занимава с разглеждане на бази данни. В тази статия, втората и последната в серията VLOOKUP, разглеждаме тази друга, по-малко известна употреба за функцията VLOOKUP.
Ако още не сте го направили, моля, прочетете първата статия VLOOKUP - тази статия ще приеме, че много от понятията, обяснени в тази статия, вече са известни на читателя.
Когато работим с бази данни, VLOOKUP получава "уникален идентификатор", който служи за идентифициране на кой запис на данни искаме да намерим в базата данни (например код на продукт или идентификационен номер на клиент). Този уникален идентификатор трябва да съществуват в базата данни, в противен случай VLOOKUP ни връща грешка. В тази статия ще разгледаме начин за използване на VLOOKUP, където идентификаторът изобщо не трябва да съществува в базата данни. Това е почти сякаш VLOOKUP може да възприеме подход "достатъчно близък до достатъчно добър" за връщане на данните, които търсим. При определени обстоятелства това е така точно това, от което се нуждаем.
Ще илюстрираме тази статия с пример от реалния свят - този на изчисляването на комисионните, които се генерират на набор от данни за продажбите. Ще започнем с един много прост сценарий и след това ще го направим по-сложно, докато единственото рационално решение на проблема е използването на VLOOKUP. Първоначалният сценарий в нашата фиктивна компания работи по следния начин: Ако продавачът създава повече от 30 000 долара продажби за дадена година, комисионната, която получават при тези продажби, е 30%. В противен случай тяхната комисионна е едва 20%. Досега това е доста прост работен лист:
За да използва този работен лист, продавачът въвежда данните за продажбите си в клетка B1 и формулата в клетка В2 изчислява коефициента на комисиона, който има право да получи, който се използва в клетка B3 за изчисляване на общата сума, която продавачът дължи е просто умножение на B1 и B2).
Клетката В2 съдържа единствената интересна част от този работен лист - формулата за определяне коя комисионна да използвате: тази По-долу прагът от 30 000 долара или този по-горе прагът. Тази формула използва функцията Excel, наречена АКО, За тези читатели, които не са запознати с ФИ, работи по следния начин:
IF (условие, стойност, ако е истина, стойност, ако е фалшива)
Където състояние е израз, който се оценява и на двете вярно или фалшив, В примера по - горе, състояние е изразът B1<>, който може да се чете като "B1 е по-малко от B5?", или, по друг начин, "Общите продажби са по-малки от прага". Ако отговорът на този въпрос е "да" (вярно), тогава използваме ако е вярно параметър на функцията, а именно B6 в този случай - коефициентът на комисиона, ако общият обем на продажбите е бил По-долу прагът. Ако отговорът на въпроса е "не" (невярно), тогава използваме стойност, ако е фалшива параметър на функцията, а именно B7 в този случай - коефициентът на комисиона, ако общият обем на продажбите е бил по-горе прагът.
Както можете да видите, като използвате общо продажби от $ 20,000, ни давате комисионна от 20% в клетка B2. Ако въведем стойност от $ 40,000, получаваме различна комисионна:
Така че нашата електронна таблица работи.
Нека да го направим по-сложен. Нека да въведем втори праг: Ако продавачът печели повече от 40 000 долара, комисионната му се увеличава до 40%:
Достатъчно лесен за разбиране в реалния свят, но в клетка B2 нашата формула става все по-сложна. Ако погледнете отблизо формулата, ще видите, че третият параметър на оригиналната IF функция ( стойност, ако е фалшива) сега е цяла 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, примерният файл, илюстриран в тази статия, може да бъде изтеглен от тук.
