3.5. Анализ данных на основе имеющейся информации

Каким образом в приложении MS Еxcel можно сделать прогноз на основе имеющейся информации?

Подумай

  • Что такое прогноз?
  • Какие прогнозы мы делаем обычно в повседневной жизни?
  • Делают ли прогноз в науке и технике?

Новые знания

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

   Анализ динамических рядов – совокупность математико-статистических методов, предназначенных для определения структуры динамических рядов и их прогнозирования.

Прогнозирование

   Слово «прогноз» (греч. πρόγνωσις – предвидение, предсказание) – означает предсказание будущего с помощью научных методов, а также сам результат предсказания. Прогноз – научная модель явлений или предстоящих событий.

   Прогнозирование – процесс разработки прогнозов.

   К методам прогнозирования относятся:

  • статистический метод;
  • экспертная оценка (метод Дельфи);
  • моделирование.
  • структуры
  • Анализ
  • и их
  • динамических рядов –
  • совокупность
  • рядов
  • динамических
  • для определения
  • прогнозирования.
  • математико-статистических методов,
  • предназначенных

Линия тренда в программе Excel

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

   Виды линий тренда в программе Excel

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

Коэффициент R-квадрат

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

  • для составления
  • Линия тренда
  • табличных данных.
  • прогнозов
  • который
  • имеющихся
  • основе
  • на
  • это график
  • используется
  • в Excel –
  • функции,
   Руслан любит читать страницы блогеров в социальных сетях. Он обратил внимание, что число посетителей страниц зависит от количества постов и репостов. Если на странице опубликовано 100 статей, то количество ее посетителей составляет 228 тысяч (табл. 1).
​Теперь Руслана интересуют ответы на следующие вопросы: сколько людей просмотрят страницу, если количество опубликованных постов будет равным 120? а если 150?
​Для определения отношения между двумя величинами Руслан составил формулу, по которой, зная количество постов, можно вычислить количество посетителей страницы. Что представляет собой данная формула?

Таблица 1

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

у=kx+b

   Если в этом уравнении будут известны значения коэффициента k и свободного члена b, то, взяв любое значение х вычислим соответствующее ему значение у.
​На основе табличных данных построим линейный график.

   Для этого, выделив табличные данные, необходимо выполнить следующую последовательность команд: Вставка =>Диаграммы=>Точечная =>Точечная с маркерами, тогда получим диаграмму, как на рисунке 1.

Рис. 1. Корреляция

   На диаграмме по оси х расположено количество постов, по оси у – количество пользователей страницы (тысяч человек). Как видно на диаграмме точки не расположены на одной линии (прямой), а разбросаны. Можем ли провести одну линию по возможности наиболее близко расположенную ко всем точкам? Для ответа на данный вопрос нужно проверить корреляцию этих точек. Если при изменении одной величины, изменяется среднее значение другой величины, то такая статистическая зависимость называется корреляционной зависимостью. В программе Excel имеется функция КОРРЕЛ (корреляция) (рис. 1). Значение корреляции функции обозначается R. Корреляция означает c лат. correlatіo – отношение, взаимосвязь. Если значение корреляции функции R > 0, то связь положительно возрастающая, если R < 0, то связь отрицательно убывающая. При приближении значения корреляции функции к 1, линии связи приближаются к прямой. В нашем случае R = 0,96704012, то есть отчетливо видна линейная зависимость.

Изображение достоверной величины аппроксимации на диаграмме

   Для изображения на диаграмме линий линейного тренда выберем одну точку, например, пятую по счету. Выделим ее левой кнопкой мыши, в контекстном меню выполним команду «Добавить линию тренда...» (рис. 2).

Рис. 2

   Из шести видов тренда, представленных на рисунке 2 выберем тренд «Линейная». Для отражения на диаграмме уравнения зависимости линейного тренда установим флажок в строке «показывать уравнение на диаграмме». Для размещения на диаграмме достоверной величины аппроксимации  установим флажок в строке «поместить на диаграмму величину достоверности аппроксимации R2». При нажатии на кнопку «Закрыть» получим диаграмму, на которой будет отражена линия тренда, записаны уравнение зависимости и значение R2 (рис. 3).

Рис. 3

Расчеты Руслана

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

1-метод. На диаграмме для х = 120 по линейной зависимости = 2,257– 16,23 нужно выполнить прогнозирование (таблица 1). Если в результате вычислений количество посетителей страницы составит 254 тысячи, а количество статей – 150, то можно предположить, что посетителей будет 322 тысячи.

у= 2,257*120 - 16,23
у=254,31

   2-й метод. Для вычисления прогноза используем функцию ПРЕДСКАЗ. Она относится к категории статистических. Результаты действия функции можно увидеть на рисунке 4:
​= ПРЕДСКАЗ (известные_значения_y; известные_значения_x).
​  Вместо х из таблицы 1 возьмем для прогноза значение 120, вместо у укажем количество пользователей.
​Для построения прогноза вместо х возьмем промежуток значений, расположенных выше х=120. Результат прогнозирования получился равным 254,65, как и в первом методе.

Рис. 4

Вопросы

1. Что такое динамические ряды? Какие функции они выполняют?

2. Какие функции используются в программе Excel для прогнозирования?

  • статистический метод
  • экспертная оценка (метод Дельфи)
  • ПРЕДСКАЗ
  • КОРРЕЛ

3. Объясните назначение функции КОРРЕЛ.

4. Что такое линейный тренд?

  • Линейный тренд - это простой тип прогноза, который можно использовать только для описания закономерностей, которые уменьшаются с постоянной скоростью.
  • Линейный тренд - это простой тип прогноза, который подходит для описания моделей, которые увеличиваются или уменьшаются с постоянной скоростью.
  • Линейный тренд - это простой тип прогноза, который подходит для описания закономерностей, которые увеличиваются или уменьшаются с разной скоростью.
  • Линейный тренд - это простой тип прогноза, который можно использовать только для описания закономерностей, которые растут с постоянной скоростью.

5. Какие виды линейных трендов вы знаете?

  • Экспоненциальный
  • Единичный
  • Линейный
  • Логарифмический
  • Степенной
  • Квадратичный
  • Полиномиальный

6. Каково назначение функции ПРЕДСКАЗ?

Задания

Прикрепите свой файл к этому заданию, нажав «Добавить свой материал».

Таблица 2. Погода за 10 дней

  • Абсолютный метод
  • Экспертная оценка (метод Дельфи)
  • Моделирование
  • Совершенствование
  • Статистический метод
Өтінемін күте тұрыңыз