Як користуватися функцією ВВР в Excel: зрозуміла інструкція для чайників, відео, порівняння двох таблиць, що випадає список, приклади. Формула функції ВПР в Excel: приклади, кілька умов, розшифровка
Стаття-інструкція: «Функція ВПР в Excel для чайників».
Навігація
Функція ВПР в Excel дає можливість переставляти дані однієї таблиці в іншу по осередках. Це дуже популярний і зручний спосіб, і про нього ми поговоримо в сьогоднішньому огляді.
Функція ВПР в Excel: інструкція для чайників
Припустимо, перед нами стоїть наступне завдання. На нашу фабрику завезли необхідні нам матеріали, які представлені в наступній таблиці:
Таблиця в «Excel»
В іншій таблиці ми маємо той же самий список із зазначенням вартості цих матеріалів:
Прайс-лист в «Excel»
Тобто, в першій таблиці ми маємо кількість матеріалів, а в другій - вартість за штуку (руб / кв.м). Наше завдання, обчислити загальну вартість всіх матеріалів, які були завезені на фабрику. Для цього нам потрібно перенести показники з другої таблиці в першу і за допомогою множення знайти відповідь.
Приступимо до справи:
- У першій таблиці нам не вистачає двох стовпців - « Ціна»(Тобто вартість за 1 кв.м) і« вартість»(Тобто загальна вартість завезеного матеріалу). Додамо ці стовпці. Тепер виділіть в стовпці « Ціна»Верхню перший осередок, запустіть« Майстер функцій»(Натиснути одночасно« F3»І« Shift»), У вкладці« формули" Натисніть на " Посилання та масиви»І в випадаючому списку виберіть« ВПР».
Запустіть «Майстер функцій», у вкладці «Формули» натисніть на «Посилання та масиви» і у випадаючому списку виберіть «ВПР»
- Далі в новому вікні навпроти пункту « шукане значення»Спостерігаємо такі показники: A1: А15. Тобто програма фіксує діапазон найменувань матеріалів у відповідному стовпі « матеріали». Те ж саме програма повинна показати і в другій таблиці.
Спостерігаємо такі показники: A1: А15
- Тепер розберемося з другим пунктом - « Таблиця»(Друга таблиця з вартістю матеріалів). Натисніть на цей пункт, потім виділіть в другій таблиці діапазон найменувань матеріалів разом з цінами. У підсумку, результат повинен бути наступним.
Натисніть на пункт «Таблиця»
- Далі виділіть результати в пункті « Таблиця»І натисніть на« F4», Після чого в них з'явиться новий символ« $»- таким чином програма буде посилатися на показники в« Таблиця».
Виділіть результати в пункті «Таблиця» і натисніть на «F4»
- Далі переходимо до третього пункту - « номер стовпця». Вкажіть тут « 2». В останньому четвертому пункті « інтервальний перегляд»Вкажіть« БРЕХНЯ». Після всіх цих маніпуляцій натисніть на « ОК».
В останньому четвертому пункті «Інтервальний перегляд» вкажіть «БРЕХНЯ»
- Останній штрих. Курсором мишки натисніть на нижній правий кут таблиці і потягніть вниз то тих пір, поки не побачите повний список найменувань матеріалів разом з цінами.
Курсором мишки натисніть на нижній правий кут таблиці і потягніть вниз
Все, ми зв'язали обидві наші таблиці. Обчислити загальну вартість матеріалів буде простіше простого. Тепер потрібно враховувати, що якщо прайс-лист зміниться, то зміняться і отримані нами результати. Тому нам потрібно уникнути цього за допомогою наступних дій:
- Виділіть всі показники в стовпці « Ціна», Натисніть на ньому правою кнопкою мишки і далі - на« копіювати».
- Знову натисніть правою кнопкою мишки по вже виділених цінами і потім - на « спеціальна вставка».
- У віконці поставте галку, як показано на скріншоті, і натисніть на « ОК»
Поставте галку на «Значення»
Порівнюємо дві таблиці за допомогою функції ВПР в Excel
Припустимо, що прайс-лист змінився, і нам необхідно порівняти дві таблиці - нові і старі ціни:
Прайс лист
Для цього скористаємося відповідною функцією в « Excel»:
- У старій таблиці додайте стовпець « Нова ціна»
Додаємо стовпець «Нова ціна»
- Далі повторюємо дії, які ми робили вище - виділяємо першу верхню клітинку, вибираємо « ВПР", у пункті " Таблиця»Отримуємо результати і натискаємо на« F4».
в пункті «Таблиця» одержуємо наступні результати
- Тобто на скріншоті вище ми спостерігаємо, що з таблиці з новими цінами ми перенесли вартість кожного матеріалу в стару таблицю і отримали наступні результати.
отримана таблиця
Працюємо з декількома умовами за допомогою функція ВПР в Excel
Вище ми працювали з однією умовою - найменуванням матеріалів. Але в реальності обставини можуть скластися інакше. Ми можемо отримати таблицю з двома і більше умовами, наприклад, як з найменуванням матеріалів, так і з найменуванням їх постачальників:
Таблиця з найменуваннями постачальників і матеріалів
Тепер наше завдання ускладнюється. Припустимо, нам необхідно знайти, в яку ціну поставляє той чи інший матеріал окремо взятий постачальник. Ще складніше виявиться ситуація, коли постачальник продає кілька матеріалів, при тому що самих постачальників теж може бути кілька.
Спробуємо вирішити це завдання:
- Додайте в таблицю лівий крайній стовпець і об'єднайте два стовпці - « матеріали»І« постачальники».
Об'єднайте два стовпці - «Матеріали» і «Постачальники»
- Точно так же об'єднайте шукані критерії запиту
Об'єднайте шукані критерії запиту
- Знову в пункті « Таблиця»Задайте відповідні показники
У пункті «Таблиця» задайте такі показники
- На скріншоті вище ми бачимо наступну формулу (1 - об'єкт пошуку; 2 - місце пошуку; 3 - взяті нами дані).
1 - об'єкт пошуку; 2 - місце пошуку; 3 - взяті нами дані
Працюємо з списком, що випадає
Припустимо, що у нас певні дані (наприклад, « матеріали») Показуються в розкривається списку. Наше завдання, щоб при цьому було доступно бачити ще й вартість.
Для цього зробимо наступні кроки:
- Натискаємо мишкою на осередок « E8»І у вкладці« дані»Вибираємо« перевірку даних»
Натисніть на «Перевірка даних»
- Далі у вкладці « параметри" Оберіть " перелік»І натисніть на« ОК»
Виберіть «Список» і натисніть на «ОК»
- Тепер в списку, що випадає ми бачимо пункт « Ціна»
Отримуємо пункт «Ціна»
- Далі нам потрібно, щоб навпроти пункту « Ціна»Виставлялася вартість матеріалу
- Натисніть на осередок « E9», В« Майстрі функцій" Оберіть " ВПР». Навпаки пункту « Таблиця»Повинні бути виставлені следующе показники.
В «Таблиця» повинні бути следующе показники
- Натисніть на " Ок»І отримаєте результат
отримуємо результати