Функцията VLOOKUP. Използване на функцията VLOOKUP. Excel - VPR

Приложението Excel е популярно благодарение нанеговата достъпност и простота, тъй като не изисква специални знания и умения. Табличният изглед на предоставянето на информация е разбираем за всеки потребител и широка гама от инструменти, включително "Master of Functions", позволява всякакви манипулации и изчисления с предоставените данни.

Една от добре познатите формули на Excel е вертикалното гледане. Използването на функцията VLOOK на пръв поглед изглежда доста сложно, но е само в началото.

Как работи Excel

При работа с формулата VLR трябва да се вземе предвид товатя търси стойността за търсене само по колони, а не по редове. За да се използва функцията, се изисква минималният брой колони - два, максималният отсъства.

Функцията VLOOKUP търси за даден критерий,който може да има всеки формат (текст, цифров, паричен, дата и час и т.н.) в таблицата. В случай на намиране на запис, той извежда (замества) стойността, въведена в същия ред, но от необходимата колона в таблицата, т.е. съответстваща на посочения критерий. Ако желаната стойност не е намерена, се извежда грешката # N / A (в английската версия # N / A).

Необходимо е да се използва

Функцията VLOOK идва на оператора, когатя се нуждае от бързо намиране и прилагане в по-нататъшно изчисление, анализ или прогнозиране на определена стойност от таблица с големи размери. Основното при използването на тази формула е да се уверите, че избраната област за търсене е била правилно избрана. Той трябва да включва всички записи, т.е. от първия до последния.

Най-честата употреба на VLF (функцияExcel) е сравнението или добавянето на данни в две таблици, като се използва определен критерий. А диапазоните за търсене могат да бъдат големи и да настанят хиляди полета, поставени на различни листове или книги.

Функцията VLOOK е показана, как да я използвате, какИзвършете изчисления, като например в горната фигура. Тук се разглежда таблицата с размерите на продажбите на дребно в зависимост от региона и управителя. Критерият за търсене е конкретен мениджър (името и фамилното име), а търсената стойност е размерът на продажбите му.

В резултат на функцията VLOOKUP се създава нова таблица, в която конкретен издирван мениджър бързо сравнява продажната си сума.

Алгоритъмът за попълване на формулата

Формулата VLOOK се намира в раздела "Помощ за функции" и в раздела "Референции и масиви". Функционалният диалог има следния вид:

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

• Стойността за търсене е това, което трябва да намери функцията, а опциите са стойностите на клетката, адреса й, името, дадено от оператора. В нашия случай това е фамилното име и името на мениджъра.
• Таблица - диапазонът от редове и колони, в които се търси критерият.
• Колона номер - серийният му номер, който е сума от продажбите, че е резултат от формулата.
• Интервален изглед. Тя държи стойността FALSE или TRUE. И FALSE връща само точно съвпадение, TRUE - ви позволява да търсите приблизителна стойност.

Пример за използване на функцията

Функцията VLOOKUP може да има пример за използванеследните: при извършване на дейност на търговско предприятие в таблиците Excel в колона А се записва наименованието на продукта, а в колона Б - съответната цена. За да напишете изречение в колона C, трябва да намерите разходите за определен продукт, който искате да изведете в колона D.

Добър пример за организиране на маса

А	В	C	D
продукт 1	90	продукт 3	60
продукт 2	120	продукт 1	90
продукт 3	60	продукт 4	100
продукт 4	100	продукт 2	120

Формулата, написана на D, ще изглежда така: = VLP (C1, A1: B5; 2; 0), т.е. VLOOK (стойност за търсене, диапазон от данни на таблицата, колона ordinal, 0). Като четвърти аргумент, вместо 0, можете да използвате FALSE.

За да попълните таблицата с изречения, получената формула трябва да бъде копирана в цялата колона D.

За да фиксирате областта на работните данни, можетес помощта на абсолютни референции. За да направите това, знаците $ се предхождат ръчно преди азбучните и цифровите стойности на адресите на крайните леви и десни клетки на таблицата. В нашия случай формулата приема формата: = ВПР (С1; $ А $ 1: $ В $ 5; 2; 0).

Грешки при употреба

Функцията VLOOKUP не работи и след това се появява съобщение в изходната колона на резултата за грешка (# N / A или # N / A). Това се случва в такива случаи:

1. Въведена е формулата и колоната с изискваните критерии не е попълнена (в този случай колона С).
2. Колона C има стойност, която липсвав колона А (в обхвата на търсенето на данни). За да проверите наличността на желаната стойност, изберете колоната от критерии и в раздела "Редактиране" - "Намери" вмъкнете този запис, започнете търсенето. Ако програмата не го намери, тогава тя липсва.
3. Форматите на клетките на колони А и С (изискваните критерии)различен, например един текст, а другият - числен. Можете да промените клетъчния формат, ако отидете на редактирането на клетките (F2). Такива проблеми обикновено възникват при импортиране на данни от други приложения. За да се избегнат такива грешки, във формулата VLOOK могат да бъдат вградени следните функции: KEY или TEXT. Изпълнението на тези алгоритми автоматично преобразува формата на клетките.
4. В кода за функции има ненужни знаци или интервали. След това трябва внимателно да проверите формулата за наличие на входни грешки.
5. Приблизително търсене е посочено, т.е. четвъртотоаргументът на функцията VLOOK има стойност 1 или TRUE, а таблицата не е сортирана по възходяща стойност. В този случай колоната от търсените критерии трябва да бъде сортирана във възходящ ред.

Освен това, когато организирате нова обобщаваща таблица, определените критерии за търсене могат да бъдат във всякакъв ред и последователност и не е необходимо да се съдържат в пълен списък (частичен подбор).

Характеристики на използването като интервален изглед 1 или TRUE

Грешката при номер 5 е доста разпространена и е ясно илюстрирана на фигурата по-долу.

В този пример, списък на имената според номерациятасортирани не във възходящ ред, а в низходящ ред. И като интервален изглед беше използван TRUE критерият (1), който незабавно прекъсва търсенето, когато се намери стойност, по-голяма от търсената, така че се получава грешка.

Когато прилагате 1 или TRUE в четвъртия аргументНеобходимо е да се уверите, че колоната с необходимите критерии се сортира във възходящ ред. С използването на 0 или FID, тази нужда изчезва, но също така няма възможност за интервално сканиране.

Просто имайте предвид, че е особено важно да сортирате таблиците с интервали. В противен случай функцията VLOOKUP ще изпрати неправилни данни към клетките.

Други нюанси при работа с функцията VLOOKUP

За удобство при работа с такава формула можете да озаглавите обхвата на таблицата, в която се извършва търсенето (вторият аргумент), както е показано на фигурата.

В този случай площта на таблицата за продажби има право. За тази цел се избира таблица, с изключение на заглавията на колоните, и името й (вляво под лентата на разделите) се наименува.

Друг вариант - заглавие - предполага избор на набор от данни, след това отидете в менюто "Вмъкване" - "Име" - "Присвояване".

За да използвате поставените даннина друг лист от работната книга, като използвате функцията VLOOKUP, трябва да посочите местоположението на диапазона от данни във втория аргумент на формулата. Например, = VPR (Al; List2! $ A $ 1: $ B $ 5; 2; 0), където Sheet2! - е връзка към необходимия лист на книгата и $ A $ 1: $ в $ 5 - адреса на обхвата на извличането на данни.

Пример за организация на образователния процес с VPR

В Excel е доста удобно да не използва функцията VPRсамо за фирмите, занимаващи се с търговия, но и с образователните институции, за да оптимизират процеса на сравняване на учениците (учениците) с техните оценки. Примери за тези задачи са показани на фигурите по-долу.

Има две таблици със списъци с ученици. Едната с техните оценки, втората показва възрастта. Необходимо е да се сравнят двете таблици, така че заедно с възрастта на учениците да се извлекат оценките им, т.е. да се въведе допълнителна колона във втория списък.

Функцията VLOOK перфектно се справя с решението на товазадача. В графа G под заглавието "Оценки" се изписва съответната формула: = VPR (E4, B3: C13, 2, 0). Той трябва да бъде копиран в цялата колона на таблицата.

В резултат на това функцията VLR ще генерира оценки, получени от определени ученици.

Пример за организиране на търсачка с VLOOK

Друг пример за използване на функцията VLOOK еорганизацията на търсещата машина, когато в базата данни според определените критерии е необходимо да се намери съответната стойност. Така че фигурата показва списък с псевдонимите на животните и тяхната принадлежност към определен вид.

С помощта на VLOOK е създадена нова таблица, в коятое лесно да се намери външния вид на животното с името на животното. Тези търсачки са подходящи при работа с големи списъци. За да не преглеждате ръчно всички записи, можете бързо да използвате търсенето и да получите желания резултат.

</ p>