Садржај
- Функција ВЛООКУП у Екцел-у – општи опис и синтакса
- ВЛООКУП Примери
- Тачно или приближно подударање у функцији ВЛООКУП
- ВЛООКУП у Екцелу – ово морате запамтити!
Данас почињемо серију чланака који описују једну од најкориснијих функција Екцел-а − ВПР (ВЛООКУП). Ова функција је истовремено једна од најсложенијих и најмање разумљивих.
У овом туторијалу на ВПР Покушаћу да изложим основе што је једноставније могуће како би процес учења био што јаснији за неискусне кориснике. Поред тога, проучићемо неколико примера са Екцел формулама које ће демонстрирати најчешће случајеве употребе функције ВПР.
Функција ВЛООКУП у Екцел-у – општи опис и синтакса
Па шта је ВПР? Па, пре свега, то је Екцел функција. Шта ради? Он тражи вредност коју наведете и враћа одговарајућу вредност из друге колоне. технички говорећи, ВПР тражи вредност у првој колони датог опсега и враћа резултат из друге колоне у истом реду.
У најчешћој апликацији, функција ВПР претражује базу података за дати јединствени идентификатор и издваја неке информације везане за њега из базе података.
Прво слово у називу функције ВПР (ВЛООКУП) значи Ввертикала (Vвертикала). По њему можете разликовати ВПР од ГПР (ХЛООКУП), који тражи вредност у горњем реду опсега − Гхоризонтално (Hхоризонтално).
функција ВПР доступно у Екцел 2013, Екцел 2010, Екцел 2007, Екцел 2003, Екцел КСП и Екцел 2000.
Синтакса функције ВЛООКУП
функција ВПР (ВЛООКУП) има следећу синтаксу:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
Као што видите, функција ВПР у Мицрософт Екцел-у има 4 опције (или аргументе). Прва три су обавезна, последња је опциона.
- лоокуп_валуе (лоокуп_валуе) – Вредност коју треба тражити. Ово може бити вредност (број, датум, текст) или референца ћелије (која садржи тражену вредност), или вредност коју враћа нека друга Екцел функција. На пример, ова формула ће тражити вредност 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
Ако је тражена вредност мања од најмање вредности у првој колони опсега који се тражи, функција ВПР пријавиће грешку #АТ (#Н/А).
- табле_арраи (табела) – две или више колона података. Запамтите, функцију ВПР увек тражи вредност у првој колони опсега датог у аргументу табле_арраи (сто). Опсег који се може видети може да садржи различите податке, као што су текст, датуми, бројеви, логичке вредности. Функција не разликује велика и мала слова, што значи да се велика и мала слова сматрају истим. Дакле, наша формула ће тражити вредност 40 у ћелијама из A2 до АКСНУМКС, јер је А прва колона опсега А2:Б15 датог у аргументу табле_арраи (сто):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- цол_индек_нум (број_колоне) је број колоне у датом опсегу из којег ће бити враћена вредност у пронађеном реду. Крајња лева колона у датом опсегу је 1, друга колона је 2, трећа колона је 3 и тако даље. Сада можете прочитати целу формулу:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)Формула која тражи вредност 40 у опсегу АКСНУМКС: АКСНУМКС и враћа одговарајућу вредност из колоне Б (јер је Б друга колона у опсегу А2:Б15).
Ако вредност аргумента цол_индек_нум (број_колоне) мањи од 1онда ВПР пријавиће грешку #ВАЛУЕ! (#ВРЕДНОСТ!). А ако је више од броја колона у опсегу табле_арраи (табела), функција ће вратити грешку #РЕФ! (#ЛИНК!).
- ранге_лоокуп (ранге_лоокуп) – одређује шта треба тражити:
- тачно подударање, аргумент мора бити једнак ЛАЖ (ФАЛСЕ);
- приближно подударање, аргумент једнак ТРУЕ ЦОДЕ (ТРУЕ) или уопште није наведено.
Овај параметар није обавезан, али веома важан. Касније у овом туторијалу ВПР Показаћу вам неколико примера који објашњавају како да напишете формуле за проналажење тачних и приближних подударања.
ВЛООКУП Примери
Надам се да је функција ВПР постати ти мало јаснији. Сада погледајмо неке случајеве употребе ВПР у формулама са реалним подацима.
Како користити ВЛООКУП за претрагу у другом Екцел листу
У пракси формуле са функцијом ВПР се ретко користе за тражење података на истом радном листу. Чешће него не, тражићете и преузимати одговарајуће вредности са другог листа.
Да бисте користили ВПР, претражите у другом Мицрософт Екцел листу, Морате у аргументу табле_арраи (табела) наведите име листа са знаком узвика иза којег следи опсег ћелија. На пример, следећа формула показује да је опсег А2: Б15 је на листу са именом СхеетКСНУМКС.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
Наравно, назив листа не мора да се уноси ручно. Само почните да куцате формулу, а када је у питању аргумент табле_арраи (табела), пређите на жељени лист и мишем изаберите жељени опсег ћелија.
Формула приказана на снимку екрана испод тражи текст „Производ 1“ у колони А (то је 1. колона опсега А2:Б9) на радном листу Цене.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
Имајте на уму да када тражите текстуалну вредност, морате је ставити у наводнике (“”), као што се обично ради у Екцел формулама.
За аргумент табле_арраи (табела) пожељно је увек користити апсолутне референце (са знаком $). У овом случају, опсег претраге ће остати непромењен када се формула копира у друге ћелије.
Претражујте у другој радној свесци помоћу ВЛООКУП-а
Да функционише ВПР радили између две Екцел радне свеске, потребно је да наведете име радне свеске у угластим заградама пре назива листа.
На пример, испод је формула која тражи вредност 40 на листу СхеетКСНУМКС у књизи Нумберс.клск:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
Ево најлакшег начина да креирате формулу у Екцел-у помоћу ВПРкоја повезује са другом радном свеском:
- Отвори обе књиге. Ово није потребно, али је лакше направити формулу на овај начин. Не желите да ручно унесете назив радне свеске, зар не? Поред тога, заштитиће вас од случајних грешака у куцању.
- Почните да куцате функцију ВПРа када је у питању аргументација табле_арраи (табела), пређите на другу радну свеску и изаберите тражени опсег претраге у њој.
Снимак екрана испод приказује формулу са претрагом постављеном на опсег у радној свесци ПрицеЛист.клск на листу Цене.
функција ВПР ће радити чак и када затворите тражену радну свеску и пуна путања до датотеке радне свеске се појави на траци формуле, као што је приказано у наставку:
Ако назив радне свеске или листа садржи размаке, онда се мора ставити у апострофе:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
Како користити именовани опсег или табелу у формулама са ВЛООКУП-ом
Ако планирате да користите исти опсег претраге у више функција ВПР, можете креирати именовани опсег и унети његово име у формулу као аргумент табле_арраи (сто).
Да бисте креирали именовани опсег, једноставно изаберите ћелије и унесите одговарајуће име у поље Име, лево од траке формуле.
Сада можете записати следећу формулу за проналажење цене производа Производ КСНУМКС:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
Већина имена опсега функционише за целу Екцел радну свеску, тако да нема потребе да наведете име листа за аргумент табле_арраи (табела), чак и ако се формула и опсег претраге налазе на различитим радним листовима. Ако се налазе у различитим радним свескама, онда пре назива опсега морате навести назив радне свеске, на пример, овако:
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)
Дакле, формула изгледа много јаснија, слажете се? Такође, коришћење именованих опсега је добра алтернатива апсолутним референцама јер се именовани опсег не мења када копирате формулу у друге ћелије. То значи да можете бити сигурни да ће опсег претраге у формули увек остати тачан.
Ако конвертујете опсег ћелија у пуноправну Екцел табелу помоћу команде Табела (Табела) картица Инсертион (Инсерт), онда када изаберете опсег помоћу миша, Мицрософт Екцел ће аутоматски додати називе колона (или име табеле ако изаберете целу табелу) у формулу.
Готова формула ће изгледати отприлике овако:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
Или можда чак и овако:
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
Када користите именоване опсеге, везе ће упућивати на исте ћелије без обзира где копирате функцију ВПР у оквиру радне свеске.
Коришћење џокер знакова у ВЛООКУП формулама
Као и код многих других функција, ВПР Можете користити следеће џокер знакове:
- Знак питања (?) – замењује било који појединачни знак.
- Звездица (*) – замењује било који низ знакова.
Коришћење џокер знакова у функцијама ВПР може бити корисно у многим случајевима, на пример:
- Када се не сећате тачно текста који треба да пронађете.
- Када желите да пронађете неку реч која је део садржаја ћелије. Знам да ВПР тражи по садржају ћелије у целини, као да је опција омогућена Упарите цео садржај ћелије (Цела ћелија) у стандардној Екцел претрази.
- Када ћелија садржи додатне размаке на почетку или на крају садржаја. У таквој ситуацији, можете дуго да мучите мозак, покушавајући да схватите зашто формула не функционише.
Пример 1: Тражење текста који почиње или се завршава одређеним знаковима
Рецимо да желите да тражите одређеног купца у бази података приказаној у наставку. Не сећате се његовог презимена, али знате да почиње са „ацк“. Ево формуле која ће добро обавити посао:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
Сада када сте сигурни да сте пронашли тачно име, можете користити исту формулу да пронађете износ који је платио овај купац. Да бисте то урадили, само промените трећи аргумент функције ВПР до жељеног броја колоне. У нашем случају, ово је колона Ц (3. у опсегу):
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
Ево још неколико примера са џокер знаковима:
~ Пронађите име које се завршава на "човек":
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ Пронађите име које почиње са „ад” и завршава се са „син”:
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ На листи налазимо прво име које се састоји од 5 карактера:
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
Да функционише ВПР са џокер знаковима ради исправно, као четврти аргумент који увек треба да користите ЛАЖ (ФАЛСЕ). Ако опсег претраге садржи више од једне вредности која се подудара са терминима за претрагу са џокер знаковима, тада ће бити враћена прва пронађена вредност.
Пример 2: Комбинујте џокер знакове и референце ћелија у ВЛООКУП формулама
Сада погледајмо мало сложенији пример како претраживати помоћу функције ВПР по вредности у ћелији. Замислите да је колона А листа лиценцних кључева, а колона Б листа имена која поседују лиценцу. Поред тога, у ћелији Ц1 имате део (неколико знакова) неке врсте лиценцног кључа и желите да пронађете име власника.
Ово се може урадити помоћу следеће формуле:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
Ова формула тражи вредност из ћелије Ц1 у датом опсегу и враћа одговарајућу вредност из колоне Б. Имајте на уму да у првом аргументу користимо знак амперсанд (&) пре и после референце ћелије да повежемо текстуални низ.
Као што можете видети на слици испод, функција ВПР враћа „Џереми Хил“ јер његов лиценцни кључ садржи низ знакова из ћелије Ц1.
Имајте на уму да аргумент табле_арраи (табела) на снимку екрана изнад садржи име табеле (Табела7) уместо навођења опсега ћелија. То је оно што смо урадили у претходном примеру.
Тачно или приближно подударање у функцији ВЛООКУП
И на крају, хајде да детаљније погледамо последњи аргумент који је наведен за функцију ВПР - ранге_лоокуп (интервал_виев). Као што је поменуто на почетку лекције, овај аргумент је веома важан. Можете добити потпуно различите резултате у истој формули са њеном вредношћу ТРУЕ ЦОДЕ (ТАЧНО) или ЛАЖ (ФАЛСЕ).
Прво, хајде да сазнамо шта Мицрософт Екцел подразумева под тачним и приближним подударањима.
- Ако аргумент ранге_лоокуп (ранге_лоокуп) је једнако ЛАЖ (ФАЛСЕ), формула тражи тачно подударање, тј. потпуно исту вредност као дату у аргументу лоокуп_валуе (лоокуп_валуе). Ако у првој колони опсега табле_арраи (табела) наиђе на две или више вредности које одговарају аргументу лоокуп_валуе (сеарцх_валуе), тада ће бити изабран први. Ако се не пронађе ниједно подударање, функција ће пријавити грешку #АТ (#Н/А). На пример, следећа формула ће пријавити грешку #АТ (#Н/А) ако нема вредности у опсегу А2:А15 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - Ако аргумент ранге_лоокуп (ранге_лоокуп) је једнако ТРУЕ ЦОДЕ (ТРУЕ), формула тражи приближно подударање. Тачније, прво функција ВПР тражи тачно подударање, а ако није пронађено, бира приближно. Приближно подударање је највећа вредност која не прелази вредност наведену у аргументу. лоокуп_валуе (лоокуп_валуе).
Ако аргумент ранге_лоокуп (ранге_лоокуп) је једнако ТРУЕ ЦОДЕ (ТРУЕ) или није наведено, онда вредности у првој колони опсега треба сортирати у растућем редоследу, односно од најмањег до највећег. Иначе, функција ВПР може вратити погрешан резултат.
Да би боље разумели важност избора ТРУЕ ЦОДЕ (ИСТИНА) или ЛАЖ (ФАЛСЕ), погледајмо још неке формуле са функцијом ВПР и погледајте резултате.
Пример 1: Проналажење тачног подударања са ВЛООКУП-ом
Као што се сећате, за тражење тачног подударања, четврти аргумент функције ВПР требало би да буде важно ЛАЖ (ФАЛСЕ).
Вратимо се на табелу из првог примера и сазнамо која животиња може да се креће великом брзином 50 миља на сат. Верујем да вам ова формула неће изазвати никакве потешкоће:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
Имајте на уму да наш опсег претраге (колона А) садржи две вредности 50 – у ћелијама A5 и A6. Формула враћа вредност из ћелије B5. Зашто? Јер када се тражи тачно подударање, функција ВПР користи прву пронађену вредност која одговара оној која се тражи.
Пример 2: Коришћење ВЛООКУП-а за проналажење приближног подударања
Када користите функцију ВПР за тражење приближног подударања, односно када аргумент ранге_лоокуп (ранге_лоокуп) је једнако ТРУЕ ЦОДЕ (ТРУЕ) или изостављено, прва ствар коју треба да урадите је да сортирате опсег према првој колони у растућем редоследу.
Ово је веома важно јер функција ВПР враћа следећу највећу вредност после дате, а затим се претрага зауставља. Ако занемарите исправно сортирање, на крају ћете добити врло чудне резултате или поруку о грешци. #АТ (#Н/А).
Сада можете користити једну од следећих формула:
=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)
Као што видите, желим да сазнам која од животиња има најближу брзину 69 миља на сат. И ево резултата који ми је функција вратила ВПР:
Као што видите, формула је дала резултат Антилопе (Антилопа), чија брзина 61 миља на сат, иако списак такође укључује гепард (Гепард) који трчи на брзину 70 миља на сат, а 70 је ближе 69 него 61, зар не? Зашто се ово дешава? Јер функција ВПР када се тражи приближно подударање, враћа највећу вредност која није већа од оне која се тражи.
Надам се да ови примери бацају светло на рад са функцијом ВПР у Екцел-у, а ви на њу више не гледате као на аутсајдера. Сада не шкоди укратко поновити кључне тачке материјала који смо проучавали како бисмо га боље фиксирали у памћењу.
ВЛООКУП у Екцелу – ово морате запамтити!
- функција ВПР Екцел не може да гледа лево. Увек тражи вредност у крајњој левој колони опсега датог аргументом табле_арраи (сто).
- У функцији ВПР све вредности не разликују велика и мала слова, односно мала и велика слова су еквивалентна.
- Ако је вредност коју тражите мања од минималне вредности у првој колони опсега који се тражи, функција ВПР пријавиће грешку #АТ (#Н/А).
- Ако 3. аргумент цол_индек_нум (број_колоне) мањи од 1функција ВПР пријавиће грешку #ВАЛУЕ! (#ВРЕДНОСТ!). Ако је већи од броја колона у опсегу табле_арраи (табела), функција ће пријавити грешку #РЕФ! (#ЛИНК!).
- Користите апсолутне референце ћелије у аргументу табле_арраи (табела) тако да се при копирању формуле сачува исправан опсег претраге. Покушајте да користите именоване опсеге или табеле у Екцел-у као алтернативу.
- Када радите приближну претрагу подударања, запамтите да прва колона у опсегу који тражите мора бити сортирана узлазним редоследом.
- На крају, запамтите важност четвртог аргумента. Користите вредности ТРУЕ ЦОДЕ (ИСТИНА) или ЛАЖ (НЕЛАЖНО) намерно и решићете се многих главобоља.
У следећим чланцима нашег упутства за функцију ВПР у Екцел-у ћемо научити напредније примере, као што је извођење различитих прорачуна коришћењем ВПР, издвајање вредности из више колона и још много тога. Хвала вам што сте прочитали овај водич и надам се да се видимо поново следеће недеље!