мазмуну
- 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 вариант (же аргумент) бар. Биринчи үчөө милдеттүү, акыркысы милдеттүү.
- search_value (lookup_value) – Издөө керек болгон маани. Бул маани (сан, дата, текст) же уяча шилтемеси (издөө маанисин камтыган) же башка Excel функциясы тарабынан кайтарылган маани болушу мүмкүн. Мисалы, бул формула маанини издейт 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
Эгерде издөө мааниси изделип жаткан диапазондун биринчи тилкесиндеги эң кичине мааниден аз болсо, функция VPR ката жөнүндө кабарлайт #AT (#Жок).
- table_array (таблица) – эки же андан көп маалымат мамычасы. Эсиңизде болсун, функция VPR аргументте берилген диапазондун биринчи тилкесиндеги маанини дайыма издейт table_array (таблица). Көрүнүүчү диапазон текст, даталар, сандар, логикалык маанилер сыяктуу ар кандай маалыматтарды камтышы мүмкүн. Функция чоң жана кичине тамгалар бирдей каралат дегенди билдирет. Ошентип, биздин формула маанини издейт 40 тартып клеткаларда A2 үчүн A15, анткени А аргументте берилген A2:B15 диапазонунун биринчи тилкеси table_array (таблица):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- col_index_num (column_number) - табылган катардагы маани кайтарыла турган берилген диапазондогу тилкенин саны. Берилген диапазондогу эң сол тилке 1, экинчи тилке болуп саналат 2, үчүнчү тилке болуп саналат 3 жана башка. Эми сиз формуланы толугу менен окуй аласыз:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)Наркы издеп жаткан формула 40 диапазондо A2:A15 жана В мамычасынан тиешелүү маанини кайтарат (анткени B A2:B15 диапазонундагы экинчи тилке).
Аргументтин мааниси болсо col_index_num (мамыча_саны) аз 1ошондо VPR ката жөнүндө кабарлайт #VALUE! (#VALUE!). Ал эми ал диапазондогу тилкелердин санынан көп болсо table_array (таблица), функция катаны кайтарат #РЕФ! (#LINK!).
- диапазонду_издөө (аралык_издөө) – эмнени издөө керектигин аныктайт:
- так дал келүү, аргумент бирдей болушу керек ЖАЛГАН (ЖАЛГАН);
- болжолдуу дал келүү, аргумент барабар ЧЫНЫГЫ КОД (ЧЫН) же такыр көрсөтүлгөн эмес.
Бул параметр милдеттүү эмес, бирок абдан маанилүү. Кийинчерээк бул окуу куралында VPR Мен сизге так жана болжолдуу дал келүүлөрдү табуу үчүн формулаларды кантип жазууну түшүндүргөн бир нече мисалдарды көрсөтөм.
VLOOKUP Мисалдар
Функция деп үмүттөнөм VPR сага бир аз түшүнүктүү болуп калат. Эми кээ бир колдонуу учурларын карап көрөлү VPR реалдуу маалыматтар менен формулаларда.
Башка Excel барагында издөө үчүн VLOOKUP кантип колдонсо болот
Практикада функциясы бар формулалар VPR бир эле иш барагында маалыматтарды издөө үчүн сейрек колдонулат. Көбүнчө, сиз башка барактан тиешелүү маанилерди издеп, чыгарып аласыз.
колдонуу үчүн VPR, башка Microsoft Excel барагында издөө, Сиз аргументте болушуңуз керек table_array (таблица) барактын атын илеп белгиси менен, андан кийин уячалардын диапазону менен көрсөтүңүз. Мисалы, төмөнкү формула диапазонун көрсөтүп турат A2: B15 аттуу баракта турат Sheet2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
Албетте, барактын атын кол менен киргизүү зарыл эмес. Жөн гана формуланы терип баштаңыз, ал эми аргументке келгенде table_array (таблица), керектүү баракка которулуңуз жана чычкан менен каалаган уячалардын диапазонун тандаңыз.
Төмөнкү скриншотто көрсөтүлгөн формула иш барагындагы А тилкесинде (ал A1:B1 диапазонунун 2-тилкеси) "9-өнүм" текстин издейт баалар.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
Тексттин маанисин издеп жатканда, адатта Excel формулаларында жасалгандай, аны тырмакчага («») алуу керек экенин унутпаңыз.
Аргумент үчүн table_array (таблица) дайыма абсолюттук шилтемелерди колдонуу керек ($ белгиси менен). Бул учурда формуланы башка уячаларга көчүрүү учурунда издөө диапазону өзгөрүүсүз калат.
VLOOKUP менен башка иш китебинен издеңиз
иштеши үчүн VPR эки Excel жумушчу китебинин ортосунда иштесеңиз, иш китебинин атын барак аталышынын алдында чарчы кашаанын ичинде көрсөтүшүңүз керек.
Мисалы, төмөндө маанини издеген формула келтирилген 40 баракта Sheet2 Китепте Numbers.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
Бул жерде Excelде формула түзүүнүн эң оңой жолу VPRбашка иш китебине шилтемелер:
- Эки китепти тең ачыңыз. Бул талап кылынбайт, бирок формуланы ушундай жол менен түзүү оңой. Жумуш китебинин атын кол менен киргизгиңиз келбейт, туурабы? Мындан тышкары, ал сизди кокус каталардан коргойт.
- Функцияны терип баштаңыз VPRжана талаш-тартыштарга келгенде table_array (таблица), башка иш китебине которулуңуз жана андагы керектүү издөө диапазонун тандаңыз.
Төмөндөгү скриншот жумушчу китебиндеги диапазонго коюлган издөө менен формуланы көрсөтөт PriceList.xlsx баракта баалар.
милдети VPR изделген иш китебин жапканыңызда да иштейт жана жумушчу китеп файлына толук жол формула тилкесинде пайда болот, төмөндө көрсөтүлгөндөй:
Эгерде иш китебинин же барактын аталышында боштуктар болсо, анда ал апостроф менен тиркелүүгө тийиш:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
VLOOKUP менен формулаларда аталган диапазону же таблицаны кантип колдонуу керек
Эгерде сиз бир эле издөө диапазонун бир нече функцияда колдонууну пландаштырсаңыз VPR, сиз аталган диапазонду түзүп, анын атын формулага аргумент катары киргизсеңиз болот table_array (таблица).
Аты аталган диапазонду түзүү үчүн, жөн гана уячаларды тандап, талаага ылайыктуу атты киргизиңиз биринчи аты, формула тилкесинин сол жагында.
Эми буюмдун баасын табуу үчүн төмөнкү формуланы жазсаңыз болот Продукт 1:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
Көпчүлүк диапазон аттары бүтүндөй Excel иш китеби үчүн иштейт, андыктан аргумент үчүн барактын атын көрсөтүүнүн кереги жок table_array (таблица), формула жана издөө диапазону башка иш барактарында болсо да. Эгерде алар ар кандай иш китептеринде болсо, анда диапазондун аталышынан мурун иш китебинин атын көрсөтүү керек, мисалы, төмөнкүдөй:
=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 формулаларында Wildcards колдонуу
Башка көптөгөн функциялар сыяктуу эле, VPR Төмөнкү белгилерди колдоно аласыз:
- Суроо белгиси (?) – кандайдыр бир белгини алмаштырат.
- Жылдызча (*) – символдордун каалаган ырааттуулугун алмаштырат.
Функцияларда Wildcards колдонуу 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 төртүнчү аргумент катары сиз ар дайым колдонушуңуз керек ЖАЛГАН (ЖАЛГАН). Эгерде издөө диапазонунда издөө шарттарына айкалыштыруу белгилери менен дал келген бирден ашык маани камтылса, анда табылган биринчи маани кайтарылат.
2-мисал: VLOOKUP формулаларында уяча шилтемелерин жана айкалыштыруу белгилерин бириктириңиз
Эми функцияны колдонуп издөөнүн бир аз татаалыраак мисалын карап көрөлү VPR уячадагы мааниси боюнча. А тилкеси лицензиялык ачкычтардын тизмеси, ал эми В тилкеси лицензияга ээ болгон ысымдардын тизмеси экенин элестетиңиз. Мындан тышкары, сизде C1 уячасында кандайдыр бир лицензиялык ачкычтын бир бөлүгү (бир нече символдор) бар жана сиз ээсинин атын тапкыңыз келет.
Бул төмөнкү формула аркылуу жасалышы мүмкүн:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
Бул формула берилген диапазондогу C1 уячасынын маанисин издеп, В тилкесиндеги тиешелүү маанини кайтарат. Биринчи аргументте биз текст сапты байланыштыруу үчүн уяча шилтемесинен мурун жана андан кийин амперсанд (&) символун колдонорубузга көңүл буруңуз.
Төмөнкү сүрөттө көрүнүп тургандай, функция VPR "Джереми Хиллди" кайтарат, анткени анын лицензиялык ачкычы C1 уячасындагы символдордун ырааттуулугун камтыйт.
Аргументке көңүл буруңуз table_array (таблица) жогорудагы скриншотто уячалардын диапазонун көрсөтүүнүн ордуна таблицанын атын (таблица 7) камтыйт. Бул биз мурунку мисалда кылганбыз.
VLOOKUP функциясында так же болжолдуу дал келүү
Акыр-аягы, функция үчүн көрсөтүлгөн акыркы аргументти кененирээк карап чыгалы VPR - диапазонду_издөө (аралык_көрүү). Сабактын башында айтылгандай, бул аргумент абдан маанилүү. Бир эле формулада анын мааниси менен такыр башка натыйжаларды ала аласыз ЧЫНЫГЫ КОД (ЧЫН) же ЖАЛГАН (ЖАЛГАН).
Биринчиден, Microsoft Excel так жана болжолдуу дал келүүлөр менен эмнени билдирерин карап көрөлү.
- Аргумент болсо диапазонду_издөө (аралык_издөө) барабар ЖАЛГАН (ЖАЛГАН), формула так дал келүүнү издейт, башкача айтканда, аргументте берилген мааниге дал келет. search_value (издөө_маани). Эгерде диапазондун биринчи тилкесинде тмүмкүн_массив (таблица) аргументке дал келген эки же андан көп баалуулуктарга жолугат search_value (издөө_маани), анда биринчиси тандалат. Эгерде эч кандай дал келбесе, функция ката жөнүндө кабарлайт #AT (#Жок). Мисалы, төмөнкү формула ката жөнүндө кабарлайт #AT (#Жок) эгерде A2:A15 диапазонунда маани жок болсо 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - Аргумент болсо диапазонду_издөө (аралык_издөө) барабар ЧЫНЫГЫ КОД (ЧЫН), формула болжолдуу дал келүүнү издейт. Тагыраак айтканда, биринчи функция VPR так дал келүүнү издейт, эгер эч ким табылбаса, болжолдуу бирин тандайт. Болжолдуу дал келүү - бул аргументте көрсөтүлгөн мааниден ашпаган эң чоң маани. search_value (издөө_маани).
Аргумент болсо диапазонду_издөө (аралык_издөө) барабар ЧЫНЫГЫ КОД (ЧЫН) же көрсөтүлбөсө, анда диапазонун биринчи тилкесиндеги маанилер өсүү тартибинде, башкача айтканда, кичинеден чоңго чейин иреттелиши керек. Болбосо, функция VPR туура эмес жыйынтыкты кайтарышы мүмкүн.
Тандоо маанилүүлүгүн жакшыраак түшүнүү үчүн ЧЫНЫГЫ КОД (ЧЫНДЫК) же ЖАЛГАН (ЖАЛГАН), келгиле, функция менен дагы бир нече формулаларды карап көрөлү VPR жана натыйжаларды карагыла.
1-мисал: VLOOKUP менен так дал келүүнү табуу
Эсиңизде болгондой, так дал келүүнү издөө үчүн функциянын төртүнчү аргументи VPR маанилүү болушу керек ЖАЛГАН (ЖАЛГАН).
Келгиле, биринчи мисалдан эле таблицага кайрылып, кайсы жаныбардын ылдамдык менен кыймылдай ала турганын билели 50 саатына миль. Бул формула сизге эч кандай кыйынчылык жаратпайт деп ишенем:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
Биздин издөө диапазону (А тилкеси) эки маанини камтыйт 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 солго карай албайт. Ал ар дайым аргумент тарабынан берилген диапазондун эң сол тилкесиндеги маанини издейт table_array (таблица).
- Функцияда VPR бардык маанилер регистрди сезбейт, башкача айтканда, кичине жана чоң тамгалар эквиваленттүү.
- Эгер сиз издеп жаткан маани изделип жаткан диапазондун биринчи тилкесиндеги минималдуу мааниден аз болсо, функция VPR ката жөнүндө кабарлайт #AT (#Жок).
- 3-аргумент болсо col_index_num (мамыча_саны) аз 1милдети VPR ката жөнүндө кабарлайт #VALUE! (#VALUE!). Эгерде ал диапазондогу тилкелердин санынан көп болсо table_array (таблица), функция ката жөнүндө кабарлайт #РЕФ! (#LINK!).
- Аргументте абсолюттук уяча шилтемелерин колдонуңуз table_array (таблица) формуланы көчүрүп жатканда туура издөө диапазону сакталышы үчүн. Альтернатива катары Excelде аталган диапазондорду же таблицаларды колдонуп көрүңүз.
- Болжолдуу дал келүүчү издөөнү аткарып жатканда, сиз издеп жаткан диапазондогу биринчи тилке өсүү тартибинде иреттелиши керектигин унутпаңыз.
- Акырында, төртүнчү аргументтин маанилүүлүгүн эстегиле. Маанилерди колдонуңуз ЧЫНЫГЫ КОД (ЧЫНДЫК) же ЖАЛГАН (ЖАЛГАН) атайылап жана көп баш оорудан арыласыз.
Функция боюнча окуу куралыбыздын кийинки макалаларында VPR Excelде биз ар кандай эсептөөлөрдү колдонуу сыяктуу өркүндөтүлгөн мисалдарды үйрөнөбүз VPR, бир нече тилкеден маанилерди алуу жана башкалар. Бул окуу куралын окуганыңыз үчүн рахмат жана сизди кийинки аптада дагы көрөбүз деп үмүттөнөм!