Мазмұны
- Excel бағдарламасында бірнеше шарт бойынша іздеу
- VLOOKUP көмегімен 2-ші, 3-ші және т.б. мәндерді шығарамыз
- Қажетті мәннің барлық қайталануларын шығарып алыңыз
- Белгілі жол және баған бойынша XNUMXD іздеу
- Бір формулада бірнеше VLOOKUP пайдалану
- VLOOKUP және INDIRECT көмегімен әртүрлі кестелердегі деректерді динамикалық ауыстыру
Функция туралы оқулықтың екінші бөлігінде VPR Excel бағдарламасында (VLOOKUP) біз барлық қуатты басқаруға көмектесетін бірнеше мысалдарды талдаймыз VPR ең өршіл Excel тапсырмаларын шешу үшін. Мысалдар бұл мүмкіндіктің қалай жұмыс істейтіні туралы негізгі біліміңіз бар деп болжайды. Олай болмаса, синтаксис пен негізгі қолдануды түсіндіретін осы оқу құралының бірінші бөлімінен бастағыңыз келуі мүмкін. VPR. Кәне, бастайық.
Excel бағдарламасында бірнеше шарт бойынша іздеу
функция VPR Excel бағдарламасында дерекқордағы белгілі бір мәнді іздеуге арналған шынымен қуатты құрал. Дегенмен, айтарлықтай шектеу бар – оның синтаксисі тек бір мәнді іздеуге мүмкіндік береді. Бірнеше шарттар бойынша іздегіңіз келсе ше? Төменде шешімін табасыз.
1-мысал: 2 түрлі критерий бойынша іздеу
Бізде тапсырыстар тізімі бар және біз оны тапқымыз келеді делік Тауарлардың саны (саны), екі критерийге негізделген – Клиент аты (Тұтынушы) и Өнімнің атауы (Өнім). Сатып алушылардың әрқайсысы тауарлардың бірнеше түріне тапсырыс бергендігімен мәселе күрделене түседі, мұны төмендегі кестеден көруге болады:
тұрақты функция VPR бұл сценарийде жұмыс істемейді, себебі ол берілген іздеу мәніне сәйкес келетін бірінші мәнді қайтарады. Мысалы, егер сіз заттың санын білгіңіз келсе тәттілерсатып алушы тапсырыс берген Джереми Хилл, келесі формуланы жазыңыз:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– бұл формула нәтижені қайтарады 15өнімге сәйкес келеді Алма, себебі бұл сәйкес келетін бірінші мән.
Қарапайым шешім бар - барлық қажетті критерийлерді біріктіретін қосымша баған жасаңыз. Біздің мысалда бұл бағандар Клиент аты (Тұтынушы) и Өнімнің атауы (Өнім). Біріктірілген баған әрқашан іздеу ауқымындағы ең сол жақ баған болуы керек екенін ұмытпаңыз, себебі бұл функция сол жақ баған. VPR мәнді іздегенде жоғары қарайды.
Сонымен, кестеге көмекші баған қосасыз және оның барлық ұяшықтарына келесі формуланы көшіріңіз: =B2&C2. Жолдың оқылатын болуын қаласаңыз, біріктірілген мәндерді бос орынмен бөлуге болады: =B2&» «&C2. Осыдан кейін сіз келесі формуланы пайдалана аласыз:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Клетка қайда B1 аргументтің біріктірілген мәнін қамтиды іздеу_ мәні (іздеу_мәні) және 4 – Аргумент col_index_num (баған_нөмірі), яғни алынатын деректерді қамтитын бағанның нөмірі.
2-мысал: кестені басқа парақта қарау арқылы екі шарт бойынша VLOOKUP
Егер басқа парақта немесе басқа Excel жұмыс кітабында орналасқан екінші кестеден (Іздеу кестесі) деректерді қосу арқылы негізгі кестені (Негізгі кесте) жаңарту қажет болса, қажетті мәнді кірістіретін формулада тікелей жинауға болады. негізгі кестеге.
Алдыңғы мысалдағыдай, біріктірілген мәндері бар Іздеу кестесінде көмекші баған қажет болады. Бұл баған іздеу ауқымындағы ең сол жақ баған болуы керек.
Сонымен формула VPR былай болуы мүмкін:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Мұнда B және C бағандары сәйкесінше тұтынушы аттары мен өнім атауларын және сілтемені қамтиды Тапсырыс!$A&$2:$D$2 басқа парақта іздеу үшін кестені анықтайды.
Формула оқуға ыңғайлы болу үшін көрініс ауқымына атау беруге болады, содан кейін формула әлдеқайда қарапайым болады:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Формула жұмыс істеуі үшін сіз қарап отырған кестенің ең сол жақ бағанындағы мәндер іздеу шартындағыдай етіп біріктірілуі керек. Жоғарыдағы суретте біз u2bu2band мәндерін біріктірдік, олардың арасына бос орын қойдық, сол сияқты функцияның бірінші аргументінде де істеу керек (BXNUMX& «» & CXNUMX).
Есіңізде болсын! функция VPR 255 таңбамен шектелсе, ол ұзындығы 255 таңбадан асатын мәнді іздей алмайды. Мұны есте сақтаңыз және қалаған мәннің ұзындығы осы шектен аспайтынына көз жеткізіңіз.
Көмекші бағанды қосу ең талғампаз және әрқашан қолайлы шешім емес екеніне келісемін. Көмекші бағансыз бірдей әрекетті орындауға болады, бірақ бұл функциялар тіркесімі бар әлдеқайда күрделі формуланы қажет етеді INDEX (ИНДЕКС) және MATCH (КӨБІРЕК АШЫҚ).
VLOOKUP көмегімен 2-ші, 3-ші және т.б. мәндерді шығарамыз
Мұны сіз бұрыннан білесіз VPR бір ғана сәйкес мәнді қайтара алады, дәлірек айтқанда, бірінші табылған. Бірақ бұл мән қаралған массивте бірнеше рет қайталанса және олардың 2-ші немесе 3-ші бөлігін шығарып алғыңыз келсе ше? Егер барлық мәндер болса ше? Мәселе күрделі болып көрінеді, бірақ шешімі бар!
Кестенің бір бағанында тұтынушылардың аттары (Тұтынушы аты), ал екінші бағанда олар сатып алған өнімдер (Өнім) бар делік. Берілген тұтынушы сатып алған 2-ші, 3-ші және 4-ші заттарды табуға тырысайық.
Ең оңай жолы - бағанның алдында көмекші бағанды қосу тұтынушы аты және оны әрбір аттың қайталану нөмірі бар тұтынушы аттарымен толтырыңыз, мысалы, Джон До1, Джон До2 және т.б. функцияны пайдаланып нөмірлеуді орындаймыз COUNTIF (COUNTIF), тұтынушы аттары B бағанында екенін ескере отырып:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Осыдан кейін сіз қалыпты функцияны пайдалана аласыз VPRқажетті тапсырысты табу үшін. Мысалға:
- табу 2-ші жж тұтынушы тапсырыс берген зат Дэн Браун:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - табу 3-ші жж тұтынушы тапсырыс берген зат Дэн Браун:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Іс жүзінде, келесі суретте көрсетілгендей мәтіннің орнына іздеу мәні ретінде ұяшық сілтемесін енгізуге болады:
Егер сіз тек іздесеңіз 2-е қайталау үшін күрделі формуланы жасау арқылы оны көмекші бағансыз жасауға болады:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
Бұл формулада:
- $F$2 – сатып алушының аты-жөнін қамтитын ұяшық (ол өзгеріссіз, ескеріңіз – сілтеме абсолютті);
- $ B $ – баған тұтынушы аты;
- Table4 – Сіздің үстеліңіз (бұл орын әдеттегі ассортимент болуы мүмкін);
- $ C16 – кестеңіздің немесе ауқымыңыздың соңғы ұяшығы.
Бұл формула тек екінші сәйкес мәнді табады. Қалған қайталауларды шығарып алу қажет болса, алдыңғы шешімді пайдаланыңыз.
Барлық сәйкестіктердің тізімі қажет болса – функция VPR бұл көмекші емес, өйткені ол бір уақытта тек бір мәнді қайтарады – кезең. Бірақ Excel функциясы бар INDEX (INDEX), бұл тапсырманы оңай жеңе алады. Мұндай формула қалай көрінетінін сіз келесі мысалда білесіз.
Қажетті мәннің барлық қайталануларын шығарып алыңыз
Жоғарыда айтылғандай VPR сканерленген ауқымнан барлық қайталанатын мәндерді шығара алмайды. Мұны істеу үшін сізге бірнеше Excel функцияларынан тұратын сәл күрделірек формула қажет, мысалы INDEX (ИНДЕКС), Шағын (КІШІ) және ҚАТАР (ТҮЗУ)
Мысалы, төмендегі формула B2:B2 ауқымындағы F16 ұяшығындағы мәннің барлық қайталануларын табады және C бағанындағы бірдей жолдардың нәтижесін береді.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Бұл массив формуласын ұяшықтар сияқты бірнеше көрші ұяшықтарға енгізіңіз F4: F8төмендегі суретте көрсетілгендей. Ұяшықтардың саны ізделетін мәннің қайталануларының максималды мүмкін санына тең немесе одан көп болуы керек. Басуды ұмытпаңыз Ctrl + Shift + Enter пернелер тіркесімімассив формуласын дұрыс енгізу үшін.
Егер сіз оның қалай жұмыс істейтінін түсінгіңіз келсе, формуланың егжей-тегжейіне біраз тоқталайық:
Part 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – F2 ұяшығындағы мәнді B2:B16 ауқымындағы мәндердің әрқайсысымен салыстырыңыз. Егер сәйкестік табылса, онда өрнек STRING(C2:C16)-1 сәйкес жолдың нөмірін қайтарады (мән -1 тақырып жолын қоспауға мүмкіндік береді). Сәйкестік болмаса, функция IF (IF) бос жолды қайтарады.
Функция нәтижесі IF (Егер) осындай көлденең массив болады: {1,"",3,"",5,"","","","","","",12,"","",""}
Part 2:
ROW()-3
СТРОКА()-3
Мұнда функция ҚАТАР (LINE) қосымша есептегіш ретінде әрекет етеді. Формула F4:F9 ұяшықтарына көшірілгендіктен, санды шегереміз 3 мәнді алу үшін функция нәтижесінен 1 ұяшықта F4 (4-жол, 3-ті шегеріңіз) алу үшін 2 ұяшықта F5 (5-жол, 3-ті алу) және т.б.
Part 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
функция Шағын (SMALL) қайтарады н-о деректер массивіндегі ең кіші мән. Біздің жағдайда қай позиция (ең кішіден) қайтарылатыны функциямен анықталады ҚАТАР (LINE) (2-бөлімді қараңыз). Сонымен, жасуша үшін F4 функция КІШІ({массив},1) қайтару 1-ші жж (ең кіші) массив элементі, яғни 1. Ұяшық үшін F5 қайтару 2-ші жж массивтің ең кіші элементі, яғни 3Және т.б.
Part 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
функция INDEX (INDEX) жай ғана массивтегі белгілі бір ұяшықтың мәнін қайтарады С2: С16. Ұяшық үшін F4 функция ИНДЕКС($C$2:$C$16) қайтарылады Алмаүшін F5 функция ИНДЕКС($C$2:$C$16) қайтарылады тәттілер және тағы басқа.
Part 5:
IFERROR()
ЕСЛИОШИБКА()
Соңында формуланы функцияның ішіне енгіземіз ҚАТЕЛІК (IFERROR), себебі сіз қате туралы хабарға риза болмайсыз #AT (#Жоқ) егер формула көшірілетін ұяшықтар саны қаралатын ауқымдағы қайталанатын мәндер санынан аз болса.
Белгілі жол және баған бойынша XNUMXD іздеу
Excel бағдарламасында XNUMXD іздеуді орындау белгілі жол және баған нөмірі бойынша мәнді іздеуді қамтиды. Басқаша айтқанда, сіз белгілі бір жол мен бағанның қиылысында ұяшық мәнін шығарып жатырсыз.
Олай болса, кестемізге бұрылып, функциясы бар формуланы жазайық VPR, ол наурыз айында сатылған лимондардың құны туралы ақпаратты табады.
ХNUMXD іздеуді орындаудың бірнеше жолы бар. Опцияларды қарап шығыңыз және сізге сәйкес келетінін таңдаңыз.
VLOOKUP және MATCH функциялары
Сіз көптеген функцияларды пайдалана аласыз VPR (VLOOKUP) және КӨБІРЕК АШЫҚ (МАТЧ) өрістердің қиылысындағы мәнді табу үшін Өнімнің атауы (жол) және ай қарастырылатын массивтің (бағаны):
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
Жоғарыдағы формула қалыпты функция VPR, ол A2 мен A9 ұяшықтарындағы «Лимондар» мәнінің дәл сәйкестігін іздейді. Бірақ наурыз айындағы сатылымдар қай бағанда екенін білмегендіктен, үшінші функция аргументі үшін баған нөмірін орната алмайсыз. VPR. Оның орнына функция пайдаланылады КӨБІРЕК АШЫҚосы бағанды анықтау үшін.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Адам тіліне аударылған бұл формула мынаны білдіреді:
- Біз «Мар» - аргумент кейіпкерлерін іздейміз іздеу_ мәні (іздеу_мәні);
- A1-ден I1-ге дейінгі ұяшықтарды қарау – аргумент іздеу_массиві (іздеу_массив);
- Дәл сәйкестікті қайтару – аргумент сәйкестік_түрі (сәйкестік_түрі).
пайдалану 0 үшінші аргументте сіз функцияларды айтасыз КӨБІРЕК АШЫҚ іздеген мәнге дәл сәйкес келетін бірінші мәнді іздеңіз. Бұл мәнге тең FALSE (ЖАЛҒАН) төртінші аргумент үшін VPR.
Excel бағдарламасында екі жақты іздеу формуласын осылай жасауға болады, ол екі өлшемді іздеу немесе екі бағытты іздеу деп те аталады.
СУMPRODUCT функциясы
функция SUMPRODUCT (SUMPRODUCT) таңдалған массивтердің туындыларының қосындысын қайтарады:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
INDEX және MATCH функциялары
Келесі мақалада мен бұл функцияларды егжей-тегжейлі түсіндіремін, сондықтан әзірге мына формуланы көшіруге болады:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Атаулы диапазондар және қиылысу операторы
Егер сіз барлық осы күрделі Excel формулаларын меңгермесеңіз, сізге бұл көрнекі және есте қаларлық әдіс ұнауы мүмкін:
- Кестені таңдаңыз, қойындыны ашыңыз формулалар (Формулалар) және басыңыз Таңдаудан жасау (Таңдаудан жасау).
- Жәшіктерді белгілеңіз Жоғарғы қатар (жоғарыдағы жолда) және Сол жақ баған (сол жақтағы бағанда). Microsoft Excel электрондық кестенің жоғарғы жолындағы және сол жақ бағанындағы мәндерден ауқымдарға атаулар тағайындайды. Енді формулалар жасамай-ақ осы атауларды пайдаланып тікелей іздеуге болады.
- Кез келген бос ұяшыққа жазыңыз =жол_атауы баған_атауы, мысалы, келесідей:
=Лимон наурыз
… немесе керісінше:
= Мар Лимонс
Жол және баған атаулары бос орынмен бөлінуі керек екенін есте сақтаңыз, бұл жағдайда қиылысу операторы сияқты жұмыс істейді.
Атты енгізген кезде, Microsoft Excel бағдарламасы формуланы енгізген кездегідей сәйкес атаулар тізімі бар құралдар кеңесін көрсетеді.
- баспасөз кіру және нәтижені тексеріңіз
Жалпы, жоғарыда аталған әдістердің қайсысын таңдасаңыз да, екі өлшемді іздеудің нәтижесі бірдей болады:
Бір формулада бірнеше VLOOKUP пайдалану
Негізгі кесте мен іздеу кестесінде ортақ бір баған болмайды және бұл әдеттегі функцияны пайдалануға жол бермейді. VPR. Дегенмен, бізді қызықтыратын ақпарат жоқ, бірақ негізгі кесте мен іздеу кестесі бар ортақ баған бар басқа кесте бар.
Келесі мысалды қарастырайық. Бізде бағанасы бар Негізгі кесте бар SKU (жаңа), мұнда басқа кестеден сәйкес бағалары бар баған қосқыңыз келеді. Сонымен қатар, бізде 2 іздеу кестесі бар. Біріншісі (1-кесте) жаңартылған сандарды қамтиды SKU (жаңа) және өнім атаулары, ал екіншісі (2-кестені іздеу) – өнім атаулары мен ескі нөмірлер SKU (ескі).
Екінші іздеу кестесінен негізгі кестеге бағаларды қосу үшін қос деп аталатын әрекетті орындау керек VPR немесе кірістірілген VPR.
- Функцияны жаз VPR, ол кестеде өнім атауын табады Іздеу кестесі 1пайдалана отырып, Артикул, қалаған мән ретінде:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)осында Жаңа_SKU – атаулы диапазон $A:$B кестеде Іздеу кестесі 1, 2 – бұл В бағаны, онда тауарлардың атаулары (жоғарыдағы суретті қараңыз)
- Кестеден бағаларды енгізу формуласын жазыңыз Іздеу кестесі 2 белгілі өнім атауларына негізделген. Мұны істеу үшін жаңа функцияның іздеу мәні ретінде бұрын жасалған формуланы қойыңыз VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)осында баға – атаулы диапазон $A:$C кестеде Іздеу кестесі 2, 3 бағаларды қамтитын C бағаны.
Төмендегі суретте біз жасаған формуламен қайтарылған нәтиже көрсетілген:
VLOOKUP және INDIRECT көмегімен әртүрлі кестелердегі деректерді динамикалық ауыстыру
Алдымен, бір-бірімізді дұрыс түсінетінімізге көз жеткізу үшін «Әртүрлі кестелердегі деректерді динамикалық ауыстыру» өрнегі арқылы нені білдіретінімізді түсіндіріп алайық.
Бірдей пішімдегі деректері бар бірнеше парақ болған жағдайлар бар және берілген ұяшыққа енгізілген мәнге байланысты белгілі бір парақтан қажетті ақпаратты алу қажет. Менің ойымша, мұны мысалмен түсіндіру оңайырақ.
Бірдей өнімдері бар және бірдей пішімдегі бірнеше аймақтар үшін сату есептері бар деп елестетіңіз. Белгілі бір аймақ үшін сату көрсеткіштерін тапқыңыз келеді:
Егер сізде тек екі есеп болса, онда сіз функциялары бар ұятсыз қарапайым формуланы пайдалана аласыз VPR и IF (IF) іздеу үшін қажетті есепті таңдау үшін:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Қайда:
- $D $2 өнім атауын қамтитын ұяшық болып табылады. Формула басқа ұяшықтарға көшіру кезінде іздеу мәнін өзгертпеу үшін мұнда абсолютті сілтемелерді қолданатынымызды ескеріңіз.
- $D3 аймақ атауы бар ұяшық болып табылады. Біз абсолютті баған сілтемесін және салыстырмалы жол сілтемесін қолданамыз, себебі формуланы бір бағандағы басқа ұяшықтарға көшіруді жоспарлап отырмыз.
- FL_Sales и CA_Sales – сәйкес сату есептерін қамтитын кестелердің (немесе аталған ауқымдардың) атаулары. Сіз, әрине, әдеттегі парақ атауларын және ұяшық ауқымының сілтемелерін пайдалана аласыз, мысалы 'FL Sheet'!$A$3:$B$10, бірақ аталған ауқымдар әлдеқайда ыңғайлы.
Дегенмен, мұндай кестелер көп болған кезде, функция IF ең жақсы шешім емес. Оның орнына функцияны пайдалануға болады ТӘУЕЛСІЗДІК (ЖАМАУ) қажетті іздеу ауқымын қайтару үшін.
Өздеріңіз білетіндей, функция ТӘУЕЛСІЗДІК мәтін жолы арқылы берілген сілтемені қайтару үшін пайдаланылады, бұл бізге дәл қазір қажет. Сонымен, жоғарыдағы формуладағы өрнекті функциямен батыл ауыстырыңыз IF функциясымен байланыстыру ТӘУЕЛСІЗДІК. Міне, комбинация VPR и ТӘУЕЛСІЗДІК үлкен жұмыс істейді:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Қайда:
- $D $2 – бұл өнім атауы бар ұяшық, ол абсолютті сілтемеге байланысты өзгермейді.
- $D3 аймақ атауының бірінші бөлігін қамтитын ұяшық. Біздің мысалда бұл FL.
- _Сату – барлық аталған ауқымдар немесе кестелер атауының ортақ бөлігі. D3 ұяшығындағы мәнмен біріктірілгенде, ол қажетті ауқымның толық жарамды атауын құрайды. Төменде функцияны жаңадан бастағандар үшін кейбір мәліметтер берілген ТӘУЕЛСІЗДІК.
INDIRECT және VLOOKUP қалай жұмыс істейді
Алдымен функцияның синтаксисін еске түсіруге рұқсат етіңіз ТӘУЕЛСІЗДІК (ЖАНА):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Бірінші аргумент ұяшық сілтемесі (A1 немесе R1C1 мәнері), ауқым атауы немесе мәтін жолы болуы мүмкін. Екінші аргумент бірінші аргументте қандай сілтеме стилі бар екенін анықтайды:
- A1егер аргумент болса НАҒЫЗ КОД (ШЫН) немесе көрсетілмеген;
- R1C1, егер FАС Е (ЖАЛҒАН).
Біздің жағдайда сілтемеде стиль бар A1, сондықтан сіз екінші аргументті қалдырып, біріншісіне назар аудара аласыз.
Сонымен, сатылым туралы есептерімізге оралайық. Естеріңізде болса, әрбір есеп жеке парақта орналасқан бөлек кесте. Формула дұрыс жұмыс істеуі үшін кестелерді (немесе ауқымдарды) атау керек және барлық атауларда ортақ бөлік болуы керек. Мысалы, келесідей: CA_Sales, FL_Sales, TX_Sales және тағы басқа. Көріп отырғаныңыздай, «_Сату» барлық атауларда бар.
функция ТӘУЕЛСІЗДІК D бағанындағы мәнді және «_Sales» мәтіндік жолын қосады, осылайша айтады VPR қай кестеден іздеу керек. Егер D3 ұяшығында «FL» мәні болса, формула кестені іздейді FL_Sales, егер «CA» болса – кестеде CA_Sales және тағы басқа.
Функциялардың нәтижесі VPR и ТӘУЕЛСІЗДІК мынадай болады:
Егер деректер әртүрлі Excel кітаптарында орналасса, онда аталған ауқымның алдына кітаптың атын қосу керек, мысалы:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Егер функция ТӘУЕЛСІЗДІК басқа жұмыс кітабына сілтеме жасайды, бұл жұмыс кітабы ашық болуы керек. Егер ол жабық болса, функция қате туралы хабарлайды. #REF! (#SSYL!).