Обработка статистических данных
I. Задание 2 пункт1:
1. Занести на первый лист название группы, ФИО, № варианта, № задания, исходные данные (данные первого столбца - Х1, второго столбца - Х2, третьего - У).
2. Скопировать с Лист1 на Лист2 значения Х1 и У.
3. Выделить эти 2 столбца и отсортировать по значениям Х1 (Данные - Сортировка).
4. Определить, есть ли взаимосвязь между двумя столбцами значений: Мастер функций – Статистические - Функция КОРРЕЛ. В качестве первого массива ввести адреса ячеек со значениями Х1, массив2 – адреса ячеек со значениями Y. Если результат > ±0,5 – значения взаимосвязаны.
5. Построить точечную диаграмму.
6. Выделить любую точку на диаграмме - Диаграмма - Добавить линию тренда.
7. На вкладке Тип выбрать тренд.
8. На вкладке Параметры поставить флажки ( V ) - выводить уравнение на диаграмме и выводить величину достоверности аппроксимации R2
9. Выполнить пункты 6-8 для каждой линии тренда.
10. Из всех зависимостей выбрать лучшую (R2 ближе к 1) и по полученной формуле рассчитать столбец У расчетное.
11. Скопировать с Лист1 на Лист3 значения Х2 и У.
12. Выделить эти 2 столбца и отсортировать по значениям Х2 (Данные - Сортировка).
13. Повторить пункты 4 - 10.
II.Задание 2 пункт 2:
1. Скопировать с Лист1 на Лист4 значения Х1, Х2 и У. Необходимо получить линейную зависимость вида:
У=А1*Х1+А2*Х2+В,
т.е. необходимо найти коэффициенты А1, А2, В.
2.
 |
Сервис - Анализ данных - Регрессия. (при отсутствии - Сервис- Надстройка -Пакет Анализа).
3. Ввести:
- входной интервал Y - диапазон ячеек с У, включая заголовок;
- входной интервал Х - диапазон ячеек с Х1 и Х2, включая заголовок;
- поставить флажок Метки;
- вывод результата - на новый лист.
 |
Появится новый лист с результатами работы пакета анализа. На листе результатов регрессии:
4. Скопировать полученные коэффициенты на лист с данными (лист 4) и записать полученное уравнение:
5. С листа 2 на лист 4 скопировать отсортированные значения Х1 в столбец.
6. С листа3 скопировать отсортированные значения Х2 на лист4 в строку следующим образом: на листе 3 выделить Х2 - Правка - копировать, на листе 4 выделить ячейку, с которой будут начинаться значения Х2 - Правка - Специальная вставка - Транспонировать.
7. Рассчитать значения У по полученной формуле. Например, если:
ячейка C2 - коэффициент a1, ячейка D2 - коэффициент a2, ячейка B2 - b
ячейка A8 и ниже - значения x1, ячейка В7 и правее - значения x2,
 |
то формула имеет вид:
=$C$2*$A8+$D$2*B$7+$B$2
8. Выделить все данные и построить поверхность (Мастер диаграмм)
III. Задание 2 пункт 3:
В полученные уравнения (наилучшие уравнения на листе 2, 3 и полученная линейная зависимость на листе 4) подставить найденные значения коэффициентов и заданные значения Х1 и Х2, получив прогнозные значение У.