Екцел КСЛООКУП функција: Све што треба да знате (10 примера)

Гледајте видео - Екцел КСЛООКУП функција (10 примера КСЛООКУП)

Екцел КСЛООКУП функција коначно је стигао.

Ако сте користили ВЛООКУП или ИНДЕКС/МАТЦХ, сигуран сам да ће вам се свидети флексибилност коју пружа функција КСЛООКУП.

У овом водичу ћу покрити све што треба знати о функцији КСЛООКУП и неке примере који ће вам помоћи да знате како да је најбоље користите.

Па да почнемо!

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

КСЛООКУП је нова функција Оффице 365 и нова је и побољшана верзија функције ВЛООКУП/ХЛООКУП.

Ради све што је ВЛООКУП радио, и много више.

КСЛООКУП је функција која вам омогућава да брзо потражите вредност у скупу података (вертикално или хоризонтално) и вратите одговарајућу вредност у неки други ред/колону.

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

Моћ ове функције ће постати још јаснија како дубоко зароним у неке Примери КСЛООКУП -а касније у овом водичу.

Али пре него што уђем у примере, поставља се велико питање - како да добијем приступ КСЛООКУП -у?

Како приступити КСЛООКУП -у?

До сада је КСЛООКУП доступан само за кориснике система Оффице 365.

Дакле, ако користите претходне верзије програма Екцел (2010/2013/2016/2019), нећете моћи да користите ову функцију.

Такође нисам сигуран да ли ће ово икада бити објављено за претходне верзије или не (можда Мицрософт може да направи додатак на начин на који су то учинили за Повер Куери). Али од сада ћете га моћи користити само ако сте у систему Оффице 365.

Кликните овде за надоградњу на Оффице 365

У случају да сте већ у систему Оффице 365 (кућно, лично или универзитетско издање) и немате приступ њему, идите на картицу Датотека, а затим кликните на налог.

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

Очекујем да ће КСЛООКУП ускоро бити доступан на свим верзијама Оффице 365.

Напомена: КСЛООКУП је доступан и за Оффице 365 за Мац и Екцел за веб (Екцел на мрежи)

Синтакса функције КСЛООКУП

Испод је синтакса функције КСЛООКУП:

= КСЛООКУП (лоокуп_валуе, лоокуп_арраи, ретурн_арраи, [иф_нот_фоунд], [матцх_моде], [сеарцх_моде])

Ако сте користили ВЛООКУП, приметићете да је синтакса прилично слична, са неким сјајним додатним функцијама, наравно.

Не брините ако синтакса и аргумент изгледају превише. Покривам их неким лаким КСЛООКУП примерима касније у овом водичу који ће то учинити кристално јасним.

КСЛООКУП функција може испричати 6 аргумената (3 обавезна и 3 опционална):

1. лоокуп_валуе - вредност коју тражите
2. лоокуп_арраи - низ у којем тражите вредност претраживања
3. ретурн_арраи - низ из којег желите да преузмете и вратите вредност (што одговара позицији на којој се налази вредност за претраживање)
4. [иф_нот_фоунд] - вредност коју треба вратити у случају да вредност за проналажење није пронађена. У случају да не наведете овај аргумент, вратиће се грешка #Н/А
5. [матцх_моде] - Овде можете одредити врсту подударања коју желите:
   • 0 - Потпуно подударање, где би вредност лоокуп_валуе требало да се потпуно подудара са вредношћу у низу лоокуп_арраи. Ово је подразумевана опција.
   • -1 - Тражи потпуно подударање, али ако се пронађе, враћа следећу мању ставку/вредност
   • 1 - Тражи потпуно подударање, али ако се пронађе, враћа следећу већу ставку/вредност
   • 2 - Да бисте извршили делимично подударање помоћу џокера (* или ~)
6. [начин_претраживања] - Овде наводите како функција КСЛООКУП треба да претражује низ лоокуп_арраи
   • 1 - Ово је подразумевана опција где функција почиње да тражи лоокуп_валуе од врха (прва ставка) до дна (последња ставка) у лоокуп_арраи -у
   • -1 - Врши ли претрагу одоздо према горе. Корисно када желите да пронађете последњу одговарајућу вредност у лоокуп_арраи
   • 2 - Обавља бинарно претраживање где је потребно сортирање података по растућем редоследу. Ако се не сортира, то може дати грешке или погрешне резултате
   • -2 - Обавља бинарно претраживање где се подаци морају сортирати по опадајућем редоследу. Ако се не сортира, то може дати грешке или погрешне резултате

Примери функција КСЛООКУП

Пређимо сада на занимљив део - неколико практичних примера КСЛООКУП -а.

Ови примери ће вам помоћи да боље разумете како КСЛООКУП ради, како се разликује од ВЛООКУП -а и ИНДЕКС/МАТЦХ -а и неких побољшања и ограничења ове функције.

Кликните овде да преузмете датотеку примера и пратите је

Пример 1: Дохватите вредност за претраживање

Претпоставимо да имате следећи скуп података и желите да преузмете математички резултат за Грега (вредност претраживања).

Испод је формула која то ради:

= КСЛООКУП (Ф2, А2: А15, Б2: Б15)

У горњој формули, управо сам користио обавезне аргументе где тражи име (од врха до дна), проналази потпуно подударање и враћа одговарајућу вредност из Б2: Б15.

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

Једна тренутна корист од лоокуп_арраи и ретурн_арраи као засебних аргумената значи да сада можете погледај лево. ВЛООКУП је имао ово ограничење где можете само да погледате горе и пронађете вредност која је са десне стране. Али са КСЛООКУП -ом, то ограничење је нестало.

Ево примера. Имам исти скуп података, где је име са десне стране, а ретурн_ранге са леве стране.

Испод је формула коју могу користити да добијем резултат за Грега у математици (што значи погледати лево од лоокуп_валуе)

= КСЛООКУП (Ф2, Д2: Д15, А2: А15)

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

Пример 2: Потражите и преузмите цео запис

Узмимо исте податке за пример.

У овом случају, не желим само да преузмем Грегов резултат у математици. Желим да добијем бодове из свих предмета.

У овом случају могу користити следећу формулу:

= КСЛООКУП (Ф2, А2: А15, Б2: Д15)

Горња формула користи опсег ретурн_арраи који је више од колоне (Б2: Д15). Дакле, када се вредност претраживања пронађе у А2: А15, формула враћа цео ред из ретурн_арраи.

Такође, не можете избрисати само ћелије које су део низа које су аутоматски попуњене. У овом примеру не можете избрисати Х2 или И2. Ако покушате, ништа се не би догодило. Ако одаберете ове ћелије, формула у траци са формулама ће бити засивљена (што значи да се не може променити)

Формулу можете избрисати у ћелији Г2 (где смо је првобитно унели), она ће избрисати цео резултат.

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

Пример 3: Двосмерно тражење помоћу КСЛООКУП -а (хоризонтално и вертикално тражење)

Испод је скуп података где желим да знам резултат Грега из математике (субјект у ћелији Г2).

То се може урадити коришћењем двосмерног претраживања где тражим име у колони А и назив предмета у реду 1. Предност овог двосмерног тражења је што је резултат независан од имена ученика имена предмета. Ако променим назив предмета у Хемија, ова двосмерна КСЛООКУП формула би и даље радила и дала би ми тачан резултат.

Испод је формула која ће извршити двосмерно тражење и дати тачан резултат:

= КСЛООКУП (Г1, Б1: Д1, КСЛООКУП (Ф2, А2: А15, Б2: Д15))

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

Дакле, резултат КСЛООКУП -а (Ф2, А2: А15, Б2: Д15) је {21,94,81}, што је низ оцена које је Грег постигао у овом случају.

Ово се затим поново користи у спољној формули КСЛООКУП као повратни низ. У спољној формули КСЛООКУП тражим назив предмета (који се налази у ћелији Г1), а низ за претраживање је Б1: Д1.

Ако је назив предмета Матх, ова спољна КСЛООКУП формула дохваћа прву вредност из повратног низа - што је {21,94,81} у овом примеру.

Ово чини исто што је до сада постигнуто коришћењем комбинације ИНДЕКС и МАТЦХ

Кликните овде да преузмете датотеку примера и пратите је

Пример 4: Када вредност тражења није пронађена (руковање грешкама)

Обрада грешака је сада додата формули КСЛООКУП.

Четврти аргумент у функцији КСЛООКУП је [иф_нот_фоунд], где можете одредити шта желите у случају да се тражење не може пронаћи.

Претпоставимо да имате скуп података као што је приказано испод где желите да добијете резултат из математике у случају да се подудара, а у случају да име није пронађено, желите да се вратите - „Није се појавило“

Формула испод ће то учинити:

= КСЛООКУП (Ф2, А2: А15, Б2: Б15, "Није се појавио")

У овом случају, ја сам тешко кодирао шта желим да добијем у случају да нема подударања. Такође можете да користите ћелијску референцу на ћелију или формулу.

Пример 5: Уметнути КСЛООКУП (претраживање у више опсега)

Генијалност аргумента [иф_нот_фоунд] је у томе што вам омогућава да га користите угнежђена КСЛООКУП формула.

На пример, претпоставимо да имате две одвојене листе као што је приказано испод. Док ове две табеле имам на истом листу, можете их ставити у засебне листове или чак радне свеске.

Испод је угнежђена КСЛООКУП формула која ће проверити име у обе табеле и вратити одговарајућу вредност из наведене колоне.

= КСЛООКУП (А12, А2: А8, Б2: Б8, КСЛООКУП (А12, Ф2: Ф8, Г2: Г8))

У горњој формули сам користио аргумент [иф_нот_фоунд] да бих користио другу формулу КСЛООКУП. Ово вам омогућава да додате други КСЛООКУП у исту формулу и скенирате две табеле са једном формулом.

Нисам сигуран колико угнежђених КСЛООКУП -ова можете користити у формули. Покушао сам до 10 и успело је, а онда сам одустао 🙂

Пример 6: Пронађите последњу одговарајућу вредност

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

Претпоставимо да имате скуп података као што је приказано у наставку и желите да проверите када је последња особа запослена у сваком одељењу и који је датум запошљавања.

Формула испод тражи последњу вредност сваког одељења и даје назив последњег запосленог:

= КСЛООКУП (Ф1, $ Б $ 2: $ Б $ 15, $ А $ 2: $ А $ 15 ,,,-1)

И доња формула ће дати датум последњег запошљавања за свако одељење:

= КСЛООКУП (Ф1, $ Б $ 2: $ Б $ 15, $ Ц $ 2: $ Ц $ 15 ,,,-1)

Будући да КСЛООКУП има уграђену функцију за одређивање смера претраживања (од првог до последњег или од последњег до првог), то се ради помоћу једноставне формуле. Са вертикалним подацима, ВЛООКУП и ИНДЕКС/МАТЦХ увек гледају одозго надоле, али са КСЛООКУП -ом и могу одредити смер и одоздо према горе.

Пример 7: Приближно подударање са КСЛООКУП -ом (Пронађи пореску стопу)

Још једно значајно побољшање са КСЛООКУП -ом је то што сада постоје четири начина подударања (ВЛООКУП има 2, а МАТЦХ 3).

Можете да наведете било који од четири аргумента да бисте одлучили како треба пронаћи вредност претраживања:

• 0 - Потпуно подударање, где би вредност лоокуп_валуе требало да се потпуно подудара са вредношћу у низу лоокуп_арраи. Ово је подразумевана опција.
• -1 - Тражи потпуно подударање, али ако се пронађе, враћа следећу мању ставку/вредност
• 1 - Тражи потпуно подударање, али ако се пронађе, враћа следећу већу ставку/вредност
• 2 - Да бисте извршили делимично подударање помоћу џокера (* или ~)

Али најбољи део је што не морате да бринете да ли су ваши подаци сортирани узлазним или силазним редоследом. Чак и ако подаци нису сортирани, КСЛООКУП ће се побринути за то.

Испод имам скуп података у којем желим да пронађем провизију сваке особе - а провизију је потребно израчунати помоћу табеле са десне стране.

Испод је формула која ће то учинити:

= КСЛООКУП (Б2, $ Е $ 2: $ Е $ 6, $ Ф $ 2: $ Ф $ 6,0, -1)*Б2

Ово једноставно користи продајну вредност као тражење и гледа кроз табелу за претраживање са десне стране. У овој формули сам користио -1 као пети аргумент ([матцх_моде]), што значи да ће тражити потпуно подударање, а када га не пронађе, вратиће вредност само мању од вредности за претраживање .

И као што сам рекао, не морате да бринете да ли су ваши подаци сортирани.

Кликните овде да преузмете датотеку примера и пратите је

Пример 8: Хоризонтално тражење

КСЛООКУП може вршити вертикално и хоризонтално претраживање.

Испод имам скуп података где имам имена ученика и њихове резултате у редовима, и желим да преузмем резултат за име у ћелији Б7.

Формула испод ће то учинити:

= КСЛООКУП (Б7, Б1: О1, Б2: О2)

Ово није ништа друго до једноставно претраживање (слично ономе што смо видели у примеру 1), већ хоризонтално.

Сви примери које покривам о вертикалном тражењу могу се такође урадити са хоризонталним претраживањем помоћу КСЛООКУП -а (опроштај од ВЛООКУП -а и ХЛООКУП -а).

Пример 9: Условно тражење (коришћење КСЛООКУП -а са другим формулама)

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

Испод је скуп података где имам имена ученика и њихове оцене, и желим да знам име ученика који је постигао максимум из сваког предмета и број ученика који је постигао више од 80 у сваком предмету.

Испод је формула која ће дати име ученика са највећим оценама у сваком предмету:

= КСЛООКУП (МАКС (КСЛООКУП (Г1, $ Б $ 1: $ Д $ 1, $ Б $ 2: $ Д $ 15)), КСЛООКУП (Г1, $ Б $ 1: $ Д $ 1, $ Б $ 2: $ Д $ 15), $ А $ 2: $ А $ 15)

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

На пример, за математику, када користим КСЛООКУП (Г1, $ Б $ 1: $ Д $ 1, $ Б $ 2: $ Д $ 15), то ми даје све оцене из математике. Затим могу користити функцију МАКС да пронађем максимални резултат у овом опсегу.

Овај максимални резултат тада постаје моја вредност претраживања, а опсег претраживања би био низ који враћа КСЛООКУП (Г1, $ Б $ 1: $ Д $ 1, $ Б $ 2: $ Д $ 15)

Користим ово у оквиру друге КСЛООКУП формуле да дохватим име ученика који је постигао максималне оцене.

А да бисте избројали број ученика који су постигли више од 80, користите доњу формулу:

= ЦОУНТИФ (КСЛООКУП (Г1, $ Б $ 1: $ Д $ 1, $ Б $ 2: $ Д $ 15), "> 80")

Овај једноставно користи формулу КСЛООКУП да добије распон свих вредности за дати предмет. Затим га умотава у функцију ЦОУНТИФ да би добио број бодова који је већи од 80.

Пример 10: Коришћење џокер знакова у КСЛООКУП -у

Баш као што можете користити замјенске знакове у ВЛООКУП -у и МАТЦХ -у, то можете учинити и са КСЛООКУП -ом.

Али постоји разлика.

У КСЛООКУП -у морате да наведете да користите џокер знакове (у петом аргументу). Ако ово не наведете, КСЛООКУП ће вам дати грешку.

Испод је скуп података где имам називе компанија и њихову тржишну капитализацију.

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

Испод је формула која ће то учинити:

= КСЛООКУП ("*" & Д2 & "*", $ А $ 2: $ А $ 11, $ Б $ 2: $ Б $ 11,, 2)

У горњој формули, користио сам звјездицу (*) замјенски знак прије и након Д2 (мора бити унутар двоструких наводника и спојити са Д2 помоћу знака &).

Ово говори формули да прегледа све ћелије, а ако садржи реч у ћелији Д2 (која је Аппле), сматрајте је потпуном подударношћу. Без обзира колико и који знакови су пре и после текста у ћелији Д2.

А како би КСЛООКУП прихватио замјенске знакове, пети аргумент је постављен на 2 (подударање замјенских знакова).

Пример 11: Пронађите последњу вредност у колони

Пошто вам КСЛООКУП омогућава претраживање од дна до врха, можете лако пронаћи последњу вредност на листи, као и дохватити одговарајућу вредност из колоне.

Претпоставимо да имате скуп података као што је приказано у наставку и желите да знате која је последња компанија и колика је тржишна капитализација ове последње компаније.

Формула испод даје вам име последње компаније:

= КСЛООКУП ("*", А2: А11, А2: А11,, 2, -1)

И доња формула ће дати тржишну капитализацију последње компаније на листи:

= КСЛООКУП ("*", А2: А11, Б2: Б11,, 2, -1)

Ове формуле поново користе заменске знакове. У овим случајевима сам користио звездицу (*) као вредност претраживања, што значи да би ово прву ћелију на коју наиђе сматрало тачним подударањем (као што би звездица могла бити било који знак и било који број знакова).

А пошто је смер одоздо према горе (за вертикално поређане податке), вратиће последњу вредност на листи.

А друга формула од тада користи засебни ретурн_ранге за добијање тржишне капитализације презимена на листи.

Кликните овде да преузмете датотеку примера и пратите је

Шта ако немате КСЛООКУП?

Будући да ће КСЛООКУП вероватно бити доступан само корисницима Оффице 365, један од начина да га добијете је надоградња на Оффице 365.

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

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

У случају да имате друге претплате на Оффице 365 (као што је Ентерприсе), сигуран сам да ће КСЛООКУП и друге сјајне функције (попут динамичких низова, формула као што су СОРТ и ФИЛТЕР) ускоро постати доступне.

У случају да користите Екцел 2010/2013/2016/2019, нећете имати КСЛООКУП, па ћете морати да наставите да користите комбинацију ВЛООКУП, ХЛООКУП и ИНДЕКС/МАТЦХ да бисте најбоље искористили формуле за тражење.

Компатибилност са КСЛООКУП -ом уназад

Ово је једна ствар око које морате бити опрезни - КСЛООКУП је НИСУ компатибилни уназад.

То значи да ће, ако креирате датотеку и користити формулу КСЛООКУП, а затим је отворити у верзији која нема КСЛООКУП, приказати грешке.

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

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

Надам се да вам је овај водич био користан!