Садржај
Недавно смо посветили чланак једној од најкориснијих Екцел функција под називом ВПР и показао како се може користити за издвајање потребних информација из базе података у ћелију радног листа. Такође смо споменули да постоје два случаја употребе функције ВПР а само један од њих се бави упитима базе података. У овом чланку ћете научити још један мање познат начин коришћења функције ВПР у програму Екцел.
Ако то још нисте урадили, обавезно прочитајте последњи чланак о функцији ВПР, јер све информације у наставку претпостављају да сте већ упознати са принципима описаним у првом чланку.
При раду са базама података, функције ВПР прослеђује се јединствени идентификатор који се користи за идентификацију информација које желимо да пронађемо (на пример, код производа или идентификациони број купца). У супротном, овај јединствени код мора бити присутан у бази података ВПР пријавиће грешку. У овом чланку ћемо погледати овај начин коришћења функције ВПРкада ИД уопште не постоји у бази података. Као да је функција ВПР прешао на приближни режим, и бира које податке ће нам дати када желимо нешто да пронађемо. У одређеним околностима, то је управо оно што је потребно.
Пример из живота. Поставили смо задатак
Илуструјмо овај чланак примером из стварног живота – израчунавањем провизија на основу широког спектра метрика продаје. Почећемо са врло једноставном опцијом, а затим ћемо је постепено компликовати све док једино рационално решење проблема не буде коришћење функције ВПР. Почетни сценарио за наш фиктивни задатак је следећи: ако продавац заради више од 30000 долара у продаји за годину дана, онда је његова провизија 30%. Иначе, провизија је само 20%. Ставимо то у облику табеле:
Продавац уноси своје податке о продаји у ћелију Б1, а формула у ћелији Б2 одређује тачну стопу провизије коју продавац може да очекује. Заузврат, резултујућа стопа се користи у ћелији Б3 за израчунавање укупне провизије коју би продавац требало да добије (једноставно множење ћелија Б1 и Б2).
Најинтересантнији део табеле налази се у ћелији Б2 – ово је формула за одређивање стопе провизије. Ова формула садржи Екцел функцију под називом IF (АКО). За оне читаоце који нису упознати са овом функцијом, објаснићу како она функционише:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Услов је аргумент функције који узима вредност било које ТРУЕ ЦОДЕ (ТАЧНО), или ЛАЖ (ФАЛСЕ). У горњем примеру, израз Б1
Да ли је тачно да је Б1 мање од Б5?
Или можете рећи другачије:
Да ли је тачно да је укупан износ продаје за годину мањи од граничне вредности?
Ако одговоримо на ово питање ДА (ТРУЕ), онда се функција враћа вредност ако је тачно (вредност ако је ТРУЕ). У нашем случају, то ће бити вредност ћелије Б6, односно стопа провизије када је укупна продаја испод прага. Ако одговоримо на питање НЕ (ФАЛСЕ) затим се враћа вредност ако је нетачна (вредност ако је ФАЛСЕ). У нашем случају, ово је вредност ћелије Б7, односно стопа провизије када је укупна продаја изнад прага.
Као што видите, ако узмемо укупну продају од 20000 долара, добићемо стопу провизије од 2% у ћелији Б20. Ако унесемо вредност од $40000, онда ће се стопа провизије променити за 30%:
Овако функционише наш сто.
Компликујемо задатак
Хајде да мало отежамо ствари. Хајде да поставимо још један праг: ако продавац заради више од 40000 долара, онда се стопа провизије повећава на 40%:
Чини се да је све једноставно и јасно, али наша формула у ћелији Б2 постаје приметно компликованија. Ако пажљиво погледате формулу, видећете да је трећи аргумент функције IF (ИФ) претворио у још једну пуноправну функцију IF (АКО). Ова конструкција се назива угнежђивање функција једне у другу. Екцел са задовољством дозвољава ове конструкције, чак и раде, али их је много теже читати и разумети.
Нећемо улазити у техничке детаље – зашто и како функционише, и нећемо улазити у нијансе писања угнежђених функција. На крају крајева, ово је чланак посвећен функцији ВПР, није потпуни водич за Екцел.
У сваком случају, формула постаје компликованија! Шта ако уведемо другу опцију за стопу провизије од 50% за оне продавце који зарађују више од 50000 долара у продаји. А ако је неко продао више од 60000 долара, да ли ће платити 60% провизије?
Сада је формула у ћелији Б2, чак и ако је написана без грешака, постала потпуно нечитљива. Мислим да је мало оних који желе да користе формуле са 4 нивоа угнежђења у својим пројектима. Мора да постоји лакши начин?!
И постоји такав начин! Функција ће нам помоћи ВПР.
Примењујемо функцију ВЛООКУП да решимо проблем
Хајде да мало променимо дизајн нашег стола. Задржаћемо сва иста поља и податке, али их уредити на нови, компактнији начин:
Одвојите тренутак и проверите нови сто Табела стопа укључује исте податке као претходна табела прагова.
Главна идеја је да користите функцију ВПР да се одреди жељени тарифни став према табели Табела стопа зависно од обима продаје. Имајте на уму да продавац може продати робу за износ који није једнак једном од пет прагова у табели. На пример, могао би да прода за 34988 долара, али не постоји такав износ. Да видимо како функционише ВПР може да се носи са таквом ситуацијом.
Уметање функције ВЛООКУП
Изаберите ћелију Б2 (где желимо да убацимо нашу формулу) и пронађите ВЛООКУП (ВЛООКУП) у библиотеци Екцел функција: формула (формуле) > Библиотека функција (Библиотека функција) > Преглед и референце (Референце и низови).
Појављује се оквир за дијалог Аргументи функције (Аргументи функције). Уписујемо вредности аргумената један по један, почевши од Лоокуп_валуе (Лоокуп_валуе). У овом примеру, ово је укупан износ продаје из ћелије Б1. Ставите курсор у поље Лоокуп_валуе (Лоокуп_валуе) и изаберите ћелију Б1.
Затим морате одредити функције ВПРгде тражити податке. У нашем примеру, ово је табела Табела стопа. Ставите курсор у поље Табле_арраи (Табела) и изаберите целу табелу Табела стопаосим заглавља.
Затим морамо да одредимо коју колону да извучемо податке из наше формуле. Интересује нас стопа провизије која се налази у другој колони табеле. Стога, за аргумент Цол_индек_нум (Број_колоне) унесите вредност 2.
И на крају, уводимо последњи аргумент – Ранге_лоокуп (Интервал_лоокуп).
Важно: управо употреба овог аргумента чини разлику између два начина примене функције ВПР. Када радите са базама података, аргумент Ранге_лоокуп (ранге_лоокуп) увек мора имати вредност ЛАЖ (ФАЛСЕ) за тражење тачног подударања. У нашем коришћењу функције ВПР, морамо оставити ово поље празним или унети вредност ТРУЕ ЦОДЕ (ИСТИНИТО). Изузетно је важно правилно одабрати ову опцију.
Да би било јасније, представићемо ТРУЕ ЦОДЕ (ТАЧНО) на терену Ранге_лоокуп (Интервал_лоокуп). Иако, ако оставите поље празно, ово неће бити грешка, јер ТРУЕ ЦОДЕ је његова подразумевана вредност:
Попунили смо све параметре. Сада притискамо OK, а Екцел креира формулу за нас помоћу функције ВПР.
Ако експериментишемо са неколико различитих вредности за укупан износ продаје, онда ћемо се уверити да формула функционише исправно.
Zakljucak
Када функција ВПР ради са базама података, аргумент Ранге_лоокуп (ранге_лоокуп) мора прихватити ЛАЖ (ФАЛСЕ). И вредност унета као Лоокуп_валуе (Лоокуп_валуе) мора постојати у бази података. Другим речима, тражи се тачно подударање.
У примеру који смо погледали у овом чланку, нема потребе да добијете тачно подударање. Ово је случај када функција ВПР мора да се пребаци на приближни режим да би се добио жељени резултат.
На пример: Желимо да одредимо коју стопу да користимо у обрачуну провизије за продавца са обимом продаје од 34988 УСД. Функција ВПР враћа нам вредност од 30%, што је апсолутно тачно. Али зашто је формула одабрала ред који садржи тачно 30%, а не 20% или 40%? Шта се подразумева под приближном претрагом? Да будемо јасни.
Када аргумент Ранге_лоокуп (интервал_лоокуп) има вредност ТРУЕ ЦОДЕ (ТРУЕ) или изостављена, функција ВПР пролази кроз прву колону и бира највећу вредност која не прелази вредност тражења.
Важна тачка: Да би ова шема функционисала, прва колона табеле мора бити сортирана узлазним редоследом.