Увод у функцију ВЛООКУП
Функција ВЛООКУП у Екцелу је без сумње функција која се највише користи и говори. На сваком разговору за посао који захтева Екцел вештине, ово је најчешће постављано питање које ако знате да користите функцију ВЛООКУП.
Функција ВЛООКУП спада у категорију претраживања екцел функција. В у ВЛООКУП -у означава вертикалу. Ова функција се користи за тражење података који су вертикално структурирани. За хоризонтално тражење постоји функција која се зове ХЛООКУП.
Екцел ВЛООКУП функција тражи прво подударање дате вредности која се назива вредност претраживања, у табели података и враћа вредност из датог индекса колоне. Подударање може бити приближно или тачно.
Иако је ВЛООКУП једноставан за употребу, он има своја ограничења и позадине. Разговараћемо о свему, укључујући предности и слабости функције.
Синтакса:
= ВЛООКУП (лоокуп_валуе, табле_арраи, цол_индек_нумбер, [ранге_лоокуп])
Шта је вредност тражења
Лоокуп_валуе: То је вредност коју желите да тражите у низу табела.
На пример, ако тражите ролу бр. 110 у ученичком столу онда његова 110 је вредност тражења.
Поље_табеле: Табела из које желите да тражите вредност тражења.
На пример, ако тражите 110 у низу табела Б5: Е17. Онда Б5: Е17 је ваш таблични низ.
цол_индек_нумбер: Број колоне у низу табела из које желите да преузмете резултате.
На пример ако је у низу табела Б5: Е17 желите да добијете вредност из колоне Д онда ће ваш индекс колоне бити 3 (рачунајући од Б до Д (Б, Ц, Д)).
[ранге_лоокуп]: ФАЛСЕ ако желите да тражите тачну вредност, ТРУЕ ако желите приближно подударање.
Како се користи функција ВЛООКУП
Када имате структуриране податке и желите да тражите податке у тим подацима, решење је ВЛООКУП.
ВЛООКУП Начини подударања
ВЛООКУП има два начина подударања, приближно и потпуно подударање. Подразумевано, ВЛООКУП изводи приближно подударање. Али ако желите да приморате функцију ВЛООКУП да изврши потпуно подударање, можете то учинити и ви. У већини случајева ја и ја чврсто верујемо да и други покушавамо да направимо потпуно подударање помоћу функције ВЛООКУП. Не разумем зашто програмери програма Екцел сматрају да би корисници желели да користе приближно приближне резултате.
1. ВЛООКУП Приближно подударање
= ВЛООКУП (лоокуп_валуе, табле_арраи, цол_индек_нумбер, 1)
У приближном подударању ВЛООКУП -а, ако вредност није пронађена, тада се подудара се последња вредност која је мања од вредности тражења и враћа се вредност из датог индекса колоне.
ВЛООКУП се подразумевано подудара приближно ако изоставимо променљиву тражење опсега. Приближно подударање је корисно када желите да направите приближно подударање и када низ табела сортирате по растућем редоследу.
Влоокуп тражи вредност и ако не пронађе вредност у низу табела, онда се подудара са вредношћу која је мања од те вредности у низу табела. Хајде да то схватимо на примеру.
Пример 1
Овде имамо списак ученика. Сваки ученик је постигао неке оцене на тесту. Сада желим да дам оцену према њиховим оценама.
Сваки ученик који је постигао мање од 40 бодова треба да има ознаку Ф, студент који је постигао оцену између 40 и 60 треба да има ознаку Ц, и тако даље као што је приказано на слици испод.
Ову формулу ВЛООКУП бисмо написали у Е2 и повукли је надоле.
= ВЛООКУП (Д2, $ Х $ 2: $ И $ 6,2, Тачно)
Како то ради?
У горњем примеру наше вредности претраживања су у колони Д, почев од Д2. Низ табела је Х2: И6 (имајте на уму да је сортиран узлазно и апсолутно). Колона из које добијамо податке је 2. И тип подударања који смо дефинисали приближно додавањем ТРУЕ (ништа и 1 неће значити исто).
Па хајде да испитамо прву вредност тражења Д2 која садржи 40.
- ВЛООКУП тражи 40 у првој колони (Х) табличног низа Х2: И6.
- Налази 40 на другом месту у ћелији Х3.
- Сада прелази у другу колону са Х3 на И3 и враћа Д.
У овом случају влоокуп је пронашао потпуно подударање. Да видимо следећи случај.
Друга вредност претраге Д3 која садржи 36.
- ВЛООКУП тражи 36 у првој колони поља табеле Х2: И6.
- ВЛООКУП није могао да пронађе 36 нигде у првој колони.
- Пошто ВЛООКУП није могао да пронађе 36, подудара се са последњом пронађеном вредношћу која је мања од вредности тражења.
- Прва вредност која је мања од 36 је 0, па враћа Ф која је повезана са 0.
Ово се дешава за сваки случај овде. За 92, последња вредност која је мања од 92 је 90. Отуда се А враћа за приближно приближавање.
2. ВЛООКУП Тачно подударање
Овај најчешће коришћени облик ВЛООКУП -а, а може бити и најкориснији. Када желите да тражите тачну вредност у првој колони поља табеле, користите потпуно подударање. На пример, ако тражите образац са ИД -ом, најрадије бисте желели потпуно подударање уместо било које вредности која је мања од ове вредности.
Да бисмо присилили ВЛООКУП на потпуно подударање у екцелу, прослеђујемо 0 или ФАЛСЕ као ранге_лоокуп параметар.
= ВЛООКУП (лоокуп_валуе, табле_арраи, цол_индек_нумбер, 0)
Ако вредност није пронађена у првој колони поља табеле, формула ће вратити грешку #Н/А.
Погледајмо пример.
Пример 2
У овом примеру желим да напишем само списак ученика у А2, а у Б2 желим да преузмем име ученика а у Ц2 његов Оцена. Једноставно. Табеларни низ је Б5: Д17. Зашто?
Да бисте то урадили, напишите ове две формуле у ћелију у ћелију Б2 и Ц2.
= ВЛООКУП (А2, $ Б $ 5: $ Е $ 17,2,0)
= ВЛООКУП (А2, $ Б $ 5: $ Е $ 17,3,0)
Сада који год ИД да упишете у ћелију А2, функција ВЛООКУП ће тражити потпуно подударање у колони Б и вратиће вредност из датог цол_индек.
Како функционише ВЛООКУП са тачним подударањем?
ВЛООКУП за тачно подударање у Екцелу је једноставан. Тражи вредност лоокуп -а у првој колони датог табле_арраи -а и враћа вредност из вредности из датог броја_индекса. Ако вредност није пронађена, ВЛООКУП враћа грешку #Н/А.
Најбоља употреба функције ВЛООКУП
Влоокуп има много употреба. Расправљаћу о неким најкориснијим случајевима употребе.
3. Користите Екцел ВЛООКУП за оцењивање уместо сложене угнежђене ИФ функције
Користите ВЛООКУП приближно подударање да бисте заменили угнежђену ИФ функцију. У примеру 1 смо видели како можемо користити влоокуп за оцењивање ученика према њиховим оценама.
Приближно подударање ВЛООКУП -а брже је него угнежђено ако користи интерно бинарно претраживање.
4. Користите ВЛООКУП за преузимање података
Овај најчешћи задатак који се лако може извршити помоћу функције ВЛООКУП у Екцелу. У примеру 2 то смо урадили да дохватимо податке ученика користећи њихов број бр.
5. Проверите да ли је вредност у запису помоћу Влоокупа.
ВЛООКУП се може користити за проверу да ли се нека вредност налази на некој листи или не.
У примеру 2 када пишемо 152, враћа грешку #Н/А. Ако ВЛООКУП враћа #Н/А, то значи да нигде у датим подацима није пронашао дату вредност.
Припремио сам кратак чланак о овоме. Можете га прочитати овде.
Коришћење ВЛООКУП формуле за проверу да ли вредност постоји
Аутоматско добијање индекса колоне
Припадам оној групи људи, која не жели да понавља исти задатак изнова и изнова. Тако да ме промена броја цол_индек увек изнова нервира и увек то избегавам. Заправо, избегавам било какво уређивање формула када га напишем, осим ако и док не постоји друга опција.
Индекс колоне може се аутоматски израчунати у Екцелу. Постоји неколико начина за то. Погледајмо неке од њих овде.
6. Користите функцију ВЛООКУП са функцијом МАТЦХ
Дакле, као што знамо да функција МАТЦХ враћа пронађени индекс усклађене вредности.
Дакле, ако имамо потпуно исте наслове на локацији за тражење као изворни подаци, онда можемо користити за добивање цол_индек. Како? Да видимо…
У доњим подацима желимо само да напишемо број ролне у Г2 и желимо да преузмемо име ученика, оцене и оцену у Х2, који год наслов тамо упишемо.
Напишите ову формулу у ћелију Х2
= ВЛООКУП (Г2, Б2: Е14, МАТЦХ (Х1, Б1: Е1,0), 0)
Сада кад год промените наслов у Х1 та вредност у Х2 ће бити приказана из те колоне.
Можете да користите падајући мени помоћу провере података који садржи све наслове из табеле како бисте га учинили лакшим за коришћење.
Овде ВЛООКУП ради потпуно подударање. Сада ВЛООКУП ради своје уобичајене ствари извлачења података. Магију чини функција МАТЦХ на положају цол_индек.
Овде ВЛООКУП види било коју вредност у Г2 у првој колони матрице табела Б2: Е14. Сада на месту цол_индек имамо УТАКМИЦА (Х1, Б1: Е1,0).
МАТЦХ функција тражи било коју вредност у Х1 у опсегу Б1: Е1 и враћа индекс вредности подударања.
На пример, када куцамо ученик у Н1 то тражи у домету Б1: Е1. Налази се у Ц2. дакле враћа 2. Ако откуцамо Оцена вратиће се оцена ученика.
Имајте на уму да када откуцамо Регион, враћа се #Н/А. Зато што је ван опсега табеле. О томе ћемо касније у чланку.
Ово је најбољи начин да ВЛООКУП буде аутоматски. Постоје и друге методе, али нису толико флексибилне од ове.
7. Користите функцију ВЛООКУП са функцијом ЦОЛУМН
Функција колона враћа број колоне испоручене референце. А шта нам је потребно за преузимање података помоћу ВЛООКУП -а? Цол_индек. Дакле, помоћу функције ЦОЛУМН можемо наравно добити податке из било којег скупа података.
Узмимо горњи пример. Али сада морамо да преузмемо целе податке. Не само једна колона.
Напишите ову формулу у Х2 и копирајте у И2 и Ј2.
= ВЛООКУП ($ Г2, $ Б $ 2: $ Е $ 14, ЦОЛУМН (Б1), 0)
Ово ће вам дати динамичан резултат. Како? Хајде да видимо.
ВЛООКУП ради као и обично. На цолумн_индек смо писали КОЛОНА (Б1), што ће се превести у 2, будући да име студента имамо у колони 2 из колоне Б, враћа име ученика.
Важно: Ово функционише јер наша табела почиње од колоне Б. Ако се ваша таблица налази на средини листа, морат ћете одузети или додати неке бројеве тако да одговара вашим захтјевима.
На пример, ако је горња табела полазила од Ц1 у истој структури, све формуле испод су радиле добро.
= ВЛООКУП ($ Г2, $ Ц $ 2: $ Ф $ 14, ЦОЛУМН (Б1), 0)
= ВЛООКУП ($ Г2, $ Ц $ 2: $ Ф $ 14, ЦОЛУМН (Ц1) -1,0)
= ВЛООКУП ($ Г2, $ Ц $ 2: $ Ф $ 14, ЦОЛУМН (А1) +1,0)
8. ВЛООКУП за спајање података
У већини база података постоји један упит који се спаја са табелама, углавном се назива упит за придруживање, али МС Екцел нема ни једну базу података, па стога нема упит за придруживање. Али то не значи да не можемо да спојимо две табеле ако имамо неке заједничке колоне у две табеле.
Дакле, када добијете неке податке из два одељења исте компаније, желели бисте да их спојите како бисте анализирали те податке.
На пример, када добијете оцене ученика од наставника, а затим записник о похађању са административног одељења, желели бисте да видите који је ученик добио коју оцену и колики је његов проценат похађања, на једном месту.
Морамо их спојити у табели испод.
Ознаке, морамо да добијемо из табеле ознака, а присуство из података о посећености.
Напишите испод формуле у ћелији Д19 да бисте добили ознаке и превукли надоле.
= ВЛООКУП (Б19, $ Б $ 2: $ Д $ 15,3,0)
Напишите испод формуле у ћелију Е19
= ВЛООКУП (Б19, $ Г $ 3: $ И $ 15,3,0)
Спојили смо две табеле у једну табелу. Наравно, можете додати више од две табеле. Потребно је само да добијете податке из свих табела које желите на једном месту помоћу ВЛООКУП -а.
Најбоље праксе ВЛООКУП -а
Дакле, сви горе наведени примери само смо користили влоокуп са сировим подацима. У свим горе наведеним примерима били смо опрезни у погледу података које смо користили. Морали смо бити опрезни при давању референци на низове табела. Али постоје одређени начини који могу значајно смањити овај напор.
9. Користите апсолутне референце са ВЛООКУП -ом
Можда сте приметили да сам у неким чланцима користио апсолутне опсеге (знак $ ранге). Па, апсолутни беси се користе када не желимо да се опсег мења приликом копирања ћелије формуле у друге ћелије.
На пример, ако имам = ВЛООКУП (Г2,Б2: Е14, 2,0), 0) у ћелији Х2, а ако је копирам или превучем надоле у ћелију Х3 формула ће се променити у = ВЛООКУП (Г3,Б3: Е15, 2,0). Све референце се релативно мењају. Овде бисмо можда желели да променимо референцу вредности претраживања, али не и низ табела. Зато што Б2 излази или се налази у распону и можда га желимо имати увек у низовима табела.
Дакле, да бисмо то спречили, користимо апсолутне референце. Чинимо те опсеге апсолутним које не желимо да мењамо, попут табле_арраи у Х2, = ВЛООКУП (Г2,$ Б $ 2: $ Е $ 14, 2,0), 0). Када копирате Х2 у Х3 формула ће бити = ВЛООКУП (Г3,$ Б $ 2: $ Е $ 14, 2,0), 0). Имајте на уму да се вредност прегледавања мења, али не и табле_арраи.
10. Користите именоване опсеге са ВЛООКУП -ом
У горњем примеру, користили смо апсолутно референцирање за исправљање табле_арраи. Прилично је нејасан и тешко га је читати. Шта ако можеш само да пишеш „Подаци“ на месту $ Б $ 2: $ Е $ 14 и то би се односило на $ Б $ 2: $ Е $ 14 увек. То ће учинити још лакшим читање и писање формуле.
Зато само изаберите опсег и дајте му име „подаци“.
Да бисте именовали опсег, изаберите опсег. Десним тастером миша кликните на Дефине Наме и именујте распон који желите. Сада само = ВЛООКУП (Г3,података,2,0), 0) формула ће радити потпуно исто као и раније.
Више о именованим опсезима можете прочитати у Екцелу. Овде сам објаснио сваки аспект именованог опсега.
11. Замјенски знакови за дјеломично подударање помоћу ВЛООКУП -а
ВЛООКУП дозвољава делимично подударање. Да, док радите потпуно подударање ВЛООКУП -а, можете користити замјенске операторе за дјеломично подударање. На пример, ако желите да потражите вредност која почиње са „Гил“, можете користити џокер оператор * са „Гил“ као „Гил *“. Погледајмо пример.
Овде желим да добијем оцене ученика чије име почиње са Гил. Да бих то постигао, написаћу формулу испод.
= ВЛООКУП ("*Шкрга", Ц2: Д14,2,0)
Постоје два џокера који су доступни у екцелу за употребу са функцијом ВЛООКУП.
Звјездица (*) замјенски знак. Ово се користи када корисник не зна број знакова који постоје и зна само неке знакове подударања. На пример, ако корисник зна име које почиње са „См“, тада ће написати „См*“. Ако корисник зна да вредност претраживања која се завршава са „123“, он ће написати „*123“. (вилд цард ради само са текстовима). А он зна да се "се" налази унутар текста, па ће написати "*се*".
Знак питања ("?"). Ово се користи када корисник зна број недостајућих знакова. На пример, ако желим да потражим вредност која има 4 знака, али не знам шта су, једноставно ћу написати „????“ на месту вредности тражења.
Влоокуп ће вратити одговарајућу вредност прве пронађене вредности која има тачно четири знака.
Важно: Џокер картица ради само са текстуалним вредностима. Претворите бројеве у текст ако желите да користите џокер.
Слабости функције ВЛООКУП
ВЛООКУП је заиста моћна и лака функција за дохваћање вредности, али постоје многа подручја у којима ВЛООКУП једноставно није од велике помоћи. Ако радите на Екцелу, морате знати и њих и како да обавите задатак који ВЛООКУП не може да уради.
12. Није могуће дохватити вредност с леве стране табеле_Арраи
Највећа мана функције ВЛООКУП је та што не може дохватити вредност са леве стране поља табеле. ВЛООКУП тражи само десно од вредности тражења.
То је зато што ВЛООКУП тражи дато вредност тражења у првој колони датог таблични низ. Никада неће вратити вредност изван табеле. А ако покушате да задржите леву колону у низу табела, она постаје прва колона низа табела, па ће се у том опсегу тражити вредност за претраживање. Што ће вероватно генерисати грешку.
За тражење вредности са леве стране тражене вредности уместо тога користимо ИНДЕКС-МАТЦХ. Функција ИНДЕКС-МАТЦХ може тражити вриједности из било које колоне на листу. Заправо, опсег тражења и опсег из којег желите да тражите вредности су потпуно независни, што чини подударање индекса високо прилагодљивим.
ВЛООКУП враћа #Н/А чак и када постоји вредност претраживања.
#Н/А Грешка се враћа функцијом ВЛООКУП када вредност претраживања није пронађена. Можда ће вам бити досадно ако вредност постоји у опсегу, али ВЛООКУП враћа грешку #Н/А.
14. Када су бројеви обликовани као текст
То се углавном дешава када се бројеви обликују као текст. Када нађете помоћу ЦТРЛ+Ф команда ће је Екцел пронаћи, али када покушате да користите ВЛООКУП, враћа #Н/А. Овај задатак се у већини интервјуа даје као трик питања.
На горњој слици можете видети да је 101 тачно тамо, али формула враћа грешку. Како то подносите? Па, постоје две методе.
1. Претворите текст у број у подацима
Ову зелену ознаку можете видети у горњем левом углу у роллно колони. Они указују да је нешто необично у вредности ћелије. Када одаберете ћелију, она ће показати да је број обликован као текст.
Када кликнете на ускличник (!), Приказаће вам се опција Претвори у број. Одаберите све ћелије и кликните на ову опцију. Све вредности ће се претворити у текст.
2. Претворите број за претраживање у текст у формули
У горњем примеру смо мутирали оригиналне податке. Можда нећете желети да мењате ништа у оригиналним подацима. Дакле, уместо тога бисте желели да то урадите у формули. Да бисте променили број у текст у формули ВЛООКУП, напишите ово.
= ВЛООКУП (ТЕКСТ (Г2, "0"), Б2: Д14,2,0)
Овде формула динамички мења број у текст, а затим га усклађује са датим опсегом.
15. Текст који има водећи и задњи размак
Неки подаци које пружају оператери за унос података и подаци са Интернета нису чисти. Могу имати завршне размаке или неке неисписиве знакове. Приликом претраживања помоћу ових вредности, можда ћете видети грешку #Н/А. Да бисте то избегли, прво очистите податке. За уклањање водећих размака користите функцију ТРИМ. ТРИМ уклања неограничен број почетних или задњих размака из текста.
Дакле, предлажем да додате нову колону и тамо унесете очишћене податке. Затим налепите вредност. Сада, ако желите, можете се ослободити оригиналне колоне.
= ТРИМ (ЦЛЕАН (текст))
Руковање грешкама ВЛООКУП -а
Као што знате, ВЛООКУП не успева да пронађе вредност, враћа грешку #Н/А. Па, у реду је добити грешку #Н/А, можда понекад намеравате да је добијете, на пример када желите да проверите да ли вредност већ постоји на листи или не. Тамо #Н/А означава да вредност не постоји.
Али #Н/А изгледа ружно. Како би било да добијете излаз прилагођен кориснику, попут „Ид нот фоунд“ или „Цустомер нот Фоунд“. Можемо користити ИФЕРРОР са функцијом ВЛООКУП да избегнемо #Н/А.
Користите доњу формулу за хватање грешке у ВЛООКУП -у.
= ИФЕРРОР (ВЛООКУП (150, Б2: Е14,2,0), "Ролл но нот фоунд")
16. ВЛООКУП са ВИШЕ Критеријума
ВЛООКУП не може да ради са више критеријума.Да, можете имати само један критеријум за тражење вредности помоћу формуле ВЛООКУП.
На пример, ако имате име и презиме у две одвојене колоне.
А сада ако желите да потражите вредност чије је име Јохн и Презиме Даве, онда то не можете учинити нормално.
Али за то постоји решење. Можете створити засебну колону повезивањем имена и презимена, а затим потражите ту колону с именом и презименом.
Тамо је ИНДЕКС-МАТЦХ алтернатива која може да тражи више критеријума без икакве колоне помоћи.
17. ВЛООКУП Дохваћа само прву пронађену вриједност
Замислите да имате неке податке у централном региону. Сада желите да добијете првих 5 записа података централног региона. ВЛООКУП ће вратити само прву централну вредност у свих пет записа. Ово је главна позадина.
Али то не значи да то не можемо постићи. Можемо користити сложену формулу низа као што је доле.
= ИНДЕКС ($ Ц $ 2: $ Ц $ 14, СМАЛЛ (ИФ (Г2 = $ А $ 2: $ А $ 14, РОВ ($ А $ 2: $ А $ 14) -РОВ ($ А $ 2) +1), РОВ (1: 1)))
Напишите ову формулу и притисните ЦТРЛ+СХИФТ+ЕНТЕР.
И готово је.
Објаснио сам у чланку како да потражите више вредности у Екцелу.
Па да, момци, у овом чланку сам обрадио све могуће аспекте функције ВЛООКУП према мом знању. Ако мислите да сам нешто пропустио, молимо вас да ме обавестите у одељку за коментаре испод.
Влоокуп Топ 5 вредности са дупликатом вредности помоћу ИНДЕКС-МАТЦХ у Екцелу
ВЛООКУП Више вредности
ВЛООКУП са Динамиц Цол Индек
Делимично подударање са функцијом ВЛООКУП
Користите ВЛООКУП из две или више табела за претраживање
Поглед према датуму у Екцелу
Коришћење ВЛООКУП формуле за проверу да ли вредност постоји
Како ВЛООКУП из различитих Екцел листова
ВЛООКУП са бројевима и текстом
ИФ, ИСНА и ВЛООКУП функције
ИСНА и ВЛООКУП функција
ИФЕРРОР и ВЛООКУП функција