Застосування та визначення цієї функції Excel
VLOOKUP - це функція Excel, за допомогою якої користувач може шукати та оцінювати вміст таблиці. Ця функція доступна у версіях Excel 2007 для Windows та Mac.
Що таке VLOOKUP?
Можливі варіанти використання VLOOKUP слід пояснити тут на прикладі: У цьому ви є великим шанувальником літератури і тому створили власну електронну таблицю Excel, в якій ви можете ретельно сортувати зібрані вами книги. Кожна робота вводиться з інформацією про такі категорії:
-
автором
-
титул
-
Номер сторінки
-
Рік видання
Тепер ви хотіли б дати подрузі книжкову пораду, яку можна взяти з собою під час наступної зустрічі. На жаль, можна думати лише про автора, а не про назву книги. Тут вступає в дію VLOOKUP, оскільки він може використовувати це вхідне значення, щоб одним махом викинути потрібну інформацію.
Як використовується VLOOKUP?
Перш ніж навіть подумати про формулювання формул, слід визначити, де пізніше будуть розташовані поля введення та різні поля виводу. Для цього має сенс створити окрему таблицю, яка спочатку є порожньою і, таким чином, дає місце для зазначеної інформації. Якщо ви розробите цю нову таблицю на прикладі існуючої таблиці, згодом ви отримаєте перевагу економії часу.
На цій основі формулу VLOOKUP можна створити або вручну, або автоматично створити в Excel. Новачкам варто скористатися останнім підходом, щоб поступово ознайомитися зі структурою та ефектом формули. Для цього на вкладці «Формули» вибирається кнопка «Вставити функцію». VLOOKUP прихований у вікні, що відкриється. Після підтвердження знову відкривається вікно, в якому можна заповнити чотири параметри формули. Це:
-
Критерій пошуку
-
матриця
-
Покажчик стовпця
-
Area_reference
Тому необроблений проект формули виглядає так:
= VLOOKUP (критерій пошуку, матриця, індекс стовпця, діапазон_посилання)
і в одному з можливих додатків, як це:
= VLOOKUP (H3; A3: E40; 5)
Критерій пошуку
Щоб функція знала, яке значення слід використовувати як вихідну точку, рядок, який був вибраний як поле введення двома кроками раніше, зазначається у полі «Критерій пошуку». У нашому прикладі туди введено ім’я автора книги «Філіп Пульманн». Це робить формулу гнучкою і її не потрібно повторно коригувати, як тільки введене значення змінюється.
матриця
Поле введення "Матриця" описує таблицю, в якій можна знайти інформацію для виведення. Таким чином, ця спеціальна матриця також містить стовпці для назви книги, номера сторінки та року видання.
Матриця повністю вибирається один раз без заголовків від верхнього лівого до нижнього правого поля. Таким чином, Excel знає, який вміст слід враховувати при оцінці.
Покажчик стовпця
Поле введення для "індексу стовпця" пропонує користувачеві визначити стовпець матриці, в якому вказано лише шукане значення. Призначення стовпців хронологічно нумерується. Це означає, що перший стовпець таблиці отримує значення 1, другий - значення 2 і т. Д. У нашому прикладі це відповідає індексу стовпця 1 для автора, індекс стовпця 2 для заголовка, індекс стовпця 3 для номера сторінки та індекс колонки 4 за рік видання.
Для того, щоб таблиця була максимально гнучкою, заголовок стовпця можна зв’язати замість номера. Це має ту перевагу, що формулу також можна без проблем переносити в інші рядки, оскільки заголовок стовпця можна гнучко адаптувати кожного разу.
Увага: VLOOKUP зчитує матрицю зліва направо, тому індекс стовпця повинен бути розміщений праворуч від стовпця для критерію пошуку, щоб функція врахувала його!
Area_reference
Параметр "Range_Lookup" доповнює формулу VLOOKUP, вказуючи точність оцінки таблиці. Однак він відрізняється від вищезгаданих компонентів формули, оскільки є необов’язковим. Якщо значення "0" введено як "неправильне", Excel шукатиме лише значення, яке було вказано як критерій пошуку. Однак зі значенням 1 для "істини" пошук продовжується для очевидних значень, якщо точне значення не вдалося знайти.
Вказувати цей параметр необов’язково, оскільки значення 1 встановлено за замовчуванням. Цей параметр стане в нагоді пізніше в розширеному VLOOKUP з кількома критеріями пошуку.
Злиття
Як тільки всі необхідні параметри були налаштовані, можна використовувати VLOOKUP. Після введення критерію пошуку та підтвердження функції значення, яке ви шукаєте, з’являється у рядку, визначеному як поле виводу.
У нашому прикладі зараз відображається назва книги «Золотий компас», що відповідає введеному автором. Щоб швидко дізнатися номер сторінки та рік публікації, нічого не потрібно робити, як перетягнути існуючу формулу VLOOKUP у наступні комірки. Це настільки легко, оскільки індекс стовпця VLOOKUP був пов’язаний із заголовком стовпця першої таблиці, а друга таблиця також структурована в тому ж порядку.
У разі, якщо таблиці повинні відрізнятися одна від одної або все -таки виникає помилка, формулу VLOOKUP також можна змінити вручну. Для цього передостанню цифру для індексу стовпця потрібно зіставити зі стовпцем нового значення, яке буде виведено.
VLOOKUP з кількома критеріями пошуку
Досить часто трапляється, що одного критерію пошуку недостатньо для точної оцінки великої таблиці Excel. Тоді має сенс запустити VLOOKUP з кількома критеріями пошуку. Для цього існуючу формулу необхідно доповнити додатковою функцією IF. Таким чином, під час подання заявки можна врахувати до восьми різних критеріїв пошуку.
VLOOKUP у кількох електронних таблицях Excel
Якщо критерій пошуку можна знайти не тільки в одній таблиці, але, можливо, і в іншій, формулу VLOOKUP можна відповідно змінити. Для цього як функцію if, так і функцію ISERROR необхідно розмістити перед існуючою формулою. Для цього потрібно п'ять параметрів:
-
Критерій пошуку
-
Матриця 1 і Матриця 2
-
Індекс стовпця1 та індекс стовпця2
Результат виглядає так:
= IF (ПОМИЛКА (VLOOKUP (критерій пошуку, матриця1, стовпець-індекс1, 0));
VLOOKUP (критерій пошуку; матриця2; індекс стовпця2,0); VLOOKUP (критерій пошуку; матриця1; індекс стовпця1;))
і в одному з можливих додатків, як це:
= IF (ПОМИЛКА (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
Критерій пошуку використовується для вставлення значення, яке потрібно шукати, у дві таблиці. Matrix1 та Matrix2 визначають відповідні області комірок двох таблиць. Стовпець index1 та стовпець index2 використовуються для більш детального визначення, у яких стовпцях відповідних таблиць слід шукати.
Якщо значення, яке ви шукаєте, є в обох таблицях, Excel виведе результат з першої таблиці. Однак, якщо значення не знайдено ні в одній із двох таблиць, з’являється повідомлення про помилку. Перевага формули в тому, що два списки не повинні мати однакову структуру або бути однакового розміру.
Призначте значення категоріям за допомогою VLOOKUP
Додаткова функція VLOOKUP дозволяє автоматично розділяти перелічені значення на букви та предикати на ваш вибір. У нашому попередньому прикладі для типу книги слід вставити додатковий стовпець таблиці. Книги довжиною до 50 сторінок повинні потрапити до жанру новели, тоді як книги від 51 до 150 сторінок віднесені до новели та від 151 сторінки до роману. Щоб це стало можливим, у VLOOKUP не потрібна додаткова формула, а лише використання фігурних дужок “{}”. Готова формула виглядає так:
= VLOOKUP (B1; {1. "Новела"; 51. "Новела"; 151. "Роман"}; 2)
Вміст фігурних дужок вказує на матрицю, яка визначає область відповідного типу книги. Тому віднесення довжини сторони до відповідного роду міститься у фігурних дужках. У формулі використовуються пари значень, кожне з яких розділено точкою. Матриця {1. "Новела"; 51. "Новела"; 151. "Роман"} читається так:
"З 1 показу коротка історія, з 51 - повість, з 151 - роман".
Цю матрицю можна легко адаптувати до різних завдань. Це стосується, з одного боку, розміру та кількості матриць, а також їх позначення. Таким чином, в результаті можна вивести рядки або цифри замість окремих літер. Все, що вам потрібно зробити, це відрегулювати літери у формулі.
VLOOKUP на кількох робочих аркушах
Ще одна функція VLOOKUP дозволяє своїм користувачам пов'язувати вміст, розміщений у різних електронних таблицях. У нашому прикладі цей параметр може бути корисним, коли інформація спочатку сортується на різних робочих аркушах, а потім оновлюється у зведеній таблиці.
Уявіть собі, що, крім ваших книг, ви також перераховуєте зібрані фільми в таблиці Excel. Потім ви об’єднуєте обидві колекції в одну велику таблицю.
Перевага цієї процедури полягає не тільки в збільшенні порядку, але і у запобіганні потенційних помилок. Якщо ви хочете створити новий запис або оновити існуючий, вам не потрібно шукати у великій таблиці, але натомість ви можете отримати доступ до менших. Потім значення автоматично переносяться до узагальнюючої таблиці Excel. Це робить переписування у великій таблиці зайвим, що у кращому випадку дозволяє уникнути невдалого ходу та подальшого ланцюжка повідомлень про помилки.
Як виглядає формула?
Ця функція знову стає можливою, вставивши іншу формулу. Під час пошуку за кількома критеріями потрібна додаткова формула IF, для роботи з кількома аркушами потрібна формула INDIRECT. Це дозволяє вказати діапазон з іншої електронної таблиці для матриці VLOOKUP.
= VLOOKUP (критерій пошуку; INDIRECT (матриця); індекс стовпця; діапазон_посилання)
Увага: Ця формула буде працювати лише в тому випадку, якщо окремі таблиці на різних аркушах мають ті ж назви, що і заголовки стовпців загальної таблиці. Цілі таблиці можна назвати у "Полі імені" у верхньому лівому куті над сіткою комірок. Таблиці, які вже були названі, можна переглянути за допомогою комбінації клавіш Ctrl + F3.
Робота з новими повідомленнями про помилки
Робота зі зв'язаними таблицями Excel може призвести до небажаних проблем. Це включає, зокрема, вихід неправильних значень. Якщо виводиться неправильне значення 0, виникає невелика проблема в налаштуваннях Excel, яку можна швидко виправити.
Поширене повідомлення про помилку #NV, навпаки, є навмисною функцією VLOOKUP, яка вказує користувачеві, що необхідне значення недоступне. Ця примітка може бути оформлена по -різному за допомогою формули.
VLOOKUP - огляд
VLOOKUP - це корисна функція Excel, яку можна використовувати для пошуку та оцінки таблиць. Його переваги проявляються у зручному та гнучкому застосуванні. Таким чином, будь -хто, хто регулярно працює з таблицями Excel, може скористатися цією функцією. Будь то приватний колекціонер, який створює власні невеликі таблиці, або велика компанія, яка обробляє значно більші набори даних.
З іншого боку, якщо у вас все ще залишаються запити без відповіді, які VLOOKUP не може задовольнити, ви можете чекати на додаткову опцію Excel: Microsoft пропонує користувачам Excel 365 новий XLOOKUP з початку 2022-2023 років. Це спирається на компетенції VLOOKUP і доповнює їх додатковими, іноді навіть простішими функціями. Тому на цьому етапі також відкривається нова процедура оцінки даних.
