Функція ВПР і її використання

1. Синтаксис функції ВПР
2.Швидке порівняння двох таблиць за допомогою ВПР
3. Функція ВПР з декількома умовами
4. ВПР і випадаючий список








ВПР (VLOOKUP англ.) – це одна із самих популярних і корисних функцій Excel із розділу «Посилання та масиви». ВПР дозволяє переставити дані з однієї таблиці у відповідну клітинку іншої таблиці. ВПР шукає значення в крайньому лівому стовпці таблиці і повертає значення комірки, яка знаходиться у вказаному стовпці того ж рядка.

Синтаксис функції ВПР: 


=ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
  • искомое_значение – значення, яке має бути знайденим в першому стовпці массиву (значення, посилання або рядок тексту)
  • таблица – таблиця з текстом, числами або логічними значеннями, в якій здійснюється пошук даних.
  • номер_столбца – номер стовпця в таблиці, із якого потрібно витягнути значення.
  • [интервальный_просмотр] – логічне значення, яке оприділяє точно (ЛОЖЬ) чи приблизно (ИСТИНА або відсутність значення) повинен виконуватись пошук в першому стовпці таблиці.

Варто відмітити, що ИСТИНА, або 1, або відсутність значення дуже корисною є, якщо, наприклад, у другій таблиці у значення із додатковими пробілами.

Наприклад в нас є прайс. Наше завдання – заповнити ціну у стовпці F.


Для розв’язання такої задачі ми використаємо функцію ВПР, яка легко і ефективно визначить ціну.

Розв’язок буде: =впр(D3;A:B;2;ЛОЖЬ)


В нашому прикладі – нам потрібно знайти ціну для абрикоса (в першому рядку) з таблиці A:B. Ціна в таблиці, тобто те значення, яке нам потрібно перетягнути, знаходиться в другому стовпчику. І так, як в нас назва товарів у двох табличках співпадають, то ми використовуємо точний пошук (ЛОЖЬ або 0).
Прописавши ми формулу для першого значення копіюємо його на цілий стовпець.

Швидке порівняння двох таблиць за допомогою ВПР


Функція ВПР допоможе порівняти значення у великих таблицях. Припустимо у нашому попередньому прайсі помінялися ціни. Нам потрібно порівняти ціни у двох прайсах. 


В старому прайсі добавляємо стовпець «Нова ціна». І прописуємо формулу за допомогою ВПР, перетягаючи нову ціну  відповідному значенні:

=ВПР(A3;$E$3:$F$15;2;0)



Копіюємо формулу на цілий стовпець і можемо приступити до порівнювання зміни цін. Тут можна добавити стовпець дельту ціну. Але це вже як кому зручно, адже головне завдання ми уже зробили за допомогою ВПР.

Функція ВПР з декількома умовами


В попередніх прикладах ми показали використання звичайної функції ВПР у найпростіших випадках. Але на практиці дуже часто є випадки, коли значення потрібно перетягнути по двох або навіть більше умовах.

Приклад: у нас є таблиця-прайс 1, де ціна присвоюється артикулу товару і є таблиця розшифрування артикулів 2. Нам потрібно заповнити ціну для конкретної назви товару у таблиці 3.


Тоді формула буде мати наступний вигляд: =ВПР(ВПР(G3;$D$3:$E$15;2;0);$A$3:$B$15;2;0)


Друга функція ВПР, яка використовується всередині, вичислює значення для першої функції ВПР.


ВПР і випадаючий список

Наприклад, перед нами поставили задачу з випадаючим списком товарів, для яких потрібно вичислити ціну.
Для початку створимо випадаючий список із таблиці, для якої ми в попередньому завданні вичисляли ціну. Для цього в клітинку, в якій буде назва товару ставимо курсор, заходимо у вкладку «Данные» - Меню «Проверка данных» - «Список». У комірці «Источник» віділяємо список товарів із тмблиці =$G$3:$G$15


В нас з’являється випадаючий список із назвами товарів:


Розраховуємо ціну: =ВПР(J4;G3:H15;2;0)

Тепер для конкретного товару з’являється ціна. І коли ми будемо вибирати інше значення із випадаючого списку, то ціна буде відповідно автоматично змінюватись.
Такий трюк в Excel з ВПР інколи дуже корисний та зручний.

Скачати файл з прикладом використання функції ВПР в Excel можна тут.

Таблиця Excel – просте рішення для складної задачі


Всі, хто працюють в Excel знають, що його можливості неможливо вичерпати. Excel створений для ефективної і найбільш оптимальної роботи. Одна із таких функцій Excel являється таблиця Excel. Таблиця Excel інколи знаходить для складного завдання найпростіше рішення. Можливості таблиці Excel перетворить масив даних на зручну та ефективну таблицю завдяки ряду вбудованих функцій і властивостей.

Отже, розберемося що корисного містить така таблиця:
1.       Хороше візуальне сприйняття інформації, завдяки великій кількості візуальних ефектів таблиці Excel.
2.       Вид таблиці та її дизайн можна змінити відповідно до ваших побажань. Межі клітинок і їх розміри можна також легко відредагувати.
3.       Ще одна корисна функція – це можливість фарбувати фон осередків. Така можливість забезпечує найбільш ефективний спосіб передачі інформації.
4.       Полегшений аналіз даних таблиці. Легке керування даними
5.       Легке сортування таблиці за вибраним параметром.
6.       Вбудований фільтр для даних таблиці.
7.       Автоматичний підсумок з набором різних функцій для вибору.

Така таблиця інколи дуже корисна і ефективна, але звичайно ж не завжди. Існують ряд випадків, коли краще відмовитись від такої функції.

Всі, хто працював з електронними таблицями точно бачив такі таблиці, але напевно не догадувався про її спосіб створення та можливості. Далі на практиці подивимся найбільш використовуваний спосіб створення таблиці Excel, та основні моменти роботи з такою функцією.

Створення таблиці Excel з потрібним стилем:

Найперше поставимо перед собою задачу: визначимо середньомісячну ціну продуктів та їх дельту (зміну).

Відповідно до умови нам потрібно визначитись з видом та розміром таблиці. Припустимо, що таблиця з початковими даними виглядатиме наступним чином:

Для створення таблиці:
  • Виділяємо побудовану раніше таблицю. У вкладці «Вставить» вибираємо функцію «Таблица».
  • Виділяємо побудовану раніше таблицю. На вкладці «Главная» в меню «Стилі» вибираємо функцію: «Форматировать, как таблицу». Відкриваємо випадаюче меню і вибираємо дизайн таблиці, який вважаємо за доцільний:


Далі з’являють вікно для підтведження. Ставимо галочку на меню «Таблица с заголовками», так як наша таблиця уже має заголовки кожного стовпця. І в результаті ми отримуємо таблицю Excel, в подальшому яку будемо редагувати та вдосконалювати.

Варто відмітити, що така таблиця стала візуально чіткішою, завдяки почергового затінення.

Забезпечення цілісності даних

Наприклад, для необхідності, можна дозволяти вводити в певний стовпець дані тільки певного формату: текс, число, дата і т.п.


Редагування таблиці: рядок заголовка, строкаті рядки

Варто відмітити, що у будь-який час можна добавити ще нові рядки. Для цього потрібно просто почати вводити текст внизу таблиці без рядочка підсумка і таблиця автоматично продовжиться ще на один рядок донизу.

Або ж за допомогою «маркера змінення розміру» таблиці можна автоматично добавити кілька пустих рядків:





За допомогою контекстного меню у вкладці «Конструктор» ми можемо змінювати параметри таблиці:


В першому меню конструктора таблиці можна присвоїти назву створеній таблиці. По замовчуванню вона зараз називається «Таблица5». Назва таблиці дуже корисна, коли при створенні великого інтегрованого звіту таких таблиць є дуже багато.

«Параметры стилей таблиц»: «строка заголовка», «строка итогов», «чередующиеся строки», «первый столбец», «последний столбец», «чередующиеся столбцы»: за допомогою цих функцій можна добавити або забрати рядок із заголовком або підсумком, можна візуально відмітити перший або останній стовбець, замалювати чергуючим кольором стовпці або ж рядки.

Обчислювальний стовпець

Для початку давайте заповнимо порожні стовпці:
  •      «Середньомісячна ціна», що являється середнім арифметичним «Ціна(лютий)» та «Ціна (березень)»: =СРЗНАЧ(Таблица5[@[Ціна (лютий)]:[Ціна (березень)]])
  • «Дельта ціни» - різниця відповідних даних: =[@[Ціна (березень)]]-[@[Ціна (лютий)]]
Великою перевагою такої таблиці є те, що прописуючи формулу в першому рядку Excel миттєво створює обчислювальний стовпець, і формула автоматично продовжується на всі решту рядків у цьому стовпці.

Структуровані посилання

З формул видно, що в такій таблиці клітинкам автоматично присвоюються структуровані посилання – поєднання імен таблиць і стовпців, що дуже зручно для написання формул. Така функція просто безцінна у таблицях з великим розміром. Про структуровані посилання ми будемо говорити пізніше.

Рядок підсумків

Для заповнення рядка підсумку «Итог» не потрібно навіть прописувати формули, потрібно лише вибрати функцію із випадаючого меню, як на рисунку. Крім того, дуже зручно, що вибір функції активний для кожного стовпця окремо, що теж дуже зручно. Для нашої задачі доцільно буде порахувати середню ціну.


Фільтрація даних в рядку заголовка

Ще однією дуже корисною для великих таблиць функцією є фільтрація, яку ми можемо установити або забрати а допомогою меню «Кнопка фильтра». Наприклад в нашій таблиці ми можемо відфільтрувати тільки фрукти, або овочі, або гриби:


Для видалення фільтра потрібно натиснути «Удалить фильтр из столбца «Вид товару», у
спливаючому вікні, що на рисунку.

Хоча, крім такого звичайного використання фільтру, дуже корисною фільтрацією являється саме фільтрація числових даних, а не текстових. Наприклад: нам потрібно відфільтрувати тільки ті продукти, ціна яких знизилась у березні, тобто «Дельта ціни» більша 0:



А для групування текстових значень групувого значення, таких як у нас «овочі», «фрукти», «гриби» можна використати функцію «Вставить срез»:



У вікні, що з’являється вибираємо поле для фільтрації. У нас це буде «Вид товару». Так ми зможемо з легкістю проаналізувати кожну групу товару, що інколи просто необхідно. Такий зріз буде виглядати наступним чином:




Вид та дизайн такої таблиці можна змінити у будь-який момент в меню «Конструктор» - «Стили таблиц»:


Якщо на якомусь етапі створення таблиці Вам не підходить така функції і ви бажаєте вернутись до звичайного діапазону Excel, то це можна зробити у контекстному меню «Конструктор» - «Переобразить в диапазон» в розділі «Инструменты».

Для сортування даних в таблиці Excel достатньо просто вибрати параметр за яким ми хочемо відсортувати дані в таблиці у необхідному нам стовпці і за необхідним значенням. Так як на рисунку сортування вибирається для текстового стовпця, то Excel пропонує відсортувати за алфавітом або коліром. Для цифрофих даних було б – за зростанням, спаданням або коліром.


Отже, ми переконалися, що таблиця Excel – проста у використанні та зручна функція, яка полегшує створення звітів. Завдяки такій таблиці ми можемо керувати даними з допомогою.

Файл з прикладом таблиці ви можете подивитись тут.

Я рекомендую під час прочитання статті паралельно відтворювати всі практичні моменти самостійно, це покращить розуміння таблиць.

EXCEL, як космос

Всім відомо, що Excel - це найбільш популярний і потужний офісний додаток, який використовується в багатьох сферах для проведення розрахунків, аналізу даних, прогнозування, створення таблиць, діаграм і багато-багато іншого. А це і не дивно, бо Excel - це простий і ефективний засіб для вирішення багатьох задач в економічній і бухгалтерській діяльності. Більшість людей вважають, що Excel - це лише таблиця. Але насправді Excel, найперше і найголовніше, покликаний для оптимізації і автоматизації робочого процесу.

Є безліч різноманітних книг, статей інструкцій, відео, блогів і т.п. з тематикою електронних таблиць Excel, згідно яких можна навчатись. Поки що мій блог не має значущих переваг перед ними, але, думаю, в майбутньому обов’язково матиме. Мета блогу “Excel, як космос - поділитися досвідом, вміннями і прагненням самовдосконалюватись. Ми будемо навчатись перетворювати дані в цінну і корисну інформацію, яка буде наводити на логічні висновки і рішення.

В моєму блозі про Excel Ви зможете знайти:

  • Найпопулярніші формули, їх застосування та можливості.
  • Трюки та корисні поради, які зроблять Вашу роботу в Excel більш ефективною.
  • Практичне пояснення використання формул, ілюстровані інсрукції та файли з прикладами.
  • Побудова ефективних графіків та діаграм.
  • і ще багато іншого...
Чому я порівняла Excel з космосом? - спитаєте ви. Все просто! Космос - це унікальна і недосяжна для нашої свідомості “річ”, якщо так його можна назвати. Космос не має меж і постійно вабить людський світ своєю загадковість і можливостями.
Ось так вабить і Excel! Можливості Excel не можна перерахувати. Робота в цьому офісному додатку дійсно цікава і корисна. Excel постійно вдосконалюється, тому і повністю осягти його можливості майже не реально. Тому нумо почнімо досліджувати космічні можливості Excel, самовдосконалюватись і оптимізувати свою роботу!

Ви також можете прочитати цю статтю на російській мові.

Популярні статті: