ч (зазвичай діапазон) відомих значень d . Якщо безліч Y - стовпець, то діапазон D повинен мати стільки ж рядків, скільки стовпець значень Y. Кількість стовпців діапазону D визначає кількість n невідомих коефіцієнтів m i регресійній моделі
K - логічне значення, яке вказує, чи потрібно, щоб константа b дорівнювала 0. Якщо K = 1 (має значення ІСТИНА), то b обчислюється звичайним чином. Якщо K = 0 (має значення БРЕХНЯ), то b покладається рівним 0. p> С - логічне значення, яке вказує, чи потрібно повернути додаткову статистику по регресії. Якщо С = 1 (ІСТИНА), то функція ЛИНЕЙН повертає додаткову регресійну статистику (додаткову інформацію про регресійної статистиці можна отримати скориставшись довідкою Excel). Якщо С = 0 (БРЕХНЯ) то функція ЛИНЕЙН повертає тільки коефіцієнти m і постійну b . p> У даний роботі математична модель якості зрізу y1 і сили вирубки y2 записують у вигляді полінома такого вигляду:
(3)
Тут x 1 - значення зазору в кодовому масштабі, x 2 - код матеріалу, z 1 - квадратична функція від x 1 .
У натуральному масштабі матриця плану експерименту виглядає наступним чином:
Номер опитаX 1 X 2 < span align = "justify"> Якість зрізу, y 1 Сила вирубки [кН], y 2 10,030219720,050119530,10119240,031310050,05129860,11196
Для обробки даних експерименту переходять до кодованому масштабом. У цьому завданню за методикою, викладеною в [1] отримано такі формули для переходу від натурального до кодованому масштабом:
(4)
Оптимізацію отриманих регресійних моделей можна здійснити двома способами: або візуально, побудувавши графіки, або використовуючи методи оптимізації, реалізовані в Excel.
Послідовність виконання
1. Запустити EXCEL
2. Перейменуйте аркуші книги таким чином: Лист1 - Модель, Лист2 - Сталь, Ліст3 - Мідь. Для цього необхідно натиснути правою клавішею миші по ярличку листа і в контекстному меню вибрати пункт Перейменувати.
. На аркуші Модель створити таблицю з вихідними даними експерименту і пояснюючими написами за таким зразком (при заповненні таблиць користуйтеся способами копіювання та автозаповнення):
ABCDEFGH1Ісследованіе операції вирубки листових образцов2Матріца плану в натуральному масштабе3N опитаX1X2410, 030520,050630,10740,031850,051960,1110 Матриця плану в кодованому масштабе11N опитаx1x2z1x1x2x2z1y1y2, кН12d1d2d3d4d5131219714211951531192164310017529818619619Коэффициенты регресійних моделей виду 20y = b + m1 * d1 + m2 * d2 + m3 * d3 + m4 * d4 + m5 * d521m5m4m3m2m1b22y123y2
4. На аркуші Сталь та Мідь
ABCDEFGH1Варьірованіе зазором 2X1x1x2z1x1x2x2z1y1y2, кН30, 140,0950,0860,0770,0680,0590,04100,0311 Пошук оптимального зазора12X1x1x2z1x1x2x2z1y1y2, кН
5. Заповніть діапазон B13: D18 формулами (4) для переходу від натурального масштабу до кодованому. Для комірки B13 формула буде виглядати наступним чином: = 100 * (B4-0, 06)
6. Заповніть діапазон E13: F18 формулами для підрахунку творів x 1 x 2 і x 2 z 1
. В діапазон B22: G22 введіть формулу для визначення коефіцієнтів регресії для моделі якості зрізу (y1) за допомогою майстра функцій. Послідовність дій наведена нижче:
Виділити B22: G22
Меню Вставка-Функція (або кнопка Вставка функції)
Категорія - Статистичні, Функція - ЛИНЕЙН, Кнопка OK
Вікно Ізв_знач_y - G13: G18 (мишею або з клавіатури)
Вікно Ізв_знач_x - B13: F18 (мишею або з клавіатури)
Вікно Константа - 1
Вікно Стат - 0
Натиснути клавіатурну комбінацію Ctrl-Shift-Enter
Формула масиву уставиться у виділений діапазон...
