Тема: таблиці підстановки даних з двома змінними

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

Вимоги до оформлення звіту:

Звіт має включати назву теми, номер лабораторної роботи, мету, стислий конспект теоретичних відомостей, опис виконання практичної частини.

Теоретичні відомості

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

Таблиці підстановки с двома змінними використовують одну формулу с двома наборами значень. Формула повинна посилатися на дві різні комірки вводу.

Практична частина

1. Побудувати таблицю за зразком (Рис. 1). Зверніть увагу, що значення клітин СП, С13,

С14, С16 обчислюються за формулами, а всі інші -заповнюються вручну.

Загальну суму видатків обчислити за формулою =СУММ(С4:С10)

Валовий прибуток =С12-СІІ.

Відсоток прибутку =С13/С12.

Чистий прибуток =С12*С14-С15.

2. Розрахувати обсяг валового прибутку в залежності від різного відсотку прибутку (від 10 до 30% з кроком у 5%) та змінного обсягу виручки (від 4 до 7 тис. грн з кроком у 500 грн).

До коміри F3 внести формулу =С12*С14. Шляхом автоматичного заповнення внести варіації
Рис. 1 виручки та ставок прибутку, як показано на рис. 2.

3. Виділити діапазон F3:M8, натиснути Данные — Таблица подстановки. До комірки Подставлять значення по строкам внести C14, до комірки Подставлять значения по столбцам внести С12. Натискаємо ОК.

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

Рис. 2

4. Для більш ефективного та наглядного аналізу побудуємо таблицю розрахунку чистого
прибутку за аналогом таблиці розрахунку валового прибутку. Для цього до комірки F12
введемо формулу =С12*С14-С15 (рис. 3).

Рис. 3

5. Визначимо за допомогою функцій три найменших та три найбільших значення чистого
прибутку з отриманого на рис. 3розрахунку.

На цьому ж Листі нижче від розрахунку чистого прибутку викличте статистичну функцію НАИБОЛЬШИЙ тазадайте наступним чином ЇЇ аргументи:

=НАИБОЛЬШЫЙ(G13:M17;1)

Результатом виконання цієї функції повинно стати число 1200. Змініть значення 1 аргументу цієї функції на 2, та отримайте друге найбільше значення чистого прибутку (рис. 4).

Рис. 4

Аналогічно отримайте третє найбільше число масиву.

Для визначення найменших значень викличте статистичну функцію НАИМЕНЬШИЙ та задайте наступним чином її аргументи:

=НАИМЕНЬШИЙ(G13:M17;1)

Результатом виконання цієї функції повинно стати число -500. Аналогічно отримайте інші найменші числа масиву (рис. 4).

6. Визначимо положення (по величині) значення в ряду обраних значень - ранг. Для цього викличемо в клітині поряд із першим найбільшим значенням масиву статистичну функцію РАНГ

Аргументом число встановимо посилання на адресу клітини, де розташована цифра 1200 (рис. 4).

Аргументом ссылка визначимо діапазон клітин, в якому розташований ряд найбільших та найменших чисел (рис. 4).

Аргумент порядок опустимо (він може отримувати значення 1 або 0 і визначає порядок сортування масиву чисел по зростанню чи по зменшенню).

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

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

Завдання для самостійної роботи

1) Скопіюйте всі таблиці, отримані при виконанні практичної частини на новий аркуш
(наприклад, Лист 2).

До комірки В17 нового аркуша додайте показник Рівень рентабельності (на основі чистого прибутку).

До комірки С17 введіть формулу розрахунку цього показника. Зверніть увагу на те, що при обчисленні рівня рентабельності формула обов'язково повинна містити дві змінні комірки:

- виручка;

- відсоток прибутку.

Побудуйте таблицю підстановки з двома даними (виручка та відсоток прибутку). Задайте інтервал виручки від 4 до 10 тис. грн із шагом в одну тисячу. Змінюйте відсоток прибутку в межах 10-30% із кроком 2%.

2) На новому аркуші (Лист 3) розрахувати, можливі обсяги фіксованих щомісячних
платежів по кредиту розміром 50 000 грн для варіантів річних ставок платні по кредиту від 15
до 18,5% із кроком 0,25%. На термін від 1,5 до 3,5 років із кроком у півроку. Визначте, третє
найбільше та найменше значення щомісячних платежів із отриманого масиву.

ППЛАТ (ставка; кпер; пс; бс; тип)

Ставка — процентна ставка по позичці.

Кпер — загальне число виплат по позичці.

Пс — наведена на сучасний момент вартість, або сума, що на сучасний момент дорівнює майбутнім платежам.

Бс — необхідне значення майбутньої вартості, або залишку засобів після останньої виплати. Якщо аргумент бс не наводити, то він прирівнюється 0 (нулю).

Тип — число 0 (нуль) або 1, що означає, коли повинна здійснюватись виплата. О чи не наведене значення - в кінці періоду; 1 — на початку.