Приложение и дефиниция на тази функция на Excel
VLOOKUP е функция на Excel, с която потребителят може да търси и оценява съдържанието на таблицата. Тази функция е налична във версии от Excel 2007 за Windows и Mac.
Какво представлява VLOOKUP?
Възможните употреби на VLOOKUP трябва да бъдат обяснени тук чрез пример: В този вие сте голям фен на литературата и затова сте създали своя собствена електронна таблица в Excel, в която можете внимателно да сортирате книгите, които сте събрали. Всяко произведение се въвежда с информация за следните категории:
-
автор
-
заглавие
-
Номер на страницата
-
Година на издаване
Сега бихте искали да дадете на приятел съвет за книга, който да вземете със себе си при следващата ви среща. За съжаление можете да мислите само за автора, а не за заглавието на книгата. Това е мястото, където VLOOKUP влиза в игра, тъй като може да използва тази входна стойност, за да изхвърли информацията, която търсите с един замах.
Как се използва VLOOKUP?
Преди дори да мислите за формулиране на формули, трябва да се определи къде полето за въвеждане и различните полета за изход ще бъдат разположени по -късно. За да направите това, има смисъл да създадете отделна таблица, която първоначално е празна и по този начин позволява място за споменатата информация. Ако проектирате тази нова таблица въз основа на примера на съществуващата таблица, по-късно ще имате предимство за спестяване на време.
На тази основа формулата VLOOKUP може да бъде създадена ръчно или автоматично генерирана от Excel. За начинаещи си струва да използват последния подход, за да опознаят постепенно структурата и ефекта на формулата. За да направите това, бутонът за „Вмъкване на функция“ е избран в раздела „Формули“. VLOOKUP е скрит в прозореца, който се отваря. След потвърждение отново се отваря прозорец, в който могат да бъдат попълнени четирите параметъра на формулата. Това са:
-
Критерий за търсене
-
матрица
-
Индекс на колона
-
Area_reference
Следователно необработеният проект на формулата изглежда така:
= VLOOKUP (критерий за търсене, матрица, индекс на колона, диапазон_връзка)
и в едно възможно приложение като това:
= VLOOKUP (H3; A3: E40; 5)
Критерий за търсене
За да може функцията да знае коя стойност трябва да се използва като отправна точка, редът, избран като поле за въвеждане две стъпки по -рано, се отбелязва в полето "Критерий за търсене". В нашия пример там е въведено името на автора на книгата „Филип Пулман“. Това прави формулата гъвкава и не е необходимо да се коригира отново веднага щом въведената стойност се промени.
матрица
Полето за въвеждане "Матрица" описва таблицата, в която може да се намери информацията за извеждане. Следователно тази специална матрица съдържа и колоните за заглавието на книгата, номера на страницата и годината на публикуване.
Матрицата се избира изцяло веднъж без заглавията от горния ляв до долния десен край. По този начин Excel знае кое съдържание трябва да се вземе предвид при оценката.
Индекс на колона
Полето за въвеждане на "индекс на колона" подканва потребителя да определи колоната на матрицата, в която е посочена само търсената стойност. Присвояването на колоните е номерирано хронологично. Това означава, че първата колона на таблицата получава стойност 1, втората стойност 2 и т.н. В нашия пример това съответства на индекс на колона 1 за автора, индекс на колона 2 за заглавието, индекс на колона 3 за номера на страницата и колонен индекс 4 за годината на публикуване.
За да бъде таблицата възможно най -гъвкава, заглавието на колоната може да бъде свързано вместо номера. Това има предимството, че формулата също може да бъде прехвърлена в други редове без никакви проблеми, тъй като заглавието на колоната може да се адаптира гъвкаво всеки път.
Внимание: VLOOKUP чете матрицата отляво надясно, поради което индексът на колоната трябва да бъде поставен вдясно от колоната за критерия за търсене, за да бъде взета предвид от функцията!
Area_reference
Параметърът "Range_Lookup" допълва формулата VLOOKUP, като посочва точността, с която се оценява таблицата. Той обаче се различава от споменатите по -рано компоненти на формулата, тъй като не е задължителен. Ако стойността 0 е въведена за „неправилна“, Excel търси само стойността, посочена като критерий за търсене. Със стойността 1 за „true“ обаче търсенето продължава за очевидни стойности, ако точната стойност не може да бъде намерена.
Посочването на този параметър не е задължително, защото стойността 1 е зададена по подразбиране. Тази настройка ще бъде полезна по -късно в разширения VLOOKUP с множество критерии за търсене.
Сливането
Веднага след като всички необходими параметри са настроени, може да се използва VLOOKUP. След като въведете критерия за търсене и потвърдите функцията, стойността, която търсите, се появява в реда, който е определен като изходно поле.
В нашия пример сега се показва заглавието на книгата „Златният компас“, което съответства на въведения автор. За да разберете бързо номера на страницата и годината на публикуване, не трябва да се прави нищо повече от плъзгане на съществуващата формула VLOOKUP в следващите клетки. Това е толкова лесно, защото индексът на колоната на VLOOKUP е свързан с заглавието на колоната на първата таблица, а втората таблица също е структурирана в същия ред.
В случай, че таблиците трябва да се различават една от друга или че въпреки всичко възникне грешка, формулата VLOOKUP може да бъде променена и ръчно. За да направите това, предпоследната цифра за индекса на колоната трябва да бъде съпоставена с колоната на новата стойност, която трябва да бъде изведена.
VLOOKUP с множество критерии за търсене
Достатъчно често се случва, че един критерий за търсене не е достатъчен за точна оценка на голяма таблица в Excel. Тогава има смисъл да стартирате VLOOKUP с няколко критерия за търсене. За да направите това, съществуващата формула трябва да бъде допълнена с допълнителна функция IF. По този начин могат да се вземат предвид до осем различни критерии за търсене по време на кандидатстването.
VLOOKUP в множество електронни таблици на Excel
Ако критерият за търсене може да бъде намерен не само в една таблица, но евентуално и в друга, формулата VLOOKUP може да бъде съответно коригирана. За целта както функцията if, така и функцията ISERROR трябва да бъдат поставени пред съществуващата формула. За това са необходими пет параметъра:
-
Критерий за търсене
-
Матрица 1 и Матрица 2
-
Индекс на колона1 и индекс на колона2
Резултатът изглежда така:
= IF (ISERROR (VLOOKUP (критерий за търсене, матрица1, колона-индекс1, 0));
VLOOKUP (критерий за търсене; матрица2; индекс на колона2,0); VLOOKUP (критерий за търсене; матрица1; индекс на колона1;))
и в едно възможно приложение като това:
= IF (ISERROR (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
Критерият за търсене се използва за вмъкване на стойността, която трябва да се търси в двете таблици. Matrix1 и Matrix2 определят съответните области на клетките на двете таблици. Колоната index1 и колоната index2 се използват, за да дефинират по -подробно кои колони от съответните таблици трябва да се търсят.
Ако стойността, която търсите, се среща в двете таблици, Excel ще изведе резултата от първата таблица. Ако обаче стойността не е намерена в нито една от двете таблици, се появява съобщение за грешка. Предимството на формулата е, че двата списъка не трябва да имат еднаква структура или да са с еднакъв размер.
Присвояване на стойности на категории с помощта на VLOOKUP
Допълнителна функция на VLOOKUP позволява изброените стойности да бъдат автоматично разделени на букви и предикати по ваш избор. В предишния ни пример трябва да се вмъкне допълнителна колона в таблицата за типа книга. Книгите с дължина до 50 страници трябва да попаднат в жанра на новелата, докато книгите от 51 до 150 страници са отнесени към новелата и от 151 страници към романа. За да стане това възможно, не се изисква допълнителна формула във VLOOKUP, а само използването на къдрави скоби „{}“. Готовата формула изглежда така:
= VLOOKUP (B1; {1. "Разказ"; 51. "Новела"; 151. "Роман"}; 2)
Съдържанието на фигурните скоби показва матрица, която определя областта на съответния тип книга. Следователно приписването на страничната дължина на съответния род е поместено в къдравите скоби. Формулата използва двойки стойности, всяка разделена с точка. Матрицата {1. "Разказ"; 51. "Новела"; 151. "Роман"} се чете, както следва:
"От 1 предаване кратка история, от 51 показване на новела, от 151 показване на роман."
Тази матрица може лесно да се адаптира към различни задачи. Това се отнася от една страна за размера и броя на матриците, както и за тяхното обозначение. Така че е възможно да се изведат низове или числа в резултат вместо отделни букви. Всичко, което трябва да направите, е да коригирате буквите във формулата.
VLOOKUP в множество работни листове
Друга функция на VLOOKUP позволява на потребителите да свързват съдържание, което се намира в различни електронни таблици. За нашия пример тази опция може да бъде полезна, когато информацията първо се сортира в различни работни листове и след това се актуализира в обобщена таблица.
Представете си, че освен книгите си, вие изброявате и събраните си филми в електронна таблица на Excel. След това комбинирате двете колекции в една голяма таблица.
Предимството на тази процедура е не само в увеличения ред, но и в избягването на потенциални грешки. Ако искате да създадете нов запис или да актуализирате съществуващ, не е нужно да търсите в голямата таблица, но вместо това можете да получите достъп до по -малките. След това стойностите автоматично се прехвърлят в обобщаващата таблица на Excel. Това прави пренаписването в голямата таблица излишно, което в най -добрия случай избягва злополучен ход и последващо подреждане на съобщения за грешка.
Как изглежда формулата?
Тази функция става възможна отново чрез вмъкване на друга формула. Докато търсенето с множество критерии изисква допълнителна формула IF, работата с множество работни листове изисква формула INDIRECT. Това позволява да се посочи диапазон от друга електронна таблица за матрицата VLOOKUP.
= VLOOKUP (критерий за търсене; INDIRECT (матрица); индекс на колона; range_link)
Внимание: Тази формула ще работи само ако отделните таблици в различните листове имат същите имена като заглавията на колоните в общата таблица. Цели таблици могат да бъдат наименувани в полето „Име“ горе вляво над решетката на клетките. Таблиците, които вече са кръстени, могат да се разглеждат с комбинацията от клавиши Ctrl + F3.
Справяне с възникващи съобщения за грешка
Работата със свързани таблици на Excel може да доведе до нежелани проблеми. Това включва по -специално извеждането на грешни стойности. В случай, че се изведе грешна стойност 0, има малък проблем в настройките на Excel, който може да бъде отстранен бързо.
Общото съобщение за грешка #NV, от друга страна, е умишлена функция на VLOOKUP, която показва на потребителя, че необходимата стойност не е налична. Тази бележка може да бъде проектирана по различен начин с помощта на формула.
VLOOKUP - преглед
VLOOKUP е полезна функция на Excel, която може да се използва за търсене и оценка на таблици. Предимствата му са очевидни в удобното за потребителя и гъвкаво приложение. По този начин всеки, който редовно работи с таблици в Excel, може да се възползва от функцията. Независимо дали частният колекционер създава свои собствени малки таблици, или голямата компания, която обработва значително по -значителни масиви от данни.
Ако, от друга страна, все още имате отговор на заявки, на които VLOOKUP не може да отговори, можете да очаквате допълнителна опция на Excel: Microsoft предлага на потребителите на Excel 365 новия XLOOKUP от началото на 2022-2023 г. Това се основава на компетенциите на VLOOKUP и ги допълва с допълнителни, понякога дори по -прости функции. Следователно в този момент се отваря нова рутина в оценката на данните.
