Практична робота 12 на тему Використання засобів оптимізації у середовищі MS Excel

« Назад

Практична робота №12 Тема: Використання засобів оптимізації у середовищі MS Excel

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

Завдання 1: Розвя’зати задачу розрахунку виплат за вкладами, використовуючи програму Подбор параметра.

Технологія виконання роботи

1. Завантажити табличний процесор Excel.

2. На робочому аркуші Лист1 побудувати таблицю для розрахунку виплат за вкладами.

	A	B	C
11		Розрахунок виплат за вкладами
12		Розмір вкладу, грн., V	4000,00
13		Термін вкладу, років, T	5,00
14		Відсоткова ставка, %, P	12,00
15		Коефіцієнт нарощування, k
16		Сума до виплати, грн., S

Використовуючи функцію Подбор параметра та дані таблиці, знайти, при якому значенні терміну вкладу сума виплат становитиме 12 000 грн. Для цього слід виконати наступні дії:

– виділити комірку С16 і записати формулу ;

– запустити програму Подбор параметра;

– в діалоговому вікні Подбор параметра заповнити текстові поля;

– в діалоговому вікні Подбор параметра клацнути на кнопці ОК;

– проаналізувати отриманий результат. Яке значення терміну вкладу з’явилось в комірці С13?

3. Скопіювати таблицю для розрахунку виплат за вкладами нижче.

	A	B	C
18		Розрахунок виплат за вкладами
19		Розмір вкладу, грн., V	4000,00
20		Термін вкладу, років, T	5,00
21		Відсоткова ставка, %, P	12,00
22		Коефіцієнт нарощування, k
23		Сума до виплати, грн., S

Використовуючи Подбор параметра та дані наведеної таблиці, самостійно знайти, при якому значенні відсоткової ставки сума виплат становитиме 12 000 грн.

Проаналізувати отриманий результат. Яке значення відсоткової ставки з’явилось в комірці С21?

4. Перейменуйте Лист1 на Підбір параметра_Прізвище.

5. На робочому аркуші Лист2 розв’язати кубічне рівняння, використовуючи Подбор параметра, за варіантом.

Варіанти:

Завдання 2: Розв’язати задачу, використовуючи програму Поиск решения.

1. На робочому аркуші Лист3 побудувати таблицю «Відомості про випуск продукції за місяць».

	D		E		F		G
6	ВІДОМОСТІ ПРО ВИПУСК ПРОДУКЦІЇ ЗА МІСЯЦЬ
7	Назва продукції	Витрати на одиницю продукції, грн.		Кількість одиниць		Витрати на партію продукції, грн.
8	Продукція 1	100		25		?
9	Продукція 2	80		35		?
10	Продукція 3	150		15		?
11	Продукція 4	230		15		?
12	Продукція 5	96		32		?
13	Продукція 6	130		15		?
14	Всього			?		?

Використовуючи програму Поиск решения, знайти оптимальний план випуску продукції (скільки одиниць товару кожного виду слід випускати), при якому витрати на виготовлення будуть мінімальними. Кількість продукції кожного виду змінюється в межах від 15 до 35 одиниць, а загальна кількість одиниць складає 100.

Для розв’язання задачі слід виконати наступні дії:

– виконати необхідні обчислення у стовпці Витрати на партію товару;

– у рядку Всього обчислити сумарну кількість одиниць продукції та сумарні витрати на партію продукції;

– виділити комірку, в якій обчислено сумарні витрати на партію продукції; ця комірка є цільовою;

– запустити програму Поиск решения;

– в діалоговому вікні Параметры поиска решений заповнити текстові поля;

– натиснути кнопку Добавить для внесення обмежень по загальній кількості продукції;

– натиснути кнопку Добавить для внесення обмеження: кількість одиниць продукції кожного виду не перевищує 35 одиниць;

– натиснути кнопку Добавить для внесення обмеження: кількість одиниць продукції кожного виду не менше 15 одиниць;

– натиснути кнопку Добавить для внесення обмеження: кількість одиниць продукції кожного виду - ціле число;

– натиснути кнопку ОК, результатом буде діалогове вікно;

– натиснути кнопку Найти решение, натиснути ОК;

– проаналізувати результат у комірках F8:F13 та значення G14. Значення величин у комірках F8:F13 та G14 і є розв’язком задачі.

2. Перейменувати Лист3 на Поиск решения_Прізвище.

3. Зберегти електронну таблицю під назвою Прізвище_Практична12 на сервері у власній папці.

4. Оформити звіт і здати роботу викладачу.

Контрольні питання

1) Яке призначення має функція Подбор параметра?

2) Які фінансово-економічні задачі можна розв’язувати за допомогою функції Подбор параметра?

3) Які задачі можна розв’язувати з використанням програми Поиск решения?

4) Яку комірку називають цільовою?

5) Як записати обмеження на значення у певних комірках в програмі Поиск решения?

6) Як задати точність розв’язку?

7) Наведіть приклади задач, які потребують використання програми Поиск решения.

8) Що таке Диспетчер сценариев?

З повагою ІЦ “KURSOVIKS”!