Функција ВЛООКУП - Увод
ВЛООКУП функција је референтна тачка.
Знате нешто у Екцелу ако знате да користите функцију ВЛООКУП.
Ако то не учините, боље да у животопису не наведете Екцел као једно од својих јачих подручја.
Био сам део панел -интервјуа где је чим је кандидат поменуо Екцел као своју област стручности прво што се тражило - разумели сте - функција ВЛООКУП.
Сада када знамо колико је ова Екцел функција важна, има смисла потпуно је размотрити како бисмо могли поносно рећи - „Знам нешто у Екцел -у“.
Ово ће бити велики ВЛООКУП водич (по мојим стандардима).
Покрићу све што треба да знам о томе, а затим ћу вам показати корисне и практичне примере ВЛООКУП -а.
Зато се закопчајте.
Време је за полетање.
Када користити функцију ВЛООКУП у програму Екцел?
Функција ВЛООКУП је најпогоднија за ситуације када тражите одговарајућу тачку података у колони, а када се пронађе одговарајућа тачка података, идите десно у том реду и дохватите вредност из ћелије која је одређени број колоне десно.
Узмимо овде једноставан пример да бисмо разумели када треба користити Влоокуп у Екцелу.
Сетите се када је листа резултата испита била залепљена на огласну таблу и сви су полудели проналазећи своја имена и оцене (барем се то дешавало док сам био у школи).
Ево како је то функционисало:
- Отиђете до огласне табле и почнете да тражите своје име или број за упис (прелазите прстом од врха до дна на листи).
- Чим уочите своје име, померите очи десно од имена/уписног броја да бисте видели своје резултате.
А то је управо оно што функција Екцел ВЛООКУП ради за вас (слободно користите овај пример у свом следећем интервјуу).
Функција ВЛООКУП тражи одређену вредност у колони (у горњем примеру је то било ваше име), а када пронађе наведено подударање, враћа вредност у истом реду (оцене које сте добили).
Синтакса
= ВЛООКУП (лоокуп_валуе, табле_арраи, цол_индек_нум, [ранге_лоокуп])
Улазни аргументи
- лоокуп_валуе - ово је вредност тражења коју покушавате да пронађете у крајњој левој колони табеле. То може бити вредност, референца ћелије или текстуални низ. У примеру записника ово би било ваше име.
- табле_арраи - ово је низ табела у коме тражите вредност. Ово може бити референца на опсег ћелија или именовани опсег. У примеру оцењивања, ово би била цела табела која садржи оцене за све за сваки предмет
- цол_индек - ово је број индекса колоне из којег желите да преузмете одговарајућу вредност. У примеру табеле резултата, ако желите резултате за математику (која је прва колона у табели која садржи оцене), погледали бисте у колону 1. Ако желите оцене за физику, гледали бисте у колону 2.
- [ранге_лоокуп] - овде одређујете да ли желите потпуно или приближно подударање. Ако је изостављено, подразумевано је ТРУЕ - приближно подударање (види додатне напомене испод).
Додатне напомене (досадно, али важно је знати)
- Подударање може бити тачно (ФАЛСЕ или 0 у опсегу_лоокуп) или приближно (ТРУЕ или 1).
- При приближном тражењу, уверите се да је листа сортирана у растућем редоследу (од врха до дна), у супротном би резултат могао бити нетачан.
- Када је ранге_лоокуп ТРУЕ (приближно тражење) и подаци су сортирани узлазно:
- Ако функција ВЛООКУП не може пронаћи вриједност, враћа највећу вриједност, која је мања од лоокуп_валуе.
- Враћа грешку #Н/А ако је лоокуп_валуе мања од најмање вредности.
- Ако је лоокуп_валуе текст, могу се користити заменски знакови (погледајте доњи пример).
Надајући се да имате основно разумевање шта функција ВЛООКУП може да уради, хајде да огулимо овај лук и погледамо неке практичне примере функције ВЛООКУП.
10 примера Екцел ВЛООКУП -а (основни и напредни)
Ево 10 корисних примера коришћења програма Екцел Влоокуп који ће вам показати како га користити у свакодневном послу.
Пример 1 - Проналажење Брадове математичке оцене
У доњем примеру ВЛООКУП-а имам списак имена ученика у крајњој левој колони и ознаке у различитим предметима у колонама Б до Е.
Идемо сада на посао и користимо функцију ВЛООКУП за оно што најбоље ради. Из наведених података морам знати колико је Брад постигао гол у математици.
Из наведених података морам знати колико је Брад постигао гол у математици.
Ево формуле ВЛООКУП која ће вратити Брадов резултат из математике:
= ВЛООКУП ("Брад", $ А $ 3: $ Е $ 10,2,0)
Горња формула има четири аргумента:
- „Бред: - ово је вредност претраживања.
- $ А $ 3: $ Е $ 10 - ово је опсег ћелија у коме тражимо. Упамтите да Екцел тражи вредност за тражење у крајњој левој колони. У овом примеру, тражио би име Брад у А3: А10 (што је крајња лева колона наведеног низа).
- 2 - Када функција уочи Бредово име, она ће отићи у другу колону низа и вратити вредност у исти ред као и Бредова. Вредност 2 овде указује на то да тражимо резултат из друге колоне наведеног низа.
- 0 - ово говори функцији ВЛООКУП да тражи само тачна подударања.
Ево како ВЛООКУП формула ради у горњем примеру.
Прво, тражи вредност Брад у крајњој левој колони. Иде одозго надоле и налази вредност у ћелији А6.
Чим пронађе вредност, иде десно у другу колону и преузима вредност у њој.
Можете користити исту конструкцију формуле да бисте добили било чије оцене из било ког предмета.
На пример, да бисте пронашли Маријине ознаке у хемији, користите следећу формулу ВЛООКУП:
= ВЛООКУП ("Мариа", $ А $ 3: $ Е $ 10,4,0)
У горњем примеру, вредност претраживања (име ученика) је унета у двоструке наводнике. Такође можете да користите референцу ћелије која садржи вредност тражења.
Предност коришћења референце ћелије је што чини формулу динамичком.
На пример, ако имате ћелију са именом ученика и дохватате резултат за математику, резултат би се аутоматски ажурирао када промените име ученика (као што је приказано испод):
Ако унесете вредност тражења која се не налази у крајњој левој колони, она враћа грешку #Н/А.
Пример 2 - Двосмерно тражење
У горе наведеном примеру 1, тешко смо кодирали вредност колоне. Дакле, формула би увек враћала резултат за математику јер смо користили 2 као број индекса колоне.
Али шта ако желите да вредност ВЛООКУП и број индекса колоне учините динамичким. На пример, као што је приказано испод, можете променити име ученика или назив предмета, а формула ВЛООКУП доноси тачан резултат. Ово је пример двосмерне ВЛООКУП формуле.
Ово је пример двосмерне функције ВЛООКУП.
Да бисте направили ову двосмерну формулу за претраживање, морате да учините и колону динамичком. Дакле, када корисник промени тему, формула аутоматски бира исправну колону (2 у случају математике, 3 у случају физике, итд.).
Да бисте то урадили, морате користити функцију МАТЦХ као аргумент колоне.
Ево формуле ВЛООКУП која ће то учинити:
= ВЛООКУП (Г4, $ А $ 3: $ Е $ 10, МАТЦХ (Х3, $ А $ 2: $ Е $ 2,0), 0)
Горња формула користи МАТЦХ (Х3, $ А $ 2: $ Е $ 2,0) као број колоне. МАТЦХ функција узима назив предмета као вредност претраживања (у Х3) и враћа своју позицију у А2: Е2. Дакле, ако користите Матх, вратио би 2 јер се Матх налази у Б2 (што је друга ћелија у наведеном опсегу низа).
Пример 3 - Коришћење падајућих листа као вредности за тражење
У горњем примеру морамо ручно унети податке. То би могло одузети много времена и склоно грешкама, посебно ако имате огромну листу вредности за тражење.
Добра идеја у таквим случајевима је стварање падајуће листе вредности за тражење (у овом случају то могу бити имена ученика и предмети), а затим једноставно изабрати са листе.
На основу избора, формула би аутоматски ажурирала резултат.
Нешто као што је приказано испод:
Ово чини добру компоненту контролне табле јер можете имати огроман скуп података са стотинама ученика на задњој страни, али крајњи корисник (рецимо наставник) може брзо добити оцене ученика у предмету једноставним избором из падајући мени.
Како ово направити:
Формула ВЛООКУП која се користи у овом случају иста је као и у примеру 2.
= ВЛООКУП (Г4, $ А $ 3: $ Е $ 10, МАТЦХ (Х3, $ А $ 2: $ Е $ 2,0), 0)
Вредности за претраживање су претворене у падајуће листе.
Ево корака за креирање падајуће листе:
- Одаберите ћелију у којој желите падајућу листу. У овом примеру, у Г4, желимо имена ученика.
- Идите на Подаци -> Алати за податке -> Валидација података.
- У дијалогу Валидација података, на картици поставки, изаберите Листа са падајућег менија Дозволи.
- У извору изаберите $ А $ 3: $ А $ 10
- Притисните ОК.
Сада ћете имати падајућу листу у ћелији Г4. Слично, можете створити један у Х3 за субјекте.
Пример 4 - Тросмерно тражење
Шта је тросмерно тражење?
У примеру 2, користили смо једну табелу за претраживање са оценама за ученике из различитих предмета. Ово је пример двосмерног претраживања јер користимо две променљиве за добијање резултата (име ученика и име испитаника).
Претпоставимо да за годину дана студент има три различита нивоа испита, јединични тест, средњи и завршни испит (то сам имао док сам био студент).
Тросмерно тражење било би могућност да се студенту дају оцене за одређени предмет са наведеног нивоа испита.
Нешто као што је приказано испод:
У горњем примеру, функција ВЛООКУП може да тражи у три различите табеле (Унит Тест, Мидтерм и Финал Екам) и враћа резултат за наведеног ученика у наведеном предмету.
Ево формуле која се користи у ћелији Х4:
= ВЛООКУП (Г4, ИЗАБЕРИТЕ (ИФ (Х2 = "Јединични тест", 1, ИФ (Х2 = "Средњи рок, 2,3)), $ А $ 3: $ Е $ 7, $ А $ 11: $ Е $ 15, $ А $ 19: $ Е $ 23), МАТЦХ (Х3, $ А $ 2: $ Е $ 2,0), 0)
Ова формула користи функцију ЦХООСЕ да би се уверила да се позива на праву табелу. Хајде да анализирамо ИЗАБЕРИ део формуле:
ИЗАБЕРИТЕ (ИФ (Х2 = "Јединични тест", 1, ИФ (Х2 = "Средњи рок", 2,3)), $ А $ 3: $ Е $ 7, $ А $ 11: $ Е $ 15, $ А $ 19: $ Е $ 23 )
Први аргумент формуле је ИФ (Х2 = “Унит Тест”, 1, ИФ (Х2 = “Мидтерм”, 2,3)), који проверава ћелију Х2 и види на који ниво испита се мисли. Ако је у питању Унит Тест, враћа $ А $ 3: $ Е $ 7, који има бодове за Унит Тест. Ако је средњи рок, враћа $ 11 УСД: $ 15 УСД, у супротном враћа 19 УСД: 23 УСД.
Ово чини низ табела ВЛООКУП динамичним и самим тим чини тражење у три смера.
Пример 5 - Добијање последње вредности са листе
Можете креирати формулу ВЛООКУП да бисте добили последњу нумеричку вредност са листе.
Највећи позитиван број који можете користити у програму Екцел је 9.99999999999999Е+307. То такође значи да је највећи број за претраживање у ВЛООКУП броју исти.
Мислим да вам никада неће бити потребна било каква калкулација која укључује тако велики број. И то је управо оно што можемо користити да добијемо последњи број на листи.
Претпоставимо да имате скуп података (у А1: А14) као што је приказано испод и желите да добијете последњи број на листи.
Ево формуле коју можете користити:
= ВЛООКУП (9.99999999999999Е+307, $ А $ 1: $ А $ 14,ИСТИНА)
Имајте на уму да горња формула користи приближно подударање ВЛООКУП (приметите ТРУЕ на крају формуле, уместо ФАЛСЕ или 0). Такође имајте на уму да списак не мора да се сортира да би ова ВЛООКУП формула радила.
Ево како функционише приближна функција ВЛООКУП. Скенира крајњу леву колону одозго надоле.
- Ако пронађе потпуно подударање, враћа ту вредност.
- Ако пронађе вредност која је већа од вредности за претраживање, враћа вредност у ћелији изнад ње.
- Ако је вредност за претраживање већа од свих вредности на листи, враћа последњу вредност.
У горњем примеру, на делу је трећи сценарио.
Од 9.99999999999999Е+307 је највећи број који се може користити у Екцелу, када се ово користи као вредност за претраживање, враћа последњи број са листе.
На исти начин, можете га користити и за враћање последње текстуалне ставке са листе. Ево формуле која то може учинити:
= ВЛООКУП ("ззз", $ А $ 1: $ А $ 8,1,ИСТИНА)
Следи иста логика. Екцел прегледава сва имена, а пошто се ззз сматра већим од било ког имена/текста који почиње с азбукама пре ззз, вратио би последњу ставку са листе.
Пример 6 - Делимично тражење помоћу џокер знакова и ВЛООКУП -а
Екцел замјенски знакови могу бити од велике помоћи у многим ситуацијама.
То је тај чаробни напитак који вашим формулама даје супер моћи.
Делимично тражење је потребно када морате да тражите вредност на листи, а не постоји потпуно подударање.
На пример, претпоставимо да имате скуп података као што је приказано у наставку, и желите да потражите компанију АБЦ на листи, али листа има АБЦ Лтд уместо АБЦ.
Не можете користити АБЦ као вредност за претраживање јер нема тачног подударања у колони А. Приближно подударање такође доводи до погрешних резултата и захтева да се листа сортира по растућем редоследу.
Међутим, можете користити замјенски знак унутар функције ВЛООКУП да бисте добили подударање.
Унесите следећу формулу у ћелију Д2 и превуците је у друге ћелије:
= ВЛООКУП ("*" & Ц2 & "*", $ А $ 2: $ А $ 8,1, ФАЛСЕ)
Како функционише ова формула?
У горњој формули, уместо да се користи вредност за тражење онаква каква је, она је са обе стране окружена звездицом (*) - “*” & Ц2 & “*”
Звездица је заменски знак у Екцелу и може представљати било који број знакова.
Коришћење звездице са обе стране вредности за претраживање говори Екцелу да мора да тражи било који текст који садржи реч у Ц2. Може имати било који број знакова пре или после текста у Ц2.
На пример, ћелија Ц2 има АБЦ, па функција ВЛООКУП прегледа имена у А2: А8 и тражи АБЦ. Проналази подударност у ћелији А2, јер садржи АБЦ у АБЦ Лтд. Није важно да ли има знакова лево или десно од АБЦ. Све док у текстуалном низу не постоји АБЦ, сматраће се подударањем.
Напомена: Функција ВЛООКУП увек враћа прву одговарајућу вредност и престаје да тражи даље. Дакле, ако имате АБЦ Лтд., и АБЦ Цорпоратион на листи, вратиће прву, а остале ће игнорисати.
Пример 7 - ВЛООКУП Враћање грешке упркос подударању вредности тражења
Може вас излудети када видите да постоји одговарајућа вредност претраживања и да функција ВЛООКУП враћа грешку.
На пример, у доњем случају постоји подударање (Матт), али функција ВЛООКУП и даље враћа грешку.
Сада, док видимо да постоји подударност, оно што не можемо видети голим оком је да би могло постојати водећи или задњи простор. Ако имате ове додатне размаке пре, после или између вредности тражења, то се не подудара потпуно.
То је често случај када увозите податке из базе података или их преузимате од неког другог. Ови водећи/задњи простори имају тенденцију да се ушуњају.
Решење овде је функција ТРИМ. Уклања све водеће или задње размаке или додатне размаке између речи.
Ево формуле која ће вам дати прави резултат.
= ВЛООКУП ("Матт", ТРИМ ($ А $ 2: $ А $ 9), 1,0)
Пошто је ово формула низа, користите Цонтрол + Схифт + Ентер уместо само Ентер.
Други начин би могао бити да прво прегледате низ за претраживање функцијом ТРИМ како бисте били сигурни да су сви додатни простори нестали, а затим користите функцију ВЛООКУП као и обично.
Пример 8 - Претразивање осетљиво на велика и мала слова
Подразумевано, вредност претраживања у функцији ВЛООКУП не разликује велика и мала слова. На пример, ако је ваша вредност за претраживање МАТТ, мат или Матт, све је исто за функцију ВЛООКУП. Вратиће прву одговарајућу вредност без обзира на случај.
Али ако желите да тражите велика и мала слова, морате да користите функцију ЕКСАЦТ заједно са функцијом ВЛООКУП.
Ево примера:
Као што видите, постоје три ћелије са истим именом (у А2, А4 и А5), али са различитим словима абецеде. На десној страни имамо три имена (Матт, МАТТ и мат) заједно са њиховим резултатима у математици.
Сада функција ВЛООКУП није опремљена за руковање вредностима за претраживање које разликују велика и мала слова. У овом горњем примеру, увек би се вратило 38, што је резултат за Матта у А2.
Да бисмо га разликовали од малих и великих слова, морамо да користимо помоћну колону (као што је приказано испод):
Да бисте добили вредности у помоћној колони, користите функцију = РОВ (). Једноставно ће добити број реда у ћелији.
Када добијете помоћну колону, ево формуле која ће дати резултат претраживања осетљив на велика и мала слова.
= ВЛООКУП (МАКС (ТАЧНО (Е2, $ А $ 2: $ А $ 9)*(РОВ ($ А $ 2: $ А $ 9))), $ Б $ 2: $ Ц $ 9,2,0)
Хајде сада да рашчланимо и схватимо шта ово ради:
- ЕКСАЦТ (Е2, $ А $ 2: $ А $ 9) - Овај део би упоредио вредност тражења у Е2 са свим вредностима у А2: А9. Враћа низ ТРУЕ/ФАЛСЕс где се враћа ТРУЕ где постоји потпуно подударање. У овом случају, вратио би следећи низ: {ТРУЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ}.
- ЕКСАЦТ (Е2, $ А $ 2: $ А $ 9)*(РОВ ($ А $ 2: $ А $ 9)) - Овај део множи низ ТРУЕ/ФАЛСЕ са бројем реда. Где год је ТРУЕ, он даје број реда , иначе даје 0. У овом случају, вратило би се {2; 0; 0; 0; 0; 0; 0; 0}.
- МАКС (ТАЧНО (Е2, $ А $ 2: $ А $ 9)*(РОВ ($ А $ 2: $ А $ 9))) - Овај део враћа максималну вредност из низа бројева. У овом случају, вратило би се 2 (што је број редака где постоји потпуно подударање).
- Сада једноставно користимо овај број као вредност за претраживање и користимо низ за претраживање као Б2: Ц9
Напомена: Пошто је ово формула низа, користите Цонтрол + Схифт + Ентер уместо само ентер.
Пример 9 - Коришћење ВЛООКУП -а са више критеријума
Екцел ВЛООКУП функција, у свом основном облику, може тражити једну вредност претраживања и вратити одговарајућу вредност из наведеног реда.
Али често постоји потреба за употребом ВЛООКУП -а у Екцелу са више критеријума.
Претпоставимо да имате податке са именом ученика, врстом испита и резултатом из математике (као што је приказано испод):
Коришћење функције ВЛООКУП за добијање оцене из математике за сваког ученика за одговарајуће испитне нивое могло би бити изазов.
На пример, ако покушате да користите ВЛООКУП са Маттом као вредношћу тражења, увек ће се вратити 91, што је оцена за прво појављивање Матта на листи. Да бисте добили резултат за Матт за сваку врсту испита (Унит Тест, Мид Терм и Финал), морате да креирате јединствену вредност претраживања.
То се може урадити помоћу колоне помоћника. Први корак је уметање помоћне колоне лево од резултата.
Сада, за креирање јединственог квалификатора за сваку инстанцу имена, користите следећу формулу у Ц2: = А2 & ”|” & Б2
Копирајте ову формулу у све ћелије у помоћној колони. Ово ће створити јединствене вредности тражења за сваку инстанцу имена (као што је приказано испод):
Док се имена понављала, нема понављања када се име комбинује са нивоом испитивања.
Ово олакшава посао јер сада можете користити вредности помоћних колона као вредности за тражење.
Ево формуле која ће вам дати резултат у Г3: И8.
= ВЛООКУП ($ Ф3 & "|" & Г $ 2, $ Ц $ 2: $ Д $ 19,2,0)
Овде смо комбиновали име ученика и ниво испита да бисмо добили вредност претраживања, а ми користимо ову вредност тражења и проверавамо је у помоћној колони да бисмо добили одговарајући запис.
Напомена: У горњем примеру смо користили | као сепаратор при спајању текста у помоћној колони. У неким изузетно ретким (али могућим) условима, можда имате два различита критеријума, али на крају дају исти резултат када се комбинују. Ево примера:
Имајте на уму да, иако су А2 и А3 различити, а Б2 и Б3 различити, комбинације на крају остају исте. Али ако користите сепаратор, чак би и комбинација била другачија (Д2 и Д3).
Ево водича о томе како користити ВЛООКУП са више критеријума без употребе помоћних колона. Овде можете погледати и мој видео водич.
Пример 10 - Руковање грешкама при коришћењу функције ВЛООКУП
Екцел ВЛООКУП функција враћа грешку када не може пронаћи наведену вредност претраживања. Можда не желите да ружна вредност грешке наруши естетику ваших података у случају да ВЛООКУП не може да пронађе вредност.
Можете лако уклонити вредности грешака са било којим значењем цео текст, попут „Није доступно“ или „Није пронађено“.
На пример, у доњем примеру, када покушате да пронађете резултат Брада на листи, он враћа грешку јер се Брадово име не налази на листи.
Да бисте уклонили ову грешку и заменили је нечим смисленим, умотајте своју функцију ВЛООКУП у функцију ИФЕРРОР.
Ево формуле:
= ИФЕРРОР (ВЛООКУП (Д2, $ А $ 2: $ Б $ 7,2,0), "Није пронађено")
Функција ИФЕРРОР проверава да ли је вредност враћена првим аргументом (која је у овом случају функција ВЛООКУП) грешка или не. Ако није грешка, враћа вредност помоћу функције ВЛООКУП, иначе враћа Нот Фоунд.
ИФЕРРОР функција је доступна од програма Екцел 2007 па надаље. Ако користите верзије пре тога, користите следећу функцију:
= ИФ (ИСЕРРОР (ВЛООКУП (Д2, $ А $ 2: $ Б $ 7,2,0)), "Нот Фоунд", ВЛООКУП (Д2, $ А $ 2: $ Б $ 7,2,0))
Такође погледајте: Како се носити са грешкама ВЛООКУП -а у програму Екцел.
То је то у овом ВЛООКУП водичу.
Покушао сам да обухватим главне примере коришћења функције Влоокуп у Екцелу. Ако желите да видите још примера додатих овој листи, обавестите ме у одељку за коментаре.
Напомена: Потрудио сам се да лекторирам овај водич, али у случају да пронађете грешке или правописне грешке, обавестите ме 🙂
Користећи Функција ВЛООКУП у програму Екцел - Видео
- Екцел ХЛООКУП функција.
- Екцел КСЛООКУП функција
- Екцел ИНДЕКС функција.
- Екцел ИНДИРЕКТНА функција.
- Екцел МАТЦХ функција.
- Екцел ОФФСЕТ функција.
