Презентація на тему «MS Exsel»

content: MS ExselРозв'язування задачіз використаннямтехнології “Що, якщо”

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

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

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

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

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

content: www.themegallery.comВикористання засобу Підбір параметраЗадача2. Дані про видачу банком кредитів клієнтам міститься в таблиці. Визначити, яким має бути відсоток ставки за надані кредити, щоб банк зміг одержати прибуток розміром 20 000 тис. грн.Банківський кредитКлієнтВидана сума тис. грн.Сума повернення тис. грн.Клієнт 122 300Клієнт 236 500Клієнт 318 450Клієнт 460 000Клієнт 543 280РазомВідсотокПрибуток

content: www.themegallery.comВикористання засобу Підбір параметра

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

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

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

content: www.themegallery.comЗадача 1. Підприємство виробляє фарбу для внутрішніх і зовнішніх робіт з сировини двох типів: С1 і С2:Використання засобу Пошук розв'язкуВитрати сировини (в тонах) на 1 т фарбиДобовий запас сировини, тДля зовнішніх робітДля внутрішніх робітСировина С16424Сировина С2126Прибуток на 1 т фарби тис. грн.54Відділом маркетингу обмежене щоденне виробництво фарби для внутрішніх робіт до 2 т через відсутність належного попиту, а також виявлено, що щоденне виробництво фарбі для зовнішніх робіт не повинно перевищувати аналогічний показник виробництва фарби для внутрішніх робіт більше ніж на 1 т. Знайти оптимальне співвідношення між видами продукції, які виробляє підприємство з метою одержання максимально щоденного прибутку

content: www.themegallery.comВикористання засобу Пошук розв'язку1. Створюємо ЕТ

content: www.themegallery.comВикористання засобу Пошук розв'язку2. Обчислюємо значення в чарунках Е3, Е4

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

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

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

content: www.themegallery.comВикористання засобу Пошук розв'язку6. Виконуємо пошук

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

content: www.themegallery.comВикористання засобу Пошук розв'язкуВид інградієнтаСтрава АСтрава БСтрава ВІнгредієнт 1105010Інгредієнт 22040Інгредієнт 3201030Інгредієнт 43015Задача 2. У ресторані готують фірмові страви трьох видів , використовуючи інгредієнти чотирьох видів. Норму використання інгредієнтів (у грамах) для приготування однієї порції наведено у таблиці.Вартість однієї порції страви А стоїть 120 грн., страви Б – 100 грн., а страви В – 80 грн. Що дня до ресторану доставляють 5 кг інгредієнта 1, по 4 кг інгредієнта 2 і 3 та 3 кг інгредієнта 4. Яку кількість порцій кожної з фірмових страв слід приготувати, щоб загальна вартість страв була максимальною.

content: 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

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