Теоретическая справка

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

Такого рода задачи приближения функций часто возникают:

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

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

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

В Excel для построения регрессий имеются две возможности.

  1. Добавление выбранных регрессий (линий тренда - trendlines) в диаграмму, построенную на основе таблицы данных для исследуемой характеристики процесса (доступно лишь при наличии построенной диаграммы);
  2. Использование встроенных статистических функций рабочего листа Excel, позволяющих получать регрессии (линии тренда) непосредственно на основе таблицы исходных данных.

Добавление линий тренда в диаграмму

Для таблицы данных, описывающих некоторый процесс и представленных диаграммой, в Excel имеется эффективный инструмент регрессионного анализа, позволяющий:

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

На основе данных диаграммы Excel позволяет получать линейный, полиномиальный, логарифмический, степенной, экспоненциальный типы регрессий, которые задаются уравнением:

y = y(x)

где x - независимая переменная, которая часто принимает значения последовательности натурального ряда чисел (1; 2; 3; …) и производит, например, отсчет времени протекания исследуемого процесса (характеристики).

1 . Линейная регрессия хороша при моделировании характеристик, значения которых увеличиваются или убывают с постоянной скоростью. Это наиболее простая в построении модель исследуемого процесса. Она

y = mx + b

где m - тангенс угла наклона линейной регрессии к оси абсцисс; b - координата точки пересечения линейной регрессии с осью ординат.

2 . Полиномиальная линия тренда полезна для описания характеристик, имеющих несколько ярко выраженных экстремумов (максимумов и минимумов). Выбор степени полинома определяется количеством экстремумов исследуемой характеристики. Так, полином второй степени может хорошо описать процесс, имеющий только один максимум или минимум; полином третьей степени - не более двух экстремумов; полином четвертой степени - не более трех экстремумов и т. д.

В этом случае линия тренда строится в соответствии с уравнением:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

где коэффициенты c0, c1, c2,... c6 - константы, значения которых определяются в ходе построения.

3 . Логарифмическая линия тренда с успехом применяется при моделировании характеристик, значения которых вначале быстро меняются, а затем постепенно стабилизируются.

Строится в соответствии с уравнением:

y = c ln(x) + b

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

Строится в соответствии с уравнением:

y = c xb

где коэффициенты b, с - константы.

5 . Экспоненциальную линию тренда следует использовать в том случае, если скорость изменения данных непрерывно возрастает. Для данных, содержащих нулевые или отрицательные значения, этот вид приближения также неприменим.

Строится в соответствии с уравнением:

y = c ebx

где коэффициенты b, с - константы.

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

Определяется по формуле:

Для добавления линии тренда к ряду данных следует:

  • активизировать построенную на основе ряда данных диаграмму, т. е. щелкнуть в пределах области диаграммы. В главном меню появится пункт Диаграмма;
  • после щелчка на этом пункте на экране появится меню, в котором следует выбрать команду Добавить линию тренда.

Эти же действия легко реализуются, если навести указатель мыши на график, соответствующий одному из рядов данных, и щелкнуть правой кнопкой мыши; в появившемся контекстном меню выбрать команду Добавить линию тренда. На экране появится диалоговое окно Линия тренда с раскрытой вкладкой Тип (рис. 1).

После этого необходимо:

Выбрать на вкладке Тип необходимый тип линии тренда (по умолчанию выбирается тип Линейный). Для типа Полиномиальная в поле Степень следует задать степень выбранного полинома.

1 . В поле Построен на ряде перечислены все ряды данных рассматриваемой диаграммы. Для добавления линии тренда к конкретному ряду данных следует в поле Построен на ряде выбрать его имя.

При необходимости, перейдя на вкладку Параметры (рис. 2), можно для линии тренда задать следующие параметры:

  • изменить название линии тренда в поле Название аппроксимирующей (сглаженной) кривой.
  • задать количество периодов (вперед или назад) для прогноза в поле Прогноз;
  • вывести в область диаграммы уравнение линии тренда, для чего следует включить флажок показать уравнение на диаграмме;
  • вывести в область диаграммы значение достоверности аппроксимации R2, для чего следует включить флажок поместить на диаграмму величину достоверности аппроксимации (R^2);
  • задать точку пересечения линии тренда с осью Y, для чего следует включить флажок пересечение кривой с осью Y в точке;
  • щелкнуть на кнопке OK, чтобы закрыть диалоговое окно.

Для того, чтобы начать редактирование уже построенной линии тренда, существует три способа:

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

    • На экране появится диалоговое окно Формат линии тренда (рис. 3), содержащее три вкладки: Вид, Тип, Параметры, причем содержимое последних двух полностью совпадает с аналогичными вкладками диалогового окна Линия тренда (рис.1-2). На вкладке Вид, можно задать тип линии, ее цвет и толщину.

    Для удаления уже построенной линии тренда следует выбрать удаляемую линию тренда и нажать клавишу Delete.

    Достоинствами рассмотренного инструмента регрессионного анализа являются:

    • относительная легкость построения на диаграммах линии тренда без создания для нее таблицы данных;
    • достаточно широкий перечень типов предложенных линий трендов, причем в этот перечень входят наиболее часто используемые типы регрессии;
    • возможность прогнозирования поведения исследуемого процесса на произвольное (в пределах здравого смысла) количество шагов вперед, а также назад;
    • возможность получения уравнения линии тренда в аналитическом виде;
    • возможность, при необходимости, получения оценки достоверности проведенной аппроксимации.

    К недостаткам можно отнести следующие моменты:

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

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

    Нельзя дополнить линиями тренда ряды данных на объемных, нормированных, лепестковых, круговых и кольцевых диаграммах.

    Использование встроенных функций Excel

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

    В Excel имеется несколько функций для построения линейной регрессии, в частности:

    • ТЕНДЕНЦИЯ;
    • ЛИНЕЙН;
    • НАКЛОН и ОТРЕЗОК.

    А также несколько функций для построения экспоненциальной линии тренда, в частности:

    • РОСТ;
    • ЛГРФПРИБЛ.

    Следует отметить, что приемы построения регрессий с помощью функций ТЕНДЕНЦИЯ и РОСТ практически совпадают. То же самое можно сказать и о паре функций ЛИНЕЙН и ЛГРФПРИБЛ. Для четырех этих функций при создании таблицы значений используются такие возможности Excel, как формулы массивов, что несколько загромождает процесс построения регрессий. Заметим также, что построение линейной регрессии, на наш взгляд, легче всего осуществить с помощью функций НАКЛОН и ОТРЕЗОК, где первая из них определяет угловой коэффициент линейной регрессии, а вторая - отрезок, отсекаемый регрессией на оси ординат.

    Достоинствами инструмента встроенных функций для регрессионного анализа являются:

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

    А к недостаткам относится то, что в Excel нет встроенных функций для создания других (кроме линейного и экспоненциального) типов линий тренда. Это обстоятельство часто не позволяет подобрать достаточно точную модель исследуемого процесса, а также получить близкие к реальности прогнозы. Кроме того, при использовании функций ТЕНДЕНЦИЯ и РОСТ не известны уравнения линий тренда.

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

    Примеры решения конкретных задач

    Рассмотрим решение конкретных задач с помощью перечисленных инструментов пакета Excel.

    Задача 1

    С таблицей данных о прибыли автотранспортного предприятия за 1995-2002 гг. необходимо выполнить следующие действия.

    1. Построить диаграмму.
    2. В диаграмму добавить линейную и полиномиальную (квадратичную и кубическую) линии тренда.
    3. Используя уравнения линий тренда, получить табличные данные по прибыли предприятия для каждой линии тренда за 1995-2004 г.г.
    4. Составить прогноз по прибыли предприятия на 2003 и 2004 гг.

    Решение задачи

    1. В диапазон ячеек A4:C11 рабочего листа Excel вводим рабочую таблицу, представленную на рис. 4.
    2. Выделив диапазон ячеек В4:С11, строим диаграмму.
    3. Активизируем построенную диаграмму и по описанной выше методике после выбора типа линии тренда в диалоговом окне Линия тренда (см. рис. 1) поочередно добавляем в диаграмму линейную, квадратичную и кубическую линии тренда. В этом же диалоговом окне открываем вкладку Параметры (см. рис. 2), в поле Название аппроксимирующей (сглаженной) кривой вводим наименование добавляемого тренда, а в поле Прогноз вперед на: периодов задаем значение 2, так как планируется сделать прогноз по прибыли на два года вперед. Для вывода в области диаграммы уравнения регрессии и значения достоверности аппроксимации R2 включаем флажки показывать уравнение на экране и поместить на диаграмму величину достоверности аппроксимации (R^2). Для лучшего визуального восприятия изменяем тип, цвет и толщину построенных линий тренда, для чего воспользуемся вкладкой Вид диалогового окна Формат линии тренда (см. рис. 3). Полученная диаграмма с добавленными линиями тренда представлена на рис. 5.
    4. Для получения табличных данных по прибыли предприятия для каждой линии тренда за 1995-2004 гг. воспользуемся уравнениями линий тренда, представленными на рис. 5. Для этого в ячейки диапазона D3:F3 вводим текстовую информацию о типе выбранной линии тренда: Линейный тренд, Квадратичный тренд, Кубический тренд. Далее вводим в ячейку D4 формулу линейной регрессии и, используя маркер заполнения, копируем эту формулу c относительными ссылками в диапазон ячеек D5:D13. Следует отметить, что каждой ячейке с формулой линейной регрессии из диапазона ячеек D4:D13 в качестве аргумента стоит соответствующая ячейка из диапазона A4:A13. Аналогично для квадратичной регрессии заполняется диапазон ячеек E4:E13, а для кубической регрессии - диапазон ячеек F4:F13. Таким образом, составлен прогноз по прибыли предприятия на 2003 и 2004 гг. с помощью трех трендов. Полученная таблица значений представлена на рис. 6.

    Задача 2

    1. Построить диаграмму.
    2. В диаграмму добавить логарифмическую, степенную и экспоненциальную линии тренда.
    3. Вывести уравнения полученных линий тренда, а также величины достоверности аппроксимации R2 для каждой из них.
    4. Используя уравнения линий тренда, получить табличные данные о прибыли предприятия для каждой линии тренда за 1995-2002 гг.
    5. Составить прогноз о прибыли предприятия на 2003 и 2004 гг., используя эти линии тренда.

    Решение задачи

    Следуя методике, приведенной при решении задачи 1, получаем диаграмму с добавленными в нее логарифмической, степенной и экспоненциальной линиями тренда (рис. 7). Далее, используя полученные уравнения линий тренда, заполняем таблицу значений по прибыли предприятия, включая прогнозируемые значения на 2003 и 2004 гг. (рис. 8).

    На рис. 5 и рис. видно, что модели с логарифмическим трендом, соответствует наименьшее значение достоверности аппроксимации

    R2 = 0,8659

    Наибольшие же значения R2 соответствуют моделям с полиномиальным трендом: квадратичным (R2 = 0,9263) и кубическим (R2 = 0,933).

    Задача 3

    С таблицей данных о прибыли автотранспортного предприятия за 1995-2002 гг., приведенной в задаче 1, необходимо выполнить следующие действия.

    1. Получить ряды данных для линейной и экспоненциальной линии тренда с использованием функций ТЕНДЕНЦИЯ и РОСТ.
    2. Используя функции ТЕНДЕНЦИЯ и РОСТ, составить прогноз о прибыли предприятия на 2003 и 2004 гг.
    3. Для исходных данных и полученных рядов данных построить диаграмму.

    Решение задачи

    Воспользуемся рабочей таблицей задачи 1 (см. рис. 4). Начнем с функции ТЕНДЕНЦИЯ:

    1. выделяем диапазон ячеек D4:D11, который следует заполнить значениями функции ТЕНДЕНЦИЯ, соответствующими известным данным о прибыли предприятия;
    2. вызываем команду Функция из меню Вставка. В появившемся диалоговом окне Мастер функций выделяем функцию ТЕНДЕНЦИЯ из категории Статистические, после чего щелкаем по кнопке ОК. Эту же операцию можно осуществить нажатием кнопки (Вставка функции) стандартной панели инструментов.
    3. В появившемся диалоговом окне Аргументы функции вводим в поле Известные_значения_y диапазон ячеек C4:C11; в поле Известные_значения_х - диапазон ячеек B4:B11;
    4. чтобы вводимая формула стала формулой массива, используем комбинацию клавиш + + .

    Введенная нами формула в строке формул будет иметь вид: ={ТЕНДЕНЦИЯ(C4:C11;B4:B11)}.

    В результате диапазон ячеек D4:D11 заполняется соответствующими значениями функции ТЕНДЕНЦИЯ (рис. 9).

    Для составления прогноза о прибыли предприятия на 2003 и 2004 гг. необходимо:
    1. выделить диапазон ячеек D12:D13, куда будут заноситься значения, прогнозируемые функцией ТЕНДЕНЦИЯ.
    2. вызвать функцию ТЕНДЕНЦИЯ и в появившемся диалоговом окне Аргументы функции ввести в поле Известные_значения_y - диапазон ячеек C4:C11; в поле Известные_значения_х - диапазон ячеек B4:B11; а в поле Новые_значения_х - диапазон ячеек B12:B13.
    3. превратить эту формулу в формулу массива, используя комбинацию клавиш Ctrl + Shift + Enter.
    4. Введенная формула будет иметь вид: ={ТЕНДЕНЦИЯ(C4:C11;B4:B11;B12:B13)}, а диапазон ячеек D12:D13 заполнится прогнозируемыми значениями функции ТЕНДЕНЦИЯ (см. рис. 9).

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

    На рис.10 представлена таблица в режиме показа формул.

    Для исходных данных и полученных рядов данных построена диаграмма, изображенная на рис. 11.

    Задача 4

    С таблицей данных о поступлении в диспетчерскую службу автотранспортного предприятия заявок на услуги за период с 1 по 11 число текущего месяца необходимо выполнить следующие действия.

    1. Получить ряды данных для линейной регрессии: используя функции НАКЛОН и ОТРЕЗОК; используя функцию ЛИНЕЙН.
    2. Получить ряд данных для экспоненциальной регрессии с использованием функции ЛГРФПРИБЛ.
    3. Используя вышеназванные функции, составить прогноз о поступлении заявок в диспетчерскую службу на период с 12 по 14 число текущего месяца.
    4. Для исходных и полученных рядов данных построить диаграмму.

    Решение задачи

    Отметим, что, в отличие от функций ТЕНДЕНЦИЯ и РОСТ, ни одна из перечисленных выше функций (НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ) не является регрессией. Эти функции играют лишь вспомогательную роль, определяя необходимые параметры регрессии.

    Для линейной и экспоненциальной регрессий, построенных с помощью функций НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ, внешний вид их уравнений всегда известен, в отличие от линейной и экспоненциальной регрессий, соответствующих функциям ТЕНДЕНЦИЯ и РОСТ.

    1 . Построим линейную регрессию, имеющую уравнение:

    y = mx+b

    с помощью функций НАКЛОН и ОТРЕЗОК, причем угловой коэффициент регрессии m определяется функцией НАКЛОН, а свободный член b - функцией ОТРЕЗОК.

    Для этого осуществляем следующие действия:

    1. заносим исходную таблицу в диапазон ячеек A4:B14;
    2. значение параметра m будет определяться в ячейке С19. Выбираем из категории Статистические функцию Наклон; заносим диапазон ячеек B4:B14 в поле известные_значения_y и диапазон ячеек А4:А14 в поле известные_значения_х. В ячейку С19 будет введена формула: =НАКЛОН(B4:B14;A4:A14);
    3. по аналогичной методике определяется значение параметра b в ячейке D19. И ее содержимое будет иметь вид: =ОТРЕЗОК(B4:B14;A4:A14). Таким образом, необходимые для построения линейной регрессии значения параметров m и b будут сохраняться соответственно в ячейках C19, D19;
    4. далее заносим в ячейку С4 формулу линейной регрессии в виде: =$C*A4+$D. В этой формуле ячейки С19 и D19 записаны с абсолютными ссылками (адрес ячейки не должен меняться при возможном копировании). Знак абсолютной ссылки $ можно набить либо с клавиатуры, либо с помощью клавиши F4, предварительно установив курсор на адресе ячейки. Воспользовавшись маркером заполнения, копируем эту формулу в диапазон ячеек С4:С17. Получаем искомый ряд данных (рис. 12). В связи с тем, что количество заявок - целое число, следует установить на вкладке Число окна Формат ячеек числовой формат с числом десятичных знаков 0.

    2 . Теперь построим линейную регрессию, заданную уравнением:

    y = mx+b

    с помощью функции ЛИНЕЙН.

    Для этого:

    1. вводим в диапазон ячеек C20:D20 функцию ЛИНЕЙН как формулу массива: ={ЛИНЕЙН(B4:B14;A4:A14)}. В результате получаем в ячейке C20 значение параметра m, а в ячейке D20 - значение параметра b;
    2. вводим в ячейку D4 формулу: =$C*A4+$D;
    3. копируем эту формулу с помощью маркера заполнения в диапазон ячеек D4:D17 и получаем искомый ряд данных.

    3 . Строим экспоненциальную регрессию, имеющую уравнение:

    y = bmx

    с помощью функции ЛГРФПРИБЛ оно выполняется аналогично:

    в диапазон ячеек C21:D21 вводим функцию ЛГРФПРИБЛ как формулу массива: ={ ЛГРФПРИБЛ (B4:B14;A4:A14)}. При этом в ячейке C21 будет определено значение параметра m, а в ячейке D21 - значение параметра b;
  • в ячейку E4 вводится формула: =$D*$C^A4;
  • с помощью маркера заполнения эта формула копируется в диапазон ячеек E4:E17, где и расположится ряд данных для экспоненциальной регрессии (см. рис. 12).

    • На рис. 13 приведена таблица, где видны используемые нами функции с необходимыми диапазонами ячеек, а также формулы.

    Для исходных данных и полученных рядов данных построена диаграмма, изображенная на рис. 14.

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

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

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

    Но она может быть построена с применением одного из пяти видов аппроксимации:

    • Линейной;
    • Экспоненциальной;
    • Логарифмической;
    • Полиномиальной;
    • Степенной.

    Рассмотрим каждый из вариантов более подробно в отдельности.

    Способ 1: линейное сглаживание

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

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


    Сглаживание, которое используется в данном случае, описывается следующей формулой:

    В конкретно нашем случае формула принимает такой вид:

    y=-0,1156x+72,255

    Величина достоверности аппроксимации у нас равна 0,9418 , что является довольно приемлемым итогом, характеризующим сглаживание, как достоверное.

    Способ 2: экспоненциальная аппроксимация

    Теперь давайте рассмотрим экспоненциальный тип аппроксимации в Эксель.


    Общий вид функции сглаживания при этом такой:

    где e – это основание натурального логарифма.

    В конкретно нашем случае формула приняла следующую форму:

    y=6282,7*e^(-0,012*x)

    Способ 3: логарифмическое сглаживание

    Теперь настала очередь рассмотреть метод логарифмической аппроксимации.


    В общем виде формула сглаживания выглядит так:

    где ln – это величина натурального логарифма. Отсюда и наименование метода.

    В нашем случае формула принимает следующий вид:

    y=-62,81ln(x)+404,96

    Способ 4: полиномиальное сглаживание

    Настал черед рассмотреть метод полиномиального сглаживания.


    Формула, которая описывает данный тип сглаживания, приняла следующий вид:

    y=8E-08x^6-0,0003x^5+0,3725x^4-269,33x^3+109525x^2-2E+07x+2E+09

    Способ 5: степенное сглаживание

    В завершении рассмотрим метод степенной аппроксимации в Excel.


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

    Общая формула, описывающая данный метод имеет такой вид:

    В конкретно нашем случае она выглядит так:

    y = 6E+18x^(-6,512)

    Как видим, при использовании конкретных данных, которые мы применяли для примера, наибольший уровень достоверности показал метод полиномиальной аппроксимации с полиномом в шестой степени (0,9844 ), наименьший уровень достоверности у линейного метода (0,9418 ). Но это совсем не значит, что такая же тенденция будет при использовании других примеров. Нет, уровень эффективности у приведенных выше методов может значительно отличаться, в зависимости от конкретного вида функции, для которой будет строиться линия тренда. Поэтому, если для этой функции выбранный метод наиболее эффективен, то это совсем не означает, что он также будет оптимальным и в другой ситуации.

    Если вы пока не можете сразу определить, основываясь на вышеприведенных рекомендациях, какой вид аппроксимации подойдет конкретно в вашем случае, то есть смысл попробовать все методы. После построения линии тренда и просмотра её уровня достоверности можно будет выбрать оптимальный вариант.

    (Обратите внимание на дополнительный раздел от 04.06.2017 в конце статьи.)

    Учет и контроль! Те, кому за 40 должны хорошо помнить этот лозунг из эпохи построения социализма и коммунизма в нашей стране.

    Но без хорошо налаженного учета невозможно эффективное функционирование ни страны, ни области, ни предприятия, ни домашнего хозяйства при любой общественно-экономической формации общества! Для составления прогнозов и планов деятельности и развития необходимы исходные данные. Где их брать? Только один достоверный источник – это ваши статистические учетные данные предыдущих периодов времени.

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

    «Мы, помню, 5 лет назад изготавливали до 1000 штук таких изделий в месяц, а сейчас и 700 еле-еле собираем!». Открываем статистику и видим, что 5 лет назад и 500 штук не изготавливали…

    «Во сколько обходится километр пробега твоего автомобиля с учетом всех затрат?» Открываем статистику – 6 руб./км. Поездка на работу – 107 рублей. Дешевле, чем на такси (180 рублей) более чем в полтора раза. А бывали времена, когда на такси было дешевле…

    «Сколько времени требуется для изготовления металлоконструкций уголковой башни связи высотой 50 м?» Открываем статистику – и через 5 минут готов ответ…

    «Сколько будет стоить ремонт комнаты в квартире?» Поднимаем старые записи, делаем поправку на инфляцию за прошедшие годы, учитываем, что в прошлый раз купили материалы на 10% дешевле рыночной цены и – ориентировочную стоимость мы уже знаем…

    Ведя учет своей профессиональной деятельности, вы всегда будете готовы ответить на вопрос начальника: «Когда!!!???». Ведя учет домашнего хозяйства, легче спланировать расходы на крупные покупки, отдых и прочие расходы в будущем, приняв соответствующие меры по дополнительному заработку или по сокращению необязательных расходов сегодня.

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

    Аппроксимация в Excel статистических данных аналитической функцией.

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

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

    1. Включаем Excel и помещаем на лист таблицу с данными статистики.

    2. Далее строим и форматируем точечную диаграмму, в которой по оси X задаем значения аргумента – количество переработанных уголков в тоннах. По оси Y откладываем значения исходной функции – общий выпуск металлоконструкций в месяц, заданные таблицей.

    3. «Наводим» мышь на любую из точек на графике и щелчком правой кнопки вызываем контекстное меню (как говорит один мой хороший товарищ — работая в незнакомой программе, когда не знаешь, что делать, чаще щелкай правой кнопкой мыши…). В выпавшем меню выбираем «Добавить линию тренда…».

    4. В появившемся окне «Линия тренда» на вкладке «Тип» выбираем «Линейная».

    6. На графике появилась прямая линия, аппроксимирующая нашу табличную зависимость.

    Мы видим кроме самой линии уравнение этой линии и, главное, мы видим значение параметра R 2 – величины достоверности аппроксимации! Чем ближе его значение к 1, тем наиболее точно выбранная функция аппроксимирует табличные данные!

    7. Строим линии тренда, используя степенную, логарифмическую, экспоненциальную и полиномиальную аппроксимации по аналогии с тем, как мы строили линейную линию тренда.

    Лучше всех из выбранных функций аппроксимирует наши данные полином второй степени, у него максимальный коэффициент достоверности R 2 .

    Однако хочу вас предостеречь! Если вы возьмете полиномы более высоких степеней, то, возможно, получите еще лучшие результаты, но кривые будут иметь замысловатый вид…. Здесь важно понимать, что мы ищем функцию, которая имеет физический смысл. Что это означает? Это означает, что нам нужна аппроксимирующая функция, которая будет выдавать адекватные результаты не только внутри рассматриваемого диапазона значений X, но и за его пределами, то есть ответит на вопрос: «Какой будет выпуск металлоконструкций при количестве переработанных за месяц уголков меньше 45 и больше 168 тонн!» Поэтому я не рекомендую увлекаться полиномами высоких степеней, да и параболу (полином второй степени) выбирать осторожно!

    Итак, нам необходимо выбрать функцию, которая не только хорошо интерполирует табличные данные в пределах диапазона значений X=45…168, но и допускает адекватную экстраполяцию за пределами этого диапазона. Я выбираю в данном случае логарифмическую функцию, хотя можно выбрать и линейную, как наиболее простую. В рассматриваемом примере при выборе линейной аппроксимации в excel ошибки будут больше, чем при выборе логарифмической, но не на много.

    8. Удаляем все линии тренда с поля диаграммы, кроме логарифмической функции. Для этого щелкаем правой кнопкой мыши по ненужным линиям и в выпавшем контекстном меню выбираем «Очистить».

    9. В завершении добавим к точкам табличных данных планки погрешностей. Для этого правой кнопкой мыши щелкаем на любой из точек на графике и в контекстном меню выбираем «Формат рядов данных…» и настраиваем данные на вкладке «Y-погрешности» так, как на рисунке ниже.

    10. Затем щелкаем по любой из линий диапазонов погрешностей правой кнопкой мыши, выбираем в контекстном меню «Формат полос погрешностей…» и в окне «Формат планок погрешностей» на вкладке «Вид» настраиваем цвет и толщину линий.

    Аналогичным образом форматируются любые другие объекты диаграммы в Excel !

    Окончательный результат диаграммы представлен на следующем снимке экрана.

    Итоги.

    Результатом всех предыдущих действий стала полученная формула аппроксимирующей функции y=-172,01*ln (x)+1188,2. Зная ее, и количество уголков в месячном наборе работ, можно с высокой степенью вероятности (±4% — смотри планки погрешностей) спрогнозировать общий выпуск металлоконструкций за месяц! Например, если в плане на месяц 140 тонн уголков, то общий выпуск, скорее всего, при прочих равных составит 338±14 тонн.

    Для повышения достоверности аппроксимации статистических данных должно быть много. Двенадцать пар значений – это маловато.

    Из практики скажу, что хорошим результатом следует считать нахождение аппроксимирующей функции с коэффициентом достоверности R 2 >0,87. Отличный результат – при R 2 >0,94.

    На практике бывает трудно выделить один самый главный определяющий фактор (в нашем примере – масса переработанных за месяц уголков), но если постараться, то в каждой конкретной задаче его всегда можно найти! Конечно, общий выпуск продукции за месяц реально зависит от сотни факторов, для учета которых необходимы существенные трудозатраты нормировщиков и других специалистов. Только результат все равно будет приблизительным! Так стоит ли нести затраты, если есть гораздо более дешевое математическое моделирование!

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

    Затронутый вопрос аппроксимации функции одной переменной имеет широкое практическое применение в разных сферах жизни. Но гораздо большее применение имеет решение задачи аппроксимации функции нескольких независимых переменных…. Об этом и не только читайте в следующих статьях на блоге.

    Подписывайтесь на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы.

    Не забывайте подтверждать подписку кликом по ссылке в письме, которое придет к вам на указанную почту (может прийти в папку « Спам» )!!!

    С интересом прочту Ваши комментарии, уважаемые читатели! Пишите!

    P.S. (04.06.2017)

    Высокоточная красивая замена табличных данных простым уравнением.

    Вас не устраивают полученные точность аппроксимации (R 2 <0,95) или вид и набор функций, предлагаемые MS Excel?

    Размеры выражения и форма линии аппроксимирующего полинома высокой степени не радует глаз?

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

    При использовании предлагаемого алгоритма действий найдена весьма компактная функция, обеспечивающая высочайшую точность аппроксимации: R 2 =0,9963!!!

    ЗАВИСИМОСТЕЙ

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

    10.1. Линейная регрессия

    Самый простой и популярной является аппроксимация прямой линией – линейная регрессия.

    Пусть мы имеем фактическую информацию об уровнях прибыли Y в зависимости от размера X капиталовложений – Y(X). На рис. 10.1-1 показаны четыре такие точки М(Y,X). Пусть также у нас имеются основания предполагать, что зависимость эта линейная, т.е. имеет вид Y=А+ВX. Если бы нам удалось найти коэффициенты A и B и по ним построить прямую (например, такую, как на рисунке), в дальнейшем мы могли бы сделать осознанные предположения о динамике бизнеса и возможном коммерческом состоянии предприятия в будущем. Очевидно, что нас бы устроила прямая, находящаяся как можно ближе к известным точкам М(Y,X), т.е. имеющая минимальную сумму отклонений или сумму ошибок (на рисунке отклонения показаны пунктирными линиями). Известно, что существует только одна такая прямая.

    Для решения этой задачи используют метод наименьших квадратов ошибок. Разность (ошибка) между известным значением Y1 точки М1(Y1,X1) и значением Y(X1), вычисленным по уравнению прямой для того же значения X1, составит

    D1 = Y1 – A – B X1.

    Такая же разность

    для X=X2 составит D2 = Y2 – A – B X2;

    для X=X3 D3 = Y3 – A – B X3;

    и для X=X4 D4 = Y4 – A – B X4.

    Запишем выражение для суммы квадратов этих ошибок

    Ф(A,В)=(Y1–A–B X1) 2 +(Y2–A–B X2) 2 +(Y3–A–B X3) 2 +(Y4–A–B X4) 2

    или сокращенно Ф(B,A) = å(Yi – A – BXi) 2 .

    Здесь нам известны все X и Y и неизвестны коэффициенты A и B. Проведем искомую прямую так (т.е. выберем A и B такими), чтобы эта сумма квадратов ошибок Ф(A,B) была минимальной. Условиями минимальности являются известные соотношения

    ¶Ф(A,B)/¶A=0 и ¶Ф(A,B)/¶B=0.

    Выведем эти выражения (индексы при знаке суммы опускаем):

    ¶[å(Yi–A–B Xi) 2 ]/¶A = å(Yi–A–B Xi)(–1)

    ¶[å(Yi–A–B Xi) 2 ]/¶B = å(Yi–A–B Xi)(–Xi).

    Преобразуем полученные формулы и приравняем их нулю

    Решение задач аппроксимации средствами Excel

    доктор физ.– мат. наук, профессор Гавриленко В.В. ассистент Парохненко Л.М.

    (Национальный транспортный университет)

    Теоретическая справка. На практике при моделировании различных про-

    цессов, в частности, экономических, физических, технических, социальных,

    широко используются те или иные способы вычисления приближенных значе-

    ний функций по известным их значениям в некоторых фиксированных точках.

    Такого рода задачи приближения функций часто возникают:

    ∙ при построении приближенных формул для вычисления значений характер-

    ных величин исследуемого процесса по данным таблиц, полученным в ре-

    зультате физического или вычислительного эксперимента;

    ∙ при численном интегрировании, численном дифференцировании, числен-

    ном решении дифференциальных уравнений и т.д.;

    ∙ при необходимости вычисления значений функций в промежуточных точ-

    ках рассматриваемого интервала;

    ∙ при определении значений характерных величин процесса за пределами рас-

    сматриваемого интервала, в частности, при необходимости заглянуть в

    “ прошлое”), то есть при определении значений показателей процесса до на-

    чала наблюдения;

    ∙ в прогнозировании, то есть при получении предварительных оценок буду-

    щих значений интересуемых показателей процесса (возможность заглянуть

    в “ будущее”).

    Если для моделирования некоторого процесса, заданного таблицей, по-

    строить приближенно описывающую данный процесс функцию на основе ме-

    тода наименьших квадратов, то она называется аппроксимирующей функцией

    (регрессией), а сама задача построения аппроксимирующих функций называет-

    ся задачей аппроксимации.

    В данной статье рассмотрены возможности пакета Excel при реше-

    нии задач аппроксимации, а именно, приведены методы и приемы построения

    (создания) регрессий для таблично заданных функций, что является основой регрессионного анализа.

    В Excel для построения регрессий имеются такие возможности, как:

    1) добавление выбранных регрессий (линий тренда) в диаграмму, построенную на основе таблицы данных для исследуемой характеристики процесса (этим инструментом можно воспользоваться лишь при наличии построенной диа-

    2) использование встроенных статистических функций рабочего листа Excel ,

    позволяющих получать регрессии (линии тренда) на основе таблицы исход-

    ных данных (использование данного инструмента предварительно не связы-

    вается с наличием соответствующей диаграммы).

    Добавление линий тренда в диаграмму

    Для таблицы данных, описывающих некоторый процесс и представленных диаграммой, в Excel имеется эффективный инструмент регрессионного анали-

    за, позволяющий:

    ∙ строить на основе метода наименьших квадратов и добавлять в диаграмму пять типов регрессий (линий тренда), которые с той или иной степенью точно-

    сти моделируют исследуемый процесс;

    ∙ добавлять к диаграмме уравнение построенной регрессии;

    ∙ определять степень соответствия выбранной регрессии отображаемым на диаграмме данным.

    Построенные модели процесса – линии тренда (trendlines) показывают

    тенденцию изменения данных, дают возможность определять значения иссле-

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

    На основе данных диаграммы Excel позволяет получать такие типы регрес-

    сий или линий тренда, как линейный, полиномиальный, логарифмический, сте-

    пенной, экспоненциальный, которые задаются уравнением y = y(x) , где x – неза-

    висимая переменная, которая часто принимает значения последовательности натурального ряда чисел (1; 2; 3; …) и производит, например, отсчет времени протекания исследуемого процесса.

    1. Линейная регрессия хороша при моделировании характеристик, значения которых увеличиваются или убывают с постоянной скоростью. Это наиболее простая в построении, но наименее точная модель исследуемого процесса.

    y = m x + b ,

    где m – угол наклона линейной регрессии к оси абсцисс; b – координата точки пересечения линейной регрессии с осью ординат.

    2. Полиномиальная линия тренда полезна для описания характеристик,

    имеющих несколько ярко выраженных экстремумов (максимумов и миниму-

    мов). Выбор степени полиномиальной линии тренда (полинома) определяется количеством экстремумов исследуемой характеристики. Так, полином второй степени может хорошо описать характеристику, имеющую только один макси-

    мум или минимум; полином третьей степени – не более двух экстремумов; по-

    лином четвертой степени – не более трех экстремумов и т.д.

    Строится в соответствии с уравнением

    y = c0 + c1 x + c2 x2 + c3 x3 + c4 x4 + c5 x5 + c6 x6 ,

    где коэффициенты c 0 , c 1 , c 2 ,...c 6 – константы.

    3. Логарифмическая линия тренда с успехом применяется при моделирова-

    нии характеристик, значения которых вначале быстро растут или убывают по величине, а затем постепенно стабилизируются.

    Строится в соответствии с уравнением

    y = c× ln(x)+ b,

    4. Степенная линия тренда дает хорошие результаты, если значения иссле-

    дуемой зависимости характеризуются постоянным изменением скорости роста.

    Примером такой зависимости может служить график равноускоренного движе-

    ния автомобиля. При наличии в данных нулевых или отрицательных значений использовать степенную линию тренда нельзя.

    Строится в соответствии с уравнением

    y = c× xb ,

    где коэффициенты b, с – константы.

    5. Экспоненциальная линия тренда следует использовать в том случае, если скорость изменения данных непрерывно возрастает. Для данных, содержащих нулевые или отрицательные значения, этот вид приближения неприменим.

    Строится в соответствии с уравнением

    y = c× eb × x ,

    где коэффициенты b, с – константы.

    При подборе линии тренда Excel автоматически рассчитывает значение величиныR 2 , которая характеризует достоверность аппроксимации: чем ближе значениеR 2 к единице, тем надежнее линия тренда аппроксимирует исследуе-

    мый процесс. При необходимости значение R 2 всегда можно отобразить на

    диаграмме.

    Определяется по формуле

    R 2 = 1-

    Σ1 = ∑(yj − Yj )2

    S2 =∑Y j 2 -

    × (∑Yj )2

    Для добавления линии тренда к ряду данных следует:

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

    нуть в пределах области диаграммы. В главном меню появится пункт Диа-

    2. После щелчка на этом пункте на экране появится меню, в котором следует выбрать команду Добавить линию тренда.

    затель мыши к графику, построенного на ряде данных, и щелкнуть правой кла-

    вишей мыши, и в появившемся контекстном меню выбрать команду Добавить

    линию тренда. На экране появится диалоговое окно Линия тренда с раскры-

    той вкладкой Тип (рис.1).

    Рис.1. Вкладка Тип диалогового окна Формат линии тренда

    3. Выбрать на вкладке Тип необходимый тип линии тренда (по умолчанию выбирается тип Линейный). Для типа Полиномиальная в поле Степень сле-

    дует задать степень выбранного полинома.

    4. В поле Построен на ряде перечислены все ряды данных рассматриваемой диаграммы. Для добавления линии тренда к конкретному ряду данных следует в поле Построен на ряде выбрать его имя.

    5. При необходимости, перейдя на вкладку Параметры (рис.2), можно для ли-

    нии тренда задать следующие параметры:

    ∙ Изменить название линии тренда в поле Название аппроксимирующей

    (сглаженной) кривой;

    ∙ Задать количество периодов (вперед или назад) для прогноза в поле Про-

    ∙ Вывести в область диаграммы уравнение линии тренда, для чего следует ус-

    тановить флажок для опции «показать уравнение на диаграмме».

    ∙ Вывести в область диаграммы значение достоверности аппроксимации R 2 ,

    для чего следует установить флажок для опции «поместить на диаграмму ве-

    личину достоверности аппроксимации (R^2) ».

    ∙ Задать точку пересечения линии тренда с осью Y, для чего следует устано-

    вить флажок для опции «пересечение кривой с осью Y в точке: ». 6. Нажать клавишуOK .

    Рис.2. Вкладка Параметры диалогового окна Линия тренда

    Для редактирования уже построенной линии тренда следует:

    1. Щелкнуть левой клавишей мыши по той линии тренда, которую требуется

    изменить.

    2. Нажать в главном меню клавишу Формат, а появившемся контекстном ме-

    ню выбрать команду Выделенная линия тренда.

    Пункты 1–2 легко реализуются также следующим приемом: направить ука-

    затель мыши к графику линии тренда, щелкнуть правой клавишей мыши, и в появившемся контекстном меню выбрать команду Формат линии тренда.

    Еще легче реализуются пункты 1–2: двойным щелчком левой клавишей мыши по графику линии тренда.

    3. На экране появится диалоговое окно Формат линии тренда (рис.3), содер-

    жащее три вкладки: Вид, Тип, Параметры, причем содержимое вкладок Тип,

    Параметры полностью совпадает с аналогичными вкладками диалогового ок-

    на Линия тренда (рис.1–2).

    4. При необходимости, перейдя на вкладку Вид (рис.3), можно для линии тренда задать тип линии, ее цвет и толщину.

    5. Нажать клавишу OK .

    Для удаления уже построенной линии тренда следует выбрать удаляемую линию тренда и нажать клавишуDelete .

    Достоинствами этого инструмента регрессионного анализа являются:

    ∙ относительная легкость построения на диаграммах линии тренда без созда-

    ния для нее таблицы данных;

    ∙ достаточно широкий перечень типов предложенных линий трендов, причем в этот перечень входят наиболее часто используемые регрессии;

    ∙ возможность прогнозирования поведения исследуемого процесса на произ-

    вольное (в пределах здравого смысла) количество шагов вперед, а также назад;

    ∙ возможность получения уравнения линии тренда в аналитическом виде;

    ∙ возможность, при необходимости, получения оценки достоверности прове-

    денной аппроксимации.

    К недостаткам можно отнести следующие моменты:

    ∙ построение линии тренда осуществляется лишь при наличии построенной на ряде данных диаграммы;

    ∙ несколько загроможден процесс формирования рядов данных для исследуе-

    мой характеристики на основании полученных для нее уравнений линий трен-

    да, так как коэффициенты этих уравнений при каждом изменении значений ря-

    да данных пересчитываются, но лишь в пределах области диаграммы;

    ∙ в отчетах сводных диаграмм при изменении представления диаграммы или связанного отчета сводной таблицы имеющиеся линии тренда не сохраняются,

    то есть до проведения линий тренда или другого форматирования отчета свод-

    ных диаграмм следует убедиться, что макет отчета удовлетворяет необходи-

    мым требованиям.

    Рис.3. Вкладка Вид диалогового окна Формат линии тренда

    Линиями тренда можно дополнить ряды данных, представленные на гра-

    фиках, гистограммах, плоских ненормированных диаграммах с областями, ли-

    нейчатых, точечных, пузырьковых и биржевых диаграммах.

    Нельзя дополнить линиями тренда ряды данных на объемных, нормиро-

    ванных, лепестковых, круговых и кольцевых диаграммах. При замене типа диа-

    граммы на один из вышеперечисленных, а также при изменении представления отчета сводной диаграммы или связанного отчета сводной таблицы соответст-

    вующие данным линии тренда будут утеряны.

    Использование встроенных функций Excel

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

    В Excel имеется несколько вариантов построения линейной регрессии (ли-

    нейного тренда), в частности:

    ∙ с помощью функции ТЕНДЕНЦИЯ;

    ∙ с помощью функции ЛИНЕЙН;

    ∙ с помощью функций НАКЛОН и ОТРЕЗОК .

    В Excel имеется также несколько вариантов построения экспоненциальной линии тренда, в частности:

    ∙ с помощью функции РОСТ;

    ∙ с помощью функции ЛГРФПРИБЛ.

    Следует отметить, что приемы построения регрессий с помощью функций

    ТЕНДЕНЦИЯ и РОСТ практически совпадают. То же самое можно сказать и о паре функций ЛИНЕЙН и ЛГРФПРИБЛ. Для всех этих четырех функций при создании таблицы значений используются такие возможности Excel , как формулы массивов, что несколько загромождает процесс построения регрес-

    сий. Заметим также, что построение (создание) линейной регрессии, на наш взгляд, легче всего осуществить с помощью функций НАКЛОН и ОТРЕЗОК,

    где первая из них определяет угловой коэффициент линейной регрессии, а вто-

    рая – отрезок, отсекаемый регрессией на оси ординат.

    Достоинствами данного инструмента регрессионного анализа являются:

    ∙ достаточно простой однотипный процесс формирования рядов данных ис-

    следуемой характеристики для всех встроенных статистических функций, за-

    дающих линии тренда;

    ∙ стандартная методика построения линий тренда на основе сформированных рядов данных;

    ∙ возможность прогнозирования поведения исследуемого процесса на необ-

    ходимое количество шагов вперед или назад.

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

    ного) типов линий тренда. Это обстоятельство часто не позволяет подобрать с помощью выше перечисленных встроенных функций достаточно точную мо-

    дель исследуемого процесса, а также получать близкие к реальности прогнозы.

    Кроме того, при использовании функций ТЕНДЕНЦИЯ и РОСТ не известны уравнения линий тренда.

    тьи – на конкретных примерах показать возможности пакета Excel при реше-

    нии задач аппроксимации; продемонстрировать, каким эффективными инстру-

    ментами для построения регрессий и прогнозирования обладает Excel ; проил-

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

    Предложенная в статье методика по овладению навыков решения средства-

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

    задач оптимизации, транспортных задач) может быть полезна и интересна пользователям. Это связано с тем, что пакет Excel установлен практически на каждом современном компьютере, в то время как такие известные специализи-

    рованные математические пакеты, как Mathematica ,Maple ,Matlab ,Mathcad ,

    обладающие более мощными возможностями для построения регрессий и про-

    гнозирования, используются значительно меньшей пользовательской аудито-

    Ниже приводятся решения конкретных задач с помощью перечисленных инструментов пакета Excel .

    Задача 1 . Для таблицы данных о прибыли автотранспортного предприятия за 1995–2002 г.г. необходимо выполнить следующие действия.