ЗМІСТ
Введення
1 Введення в Microsoft Excel
2 Основні функції в Microsoft Excel
3 Формули в Microsoft Excel
4 Загальні відомості про алгоритми
5 Метод половинного ділення
6 Рішення завдання
7 Поняття оптимізаційних завдань та оптимізаційних моделей
8 Рішення завдання
Висновок
Додаток А
Додаток Б
Додаток В
Додаток Г
Додаток Д
ВСТУП
Завдання, що вимагають оптимізації, зустрічаються в самих різних сферах людської діяльності, так як на їх вирішенні базується прийняття рішень. Завжди люди, приступаючи до реалізації своїх дій, роздумували над їх можливими наслідками і приймали рішення, вибираючи тим чи іншим чином способи здійснення конкретних заходів. Кожне розумне дія є в певному сенсі і оптимальним, бо воно, як правило, вибирається після порівняння з іншими варіантами.
У зв'язку зі складністю прикладних оптимізаційних завдань прийняття рішень у них все в меншій мірі стало грунтуватися на «здоровому сенсі», інтуїції та досвіді людини. Необхідний науковий підхід, що базується на математичному описі розв'язуваних проблем.
Перші завдання з вивчення екстремальних властивостей геометричних фігур (коло, квадрат і т.д.) були вирішені ще в стародавні століття. Потужним поштовхом до розвитку методів оптимізації послужило створення диференціального й інтегрального числень. Протягом короткого проміжку часу були створені нові розділи теорії (лінійне програмування, теорія оптимального управління і т. д.), які призвели до розробки ряду ефективних чисельних методів розв'язання різноманітних екстремальних задач.
В даний час для вирішення складних статистичних, комерційних, наукових та інженерних завдань використовують комп'ютер, який дозволяє вирішувати завдання з багатьма змінними. Що входить до найбільш широко поширений програмний пакет Microsoft Office табличний процесор Excel своєму розпорядженні засоби для вирішення широкого кола завдань оптимізації:
1. Асортимент продукції. Максимізація випуску товарів при обмеженнях на сировину для виробництва цих товарів.
2. Штатний розклад. Складання штатного розкладу для досягнення найкращих результатів при найменших витратах.
3. Планування перевезень. Мінімізація витрат на транспортування товарів.
4. Складання суміші. Досягнення заданої якості суміші при найменших витратах.
1 ВСТУП У Microsoft Excel
Будь-який вид діяльності потребує систематизації даних, що зберігаються. З розширенням переліку продуктів, послуг та клієнтів будь-якому бізнесу необхідна комплексна система для зберігання великого обсягу фінансової та іншої документації.
Microsoft Excel - це програма, призначена для організації даних у таблиці для документування, зіставлення та графічного представлення інформації. Наприклад, можна використовувати Excel для підсумовування, обчислення середнього або максимального числа продажів за день; створення графіка, що показує певний відсоток продажів, порівняння загального обсягу продажів за день з тим же показником інших днів тижня. Excel звільняє від проведення цих обчислень вручну.
При запуску Excel з'являється новий документ. З цього моменту можна вводити інформацію, змінювати оформлення даних, обробляти дані або шукати інформацію у файлах довідки Excel
Головною складовою частиною документа Microsoft Excel є поле, яке містить певну інформацію. У Excel це поле називається осередком. Кожна клітинка знаходиться на перетині рядка (горизонтальної послідовності осередків) і стовпців (вертикальної послідовності осередків); рядки позначені числами, а стовпці - літерами. Номер рядка і літера стовпця, що позначають певну клітинку, називається посиланням на клітинку.
Робочий лист складається з набору рядків і стовпців і представляє сторінку в документі Excel. Робочої книгою називається один або кілька робочих аркушів. Створюючи документ Excel, створюється робоча книга з трьома робочими листами.
2 ОСНОВНІ ФУНКЦІЇ У Microsoft Excel
функції - це спеціально створені формули для обробки даних. Програма Excel має сотні вбудованих функцій, які призначені для проведення найрізноманітніших обчислень. Багато з цих функцій нам і ніколи і не знадобляться. Тут є як досить прості функції, наприклад, тригонометричні, так і досить складні, наприклад, функції для визначення стандартного відхилення або для проведення статистичного аналізу.
Працюючи в Excel, майже завжди можна знайти підходящу функцію, яка призначена для вирішення найрізноманітніших обчислювальних завдань. Ці функції розділені на наступні категорії:
- Фінансові;
- Дата і час;
- Математичні;
- Статистичні;
- Посилання і масиви;
- Робота з базою даних;
- Текстові;
- Логічні;
- Інженерні.
Кожна функція має один або декілька аргументів. Аргументом називаються значення, з якими оперує функція. У залежності від формули аргументом можуть бути посилання на клітинку, ім'я клітинки, діапазон комірок, число, логічне значення або текст. У деяких функцій немає аргументу (наприклад, функція ПІ).
Логічні функції це функції типу якщо, і, не, істина, брехня. Ці функції використовуються для перевірки умов і для визначення, чи є те чи інше твердження істинним або хибним. Для оцінки логічних умов використовуються функції якщо, і, або, не.
Логічні умови, формули або функції можуть повертати значення істина або брехня. Логічне умова може бути або правдою, або брехнею.
Функція АБО повертає TRUE, якщо хоча б один з аргументів має значення ІСТИНА; повертає FALSE, якщо всі аргументи мають значення FALSE.
Функція НЕ міняє на протилежне значення свого аргументу. Якщо аргумент має значення ІСТИНА, функція НЕ повертає значення FALSE і навпаки.
За допомогою функції ЯКЩО можна оцінити до 30 логічних умов, і повернути різні числові або текстові значення, в залежності від того, чи будуть логічні умови істинними або помилковими.
У Microsoft Excel є тільки одна категорія математичних функцій, але для зручності розгляду її можна розбити на три типи:
1) арифметичні функції;
2) алгебраїчні функції;
3) тригонометричні функції.
Арифметичні функції використовують такі математичні дії, як додавання, віднімання, множення і ділення.
Алгебраїчні функції дозволяють обчислювати логарифми, експоненти, квадратні корені та інші.
Тригонометричні функції дозволяють обчислити синуси, косинуси, тангенси і так далі.
3 ФОРМУЛИ У MICROSOFT EXCEL
Формули являють собою вирази, за якими виконуються обчислення на сторінці. Формула починається зі знака рівності (=).
Формула також може включати наступні елементи:
- Функції;
- Посилання;
- Оператори (знак або символ, що задає тип обчислення у формулі. Існують математичні, логічні оператори, оператори порівняння та посилань);
- Константи (постійне (не обчислюване) значення).
Посилання вказує на клітинку або діапазон клітинок аркуша та передає в Microsoft Excel відомості про розташування значень або даних, які потрібно використовувати у формулі. За допомогою посилань можна використовувати в одній формулі дані, що знаходяться в різних частинах аркуша, а також використовувати в декількох формулах значення однієї клітинки. Крім того, можна задавати посилання на клітинки інших аркушів тієї ж книги та на інші книги. Посилання на клітинки інших книг називаються зв'язками.
Існують відносні, абсолютні і змішані посилання.
Відносне посилання у формулі, наприклад A1, заснована на відносній позиції клітинки, яка містить формулу, і клітинку, на яку вказує посилання. При зміні позиції комірки, що містить формулу, змінюється й посилання.
Абсолютне посилання клітинки у формулі, наприклад $ A $ 1, завжди посилається на клітинку, розташовану в певному місці. При зміні позиції комірки, що містить формулу, абсолютне посилання не змінюється.
Змішана посилання містить або абсолютний стовпець і відносну рядок, або абсолютну рядок і відносний стовпець. Абсолютне посилання стовпців набуває вигляду $ A1, $ B1 і т. д. Абсолютне посилання рядки набуває вигляду A $ 1, B $ 1 і т. д.
4 Загальні відомості про алгоритми.
Алгоритм - припис послідовності дій, спрямованих на вирішення поставленого завдання. У Exel алгоритм записується у вигляді послідовності операторів, які включають значення, посилання і формули.
Алгоритм має властивості:
1) однозначності - виключає довільне тлумачення і призводить до одного й того ж результату при однакових вихідних даних;
2) масовість - застосовується до інших подібних завданням;
3) результативність - покрокове виконання завдання призводить до кінцевого результату.
Виділяється декілька типів алгоритмічних структур:
1. Лінійна структура.
2. Розгалужуються структура:
а) з однією гілкою;
б) з двома гілками;
в) з безліччю гілок.
3. Циклічна структура.
Прийнято виділяти дві циклічні структури з логічним умовою до і після тіла циклу.
Стосовно до електронних таблиць це не зовсім точно і справедливо, так як важливий і спосіб організації виходу з циклу, а це:
- Нескінченний цикл;
- Вкладені циклічні структури;
- Цикл з заданих наперед кількістю повторень;
-Розрахунково-динамічний цикл (новий, характерний для електронної таблиці), кількість повторень якого визначається в ході перерахунку таблиці, а параметри задаються в результаті посилання на клітинку, де міститися розрахунково-змінні дані.
- Ітераційний цикл (кількість повторень заздалегідь невідомо й залежить від здійснення або досягнення заданої точності або послідовності наближень до шуканого значенням, де обчислення подальшого члена проводиться через попередній член);
5 Метод половинного ділення
Цей метод відрізняється від вище розглянутих методів тим, що для нього не вимагається виконання умови, що перша і друга похідна зберігають знак на інтервалі [a, b]. Метод половинного поділу сходиться для будь-яких безперервних функцій f (x) в тому числі недиференційованої.
Розділимо відрізок [a, b] навпіл точкою
Якщо 
(Що практично найбільш ймовірно), то можливі два випадки: або f (x) змінює знак на відрізку [a, c] (Мал. 1), або на відрізку [c, b] (Мал. 2)
Вибираючи в кожному випадку той відрізок, на якому функція змінює знак, і продовжуючи процес половинного поділу далі, можна дійти до як завгодно малого відрізку, що містить корінь рівняння.
6 РІШЕННЯ ЗАДАЧІ
Дана наступна функція:
F (х) = 60 * sin (5.5 * x * pi/180) -69 * cos (2.7 * x * pi/180)-exp (x/192) -181 / x
де Х змінюється від 0 до 400. Знайти точки перетину функції з точкою А (А = 0).
Для знаходження точок перетину використовуємо метод половинного ділення. Для цього від даної функції віднімемо А (F (x)-А).
Побудуємо алгоритм (додаток А).
Для того, щоб знайти точки перетину функції з точкою А, побудуємо графік (додаток В) за даними наведеними у таблиці (додаток Г).
У графі Е2 введемо формулу для знаходження значень де відбувається зміна знаку = ЕСЛИ (В2 * В3 <= 0; "зміна знака"; "").
За отриманими даними знайдемо точки перетину даної функції з точкою А у точках, де відбувається зміна знака.
Наприклад, зміна знака відбувається при значенні Х = 15, тоді в клітинку G 2 введемо значення Х1 = 15, а в комірку G3 введемо формулу = ЕСЛИ (J2 * L2 <= 0; G2; I2). У осередок Н2-значення Х2 = 20, а в комірку Н3 введемо формулу = ЕСЛИ (J2 * L2 <= 0; I2; H2), це означає, що на цьому інтервалі про виходить перетин функції з координатною віссю, тобто з точкою А . Для знаходження середнього значення в клітинку I2 введемо формулу = (G2 + H2) / 2. У комірки J2, K2, L2 введемо формули заданої в умові функції, де Х, для кожної з заданих осередків, буде приймати значення Х1, Х2, Хср. відповідно.
Для того, щоб визначити на якій половині відбувається зміна знака в клітинку М2 введемо формулу
= ЕСЛИ (J2 * L2 <= 0; "зміна знака на 1-ій половині"; "Змінити знака на 2-ій половині").
У стовпці N наведено кількість кроків, за яке будить досягнута точність визначення значення (х) не нижче 0,001.
Для визначення похибки, в клітинку О2 введемо формулу = 0-L2. Таким чином з наведеної таблиці видно, що значення Х з точністю до 0,001 визначено за 14 кроків.
GHIJKLMNO
За отриманими даними за допомогою майстра діаграм побудуємо графік похибки.
Для визначення правильності рішення зробимо перевірку за допомогою підбору параметрів.
Для цього в комірку А107 введемо формулу заданої функції, а в комірку В107 введемо значення Х при якому відбувається зміна знака. Далі необхідно поставити курсор в комірку А107 і з меню сервіс вибрати підбір параметра. У вікні ввести необхідні дані, натиснути кнопку ОК.
Введення
1 Введення в Microsoft Excel
2 Основні функції в Microsoft Excel
3 Формули в Microsoft Excel
4 Загальні відомості про алгоритми
5 Метод половинного ділення
6 Рішення завдання
7 Поняття оптимізаційних завдань та оптимізаційних моделей
8 Рішення завдання
Висновок
Додаток А
Додаток Б
Додаток В
Додаток Г
Додаток Д
ВСТУП
Завдання, що вимагають оптимізації, зустрічаються в самих різних сферах людської діяльності, так як на їх вирішенні базується прийняття рішень. Завжди люди, приступаючи до реалізації своїх дій, роздумували над їх можливими наслідками і приймали рішення, вибираючи тим чи іншим чином способи здійснення конкретних заходів. Кожне розумне дія є в певному сенсі і оптимальним, бо воно, як правило, вибирається після порівняння з іншими варіантами.
У зв'язку зі складністю прикладних оптимізаційних завдань прийняття рішень у них все в меншій мірі стало грунтуватися на «здоровому сенсі», інтуїції та досвіді людини. Необхідний науковий підхід, що базується на математичному описі розв'язуваних проблем.
Перші завдання з вивчення екстремальних властивостей геометричних фігур (коло, квадрат і т.д.) були вирішені ще в стародавні століття. Потужним поштовхом до розвитку методів оптимізації послужило створення диференціального й інтегрального числень. Протягом короткого проміжку часу були створені нові розділи теорії (лінійне програмування, теорія оптимального управління і т. д.), які призвели до розробки ряду ефективних чисельних методів розв'язання різноманітних екстремальних задач.
В даний час для вирішення складних статистичних, комерційних, наукових та інженерних завдань використовують комп'ютер, який дозволяє вирішувати завдання з багатьма змінними. Що входить до найбільш широко поширений програмний пакет Microsoft Office табличний процесор Excel своєму розпорядженні засоби для вирішення широкого кола завдань оптимізації:
1. Асортимент продукції. Максимізація випуску товарів при обмеженнях на сировину для виробництва цих товарів.
2. Штатний розклад. Складання штатного розкладу для досягнення найкращих результатів при найменших витратах.
3. Планування перевезень. Мінімізація витрат на транспортування товарів.
4. Складання суміші. Досягнення заданої якості суміші при найменших витратах.
1 ВСТУП У Microsoft Excel
Будь-який вид діяльності потребує систематизації даних, що зберігаються. З розширенням переліку продуктів, послуг та клієнтів будь-якому бізнесу необхідна комплексна система для зберігання великого обсягу фінансової та іншої документації.
Microsoft Excel - це програма, призначена для організації даних у таблиці для документування, зіставлення та графічного представлення інформації. Наприклад, можна використовувати Excel для підсумовування, обчислення середнього або максимального числа продажів за день; створення графіка, що показує певний відсоток продажів, порівняння загального обсягу продажів за день з тим же показником інших днів тижня. Excel звільняє від проведення цих обчислень вручну.
При запуску Excel з'являється новий документ. З цього моменту можна вводити інформацію, змінювати оформлення даних, обробляти дані або шукати інформацію у файлах довідки Excel
Головною складовою частиною документа Microsoft Excel є поле, яке містить певну інформацію. У Excel це поле називається осередком. Кожна клітинка знаходиться на перетині рядка (горизонтальної послідовності осередків) і стовпців (вертикальної послідовності осередків); рядки позначені числами, а стовпці - літерами. Номер рядка і літера стовпця, що позначають певну клітинку, називається посиланням на клітинку.
Робочий лист складається з набору рядків і стовпців і представляє сторінку в документі Excel. Робочої книгою називається один або кілька робочих аркушів. Створюючи документ Excel, створюється робоча книга з трьома робочими листами.
2 ОСНОВНІ ФУНКЦІЇ У Microsoft Excel
функції - це спеціально створені формули для обробки даних. Програма Excel має сотні вбудованих функцій, які призначені для проведення найрізноманітніших обчислень. Багато з цих функцій нам і ніколи і не знадобляться. Тут є як досить прості функції, наприклад, тригонометричні, так і досить складні, наприклад, функції для визначення стандартного відхилення або для проведення статистичного аналізу.
Працюючи в Excel, майже завжди можна знайти підходящу функцію, яка призначена для вирішення найрізноманітніших обчислювальних завдань. Ці функції розділені на наступні категорії:
- Фінансові;
- Дата і час;
- Математичні;
- Статистичні;
- Посилання і масиви;
- Робота з базою даних;
- Текстові;
- Логічні;
- Інженерні.
Кожна функція має один або декілька аргументів. Аргументом називаються значення, з якими оперує функція. У залежності від формули аргументом можуть бути посилання на клітинку, ім'я клітинки, діапазон комірок, число, логічне значення або текст. У деяких функцій немає аргументу (наприклад, функція ПІ).
Логічні функції це функції типу якщо, і, не, істина, брехня. Ці функції використовуються для перевірки умов і для визначення, чи є те чи інше твердження істинним або хибним. Для оцінки логічних умов використовуються функції якщо, і, або, не.
Логічні умови, формули або функції можуть повертати значення істина або брехня. Логічне умова може бути або правдою, або брехнею.
Функція АБО повертає TRUE, якщо хоча б один з аргументів має значення ІСТИНА; повертає FALSE, якщо всі аргументи мають значення FALSE.
Функція НЕ міняє на протилежне значення свого аргументу. Якщо аргумент має значення ІСТИНА, функція НЕ повертає значення FALSE і навпаки.
За допомогою функції ЯКЩО можна оцінити до 30 логічних умов, і повернути різні числові або текстові значення, в залежності від того, чи будуть логічні умови істинними або помилковими.
У Microsoft Excel є тільки одна категорія математичних функцій, але для зручності розгляду її можна розбити на три типи:
1) арифметичні функції;
2) алгебраїчні функції;
3) тригонометричні функції.
Арифметичні функції використовують такі математичні дії, як додавання, віднімання, множення і ділення.
Алгебраїчні функції дозволяють обчислювати логарифми, експоненти, квадратні корені та інші.
Тригонометричні функції дозволяють обчислити синуси, косинуси, тангенси і так далі.
3 ФОРМУЛИ У MICROSOFT EXCEL
Формули являють собою вирази, за якими виконуються обчислення на сторінці. Формула починається зі знака рівності (=).
Формула також може включати наступні елементи:
- Функції;
- Посилання;
- Оператори (знак або символ, що задає тип обчислення у формулі. Існують математичні, логічні оператори, оператори порівняння та посилань);
- Константи (постійне (не обчислюване) значення).
Посилання вказує на клітинку або діапазон клітинок аркуша та передає в Microsoft Excel відомості про розташування значень або даних, які потрібно використовувати у формулі. За допомогою посилань можна використовувати в одній формулі дані, що знаходяться в різних частинах аркуша, а також використовувати в декількох формулах значення однієї клітинки. Крім того, можна задавати посилання на клітинки інших аркушів тієї ж книги та на інші книги. Посилання на клітинки інших книг називаються зв'язками.
Існують відносні, абсолютні і змішані посилання.
Відносне посилання у формулі, наприклад A1, заснована на відносній позиції клітинки, яка містить формулу, і клітинку, на яку вказує посилання. При зміні позиції комірки, що містить формулу, змінюється й посилання.
Абсолютне посилання клітинки у формулі, наприклад $ A $ 1, завжди посилається на клітинку, розташовану в певному місці. При зміні позиції комірки, що містить формулу, абсолютне посилання не змінюється.
Змішана посилання містить або абсолютний стовпець і відносну рядок, або абсолютну рядок і відносний стовпець. Абсолютне посилання стовпців набуває вигляду $ A1, $ B1 і т. д. Абсолютне посилання рядки набуває вигляду A $ 1, B $ 1 і т. д.
4 Загальні відомості про алгоритми.
Алгоритм - припис послідовності дій, спрямованих на вирішення поставленого завдання. У Exel алгоритм записується у вигляді послідовності операторів, які включають значення, посилання і формули.
Алгоритм має властивості:
1) однозначності - виключає довільне тлумачення і призводить до одного й того ж результату при однакових вихідних даних;
2) масовість - застосовується до інших подібних завданням;
3) результативність - покрокове виконання завдання призводить до кінцевого результату.
Виділяється декілька типів алгоритмічних структур:
1. Лінійна структура.
2. Розгалужуються структура:
а) з однією гілкою;
б) з двома гілками;
в) з безліччю гілок.
3. Циклічна структура.
Прийнято виділяти дві циклічні структури з логічним умовою до і після тіла циклу.
Стосовно до електронних таблиць це не зовсім точно і справедливо, так як важливий і спосіб організації виходу з циклу, а це:
- Нескінченний цикл;
- Вкладені циклічні структури;
- Цикл з заданих наперед кількістю повторень;
-Розрахунково-динамічний цикл (новий, характерний для електронної таблиці), кількість повторень якого визначається в ході перерахунку таблиці, а параметри задаються в результаті посилання на клітинку, де міститися розрахунково-змінні дані.
- Ітераційний цикл (кількість повторень заздалегідь невідомо й залежить від здійснення або досягнення заданої точності або послідовності наближень до шуканого значенням, де обчислення подальшого члена проводиться через попередній член);
5 Метод половинного ділення
Цей метод відрізняється від вище розглянутих методів тим, що для нього не вимагається виконання умови, що перша і друга похідна зберігають знак на інтервалі [a, b]. Метод половинного поділу сходиться для будь-яких безперервних функцій f (x) в тому числі недиференційованої.
Розділимо відрізок [a, b] навпіл точкою
Рис. 1 | Рис. 2 |
6 РІШЕННЯ ЗАДАЧІ
Дана наступна функція:
F (х) = 60 * sin (5.5 * x * pi/180) -69 * cos (2.7 * x * pi/180)-exp (x/192) -181 / x
де Х змінюється від 0 до 400. Знайти точки перетину функції з точкою А (А = 0).
Для знаходження точок перетину використовуємо метод половинного ділення. Для цього від даної функції віднімемо А (F (x)-А).
Побудуємо алгоритм (додаток А).
Для того, щоб знайти точки перетину функції з точкою А, побудуємо графік (додаток В) за даними наведеними у таблиці (додаток Г).
У графі Е2 введемо формулу для знаходження значень де відбувається зміна знаку = ЕСЛИ (В2 * В3 <= 0; "зміна знака"; "").
За отриманими даними знайдемо точки перетину даної функції з точкою А у точках, де відбувається зміна знака.
Наприклад, зміна знака відбувається при значенні Х = 15, тоді в клітинку G 2 введемо значення Х1 = 15, а в комірку G3 введемо формулу = ЕСЛИ (J2 * L2 <= 0; G2; I2). У осередок Н2-значення Х2 = 20, а в комірку Н3 введемо формулу = ЕСЛИ (J2 * L2 <= 0; I2; H2), це означає, що на цьому інтервалі про виходить перетин функції з координатною віссю, тобто з точкою А . Для знаходження середнього значення в клітинку I2 введемо формулу = (G2 + H2) / 2. У комірки J2, K2, L2 введемо формули заданої в умові функції, де Х, для кожної з заданих осередків, буде приймати значення Х1, Х2, Хср. відповідно.
Для того, щоб визначити на якій половині відбувається зміна знака в клітинку М2 введемо формулу
= ЕСЛИ (J2 * L2 <= 0; "зміна знака на 1-ій половині"; "Змінити знака на 2-ій половині").
У стовпці N наведено кількість кроків, за яке будить досягнута точність визначення значення (х) не нижче 0,001.
Для визначення похибки, в клітинку О2 введемо формулу = 0-L2. Таким чином з наведеної таблиці видно, що значення Х з точністю до 0,001 визначено за 14 кроків.
| X1 | X2 | Xср | F (x1) | F (x2) | F (xcр) | Кількість кроків | Погреш-ність | |
| 15,000 | 20,000 | 17,500 | -6,129 | 5,665 | 1,368 | зміна знака на 1-ій половині | 1 | -1,3678 |
| 15,000 | 17,500 | 16,250 | -6,129 | 1,368 | -1,969 | зміна знака на 2-ій половині | 2 | 1,9692 |
| 16,250 | 17,500 | 16,875 | -1,969 | 1,368 | -0,199 | зміна знака на 2-ій половині | 3 | 0,1991 |
| 16,875 | 17,500 | 17,188 | -0,199 | 1,368 | 0,610 | зміна знака на 1-ій половині | 4 | -0,6096 |
| 16,875 | 17,188 | 17,031 | -0,199 | 0,610 | 0,212 | зміна знака на 1-ій половині | 5 | -0,2116 |
| 16,875 | 17,031 | 16,953 | -0,199 | 0,212 | 0,008 | зміна знака на 1-ій половині | 6 | -0,0078 |
| 16,875 | 16,953 | 16,914 | -0,199 | 0,008 | -0,095 | зміна знака на 2-ій половині | 7 | 0,0952 |
| 16,914 | 16,953 | 16,934 | -0,095 | 0,008 | -0,044 | зміна знака на 2-ій половині | 8 | 0,0436 |
| 16,934 | 16,953 | 16,943 | -0,044 | 0,008 | -0,018 | зміна знака на 2-ій половині | 9 | 0,0179 |
| 16,943 | 16,953 | 16,948 | -0,018 | 0,008 | -0,005 | зміна знака на 2-ій половині | 10 | 0,0050 |
| 16,948 | 16,953 | 16,951 | -0,005 | 0,008 | 0,001 | зміна знака на 1-ій половині | 11 | -0,0014 |
| 16,948 | 16,951 | 16,949 | -0,005 | 0,001 | -0,002 | зміна знака на 2-ій половині | 12 | 0,0018 |
| 16,949 | 16,951 | 16,950 | -0,002 | 0,001 | 0,000 | зміна знака на 2-ій половині | 13 | 0,0002 |
| 16,950 | 16,951 | 16,950 | 0,000 | 0,001 | 0,001 | зміна знака на 1-ій половині | 14 | -0,0006 |
За отриманими даними за допомогою майстра діаграм побудуємо графік похибки.
Для визначення правильності рішення зробимо перевірку за допомогою підбору параметрів.
Для цього в комірку А107 введемо формулу заданої функції, а в комірку В107 введемо значення Х при якому відбувається зміна знака. Далі необхідно поставити курсор в комірку А107 і з меню сервіс вибрати підбір параметра. У вікні ввести необхідні дані, натиснути кнопку ОК.
| А | У | |
| 105 | Підбір параметрів | |
| 106 | F (X) | X |
| 107 | 0,0000 | 16,950 |
| 108 | 0,0005 | 28,806 |
| 109 | 0,0003 | 54,235 |
| 110 | 0,0000 | 98,448 |
| 111 | -0,0002 | 146,365 |
| 112 | 0,0000 | 158,039 |
| 113 | 0,0000 | 185,884 |
| 114 | 0,0001 | 230,163 |
| 115 | 0,0000 | 318,118 |
| 116 | 0,0009 | 361,607 |
У вікні Результат підбору параметра потрібно натиснути
кнопку ОК, після чого в осередках А107 і В107 з'явиться результат пошуку.
7 Поняття оптимізаційних завдань та оптимізаційних моделей
Економіко-математичні завдання, мета яких полягає в знаходженні найкращого, тобто оптимального з точки зору одного або кількох критеріїв варіанти використання наявних ресурсів, називаються оптимізаційними.
Оптимізаційні завдання вирішуються за допомогою оптимізаційних моделей методами математичного програмування.
Математичне програмування - це розділ прикладної математики, який вивчає задачі оптимізації та методи їх вирішення з орієнтацією на сучасні засоби комп'ютерної техніки.
Структура оптимізаційної моделі включає цільову функцію, області допустимих рішень і системи обмежень, що визначають цю область. Цільова функція в узагальненому вигляді також складається з трьох елементів:
· Керованих змінних;
· Некерованих змінних;
· Форми функції (виду залежності між ними).
Область допустимих рішень - це область, в межах якої здійснюється вибір рішень. В економічних задачах вона обмежена наявними ресурсами та умовами, які записуються у вигляді системи обмежень, що складається з рівнянь і нерівностей.
Головне завдання математичного програмування - це знаходження екстремуму функцій при виконанні зазначених обмежень. Якщо система обмежень несумісна, то область допустимих рішень є порожньою.
Сутність задач оптимізації: визначити значення змінних х 1, х 2 ,..., х n, які забезпечують екстремум цільової функції Е, з урахуванням обмежень, накладених на аргументи цієї функції. При цьому складність вирішення завдань залежить:
· Від виду функціональних залежностей, тобто від зв'язку функції Е з елементами рішення;
· Від розмірності задачі, тобто від кількості елементів рішення;
· Від виду та кількості обмежень, накладених на елементи рішення.
8 РІШЕННЯ ЗАДАЧІ
Кондитерська фабрика для виробництва трьох видів карамелі А, В і С використовує три види сировини: цукровий пісок, патоку та фруктове пюре. Норми витрати сировини на виробництво
| Найменування сировини | Норми витрати (кг / кг.) | |||
| A | B | C | ||
| Цукровий пісок | 0,6 | 0,5 | 0,6 | |
| Патока | 0,4 | 0,4 | 0,3 | |
| Фруктове пюре | 0,1 | 0,2 | 0,2 | |
Запаси сировини на складі відповідно рівні V1, V2 і V3 кг. Прибуток від реалізації
| Запаси сировини (кг) | Прибуток від реалізації (руб. / кг.) | ||||
| V1 | V2 | V3 | Pa | Pb | Pc |
| 800 | 600 | 120 | 1,08 | 1,12 | 1,28 |
Нехай х 1 - карамель виду А (кг.)
х 2 - карамель виду В (кг)
х 3 - карамель виду С (кг).
Тоді система обмежень і цільова функція запишуться таким чином:
х1 * 0,6 + х2 * 0,5 + х3 * 0,6 <= 800
х1 * 0,4 + х2 * 0,4 + х3 * 0,3 <= 600 обмеження на запаси сировини (цукровий
х1 * 0.1 + х2 * 0,2 + х3 * 0,2 <= 120 пісок, патока, фруктове пюре)
х1> = 0; x2> = 0; x3> = 0;
x1, x2, x3-цілі числа.
Для вирішення завдання в Excel запишемо її у вигляді, представленому на таблиці 1.
Таблиця 1 - Таблиця для вирішення задачі
| Кг. | обмеження | |||||
| х1 | 0 | 800 | > = | 0 | ||
| х2 | 0 | 600 | > = | 0 | ||
| х3 | 0 | 120 | > = | 0 | ||
| Mах прибуток: | 0 | |||||
Викликаю Пошук рішення з меню Сервіс.
Визначаю цільову комірку - $ D $ 8, встановлюю перемикач в максимальне значення. Вводжу діапазон змінних клітинок ($ B $ 11: $ В $ 13) і вношу обмеження. Перш за все, кількість продукту не може бути негативним ($ B $ 11: $ В $ 13> = 0), далі додаю обмеження на запаси сировини, яка повинна бути не більше нормативного (800> = G $ 5; 600> = G $ 6, 120> = G $ 7). Натискаю кнопку Виконати.
У вікні Результати пошуку рішення натискаю кнопку ОК і отримую вирішення завдання (додаток Д). З отриманих даних видно, що максимальний прибуток при виробництві карамелі склала 1296 рублів, причому така прибуток буде отриманий при виробництві 1200кг. Карамель види А.
Для перевірки правильності рішення введемо додаткові обмеження.
У першому варіанті я ввела обмеження на карамель виду В і отримала результат наведений в таблиці 1.
Таблиця 1
| Варіант 1 | Запаси сировини (кг) | Обмеження | ||||
| Х1 | 1170 | 800 | > = | 709,5 | ||
| Х2 | 15 | 600 | > = | 474 | ||
| Х3 | 0 | 120 | > = | 120 | ||
| Цільова функція | 1280,4 | Додаткове обмеження | Х2> = 15 | |||
У другому варіанті я ввела обмеження на карамель виду С і отримала такий результат
| Варіант 2 | Запаси сировини (кг) | Обмеження | ||
| Х1 | 1180 | 800 | > = | 714 |
| Х2 | 0 | 600 | > = | 475 |
| Х3 | 10 | 120 | > = | 120 |
| Цільова функція | 1287,2 | Додаткове обмеження | Х3> = 10 | |
За отриманими даними можна зробити висновок, що початкове рішення задачі було вірним.
ВИСНОВОК
У цій роботі були вирішені оптимізаційні задачі з використанням програмних засобів Microsoft Excel.У процесі вирішення першого завдання були побудовані: графік функції F (x) з урахуванням параметра А = 0 в заданому діапазоні значень змінної Х, які змінюються в діапазоні від 0 до 400. Були знайдені інтервали значень змінної Х в межах, яких функція приймає значення параметра А. При використанні методу половинного поділу були знайдені значення змінної Х, при яких функція приймає значення параметра А, відповідно із заданою точністю, що дорівнює 0,001. Перевірка правильності обчислень була здійснена за допомогою «Добору параметра».
Вирішення другого завдання здійснювалося за допомогою «Пошуку рішень» засобами Microsoft Excel. Було складено цільова функція та обмеження (відповідні умові задачі). У результаті був вибраний оптимальний варіант рішення. Для перевірки цього варіанту були внесені додаткові обмеження, які показали, що початково оптимальний варіант рішення був вірний.
ДОДАТОК А
Початок
Опис F (х), А, Δ
з = (a + b) / 2
F (a), F (b), F (c)
немає
a: = cb: = c
ні так
Виводи з, F (c), F (c)-A
Кінець
ДОДАТОК Б
ДОДАТОК В
ДОДАТОК Г
А В С DE
| X | F (x) | A | F (x)-A | Зміна знака | |||||
| 5 | -76,6150 | 0 | -76,6150 | ||||||
| 10 | -31,4838 | 0 | -31,4838 | ||||||
| 15 | -6,1292 | 0 | -6,1292 | зміна знака | |||||
| 20 | 5,6646 | 0 | 5,6646 | ||||||
| 25 | 5,7512 | 0 | 5,7512 | зміна знака | |||||
| 30 | -2,4673 | 0 | -2,4673 | ||||||
| 35 | -13,9441 | 0 | -13,9441 | ||||||
| 40 | -23,0017 | 0 | -23,0017 | ||||||
| 45 | -24,6667 | 0 | -24,6667 | ||||||
| 50 | -15,8988 | 0 | -15,8988 | зміна знака | |||||
| 55 | 3,6061 | 0 | 3,6061 | ||||||
| 60 | 31,2394 | 0 | 31,2394 | ||||||
| 65 | 61,9826 | 0 | 61,9826 | ||||||
| 70 | 89,4820 | 0 | 89,4820 | ||||||
| 75 | 107,4577 | 0 | 107,4577 | ||||||
| 80 | 111,1312 | 0 | 111,1312 | ||||||
| 85 | 98,3486 | 0 | 98,3486 | ||||||
| 90 | 70,1426 | 0 | 70,1426 | ||||||
| 95 | 30,6047 | 0 | 30,6047 | зміна знака | |||||
| 100 | -13,9123 | 0 | -13,9123 | ||||||
| 105 | -56,0851 | 0 | -56,0851 | ||||||
| 110 | -89,1227 | 0 | -89,1227 | ||||||
| 115 | -108,1489 | 0 | -108,1489 | ||||||
| 120 | -111,1603 | 0 | -111,1603 | ||||||
| 125 | -99,3512 | 0 | -99,3512 | ||||||
| 130 | -76,7403 | 0 | -76,7403 | ||||||
| 135 | -49,1871 | 0 | -49,1871 | ||||||
| 140 | -23,0264 | 0 | -23,0264 | ||||||
| 145 | -3,6307 | 0 | -3,6307 | зміна знака | |||||
| 150 | 5,7743 | 0 | 5,7743 | ||||||
| 155 | 4,7747 | 0 | 4,7747 | зміна знака | |||||
| 160 | -4,2332 | 0 | -4,2332 | ||||||
| 165 | -16,7039 | 0 | -16,7039 | ||||||
| 170 | -27,1093 | 0 | -27,1093 | ||||||
| 175 | -30,3377 | 0 | -30,3377 | ||||||
| 180 | -23,0020 | 0 | -23,0020 | ||||||
| 185 | -4,3520 | 0 | -4,3520 | зміна знака | |||||
| 190 | 23,4221 | 0 | 23,4221 | ||||||
| 195 | 55,5727 | 0 | 55,5727 | ||||||
| 200 | 85,7823 | 0 | 85,7823 | ||||||
| 205 | 107,5385 | 0 | 107,5385 | ||||||
| 210 | 115,5876 | 0 | 115,5876 | ||||||
| 215 | 107,1397 | 0 | 107,1397 | ||||||
| 220 | 82,5521 | 0 | 82,5521 | ||||||
| 225 | 45,3337 | 0 | 45,3337 | ||||||
| 230 | 1,4645 | 0 | 1,4645 | зміна знака | |||||
| 235 | -41,8225 | 0 | -41,8225 | ||||||
| 240 | -77,5282 | 0 | -77,5282 | ||||||
| 245 | -100,3165 | 0 | -100,3165 | ||||||
| 250 | -107,5698 | 0 | -107,5698 | ||||||
| 255 | -99,8416 | 0 | -99,8416 | ||||||
| 260 | -80,6115 | 0 | -80,6115 | ||||||
| 265 | -55,4037 | 0 | -55,4037 | ||||||
| 270 | -30,4751 | 0 | -30,4751 | ||||||
| 275 | -11,3711 | 0 | -11,3711 | ||||||
| 280 | -1,6789 | 0 | -1,6789 | ||||||
| 285 | -2,2580 | 0 | -2,2580 | ||||||
| 290 | -11,1210 | 0 | -11,1210 | ||||||
| 295 | -23,9866 | 0 | -23,9866 | ||||||
| 300 | -35,3741 | 0 | -35,3741 | ||||||
| 305 | -39,9858 | 0 | -39,9858 | ||||||
| 310 | -34,0560 | 0 | -34,0560 | ||||||
| 315 | -16,3539 | 0 | -16,3539 | зміна знака | |||||
| 320 | 11,3948 | 0 | 11,3948 | ||||||
| 325 | 44,7702 | 0 | 44,7702 | ||||||
| 330 | 77,5536 | 0 | 77,5536 | ||||||
| 335 | 103,0577 | 0 | 103,0577 | ||||||
| 340 | 115,5964 | 0 | 115,5964 | ||||||
| 345 | 111,7635 | 0 | 111,7635 | ||||||
| 350 | 91,2325 | 0 | 91,2325 | ||||||
| 355 | 56,8943 | 0 | 56,8943 | ||||||
| 360 | 14,2986 | 0 | 14,2986 | зміна знака | |||||
| 365 | -29,4800 | 0 | -29,4800 | ||||||
| 370 | -67,3017 | 0 | -67,3017 | ||||||
| 375 | -93,4252 | 0 | -93,4252 | ||||||
| 380 | -104,6518 | 0 | -104,6518 | ||||||
| 385 | -100,9012 | 0 | -100,9012 | ||||||
| 390 | -85,0963 | 0 | -85,0963 | ||||||
| 395 | -62,3901 | 0 | -62,3901 | ||||||
| 400 | -38,9164 | 0 | -38,9164 | зміна знака | |||||
ДОДАТОК Д
| A | B | C | D | E | F | G |
| Найменування сировини | Норми витрати (кг / кг.) | Запаси сировини (кг) | Обмеження | |||
| A | B | C | ||||
| Цукровий пісок | 0,6 | 0,5 | 0,6 | 800 | > = | 720 |
| Патока | 0,4 | 0,4 | 0,3 | 600 | > = | 480 |
| Фруктове пюре | 0,1 | 0,2 | 0,2 | 120 | > = | 120 |
| Прибуток від реалізації (руб. / кг.) | 1,08 | 1,12 | 1,28 | |||
| Цільова функція | 1296 | |||||
| x1 | 1200 | А = х1 | ||||
| x2 | 0 | В = х2 | ||||
| x3 | 0 | С = х3 | ||||
Література
1. Банді Б. Основи лінійного програмування. - М.: Радіо і зв'язок, 1989.
2. Карпов Б. Microsoft Excel 2000. Довідник .- Пітер, 2002.
3. Семеніщенков А. Microsoft Excel. Параметри та методи практичного програмування. - Брянськ, 1998.
4. Ричков В. Microsoft Excel 2000. - Пітер, 2000.