Функция ТЕНДЕНЦИЯ аппроксимирует прямой линией (по методу наименьших квадратов) массивы известных значений yи x. Рассчитывает значения yдля новых значений x.
Синтаксис:
изв_знач_y - это множество значений y, которые уже известны для соотношения y = mx + b.
изв_знач_x - это необязательное множество значений x, которые уже известны для соотношения y = mx + b.
нов_знач_x - это новые значения x, для которых ТЕНДЕНЦИЯ возвращает соответствующие значения y.
Константа - это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.
Если константа имеет значение ИСТИНА или опущена, то b вычисляется обычным образом.
Если константа имеет значение ЛОЖЬ, то b полагается равным 0, и значения mподбираются таким образом, чтобы выполнялось соотношение y = mx.
В качестве примера использования этой функции выполним расчет прибыли за 2006 год на основе данных, приведенных на рис. 92.
Установите курсор в ячейку С12, выберите команду Функция в меню Вставка, а затем выберите функцию ТЕНДЕНЦИЯ. На экране появится диалоговое окно функции ТЕНДЕНЦИЯ (рис. 97).
Рис. 97. Окно диалога функции ТЕНДЕНЦИЯ
в появившемся окне введите исходные данные и получите результат (рис. 98).
Рис. 98. Результаты прогнозирования с помощью функции ТЕНДЕНЦИЯ
Сведем результаты прогнозирования с помощью этих функций в одну таблицу (рис. 99).
Рис. 99. Результаты прогнозирования с помощью трех функций
Как видно из этого рисунка результаты прогнозирования с использованием функций ПРЕДСКАЗ и ТЕНДЕНЦИЯ совпадают, так как в обоих случаях использовалась линейная зависимость, в то время как функция РОСТ дала большее значение прогнозируемой величины вследствие применения экспоненциальной зависимости.
Прогнозирование различных экономических показателей можно проводить с использованием диаграмм. После создания диаграммы в нее добавляется линия тренда и показывается уравнение тренда, а коэффициенты этого уравнения можно использовать для получения числовых значений прогнозируемых данных. Причем, в отличие от ранее рассмотренных статистических функций ПРЕДСКАЗ, РОСТ и ТЕНДЕНЦИЯ, которые позволяют использовать только линейные и экспоненциальные кривые, для линий тренда могут быть использованы помимо линейного и экспоненциального также логарифмический, полиномиальный и степенной законы. Это существенно расширяет возможности прогнозирования различных показателей.
В качестве примера рассмотрим прогноз объема продаж на ближайшие два месяца по результатам продаж за предыдущие семь месяцев (рис. 100). Исходные данные соответствуют так называемым сезонным колебаниям спроса.
Рис. 100. Исходные данные по продажам
На основе этих данных создадим гистограмму (она более рельефно отражает характер зависимости). Выделим область данных диаграммы и запустим Мастер диаграмм (нажав на пиктограмму ). Окно диалога шага 1 Мастера диаграмм приведено на рис. 101.
Рис. 101. Окно диалога шага 1 Мастера диаграмм
Выберите обычную гистограмму и нажмите кнопку Далее. На экране появится окно диалога шага 2 с гистограммой (рис. 101).
Рис. 101. Гистограмма исходных данных
На диаграмме показаны два ряда значений – месяцы и объем продаж. Первый ряд (месяцы) нам не нужен, поэтому удалите его, выбрав вкладку Ряд (рис. 102).
Рис. 102. Удаление ряда 1
и нажмите кнопку Удалить(ряд 1 удалится), а затем нажмите кнопку Далее. На экране появится окно диалога шага 3 Мастера диаграмм (рис.103). Если бы Вы выбрали из списка типов диаграмм не гистограмму, а точечную диаграмму, то она вывела бы на экран сразу только один ряд данных - ряд 2.
Рис. 103. Окно диалога шага 3 Мастера диаграмм
Введите в соответствующие поля название диаграммы и подписи по осям X и Y. Нажмите кнопку Далееи затем кнопку Готово. На рис. 104 приведена гистограмма исходных данных.
Рис. 104. Гистограмма исходных данных
Из рис. 104 видно, что с помощью функций ПРЕДСКАЗ, РОСТ или ТЕНДЕНЦИЯ учесть эти сезонные изменения спроса нельзя. Установите курсор на столбике и нажмите правую кнопку мыши. Откроется контекстное меню, в котором выберите команду Добавить линию тренда(рис. 105).
Рис. 105. Добавление линии тренда на диаграмму
После выбора этой команды на экране появится окно диалога Линия тренда(рис. 106).
Рис. 106 Окно диалога Линия тренда
Наиболее подходящим для наших исходных данных является полином нечетной степени. Выберите полином третьей степени, а на вкладке Параметры(рис. 107) поставьте галочку в окошке показать уравнение на диаграмме.
Рис. 107. Окно диалога Параметры
Нажмите кнопку ОК, и на экране появится результирующая диаграмма с уравнением линии тренда (рис. 108).
Рис. 108. Результирующая диаграмма с линией тренда
Прогноз на два будущих месяца, полученный подстановкой значений 8 и 9 в уравнение линии тренда, приведен в таблице (рис. 109).
Рис. 109. Результаты прогнозирования
Диаграмма, построенная с учетом исходных данных и результатов прогнозирования, приведена на рис. 110.
Рис. 110. Диаграмма, построенная с учетом результатов прогнозирования
Как следует из анализа рис. 110 прогноз на 9-ый месяц вряд ли удачен. Поэтому необходимо более аккуратно выбирать как интервал прогнозирования (небольшой), так и вид линии тренда. Так, выбор в качестве линии тренда полинома пятой степени дает более удачный результат (рис. 111).
Рис. 111. Результаты прогнозирования для полинома пятой степени
). Окно диалога шага 1 Мастера диаграмм приведено на рис. 101.
