1Aug
У недавно објављеном чланку, представили смо функцију Екцел под називом ВЛООКУП и објаснили како се може користити за преузимање података из базе података у ћелију на локалном радном листу.У том чланку поменули смо да је било две употребе за ВЛООКУП, а само једна од њих се бавила питањима базе података.У овом чланку, другом и финалу у серији ВЛООКУП, испитујемо ову другу, мање познату употребу за ВЛООКУП функцију.
Ако то још нисте урадили, прочитајте први чланак ВЛООКУП-а - овај чланак ће претпоставити да су многи појмови објашњени у том чланку већ познати читаоцу.
Када радите са базама података, ВЛООКУП-у се усваја "јединствени идентификатор" који служи за идентификацију података који желимо наћи у бази података( нпр. Код производа или ИД-а клијента).Овај јединствени идентификатор мора постојати у бази података, иначе ВЛООКУП нам враћа грешку.У овом чланку размотрићемо начин коришћења ВЛООКУП-а где идентификатор уопште не мора да постоји у бази података.Скоро као да ВЛООКУП може усвојити приступ "довољно је довољно добар" за враћање података које тражимо.У одређеним околностима, ово је
тачно оно што нам је потребно.Овај чланак ћемо илустровати примјером стварног свијета - израчунавање провизија које се генеришу на скупу података о продаји.Почећемо са врло једноставним сценаријем, а затим прогресивно учинити га сложенијим, све док се једино рационално решење проблема не користи ВЛООКУП.Почетни сценарио у нашој фиктивној компанији ради овако: Ако продавац створи више од 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 !
Ако желите да вежбате са ВЛООКУП-ом, фајл узорка илустрованог у овом чланку може се преузети одавде.