змест
Гэты невялікі падручнік тлумачыць, як стварыць функцыю ВПР (VLOOKUP) з улікам рэгістра, паказвае некалькі іншых формул, у якіх Excel можа шукаць з улікам рэгістра, і паказвае на моцныя і слабыя бакі кожнай функцыі.
Мяркую, кожны карыстальнік Excel ведае, якая функцыя выконвае вертыкальны пошук. Правільна, гэта функцыя ВПР. Аднак пра гэта мала хто ведае ВПР не адчувальны да рэгістра, г.зн. ніжнія і верхнія літары для яго ідэнтычныя.
Вось кароткі прыклад, які дэманструе няздольнасць ВПР распазнаць рэестр. Дапусцім, у камеру A1 змяшчае значэнне «рахунак» і вочка A2 – «Вэксаль», формула:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... спыніць пошук на «рахунак», паколькі гэтае значэнне стаіць першым у спісе, і вынясе значэнне з ячэйкі B1.
Далей у гэтым артыкуле я пакажу вам, як гэта зрабіць ВПР з улікам рэгістра. Акрамя таго, мы даведаемся яшчэ некалькі функцый, якія могуць выконваць пошук у Excel з улікам рэгістра.
Пачнем з самага простага – ПОГЛЯД (ПАГЛЯД) і SUMPRODUCT (SUMPRODUCT), якія, на жаль, маюць некалькі істотных абмежаванняў. Далей мы больш падрабязна разгледзім крыху больш складаную формулу INDEX+MATCH (INDEX+MATCH), які бездакорна працуе ў любой сітуацыі і з любым наборам дадзеных.
Функцыя VLOOKUP адчувальная да рэгістра
Як вы ўжо ведаеце, звычайная функцыя ВПР неадчувальны да рэгістра. Аднак ёсць спосаб зрабіць яго адчувальным да рэгістра. Для гэтага трэба дадаць у табліцу дапаможны слупок, як паказана ў наступным прыкладзе.
Дапусцім, у слупок B ёсць ідэнтыфікатары прадукту (прадмет), і вы хочаце атрымаць цану прадукту і адпаведны каментар з слупкоў C и D. Праблема ў тым, што ідэнтыфікатары ўтрымліваюць як малыя, так і вялікія літары. Напрыклад, значэння вочак B4 (001Tvci3u) і B5 (001Tvci3U) адрозніваюцца толькі ў выпадку апошняга сімвала, u и U адпаведна.
Як вы можаце сабе ўявіць, звычайная формула пошуку
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
вернецца $ 90, так як значэнне 001Твцы3у знаходзіцца ў дыяпазоне пошуку раней, чым 001Твцы3У. Але гэта не тое, што нам трэба, праўда?
Для пошуку з дапамогай функцыі ВПР у Excel з улікам рэгістра вам трэба будзе дадаць дапаможны слупок і запоўніць яго вочкі наступнай формулай (дзе B - слупок пошуку):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Гэтая формула разбівае патрэбнае значэнне на асобныя сімвалы, замяняе кожны сімвал сваім кодам (напрыклад, замест A у 65, замест гэтага a код 97), а затым аб'ядноўвае гэтыя коды ў унікальны радок лічбаў.
Пасля гэтага мы выкарыстоўваем простую функцыю ВПР для пошуку з улікам рэгістра:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Правільная праца функцыі ВПР улік рэгістра залежыць ад двух фактараў:
- Дапаможны слупок павінен быць самым левым слупком у бачным дыяпазоне.
- Значэнне, якое вы шукаеце, павінна ўтрымліваць код знака замест рэальнага значэння.
Як правільна выкарыстоўваць функцыю CODE
Формула, устаўленая ў ячэйкі дапаможнага слупка, мяркуе, што ўсе значэнні пошуку маюць аднолькавую колькасць сімвалаў. Калі няма, то вам трэба ведаць найменшыя і самыя вялікія лічбы і дадаць як мага больш функцый КАЛІПАМЫЛКА (КАЛІПАМЫЛКА) Колькі знакаў складае розніца паміж самым кароткім і самым доўгім шуканым значэннем.
Напрыклад, калі самае кароткае значэнне пошуку складае 3 сімвалы, а самае доўгае - 5 сімвалаў, выкарыстоўвайце наступную формулу:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Для функцыі ПСТР (MID) Вы прыводзіце наступныя аргументы:
- 1-ы аргумент - тэкст (тэкст) - гэта тэкст або спасылка на ячэйку, якая змяшчае сімвалы, якія трэба вылучыць (у нашым выпадку гэта B2)
- 2-ы аргумент - пачатковы_нумер (пачатковая_пазіцыя) - гэта пазіцыя першага з гэтых сімвалаў, якія будуць вынятыя. вы ўваходзіце 1 у першай функцыі ПСТР, 2 – у другой функцыі ПСТР і г.д.
- 3-ы аргумент - колькасць_знакаў (number_of_characters) – Вызначае колькасць сімвалаў, якія трэба атрымаць з тэксту. Паколькі нам увесь час патрэбны толькі 1 сімвал, ва ўсіх функцыях мы пішам 1.
Абмежаванні: функцыя ВПР не з'яўляецца лепшым рашэннем для пошуку ў Excel з улікам рэгістра. Па-першае, неабходна дадаць дапаможны слупок. Па-другое, формула добра спраўляецца толькі ў тым выпадку, калі дадзеныя аднастайныя або вядома дакладная колькасць знакаў у шуканых значэннях. Калі гэта не ваш выпадак, лепш скарыстацца адным з рашэнняў, якія мы пакажам ніжэй.
Функцыя LOOKUP для пошуку з улікам рэгістра
функцыя ПОГЛЯД (LOOKUP), звязаныя ВПР, аднак яго сінтаксіс дазваляе ажыццяўляць пошук з улікам рэгістра без дадання дапаможнага слупка. Для гэтага выкарыстоўвайце ПОГЛЯД у спалучэнні з функцыян EXACT (ДАкладна).
Калі ўзяць дадзеныя з папярэдняга прыкладу (без дапаможнага слупка), то з задачай справіцца наступная формула:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Пошук формул у дыяпазоне A2: A7 дакладнае супадзенне са значэннем ячэйкі F2 адчувальны да рэгістра і вяртае значэнне са слупка B таго ж радка.
як ВПРфункцыя ПОГЛЯД аднолькава працуе з тэкставымі і лікавымі значэннямі, як вы бачыце на скрыншоце ніжэй:
Важна! Для таго, каб функцыя ПОГЛЯД працавалі правільна, значэнні ў слупку пошуку павінны быць адсартаваны ў парадку ўзрастання, г.зн. ад найменшага да найбольшага.
Дазвольце мне коратка растлумачыць, як функцыя працуе EXACT у формуле, паказанай вышэй, бо гэта ключавы момант.
функцыя EXACT параўноўвае два тэкставыя значэнні ў 1-м і 2-м аргументах і вяртае TRUE, калі яны абсалютна аднолькавыя, або FALSE, калі не. Для нас важная функцыя EXACT з улікам рэгістра.
Давайце паглядзім, як працуе наша формула ПРАГЛЯД+ДАкладНА:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- функцыя EXACT параўноўвае значэнне ячэйкі F2 з усімі элементамі ў слупок A (A2:A7). Вяртае TRUE, калі знойдзена дакладнае супадзенне, інакш - FALSE.
- Так як вы даяце першы аргумент функцыі ПОГЛЯД значэнне TRUE, яно здабывае адпаведнае значэнне з названага слупка (у нашым выпадку, слупка B), толькі калі знойдзена дакладнае супадзенне, з улікам рэгістра.
Я спадзяюся, што гэта тлумачэнне было зразумелым і цяпер вы зразумелі асноўную ідэю. Калі так, то ў вас не ўзнікне ніякіх цяжкасцяў з іншымі функцыямі, якія мы разбяром далей, т.к. усе яны працуюць па адным прынцыпе.
Абмежаванні: Дадзеныя ў слупку пошуку павінны быць адсартаваны ў парадку ўзрастання.
SUMPRODUCT – знаходзіць тэкставыя значэнні з улікам рэгістра, але вяртае толькі лічбы
Як вы ўжо зразумелі з назвы, SUMPRODUCT (SUMPRODUCT) - яшчэ адна функцыя Excel, якая дапаможа вам зрабіць пошук з улікам рэгістра, але верне толькі лікавыя значэнні. Калі гэты варыянт вас не задавальняе, то можна адразу прыступаць да пучка INDEX+MATCH, які дае рашэнне для любога выпадку і для любых тыпаў дадзеных.
Спачатку дазвольце мне коратка растлумачыць сінтаксіс гэтай функцыі, гэта дапаможа вам лепш зразумець наступную формулу з улікам рэгістра.
функцыя SUMPRODUCT памнажае элементы зададзеных масіваў і вяртае суму вынікаў. Сінтаксіс выглядае так:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Паколькі нам патрэбны пошук з улікам рэгістра, мы выкарыстоўваем функцыю EXACT (EXACT) з папярэдняга прыкладу ў якасці аднаго з множнікаў:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Як вы памятаеце, EXACT параўноўвае значэнне ячэйкі F2 з усімі элементамі ў слупок A. Вяртае TRUE, калі знойдзена дакладнае супадзенне, інакш - FALSE. У матэматычных аперацыях Excel прымае TRUE як 1, і FALSE для 0Далей SUMPRODUCT памнажае гэтыя лічбы і сумуе вынікі.
Нулі не залічваюцца, таму што пры множанні яны заўсёды даюць 0. Давайце больш падрабязна разгледзім, што адбываецца пры дакладным супадзенні ў слупку A знайшлі і вярнулі 1… Функцыя SUMPRODUCT памнажае лік у слупку B on 1 і вяртае вынік - сапраўды такі ж лік! Гэта таму, што вынікі іншых прадуктаў роўныя нулю, і яны не ўплываюць на выніковую суму.
На жаль функцыя SUMPRODUCT не можа працаваць з тэкставымі значэннямі і датамі, бо іх нельга памнажаць. У гэтым выпадку вы атрымаеце паведамленне пра памылку #VALUE! (#ЗНАЧЭННЕ!), як у ячэйцы F4 на малюнку ніжэй:
Абмежаванні: Вяртае толькі лікавыя значэнні.
INDEX + MATCH – пошук з улікам рэгістра для любога тыпу дадзеных
Нарэшце, мы блізкія да неабмежаванай і адчувальнай да рэгістра формулы пошуку, якая працуе з любым наборам даных.
Гэты прыклад апошні не таму, што лепшае застаецца на дэсерт, а таму, што веды, атрыманыя з папярэдніх прыкладаў, дапамогуць вам лепш і хутчэй зразумець формулу з улікам рэгістра. INDEX+MATCH (ІНДЭКС+СУПАДЗЕННЕ).
Як вы, напэўна, здагадаліся, сумяшчэнне функцый БОЛЬШ ВЫКЛЮЧАНЫ и індэкс выкарыстоўваецца ў Excel як больш гнуткая і магутная альтэрнатыва для ВПР. Артыкул Выкарыстанне INDEX і MATCH замест VLOOKUP выдатна растлумачыць, як гэтыя функцыі працуюць разам.
Я проста рэзюмую асноўныя моманты:
- функцыя БОЛЬШ ВЫКЛЮЧАНЫ (MATCH) шукае значэнне ў зададзеным дыяпазоне і вяртае яго адносную пазіцыю, гэта значыць нумар радка і/ці слупка;
- Далей функцыя індэкс (INDEX) вяртае значэнне з вызначанага слупка і/ці радка.
Сфармуляваць INDEX+MATCH можа шукаць з улікам рэгістра, вам трэба толькі дадаць да яго адну функцыю. Няцяжка здагадацца, што гэта зноў жа EXACT (ДАкладна):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
У гэтай формуле EXACT працуе гэтак жа, як і ў спалучэнні з функцыяй ПОГЛЯД, і дае той жа вынік:
Звярніце ўвагу, што формула INDEX+MATCH у фігурных дужках з'яўляецца формула масіва, і вы павінны завяршыць яе, націснуўшы Ctrl + Shift + Enter.
Чаму INDEX+MATCH - лепшае рашэнне для пошуку з улікам рэгістра?
Асноўныя перавагі пучка індэкс и БОЛЬШ ВЫКЛЮЧАНЫ:
- Не патрабуе дадання дапаможнага слупка, у адрозненне ад ВПР.
- Не патрабуе сартавання слупка пошуку, у адрозненне ад ПОГЛЯД.
- Працуе з усімі тыпамі даных - лічбамі, тэкстам і датамі.
Гэтая формула здаецца ідэальнай, ці не так? На самай справе гэта не так. І вось чаму.
Выкажам здагадку, што ячэйка ў слупку вяртанага значэння, звязаная са значэннем пошуку, пустая. Які вынік верне формула? не? Давайце паглядзім, што на самай справе вяртае формула:
Ой, формула вяртае нуль! Гэта не можа быць вялікай праблемай, калі вы працуеце з чыстымі тэкставымі значэннямі. Але калі табліца змяшчае лічбы, у тым ліку «сапраўдныя» нулі, гэта становіцца праблемай.
Фактычна, усе іншыя формулы пошуку (VLOOKUP, LOOKUP і SUMPRODUCT), якія мы абмяркоўвалі раней, паводзяць сябе такім жа чынам. Але вы хочаце ідэальную формулу, так?
Каб зрабіць формулу адчувальнай да рэгістра INDEX+MATCH ідэальны, пастаўце яго ў функцыю IF (IF), які праверыць ячэйку з вяртаным значэннем і верне пусты вынік, калі яна пустая:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
У гэтай формуле:
- B - гэта слупок з вяртанымі значэннямі
- 1+ лік, які ператварае адноснае становішча ячэйкі, якое вяртаецца функцыяй БОЛЬШ ВЫКЛЮЧАНЫ, на сапраўдны адрас ячэйкі. Напрыклад, у нашай функцыі БОЛЬШ ВЫКЛЮЧАНЫ дадзены масіў пошуку A2: A7, гэта значыць адноснае размяшчэнне клеткі A2 воля 1, таму што гэта першы ў масіве. Але фактычнае становішча клеткі A2 у калонцы ёсць 2, таму дадаем 1каб кампенсаваць розніцу і мець функцыю ускосна (УСКОСНЫ) атрымаў значэнне з патрэбнай ячэйкі.
На малюнках ніжэй паказана выпраўленая формула з улікам рэгістра INDEX+MATCH У дзеянні. Ён вяртае пусты вынік, калі вернутая ячэйка пустая.
Я перапісаў формулу ў слупкі B:Dкаб змясціць радок формул на скрыншоце.
Формула вяртаецца 0калі вернутая ячэйка змяшчае нуль.
Калі вы хочаце спасылку індэкс и БОЛЬШ ВЫКЛЮЧАНЫ адлюстроўваецца нейкае паведамленне, калі вяртанае значэнне пустое, вы можаце запісаць яго ў апошнія двукоссі (“”) формулы, напрыклад, так:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
