Мазмұны
- Excel бағдарламасындағы VLOOKUP функциясы – жалпы сипаттама және синтаксис
- VLOOKUP мысалдары
- VLOOKUP функциясындағы дәл немесе шамамен сәйкестік
- Excel бағдарламасында VLOOKUP – мұны есте сақтау керек!
Бүгін біз Excel бағдарламасының ең пайдалы мүмкіндіктерінің бірін сипаттайтын мақалалар сериясын бастаймыз - VPR (VLOOKUP). Бұл функция, сонымен бірге, ең күрделі және ең аз түсінікті функциялардың бірі болып табылады.
Бұл оқулықта VPR Тәжірибесіз пайдаланушылар үшін оқу үдерісін барынша түсінікті ету үшін негіздерді мүмкіндігінше қарапайым етіп көрсетуге тырысамын. Сонымен қатар, біз Excel формулалары бар бірнеше мысалдарды зерттейміз, олар функцияның ең жиі қолданылатын жағдайларын көрсетеді VPR.
Excel бағдарламасындағы VLOOKUP функциясы – жалпы сипаттама және синтаксис
Сонымен бұл не VPR? Ең алдымен, бұл Excel функциясы. Ол не істейді? Ол сіз көрсеткен мәнді іздейді және басқа бағандағы сәйкес мәнді қайтарады. Техникалық тұрғыдан алғанда, VPR берілген ауқымның бірінші бағанындағы мәнді іздейді және сол жолдағы басқа бағаннан нәтижені қайтарады.
Ең көп тараған қолданбада функция VPR берілген бірегей идентификаторды дерекқордан іздейді және оған қатысты кейбір ақпаратты дерекқордан шығарады.
Функция атауындағы бірінші әріп VPR (VLOOKUP) дегенді білдіреді Ввертикалды (Vвертикалды). Онымен ажыратуға болады VPR -дан GPR (HLOOKUP), ол − ауқымының жоғарғы жолындағы мәнді іздейді Гкөлденең (Hкөлденең).
функция VPR Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP және Excel 2000 нұсқаларында қол жетімді.
VLOOKUP функциясының синтаксисі
функция VPR (VLOOKUP) келесі синтаксиске ие:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
Көріп отырғаныңыздай, функция VPR Microsoft Excel бағдарламасында 4 опция (немесе аргумент) бар. Алғашқы үшеуі міндетті, соңғысы міндетті емес.
- іздеу_ мәні (іздеу_мәні) – ізделетін мән. Бұл мән (сан, күн, мәтін) немесе ұяшық сілтемесі (іздеу мәнін қамтитын) немесе басқа Excel функциясымен қайтарылған мән болуы мүмкін. Мысалы, бұл формула мәнді іздейді 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
Іздеу мәні ізделетін ауқымның бірінші бағанындағы ең кіші мәннен аз болса, функция VPR қате туралы хабарлайды #AT (#Жоқ).
- кесте_арасы (кесте) – деректердің екі немесе одан да көп бағандары. Есіңізде болсын, функция VPR әрқашан аргументте берілген ауқымның бірінші бағанындағы мәнді іздейді кесте_арасы (кесте). Көрінетін ауқым мәтін, күндер, сандар, логикалық мәндер сияқты әртүрлі деректерді қамтуы мүмкін. Функция регистрді сезбейді, яғни бас және кіші әріптер бірдей деп саналады. Сондықтан біздің формула мәнді іздейді 40 жасушаларында A2 дейін A15, себебі А аргументте берілген A2:B15 ауқымының бірінші бағанасы кесте_арасы (кесте):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- col_index_num (баған_саны) - табылған жолдағы мән қайтарылатын берілген ауқымдағы бағанның нөмірі. Берілген ауқымдағы ең сол жақ баған 1, екінші баған 2, үшінші баған 3 және тағы басқа. Енді сіз формуланы толығымен оқи аласыз:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)Мәнді іздейтін формула 40 диапазонда А2: А15 және B бағанынан сәйкес мәнді қайтарады (себебі B A2:B15 ауқымындағы екінші баған).
Аргументтің мәні болса col_index_num (баған_саны) мәнінен аз 1содан кейін VPR қате туралы хабарлайды # МАҢЫЗ! (#МӘН!). Ал егер ол диапазондағы бағандар санынан көп болса кесте_арасы (кесте), функция қатені қайтарады #REF! (#LINK!).
- ауқымды_іздеу (аралықты_іздеу) – нені іздеу керектігін анықтайды:
- дәл сәйкестік, аргумент тең болуы керек FALSE (ЖАЛҒАН);
- жуық сәйкестік, аргумент тең НАҒЫЗ КОД (ШЫН) немесе мүлде көрсетілмеген.
Бұл параметр міндетті емес, бірақ өте маңызды. Кейінірек осы оқулықта VPR Мен сізге дәл және жуық сәйкестіктерді табу үшін формулаларды жазу жолын түсіндіретін бірнеше мысалдарды көрсетемін.
VLOOKUP мысалдары
Функция деп үміттенемін VPR сізге біршама түсінікті болады. Енді кейбір пайдалану жағдайларын қарастырайық VPR нақты деректері бар формулаларда.
Басқа Excel парағында іздеу үшін VLOOKUP пайдалану жолы
Практикада функциясы бар формулалар VPR бір жұмыс парағындағы деректерді іздеу үшін сирек пайдаланылады. Көбінесе сіз басқа парақтан сәйкес мәндерді іздеп, шығарып аласыз.
пайдалану үшін VPR, басқа Microsoft Excel парағында іздеу, Сіз дәлелде болуыңыз керек кесте_арасы (кесте) ұяшықтар ауқымынан кейін леп белгісі бар парақ атауын көрсетіңіз. Мысалы, келесі формула диапазонды көрсетеді A2: B15 деп аталатын парақта орналасқан Парақ2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
Әрине, парақ атауын қолмен енгізу қажет емес. Тек формуланы теруді бастаңыз, ал дәлелге келгенде кесте_арасы (кесте), қажетті параққа ауысыңыз және тінтуірдің көмегімен қажетті ұяшықтар ауқымын таңдаңыз.
Төмендегі скриншотта көрсетілген формула жұмыс парағындағы А бағанындағы (бұл A1:B1 ауқымының 2-бағаны) «9-өнім» мәтінін іздейді. бағасы.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
Мәтіндік мәнді іздеу кезінде әдетте Excel формулаларында орындалатындай, оны тырнақшаға («») алу керек екенін есте сақтаңыз.
Аргумент үшін кесте_арасы (кесте) әрқашан абсолютті сілтемелерді ($ белгісімен) пайдаланған жөн. Бұл жағдайда формуланы басқа ұяшықтарға көшіру кезінде іздеу ауқымы өзгеріссіз қалады.
VLOOKUP арқылы басқа жұмыс кітабында іздеу
Жұмыс істеу үшін VPR екі Excel жұмыс кітабының арасында жұмыс істегенде, парақ атауының алдында жұмыс кітабының атын төртбұрышты жақшада көрсету керек.
Мысалы, төменде мәнді іздейтін формула берілген 40 парақта Парақ2 кітапта Сандар.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
Міне Excel бағдарламасында формула жасаудың ең оңай жолы VPRол басқа жұмыс кітабына сілтеме жасайды:
- Екі кітапты да ашыңыз. Бұл талап етілмейді, бірақ формуланы осылай жасау оңайырақ. Жұмыс кітабының атын қолмен енгізгіңіз келмейді, солай ма? Сонымен қатар, ол сізді кездейсоқ қателерден қорғайды.
- Функцияны теруді бастаңыз VPRжәне дауға келгенде кесте_арасы (кесте), басқа жұмыс кітабына ауысып, ондағы қажетті іздеу ауқымын таңдаңыз.
Төмендегі скриншот жұмыс кітабындағы ауқымға іздеу орнатылған формуланы көрсетеді PriceList.xlsx парақта бағасы.
функция VPR ізделген жұмыс кітабын жапқанда да жұмыс істейді және жұмыс кітабы файлының толық жолы төменде көрсетілгендей формула жолында пайда болады:
Егер жұмыс кітабының немесе парақтың атауында бос орындар болса, онда ол апострофпен алынуы керек:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
VLOOKUP функциясы бар формулалардағы аталған ауқымды немесе кестені пайдалану жолы
Бір іздеу ауқымын бірнеше функцияларда пайдалануды жоспарласаңыз VPR, сіз аталған ауқымды жасай аласыз және оның атын формулаға аргумент ретінде енгізе аласыз кесте_арасы (кесте).
Атаулы ауқымды жасау үшін ұяшықтарды таңдап, өріске сәйкес атауды енгізіңіз аты, формула жолының сол жағында.
Енді өнімнің бағасын табу үшін келесі формуланы жазуға болады Өнім 1:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
Көптеген ауқым атаулары бүкіл Excel жұмыс кітабы үшін жұмыс істейді, сондықтан аргумент үшін парақ атауын көрсетудің қажеті жоқ кесте_арасы (кесте), тіпті формула мен іздеу ауқымы әртүрлі жұмыс парақтарында болса да. Егер олар әртүрлі жұмыс кітаптарында болса, онда ауқым атауының алдында жұмыс кітабының атын көрсету керек, мысалы, келесідей:
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)
Демек, формула әлдеқайда анық көрінеді, келісесіз бе? Сондай-ақ, аталған ауқымдарды пайдалану абсолютті сілтемелерге жақсы балама болып табылады, себебі формуланы басқа ұяшықтарға көшірген кезде аталған ауқым өзгермейді. Бұл формуладағы іздеу ауқымы әрқашан дұрыс болып қалатынына сенімді бола алатыныңызды білдіреді.
Пәрмен арқылы ұяшықтар ауқымын толыққанды Excel электрондық кестесіне түрлендірсеңіз үстел (Кесте) қойындысы енгізу (Кірістіру), содан кейін тінтуірдің көмегімен ауқымды таңдаған кезде, Microsoft Excel бағдарламасы автоматты түрде формулаға баған атауларын (немесе бүкіл кестені таңдасаңыз, кесте атауын) қосады.
Дайын формула келесідей болады:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
Немесе тіпті келесідей болуы мүмкін:
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
Атаулы ауқымдарды пайдаланған кезде, функцияны көшіргеніңізге қарамастан, сілтемелер бірдей ұяшықтарды көрсетеді VPR жұмыс дәптері ішінде.
VLOOKUP формулаларында қойылмалы таңбаларды пайдалану
Көптеген басқа функциялар сияқты, VPR Келесі қойылмалы таңбаларды пайдалануға болады:
- Сұрақ белгісі (?) – кез келген бір таңбаны ауыстырады.
- Жұлдызша (*) – таңбалардың кез келген тізбегін ауыстырады.
Функцияларда қойылмалы таңбаларды пайдалану VPR көптеген жағдайларда пайдалы болуы мүмкін, мысалы:
- Мәтінді дәл есте сақтамаған кезде табу керек.
- Ұяшықтың мазмұнына кіретін сөзді тапқыңыз келгенде. Соны біл VPR опция қосылған сияқты тұтастай ұяшықтың мазмұны бойынша іздейді Толық ұяшық мазмұнын сәйкестендіріңіз (Толық ұяшық) стандартты Excel іздеуінде.
- Ұяшық мазмұнның басында немесе соңында қосымша бос орындарды қамтыған кезде. Мұндай жағдайда сіз формуланың неліктен жұмыс істемейтінін анықтауға тырысып, миыңызды ұзақ уақыт бойы шайқауға болады.
1-мысал: Белгілі бір таңбалармен басталатын немесе аяқталатын мәтінді іздеу
Төменде көрсетілген дерекқордан белгілі бір тұтынушыны іздегіңіз келеді делік. Сіз оның фамилиясын есіңізде сақтамайсыз, бірақ оның «ак» деп басталатынын білесіз. Міне жұмысты жақсы орындайтын формула:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
Енді дұрыс атауды тапқаныңызға сенімді болсаңыз, осы тұтынушы төлеген соманы табу үшін сол формуланы пайдалана аласыз. Ол үшін функцияның үшінші аргументін өзгерту жеткілікті VPR қажетті баған нөміріне. Біздің жағдайда бұл C бағаны (диапазондағы 3-ші):
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
Қойылмалы таңбалары бар тағы бірнеше мысалдар:
~ «Адам» әрпімен аяқталатын есімді табыңыз:
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ «Жарнама» сөзінен басталып, «сон» деп аяқталатын есімді табыңыз:
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ Тізімде 5 таңбадан тұратын бірінші атауды табамыз:
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
Жұмыс істеу үшін VPR қойылмалы таңбалармен дұрыс жұмыс істеді, төртінші дәлел ретінде сіз әрқашан пайдалануыңыз керек FALSE (ЖАЛҒАН). Іздеу ауқымында қойылмалы таңбалармен іздеу шарттарына сәйкес келетін бірнеше мән болса, онда бірінші табылған мән қайтарылады.
2-мысал: VLOOKUP формулаларында қойылмалы таңбалар мен ұяшық сілтемелерін біріктіріңіз
Енді функцияны пайдаланып іздеу жолының сәл күрделірек мысалын қарастырайық VPR ұяшықтағы мән бойынша. А бағаны лицензия кілттерінің тізімі, ал В бағаны лицензиясы бар атаулардың тізімі екенін елестетіп көріңіз. Бұған қоса, сізде C1 ұяшығында лицензия кілтінің бір бөлігі (бірнеше таңба) бар және сіз иесінің атын тапқыңыз келеді.
Мұны келесі формула арқылы жасауға болады:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
Бұл формула берілген ауқымдағы C1 ұяшығынан мәнді іздейді және В бағанынан сәйкес мәнді қайтарады. Бірінші аргументте мәтін жолын байланыстыру үшін ұяшық сілтемесінің алдында және кейін амперсанд (&) таңбасын қолданатынымызды ескеріңіз.
Төмендегі суретте көріп отырғаныңыздай, функция VPR «Джереми Хилл» қайтарады, себебі оның лицензиялық кілті C1 ұяшығындағы таңбалар тізбегін қамтиды.
Аргумент екенін ескеріңіз кесте_арасы (кесте) жоғарыдағы скриншотта ұяшықтар ауқымын көрсетудің орнына кестенің атын (Кесте 7) қамтиды. Біз алдыңғы мысалда осылай жасадық.
VLOOKUP функциясындағы дәл немесе шамамен сәйкестік
Соңында, функция үшін көрсетілген соңғы аргументті толығырақ қарастырайық VPR - ауқымды_іздеу (аралық_көрініс). Сабақтың басында айтылғандай, бұл дәлел өте маңызды. Бір формулада оның мәнімен мүлдем басқа нәтижелерді алуға болады НАҒЫЗ КОД (ШЫН) немесе FALSE (ЖАЛҒАН).
Алдымен, Microsoft Excel дәл және шамамен сәйкестіктер арқылы нені білдіретінін білейік.
- Аргумент болса ауқымды_іздеу (ауқым_іздеу) тең FALSE (ЖАЛҒАН), формула дәл сәйкестікті, яғни аргументте берілген мәнді дәл іздейді іздеу_ мәні (іздеу_мәні). Егер диапазонның бірінші бағанында tқабілетті_массив (кесте) аргументке сәйкес келетін екі немесе одан да көп мәндерді кездестіреді іздеу_ мәні (іздеу_мәні), содан кейін біріншісі таңдалады. Сәйкестік табылмаса, функция қате туралы хабарлайды #AT (#Жоқ). Мысалы, келесі формула қате туралы хабарлайды #AT (#Жоқ) егер A2:A15 ауқымында мән болмаса 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - Аргумент болса ауқымды_іздеу (ауқым_іздеу) тең НАҒЫЗ КОД (ШЫН), формула шамамен сәйкестікті іздейді. Дәлірек айтқанда, алдымен функция VPR дәл сәйкестікті іздейді, егер ешқайсысы табылмаса, шамамен біреуін таңдайды. Шамамен сәйкестік - аргументте көрсетілген мәннен аспайтын ең үлкен мән. іздеу_ мәні (іздеу_мәні).
Аргумент болса ауқымды_іздеу (ауқым_іздеу) тең НАҒЫЗ КОД (ШЫН) немесе көрсетілмеген болса, диапазонның бірінші бағанындағы мәндерді өсу ретімен, яғни ең кішіден үлкенге қарай сұрыптау керек. Әйтпесе, функция VPR қате нәтиже қайтаруы мүмкін.
Таңдаудың маңыздылығын жақсырақ түсіну үшін НАҒЫЗ КОД (ШЫНДЫҚ) немесе FALSE (FALSE), функциясы бар тағы бірнеше формулаларды қарастырайық VPR және нәтижелерін қараңыз.
1-мысал: VLOOKUP көмегімен дәл сәйкестікті табу
Естеріңізде болса, дәл сәйкестікті іздеу үшін функцияның төртінші аргументі VPR маңызды болуы керек FALSE (ЖАЛҒАН).
Ең бірінші мысалдан кестеге оралайық және қай жануардың жылдамдықпен қозғала алатынын білейік 50 сағатына миль. Бұл формула сізге қиындық тудырмайды деп ойлаймын:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
Біздің іздеу ауқымымызда (A бағанында) екі мән бар екенін ескеріңіз 50 – жасушаларда A5 и A6. Формула ұяшықтан мәнді қайтарады B5. Неліктен? Өйткені дәл сәйкестікті іздеу кезінде функция VPR ізделетініне сәйкес келетін бірінші табылған мәнді пайдаланады.
2-мысал: Шамамен сәйкестікті табу үшін VLOOKUP пайдалану
Функцияны пайдаланған кезде VPR жуық сәйкестікті іздеу үшін, яғни аргумент болған кезде ауқымды_іздеу (ауқым_іздеу) тең НАҒЫЗ КОД (ШЫН) немесе түсірілген болса, бірінші істеу керек - ауқымды бірінші баған бойынша өсу ретімен сұрыптау.
Бұл өте маңызды, себебі функция VPR берілгеннен кейінгі келесі ең үлкен мәнді қайтарады, содан кейін іздеу тоқтатылады. Егер дұрыс сұрыптауды елемейтін болсаңыз, сіз өте оғаш нәтижелерге немесе қате туралы хабарға ие боласыз. #AT (#Жоқ).
Енді сіз келесі формулалардың бірін пайдалана аласыз:
=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)
Көріп отырғаныңыздай, мен жануарлардың қайсысына ең жақын жылдамдықты білгім келеді 69 сағатына миль. Міне, нәтиже функция маған қайтарылды VPR:
Көріп отырғаныңыздай, формула нәтижені қайтарды Бөкен (Антилопа), оның жылдамдығы 61 сағатына миль, дегенмен тізімде де бар сілеусін (Гепард) жылдамдықпен жүгіретін 70 миль/сағ, ал 70 69-ге қарағанда 61-ға жақын, солай емес пе? Неліктен бұл болып жатыр? Өйткені функция VPR шамамен сәйкестікті іздеу кезінде ізделетін мәннен үлкен емес ең үлкен мәнді қайтарады.
Бұл мысалдар функциямен жұмыс істеуге біраз жарық түсіреді деп үміттенемін VPR Excel бағдарламасында және сіз оған бөтен адам ретінде қарамайсыз. Енді біз зерттеген материалды жадта жақсырақ бекіту үшін қысқаша қайталаудың зияны жоқ.
Excel бағдарламасында VLOOKUP – мұны есте сақтау керек!
- функция VPR Excel солға қарай алмайды. Ол әрқашан аргумент арқылы берілген ауқымның сол жақ бағанындағы мәнді іздейді кесте_арасы (кесте).
- Функцияда VPR барлық мәндер регистрді сезбейді, яғни кіші және үлкен әріптер баламалы.
- Егер сіз іздеп жатқан мән ізделетін ауқымның бірінші бағанындағы ең аз мәннен аз болса, функция VPR қате туралы хабарлайды #AT (#Жоқ).
- 3-аргумент болса col_index_num (баған_саны) мәнінен аз 1функция VPR қате туралы хабарлайды # МАҢЫЗ! (#МӘН!). Егер ол ауқымдағы бағандар санынан көп болса кесте_арасы (кесте), функция қате туралы хабарлайды #REF! (#LINK!).
- Аргументте абсолютті ұяшық сілтемелерін пайдаланыңыз кесте_арасы (кесте) формуланы көшіру кезінде дұрыс іздеу ауқымы сақталуы үшін. Excel бағдарламасында атаулы ауқымдарды немесе кестелерді балама ретінде пайдаланып көріңіз.
- Шамамен сәйкестікті іздеу кезінде сіз іздеп жатқан ауқымдағы бірінші баған өсу ретімен сұрыпталуы керек екенін есте сақтаңыз.
- Соңында, төртінші дәлелдің маңыздылығын есте сақтаңыз. Мәндерді пайдаланыңыз НАҒЫЗ КОД (ШЫНДЫҚ) немесе FALSE (ЖАЛҒАН) әдейі айтып, көп бас ауруынан құтыласыз.
Біздің функционалдық оқулықтың келесі мақалаларында VPR Excel бағдарламасында біз әртүрлі есептеулерді қолдану сияқты кеңейтілген мысалдарды үйренеміз VPR, бірнеше бағандардан мәндерді шығару және т.б. Осы оқулықты оқығаныңыз үшін рахмет және келесі аптада тағы кездесеміз деп үміттенемін!