01 од 03
Најдете приближни резултати на податоци со VLOOKUP на Excel
Како функционира VLOOKUP функцијата
Функцијата VLOOKUP на Excel, која се залага за вертикален преглед, може да се користи за да се пронајдат одредени информации кои се наоѓаат во табела на податоци или база на податоци.
VLOOKUP нормално враќа едно поле на податоци како негов излез. Како тоа го прави ова:
- Давате име или lookup_value што му кажува на VLOOKUP во кој ред или запис на табелата со податоци да ги бара саканите податоци
- Го снабдувате бројот на колоната - познат како col_index_num - на податоците што ги барате
- Функцијата бара lookup_value во првата колона од табелата со податоци
- VLOOKUP потоа ги лоцира и враќа информациите што ги барате од друго поле од истата евиденција користејќи го дадениот број на колоната
Сортирање на податоците прво
Иако не е секогаш потребно, обично е најдобро да се сортира опсегот на податоци што VLOOKUP ги бара во растечки редослед со користење на првата колона од опсегот за клучот за сортирање.
Ако податоците не се подредени, VLOOKUP може да врати неточен резултат.
Синтаксата и аргументите на VLOOKUP функцијата
Синтаксата на функцијата се однесува на изгледот на функцијата и го вклучува името на функцијата, загради и аргументи .
Синтаксата за функцијата VLOOKUP е:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
lookup _value - (задолжително) вредноста за пребарување - како што се количината продадена на сликата погоре
table_array - (задолжително) Ова е табелата на податоци кои VLOOKUP ги бара да ги пронајдат информациите што ги следите.
- Table_array мора да содржи најмалку две колони податоци
- Првата колона вообичаено содржи lookup_value
col_index_num - (задолжително) бројот на колоната на вредноста што ја сакате.
- Броењето започнува со колоната search_key како колона 1
- Ако col_index_num е поставен на број поголем од бројот на колони избрани во аргументот table_array #REF! грешка се враќа од функцијата
range_lookup - (опционално) покажува дали опсегот е подреден по растечки редослед или не.
- Податоците во првата колона се користат како клуч за сортирање
- Булева вредност - TRUE или FALSE се единствените прифатливи вредности
- Ако е испуштено, вредноста е стандардно поставена на TRUE
- Ако е поставено на TRUE или изоставено и првата колона од опсегот не е подредена по растечки редослед, може да се појави неточен резултат
- Ако е поставено на TRUE или исфрлено и точното совпаѓање за вредноста на пребарување не е пронајдена, најблискиот натпревар помал по големина или вредност се користи како search_key
- Ако е поставено на FALSE, VLOOKUP прифаќа само точно пребарување за вредноста на пребарувањето . Ако постојат повеќе соодветни вредности, се враќа првата соодветна вредност
- Ако е поставено на FALSE и не се пронајде никаква соодветна вредност за search_key , функцијата # N / A ја враќа функцијата
Пример: Пронајдете ја стапката на попуст за купената количина
Примерот на сликата погоре користи функцијата VLOOKUP да ја пронајде дисконтната стапка што се разликува во зависност од количината на купени предмети.
Примерот покажува дека попустот за купување на 19 предмети е 2%. Ова е затоа што колоната Quantity содржи опсези на вредности. Како резултат на тоа, VLOOKUP не може да најде точен натпревар. Наместо тоа, мора да се најде приближен натпревар со цел да се врати точната дисконтна стапка.
За да најдете приближни натпревари:
- сортирање на податоците во table_array во растечки редослед;
- поставете аргумент range_lookup на TRUE
Во примерот, следнава формула која ја содржи функцијата VLOOKUP се користи за да се најде попуст за количините на купените стоки.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, ИСТИНА)
И покрај тоа што оваа формула може да се внесе во лист на лист, друга опција, како што се користи со чекорите наведени подолу, е да се користи дијалогот за функцијата за да ги внесе своите аргументи.
- Користењето на дијалогот често го олеснува правилно внесување на аргументите на функцијата.
Отворање на диалогот VLOOKUP
Чекорите кои се користат за да влезат во функцијата VLOOKUP прикажана на сликата погоре во ќелија Б2 се:
- Кликнете на ќелијата B2 за да ја направите активната ќелија - локацијата каде што се прикажани резултатите од функцијата VLOOKUP
- Кликнете на табулаторот Формула .
- Изберете Пронајди и референца од лентата за да ја отворите паѓачката листа на функции
- Кликнете на VLOOKUP во листата за да го прикажете дијалогот за функцијата
02 од 03
Внесување на аргументите на VLOOKUP функцијата на Excel
Укажувајќи на референци на клетките
Аргументите за функцијата VLOOKUP се внесуваат во одделни линии на дијалогот како што е прикажано на сликата погоре.
Референците на ќелиите што треба да се користат како аргументи можат да бидат внесени во точната линија или, како што е направено во чекорите подолу, укажувајќи, што подразбира нагласување на саканиот опсег на ќелии со покажувачот на глувчето, може да се користи за внесување во дијалогот .
Предностите на користење на посочување вклучуваат:
- Тоа е побрзо од пишување;
- Помалку грешки се прават влезни во точни референци на клетките.
Користење релативни и апсолутни референци на клетки со аргументи
Не е невообичаено да се користат повеќе копии од VLOOKUP за да се вратат различни информации од истата табела на податоци. За полесно да го направите ова, често VLOOKUP може да се копира од една ќелија во друга. Кога функциите се копираат во други ќелии, мора да се внимава да се добијат точни референци на ќелиите со оглед на новата локација на функцијата.
На сликата погоре, знаците за долар ( $ ) ги опкружуваат референтните ќелии за аргументот table_array, што укажува на тоа дека тие се апсолутни референци на ќелиите , што значи дека тие нема да се менуваат ако функцијата е копирана во друга ќелија. Ова е пожелно, бидејќи повеќе копии на VLOOKUP сите ќе се однесуваат на иста маса на податоци како извор на информации.
Референцата на ќелијата што се користи за lookup_value, од друга страна , не е опкружена со знаци за долар, што ја прави релативна референтна ќелија. Релативните референци на ќелиите се менуваат кога се копираат за да ја одразат нивната нова локација во однос на позицијата на податоците што тие ги референцираат.
Внесување на аргументите за функции
- Кликнете на линијата Lookup _value во полето за дијалог VLOOKUP
- Кликнете на ќелијата C2 во работниот лист за да ја внесете оваа референца на ќелијата како аргумент search_key
- Кликнете на линијата Table_array на дијалогот
- Означете ги ќелиите C5 до D8 на работниот лист за да го внесете овој опсег како аргумент Table_array - насловите на табелата не се вклучени
- Притиснете го копчето F4 на тастатурата за да го смените опсегот на апсолутни референци на ќелиите
- Кликнете на линијата Col_index_num од дијалогот
- Внесете 2 на оваа линија како аргумент Col_index_num , бидејќи стапките на попуст се наоѓаат во колона 2 од аргументот Table_array
- Кликнете на линијата Range_lookup од дијалоговото поле
- Внесете го зборот True како Range_lookup аргумент
- Притиснете го копчето Enter на тастатурата за да го затворите дијалогот и да се вратите на работниот лист
- Одговорот 2% (дисконтната стапка за купената количина) треба да се појави во ќелијата Д2 на работниот лист
- Кога ќе кликнете на ќелијата D2, целосната функција = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) се појавува на лентата со формули над работниот лист
Зошто VLOOKUP се вратил 2% како резултат
- Во примерот, колоната Quantity не содржи точно совпаѓање за вредноста на search_key од 19.
- Бидејќи аргументот is_sorted е поставен на TRUE, VLOOKUP ќе најде приближно совпаѓање со вредноста на search_key .
- Најблиската вредност во големина која сè уште е помала од вредноста на search_key од 19 е 11.
- VLOOKUP, според тоа, го бара попустниот процент во редот кој содржи 11, и како резултат на тоа, враќа есконтната стапка од 2%.
03 од 03
Excel VLOOKUP не работи: # N / A и #REF Грешки
Пораки за грешки VLOOKUP
Следниве грешки се поврзани со VLOOKUP.
A # N / A ("вредност не е достапна") Грешка е прикажана Ако:
- Lookup _value не е пронајден во првата колона на аргументот за опсег
- Аргументот Table_array е неточен. На пример, аргументот може да содржи празни колони од левата страна од опсегот
- Аргументот Range_lookup е поставен на FALSE и точното совпаѓање за аргументот search_key не може да се најде во првата колона од опсегот
- Аргументот Range_lookup е поставен на TRUE и сите вредности во првата колона од опсегот се поголеми од search_key
#REF! ("повикување надвор од опсегот") Грешка е прикажана Ако:
- Аргументот Col_index_num е поголем од бројот на колони во Table_array