Статистические функции в Microsoft Excel - polpoz.ru o_O
Главная
Поиск по ключевым словам:
страница 1
Похожие работы
Название работы Кол-во страниц Размер
Табличний редактор Microsoft Excel 1 145.75kb.
Объекты Microsoft Excel 77 Автоматизированный бланк 79 Модернизация... 1 27.44kb.
Курсы «Оператор пк». Microsoft Office Excel. Занятие 2 1 33.04kb.
Лабораторная работа №2 Создание и редактирование диаграмм в документах... 1 79.49kb.
Табличний редактор Microsoft Excel 1 21.45kb.
Видеокурс по Microsoft Excel 2007 предлагает вам познакомиться с... 1 157.18kb.
В формулах при обращении к ячейкам используется два способа адресации... 1 43.52kb.
«Составление и обработка материалов в программе Microsoft Excel. 1 172.67kb.
Вопросы, возникающие в процессе обучения и самостоятельной работы... 1 37.67kb.
Программа для работы с электронными таблицами, созданная корпорацией... 1 166.58kb.
Информационные технологии в профессиональной деятельности 2 1096.26kb.
Лабораторная работа №1 Интегрирование систем оду сергей Суровцев... 1 52.29kb.
1. На доске выписаны n последовательных натуральных чисел 1 46.11kb.

Статистические функции в Microsoft Excel - страница №1/1

Статистические функции в Microsoft Excel.
В эконометрике для проведения расчетов часто используют табличный процессор Microsoft Excel. Выясним его возможности для проведения эконометрического анализа. Microsoft Excel располагает почти всеми инструментальными средствами для проведения эконометрического анализа. Более подробно остановимся на использовании некоторых функций и программ, которые часто применяются в эконометрическом анализе.

Чтобы вывести список функций, необходимо выберите в меню «Формулы» команду «Вставить функцию», категория «Статистические» или нажать кнопку «fx» на панели инструментов. Здесь представлен довольно обширный список статистических функций. Нас интересуют в первую очередь функции, позволяющие выполнять корреляционно-регрессионный анализ. Вторая возможность имеется в опции «Анализ данных», меню «Данные», которая устанавливается, как правило, дополнительно. Рассмотрим вначале возможности, заложенные в Мастере функций.

С помощью функций Microsoft Excel можно рассчитать коэффициент корреляции. Для этого необходимо на вкладке «Формулы» вызвать «Вставить функцию» Категория: «Статистические»функция: «КОРРЕЛ»; в качестве «массива 1» выделить весь диапазон числовых значений одного из имеющихся показателей, в качестве «массива 2» выделить весь диапазон числовых значений второго показателя.

С помощью функций Microsoft Excel можно определить параметры однофакторных и многофакторных линейных и нелинейных функций. Однако нелинейные функции сначала необходимо привести к линейному виду. Существует несколько способов определения параметров уравнений регрессий.



  1. С помощью статистических функций «ОТРЕЗОК» и «НАКЛОН». Функция «Наклон» служит для определения параметра b уравнения регрессии, а функция «Отрезок» – для определения свободного члена уравнения – а. Для того, чтобы определить параметры уравнения а и b необходимо в меню «Формулы» вызвать «Вставить функцию» Категория: «Статистические»функция: «НАКЛОН»; в строку «Известные значения у » выделить весь диапазон числовых значений показателя «у», в строку «Известные значения х» выделить весь диапазон числовых значений показателя «х».

В меню «Формулы» вызвать «Вставить функцию» Категория: «Статистические»функция: «ОТРЕЗОК»; в строку «Известные значения у » выделить весь диапазон числовых значений показателя «у», в строку «Известные значения х» выделить весь диапазон числовых значений показателя «х».

  1. С помощью статистической функции «Линейн». Практически все эконометрические модели можно идентифицировать с помощью этой функции. Прежде чем начать работать с этой функцией необходимо в Microsoft Excel выделите область пустых ячеек 5 на 2 (5 строк и 2 столбца). Далее в меню «Формулы» вызвать «Вставить функцию» Категория: «Статистические»функция: «Линейн» и нажать кнопку ОК. После этого появится диалоговое окно:



Известные значения у (Изв_знач_у): введите диапазон, который содержит данные результативной переменной;

Известные значения х (Изв_знач_х): введите диапазон, который содержит данные объясняющей переменной;

Константа – логическое значение, указывающее на наличие или отсутствие свободного члена в уравнении; при Константе = 1 свободный член рассчитывается обычным способе; при Константе = 0 свободный член равен 0. Мы будем принимать значение Константы равное 1.

Статистика – логическое значение, указывающее на возможность вывода дополнительной информации по регрессионному анализу. При Статистике = 1 дополнительная информация выводится; при Статистике = 0 выводятся только оценки параметров уравнения. Мы будем принимать значение Статистики равное 1.

Для запуска нажимаем 4 кнопки: Ctrl, Shift, Enter и мышкой на ОК.

Получаем результат в виде следующей таблицы:
Результаты регрессионного анализа


Значение коэффициента b

Значение коэффициента а

Среднеквадратическое отклонение b

Среднеквадратическое отклонение а

Коэффициент детерминации R2

Среднеквадратическое отклонение y

F - статистика

Число степеней свободы

Регрессионная сумма квадратов

Остаточная сумма квадратов


Функция «Линейн» имеет следующие достоинства:

  1. при изменении исходных данных происходит автоматический перерасчет этой функции;

  2. результаты расчетов содержат необходимую информацию, чтобы проверить достоверность модели и ее коэффициентов;

  3. можно получить результаты расчетов для любого количества факторов включаемых в модель;

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

Функция «Линейн» имеет следующие недостатки:

  1. процедура расчетов плохо запоминается;

  2. не выдается точечный интервальный прогноз;

  3. не выдаются доверительные интервалы уравнения регрессии;

  4. не выдаются критические значения критерия Фишера и Стьюдента.




  1. Для нахождения параметров показательной парной регрессии (не приводя ее к линейному виду) можно использовать статистическую функцию «ЛГРФПРИБЛ». Основные этапы ее построения аналогичны функции «Линейн». Диалоговое окно функции «ЛГРФПРИБЛ» выглядит следующим образом:




  1. С помощью статистического пакета «Анализ данных».

Для приведения нелинейных уравнений к линейному виду могут понадобиться Математические функции, такие как «LN» и «EXP».


Статистический пакет «Анализ данных».
В состав Microsoft Excel входит набор средств анализа данных, предназначенный для решения статистических и эконометрических задач, который называется пакетом анализа. Для проведения анализа данных с помощью этих инструментов следует указать входные данные и выбрать параметры; анализ будет проведен с помощью подходящей статистической или инженерной макрофункции, а результат будет помещен в выходной диапазон. Чтобы вывести список доступных инструментов анализа, выберите команду Анализ данных в меню Данные. Диалоговое окно выглядит следующим образом:


Рассмотрим нахождение параметров уравнений регрессии в MS Excel с помощью команды «Анализ данных».

Первым шагом необходимо установить на вашем компьютере Пакет анализа (если он еще не установлен). Для это нужно зайти в меню «Файл»  «Надстройки» далее выбрать «Пакет анализа» нажать кнопку «Перейти». После этого появится диалоговое окно, в котором необходимо поставить галочку напротив «Пакета анализа», а затем нажать ОК. После этого выберите в меню «Данные»  «Анализ данных»  «Регрессия» и нажмите кнопку ОК. Перед вами появится диалоговое окно, которое необходимо заполнить:

Входной интервал Y – это диапазон, который содержит данные результативной переменной;

Входной интервал Х - это диапазон, который содержит данные объясняющей переменной;

Метки – это флажок, который указывает содержит ли первая строка название столбцов;

Константа-ноль – это флажок указывающий на наличие/отсутствие свободного члена в уравнении;

Выходной интервал – интервал, в котором будут выведены результаты регрессионного анализа данных (достаточно указать только левую верхнюю ячейку будущего интервала);

Новый рабочий лист – результаты регрессионного анализа данных можно вывести на новый рабочий лист;

Новая рабочая книга - результаты регрессионного анализа данных можно вывести в новую рабочую книгу;

Дополнительно можно получить информацию и графики остатков, график нормальной вероятности.



Результаты регрессионного анализа будут называться «ВЫВОД ИТОГОВ» и содержать 3 таблицы:
1. Показатели таблицы «Регрессионная статистика»:

  • множественный R – коэффициент корреляции R;

  • R–квадрат – коэффициент детерминации R2;

  • нормированный R – нормированное значение коэффициента корреляции;

  • стандартная ошибка – стандартное отклонение остатков;

  • наблюдения – число исходных наблюдений.

2. Показатели таблицы «Дисперсионный анализ»:

  • столбец «F» – расчетное значение F-критерия Фишера Fр;

  • столбец «Значимость F» – значение уровня значимости, соответствующее вычисленному значению Fр.

3. Показатели таблицы 3:

  • ячейка на пересечении столбца «Коэффициенты» и строки «Y - пересечение» – значение параметра уравнения линейной регрессии а - «отрезок»;

  • ячейка на пересечении столбца «Коэффициенты» и строки «Переменная Х1» – значение параметра уравнения линейной регрессии b - «наклон».

Очень часто при построении множественных регрессионных моделей приходится использовать еще одну команду «Анализа данных»  «Корреляция», которая позволяет проверить модель на наличие мультиколлинеарных факторов, с помощью матрицы парных коэффициентов корреляции.



Для того, чтобы рассчитать матрицу парных коэффициентов корреляции необходимо в меню «Данные» вызвать «Анализ данных»  «Корреляция». В раскрывшемся диалоговом окне заполнить строки:



«Входной интервал» - выделить весь диапазон числовых значений исходных данных, включая результирующую и все объясняющие (факторные) переменные;

Если необходимо задать параметры вывода.

Рассмотрим нахождение параметров простых регрессий в статистическом пакете Stadia. Он позволяет построить регрессию любого вида. Воспользуемся в Stadia меню (рис. 3.5, рис. 3.6): Статист=F9/ Простая регрессия/тренд.

Рис. 3.5. Окно для работы с пакетом Stadia

Рис. 3.6. Выбор статистических методов в Stadia


Указываются результирующая и объясняющие переменные (рис. 3.7), и выбирается форма зависимости (рис. 3.8).



Рис. 3.7. Выбор типа переменных



Рис. 3.8. Выбор вида регрессии

Результат выдается в отдельный текстовый файл. После построения регрессии предлагается провести дальнейший анализ, построить график или вернуться к выбору новой модели (рис. 3.9).







Рис. 3.9. Дополнительный анализ модели


izumzum.ru