зміст
- Функція VLOOKUP в Excel – загальний опис і синтаксис
- Приклади VLOOKUP
- Точна або приблизна відповідність у функції VLOOKUP
- VLOOKUP в Excel – це потрібно пам’ятати!
Сьогодні ми починаємо серію статей, що описують одну з найкорисніших функцій Excel − VPR (ВПР). Ця функція, водночас, є однією з найскладніших і найменш вивчених.
У цьому підручнику на VPR Я постараюся викласти основи якомога простіше, щоб зробити процес навчання максимально зрозумілим для недосвідчених користувачів. Крім того, ми вивчимо кілька прикладів із формулами Excel, які продемонструють найпоширеніші випадки використання функції VPR.
Функція VLOOKUP в Excel – загальний опис і синтаксис
Так що це таке VPR? Ну, по-перше, це функція Excel. Що вона робить? Він шукає вказане вами значення та повертає відповідне значення з іншого стовпця. Технічно кажучи, VPR шукає значення в першому стовпці заданого діапазону та повертає результат з іншого стовпця в тому ж рядку.
У найпоширенішому застосуванні функція VPR шукає в базі даних даний унікальний ідентифікатор і витягує певну інформацію, пов’язану з ним, з бази даних.
Перша літера в назві функції VPR (VLOOKUP) означає Ввертикальний (Vвертикальний). По ньому можна відрізнити VPR від GPR (HLOOKUP), який шукає значення у верхньому рядку діапазону − Ггоризонтальний (Hгоризонтально).
функція VPR доступний у Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP і Excel 2000.
Синтаксис функції VLOOKUP
функція VPR (VLOOKUP) має такий синтаксис:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
Як бачите, функція VPR в Microsoft Excel має 4 варіанти (або аргументи). Перші три обов'язкові, останній необов'язковий.
- lookup_value (lookup_value) – значення, яке потрібно шукати. Це може бути значення (число, дата, текст) або посилання на клітинку (що містить пошукове значення), або значення, яке повертає інша функція Excel. Наприклад, ця формула шукатиме значення 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
Якщо шукане значення менше найменшого значення в першому стовпці діапазону, що шукається, функція VPR повідомить про помилку #AT (#Н/Д).
- table_array (таблиця) – два або більше стовпців даних. Пам'ятайте, функція VPR завжди шукає значення в першому стовпці діапазону, указаного в аргументі table_array (таблиця). Видимий діапазон може містити різні дані, такі як текст, дати, числа, логічні значення. Функція нечутлива до регістру, тобто символи верхнього та нижнього регістру вважаються однаковими. Отже, наша формула шукатиме значення 40 в осередках від A2 до A15, оскільки A є першим стовпцем діапазону A2:B15, указаного в аргументі table_array (таблиця):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- col_index_num (номер_стовпця) — це номер стовпця в заданому діапазоні, з якого буде повернуто значення в знайденому рядку. Крайній лівий стовпець у заданому діапазоні 1, другий стовпець 2, третій стовпець є 3 і так далі. Тепер ви можете прочитати всю формулу:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)Формула шукає значення 40 в асортименті A2: A15 і повертає відповідне значення зі стовпця B (оскільки B є другим стовпцем у діапазоні A2:B15).
Якщо значення аргументу col_index_num (номер_стовпця) менше ніж 1потім VPR повідомить про помилку #VALUE! (#ЦІННІСТЬ!). І якщо воно більше, ніж кількість стовпців у діапазоні table_array (таблиця), функція поверне помилку #REF! (#ПОСИЛАННЯ!).
- пошук_діапазону (range_lookup) – визначає, що шукати:
- точний збіг, аргумент має бути рівним ПОМИЛКОВИЙ (ПОМИЛКОВИЙ);
- приблизний збіг, аргумент дорівнює ІСТИННИЙ КОД (ІСТИНА) або не вказано взагалі.
Цей параметр необов'язковий, але дуже важливий. Далі в цьому посібнику VPR Я покажу вам кілька прикладів, що пояснюють, як писати формули для знаходження точних і наближених збігів.
Приклади VLOOKUP
Сподіваюся на функцію VPR стане вам трохи зрозумілішим. Тепер давайте розглянемо деякі випадки використання VPR у формулах з реальними даними.
Як використовувати VLOOKUP для пошуку на іншому аркуші Excel
На практиці формули з функцією VPR рідко використовуються для пошуку даних на одному аркуші. Найчастіше ви будете шукати та отримувати відповідні значення з іншого аркуша.
Для того щоб використовувати VPR, шукайте в іншому аркуші Microsoft Excel, Ви повинні в аргументі table_array (таблиця) вкажіть назву аркуша зі знаком оклику та діапазоном клітинок. Наприклад, наступна формула показує, що діапазон А2: В15 знаходиться на аркуші під назвою Sheet2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
Звичайно, назву аркуша не потрібно вводити вручну. Просто почніть вводити формулу, а коли справа доходить до аргументу table_array (таблиця), перейдіть на потрібний аркуш і виділіть мишкою потрібний діапазон комірок.
Формула, показана на знімку екрана нижче, шукає текст «Продукт 1» у стовпці A (це перший стовпець діапазону A1:B2) на аркуші. ціни.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
Пам’ятайте, що під час пошуку текстового значення ви повинні взяти його в лапки («»), як це зазвичай робиться у формулах Excel.
Для аргументації table_array (таблиця) бажано завжди використовувати абсолютні посилання (зі знаком $). У цьому випадку діапазон пошуку залишиться незмінним при копіюванні формули в інші комірки.
Шукайте в іншій книзі за допомогою VLOOKUP
Функціонувати VPR працюючи між двома робочими книгами Excel, вам потрібно вказати назву робочої книги в квадратних дужках перед назвою аркуша.
Наприклад, нижче наведено формулу, яка шукає значення 40 на аркуші Sheet2 у книзі Numbers.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
Ось найпростіший спосіб створити формулу в Excel за допомогою VPRякий посилається на іншу книгу:
- Відкрийте обидві книги. Це не обов’язково, але таким чином легше створити формулу. Ви не хочете вводити назву книги вручну, чи не так? Крім того, це захистить вас від випадкових помилок.
- Почніть вводити функцію VPRі коли справа доходить до аргументу table_array (таблиця), перейдіть до іншої книги та виберіть у ній потрібний діапазон пошуку.
На знімку екрана нижче показано формулу з пошуком, налаштованим на діапазон у книзі PriceList.xlsx на аркуші ціни.
функція VPR працюватиме, навіть якщо ви закриєте робочу книгу, у якій шукаєте, і повний шлях до файлу робочої книги з’явиться на панелі формул, як показано нижче:
Якщо в назві зошита або аркуша є пробіли, то його потрібно поставити в апостроф:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
Як використовувати іменований діапазон або таблицю у формулах за допомогою VLOOKUP
Якщо ви плануєте використовувати той самий діапазон пошуку в кількох функціях VPR, ви можете створити іменований діапазон і ввести його назву у формулу як аргумент table_array (таблиця).
Щоб створити іменований діапазон, просто виберіть комірки та введіть відповідне ім’я в полі Ім'я, ліворуч від рядка формул.
Тепер ви можете записати таку формулу для знаходження ціни товару Продукт 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 за допомогою команди таблиця Вкладка (Таблиця). вставка (Вставити), тоді, коли ви виберете діапазон за допомогою миші, Microsoft Excel автоматично додасть назви стовпців (або назву таблиці, якщо ви виберете всю таблицю) до формули.
Готова формула буде виглядати приблизно так:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
А може навіть так:
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
У разі використання іменованих діапазонів посилання вказуватимуть на ті самі клітинки незалежно від того, куди ви копіюєте функцію VPR в межах робочого зошита.
Використання символів підстановки у формулах VLOOKUP
Як і багато інших функцій, VPR Ви можете використовувати наступні символи підстановки:
- Знак питання (?) – замінює будь-який окремий символ.
- Зірочка (*) – замінює будь-яку послідовність символів.
Використання символів підстановки у функціях VPR може бути корисним у багатьох випадках, наприклад:
- Коли ви точно не пам'ятаєте текст, який потрібно знайти.
- Коли ви хочете знайти якесь слово, яке є частиною вмісту клітинки. Знати, що VPR шукає за вмістом комірки в цілому, ніби цей параметр увімкнено Відповідає вмісту всього комірки (Вся клітинка) у стандартному пошуку Excel.
- Коли комірка містить додаткові пробіли на початку або в кінці вмісту. У такій ситуації можна довго ламати голову, намагаючись зрозуміти, чому формула не працює.
Приклад 1: Пошук тексту, який починається або закінчується певними символами
Припустімо, ви хочете знайти конкретного клієнта в базі даних, наведеній нижче. Ви не пам'ятаєте його прізвище, але знаєте, що воно починається на «ack». Ось формула, яка чудово впорається зі своєю роботою:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
Тепер, коли ви впевнені, що знайшли правильну назву, ви можете скористатися тією самою формулою, щоб знайти суму, сплачену цим клієнтом. Для цього просто змініть третій аргумент функції VPR до потрібного номера стовпця. У нашому випадку це стовпець C (3-й у діапазоні):
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
Ось ще кілька прикладів із символами підстановки:
~ Знайдіть ім’я, яке закінчується на «чоловік»:
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ Знайдіть ім’я, яке починається на «ad» і закінчується на «son»:
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ Знаходимо перше ім'я в списку, що складається з 5 символів:
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
Функціонувати VPR із символами узагальнення працювало правильно, як четвертий аргумент, який ви завжди повинні використовувати ПОМИЛКОВИЙ (ПОМИЛКОВИЙ). Якщо діапазон пошуку містить більше одного значення, яке відповідає пошуковим термінам із символами підстановки, буде повернуто перше знайдене значення.
Приклад 2: поєднання символів узагальнення та посилань на клітинки у формулах VLOOKUP
Тепер давайте розглянемо трохи складніший приклад пошуку за допомогою функції VPR за значенням у клітинці. Уявіть, що стовпець A — це список ліцензійних ключів, а стовпець B — список імен, які володіють ліцензією. Крім того, у вас є частина (кілька символів) якогось ліцензійного ключа в клітинці C1, і ви хочете знайти ім’я власника.
Це можна зробити за такою формулою:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
Ця формула шукає значення клітинки C1 у заданому діапазоні та повертає відповідне значення зі стовпця B. Зверніть увагу, що в першому аргументі ми використовуємо символ амперсанда (&) перед і після посилання на клітинку, щоб зв’язати текстовий рядок.
Як ви можете бачити на малюнку нижче, функція VPR повертає «Jeremy Hill», оскільки його ліцензійний ключ містить послідовність символів із клітинки C1.
Зверніть увагу, що аргумент table_array (таблиця) на знімку екрана вище містить назву таблиці (Таблиця7) замість вказівки діапазону клітинок. Це те, що ми зробили в попередньому прикладі.
Точна або приблизна відповідність у функції VLOOKUP
І нарешті, давайте детальніше розглянемо останній аргумент, який вказано для функції VPR - пошук_діапазону (interval_view). Як було зазначено на початку уроку, цей аргумент дуже важливий. Ви можете отримати абсолютно різні результати в одній формулі з її значенням ІСТИННИЙ КОД (ПРАВДА) або ПОМИЛКОВИЙ (ПОМИЛКОВИЙ).
Спочатку давайте з’ясуємо, що означає Microsoft Excel під точними та приблизними збігами.
- Якщо аргумент пошук_діапазону (діапазон_пошуку) дорівнює ПОМИЛКОВИЙ (FALSE), формула шукає точний збіг, тобто точно таке саме значення, яке вказано в аргументі lookup_value (пошукове_значення). Якщо в першому стовпці діапазону tздатний_масив (таблиця) зустрічає два або більше значень, які відповідають аргументу lookup_value (пошукове_значення), тоді буде вибрано перше. Якщо збігів не знайдено, функція повідомить про помилку #AT (#N/A). Наприклад, наступна формула повідомить про помилку #AT (#N/A), якщо в діапазоні A2:A15 немає значення 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - Якщо аргумент пошук_діапазону (діапазон_пошуку) дорівнює ІСТИННИЙ КОД (ІСТИНА), формула шукає приблизний збіг. Точніше, спочатку функція VPR шукає точну відповідність, а якщо її не знайдено, вибирає приблизну. Приблизний збіг — це найбільше значення, яке не перевищує значення, вказане в аргументі. lookup_value (пошукове_значення).
Якщо аргумент пошук_діапазону (діапазон_пошуку) дорівнює ІСТИННИЙ КОД (TRUE) або не вказано, то значення в першому стовпці діапазону повинні бути відсортовані в порядку зростання, тобто від найменшого до найбільшого. В іншому випадку функція VPR може повернути помилковий результат.
Щоб краще зрозуміти важливість вибору ІСТИННИЙ КОД (ПРАВДА) або ПОМИЛКОВИЙ (FALSE), давайте розглянемо ще кілька формул із функцією VPR і подивіться на результати.
Приклад 1: Пошук точної відповідності за допомогою VLOOKUP
Як ви пам'ятаєте, для пошуку точного збігу використовується четвертий аргумент функції VPR повинно мати значення ПОМИЛКОВИЙ (ПОМИЛКОВИЙ).
Давайте повернемося до таблиці з самого першого прикладу і дізнаємося, яка тварина може рухатися зі швидкістю 50 миль на годину. Думаю, ця формула не викличе у вас труднощів:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
Зауважте, що наш діапазон пошуку (стовпець A) містить два значення 50 – в клітинах A5 и A6. Формула повертає значення з клітинки B5. чому Оскільки при пошуку точної відповідності функція VPR використовує перше знайдене значення, яке відповідає шуканому.
Приклад 2: використання функції VLOOKUP для пошуку приблизної відповідності
Коли ви використовуєте функцію VPR для пошуку приблизного збігу, тобто коли аргумент пошук_діапазону (діапазон_пошуку) дорівнює ІСТИННИЙ КОД (TRUE) або пропущено, перше, що вам потрібно зробити, це відсортувати діапазон за першим стовпцем у порядку зростання.
Це дуже важливо, оскільки функція VPR повертає наступне за величиною значення після заданого, а потім пошук зупиняється. Якщо ви знехтуєте правильним сортуванням, ви отримаєте дуже дивні результати або повідомлення про помилку. #AT (#Н/Д).
Тепер ви можете використовувати одну з наступних формул:
=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)
Як бачите, я хочу дізнатися, яка з тварин має найближчу швидкість 69 миль на годину. І ось результат, який повернула мені функція VPR:
Як бачите, формула повернула результат Антилопа (Антилопа), чия швидкість 61 миль на годину, хоча список також включає Cheetah (Гепард), який біжить на швидкості 70 миль на годину, а 70 ближче до 69, ніж до 61, чи не так? Чому це відбувається? Оскільки функція VPR під час пошуку приблизної відповідності повертає найбільше значення, яке не перевищує шукане.
Я сподіваюся, що ці приклади проливають світло на роботу з функцією VPR в Excel, і ви більше не дивитеся на неї як на аутсайдера. Тепер не завадить коротко повторити ключові моменти вивченого матеріалу, щоб краще закріпити його в пам'яті.
VLOOKUP в Excel – це потрібно пам’ятати!
- функція VPR Excel не може дивитися ліворуч. Він завжди шукає значення в крайньому лівому стовпці діапазону, заданого аргументом table_array (таблиця).
- У функції VPR всі значення нечутливі до регістру, тобто малі та великі літери еквівалентні.
- Якщо значення, яке ви шукаєте, менше мінімального значення в першому стовпці діапазону, що шукається, функція VPR повідомить про помилку #AT (#Н/Д).
- Якщо 3-й аргумент col_index_num (номер_стовпця) менше ніж 1функція VPR повідомить про помилку #VALUE! (#ЦІННІСТЬ!). Якщо воно більше, ніж кількість стовпців у діапазоні table_array (таблиця), функція повідомить про помилку #REF! (#ПОСИЛАННЯ!).
- Використовуйте абсолютні посилання на клітинки в аргументі table_array (таблиця), щоб під час копіювання формули зберігався правильний діапазон пошуку. Спробуйте використовувати іменовані діапазони або таблиці в Excel як альтернативу.
- Виконуючи пошук приблизної відповідності, пам’ятайте, що перший стовпець у діапазоні, який ви шукаєте, має бути відсортований у порядку зростання.
- Нарешті, пам'ятайте про важливість четвертого аргументу. Використовуйте значення ІСТИННИЙ КОД (ПРАВДА) або ПОМИЛКОВИЙ (НЕПРАВДА) навмисно, і ви позбудетеся багатьох головних болів.
У наступних статтях нашого посібника з функцій VPR в Excel ми дізнаємося про складніші приклади, такі як виконання різних обчислень за допомогою VPR, вилучення значень із кількох стовпців тощо. Дякую, що прочитали цей підручник, і сподіваюся побачити вас знову наступного тижня!