Функція ВПР в Excel — не так страшно, як здається: покроковий гайд для легкої роботи
Microsoft Excel залишається надійним помічником у царині чисел та таблиць протягом десятиліть. За різними підрахунками програмою користуються від 1,1 до 1,5 мільярда людей для особистих та професійних потреб. Універсальність, зручність використання та можливість інтеграції з різними бізнес-процесами зробили Excel незамінним інструментом.
Цікаво, що офісні працівники витрачають 38% свого робочого часу на використання Excel. Незважаючи на це, більше половини з них ніколи не проходили жодної формальної підготовки. І даремно, адже інструмент Excel надає широкий набір функцій, які полегшують роботу з даними, і ВПР є однією з них.
Отож, якщо впр ексель звучить як незрозумілий набір літер, тоді ця стаття для вас. Сьогодні ми розберемось із тим, що це за функція, кому вона стане у пригоді та як її використовувати.
Що таке функція ВПР в Excel і навіщо її використовувати
Уявімо, що перед вами стоїть завдання порахувати прибуток магазину за попередній місяць. У вас є дві таблиці — одна із цінами, а друга — із кількістю проданого товару. Кожна із них налічує кілька тисяч рядків. Отож, шляхів кілька: принести у жертву свій сон та рахувати все вручну, або ж довірити цей процес Microsoft Excel та спеціальній функції вертикального перегляду.
ВПР (або VLOOKUP) — це вбудована функція, яку використовують для швидкого пошуку конкретного значення в електронній таблиці. Функція дозволяє працювати із даними одразу з кількох таблиць, що значно економить час та зусилля.
Як це працює: функція приймає певний набір символів як запит та шукає збіг із ним у крайньому лівому стовпці заданого діапазону. Після цього значення із комірки, яка розташована в сусідньому стовпчику, копіюється у той же рядок.
Щоб використовувати ВПР в Excel, вам потрібно скористатися формулою та ввести окремі значення відповідно до своїх потреб:
- lookup_value: клітинка, яка містить значення, яке потрібно знайти. Наприклад: А5.
- table_array: місце, де, на вашу думку, розташоване потрібне значення, і де ви хочете, щоб Excel його шукав. Наприклад: A1:D10
- column_index_num: стовпець, де розташоване значення. Наприклад: 4.
- range_lookup: для цього можна задати параметри TRUE (приблизна відповідність) або FALSE (точна відповідність).
Початок роботи із ВПР Excel: покрокова інструкція з прикладами
У цьому блоці ми розглянемо, як працює зазначена формула на практиці. Однак для того, щоб скористатися усіма перевагами від використання магічної ВПР, озбройтеся такою інформацією:
- значення, яке потрібно знайти;
- діапазон, в якому розташоване шукане значення;
- номер стовпця в діапазоні, що містить значення;
- за бажанням визначтесь із параметрами TRUE або FALSE.
Підсумовуючи, остаточна формула виглядатиме так:
=ВПР(потрібне значення; діапазон з шуканим значенням; номер стовпця в діапазоні із шуканим значенням; (TRUE) або (FALSE)).
Важливо: формула може не спрацювати, якщо дані розташовані у двох окремих таблицях. У такому випадку краще зробити одну спільну таблицю із різними вкладками, і у подальшому працювати з ними.
Отож, у вас є кілька таблиць, серед яких потрібно знайти необхідні дані. Для цього за допомогою кнопки “fx” необхідно викликати “Майстер функцій”. Інші способи: комбінація SHIFT та F3; вкладка “Формула”, категорія “Посилання та масиви” та вибір функції ВПР.
Перед нами відкрилось вікно із аргументами функції. Як їх заповнювати:
- У полі “Значення підстановки” додаємо дані, які потрібно знайти. У полі “Таблиця” вказуємо, які значення функція має зіставити.
- У полі “Номер стовпця” ставимо вказуємо номер стовпця із даними, які потрібно “підтягнути” в першу таблицю.
- У полі “Точність пошуку”: TRUE, якщо потрібні приблизні дані; FALSE, якщо потрібні точні дані (найбільш оптимально).
Загальний приклад для функції ВПР Excel
А тепер пояснимо на конкретному прикладі: припустимо, що нам потрібно заповнити ціну на абрикоси у стовпчику F. Ціна на абрикоси вказана у таблиці A:B. Значення, яке потрібно перетягнути, вказана в другому стовпчику. Оскільки назви товарів в обох таблицях збігаються, потрібно використати точний пошук — FALSE.
Прописуємо відповідну формулу: =VLOOKUP(D3;A:B;2;0) та протягуємо результат на весь стовпчик.
Приклад 1. Порівняння двох таблиць
Тепер уявімо, що у прайсі змінилися ціни, і тепер їх потрібно порівняти. Для початку створюємо стовпчик “Нова ціна” і прописуємо формулу ВПР. При цьому нову ціну перетягуємо у відповідному значенні:
Приклад 2. ВПР з кількома умовами
На жаль, при виконанні повсякденних робочих завдань попередніх прикладів використання може бути недостатньо. Так, іноді значення потрібно перетягувати за двома та більше умовами.
Наприклад, у нас є таблиця, де ціна присвоюється артикулу товару. Друга таблиця містить розшифровку цих артикулів. Завдання: заповнити ціну в останній таблиці.
Прописуємо формулу: =VLOOKUP(VLOOKUP(G3;$D$3:$E$15;2;0);$A$3:$B$15;2;0) та протягуємо на весь стовпчик.
Приклад 3. Випадаючий список
Тепер ми маємо список товарів, для яких потрібно визначити ціну. Перший крок — створення випадаючого списку. Для початку ставимо курсор у клітинку, де буде назва товару. Переходимо у розділ “Дані” та обираємо “Перевірка даних”.
У комірці “Тип даних” обираємо список, у полі “Джерело” обираємо назву товарів у стовпчику G. Формула підтягується автоматично.
Спадаючий список створено. Далі розраховуємо ціну за формулою, а результат автоматично підтягнеться відповідно до обраної назви із випадаючого списку.
Помилки, які можуть виникнути під час роботи
Усі ми вчимось на помилках, однак ніхто не любить їх припускатися. Для того, щоб пришвидшити роботу, варто діяти на випередження та ознайомитись із найпоширенішими помилками:
- Функція впр видає помилкове значення. Якщо за точність пошуку обрано “TRUE” (або взагалі не вказано жодного значення), тоді перший стовпчик необхідно відсортувати за алфавітом або в числовому порядку. Якщо цього не зробити, тоді функція видасть помилку.
- #N/A. Помилка виникає тоді, коли точність пошуку має параметр TRUE, а значення аргументу менше за найменше значення в першому стовпці таблиці.
- #REF!. Виникає тоді, коли значення номеру стовпця більше, ніж загальна кількість стовпців у таблиці.
- #VALUE!. Якщо значення у масиві таблиці менше за 1.
- #NAME?. Вказує на відсутність лапок у формулі.
- #SPILL!. Свідчить про те, що формула використовує весь стовпець як посилання. Щоб цього уникнути, варто використовувати традиційний метод і посилатися не на весь стовпчик, а на одну клітинку.
Поради щодо роботи із ВПР в Excel
Помилок уникнули, а тому саме час підвищити ефективність своїх зусиль за допомогою практичних порад. Рекомендації, що наведені нижче, допоможуть виконувати поставлені завдання швидше та якісніше:
- Використовуйте абсолютні посилання для значення точності пошуку. Такий крок допомагає спрямувати формулу в один і той самий діапазон пошуку.
- Числа та дати мають бути у форматі цифр. При роботі із формулою ВПР слід переконатися, що усі числові дані не представлені у вигляді тексту.
- Перед початком роботи відсортуйте перший стовпчик. Це особливо важливо, якщо в якості точності пошуку ви плануєте використати параметр TRUE.
- Використовуйте спеціальні символи для узагальнення. Такі символи, як знак питання (?) і зірочка (*) допоможуть знайти схожі значення набагато швидше. Наприклад: знак питання відповідає будь-якому одному символу, і зірочка — будь-якій послідовності символів.
- Перевірте дані на наявність некоректних символів. Зокрема важливо переконатися, що текстові дані не містять пробілів (як на початку, так і в кінці), лапок або інших спеціальних символів.
Висновки
Як ми бачимо, функція впр ексель є однією з найкорисніших. Її можна застосовувати по-різному, аби полегшувати повсякденну роботу із великими обсягами даних. Сподіваємось, що ця стаття допомогла вам у здійсненні перших кроків по роботі із функцією ВПР в Microsoft Excel. Звичайно, що в одному матеріалу неможливо одразу охопити усі сценарії практичного застосування формули. Натомість, до того, як перейти до виконання більш складних завдань, потрібно впевнено опанувати базу. Отож, сьогодні ми стали трохи ближчими із формулами Excel — працюємо, надихаємось і не зупиняємось!
