Эконометрика / Лабораторные работы в Microsoft Excel/

2 Лабораторная работа 1 Основные описательные статистики С помощью генератора случайных чисел для дискретной случайной величины X создать выборку объема N=30. Открыть меню « Анализ данных » на вкладке « Данные ». ( Если его нет, то установить : Файл \ Параметры \ Надстройки ; выбрать Пакет анализа и нажать Перейти ; поставить флажок Пакет анализа и нажать ОК ). Выбрать Генерация случайных чисел. В появившемся окне задать параметры нормального распределения : среднее = {N варианта * 10 }; стандартное отклонение = {N варианта * 2 }. Отсортировать полученные значения в столбце A по возрастанию (вариационный ряд). Округлить значения (столбец B - функция ОКРУГЛ()). « Число_разрядов» выбрать самостоятельно.

3 Для полученной выборки x 1,..., x N объема N =30 вычислить 1. Оценку математического ожидания - выборочное среднее ( функция СРЗНАЧ ()) 2. Оценку дисперсии ( центральный момент второго порядка ) - выборочную дисперсию s 2, несмещенную оценку дисперсии s x 2 ( функция ДИСП. В ()) 3. Среднее квадратичное отклонение s x ( функция СТАНДОТКЛОН. В ()) 4. Стандартная ошибка среднего 5. Выборочный центральный момент 3- го порядка μ 3 * и несмещенную оценку μ 3 6. Выборочный центральный момент 4- го порядка μ 4 * 7. Коэффициент асимметрии ( функция СКОС ()) 8. Коэффициент эксцесса ( функция ЭКСЦЕСС ()) 9. Размах выборки

4 10. Среднее отклонение ( функция СРОТКЛ ()) 11. Относительное отклонение по модулю ( линейный коэффициент вариации ) 12. Мера точности 13. Вероятное отклонение 14. Мера изменчивости ( коэффициент вариации )

5 Вычислить параметры распределения с помощью Анализ данных \ Описательная статистика и сравнить с рассчитанными по формулам

6 Пример таблицы с вычисленными описательными статистиками Мода ( функция МОДА. ОДН ()) Медиана ( функция МЕДИАНА ()) = Пример таблицы, полученной при выполнении « Анализа данных » Примечание. Функция МОДА. ОДН () Возвращает наиболее часто встречающееся или повторяющееся значение в массиве или интервале данных.

7 Построить гистограмму с помощью Анализ данных \ Гистограмма

8 Пример гистограммы, построенной с помощью Анализ данных \ Гистограмма ( интервал Карманов выбирался автоматически ) Вычислить моду распределения по таблице частот. 1. Определить модальный интервал ( по максимальной частоте ). В примере : Определить значение моды по формуле где M 0 – значение моды ( в примере = 51.16), x 0 – нижняя граница модального интервала (43.4), h – величина интервала (12.2), f m – частота модального интервала (12), f m-1 – частота интервала, предшествующего модальному (5), f m+1 – частота интервала, следующего за модальным (8).

9 Лабораторная работа 2 Группировка данных Сгруппировать полученные в лабораторной работе 1 значения случайной величины X. Скопировать столбец X на новый рабочий лист ( Вставить \ значения ), например, в A2:A31 Выбрать число интервалов группировки. Рассчитать количество интервалов по формулам : ( Стерджесс ) ( Брукс и Каррузер ) ( Хейнхольд ) ( Максимов Ю. Д.) Рассчитать длину интервала Округлить h, например, один знак до запятой – до десятых ; два – до целых ; три или более – кратно 50 или 100 Рассчитать границы и середину интервалов

10 Вычислить Абсолютные частоты n i ( функция ЧАСТОТА. Относится к диапазону ячеек : выделить диапазон для результата, ввести формулу, нажать Ctrl+Shift+Enter ) Относительные частоты и Накопленные частоты Накопленные относительные частоты Выборочную функцию распределения вероятности

11 Вычислить Среднее значение ( для сгруппированных данных ) Дисперсию Среднее квадратичное отклонение s x Построить интегральную и дифференциальную функции для нормального закона распределения с полученными средним значением и дисперсией. Вычислить с помощью функции НОРМ. РАСП (), параметр Интегральная = 0 для дифференциального закона распределения = 1 для интегрального закона распределения Вычислить дифференциальную функцию распределения по формуле Примечание. У операции « унарный минус » выше приоритет, чем у ^, поэтому в формуле нужны скобки. Например, EXP(-((H2-$H$11)^2)…

12 Пример таблицы Построить гистограмму с помощью Анализ данных \ Гистограмма интервал карманов – как в функции ЧАСТОТА. ( в примере : G2:G6 ); Установить флажок Интегральный процент

13 Построить гистограммы Абсолютных и относительных частот Кумулятивных и относительных кумулятивных частот

14 Построить гистограммы Эмпирический дифференциальный и интегральный законы распределения Интегральную и дифференциальную функции для нормального закона распределения

15 Вычислить Основные описательные статистики ( лабораторная работа 1) При доверительной вероятности γ = 0.95 ( уровне значимости α = 0.05 ) найти Доверительный интервал для среднего значения по формуле t α,N 1 определяется с помощью функции СТЬЮДЕНТ. ОБР () с помощью функции ДОВЕРИТ. СТЬЮДЕНТ () – вычисляет « Анализ данных \ Описательная статистика » – поставить флажок Уровень надежности Доверительный интервал для дисперсии χ α,N 1 определяется с помощью функции ХИ 2. ОБР () Доверительный интервал для среднего квадратичного отклонения

16 Лабораторная работа 4 Линейная регрессия Даны две выборки, состоящие из N значений случайных величин X и Y, x 1, x 2, …, x N и y 1, y 2, …, y N. X независимая переменная, влияющая на Y. y = (y 1, y 2, …, y N ) отклик, x = (x 1, x 2, …, x N ) фактор, влияющий на отклик. 1. Построить уравнение регрессии Метод наименьших квадратов

17 Метод наименьших квадратов

18 Вычислить коэффициенты регрессии 1. Методом наименьших квадратов, решив систему линейных уравнений (*) 2. С помощью функций НАКЛОН (), ОТРЕЗОК () 3. С помощью функции ЛИНЕЙН () 4. Построив линию тренда на диаграмме 5. С помощью « Анализ данных \ Регрессия »

19 Выполнение работы Ввести исходные данные на рабочий лист Линейная регрессия Для решения системы ввести матрицу коэффициентов и правую часть системы, найти значения коэффициентов регрессии – решить систему с помощью функций МУМНОЖ (), МОБР () выделить две ячейки, ввести формулу и нажать Ctrl+Shift+Enter

20 Функции рабочего листа НАКЛОН (), ОТРЕЗОК (), ЛИНЕЙН () Выделить два столбца и пять строк, ввести формулу, нажать Ctrl+Shift+Enter

21 Построение диаграммы и линии тренда Выделить два столбца значений X и Y вместе с заголовками Вставить точечную диаграмму ( Тип диаграммы – Точечная ) Выделить на диаграмме ряд В контекстном меню выбрать Добавить линию тренда Изменить параметры линии тренда ( см. след слайд ) : Линейная ( установлено по умолчанию ), Показывать уравнение на диаграмме, Поместить на диаграмме величину достоверности аппроксимации R 2

22 Построение диаграммы и линии тренда

23 Анализ данных \ Регрессия

24 Анализ данных \ Регрессия Проверить, чтобы значения коэффициентов регрессии, найденные разными способами, совпадали !

25 2. Вычислить коэффициент корреляции Выборочная ковариация ( функция КОВАРИАЦИЯ. В ()) Коэффициент корреляции ( функция КОРРЕЛ (), Анализ данных \ Корреляция )

26 Вычисление коэффициента корреляции

27 3. Оценить уровень значимости уравнения регрессии Коэффициент детерминации В случае одной независимой переменной X F - статистика m – число параметров в уравнении регрессии, ( a 0, a 1 ), m = 2 ( сравниваем с F.ОБР. ПХ ( α = 0,05; k 1 = m – 1; k 2 = N – m = 23) = 4,28

28 Сумма квадратов, обусловленная регрессией (RSS) Сумма квадратов, обусловленная ошибкой (ESS) Общая сумма квадратов (TSS) Фактическая дисперсия Остаточная дисперсия Вычислить

29 4. Оценить значимость коэффициентов уравнения регрессии Несмещенная оценка дисперсии остаточной компоненты d jj - диагональные элементы матрицы C -1 ( обратной матрицы системы уравнений (*)) Стандартные ошибки коэффициентов уравнения регрессии Стандартная ошибка оценки t - статистика СТЬЮДРАСПОБР ( α = 0,05; N – m = 23) = 2,069 Доверительный интервал

30 5. Оценить значимость коэффициента корреляции

31 5. Найти доверительные интервалы для среднего и дисперсии

32 Лабораторная работа 5 Линейная регрессия для сгруппированных данных Даны две выборки, состоящие из K значений случайной величины X, x 1, x 2, …, x K и M значений случайной величины Y y 1, y 2, …, y M. Каждая пара значений (x j, y i ) встречается n ji раз. 1. Построить уравнение регрессии Метод наименьших квадратов

33 Выполнение работы Ввести исходные данные на рабочий лист Линейная регрессия – сгруппированные данные Для решения системы ввести матрицу коэффициентов и правую часть системы, найти значения коэффициентов регрессии – решить систему с помощью функций МУМНОЖ (), МОБР () Вспомогательный столбец H для расчета

34 Выполнение работы Сделать проверку

35 2. Построить уравнение регрессии Сделать самостоятельно по аналогии с п. 1

36 3. Вычислить коэффициент корреляции Выборочная ковариация Коэффициент корреляции Примечания При вычислении средних значений и средних квадратичных отклонений использовать формулы для сгруппированных данных ( функции СРЗНАЧ () и СТАНДОТКЛОН. В () не подходят ) Ковариацию вычислить, используя вспомогательный столбец

37 Сумма квадратов, обусловленная регрессией (RSS) Сумма квадратов, обусловленная ошибкой (ESS) Общая сумма квадратов (TSS) Фактическая дисперсия Остаточная дисперсия Вычислить

38

39 Лабораторная работа 6 Множественная линейная регрессия Даны три выборки, состоящие из N значений случайных величин X и Y и Z, x 1, x 2, …, x N ; y 1, y 2, …, y N и z 1, z 2, …, z N. 1. Построить уравнение регрессии Метод наименьших квадратов

40 Выполнение работы Ввести исходные данные на рабочий лист Множественная линейная регрессия Для решения системы ввести матрицу коэффициентов и правую часть системы, найти значения коэффициентов регрессии – решить систему с помощью функций МУМНОЖ (), МОБР () Вычислить коэффициенты регрессии при помощи функции ЛИНЕЙН ()

41 Анализ данных \ Регрессия

42 Анализ данных \ Корреляция

43 Вычислить F.ОБР. ПХ ( α = 0,05; k 1 = m – 1; k 2 = N – m = 22) = 3,44 СТЬЮДРАСПОБР ( α = 0,05; N – m = 22) = 2,074