Статистический анализ рядов динамики в MS Excel
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ
РОССИЙСКОЙ ФЕДЕРАЦИИ
РОССИЙСКИЙ ЭКОНОМИЧЕСКИЙ УНИВЕРСИТЕТ им. Г.В. ПЛЕХАНОВА
КАФЕДРА ИНФОРМАЦИОННЫХ ТЕХНОЛОГИЙ
Курсовая работа по дисциплине
"Информационные технологии в
экономике"
на тему: "Статистический анализ рядов
динамики в MS Excel"
Выполнил
студент 1 курса Группы № «415» / дневное отделение
Факультета
математической экономики, статистики, информатики
Симанов
Максим Денисович
Научный
руководитель: профессор кафедры ИТ
Музычкин
Павел Арсенович
Москва 2015
Оглавление
Введение
. Общие сведения о рядах динамики
. Статистический анализ рядов динамики
.1 Основные статистические показатели рядов динамики.
.1.1 Показатели изменения уровней ряда динамики
.1.2 Средние показатели изменения уровней ряда динамики
. Связный анализ рядов динамики.
.1 Пакет анализа данных
.2 Корреляционный анализ рядов динамики.
.2.1 Расчет коэффициента парной корреляции.
.2.2 Расчет коэффициента множественной корреляции.
.3 Регрессионный анализ рядов динамики
.4 Методы анализа рядов динамики.
.5 Элементы интерполяции и экстраполяции.
.5.1 Интерполяция ряда динамики.
.5.2 Экстраполяция ряда динамики.
.6 Непараметрические коэффициенты связи.
. Встроенные функции MS Excel для анализа рядов
динамики.
.1 Основные статистические функции
.2 Функция ЛИНЕЙН.
.3 Функция КОРРЕЛ.
Заключение.
Список литературы
Приложение
Введение
В настоящее время наиболее широко используемым программным обеспечением
является программное обеспечение, работающее в среде Windows. Одним из
составляющих программного обеспечения среды Windows, является программа
Microsoft Excel, которая является мощным средством для работы с таблицами
статистических данных. Она позволяет упорядочивать, обрабатывать определенным
образом, графически представлять и анализировать различные виды статистической
информации.
Ряды динамики, статистические ряды, характеризующие изменение (развитие)
социально-экономических явлений во времени. Без анализа рядов динамики не
мыслимы экономические расчеты, прогнозы, связанные с поведением экономической
системы и различных ее составляющих.Excel содержит много встроенных функций,
использование которых значительно облегчает обработку статистической
информации, касающуюся рядов динамики. В Excel насчитывается 80 статистических
функций, благодаря которым достаточно просто и удобно проводить экономические и
статистические расчеты.
Статистические функции позволяют выполнять следующие действия:
· Проводить предварительную обработку данных;
· Рассчитывать характеристики положения и рассеивания;
· Проводить выравнивание рядов динамики с помощью различных
методов;
· Определять параметры законов распределения непрерывных
случайных величин;
· Проверять статистические гипотезы о параметрах распределения
и виде закона распределения случайных величин;
· Определять параметры законов распределения дискретных
случайных величин;
· Строить линейные и нелинейные уравнения регрессии, проводить
их анализ и давать прогнозные оценки результативного признака.
Целью данной курсовой работы является статистический анализ рядов динамики
в среде MS Excel. Для того чтобы наиболее полно раскрыть все возможности MS
Excel в качестве объекта исследования будут использованы значения нескольких
мировых фондовых индексов и курсов валют за определенный временной отрезок.
1. Общие
сведения о рядах динамики
Ряды динамики - статистические данные, отображающие развитие во времени
изучаемого явления. Их также называют динамическими рядами, временными рядами.
В каждом ряду динамики имеется два основных элемента:
показатель времени t;
соответствующие им уровни развития изучаемого явления y;
В качестве показаний времени в рядах динамики выступают либо определенные
даты (моменты), либо отдельные периоды (годы, кварталы, месяцы, сутки).
Уровни рядов динамики отображают количественную оценку (меру) развития во
времени изучаемого явления. Они могут выражаться абсолютными, относительными
или средними величинами.
Ряды динамики различаются по следующим признакам:
По времени. В зависимости от характера изучаемого явления уровни рядов
динамики могут относиться или к определенным датам (моментам) времени, или к
отдельным периодам. В соответствии с этим ряды динамики подразделяются на
моментные и интервальные.
Моментные ряды динамики отображают состояние изучаемых явлений на
определенные даты (моменты) времени.
Особенностью моментного ряда динамики является то, что в его уровни могут
входить одни и те же единицы изучаемой совокупности. Хотя и в моментном ряду
есть интервалы - промежутки между соседними в ряду датами, - величина того или
иного конкретного уровня не зависит от продолжительности периода между двумя
датами. Поэтому при суммировании уровней моментного ряда может возникнуть
повторный счет.
Посредством моментных рядов динамики в торговле изучаются товарные
запасы, состояние кадров, количество оборудования и других показателей,
отображающих состояние изучаемых явлений на отдельные даты (моменты) времени.
Интервальные ряды динамики отражают итоги развития (функционирования)
изучаемых явлений за отдельные периоды (интервалы) времени.
Каждый уровень интервального ряда уже представляет собой сумму уровней за
более короткие промежутки времени. При этом единица совокупности, входящая в
состав одного уровня, не входит в состав других уровней.
Особенностью интервального ряда динамики является то, что каждый его уровень
складывается из данных за более короткие интервалы (субпериоды) времени. При
прочих равных условиях уровень интервального ряда тем больше, чем больше длина
интервала, к которому этот уровень относится.
Рис.
1 Пример интервального ряда
Свойство суммирования уровней за последовательные интервалы времени
позволяет получить ряды динамики более укрупненных периодов.
Посредством интервальных рядов динамики в торговле изучают изменения во
времени поступления и реализации товаров, суммы издержек обращения и других
показателей, отображающих итоги функционирования изучаемого явления за
отдельные периоды.
Статистическое отображение изучаемого явления во времени может быть
представлено рядами динамики с нарастающими итогами. Их применение обусловлено
потребностями отображения результатов развития изучаемых показателей не только
за данный отчетный период, но и с учетом предшествующих периодов. При
составлении таких рядов производится последовательное суммирование смежных
уровней . Этим достигается суммарное обобщение результата развития изучаемого
показателя с начала отчетного периода (года, месяца, квартала и т. д.).
Ряды динамики с нарастающими итогами строятся при определении общего
объема товарооборота в розничной торговле. Так, обобщением товарно - денежных
отчетов за последние операционные периоды (пятидневки, недели, декады и т. д.).
По форме представления уровней. Могут быть построены также ряды динамики,
уровни которых представляют собой относительные и средние величины. Они также
могут быть либо моментными либо интервальными .
В интервальных рядах динамики относительных и средних величин
непосредственное суммирование уровней само по себе лишено смысла, так как
относительные и средние величины являются производными и исчисляются через
деление других величин.
По расстоянию между датами или интервалам времени выделяют полные или
неполные ряды динамики.
Полные ряды динамики имеют место тогда, когда даты регистрации или
окончания периодов следуют друг за другом с равными интервалами. Это
равноотстоящие ряды динамики. Неполные - когда принцип равных интервалов не
соблюдается.
По числу показателей можно выделить изолированные и комплексные
(многомерные) ряды динамики. Если ведется анализ во времени одного показателя,
имеем изолированный ряд динамики. Комплексный ряд динамики получается в том
случае, когда в хронологической последовательности дается система показателей,
связанных между собой единством процесса или явления.
2. Статистический
анализ рядов динамики
2.1 Основные статистические показатели рядов динамики
2.1.1 Показатели
изменения уровней ряда динамики
Анализ скорости и интенсивности развития явления во времени
осуществляется с помощью статистических показателей, которые получаются в
результате сравнения уровней между собой. К таким показателям относятся:
· абсолютный прирост;
· коэффициент роста;
· темп роста;
· темп прироста;
· абсолютное значение одного процента прироста.
Возможны два варианта сравнения уровней рядов динамики:
при первом варианте каждый уровень сравнивают с одним уровнем (база
сравнения);
при втором варианте каждый уровень сравнивают с предшествующим уровнем
(база сравнения все время изменяется)
Таблица
1
|
Показатель
|
Базисный
|
Цепной
|
|
Абсолютный прирост
|
 
|
|
|
Коэффициент роста
|
 
|
|
|
Темп роста
|
 
|
|
|
Темп прироста
|
 
|
|
|
Абсолютное значение одного
процента прироста
|
|
|
Пример №1. Пусть имеется динамика изменения индекса Nasdaq100 за период с
17.03.2005 по 20.04.2005. Необходимо рассчитать основные аналитические
показатели для данного ряда динамики.
Решим задачу для базисных показателей. База сравнения - показатели на
17.05.2005. Тогда алгоритм действий следующий:
Записываем
формулу для расчета в ячейку, которая соответствует 18.05.2005, при этом делаем
фиксацию ячейки, являющуюся базой сравнения (используем индекс $ для создания
абсолютной ссылки на ячейку) 
- для
показателя абсолютный рост, аналогично для остальных показателей.
Затем,
чтобы не писать формулы несколько раз, выделяем ячейку и, удерживая её за
нижний правый угол протаскиваем до ячейки, соответствующей 20.04.2005.
Теперь
решим задачу для цепных показателей. Алгоритм действий следующий:
Записываем
формулы для расчета каждого показателя на 18.05.2005 и 19.05.2005. Для
показателя абсолютного прироста формула будет иметь вид: 
; 
.
Затем
выделяем ячейки с формулами и с помощью протаскивания находим остальные
показатели.
В
результате должны получиться значения представленные на рис.2
Современную
статистику невозможно представить без применения графиков. Выразительность,
доходчивость, лаконичность, универсальность, обозримость графических
изображений сделали их очень важной составляющей анализа статистической
информации. График помогает быстро передать суть рассматриваемой задачи. С
помощью графиков можно превратить данные электронной таблицы в картинку,
которая дает возможность легко сравнить или сразу увидеть тенденцию.
Рис.
2 Показатели изменения рядов
В
MS Excel заложено большое количество функций, позволяющих построить необходимый
статистический график. Построим диаграмму, использовав данные примера №1. Пусть
необходимо графически отобразить темп роста индекса Nasdaq100 за изученный
период. Алгоритм построения следующий: задаем диапазоны данных для построения
диаграммы (диапазоны, содержащие даты и темпы роста) 
щелкните левой кнопкой мыши по инструменту Мастер
диаграмм переходим к следующему шагу и задаем тип диаграммы (в
данном случае выбираем объемную гистограмму) переходим к следующему шагу, на
котором задаем название диаграммы, и название осей размещаем диаграмму на новом листе. Получившаяся
диаграмма представлена на рис.3.
Очень
важным понятием при построении диаграмм является понятие тренда. Тренд - это
общее направление изменения явления во времени. Построив линию тренда, можно
ответить на вопрос о существовании тенденции развития изучаемого явления.
Алгоритм
построения тренда следующий:
1. Щелчок левой кнопкой мыши на построенный ряд значений;
2. Правой
кнопкой мыши по выделенному объектув
появившемся меню добавить линию тренда;
. Выбираем
тип линии тренда, наиболее точно соответствующий изучаемому явлению;
. Устанавливаем
параметр на отображение уравнения тренда.
Рис.
3 Темп роста индекса Nasdaq100
2.1.2 Средние
показатели изменения уровней ряда динамики
Помимо показателей, о которых было рассказано в предыдущей главе, для
анализа изменений уровней ряда динамики рассчитываются средние показатели. К
средним показателям относятся:
Средний уровень интервального ряда динамики, к которым относятся
исследуемые в данной работе ряды индексов и курсов валют, рассчитывается по
формуле простой арифметической средней. Средний уровень интервального ряда
динамики находится по формуле:
Средний
абсолютный прирост - этот показатель дает возможность установить, насколько в
среднем на единицу времени должен увеличиваться уровень ряда (в абсолютном
выражении), чтобы отправляясь от начального уровня за данное число периодов,
достигнуть конечного уровня. Для его определения используется формула простой
арифметической средней:
Средний
коэффициент роста, показывающий, во сколько раз в среднем за единицу времени
изменился уровень динамического ряда.
Средний
темп роста представляет собой показатель среднего коэффициента роста,
выраженный в процентах.
Средний
темп прироста показывает на сколько процентов в среднем за единицу времени
изменялись уровни ряда динамики в течение определенного периода времени.
Алгоритм
расчета средних показателей ряда динамики следующий:
Занести
исходные данные в таблицу;
Создаем
вторую таблицу для средних показателей;
Как
в примере №1 записываем формулы для расчета средних показателей, наибольшую
трудность может вызвать формула расчета среднего коэффициента роста, для ее
ввода: Мастер функций Математические
Корень(Число) (если n-1 четное число, то достаточно прописать функцию Корень в
2 раза меньшее число раз, чем показатель степени; если же n-1 нечетное число,
то нужно воспользоваться помощью калькулятора).
3. Связный анализ
рядов динамики
3.1 Пакет анализа данных
Набор средств анализа данных, называемый Пакет анализа предназначен для
решения сложных статистических и инженерных задач.
Чтобы получить доступ к функциям пакета анализа необходимо:
В пункте меню Сервис выполнить команду Надстройки. На экране появится
окно диалога Надстройки;
Выбрать пункт Пакет анализа. Начнет загружаться пакет Анализ данных.
Затем на экране появится окно диалога.
Рис.
4 Пакет анализа данных
В окне диалога Анализ данных отображается список инструментов,
приведенный ниже.
· Однофакторный дисперсионный анализ.
· Двухфакторный дисперсионный анализ с повторениями.
· Двухфакторный дисперсионный анализ без повторений.
· Корреляция.
· Ковариация.
· Описательная статистика.
· Экспоненциальное сглаживание.
· Двухвыборочный F-тест для дисперсии.
· Анализ Фурье.
· Гистограмма.
· Скользящее среднее.
· Генерация случайных чисел.
· Ранг и персентиль.
· Регрессия.
· Выборка.
· Парный двухвыборочный t-тест для средних.
· Двухвыборочный t-тест с одинаковыми дисперсиями.
· Двухвыборочный t-тест с различными дисперсиями.
· Двухвыборочный z-тест для средних.
3.2 Корреляционный
анализ рядов динамики
Корреляционный анализ заключается в определении степени тесноты связи
между факторным и результативным признаком.
Различают следующие виды корреляционной зависимости:
парная корреляция, при которой изучается зависимость результативного
признака от одного факторного признака или двумя факторными признаками;
частная корреляция, при которой изучается зависимость результативного
признака от одного факторного признака при фиксированном значение других
факторных признаков;
множественная корреляция, при которой изучается зависимость
результативного признака от двух и более факторных признаков.
Связь между факторным и результативным признаком классифицируют по
аналитическому выражению, направлению и степени тесноты связи.
Корреляционный анализ дает возможность установить, ассоциированы ли
наборы данных по величине, то есть, большие значения из одного набора данных
связаны с большими значениями другого набора (положительная корреляция), или,
наоборот, малые значения одного набора связаны с большими значениями другого
(отрицательная корреляция), или данные двух диапазонов никак не связаны (корреляция
близка к нулю).
3.2.1 Расчет
коэффициента парной корреляции
Пример
№2. Имеются данные фондовых индексов Dowjons65 и Nasdaq100. Используя данные
представленные в таблице исходных данных (см. приложение) рассчитать
коэффициент парной корреляции между данными индексами.
Алгоритм решения следующий:
СервисАнализ данныхКорреляцияOK;
Выделяем
входной диапазон (Ссылка на диапазон, содержащий анализируемые данные. Ссылка
должна состоять не менее чем из двух смежных диапазонов данных, данные в
которых расположены по строкам или столбцам);
Устанавливаем
галочку напротив Метки в первой строке (Если первая строка исходного диапазона
содержит названия столбцов, установите переключатель в положение Метки в первой
строке. Если названия строк находятся в первом столбце входного диапазона,
установите переключатель в положение Метки в первом столбце. Если входной
диапазон не содержит меток, то необходимые заголовки в выходном диапазоне будут
созданы автоматически);
Устанавливаем
выходной диапазон (ссылка на левую верхнюю ячейку выходного диапазона.
Поскольку коэффициент корреляции двух наборов данных не зависит от
последовательности их обработки, то выходная область занимает только половину
предназначенного для нее места. Ячейки выходного диапазона, имеющие совпадающие
координаты строк и столбцов, содержат значение 1, так как каждая строка или
столбец во входном диапазоне полностью коррелирует с самим собой.) OK.
Если
каждый шаг был выполнен верно, то коэффициент корреляции должен быть равен
R2=0,954. Такое значение коэффициента парной корреляции свидетельствует о
сильной прямой зависимости между изучаемыми индексами.
статистический анализ динамика ряд
3.2.2 Расчет
коэффициента множественной корреляции
Пакет анализа данных позволяет решить более сложную задачу, связанную с
расчетом коэффициента множественной корреляции.
Пример №3. Допустим необходимо определить зависимость между USD и двумя
факторными признаками Forex и Euro.
Алгоритм действий следующий:
Выполняем те же шаги, что и в примере №3, только во входной диапазон
выделяем три столбца, со значениями признаков (USD, Euro, Forex).
Множественный коэффициент корреляции для двух факторных признаков
рассчитывается по формуле:
Рассчитав
парные коэффициенты между тремя признаками (см. таблицу ниже), подставляем
полученные коэффициенты в формулу множественного коэффициента корреляции.
Таблица
2
|
FOREX(x1)
|
USD(y)
|
EURO(x2)
|
|
FOREX(x1)
|
1
|
|
|
|
USD(y)
|
-0,82027962
|
1
|
|
|
EURO(x2)
|
0,695326432
|
-0,791664
|
1
|
3.3 Регрессионный
анализ рядов динамики
Регрессионный анализ состоит в определении аналитической связи при
которой изменение факторных признаков приводит к изменению результативного
признака.
Линейный регрессионный анализ заключается в подборе графика для набора
наблюдений с помощью метода наименьших квадратов. Регрессия используется для
анализа воздействия на отдельную зависимую переменную значений одной или более
независимых переменных.
Произвести регрессионный анализ в MS Excel можно как с помощью пакета
анализа данных, так и с помощью встроенной функции Линейн (о которой рассказано
в параграфе). Рассмотрим алгоритм провидения регрессионного анализа с использованием
пакета анализа данных:
СервисАнализ данныхРегрессияOK
Выбираем
Входной интервал Y (ссылка на диапазон анализируемых зависимых данных. Диапазон
должен состоять из одного столбца);
Выбираем
Входной интервал X (ссылка на диапазон независимых данных, подлежащих анализу.
Microsoft Excel располагает независимые переменные этого диапазона слева
направо в порядке возрастания);
Устанавливаем
Уровень надежности (флажок означает что в выходной диапазон будет включен
дополнительный уровень, в соответствующее поле введите уровень надежности,
который будет использован дополнительно к уровню 95%, применяемому по
умолчанию);
Устанавливаем
Выходной диапазон (ссылка на левую верхнюю ячейку выходного диапазона.
Необходимо отвести, по крайней мере, семь столбцов для итогового диапазона,
который будет включать в себя: результаты дисперсионного анализа, коэффициенты
регрессии, стандартную погрешность вычисления Y, среднеквадратичные отклонения,
число наблюдений, стандартные погрешности для коэффициентов.)
3.4 Методы
анализа рядов динамики
Очень часто проведя статистическое наблюдение, полученные данные не
отражают тенденцию изменения уровней рядов динамики. Для выявления основной
тенденции изменения уровней ряда динамики применяют следующие методы:
метод скользящей средней;
метод аналитического выравнивания.
Сглаживание ряда динамики с помощью скользящей средней заключается в том,
что вычисляется средний уровень из определенного числа первых по порядку
уровней ряда, затем - средний уровень из такого же числа уровней, начиная со
второго, далее - начиная с третьего и т.д. Таким образом, при расчетах среднего
уровня как бы «скользят» по ряду динамики от его начала к концу, каждый раз
отбрасывая один уровень в начале и добавляя один следующий.
Значение скользящей средней, при условии, что m - нечетное число,
определяют по формуле:
(*)
где
yi - фактическое значение i-го уровня;- число уровней,
входящих в интервал сглаживания (m=2p+1);t - текущий
уровень ряда динамики;- порядковый номер уровня в интервале сглаживания;- при
нечетном m 
.
Интервал
сглаживания, т.е. число входящих в него уровней т определяют, используя
следующее правило: если необходимо сгладить мелкие, беспорядочные колебания, то
интервал сглаживания берут по возможности большим; если же нужно сохранить
более мелкие волны и освободиться от периодически повторяющихся колебаний,
возникающих, например, из-за автокорреляции уровней, - интервал сглаживания
уменьшают.
Пример №4. Сгладить ряд динамики, дающий значения индекса Nasdaq100 с
17.03.2005 по 20.04.2005 (данные приводятся в таблице Динамика индекса
Nasdaq100).
В пакете Анализ данных инструмент Скользящее среднее пользуется для
расчета значений в прогнозируемом периоде на основе среднего значения
переменной для указанного числа предшествующих периодов.
Рассмотрим работу пакета анализа по определению скользящей средней ряда
динамики.
В категории Входные данные необходимо указать значение параметра Интервал
(число уровней m, входящих в интервал сглаживания).
Таблица
3
|
дата
|
17.03.05
|
18.03.05
|
19.03.05
|
20.03.05
|
21.03.05
|
22.03.05
|
23.03.05
|
24.03.05
|
25.03.05
|
26.03.05
|
27.03.05
|
28.03.05
|
29.03.05
|
30.03.05
|
31.03.05
|
01.04.05
|
|
Nasdaq100
|
1487,63
|
1484,40
|
1484,45
|
1465,09
|
1471,77
|
1469,94
|
1462,64
|
1458,48
|
1454,32
|
1472,71
|
1464,34
|
1491,74
|
1482,53
|
1469,35
|
1484,04
|
1487,26
|
|
02.04.05
|
03.04.05
|
04.04.05
|
05.04.05
|
06.04.05
|
07.04.05
|
08.04.05
|
09.04.05
|
10.04.05
|
11.04.05
|
12.04.05
|
13.04.05
|
14.04.05
|
15.04.05
|
16.04.05
|
17.04.05
|
18.04.05
|
19.04.05
|
20.04.05
|
|
1476,72
|
1483,75
|
1480,67
|
1499,71
|
1485,60
|
1490,18
|
1491,38
|
1478,55
|
1489,16
|
1461,68
|
1441,13
|
1408,59
|
1414,07
|
1401,14
|
1409,98
|
1406,26
|
1405,38
|
1404,50
|
1403,63
|
Ехсеl предлагает в качестве параметров вывода Вывод графика и вывод
Стандартных погрешностей, которые рассчитываются по формуле:
(**)
Если
предшествующих данных недостаточно для построения прогноза, Ехсеl возвратит
ошибочное значение #Н/Д.
Выходной
диапазон и исходные данные должны находиться на одном листе. По этой причине
параметры Новый лист и Новая книга недоступны.
Решим
пример, используя инструмент Скользящее среднее.
Алгоритм
действий следующий:
Формируем
таблицу исходных данных (см. табл. 1);
Сервис
Анализ данных Скользящее
среднее ОК;
Входной
интервал В7:B42;
Интервал:
5 (указывает на вид скользящей средней);
Выходной
интервал: B248;
Поставить
флажки для вывода графика и стандартных погрешностей;
ОК.
Ехсеl
представит результаты решения в следующем виде (см. рис. 5).
Другой
метод анализа рядов динамики - это метод аналитического выравнивания, который
заключается в том, что по исходным данным на основе математической функции,
которая наиболее точно отражает основную тенденцию изменения уровней ряда
динамики, разрабатывается теоретическая функция, зависящая от параметра
времени. Подбор математической функции осуществляется методом наименьших
квадратов.
Рис.
5 Выравнивание ряда динамики
В MS Excel можно использовать несколько различных математических функций
для анализа ряда динамики. Например, при помощи функции Линейн можно вычислить
коэффициенты уравнения функции, показывающей зависимость курса USD от времени и
затем с помощью данных коэффициентов рассчитать теоретические уровни данного
ряда динамики. Важно отметить, что рассматриваемая функция возвращает массив из
двух ячеек, поэтому перед вводом формулы нужно выделить две ячейки, а закончить
ввод формулы одновременным нажатием трех клавиш Ctrl+Shift+Enter.
3.5 Элементы
интерполяции и экстраполяции
3.5.1 Интерполяция
ряда динамики
Довольно часто ряд не является непрерывным, что очень сильно затрудняет
задачу анализа такого ряда динамики. Для решения такой задачи применяется метод
интерполяции. Интерполяция - это заполнение недостающих уровней внутри
исходного ряда динамики. Интерполяция может производиться на основе среднего
абсолютного прироста, среднего темпа роста и помощью аналитического
выравнивания. Она также основана на том или ином предположении о тенденции
изменения уровней, но характер этого прогноза несколько иной: здесь уже не
приходится предполагать, что тенденция, характерная для прошлого, сохранится и
в будущем.
При интерполяции считается, что ни выявленная тенденция, ни ей характер
не претерпели существенных изменений в том промежутке времени, уровень которого
нам не известен. Такое предположение обычно является более обоснованным, чем
предположение о будущей тенденции.
Пример
№5. Приводятся данные изменения курса Euro за определенный период (см.
приложение). При этом в ряду есть несколько отсутствующих уровней. Произвести
заполнение пропусков в уровнях ряда динамики для дальнейшего анализа.
Алгоритм действий следующий:
Создаем таблицу, состоящую из двух столбцов. Первый столбец содержит
даты, второй столбец значения уровней ряда динамики Euro. Ячейки, с
отсутствующими значениями, оставляем пустыми;
Выделяем
известные значения, начиная с первой ячейки до последней содержащей известное
значение уровня рядазатем, нажав правую кнопку мыши, протаскиваем серую
рамку на позиции, которые неизвестны в
появившемся контекстном меню, которое появится, выбираем пункт Линейное
приближение. В данном случае на основе линейной зависимости пустые ячейки будут
заполнены;
Выделяем
смежные заполненные ячейки до тех пор пока не встретиться пустая ячейка без
значенияповторяем ту же операцию, что и в предыдущем пункте и
т.д. В итоге получим непрерывный ряд динамики.
3.5.2 Экстраполяция
ряда динамики.
Составление надежных прогнозов - необходимое условие, во-первых,
регулирования социально-экономических процессов, во-вторых, достижения
коммерческого успеха. Важное значение при этом имеют статистические методы
экстраполяции.
Экстраполяция - это распространение выявленных при анализе рядов динамики
закономерностей развития изучаемого объекта на будущее (при предположении, что
выявленная закономерность, выступающая в качестве базы прогнозирования,
сохраняется и в дальнейшем).
Точность прогноза зависит от следующих факторов:
· стабильности условий, сформировавших в базисном ряду динамики
его основные компоненты;
· продолжительности базисного ряда динамики;
· сроков прогнозирования (периода упреждения).
В предыдущем параграфе, заполнив пропуски с помощью метода интерполяции,
был получен непрерывный ряд динамики. Как уже отмечалось, MS Excel поддерживает
несколько различных математических функций, позволяющих выявить тенденцию
развития процесса.
В данной работе составлен прогноз на основе линейной зависимости.
Алгоритм построения прогноза схож с алгоритмом, использованным в примере №6.
При прогнозировании выделяется весь массив данных и производится протаскивание
на требуемое количество позиций. MS Excel запросит о виде математической
функции, на основе которой делается прогноз.
Пример №7. Используя данные в примере №6 спрогнозировать изменение курса
Euro на 4 позиции вперед.
Если все выполнено согласно алгоритму, то должны получится данные
представленные в таблице «Прогноз и фактические значения курса Euro».
Прогноз и фактические значения курса Euro.
Таблица
4
|
Дата
|
Фактические данные
|
Прогноз
|
|
17.04.2005
|
35,930
|
35,883
|
|
18.04.2005
|
36,142
|
35,910
|
|
19.04.2005
|
36,216
|
35,936
|
|
20.04.2005
|
36,280
|
35,963
|
3.6 Непараметрические
коэффициенты связи
Для изучения степени тесноты связи между двумя количественными признаками
вычисляют ранговый коэффициент связи Спирмена.
Ранжирование - процедура упорядочивания объектов изучения производимая на
основе предпочтений. Ранг - порядковый номер значений признака, расположенного
в порядке возрастания или убывания количественных значений.
Коэффициент Спирмена рассчитывается по формуле:
где n - число пар рангов;i -
разности рангов.
Коэффициент Спирмена может
принимать значения от -1 до 1. Чем ближе его величина по модулю к единице, тем
сильнее связь между признаками.
Пример №6. Рассчитать
коэффициент Спирмена между индексами Nasdaq100, Dowjons65 , Forex, USD, Euro
(см. в приложение таблицу исходных данных).
На первый взгляд задач кажется очень объемной, но функциональные
возможности MS Excel позволяют решить эту задачу более рационально.
Разобьем решение на две части: в первой части покажем, как рассчитать
коэффициент Спирмена для двух рядов динамики; во второй части составим
уравнение, позволяющее решить всю задачу.
Алгоритм действий следующий:
Формируем
исходную таблицу из 5 столбцов, каждый столбец - это ряд динамикиприсваем каждому массиву данных соответстующее
название, ВставкаИмяПрисвоить
(например, ряду динамики Forex присваиваем такое же имя);
Создаем
вторую таблицу из 6 столбцов, в которой первый и второй столбцы - массивы
исследуемых данных; третий и четвертый столбец, состоят из рангов значений
уровней первого ряда.
Ранжирование
в Excel проводится с помощью функции РАНГ(число, ссылка, порядок). Выделяем
весь столбецВставкаФункции СтатистическиеРанг.
Вводим в ячейку Число и Ссылка массив значений первого ряда динамики, а в
ячейки Порядок устанавливаем логическую переменную Ложь нажимаем Ctrl+Shift+Enter. Аналогично и для
четвертого столбца.
Теперь
решим вторую часть задачи. Для того чтобы не заносить дополнительные данные
воспользуемся функцией Если.
Вставка
функций Мастер функцийКатегория
логические выбирете функцию Если OK;
Выделяем
все ячейки первого столбца нажимаем на кнопку со значком функция и вводим следующую сложную функцию:
=ЕСЛИ(O9=статистика!B7;NASDAQ100;ЕСЛИ(O9=статистика!C7;DOWJONS65;ЕСЛИ(O9=статистика!D7;Forex;ЕСЛИ(O9=статистика!E7;USD;ЕСЛИ(O9=статистика!F7;EURO;0)))))
нажимаем
Ctrl+Shift+Enterаналогично для второго столбца.
В
результате, меняя названия индексов в шапке таблицы, будет происходить
автоматическая замена массивов данных для вычислений коэффициента, что
существенно упрощает решение задачи.
Рис.
6 Результаты решения Примера №7
4. Встроенные
функции MS Excel для анализа рядов
динамики
Excel содержит несколько сотен функций, которые выполняют широкий спектр
различных вычислений. Статистических насчитывается 80 функций. Рассмотрим те из
них, которые позволяют анализировать ряды динамики и очень важны в этом
анализе.
4.1 Основные
статистические функции
Существует целый набор основных статистических функций без которых трудно
представить себе расчет статистических показателей. Знание данных функций
существенно облегчает решение сложных статистических задач. В таблице
статистических функций приведен список таких функций.
Пример №8. Используя табличные формулы рассчитать возможные
статистические показатели для ряда динамики Nasdaq100, Dowjons65, Forex, USD,
Euro.
Алгоритм
выполнения следующий: Вставка Мастер
функций Статистические в
появившемся списке выбираем необходимые функции далее
появляется панель необходимых условий для задания функции, заполняем нужные
ячейкиOK.
Таблица
5
|
Наименование
|
Математическая формула
|
Функция MS Excel
|
|
Средняя арифметическая
|
|
СРЗНАЧ()
|
|
Среднее линейное отклонение
|
|
СРОТКЛ()
|
|
Дисперсия по генеральной
совокупности
|
|
ДИСПР()
|
|
Дисперсия смещенная (по
выборке)
|
|
ДИСПРА()
|
|
Стандартное отклонение по
генеральной совокупности
|
|
СТАНДОТКЛОНП()
|
|
Стандартное отклонение
смещенное
|
|
СТАНДОТКЛОН()
|
|
Коэффициент вариации
|
|
|
3.7 Функция
ЛИНЕЙН
Синтаксис функции: ЛИНЕЙН (известные значения y; известные значения x;
конст; статистика)
Рассчитывает статистику для ряда с применением метода наименьших
квадратов, чтобы вычислить прямую линию, которая наилучшим образом
аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает
полученную прямую. Поскольку возвращается массив значений, функция должна задаваться
в виде формулы массива.
Уравнение для прямой линии имеет следующий вид:
= mx + b или
= m1x1 + m2x2 + ... + b (в случае нескольких диапазонов значений x), где
зависимое значение y - функция независимого значения x, значения m -
коэффициенты, соответствующие каждой независимой переменной x, а b -
постоянная. Заметим, что y, x и m могут быть векторами. Функция ЛИНЕЙН
возвращает массив {mn;mn-1;...;m1;b}. ЛИНЕЙН может также возвращать
дополнительную регрессионную статистику.
Пример №9. При помощи функции ЛИНЕЙН вычислить коэффициенты двух
уравнений регрессии, показывающих зависимость Dowjons65 от Nasdaq100,Dowjons65
от Forex.
Алгоритм решения следующий:
перед вводом формулы выделяем две смежные ячейки;
ввод непосредственно формулы;
=ЛИНЕЙН (статистика!C8:C42;статистика!B8:B42;ИСТИНА;ЛОЖЬ)
=ЛИНЕЙН (статистика!C8:C42;статистика!D8:D42;ИСТИНА;ЛОЖЬ)
нажать три клавиши Ctrl+Shift+Enter.
Пример №10. При помощи функции ЛИНЕЙН вычислить коэффициенты уравнения
множественной регрессии курса USD от индексов Dowjons65, Nasdaq100, Forex.
Алгоритм решения следующий:
перед вводом формулы для размещения коэффициентов выделяем 4 ячейки;
в состав переменных y включаем массив USD, в состав переменной x 3
столбца со статьями Dowjons65, Nasdaq100, Forex. Необходимо задать два аргумента
логического типа. Один из них имеет значение Истина, а другой - Ложь.
закончить ввод формулы нажатием клавиш Ctrl+Shift+Enter.
Стоит отметить, что если последняя переменная имеет значение Истина, то
после окончания ввода на экране появится массив с дополнительными
характеристиками регрессии. Такие характеристики выводятся автоматически, если
использовать Пакет анализа данных при решении аналогичной задачи.
3.8 Функция
КОРРЕЛ
Синтаксис функции: КОРРЕЛ (Массив 1, Массив 2).
Данная функция возвращает коэффициент корреляции меду интервалами ячеек
Массив 1 и Массив 2. Коэффициент корреляции используется для определения
наличия взаимосвязи между двумя свойствами размерность, равную произведению
размерностей случайных величин. Величина, коэффициента корреляции
рассчитывается по формуле:
,
где
;
.
Коэффициент
корреляции находится в пределах -1
1.
Заметки:
Если
аргумент, который является массивом или ссылкой, содержит текст, логические
значения или пустые ячейки, то такие значения игнорируются; однако ячейки,
которые содержат нулевые значения, учитываются.
Если
Массив 1 и Массив 2 имеют различное количество точек данных, то функция КОРРЕЛ
возвращает значение ошибки #Н/Д.
Если
Массив 1 либо Массив 2 пуст, или если σ (стандартное отклонение) их значений равно нулю, то функция КОРРЕЛ
возвращает значение ошибки #ДЕЛ/0!.
Пример №11. Рассчитать коэффициент парной корреляции между Nasdaq100 и
Dowjons65 (см. приложение).
Алгоритм действий следующий:
сформируем исходные данные в виде таблицы;
выберем ячейку, в которую будет выведен результат вычисления;
Мастер
функций статистические КОРРЕЛ;
в
поле Массив 1 - исходные данные (Dowjons65), в поле Массив 2 - исходные данные
(Nasdaq100); для удобства массивам присвоены соответствующие имена;
в
окне функции появится результат решения. После нажатия кнопки OK в выбранной
ранее ячейке появится результат расчета 0,954.
Пример №12. Рассчитать коэффициент множественной корреляции курса USD,
Euro и индекса Forex.
Алгоритм решения следующий:
проводим аналогичные действия, что и в примере 11, т.е. вычисляем парные
коэффициенты корреляции;
затем
полученные значения подставляем в формулу для расчета коэффициента
множественной корреляции (см. параграф множественный коэффициент корреляции).
=КОРЕНЬ((B29*B29+C30*C30-2*B29*C30*B30)/(1-B30*B30))
Заключение
Целью курсовой работы был анализ рядов динамики с помощью табличного
процессора MS Excel. Для достижения цели курсовой работы были решены следующие
задачи:
· Интерполяция рядов динамики
· Расчет основных статистических показателей
· Выравнивание ряда динамики
· Расчет коэффициента парной корреляции
· Расчет коэффициента множественной корреляции
· Вычисление параметров уравнения регрессии для зависимости
индексов и курсов валют
· Экстраполяция рядов динамики
· Интерполяция рядов динамики
· Вычисление непараметрического коэффициента связи и
оптимизация данной задачи
· Построение графиков, отражающих результаты работы
Решение перечисленных задач позволяет сделать вывод о том, что программа
MS Excel является мощным и гибким инструментом в области обработки и анализа
информации связанной с рядами динамики. Функциональные возможности MS Excel
существенно упрощают решение задачи, связанной с анализом рядов динамики. А,
следовательно, делают данную программу очень удобной для использования при
создании статистических и инженерных проектов и решение задач, относящихся к
социально-экономической сфере.
Список
литературы
1. Теория
статистики: Учебник / под редакцией Р.А. Шмойловой - М.:ФИНАНСЫ И СТАТИСТИКА,
2001.
. Козлов
А.Ю., Шишов В.Ф. Пакет Анализа MS Excel в экономико-статистических расчетах:
Учебное пособие / под редакцией В.С. Мхитаряна - М.: ЮНИТИ-ДАНА, 2003.
. Додж
М., Стинсон К. Эффективная работа с Microsoft Excel
2000 - СПб.: Питер, 2003.
. Интернет
сайт РИА «Росбизнесконсалтинг» www.rbc.ru
Приложение
Таблица исходных данных рядов динамики.
|
Дата
|
Фондовые индексы
|
Валютные курсы (руб.)
|
|
NASDAQ100
|
DOWJONS65
|
FOREX
|
USD
|
EURO
|
|
17.03.2005
|
1487,63
|
3404,45
|
1,337
|
27,489
|
36,665
|
|
18.03.2005
|
1484,40
|
3303,96
|
1,331
|
27,461
|
36,820
|
|
19.03.2005
|
1484,45
|
3393,22
|
1,317
|
27,503
|
36,636
|
|
20.03.2005
|
1465,09
|
3365,84
|
1,309
|
27,544
|
36,452
|
|
21.03.2005
|
1471,77
|
3351,54
|
1,298
|
27,576
|
36,329
|
|
22.03.2005
|
1469,94
|
3362,77
|
1,294
|
27,638
|
36,145
|
|
23.03.2005
|
1462,64
|
3354,33
|
1,296
|
27,697
|
36,026
|
|
24.03.2005
|
1458,48
|
3351,67
|
1,296
|
27,711
|
36,889
|
|
25.03.2005
|
1454,32
|
3349,01
|
1,295
|
27,747
|
36,281
|
|
26.03.2005
|
1472,71
|
3369,51
|
1,288
|
27,785
|
36,233
|
|
27.03.2005
|
1464,34
|
3329,49
|
1,292
|
27,820
|
35,913
|
|
28.03.2005
|
1491,74
|
3377,32
|
1,294
|
27,831
|
35,994
|
|
29.03.2005
|
1482,53
|
3373,19
|
1,296
|
27,826
|
36,056
|
|
30.03.2005
|
1469,35
|
3353,00
|
1,291
|
27,855
|
36,027
|
|
31.03.2005
|
1484,04
|
3364,86
|
1,291
|
27,939
|
36,153
|
|
01.04.2005
|
1487,26
|
3366,57
|
1,290
|
27,932
|
36,001
|
|
02.04.2005
|
1476,72
|
3363,08
|
1,285
|
27,958
|
35,978
|
|
03.04.2005
|
1483,75
|
3377,31
|
1,287
|
27,893
|
35,948
|
|
04.04.2005
|
1480,67
|
3377,35
|
1,287
|
27,939
|
35,816
|
|
05.04.2005
|
1499,71
|
3394,31
|
1,286
|
27,874
|
35,899
|
|
06.04.2005
|
1485,60
|
3341,36
|
1,293
|
27,809
|
35,920
|
|
07.04.2005
|
1490,18
|
3369,16
|
1,293
|
27,879
|
35,744
|
|
08.04.2005
|
1491,38
|
3369,13
|
1,293
|
27,829
|
35,767
|
|
09.04.2005
|
1478,55
|
3351,06
|
1,298
|
27,812
|
35,733
|
|
10.04.2005
|
1489,16
|
3376,99
|
1,292
|
27,793
|
35,937
|
|
11.04.2005
|
1461,68
|
3329,11
|
1,291
|
27,765
|
36,064
|
|
12.04.2005
|
1441,13
|
3272,87
|
1,282
|
27,791
|
35,942
|
|
13.04.2005
|
1408,59
|
3213,48
|
1,292
|
27,829
|
35,822
|
|
14.04.2005
|
1414,07
|
3228,61
|
1,292
|
27,903
|
35,722
|
|
15.04.2005
|
1401,14
|
3204,28
|
1,291
|
27,854
|
35,862
|
|
16.04.2005
|
1409,98
|
3221,01
|
1,301
|
27,863
|
35,863
|
|
17.04.2005
|
1406,26
|
3216,41
|
1,284
|
27,875
|
35,883
|
|
18.04.2005
|
1405,38
|
3216,23
|
1,283
|
27,880
|
35,910
|
|
19.04.2005
|
1404,50
|
3216,06
|
1,282
|
27,885
|
35,936
|
|
20.04.2005
|
1403,63
|
3215,88
|
1,281
|
27,890
|
35,963
|