Функција VLOOKUP во Excel – Водич за почетници: синтакса и примери

содржина

Функција VLOOKUP во Excel – општ опис и синтакса
- Синтакса на функцијата VLOOKUP
VLOOKUP Примери
Точно или приближно совпаѓање во функцијата VLOOKUP
- Пример 1: Наоѓање точно совпаѓање со VLOOKUP
- Пример 2: Користење на VLOOKUP за наоѓање приближна совпаѓање
VLOOKUP во Excel - треба да го запомните ова!

Денес започнуваме серија написи кои опишуваат една од најкорисните карактеристики на Excel − VPR (VLOOKUP). Оваа функција, во исто време, е една од најкомплексните и најмалку разбраните.

Во ова упатство за VPR Ќе се обидам да ги изложам основите што е можно поедноставно за да го направам процесот на учење што е можно појасен за неискусните корисници. Дополнително, ќе проучиме неколку примери со формули на Excel кои ќе ги покажат најчестите случаи на употреба на функцијата VPR.

Општ опис и синтакса
VLOOKUP Примери
- Како да користите VLOOKUP за пребарување во друг лист на Excel
- Пребарувајте во друга работна книга со VLOOKUP
- Како да користите именуван опсег или табела во формули со VLOOKUP
- Користење на џокери во формули VLOOKUP
Точно или приближно совпаѓање во функцијата VLOOKUP
VLOOKUP во Excel - треба да го запомните ова!

Функција VLOOKUP во Excel – општ опис и синтакса

Па што е тоа 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 опции (или аргументи). Првите три се задолжителни, последното не е задолжително.

вредност на пребарување (lookup_value) – Вредноста што треба да се бара. Ова може да биде вредност (број, датум, текст) или референца на ќелија (која ја содржи вредноста за пребарување) или вредност вратена од некоја друга функција на Excel. На пример, оваа формула ќе ја бара вредноста 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Ако вредноста за пребарување е помала од најмалата вредност во првата колона од опсегот што се бара, функцијата VPR ќе пријави грешка #AT (# N/A).

низа на маса (табела) – две или повеќе колони податоци. Запомнете, функцијата VPR секогаш ја бара вредноста во првата колона од опсегот даден во аргументот низа на маса (табела). Опсегот што може да се гледа може да содржи различни податоци, како што се текст, датуми, бројки, булови. Функцијата не е чувствителна на букви, што значи дека големите и малите букви се сметаат за исти. Така, нашата формула ќе ја бара вредноста 40 во клетките од A2 до A15, бидејќи A е првата колона од опсегот A2:B15 дадена во аргументот низа на маса (табела):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

коло_индекс_број (колона_број) е бројот на колоната во дадениот опсег од кој ќе се врати вредноста во пронајдениот ред. Најлевата колона во дадениот опсег е 1, втората колона е 2, третата колона е 3 и така натаму. Сега можете да ја прочитате целата формула:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Формула која бара вредност 40 во опсегот A2: A15 и ја враќа соодветната вредност од колоната B (бидејќи B е втората колона во опсегот A2:B15).

Ако вредноста на аргументот коло_индекс_број (број_колона) помалку од 1потоа VPR ќе пријави грешка #VALUE! (#ВРЕДНОСТ!). И ако е повеќе од бројот на колони во опсегот низа на маса (табела), функцијата ќе врати грешка # РЕФ! (#ЛИНК!).

опсег_преглед (range_lookup) – одредува што да барате:
- точно совпаѓање, аргументот мора да биде еднаков НЕТОЧНО (НЕТОЧНО);
- приближно совпаѓање, аргументот е еднаков ВИСТИНСКИ КОД (ТОЧНО) или воопшто не е одредено.
Овој параметар е опционален, но многу важен. Подоцна во ова упатство на VPR Ќе ви покажам неколку примери кои објаснуваат како да пишувате формули за наоѓање точни и приближни совпаѓања.

VLOOKUP Примери

Се надевам на функцијата VPR да ти стане малку појасно. Сега да погледнеме некои случаи на употреба VPR во формули со реални податоци.

Како да користите VLOOKUP за пребарување во друг лист на Excel

Во пракса, формули со функција VPR ретко се користат за пребарување на податоци на истиот работен лист. Почесто отколку не, ќе барате и ќе ги враќате соодветните вредности од друг лист.

За да се користи VPR, барај во друг лист на Microsoft Excel, Мора во аргументот низа на маса (табела) наведете го името на листот со извичник проследено со опсег на ќелии. На пример, следната формула покажува дека опсегот А2: Б15 е на лист со име Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Се разбира, името на листот не мора да се внесува рачно. Само почнете да ја пишувате формулата, и кога станува збор за аргументот низа на маса (табела), префрлете се на саканиот лист и изберете го саканиот опсег на ќелии со глувчето.

Формулата прикажана на екранот подолу го бара текстот „Производ 1“ во колоната А (тоа е првата колона од опсегот A1:B2) на работниот лист цени.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Запомнете дека кога барате текстуална вредност, мора да ја ставите во наводници („“), како што обично се прави во формулите на Excel.

За аргумент низа на маса (табела) пожелно е секогаш да се користат апсолутни референци (со знакот $). Во овој случај, опсегот на пребарување ќе остане непроменет при копирање на формулата во други ќелии.

Пребарувајте во друга работна книга со VLOOKUP

Да функционира VPR работено помеѓу две работни книги на Excel, треба да го наведете името на работната книга во квадратни загради пред името на листот.

На пример, подолу е формула која ја бара вредноста 40 на листот Sheet2 во книгата Броеви.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Ова е најлесниот начин да креирате формула во Excel со VPRкоја се поврзува со друга работна книга:

Отворете ги двете книги. Ова не е потребно, но полесно е да се создаде формула на овој начин. Не сакате рачно да го внесете името на работната книга, нели? Покрај тоа, ќе ве заштити од случајни печатни грешки.
Почнете да пишувате функција VPRа кога станува збор за расправијата низа на маса (табела), префрлете се на друга работна книга и изберете го бараниот опсег за пребарување во неа.

Сликата од екранот подолу ја прикажува формулата со поставено пребарување на опсег во работната книга Ценовник.xlsx на листот цени.

функција VPR ќе работи дури и кога ќе ја затворите пребаруваната работна книга и целосната патека до датотеката со работната книга ќе се појави во лентата со формули, како што е прикажано подолу:

Ако името на работната книга или листот содржи празни места, тогаш тоа мора да биде затворено во апострофи:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Како да користите именуван опсег или табела во формули со VLOOKUP

Ако планирате да го користите истиот опсег на пребарување во повеќе функции VPR, можете да креирате именуван опсег и да го внесете неговото име во формулата како аргумент низа на маса (табела).

За да креирате именуван опсег, едноставно изберете ги ќелиите и внесете соодветно име во полето Firstname, лево од лентата со формула.

Сега можете да ја запишете следната формула за пронаоѓање на цената на производот Производ 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: Барате текст што започнува или завршува со одредени знаци

Да речеме дека сакате да пребарувате за одреден клиент во базата на податоци прикажана подолу. Не се сеќавате на неговото презиме, но знаете дека почнува со „ack“. Еве формула која ќе ја заврши работата одлично:

=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 во дадениот опсег и ја враќа соодветната вредност од колоната B. Забележете дека во првиот аргумент, користиме знак (&) пред и по референцата на ќелијата за да ја поврземе текстуалната низа.

Како што можете да видите на сликата подолу, функцијата VPR враќа „Jeremy Hill“ бидејќи неговиот клуч за лиценца ја содржи низата знаци од ќелијата C1.

Забележете дека аргументот низа на маса (табела) во горната слика од екранот го содржи името на табелата (Табела7) наместо да специфицира опсег на ќелии. Ова е она што го направивме во претходниот пример.

Точно или приближно совпаѓање во функцијата VLOOKUP

И, конечно, да го разгледаме подетално последниот аргумент што е наведен за функцијата VPR - опсег_преглед (интервал_преглед). Како што беше споменато на почетокот на лекцијата, овој аргумент е многу важен. Можете да добиете сосема различни резултати во истата формула со нејзината вредност ВИСТИНСКИ КОД (ВИСТИНА) или НЕТОЧНО (НЕТОЧНО).

Прво, ајде да дознаеме што значи Microsoft Excel под точни и приближни совпаѓања.

Доколку аргументот опсег_преглед (опсег_преглед) е еднаков на НЕТОЧНО (FALSE), формулата бара точно совпаѓање, односно точно истата вредност како што е дадена во аргументот вредност на пребарување (барање_вредност). Ако во првата колона од опсегот тможе_низа (табела) наидува на две или повеќе вредности кои одговараат на аргументот вредност на пребарување (search_value), тогаш ќе се избере првиот. Ако не се најдат совпаѓања, функцијата ќе пријави грешка #AT (# N/A). На пример, следнава формула ќе пријави грешка #AT (# N/A) ако нема вредност во опсегот A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Доколку аргументот опсег_преглед (опсег_преглед) е еднаков на ВИСТИНСКИ КОД (ТОЧНО), формулата бара приближно совпаѓање. Поточно, прво функцијата VPR бара точно совпаѓање и ако не се најде, избира приближна. Приближно совпаѓање е најголемата вредност што не ја надминува вредноста наведена во аргументот. вредност на пребарување (барање_вредност).

Доколку аргументот опсег_преглед (опсег_преглед) е еднаков на ВИСТИНСКИ КОД (ТОЧНО) или не е наведено, тогаш вредностите во првата колона од опсегот треба да се подредат по растечки редослед, односно од најмали до најголеми. Во спротивно, функцијата VPR може да врати погрешен резултат.

За подобро да се разбере важноста на изборот ВИСТИНСКИ КОД (ВИСТИНА) или НЕТОЧНО (FALSE), ајде да погледнеме уште неколку формули со функцијата 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 (# N/A).

Сега можете да користите една од следниве формули:

=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, и повеќе не ја гледате како аутсајдер. Сега не е повредено накратко да ги повториме клучните точки од материјалот што го проучувавме за подобро да го поправиме во меморијата.

VLOOKUP во Excel - треба да го запомните ова!

функција VPR Excel не може да изгледа лево. Секогаш ја бара вредноста во најлевата колона од опсегот дадена со аргументот низа на маса (табела).
Во функција VPR сите вредности се неосетливи на големи букви, односно малите и големите букви се еквивалентни.
Ако вредноста што ја барате е помала од минималната вредност во првата колона од опсегот што се бара, функцијата VPR ќе пријави грешка #AT (# N/A).
Ако 3-ти аргумент коло_индекс_број (број_колона) помалку од 1функција VPR ќе пријави грешка #VALUE! (#ВРЕДНОСТ!). Ако е поголем од бројот на колони во опсегот низа на маса (табела), функцијата ќе пријави грешка # РЕФ! (#ЛИНК!).
Користете апсолутни референци на ќелиите во аргументот низа на маса (табела) за да се зачува правилниот опсег на пребарување при копирање на формулата. Обидете се да користите именувани опсези или табели во Excel како алтернатива.
Кога правите приближно пребарување за совпаѓање, запомнете дека првата колона во опсегот што го барате мора да биде подредена во растечки редослед.
Конечно, запомнете ја важноста на четвртиот аргумент. Користете вредности ВИСТИНСКИ КОД (ВИСТИНА) или НЕТОЧНО (НЕТОЧНО) намерно и ќе се ослободите од многу главоболки.

Во следните написи од нашето упатство за функции VPR во Excel, ќе научиме понапредни примери, како што се извршување на различни пресметки користејќи VPR, извлекување вредности од повеќе колони и многу повеќе. Ви благодариме што го прочитавте ова упатство и се надевам дека ќе се видиме повторно следната недела!