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




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

MS Excel включает мощные средства для решения задач статистики. Изучение материалов лекции позволит приобрести навыки решения задач анализа и прогнозирования средствами табличного редактора. Оцените, насколько просто и быстро решаются подобные задачи с помощью прикладных программ.

ПРОГНОЗИРОВАНИЕ И СТАТИСТИЧЕСКИЙ АНАЛИЗ

Частотный анализ в среде MS Excel

В задачах анализа и статистики приходится отвечать на вопрос, как часто данные выборки встречаются в заданных интервалах значений.

Инструментом, позволяющим решать подобные задачи, в MS Excel является функция ЧАСТОТА(), относящаяся к категории статистических функций. Функция используется для построения интервального распределения и возвращает распределение частот в виде вертикального массива. Для данного множества значений и заданного множества карманов (интервалов) частотное распределение подсчитывает, сколько значений попадает в каждый интервал.

В качестве массива данных может быть одномерный  или двумерный массив.

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

=ЧАСТОТА ( массив_данных; массив_карманов)

Для частотного анализа можно использовать команду Анализ данных вкладки Данные. В окне Анализ данных следует выбрать элемент Гистограмма.

Пример 1. Определим, как часто параметр, характеризующий качество радиоканала, принимает значения из указанных диапазонов. Будем использовать результаты наблюдений, представленные на рисунке 83.

Порядок действий:

1. Заполним рабочий лист А3:А22 исходными данными (рисунок 1).

2. В свободный диапазон клеток (С3:С10) введем верхние границы интервалов (от -10 до 25 с шагом 5).

3. Выделим блок ячеек столбца, смежного со столбцом интервалов (D3:D11). Для того чтобы подсчитать количество значений, превышающих нижнюю границу интервала, выделяется диапазон, на одну ячейку  больше, чем диапазон интервалов.

4. В диапазон D3:D11введем формулу  { =ЧАСТОТА(E2:E15;J2:J6)}.

5. Для этого воспользуемся библиотекой функции вкладки Формулы. Из категории «Статистические» выберем функцию ЧАСТОТА(). В диалоговом окне функции заполним поля  массива выборки и массива интервалов (рисунок 1). Для расчета элементов массива, не выходя из окна диалога, нажмем комбинацию клавиш + + >. 

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l13_1.gif
Рисунок 1 – Пример заполнения диалогового окна функции ЧАСТОТА() 

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

Рассмотрим еще один пример обработки статистических наблюдений.

Пример 2. По результатам отчетных данных о продажах определим, как распределяются объемы продаж в рассматриваемых интервалах.

Порядок действий:

1. Заполним диапазон рабочего листа исходными данными. Для расчета совокупного потока денежных средств воспользуемся формулой   =C2+B3 (рисунок 3).

2. В свободном диапазоне, например, Е2:Е5, введем верхние границы интервалов (например, 1000, 2000, 3000 …).

3. Выберем элемент Гистограмма в меню Анализ данных.

4. Заполним диалоговое окне Мастера в соответствии с рисунком 153: в качестве входного диапазона укажем диапазон В2:В25, диапазона интервалов – Е2:Е5, выведем результаты и график на новый рабочий лист.

5. Нажмем ОК. Результаты частотного анализа представлены на рисунке 4.

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

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

ЛЕКЦИЯ 13

Часть 2

Решение задач прогнозирования в среде MS Excel

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

Для решения задач прогнозирования в среде MS Excel используется Пакет анализа, включающий инструменты анализа. Выбрав инструмент для анализа данных, и задав необходимые параметры, можно быстро решать сложные статистические задачи, сопровождая их графической интерпретацией.

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

При выборе метода прогнозирования учитывают характер изменения случайной величины временного ряда. Если вариация средних значений незначительна и все наблюдения временного ряда имеют одинаковую значимость для прогноза, то используют метод скользящего среднего. Скользящие средние позволяют сгладить (отфильтровать) случайные и периодические колебания временного ряда. Сглаживание простой скользящей средней является наиболее распространенной процедурой сглаживания.

В инструменте анализа MS Excel Скользящее среднее количество значений, участвующих в вычислении прогнозируемой величины, задается параметром Интервал. Величину интервала сглаживания выбирают тем больше, чем более необходимо сгладить мелкие колебания значений ряда. Метод Простой скользящей средней дает хорошие результаты в динамических рядах с линейной тенденцией развития.

 Если для прогноза наиболее значимыми являются последние результаты наблюдений, то используют метод экспоненциального сглаживания. В методе экспоненциального сглаживания каждое значение участвует в формировании прогнозируемых значений с переменным весом, который убывает по мере «устаревания» данных. В инструменте анализа MS Excel Экспоненциальное сглаживание весовой коэффициент, или параметр сглаживания,  определяется параметром Фактор затухания. Обычно для временных рядов в экономических задачах величину параметра сглаживания задают в интервале от 0,1 до 0,3. Начальное расчетное значение  в процедуре Экспоненциальное сглаживание пакета Анализа MS Excel принимается равным уровню первого члена ряда. Метод  обеспечивает хорошее согласование исходных и расчетных данных для первых значений ряда. Если конечные вычисленные значения значительно отличаются от соответствующих исходных данных, то целесообразно изменить величину параметра сглаживания. Оценить величины расхождений можно на основе стандартных погрешностей и графика, которые пакет Анализа позволяет вывести вместе с расчетными значениями ряда.

Рассмотрим возможности прогнозирования показателей деятельности предприятия, занимающегося предоставлением услуг связи.

Пример 1. Вычислить прогнозируемое значение величины объема продукции (услуг) предприятия методом скользящего среднего.

Для решения создадим на рабочем листе столбец, содержащий данные об объеме услуг в млн. руб., оказанных предприятием за последние 10  лет. Выявим тенденцию изменения показателя с помощью скользящего среднего. Выберем трехгодичный период скользящего среднего, так как за меньший период скользящее среднее может не отразить тенденцию, а за более продолжительный – сгладит ее.

Для вычислений воспользуемся способом прямого введения формулы. Чтобы получить трехлетнее скользящее среднее объема выполненных услуг для нашего примера, введем в ячейку B5 формулу для вычисления  =СРЗНАЧ(A2:A4). Скопируем формулу в интервал B6:B11 (рисунок 5).

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

Проиллюстрируем результаты графиком, отражающим динамику изменения исходных данных и скользящего среднего (рисунок 6).

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

Другим способом решения является использование для определения скользящего целого Пакета анализа.

Опишем порядок действий для решения задачи:

1. Выполним команду Данные/Анализ данных и выберем из списка инструментов анализа Скользящее среднее.

2. В диалоговом окне укажем параметры для вычисления скользящего среднего:

  • в качестве входного интервала выделим блок ячеек, содержащий данные об объеме услуг;

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

  • зададим вывод графика и стандартных погрешностей.

Excel сам выполнит работу по внесению значений в формулу для вычислений скользящего среднего. Из-за недостаточного количества данных при вычислении среднего значения для первых результатов наблюдений в начальных ячейках выходного диапазона будет выведено значение ошибки #Н/Д. Проанализируйте используемые расчетные формулы и полученные результаты.

Аналогично вычислите пятилетние простые скользящие средние. Сравните результаты сглаживания для двух вариантов расчета.

Пример 2. Вычислим прогнозируемое значение величины объема продукции (услуг) предприятия методом экспоненциального сглаживания. 

Порядок действий:

1. На листе MS Excel создадим список, содержащий данные о численности сотрудников фирмы за последние 10  лет. Данные введем произвольно так, чтобы прослеживалась тенденция.

2. Проведем сглаживание временного ряда с использованием экспоненциальной средней с параметрами сглаживания 0,1 и затем 0,3. По результатам  расчетов построим график и определим, какой из полученных временных рядов носит более сглаженный характер.

3. Воспользуемся командой меню Данные/Анализ данных и выберем из списка инструментов анализа Экспоненциальное сглаживание. Укажем параметры для вычисления скользящего среднего:

- в качестве входного интервала выделим блок ячеек, содержащий данные о численности;

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

- зададим вывод графика и стандартных погрешностей.

4. Добавим линии тренда на полученных графиках. Для этого выберем линию графика (просто щелкните правой кнопкой мыши на линии графика) и в контекстном меню – пункт Добавить линию тренда. В диалоговом окне выберем наиболее подходящий для наших данных тип тренда (например, линейная фильтрация) и установим флажок вывода уравнения аппроксимирующей кривой на графике.

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

Существует несколько методов подбора кривых. Одним из самых простых является визуальный метод. Если на графике недостаточно просматривается тенденция развития (тренд), то производят, как описано выше, сглаживание ряда, а затем подбирается кривая, соответствующая новому ряду. В этом случае также применяются современные программные средства компьютерных систем. В  MS Excel встроены специальные функции, позволяющие рассчитывать прогнозируемые значения на определенный период.

Excel проводит линейную экстраполяцию, т.е. рассчитывает наиболее подходящую прямую, которая проходит через серию заданных точек. Задача заключается в нанесении на график набора точек, а затем в подборе линии, по которой можно проследить развитие функции с наименьшей ошибкой. Эта линия называется линией ТРЕНДА. Пользователь может использовать результат вычислений для анализа тенденций и краткосрочного прогнозирования.

Можно ввести последовательность значений, соответствующих простому линейному или экспоненциальному тренду, с помощью маркера заполнения или команды Ряд. Для расширения сложных и нелинейных данных можно использовать функции или средство регрессионного анализа, доступное в надстройке «Пакет анализа».

Excel может автоматически проводить линии тренда, различных типов непосредственно на диаграмме. Вычисления можно производить двумя способами:

- с помощью маркера заполнения;

- с помощью функций рабочего листа.

Опишем порядок действий для каждого из способов.

Первый способ:

Линейное приближение

1. Выделить ячейки с результатами наблюдений.

2. Перетащить с помощью левой кнопки мыши маркер заполнения, чтобы выделенными оказались также и ячейки, для которых необходимо рассчитать прогнозируемые значения. Рассчитанные таким образом значения соответствуют линейному прогнозу.

Экспоненциальное приближение

1. Выделить ячейки с результатами наблюдений.

2. Перетащить маркер заполнения с помощью правой кнопки мыши, чтобы выделенными оказались также и ячейки, для которых необходимо рассчитать прогнозируемые значения.

3. В появившемся контекстном меню выбрать команду «Экспоненциальное приближение».

Второй способ

В MS Excel встроены статистические функции рабочего листа.

ТЕНДЕНЦИЯ() - возвращает значения в соответствии с линейной аппроксимацией по методу наименьших квадратов.

РОСТ() - возвращает значения в соответствии с экспоненциальным трендом.

Использование этих функций – это еще один способ проведения регрессионного анализа.

Формат функции ТЕНДЕНЦИЯ()

=ТЕНДЕНЦИЯ (изв_знач_Y; изв_знач_X; нов_знач_X; константа)

Функция РОСТ() имеет тот же формат, но возвращает значения в соответствии с экспоненциальным трендом.

Прогнозирование с использованием функции ЛИНЕЙН()

Для линейной регрессии линия тренда, полученное методом наименьших квадратов, представляется простым уравнением

y = mx+b

где у – зависимая переменная, которая представляет значение тренда;

x – независимая переменная;

m – наклон линии тренда;

b – константа, представляющая собой стартовое значение тренда.

Функция ЛИНЕЙН() вычисляет коэффициент наклона и константу на линии тренда, используя метод наименьших квадратов для получения прямой, наилучшим образом аппроксимирующей имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Зная эти коэффициенты можно вычислить прогнозируемые значения, подставив новые значения  x в уравнение линейной регрессии.

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

y = mx + b или

y = m1x1 + m2x2 + ... + b (в случае нескольких диапазонов значений x),

где зависимое значение y — функция независимого значения x;

значения m — коэффициенты, соответствующие каждой независимой переменной x;

b — постоянная.

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

=ЛИНЕЙН(известные_значения_y;известные_значения_x;конст;статистика)

Y, x и m могут быть векторами. Функция возвращается массив {mn;mn-1;...;m1;b} и должна задаваться в виде формулы массива. Функция ЛИНЕЙН() может также возвращать дополнительную регрессионную статистику.

Известные_значения_y    — множество значений известных y для уравнения y = mx + b.

Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.

Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная. 

Известные_значения_x    — необязательное множество значений x, которые уже известны для соотношения y = mx + b.

Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то известные_значения_y и известные_значения_x могут иметь любую форму, при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором.

Если известные_значения_x пропущены, то предполагается, что это массив {1;2;3;...} такого же размера, как и известные_значения_y.

Конст   — логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

Если конст имеет значение ИСТИНА или пропущено, то b вычисляется обычным образом.

Если аргумент конст имеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются так, чтобы выполнялось соотношение y=mx.

Статистика   — логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии.

Если аргумент статистика имеет значение ИСТИНА, то функция ЛИНЕЙН() возвращает дополнительную регрессионную статистику.

Если аргумент статистика имеет значение ЛОЖЬ или пропущен, то функция ЛИНЕЙН() возвращает только значения m и b.
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