Прогнозування значень

 

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

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

Метод ковзаючого середнього. Ковзаюче середнє використовується для розрахунку значень в прогнозованому періоді на основі середнього значення змінної для вказаного числа попередніх періодів.

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

Цей метод може використовуватися для прогнозу збуту, запасів і інших процесів. Розрахунок прогнозованих значень виконується по наступній|такій| формулі:

де: N - число попередніх періодів, що входять в ковзаюче середнє; A -фактичне значення у момент часу j, F_j - прогнозоване значення у момент часу j, t - інтервал згладжування (вибирається в більшості випадків рівним 3).

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

Приклад. Одержати прогнозоване значення сальдо рахунку 051 використовуючи метод ковзаючого середнього. За допомогою команди меню Сервис ► Анализ данных викликати діалогове вікно Анализ данных. Вибрати інструмент аналізу - Скользящее среднее. Вказати параметри як на рис. 6.73. Параметр Интервал вказати 3 (розрахунок середнього з 3 суміжних значень).

 

 

Рисунок 6.73. Параметри ковзаючого середнього

 

Після натиснення кнопки ОК отримаємо таблицю прогнозних значень і графік рис.6.74.

 

 

Рисунок 6.74. Графік ковзаючого середнього Сальдо рахунку|лічби| 051

Метод експоненціального згладжування. Метод експоненціального згладжування припускає|передбачає|, що останні значення тимчасового ряду|лави| більш значущі для прогнозу. Кожне прогнозне значення можна визначити на основі виразу|вираження|:

де - прогнозне значення, - прогнозне значення попереднього періоду, - чинник загасання (застосовується, як правило, рівним 0.3), - фактичне значення.

 

Наприклад, нам необхідно набути прогнозного значення сальдо того, що вважає за 051 метод експоненціального згладжування. За допомогою команди меню Сервис ► Анализ данных викликати діалогове вікно Анализ данных. Вибрати інструмент аналізу - Экспоненциальное сглаживание Экспоненциальное сглаживание і вказати параметри згідно рис. 6.75.

 

Рисунок 6.75. Параметри експоненціального згладжування

Результат експоненціального згладжування після натиснення кнопки ОК приведенийна рис. 6.76. По графіку можна визначити найбільш ймовірне значення сальдо рахунку|лічби| на майбутній обліковий період.

 

 

Рисунок 6.76. Графік експоненціального згладжування

6.9.7.3. Метод регресії

 

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

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

Вигляд|вид| і параметри рівняння регресії встановлюються за допомогою методу найменших квадратів.

За типом рівняння регресії розрізняють: лінійну і нелінійну.

Для оцінки отриманих результатів за допомогою рівняння регресії використовуються наступні|слідуючі| показники:

 

· загальна|спільна| дисперсія, обумовлена впливом всіх чинників|факторів| в сукупності:

,

 

· факторна дисперсія, що відображає|відбиває| варіацію значень функції регресії від дії одиничного|поодинокого| чинника|фактору|:

 

,

 

· залишкова дисперсія відображає|відбиває| варіацію значень регресивної функції від дії всіх чинників|факторів|, окрім|крім| виділеного:

 

 

Основні співвідношення:

 

, .

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

,

 

де n - розмір вибірки, m - число чинників.

 

Використовуючи F - критерійФішера для визначення критичного значення - F _кр при k₁ = m, k₂ = n - m.

Обчислене критичне значення порівнюється з фактичним F_r. Якщо F_r > F_кр величина R визнається істотною. Величина F_кр обчислюється за допомогою вбудованої функції FРАСПРОБР. На практиці використовується поріг, рівний 0, 7. Зв'язок вважається за сильний і рівняння регресії придатне для прогнозування, якщо R більше 0, 7. Стандартне рівняння парної регресії лінійного вигляду: y=a₀ +bx.

Для кожного коефіцієнта рівняння регресії обчислюється оцінка t - критерія Стьюдента:

 

· стандартна помилка коефіцієнта регресії;

· t -статистика(відношеннякоефіцієнта до стандартної помилки).

 

Критичні значення t-статистики обчислюється за допомогою вбудованої функції СТЬЮДРАСОБР. Якщо t - статистика значуща, коефіцієнти приймаються для побудови рівняння регресії, інакше з рівняння регресії виключається змінна.

Приклад|зразок|. Побудувати|спорудити| рівняння регресії для розрахунку витрат|затрат| на ремонт устаткування|обладнання| залежно від терміну експлуатації і випуску продукції. Початкові|вихідні| дані для аналізу представлені|уявляти| в таблиці 6.15.

 

Таблиця6.15. Витрати на ремонт устаткування

 

За допомогою команди Сервис ► Анализ данных викликати діалогове вікно Анализ данных. Вибрати інструмент аналізу – Регрессия. Вказати параметри для регресії (рис.6.77).

Параметр Константа – ноль не вказується, оскільки лінія регресії не проходить через початок координат. Рівень надійності - 67% оскільки рівень 95% обчислюється автоматично. Після установки параметрів натиснути кнопку ОК.

 

Рисунок 6.77. Параметри регресії

 

Результати аналізу представлені|уявляти| в трьох таблицях (таблиця. 6.16 – 6.18).

Таблиця6.16. Коефіцієнт детерміації

Ці значення свідчать|засвідчують| про наявність сильного зв'язку показника витрат|затрат| на ремонт від терміну служби устаткування|обладнання| і обсягу випуску продукції.

 

Таблиця6.17. Дисперсійний аналіз

Таблиця6.17 містить|утримує| умовні позначення показників, що розраховуються, для регресії, залишку|остачі| і підсумку:

· df - число мір свободи (незалежні значення);

· SS - сума квадратів відхилень;

· MS - дисперсія, розраховується як відношення SS/df;

· F- відношеннядисперсії регресії до дисперсії залишку;

· Значущість F - рівень значущості, розраховується як SS Регрессии/MS Остатка.

 

Рівняння регресії значуще для прогнозування, якщо вираз (Значимость F) близький до 1.

Таблиця6.18. Параметри рівняння регресії

 

Коефіцієнт змінної може використовуватися в рівнянні регресії, якщо обчислена для нього вираз (Р - Значение) близький до 1.

В даному випадку параметр «Випуск продукції» і «Y- пересечение” (вільний член рівняння регресії) не є значущим. Тому модельне рівняння регресії:

можна представити|уявляти| у вигляді|виді|:

 

.

 

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

 

.

Контрольні питання до розділу 6

1. Що таке табличний процесор Excel? Яке призначення та технічні можливості процесора Excel?

2. Які елементи має екран електронної таблиці для спілкування з користувачем?

3. У якому діалоговому вікні відбувається на­строювання електронної таблиці та як його встановити?

4. Яке вікно табличного процесора використовується для введення даних?

5. Яке діалогове вікно використовується для настроювання панелей інструментів і меню команд?

6. Що таке книга, лист, стовпець, рядок, комірка електронної таблиці та навіщо вони потрібні?

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

8. Яка кількість комірок на одному листу?

9. Як формується адрес комірки та які бувають адреси?

10. Поясніть наступну запис: [Про­дукція.xls]Товари! D23.

11. Поясніть таку запис: ‘С: \Мои доку­менты\Excel \[Продукція.xls]Товари’! D3.

12. Які бувають адреси комірок у табличному процесорі Excel?

13. Які це адреса комірок: A3, $A3, A$3, $A$3?

14. Яка функціональна клавіша використовується для встановлення абсолютної адреси комірки?

15. Як позначається діапазон комірок у табличному процесорі Excel?

16. Які існують способи створення, відкриття, збереження, закриття, видалення файлу робочої книги?

17. Чи можливо виконувати операції з робочими листами, якщо так то які?

18. Що треба зробити з коміркою для введення або редагування даних?

19. Як вводяться та редагуються дані та які існують типи даних у табличному процесорі Excel?

20. Які існують способи копіювання, переміщення, вилучення комірок з даними?

21. Що розуміють під автоматизацією введення даних?

22. Як можна надрукувати документ Microsoft Excel та вказати необхідні параметри для друку?

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

24. Проаналізуйте числові формати у табличному процесорі.

25. Чи можливо контролювати дані які вводяться в комірки, якщо так то які методи контролю даних доступні?

26. Які існують методи захисту даних від несанкціонованого доступу, зміни складу, структури й вмісту робочих книг, листів і комірок?

27. Що таке формула, як вона водиться та які вона може мати компоненти?

28. Які операції з формулами можна виконувати у табличному процесорі Excel?

29. Що розуміють під редагування формул?

30. Як копіювати та переміщувати формули, зробіть аналіз цих операцій?

31. Що таке майстер функцій у табличному процесорі та як з ним працювати?

32. Що таке масив даних та які бувають масиви?

33. Які типи даних використовують в масивах?

34. Проаналізуйте виконання операцій з масивами у табличному процесорі Excel.

35. Яка різниця між введенням звичайних формул і формул з масивами?

36. Як виглядає формула з масивами?

37. Чи можуть містити зовнішні посилання формули у таблицях на комірки іншої робочої книги?

38. Яким чином можна включити у формулу вбудовану функцію?

39. У якому вікні вводяться параметри вбудованих функцій?

40. Що уявляє собою іменований блок комірок?

41. Яка максимальна допустима глибина вкладень вбудованих функцій у формулі?

42. Які типи даних можуть бути параметрами функцій?

43. Яким чином можна вводити діапазон комірок у палітру функції?

44. Проаналізуйте категорії вбудованих функцій табличного процесора Excel.

45. Що таке константа та змінна у табличному процесорі Excel, які можуть використовуватися в якості параметрів функції?

46. Проаналізуйте призначення та використання основних вбудованих функцій категорії «Ссылки и массивы».

47. Проаналізуйте призначення та використання основних вбудованих функцій категорії «Текстовые функции».

48. Проаналізуйте призначення та використання основних вбудованих функцій категорії «Дата время».

49. Проаналізуйте призначення та використання основних вбудованих функцій категорії «Проверка свойств и значений».

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

51. Проаналізуйте призначення та використання основних вбудованих функцій категорії «Математические».

52. Проаналізуйте призначення та використання основних логічних вбудованих функцій: ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ.

53. Чи дозволяє табличний процесор працювати з реляційними структурами?

54. Що таке список – таблиця у табличному процесорі Excel?

55. Яка різниця між полем та записом?

56. Яким вимогам повинен відповідати список бази даних у табличному процесорі Excel?

57. Перерахуйте традиційні операції зі списками бази даних.

58. Складіть словесний алгоритм створення бази даних за допомогою екранної форми, яка містить імена і зна­чення полів записів списку.

59. Чи дозволяє форма відбирати записи по критеріях?

60. За допомогою якої команди виконується сортування записів бази даних (списку)?

61. Які способи фільтрації записів списків існують в табличному процесорі Excel?

62. Яка команда використовується для відміни всіх умов фільтрації?

63. Складіть словесний алгоритм побудови таблиць.

64. Яке призначення вкладок діалогового вікна Формат ячеек, для форматування даних?

65. Що уявляє собою графік? Проаналізуйте можливості Excel для побудови діаграм.

66. Складіть словесний алгоритм побудови діаграм.

67. Які існують прийоми редагування діаграм, який з них найкращій?

68. Яке призначення команд для редагування діаграм: Формат оси, Формат названия оси, Формат заголовка диаграммы, Формат области построения, Формат рядов данных, Формат легенды.

69. Як побудувати графіки функцій з однією та двома умовами?

70. Вкажіть послідовність дій пари побудові графіків двох функцій в одній системі координат.

71. Як будується поверхня по відомій функції?

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

73. Проаналізуйте арифметичні операції з масивами.

74. Якііснують вбудовані функції для роботи з матрицями?

75. Які задачі можна вирішувати з використанням технології Підбір параметра?

76. Яких правил треба дотримуватись при використанні технології підбору параметра?

77. Для яких задач використовується технологія пошуку рішення? Що це за технологія?

78. Як можна сформулювати задачу оптимізації пошуку у загальному виді?

79. Що таке статистичний регресивний аналіз даних? Поясніть синтаксис функцій: ПРЕДСКАЗ, РОСТ, ТЕНДЕНЦИЯ.

80. Поясніть обмін даними між додатками за допомогою принципу OLE?

81. Які основні функції табличного процесора Excel призначених для розрахунку фінансових операцій за кредитами та позичками?

82. Поясніть призначення та синтаксис функцій для розрахунку майбутньої вартості грошей: БС, БЗРАСПИС?

83. Поясніть призначення та синтаксис функцій для розрахунку поточної вартості грошей: ПС, ЧПС, ЧИСТНЗ?

84. Поясніть призначення та синтаксис функцій визначення терміну платежу і процентної ставки: КПЕР, СТАВКА, ЭФФЕКТ, НОМИНАЛ?

85. Поясніть призначення та синтаксис функцій розрахунку платежів зв’язаних із погашенням позичок: ПЛТ, ОБЩПЛАТ, ОСПЛАТ, ОБЩДОХОД?

86. Поясніть призначення та синтаксис функцій для розрахунку амортизації майна: АПЛ, АСЧ, ДДОБ, ПУО, ФУО?

87. Що розуміють під інформаційними технологіями статистичного аналізу і прогнозування в табличному процесорі?

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

89. Поясніть на прикладі метод ковзаючого середнього.

90. Навіщо потрібен метод регресії? Наведіть приклад.

 

 

РОЗДІЛ 7. ПРОГРАМНІ ЗАСОБИ РОБОТИ З БАЗАМИ ТА