Даруємо!!!

Що робить функція VLOOKUP в Google Tables


Використання функції ВПР (VLOOKUP) для підстановки значень

Постановка завдання

Отже, маємо дві таблиці – таблицю замовлень і прайс-лист:

vlookup1.gif

завдання – підставити ціни з прайс-листа в таблицю замовлень автоматично, орієнтуючись на назву товару з тим, щоб потім можна було порахувати вартість.

Рішення

В наборі функцій Excel, в категорії Посилання та масиви (Lookup and reference) є функція ВПР (VLOOKUP). Ця функція шукає задане значення (в нашому прикладі це слово “Яблука”) в крайньому лівому стовпчику зазначеної таблиці (прайс-листа) рухаючись зверху-вниз і, знайшовши його, видає вміст сусідній осередку (23) Схематично роботу цієї функції можна уявити так:

vlookup2.gif

Для простоти подальшого використання функції відразу зробіть одну річ – дайте діапазону комірок прайс-листа власне ім’я. Для цього виділіть всі комірки прайс-листа крім “шапки” (G3: H19), виберіть в меню Вставка – Ім’я – Присвоїти (Insert – Name – Define) або натисніть CTRL + F3 і введіть будь-яке ім’я (без пробілів), наприклад Прайс.Тепер в подальшому можна буде використовувати це ім’я для посилання на прайс-лист.

Тепер використовуємо функцію ВПР.Виділіть клітинку, куди вона буде введена (D3) і відкрийте вкладку Формули – Вставка функції (Formulas – Insert Function).У категорії Посилання та масиви (Lookup and Reference) знайдіть функцію ВПР (VLOOKUP) і натисніть ОК. З’явиться вікно введення аргументів для функції:

vlookup3.png

Заповнюємо їх по черзі:

  • Шукане значення (Lookup Value) – то найменування товару, яке функція повинна знайти в крайньому лівому стовпчику прайс-листа. У нашому випадку – слово “Яблука” з осередку B3.
  • Таблиця (Table Array) – таблиця з якої беруться шукані значення, тобто наш прайс-лист. Для посилання використовуємо власне ім’я “Прайс” дане раніше. Якщо ви не давали ім’я, то можна просто виділити таблицю, але не забудьте натиснути потім клавішу F4, щоб закріпити посилання знаками долара, тому що в іншому випадку вона буде зісковзувати при копіюванні нашої формули вниз, на інші комірки стовпчика D3: D30.
  • Номер_стовпчика (Column index number) – порядковий номер (не буква!) Стовпчика в прайс-листі з якого будемо брати значення ціни. Перший стовпець прайс-листа з назвами має номер 1, отже нам потрібна ціна з шпальти з номером 2.
  • Інтервальний_просмотр (Range Lookup) – в це поле можна вводити тільки два значення: БРЕХНЯ або ІСТИНА:
    • Якщо введено значення 0 або (FALSE),то фактично це означає, що дозволено пошук тільки точноївідповідності,тобто якщо функція не знайде в прайс-листі укзано в таблиці замовлень нестандартного товару (якщо буде введено, наприклад, “Кокос”), то вона видасть помилку # Н / Д (немає даних).
    • Якщо введено значення 1 або (TRUE),то це означає, що Ви дозволяєте запитом не знайдено точного, а приблизноговідповідності,тобто у випадку з “кокосом” функція спробує знайти товар з найменуванням, яке максимально схоже на “кокос” і видасть ціну для цього найменування. У більшості випадків така приблизна підстановка може зіграти з користувачем злий жарт, підставивши значення, не тої товару, який був насправді! Так що для більшості реальних бізнес-завдань приблизний пошук краще не дозволяти.

Усе! Залишилося натиснути ОК і скопіювати введену функцію на весь стовпець.

Помилки # Н / Д

Функція ВПР (VLOOKUP) повертає помилку # Н / Д (# N / A) якщо:

  • Включений точний пошук (аргумент Інтервальний перегляд =0)і шуканого найменування немає в Таблиці.
  • Включений приблизний пошук (Інтервальний перегляд =1),але Таблиця, в якій відбувається запитом не знайдено відсортована по зростанню найменувань.
  • Формат комірки, звідки береться шукане значення найменування (наприклад B3 в нашому випадку) і формат осередків першого стовпчика (F3: F19) таблиці відрізняються (наприклад, числовий і текстовий). Цей випадок особливо характерний при використанні замість текстових найменувань числових кодів (номера рахунків, ідентифікатори, дати і т.п.) В цьому випадку можна використовувати функції Ч і ТЕКСТ для перетворення форматів даних. Виглядати це буде приблизно так:
    = ВПР (ТЕКСТ (B3); прайс; 0)
  • Функція не може знайти потрібного значення, тому що в коді присутні прогалини або невидимі недруковані знаки (перенесення рядки і т.п.). В цьому випадку можна використовувати текстові функції (TRIM) і (CLEAN) для їх видалення:
    = VLOOKUP (TRIM (CLEAN (B3)); прайс; 0)

Для придушення повідомлення про помилку # Н / Д (# N / A) в тих випадках, коли функція не може знайти точно відповідності, можна скористатися функцією (IFERROR). Так, наприклад, ось така конструкція перехоплює будь-які помилки створювані ВВР і замінює їх нулями:

= IFERROR (VLOOKUP (B3; прайс; 2; 0); 0)