мазмуну
Бул кичинекей окуу куралы функцияны кантип жасоону түшүндүрөт VPR (VLOOKUP) регистрди сезгич, Excel регистрди сезгич түрдө издей турган бир нече башка формулаларды көрсөтөт жана ар бир функциянын күчтүү жана алсыз жактарын көрсөтөт.
Ар бир Excel колдонуучусу вертикалдуу издөөнү кандай функция аткарарын билет деп ойлойм. Туура, бул функция VPR. Бирок, муну аз эле адамдар билет VPR тамга сезгич эмес, башкача айтканда, кичине жана чоң тамгалар ал үчүн бирдей.
Бул жерде жөндөмсүздүгүн көрсөткөн тез мисал VPR реестрди таануу. клеткада дейли A1 "эсеп" маанисин жана уячаны камтыйт A2 - "Эсеп", формула:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… "эсепте" издөөнү токтотот, анткени бул маани тизмеде биринчи орунда турат жана уячадан маанини чыгарат. B1.
Кийинчерээк бул макалада мен сага кантип кылууну көрсөтөм VPR тамга сезгич. Мындан тышкары, биз Excelде регистрге сезимтал издөөлөрдү аткара турган дагы бир нече функцияларды үйрөнөбүз.
Биз эң жөнөкөйдөн баштайбыз - КӨРҮҮ (ИЗДӨӨ) жана СУПРОДУКТ (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 тамга сезгич эки фактордон көз каранды:
- Жардамчы тилке көрүнүүчү диапазондун эң сол жагында болушу керек.
- Сиз издеп жаткан маани чыныгы маанинин ордуна символдун кодун камтышы керек.
КОД функциясын кантип туура колдонуу керек
Көмөкчү мамычанын уячаларына киргизилген формула сиздин бардык издөө маанилериңизде бирдей сандагы символдор бар деп болжолдойт. Эгерде андай болбосо, анда эң кичине жана эң чоң сандарды билип, ошончо функцияларды кошуу керек КАТА (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-аргумент - башталгыч_саны (старт_позиция) - бул чыгарыла турган символдордун биринчисинин абалы. сен киресиң 1 биринчи функцияда PSTR, 2 – экинчи функцияда PSTR жана башкалар
- 3-аргумент - символдор_саны (белгилердин_саны) – Тексттен чыгарыла турган символдордун санын көрсөтөт. Бизге ар дайым 1 гана символ керек болгондуктан, бардык функцияларда биз жазабыз 1.
чектөөлөр: милдети VPR Excelде регистрге сезимтал издөөлөр үчүн мыкты чечим эмес. Биринчиден, көмөкчү мамычаны кошуу талап кылынат. Экинчиден, маалыматтар бир тектүү болсо, же изделген маанилердеги белгилердин так саны белгилүү болгондо гана формула жакшы иштейт. Эгер бул сиздин жагдайыңыз болбосо, анда биз төмөндө көрсөткөн чечимдердин бирин колдонгонуңуз жакшы.
Регистрге сезимтал издөө үчүн LOOKUP функциясы
милдети КӨРҮҮ (издөө) байланыштуу VPR, бирок анын синтаксиси жардамчы тилкени кошпостон, регистрге сезимтал издөөгө мүмкүндүк берет. Бул үчүн, колдонуңуз КӨРҮҮ функциясы менен айкалыштырылган пост (ТАК).
Эгерде биз мурунку мисалдагы маалыматтарды алсак (жардамчы тилкесиз), анда төмөнкү формула тапшырманы аткарат:
=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 регистрге сезимтал жана ошол эле саптын В тилкесиндеги маанини кайтарат.
сыяктуу VPRмилдети КӨРҮҮ төмөнкү скриншот көрүп тургандай, текст жана сандык маанилер менен бирдей иштейт:
Маанилүү! Функция үчүн КӨРҮҮ туура иштесе, издөө тилкесиндеги маанилер өсүү тартибинде, б.а. кичинеден чоңго чейин иреттелиши керек.
Функциянын кантип иштээрин кыскача түшүндүрүп берейин пост жогоруда көрсөтүлгөн формулада, анткени бул негизги пункт.
милдети пост 1-жана 2-аргументтеги эки тексттик маанини салыштырып, эгерде алар так окшош болсо, TRUE, же болбосо, ЖАЛГАН деп кайтарат. Бул функция биз үчүн маанилүү пост тамга сезгич.
Келгиле, биздин формула кандай иштээрин карап көрөлү КӨРҮҮ+ТАК:
=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 кайтарат, антпесе ЖАЛГАН.
- Сиз биринчи функциянын аргументин бергенден кийин КӨРҮҮ мааниси TRUE, ал көрсөтүлгөн тилкеден тиешелүү маанини чыгарат (биздин учурда, В тилкеси), эгерде так дал келүү табылса гана, регистрди эске алуу менен.
Бул түшүндүрмө түшүнүктүү болду деп үмүттөнөм, эми сиз негизги ойду түшүндүңүз. Эгер ошондой болсо, анда биз андан ары талдай турган башка функциялар менен эч кандай кыйынчылыктарга дуушар болбойт, анткени. алардын бардыгы бирдей принцип боюнча иштешет.
чектөөлөр: Издөө тилкесиндеги маалыматтар өсүү тартибинде иреттелиши керек.
SUMPRODUCT – тексттин маанилерин табат, регистрге сезимтал, бирок сандарды гана кайтарат
Сиз аталышынан түшүнгөндөй, СУПРОДУКТ (SUMPRODUCT) – Excelдин дагы бир функциясы, ал сизге регистрди эске алуу менен издөө жүргүзүүгө жардам берет, бирок сандык маанилерди гана кайтарат. Эгер бул параметр сизге жакпаса, анда сиз дароо таңгакка өтсөңүз болот ИНДЕКС+МАТЧ, бул ар кандай учурда жана ар кандай маалымат түрлөрү үчүн чечим берет.
Биринчиден, бул функциянын синтаксисин кыскача түшүндүрүп берейин, бул сизге кийинки регистрге сезимтал формуланы жакшыраак түшүнүүгө жардам берет.
милдети СУПРОДУКТ берилген массивдердин элементтерин көбөйтөт жана натыйжалардын суммасын кайтарат. Синтаксис мындай көрүнөт:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Бизге регистрге сезимтал издөө керек болгондуктан, функцияны колдонобуз пост (ТАК) мурунку мисалдан көбөйтүүчүлөрдүн бири катары:
=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 кайтарат, антпесе ЖАЛГАН. Математикалык операцияларда Excel TRUE деп кабыл алат 1, жана FALSE үчүн 0Андан ары СУПРОДУКТ бул сандарды көбөйтөт жана жыйынтыктарды чыгарат.
Нөлдөр эсептелбейт, анткени көбөйтүлгөндө алар ар дайым берет 0. Келгиле, тилкеде так дал келгенде эмне болорун кененирээк карап чыгалы A таап кайтты 1… Функция СУПРОДУКТ тилкедеги санды көбөйтөт B on 1 жана натыйжаны кайтарат - так эле сан! Себеби, башка продукциялардын жыйынтыгы нөлгө барабар жана алар алынган суммага таасир этпейт.
Тилекке каршы функция СУПРОДУКТ текст баалуулуктары жана даталар менен иштей албайт, анткени аларды көбөйтүү мүмкүн эмес. Бул учурда, сиз ката билдирүүсүн аласыз #VALUE! (#VALUE!) клеткадагыдай F4 төмөнкү сүрөттө:
чектөөлөр: Сандык маанилерди гана кайтарат.
INDEX + MATCH – ар кандай маалымат түрү үчүн регистрди эске алуу менен издөө
Акыр-аягы, биз ар кандай маалымат топтому менен иштеген чексиз жана регистрге сезимтал издөө формуласына жакынбыз.
Бул мисал эң жакшысы десертке калтырылганы үчүн эмес, мурунку мисалдардан алынган билим регистрге сезимтал формуланы жакшыраак жана тезирээк түшүнүүгө жардам бергендиктен акыркы болуп келет. ИНДЕКС+МАТЧ (INDEX+МАТЧ).
Сиз ойлогондой, функциялардын айкалышы КӨБҮРӨГҮ АЧЫРЫЛГАН и INDEX үчүн ийкемдүү жана күчтүү альтернатива катары Excelде колдонулат VPR. VLOOKUP ордуна INDEX жана MATCH колдонуу макаласы бул функциялардын кантип чогуу иштешин эң сонун түшүндүрөт.
Мен жөн гана негизги ойлорду кайталайын:
- милдети КӨБҮРӨГҮ АЧЫРЫЛГАН (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))
Бул формулада пост функциясы менен бирдикте иштейт КӨРҮҮ, жана ошол эле натыйжаны берет:
формула экенин белгилей кетүү керек ИНДЕКС+МАТЧ тармал кашаага алынган массив формуласы жана аны басуу менен бүтүрүшүңүз керек Ctrl+Shift+Enter.
Эмне үчүн INDEX+MATCH регистрге сезимтал издөө үчүн эң жакшы чечим?
Пакеттин негизги артыкчылыктары INDEX и КӨБҮРӨГҮ АЧЫРЫЛГАН:
- айырмаланып, жардамчы тилке кошууну талап кылбайт VPR.
- Издөө тилкесинин сорттолушун талап кылбайт, айырмаланып турат КӨРҮҮ.
- Маалыматтын бардык түрлөрү менен иштейт - сандар, текст жана даталар.
Бул формула идеалдуу көрүнөт, туурабы? Чынында, андай эмес. Ошондон улам.
Издөө мааниси менен байланышкан кайтаруу маани тилкесиндеги уяча бош деп ойлойлу. Формула кандай натыйжа берет? Жок? Келгиле, формула чындыгында эмнени кайтарып берерин карап көрөлү:
Ой, формула нөлдү кайтарат! Эгер сиз таза текст баалуулуктары менен иштеп жатсаңыз, бул чоң көйгөй болбошу мүмкүн. Бирок, эгерде таблицада сандар, анын ичинде "чыныгы" нөлдөр болсо, бул көйгөйгө айланат.
Чындыгында, биз мурда талкуулаган бардык башка издөө формулалары (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+ функция тарабынан кайтарылган уячанын салыштырмалуу абалын бурган сан КӨБҮРӨГҮ АЧЫРЫЛГАН, клетканын чыныгы дарегине. Мисалы, биздин функцияда КӨБҮРӨГҮ АЧЫРЫЛГАН издөө массиви берилген A2:A7, башкача айтканда, клетканын салыштырмалуу абалы 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.")
