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

Програма за кандидатстване

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

Как работи Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

За да определите областта на работния обхват на данните, можете да използвате абсолютни препратки. За да направите това, знаците $ се предхождат ръчно преди азбучните и цифровите стойности на адресите на крайните леви и десни клетки на таблицата. В нашия случай формулата приема формата: = VPR (C1 - $ A $ 1: $ B $ 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 (A1-Списък2! $ А $ 1: $ B $ 5- 2-0), където лист 2! - е връзка към необходимия лист на книгата и $ A $ 1: $ в $ 5 - адреса на обхвата на извличането на данни.

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

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

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

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

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

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

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

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