Розрахунок курсової роботи в табличному процесорі Excel

• Для продовження розрахунків за допомогою Exсel необхідно створити новий лист Microsoft Excel з ім'ям у форматі группа_фамилия студента.xls (наприклад 4ПС1Иванов.xls).

• Відкрити документ і виділивши всі осередки встановити шрифт 14 пт Times New Roman. Це необхідно для спрощення перенесення результатів розрахунку в Microsoft Word при оформленні розрахункової частини КР.

• У лівому верхньому кутку першого листа ввести число дослідів в матриці N і число повторних дослідів m. Щоб уникнути надалі невідповідностей, настійно рекомендується вводити дані в осередки згідно методичних вказівок і приведених скриншотов.

• По п.1.2 методичних вказівок (МУ) визначити верхній, нижній і основний рівні чинника. Заповнити за своїми даними таблицю аналогічну 2.1 МУ (Рис. 1.1). Звертаємо увагу на те, що створення таблиці починається з осередку А21.

• Д ля форматування умовних позначень (наприклад щоб ввести підрядковий індекс) необхідно виконати команду Формат осередки в меню Формат (або кликнути ікону на панелі інструментів). Тут на вкладці Шрифт можна відформатувати символ, поставивши галочок в полі Видозміна -надстрочный або підрядковий.

Рис.3.1 Зразок оформлення таблиці 2.1

• Потім створити таблицю аналогічну таблиці 2.2 методичних вказівок. У стовпцях чинників Х0.Х₃ замість + і – ввести 1 і -1 відповідно (див. Рис. ).

• Для розрахунку середнього арифметичного (у скриншоте Yu(ср)) необхідно скористатися функцією СРЗНАЧ. Формулу можна ввести уручну або скористатися командою Вставка функції fx. Для вставки необхідно встановити курсор в осередок I9 і кликнути значок fx(Вставка функції) біля рядка формул. У віконці категорії вибрати Статистичні, прокрутити список формул і вибрати СРЗНАЧ. У меню, що відкрилося, вибрати дипазон обчислень (у даному прикладі F9:H9). Розповсюдити формулу на весь стовпець.

• Розрахувати дисперсію S2u(Y) за допомогою функції ДІСП. Розрахувати суму дисперсій в осередку J17с допомогою функції СУМ.

• По п. 2.4.1 МУ розрахувати коефіцієнти регресії b0.b₁₂₃. Розрахункові формули вводити в осередки М9:м16.

• Виходячи з розрахункових даних записати рівняння регресії (см Рис. 3.1 ,б)

Рис. 3.1 Зразок оформлення таблиці 2.2

Рис 3.1.

• По п.2.4.2 МУ розрахувати дисперсії коефіцієнтів регресии S2(bi)

• Скласти таблицю для розрахункових значень критерію Стьюдента. При введенні формул в осередки V9:V16 слідує посилання на осередок Р14 робити не відносною, а абсолютною (тобто у вигляді $P$14). Інакше при розповсюдженні формули програма видасть помилку.

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

Рис. 3.1 ,б

• Переписати рівняння, залишивши тільки значущі коефіцієнти. У осередку AG2 ввести значення Nk (число коефіцієнтів в рівнянні після того, як були відкинуті незначущі)

• Знайти значення YRU необхідно використовуючи отримане рівняння регресії, в якому залишилися тільки значущі коефіцієнти. Розрахункові значення YRU занести в таблицю 2.3. Заповнити таблиці, розрахувавши решту значень (див. рис.3.2).

Рис 3.2

• Розрахувати S2НАД використовуючи дані таблиць. Розрахувати FR по ф-лам (2.* або 2.**) залежно від рассчитаного значення S2НАД.

• Знайти FT (табличне значення критерію Фішера) по прил. 4 (Севостьянов). У табл. додатки стовпці це значення f(S2y) – число мір свободи для більшої дисперсії (розраховувалося при перевірки значущості коефіцієнтів по критерію Стьюдента), а рядки f(S2НАД) -число мір свободи для меншої дисперсії f(S2НАД)= N-Nk. (Nk – число коефіцієнтів в рівнянні після, того як були відкинуті незначущі члени рівняння, значення в осередку AG2)

Наприклад f(S2y)=16, f(S2НАД)=3. Стовпця f(S2y)=16 ні, є тільки стовпці f(S2y)=15 і 20. Тому вибираємо значення FT на перетині стовпця 15 і рядки 3 (FT=8,69).

• Порівняти значення FT і FR. Зробити виводи про адекватність моделі.

Обробка результатів експерименту. Графічна інтерпретація результатів розрахунку.

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

1. Переписуємо рівняння залишивши тільки коефіцієнт один з чинників – наприклад Х1 (якщо він значущий і не був відкинутий на попередніх етапах обробки даних).

2. У діапазоні зміни цього чинника вибираємо декілька крапок. По-перше це верхній, нижній і основний рівні чинника. У межах між верхнім і нижнім рівнями чинника, вибрати ще дві крапки (всього виходить 5 крапок), підставляємо послідовно ці значення в рівняння і отримуємо відповідно 5 значень YRU. По цих значеннях будуватися графік, при цьому по горизонтальній осі відкладаються значення Х1_, а по вертикальній – відповідні їм YRU (див. Рис. !!!!!!). У 21-му рядку розрахункового файлу приведені для прикладу розрахункові формули для побудови графічних залежностей.

3. Операції по пп.1-2 повторюємо для решти чинників – Х2, Х3., якщо вони значущі, і не були відкинуті на попередньому етапі обробки даних.

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

Рис. 3.3