Сьогодні ми починаємо серію статей, що описують одну з найбільш корисних функцій Excel - ВПР (VLOOKUP). Ця функція, в той же час, одна з найбільш складних і найменш зрозумілих.
У цьому підручнику з ВПР я спробую викласти основи максимально простою мовою, щоб зробити процес навчання для недосвідчених користувачів максимально зрозумілим. Крім цього, ми вивчимо кілька прикладів з формулами Excel, які продемонструють найбільш поширені варіанти використання функції ВПР.
Функція ВПР в Excel - загальний опис і синтаксис
Отже, що ж таке ВВР. Ну, по-перше, це функція Excel. Що вона робить? Вона шукає заданий Вами значення і повертає відповідне значення з іншого шпальти. Говорячи технічною мовою, ВВР шукає значення в першому стовпці заданого діапазону і повертає результат з іншого шпальти в тому ж рядку.
У самому звичному застосуванні, функція ВПР шукає в базі даних заданий унікальний ідентифікатор і витягує з бази якусь пов'язану з ним інформацію.
Перша буква в назві функції ВПР (VLOOKUP) означає В вертикальна (V ertical). По ній Ви можете відрізнити ВВР від ГПР (HLOOKUP), яка здійснює пошук значення у верхньому рядку діапазону - Г орізонтальний (H orizontal).
Синтаксис функції ВПР
Функція ВПР (VLOOKUP) має ось такий синтаксис:
Як бачите, функція ВПР в Microsoft Excel має 4 параметри (або аргументи). Перші три - обов'язкові, останній - за потребою.
- lookup_value (шукане_значення) - значення, яке потрібно шукати.
Якщо шукане значення буде менше, ніж найменше значення в першому стовпці проглядається діапазону, функція ВПР повідомить про помилку # N / A (# Н / Д).
- table_array (таблиця) - два або більше стовпчика з даними.
Запам'ятайте, функція ВПР завжди шукає значення в першому стовпці діапазону, заданого в аргументі table_array (таблиця). У просматриваемом діапазоні можуть бути різні дані, наприклад, текст, дати, числа, логічні значення. Літери не враховується функцією, тобто символи верхнього і нижнього регістра вважаються однаковими.
Отже, наша формула буде шукати значення 40 в осередках від A2 до A15. тому що A - це перший стовпець діапазону A2: B15, заданого в аргументі table_array (таблиця):
- col_index_num (номер_стовпчика) - номер стовпця в заданому діапазоні, з якого буде повернуто значення, що знаходиться в знайденої рядку.
Крайній лівий стовпець в заданому діапазоні - це 1. другий стовпець - це 2. третій стовпець - це 3 і так далі. Тепер Ви можете прочитати всю формулу:
Формула шукає значення 40 в діапазоні A2: A15 і повертає відповідне значення зі стовпця B (оскільки B - це другий стовпець в діапазоні A2: B15).
Цей параметр не обов'язковий, але дуже важливий. Далі в цьому підручнику з ВПР я покажу Вам кілька прикладів, що пояснюють як правильно складати формули для пошуку точного і приблизного збігу.
Приклади з функцією ВВР
Я сподіваюся, функція ВПР стала для Вас трохи зрозуміліше. Тепер давайте розглянемо кілька прикладів використання ВПР в формулах з реальними даними.
Як, використовуючи ВПР, виконати пошук на іншому аркуші Excel
На практиці формули з функцією ВВР рідко використовуються для пошуку даних на тому ж аркуші. Найчастіше Ви будете шукати і витягувати відповідні значення з іншого листа.
Щоб, використовуючи ВПР. виконати пошук на іншому аркуші Microsoft Excel, Ви повинні в аргументі table_array (таблиця) вказати ім'я аркуша зі знаком оклику, а потім діапазон комірок. Наприклад, наступна формула показує, що діапазон A2: B15 знаходиться на аркуші з ім'ям Sheet2.
Звичайно ж, ім'я листа не обов'язково вводити вручну. Просто почніть вводити формулу, а коли справа дійде до аргументу table_array (таблиця), перейдіть на потрібний лист і виділіть мишею необхідний діапазон комірок.
Формула, показана на скріншоті нижче, шукає текст "Product 1" в стовпці A (це 1-ий стовпець діапазону A2: B9) на аркуші Prices.
= VLOOKUP ( "Product 1", Prices! $ A $ 2: $ B $ 9,2, FALSE)
= ВПР ( "Product 1"; Prices! $ A $ 2: $ B $ 9; 2; ЛОЖЬ)
Будь ласка, пам'ятайте, що при пошуку текстового значення Ви зобов'язані укласти його в лапки ( ""), як це зазвичай робиться в формулах Excel.
Для аргументу table_array (таблиця) бажано завжди використовувати абсолютні посилання (зі знаком $). В такому випадку діапазон пошуку залишатиметься незмінним при копіюванні формули в інші осередки.
Пошук в іншій робочій книзі за допомогою ВВР
Щоб функція ВПР працювала між двома робочими книгами Excel, потрібно вказати ім'я книги в квадратних дужках перед назвою листа.
Наприклад, нижче показана формула, яка шукає значення 40 на аркуші Sheet2 в книзі Numbers.xlsx:
Ось найпростіший спосіб створити в Excel формулу з ВПР. яка посилається на іншу робочу книгу:
- Відкрийте обидві книги. Це не обов'язково, але так простіше створювати формулу. Ви ж не хочете вводити ім'я робочої книги вручну? До того ж, це захистить Вас від випадкових помилок.
- Почніть вводити функцію ВПР. а коли справа дійде до аргументу table_array (таблиця), перейдіть на іншу робочу книгу і виділіть в ній потрібний діапазон пошуку.
На знімку екрана, показаному нижче, видно формулу, в якій для пошуку заданий діапазон в робочій книзі PriceList.xlsx на аркуші Prices.
Функція ВПР буде працювати навіть, коли Ви закриєте робочу книгу, в якій проводиться пошук, а в рядку формул з'явиться повний шлях до файлу робочої книги, як показано нижче:
Якщо назва робочої книги або аркуша містить прогалини, то його потрібно укласти в апострофи:
Як використовувати іменований діапазон або таблицю в формулах з ВПР
Якщо Ви плануєте використовувати один діапазон пошуку в кількох функціях ВПР. то можете створити іменований діапазон і вводити його ім'я в формулу в якості аргументу table_array (таблиця).
Щоб створити іменований діапазон, просто виділіть комірки і введіть відповідну назву в поле Ім'я. зліва від рядка формул.
Тепер Ви можете записати ось таку формулу для пошуку ціни товарів Product 1:
= VLOOKUP ( "Product 1", Products, 2)
= ВПР ( "Product 1"; Products; 2)Більшість імен діапазонів працюють для всієї робочої книги Excel, тому немає необхідності вказувати ім'я листа для аргументу table_array (таблиця), навіть якщо формула і діапазон пошуку знаходяться на різних аркушах книги. Якщо ж вони знаходяться в різних книгах, то перед ім'ям діапазону потрібно вказати назву робочої книги, наприклад, ось так:
= VLOOKUP ( "Product 1", PriceList.xlsx! Products, 2)
= ВПР ( "Product 1"; PriceList.xlsx! Products; 2)Якщо перетворити діапазон комірок в повноцінну таблицю Excel, скориставшись командою Table (Таблиця) на вкладці Insert (Вставка), то при виділенні діапазону мишею, Microsoft Excel автоматично додасть в формулу назви стовпців (або назва таблиці, якщо Ви виділіть всю таблицю).
Готова формула буде виглядати приблизно ось так:
= VLOOKUP ( "Product 1", Table46 [[Product]: [Price]], 2)
= ВПР ( "Product 1"; Table46 [[Product]: [Price]]; 2)А може навіть так:
= VLOOKUP ( "Product 1", Table46,2)
= ВПР ( "Product 1"; Table46; 2)При використанні іменованих діапазонів, посилання будуть вести до тих же осередків, не залежно від того, куди Ви копіюєте функцію ВПР в межах робочої книги.
Використання символів підстановки в формулах з ВПР
Як і в багатьох інших функціях, в ВВР Ви можете використовувати наступні символи підстановки:
- Знак питання (?) - замінює один будь-який символ.
- Зірочка (*) - замінює будь-яку послідовність символів.
Використання символів підстановки у функціях ВПР може стати в нагоді в багатьох випадках, наприклад:
- Коли Ви не пам'ятаєте в точності текст, який потрібно знайти.
- Коли Ви хочете знайти якесь слово, яке є частиною вмісту комірки. Знайте, що ВПР шукає по вмісту осередку цілком, як при включеній опції Match entire cell content (Осередок цілком) в стандартному пошуку Excel.
- Коли в осередку містяться додаткові пробіли на початку або в кінці вмісту. У такій ситуації Ви можете довго ламати голову, намагаючись зрозуміти, чому формула не працює.
Приклад 1: Шукаємо текст, що починається або закінчується певними символами
Припустимо, що Ви хочете знайти певного клієнта в базі даних, показаної нижче. Ви не пам'ятаєте його прізвище, але знаєте, що вона починається на "ack". Ось така формула відмінно впорається з цим завданням:
Тепер, коли Ви впевнені, що знайшли правильне ім'я, можна використовувати цю ж формулу, щоб знайти суму, сплачену цим клієнтом. Для цього достатньо змінити третій аргумент функції ВПР на номер потрібного стовпчика. У нашому випадку це стовпець C (3-й в діапазоні):
Ось ще кілька прикладів з символами підстановки:
Знаходимо ім'я, яке закінчується на "man":
Знаходимо ім'я, що починається на "ad" і закінчується на "son":
Знаходимо перше ім'я в списку, що складається з 5 символів:
= VLOOKUP ( ".", $ A $ 2: $ C $ 11,1, FALSE)
= ВПР ( "."; $ A $ 2: $ C $ 11; 1; ЛОЖЬ)Щоб функція ВПР з символами підстановки працювала правильно, як четвертого аргументу завжди потрібно використовувати FALSE (ЛОЖЬ). Якщо діапазон пошуку містить більше одного значення, що підходить під умови пошуку з символами підстановки, то буде повернута первинна знайдене значення.
Приклад 2: Поєднуємо символи підстановки і посилання на осередки в формулах з ВПР
А тепер давайте розберемо трохи складніший приклад, як здійснити пошук за допомогою функції ВПР за значенням в якийсь осередку. Уявіть, що в стовпці A знаходиться список ліцензійних ключів, а в стовпці B список імен, які володіють ліцензією. Крім цього, у Вас є частина (кілька символів) якогось ліцензійного ключа в осередку C1, і Ви хочете знайти ім'я власника.
Це можна зробити, використовуючи ось таку формулу:
Ця формула шукає значення з комірки C1 в заданому діапазоні і повертає відповідне значення зі стовпця B. Зверніть увагу, що в першому аргументі ми використовуємо символ амперсанда () до і після заслання на осередок, щоб зв'язати текстову рядок.
Як видно на малюнку нижче, функція ВПР повертає значення "Jeremy Hill", оскільки його ліцензійний ключ містить послідовність символів з осередку C1.
Зауважте, що аргумент table_array (таблиця) на скріншоті зверху містить ім'я таблиці (Table7) замість вказівки діапазону осередків. Так ми робили в попередньому прикладі.
Точне або наближене збіг у функції ВПР
Для початку давайте з'ясуємо, що в Microsoft Excel розуміється під точним і наближеним збігом.
Наприклад, наступна формула повідомить про помилку # N / A (# Н / Д), якщо в діапазоні A2: A15 немає значення 4:
Щоб краще зрозуміти важливість вибору TRUE (ІСТИНА) або FALSE (ЛОЖЬ), давайте розберемо ще кілька формул з функцією ВВР і подивимося на результати.
Приклад 1: Пошук точного збігу за допомогою ВВР
Як Ви пам'ятаєте, для пошуку точного збігу, четвертий аргумент функції ВПР повинен мати значення FALSE (ЛОЖЬ).
Давайте знову звернемося до таблиці з самого першого прикладу і з'ясуємо, яка тварина може пересуватися зі швидкістю 50 миль на годину. Я вірю, що ось така формула не викличе у Вас труднощів:
Зверніть увагу, що наш діапазон пошуку (стовпець A) містить два значення 50 - в осередках A5 і A6. Формула повертає значення з комірки B5. Чому? Тому що при пошуку точного збігу функція ВПР використовує перше знайдене значення, що збігається з шуканим.
Приклад 2: Використовуємо ВВР для пошуку приблизного збігу
Це дуже важливо, оскільки функція ВПР повертає наступне найбільше значення після заданого, а потім пошук зупиняється. Якщо Ви зневажите правильної сортуванням, справа закінчиться тим, що Ви отримаєте дуже дивні результати або повідомлення про помилку # N / A (# Н / Д).
Ось тепер можна використовувати одну з наступних формул:
= VLOOKUP (69, $ A $ 2: $ B $ 15,2, TRUE) або = VLOOKUP (69, $ A $ 2: $ B $ 15,2)
= ВПР (69; $ A $ 2: $ B $ 15; 2; ІСТИНА) або = ВПР (69; $ A $ 2: $ B $ 15; 2)Як бачите, я хочу з'ясувати, у якого з тварин швидкість найближче до 69 миль в годину. І ось який результат мені повернула функція ВПР:
Як бачите, формула повернула результат Антилопа (Antelope), швидкість якої 61 миля на годину, хоча в списку є також Гепард (Cheetah), який біжить зі швидкістю 70 миль на годину, а 70 ближче до 69, ніж 61, чи не так? Чому так відбувається? Тому що функція ВПР при пошуку приблизного збігу повертає найбільше значення, що не перевищує шукане.
Сподіваюся, ці приклади пролили трохи світла на роботу з функцією ВВР в Excel, і Ви більше не дивіться на неї, як на чужака. Тепер не завадить коротко повторити ключові моменти вивченого нами матеріалу, щоб краще закріпити його в пам'яті.
ВПР в Excel - це потрібно запам'ятати!
Схожі статті