Изучение базовых методов и моделей анализа данных с использованием электронных таблиц MS Excel

Министерство образования и  науки РФ

ФГБОУ ВПО Казанский национальный исследовательский технический университет     им. А.Н. Туполева

Институт технической кибернетики  и информатики

Кафедра систем информационной безопасности

 

 

 

 

 

 

Отчет по лабораторной работе №5 по теме

«Изучение базовых  методов и моделей анализа  данных с использованием электронных  таблиц MS Excel»

 

 

 

 

 

 

 

Выполнил:

студент группы 4206

**********

Преподаватель:

Тумбинская М. В.

 

 

 

 

 

 

Вариант 1

Казань – 2013

 

Задание 1.1. Построение линии тренда и прогнозирование.

Для следующей таблицы (Табл.1.1): построить диаграмму, добавить в неё линейную и экспоненциальную линии тренда, вывести уравнения полученных линий тренда, а также величины достоверности аппроксимации R2 для каждой из них; используя уравнения линий тренда, получить табличные данные по средней успеваемости студента для каждой линии тренда за 9и 10 семестры.

 

Семестр	Средняя успеваемость студента
1	3,8
2	4
3	4,2
4	4
5	4,5
6	4,5
7	4,5
8	3,9
9	?
10	?

 

1) Для известных значений Семестра и Успеваемости построим диаграмму.

2) Выделим диаграмму. В главном меню выберем: Диаграмма->Добавить линию тренда. В Открывшемся окне выберем вид линии тренда «Линейная»

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3) Перейдём во вкладку «Параметры». Для того, чтобы прогнозировать значения для неизвестной части таблицы, установим параметр 2 для значения «Прогноз». Включить параметры «Показывать уравнение на диаграмме» и «Поместить на диаграмму величину достоверности аппроксимации (R^2)». Нажмём «ОК».

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4) Повторим пункты 2 и 3 для экспоненциальной линии тренда

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5) Заполним таблицу при помощи связанных формул, используя формулы линий трендов

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

6) Получим график с двумя линиями трендов и формулами для них.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Задание 2.1. Построение линий тренда и прогнозирование при помощи встроенных функций Microsoft Excel.

Для таблицы из задания 1 получить ряды данных для линейной и экспоненциальной линии тренда с использованием функций ТЕНДЕНЦИЯ и РОСТ; используя функции ТЕНДЕНЦИЯ и РОСТ, составить прогноз о средней успеваемости студента на 9 и 10 семестры; для исходных данных и полученных рядов данных построить диаграмму.

 

1) Выделяем диапазон ячеек D4:D11, который следует заполнить значениями функции ТЕНДЕНЦИЯ, соответствующими известным данным об успеваемости.

 

 

 

 

 

 

 

 

 

 

 

 

 

2) Вызываем команду Функция из меню Вставка. В появившемся диалоговом окне Мастер функций выделяем функцию ТЕНДЕНЦИЯ из категории Статистические, после чего щелкаем по кнопке ОК. Эту же операцию можно осуществить нажатием кнопки Вставка функции стандартной панели инструментов.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3) В появившемся диалоговом окне Аргументы функции введём в поле «Известные_значения_y» диапазон ячеек столбца «Успеваемость»; в поле «Известные_значения_х» – диапазон ячеек столбца «Семестр». Для того чтобы функция распространилась на весь массив нажмём комбинацию клавиш «Сtrl+Shift+Enter».

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4) Теперь спрогнозируем прибыль на 9 и 10 семестры. Для этого выделим диапазон ячеек столбца для функции ТЕНДЕНЦИЯ, соответствующие неизвестным значениям столбца «Успеваемость», и вновь вызовем функцию ТЕНДЕНЦИЯ. К параметрам функции прибавится диапазон значений новых значений поля «Семестр». Для того чтобы функция распространилась на весь массив нажмём комбинацию клавиш «Сtrl+Shift+Enter».

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5) Повторим вычислим значения функции РОСТ в следующем столбце аналогично пунктам 1-4. Получим результат.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

6) Итоговая таблица.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

7) Построим  графики

 

 

 

 

 

Итог: Я изучил базовые методы и модели анализа данных с использованием Microsoft Excel, научился строить графики функций по заданным значениям, анализировать их и даже прогнозировать будущие значения, используя линейную и экспоненциальную линии тренда и специальные функции «Тенденция» и «Рост».