Напевно багатьом активним користувачам табличного редактора Excel періодично доводилося стикатися з ситуаціями, в яких виникала необхідність підставити значення з однієї таблиці в іншу. Ось уявіть, на ваш склад зайшов якийсь товар. У нашому розпорядженні є два файли: один з переліком найменувань отриманого товару, другий - прайс-лист цього самого товару. Відкривши прайс-лист, ми виявляємо, що позицій в ньому більше і розташовані вони не в тій послідовності, що в файлі з переліком найменувань. Навряд чи комусь із нас сподобається ідея звірити обидва файли і перенести ціни з одного документа в інший вручну. Зрозуміло, в разі, коли мова йде про 5-10 позиціях, механічне внесення даних цілком можливо, але що робити, якщо число найменувань перевалює за 1000? В такому випадку впоратися з монотонною роботою нам допоможе Excel і його чарівна функція ВПР (або vlookup, якщо мова йде про англомовної версії програми).
ВПР дозволяє працювати з великими таблицями, переносячи дані з однієї в іншу
Що таке ВВР і як нею користуватися?
Отже, на початку нашої роботи по перетворенню даних з однієї таблиці в іншу буде доречним зробити невеликий огляд функції ВПР. Як ви, напевно, вже встигли зрозуміти, vlookup дозволяє переносити дані з однієї таблиці в іншу, заповнюючи тим самим необхідні нам осередки автоматично. Для того щоб функція ВПР працювала коректно, зверніть увагу на наявність в заголовках вашої таблиці об'єднаних осередків. Якщо такі є, вам необхідно буде їх розбити.
Припустимо, нам необхідно заповнити «Таблицю замовлень» даними з «Прайс листа»
Отже, перед нами стоїть завдання - перенести ціни наявних товарів в таблицю з їх найменуваннями і розрахувати загальну вартість кожного товару. Щоб це зробити, нам належить виконати наступний алгоритм:
- Для початку приведіть таблицю Excel в необхідний вам вид. Додайте до заготовленої матриці даних два стовпці з назвами «Ціна» і «Вартість». Виберіть для осередків, що знаходяться в діапазоні новоутворених стовпців, грошовий формат.
- Тепер активуйте перший осередок в блоці «Ціна» і викличте «Майстер функцій». Зробити це можна, натиснувши на кнопку «fx», розташовану перед рядком формул, або затиснувши комбінацію клавіш «Shift + F3». У діалоговому вікні відшукайте категорію «Посилання та масиви». Тут нас не цікавить нічого крім функції ВПР. Виберіть її та натисніть «ОК». До речі, слід сказати, що функція VLOOKUP може бути викликана через вкладку «Формули», в випадаючому списку якої також знаходиться категорія «Посилання та масиви».
- Після активації ВВР перед вами відкриється вікно з переліком аргументів обраної вами функції. В поле «Шукане значення» вам буде потрібно внести діапазон даних, що міститься в першому стовпчику таблиці з переліком товарів, що надійшли і їх кількістю. Тобто вам потрібно сказати Excel, що саме йому слід знайти в другій таблиці і перенести в першу.
- Після того як перший аргумент позначений, можна переходити до другого. У нашому випадку в ролі другого аргументу виступає таблиця з прайсом. Встановіть курсор миші в поле аргументу і перейдіть в лист з переліком цін. Вручну виділіть діапазон з осередками, що знаходяться в області стовпців з назвами товарної продукції і їх ціною. Вкажіть Excel, які саме значення необхідно зіставити функції VLOOKUP.
- Для того щоб Excel не плутався і посилався на потрібні вам дані, важливо зафіксувати задану йому посилання. Щоб зробити це, виділіть в поле «Таблиця» необхідні значення і натисніть клавішу F4. Якщо все виконано вірно, на екрані повинен з'явитися знак $.
- Тепер ми переходимо до поля аргументу «Номер сторінки» і задаємо йому значення «2». У цьому блоці знаходяться всі дані, які потрібно відправити в нашу робочу таблицю, а тому важливо привласнити «інтервальних перегляду» помилкове значення (встановлюємо позицію «БРЕХНЯ»). Це необхідно для того, щоб функція ВПР працювала тільки з точними значеннями і не округляла їх.
Внесіть формулу для першого стовпця
Тепер, коли всі необхідні дії виконані, нам залишається лише підтвердити їх натисканням кнопки «ОК». Як тільки в першій клітинці зміняться дані, нам потрібно буде застосувати функцію ВПР до всього Excel документу. Для цього достатньо розмножити VLOOKUP по всьому стовпцю «Ціна». Зробити це можна за допомогою перетягування правого нижнього кутка осередки зі зміненим значенням до самого низу стовпця. Якщо все вийшло, і дані змінилися так, як нам було необхідно, ми можемо приступити до розрахунку загальної вартості наших товарів. Для виконання цієї дії нам необхідно знайти твір двох стовпців - «Кількості» і «Ціни». Оскільки в Excel закладені всі математичні формули, розрахунок можна надати «рядку формул», скориставшись вже знайомим нам значком «fx».
Важливий момент
Здавалося б, все готово і з нашим завданням VLOOKUP впоралася, але не тут-то було. Справа в тому, що в стовпці «Ціна» як і раніше залишається активною функція ВПР, свідченням цього факту є відображення останньої у рядку формул. Тобто обидві наші таблиці залишаються пов'язаними одна з одною. Такий тандем може привести до того, що при зміні даних в таблиці з прайсом, зміниться і інформація, що міститься в нашому робочому файлі з переліком товарів.
Подібної ситуації краще уникнути за допомогою поділу двох таблиць. Щоб це зробити, нам необхідно виділити комірки, що знаходяться в діапазоні стовпчика «Ціна», і клацнути по ньому правою кнопкою миші. У віконці виберіть і активуйте опцію «Копіювати». Після цього, не знімаючи виділення з обраної області осередків, знову натисніть праву кнопку миші і виберіть опцію «Спеціальна вставка».
Активація цієї опції приведе до відкриття на вашому екрані діалогового вікна, в якому вам потрібно буде поставити прапорець біля категорії «Значення». Підтвердіть вчинене вами дії, клікнувши на кнопку «ОК».
Повертаємося до нашої рядку формул і перевіряємо наявність в стовпці «Ціна» активної функції VLOOKUP. Якщо на місці формули ви бачите просто числові значення, значить, все вийшло, і функція ВПР відключена. Тобто зв'язок між двома файлами Excel розірвана, а загроза незапланованої зміни або видалення прикріплених з таблиці з прайсом даних немає. Тепер ви можете сміливо користуватися табличним документом і не хвилюватися, що буде, якщо «Прайс-лист» виявиться закритий або переміщений в інше місце.
Потягніть маркер в правому нижньому кутку комірки для застосування формули до всієї таблиці
Як порівняти дві таблиці в Excel?
За допомогою функції ВПР ви зможете в лічені секунди зіставити кілька різних значень, щоб, наприклад, порівняти, як змінилися ціни на наявний товар. Щоб зробити це, потрібно прописати VLOOKUP в порожньому стовпці і заслати функцію на змінилися значення, які перебувають в іншій таблиці. Найкраще, якщо стовпець «Нова ціна» буде розташований відразу за стовпцем «Ціна». Таке рішення дозволить вам зробити зміни прайса більш наочними для порівняння.
Можливість роботи з декількома умовами
Ще одним безсумнівним достоїнством функції VLOOKUP є його здатність працювати з декількома параметрами, властивими вашого товару. Щоб знайти товар за двома або більше характеристикам, необхідно:
- Створити два (або, при необхідності, більш) умов для пошуку.
- Додати новий стовпець, в який в процесі роботи функції додадуться всі інші стовпці, по яким відбувається пошук товару.
- В отриманому стовпці, по вищеописаного алгоритму, вводимо вже знайому нам формулу функції VLOOKUP.
На закінчення варто сказати, що підтримка Excel такої функції, як ВВР, значно спрощує роботу з табличній інформацією. Не бійтеся використовувати VLOOKUP в роботі з величезною кількістю даних, адже як би вони не були оформлені, принцип роботи функції завжди один і той же. Все, що вам необхідно зробити - правильно визначити її аргументи.