Презентація "MS Exsel"

Попередній слайд
Наступний слайд


Завантажити презентацію "MS Exsel"
Слайд #1
MS Exsel
Розв’язування задач
із використанням
технології “Що, якщо”


Слайд #2
www.themegallery.com
Використання засобу Підбір параметра
У багатьох задачах певний результат є відомим, а от значення
параметрів, за яких цей результат досягається, - ні.
Приклад задачі
За якого обсягу випуску певної продукції фірма отримає прибуток у 100 000 гривень.
У математиці клас таких задач є найширшим. До нього відносяться задачі на розв’язання алгебраїчних рівнянь та нерівностей, пошук екстремумів функцій тощо.


Слайд #3
www.themegallery.com
Використання засобу Підбір параметра
У подібних задачах використовується поняття цільової функії.
Вона повинна досягти певного значення або оптимізуватися.
Цільова функція залежить від параметрів.
Задача знаходження певного значення цільової функції полягає у підборі таких значень параметрів, за яких цільова функція досягне необхідних значень
У MS Exsel є спеціальний засіб, який автоматично підбирає потрібні значення параметрів, знаходячи певне значення цільової функції. Він називається Підбір параметра.


Слайд #4
www.themegallery.com
Використання засобу Підбір параметра
Задача1. Знаходження коренів алгебраїчного рівняння
x3-2x2-5x-6=0
Спосіб виконання:
Визначимо наближені значення коренів рівняння
А) Створимо таблицю значень на відрізку;
Б) Побудуємо графік цільової функції;


Слайд #5
www.themegallery.com
Використання засобу Підбір параметра
2. Клітинки В43:В45 будемо вважати цільовими и скопіюємо в них формулу;
3. У чарунки А43:А45 – приблизне значення коренів;


Слайд #6
www.themegallery.com
Використання засобу Підбір параметра
4. Викликати засіб Підбір параметру (Сервіс > Підбір параметру)
Заповнення вікна форми Підбір параметра
Результат обчислень


Слайд #7
www.themegallery.com
Використання засобу Підбір параметра
Задача2. Дані про видачу банком кредитів клієнтам міститься в таблиці. Визначити, яким має бути відсоток ставки за надані кредити, щоб банк зміг одержати прибуток розміром 20 000 тис. грн.
Банківський кредит
Клієнт
Видана сума тис. грн.
Сума повернення тис. грн.
Клієнт 1
22 300
Клієнт 2
36 500
Клієнт 3
18 450
Клієнт 4
60 000
Клієнт 5
43 280
Разом
Відсоток
Прибуток


Слайд #8
www.themegallery.com
Використання засобу Підбір параметра


Слайд #9
www.themegallery.com
Використання засобу Підбір параметра
Задача 3. На деякому підприємстві випускається 1000 одиниць товару. Собівартість одиниці товару становить 10 грн., крім того фірма несе щомісячні витрати у розмірі 2000 грн., не пов’язані безпосередньо з виробництвом, та сплачує податок у розмірі 15% від доходу. Потрібно визначити, якою має бути ціна одиниці продукції, щоб підприємство отримувало щомісячний прибуток у розмірі 5000 грн. (припускається, що вся продукція продається).


Слайд #10
www.themegallery.com
Використання засобу Пошук розв’язку
У багатьо задачах вимагається не просто знайти який-небудь розв’язок, а підібрати серед усіх розв’язків найкращій. Йдеться про задачі підбору збалансованого харчування, транспортні задачи та багато інших – їх ще називають задачами оптимізації.
Математична модель оптимізаційної задачі
Перш ніж розв’язувати задачу оптимізації її треба описати
у математичному вигляді, тобто побудувати її математичні модель.
Модель оптимізаційної задачі складається з елементів:
змінні – невідомі величини, значення яких необхідно знайти в
результаті розв’язання задачи;
цільова функція – величина, яка залежить від змінних і значення
якої потрібно максимізувати чи мінімізувати;
критерії – вимоги мінімізації чи максимізації цільової функції;
обмеження – умови, яким мають задовольняти змінні.


Слайд #11
www.themegallery.com
Використання засобу Пошук розв’язку
Алгоритм розв’язання задачі оптимізації
Введення позначень(яка змінна, яку величину позначає);
Створення цільової функції та критерію (визначити яка величина
буде максимізуватися або мінімізуватися, та записати формулу
залежності цієї величини від змінних;
3. Складання системи обмежень(нерівності або рівняння, яким мають задовольняти значення змінних);
4. Розв’язання задачі


Слайд #12
www.themegallery.com
Задача 1. Підприємство виробляє фарбу для внутрішніх і зовнішніх робіт з сировини двох типів: С1 і С2:
Використання засобу Пошук розв’язку
Витрати сировини (в тонах) на 1 т фарби
Добовий запас сировини, т
Для зовнішніх робіт
Для внутрішніх робіт
Сировина С1
6
4
24
Сировина С2
1
2
6
Прибуток на 1 т фарби тис. грн.
5
4
Відділом маркетингу обмежене щоденне виробництво фарби для внутрішніх робіт до 2 т через відсутність належного попиту, а також виявлено, що щоденне виробництво фарбі для зовнішніх робіт не повинно перевищувати аналогічний показник виробництва фарби для внутрішніх робіт більше ніж на 1 т. Знайти оптимальне співвідношення між видами продукції, які виробляє підприємство з метою одержання максимально щоденного прибутку


Слайд #13
www.themegallery.com
Використання засобу Пошук розв’язку
1. Створюємо ЕТ


Слайд #14
www.themegallery.com
Використання засобу Пошук розв’язку
2. Обчислюємо значення в чарунках Е3, Е4


Слайд #15
www.themegallery.com
Використання засобу Пошук розв’язку
3. Обчислюємо загальний прибуток (цільова функція)


Слайд #16
www.themegallery.com
Використання засобу Пошук розв’язку
4. Активуємо чарунку В7 і викликаємо засіб
Пошук роз’язку (Сервіс > Поиск решения)


Слайд #17
www.themegallery.com
Використання засобу Пошук розв’язку
5. Заповнюємо діалогове вікно в залежності від
умов задачі


Слайд #18
www.themegallery.com
Використання засобу Пошук розв’язку
6. Виконуємо пошук


Слайд #19
www.themegallery.com
Використання засобу Пошук розв’язку
7. За необхідністю можна проаналізувати результати


Слайд #20
www.themegallery.com
Використання засобу Пошук розв’язку
Вид інградієнта
Страва А
Страва Б
Страва В
Інгредієнт 1
10
50
10
Інгредієнт 2
20
0
40
Інгредієнт 3
20
10
30
Інгредієнт 4
30
15
0
Задача 2. У ресторані готують фірмові страви трьох видів , використовуючи інгредієнти чотирьох видів. Норму використання інгредієнтів (у грамах) для приготування однієї порції наведено у таблиці.
Вартість однієї порції страви А стоїть 120 грн., страви Б – 100 грн., а страви В – 80 грн. Що дня до ресторану доставляють 5 кг інгредієнта 1, по 4 кг інгредієнта 2 і 3 та 3 кг інгредієнта 4. Яку кількість порцій кожної з фірмових страв слід приготувати, щоб загальна вартість страв була максимальною.


Слайд #21
www.themegallery.com
Використання засобу Пошук розв’язку
Математична модель
Введемо позначення х1 – кількість порцій страви А, х2 – кількість порцій страви – Б, х3 – кількість порцій страви В.
Загальна вартість страв визначається за формулою 120х1+100х2+80х3 – формула цільової функції.
З таблиці випливають умови:
20х1+50х2+10х3<=5000 (Інгредієнт 1)
20х1+40х2<=4000 (Інгредієнт 2)
20х1+10х2+30х3<=4000 (Інгредієнт 3)
30х1+15х2<=3000 (Інгредієнт 4)
х1, х2, х3 – цілі числа
x1>=0, x2>=0, x3>=0


Слайд #22
www.themegallery.com
Використання засобу Пошук розв’язку
Задача 3. У зоопарку використовують два види кормів: 100 г першого корму містить 2 г білків, 6 г жирів, 4 г вуглеводів і коштує 5 грн; 100 г другого корму містить 3 г білків, 2 г жирів, 9 г вуглеводів і коштує 4 грн. Потрібно скласти раціон харчування тварини в зоопарку за умови, що вона має щодня отримувати не менше 60 г білків, 80 г жирів і 150 г вуглеводів, а вартість добової норми їжі повинна бути мінімальною.