Екцел 2020: Дванаест предности КСЛООКУП-а - Екцел савети

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

  1. Подразумевано је тачно подударање.
  2. Трећи аргумент ВЛООКУП-а заснован на целобројним вредностима сада је одговарајућа референца.
  3. ИФНА је уграђена за руковање вредностима које недостају.
  4. КСЛООКУП нема проблема да крене лево.
  5. Пронађите следеће мање или следеће веће подударање без сортирања табеле.
  6. КСЛООКУП може да врши ХЛООКУП.
  7. Пронађите последње подударање претраживањем одоздо.
  8. Замењиви знакови су подразумевано „искључени“, али можете их поново укључити.
  9. Вратите свих 12 месеци у једној формули.
  10. Може да врати референцу на ћелију ако је КСЛООКУП поред двотачке као што је КСЛООКУП (); КСЛООКУП ()
  11. Може да направи двосмерну утакмицу као што то може ИНДЕКС (, МАТЦХ, МАТЦХ).
  12. Може сумирати сва претраживања у једној формули као што је ЛООКУП.

Ево синтаксе: = КСЛООКУП (Лоокуп_Валуе, Лоокуп_Арраи, Ресултс_Арраи, (иф_нот_фоунд), (матцх_моде), (сеарцх_моде)).

Предност КСЛООКУП 1: Подразумевано тачно подударање

99% мојих ВЛООКУП формула се завршава са, ФАЛСЕ или, 0 да укаже на тачно подударање. Ако увек користите верзију ВЛООКУП-а са тачним подударањем, можете започети искључивање матцх_моде функције КСЛООКУП.

На следећој слици тражите В25-6 из ћелије А4. Желите да потражите ту ставку у Л8: Л35. Када се пронађе, желите одговарајућу цену из колоне Н. Није потребно навести Фалсе као матцх_моде јер КСЛООКУП подразумевано подудара тачно.

КСЛООКУП вредност у А4. Погледајте Л8: Л35. Вратите одговарајућу цену из Н8: Н35.

Предност КСЛООКУП 2: Низ_резултата_ је референца уместо целог броја

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

Стари ВЛООКУП не би успео ако би неко убацио нову колону у табелу претраживања. КСЛООКУП наставља да ради.

КСЛООКУП Предност 3: ИФНА је уграђен као опциони аргумент

Ужасна грешка # Н / А се враћа када ваша вредност претраживања није пронађена у табели. У прошлости, да бисте заменили # Н / А нечим другим, морали бисте да користите ИФЕРРОР или ИФНА омотане око ВЛООКУП-а.

Када ставка није пронађена, враћа # Н / А са ВЛООКУП или КСЛООКУП …

Захваљујући предлогу Рика на мом ИоуТубе каналу, Екцел тим је уврстио необавезни четврти аргумент за иф_нот_фоунд. Ако желите да замените те # Н / А грешке нулом, једноставно додајте 0 као четврти аргумент. Или бисте могли да користите неки текст, на пример „Вредност није пронађена“.

Необавезни четврти аргумент у КСЛООКУП-у је "ако није пронађен". Тамо ставите 0 или „Није пронађено“.

КСЛООКУП Предност 4: Нема проблема при гледању лево од кључног поља

ВЛООКУП не може да гледа лево од поља кључа без прибегавања ВЛООКУП-у (А4, ЦХООСЕ ((1,2), Г7: Г34, Ф7: Ф34), 2, Фалсе). Са КСЛООКУП-ом нема проблема с постављањем низа резултата лево од низа Лоокуп_рачуна.

Са КСЛООКУП-ом нема проблема са враћањем категорије из колоне Ф док се траже бројеви делова у колони Г. То је увек била слабост ВЛООКУП-а: није могао да гледа лево.

КСЛООКУП Предност 5: Следеће мање или следеће веће подударање без сортирања

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

Пример верзије ВЛООКУП-а са приближним подударањем. Свака продаја од 10 хиљада до 20 хиљада добија бонус од 12 долара.

Али ВЛООКУП није имао могућност да врати тачно подударање или следећу већу ставку. Да бисте то урадили, морали сте да пређете на употребу МАТЦХ-а са -1 као матцх_моде и морали сте бити опрезни да би табела претраживања била сортирана силазно.

Опциони пети аргумент КСЛ_ОККУП-а матцх_моде може тражити само тачно подударање, једнако или само мање, једнако или само веће. Имајте на уму да вредности у КСЛООКУП-у имају више смисла него у МАТЦХ-у:

  • -1 проналази вредност једнаку или само мању
  • 0 наћи тачно подударање
  • 1 проналази вредност једнаку или само већу.

Али, најневероватнији део: табела за преглед не мора да се сортира и било који матцх_моде ће радити.

Испод, матцх_моде од -1 пронађите следећу мању ставку.

Пети аргумент КСЛООКУП-а је Матцх_Моде. 0 је за тачно подударање. Негативни се користи за Тачно подударање или Следећи мањи предмет. Позитивна 1 је за тачно подударање или следећу већу ставку. 2 је за Вилдцард Матцх. Да бисте пресликали шта би урадио ВЛООКУП са Труе у четвртом аргументу, ставите негативни као аргумент матцх_моде у КСЛООКУП.

Овде, матцх_моде оф 1, проналази које је возило потребно у зависности од броја људи у забави. Имајте на уму да табела за претрагу није сортирана према путницима и да се име возила налази лево од тастера.

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

Табела каже:

  • Аутобус прима 64 особе
  • Аутомобил има 4 особе
  • Мотоцикл има 1 особу
  • Тоур Ван има 12 људи
  • У комби је смештено 6 људи.

Као бонус, подаци се сортирају према возилу (у старом решењу, користећи МАТЦХ, табела би морала да се сортира силазно према капацитету. Такође: Возило је лево од капацитета.

КСЛООКУП Предност 6: Бочно КСЛООКУП замењује ХЛООКУП

Лоокуп_арраи и ресултс_арраи могу бити хоризонтални са КСЛООКУП-ом, што олакшава замену ХЛООКУП-а.

Овде је табела за претраживање хоризонтална. Раније би за ово био потребан ХЛООКУП, али КСЛООКУП може да се носи са столом који иде бочно.

КСЛООКУП Бенефит 7: Потражите најновији меч одоздо

На ИоуТубеу имам стари видео како одговарам на питање са британске фарме коња. Имали су возни парк. Сваки пут када би возило ушло по гориво или сервис, они су евидентирали возило, датум и километражу у табелу. Желели су да пронађу најновије познате километраже за свако возило. Иако би МАКСИФС из ере Екцел 2017 могао ово решити данас, решење пре много година била је тајна формула која је користила ЛООКУП и подразумевала је поделу са нулом.

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

Пронађите последње подударање на листи.

Белешка

Иако је ово велико побољшање, омогућава вам само да пронађете први или последњи меч. Неки људи су се надали да ће вам ово омогућити да пронађете друго или треће подударање, али то није намера аргумента сеарцх_моде.

Опрез

Горња слика показује да постоје режими претраживања који користе стару бинарну претрагу. Јое МцДаид не саветује употребу ових. Прво, побољшани алгоритам претраживања из 2018. године је довољно брз да нема значајне користи од брзине. Друго, ризикујете да несавесни сарадник сортира табелу претраживања и уведе погрешне одговоре.

Предност 8: КСЛООКУП: Замењиви знакови су подразумевано „искључени“

Већина људи није схватила да ВЛООКУП третира звездицу, знак питања и тилду као џокер знакове као што је описано у „# 51 Употреба џокер знака у ВЛООКУП-у“ на страници 143. Код КСЛООКУП-а, џокери су подразумевано искључени. Ако желите да КСЛООКУП третира ове знакове као џокер знакове, користите 2 као Матцх_Моде.

Веома је мало људи схватило да ВЛООКУП звездице у вредности претраживања третира као џокер знак. КСЛООКУП подразумевано не користи џокер знакове, али можете га присилити да се понаша као ВЛООКУП ако користите начин подударања 2: Подударање знакова џокер знакова.

КСЛООКУП Погодност 9: Вратите свих 12 месеци у једну формулу!

Ово је заиста предност Динамиц Арраис-а, али то је мој омиљени разлог што волим КСЛООКУП. Када у претраживању морате да вратите свих 12 месеци, једна формула унета у Б6 са правоугаоним низом ретурн_арраи вратиће више резултата. Ти резултати ће се прелити у суседне ћелије.

На доњој слици, једна формула унета у Б7 враћа свих 12 одговора приказаних у Б7: М7.

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

Предност КСЛООКУП 10: Може да врати референцу на ћелију ако је у близини дебелог црева

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

Размотрите следећу слику. Неко одабере Цхерри у Е4 и Фиг у Е5. Желите формулу која ће сумирати све од Б6 до Б9.

На слици су приказане две КСЛООКУП формуле у две ћелије. Први враћа 15 из ћелије Б6. Друго понављање 30 из Б9. Али онда у трећој ћелији постоји формула која спаја две КСЛООКУП формуле двотачком и затим умотава у функцију СУМ. Резултат је ЗБИР Б6: Б9, јер КСЛООКУП може вратити референцу на ћелију ако се функција појави поред оператора као што је двотачка. Да би доказали да ово делује, следећих неколико слика ће приказати ову формулу у дијалогу Процена формуле.

На горњој слици можете видети да ће КСЛООКУП Е4 вратити 15 из ћелије Б6. КСЛООКУП Е5 вратиће 30 из Б9. Међутим, ако узмете две КСЛООКУП функције из ћелија Д9 и Д10 и сложите их са двотачком између њих, понашање КСЛООКУП-а се мења. Уместо да врати 15, први КСЛООКУП враћа адресу ћелије Б6!

Да бих то доказао, изабрао сам Д7 и користио сам Формуле, процени формулу. Након два пута притиска на Евалуате, следећи део који треба израчунати је КСЛООКУП („Трешња“, А4: А29, Б4: Б29), као што је овде приказано.

Ово приказује дијалог Евалуате Формула непосредно пре процене првог КСЛООКУП-а. Овај КСЛООКУП се појављује непосредно пре дебелог црева.

Притисните Евалуате поново и запањујуће, КСЛООКУП формула враћа $ Б $ 6 уместо 15 сачуваних у Б6. То се дешава јер одмах иза ове КСЛООКУП формуле постоји дебело црево.

Притисните Евалуате и први КСЛООКУП враћа $ Б $ 6 уместо 15.

Притисните Евалуате још два пута, а привремена формула ће бити = СУМ (Б6: Б9).

Након процене другог КСЛООКУП-а, привремена формула је = СУМ (Б6: Б9).

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

  • Дебело црево
  • Простор (оператер раскрснице)
  • Зарез (оператер Уније)

Предност КСЛООКУП-а 11: Двосмерни меч попут ИНДЕКСА (, УТАКМИЦА, УТАКМИЦА)

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

За двосмерно подударање желите да пронађете који ред садржи број рачуна А621 приказан у Ј3. Дакле, КСЛООКУП започиње довољно лако: = КСЛООКУП (Ј3, А5: А15. Али онда морате навести низ резултата_. Можете користити исти трик као у КСЛООКУП Бенефит 9: Вратите свих 12 месеци у једну формулу горе, али користите га за враћање вертикалног вектора. Унутрашњи КСЛООКУП тражи месец Ј4 у заглављима месеца у Б4: Г4. Поље_врата је одређено као Б5: Г15. Резултат је да унутрашњи КСЛООКУП враћа низ као што је приказано у И10 : И20 доле. Будући да се А621 налази у петој ћелији низа лоокуп_арраи, а 104 налази се у петој ћелији низа ресултс_арраи, тачан одговор добијате из формуле. Испод Ј6 показује стари начин. Ј7 враћа нови начин.

КСЛоокуп Ј3 на листи рачуна у А5: А15. За низ резултата користите КСЛООКУП (Ј4, Б4: Г4, Б5: Г15). У овој формули, Б4: Г4 је списак месеци. Б5: Г15 је правоугаони низ вредности за све рачуне за све месеце. У другој ћелији, само унутрашњи КСЛООКУП показује како враћа целу колону вредности за мај.

Предност КСЛООКУП 12: Зброј свих вредности претраживања у једну формулу

Древна функција ЛООКУП нудила је два чудна трика. Прво, ако покушавате да утврдите укупан износ додатних трошкова који се могу акумулирати, можете затражити да ЛООКУП тражи све вредности у једној формули. На слици испод ЛООКУП (Ц4: Ц14 врши 11 претраживања. Али функција ЛООКУП није понудила тачно подударање и захтевала је сортирање табеле за претраживање.

Потражите 13 вредности и збројите их. Ово је некада радило са ЛООКУП-ом, али такође ради и са КСЛООКУП-ом. Наведите све вредности претраживања Ц4: Ц14 као први аргумент. Умотајте КСЛООКУП у функцију СУМ.

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

Трик коришћења ЛООКУП-а за сумирање свих резултата претраживања успео је само са приближном верзијом претраживања која се подудара. Овде КСЛООКУП тачно подудара сва имена у Л4: Л14 и добија укупно све резултате.

Бонус савет: Шта је са Твистед ЛООКУП?

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

Овде је претраживачки низ вертикалан, а низ резултата хоризонтални. Стара функција ЛООКУП то може да поднесе, али да бисте то урадили са КСЛООКУП-ом, морате да умотате било који низ у ТРАНСПОСЕ.

Занимљиви Чланци...