Съдържание
Наскоро посветихме статия на една от най-полезните функции на Excel, наречена ВПР и показа как може да се използва за извличане на необходимата информация от база данни в клетка на работен лист. Споменахме също, че има два случая на използване на функцията ВПР и само един от тях се занимава със заявки към база данни. В тази статия ще научите друг по-малко известен начин за използване на функцията ВПР в Excel.
Ако все още не сте го направили, не забравяйте да прочетете последната статия за функцията ВПР, тъй като цялата информация по-долу предполага, че вече сте запознати с принципите, описани в първата статия.
При работа с бази данни, функции ВПР предава се уникален идентификатор, който се използва за идентифициране на информацията, която искаме да намерим (например код на продукт или идентификационен номер на клиент). Този уникален код трябва да присъства в базата данни, в противен случай ВПР ще съобщи за грешка. В тази статия ще разгледаме този начин на използване на функцията ВПРкогато идентификаторът изобщо не съществува в базата данни. Сякаш функцията ВПР превключва на приблизителен режим и избира какви данни да ни предостави, когато искаме да намерим нещо. При определени обстоятелства това е точно това, което е необходимо.
Пример от живота. Ние поставихме задачата
Нека илюстрираме тази статия с пример от реалния живот – изчисляване на комисионни въз основа на широк набор от показатели за продажби. Ще започнем с много проста опция, а след това постепенно ще я усложняваме, докато единственото рационално решение на проблема стане използването на функцията ВПР. Първоначалният сценарий за нашата фиктивна задача е следният: ако търговец направи повече от $30000 30 продажби за една година, тогава неговата комисионна е 20%. В противен случай комисионната е само XNUMX%. Нека го представим под формата на таблица:
Продавачът въвежда своите данни за продажби в клетка B1, а формулата в клетка B2 определя правилния процент на комисионната, който продавачът може да очаква. На свой ред получената ставка се използва в клетка B3 за изчисляване на общата комисионна, която продавачът трябва да получи (просто умножаване на клетки B1 и B2).
Най-интересната част от таблицата се съдържа в клетка B2 – това е формулата за определяне на комисионната. Тази формула съдържа функция на Excel, наречена IF (АКО). За тези читатели, които не са запознати с тази функция, ще обясня как работи:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
състояние е аргумент на функция, който приема стойността на едно от двете ИСТИНСКИ КОД (ВЯРНО), или FALSE (НЕВЯРНО). В горния пример изразът B1
Вярно ли е, че B1 е по-малко от B5?
Или можете да го кажете по различен начин:
Вярно ли е, че общата сума на продажбите за годината е по-малка от праговата стойност?
Ако отговорим на този въпрос ДА (TRUE), тогава функцията се връща стойност, ако е вярно (стойност, ако е TRUE). В нашия случай това ще бъде стойността на клетка B6, т.е. комисионна, когато общите продажби са под прага. Ако отговорим на въпроса НЕ (FALSE) след това се връща стойност, ако е невярно (стойност, ако е FALSE). В нашия случай това е стойността на клетка B7, т.е. комисионна, когато общите продажби са над прага.
Както можете да видите, ако вземем общите продажби от $20000 2, получаваме 20% комисионна в клетка B40000. Ако въведем стойност от $30 XNUMX, тогава процентът на комисионната ще се промени с XNUMX%:
Ето как работи нашата таблица.
Ние усложняваме задачата
Нека направим нещата малко по-трудни. Нека зададем друг праг: ако продавачът спечели повече от $40000 40, тогава процентът на комисионната се увеличава до XNUMX%:
Всичко изглежда просто и ясно, но нашата формула в клетка B2 става забележимо по-сложна. Ако се вгледате внимателно във формулата, ще видите, че третият аргумент на функцията IF (IF) се превърна в друга пълноценна функция IF (АКО). Тази конструкция се нарича влагане на функции една в друга. Excel с радост позволява тези конструкции и те дори работят, но са много по-трудни за четене и разбиране.
Няма да навлизаме в технически подробности – защо и как работи и няма да навлизаме в нюансите на писане на вложени функции. Все пак това е статия, посветена на функцията ВПР, не пълно ръководство за Excel.
Какъвто и да е случаят, формулата става по-сложна! Какво ще стане, ако въведем друга опция за комисионна от 50% за тези продавачи, които правят над $50000 60000 продажби. И ако някой е продал повече от $60 XNUMX, ще плати ли XNUMX% комисионна?
Сега формулата в клетка B2, дори и да е написана без грешки, е станала напълно нечетлива. Мисля, че малко са тези, които искат да използват формули с 4 нива на влагане в своите проекти. Трябва да има по-лесен начин?!
И има такъв начин! Функцията ще ни помогне ВПР.
Прилагаме функцията VLOOKUP, за да разрешим проблема
Нека променим малко дизайна на нашата маса. Ще запазим всички същите полета и данни, но ще ги подредим по нов, по-компактен начин:
Отделете малко време и се уверете, че новата маса Таблица с тарифи включва същите данни като предишната таблица с прагове.
Основната идея е да използвате функцията ВПР за определяне на желаната тарифна ставка съгласно таблицата Таблица с тарифи в зависимост от обема на продажбите. Моля, обърнете внимание, че продавачът може да продава стоки за сума, която не е равна на един от петте прага в таблицата. Например, той може да продаде за $34988, но няма такава сума. Нека да видим как функционира функцията ВПР може да се справи с такава ситуация.
Вмъкване на функция VLOOKUP
Изберете клетка B2 (където искаме да вмъкнем нашата формула) и намерете ВОЛОКУП (VLOOKUP) в библиотеката с функции на Excel: формули (формули) > Функция библиотека (Библиотека с функции) > Търсене и справка (Препратки и масиви).
Появява се диалогов прозорец Функционални аргументи (Аргументи на функцията). Попълваме стойностите на аргументите един по един, започвайки с Търсене_стойност (Търсена_стойност). В този пример това е общата сума на продажбите от клетка B1. Поставете курсора в полето Търсене_стойност (Търсена_стойност) и изберете клетка B1.
След това трябва да посочите функциите ВПРкъде да търся данни. В нашия пример това е таблица Таблица с тарифи. Поставете курсора в полето Таблица_масив (Таблица) и изберете цялата таблица Таблица с тарифис изключение на заглавките.
След това трябва да посочим от коя колона да извлечем данни, използвайки нашата формула. Интересува ни процентът на комисионната, който е във втората колона на таблицата. Следователно, за аргумента Col_index_num (Колона_номер) въведете стойност 2.
И накрая, въвеждаме последния аргумент – Търсене_обхват (Интервал_търсене).
Важно: именно използването на този аргумент прави разликата между двата начина за прилагане на функцията ВПР. При работа с бази данни аргументът Търсене_обхват (range_lookup) винаги трябва да има стойност FALSE (FALSE), за да търсите точно съвпадение. При нашето използване на функцията ВПР, трябва да оставим това поле празно или да въведем стойност ИСТИНСКИ КОД (ВЯРНО). Изключително важно е да изберете правилно тази опция.
За да стане по-ясно, ще ви представим ИСТИНСКИ КОД (ВЯРНО) в полето Търсене_обхват (Интервал_търсене). Въпреки че, ако оставите полето празно, това няма да е грешка, тъй като ИСТИНСКИ КОД е стойността му по подразбиране:
Попълнили сме всички параметри. Сега натискаме OK, а Excel създава формула за нас с функция ВПР.
Ако експериментираме с няколко различни стойности за общата сума на продажбите, тогава ще се уверим, че формулата работи правилно.
Заключение
Когато функцията ВПР работи с бази данни, аргумент Търсене_обхват (range_lookup) трябва да приеме FALSE (НЕВЯРНО). И стойността, въведена като Търсене_стойност (Lookup_value) трябва да съществува в базата данни. С други думи, търси точно съвпадение.
В примера, който разгледахме в тази статия, няма нужда да получавате точно съвпадение. Такъв е случаят, когато функцията ВПР трябва да превключи на приблизителен режим, за да върне желания резултат.
Например: Искаме да определим каква ставка да използваме при изчисляването на комисионната за продавач с обем на продажбите от $34988. функция ВПР ни връща стойност от 30%, което е абсолютно правилно. Но защо формулата избра реда, съдържащ точно 30%, а не 20% или 40%? Какво се разбира под приблизително търсене? Нека сме наясно.
Когато аргументът Търсене_обхват (interval_lookup) има стойност ИСТИНСКИ КОД (TRUE) или пропусната функция ВПР преминава през първата колона и избира най-голямата стойност, която не надвишава търсената стойност.
Важна точка: За да работи тази схема, първата колона на таблицата трябва да бъде сортирана във възходящ ред.