VLOOKUP в Excel просто пояснив, як комп’ютерна підготовка

6 квітня 2020 р. В Excel

vlookup

VLOOKUP і великі обсяги даних просто належать разом. Якщо ви багато працюєте з Excel, ви будете знати, скільки вмісту може містити таблиця. Перегляд кожного окремого елемента вручну та введення додаткової інформації займає багато часу. Для полегшення роботи в Excel існує так званий VLOOKUP. Ця формула стосується не тільки робочого аркуша, але й, наприклад, об’єднує дані з двох різних таблиць і правильно їх призначає. Цей посібник покаже вам, як саме працює VLOOKUP Excel.


Багато користувачів задають собі питання: Що таке насправді VLOOKUP? Функція VLOOKUP використовується, коли ви хочете порівняти та/або узагальнити кілька таблиць Excel.

  • С. виступає за Перпендикулярний, оскільки Excel використовує цю функцію для пошуку в першому вертикальному стовпці обох таблиць вказаного цільового значення.
  • Як аналог є також ПОГЛЯД, який здійснює пошук зліва направо.

Простіше кажучи, це означає: VLOOKUP допомагає визначити відповідні дані в стовпці на основі певних критеріїв пошуку. Однак це лише перший крок. Однак, перш ніж вдаватися до більш детальних відомостей, давайте швидко розглянемо різні способи використання VLOOKUP.


В основному, ви можете застосувати VLOOKUP в Excel у трьох областях:

  1. Якщо ви хочете перевірити, чи певне значення в одній таблиці міститься і в іншій.
  2. Якщо ви хочете порівняти дві таблиці між собою.
  3. Якщо ви хочете розширити вміст однієї таблиці даними з другої таблиці.


Як згадувалося раніше, VLOOKUP - це формула. Це показує Excel, за якими критеріями ви хочете щось шукати - зокрема, що, де та номер стовпця.

Як і будь-яка функція в Excel, вона починається з = Підписати та Назва команди, так = ПЕРЕГЛЯД. Тепер Excel знає, що він повинен призначити щось для вибраної комірки. Повна формула, яку ви вводите як VLOOKUP в Excel, має таку структуру:

= VLOOKUP (критерій пошуку, матриця, індекс, true/false)

Таким чином, формула визначається чотирма окремими аргументами:

Критерій пошуку Це значення, яке є в обох списках для порівняння. Отже, ви використовуєте критерій пошуку, щоб вказати значення у списку 1, який також є у списку 2.
Матриця Це знаходиться в списку для порівняння (Список 2) і є областю, в якій знаходиться інформація для Списку 1.
Індекс стовпця Це сигналізує в Excel, який стовпець слід перенести з матриці до Списку 1 за допомогою VLOOKUP.
Посилання на область Ця інформація є необов’язковою. Якщо ви хочете використовувати його, він буде заповнений True або False.


Коли ми говоримо про “істинно”, це означає приблизну відповідність критерію пошуку зі списку 1 критерію матриці зі списку 2. Якщо ви вводите true, то точне значення для критерію пошуку у формулі не використовується. Натомість це також найближче наближення. Наприклад, у випадку списків товарів це можуть бути цінові шкали або кількісні шкали.

Якщо, з іншого боку, значення "false", значення передається лише в тому випадку, якщо критерій пошуку відтворено точно. Це було б у випадку зі статтями чи номерами персоналу.

Для тих, хто ніколи не працював з VLOOKUP, спочатку формула звучить досить складно. Однак за цим стоїть дуже простий принцип. Якщо ви хочете створити VLOOKUP в Excel, зверніть увагу на такі моменти:

  1. Введіть значення пошуку - критерій пошуку, який потрібно шукати.
  2. Звужте область пошуку.
  3. Не забудьте номер стовпця поверненого значення.
  4. Якщо ви хочете отримати точний результат, додайте False в кінці. З іншого боку, якщо приблизного збігу достатньо, будь ласка, використовуйте True.

Щоб зрозуміти речі, складіть формулу ще раз:

= VLOOKUP (пошук/критерій пошуку; область пошуку/матриця; номер стовпця/індекс; true/false)

Складні формули найкраще проілюструвати на прикладі. У цьому випадку ми дотримуємось списку товарів, для введення якого повинні бути призначені ціни. Доступні дві таблиці:

  • Червона таблиця містить номери та назви товарів.
  • Однак у зеленій таблиці вказані ціни на продукти.

Метою VLOOKUP є поєднання інформації з обох таблиць Excel в одну.

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

  1. Виділіть комірку та введіть критерій пошуку.
    У цьому прикладі ми клацаємо на клітинку в області C2 і вводимо формулу name = sreference та перший аргумент. У цьому випадку це буде A2, оскільки ми хочемо шукати номер товару 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. Клієнт B має оборот в 310 000 євро. Однак у списку знижок ви можете бачити, що клієнт отримує знижку 2%, якщо оборот становить від 200 000 до 300 000 євро. Тепер VLOOKUP також повинен «читати між рядків». Достатньо приблизного перевизначення. Область посилань не заповнюється - або введено TRUE як альтернативу.
  4. Натискаючи клавішу Enter та заповнюючи функцію на всіх записах даних, знижки стають видимими для всіх клієнтів.