Учебно-методическое пособие для студентов экономического и физико-математического факультетов (стр. 3 из 16)
Для выбора и подключения надстройки необходимо выбрать пункт меню Сервис, Надстройки. После этого появится диалоговое окно, изображенное на рисунке 1.
Все дополнительные возможности, которые осуществляют статистический анализ данных, находятся в надстройке Пакет анализа, поэтому данную надстройку необходимо отметить, то есть поставить галочку напротив соответствующего пункта. После нажатия на кнопку OK ничего визуально не изменится, однако после повторного выбора пункта меню Сервис появляется дополнительный подпункт Анализ данных. Выбор данного пункта приводит к появлению диалогового окна (рис. 2).
Рис. 1
Рис. 2
4
Выбрав конкретный инструментарий анализа и нажав на кнопку Справка, можно получить исчерпывающую информацию по данному инструменту от небольшой теоретической части до полного описания всей необходимой входной информации.
Инструментарий Генерация случайных чисел позволяет сгенерировать множество значений случайной величины, имеющей какой-либо закон распределения. Выбор данного инструмента приведет к появлению диалогового окна (рис. 3):
Рис. 3
Рассмотрим вначале обязательные параметры, которые необходимо задать при работе с данной надстройкой.
Во-первых — это тип распределения. Имеется возможность выбрать Нормальное, Равномерное, Пуассоновское, Биноминальное и некоторые другие виды распределений. При этом для каждого распределения необходимо задавать свои параметры. Мы в дальнейшем будем рассматривать случайные величины, имеющие нормальное и равномерное распределение. При выборе равномерного распределения в качестве параметра необходимо задать интервал, а при нормальном распределении необходимо задать Среднее и Стандартное отклонение.
Во-вторых — количество генерируемых чисел. Это можно сделать двумя способами: указать число строк и столбцов, Число переменных — число столбцов, а Число случайных чисел — число строк в которых разместятся сгенерированные числа. В данном случае набор случайных чисел будет помещен на новый лист. Однако часто необходимо получить набор случайных чисел в некотором диапазоне на рабочем листе. Для этого воспользуемся пунктом Параметры вывода, который задает месторасположение генерируемых чисел. В этом случае весь указанный диапазон на исходном листе будет заполнен случайными числами. Например, указав Выходной интервал в виде $B$5:$C$11, получим 14 случайных чисел, расположенных в этих ячейках.
Кроме вида распределения и количества случайных чисел можно менять параметр — Случайное рассеивание. В качестве значения данной опции указывается произвольное целое число. Данное значение необходимо для того, чтобы получать одинаковый набор случайных чисел.
Инструментарий Выборка позволяет сформировать какую-либо выборку из имеющегося набора чисел, при этом Параметры вывода имеют тот же самый смысл, то есть место, куда будет выдаваться значение. Входной интервал задает всю генеральную совокупность, из которой будет осуществлён выбор. Метод выборки задает способ формирования выборки: периодическая или случайная. В периодической выборке задается период и каждое последующее число с номером кратным периоду будет скопировано в выборку. Процесс создания выборки прекратится при достижении конца входного диапазона. В случайной выборке задается только число значений в конечной выборке, при этом любое исходное значение может быть выбрано более одного раза.
Предположим, что сгенерирована совокупность случайных чисел, отвечающая нормальному закону распределения с математическим ожиданием 0 и среднеквадратичным отклонением 1, которая содержит 100 строк и 100 столбцов, и помещена на 4-м листе. Тогда для того, чтобы выбрать 20 чисел и поместить их на новый лист можно указать параметры диалогового окна Выборка, показанные на рисунке 4.
Рис. 4
Надстройки Корреляция и Ковариация позволяют создавать корреляционную и ковариационную таблицы. Данные надстройки имеют одинаковые диалоговые окна и отличаются только заголовком. На рисунке 5 представлено диалоговое окно, которое появляется после выбора инструментария корреляция.
Рис. 5
Пункты Входной интервал и Параметры вывода задаются аналогично другим надстройкам, поэтому подробно не будем на них останавливаться. Переключатель Метки в первой строке позволяет выдавать в сгенерированной таблице заголовки столбцов и строк.
В отличие от функций, вычисления значений корреляции и ковариации КОВАР и КОРЕЛЛ, надстройки вычисляют корреляционную и ковариационную матрицы, для произвольного количества случайных величин. Поскольку данные матрицы являются симметричными, то выводится только одна часть, при этом в корреляционной матрице на диагонали находятся единицы, а в ковариационной матрице на диагонали находятся значения дисперсий во всей генеральной совокупности.
Если для данных из таблицы 1 вызвать надстройку Ковариация, указав входной интервал в виде диапазона A1:C16 и опцию Метка в первой строке, а также задав некоторые параметры вывода, будет автоматически сгенерирована таблица 3.
Таблица 3
| Номер наблюдения | Цена x (р.) | Спрос y (тыс.шт.) | |
| Номер наблюдения | 18,66667 | ||
| Цена x (р.) | 2,248 | 0,276116 | |
| Спрос y (тыс.шт.) | –17,2239 | –2,12699 | 20,85071 |
Нетрудно заметить, что полученное в данной таблице значение ковариации –2,12699 совпадает со значением полученными нами ранее посредством функции КОВАР, а значения дисперсий 0,276116 и 20,85071 отличаются, поскольку в данной таблице вычисляется значение дисперсии по всей генеральной совокупности.
Задания для самостоятельной работы
1. Для таблицы из приложения (номер варианта соответствует номеру вашего компьютера) найдите среднее значение, смещённую и несмещённую дисперсию, среднеквадратичное отклонение в каждом столбце.
2. Получите корреляционную и ковариационную таблицу для этих же данных. Сделайте заключения об имеющихся линейных связях.
3. На одном листе Excel сгенерируйте набор из 10000 случайных чисел, имеющих равномерное распределение на интервале от 0 до 10. Найдите значение среднего и дисперсии во всей таблицы случайных чисел, которую впоследствии будем ассоциировать со всей генеральной совокупностью. Это можно сделать посредством имеющихся в Excel формул. Создайте 10 выборок из данной генеральной совокупности по 20 элементов в каждой, используя 5 раз периодическую и 5 раз случайную выборки. Поместите каждую выборку на отдельный лист. С помощью статистических функций исследуйте данные выборки, а именно, найдите выборочное среднее, несмещённую (выборочную) и смещённую (по всей генеральной совокупности) оценки дисперсии.
Замечание
Если случайная величина X имеет равномерное распределение между значениями a и b, то математическое ожидание может быть вычислено по формуле
, а дисперсия 
. Цель: изучить возможности электронной таблицы Excel по обработке парной линейной регрессии.
Основные формулы и понятия:
у = a + b×х + u — модели парной линейной регрессии;
y = а + b×x — уравнение линейной регрессии;
— значение наблюдений;
— остаток в i-м наблюдении; 
— расчетное значение у в i-м наблюдении (точечный прогноз); 
) — суммы квадратов остатков; 
— уравнения для параметров регрессии; 
— общая сумма квадратов отклонений; 
— объясненная сумма квадратов отклонений; 
— необъясненная (остаточная) сумма квадратов отклонений; 
— коэффициент детерминации.