55017

Використання надбудови. Пошук розв’язку електронних таблиць Microsoft Excel

Научная статья

Педагогика и дидактика

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

Украинкский

2014-03-21

466.5 KB

16 чел.

 Використання

надбудови

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

електронних таблиць

Microsoft Excel


ВСТУП

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

Електронна таблиця, як і розграфлена на папері, розділена на стовпці і рядки, у вічка яких записана різного роду інформація: тексти, числа, формули і так далі. Існуючі електронні таблиці розрізняються, в основному, набором виконуваних функцій і зручністю інтерфейсу. Більше 90% користувачів персональних комп'ютерів працюють з Microsoft Excel.

В теоретичної частині роботи розглядається питання: «Засіб «Пошук розв’язку. Розв’язання задач з використанням функціональних можливостей електронних таблиць Microsoft Excel».

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

Microsoft Excel застосовується при вирішенні планово-економічних, фінансових, техніко-економічних і інженерних завдань, при виконанні бухгалтерського і банківського обліку, для статистичній обробці інформації, для аналізу даних і прогнозування проектів, при заповненні податкових декларацій.

Електронні таблиці Excel дозволяють обробляти статистичну інформацію і представляти дані у вигляді графіків і діаграм. Їх можна використовувати і в повсякденному житті для обліку і аналізу витрачання грошових коштів: при щоденній покупці продуктів і господарських товарів, при оплаті рахунків і так далі. Можливість швидкого перерахунку даних під час вступу новій інформації, розрахунку декількох варіантів вихідних даних полегшує моделювання різних ситуацій і вибір оптимального варіанту.


НАДБУДОВА EXCEL

У Excel передбачені надбудови, які дозволяють розширити можливості програми, доповнити її новими командами і функціями. Деякі надбудови можуть використовуватися лише в Excel. Інші програми надбудов для Excel або Microsoft Office відомі як надбудови моделі компонентних об'єктів (Сом-надбудови). Для установки програми надбудови і її завантаження в Excel необхідно використовувати команду Надбудови (Add-Ins) з меню Сервіс (Tools) і в діалоговому вікні команди встановити прапорці поряд із знов встановлюваними компонентами.



Діалогове вікно, використовуване для установки/удаления надбудов

Розглянемо одну із надбудов Excel Пошук розв'язку.


ПОШУК РОЗВ'ЯЗКУ

Елементи діалогового вікна Поиск Решения

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

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

Изменяя ячейки - служить для вказівки комірок, значення яких змінюються в процесі пошуку розв'язку доти, поки не будуть виконані накладені обмеження й умова оптимізації значення комірки, зазначеної в поле Установить целевую ячейку.

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

Ограничения - служить для відображення списку граничних умов поставленої задачі.

Добавить - служить для відображення діалогового вікна Добавить ограничения.

Изменить - служить для відображення діалогового вікна Изменить ограничения.

Удалить - служить для зняття зазначеного обмеження.

Выполнить - служить для запуску пошуку розв'язку поставленої задачі.

Закрыть - служить для виходу з вікна діалогу без запуску пошуку розв'язку поставленої задачі. При цьому зберігаються установки зроблені у вікнах діалогу, що з'являлися після натискань на кнопки Параметры, Добавить, Изменить або Удалить.

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

Восстановить - служить для очищення полів вікна діалогу і відновлення значень параметрів пошуку розв'язку, які використовуються за замовчуванням.

Елементи діалогового вікна Параметры поиска решения

Можна змінювати умови і варіанти пошуку розв'язку для лінійних і нелінійних задач, а також завантажувати і зберігати оптимизовані моделі. Значення і стани елементів керування, які використовувані за замовчуванням, підходять для розв'язання більшості задач.

Максимальное время - служить для обмеження часу, що відпускається на пошук розв'язку задачі. У поле можна ввести час (у секундах), який не перевищує 32767; значення 100, використовуване за замовчуванням, підходить для Розв'язання більшості простих задач.

Предельное число итераций - служить для керування часом розв'язання задачі, шляхом обмеження числа проміжних обчислень. У поле можна ввести час (у секундах) який не перевищує 32767; значення 100, використовуване за замовчуванням, підходить для розв'язання більшості простих задач.

Относительная погрешность - служить для завдання точності, з яким визначається відповідність комірки цільовому значенню або наближення до зазначених границь. Поле повинне містити десятковий дріб від 0 (нуля) до 1. Чим більше десяткових знаків у числі, що задається, тим вище точність - наприклад, число 0,0001 представлене з більш високою точністю, ніж 0,01.

Допустимое отклонение - служить для завдання допуску на відхилення від оптимального розв'язання, якщо безліч значень комірки, що впливає, обмежено безліччю цілих чисел. При вказівці більшого допуску пошук розв'язку закінчується швидше.

Сходимость - коли відносна зміна значення в цільовій комірці за останні п'ять ітерацій стає менше числа, зазначеного у полі Сходимость, пошук припиняється. Збіжність застосовується тільки до нелінійних задач, умовою служить дріб з інтервалу від 0 (нуля) до 1. Кращу збіжність характеризує більша кількість десяткових знаків — наприклад, 0,0001 відповідає меншій відносній зміні у порівнянні з 0,01. Краща збіжність вимагає більшого часу на пошук оптимального розв'язку.

Линейная модель - служить для прискорення пошуку розв'язку лінійної задачі оптимізації.

Показывать результаты итераций - служить для припинення пошуку розв'язку, якщо потрібен перегляд результатів окремих ітерацій.

Автоматическое масштабирование - служить для включення автоматичної нормалізації вхідних і вихідних значень, що якісно розрізняються з величиною — наприклад, максимізація прибутку у відсотках стосовно вкладень, обчислювальним у мільйонах карбованців.

Неотрицательные значения - дозволяє встановити нульову нижню границю для тих комірок, які впливають, для яких вона не була зазначена в поле Ограничения діалогового вікна Добавить ограничения.

Оценка - служить для вказівки методу екстраполяції — лінійного або квадратична — використовуваного для одержання вихідних оцінок значень перемінних у кожнім одномірнім пошуку.

Линейная.    Служить для використання лінійної екстраполяції уздовж дотичного вектора.

Квадратичная.    Служить для використання квадратичної екстраполяції, що дає кращі результати при розв'язанні нелінійних задач.

Призводные - служить для вказівки методу чисельного диференціювання — прямі або центральні похідні — який використовується для обчислення частинних похідних цільових і обмежуючих функцій.

Прямые.    Використовується в більшості задач, де швидкість зміни обмежень відносно невисока.

Центральные.    Використовується для функцій, що мають розривну похідну. Даний спосіб вимагає більше обчислень, однак його застосування може бути виправданим, якщо видається повідомлення про те, що одержати більш точне розв'язання не вдається.

Метод - служить для вибору алгоритму оптимізації — метод Ньютона або сполучених градієнтів — для вказівки напрямку пошуку.

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

Метод сопряженных градиентов.    Реалізація методу спряжених градієнтів, який потребує менше пам'яті, але виконується більше ітерацій, ніж у методі Ньютона. Даний метод варто використовувати, якщо задача досить велика і необхідно заощаджувати пам'ять, а також якщо ітерації дають занадто малу відмінність у послідовних наближеннях.

Загрузить модель - служить для відображення на екрані діалогового вікна Загрузить модель, у якому можна задати посилання на область комірок, що містять завантажувальну модель.

Сохранить модель - служить для відображення на екрані діалогового вікна Сохранить модель, у якому можна задати посилання на область комірок, призначену для збереження моделі оптимізації. Даний варіант передбачений для збереження на листку більш однієї моделі оптимізації — перша модель зберігається автоматично.

Елементи діалогових вікон

Добавить ограничения і Изменить ограничения

Ссылка на ячейку - служить для вказівки комірки або діапазону, на значення яких необхідно накласти обмеження.

Ограничение - служить для завдання умови, що накладається на значення комірки або діапазону, зазначеного у полі Ссылка на ячейку. Виберіть необхідний умовний оператор ( <=, =, >=, Int або Bin ). Введіть обмеження — число, формулу, посилання на комірку або діапазон — у поле праворуч від списку, що розкривається.

Добавить - натисніть на кнопку, щоб, не повертаючись у вікно діалогу Параметры поиска решения, завдати нову умову на пошук розв'язання задачі.


Звіт про знайдені розв’язки

За результатами пошуку розв’язку  можна створювати звіти декількох видів. У звіті Результати відображаються цільова клітинка і ті з впливаючих клітинок, значення яких змінювалися під час пошуку. Крім того, вказуються початкові і кінцеві значення цих клітинок, формули і накладені обмеження.

Створений звіт з'являється на новому листі книги. Для створення звіту потрібно:

  •  у вікні Результати пошуку розв’язку вибрати потрібний звіт із списку Тип звіту;
  •  натиснути кнопку ОК.

Результати пошуку розв’язку

Задання типу звіту

Задання параметрів для пошуку розв’язку

Задання збереження знайденого розв’язку

Створений звіт про розв’язку.

Розв'язок системи нелінійних рівнянь

Засіб пошуку розв'язку дозволяє знаходити розв'язання систем нелінійних рівнянь. Розглянемо, як це робиться, на прикладі розв'язання наступної системи рівнянь:

     (1)

Нагадаємо, що пари (x,y) є розв'язанням системи (1) тоді і тільки тоді, коли вона є розв'язанням наступного рівняння з двома невідомими:

(x2+y2-3)2+(2x+3y-1)2=0.     (2)

За допомогою засобу пошуку розв'язку замість системи (1) будемо розв'язувати рівносильне їй рівняння (2). Відзначимо, що розв'язанням системи рівнянь (1) є точки перетинання окружності з радіусом, рівним 3, і прямій. Отже, рівняння (2) має не більш двох різних розв'язків.

Обумовлене розв'язання нелінійної задачі залежить від початкового наближення, вдалий підбор якого дуже важливий. У даному випадку локалізувати корені можна, наприклад, протабулювавши ліву частину рівняння (2) за змінним x і y на відрізку [-3,3] із кроком 1,5. Результат табулювання наведений на рис. 4.

Рисунок 4 – Результат табулювання лівої частини рівняння

В комірки A2:A6 і B1:F1 введені значення x і y, відповідно. В комірку B2 введена формула

=($A2^2+B$1^2-3)^2+(2*$A2+3*B$1-1)^2

яка обчислює праву частину рівняння (2) при значеннях x і y з комірок A2 і B1, відповідно. Протягнемо цю формулу на діапазон B2:F6. З рис. 4 видно, що за початкове наближення до кореня розумно вибрати наступні пари значень (-1,5;1,5), (1,5;0) і (1,5;-1,5). Можна переконатися, що дві останні пари початкових наближень за допомогою засобу пошуку розв'язку будуть приводити до перебування того самого розв'язання.

Для перебування першого кореня відведемо під змінні x і y комірку A10 і B10, відповідно, і введемо в них початкові наближення -1,5 і 1,5. В комірку C10 введемо формулу

==(A10^2+B10^2-3)^2+(2*A10+3*B10-1)^2

яка обчислює значення правої частини рівняння (2) для цих значення невідомих. Потім викличемо команду Сервис, Поиск решения.

У діалоговому вікні Поиск решения в поле Установить целевую ячейку вводимо C10. У поле Изменяя ячейки вводимо діапазон комірок A10:B10. У групі Равной встановимо перемикач у положення  Значению, у полі введення якого вводимо 0. Не забудьте переконатися, що в діалоговому вікні Параметры поиска решения (рис. 2) знятий прапорець  Линейная модель.

Після натискання на кнопку  Выполнить засіб пошуку розв'язку знаходить розв'язання, що поміщає у комірки A10 і B10. У даному випадку це будуть значення -1,268 і 1,179. У комірці C10 обчислюється значення правої частини рівняння (2) при цих значеннях невідомих. Тому що засіб пошуку розв'язку знаходить розв'язання наближено, то у комірці C10 у загальному випадку буде число, відмінне від нуля, але досить близьке до нього. У нашому прикладі це 8.89E-09, тобто 8.8910-9. Якщо в комірці C10 буде велике число, то розв'язання знайдене невірно.

Аналогічно знаходиться друге розв'язання, використовуючи початкове наближення (1,5;-1,5). Розв'язанням будуть значення 1,576 і -0,717. Початкове наближення (1,5;0) приводить до того ж розв'язання.

Транспортна задача

Розглянемо ще один приклад, де використовується засіб пошуку розв'язку. Припустимо, що фірма має 4 фабрики і 5 центрів розподілу її товарів. Фабрики фірми розташовуються в Луганську, Донецьку, Одесі і Миколаєві з виробничими можливостями 200, 150, 225 і 175 одиниць продукції щодня, відповідно. Центри розподілу товарів фірми розташовуються у Сімферополі, Львові, Дніпропетровську, Києві і Полтаві з потребами в 100, 200, 50, 250 і 150 одиниць продукції щодня, відповідно. Збереження на фабриці одиниці продукції, не поставленої у центр розподілу, обходиться в €0,75 у день, а штраф за прострочене постачання одиниці продукції, замовленої споживачем у центрі розподілу, але там не знаходиться, дорівнює €2,5 у день. Вартість перевезення одиниці продукції з фабрик у пункти розподілу наведена в табл. 1.

Таблиця 1 - Транспортні витрати

1

2

3

4

5

Сімферополь

Львів

Дніпропетровськ

Київ

Полтава

1

Луганськ

1,5

2

1,75

2,25

2,25

2

Донецьк

2,5

2

1,75

1

1,5

3

Одеса

2

1,5

1,5

1,75

1,75

4

Миколаїв

2

0,5

1,75

1,75

1,75

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

Оскільки дана модель збалансована (сумарний обсяг зробленої продукції дорівнює сумарному обсягові потреб у ній), то в цій моделі не треба враховувати витрати, зв'язані як зі складуванням, так і з недопоставками продукції. У противному випадку в модель потрібно було б ввести:

  •  у випадку надвиробництва – фіктивний пункт розподілу, вартість перевезень одиниці продукції в який покладається рівною вартості складування, а обсяги перевезень – обсягам складування надлишків продукції на фабриках
  •  у випадку дефіциту – фіктивну фабрику, вартість перевезень одиниці продукції з якої покладається рівної вартості штрафів за недопоставку продукції, а обсяги перевезень – обсягам недопоставок продукції в пункти розподілу

Для розв'язання даної задачі побудуємо її математичну модель. Невідомими в даній задачі є обсяги перевезень. Нехай xij – обсяг перевезень з i-й фабрики в j-й центр розподілу. Функція мети – це сумарні транспортні витрати, тобто

де cij – вартість перевезення одиниці продукції з i-й фабрики в j-й центр розподілу.

Невідомі в даній задачі повинні задовольняти наступним обмеженням:

  •  Обсяги перевезень не можуть бути негативними
  •  Тому що модель збалансована, то вся продукція повинна бути вивезена з фабрик, а потреби всіх центрів розподілу повинні бути цілком задоволені

У результаті маємо наступну модель:

мінімізувати:

при обмеженнях:

,  ,

де ai – обсяг виробництва на i-й фабриці, bj – попит у j-му центрі розподілу.

Для розв'язання цієї задачі за допомогою методу пошуку розв'язку введемо дані, як показано на рис. 5.

У комірки A1:E4 введені вартості перевезень. Комірки A6:E9 відведені під значення невідомих (обсяги перевезень). В комірки G6:G9 введені обсяги виробництва на фабриках, а в комірки A11:E11 введена потреба в продукції в пунктах розподілу. В комірку F10 введена цільова функція

=СУММПРОИЗВ(A1:E4;A6:E9)

Рисунок 5 - Вхідні дані транспортної задачі

В комірки A10:E10 введені формули

=СУММ(A6:A9)

=СУММ(B6:B9)

=СУММ(C6:C9)

=СУММ(D6:D9)

=СУММ(E6:E9)

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

В комірки F6:F9 введені формули

=СУММ(A6:E6)

=СУММ(A7:E7)

=СУММ(A8:E8)

=СУММ(A9:E9),

які обчислюють обсяг продукції, що вивозиться з фабрик.

Тепер виберемо команду Сервис, Поиск решения і заповнимо діалогове вікно, що  відкрилося: Поиск решения, як показано на рис.5 .

В діалоговому вікні  Параметры поиска решения (рис. 2) треба установити прапорець  Линейная модель. Після натискання кнопки Выполнить засіб пошуку розв'язку знаходить оптимальний план постачань продукції і відповідні йому транспортні витрати (рис. 5, 6).


Задача про призначення

Розглянемо приклад розв'язання задачі про призначення. Четверо робітників можуть виконувати чотири види робіт, вартості cij виконання i-м робітником j й роботи наведені у комірках діапазону A1:D4 (рис. 7).

Рисунок 7 - Вартості робіт у задачі про призначення

У цій таблиці рядки відповідають робітникам, а стовпці – роботам. Необхідно скласти план виконання робіт так, щоб усі роботи були виконані, кожен робітник був завантажений тільки на одній роботі, а сумарна вартість виконання всіх робіт була мінімальною. Відзначимо, що дана задача є збалансованою, тобто число робіт збігається з числом робітників. Якщо задача не збалансована, то перед початком розв'язування її необхідно збалансувати, ввівши відсутнє число фіктивних рядків або стовпців з досить великими штрафними вартостями робіт.

Для розв'язання даної задачі побудуємо її математичну модель. Нехай змінна xij=1, якщо i-м робітником виконується j-я робота, і xij=0, якщо i-м робітником не виконується j-я робота. Тоді модель має такий вигляд:

мінімізувати:

при обмеженнях:

,

.

Для розв'язання цієї задачі, за допомогою засобу пошуку рішень, відведемо під невідомі діапазон комірок F2:I5. В комірку J1 введемо цільову функцію =СУММПРОИЗВ(F2:I5;A1:D4), яка обчислює вартість робіт. Введемо формули, що задають ліві частини обмежень (рис. 8).

Рисунок 8 – Ліві частини обмежень у задачі про призначення

Потім виберемо команду  Сервис, Поиск решения і заповнимо діалогове вікно, що відкрилося, Поиск решения, як показано на рис. 9.

Не забудьте в діалоговому вікні  Параметры поиска решения установити прапорець Линейная модель. Після натискання кнопки Выполнить засіб пошуку розв'язку знайде оптимальний розв'язок (рис. 10).

Зазначимо, що прапорець Формулы діалогового вікна Параметры, що відкривається командою Сервис, Параметры, забезпечує відображення формул в комірках, якщо вони там знаходяться (рис. 9).

Рисунок 9 – Діалогове вікно Поиск решения  задачі про призначення

Рисунок 10 – Оптимальний план робіт у задачі про призначення

Рівняння регресії

У даному розділі буде розглянута задача побудови регресійної моделі. За допомогою засобу пошуку розв'язку буде вирішена задача перебування рівняння регресії для однієї залежної й однієї незалежної змінних. Хоча розглянута модель має дуже специфічний вид, описаний підхід дозволяє досліджувати будь-яке рівняння регресії.

Загальний підхід до побудови рівняння регресії

на прикладі лінійної моделі

Розглянемо, як розв’язується  задача нелінійної оптимізації за допомогою засобу пошуку розв'язку на прикладі побудови лінійного рівняння регресії., Наприклад, є обсяг реалізації фірми, що торгує старими автомобілями, за шість тижнів її роботи. Значення величин, що спостерігаються, наведені на рис. 11, де x – номер звітного тижня, а y  обсяг реалізації за цей тиждень.

Рисунок 11 – Вхідні дані для побудови лінійної моделі

Необхідно побудувати лінійну модель y=mx+b, яка щонайкраще описує значення, що спостерігаються. Звичайно m і b підбираються так, щоб мінімізувати суму квадратів різниць між тими, що спостерігаються і теоретичними значеннями залежної змінної y, тобто мінімізувати

 

де n- число спостережень (у даному випадку n=6).

Для розв'язання цієї задачі відведемо під змінні m і b комірки D3 і E3, відповідно, а в комірку F3 введемо функцію, що мінімізується

{=СУММКВРАЗН(B2:B7;E3+D3*A2:A7)}

Функція СУММКВРАЗН обчислює суму квадратів різниць для елементів зазначених масивів.

Тепер виберемо команду  Сервис, Поиск решения і заповнимо діалогове вікно, що  відкрилося, Поиск решения, як показано на рис. 12.

Відзначимо, що на змінні m і b обмеження не накладаються. У результаті обчислень засіб пошуку розв'язку знайде: m=1,88571 і b=5,400.

Рисунок 12 – Діалогове вікно Поиск решения для розрахунку рівняння регресії

Функції робочого листа для рівнянь лінійної регресії

Параметри  m і b лінійної моделі y =mx + b з попереднього розділу можна визначити за допомогою функцій  НАКЛОН і ОТРЕЗОК.

Функція НАКЛОН визначає коефіцієнт нахилу лінійного тренду.

Синтаксис:

НАКЛОН(відомі_значення_y; відомі_значення_x)

Функція ОТРЕЗОК визначає точку перетинання лінії лінійного тренда з оссю ординат

Синтаксис:

ОТРЕЗОК(відомі_значення_y; відомі_значення_x)

Аргументи функцій НАКЛОН і ОТРЕЗОК.

Відомі_значення_y — масив або інтервал комірок, що містять числові залежні точки даних.

Відомі_значення_x — безліч незалежних точок даних.

Зауваження:

  •  Аргументи повинні бути числами або іменами, масивами або посиланнями, що містять числа.
  •  Якщо аргумент, який є масивом або посиланням, містить текст, логічні значення або порожні комірки, ці значення ігноруються; комірки, що містять нульові значення, враховуються.
  •  Якщо відомі_значення_y і відомі_значення_x містять різну кількість точок даних або зовсім не містять точок даних, то функції НАКЛОН і ОТРЕЗОК повертають значення помилки #Н/Д.

Функції  НАКЛОН і ОТРЕЗОК обчислюються за наступними формулами:

,

,

де ,

Коефіцієнти m і b можна знайти й іншим способом. Побудуйте точковий графік на діапазоні комірок A2:B7, виділіть точки графіка подвійним натисканням, а потім клацніть їх правою кнопкою миші. У контекстному меню, що розгорнулося, виберіть команду Линия тренда.

Лінії тренда

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

Лінійна апроксимація — це пряма лінія, що щонайкраще описує набір даних. Вона застосовується в найпростіших випадках, коли точки даних розташовані близько до прямої. Говорячи іншими словами, лінійна апроксимація гарна для величини, що зростає або спадає із постійною швидкістю.

Логарифмічна апроксимація добре описує величину, що спочатку швидко зростає або спадає, а потім поступово стабілізується. Описує як позитивні, так і негативні величини.

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

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

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

Використання як наближення ковзного середнього дозволяє згладити коливання даних і в такий спосіб більш наочно показати характер залежності. Така лінія тренда будується за зазначеним числом точок (воно задається параметром Шаг). Елементи даних усереднюються, і отриманий результат використовується як середнє значення для наближення. Так, якщо Шаг дорівнює 2, перша точка кривої, що згладжує, визначається як середнє значення перших двох елементів даних, друга точка - як середнє наступних двох елементів і так далі.

Додавання лінії тренду в діаграму відбувається при виділенні лінії графіка і виклику контекстного меню. У діалоговому вікні Линия тренда в групі Построение линии тренда (сглаживание и аппроксимация) вибираємо необхідну лінію тренда (рис.13). На вкладинці Параметры (рис. 14) можна встановити прапорці Показывать уравнение на диаграмме і Поместить на диаграмму величину достоверности аппроксимации.

Надійність линии тренда   Найбільш надійна лінія тренда, для якої значення R-квадрат дорівнює або близько до 1. При підборі лінії тренда до даних Excel автоматично розраховує значення R2. Можна відобразити це значення на діаграмі.

Прапорець Пересечение кривой Y с осью в точке (рис. 14) установлюється тільки в тому випадку, якщо ця точка відома.

У групі Прогноз можна задати обчислення прогнозу вперед та назад на введене у полі кількість одиниць


Список літератури

  1.  Гарнаев А. Использование MS Excel и VBA в экономике и финансах. – СПб: БХВ – Санкт-Петербург, 2000. –336с.
  2.  Гельман В.Я. Решение математических задач средствами Excel: Практикум. - К.: ЦУЛ, 2003.-395с.
  3.  Орлова И.В. Экономико-математические методы и модели. Выполнение расчетов в среде Excel: Практикум: Учеб. пособ. для вузов. – М.: ЗАО "Финстатинформ", 2000. – 136с.
  4.  Златопольский Д.М. 1700 заданий по Excel. Основы информатики. - СПб.:BHV, БХВ — Санкт-Петербург, 2003. - 544с.
  5.  Лавренов С.М. Excel. Сборник примеров и задач. – М.: Финансы и статистика, 2000. – 336с.
  6.  Блатнер П., Ульрих Л., Кук К. Использование Excel 2000. Специальное издание. -М., К., СПб. 2000.
  7.  Карпов Б. Microsoft Office 2000 Справочник. - СПб.: Питер, 2000.
  8.  Ахметов К., Борзенко А. Современный персональный компьютер. - М.: Компьютер-пресс, 1995.
  9.  Левин А. Самоучитель работы на компьютере. - М.: Нолидж, 1999.
  10.  ДоджМ., Кината К., Стинсон К. Эффективная работа с Excel 7.0 для Windows - СПб.: Питер, 1996.
  11.  Новиков Ф.А., Яценко А. Microsoft Office в целом–СПб.: БХВ-Санкт-Петербург, 2000.
  12.  Стоцкий Ю. Office 2000: самоучитель–СПб.: Питер, 2000.
  13.  Электронный офис. В 2-х тт./ Каратыгин С. и др. – М.: Нолидж, 1999.
  14.  Йорг Шиб. Windows: сотни полезных советов. - М.: Бином, 1996.
  15.  Курицкий Б.Я. Поиск оптимальных решений средствами Excel 7.0. - СПб.: Питер, 1997.
  16.  Мартик Альтхауз Михаэль Орлет, Excel 7.0. - М.: Биком, 1998.
  17.  Николь Н., Альбрехт Р. Электронные таблицы Excel 7.0. -М.: ЭКОМ., 1998.
  18.  Пробитюк А. Excel 7.0 для Windows в бюро. -К., BHV, 1997.
  19.  Руденко В.Д., Макарчук О.М., Патланжоглу М.О. Практичный курс інформатики. - К.: Фенікс, 1997.


Рисунок
1  - Диалогове вікно Поиск решения

Рисунок 2  - Діалогове вікно Параметры поиска решения

Рисунок 3  - Діалогове вікно  Добавить ограничения

Рисунок 5 – Діалогове вікно Поиск решения для транспортної задачі

Рисунок 6 – Оптимальне рішення транспортної задачі

Рисунок 14 – Вкладка Параметры діалогового вікна Линия тренда

исунок 13 – Вкладка Тип діалогового вікна

Линии тренда

  1.  

 

А также другие работы, которые могут Вас заинтересовать

39688. Современные перспективные направления повышения точности 61 KB
  Все сказанное определяет виртуальный образ технологической системы. Следовательно технологическая система станка должна быть оснащена соответствующими вычислительными средствами возмещающими деятельность человека и соответствующую часть технологической системы. Вычислительная система станка кроме традиционных задач управления процессом обработки должна выполнять следующие задачи: оценку точностных возможностей технологической системы на основе информации полученной подсистемами диагностики состояния станка и инструмента; оценку...
39690. Поднастройка станков. Автоматическое управление точностью в процессе обработки 134 KB
  Автоматическое управление точностью в процессе обработки Для обеспечения требуемой точности обработки партии заготовок недостаточно правильно осуществить настройку станка. Под влиянием погрешностей в процессе обработки происходит смещение поля рассеивания размеров деталей к границе допуска. Задача состоит в том чтобы обеспечить необходимую точность обработки в пределах поля допуска и иметь наименьшее количество поднастроек. Для повышения точности и производительности обработки необходимо или уменьшать составляющие погрешности обработки т.
39691. Анализ точности методами математической статистики 149.5 KB
  Систематические постоянные погрешности могут быть выявлены измерением деталей после обработки и их влияние может быть уменьшено технологическими мерами. Кривые распределения и оценка точности на их основе Статистический метод оценки точности применяется в условиях производства большого количества деталей. Для его применения необходимо произвести выборку деталей из обрабатываемых на исследуемой операции. По результатам измерения деталей выборки строится опытная кривая распределения к которой по критерию согласия подбирается теоретический...
39692. Вибрации при механической обработке 55 KB
  Наибольшее влияние на процессы резания оказывают вынужденные колебания и автоколебания. В отличие от вынужденных колебаний автоколебания начинаются одновременно с началом процесса резания и прекращаются с его окончанием. Причиной возникновения автоколебаний является сам процесс резания Переменная сила поддерживающая колебания создается и управляется процессом резания и при его прекращении исчезает. Автоколебания возникают в связи с непостоянством сил резания вследствие изменения сил трения стружки по передней поверхности режущего...
39693. Особенности проектирования технологических процессов механической обработки для ГПС без использования ПР 39 KB
  Заготовки устанавливаются и закрепляются в приспособлениях которые в виде различных наладок монтируются на палетах. Наладчик комплектует наладку и устанавливает заготовки в соответствии со схемой установки транслируемой системой управления ГПС на экран терминала участка комплектации. Наиболее приемлемы три варианта обработки: сохранение на окончательно обработанной заготовке одной необработанной поверхности для базирования закрепления и обработки заготовок за один установ; предварительная обработка вне ГПС на участке подготовки баз...
39694. Точность и надежность обработки заготовок в ГПС 43.5 KB
  На основании оценки надежности технологических систем производится: оптимизация технологических маршрутов операций и режимов обработки; выбор средств технологического оснащения; установление периодичности замены режущего инструмента; установление такта выпуска изделий. При ужесточении этих требований например для квалитетов IT5 и IT6 возрастает роль составляющих погрешностей обработки обусловленных ошибками начальной настройки инструмента его износа тепловыми деформациями технологической системы ошибками установки инструмента...
39695. ТЕХНОЛОГИЯ ПРОИЗВОДСТВА ДЕТАЛЕЙ МАШИН В ГИБКИХ ПРОИЗВОДСТВЕННЫХ СИСТЕМАХ 111.5 KB
  Опыт внедрения гибких автоматизированных систем в механообработке показывает возможность снижения трудоемкости обработки заготовок в несколько раз; сокращения обслуживающего персонала; увеличения выпуска продукции за счет повышения загрузки оборудования сокращения сроков и стоимости подготовки производства. К основным преимуществам гибких производственных систем механообработки относится: резкое увеличение производительности труда в процессе изготовления единичной и мелкосерийной продукции; быстрое реагирование на изменение требований...
39696. Особенности проектирования технологических процессов для ГПС 114 KB
  Дальнейший анализ заготовок обработка которых предполагается в ГПС производится в следующей последовательности: анализ возможности унификации конструктивных элементов и параметров деталей подготовка предложений по отработке конструкций на технологичность; анализ возможности получения заготовок более прогрессивными методами формообразования в целях уменьшения трудоемкости механообработки расхода материалов улучшения качества изделий и подготовка предложений по переводу технологии на прогрессивные методы получения заготовок; ...