ВПР - це функція Excel, що дозволяє виконувати пошук в певному стовпці за даними з іншого шпальти. Функція ВПР в Excel використовується також і для перенесення даних з однієї таблиці в іншу. Існує три умови:
- Таблиці повинні розташовуватися в одній книзі Excel.
- Шукати можна тільки серед статичних даних (не формулою).
- Умова пошуку повинно розташовуватися в першому стовпці використовуваних даних.
Формула ВВР в Excel
Синтаксис ВВР в русифікованому Excel має вигляд:
ВПР (критерій пошуку; діапазон даних; номер стовпчика з результатом; умова пошуку)
У дужках вказані аргументи, необхідні для пошуку підсумкового результату.
Критерій пошуку
Адреса комірки аркуша Excel, в якій зазначаються дані для здійснення пошуку в таблиці.
діапазон даних
Всі адреси, серед яких здійснюється пошук. В якості першого стовпця слід вказати той, в якому розташований критерій пошуку.
Номер стовпця для підсумкового значення
Номер стовпця, звідки буде братися значення при знайденому збігу.
Умова для пошуку
Логічне значення (істина / 1 або брехня / 0), яке вказує приблизне збіг шукати (1) або точне (0).
ВПР в Excel: приклади функції
Принцип роботи функції простий. Перший аргумент містить критерій для пошуку. Як тільки знайдено збіг в таблиці (другий аргумент), то з потрібного стовпця (третій аргумент) знайденої рядки береться інформація і підставляється в клітинку з формулою.
Просте застосування ВВР - пошук значень в таблиці Excel. Він має значення в великих обсягах даних.
Знайдемо кількість фактично випущеної продукції за назвою місяця.
Результат виведемо праворуч від таблиці. В осередку з адресою H3 будемо вводити шукане значення. У прикладі тут буде вказуватися назва місяця.
В осередку H4 введемо саму функцію. Це можна робити вручну, а можна скористатися майстром. Для виклику поставте покажчик на осередок H4 і натисніть значок Fx близько рядки формул.
Відкриється вікно майстра функцій Excel. У ньому необхідно знайти ВВР. Виберіть в списку, що випадає "Повний алфавітний перелік" перед початком введення ВВР. Виділіть знайдену функцію і натисніть "ОК".
З'явиться вікно ВВР для таблиці Excel.
Щоб вказати перший аргумент (критерій), поставте курсор в перший рядок і клацніть по комірці H3. Її адреса з'явиться в рядку. Для виділення діапазону поставте курсор у другому рядку і почніть виділяти мишею. Вікно згорнеться до рядка. Це робиться для того, щоб вікно не заважало бачити Вам весь діапазон і не заважало виконувати дії.
Як тільки Ви закінчите виділення і відпустіть ліву кнопку миші, вікно повернеться в свій нормальний стан, а у другому рядку з'явиться адреса діапазону. Він обчислюється від лівої верхньої комірки до правої нижньої. Їх адреси розділені оператором ":" - беруться всі адреси між першим і останнім.
Переводите курсор в третій рядок і вважайте, з якого стовпця будуть братися дані при знайденому збігу. У нашому прикладі це 3.
Останній рядок залиште порожній. За замовчуванням значення дорівнюватиме 1, подивимося, яке значення виведе наша функція. Натисніть "ОК".
Результат бентежить. "Н / Д" означає некоректні дані для функції. Ми не вказали значення в осередку H3, і функція шукає порожнє значення.
Введемо назву місяця і значення зміниться.
Тільки воно не відповідає дійсності, адже даний фактична кількість випущеної продукції в січні дорівнює 2000.
Це вплив аргументу "Умова пошуку". Змінимо його на 0. Для цього поставте покажчик на клітинку з формулою і знову натисніть Fx. У вікні, введіть "0" в останній рядок.
Натискайте "ОК". Як бачимо, результат змінився.
Щоб перевірити друга умова з початку нашої статті (серед формул функція не шукає) змінимо умови для функції. Збільшимо діапазон і спробуємо вивести значення з шпальти з обчислюються значеннями. Вкажіть значення як на скріншоті.
Натисніть "Ок". Як бачите, результат пошуку виявився 0, хоча в таблиці стоїть значення 85%.
ВПР в Excel "розуміє" тільки фіксовані значення.
Порівняння даних двох таблиць Excel
ВПР в Excel може бути використана для порівняння даних двох таблиць. Наприклад, нехай у нас є два листа з даними про випуск продукції двома цехами. Ми можемо порівняти фактичний випуск для обох. Нагадаємо, що для перемикання між листами служать їх ярлики в нижній частині вікна.
На двох аркушах ми маємо однакові таблиці з різними даними.
Як бачимо, план випуску у них однаковий, а ось фактичний відрізняється. Перемикатися і порівнювати через підрядник навіть для невеликих обсягів даних дуже незручно. На третьому аркуші створимо таблицю з трьома стовпцями.
У осередок B2 введемо функцію ВПР. Як перший аргумент вкажемо клітинку з місяцем на поточному аркуші, а діапазон виберемо з листа "Цех1". Щоб при копіюванні діапазон не зміщувати, натисніть F4 після вибору діапазону. Це зробить посилання абсолютної.
Розтягніть формулу на весь стовпець.
Аналогічно введіть формулу в наступний стовпець, тільки діапазон виділяйте на аркуші "Цех2".
Після копіювання Ви отримаєте зведений звіт з двох аркушів.
Підстановка даних з однієї таблиці Excel в іншу
Виконується ця дія аналогічно. Для нашого прикладу можна не створювати окрему таблицю, а просто ввести функцію в стовпець будь-який з таблиць. Покажемо на прикладі першої. Встановіть покажчик в останній стовпець.
І в клітинку G3 помістіть функцію ВПР. Діапазон знову беремо з сусіднього листа.
В результаті стовпець другий таблиці буде скопійований в першу.
Ось і вся інформація про непомітною, але корисної функції ВПР в Excel для чайників. Сподіваємося, вона допоможе Вам при вирішенні задач.
Відмінного Вам дня!
