Використання Microsoft Office Excel для аналізу даних і розв’язання задач оптимізації

Для оцінки ступеня зв'язку між величинами використовується коефіцієнт множинної кореляції R Пірсона (кореляційне відношення), що може приймати значення від 0 до 1. R =0 якщо між величинами немає ніякого зв'язку й R =1, якщо між величинами є функціональний (детермінований) зв'язок. У більшості випадків R приймає проміжні значення від 0 до 1. Величина R ² називається коефіцієнтом детермінації.

Задачею побудови регресійної залежності є знаходження вектора коефіцієнтів M моделі (1) при якому коефіцієнт R приймає максимальне значення.

Для оцінки значимості R застосовується F -Критерій Фішера, що обчислюється за формулою:

де n - розмір вибірки (кількість експериментів); k - число коефіцієнтів моделі. Якщо F перевищує деяке критичне значення для даних n і k і прийнятої довірчої ймовірності, то величина R вважається істотною. Таблиці критичних значень F приводяться в довідниках по математичній статистиці.

Таким чином, значимість R визначається не тільки його величиною, але й співвідношенням між кількістю експериментів і кількістю коефіцієнтів (параметрів) моделі. Дійсно, кореляційне відношення для n =2 для простої лінійної моделі дорівнює 1 (через 2 точки на площині можна завжди провести єдину пряму). Однак, якщо експериментальні дані є випадковими величинами, довіряти такому значенню R треба з великою обережністю. Звичайно для одержання значимого R і достовірної регресії прагнуть до того, щоб кількість експериментів істотно перевищувало кількість коефіцієнтів моделі (n >> k).

Для побудови лінійної регресійної моделі необхідно:

1) підготувати список з n рядків і m стовпців, що містить експериментальні дані (стовпець, що містить вихідну величину y повинен бути або першим, або останнім у списку);

2) звернутися до меню Сервіс/Аналіз даних/Регресія

Якщо пункт "Аналіз даних" у меню "Сервіс" відсутній, отже варто звернутися до пункту "Надбудови" того ж меню й встановити прапорець "Пакет аналізу".

3) у діалоговому вікні "Регресія" задати:

• вхідний інтервал Y;
• вхідний інтервал X;
• вихідний інтервал - верхня ліва комірка інтервалу, у якій будуть міститися результати обчислень (рекомендується розмістити на новому робочому аркуші);

4) Натиснути "Ok" і проаналізувати результати.

Пошук розв’язку

Excel має кілька програм-надбудов, що виконують розв’язання різних задач. Однієї з надбудов є " Пошук розв’язку ", що дозволяє вирішувати оптимізаційні задачі в Excel. Найчастіше це задачі лінійного програмування (ЛП).

Загальне формулювання задачі ЛП: знайти невід’ємний розв’язок X системи лінійних рівнянь AX=B, при якому цільова функція f=CX приймає максимальне (мінімальне) значення, де A - матриця коефіцієнтів; B - об'єми ресурсів.

Економічний зміст системи AX=B полягає в завданні обмежень на ресурси, що витрачаються.

Економічний зміст цільової функції f=CX полягає в максимальному прибутку або мінімальній собівартості, що одержується від оптимального розв’язку X. Наприклад, якщо X - вектор об'ємів випуску продукції, а С - вектор прибутку, що одержується від одиниці кожного виду продукції, то f - сумарний прибуток від випуску всієї продукції.

Розглянемо роботу надбудови " Пошук розв’язку " на прикладі задачі про раціон годівлі тварин. Потрібно скласти такий раціон годівлі тварин трьома видами корму, при якому вони одержать необхідну кількість живильних речовин A і B і собівартість кормів буде мінімальна. Ціни кормів, необхідне кількість живильних речовин і їхній зміст у кожному кормі показані в таблиці.

Якщо позначити X=(x₁, x₂, x₃) — шукана кількість кормів, то задача ЛП формулюється так:

Знайти розв’язок X системи

при якому цільова функція

приймає мінімальне значення.

Математичне формулювання задачі необхідно оформити у вигляді таблиці, що показує основні залежності.

Середовища таблиці мають наступний зміст:

• діапазон A1:C2 - містить матрицю A;
• діапазон D1:D2 - містить вектор ресурсів В;
• діапазон A6:C6 - містить вектор цін С;
• діапазон A4:C4 - містить вектор розв’язків X, початкові значення якого задані нулю і який буде оптимізований програмою;
• діапазон E1:E2 - містить вирази, що обчислюють добуток AX;
• осередок E6 - містить вирази, що обчислює f=CX.

Виклик програми пошуку розв’язку виконується через меню "Сервіс\Пошук розв’язку...". У вікні, що відкрився, "Пошук розв’язку" необхідно встановити наступні параметри:

• " Встановити цільове середовище " — E6;
• Встановити перемикач " Рівної мінімальному значенню ";
• у поле " змінюючи середовище " указати діапазон A4:C4;
• в області " Обмеження " натиснути кнопку " Додати " і у вікні " Додавання обмежень " ввести обмеження: E1>=D1 і E2>=D2;

• Натиснути кнопку "Параметри..." і у вікні, що відкрилося, встановити прапорці "Лінійна модель", "Ненегативні значення" і вибрати перемикач "Оцінка" - "Лінійна".

Для запуску програми необхідно у вікні "Пошук розв’язку" натиснути кнопку "Виконати". Результати обчислень будуть записані в змінювані комірки таблиці. У підсумку таблиця повинна мати такий вигляд.

Таким чином, тварин варто годувати першим кормом у кількості 0,38 кг, третім — 3,85 кг і не використовувати другий корм взагалі. При такому раціоні витрати на годівлю однієї тварини складуть 11,88 грн.

Рис. 1. Таблиця для введення умов задачі лінійного програмування

2.2. В осередок D6 ввести формулу =СУММПРОИЗВ(B$3:C$3;B6:C6).

2.3. В осередок D9 ввести формулу =СУММПРОИЗВ(B$3:C$3;B9:C9) і розмножити по стовпці в осередках D10 і D11.

Розв’язання задачі

3.1. У меню вибрати «Сервіс - Пошук розв’язку». У поле «Встановити цільове середовище:» набрати $D$6. У поле «Рівної:» установити маркер в «Максимальному значенню».

3.2. В поле «Змінюючи середовище» ввести $B$3:$C$3.

3.3. Встановити курсор-прямокутник в поле «Обмеження». Натиснути на кнопку «Додати». У поле «Посилання на осередок:» ввести $B$3. Вибрати знак >=. В поле «Обмеження» ввести =$B$4. Нажати кнопку «Додати».

3.4. В поле «Посилання на середовище:» ввести $З$3. Вибрати знак >=. В поле «Обмеження» ввести =$З$4. Натиснути кнопку «Додати».

3.5. В поле «Посилання на середовище:» ввести $D$9. Вибрати знак <=. В поле «Обмеження» ввести =$F$9. Натиснути кнопку «Додати»

3.6. В поле «Посилання на середовище:» ввести $D$10. Вибрати знак <=. В поле «Обмеження» увести =$F$10. Нажати кнопку «Додати»

3.7. В поле «Посилання на середовище:» ввести $D$11. Вибрати знак <=. В поле «Обмеження» ввести =$F$11. Нажати кнопку OK. У результаті діалогове вікно Пошук розв’язку повинне бути заповнене також як на рис. 2.

Рис. 2. Діалогове вікно Пошук розв’язку

Завдання до роботи

Зробити регресійний аналіз даних, проаналізувати вплив початкових даних на оцінні коефіцієнти регресійної моделі, і при необхідності перерахувати її. Розв’язати задачу лінійного програмування засобами Excel.

Порядок виконання роботи

1). Проведення регресійного аналізу:

– Створити аркуш з ім'ям «Регресійний аналіз»;

– Вибрати собі з пункту «Варіанти завдання до роботи» завдання для проведення регресійного аналізу. Номер свого варіанту дізнатись у викладача;

– Зробити математичне формулювання задачі у вигляді таблиці вихідних даних. Вибір вихідних даних відбувається по всіх рядках таблиці «Дані для проведення регресійного аналізу», а стовпці вибираються по таблиці «Регресійний аналіз» відповідно до номера свого варіанта;

– Запустити пакет регресійного аналізу через меню «Сервіс - Пакет аналізу – Регресія».

– У вікні «Регресія» задати інтервали, що містять Y і X. Результати обробки розташувати на аркуші «Регресійний аналіз»;

– Проаналізувати значення t-статистики для кожного коефіцієнта моделі. Якщо яке-небудь значення не перевищує за абсолютним значенням граничного значення 3, то відповідний фактор X варто вважати не впливає істотно на Y, виключити його з подальшої обробки й повторити «Регресійний аналіз».

2). Рішення задачі лінійного програмування (ЗЛП):

– Вибрати задачу лінійного програмування відповідно до свого варіанта з розділу «Задачі лінійного програмування» пункту «Варіанти завдань до роботи»;

– Створити новий аркуш із ім'ям «РЗЛП»

– На знову створеному аркуші зробити математичне формулювання ЗЛП у вигляді таблиць вихідних даних;

– Відкрити вікно пошуку рішень через меню «Сервіс - Пошук рішення...».

– Вирішити ЗЛП для свого варіанта, на основі послідовності, наведеної в прикладі, описаному в пункті «Методика виконання роботи». Вихідні дані й результат розмістити на аркуші «РЗЛП».

3). Доповісти викладачеві про закінчення виконання роботи;

Вимога до захисту роботи

1). Звіт про виконання лабораторної роботи;

2). Конспект порядку виконання лабораторної роботи.

3). Знати відповіді на ВСІ контрольні питання.

Зміст звіту

1). Назва й мета лабораторної роботи;

2). Вміст аркуша «Регресійний аналіз», створеного в П.1 «Порядку виконання роботи».

Література

1). Зайден М. Excel 2000 / Зайден М.; Науч. ред.: А. Плещ, С. Молявко. - М.: Лабораторія Базових Знань, 2000 - 336 с.: іл. + табл.. - (Комп'ютер)

2). Вейсскопф Д. Excel 2000. Базовий курс (русифікована версія) / Вейсскопф Д.; Пер. с англ. під ред. В.В. Легейды, Б.Г. Жадаева. - Київ - М.- Спб.: СТОЛІТТЯ+; ЭНТРОП; Корона-Принт, 2000 - 400 с.: іл. + табл.. - Слів. термінів Excel: с. 377-384.-Предметний указ.: с. 385-393

3). Саймон Д. Аналіз даних в Excel = Excel Data Fnalysis: Наочний курс створення звітів, діаграм і вільних таблиць / Саймон Д.; Пер. с англ. И.В. Константинова; Під ред. И.В. Василенко. - М.- Спб.- Київ: Діалектика, 2004 - 517 с.: іл. + CD-ROM. - (Читаючи менше, дізнавайся більше!). - Прил.: с. 471-511.-Предметний указ.: с. 512-516

4). Лавренов С.М. Excel: Збірник прикладів і задач / Лавренов С.М.; Рец.: О.А. Козлов, Р.А. Подбельская. - М.: Фінанси й статистика, 2003 - 336 с.: іл.. - (Діалог з комп'ютером). - Библиогр.: с. 304-306.-Прил.: с. 307-332

Регресійний аналіз

Задача 1.

У трьох пунктах відправлення A, B, C зосереджений однорідний товар. Цей товар необхідно перевезти в чотири пункти призначення P, Q, R, S. Запаси товару в пунктах відправлення й потреби пунктів призначення показані в таблиці.

Вартості перевезень 1 т. вантажу з i-го пункту відправлення в j-й пункт призначення задані матрицею.

1) Знайти план перевезень вантажу, що забезпечує транспортному підприємству мінімальну вартість перевезень.

2) Вирішити попередню задачу для випадку, коли потреба в товарі в пункті Q становить 20 тон.

Урахувати, що у випадку недоліку запасів, всі потреби не можуть бути задоволені.

Задача 2.

Радіозавод випускає магнітофони двох моделей: A і B. Завод повинен випускати на місяць не менш Y_a і Y_b магнітофонів. Кожна модель приносить дохід у розмірі S_a і S_b рублів. Процес виробництва магнітофонів складається із трьох стадій: виготовлення деталей, складання виробу і його впакування. Нормативи часу на ці стадії задані матрицею T. Виробничі потужності заводу дозволяють витрачати на місяць не більше 600 годин на випуск деталей, 500 годин на складання магнітофонів і 200 годин на впакування. Скласти план випуску магнітофонів, що забезпечує заводу максимальний дохід. Визначити, яка зі стадій найбільшою мірою стримує зростання виробництва.

Задача 3.

Акціонерне товариство може обробляти на поле площею S га. чотири культури. Найбільш трудомістким етапом є збирання врожаю, що повинна здійснюватися у два етапи: спочатку вбираються 1-я й 2-я культури, а потім 3-я й 4-я. Трудомісткості збирання кожної культури становлять відповідно Т₁, Т₂, Т₃ і Т₄ людино-годин на 1 га. Для збирання суспільство може виділити R₁ людино-годин на 1-й етап і R₂ на 2-й. Скласти план посіву культур, даюший максимальний прибуток, якщо 1 га кожної з культур (при очікуваній урожайності) приносить прибуток відповідно в З₁, З₂, З₃, З₄ (тис.грн.). Визначити, який з етапів збирання обмежує зростання прибутку.

Задача 4.

Раціон череди великої рогатої худоби з 220 голів включає харчові продукти А, В, З, D і Е. У добу одна тварина повинне з'їдати не менш 2 кг продукти виду A, 1,5 кг продукту В, 0,9 кг продукту З, 3 кг продукти D і 1,8 кг продукту E. Однак у чистому виді зазначені продукти не виробляються. Вони втримуються в концентратах ДО-1, ДО-2 і ДО-3. Їхня ціна відповідно 0,5; 0,4; 0,9 грн. за кілограм. Зміст продуктів у кілограмі концентрату (в %) зазначено в таблиці.

.Побудувати модель, на основі якої скласти план покупки концентратів при якому витрати на покупку будуть мінімальні.

Задача 5.

Кондитерська фабрика для виробництва трьох видів карамелі A, B і C використовує три види сировини: цукровий пісок, патоку й фруктове пюре. Норми витрати сировини на виробництво 1 кг. карамелі задані в таблиці.

Запаси сировини на складі відповідно рівні V₁, V₂ і V₃ кг. Прибуток від реалізації 1 кг. продукції кожного виду визначається значеннями P_A, P_B і P_C. Знайти план виробництва карамелі, що забезпечує максимальний прибуток. З'ясувати, яке сировина обмежує зростання прибутку.

Задача 6.

Меблева фабрика випускає столи, стільці, крісла й ліжка. При виготовленні цих товарів використовується два види дощок. Запаси дощок і трудових ресурсів показані в таблиці.

Норми витрати сировини, трудомісткість виробництва продукції й прибуток від реалізації одиниці продукції показані в таблиці.

Виробу	Норми витрати
Ресурси	Столи	Стільці	Крісла	Ліжка
Дошки 1 типу (м)
Дошки 2 типи (м)
Трудомісткість (чол.-годину.)
Прибуток (грн.)

З урахуванням попиту на товари фабрика повинна випустити не більше 10 ліжок, а співвідношення столів і стільців повинне бути 1:6.

Знайти план виробництва меблів, що дає фабриці максимальний прибуток.

Задача 7.

Напівфабрикат надходить на фабрику у вигляді двох партій аркушів фанери. Перша партія містить 400 аркушів, друга - 250 аркушів. Із цієї фанери необхідно робити комлекты деталей: 1 комплект містить 4 деталі 1-го типу, 3 деталі 2-го й 3 деталі 3-го типи. Аркуші фанери можна розкроювати різними способами. Кількість деталей, яку можна одержати з 1 аркуша фанери показано в таблиці.

Скласти план розкрою фанери так, щоб одержати максимальну кількість комплектів деталей.

Задача 8.

Тканина трьох артикулів виробляється на ткацьких верстатах двох типів з різною продуктивністю. Сировиною для виготовлення тканин є пряжа й барвники. Основні параметри виробництва, його собівартість і ціни реалізації готового продукту показані в таблиці. Скласти план виробництва тканин, що забезпечує максимальний прибуток.

Задача 9.

Нафтопереробний завод має запаси 4-х напівфабрикатів: алкілата (S₁); крекінг-бензину (S₂); бензини прямої перегонки (S₃) і ізопентона (S₄). В результаті змішування цих компонентів у різних пропорціях, одержують 3 сорти авіаційного бензину: A, B і C. Запаси сировини, склад бензину й собівартість його виробництва дані в таблиці.

Визначити план виробництва бензину різних сортів, що забезпечує максимальний прибуток виробництва.

Задача 10.

Оптова база займається закупівлею й продажем сезонного товару. Одночасно на базі може зберігатися до 100 одиниць товару. Закупівля товару на черговий квартал здійснюється наприкінці попереднього кварталу. До початку першого кварталу на складі було 30 од. товару. Ціни придбання й продажу одиниці товару по кварталах показані в таблиці.

Скільки потрібно купувати й продавати товару за кожний квартал протягом року, щоб оптова база дістала максимальний прибуток?

Задача 11.

Цех меблевого комбінату випускає трельяжі, трюмо й тумбочки під телевізори. Норма витрати матеріалу розраховуючи на один виріб, планова собівартість, оптова ціна підприємства, плановий (місячний) асортименти й трудомісткість одиниці продукції наведені в таблиці. Запас деревостружних плит, дощок ялинових і березових 90, 30 і 14 м³ відповідно. Плановий фонд робочого часу 16800 людино-годин.

Виходячи з необхідності виконання плану по асортиментах і можливості його перевиконання по окремим (або навіть усім) показникам побудувати модель, на основі якої можна одержати план виробництва меблів, максимізуючий прибуток.

Задача 12

Фабрика випускає шкіряні штани, куртки й пальто спеціального призначення в асортименті, заданому відношенням 2:1:3. У процесі виготовлення виробу проходять три виробничих ділянки-дубильний, розкрійний і пошивний. Фабрика має практично необмежену сировинну базу, однак складна технологія висуває високі вимоги до кваліфікації робітників. Чисельність їх у рамках планованого періоду обмежена.

Час обробки виробів на кожній ділянці, їхня планова собівартість, оптова ціна підприємства наведені в таблиці.

Обмеження на фонд часу для дубильного, розкрійного, і пошивного ділянок становлять відповідно 3360, 2688 і 5040 ч.

З огляду на задані асортименти, побудувати модель, на основі якої визначити план виробництва одягу, максимальний прибуток від реалізованої продукції.

Задача 13

На придбання встаткування для нової виробничої ділянки виділено 300 тис. грн. Його передбачається розмістити на площі 45 кв. м. Ділянка може бути оснащений устаткуванням трьох видів - машинами вартістю 6 тис. грн. (тут і далі всі показники приводяться на одиницю встаткування), що розміщаються на площі 9 кв. м, продуктивністю 8 тис. одиниць продукції за зміну; машинами вартістю 3 тис. грн., що займають площа 4 кв. м, продуктивністю 4 тис. одиниць продукції за зміну; машинами вартістю 2 тис. грн. Займана ними площа 3 кв. м, продуктивність 3 тис. одиниць продукції.

Побудувати модель, на основі якої визначити план придбання встаткування, що забезпечує найбільшу продуктивність усього ділянки.

Задача 14

У плановому році будівельні організації міста переходять до спорудження будинків типів Д-1, Д-2, Д-3 і Д-4. Дані про кількість квартир різного типу в кожному із зазначених типів будинків, їхня планова собівартість наведені в таблиці. Річний план введення житлової площі становить відповідно 800, 1000, 900, 2000 і 7000 квартир зазначених типів.

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

Побудувати модель і знайти план будівництва будинків на фінансовий рік, при якому собівартість всіх будинків, що вводяться, буде мінімальною.

Задача 15

Підприємство випускає звичайний, спеціальний і декоративний сплави латуні й реалізує їх відповідно по 3; 4, 5 і 6 грн. за одиницю ваги. Його виробнича потужність дозволяє робити (за плановий період) не більше 500 од. ваги звичайного сплаву, 700 од. спеціального й 250 од. декоративного. Обов'язковими складовими сплавів є мідь, цинк, свинець і нікель. Їхня ціна відповідно 0,9; 0,7; 0,5 і 1,1 грн. за одиницю ваги.

За технологією декоративний сплав повинен містити не менш 7% нікелю, 49% міді й не більше 29% свинцю; спеціальний-не менш 3% нікелю, 71% міді, 9% цинку й не більше 21% свинцю. У звичайний сплав складові входять без обмежень.

Уважаючи, що собівартість сплавів складається тільки з вартості його інгредієнтів, побудувати модель, на основі якої знайти план випуску сплавів,- забезпечуючий максимальний прибуток.

Методичні вказівки

до виконання лабораторної роботи №8

з навчальної дисципліни

“Комп’ютерні технології та програмування“

для напряму підготовки 6.050202

„Автоматизація та комп’ютерно-інтегровані технології”

Рівне – 2013

Методичні вказівки до виконання лабораторної роботи №7 з навчальної дисципліни “Комп’ютерні технології та програмування“ для напряму підготовки 6.050202 „Автоматизація та комп’ютерно-інтегровані технології”

А.П. Сафоник - Рівне: НУВГП, 2012. – 26 с.

Розробник: А.П. Сафоник, кандидат техн. наук, доцент кафедри автоматизації та комп’ютерно-інтегрованих технологій НУВГП

Відповідальний за випуск - зав. кафедри автоматизації та комп’ютерно-інтегрованих технологій, професор, академік УЕАН Б.О. Баховець

Затверджено методичною комісією

за напрямом підготовки 6.050202

«Автоматизація та комп’ютерно-інтегровані технології»

Протокол № 2

від “ 27 ” вересня 2012 р.

Лабораторна робота № 8

Використання Microsoft Office Excel для аналізу даних і розв’язання задач оптимізації

Мета роботи: Вивчити вбудовані в Excel можливості аналізу даних на прикладі проведення регресійного аналізу. Ознайомитися зі стандартними надбудовами Excel на прикладі надбудови «Прийняття рішень», використовувати їх для розв’язання задачі лінійного програмування.

Теоретичні відомості

Что делать, если нет взаимности? А теперь спустимся с небес на землю. Приземлились? Продолжаем разговор...

Конфликты в семейной жизни. Как это изменить? Редкий брак и взаимоотношения существуют без конфликтов и напряженности. Через это проходят все...

ЧТО ПРОИСХОДИТ, КОГДА МЫ ССОРИМСЯ Не понимая различий, существующих между мужчинами и женщинами, очень легко довести дело до ссоры...

ЧТО И КАК ПИСАЛИ О МОДЕ В ЖУРНАЛАХ НАЧАЛА XX ВЕКА Первый номер журнала «Аполлон» за 1909 г. начинался, по сути, с программного заявления редакции журнала...

Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте: