1. СофтвареМицрософт ОффицеЕкцелКако се користи КСЛООКУП функција у Екцелу 2016

Грег Харвеи

Екцел 2016 за претплатнике Оффице 365 на Виндовс и Мац сада подржава нову КСЛООКУП функцију, која се рекламира као знатно једноставнија и свестранија замена за веома популарну (још увек малверисану) вертикалну претрагу, ВЛООКУП (не знам шта је Кс у КСЛООКУП значи: еКстенсиве, можда?).

За оне који још нису упознати са ВЛООКУП-ом (који се сматра трећом најчешће кориштеном функцијом одмах после СУМ-а и АВЕРАГЕ-а), ова функција претражује вертикално по редовима у крајњем левом колону означене табеле за проналажење одоздо према горе док не нађе вредност у ступац за претраживање означен бројем компензације који одговара или премашује онај који тражите. Иако је изузетно корисно за лоцирање одређених ставки на дугом попису или ступцу података у вашој радној листи, ВЛООКУП функција има неколико ограничења која ова нова функција претраживања не дијели, као КСЛООКУП:

  • Неуспјешно проналази тачне подударности за вриједност претраживања у распону претраживања Може да претражује и вертикално (по редовима) и хоризонтално (по колони) у табели, замењујући тако потребу за коришћењем ХЛООКУП функције при хоризонталном претраживању по колони Може да претражује лево или десно тако да распон претраживања у вашој претраживачкој табели не мора бити смештен у колони лево од оне која је означена као повратни опсег да би функција радила Када се користи задана вредност тачног подударања, функционише чак и када се вредности у распону претраживања не сортирају по одређеном редоследу Може да претражује од доњег реда до врха у распону поља претраживања, користећи опционални аргумент начина претраживања

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

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

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

* Имајте на уму приликом одређивања аргумената лоокуп_арраи и ретурн_арраи у вашој КСЛООКУП функцији, оба распона морају бити једнаке дужине, у супротном Екцел ће вратити #ВАЛУЕ! грешка ваше формуле. Ово је још више разлог да користите називе распона или називе ступаца у одређеној табели података када дефинирате те аргументе, а не да их упућујете или куцате у њиховим референцама ћелија.

Опционални аргумент матцх_моде може садржати било коју од следеће четири вредности:

  • 0 за тачно подударање (задано, исто као када није одређен аргумент матцх_моде) -1 за тачно подударање или наредну мању вредност 1 за тачно подударање или наредну већу вредност 2 за делимично подударање кориштењем замјенских знакова придружених референци ћелије у аргументу лоокуп_валуе

Опционални аргумент сеарцх_моде може садржавати било коју од следеће четири вредности:

  • 1 да бисте претражили прво до последње, то јест одозго на дно (подразумевано, исто као када није назначен аргумент сеарцх_моде) -1 да претражите последње до прво, односно одоздо према горе 2 за бинарну претрагу у узлазном редоследу -2 за бинарну претрагу у силазном редоследу

Најбољи начин да схватите снагу и свестраност нове КСЛООКУП функције је да је видите у деловању у Екцеловом радном листу. На следећој слици имам радни лист са једноставном таблицом података о продаји за 2019. годину, поредану по земљама. Да бисте помоћу КСЛООКУП-а вратили укупну продају из ове табеле у ћелији Е4 на основу земље коју унесете у ћелију Д4 радног листа, подузмите ове кораке:

  1. Поставите курсор ћелије у ћелију Е4 радног листа Кликните на опцију Лоокуп & Референце на картици Формулас, а затим КСЛООКУП при дну падајућег менија да бисте отворили дијалог с Аргументима функција. Кликните ћелију Д4 на радном листу да бисте унијели референцу своје ћелије у текстуално поље аргумента Лоокуп_валуе. Притисните Таб за одабир текстуалног поља аргумента Лоокуп_арраи, затим кликните ћелију А4 и држите Схифт док притиснете стрелицу Цтрл надоле да бисте одабрали А4: А8 као опсег за претраживање (јер је распон А3: Б8 дефинисан као табела података Екцела, Табела1 [Земља] појављује се у пољу за текст уместо распона А4: А8). Притисните Таб за одабир текстуалног оквира с аргументима Ретурн_арраи, затим кликните ћелију Б4 и држите притиснуту типку Схифт док притиснете стрелицу Цтрл према доље да бисте одабрали Б4: Б8 као распон који садржи вриједности које се требају вратити на основу резултата претраживања (који се појављује као Табела1 [Укупна продаја] у текстном пољу).

Кликните ОК да унесете КСЛООКУП формулу у ћелију Е4.

Стварање КСЛООКУП формула

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

Будући да КСЛООКУП функционише с десна на лево исто као и са лева на десно, можете да користите ову функцију и да вратите земљу из ове табеле продаје на основу одређене вредности продаје. Следећа слика приказује вам како се то ради. Овог пута креирате КСЛООКУП формулу у ћелији Д4 и означите вредност која је унета у ћелију Е4 (у овом случају 11.000) као аргумент лоокуп_валуе.

Поред тога, унесите -1 као аргумент матцх_моде да надјачате задано тачно подударање функције, тако да Екцел враћа земљу са тачним подударањем са продајном вредности унесеном у ћелију за претрагу Е4 или ону са следећом нижом укупном продајом (Мексико са 10.000 УСД у овом случају јер у овој табели нема земље са 11.000 УСД укупне продаје). Без именовања аргумента матцх_моде за ову формулу, Екцел би вратио #НА као резултат, јер у овој табели продаје нема тачно подударање са 11.000 УСД.

КСЛООКУП формула у Д4

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

угнијежђене КСЛООКУП функције

У ћелији Б12 створио сам следећу формулу:

= КСЛООКУП (парт_лоокуп, $ А $ 3: $ А $ 6, КСЛООКУП (датум_лоокуп, $ Б $ 2: $ Ј $ 2, $ Б $ 3: $ Ј $ 6))

Ова формула почиње дефинисањем КСЛООКУП функције која вертикално тражи по редовима тачно подударање са уносом дела који је направљен у ћелији названој парт_лоокуп (у овом случају ћелија Б10) у распону ћелија $ А $ 3: $ А $ 6 производне табеле . Имајте на уму, међутим, да је аргумент ретурн_арраи за ову оригиналну ЛООКУП функцију сама по себи друга КСЛООКУП функција.

Ова друга, угнијежђена функција КСЛООКУП претражује распон ћелија $ Б $ 2: $ Ј $ 2 водоравно по колони за тачно подударање са уносом датума који је направљен у ћелији под називом дате_лоокуп (у овом случају ћелија Б11). Аргумент ретурн_арраи за ову секунду, угнијежђену КСЛООКУП функцију је $ Б $ 3: $ Ј $ 6, распон ћелија свих производних вриједности у табели.

Начин на који ова формула функционише је да Екцел прво израчуна резултат друге, угнијежђене КСЛООКУП функције вршећи хоризонталну претрагу која, у овом случају, враћа низ у распону ћелија Д3: Д6 ступца Јун-19 (са вриједностима: 438, 153, 306 и 779) као резултат тога. Овај резултат, заузврат, постаје аргумент ретурн_арраи за оригиналну КСЛООКУП функцију која врши вертикалну претрагу по реду за тачно подударање са уносом броја дела који је направљен у ћелији Б11 (назван парт_лоокуп). Будући да у овом примеру ова ћелија парт_лоокуп садржи АБ-102, формула враћа управо производну вредност Јун-19, 306, од резултата друге, следеће КСЛООКУП функције.

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

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