Використання функції впр (vlookup) в excel
ВПР (VLOOKUP) - одна з корисних функцій Excel, так само як і одна з найменш знайомих користувачам. У цій статті ми піднімемо завісу таємниці з функції ВПР за допомогою прикладу з реальної життя. Ми створимо має практичну цінність шаблон рахунку для вигаданої компанії.
Трохи про функції ВПР
Отже, що ж таке ВВР. Думаю, Ви вже здогадалися, що це одна з безлічі функцій Excel.
За своїм основним призначенням, ВВР - це функція баз даних, тобто вона працює з таблицями або, простіше кажучи, зі списками об'єктів в таблицях Excel. Що це можуть бути за об'єкти? Та що завгодно! Ваша таблиця може містити список співробітників, товарів, покупців, CD-дисків або зірок на небі. Насправді, це не має значення.
Ось приклад списку або бази даних. В даному випадку, це список товарів, які продає вигадана компанія:
Зазвичай в списках на кшталт цього кожен елемент має свій унікальний ідентифікатор. В даному випадку унікальний ідентифікатор міститься в стовпці Item Code.
Щоб функція ВПР могла працювати зі списком, цей список повинен мати стовпець, що містить унікальний ідентифікатор (його називають Ключ або ID), і це повинен бути перший стовпець таблиці. Таблиця, представлена в прикладі вище, повністю задовольняє цій вимозі.
Найважче в роботі з функцією ВПР - це зрозуміти, для чого вона взагалі потрібна. Давайте спробуємо розібратися з цим в першу чергу.
Функція ВПР витягує з бази даних інформацію, грунтуючись на унікальному ідентифікатор.
Іншими словами, якщо Ви введете в осередок функцію ВПР і передасте їй як аргумент один з унікальних ідентифікаторів Вашої бази даних, то в результаті в комірці з'явиться якийсь шматок інформації, пов'язаний з цим унікальним ідентифікатором. Стосовно наприклад, наведеним вище: якби ми ввели в якості аргументу значення з шпальти Item Code. то як результат могли б отримати відповідне йому опис товару (Description), його ціну (Price), або наявність (In Stock). Яку саме інформацію має повернути формула, Ви зможете вирішити в процесі її створення.
Якщо все, що Вам потрібно, це один раз знайти якусь інформацію в базі даних, то створювати заради цього формулу з використанням функції ВПР - занадто складний шлях. Подібні функції, зазвичай, застосовуються в таблицях для багаторазового використання, наприклад, в шаблонах. Кожен раз, коли хто-небудь введе певний код, система буде отримувати всю необхідну інформацію у відповідні позиції листа.
створюємо шаблон
Давайте створимо шаблон рахунку, який ми зможемо використовувати безліч разів в нашій вигаданої компанії.
Для початку, запустимо Excel ...
... і створимо порожній рахунок.
Ось як це повинно працювати: користувач шаблону буде вводити коди товарів (Item Code) в стовпець А. Після чого система буде використовувати для кожного товару опис і ціну, а далі розраховувати підсумок по кожному рядку. Кількість необхідно вказати самостійно.
Для простоти прикладу, ми розташуємо базу даних з товарами в тій же книзі Excel, але на окремому аркуші:
У реальному житті бази даних частіше зберігаються в окремому файлі. Це мало турбує функцію ВПР. оскільки для неї немає різниці, де знаходиться база даних - на тому ж аркуші, на іншому аркуші книги або взагалі в окремому файлі.
Вставляємо функцію ВПР
Щоб протестувати функцію ВПР. яку ми збираємося записати, спочатку введемо коректний код товару в клітинку A11:
Далі робимо активної ту клітинку, в якій повинна з'явитися інформація, яку видобувають функцією ВВР з бази даних. Цікаво, що саме на цьому етапі багато плутаються. Поясню, що ми будемо робити далі: ми створимо формулу, яка витягне з бази даних опис товару, код якого зазначений в комірці A11. Куди ми хочемо помістити це опис? Звичайно, в осередок B11. Отже, і формулу ми запишемо туди ж.
Отже, виділіть осередок B11:
Нам потрібно відкрити список усіх існуючих функцій Excel, щоб знайти в ньому ВВР і отримати деяку допомогу в заповненні формули. Для цього треба зайти на вкладку Formulas (Формули) і виберіть команду Insert Function (Вставити функцію).
З'являється діалогове вікно, в якому можна вибрати будь-яку існуючу в Excel функцію. Щоб знайти те, що нам необхідно, ми можемо ввести в поле Search for a function (Пошук функції) слово lookup (або пошук в російськомовній версії), оскільки потрібна нам функція - це функція пошуку. Система покаже список всіх пов'язаних з цим поняттям функцій Excel. Знайдіть у списку VLOOKUP (ВВР), виберіть її мишкою і натисніть ОК.
З'явиться діалогове вікно Function Arguments (Аргументи Функції), що пропонує ввести всі необхідні аргументи для функції ВПР. Уявіть собі, що це сама функція задає Вам наступні питання:
- Який унікальний ідентифікатор Ви шукайте в цій базі даних?
- Де знаходиться база даних?
- Яку інформацію Ви б хотіли отримати від бази даних?
Перші три аргументи виділені жирним шрифтом, щоб нагадати нам, що вони є обов'язковими (функція ВПР без будь-якого з них є неповною і не зможе повернути коректне значення). Четвертий аргумент не виділено жирним, оскільки він необов'язковий і використовується за потребою.
Заповнюємо аргументи функції ВПР
Перший аргумент, який треба вказати, це Lookup_value (шукане_значення). Функція просить нас вказати, де шукати значення унікального коду товару, опис якого треба витягти. У нашому випадку, це значення в стовпці Item code. яке ми ввели раніше в клітинку A11.
Натисніть на іконку вибору праворуч від рядка введення першого аргументу.
Потім клікніть один раз по осередку, що містить код товару і натисніть Enter.
Значення комірки A11 взято в якості першого аргументу.
Тепер потрібно задати значення аргументу Table_array (Таблиця). Іншими словами, треба пояснити функції ВПР, де знаходиться база даних, в якій необхідно виконувати пошук. Клацніть по іконці вибору поруч з другим аргументом:
Тепер знайдіть базу даних і виберіть весь діапазон без рядка заголовків. Оскільки база даних знаходиться на окремому аркуші, то спочатку перейдіть на потрібний лист, клікнувши по вкладці листа:
Далі ми виділяємо всі елементи таблиці, крім рядка заголовків ...
... і натискаємо Enter. У рядку для введення другого аргументу автоматично відобразиться діапазон комірок, в якому міститься вся база даних. У нашому випадку це 'Product Database'! A2: D7.
Тепер займемося третім аргументом Col_index_num (номер_стовпчика). За допомогою цього аргументу ми вказуємо функції ВПР. який саме шматок інформації з бази даних ми хочемо отримати. В даному випадку нам необхідно витягти опис товару (Description). Якщо Ви подивіться на базу даних, то побачите, що стовпець Description це другий стовпець в таблиці. Це означає, що для аргументу Col_index_num (номер_стовпчика) ми вводимо значення 2:
Важливо зауважити, що ми вказуємо значення 2 не тому, що стовпець Description знаходиться в другому за рахунком стовпці від початку листа Excel, а тому, що він другий за рахунком в діапазоні, який вказаний в якості аргументу Table_array (Таблиця) функції ВПР (першим є стовпець з унікальним ідентифікатором). Якщо наша база даних буде починатися десь з стовпця K листа Excel, то ми все одно вкажемо значення 2 в цьому полі.
- Якщо перший стовпець бази даних (що містить унікальні значення) впорядкований по зростанню (в алфавітному порядку чи за чисельним значенням), то в цьому полі можна ввести значення TRUE (ІСТИНА) або залишити його порожнім.
- Якщо перший стовпець бази даних не впорядкований або відсортований за спаданням, тоді для цього аргументу необхідно встановити значення FALSE (ЛОЖЬ).
Так як перший стовпець нашої бази даних не впорядкований, ми вводимо для цього аргументу значення FALSE (ЛОЖЬ):
Останній штрих…
От і все! Ми ввели всю інформацію, яка потрібна функції ВПР. щоб надати нам те значення, яке нас цікавить. Тисніть ОК і зверніть увагу, що опис товару, що відповідає коду R99245. з'явилося в осередку B11:
Створена формула виглядає ось так:
Якщо ми введемо інший код в клітинку A11, то побачимо дію функції ВПР. в поле Description з'явиться опис, відповідне новому коду товару.
Ми можемо виконати ті ж кроки, щоб отримати значення ціни товару (Price) в осередку E11. Зауважте, що в осередку E11 повинна бути створена нова формула. Результат буде виглядати так:
... а формула буде виглядати так:
Зверніть увагу, що дві створені формули відрізняються тільки значенням третього аргументу, яке змінилося з 2 на 3, оскільки тепер нам потрібно витягти значення вже з третього стовпця таблиці.
Якщо ми вирішили придбати 2 одиниці товару, то запишемо 2 в клітинку D11. Далі вводимо просту формулу в осередок F11, щоб порахувати підсумок по цьому рядку:
... яка виглядає ось так:
Завершуємо створення шаблону
Наш шаблон ще не закінчений повністю. Щоб завершити його створення, зробимо наступне:
- Видалимо значення коду товару з осередку A11 і значення 2 з осередку D11. В результаті створені нами формули повідомлять про помилку.
Ми можемо виправити це, розумно застосувавши функції IF (ЯКЩО) і ISBLANK (ЕПУСТО). Змінимо нашу формулу з такого виду:
= VLOOKUP (A11, 'Product Database'! A2: D7,2, FALSE)
= ВПР (A11; 'Product Database'! A2: D7; 2; ЛОЖЬ)
= IF (ISBLANK (A11), "", VLOOKUP (A11, 'Product Database'! A2: D7,2, FALSE))
= ЕСЛИ (ЕПУСТО (A11); ""; ВПР (A11; 'Product Database'! A2: D7; 2; ЛОЖЬ))
Нам потрібно скопіювати формули з осередків B11, E11 і F11 на решту рядка нашого шаблону. Зверніть увагу, що якщо ми просто скопіюємо створені формули, то нові формули не будуть працювати правильно з нашою базою даних. Це можна виправити, записавши посилання на осередки як абсолютні. Інший спосіб, більш просунутий, це створити іменований діапазон для всіх осередків, які вміщали нашу базу даних (назвемо його Products) і використовувати ім'я діапазону замість посилань на осередки. Формула перетвориться з такою:
= IF (ISBLANK (A11), "", VLOOKUP (A11, 'Product Database'! A2: D7,2, FALSE))
= ЕСЛИ (ЕПУСТО (A11); ""; ВПР (A11; 'Product Database'! A2: D7; 2; ЛОЖЬ))
... тепер можна сміливо копіювати формули в осередки інших рядків нашого шаблону.
Якщо підійти до роботи з максимальною відповідальністю, то можна створити базу даних всіх наших клієнтів ще на одному аркуші документа. А потім вводити ідентифікатор клієнта в комірку F5, щоб автоматично заповнювати комірки B6, B7 і B8 даними про клієнта.
