Примеры решения прикладных задач с помощью Microsoft Excel

Задача 1. Определение числовых характеристик случайных величин.

Пусть x₁, x₂, …, x_n – выборочные значения некоторой случайной величины. Требуется оценить числовые характеристики этой случайной величины:

- Среднее арифметическое (как оценку математического ожидания): (1)

- Дисперсию (2)

- Среднеквадратическое отклонение (стандартное отклонение): (3)

Указания к выполнению.

1. В столбце B1:B10 введите выборочные значения:

20,3 15.4 17.2 19.2 23.3 18.1 21.9 15.3 16.8 13.2.

2. Заполните следующие ячейки столбца А:

- В ячейку A11 введите текст среднее арифметическое;

- В ячейку A12 введите текст дисперсия;

- В ячейку A13 введите текст стандартное отклонение.

3. Введите формулы(1)-(3) в соответствующие ячейки B11, B12 и B13.

4. Для выборочных значений (4) постройте график.

Задача 2. Построение эмпирической функции распределения.

Пусть x₁, x₂, …, x_n – выборочные значения некоторой случайной величины. Требуется составить таблицу частот выборки:

Таблица.

Выборочные значения xi	Частота ni	Накопленная частота	Относительная частота ni/n	Накопленная относительная частота

Указания к выполнению.

1. В столбце A1:A30 введите выборочные значения:

20,4 16.5 19.7 20.4 14.3 20.4 16.5 14.3 20.4 19.7

14.3 19.7 16.5 16.5 14.3 22.8 20.4 12.5 11.1 20.4

19.7 14.3 14.3 19.7 19.7 12.5 19.7 19.7 20.4 22.8

2. Отсортируйте данные по возрастанию (т.е. постройте вариационный ряд) и подсчитайте частоту каждого выборочного значения.

3. На новом рабочем листе создайте вышеприведенную таблицу, заполнив колонки 1 и 2 «вручную». Для вычисления значений в колонках 3-5 используйте возможности Microsoft Excel.

4. Постройте эмпирическую функцию распределения выборки (гистограмму накопленных частот – колонки 3 и 5 таблицы).

Задача 3. Расчет коэффициентов линейной регрессии.

Пусть x₁, x₂, …, x_n – выборочные значения случайной величины X, y ₁, y₂, …, y_n – выборочные значения случайной величины Y, зависящей (линейно) от X. Требуется определить зависимость Y от X.

Построим модель

где коэффициенты a и b требуется оценить.

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

(5)

где (6)

Указания к выполнению.

1. В первой строке рабочего листа запишите заголовки колонок таблицы:

В А1 введите X;

В В1 введите Y;

В С1 введите a;

В D1 введите b.

2. Заполните столбец A2:A11 выборочными значениями случайной величины X:

-0.12 -0.05 0.83 0.97 1.14 1.25 1.86 2.18 2.53 3.05

3. Заполните столбец B2:B11 выборочными значениями случайной величины Y:

1.45 1.59 3.44 3.74 4.09 4.33 5.61 6.28 7.01 8.11

4. Используя формулы (5)-(6), вычислите коэффициенты a и b линейной зависимости. Результаты расположите в ячейках С2 и D2 с оответственно.

5. Решите ту же задачу (поиск линейного приближения), используя функции Microsoft Excel, определяющие линейное приближение зависимости ряда Y от ряда X. Результаты расположите в ячейках С3 и D3 с оответственно.

6. Сравните полученные двумя способами коэффициенты линейного приближения.