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

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

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

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

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

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

Необхідність використання

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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