Таблиці даних для двох змінних

Припустимо, що потрібно проаналізувати позику для різних термінів погашення та різних процентних ставок, тобто провести аналіз функції двох змінних. У математичному вигляді дана задача матиме вираз:

ЩВ = ППЛАТ(x /12; y; ВП),

де ЩВ – щомісячні виплати (шукана величина); y – термін виплати позики (змінна); ВП – величина позики (постійна величина); x – річна процентна ставка (змінна величина).

У цьому випадку потрібно створити таблицю у вигляді, подібному до показаного на мал. 4.9. Тут мітками будуть дві комірки: А3 та А4. Ведіть терміни погашення боргу у комірки діапазон C4:F4.

Після того як виділено діапазон таблиці (B4:F14) і викликана команда Таблица подстановки, необхідно задати дві вхідні комірки: у нашому випадку по стовпцях – $A$3, по рядках – $A$4.

Мал. 4.9. Зразок таблиці підстановки для двох змінних

6. Підбір параметра

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

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

Нехай розмір вкладу становить 150000 грн, термін вкладу – 20 років, процентна ставка – 5% (мал. 4.10). Коефіцієнт нарощування обчислено за формулою (1 + проц.ставка)^термін вкладу, а суму виплат за формулою розмір вкладу * коефіцієнт нарощування.

Мал. 4.10. Таблиця для ілюстрації задачі.

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

Припустимо, що потрібно визначити якими повинні бути вхідні дані, щоб через 20 років сума виплат становила 500 000 грн. Тут йдеться або ж про збільшення розміру вкладу, або про збільшення процентної ставки. Вирішити таку задачу можна за допомогою команди Сервис Þ Подбор параметра.

Для застосування цієї функції необхідно виділити комірку, в якій буде знаходитись результат (B6), та викликати програму Подбор параметра. При цьому відкриється діалогове вікно (мал. 4.11), в якому необхідно заповнити відповідні поля. У нашому випадку шукаємо відповідь на питання: яким має бути розмір вкладу, щоб через 20 років сума виплат становила 500 000 грн.

Мал. 4.11 Аркуш з діалоговим вікном

У результаті натискання кнопки ОК, на екрані з’являється діалогове вікно (мал. 4.12), яке інформує про те, що обчислення завершилося. Результат обчислень вноситься у вихідну таблицю після натискання кнопки ОК діалогового вікна Результат подбора параметра.

Мал. 4.12. Діалогове вікно, яке інформує, що обчислення завершено

7. Пошук розв’язку

Функціонування процедури пошуку розв’язку розглянемо на прикладі.

Задача. Скласти виробничу програму випуску виробів А і В за критерієм максимальної рентабельності при заданих обмеженнях виробничої потужності чотирьох цехів заводу. Обидва вироби послідовно обробляються в цехах. Час можливої роботи цехів складає відповідно 12, 8, 16 і 12 годин за плановий період, а норми затрат часу на виготовлення одиниці виробу А по цехах відповідно рівна 2, 1, 4 і 0 год, а на виріб В – 2, 2, 0 і 4 год. Прибуток від реалізації одиниці продукції типу А становить 2 грн., а типу В – 3 грн.

Розв’язання. Для побудови математичної моделі варто дані задачі розмістити у таблиці у такий спосіб:

	Виріб А	Виріб В	Норма затрат часу, год
1 цех
2 цех
3 цех
4 цех
Прибуток	2 грн	3 грн	max

Тоді математичну модель можна записати у вигляді системи лінійних нерівностей

де x ₁ i x ₂ – кількість виробів А та В відповідно.

Цільова функція матиме вигляд:

Задача зводиться до знаходження двох невідомих x ₁ i x ₂ системи чотирьох лінійних нерівностей, котрі би задовольняли цільову функцію. Іншими словами, потрібно знайти такий план випуску продукції, при якому прибуток (цільова функція) був би максимальним.

Перед запуском процедури пошуку розв’язку вхідні дані повинні бути подані у вигляді таблиці з встановленими залежностями між даними (у формульному режимі таблиця представлена на мал. 4.13).

Мал. 4.13. Формульний вигляд таблиці даних

Вхідні дані записано у діапазон B3:D7. У цих комірках знаходяться коефіцієнти при невідомих x ₁ i x ₂ системи лінійних нерівностей (рядки 3–6) та цільової функції (рядок 7), а також відповідні вільні члени системи. У комірках B9 i C9 будуть знаходитись шукані розв’язки, а саме значення невідомих x ₁ i x ₂ (у початковому варіанті приймаємо ці значення рівними 0). Формула, за якою обчислюється цільова функція, знаходиться у комірці Е7. У комірках У Е3:Е6 записано формули, за якими можна обчислити час, який затрачається на виготовлення виробів у кожному з цехів (взаємозв’язок між коефіцієнтами та невідомими системи).

Після того як введено усі вхідні дані та встановлено взаємозв’язки між елементами задачі, потрібно запустити процедуру Поиск решения. Для цього активізуйте команду Сервис Þ Поиск решения, після чого відкриється відповідне діалогове вікно (мал. 4.14), поля якого потрібно заповнити відповідними даними.

Мал. 4.14. Діалогове вікно Поиск решения

В поле Установить целевую ячейку введіть адресу комірки, значення якої буде використовуватися як критерій оптимізації (у нашому випадку – комірка Е7, у якій знаходиться формула для обчислення прибутку від реалізації продукції. Значення у цій комірці повинно бути максимальне (для цього потрібно активізувати відповідний перемикач максимальному значению у області Равной:).

У полі Изменяя ячейки вкажіть, у яких комірках програма повинна змінювати значення для отримання оптимального розв’язку (у нашому прикладі це комірки B9:C9, в яких знаходитиметься розв’язок задачі x ₁ i x ₂).

Зауваження. При натисканні кнопки Предположить програма за замовчуванням виділить діапазон комірок, на які є посилання в цільовій комірці.

Наступним етапом заповнення діалогового вікна (мал. 4.14) є задавання обмежень на розв’язок. Для цього потрібно натиснути кнопку Добавить області Ограничения діалогового вікна. У результаті цього відкриється нове діалогове вікно (мал. 4.15).

Мал. 4.15. Діалогове вікно Добавление ограничения

У полі Ссылка на ячейку потрібно ввести адресу комірки, на вміст якої накладається певне обмеження (у нашому випадку – це лінійна комбінація невідомих та відповідних коефіцієнтів, які визначають затрати часу на виготовлення обох деталей для кожного з цехів). У полі Ограничения можна ввести або конкретне обмежувальне число, або адресу комірки, де знаходиться обмеження. Між цими полями необхідно вибрати потрібний оператор (<=, =, >=, цел., двоич.), який встановлює відношення між коміркою, на яку накладається обмеження, та самим обмеженням. Після вводу кожного з обмежень необхідно натискати кнопку Добавить.

Після введення усіх обмежень в полі Ограничения вікна Поиск решения з’являться рядки з заданими обмеженнями (мал. 4.14).

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

Після завершення обчислень з'явиться вікно (мал. 4.16), в якому програма інформує про завершення обчислень та пропонує зберегти розв’язок або повернутися до вхідних даних. Разом з появою цього вікна, у відповідних комірках робочого аркуша з’являються результати обчислень: значення невідомих (комірки B9:C9), значення при цьому розв’язку функцій, на які накладалися умови та значення цільової функції при даному розв’язку. Якщо потрібно зберегти результати роботи процедури у вигляді звіту, то можна вибрати тип звіту у відповідному полі діалогового вікна (мал. 4.16).

Мал. 4.16. Фрагмент робочого аркуша з розв’язком задачі

При використанні процедури Поиск решения користувач може задати відповідні параметри розв’язування задачі, відкривши діалогове вікно Параметры поиска решения (мал. 4.17) за допомогою кнопки Параметры діалогового вікна Поиск решения.

Мал. 4.17. Діалогове вікно Параметры поиска решения

Сукупність встановлених параметрів та обмежень можна зберегти разом з робочим аркушем у ролі моделі (кнопка Сохранить модель діалогового вікна мал. 4.17). Завантажити цю модель згодом можна буде за допомогою кнопки Загрузить модель.

8. Диспетчер сценаріїв

При моделюванні складних задач, які містять до 32 параметрів, можна використовувати диспетчер сценаріїв, який дозволяє створювати стільки варіантів розв’язку, скільки є необхідно для детального аналізу моделі ”що–якщо”. При роботі з диспетчером можна:

§ створювати декілька сценаріїв для однієї моделі ”що–якщо”, кожен з яких може мати свою власну множину змінних;

§ простежувати варіанти сценаріїв, оскільки ці функція зберігає дату зміни сценарію;

§ створювати звіт у вигляді зведеної таблиці та порівнювати між собою множини змінних, які утворилися при виконанні кожного зі сценаріїв розв’язування задачі.

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

Мал. 4.18. Таблиця для ілюстрації задачі прогнозного росту

Мал. 4.19. Формульний вигляд таблиці прогнозного росту

2. Створення сценаріїв.

Створити 3 сценарії, змінюючи значення в комірках Е3:Е7. Для створення сценарю потрібно в пункті меню Сервис вибрати команду Сценарии. У діалоговому вікні Диспечер сценария, натискаючи кнопку Добавить потрібно заповнити наступні поля:

Мал.4.20. Діалогове вікно Добавление сценария

¨ У полі Названия сценария введіть довільну назву Вашого сценарія (наприклад, сценарій 1, сценарій 2, і т.д.);

¨ У поля Изменить ячейки потрібно ввести діапазон комірок, значення яких підлягають зміні, і натискається кнопка ОК.

¨ Відкривається діологове вікно Значение ячеек сценария із набором значень комірок для сценарію. Це значення, які відповідають теперішнім показникам, які треба запам’ятати без змін, тому просто натискається ОК.

Мал. 4.21. Вікно Значения ячеек сценария

Так само створюється новий сценарій з новими значеннями, які вносяться на останньому кроці, при цьому можна просто змінити старі значення на нові, а можна занести формулу для зміни поточних значень:

1 спосіб: значення змінюються користувачем

2 спосіб: значення заносяться у вигляді формул

¨ Після натискання ОК уведені значення і формули запам’ятовуються і вікно диспетчера сценаріїв міститиме вже декілька сценаріїв.

Перший із старими значеннями, другий із новими значеннями, зміненими самим користувачем, а третій – із значеннями, які вираховувались у результаті введення формул.

Після внесення всіх змін дане вікно закривається.

Кнопка Вывести використовується для виклику і перегляду вибраного сценарію, при цьому у таблиці комірки Е3:Е7 набувають тих значень, які були введені у відповідному сценарії. За даними сценарію на окремому робочому аркуші можна створити звіт або зведену таблицю.

3. Створення структурного звіту по створених сценаріях.

Для того, щоб створити звіт по сценаріях потрібно в пункті меню Сервис вибрати команду Сценарии. В даному вікні натиснути кнопку Отчеты, встановивши перемикач на опції Структура в діалоговому вікні Отчеты по сценарию. В полі Ячейки результата ввести адреси комірок, які будуть виводитись у структурний звіт. Натиснути кнопку ОК. Вікно створеного звіту представлене на мал.4.21.

Мал. 4.21. Вікно структурного звіту

Створення звіту по сценаріях у вигляді зведеної таблиці.

Для створення звіту у вигляді зведеної таблиці, потрібно послідовно активізувати команду Сценарии з пункту меню Сервис. Натиснути кнопку Отчеты діалогового вікна Диспетчер сценариев, встановивши перемикач на опції Сводная таблица діалогового вікна Отчеты по сценарию. Натиснути кнопку ОК. Звіт у вигляді зведеної таблиці представлений на мал. 4.22.