-C4) ^ 2.
У комірки P5: P28 ця формула копіюється.
Наступні кроки виконані за допомогою автосуммирования?.
У осередок K29 введена формула=СУММ (K4: K28).
У осередок L29 введена формула=СУММ (L4: L28).
У осередок M29 введена формула=СУММ (M4: M28).
У осередок N29 введена формула=СУММ (N4: N28).
У осередок O29 введена формула=СУММ (O4: O28).
У осередок P29 введена формула=СУММ (P4: P28).
Для розрахунку коефіцієнта кореляції для лінійної апроксимації скористаємося формулою:
(2.3.1)
Для розрахунку коефіцієнта детермінованості скористаємося формулою:
(2.3.3)
Результати розрахунків представлені в таблиці 8.
Таблиця 8
У таблиці 8 в комірці C71 введена формула=K29/(L29 * M29) ^ 0,5.
У осередку F72 введена формула== 1-N29/M29.
У осередку F73 введена формула=1-O29/M29.
У осередку F74 введена формула== 1-P29/M29.
Аналіз результатів розрахунків показує, що квадратична апроксимація найкраще описує експериментальні дані.
4. Побудова графіків функцій та використання функції ЛИНЕЙН
Дослідження характеру залежності проведем в три етапи:
1. Побудова графіка залежності.
2.Построеніе лінії тренда (в даному випадку це пряма).
.Полученіе числових характеристик коефіцієнтів цього рівняння.
. 1 Побудова графіка залежності
) Виділимо інтервал B4: C28 (див. табл.2).
) Натискаємо «Вставка», вибираємо «Точкову діаграму».
) Серед точкових діаграм вибираємо діаграму з маркерами.
) Вибираємо «Макет 1».
) На що з'явилася діаграмі підписуємо назву «Лінійна апроксимація».
. 2 Побудова лінії тренда
1) Двічі клацнемо по діаграмі. Діаграма активізується.
2) Після натискання правої кнопки миші на графік, виберемо з меню команду «Додати лінію тренду»
) З'явитися діалогове вікно «Лінія тренда» - виберемо на вкладці «Тип» - «лінійний тип» і перейдемо до вкладки «Параметри». На вкладці «Параметри» зажадаємо показувати рівняння тренду на діаграмі і показувати значення R?. Натиснемо кнопку «ОК».
) На діаграмі з'явиться лінія тренда з відповідним рівнянням. При бажанні текстове поле з рівнянням можна перенести в більш зручне місце і відредагувати.
Рис.1
Для побудови квадратичної апроксимації на третьому кроці в діалоговому вікні «Лінія тренда» виберемо на вкладці «Тип» поліноміальний тип ступінь 2. Результат представлений на рис.2.
Рис. 2
Для побудови експоненційної апроксимації на третьому кроці в діалоговому вікні «Лінія тренда» виберемо на вкладці «Тип» експонентний тип. Результат представлений на рис.3.
Рис. 3
Примітка: побудови діаграм велися в Microsoft Excel 2007.
. 3 Отримання числових характеристик залежно
1) Створюємо табличну формулу (5 рядків і 2 стовпця).
2) Виділяємо область C80: D84.
) Викликаємо «майстер функцій».
) Вибираємо функцію ЛИНЕЙН.
) Визначаємо аргументи функції - у графі «ізв_знач_у» вказуємо В1: В25; в графі «ізв_знач_х» вказуємо А1: А25; графу «константа» залишаємо порожній; в графі «стат» набираємо «істина».
) Натискаємо кнопку «закінчити» і встановлюємо курсор в рядок формул.
) Встановлюємо курсор в рядок формул і натискаємо комбінацію клавіш Ctrl + Shift + Enter.
В результаті отримуємо таблицю 9.
Таблиця 9
CD8014,07823657-35,1563923810,7114749055,841406895820,9445169614,9975403883391,5410895238488067,856255173,303002
Висновок
Порівняння результатів, отриманих в середовищі Excel в матричній формі, з результатами роботи функції ЛИНЕЙН показує, що вони повністю збігаються з обчисленнями, проведеними вище. Звідси випливає, що обчислення проведені правильно.
Лінійна апроксимація має вигляд:=- 35,1556 + 14,0781x
Квадратична апроксимація має вигляд:
y=- 0,61511 + 0,55425x - 0,90104x 2
Експоненціальна апроксимація має вигляд:
y=2,42137 е 0,35140x
