5349

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

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

Информатика, кибернетика и программирование

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

Русский

2012-12-07

108 KB

169 чел.

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

Цель работы: Приобрести навыки решения задач частотного анализа с помощью функции рабочего листа анализа MS Excel.

Краткая теория

При анализе экономических показателей часто возникает вопрос, как часто встречаются показатели в заданных интервалах значений.

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

 В качестве массива данных может быть одномерный  или двумерный массив (например, A4:D15).

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

Для частотного анализа можно использовать команду Сервис/Анализ данных. Анализ данных является одной из надстроек Excel. Если в меню отсутствует эта команда, то следует выполнить команду Сервис/ Надстройки и установить соответствующий флажок в окне Надстройки.

Задание 1

С помощью функции Частота для выборки множества сумм заказов () введите в диапазон подсчитайте, сколько значений попадают в заданные интервалы значений. Например, от 0 до 1000,  от 1001 до 1500, от 1501 до 2000, от 2001 до 2500, свыше 2500.

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

  1.  На рабочем листе MS Excel введите данные об объемах заказов в 20 филиалах фирмы  за сентябрь в виде таблицы, фрагмент которой показан на рисунке.

А

B

C

D

E

1

№ филиала

Сентябрь

2

1

1230

1000

3

2

980

1500

4

….

2000

5

2500

6

  1.  В свободный диапазон клеток (столбец) введите верхние границы интервалов (Например, D2=1000, D3=1500, D4=2000, D5=2500).
  2.  Выделите блок ячеек столбца, смежного со столбцом интервалов (E2:E21). Для того, чтобы подсчитать количество значений, превышающих нижнюю границу интервала, выделяется диапазон, на одну ячейку  больше, чем диапазон интервалов.
  3.  В диапазон E2:E6 введите формулу { =ЧАСТОТА(E2:E15;J2:J6)}.

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

Рисунок 1 – Пример заполнения диалогового окна функции Частота.

  1.  Постройте диаграмму по полученным результатам.
  2.  Сохраните файл.

Задание к лабораторной работе (часть 3)

Задание 2

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

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

Часть 2

Решение задач прогнозирования в среде MS Excel. Метод скользящего среднего 

Цель работы: Приобрести навыки прогнозирования экономической деятельности предприятия с применением статистического программного пакета MS Excel.

Краткая теория

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

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

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

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

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

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

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

Задание к лабораторной работе (часть 2)

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

Порядок выполнения задания:

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

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

Рисунок 1 – Вычисление простого скользящего среднего

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

Рисунок 2 – График тенденции изменения показателя объема услуг, полученной методом простого скользящего среднего

Другим способом решения является использование для определения скользящего целого Пакета анализа. Пакет анализа является надстройкой MS Excel (выберите пункт меню Сервис/ Надстройки и установите флажок Пакет анализа).

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

  1.  Выполнить команду Сервис/Анализ данных и выбрать из списка инструментов анализа Скользящее среднее. 
  2.  В диалоговом окне укажите параметры для вычисления скользящего среднего:
  •  В качестве входного интервала выделите блок ячеек, содержащий данные об объеме услуг.
  •  Укажите Интервал- 3 (по умолчанию используется 3), в качестве выходного интервала – любую ячейку рабочего листа (просто щелкните на ячейке рабочего листа, с которой должны выводиться результаты);
  •  Задайте вывод графика и стандартных погрешностей.

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

Проанализируйте используемые расчетные формулы и полученные результаты.

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

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

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

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

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

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

Часть 3

Решение задач прогнозирования с помощью функций рабочего листа и маркера заполнения

Краткая теория

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

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

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

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

  •  С помощью маркера заполнения
  •  С помощью функций рабочего листа

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

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

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

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

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

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

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

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

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

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

Формат

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

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

Задание к лабораторной работе (часть 3)

Задание 1:

Рассчитайте линейный и экспоненциальный прогноз на один год и на последующие три периода (до 2011 года) с помощью маркера заполнения.

Задание 2:

Рассчитайте линейный и экспоненциальный прогноз на один год и затем на последующие три периода с помощью функций рабочего листа ТЕНДЕНЦИЯ и РОСТ. Для расчета интервального прогноза после заполнения параметров  диалогового окна функции  и не выходя из него нажмите комбинацию клавиш Ctrl/ Shift/ Enter.

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

{ = ТЕНДЕНЦИЯ (B3:G3;B2:G2;B2:H2)}

Определите, какая модель является наиболее точной.

Постройте графики и линии тренда для первого и второго задания.


 

А также другие работы, которые могут Вас заинтересовать

22383. Обратная связь (ОС) в усилителях 154 KB
  Влияние ОС на стабильность Ку Однако уменьшая Ку ООС увеличивает его стабильность. стабильность коэффициент усиления в усилителе с ООС в 1 раз выше чем в усилителе без ООС. Пример Пусть усилитель имеет Ку=100 и охвачен ООС причем коэффициент передачи цепи ОС . Стабилизация коэффициента усиления при введении ООС объясняется тем что увеличение усиления за счет любых причин вызывает возрастание напряжения ОС что вызывает уменьшение входного напряжения т.
22384. ОСНОВЫ ПРОЕКТИРОВАНИЯ ПРОМЫШЛЕННЫХ ЗДАНИЙ. ОБЪЕМНО-ПЛАНИРОВОЧНЫЕ И КОНСТРУКТИВНЫЕ РЕШЕНИЯ. ТИПИЗАЦИЯ СБОРНЫХ ЭЛЕМЕНТОВ 17.73 KB
  Так например элементы перекрытий и покрытий должны быть прочными и достаточно жесткими чтобы их прогиб не нарушал эксплуатационного режима здания: стены и колонны поддерживающие покрытия должны быть прочными и устойчивыми. Все здания в целом должны обладать пространственной жесткостью т. Здания бывают каркасными и бескаркасными. В бескаркасных зданиях пространственная жесткость создаётся благодаря совместной работе продольных и поперечных стен соединенных покрытиями в единую пространственную систему.
22385. СТАДИИ НАПРЯЖЕННО-ДЕФОРМИРОВАННОГО СОСТОЯНИЯ ЖЕЛЕЗОБЕТОННЫХ ЭЛЕМЕНТОВ 360.47 KB
  2: стадия I до появления трещин в бетоне растянутой зоны когда напряжения в бетоне меньше временного сопротивления растяжению и растягивающие усилия воспринимаются арматурой и бетоном совместно; стадия II после появления трещин в бетоне растянутой зоны когда растягивающие усилия в местах где образовались трещины воспринимаются apматypoй и участком бетона над трещиной а на участках между трещинами арматурой и бетоном совместно; стадия III стадия разрушения характеризующаяся относительно коротким периодом работы элемента когда...
22386. МЕТОД РАСЧЕТА КОНСТРУКЦИЙ ПО ПРЕДЕЛЬНЫМ СОСТОЯНИЯМ. СУЩНОСТЬ МЕТОДА. ДВЕ ГРУППЫ ПРЕДЕЛЬНЫХ СОСТОЯНИЙ. КЛАССИФИКАЦИЯ НАГРУЗОК. ОСНОВНЫЕ ПОЛОЖЕНИЯ РАСЧЕТА 17.19 KB
  Конструкция может потерять необходимые эксплуатационные качества по одной из двух причин: 1 в результате исчерпания несущей способности разрушения материала в наиболее нагруженных сечениях потери устойчивости некоторых элементов или всей конструкции в целом; 2 вследствие чрезмерных деформаций прогибов колебаний осадок а также изза образования трещин или чрезмерного их раскрытия. Строительные конструкции рассчитывают по методу предельных состояний который дает возможность гарантировать сохранение...
22387. ИЗГИБАЕМЫЕ ЭЛЕМЕНТЫ. РАСЧЕТЫ ПРОЧНОСТИ ПО НОРМАЛЬНЫМ И НАКЛОННЫМ СЕЧЕНИЯМ ЭЛЕМЕНТОВ ПРЯМОУГОЛЬНОГО И ТАВРОВОГО ПРОФИЛЯ. РАСЧЕТ ПОПЕРЕЧНЫХ СТЕРЖНЕЙ 866.99 KB
  РАСЧЕТЫ ПРОЧНОСТИ ПО НОРМАЛЬНЫМ И НАКЛОННЫМ СЕЧЕНИЯМ ЭЛЕМЕНТОВ ПРЯМОУГОЛЬНОГО И ТАВРОВОГО ПРОФИЛЯ. Поперечные стержни сеток распределительная арматура принимают меньших диаметров общим сечением не менее 10 сечения рабочей арматуры поставленной в месте наибольшего изгибающего момента; располагают их с шагом 250 300 мм но не реже чем через 350 мм. Железобетонные балки могут иметь прямоугольные тавровые двутавровые трапецеидальные поперечные сечения рисунок 7.2 – Формы поперечного сечения балок и схемы их армирования а прямоугольная;б...
22388. Сжатые и растянутые элементы. Конструктивные особенности. Расчет прочности центрально И Внецентренно растянутых элементов. Расчет внецентренно сжатых элементов таврового и двутаврового сечений 1.23 MB
  Расчет прочности центрально И Внецентренно растянутых элементов. Расчет внецентренно сжатых элементов таврового и двутаврового сечений. НАПРЯЖЕННОЕ СОСТОЯНИЕ РАСТЯНУТЫХ И СЖАТЫХ ЖЕЛЕЗОБЕТОННЫХ ЭЛЕМЕНТОВ Сжатые элементы. Конструктивные особенности сжатых элементов К центральносжатым элементам условно относят: промежуточные колонны в зданиях и сооружениях; верхние пояса ферм загруженных по узлам; восходящие раскосы и стойки ферменной решетки.
22389. ТРЕЩИНОСТОЙКОСТЬ И ПЕРЕМЕЩЕНИЯ ЖЕЛЕЗОБЕТОННЫХ ЭЛЕМЕНТОВ. СОПРОТИВЛЕНИЕ ОБРАЗОВАНИЮ ТРЕЩИН ЦЕНТРАЛЬНО РАСТЯНУТЫХ, ИЗГИБАЕМЫХ, ВНЕЦЕНТРЕННО СЖАТЫХ И РАСТЯНУТЫХ ЭЛЕМЕНТОВ. ТРЕЩИНОСТОЙКОСТЬ И ПЕРЕМЕЩЕНИЯ ЖЕЛЕЗОБЕТОННЫХ ЭЛЕМЕНТОВ 101.52 KB
  ТРЕЩИНОСТОЙКОСТЬ И ПЕРЕМЕЩЕНИЯ ЖЕЛЕЗОБЕТОННЫХ ЭЛЕМЕНТОВ. СОПРОТИВЛЕНИЕ ОБРАЗОВАНИЮ ТРЕЩИН ЦЕНТРАЛЬНО РАСТЯНУТЫХ ИЗГИБАЕМЫХ ВНЕЦЕНТРЕННО СЖАТЫХ И РАСТЯНУТЫХ ЭЛЕМЕНТОВ. ТРЕЩИНОСТОЙКОСТЬ И ПЕРЕМЕЩЕНИЯ ЖЕЛЕЗОБЕТОННЫХ ЭЛЕМЕНТОВ. Общие положения Трещиностойкость элементов как условлено ранее это сопротивление образованию трещин в стадии I или сопротивление раскрытию трещин в стадии II.
22390. РАСЧЕТ ПО ОБРАЗОВАНИЮ ТРЕЩИН, НОРМАЛЬНЫХ И НАКЛОННЫХ К ПРОДОЛЬНОЙ ОСИ ЭЛЕМЕНТА. СОПРОТИВЛЕНИЕ РАСКРЫТИЮ ТРЕЩИН. ОПРЕДЕЛЕНИЕ РАССТОЯНИЯ МЕЖДУ ТРЕЩИНАМИ 235.22 KB
  РАСЧЕТ ПО ОБРАЗОВАНИЮ ТРЕЩИН НОРМАЛЬНЫХ И НАКЛОННЫХ К ПРОДОЛЬНОЙ ОСИ ЭЛЕМЕНТА. СОПРОТИВЛЕНИЕ РАСКРЫТИЮ ТРЕЩИН. ОПРЕДЕЛЕНИЕ РАССТОЯНИЯ МЕЖДУ ТРЕЩИНАМИ. Расчет по образованию трещин нормальных к продольной оси элемента Этот расчет заключается в проверке условия что трещины в сечениях нормальных к продольной оси элемента не образуются если момент внешних сил М не превосходит момента внутренних усилий в сечении перед образованием трещин Мcrcт.
22391. КРИВИЗНА ОСИ ПРИ ИЗГИБЕ, ЖЕСТКОСТЬ И ПЕРЕМЕЩЕНИЯ ЖЕЛЕЗОБЕТОННЫХ ЭЛЕМЕНТОВ. ОБЩИЕ ПОЛОЖЕНИЯ РАСЧЕТА 161.5 KB
  КРИВИЗНА ОСИ ПРИ ИЗГИБЕ ЖЕСТКОСТЬ И ПЕРЕМЕЩЕНИЯ ЖЕЛЕЗОБЕТОННЫХ ЭЛЕМЕНТОВ. ОБЩИЕ ПОЛОЖЕНИЯ РАСЧЕТА Расчет перемещений железобетонных элементов прогибов и углов поворота связан с определением кривизны оси при изгибе или с определением жесткости элементов. Считается что элементы или участки элементов не имеют трещин в растянутой зоне если при действии постоянных длительных и кратковременных нагрузок с коэффициентом надежности по нагрузке γf= 1 трещины не образуются. Кривизна оси при изгибе и жесткость железобетонных элементов на участках...