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

Кому лінь чи немає часу читати – дивіться відео. Деталі та нюанси – у тексті нижче.

Постановка проблеми

Отже, у нас є дві таблиці – замовити стіл и Прайс лист:

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

рішення

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

Для зручності подальшого користування функцією зробіть відразу одне – назвіть діапазон комірок у прайс-листі власним ім’ям. Для цього виділіть усі клітинки прайс-листа, крім «шапки» (G3: H19), виберіть у меню Вставити – Ім’я – Призначити (Вставити — Назва — Визначити) або натисніть CTRL + F3 і введіть будь-яке ім’я (без пробілів), наприклад ціна… Тепер, у майбутньому, ви можете використовувати це ім’я для посилання на прайс-лист.

Тепер використовуємо функцію VPR… Виберіть комірку, куди його буде введено (D3), і відкрийте вкладку Формули – вставка функцій (Формули — Вставити функцію)… У категорії Посилання та масиви (Пошук і посилання) знайти функцію VPR (ВПР) і натисніть OK… З’явиться вікно для введення аргументів функції:

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

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

  • Бажане значення (Пошукове значення) – назва товару, який функція повинна знайти в крайньому лівому стовпчику прайс-листа. У нашому випадку слово «Яблука» з комірки B3.
  • таблиця (Табличний масив) – таблиця, з якої взяті потрібні значення uXNUMXbuXNUMX, тобто наш прайс-лист. Для довідки ми використовуємо нашу власну назву «Ціна», наведену раніше. Якщо ви не вказали назву, ви можете просто вибрати таблицю, але не забудьте натиснути кнопку F4щоб закріпити посилання зі знаками долара, тому що інакше він буде ковзати вниз під час копіювання нашої формули до решти клітинок у стовпці D3:D30.
  • Номер_стовпця (Індекс колонки) – порядковий номер (не літера!) колонки в прайс-листі, з якої ми будемо брати значення цін. Перший стовпець прайс-листа з назвами пронумерований 1, тому нам потрібна ціна зі стовпця під номером 2.
  • interval_lookup (Пошук діапазону) – в це поле можна ввести тільки два значення: FALSE або TRUE:
      • Якщо введено значення 0 or БРЕХНЯ (ПОМИЛКОВИЙ), то фактично це означає, що дозволено лише пошук точний збіг, тобто якщо функція не знайде в прайс-листі нестандартний товар, зазначений у таблиці замовлень (якщо введено, наприклад, «Кокос»), вона сформує помилку #N/A (немає даних).
      • Якщо введено значення 1 or ІСТИНА (ПРАВДА), то це означає, що ви дозволяєте пошук не точного, а приблизна відповідність, тобто у випадку з «кокосом» функція спробує знайти продукт із назвою, максимально наближеною до «кокосу», і поверне ціну на цю назву. У більшості випадків така приблизна заміна може зіграти з користувачем жарт, підставивши значення не того продукту, який там був насправді! Тому для більшості реальних бізнес-проблем краще не допускати приблизний пошук. Винятком є ​​випадки, коли ми шукаємо числа, а не текст – наприклад, під час розрахунку знижок за крок.

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

# N / A помилки та їх придушення

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

  • Точний пошук увімкнено (аргумент Інтервальний перегляд = 0), а потрібного імені немає таблиця.
  • Грубий пошук включено (Інтервальний перегляд = 1), але таблиця, в якому відбувається пошук, не відсортовано за зростанням імен.
  • Формат комірки, звідки походить потрібне значення імені (наприклад, B3 в нашому випадку), і формат комірок першого стовпця (F3: F19) таблиці відрізняються (наприклад, числовий і текстовий). ). Особливо характерним цей випадок є використання цифрових кодів (номерів рахунків, ідентифікаторів, дат тощо) замість текстових назв. У цьому випадку можна скористатися функціями Ч и TEXT для перетворення форматів даних. Це буде виглядати приблизно так:

    =ВПР(ТЕКСТ(B3);ціна;0)

    Ви можете прочитати більше про це тут.

  • Функція не може знайти потрібне значення, оскільки код містить пробіли або невидимі недруковані символи (розриви рядків тощо). У цьому випадку можна використовувати текстові функції TRIM (ТРІМ) и ДРУК(ЧИСТИЙ) щоб видалити їх:

    =VLOOKUP(ПРИБІРКИ(ОЧИСТИТИ(B3)),ціна,0)

    =ВПР(ОБРІЗАТИ(ОЧИСТИТИ(B3));ціна;0)

Для придушення повідомлення про помилку # N / A (#N/A) у випадках, коли функція не може знайти точну відповідність, ви можете скористатися цією функцією ПОМИЛКА (ЯКЩО ПОМИЛКА)… Так, наприклад, ця конструкція перехоплює будь-які помилки, створені VLOOKUP, і замінює їх нулями:

= ЯКЩО ПОМИЛКА (ВПР (B3, ціна, 2, 0), 0)

= ЯКЩО ПОМИЛКА (ВПР (B3; ціна; 2; 0); 0)

PS

Якщо потрібно витягти не одне значення, а відразу весь набір (якщо їх кілька різних), то доведеться пошаманити з формулою масиву. або скористайтеся новою функцією XLOOKUP з Office 365.

 

  • Покращена версія функції VLOOKUP (VLOOKUP 2).
  • Швидкий розрахунок знижок за крок (діапазон) за допомогою функції VLOOKUP.
  • Як створити «лівий VLOOKUP» за допомогою функцій INDEX і MATCH
  • Як використовувати функцію VLOOKUP для заповнення форм даними зі списку
  • Як витягнути з таблиці не перше, а відразу всі значення
  • Функції VLOOKUP2 і VLOOKUP3 із надбудови PLEX

 

залишити коментар