Функція ВПР. Використання функції ВПР. Excel - ВПР

Функція ВПР. Використання функції ВПР. Excel - ВПР

Прикладна програма Excel популярна завдяки своїй доступності і простоті, оскільки не вимагає особливих знань і навичок. Табличний вид надання інформації зрозумілий будь-якому користувачеві, а широкий набір інструментів, що включають "Майстер функції", дозволяє проводити будь-які маніпуляції та розрахунки з наданими даними.

Однією з широко відомих формул Excel є вертикальний перегляд. Використання функції ВПР на перший погляд здається досить складним, але це тільки спочатку.

Як працює ВПР Excel

При роботі з формулою ВПР слід враховувати, що вона здійснює пошук шуканого значення виключно за стовпчиками, а не за рядками. Для застосування функції потрібна мінімальна кількість стовпчиків - два, максимальна відсутня.

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

Потреба використання

Функція ВПР приходить на допомогу оператору, коли потрібно швидко знайти і застосувати в подальших розрахунках, аналізі або прогнозі певне значення з таблиці великих розмірів. Головне при використанні даної формули - стежити, щоб зазначена область пошуку була правильно обрана. Вона повинна включати всі записи, тобто починаючи з першого по останній.


Найчастіший випадок застосування ВПР (функція Excel) - це порівняння або додавання даних, що знаходяться в двох таблицях, при використанні певного критерію. Причому діапазони пошуку можуть бути великими і вміщувати тисячі полів, розміщуватися на різних аркушах або книгах.

Показано функцію ВПР, як користуватися нею, як проводити розрахунки, як приклад на малюнку вище. Тут розглядається таблиця розмірів роздрібних продажів залежно від регіону і менеджера. Критерієм пошуку служить конкретний менеджер (його ім 'я та прізвище), а шуканим значенням є сума його продажів.

У результаті роботи функції ВПР (VLOOKUP) формується нова таблиця, в якій конкретному шуканому менеджеру швидко зіставляється його сума продажів.

Алгоритм заповнення формули

Розташована формула ВПР у вкладці "Майстер функцій" та розділі "Посилання та масиви". Діалогове вікно функції має такий вигляд:

Аргументи у формулу вносяться в порядку черги:

  • Значенням є те, що повинна знайти функція, і варіантами якого є значення комірки, її адреса, ім 'я, задане їй оператором. У нашому випадку - це прізвище та ім 'я менеджера.
  • Таблиця - діапазон рядків і стовпчиків, в якому шукається критерій.
  • Номер стовпчика - його порядкове число, в якому розташовується сума продажів, тобто результат роботи формули.
  • Інтервальний перегляд. Він вміщує значення або БРЕХНЯ, або ПРАВДА. Причому БРЕХНЯ повертає тільки точний збіг, ІСТИНА - дозволяє пошук приблизного значення.

Приклад використання функції

Функція ВПР приклад використання може мати наступний: при веденні справ торгового підприємства в таблицях Excel в стовпці А записано найменування продукції, а в колонці В - відповідна ціна. Для складання пропозиції в стовпчику С потрібно відшукати вартість на певний продукт, яку потрібно вивести в колонці Д.

Приклад організації таблиці


А

У

С

Д

продукт 1

90

продукт 3

60


продукт 2

120

продукт 1

90

продукт 3

60

продукт 4

100


продукт 4

100

продукт 2

120

Формула, записана в Д, виглядатиме так: = ВПР (С1; А1:В5; 2; 0), тобто = ВПР (шукане значення; діапазон даних таблиці; порядковий номер стовпчика; 0). Як четвертий аргумент замість 0 можна використовувати БРЕХНЮ.

Для заповнення таблиці пропозиції отриману формулу необхідно скопіювати на весь стовпчик Д.

Ви можете закріпити область робочого діапазону даних за допомогою абсолютних посилань. Для цього вручну проставляються знаки $ перед буквеними і чисельними значеннями адрес крайніх лівих і правих комірок таблиці. У нашому випадку формула приймає вигляд: = ВПР (С1; $ А $1:$ У $5; 2; 0).


Помилки під час використання

Функція ВПР не працює, і тоді з 'являється повідомлення в стовпчику виводу результату про помилку (# N/A або # Н/Д). Це відбувається в таких випадках:

  • Формула введена, а стовпчик шуканих критеріїв не заповнений (в даному випадку стовпчик С).
  • У стовпчик C внесено значення, відсутнє у стовпчику А (у діапазоні пошуку даних). Для перевірки наявності шуканого значення слід виділити стовпчик критеріїв і у вкладці меню "Правка" - "" Знайти "" вставити даний запис, запустити пошук. Якщо програма не знаходить його, значить воно відсутнє.
  • Формати комірок стовпчиків А і С (шуканих критеріїв) різними, наприклад, у одній - текстовий, а в іншій - числовою. Змінити формат комірки можна, якщо перейти до редагування комірки (F2). Такі проблеми зазвичай виникають при імпортуванні даних з інших прикладних програм. Для уникнення подібного роду помилок у формулу ВПР є можливість вбудовувати такі функції: ЗНАЧНИЙ або ТЕКСТ. Виконання цих алгоритмів автоматично перетворює формат комірок.
  • У коді функції присутні недрукарські знаки або пробіли. Тоді слід уважно перевірити формулу на наявність помилок вводу.
  • Вказано приблизний пошук, тобто четвертий аргумент функції ВПР має значення 1 або ІСТИНА, а таблиця не відсортована за висхідним значенням. У цьому випадку стовпчик шуканих критеріїв потрібно відсортувати за зростанням.

Причому при організації нової зведеної таблиці задані шукані критерії можуть перебувати в будь-якому порядку і послідовності і не обов 'язково вміщуватися повним списком (часткова вибірка).

Особливості використання як інтервального перегляду 1 або ІСТИНИ

Помилка під № 5 є досить поширеною і наочно зображена на малюнку нижче.

У цьому прикладі список імен відповідно до нумерації відсортовано не за зростанням, а за спадним значенням. Причому як інтервальний перегляд використано критерій ІСТИНА (1), який відразу перериває пошук при виявленні значення більшого, ніж шукане, тому видається помилка.

При застосуванні 1 або ІСТИНИ в четвертому аргументі потрібно стежити, щоб стовпчик з шуканими критеріями був відсортований за зростанням. При використанні 0 або БЖІ ця необхідність відпадає, але також відсутня тоді можливість інтервального перегляду.

Просто слід враховувати, що особливо важливо сортувати інтервальні таблиці. Інакше функція ВПР буде виводити в комірки неправильні дані.

Інші нюанси при роботі з функцією ВПР

Для зручності роботи з такою формулою можна назвати діапазон таблиці, в якій проводиться пошук (другий аргумент), як це показано на малюнку.


У даному випадку область таблиці продажів озаглавлена. Для цього виділяється таблиця, за винятком заголовків стовпчиків, і в полі імені (ліворуч під панеллю вкладок) присвоюється їй назва.

Інший варіант - озаглавити - передбачає виділення діапазону даних, потім перехід в меню "Вставка" - "" Ім 'я "-" "Присвоїти" ".

Щоб використовувати дані, розміщені на іншому аркуші робочої книги, за допомогою функції ВПР, необхідно у другому аргументі формули прописати розташування діапазону даних. Наприклад, = ВПР (А1; Аркуш 2! $ А $1:$ У $5; 2; 0), де Ліст2! - є посиланням на необхідний аркуш книги, а $ А $1:$ У $5 - адреса діапазону пошуку даних.

Приклад організації навчального процесу з ВПР

Досить зручно в Excel ВПР-функцію застосовувати не тільки фірмам, що займаються торгівлею, а й навчальним установам для оптимізації процесу зіставлення учнів (студентів) з їх оцінками. Приклади даних завдань показано на малюнках нижче.

Існують дві таблиці зі списками студентів. Одна з їхніми оцінками, друга вказує вік. Необхідно зіставити обидві таблиці так, щоб нарівні з віком учнів виводилися і їх оцінки, тобто ввести додатковий стовпець у другому списку.

Функція ВПР відмінно справляється з вирішенням даного завдання. У стовпчику G під заголовком "Оцінки" записується відповідна формула: = ВПР (Е4, В3:С13, 2, 0). Її потрібно скопіювати на всю колонку таблиці.

У результаті виконання функція ВПР видасть оцінки, отримані певними студентами.

Приклад організації пошукової системи з ВПР

Ще один приклад застосування функції ВПР - це організація пошукової системи, коли в базі даних згідно з заданим критерієм слід знайти відповідне йому значення. Так, на малюнку показаний список з кличками тварин і їх приналежність до певного виду.

За допомогою ВПР створюється нова таблиця, в якій легко знайти на прізвисько тварини його вид. Актуальні подібні пошукові системи при роботі з великими списками. Щоб вручну не переглядати всі записи, можна швидко скористатися пошуком і отримати необхідний результат.