VLOOKUP - одна з найкорисніших функцій Excel, а також одна з найменш зрозумілих. У цій статті ми демістифікуємо VLOOKUP на прикладі з реального життя. Ми створимо корисний шаблон рахунку для вигаданої компанії.
VLOOKUP - це функція Excel. У цій статті передбачається, що читач вже має деяке уявлення про функції Excel і може використовувати основні функції, такі як SUM, AVERAGE і TODAY. У своєму найбільш поширеному використанні VLOOKUP - це функція бази даних, що означає, що вона працює з таблицями бази даних - або, простіше кажучи, зі списками речей на аркуші Excel. Що за речі? Ну, що завгодно. У вас може бути робочий лист зі списком співробітників, продуктів, клієнтів або компакт-дисків у вашій колекції компакт-дисків або зірок на нічному небі. Це не має великого значення.
Ось приклад списку або бази даних. В даному випадку це список продуктів, які продає наша фіктивна компанія:
Зазвичай, списки, подібні до цього, мають унікальний ідентифікатор для кожного елемента у списку. У цьому випадку унікальний ідентифікатор знаходиться в стовпчику «Код товару». Примітка. Щоб функція VLOOKUP працювала з базою даних/списком, цей список повинен мати стовпчик, що містить унікальний ідентифікатор (або «ключ», або «ідентифікатор»), і цей стовпчик повинен бути першим стовпчиком у таблиці. Наведена вище приблизна база даних задовольняє цьому критерію.
Найскладніше у використанні VLOOKUP - це зрозуміти, для чого він призначений. Отже, давайте подивимося, чи зможемо ми спочатку отримати це ясно:
VLOOKUP виводить інформацію з бази даних/списку на основі наданого екземпляра унікального ідентифікатора.
У наведеному вище прикладі ви вставили б функцію VLOOKUP в іншу електронну таблицю з кодом товару, і вона повернула б вам або опис відповідного товару, його ціну або його доступність (кількість на складі), як описано у вашому оригіналі. список. Яку з цих частин інформації вона передасть вам назад? Ну, ви повинні вирішити це при створенні формули.
Якщо все, що вам потрібно, це один фрагмент інформації з бази даних, то буде дуже складно перейти до побудови формули з функцією VLOOKUP. Зазвичай, ви використовуватимете таку функціональність у багаторазово використовуваній електронній таблиці, як шаблон. Щоразу, коли хтось вводить дійсний код товару, система витягує всю необхідну інформацію про відповідний товар.
Давайте створимо приклад цього: шаблон рахунку-фактури, який ми можемо багаторазово використовувати в нашій фіктивній компанії.
Спочатку ми запускаємо Excel і створюємо собі порожній рахунок:
Ось як це буде працювати: особа, яка використовує шаблон рахунку, заповнює серію кодів товарів у стовпчику «А», і система витягує опис і ціну кожного товару з нашої бази даних продуктів. Ця інформація буде використовуватися для розрахунку підсумкової суми для кожної позиції (за умови, що ми введемо правильну кількість).
З метою спрощення цього прикладу ми розмістимо базу даних продуктів на окремому аркуші в тій же книзі:
Насправді, більш ймовірно, що база даних продукту буде розташована в окремій робочій книзі. Це не має великого значення для функції VLOOKUP, яка насправді не дбає про те, чи розташована база даних на тому ж аркуші, на іншому аркуші або в зовсім іншій книзі.
Отже, ми створили нашу базу даних продуктів, яка виглядає наступним чином:
Щоб протестувати формулу VLOOKUP, яку ми збираємося написати, ми спочатку вводимо дійсний код товару в комірку A11 нашого порожнього рахунку:
Потім ми переміщуємо активну комірку в комірку, в якій ми хочемо, щоб інформація, витягнута з бази даних за допомогою VLOOKUP, зберігалася. Цікаво, що це крок, який більшість людей помиляються. Для подальшого пояснення: Ми збираємося створити формулу VLOOKUP, яка буде витягувати опис, відповідний коду елемента в комірці A11. Куди ми хочемо помістити цей опис, коли отримаємо його? У камері В11, звичайно. Так ось де ми пишемо формулу VLOOKUP: в комірці B11. Виберіть комірку B11 зараз.
Нам потрібно знайти список всіх доступних функцій, які може запропонувати Excel, щоб ми могли вибрати VLOOKUP і отримати деяку допомогу в заповненні формули. Це можна знайти, спочатку клацнувши вкладку «Формули», а потім натиснувши кнопку «Вставити функцію»:
З'явиться вікно, яке дозволяє нам вибрати будь-яку з функцій, доступних в Excel.
Щоб знайти той, який ми шукаємо, ми могли б ввести пошуковий термін, такий як «пошук» (тому що цікава нас функція - це функція пошуку). Система видасть нам список всіх пов'язаних з пошуком функцій в Excel. VLOOKUP є другим у списку. Виберіть його натисканням кнопки Гаразд.
З'являється вікно «Аргументи функції», яке запитує всі аргументи (або параметри), необхідні для завершення функції VLOOKUP. Ви можете думати про це поле як про функції, що ставлять нам такі запитання:
- Який унікальний ідентифікатор ви шукаєте в базі даних?
- Де база даних?
- Яку частину інформації з бази даних, пов'язаної з унікальним ідентифікатором, ви хочете отримати для вас?
Перші три аргументи показані жирним шрифтом, вказуючи на те, що вони є обов'язковими аргументами (функція VLOOKUP без них неповна і не буде повертати допустиме значення). Четвертий аргумент не виділено жирним шрифтом, що означає, що він необов'язковий:
Ми закінчимо аргументи по порядку, зверху вниз.
Перший аргумент, який нам потрібно заповнити, це аргумент Lookup_value. Функція вимагає, щоб ми вказали, де знайти унікальний ідентифікатор (в даному випадку код елемента), для якого вона повинна повертати опис. Ми повинні вибрати код товару, який ми ввели раніше (в A11).
Натисніть піктограму праворуч від першого аргументу:
Потім натисніть один раз на комірку, що містить код товару (A11), і натисніть Enter:
Значення «A11» вставляється в перший аргумент.
Тепер нам потрібно ввести значення для аргументу Table_array. Іншими словами, нам потрібно повідомити VLOOKUP, де знайти базу даних/список. Натисніть піктограму селектора поряд з другим аргументом:
Тепер знайдіть базу даних/список і виберіть весь список - не включаючи рядок заголовка. У нашому прикладі база даних розташована на окремому аркуші, тому спочатку ми натискаємо на вкладку цього аркуша:
Далі ми вибираємо всю базу даних, не включаючи рядок заголовка:
… І натисніть Enter. Діапазон комірок, що представляють базу даних (в даному випадку "База даних продуктів! A2: D7 ") автоматично вводиться для нас у другий аргумент.
Тепер нам потрібно ввести третій аргумент, Col_index_num. Ми використовуємо цей аргумент, щоб вказати VLOOKUP, яку частину інформації з бази даних, пов'язану з нашим кодом товару в A11, ми хотіли б повернути нам. У цьому конкретному прикладі ми хочемо, щоб опис товару було повернуто нам. Якщо ви подивитеся на аркуш бази даних, ви помітите, що стовпчик «Опис» є другим стовпчиком у базі даних. Це означає, що ми повинні ввести значення «2» в поле Col_index_num:
Важливо зазначити, що тут ми не вводимо «2», тому що стовпчик «Опис» знаходиться в стовпчику B на цьому аркуші. Якби база даних починалася в стовпчику K робочого аркуша, ми все одно вводили б "2" в цьому полі, тому що стовпчик "Description" - це другий стовпчик в наборі комірок, який ми вибрали при зазначенні "Table_array".
Нарешті, нам потрібно вирішити, чи вводити значення в останній аргумент VLOOKUP, Range_lookup. Цей аргумент потребує або значення true, або false, або його слід залишити порожнім. Під час використання VLOOKUP з базами даних (як це правильно в 90% випадків) спосіб вирішити, що додати до цього аргументу, можна уявити наступним чином:
Якщо перший стовпчик бази даних (стовпчик, що містить унікальні ідентифікатори) відсортований в алфавітному/числовому порядку в порядку зростання, то в цей аргумент можна ввести значення true або залишити його порожнім.
Якщо перший стовпчик бази даних не відсортовано або відсортовано за збиттям, у цей аргумент необхідно ввести значення false
Оскільки перший стовпчик нашої бази даних не відсортовано, ми вводимо false в цей аргумент:
Це воно! Ми ввели всю інформацію, необхідну для того, щоб VLOOKUP повернув потрібне нам значення. Натисніть кнопку OK і зверніть увагу, що опис, відповідний коду товару «R99245», було правильно введено в комірку B11:
Формула, яка була створена для нас, виглядає наступним чином:
