Будь-яка людина, що має справу з цифрами, – для професійного чи інтересу або просто з любові до мистецтва, – звичайно, першим ділом освоїть калькулятор. Що далі? На черзі геніальна по своїй суті програма для роботи з числами – Excel. Знати цю програму досконало, так само як і знати досконало математику, напевно, неможливо. Однак існують базові можливості в програмі, функції “Ексель”, знаючи які з упевненістю на 90 % можна говорити про вміння працювати з даними на рівні впевненого користувача. Одна з таких найважливіших функцій в Excel – VLOOKUP, вона дуже багатогранна. Як користуватися VLOOKUP в Excel (для чайників) розглядається у статті. Звичайно, сприйняття теорії не принесе повного розуміння процесу використання функції. Необхідна практика спочатку на невеличких масивах даних, потім на таблицях будь-яких розмірів.
Сутність та призначення функції ВВР Excel
Функція ВВР дозволяє заощадити багато часу таким фахівцям, як економісти, фінансисти, бухгалтери, технологи. Опис функції VLOOKUP в Excel і приклад її застосування необхідно почати з розгляду ситуації, з якою часто стикаються фахівці різного роду в своїй роботі. Якщо є дві таблиці, в яких фігурують одні і ті ж визначаються дані (наприклад, найменування товарів), та визначення до них у різних таблицях різняться, наприклад, в одній варто кількість, а в іншій ціна. При цьому стоїть завдання об’єднати всі ці дані разом і провести відповідні розрахунки – у таких ситуаціях на допомогу і приходить функція ВПР. Для розуміння, як працює VLOOKUP в Excel, нижче представлені дві таблиці. В кожній з них присутній код товару. В одній проставлена відповідна кількість, в іншій – ціна. Для підстановки ціни у першу таблицю і використовується ВВР.
Підстановка вручну даних між таблицями – це величезна трата часу і сил співробітника, помилок при цьому уникнути не вийде. За допомогою ВВР можна знайти відповідні значення швидко і просто. Для глибокого розуміння, як користуватися VLOOKUP в Excel, необхідно розібрати всі аргументи функції.
Аргументи функції
Для застосування функції необхідно встати в результуючу клітинку, вибрати на стрічці вкладку “Формули” – “Посилання та масиви” – “ВВР”. В комірці з’явився напис “=ВПР(“. Тепер необхідно правильно ввести аргументи функції. Можна зробити це через крапку з комою прямо в рядку формул. Проте починаючому користувачеві зручніше це зробити через діалогове вікно аргументи функції.
Функція ВВР має чотири аргументи – шукане значення, таблиця, номер стовпця, інтервальний перегляд. Перші три, виділені в діалоговому вікні жирним шрифтом, обов’язкові до заповнення. Пояснюємо, що таке аргументи функції:
- шукане значення – що шукати;
- таблиця – де шукати;
- номер стовпця, в якому стовпці шукати;
- інтервальний перегляд – відсортовано.
Аргумент “Шукане значення”
У перше поле потрібно ввести те, що потрібно шукати. Тут може бути число, текст, дата. Можна вводити абсолютним значенням, можна проставити посилання на клітинку з потрібним значенням. Шукане значення теоретично має бути присутнім в обох таблицях. У прикладі, наведеному вище, таким значенням може стати код товару. Щоб уникнути помилок краще скористатися посиланням.
При використанні тексту в якості шуканого значення , його необхідно взяти в лапки. При посиланні на клітинку з текстом лапки не потрібні. Регістр при введенні тексту не має значення. Може використовуватися нечіткий пошук, за фрагментом тексту. Для цього всередині лапок потрібно укласти фрагмент тексту символи *.
Найпоширенішою проблемою при використанні функції є різний формат клітинок у діапазонах. Якщо в полі першого аргументу поставити посилання на клітинку, де дані стоять в текстовому форматі, а в діапазоні пошуку дані будуть в числовому форматі, при зовнішньої ідентичності значень ВВР відповідності не знайде.
Перевести у формат просто – потрібно помножити масив на одиницю.
Аргумент “Таблиця”
Тут необхідно ввести діапазон, де функція буде шукати перше введене значення. Діапазоном для пошуку значень буде перший стовпець виділеної області. Значення, яке потрібно знайти і проставити як результат, повинне перебувати в стовпці правіше, ніж стовпець пошуку відповідності. Це є одним з ключових недоліків використання ВВР: для можливості роботи з нею таблицю часто доводиться перебудовувати, щоб шукані дані були ліворуч. Діапазон можна вводити як вручну, так і посиланням. У прикладі це виглядає так:
Діапазоном тут служить права таблиця. Шукане значення в стовпці праворуч, там Excel буде шукати значення 3187849428, значення, яке потрібно знайти і підставити як результат формули – зліва. Для отримання точного результату краще зафіксувати діапазон, виділивши його і натиснувши клавішу F4, посилання на масив стане абсолютною.
Аргумент “Номер стовпця”
Тут необхідно цифрою проставити, в якому за рахунком стовпці, від самого лівого, необхідно взяти значення для підстановки як результат обчислення. У вищевказаному прикладі – це другий стовпець, рядок аргументів необхідно проставити цифру 2. Якби між стовпцем “Код” і “Ціна” був би ще один стовпець, то потрібно було б проставити цифру 3 і так далі.
Аргумент “Інтервальний перегляд”
Заповнення це поле необов’язково, але може виявитися дуже важливим. Тут може стояти одне з двох значень – 1 (істина) або 0 (брехня). Більшість користувачів вважають, що функція даного аргументу – визначити точність збігу шуканих значень. Це не зовсім правильно. При вказівці одиниці, якщо в діапазоні таблиці є повтори, функція поверне останнім знайдене значення. При цьому функція ВВР буде приймати до уваги всі значення менше або рівні введеному в полі “Шукане значення”. Якщо функція знайде більше значення, а меншої або рівної не знайде, вона видасть помилку Н/Д.
Видно, що в полі “Значення” введено число 3187849425, такого значення у вихідному діапазоні немає і програма, знайшовши всі значення менше або рівні шуканого, повернула значення відповідне останньому, невластивому в списку кодом, – 3187848593, ціна якого 2479,46 рублів. Якщо поле “Інтервальний перегляд залишити незаповненим, функція працюватиме за тією ж схемою, що і зі значенням одиниці.
При введенні в поле аргументу значення 0 функція поверне тільки значення, відповідне рівному шуканого. При наявності в діапазоні пошуку повторень – функція візьме перше збіг. При використанні формули з аргументом 0 функція працює набагато довше, однак набагато точніше.
Особливості використання ВВР по декільком умовам
Застосування функції ВПР – процес часто творчий, що вимагає від користувача математичного мислення. Часто виникає необхідність знайти відповідність не по одному стовпцю, а по двох і навіть більше. З допомогою додаткових дій застосувати функцію VLOOKUP теж можна. Потрібно створити додаткові стовпці в обох таблицях, де об’єднати дані з розглянутих стовпців. Зробити це можна з допомогою функції “ЗЧЕПИТИ” або значка “&”.
Як користуватися функцією ВВР, якщо дані на різних аркушах
Як користуватися VLOOKUP в Excel на різних аркушах, у різних файлах?. Часто користувачі створюють якісь довідники, з якими необхідно порівняти і знайти відповідності. Відмінності роботи в таких умовах полягає в дещо іншому вигляді поля “Таблиця” у вікні аргументи функції. Перед введенням діапазону необхідно проставити номер аркуша (якщо дані знаходяться в одній книзі) або найменування файла (якщо дані не в одній книзі). Якщо у вищевказаному прикладі прайс скопіювати в окремий файл, назвати його “Прайс” і, за допомогою функції ВВР, шукати там ціни, це буде виглядати наступним чином:
Всі вищевказані дії можна зробити за допомогою посилання. Потрібно поставити курсор в полі “Таблиця”, перейти в потрібний файл і мишею виділити діапазон. Іноді буває, що таблиця не вставляється як посилання у вікно. Тоді необхідно зробити наступне: відкрити первісну таблицю, де потрібно провести розрахунки, потім через меню “Файл” – “Відкрити” знайти другу таблицю. Відкриті таким чином файли взаємодіють безвідмовно.
Всі вищевказані дії можна зробити за допомогою посилання. Потрібно поставити курсор в полі “Таблиця”, перейти в потрібний файл і мишею виділити діапазон. Іноді буває, що таблиця не вставляється як посилання у вікно. Тоді необхідно зробити наступне: відкрити первісну таблицю, де потрібно провести розрахунки, потім через меню “Файл” – “Відкрити” знайти другу таблицю. Відкриті таким чином файли взаємодіють безвідмовно.
Важити файл, в якому знаходяться з формули ВВР, посилання на інші файли буде значно більше, ніж без них. Це може зробити проблематичним, наприклад, пересилання файлу. Для того, щоб уникнути цих неприємностей, потрібно перетворити формулу значення. На стрічці вибирається пункт “Дані” та команда “Змінити зв’язку”. Навіть якщо дані взяті не з іншого файлу, завжди корисно замінити формули значеннями – це робить розрахунки більш надійними.
Випадаючий список для полегшення роботи з ВВР
Часто функція VLOOKUP не працює при дрібних неспівпадання даних. Зайвий пропуск у тексті, то дані занесені з помилками. Уникнути всіх цих неприємностей можна, використовуючи для введення значень випадаючий список в Excel. Заводити його має сенс при роботі з постійно повторюваними даними. Якщо існує якийсь довідник, який використовується в якості таблиці для порівняння, то діапазон порівняння можна взяти дані для випадаючого списку та використовувати для формування таблиці, в яку потім функцією ВВР будуть підставлятися дані. Курсор ставиться в комірку, куди потрібно ввести значення. Далі на стрічці знаходиться підрозділ “Дані”, вибирається команда “Перевірка даних”. У діалоговому вікні в полі “Тип даних” вноситься значення “Список”. У полі “Джерело” прописується діапазон довідкової таблиці. Випадаючий список сформований. Тепер при заповненні таблиці повна відповідність значень гарантовано.
Приклад використання функції VLOOKUP в Excel
Як користуватися VLOOKUP в Excel, кожен користувач вирішує сам, спираючись на практичні завдання у своїй роботі. Враховуючи всі нюанси, наведені вище, можна довести до логічного кінця попередній приклад і побудувати зручний і чіткий алгоритм застосування функції ВПР на практиці. Отже, є калькуляція витрат запасних частин на ремонт техніки. Також є прайс-лист на запасні частини. Економісту підприємства потрібно знайти витрати на запасні частини при ремонті.
Спочатку необхідно правильно прописати формулу.
Таким чином, з допомогою функції ВВР (VLOOKUP) з’явиться ціна, що відповідає коду в першому рядку. Потрібно розмножити формулу вниз, для цього виділяють клітинку з формулою і тягнуть вниз за квадрат в правому кутку комірки. У стовпці D функція повернула ціни, що відповідають кодам. Необхідно замінити формули значеннями. Для цього потрібно виділити заповнені клітинки стовпця D, скопіювати і вставити значення. Далі потрібно створити стовпець “Сума”, де ввести формулу добутку кількості і ціни, потім з допомогою підсумовування вивести підсумок за витратами.
Це приклад того, як працює VLOOKUP в Excel.
Помилки при використанні функції ВПР
На початковому етапі використання замість потрібних значень функція часто вказує на різні види помилок. Знати, що означає та чи інша помилка, – вірний шлях до її швидкому виправленню. Найбільш часто повертаються помилки:
- “Н/Д” – найпоширеніший тип помилки. Може виникнути з кількох причин.
- “ПОСИЛАННЯ” – ця помилка часто виникає при неправильному зазначенні номера стовпця, якщо стільки стовпців немає в обраному діапазоні. У таких випадках необхідно пам’ятати, що номер проставляють, починаючи з лівого стовпця виділеного діапазону, а не таблиці в цілому.
- “ІМ’Я” – помилка повертається часто при неправильному занесення тексту в “Шукане значення”. Текст необхідно прописувати в лапках.
Знання, як користуватися VLOOKUP в Excel, приходять, як і все в цій програмі, з практикою. Одне тільки вивчення теорії замість ясності, найімовірніше, принесе хаос в розумінні тієї чи іншої операції в програмі. При вивченні будь-якої функції “Ексель” користувачеві завжди ефективніше спиратися на конкретну, нехай і невелику, табличку-приклад. Це дозволяє глибше зрозуміти сутність аналізу, закріпити отримані знання. Так і з функцією ВВР в Excel. При великому обсязі практичне застосування теорії показує, що вона не настільки складна, скільки корисна.
