- •Министерство образования и науки украины
- •Содержание
- •1.2. Классификация и основные виды бизнес - процессов.
- •1.3. Описание организационной структуры
- •5. Структурные схемы.
- •2. Задание
- •Лабораторная работа № 2 Базовые технологии автоматизации задач управления. Анализ данных и принятие решений средствами ms Office
- •1. Теоретические сведения
- •1.1. Сводные таблицы для анализа данных
- •Структура сводной таблицы
- •1.2. Многомерные (olap) источники данных
- •2. Построение сводной таблицы
- •2.1. Создадим базу данных
- •2.2. Построим сводную таблицу
- •2.3. Повышение наглядности и удобства получения данных
- •2.4. Анализ данных
- •Инструментальная панель "Сводные таблицы"
- •Сводные диаграммы
- •2.5. Построение olap-куба
- •3. Задание
- •Варианты заданий
- •Лабораторная работа № 3 Анализ данных и прогноз для целей управления
- •1. Теоретические сведения
- •1.1. Методы прогнозирования
- •1.2. Методы краткосрочного прогноза
- •1.3. Метод экспоненциального сглаживания
- •Прогнозирование нестационарных показателей
- •1.5. Сезонный спрос
- •1.6. Среднесрочный прогноз и методы регрессионного анализа
- •2. Встроенные функции Excel и прогнозирование
- •2.1. Функция линейн
- •Пример выполнения задачи прогнозирования Прогноз произведем на основании уравнения регрессии (применения функции линейн в задаче прогнозирования)
- •2.2. Функция тенденция
- •2.3. Функция предсказ
- •4. Практический алгоритм решения задачи Прогнозирования
- •Задание
- •Лабораторная работа № 4
- •1.3. Пример использование таблицы подстановок
- •2. Сценарии
- •3.Оптимизация и анализ "Что, если ...?"
- •4. Задание
- •Варианты заданий
2. Встроенные функции Excel и прогнозирование
Для решения задач прогнозирования в Excel встроены несколько функций. По существу все они сводятся к нахождению оценок по методу наименьших квадратов в задаче линейной регрессии. Наряду с оценками вычисляются и их статистические характеристики, что позволяет строить доверительные интервалы и делать выводы, имеющие вероятностный характер.
2.1. Функция линейн
В общем случае решает задачу линейной множественной регрессии, вычисляя по методу наименьших квадратов вектор оценок параметров. Используется описанная нами выше модель:
Y = X*a + E (3.23)
Синтаксис вызова этой функции:
ЛИНЕЙН (Известные_значения_Y; Известные_значения_X; Конст; Статистика)
Параметры функции имеют следующий смысл:
Известные_значения_Y - задает вектор измерений.
Известные_значения_X - в общем случае матрица значений наблюдаемых параметров. Если речь идет о временном тренде, то элементы X задают моменты времени, в которые проводились измерения. Можно опустить X, если значения элементов составляют последовательность 1, 2, 3 и т. д.
Булев параметр "Конст" равен Истина (True), если в линейной записи модели присутствует дополнительно свободный член b, не входящий в вектор параметров a.
Булев параметр "Статистика" равен Истина (True), если наряду с оценками параметров вычисляются и статистические характеристики.
Результат вычислений этой функции - массив, в общем случае состоящий из 5 строк и n+1 столбцов, где n - это размерность вектора искомых параметров a.
an, an-1, … a1, b
σn, σn-1, … σ1, σb
R*R, σY
F, df
Ssreg, Ssresid
В первой строке идут оценки параметров a и свободного члена b. Оценки идут в обратном порядке, начиная с an. Они и определяют линию регрессии, позволяя рассчитать прогнозируемое значение Y в любой точке, где заданы значения наблюдаемых параметров.
В следующей строке идут среднеквадратические отклонения этих оценок. Выше мы показали, как вычислить полную корреляционную матрицу оценок. Среднеквадратические отклонения являются диагональными элементами этой матрицы. Точнее, на диагонали стоят их квадраты - дисперсии DI = σI * σI. Значения σI позволяют построить доверительный интервал для соответствующих оценок и вынести суждение об их значимости в линейной модели. Как вычисляются эти значения в Excel, нам осталось непонятно, так как алгоритм не описан. Можно лишь заметить, что применяемый алгоритм не всегда корректен с позиций классической математической статистики.
Например. Пусть оцениваются два параметра a и b, (Y = a*t +b).
И пусть выполнены всего два измерения - Y1 и Y2. Тогда, каковы бы ни были ошибки в измерениях, линия регрессии пройдет через две наблюдаемые точки. Excel скажет, что ошибок в оценках параметров нет, и выдаст значения σ1 и σ2 , равные 0, хотя ясно, что это не так.
Коэффициент детерминации R2 имеет значение в интервале от 0 до 1 и позволяет оценить, насколько хорошо сглаживаются измеренные значения линией регрессии. Он равен 1, если линия регрессии проходит через все измеренные точки. При этом можно полагать, что есть строгая функциональная зависимость между измеряемым значением Y и параметрами ai. Предыдущий пример показывает, что недостаточное количество измерений может приводить к такому же результату. Поэтому и к этому параметру надо относиться с осторожностью. Вычисляется коэффициент детерминации по формуле:
R2 = Dreg / D (3.24)
и представляет отношение дисперсии, объясняемой регрессией, к общей дисперсии. О смысле этих терминов чуть ниже.
Вы должны знать ,что означают и как используются параметры σY, F и число степеней свободы df.
Последние два значения - Ssreg и Ssresid задают дисперсию, объясняемую регрессией, и остаточную дисперсию, представляющую разность между общей дисперсией и Dreg. Обе дисперсии вычисляются "обычным" способом:
D = (YI - E)2 ; Dreg = (YI - E)2 , (3.25)
где E - среднее значение измеренных значений, а YI - сглаженные значения, вычисленные из уравнения регрессии.
Итак, для решения задач прогнозирования функция ЛИНЕЙН позволяет построить уравнение регрессии, как для временных рядов, так и в общем случае линейной множественной регрессии, когда наблюдается несколько параметров.