Лабораторна робота 15

Обчислення в MS Excel 2016

Функція VLOOKUP

Завдання: Створити нову робочу книгу.

Книга містить два робочих аркуші:

- «Аркуш 1» з даними про контрагентів;

- «Рахунок» з формою Рахунка на оплату.

На Аркуші 1 створити таблицю та занести в неї дані про Постачальника та Покупців готової продукції, рис. 1.

Рис. 1

Далі на 2 аркуші, який назвати «Рахунок» створити форму Рахунка на оплату (рис.2).

Рис.2

Далі заповнити документ потрібною інформацією.

У комірку А1 записати номер першого документа 1.

Для автоматичного заповнення Рахунка за заданим у комірці А1 номером введемо формули.

У комірці E4 (номер документа):

=А1

У комірці G4 повинна бути записана дата створеного документа.

Для автоматичного запису потрібної інформації використати функцію VLOOKUP(ВПР). У комірку G4 поставити курсор та через Мастер функций відкрити функцію VLOOKUP(ВПР) (рис.3)

1)У рядку Шукане_значення посилання на комірку А1;

2)Помістити курсор у поле Таблиця. Перейти на «Аркуш 1» та виділити діапазон комірок B5:H15; рис.4

Рис.3

Рис.4

3)перемістити курсор у поле Номер_стовпця та ввести з клавіатури значення «2»—інформація з другого стовпчика на «Аркуші 1» за рахунком справа (стовпчик С) від початку масиву, що переглядається;

4)   перемістити курсор у поле Точність пошуку та ввести з клавіатури значення FALSE (шукати точний збіг). Рис.5.

Рис.5

При натисканні Ок отримаємо результат, рис. 6.

Рис.6

Якщо у вашому документі з`явилась не дата, а число, то необхідно через Формат клітинок вказати програмі, що у цій клітинці повинна бути дата, рис.7.

Рис.7.

У комірці В6 – Постачальник записати формулу, рис.8.

Рис.8.

Результат виконаної роботи, рис.9

Рис.9.

= VLOOKUP (А1;'Аркуш 1'B5:H15;3; FALSE)

Ця формула створюється копіюванням вмісту комірки В14 у комірку D14, а потім у режимі редагування провадиться заміна в аргументі Номер_индекса_столбца значення «2» на значення «5».

Інші формули, створюються аналогічно.

У комірці C7 (розрахунковий рахунок) формула:

= VLOOKUP (А1;'Аркуш 1'B5:H15;4; FALSE)

У комірці C8 (Банк) формула:

= VLOOKUP (А1;'Аркуш 1'B5:H15;5; FALSE)

У комірці B10 (Покупець) формула:

= VLOOKUP (А1;'Аркуш 1'B5:H15;6; FALSE)

У комірці В12 (Договір) формула:

= VLOOKUP (А1;'Аркуш 1'B5:H15;7; FALSE)

Рис.11

Якщо в комірку А1 поставити номер іншого Рахунка, наприклад 4, то всі данні поміняються автоматично, рис.12

Рис.12.

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

Для прикладу використаємо Рахунок для оплати ТОВ «Смаколик» від 5.10.2021 р.

В Рахунку заповнити табличну частину, рис. 13.

Рис.13

В створеній таблиці кількість та ціна заповнюються вручну, а Сума розраховується автоматично, завдяки формулі, яку необхідно записати в комірку К16

=H16*J16

Під таблицею в комірку К19 записати формулу:

=SUM(K16:K18)

Доповнимо Рахунок модулем написання суми словами. Для цього:

- скопіювати діапазон комірок А1:Н21 з книги з назвою «Запис суми словами» (лабораторна робота 15);

- перейшовши на аркуш «Рахунок», виділити комірку N19;

- зробити вставку скопійованого діапазону комірок А1:Н21 з книги з назвою «Запис суми словами» (лабораторна робота 15). Рис.14

Рис.14

Далі з комірки N19 задати посилання на комірку К19 з сумою, вказаною у числовому форматі:

=К19

Рис.15

А у комірці А23 задати посилання на комірку N39:

=N39

У комірці А23 буде вказано суму словами.

Ще доцільно використати функцію для підрахунку кількості заповнених рядків у табличній частині документа. Для цього необхідно поставити курсор в комірку С22 та записати формулу:

=COUNT(B16:B19)

Ця формула дозволить порахувати кількість заповнених рядків таблиці і покаже результат. У нашому випадку це 2.

Заповнений документ на рис.16

Рис.16