ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 21.10.2020
Просмотров: 1371
Скачиваний: 11
Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных
www.specialist.ru
Центр Компьютерного обучения «Специалист»
16
Работа с функцией СМЕЩ
Функция СМЕЩ (OFFSET) умеет выдавать ссылку на диапазон нужного размера, сдвинутый
относительно исходной ячейки на заданное количество строк и столбцов
Возвращаемая ссылка может быть отдельной ячейкой или диапазоном ячеек. Можно задавать
количество возвращаемых строк и столбцов.
СМЕЩ
(Ссылка;Смещ_по_строкам;Смещ_по_столбцам;Высота;Ширина)
– возвращает ссылку
на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.
OFFSET
(
Reference;Rows;Cols;Height;Width
)
Ссылка
[Reference] – начальная ссылка, от которой вычисляется смещение. Может быть
ссылкой на ячейку или на диапазон смежных ячеек.
Смещ_по_строкам
[Rows] – количество строк (число), которые нужно отсчитать вверх
(отрицательное число) или вниз (положительное число) относительно начальной ссылки.
Смещ_по_столбцам
[Cols] – количество столбцов (число), которые нужно отсчитать влево
(отрицательное число) или вправо (положительное число) относительно начальной
ссылки.
Высота
[Height] – число строк. Только положительное число.
Ширина
[Width] – число столбцов. Только положительное число.
ПРИМЕР
: Определить общее количество отгрузок за несколько месяцев (с 4-го по 9-й месяц
включительно) с нескольких складов (с первых 4-х).
=СУММ(СМЕЩ(Начало;Смещение_по_строкам;Смещение_по_столбцам;Высота;Ширина))
–
суммирует количество отгрузок из диапазона ячеек, определяемого смещениями относительно
начального значения (ячейка
F2
).
Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных
Центр Компьютерного обучения «Специалист»
www.specialist.ru
17
Модуль 2.
П
РОГНОЗИРОВАНИЕ ДАННЫХ
Умение прогнозировать (предсказывать) будущее развитие событий – затраты следующего года
или ожидаемые прибыли от внедренных инноваций – это важная составляющая в планировании и
анализе любого современного бизнеса.
Существуют различные методы прогнозирования, однако не все они могут быть описаны
математической зависимостью. В Excel осуществляется
прогнозирование на основе анализа
временных рядов
– подразумевается, что имеется ряд наблюдений, проводившихся регулярно
через равные интервалы времени (например, год, квартал, месяц, неделя, сутки или даже
минуты, в зависимости от характера рассматриваемой переменной) и, проведя анализ поведения
этих наблюдений, можно создать прогноз на будущее. Исходные данные для анализа можно
разделить на составляющие:
Тренд
– общее направление развития ситуации, общая тенденция.
Сезонная составляющая
– периодически повторяющиеся колебания, оцениваемые
коэффициентами сезонности.
Случайные возмущения
– помехи, маскирующие основной тренд и сезонные колебания.
Выделение тренда
Скользящее среднее
Скользящее среднее [Moving Average] используется для расчета значений в прогнозируемом
периоде на основе среднего значения переменной для указанного числа предшествующих
периодов. Скользящее среднее, в отличие от простого среднего для всей выборки, содержит
сведения о тенденциях изменения данных. Этот метод может использоваться для прогноза сбыта,
запасов и других процессов.
Сглаживание ряда динамики с помощью скользящей средней заключается в том, что вычисляется
средний уровень из определенного числа первых по порядку уровней ряда, затем – средний
уровень из такого же числа уровней, начиная со второго, далее – начиная с третьего и т.д. Таким
образом, при расчетах среднего уровня как бы «Скользят» по ряду динамики от его начала к
концу, каждый раз отбрасывая один уровень в начале и добавляя один следующий.
Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных
www.specialist.ru
Центр Компьютерного обучения «Специалист»
18
Интервал сглаживания, т.е. число входящих в него уровней определяется по правилу: если
необходимо сгладить мелкие, беспорядочные колебания, то интервал сглаживания берут по
возможности большим; если же нужно сохранить более мелкие волны и освободиться от
периодически повторяющихся колебаний – интервал сглаживания уменьшают.
Функции регрессионного анализа
Функция ПРЕДСКАЗ
Вычисляет или предсказывает будущее значение по существующим значениям. Предсказываемое
значение – это Y-значение, соответствующее заданному X-значению. Известные значения – это X-
и Y-значения, а новое значение предсказывается с использованием линейной регрессии, которое
описывается линейным уравнением вида:
.
Коэффициент
отвечает за угол наклона прямой и
вычисляется по формуле:
∑ ̅ ̅
∑ ̅
, а
коэффициент
определяет пересечение прямой с
вертикальной осью, рассчитывается по формуле:
̅ ̅
Определение коэффициентов происходит по
методу наименьших квадратов, т.е. наклон и
положение линии тренда подбирается таким, чтобы сумма квадратов отклонений исходных
данных от построенной линии тренда была минимальна.
ПРЕДСКАЗ
(Х;Известные_значения_Y; Известные_значения_X)
– возвращает значение
линейного тренда.
FORECAST
(X;Known_y’s
;
Known_x’s)
Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных
Центр Компьютерного обучения «Специалист»
www.specialist.ru
19
X
[X] – точка (элемент) данных, для которой предсказывается значение.
Известные_значения_Y
*Known_y’s+ – известные значения зависимой переменной, на
основании которых делается предсказание.
Известные_значения_X
[Known_x’s+ – известные значения независимой переменной.
Функция ТЕНДЕНЦИЯ
Функция ПРЕДСКАЗ может анализировать влияние только одного массива исходных данных. Если
на полученные результаты оказывает влияние набор исходных данных, то задача прогноза
решается с помощью функции ТЕНДЕНЦИЯ.
Определение линии тренда происходит так же по методу наименьших квадратов, но т.к. ведется
обработка набора данных, то необходимо вводить как формулу массива, завершая ее клавишами
Ctrl
+
Shift
+
Enter
.
ТЕНДЕНЦИЯ
(Известные_значения_Y; Известные_значения_X; Новые_значения_X;Конст)
–
возвращает значения в соответствии с линейным трендом.
TREND
(Known_y’s
;
Known_x’s
;
New_x’s;Const)
Известные_значения_Y
*Known_y’s+ – известные значения результирующих параметров,
на основании которых делается предсказание.
Известные_значения_X
*Known_y’s+ – диапазон ячеек с набором влияющих переменных
(по размерам соответствует диапазону Y).
Новые_значения_X
[New_x’s] – новые значения наборов Х, для которых будут
определяться новые значения Y.
Конст
[Const] – логическое значение (0 или 1).
Истина или 1 (можно не указывать) означает, что константа
определяется по формуле.
Ложь или 0 – уравнение тренда проходит через начало координат.
Функция РОСТ
Если взаимосвязь явно нелинейная, то для прогноза используется функция РОСТ. Существует
большое количество типов данных, которые
изменяются во времени нелинейным способом.
Примерами таких данных являются объем
продаж новой продукции; прирост населения,
сложные проценты и др.
Известные значения – это X- и Y-значения, а новое
значение предсказывается по экспоненциальной
регрессии, которое описывается уравнением
вида:
.
РОСТ
(Известные_значения_Y; Известные_значения_X; Новые_значения_X;Конст)
– возвращает
значения в соответствии с экспоненциальным трендом.
GROWTH
(Known_y’s
;
Known_x’s
;
New_x’s;Const)
Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных
www.specialist.ru
Центр Компьютерного обучения «Специалист»
20
Известные_значения_Y
*Known_y’s+ – известные значения результирующих параметров,
на основании которых делается предсказание.
Известные_значения_X
[Known_x’s+ – диапазон ячеек с набором влияющих переменных
(по размерам соответствует диапазону Y).
Новые_значения_X
[New_x’s] – новые значения наборов Х, для которых будут
определяться новые значения Y.
Конст
[Const] – логическое значение (0 или 1).
Истина или 1 (можно не указывать) означает, что константа
определяется по формуле.
Ложь или 0 означает, что коэффициент
=1
и уравнение тренда тогда имеет вид:
.
Построение линий тренда
В случаях, когда требуется получить более точные прогнозируемые значения, необходимо
воспользоваться функциями. В остальных случаях, можно построить плоскую (двумерную)
диаграмму типа Гистограмма, График, Точечная, С областями и т.п., отобразить линию тренда и на
ее основе сделать прогноз графическим способом.
1.
Щелкнуть правой кнопкой мыши по нужному ряду на диаграмме и выбрать команду
Добавить линию тренда
[Add trendline].
2.
В открывшемся окне выбрать:
Тип линии для построения тренда.
Прогноз вперед на … периодов
[Forecast Forward …
periods] – продление линии тренда за пределы
известных данных диаграммы.
Показать уравнение на диаграмме
[Display Equation
on chart] – отображение математической формулы
линии тренда на графике для дальнейшего
использования.
Поместить на диаграмму величину
достоверности аппроксимации R^2
[Dispay
R-squared value on chart] – отображение на
диаграмме значение коэффициента
детерминации R^2. Чем ближе значение к 1,
тем лучше линия тренда описывает
исходные данные. Значение коэффициента
детерминации <0,7 говорят о том, что наша
кривая недостаточно хорошо описывает
исходные данные, и стоит воспользоваться
другими способами прогнозирования или
построить математическую модель
прогнозируемой ситуации.