Мундариҷа
Ин дастури хурд мефаҳмонад, ки чӣ тавр сохтани функсия VPR (VLOOKUP) ба ҳарфҳои ҳарф ҳассос аст, якчанд формулаҳои дигареро нишон медиҳад, ки Excel метавонад ба таври ҳассос ба ҳарфҳо ҷустуҷӯ кунад ва ҷиҳатҳои қавӣ ва заъфи ҳар як функсияро нишон диҳад.
Фикр мекунам, ки ҳар як корбари Excel медонад, ки кадом функсия ҷустуҷӯи амудиро иҷро мекунад. Ин дуруст аст, ин функсия аст VPR. Бо вуҷуди ин, кам одамон инро медонанд VPR ҳассос ба ҳарф нест, яъне аломатҳои хурд ва калон барои он якхелаанд.
Ин аст як мисоли фаврӣ, ки нотавонӣ нишон медиҳад VPR феҳристи эътироф. Фарз мекунем, ки дар ҳуҷайра A1 дорои арзиши "вексел" ва чашмак аст A2 – «Вексель», формула:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… ҷустуҷӯи худро дар "вексел" қатъ мекунад, зеро он арзиш дар рӯйхат дар ҷои аввал қарор дорад ва арзишро аз чашмак хориҷ мекунад B1.
Баъдтар дар ин мақола, ман ба шумо нишон медиҳам, ки чӣ тавр бояд кард VPR ҳарф калон ё хурд аст. Илова бар ин, мо якчанд функсияҳои дигарро меомӯзем, ки метавонанд дар Excel ҷустуҷӯҳои ҳассосро иҷро кунанд.
Мо бо соддатарин оғоз мекунем - VIEW (Ҷустуҷӯ) ва SUMPRODUCT (SUMPRODUCT), ки мутаассифона, якчанд маҳдудиятҳои назаррас доранд. Минбаъд, мо формулаи каме мураккабтарро бодиққат дида мебароем ИНДЕКС+МАТЧ (INDEX+MATCH), ки дар ҳама гуна вазъият ва ҳама гуна маҷмӯаи додаҳо бенуқсон кор мекунад.
Функсияи VLOOKUP ба ҳарфҳо ҳассос аст
Тавре ки шумо аллакай медонед, вазифаи муқаррарии VPR беҳассос аст. Бо вуҷуди ин, як роҳи ҳассос кардани он вуҷуд дорад. Барои ин ба ҷадвал сутуни ёрирасон илова кардан лозим аст, ки дар мисоли зерин нишон дода шудааст.
Фарз мекунем, ки дар сутун B идентификаторҳои маҳсулот мавҷуданд (Масеҳ) ва шумо мехоҳед, ки нархи маҳсулот ва шарҳи мувофиқро аз сутунҳо гиред C и D. Мушкилот дар он аст, ки идентификаторҳо ҳам ҳарфҳои хурд ва ҳам ҳарфҳои калон доранд. Масалан, арзишҳои ҳуҷайра B4 (001Tvci3u) ва B5 (001Tvci3U) танҳо дар мавриди аломати охирин фарқ мекунад, u и U мутаносибан.
Тавре ки шумо тасаввур карда метавонед, формулаи муқаррарии ҷустуҷӯ
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
бармегардад $ 90, аз арзиши 001Tvci3u дар доираи ҷустуҷӯ пештар аз он аст 001Tvci3U. Аммо ин ба мо лозим нест, ҳамин тавр не?
Барои ҷустуҷӯ бо функсия VPR дар ҳолати ҳассос ба ҳарфи 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) ва сипас ин рамзҳоро ба як сатри беназири рақамҳо муттаҳид мекунад.
Баъд аз ин, мо як функсияи оддиро истифода мебарем VPR барои ҷустуҷӯи ҳассос ҳарф:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Фаъолияти дурусти функсия VPR ҳассос ба ҳарф аз ду омил вобаста аст:
- Сутуни ёрирасон бояд сутуни чаптарин дар диапазони намоён бошад.
- Арзише, ки шумо ҷустуҷӯ мекунед, бояд ба ҷои арзиши воқеӣ рамзи аломат дошта бошад.
Чӣ тавр дуруст истифода бурдани функсияи CODE
Формуле, ки ба ячейкаҳои сутуни ёрирасон дохил карда шудааст, тахмин мекунад, ки ҳамаи арзишҳои ҷустуҷӯии шумо шумораи якхелаи аломатҳоро доранд. Агар не, пас шумо бояд рақамҳои хурдтарин ва калонтаринро донед ва ба қадри имкон хусусиятҳо илова кунед ХАТО (IFERROR) фарқияти байни кӯтоҳтарин ва дарозтарин арзиши ҷустуҷӯшуда чанд аломат аст.
Масалан, агар кӯтоҳтарин арзиши ҷустуҷӯ 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));"")
Барои функсия PSTR (MID) Шумо далелҳои зеринро пешниҳод мекунед:
- Далели 1 - матн (матн) матн ё истинод ба ячейкаест, ки дорои аломатҳои истихроҷшаванда аст (дар ҳолати мо он B2 аст)
- Далели 2 - оғози_рақам (start_position) мавқеи аввалини он аломатҳои истихроҷшаванда мебошад. дохил мешавед 1 дар вазифаи якум PSTR, 2 - дар вазифаи дуюм PSTR ва ѓайра
- Далели 3 - адад_аломатҳо (шумораи_аломатҳо) - Миқдори аломатҳоеро, ки аз матн бароварда мешаванд, муайян мекунад. Азбаски мо ҳама вақт танҳо 1 аломат лозим аст, дар ҳама вазифаҳо мо менависем 1.
Меъёрҳо: функсия VPR беҳтарин роҳи ҷустуҷӯ барои ҷустуҷӯҳои ҳассос дар Excel нест. Аввалан, илова кардани сутуни ёрирасон талаб карда мешавад. Дуюм, формула танҳо дар сурати якхела будани маълумот ё шумораи дақиқи аломатҳо дар арзишҳои ҷустуҷӯшуда маълум кори хуб мекунад. Агар ин ҳолати шумо набошад, беҳтар аст, ки яке аз роҳҳои ҳалли онро дар зер нишон диҳед.
Функсияи LOOKUP барои ҷустуҷӯи ҳассос ба ҳарфҳо
функсия VIEW (Ҷустуҷӯ) алоқаманд VPR, аммо синтаксиси он имкон медиҳад, ки бидуни илова кардани сутуни ёрирасон ҷустуҷӯҳои ҳассос ба ҳарфи ҳарфиро фароҳам оранд. Барои ин, истифода баред VIEW бо функсия якҷоя карда мешавад ЭЗОҲ (АЙН).
Агар мо маълумотро аз мисоли қаблӣ гирем (бе сутуни ёрирасон), формулаи зерин вазифаро иҷро мекунад:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Ҷустуҷӯи формула дар диапазон А2: А7 мувофиқати дақиқ бо арзиши ҳуҷайра F2 ҳассос ба ҳарф ва арзишро аз сутуни B ҳамон сатр бармегардонад.
мисли VPRфунксия VIEW бо арзишҳои матнӣ ва ададӣ баробар кор мекунад, тавре ки шумо дар тасвири зер мебинед:
Муҳимтарин! Барои иҷрои вазифа VIEW дуруст кор карда шуда бошад, арзишҳо дар сутуни ҷустуҷӯ бояд бо тартиби афзоиш, яъне аз хурдтарин ба калон мураттаб карда шаванд.
Биёед ман мухтасар шарҳ диҳам, ки ин функсия чӣ гуна кор мекунад ЭЗОҲ дар формулаи дар боло нишон додашуда, зеро ин нуқтаи асосӣ аст.
функсия ЭЗОҲ ду арзиши матниро дар аргументҳои 1 ва 2 муқоиса мекунад ва TRUE-ро бармегардонад, агар онҳо комилан якхела бошанд, ё FALSE агар не. Барои мо муҳим аст, ки функсия ЭЗОҲ ҳарф калон ё хурд аст.
Биёед бубинем, ки формулаи мо чӣ гуна кор мекунад НАМОИШ + АМАЛ:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- функсия ЭЗОҲ арзиши ҳуҷайраро муқоиса мекунад F2 бо ҳама унсурҳо дар сутун A (A2:A7). Агар мувофиқати дақиқ пайдо шавад, TRUE-ро бармегардонад, дар акси ҳол FALSE.
- Азбаски шумо далели аввалини функсияро медиҳед VIEW арзиши TRUE, он арзиши мувофиқро аз сутуни муайяншуда берун мекунад (дар ҳолати мо, сутуни B) танҳо дар сурати пайдо шудани мувофиқати дақиқ, ҳассос ба ҳарф.
Ман умедворам, ки ин тавзеҳот равшан буд ва акнун шумо фикри асосиро дарк мекунед. Агар ин тавр бошад, пас шумо бо дигар вазифаҳо мушкилот нахоҳед дошт, ки мо минбаъд таҳлил хоҳем кард, зеро. хамаи онхо аз руи як принцип кор мекунанд.
Меъёрҳо: Маълумот дар сутуни ҷустуҷӯ бояд бо тартиби афзоиш мураттаб карда шаванд.
SUMPRODUCT – арзишҳои матниро, ки ба ҳарфҳо ҳассос аст, пайдо мекунад, аммо танҳо рақамҳоро бармегардонад
Тавре ки шумо аллакай аз сарлавҳа фаҳмидаед, SUMPRODUCT (SUMPRODUCT) функсияи дигари Excel мебошад, ки ба шумо дар ҷустуҷӯи ҳассос ба ҳарфҳо кӯмак мекунад, аммо танҳо арзишҳои ададро бармегардонад. Агар ин хосият ба шумо мувофиқ наояд, шумо метавонед фавран ба баста гузаред ИНДЕКС+МАТЧ, ки барои ҳама ҳолат ва ҳама намуди додаҳо ҳалли худро медиҳад.
Аввалан, ба ман иҷозат диҳед, ки синтаксиси ин функсияро мухтасар шарҳ диҳам, ин ба шумо кӯмак мекунад, ки формулаи ҳассоси зеринро беҳтар дарк кунед.
функсия SUMPRODUCT элементҳои массивҳои додашударо зиёд мекунад ва ҷамъи натиҷаҳоро бармегардонад. Синтаксис чунин менамояд:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Азбаски мо ба ҷустуҷӯи ҳассос ниёз дорем, мо ин функсияро истифода мебарем ЭЗОҲ (EXACT) аз мисоли қаблӣ ҳамчун яке аз мултипликаторҳо:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Тавре ки шумо дар ёд доред ЭЗОҲ арзиши ҳуҷайраро муқоиса мекунад F2 бо ҳама унсурҳо дар сутун A. Агар мувофиқати дақиқ пайдо шавад, TRUE-ро бармегардонад, дар акси ҳол FALSE. Дар амалҳои математикӣ, Excel TRUE-ро ҳамчун 1, ва FALSE барои 0Минбаъд SUMPRODUCT ин ракамхоро зиёд карда, натичахоро чамъбаст мекунад.
Сифрҳо ҳисоб карда намешаванд, зеро ҳангоми зарб кардан онҳо ҳамеша медиҳанд 0. Биёед бодиққат дида бароем, ки ҳангоми мувофиқати дақиқ дар сутун чӣ ҳодиса рӯй медиҳад A ёфт ва баргаштанд 1… Функсия SUMPRODUCT адади сутунро зиёд мекунад B on 1 ва натиҷаро бармегардонад - маҳз ҳамон рақам! Ин аст, ки натиљањои дигар мањсулот ба сифр баробар буда, ба маблаѓи њосилшуда таъсир намерасонанд.
Мутаассифона, функсия SUMPRODUCT бо арзишҳо ва санаҳои матнӣ кор карда наметавонад, зеро онҳоро зиёд кардан мумкин нест. Дар ин ҳолат, шумо паёми хатогиро хоҳед гирифт # АРЗИШ! (#VALUE!) ҳамчун дар чашмак F4 дар расми зер:
Меъёрҳо: Танҳо арзишҳои ададӣ бар мегардонад.
INDEX + MATCH - ҷустуҷӯи ҳассос барои ҳар як намуди маълумот
Ниҳоят, мо ба формулаи ҷустуҷӯи номаҳдуд ва ҳассос ба ҳарфҳо наздикем, ки бо ҳама гуна маҷмӯи додаҳо кор мекунад.
Ин мисол дар охир меояд, на аз он сабаб, ки беҳтаринаш барои шириниҳо боқӣ мондааст, балки барои он ки донише, ки аз мисолҳои қаблӣ гирифта шудааст, ба шумо кӯмак мекунад, ки формулаи ҳассосро беҳтар ва зудтар дарк кунед. ИНДЕКС+МАТЧ (ИНДЕКС+МАТЧ).
Тавре ки шумо эҳтимол фаҳмидед, маҷмӯи функсияҳо БЕШТАР ФОШ и INDEX дар Excel ҳамчун алтернативаи фасењтар ва тавонотар истифода бурда мешавад VPR. Мақолаи истифодаи INDEX ва MATCH ба ҷои VLOOKUP ба таври комил шарҳ медиҳад, ки чӣ гуна ин функсияҳо якҷоя кор мекунанд.
Ман танҳо нуктаҳои асосиро такрор мекунам:
- функсия БЕШТАР ФОШ (MATCH) арзишро дар диапазони додашуда ҷустуҷӯ мекунад ва мавқеи нисбии он, яъне рақами сатр ва/ё сутунро бармегардонад;
- Минбаъд, функсия INDEX (INDEX) арзишро аз сутун ва/ё сатри муайяншуда бармегардонад.
Ба формула ИНДЕКС+МАТЧ метавонад бо ҳассос ба ҳарф ҷустуҷӯ кунад, шумо бояд ба он танҳо як функсия илова кунед. Боз тахмин кардан душвор нест, ки он чӣ гуна аст ЭЗОҲ (АЙН):
=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))
Дар ин формула ЭЗОҲ ҳамон тавре ки дар якҷоягӣ бо функсия кор мекунад VIEW, ва ҳамон натиҷа медиҳад:
Дар хотир доред, ки формула ИНДЕКС+МАТЧ Формулаи массив дар қавсҳои ҷингила дохил карда шудааст ва шумо бояд онро бо пахшкунӣ анҷом диҳед Ctrl + Shift + Enter.
Чаро INDEX+MATCH беҳтарин ҳалли ҷустуҷӯ барои ҳарфи ҳассос аст?
Афзалиятҳои асосии бастабандӣ INDEX и БЕШТАР ФОШ:
- Илова кардани сутуни ёрирасонро талаб намекунад, баръакс VPR.
- Баръакси сутуни ҷустуҷӯ мураттаб карда шуданро талаб намекунад VIEW.
- Бо ҳама намуди маълумот - рақамҳо, матн ва санаҳо кор мекунад.
Ин формула комил ба назар мерасад, ҳамин тавр не? Дар асл, ин тавр нест. Ва барои хамин.
Фарз мекунем, ки чашмаки сутуни арзиши баргардонидани бо арзиши ҷустуҷӯ холӣ аст. Формула чӣ натиҷа медиҳад? Не? Биёед бубинем, ки формула воқеан чӣ бармегардад:
Оҳ, формула сифрро бармегардонад! Агар шумо бо арзишҳои матнии тоза кор карда истода бошед, ин метавонад мушкили калон набошад. Аммо, агар ҷадвал рақамҳо, аз ҷумла сифрҳои "воқеӣ" дошта бошанд, ин мушкилот мегардад.
Дарвоқеъ, ҳамаи формулаҳои ҷустуҷӯии дигар (VLOOKUP, LOOKUP ва SUMPRODUCT), ки мо қаблан муҳокима кардем, ҳамин тавр рафтор мекунанд. Аммо шумо формулаи комилро мехоҳед, дуруст?
Барои ҳассос кардани ҳарфҳои формула ИНДЕКС+МАТЧ комил, онро дар функсия гузоред 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+ рақамест, ки мавқеи нисбии чашмакеро, ки функсия баргардонида шудааст, табдил медиҳад БЕШТАР ФОШ, ба адреси хакикии ячейка. Масалан, дар вазифаи мо БЕШТАР ФОШ массиви ҷустуҷӯ дода шудааст А2: А7, яъне мавқеи нисбии ҳуҷайра A2 хоҳад 1, зеро он аввалин дар массив аст. Аммо мавқеи воқеии ҳуҷайра A2 дар сутун аст 2, ҳамин тавр мо илова мекунем 1ки тафовутро бар-хам дода, вазифаи худро дошта бошад Бавосита (ҒАЙРУМИ) арзишро аз чашмаки дилхоҳ дарёфт кард.
Дар расмҳои зер формулаи ислоҳшудаи ҳассос ба ҳарфҳо нишон дода шудааст ИНДЕКС+МАТЧ Дар амал. Он натиҷаи холӣ медиҳад, агар чашмаки баргардонида холӣ бошад.
Ман формуларо ба сутунҳо аз нав навиштам Б:Дбарои мувофиқ кардани сатри формула дар скриншот.
Формула бармегардад 0агар чашмаки баргардонидашуда сифр дошта бошад.
Агар шумо хоҳед, пайванд INDEX и БЕШТАР ФОШ Вақте ки арзиши бозгашт холӣ аст, баъзе паёмро нишон дод, шумо метавонед онро дар нохунакҳои охирини формула ("") нависед, масалан, чунин:
=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.")
