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

MS Exsel Розв’язування задач із використанням технології “Що, якщо” www.themegallery.com LOGO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

www.themegallery.com Використання засобу Пошук розв’язку Математична модель Введемо позначення х1 – кількість порцій страви А, х2 – кількість порцій страви – Б, х3 – кількість порцій страви В. Загальна вартість страв визначається за формулою 120х1+100х2+80х3 – формула цільової функції. З таблиці випливають умови: 20х1+50х2+10х3

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