Мазмұны
Бұл шағын оқулық функцияны қалай жасау керектігін түсіндіреді 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 регистрге сезімталдық екі факторға байланысты:
- Көмекші баған көрінетін ауқымдағы ең сол жақ баған болуы керек.
- Сіз іздеген мәнде нақты мәннің орнына таңба коды болуы керек.
КОД функциясын қалай дұрыс пайдалану керек
Көмекші бағанның ұяшықтарына енгізілген формула барлық іздеу мәндерінде бірдей таңбалар саны бар деп есептейді. Егер жоқ болса, онда сіз ең кіші және ең үлкен сандарды біліп, сонша мүмкіндіктерді қосуыңыз керек ҚАТЕЛІК (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 функциясы
функция 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, әйтпесе ЖАЛҒАН мәнін қайтарады.
- Сіз бірінші функция аргументін бергендіктен VIEW мәні TRUE, ол нақты сәйкестік табылған жағдайда ғана көрсетілген бағаннан (біздің жағдайда, В бағанынан) сәйкес мәнді шығарады, регистрді ескереді.
Бұл түсініктеме түсінікті болды деп үміттенемін, енді сіз негізгі ойды түсінесіз. Олай болса, біз одан әрі талдайтын басқа функциялармен сізде қиындықтар болмайды, өйткені. олардың барлығы бір принцип бойынша жұмыс істейді.
ШЕКТЕУЛЕР: Іздеу бағанындағы деректер өсу ретімен сұрыпталуы керек.
SUMPRODUCT – регистрді ескере отырып, мәтін мәндерін табады, бірақ тек сандарды қайтарады
Тақырыптан түсінгеніңіздей, SUMPRODUCT (ҚОСЫМША) – регистрді ескеретін іздеуді жасауға көмектесетін, бірақ тек сандық мәндерді қайтаратын басқа Excel функциясы. Егер бұл опция сізге сәйкес келмесе, сіз дереу пакетке өтуіңізге болады ИНДЕКС+СӘйкестік, ол кез келген жағдайға және кез келген деректер түрлеріне шешім береді.
Алдымен, осы функцияның синтаксисін қысқаша түсіндіруге рұқсат етіңіз, бұл келесі регистрді ескеретін формуланы жақсы түсінуге көмектеседі.
функция SUMPRODUCT берілген массивтердің элементтерін көбейтеді және нәтижелердің қосындысын береді. Синтаксис келесідей көрінеді:
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Бұдан әрі SUMPRODUCT осы сандарды көбейтіп, нәтижелерді шығарады.
Нөлдер есептелмейді, өйткені көбейтілгенде олар әрқашан береді 0. Бағандағы дәл сәйкестік болғанда не болатынын егжей-тегжейлі қарастырайық A тауып, қайтарды 1… Функция SUMPRODUCT бағандағы санды көбейтеді B on 1 және нәтижені қайтарады – дәл сол сан! Себебі басқа өнімдердің нәтижелері нөлге тең және олар алынған қосындыға әсер етпейді.
Өкінішке орай, функция SUMPRODUCT мәтіндік мәндермен және күндермен жұмыс істей алмайды, өйткені оларды көбейту мүмкін емес. Бұл жағдайда сіз қате туралы хабар аласыз # МАҢЫЗ! (#VALUE!) ұяшықтағы сияқты F4 төмендегі суретте:
ШЕКТЕУЛЕР: Тек сандық мәндерді қайтарады.
INDEX + MATCH – кез келген деректер түрі үшін регистрді ескере отырып іздеу
Ақырында, біз кез келген деректер жиынымен жұмыс істейтін шектеусіз және регистрді ескеретін іздеу формуласына жақынбыз.
Бұл мысал ең жақсысы десертке қалдырылғандықтан емес, алдыңғы мысалдардан алынған білім регистрді ескеретін формуланы жақсырақ және тезірек түсінуге көмектесетіндіктен соңғы болып келеді. ИНДЕКС+СӘйкестік (ИНДЕКС+СӘйкестік).
Сіз болжағандай, функциялардың тіркесімі КӨБІРЕК АШЫҚ и 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))
Бұл формулада ДӘЛ функциясымен бірге жұмыс істейді 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айырмашылықты толтыру және функцияға ие болу ТӘУЕЛСІЗДІК (ЖАМАУ) қажетті ұяшықтан мәнді шығарып алды.
Төмендегі суреттер түзетілген регистрді ескеретін формуланы көрсетеді ИНДЕКС+СӘйкестік Әрекетте. Қайтарылған ұяшық бос болса, ол бос нәтижені қайтарады.
Мен формуланы бағандарға қайта жаздым B:Dскриншотқа формула жолын сәйкестендіру үшін.
Формула қайтарылады 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.")
