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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Відео: Функція ВПР в Excel. від А до Я

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

Відео: Використання функції ВПР в MS Excel 2007

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

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

Відео: Вкладені функції ВПР і ГПР в Excel

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

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

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

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

Відео: Функція ВПР в Excel 2013. Уроки Excel - Майстерний курс

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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