Роздрукувати сторінку

Главная \ Методичні вказівки \ Методичні вказівки \ 1387 Індивідуальна робота 1 на тему Використання засобів оптимізації у середовищі MS Excel

Індивідуальна робота 1 на тему Використання засобів оптимізації у середовищі MS Excel

« Назад

Індивідуальна робота №1 Тема: Використання засобів оптимізації у середовищі MS Excel

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

Теоретична довідка

ПРОГРАМА "ПОДБОР ПАРАМЕТРА"

Лінійне або нелінійне рівняння можна розв'язати також способом добирання параметра, щоб деяка, залежна від нього функція отримала певне значення. Цей метод має важливе значення для розв'язування задач зворотнього аналізу, наприклад такої: скільки треба купити одиниць деякого товару (це є параметр), щоб вкластися в заплановану суму (це функція).

Інша задача: яку встановити тарифну ставку (параметр) дванадцяти працівникам, щоб вкластися в запланований бюджет (функція) 1000 грн? Нехай А1 — адреса клітинки, що містить значення тарифної ставки, f(А1) – запланований бюджет, В1 – адреса комірки, в яку записано функцію f(А1), тоді

f(А1) = В1 — задане рівняння,

Бюджет(А1)=12*А1=1000.

Метод підбору параметра полягає в тому, що програма для будь-якого рівняння обчислює значення параметра А1. Алгоритм дій користувача такий. Спочатку потрібно в комірку В1 записати формулу = f(А1), далі виділити цю комірку і на вкладці Данные через кнопку Анализ «что если» запустити програму Подбор параметра. Отримаємо діалогове вікно, у якому треба заповнити три поля.

Далі слід натиснути ОК і у комірці А1 отримаємо шуканий результат.

ПРОГРАМА "ПОИСК РЕШЕНИЯ"

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

Програму «Поиск решения» можна запустити на вкладці Данные в групі Анализ.

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

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

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

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

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 розв’язати кубічне рівняння, використовуючи Подбор параметра, за варіантом.

6. На робочому аркуші Лист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 і є розв’язком задачі.

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

8. Зберегти електронну таблицю під назвою Прізвище_Індивідуальна 1 на сервері у власній папці.

9. Оформити звіт для індивідуальної роботи і здати роботу викладачу.

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