В 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, і Вам тепер все зрозуміло.
Оцінити статтю: (15
оцінок, середнє: 5,00
із 5)
Вебмайстер. Вища освіта за спеціальністю "Захист інформації". Автор більшості статей та уроків комп'ютерної грамотності
- А1. Це приблизне посилання на комірку. У ній може вказуватися будь-яке значення, залежно від результату, який бажає отримати користувач.
- База даних. Ім'я тієї галузі інформації, яку потрібно шукати. Поняття не настільки велике, як попереднє. Його можна використовувати лише за першими рядками або стовпцями.
- 2. Це порядковий номер стовпця, звідки програма черпатиме інформацію.
- БРЕХНЯ. Вказує на пошук точного збігу. Іноді вказуються інші Додаткові параметрицього слова. І програма при цьому шукатиме всі збіги та визначатиме найближчі значення.
- Коли треба визначити інформацію у великій таблиці чи знайти все повторювані дані та збіги.
- У викладацькому середовищі. Вчитель завжди може на прізвище своїх учнів за лічені миті знайти їх відвідуваність, успішність та іншу інформацію. Особливо корисною вона є, коли список студентів є великим, а викладач не може запам'ятати кожного з них.
- У роздрібній торгівлі. Якщо використовувати функцію для пошуку ціни, складу чи артикулу товару, то людина зможе швидко відповісти на розпитування покупців.
- Використовуйте вже створений документ або відкрийте новий порожній лист;
- Клацніть на клавішу «Формули», як показано на малюнку нижче;
- У рядку пошуку надрукуйте ВПР або VLOOKUP залежно від мови програми;
- Налаштуйте категорію «Повний список»;
- Клацніть на «Знайти» .
- <ЧТО>- Перший елемент. Замість нього потрібно прописати саме те значення, яке ви хочете знайти в таблиці. Також можна вписувати адресу осередку в таблиці;
- <НОМЕР_СТОЛБЦА>- тут потрібно надрукувати номер стовпчика, у межах якого здійснюватиметься перебір даних.
- <ГДЕ>- тут користувач визначає кількість осередків, задаючи їх розмірність як двовимірного масивуданих. Перший стовпчик – це елемент «ЩО»;
- <ОТСОРТИРОВАНО>- цей елемент функції ВВР відповідає за сортування першого стовпця за зростанням (перший стовпчик для «ДЕ»). В результаті успішного сортування значення стає істинним (одиниця). Якщо виникають будь-які неточності або помилки під час введення параметрів, з'являється помилкове значення сортування (нуль). Варто зауважити, що під час завдання ВВР<ОТСОРТИРОВАНО>можна пропустити, і тоді його значення за умовчанням сприймається як істина.
- Коли необхідно розділити значення об'єкта табличного процесора з його діапазонів;
- Для таблиць, у яких параметр ДЕ може містити кілька ідентичних значень. У такому разі, формула поверне тільки те, що знаходиться в останній рядку щодо масиву;
- Коли потрібно шукати значення, які більші за те, що може міститися в першому стовпчику. Так ви знайдете останній рядок таблиці практично миттєво.
ВПР Excel - це функція у відповідній програмі, яка відрізняється красою та простотою. Вона має безліч різних застосувань, її використовують у абсолютно різних сферах: починаючи від навчання та закінчуючи роздрібною торгівлею. Основна концепція функції полягає в тому, щоб шукати збіги в одній або кількох таблицях. Так можна легко знайти цікаву інформацію, витративши мінімум часу.
Загальна інформація
Excel – що це таке? Її також називають VLOOKUP в англомовній версії. Це одна з найпоширеніших функцій масивів та посилань. Фахівці, що складають шкалу BRP ADVICE, виставили рівень складності, що дорівнює 3 або 7.
Ця функція дозволяє швидко знайти у великій таблиці ті значення з рядків або стовпців, які необхідні користувачеві. Перший параметр ця програма знаходить самостійно. Стовпець вказується користувачем. Існує інша функція ДПР, де людиною відзначається рядок. Стовпець вона знаходить самостійно.
Якщо користувач вже мав справу з посиланнями та масивами, то розібратися у діях ВПР буде просто. У різних табличних документах можна зробити виноску на конкретну комірку. Її ставлять у приклад або, навпаки, вказують як виняток. У завданні для ВВР зазвичай вказується осередок у вигляді A1, D9, K8 і так далі.
Іноді посилання подається в іншому вигляді (R1C1). Одним словом, відзначається стовпець і рядок, на перетині яких знаходиться потрібна для користувача інформація. Програма отримує точну вказівку, де треба шукати ці дані.
Параметри функції на прикладі
У функції ВПР Excel спочатку вказується номер рядка, потім слідує позначення стовпця. Приблизно має вийти щось на кшталт:
ВПР(А1;База_даних;2;БРЕХНЯ)
Параметри функції при цьому означають таке:
На жаль, функція не може працювати професійніше. Деякі дані користувачеві потрібно пам'ятати (номер рядка або стовпця). Інакше він зможе знайти потрібну йому інформацію.
Аргументи ВВР
Щоб говорити, як працює функція ВПР в Excel, необхідно ознайомитися з її аргументами. Першим є потрібне значення. Воно визначає параметри пошуку, який шукатиме програма в першому стовпці таблиці. Вона не може працювати з другим та наступними, функція просто не знайде цієї інформації. Усередині формули цей аргумент вказується у лапках. Винятки становлять найменування функцій.
Інший аргумент – таблиця. Вона може вказуватись у координатній системі. І у цій таблиці, першому її стовпці функція спробує знайти шуканий елемент. Він вказується спочатку (див. вище).
Третій аргумент – номер стовпця. Тут зазначається інформація, яку шукає користувач. Наприклад, він може переглянути посаду, що відповідає прізвищу з першого стовпця, його заробітну плату, позначки і так далі. Все залежить від сфери діяльності користувача.
І останній аргумент – інтервальний перегляд. Тут вказується "1" і "0" або "Брехня" і "Правда". У першому випадку дані означатимуть, що заданий пошук є приблизним. Тоді програма почне шукати всі збіги. Якщо застосовується другий варіант, тоді функція звертатиме увагу лише на точні значення.
Поширені помилки
Функція ВПР Excel ніколи не працюватиме зі збоями, якщо відзначається неправильне завдання. Тобто у будь-яких порушеннях винен лише користувач. Є три поширені помилки. По-перше, людина часто плутається в аргументах «брехня» та «істина». Перший орієнтований на пошук точного збігу. Якщо вказувати "істина", тоді функція підбирає приблизні.
По-друге, формула ВПР Excel може позначатися те щоб пошук починався з другого чи наступного стовпця. Знайти дані можна лише за першим. Тому якщо користувач вводить якусь іншу формулу, відмінну від правил, то програма просто не зможе її розпізнати.
І, по-третє, часто неправильно вказується номер стовпця, звідки потрібна інформація. У цьому випадку потрібно перевіряти ще раз дані.
Коли використовують функцію ВВР?
Про це варто поговорити докладно. Ні для кого не є секретом, що абсолютно в різних сферах використовується функція ВПР Excel. Інструкція щодо її застосування може здатися складною, але тільки на перший погляд. Інакше вона не стала б настільки поширеною.
Пам'ятайте про головне: функція шукає інформацію за вертикаллю, тобто – по стовпцях. Її застосовують у різних ситуаціях:
Одним словом, у будь-якому середовищі, коли необхідно знайти дані з таблиці, можна використовувати ВПР.
Як використовувати ВПР у таблиці?
Розібратися в цьому неважко. Щоб формула ВПР Excel дійсно працювала, спочатку потрібно зробити таблицю. Також для повноцінного використанняфункції необхідно мінімум два стовпці, максимальна кількість таких – не обмежена.
Потім у порожню комірку потрібно ввести цю формулу, куди користувач визначає параметри пошуку збігів та інформації. Порожня ніша може розташовуватися будь-де: зверху, знизу, праворуч. Комірки потрібно розширювати, щоб знайти дані. Оскільки вони розташовуються у своїх стовпцях, їх потрібно мінімум дві. Якщо параметрів пошуку більше, то кількість комірок збільшується. Потім здійснюється перевірка роботи функції та те, наскільки правильно написана формула. Для цього натискають на «перегляд значень». Можна виявити невідповідності у формулі.
ВПР англійською Excel і російському аналогу застосовується однаково. Але є пара порад від професіоналів. Щоб функція працювала краще, особливо після зміни даних, рекомендується вводити піктограму долара між масивами. Наприклад, не A1, а $1$. Коли вбиваються первинні значення, то жодних символів та пробілів між назвами рядків та стовпців не потрібно ставити.
Також рекомендується ретельно перевіряти таблицю, щоб не було зайвих розділових знаків або пробілів. Їхня наявність не дозволить програмі нормально займатися пошуком збігів, особливо коли той лише приблизний (за параметром «Істина»).
Висновок
Функція ВПР Excel (вертикальний перегляд) є простою для досвідченого користувача. Але недосвідченому в питанні людині неважко ознайомитися з правилами її використання. Після їх освоєння користувач зможе швидко знаходити інформацію, причому не має значення, наскільки великою буде таблиця. Якщо необхідно скористатися горизонтальним переглядом, то функцію ДПР задіють.
Робота з узагальнюючою таблицею має на увазі підтягування до неї значень з інших таблиць. Якщо таблиць дуже багато, ручне перенесення забере дуже багато часу, і якщо дані постійно оновлюються, це вже буде сизифов працю. На щастя, існує функція ВВР, яка пропонує можливість автоматичної вибірки даних. Розглянемо конкретні приклади роботи цієї функції.
Назва функції ВПР розшифровується як «функція вертикального перегляду». Англійською її назва звучить - VLOOKUP. Ця функція шукає дані в лівому стовпці діапазону, що вивчається, а потім повертає отримане значення у вказану комірку. Простіше кажучи, ВВР дозволяє переставляти значення з комірки однієї таблиці, в іншу таблицю. З'ясуємо, як користуватися функцією VLOOKUP Excel.
Приклад використання ВПР
Подивимося, як працює функція ВПР на конкретному прикладі.
У нас є дві таблиці. Перша їх є таблицю закупівель, у якій розміщені найменування продуктів харчування. У наступному стовпчику після найменування розташоване значення кількості товару, який потрібно закупити. Далі слідує ціна. І в останній колонці - загальна вартість закупівлі конкретного найменування товару, яка розраховується за вбитою вже в комірку формулою множення кількості на ціну. А ось ціну нам якраз і доведеться підтягнути за допомогою функції ВПР із сусідньої таблиці, яка є прайс-листом.
Як бачимо, ціна картоплі підтягнулася до таблиці із прайс-листа. Щоб не робити таку складну процедуру з іншими товарними найменуваннями, просто стаємо в нижній правий кут заповненого осередку, щоб з'явився хрестик. Проводимо цим хрестиком до низу таблиці.
Таким чином, ми підтягнули всі потрібні дані з однієї таблиці в іншу, за допомогою функції ВПР.
Як бачимо, функція ВПР не така складна, як здається на перший погляд. Розібратися в її застосуванні не дуже важко, зате освоєння цього інструменту заощадить масу часу при роботі з таблицями.
Знайти значення таблиці допоможе функція ВПР в Excel прикладиякої описані нижче у статті.
Під час роботи з програмою у користувачів часто виникає потреба швидкого пошукуінформації в одній таблиці та перенесенні її в інший об'єкт листа.
Розуміння принципу роботи ВПР спростить вашу і допоможе швидше виконувати завдання.
Зміст:VLOOKUP (Vertical Lookup) – це ще одна назва функції, яку можна зустріти в англомовній версії табличного процесора.
Сама абревіатура ВВР означає «вертикальний перегляд».
Аналіз даних та їх пошук у таблиці здійснюється за допомогою поступового перебору елементів від рядка до рядка у кожній колонці.
Також у Excel є протилежна функція під назвою HLOOKUP або ГПР – горизонтальний перегляд.
Єдина відмінність роботи опцій полягає в тому, що ГПР проводить пошук у таблиці з перебору стовпців, а не рядків.
Найчастіше користувачі віддають перевагу саме функціям ВПР, адже більшість таблиць мають більше рядків, ніж стовпців.
Який вигляд має синтаксис ВПР?
Синтаксис функції в Excel - це набір параметрів, за допомогою яких можна викликати і задати. Запис аналогічна методом запису математичних функцій.
В результаті пошуку формули ви побачите її перебування у списку. Натиснувши елемент, внизу екрана з'явиться його формула.
За дужками вказується назва функції, а всередині дужок її параметри. Усередині формули кожен окремий параметр прописується у кутових<>скобах.
Загальний вигляд опису для ВПР виглядає так:
Рис.3 – перелік параметрів
Розглянемо докладніше кожне із значень, яке описується у дужках:
Як працює ВВР. Корисний приклад
Щоб зрозуміти принцип роботи VLOOKUP, перейдемо до розгляду конкретних прикладів. Візьмемо найпростішу таблицю із двома колонками. Нехай вона позначає код та найменування товару.
Після заповнення таблиці клікнемо на порожній осередок і результат ВПР. Клацніть на вкладку «Формули» та виберіть VLOOKUP.
Потім введіть усі необхідні параметри у вікно, зображене на малюнку 3. Підтвердьте дію. У осередку з'явиться результат виконання команди.
Рис.4 – приклад пошуку у простій таблиці
На малюнку вище у кольорових осередках вказується значення товару. Якщо ви не ввели значення для сортування, функція автоматично сприймає це як одиницю.
Завдяки цьому, процедуру пошуку буде зупинено лише коли буде досягнуто рядок зі значенням, номер якого вже перевищує об'єкт, що шукається.
Розглянемо ще один приклад використання функції, що часто зустрічається під час реальної роботиз прайсами та листами найменувань товару.
У випадку, коли користувач друкує, що останній елемент у дужках дорівнює нулю, опція перевіряє перший стовпець в заданому діапазоні масиву.
Пошук буде зупинено автоматично, як тільки знайдеться збіг параметра «ЩО» та імені товару.
Якщо в таблиці немає введеного вами ідентифікатора для імені продукції, в результаті пошуку VLOOKUP буде отримано значення «Н/Д», що означає відсутність елемента для заданого номера.
Рис.5 – другий приклад для ВВР
Коли використовувати ВВР?
Вище описано два варіанти застосування VLOOKUP.
Перша варіація VLOOKUP підійде для наступних випадків:
Перший варіант правопису VLOOKUP не може знайти елемент, якщо не було знайдено значення менше шуканого або рівне йому . У осередку для результату повернеться лише Н/Д.
Другий варіант для ВПР (із зазначенням «0» для сортування) застосовується для великих таблиць, у яких зустрічаються однакові назви кількох осередків.
VLOOKUP дозволить легко оперувати даними, адже повертає перший знайдений рядок.
У реальному житті опція використовується, коли потрібно здійснити пошук по заданому діапазону - він не обов'язково повинен відповідати всій величині таблиці.
У об'єктах аркуша, у яких зустрічаються різні види значень, ВПР допомагає знайти текстові рядки.
Рис.6 – приклад пошуку текстового значення
ВПР буває корисною, коли потрібно видалити багато зайвих прогалин. Функція швидко знаходить усі найменування з пробілами, і ви зможете швидко видалити їх. Приклад:
Рис.7 - ВПР при видаленні прогалин
Швидкодія VLOOKUP
При роботі з великими масивами даних Ексель може працювати надто повільно. На старих пристроях табличний процесор іноді навіть зависає через надто повільний пошук з ВПР.
Якщо на одному аркуші вашого документа представлено відразу кілька тисяч формул, краще подбати про сортування першого стовпця.
Це дозволяє збільшити загальну продуктивність пошуку на цілих 400% -500%.
Така різниця у швидкості виконання різних видів VLOOKUP полягає в тому, що будь-який комп'ютерної програминабагато простіше працювати з відсортованими даними, здійснюючи бінарний пошук.
Як використовувати функцію ВПР (VLOOKUP) в Excel
Покрокова демонстрація застосування функції ВПР (VLOOKUP) зв'язування двох таблиць, тобто. підстановки даних з однієї таблиці до іншої
У англійській версії ця функція називається VLOOKUP – розшифровується як вертикальний перегляд. Існує також функція ГПР, яка спрямована на горизонтальний перегляд. В основному функція ВПР використовують для того, щоб підтягнути дані з однієї таблиці до іншої, також її можна використовувати для порівняння стовпців у двох різних таблицях.
Для того, щоб нам ретельніше розібратися у всіх особливостях роботи з функцією, розглянемо прості приклади.
Ми маємо дві таблиці. У першій таблиці зберігаються дані на замовлення за день. У ній записується назва товару та кількість проданої продукції за партіями. У другій таблиці зберігається інформація щодо цін на товар. Завдання - підрахувати підсумковий виторг по кожному товару протягом дня.
Для вирішення завдання нам необхідно підтягнути ціни на товари з таблиці "Прайс-лист" у стовпець "Ціна за кг" таблиці "Замовлення". Тобто. підставити ціни, які відповідають кожному товару із нижньої таблиці у верхню.
Ексель, щоб вирішити цей приклад, має кілька можливостей, одна з яких - використання функції ВПР. Стаємо на першу комірку, де необхідно підставити дані, і починаємо писати функцію, починаючи як звичайно зі знаку рівності.
Повинна з'явитися підказка, згідно з якою ми будемо задавати їй аргументи. Якщо розшифрувати роботу ВПР з технічної мови на нормальну, то функція VLOOKUP шукатиме вибраний нами товар із таблиці "Замовлення" у крайньому лівому стовпці таблиці "Прайс-лист" і, якщо знайде цей товар, виводитиме значення його ціни, яке знаходиться на тій же рядку, як і знайдений товар. Коротко це виглядає як на скріншоті нижче.
Функція ВПР має 4 аргументи. Першим аргументом, який ми підставимо у формулу, є те саме потрібне значення, ціну для якого потрібно знайти в другій таблиці "Прайс-лист". Після написання назви функції і додавання дужки, що відкриває, клацаємо по шуканому осередку і додаємо її аргументом. У моєму випадку це осередок "E4" зі значенням "Яблука". Далі ставимо роздільник – крапка з комою.
Другим аргументом є таблиця, в якій ми шукаємо значення та з якої підтягуємо ціну. Тут потрібно просто виділити таблицю "Прайс-лист" і краще, щоб ви не зачепили зайвих осередків (шапка, порожні осередки та просто зайва інформація). В кінці не забуваємо ставити крапку з комою.
Так як у нашому прикладі таблиця, в якій проводиться пошук, знаходиться на цьому ж аркуші, що і перша, то при протягуванні формули на комірки нижче, зазначена таблиця буде автоматично з'їжджати вниз. Щоб цього не відбувалося, необхідно вибрати діапазон таблиці всередині формули лівою клавішею миші і натиснути на клавіатурі клавішу F4. До адрес осередків буде додано знак долара і посилання на таблицю перетворитися з відносної в абсолютну. Нижче ми розберемо випадки, коли створювати абсолютні посилання на осередки буде необов'язково.
Третій аргумент - номер стовпця у таблиці "Прайс-лист", з якого ми хочемо підтягнути потрібне значення. У нашому прикладі все просто. У першому стовпці ми шукаємо значення, та якщо з другого стовпця підтягуємо ціну. Ставимо цифру 2, а потім точку з комою.
Останній параметр називається "Інтервальний перегляд" - це логічний аргумент і може приймати лише 2 значення: 0 або 1 (ввімкнено/вимкнено). Цим значенням ми визначаємо точно цифра 0, або приблизно цифра 1, ми шукаємо найменування в прайс-листі. У випадку з текстовими найменуваннями краще використовувати точний пошук (цифра 0), тому що приблизно точно працює у випадку осередків з числами. Вводимо інтервальний перегляд як 0 та закриваємо дужку. Після цього натискаємо клавішу Enter, щоб формула спрацювала. У результаті ми отримаємо осередок із ціною, підтягнутою з таблиці прайс-листа.
У колонку "Підсумкова вартість" прописуємо просту формулу множення ваги партії на ціну за кг.
Вибираємо обидві комірки і, затиснувши ліву клавішу миші на правому нижньому куті виділення, протягуємо формули вниз на всі комірки, що залишилися.
У результаті отримуємо ціну та підсумкову вартість кожної партії. При подальшому використанні отриманих даних варто забувати у тому, що є результатом роботи формули, а чи не готовими значеннями. Щоб надалі мати можливість працювати з ними, потрібно скопіювати осередки з даними, що вийшли, і вставити їх назад на своє місце через спеціальну вставку як звичайні значення.
Ми розглянули з вами один із випадків, коли є потреба підтягнути дані з таблиці, яка знаходиться на цьому ж аркуші. Звичайно ж можна використовувати таблиці, які знаходяться на різних аркушах і навіть коли вони відкриті в різних документах. У цьому випадку другий аргумент функції виглядатиме трохи інакше. У випадку іншого аркуша або документа, зовсім не обов'язково задавати абсолютні посилання, а третій і четвертий аргумент краще дописувати в поле введення функції, не повертаючись назад на той аркуш, де ви починали його набирати.
Ще одним корисним застосуванням ВВР є її використання для порівняння двох таблиць з метою знаходження збігів або відмінностей. Наприклад, якщо в одній з таблиць прибрати кілька рядків, то повного збігу даних вже не буде і в деяких осередках з'явиться напис #Н/Д. Цей напис говорить про те, що значення таблиці із замовленнями не були знайдені в таблиці прайс-лист, що відповідно призводить до помилки.
Тепер давайте розглянемо випадок, де є необхідність використовувати інтервальний перегляд не точний, з цифрою 0, а приблизний, з цифрою 1. Цей спосіб використовується рідко, але є такі завдання, в яких він може вам знадобитися.
Повернемося до нашої таблиці із замовленнями та спробуємо визначити розмір партії за її вагою. У нас буде таблиця із замовленнями та таблиця з параметрами партії.
Додатково у нас виникає критерій "від і до", який говорить про розмір партії. У прикладі ми відштовхуватимемося від її ваги. Наприклад, якщо Excel показати вагу партії 15 кг, він розумітиме, що це дрібна партія.
Давайте розбиратися, як це працює. Починаємо писати функція ВПР, але наприкінці формули ставимо не 0, а 1. В аргументі, де потрібно вказати таблицю з осередками, ми не будемо вказувати стовпець з параметром "Від і до", тому що Excel такого запису не зрозуміє. Натомість ми вводимо поле критерій, який і визначатиме початок та кінець діапазону ваг. В кінці натискаємо клавішу Enter і простягаємо на всі комірки.
Є деякі особливості роботи, які потрібно пояснити, щоб було зрозуміло, що тут сталося. Ми, що з певній вазі функція витягла з другої таблиці певну партію. Щоб точніше зрозуміти логіку, давайте подивимося на вагу, наприклад, в 15 кг. У таблиці з розмірами партій такого критерію немає. Але коли ми ставимо інтервальний перегляд значення 1, функція витягуватиме значення, яке є найближчим меншим. При вазі партії в 15 кг, ВВР витягне з таблиці значення з критерієм 10. Якщо вага партії буде 47 кг, то функція витягне значення з критерієм 30, яке буде найближчим і меншим.
При роботі з функцією в такому режимі є дуже важливий момент, що ви повинні враховувати. Сортування критеріїв має обов'язково йти за зростанням - від меншого до більшого. Якщо критерії будуть відсортовані у зворотному порядку, функція не спрацює.
