Ця стаття присвячена функції ВВР. У ній буде розглянуто покрокова інструкція функції ВВР, під назвою "". У цій статті ми докладно розглянемо опис, синтаксис та приклади функції ВПР в Excel. А також розглянемо, як використовувати та розберемо основні помилки, чому не працює функція ВВР.

Синтаксис та опис функції ВПР в Excel

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

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

Літера «В» у ВВР означає «вертикальний». Вона використовується для диференціації функції ВПР та ГПР, яка шукає значення у верхньому рядку масиву («Г» означає «горизонтальний»).

Функція ВПР доступна у всіх версіях Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

Синтаксис функції ВВР виглядає наступним чином:

ВПР(шукане_значення;таблиця;номер_стовпця;[інтервальний_перегляд])

Як бачите, функція ВВРмає 4 параметри чи аргументи. Перші три параметри обов'язкові, останній - необов'язковий.

1. потрібне_значення- Це значення для пошуку.

Це може бути або значення (число, дата чи текст), або посилання на комірку(посилання на комірку, що містить значення пошуку), або значення, яке повертається деякою іншою функцією Excel. Наприклад:

• Пошук числа: = ВПР (40; A2: B15; 2) - формула шукатиме число 40.
• Пошук тексту : =ВПР(«яблука»; A2:B15; 2) - формула шукатиме текст «яблука». Зверніть увагу, що ви завжди включаєте текстові значення в подвійні лапки.
• Пошук значення з іншого осередку : =ВПР(C2; A2:B15; 2) - формула шукатиме значення в осередку C2.

1. таблиця - це два чи більше стовпця даних.

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

Отже, наша формула = ВПР (40; A2: B15; 2) шукатиме «40» в осередках від A2 до A15, тому що A - це перший стовпець таблиці A2: B15.

1. номер_стовпця - номер стовпця в таблиці, з якої має бути повернено значення у відповідному рядку.

Найлівіший стовпець у зазначеній таблиці дорівнює 1, другий стовпець - 2, третій - 3 і т.д.

4. інтервальний_переглядвизначає, чи шукаєте ви точну відповідність (брехня) або приблизну відповідність (ІСТИНА або опущено). Цей останній параметр не є обов'язковим, але дуже важливим.

Функція ВВР в Excel приклади

Тепер давайте розглянемо кілька прикладів використання функції ВВР реальних даних.

Функція ВПР на різних аркушах

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

Щоб використовувати функцію ВВРз іншого аркуша Excel, ви повинні ввести ім'я робочого аркуша і знак оклику в аргументі таблиця перед діапазоном осередків, наприклад, =ВПР(40;Лист2!A2:B15;2). Формула вказує, що діапазон пошуку A2:B15 знаходиться в Аркуш2.

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

Формула, яку ви бачите на зображенні нижче, шукає текст у комірці А2 («Продукт 3») у стовпці A (1-й стовпець діапазону пошуку A2:B9) на аркуші «Ціни»:

ВПР(A2;Ціни!$A$2:$B$8;2;БРЕХНЯ)

Функція ВПР в Excel - Функція ВПР на різних аркушах

Як використовувати іменований діапазон або таблицю у формулах ВПР

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

Щоб створити іменований діапазон, просто виберіть комірки та введіть будь-яке ім'я в полі «Ім'я», ліворуч від панелі «Формула».

Функція ВПР в Excel - Присвоєння імені діапазону

Тепер ви можете написати наступну формулу ВПР, щоб отримати ціну Продукту 1:

ВПР («Продукт 1»; Продукти; 2)

Функція ВПР в Excel - приклад функції ВПР з ім'ям діапазону

Більшість імен діапазонів Excel застосовуються до всієї книги, тому вам не потрібно вказувати ім'я робочого аркуша, навіть якщо ваш діапазон пошуку знаходиться на іншому аркуші. Такі формули набагато зрозуміліші. Крім того, використання іменованих діапазонів може бути гарною альтернативою на комірки. Оскільки іменований діапазон не змінюється, коли формула копіюється в інші осередки, і ви можете бути впевнені, що діапазон пошуку завжди залишиться вірним.

Якщо ви перетворили діапазон комірок на повнофункціональну таблицю Excel (вкладка «Вставка» --> «Таблиця»), ви можете вибрати діапазон пошуку за допомогою миші, а Microsoft Excel автоматично додасть імена колонок або ім'я таблиці у формулу:

Функція ВПР в Excel - приклад функції ВПР з ім'ям таблиці

Повна формула може виглядати приблизно так:

ВПР("Продукт 1";Таблиця6[[Продукт]:[Ціна]];2)

або навіть = ВПР ( "Продукт 1"; Таблиця 6; 2).

Як і іменовані діапазони, імена шпальт є постійними, а посилання на комірки не зміняться незалежно від того, де копіюється формула ВПР.

Функція ВПР із кількома умовами

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

Функція ВВР в Excel - Таблиця вихідних даних

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

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

Отже на аркуші «Ціни» вставляємо стовпець і в осередку А2 вводимо таку формулу:

За допомогою цієї формули ми маємо значення стовпця «Продукт» та «Тип». Заповнюємо всі осередки.

Тепер таблиця для пошуку виглядає так:

Функція ВПР в Excel - Додавання допоміжного стовпця

1. Тепер у осередку С2 на аркуші «Продажи» напишемо таку формулу ВПР:

ВПР(A2Ціни!$A$1:$D$8;4;БРЕХНЯ)

Заповнюємо для інших осередків і в результаті отримуємо ціни для кожного продукту відповідно до типу:

Функція ВПР в Excel - приклад ВПР з кількома умовами

Тепер розберемо помилки функції ВПР.

Чому не працює функція ВПР

У цій частині статті ми розглянемо чому не працює функція ВВРта можливі помилки функції ВПР.

Тип помилки	Причина	Рішення
	Неправильне розташування стовпця, яким відбувається пошук	Стовпець таблиці, яким відбувається пошук ОБОВ'ЯЗКОВО має бути крайнім лівим. Перенесіть стовпець, яким відбувається пошук у крайнє ліве положення таблиці. Або створіть допоміжний стовпець, що дублює, ліворуч в таблиці.
	Не закріплено діапазон таблиці	Якщо перше значення було виведено правильно, а після протягування формули ВПР у деяких осередках зустрічається помилка #Н/Д, діапазон таблиці не закріплений. Використовуйте ($) для закріплення діапазону таблиці, щоб при заповненні формула використовувала той самий діапазон. Або використовуйте
	Не вдалося знайти точний збіг (якщо в інтервальному переглядівибрано пошук точного значення (0)
	У інтервальному переглядівиконується пошук найближчого значення (1), а таблиця , за якою відбувається пошук, не відсортована.	Сортуйте перший стовпець таблиці за зростанням найменувань.
		Використовуйте функції ПЕЧСІМ або СЖПРОБЕЛИ.
	Значення номера стовпця перевищує число стовпців у таблиці	Перевірте номер стовпця, що містить значення, що повертається.
	У формулі пропущені лапки	Якщо ви використовуєте як шуканого значенняне посилання на комірку, а текст, його необхідно укласти в лапки. Наприклад: ВПР("Продукт 1"; Ціни!$A$2:$B$8;2;0)

Сподіваюся, що тепер навіть для чайників функція ВПР в Excelбуде зрозумілим.

Батьянов Денисна правах гостьового автора розповідає у цьому пості про те, як знайти дані в одній таблиці Excel і витягти їх до іншої, а також відкриває всі секрети функції вертикального перегляду.

Використання функції СТІЛК для вказівки колонки вилучення

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

Створення складеного ключа через &»|»&

Якщо виникає необхідність шукати по кількох стовпцях одночасно, необхідно робити складовий ключ для пошуку. Якби значення, що повертається, було не текстовим (як тут у випадку з полем «Код»), а числовим, то для цього підійшла б зручніша формула СУМЕСЛІМН (SUMIFS) і складовий ключ стовпця не знадобився б зовсім.

Це моя перша стаття для Лайфгакера. Якщо вам сподобалося, то запрошую вас відвідати мій сайт, а також із задоволенням прочитаю в коментарях про ваші секрети використання функції ВПР та подібних до неї. Дякую. :)

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

Назва функції ВПР розшифровується як «функція вертикального перегляду». Англійською її найменування звучить – VLOOKUP. Ця функція шукає дані в лівому стовпці діапазону, що вивчається, а потім повертає отримане значення у вказану комірку. Простіше кажучи, ВВР дозволяє переставляти значення з комірки однієї таблиці, в іншу таблицю. З'ясуємо, як користуватись функцією VLOOKUP у Excel.

Приклад використання ВВР

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

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

Як бачимо, ціна картоплі підтягнулася до таблиці із прайс-листа. Щоб не робити таку складну процедуру з іншими товарними найменуваннями, просто стаємо в нижній правий кут заповненого осередку, щоб з'явився хрестик. Проводимо цим хрестиком до низу таблиці.

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

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

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

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

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

Натисніть по верхньому осередку в першій таблиці в стовпці Ціна, а потім кнопку «fx» у рядку формул, щоб відкрити вікно майстра функцій.

Наступне, що ми робимо, – прописуємо аргументи у запропоновані поля.

Ставте курсив у полі «Шукане_значення»і виділяйте у першій таблиці те значення, яке шукатимемо. У мене це яблуко.

У рядку «Таблиця» необхідно виділити ту, з якої будуть братися дані – не виділяйте шапку. Зверніть увагу, що крайній лівий стовпець повинен складатися зі значень, які ми шукаємо. Тобто, мені потрібне яблуко та інші фрукти, отже, їх перелік має бути в лівому стовпці області, що виділяється.

Щоб після того, як ми напишемо формулу та розтягнемо її по всьому стовпцю, обраний діапазон не зміщувався вниз, потрібно зробити посилання абсолютними: виділіть дані в полі та натисніть «F4» . Тепер адреса на осередки стала абсолютною, до них додався знак «$», і діапазон зміщуватися не буде.

Там, де номер стовпця, поставте цифру, що відповідає в другій таблиці стовпцю, дані звідки потрібно переносити. У мене прайс складається з фруктів та ціни, мені потрібне друге, тому ставлю цифру «2».

У «Інтервальний перегляд»пишемо «БРЕХНЯ» – якщо шукати потрібно точні збіги, або «Істина» – якщо значення можуть бути наближені. Для нашого прикладу вибираємо першу. Якщо нічого не вказати в даному полі, за замовчуванням вибереться друге. Потім натисніть "ОК".

Тут зверніть увагу на наступне, якщо працюєте з числами та вказуєте «Істина», то друга таблиця (це наш прайс) обов'язково має бути відсортована за зростанням. Наприклад, при пошуку 5,25 знайдеться 5,27 і візьмуться дані з цього рядка, хоча нижче може бути ще й число 5,2599 – але формула далі дивитися не буде, оскільки вона думає, що нижче за число тільки більше.

Як же працює ВВР? Вона бере значення (яблуко) і шукає його в крайньому лівому стовпці зазначеного діапазону (перелік фруктів). При збігу береться значення з цього ж рядка, тільки того стовпця, який вказаний в аргументах (2 ), і переноситься в потрібну комірку (С2 ). Формула виглядає так:

ВПР(А2; $ G $ 2: $ H $ 12; 2; Брехня)

Тепер можете розтягнути її на необхідну кількість рядків вниз, потягнувши правий нижній кут.

Усі ціни перенесені з прайсу до таблиці закупівель відповідно до назв фруктів.

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

При додаванні стовпців на аркуш дані для аргументу «Таблиця» функції автоматично зміняться. У прикладі прайс зрушений на 2 стовпці праворуч. Виділимо будь-яку комірку з формулою і бачимо, що замість $G$2:$H$12 тепер $I$2:$J$14 .

Тепер давайте розберемося, як працювати з функцією ВПР в Екселі і з списками, що випадають. Для початку потрібно зробити список, що випадає. Виділяємо комірку, зверху відкриваємо «Дані» і тиснемо по кнопці "Перевірка даних".

У вікні «Тип даних», що відкрилося, буде «Список» , нижче вказуємо область джерела - це назви фруктів, тобто той стовпець, який є і в першій і в другій таблиці. Натисніть "ОК".

Виділяю F2 та вставляю функцію ВПР. Аргумент перший - це зроблений список (F1).

Другий – таблиця постачальника із зазначеними цінами. Пам'ятайте, що тут лівий стовпець повинен збігатися з тими даними, з яких складається список, що випадає.

Вийшло щось подібне до пошуку: вибираємо фрукт і ВПР знаходить у прайсі його ціну.

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

Тиснемо по будь-якому осередку в стовпці D і вставляємо один новий.

Я назвала його Новий прайс - тут будуть нові ціни, а в стовпці зліва, вказані старі. Нова таблиця у мене знаходиться на іншому аркуші, щоб Вам стало зрозуміло, як використовувати ВВР, якщо дані розташовані на різних аркушах. У доданому стовпці виділяємо перший порожній осередок.

Вставляємо функцію та вказуємо аргументи. Спочатку те, що шукатимемо, у прикладі яблуко (А2). Для вибору діапазону з нового прайсу, поставте курсор у поле "Таблиця" і перейдіть на потрібний аркуш, у мене "Лист1".

Виділяємо мишкою необхідні стовпці та рядки, без заголовків.

Далі робимо абсолютні посилання на осередки: «Лист1!$A$2:$B$12» . Виділіть рядок і натисніть «F4» , щоб до осередків додався знак долара. Вказуємо стовпець (2 ) і пишемо «БРЕХНЯ» .

Наприкінці натисніть кнопку «ОК» .

Тепер два стовпці з новою та старою ціною розташовані поруч і можна зробити або візуальне порівняння, або застосувавши певні формули, або умовне форматування.

Сподіваюся, у мене вийшла покрокова інструкція щодо використання та застосування функції ВПР в Excel, і Вам тепер все зрозуміло.

Оцінити статтю:

(13 оцінок, середнє: 5,00 із 5)

Вебмайстер. Вища освіта за спеціальністю "Захист інформації". Автор більшості статей та уроків комп'ютерної грамотності

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

Отже, ми з вами розглянемо чотири варіанти створення підстановної функції із двома умовами:

1. Використання функції СУМПРОВИЗВ

Ну а почнемо ми з вами із найпростішого.

Використання додаткової колонки

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

Розглянемо класичний приклад. У нас є таблиця з продажами по місяцях та містах. І нам необхідно визначити значення продажів, що відповідає двом умовам: місяць – Лютий та місто – Самара.

Використання функції ВПР у класичному вигляді нам не допоможе, тому що вона зможе повернути значення, що відповідає лише одній умові. Зі стану нам допоможе вийти додатковий стовпець, в якому ми об'єднаємо значення стовпців Місяць і Місто. Для цього в осередку А2 прописуємо формулу =B2&C2 і простягаємо цю формулу до осередку А13. Тепер ми зможемо використовувати значення стовпця А, щоб повернути потрібне значення. Прописуємо в осередку G3 формулу:

ВВР (G1A2: D13; 4; 0)

Дана формула поєднує дві умови осередків G1 і G2 в один рядок і переглядає його в стовпці А. Після того, як потрібна умова була виявлена, формула повертає значення четвертого стовпця таблиці A1:D13, тобто. стовпця Продаж.



Використання функції ВИБІР для створення нової таблиці перегляду

Якщо з якихось причин використання додаткового стовпця для нас є не варіантом, ми можемо використовувати .

Використання функції ВИБІР передбачає створення нової таблиці для перегляду, в якому значення стовпців Місяцьі Містовже об'єднані. Наша формула буде виглядати так:

ВПР(G1ВИБІР((1;2);B2:B13&C2:C13;D2:D13);2;0)

Основний момент даної формули полягає в частині ВИБІР((1;2);B2:B13&C2:C13;D2:D13), який робить дві речі:

1. Поєднує значення стовпців Місяцьі Містов один масив: ЯнвМосква, ФевМосква...
2. Об'єднує два масиву таблицю, що складається з двох стовпців.

Результатом роботи цієї функції буде таблиця, яка виглядає так:



Тепер формула стала зрозумілішою.

ВАЖЛИВО: Оскільки ми використовували формулу масиву, після закінчення введення формули натисніть Ctrl+Shift+Enter, щоб дати знати програмі про наші наміри. Після натискання цієї комбінації клавіш, програма автоматично встановить фігурні дужки на початку та наприкінці формули.

Використання функцій ІНДЕКС та ПОШУКПОЗ

Третій спосіб, який ми з вами розглянемо, також передбачає використання формули масиву та задіяє функції ІНДЕКС та ПОШУКПОЗ.

Формула буде виглядати так.

ІНДЕКС(D2:D13;ПОШУКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0))

Давайте розберемо, що робить кожна частина цієї формули.

Спочатку розглянемо функцію ПОШУКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0). В даному випадку послідовно порівнюється значення осередку G1 з кожним значенням осередків діапазону B2:B13 і повертається ІСТИНА, якщо значення збігаються і БРЕХНЯ, якщо ні. Таке ж порівняння проводиться зі значенням осередку G2 та діапазоном C2:C13. Далі ми порівнюємо обидва ці масиви, що складаються з ІСТИНА і БРЕХНЯ. Комбінація ІСТИНА * ІСТИНА дає нам результат 1 (ІСТИНА). Давайте подивимося на картинку нижче, яка допоможе пояснити принцип роботи наочно.



Тепер ми можемо сказати, де знаходиться рядок, що задовольняє обидві умови. Функція ПОШУКПОЗ відшукує положення 1 в результуючому масиві і повертає 6, так як одиниця зустрічається в шостому рядку. Далі функція ІНДЕКС повертає значення шостого рядка діапазону D2: D13.

Використання СУМПРОВИЗВ

Одна із найпотужніших формул Excel. У мене навіть є окрема стаття, присвячена цій формулі. Наш четвертий спосіб використання кількох умов полягає у написанні формули з функцією СУММПРОИЗВ. І виглядатиме вона таким чином:

СУМПРОВИЗВ((B2:B13=G1)*(C2:C13=G2);D2:D13)

Принцип роботи цієї формули схожий на принцип роботи попереднього підходу. Створюється віртуальна таблиця, в якій порівнюються значення осередків G1 та G2 з діапазонами B2:B13 та С2:С13 відповідно. Далі обидва цих масиву зіставляються і виходить масив з одиниць і нулів, де одиниця присвоюється тому рядку, в якому обидві умови збіглися. Далі цей віртуальний масив перемножується на діапазон D2: D13. Так як у нашому віртуальному масиві буде лише одна одиниця у шостому рядку, формула поверне результат 189.

Ця функція не працюватиме, якщо в діапазоні D2:D13 є текстові значення.

Щоб зрозуміти, як працює ця формула, рекомендую прочитати статтю про функцію СУММПРОІЗ.

ПІДСУМК

Отже, який спосіб використовувати? Хоча всі вони працюють стабільно, я віддаю перевагу першому способу. У своїй щоденній роботі я волію працювати з файлами, які прості для розуміння і піддаються змінам. Обидві ці вимоги відповідають умовам першого підходу.

Для кращого розуміння формул, ви можете розібратися в сьогоднішній статті.