ВЛООКУП у Екцелу: Ово функција може да уради

Примена и дефиниција ове Екцел функције

ВЛООКУП је Екцел функција помоћу које корисник може да тражи и процени садржај табеле. Ова функција је доступна у верзијама из програма Екцел 2007 за Виндовс и Мац.

Шта је ВЛООКУП?

Могуће употребе ВЛООКУП -а овде ће бити објашњене на примеру: У овом сте велики љубитељ књижевности и стога сте направили сопствену Екцел табелу у којој можете пажљиво сортирати књиге које сте сакупили. Свако дело је унето са подацима о следећим категоријама:

• аутор

• наслов

• Број стране

• Година издавања

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

Како се користи ВЛООКУП?

Пре него што уопште размислите о формулисању формула, требало би да утврдите где ће се касније унети поље за унос и различита поља за излаз. Да бисте то учинили, има смисла створити засебну табелу која је у почетку празна и на тај начин оставља простор за наведене податке. Ако дизајнирате ову нову табелу на примеру постојеће табеле, касније ћете имати предност у уштеди времена.

На основу тога, формула ВЛООКУП се може креирати ручно или аутоматски генерисати у Екцелу. За почетнике, вреди користити последњи приступ како би постепено упознали структуру и ефекат формуле. Да бисте то урадили, дугме за „Уметни функцију“ изабрано је на картици „Формуле“. ВЛООКУП је скривен у прозору који се отвори. Након потврде, поново се отвара прозор у који се могу попунити четири параметра формуле. Су:

• Критеријум претраге

• матрица

• Индекс колоне

• Ареа_референце

Сирови нацрт формуле стога изгледа овако:

= ВЛООКУП (критеријум претраге, матрица, индекс колоне, опсег_ веза)

и у једној могућој апликацији попут ове:

= ВЛООКУП (Х3; А3: Е40; 5)

Критеријум претраге

Да би функција знала коју вредност треба користити као полазну тачку, линија која је изабрана као поље за унос два корака раније бележи се у поље "критеријум претраге". У нашем примеру, ту је унето име аутора књиге „Пхиллип Пулманн“. Ово чини формулу флексибилном и не мора се поново прилагођавати чим се унета вредност промени.

матрица

Поље за унос "Матрик" описује табелу у којој се могу пронаћи информације које треба послати. Ова посебна матрица тако садржи и колоне за наслов књиге, број странице и годину издања.

Матрица се потпуно бира једном без наслова од горње леве до доње десне маргине. На овај начин Екцел зна који садржај се мора узети у обзир при оцењивању.

Индекс колоне

Поље за унос "индекса колоне" тражи од корисника да дефинише колону матрице у којој је наведена само тражена вредност. Додељивање колона нумерисано је хронолошки. То значи да прва колона табеле прима вредност 1, друга вредност 2 итд. У нашем примеру ово одговара индексу колоне 1 за аутора, индексу колоне 2 за наслов, индексу колоне 3 за број странице и индекс колоне 4 за годину издања.

Да би табела била што флексибилнија, уместо броја може се повезати наслов колоне. Ово има предност што се формула може без проблема пребацити у друге редове, јер се наслов колоне може флексибилно прилагодити сваки пут.

Пажња: ВЛООКУП чита матрицу с лева на десно, због чега се индекс колоне мора поставити десно од колоне за критеријум претраживања да би је функција узела у обзир!

Ареа_референце

Параметар "Ранге_Лоокуп" довршава формулу ВЛООКУП одређивањем тачности са којом се табела процењује. Међутим, разликује се од претходно наведених компоненти формуле јер је опционална. Ако је вредност 0 унета за „нетачно“, Екцел тражи само вредност која је наведена као критеријум претраживања. С вриједношћу 1 за "труе", међутим, потрага се наставља за очигледним вриједностима ако се тачна вриједност не може пронаћи.

Навођење овог параметра није обавезно јер је вриједност 1 задано задана. Ово подешавање ће касније бити корисно у напредном ВЛООКУП -у са више критеријума претраживања.

Спајање

Чим се поставе сви потребни параметри, може се користити ВЛООКУП. Након уноса критеријума претраге и потврде функције, вредност коју тражите појављује се у реду који је дефинисан као излазно поље.

У нашем примеру, сада је приказан наслов књиге „Златни компас“, који одговара унетом аутору. Да бисте брзо сазнали број странице и годину објављивања, ништа друго није потребно учинити него превући постојећу формулу ВЛООКУП у наредне ћелије. То је тако лако јер је индекс колоне ВЛООКУП -а повезан са заглављем колоне прве табеле, а друга табела је такође структурирана истим редоследом.

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

ВЛООКУП са више критеријума претраживања

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

ВЛООКУП у више Екцел табела

Ако се критеријум претраживања не може пронаћи само у једној табели, већ могуће и у другој, формула ВЛООКУП се може прилагодити на одговарајући начин. За ово, и иф функција и ИСЕРРОР функција морају бити постављене испред постојеће формуле. За то је потребно пет параметара:

• Критеријум претраге

• Матрик1 и Матрик 2

• Индекс колоне1 и индекс колоне2

Резултат изгледа овако:

= ИФ (ИСЕРРОР (ВЛООКУП (критеријум претраге, матрица1, колона-индекс1, 0));
ВЛООКУП (критеријум претраживања; матрица2; индекс колоне2,0); ВЛООКУП (критеријум претраживања; матрица1; индекс колоне1;))

и у једној могућој апликацији попут ове:

= ИФ (ИСЕРРОР (ВЛООКУП (Е5, А5: Б9,2, 0)), ВЛООКУП (Е5, А13: Б17,2, 0), ВЛООКУП (Е5, А5: Б9,2, 0))

Критеријум претраживања се користи за уметање вредности коју треба тражити у две табеле. Матрик1 и Матрик2 дефинишу одговарајуће ћелијске области две табеле. Индекс колоне1 и индекс колоне2 користе се за детаљније дефинисање колона одговарајуће табеле које треба претражити.

Ако се вредност коју тражите појави у обе табеле, Екцел ће исписати резултат из прве табеле. Међутим, ако вредност није пронађена ни у једној од две табеле, појавиће се порука о грешци. Предност формуле је у томе што две листе не морају имати исту структуру или бити исте величине.

Доделите вредности категоријама помоћу ВЛООКУП -а

Додатна функција ВЛООКУП -а омогућава да се наведене вредности аутоматски поделе на слова и предикате по вашем избору. У нашем претходном примеру, додатну колону табеле треба уметнути за тип књиге. Књиге дужине до 50 страница требало би да спадају у жанр приповетке, док се књиге од 51 до 150 страница додељују новели и од 151 страници роману. Да би то било могуће, у ВЛООКУП -у није потребна додатна формула, само употреба увијених заграда “{}”. Готова формула изгледа овако:

= ВЛООКУП (Б1; {1. "Кратка прича"; 51. "Новела"; 151. "Новела"}; 2)

Садржај увијених заграда означава матрицу која дефинише подручје одговарајуће врсте књиге. Додељивање бочне дужине одговарајућем роду стога је смештено унутар увијених заграда. Формула користи парове вредности, од којих је свака одвојена тачком. Матрица {1. "Кратка прича"; 51. "Новела"; 151. "Новела"} се чита на следећи начин:

"Од 1 емисије кратка прича, од 51 прикажи новелу, од 151 прикажи роман."

Ова матрица се лако може прилагодити различитим задацима. Ово се с једне стране тиче величине и броја матрица, као и њиховог означавања. Тако је могуће исписати низове или бројеве као резултат уместо појединачних слова. Све што треба да урадите је да прилагодите слова у формули.

ВЛООКУП на више радних листова

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

Замислите да, поред својих књига, у Екцел табели наведете и своје прикупљене филмове. Затим комбинујете обе колекције у једну велику табелу.

Предност ове процедуре није само у повећаном редоследу, већ и у избегавању потенцијалних грешака. Ако желите да креирате нови унос или ажурирате постојећи, не морате да тражите у великој табели, већ уместо тога можете да приступите мањим. Вредности се затим аутоматски преносе у збирну Екцел табелу. Због тога је преписивање у велику табелу сувишно, чиме се у најбољем случају избегава несрећан потез и накнадни ланац порука о грешци.

Како изгледа формула?

Ова функција је поново омогућена уметањем друге формуле. Док је за претраживање са више критеријума потребна додатна формула ИФ, рад са више радних листова захтева формулу ИНДИРЕЦТ. Ово омогућава да се за матрицу ВЛООКУП одреди опсег из друге табеле.

= ВЛООКУП (критеријум претраге; ИНДИРЕЦТ (матрица); индекс колоне; опсег_везе)

Пажња: Ова формула ће функционисати само ако појединачне табеле на различитим листовима имају исте називе као наслови колона опште табеле. Читаве табеле се могу именовати у "пољу за име" у горњем левом углу изнад мреже ћелија. Табеле које су већ именоване могу се видети комбинацијом тастера Цтрл + Ф3.

Суочавање са новим порукама грешке

Рад са повезаним Екцел табелама може довести до нежељених проблема. Ово укључује нарочито излаз погрешних вредности. У случају да се прикаже погрешна вредност 0, постоји мали проблем у поставкама програма Екцел, који се може брзо решити.

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

ВЛООКУП - преглед

ВЛООКУП је корисна Екцел функција која се може користити за претраживање и процену табела. Његове предности су евидентне у једноставној и флексибилној примени. На овај начин сви који редовно раде са Екцел табелама могу имати користи од ове функције. Био то приватни колекционар који ствара своје мале табеле или велика компанија која обрађује знатно значајније скупове података.

С друге стране, ако и даље имате неодговорене захтеве које ВЛООКУП није могао да испуни, можете се радовати додатној Екцел опцији: Мицрософт нуди Екцел 365 корисницима нови КСЛООКУП од почетка 2022-2023. Ово се надограђује на способности ВЛООКУП -а и надопуњује их додатним, понекад чак и једноставнијим, функцијама. Стога се у овом тренутку отвара и нова рутина у евалуацији података.