Часть I.

Требуется:

1. Построить точечную диаграмму, предварительно отсортировав таблицу;

2. Выдвинуть гипотезу о виде функции зависимости;

3. Рассчитать параметры модели регрессии, построить тренды;

4. Оценить адекватность построенного уравнения по величине достоверности аппроксимации;

5. Рассчитать теоретические значения по модели и построить графики фактических и расчетных данных.

6. Создать отчет в Word по всем пунктам задания, используя экранные копии Excel.

7. Написать вывод о виде функции зависимости, наилучшим образом описывающей модель.

Пример. В исходной таблице произведем сортировку по столбцу С (число гостиниц и аналогичных средств размещения) по возрастанию (по алфавиту).

Рис. Сортировка по числу гостиниц

Отсортированная таблица представлена на рисунке:

Рис. Отсортированная таблица

По отсортированным данным, используя мастер диаграмм, построим точечную диаграмму (диапазон ячеек С1:D19).

Рис. Диаграмма по отсортированным данным

После построения диаграммы, вызовем контекстовое меню, щелкнув правой кнопкой мыши по одной из точек диаграммы, и выберем в нем команду Добавить линию тренда…

Рис. Вкладка Параметры линии тренда

Во вкладке Параметры линии тренда выберем Линейная и отметим флаги показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2). Чем R² ближе к 1, тем удачнее регрессионная модель. На диаграмме появляется линия тренда.

Рис. Диаграмма с линейной линией тренда

Чаще всего выбор производится среди следующих функций:

у = ах + b - линейная функция;

у = ах² + bх + с - квадратичная (полиномиальная) функция;

у = аln(х) + b - логарифмическая функция;

у = ае^bх - экспоненциальная функция;

у = ах^b - степенная функция.

Отобразим на диаграмме все возможные тренды.

Рис. Диаграмма с разными линиями тренда

Часть II.

Требуется: рассчитать основные характеристики случайных величин.

Для расчета основных характеристик случайных величин используются следующие функции: СРЗНАЧ() – возвращает среднее арифметическое своих аргументов, КОРЕНЬ() – возвращает значение квадратного корня, а также ДИСП() и КОРРЕЛ().

ДИСП() - Оценивает дисперсию по выборке (Рис.).

Синтаксис функции: ДИСП(число1;число2; ...).

Число1, число2,... — от 1 до 255 числовых аргументов, соответствующих выборке из генеральной совокупности.

Рис.. Аргументы функции Дисп

КОРРЕЛ() – возвращает коэффициент корреляции между интервалами ячеек «массив1» и «массив2». Коэффициент корреляции используется для определения взаимосвязи между двумя свойствами. Например, можно установить зависимость между средней температурой в помещении и использованием кондиционера (Рис.).

Синтаксис функции: КОРРЕЛ(массив1;массив2).

Массив1 — это интервал ячеек со значениями, Массив2 — второй интервал ячеек со значениями.

Рис.. Аргументы функции Коррел

Получим следующие результаты (Рис.):

Рис.. Результаты расчетов с использованием математических функций

Можно сделать вывод о том, что линейная зависимость между числом гостиниц и аналогичных средств размещения (ряд X) и числом ночевок в гостиницах и аналогичных средствах размещения (ряд Y) существует, т.к. коэффициент корреляции равен 0,93729 и .

Коэффициент корреляции значим, т.к. расчетный критерий Стъюдента больше табличного критерия: 10,7566 > 2.1190.

Рассчитаем коэффициент корреляции для исходных данных с помощью функции Корреляция пакета Анализ данных.

Вызвать окно Анализ данных можно с помощью команды Анализ данных меню Данные (Рис.).

Рис.. Анализ данных

Пакет Корреляция позволяет определить коэффициенты корреляции для n-го количества рядов данных. Выбор команды Корреляция вызывает окно Корреляция (Рис.).

Рис.. Окно Корреляция

Это окно содержит две панели Входные данные и Параметры вывода. Окно Входной интервал: предназначено для ссылки на диапазон, содержащий анализируемые данные. Эта ссылка должна состоять не менее чем из двух смежных диапазонов данных, расположенных по строкам или столбцам. Флаги Группирование: зависят от расположения данных в диапазоне. Флаг Метки в первой строке (Метки в первом столбце) устанавливается в том случае, если входной интервал включал название диапазонов. Если название диапазонов были включены в интервал, а данный флаг не выставлен, после нажатия кнопки Ок, Excel выдаст сообщение об ошибке «Входной интервал содержит нечисловые данные». Если входной диапазон не содержит меток, то необходимые заголовки в выходном диапазоне будут созданы автоматически.

Если результаты необходимо поместить на имеющемся листе, то нужно установить переключатель рядом с окном Выходной интервал:, а в самом окне следует ввести ссылку на левую верхнюю ячейку выходного диапазона.

Если установить переключатель рядом с окном Новый рабочий лист:, то в книге откроется новый лист и результаты анализа будут вставлены в него, начиная с ячейки A1. При необходимости в окно можно ввести имя нового листа. По умолчанию имя листа будет соответствовать следующему после последнего имеющегося в книге листа.

Если установить переключатель рядом с окном Новая рабочая книга, то откроется новая книга, и результаты анализа будут вставлены в нее, начиная с ячейки A1 на первом листе в этой книге.

Поскольку коэффициент корреляции двух наборов данных не зависит от последовательности их обработки, то выходная область занимает только половину предназначенного для нее места.

Ячейки выходного диапазона, имеющие совпадающие координаты строк и столбцов, содержат значение 1, так как каждая строка или столбец во входном диапазоне полностью коррелирует с самим собой.

Заполняем все необходимые поля окна Корреляция (Рис.).

Входной интервал – это данные, по которым необходимо провести корреляционный анализ, в данном случае это исходные данные по числу гостиниц и аналогичных средств размещения и числу ночевок в гостиницах и аналогичных средствах размещения (С2:D19). Строка 1 также указана во входном интервале, но в ней содержатся заголовки столбцов, поэтому ставим флаг Метки в первой строке.

В выходном интервале ставим Новый рабочий лист, в котором будут вынесены результаты расчета.

Рис.. Расчет коэффициента корреляции

Полученные данные абсолютно идентичны коэффициентам полученным с помощью функции КОРРЕЛ() (Рис.).

Рис.. Результаты расчета коэффициента корреляции

Пакет Описательная статистика предназначен для расчета основных статистических показателей. Окно Описательная статистика (Рис.) содержит:

Рис.. Описательная статистика

1. панель Входные данные, аналогичную панели в окне Корреляция;

2. панель Параметры вывода содержит указание на выходной интервал, аналогичный окну Корреляция;

3. флаг Итоговая статистика обеспечивает вывод в выходной интервал среднего, стандартную ошибку (среднего), медиану, мода, стандартное отклонение, дисперсию выборки, эксцесс, асимметричность, интервал, минимум, максимум, сумму и количество значений;

4. флаг Уровень надежности, установка которого выводит в выходной интервал строку для уровня надежности. Значение, введенное в поле, соответствует требуемому уровню надежности;

5. флаг К-тый наименьший и К-тый наибольший, установка которых выводит в выходной интервал строки для k-го наибольшего и k-го наименьшего значения для каждого диапазона данных. В соответствующем окне необходимо ввести число k. Если k равно 1, эта строка будет содержать минимум или максимум из набора данных.

Далее вызовем функцию Описательная статистика из пакета Анализ данных (Рис.).

Рис.. Описательная статистика пакета Анализ данных

Выставив все необходимые флаги, нажимаем кнопку Ок, и получаем таблицу описательных статистик (Рис.).

Рис.. Таблица описательных статистик

Полученные данные совпадают с данными, рассчитанными с помощью математических функций (математическое ожидание по x и по y, дисперсия по x и по y, среднее квадратическое отклонение по x и по y).