Практические_расчеты_на_EXСEL

Рисунок 3.1 – Приклад заповнення листа Excel для пошуку найбільшого значення функції

Рисунок 3.2 – Приклад завдання параметрів у вікні засобу Поиск решения

Рисунок 3.3 – Вікно з повідомленням про результати пошуку рішення

Для створення звіту про результати пошуку рішення треба вибрати потрібний тип звіту в полі Тип отчета. Рекомендується вибирати тип звіту Результаты. Після отримання рішення робоча таблиця має вигляд, зображений на рисунку 3.4.

21

Рисунок 3.4 – Результати пошуку найбільшого значення функції

Якщо Поиск решения не знайшов рішення, необхідно у вікні

Результаты поиска решения вибрати Восстановить исходные зна-

чения, перевірити вхідні дані й розрахункові формули на листі Excel і повторити процедуру пошуку рішення.

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

Транспортна задача відноситься до задач лінійного програмування. Вона формулюється таким чином. Відомо n постачальників яко- го-небудь товару і m споживачів того самого товару. Запаси товарів у постачальників становлять a1, a2, …, an, а потреба у споживачів – b1, b2, …, bm. Відомі також ціни cij на перевезення цього товару від i-го постачальника до j-го споживача. Необхідно знайти такі обсяги перевезень xij i-го постачальника до j-го споживача, які б мінімізували за-

	n	m
трати на перевезення, що обчислюються за формулою ååcij xij .
	i=1	j=1
n	m
Якщо виконується рівняння åai =åbj , тобто запаси у постачальни-
i=1	j=1

ків дорівнюють потребам споживачів, то така задача є збалансованою (закритою), у іншому випадку – незбалансованою (відкритою).

Розглянемо процес рішення збалансованої задачі для наступних

даних:

b1 = 190, b2 = 90, b3 = 100, b4 = 130, b5 = 140, a1 = 220, a2 = 250, a3 = 180,

22

æ 28		21	18	13	24ö
ç	10	26	29	22		÷
c = ç					21÷ .
ç	27	17	23	26	34	÷
è						ø

Рекомендований порядок рішення задачі наступний. Заносимо на лист Excel вхідні дані й необхідні розрахункові формули. Для цього в комірки (наприклад, B3:F5) вводимо дані про вартість перевезень, що задані матрицею с. Комірки B8:F10 залишаємо для значень об'ємів перевезень зі складів у пункти, що будуть одержані в результаті рішення задачі.

Вкомірки B12:F12 вводимо значення b1, b2, b3, b4, b5 про кількість вантажу, що треба доставити. В комірки H8:H10 вводимо значен-

ня a1, a2, a3 про кількість вантажу, що знаходиться на пунктах постачання.

Вкомірку G8 вводимо формулу для розрахунку витрат на перевезення зі складу 1 до всіх споживачів: =СУММ(B8:F8). Для цього слід

виділити комірку G8, натиснути кнопку (Автосумма) на панелі Стандартная, виділити комірки B8:F8, натиснути Enter. Розповсюджуємо введену формулу на діапазон комірок G9:G10, оскільки витрати на перевезення з іншого складу до всіх споживачів розраховуються аналогічно.

Вкомірку B11, використовуючи кнопку Автосумма, вводимо формулу для розрахунку кількості вантажу, доставленого першому споживачу: =СУММ(B8:B10). Розповсюджуємо введену формулу на діапазон комірок C11:F11.

Вкомірку G14 вводимо формулу для розрахунку сумарних витрат на перевезення: =СУММПРОИЗВ(B3:F5;B8:F10).

Приклад заповнення листа Excel для рішення транспортної задачі показаний на рисунку 3.5.

Після цього слід дати команду Сервис ® Поиск решения, задати параметри пошуку рішення і клацнути по кнопці Выполнить для запуску процесу рішення. Приклад завдання параметрів у вікні засобу Поиск решения показаний на рисунку 3.6.

23

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

Рисунок 3.6 – Приклад завдання параметрів у вікні засобу Поиск решения

При успішному завершенні процедури пошуку рішення для створення звіту про результати пошуку рішення треба в полі Тип отчета вибрати тип звіту Результаты. На листі Excel відображаються результати рішення транспортної задачі. Вид цього листа показаний на рисунку 3.7. На новому листі формується звіт про результати.

Якщо Поиск решения не знайшов рішення, необхідно у вікні

Результаты поиска решения вибрати Восстановить исходные зна-

чения, перевірити вхідні дані й розрахункові формули на листі Excel і повторити процедуру пошуку рішення.

24

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

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

4 СТАТИСТИЧНИЙ АНАЛІЗ ДАНИХ В EXCEL

4.1 Основні статистичні функції

Для визначення статистичних характеристик ряду даних в Excel є статистичні функції, які умовно поділяють на кілька підгруп.

До підгрупи функцій порівняння включено функції для вибору числа за певним критерієм. Основними з цих функцій є:

МАКС – вибирає найбільше число діапазону; МАКСА – вибирає найбільше число діапазону з урахуванням

логічних значень і тексту; МИН (МИНА) – вибирає найменше число діапазону;

МЕДИАНА – вибирає число, розміщене посередині діапазону

25

чисел;

МОДА – вибирає число, що найчастіше зустрічається в діапазоні чисел;

НАИБОЛЬШИЙ – вибирає найбільше k-те число діапазону чисел (k визначає положення числа відносно максимуму: при k = 1 буде знайдено максимум, а при k = 2 – число перед максимумом);

НАИМЕНЬШИЙ – вибирає найменше k-те число діапазону чисел; СЧЕТ – визначає кількість чисел у діапазоні; СЧЕТЗ – визначає кількість значень у діапазоні.

Підгрупа функцій середніх об’єднує функції для визначення середніх значень чисел, основними з яких є:

СРГАРМ – визначає середнє гармонічне множини n чисел xi за

формулою 1 ån 1 ; n i=1 xi

СРГЕОМ – визначає середнє геометричне за формулою

n

n Õxi ; i=1

СРЗНАЧ – визначає середнє арифметичне множини чисел

xср = 1 ån xi ; n i=1

УРЕЗСРЕДНЕЕ – визначає середнє арифметичне множини чисел за винятком заданої частини екстремальних значень.

До підгрупи функцій відхилень і дисперсії включено функції для визначення відхилень (дисперсії) чисел від заданого числа, основними з яких є:

СРОТКЛ – визначає середнє абсолютних відхилень точок від

1

n

середнього значення за формулою

å

xi - xср

;

n

ДИСП

(ДИСПА)

i=1

–

визначає

дисперсію

1

é n

1

æ

n

ö2

ù

D =

êåxi 2

-

×çç

åxi ÷÷

ú

;

n

n -1 ê

è

i=1

ø

ú

ë i−1

û

26

ДИСПР (ДИСПРА) – визначає дисперсію, що обчислюється за

1

é n

2

1

æ

n

ö2

ù

формулою

êåxi

-

çç

åxi ÷÷

ú

;

n

n ê

è

i=1

ø

ú

ë i=1

û

СТАНДОТКЛОН – визначає стандартне відхилення значень

від середнього і обчислюється як

;

D

КВАДРОТКЛ – визначає суму квадратів відхилень від серед-

нього значення ån (xi - xср )2 .

i=1

4.2Призначення і основні можливості засобу Пакет анализа

Крім спеціальних статистичних функцій для визначення статистичних характеристик ряду даних можна використовувати спеціальний інструмент Описательная статистика з Пакета анализа Excel. Пакет анализа включає великий набір інструментів для проведення статистичного, кореляційно-регресійного, дисперсного та інших видів аналізу. Для його виклику треба дати команду Сервис ® Анализ данных. Він є додатковим інструментом Excel, тому при першому запуску на комп'ютері його може не бути у меню. У цьому випадку треба дати команду Сервис ® Надстройки, встановити прапорець біля Пакет анализа. Найчастіше використовують наступні види аналізу:

-однофакторний дисперсний, що дозволяє перевірити гіпотезу про подібність середніх значень двох або більше вибірок;

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

-кореляційний, що використовується для кількісної оцінки взаємозв’язку двох наборів даних, причому результат представлений у

27

безрозмірному виді; − регресійний, що застосовується в тих випадках, коли необхід-

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

4.2 Використання засобу Описательная статистика

Інструмент Описательная статистика з Пакета анализа Excel

дозволяє за одне звертання до нього знайти відразу всі основні статистичні характеристики й представити їх у зручній табличній формі. Для його використання вхідні дані повинні відповідати таким вимогам:

−на листу вхідного діапазону даних не повинно бути об’єднаних комірок;

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

Після виклику інструменту Описательная статистика на екрані з’являється діалогове вікно Описательная статистика, в якому потрібно задати вхідний діапазон (поле Входной интервал), включити пра-

порці Итоговая статистика і Метки в первом столбце (або Метки в первой строке), якщо вхідний діапазон містить назви, клацнути по

ОК.

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

28

5 ПОБУДОВА ЛІНІЇ ТРЕНДА І ПРОГНОЗУВАННЯ ЗНАЧЕНЬ ПАРАМЕТРУ В EXCEL

5.1 Побудова лінії тренда і її використання для цілей прогнозування

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

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

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

порці показывать уравнение на диаграмме і поместить на диаграмму величину достоверности аппроксимации (R^2).

29

Величина R2 називається коефіцієнтом детермінації (0 ≤ R2 ≤1) і характеризує степінь взаємозв'язку між вхідними значеннями параметра і значеннями, розрахованими за рівнянням регресії. Якісна оцінка степеня зв'язку випадкових величин з використанням коефіцієнта R2 може бути виконана за шкалою Чеддона. Якщо R2 має значення 0,1-0,3, то степінь зв'язку незначна, 0,3-0,5 – слабка, 0,5-0,7 – істотна, 0,7-0,9 – висока, 0,9-0,99 – дуже висока, 1 – функціональна. Якщо R2 > 0,7, то зміна функції в основному обумовлена впливом факторів, і для прогнозування можна використовувати отриману регресійну модель. Для цього треба в одержане рівняння регресії підставити те значення аргументу, для якого треба виконати прогнозування.

Приклад використання лінії тренда для цілей прогнозування зображено на рисунку 5.1, де за даними про випуск продукції за 12 періодів будується трендова модель, яка потім використовується для прогнозування об’єму випущеної продукції у двох наступних періодах.

Рисунок 5.1 – Використання лінії тренда для цілей прогнозування

5.2 Функції прогнозування

Excel надає можливості здійснювати прогнозування за допомогою статистичних функцій ТЕНДЕНЦИЯ, ПРЕДСКАЗ і РОСТ.

30