ВЛООКУП у Екцелу, део 2 Коришћење ВЛООКУП без базе података

У недавном чланку, увели смо функцију Екцел која се зове ВЛООКУП и објаснио како се може користити за дохват информација из базе података у ћелију локалног радног листа. У том чланку смо поменули да постоје два начина коришћења ВЛООКУП-а, а само један од њих се бавио питањем база података. У овом чланку, другом и коначном у низу ВЛООКУП, испитујемо ову другу, мање познату употребу за функцију ВЛООКУП.

Ако то већ нисте учинили, прочитајте први чланак за ВЛООКУП - овај чланак ће претпоставити да су многи концепти објашњени у том чланку већ познати читаоцу.

Када радите са базама података, ВЛООКУП се прослеђује "јединственом идентификатору" који служи за идентификацију података који желимо да пронађемо у бази података (нпр. Код производа или ИД клијента). Овај јединствени идентификатор муст постоје у бази података, иначе ВЛООКУП нам враћа грешку. У овом чланку ћемо испитати начин коришћења ВЛООКУП-а где идентификатор уопште не мора да постоји у бази података. Готово као да ВЛООКУП може прихватити приступ “довољно близу је довољно” за враћање података које тражимо. У одређеним околностима, то је баш тако оно што нам треба.

Овај чланак ћемо илустровати примјером из стварног свијета - израчуном провизија које се генеришу на скупу продајних бројки. Почећемо од врло једноставног сценарија, а затим га постепено учинити сложенијим, док једино рационално рјешење проблема није кориштење ВЛООКУП-а. Иницијални сценарио у нашој фиктивној компанији ради овако: Ако продавач креира више од 30.000 $ продаје у датој години, провизија коју остварују на тој продаји је 30%. У супротном, њихова провизија је само 20%. До сада је ово прилично једноставан радни лист:

Да би користио овај радни лист, продавац уноси своје продајне цифре у ћелију Б1, а формула у ћелији Б2 израчунава тачну стопу провизије коју имају право да прими, која се користи у ћелији Б3 за израчунавање укупне провизије коју продавац дугује ( је једноставно множење Б1 и Б2).

Ћелија Б2 садржи једини занимљиви део овог радног листа - формулу за одлучивање о стопи провизије коју ћемо користити: ону испод праг од 30.000 долара, или један горе Праг. Ова формула користи Екцелову функцију која се зове АКО. За оне читаоце који нису упознати са ИФ, он ради овако:

АКО(цондитион, валуе иф труе, валуе иф фалсе)

Где стање је израз који вреднује или истина или фалсе. У горњем примеру, стање је израз Б1, који се може читати као “Је ли Б1 мањи од Б5?”, или, другачије речено, “Да ли је укупна продаја мања од прага”. Ако је одговор на ово питање "да" (тачно), онда користимо валуе иф труе параметар функције, наиме Б6 у овом случају - стопа провизије ако је укупна продаја била испод Праг. Ако је одговор на питање "не" (лаж), онда користимо валуе иф фалсе параметар функције, наиме Б7 у овом случају - стопа провизије ако је укупна продаја била горе Праг.

Као што можете видети, коришћење укупне продаје од 20.000 долара даје нам провизију од 20% у ћелији Б2. Ако унесемо вредност од $ 40,000, добићемо другачију стопу провизије:

Дакле, наша табела ради.

Хајде да буде сложеније. Уведимо други праг: Ако продавач зарађује више од 40.000 долара, онда се њихова провизија повећава на 40%:

Лако је разумети у стварном свијету, али у ћелији Б2 наша формула постаје све сложенија. Ако пажљиво погледате формулу, видећете да је трећи параметар оригиналне ИФ функције ( валуе иф фалсе) сада је цела ИФ функција сама по себи. То се зове а угнежђена функција (функција унутар функције). Савршено вриједи у Екцелу (чак и ради!), Али је теже читати и разумјети.

Нећемо улазити у матице и разлоге како и зашто то ради, нити ћемо испитивати нијансе угнијежђених функција. Ово је туторијал о ВЛООКУП-у, а не у Екцелу уопште.

У сваком случају, погоршава се! А када одлучимо да ако зараде више од $ 50,000 онда имају право на 50% провизије, а ако зараде више од 60.000 $ онда имају право на провизију од 60%?

Сада је формула у ћелији Б2, иако исправна, постала практично нечитљива. Нико не треба да пише формуле где су функције уграђене четири нивоа дубоко! Сигурно мора постојати једноставнији начин?

Сигурно постоји. ВЛООКУП на спас!

Хајде да мало редизајнирамо радни лист. Задржаћемо све исте бројке, али организовати их на нови начин, више табулар начин:

Одвојите тренутак и провјерите да ли је ново Рате Табле ради потпуно исто као и низ прагова изнад.

Концептуално, оно што ћемо да урадимо је да користимо ВЛООКУП да прегледамо укупан продајни резултат продавца (из Б1) у табели стопа и да нам вратимо одговарајућу стопу провизије. Имајте на уму да је продавац заиста створио продају не једна од пет вриједности у табели стопа ($ 0, $ 30,000, $ 40,000, $ 50,000 или $ 60,000). Можда су остварили продају од $ 34,988. Важно је напоменути да $ 34,988 ради не се појављују у табели стопа. Да видимо да ли ВЛООКУП ионако може да реши наш проблем ...

Изаберемо ћелију Б2 (локацију коју желимо да ставимо у нашу формулу), а затим убацимо функцију ВЛООКУП из Формуле таб:

Тхе Аргументи функције појављује се оквир за ВЛООКУП. Аргументе (параметре) попуњавамо један по један, почевши од Лоокуп_валуе, што је у овом случају укупна продаја из ћелије Б1. Стављамо курсор у Лоокуп_валуе и затим једном кликните на ћелију Б1:

Затим треба да наведемо у ВЛООКУП коју табелу да тражимо ове податке. У овом примеру, то је табела стопа, наравно. Стављамо курсор у Табле_арраи поље, а затим означите целу табелу стопа - искључујући наслове:

Затим морамо одредити која колона у табели садржи информације које желимо да нам се формула врати. У овом случају желимо провизију, која се налази у другој колони табеле, тако да стога уносимо а 2 Инто тхе Цол_индек_нум поље:

На крају уносимо вредност у Ранге_лоокуп поље.

Важно: Коришћење овог поља разликује два начина коришћења ВЛООКУП-а. Да бисте користили ВЛООКУП са базом података, овај коначни параметар, Ранге_лоокуп, увек мора да буде подешено на ФАЛСЕ, али са овом другом употребом ВЛООКУП-а, морамо или оставити празно или унети вредност ИСТИНА. Када користите ВЛООКУП, од виталног је значаја да направите исправан избор за овај коначни параметар.

Да бисмо били експлицитни, уносимо вредност од истина у Ранге_лоокуп поље. Такође би било добро да оставите празно, јер је то подразумевана вредност:

Испунили смо све параметре. Сада кликнемо на У реду и Екцел гради нашу ВЛООКУП формулу за нас:

Ако експериментишемо са неколико различитих укупних продајних количина, можемо да се уверимо да формула функционише.

Закључак

У верзији "базе података" ВЛООКУП, где је Ранге_лоокуп параметар је ФАЛСЕ, вредност прослеђена у првом параметру (Лоокуп_валуе) муст бити присутни у бази података. Другим речима, тражимо тачно подударање.

Али у овом другом коришћењу ВЛООКУП-а, не морамо увек тражити тачно подударање. У овом случају, “довољно близу је довољно”. Али шта мислимо под “довољно близу”? Хајде да употребимо пример: Када тражимо стопу провизије на укупној продаји од $ 34,988, наша формула ВЛООКУП ће нам вратити вредност од 30%, што је тачан одговор. Зашто је изабрао ред у табели који садржи 30%? Шта, у ствари, значи “довољно близу” у овом случају? Будимо прецизни:

Када Ранге_лоокуп је подешен на ИСТИНА (или изостављено), ВЛООКУП ће гледати у колони 1 и одговарати највиша вредност која није већа од тхе Лоокуп_валуе параметар.

Такође је важно напоменути да овај систем функционише, табела мора бити сортирана узлазно по ступцу 1!

Ако желите да вежбате са ВЛООКУП-ом, узорак датотека приказан у овом чланку можете преузети овде.