В този свят, управляван от данни, човек се нуждае от различни инструменти, за да управлява данните. Данните в реално време са огромни и извличането на подробности относно някои конкретни данни определено би било изморителна задача, но с VLOOKUP в Excel , тази задача може да бъде постигната с един ред на командата. В тази статия ще научите за едно от важните Функции на Excel т.е. функцията VLOOKUP.
следдипломна диплома срещу магистър
Преди да продължим, нека да разгледаме набързо всички теми, които са обсъдени тук:
- Какво представлява VLOOKUP в Excel?
- Как работи?
- Точно съвпадение
- Приблизителен мач
- Първи мач
- Чувствителност към регистъра
- Грешки
- Двупосочно търсене
- Използване на заместващи символи
- Множество справочни таблици
Какво представлява VLOOKUP в Excel?
В Excel VLOOKUP е вградена функция който се използва за търсене и извличане на конкретни данни от Excel лист. V означава Vertical и за да се използва функцията VLOOKUP в Excel, данните трябва да бъдат подредени вертикално. Тази функция е много полезна, когато имате огромно количество данни и би било практически невъзможно ръчното търсене на някои конкретни данни.
Как работи?
Функцията VLOOKUP приема стойност, т.е. стойността за търсене и започва да я търси в най-лявата колона. Когато се намери първото появяване на справочната стойност, тя започва да се движи точно в този ред и връща стойност от колоната, която сте посочили. Тази функция може да се използва за връщане както на точни, така и на приблизителни съвпадения (Съвпадението по подразбиране е приблизително съвпадение).
Синтаксис:
Синтаксисът на тази функция е както следва:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
където,
- lookup_value е стойността, която трябва да се обърне внимание в първата колона на дадената таблица
- таблица_индекс е таблицата, от която трябва да бъдат извлечени данните
- col_index_num е колоната, от която трябва да бъде извлечена стойността
- range_lookup е логическа стойност, която определя дали справочната стойност трябва да бъде перфектно съвпадение или приблизително съвпадение ( ВЯРНО ще намери най-близкото съвпадение НЕВЯРНО проверки за точно съвпадение)
Точно съвпадение:
Когато искате функцията VLOOKUP да търси точно съвпадение на търсената стойност, ще трябва да зададете range_lookup стойност на FALSE. Обърнете внимание на следния пример, който представлява таблица, състояща се от подробности за служителите:
В случай, че искате да потърсите обозначението на някой от тези служители, можете да направите следното:
- Изберете клетката, където искате да покажете изхода, и след това въведете знак '='
- Използвайте функцията VLOOKUP и предоставете lookup_value (Тук това ще бъде идентификационният номер на служителя)
- След това предайте другите параметри, т.е. масив_ масив , col_index_num и задайте range_lookup стойност на FALSE
- Следователно функцията и нейните параметри ще бъдат: = VLOOKUP (104, A1: D8, 3, FALSE)
Функцията VLOOKUP започва да търси идентификатор на служител 104 и след това се придвижва вдясно в реда, където е намерена стойността. Продължава до col_index_num и връща стойността, присъстваща на тази позиция.
Приблизителен мач:
Тази функция на функцията VLOOKUP ви позволява да извличате стойности, дори когато нямате точно съвпадение за loopup_value. Както споменахме по-рано, за да накарате VLOOKUP да търси приблизително съвпадение, ще трябва да зададете range_lookup стойност до TRUE. Обърнете внимание на следния пример, където оценките се картографират заедно с техните оценки и класа, към който принадлежат.
- Подобно на начина, по който сте направили точното съвпадение, следвайте същите стъпки
- Вместо стойността range_lookup използвайте TRUE вместо FALSE
- Следователно функцията заедно с нейните параметри ще бъде: = VLOOKUP (55, A12: C15, 3, TRUE)
В таблица, сортирана във възходящ ред, VLOOKUP започва да търси приблизително съвпадение и спира на следващата най-голяма стойност, която е по-малка от въведената от вас справочна стойност. След това се движи точно в този ред и връща стойността от посочената колона. В горния пример, справочната стойност е 55, а следващата най-голяма справочна стойност в първата колона е 40. Следователно изходът е Втори клас.
Първи мач:
В случай че имате таблица, която се състои от множество справочни стойности, VLOOKUP спира при първото съвпадение и извлича стойност от този ред в посочената колона.
Погледнете изображението по-долу:
ID 105 се повтаря и когато справочната стойност е посочена като 105, VLOOKUP е върнал стойността от реда, който има първото появяване на справочната стойност.
Чувствителност към регистъра:
Функцията VLOOKUP не чувствителна към регистъра. В случай, че имате справочна стойност, която с главни букви и стойността, присъстваща в таблицата, е малка, VLOOKUP пак ще извлече стойността от реда, в който присъства стойността. Погледнете изображението по-долу:
Както можете да видите, стойността, която съм посочил като параметър, е „RAFA“, докато стойността, присъстваща в таблицата, е „Rafa“, но VLOOKUP все още е върнал посочената стойност. Ако имате точно съвпадение дори със случая, VLOOKUP пак ще върне първото съвпадение на справочната стойност, независимо от използвания случай. Погледнете изображението по-долу:
Грешки:
Естествено е да срещаме грешки, когато използваме функции. По същия начин можете да срещнете грешки и при използване на функцията VLOOKUP и някои от често срещаните грешки са:
- # ИМЕ
- # N / A
- #REF
- #VALUE
#NAME Грешка:
Тази грешка е основно да ви информира, че сте допуснали някаква грешка в синтаксиса. За да избегнете синтактични грешки, по-добре е да използвате съветника за функции, предоставен от Excel за всяка функция. Съветникът за функции ви помага с информация относно всеки параметър и вида на стойностите, които трябва да въведете. Погледнете изображението по-долу:
Както можете да видите, съветникът за функции ви информира да въведете всякакъв вид стойност вместо параметъра lookup_value и също така дава кратко описание на същата. По същия начин, когато изберете другите параметри, ще видите информация и за тях.
# N / A Грешка:
Тази грешка се връща в случай, че не е намерено съвпадение за дадената справочна стойност. Например, ако въведа „AFA“ вместо „RAFA“, ще получа грешка # N / A.
За да дефинирате съобщение за грешка за горните две грешки, можете да използвате функцията IFNA. Например:
Грешка #REF:
Тази грешка се среща, когато давате препратка към колона, която не е налична в таблицата.
#VALUE Грешка:
Тази грешка се среща, когато поставите грешни стойности на параметрите или пропуснете някои задължителни параметри.
Двупосочно търсене:
Двупосочното търсене се отнася до извличане на стойност от двумерна таблица от която и да е клетка на референтната таблица. За да извършите двупосочно търсене с помощта на VLOOKUP, ще трябва да използвате функцията MATCH заедно с нея.
Синтаксисът на MATCH е както следва:
СЪОТВЕТСТВИЕ (lookup_value, lookup_array, match_type)
- lookup_value е стойността, която трябва да се търси
- lookup_array е диапазонът от клетки, които се състоят от справочните стойности
- тип_съвпадение може да бъде число, т.е. 0, 1 или -1, представляващо точно съвпадение, по-малко и по-голямо от съответно
Вместо да използвате твърдо кодирани стойности с VLOOKUP, можете да го направите динамично заобикаляне в препратките на клетките. Помислете за следния пример:
инсталирайте php 7 на windows
Както можете да видите на изображението по-горе, функцията VLOOKUP приема препратката към клетката като F6 за стойността на справка и стойността на индекса на колоната се определя от функцията MATCH. Когато правите промени в някоя от тези стойности, изходът също ще се промени съответно. Погледнете изображението по-долу, където промених стойността в F6 от Chris на Leo и изходът също беше актуализиран съответно:
В случай, че променя стойността на G5, или и двете F6 и G5, тази формула ще работи съответно, показвайки съответните резултати.
Можете също да създадете падащи списъци, за да направите задачата за промяна на стойностите много удобна. В горния пример това трябва да се направи с F6 и G5. Ето как можете да създадете падащи списъци:
- Изберете Данни от лентата на лентата
- От групата Data Tools изберете Проверка на данни
- Отворете екрана с настройки и от Разрешаване изберете Списък
- Посочете масива от списъка с източници
Ето как изглежда след като сте създали падащ списък:
Използване на заместващи символи:
В случай че не знаете точната справочна стойност, но само част от нея, можете да използвате заместващи символи. В Excel символът „*“ представлява заместващ символ. Този символ информира Excel, че последователността, която идва преди, след или между тях, трябва да бъде търсена и може да има произволен брой знаци преди или след тях. Например в създадената от мен таблица, ако въведете „erg“ заедно с заместващи карти от двете страни, VLOOKUP ще върне изхода за „Sergio“, както е показано по-долу:
Множество справочни таблици:
В случай че имате множество справочни таблици, можете да използвате функцията IF заедно с нея, за да разгледате някоя от таблиците въз основа на дадено условие. Например, ако има таблица с данни за два супермаркета и трябва да разберете печалбата, получена от всеки от тях въз основа на продажбите, можете да направите следното:
Създайте основната таблица, както следва:
След това създайте двете таблици, от които трябва да се извлече печалбата.
След като това бъде направено, създайте именован диапазон за всяка от новосъздадените таблици. За да създадете именен диапазон, следвайте стъпките, дадени по-долу:
- Изберете таблицата на цялата таблица, на която искате да присвоите име
- От раздела на лентата изберете Формули и след това от групата Дефинирани имена изберете Дефиниране на име
- Ще видите следния диалогов прозорец
- Дайте произволно име по ваш избор
- Щракнете върху OK
След като това бъде направено и за двете таблици, можете да използвате тези именувани диапазони във функцията IF, както следва:
можете ли да направите масив от обекти в java
Както можете да видите, VLOOKUP е върнал подходящите стойности, за да попълни колоната Печалба според кой супермаркет те принадлежат. Вместо да напиша формулата във всяка клетка на колоната Печалба, аз просто копира формулата с цел спестяване на време и енергия.
Това ни води до края на тази статия за VLOOKUP в Excel. Надявам се да сте наясно с всичко споделено с вас. Уверете се, че практикувате възможно най-много и връщате опита си.
Имате въпрос към нас? Моля, споменете го в раздела за коментари на този блог „VLOOKUP в Excel“ и ние ще се свържем с вас възможно най-скоро.
За да получите задълбочени познания за всякакви модерни технологии, заедно с различните им приложения, можете да се регистрирате за живо с 24/7 поддръжка и доживотен достъп.