Како користити функцију КСЛООКУП у програму Екцел 2016

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

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

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

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

КСЛООКУП( вредност_проналажења , низ_потраживања , повратни_низ ,[ режим_подударања ],[ режим_претраге ])

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

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

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

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

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

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

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

Поставите курсор ћелије у ћелију Е4 радног листа

Кликните на опцију Лоокуп & Референце на картици Формуле, а затим на КСЛООКУП при дну падајућег менија да бисте отворили дијалог Аргументи функције.

Кликните на ћелију Д4 на радном листу да бисте унели њену референцу ћелије у оквир за текст аргумента Лоокуп_валуе.

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

Притисните Таб да бисте изабрали поље за текст аргумента Ретурн_арраи, а затим кликните на ћелију Б4 и држите Схифт док притискате Цтрл-стрелицу надоле да бисте изабрали Б4:Б8 као опсег који садржи вредности које треба да се врате на основу резултата претраге (који се појављује као Табела1[Укупна продаја] у оквиру за текст).

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

Креирање формуле са КСЛООКУП-ом у ћелији Е4 која враћа продају на основу земље унете у ћелију Д4.

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

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

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

Креирање формуле са КСЛООКУП у ћелији Д4 која враћа земљу на основу продаје унесене у ћелију Е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 и Екцел Онлине . То значи да ако делите радну свеску која садржи КСЛООКУП формуле са сарадницима или клијентима који користе верзију Екцел-а која не укључује ову нову функцију тражења, све ове формуле ће вратити #НАМЕ? вредности грешака када отворе радни лист.