VLOOKUP в Excel просто обяснява КАТО компютърно обучение

6 април 2020 г. в Excel

просто

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


Много потребители си задават въпроса: Какво всъщност е VLOOKUP? Функцията VLOOKUP се използва винаги, когато искате да сравните и/или обобщите множество таблици на Excel.

  • The С. означава Перпендикулярно, защото Excel използва тази функция за търсене в първата вертикална колона на двете таблици за посочената целева стойност.
  • Като аналог има и ПРЕГЛЕД, който търси отляво надясно.

Казано по-просто, това означава: VLOOKUP ви помага да определите съответните данни в колона въз основа на определени критерии за търсене. Това обаче е само първата стъпка. Преди обаче да влезем в повече подробности, нека да разгледаме набързо различните приложения на VLOOKUP.


По принцип можете да приложите VLOOKUP в Excel в три области:

  1. Ако искате да проверите дали определена стойност в едната таблица се съдържа и в другата.
  2. Ако искате да сравните две таблици помежду си.
  3. Ако искате да разширите съдържанието на една таблица с данни от втора таблица.


Както бе споменато по-рано, VLOOKUP е формула. Това показва Excel кои критерии искате да търсите - включително какво, къде и номера на колоната.

Както всяка функция в Excel, тя започва с = Подпиши и Име на командата, така = ОТГЛЕЖДАНЕ. Excel вече знае, че трябва да присвои нещо на избраната клетка. Пълната формула, която въвеждате като VLOOKUP в Excel, има следната структура:

= VLOOKUP (критерий за търсене, матрица, индекс, вярно/невярно)

По този начин формулата се дефинира от четири отделни аргумента:

Критерий за търсене Това е стойността, която се появява и в двата списъка за сравнение. Така че използвате критерия за търсене, за да посочите стойността в списък 1, който също е в списък 2.
Матрица Това се намира в списъка за сравнение (Списък 2) и е областта, в която се намира информацията за Списък 1.
Индекс на колона Това сигнализира на Excel коя колона трябва да бъде прехвърлена от матрицата в Списък 1 с помощта на VLOOKUP.
Справка за площ Тази информация не е задължителна. Ако искате да го използвате, той ще бъде запълнен с True или False.


Когато говорим за „true“, това означава приблизителното съответствие на критерия за търсене от списък 1 с критерия на матрицата от списък 2. Ако въведете true, формулата за критерия за търсене не използва точната стойност за него. Вместо това е и най-близкото приближение. В случай на продуктови списъци, например, това могат да бъдат ценови скали или количествени скали.

Ако, от друга страна, стойността е "false", стойност се прехвърля само ако критерият за търсене е възпроизведен точно. Такъв би бил случаят с номера на артикули или персонал.

За тези, които никога не са работили с VLOOKUP, формулата в началото звучи доста сложно. Обаче зад това има много прост принцип. Ако искате да създадете VLOOKUP в Excel, обърнете внимание на следните точки:

  1. Въведете справочна стойност - критерият за търсене, който искате да търсите.
  2. Стеснете областта за търсене.
  3. Не забравяйте номера на колоната на връщаната стойност.
  4. Ако искате точен резултат, добавете False в края. От друга страна, ако приблизителното съвпадение е достатъчно, моля използвайте True.

За да направите нещата по-ясни, сложете формулата отново:

= VLOOKUP (критерий за търсене/търсене; област/матрица за търсене; номер на колона/индекс; вярно/невярно)

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

  • Червената таблица съдържа номерата и имената на продуктите.
  • Зелената таблица обаче показва цените на продуктите.

Целта на VLOOKUP е да комбинира информацията от двете таблици на Excel в една.

При такива малки количества данни обикновено не би било проблем да се използва методът за копиране/поставяне. Колкото повече данни обаче има в таблица, толкова по-сложен става процесът. Освен това често има проблем, че не цялата информация винаги е пълна или вярна. VLOOKUP на Excel е много по-елегантно решение в такива случаи.

  1. Изберете клетката и въведете критерия за търсене.
    В този пример кликваме върху клетката в област C2 и въвеждаме името на формулата = sreference и първия аргумент. В този случай това би било А2, защото искаме да потърсим номера на продукта 1234 и в двете таблици на Excel.

  1. Определете матрицата, като посочите къде Excel трябва да търси свързаната информация.
    Въвеждаме = sverweis (A2; $ F & 2: $ G $ 10, защото искаме да включим всички номера на продукти и цени в радиуса на търсене. Внимание: Не забравяйте, че трябва да поставите знак за долар пред номера на полето, в противен случай формулата няма да работи.

  1. Посочете индекса на колоната, където Excel ще намери информацията, необходима за VLOOKUP. В този случай това би било в ред 2, тъй като съответната цена е там.
    Така че въведете = препратка (A2; $ F $ 2: $ G $ 10; 2).
  2. Искате ли да използвате точния критерий за търсене или е достатъчна приблизителна стойност? В този случай трябва да се търси точния номер на продукта. Можете да въведете false или стойността 0 за false.
  3. Сега кликнете върху Enter.


Цената вече се показва в клетката. Става ясно, че отделните цени се отнасят до свързаните с тях номера на продукти. Например, ако производителят промени името на продукта, това не е проблем. Цената остава същата благодарение на Excel VLOOKUP.

В този пример използваме таблица със списък на клиентите със съответните продажби (червена таблица) като основа. В зависимост от постигнатия оборот, клиентът получава отстъпка според скалата за отстъпки (зелена таблица). Таблица 1 вече е разширена, за да включва колона „Отстъпка“.

  1. Изберете клетката и въведете критерия за търсене. За да направите това, поставете Excel VLOOKUP в клетка C2. Критерият за търсене е оборотът, който се съдържа като стойност в двата списъка и към който е свързан съответният код за отстъпка в списък 2.

  1. Определете матрицата, като посочите къде Excel трябва да търси свързаната информация. В този случай източникът на информация или матрицата се простира от поле H2 до I10. Внимание: Не забравяйте знака за долар пред номера на полето.
  2. За VLOOKUP посочете индекса на колоната на Excel. Отново възниква въпросът в коя колона Excel намира отстъпката за клиента - колона 2.
  3. Клиент Б има оборот от € 310 000. В списъка с отстъпки обаче можете да видите, че клиентът получава отстъпка от 2%, ако оборотът е между 200 000 и 300 000 евро. Сега VLOOKUP също трябва да „чете между редовете“. Приблизително заместване е достатъчно. Референтната област не се попълва - или като алтернатива се въвежда TRUE.
  4. Като кликнете върху Enter и попълните функцията във всички записи на данни, отстъпките са видими за всички клиенти.