Лабораторная работа №2




НазваниеЛабораторная работа №2
страница6/6
Дата публикации22.10.2014
Размер0.8 Mb.
ТипЛабораторная работа
5-bal.ru > Информатика > Лабораторная работа
1   2   3   4   5   6

Вычисление значений степенного тренда и прогнозных значений

Экспоненциальный и логарифмический тренды могут использоваться для прогнозов в случаях, если в различных частях кривой это функции имеют очень разный темп роста значений. Линия экспоненциального тренда начинает расти медленно, а затем постоянно растет с возрастающим темпом. Логарифмический тренд вначале резко возрастает, а затем медленно сбавляет темп роста. На практике в большинстве статистических показателей таких резких изменений динамики не наблюдается, чаще это модели с некоторым стабильным ростом. В этом случае логично использовать линейный тренд, но если он дает плохую аппроксимацию, то следует попробовать использовать степенной тренд.

Регрессионное уравнение для степенного тренда имеет следующий вид:

y = mxb,

где зависимое значение y — функция независимого значения x, значения m — коэффициенты, соответствующие каждой независимой переменной x, а b — постоянная. Зная эти коэффициенты и значения независимой переменной x, можно получить значения соответствующих точек на линии тренда.

В Excel нет функции, позволяющей непосредственно вычислить значения коэффициентов m и b. В этом случае можно «сгладить» степенную кривую, преобразовав масштаб переменных x и y в логарифмическую шкалу, т.е. преобразовать степенную регрессию в линейную с помощью функции LN(). В этом случае в примере на рисунке 7 формула массива для анализа зависимости объема продаж от цены будет иметь вид: {=ЛИНЕЙН(LN(B2:B10);LN(A2:A10))}

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l13_7.gif
Рисунок 7 – Расчет коэффициентов степенного тренда

Полученный коэффициент b в регрессионном уравнении применяется как экспонента, поэтому делать преобразование обратное логарифмического не нужно. Коэффициент а  представляется в логарифмической форме, поэтому в ячейке G2 по формуле =EXP(F2) вычислено реальное значение коэффициента m, которое затем используется в формуле для вычисления прогнозных значений (рисунок 8).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l13_8.gif
Рисунок 8 – Расчет прогнозных значений степенного тренда

Использование множественного регрессионного анализа

Во множественном регрессионном анализе чаще всего решаются задачи «ЧТО-ЕСЛИ». Все описанные приемы регрессионного анализа можно применять для анализа нескольких независимых переменных.

На основе отчетных данных об объемах  продаж (рисунок 9), включающих расходы на рекламу и цену товара, проведем регрессионный анализ с помощью функции ТЕНДЕНЦИЯ(). Аргументы функции и результаты прогноза приведены на рисунках 10, 11.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l13_9.gif
Рисунок 9 – Окно функции Тенденция

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l13_10.gif
Рисунок 10 – Окно функции Тенденция

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l13_11.gif
Рисунок 11 – Результаты множественного регрессионного анализа

Корреляционный анализ в MS Excel

Наличие взаимосвязи между двумя явлениями, свойствами или показателями определяется с помощью корреляционного анализа. Коэффициент корреляции между двумя массивами переменных, например, x и y – это безразмерная величина в диапазоне от -1 до 1, определяющая степень линейной зависимости между x и y. Если коэффициент корреляции близок к +1, это означает наличие сильной положительной линейной зависимости между значениями исследуемых данных. Коэффициент корреляции близкий к -1 говорит о наличии сильной отрицательной линейной зависимостью между x и y. Коэффициент корреляции близкий к 0 означает слабую линейную зависимость между массивами переменных.

В MS Excel существует специальная функция КОРРЕЛ(), относящаяся к категории статистических. Функция КОРРЕЛ(), возвращает коэффициент корреляции меду интервалами ячеек массив1 и массив2.

Формат функции

=КОРРЕЛ(массив1;массив2), где 

массив1 — это ячейка интервала значений;

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

Замечания.

1. Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.

2. Если массив1 и массив2 имеют различное количество точек данных, то функция КОРРЕЛ() возвращает значение ошибки #Н/Д.

3. Если массив1 либо массив2 пуст, или если f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l13_12.gif(стандартное отклонение) их значений равно нулю, то функция КОРРЕЛ() возвращает значение ошибки #ДЕЛ/0!.

Пример. Определим зависимость активного населения страны и числа предприятий. Вариант решения приведен на рисунке 12.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l13_13.gif
Рисунок 12 –Вычисление коэффициента корреляции для двух массивов значений

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

Оценим степень корреляции годовой доходности акций трех компаний за ряд лет. Воспользуемся возможностями Пакета анализа, который является удобным инструментом для поиска корреляций. На вкладке Данные в группе Анализ в диалоговом окне выберем команду Корреляция (рисунок 13). В качестве входного интервала укажем диапазон исследуемых данных вместе с заголовками столбцов ($B$4:$D$30). Абсолютные ссылки Excel установит автоматически после выделения диапазона с помощью курсора. Установим флажок Метки в первой строке. Укажем верхнюю левую ячейку выходного интервала ($F$4), куда будут записаны результаты расчетов. На рисунке 200 приведен лист с исходными данными и матрицей коэффициентов корреляции.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l13_14.gif
Рисунок 13 – Диалоговое окно Корреляция

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l13_15.gif
Рисунок 14 – Вид рабочего листа с исходными данными и коэффициенты корреляции годовой доходности акций 

Как видно из рисунка, некоторые элементы матрицы оказались пустыми. Для того, чтобы полностью заполнить матрицу воспользуемся инструментом Специальная вставка:

1. Скопируем матрицу с помощью команды контекстного меню;

2. Щелкнем мышью в ячейке свободного диапазона и выберем в контекстном меню щелчком на кнопке Вставить вкладки Главная команду Специальная вставка (рисунок 15). В диалоговом окне Специальная ставка установим флажок транспонировать. После щелчка по кнопке ОК на листе отобразится транспонированная матрица (рисунок 16).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l13_16.gif
Рисунок 15 – Диалоговое окно Специальная вставка

1. Скопируем транспонированную матрицу.

2. Выделим верхнюю левую ячейку исходной матрицы и выполним команду Специальная вставка.

3. В диалоговом окне Специальная вставка установим флажок пропускать пустые ячейки и щелкнем по кнопке ОК.

Результат заполнения матрицы корреляции представлен на рисунке . Рассчитанные коэффициенты корреляции говорят о слабой положительной линейной зависимости между доходностью акций компаний «Электросвязь» и «Вымпел» и слабой отрицательной линейной зависимости между доходами компаний «Бином» – «Вымпел» и «Бином» – «Электросвязь». 

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l13_17.gif
Рисунок 16 – Результат заполнения матрицы коэффициентов корреляции

 

 

 

 

 
1   2   3   4   5   6

Похожие:

Лабораторная работа №2 iconЛабораторная работа. Получение и свойства оксидов, гидроксидов и солей
Лабораторная работа. Ряд напряжений металлов. Гальванические элементы. Электролиз юююююю

Лабораторная работа №2 iconЛабораторная работа по курсу «Теория языков программирования и методов...
Данная лабораторная работа предназначается для студентов специальности пвс изучающих «Теория языков программирования и методов трансляции»....

Лабораторная работа №2 iconЛабораторная работа №5
Лабораторная работа №5 включает 5 заданий. Для выполнения этих заданий необходимо ознакомиться с теоретическим материалом, приведенным...

Лабораторная работа №2 iconУрока физики по теме “ Измерение массы тела с помощью весов лабораторная...
Технологическая карта конструирования урока физики по теме “Измерение массы тела с помощью весов – лабораторная работа №3”

Лабораторная работа №2 iconЛабораторная работа
Уоу деревьев и кустарников, их биологией, значением в жизни человека, в мае –июне в период летней практики с многообразием травянистых...

Лабораторная работа №2 iconДата Группа
«Лабораторная работа Получение входных и выходных характеристик биполярного транзистора в схеме с оэ»

Лабораторная работа №2 iconЛабораторная работа 1
Цель работы: ознакомиться с топографической картой и научиться решать следующие задачи

Лабораторная работа №2 iconЛабораторная работа №14
Цель работы: определить экспериментально с помощью оборотного маятника ускорение свободного падения

Лабораторная работа №2 iconЛабораторная работа №4
Целью работы является изучение возможностей субд db2 по созданию и ведению баз данных

Лабораторная работа №2 iconКонспект открытого урока Предмет- биология
Методы и приемы: объяснительно иллюстративный, проблемный, лабораторная работа (исследовательский)


Учебный материал


При копировании материала укажите ссылку © 2013
контакты
5-bal.ru