Метод аппроксимации в Microsoft Excel

Метод аппроксимации в Microsoft Excel

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

Выполнение аппроксимации

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

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

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

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

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

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

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

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

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

Существует ещё один вариант её добавления. В дополнительной группе вкладок на ленте «Работа с диаграммами» перемещаемся во вкладку «Макет». Далее в блоке инструментов «Анализ» щелкаем по кнопке «Линия тренда». Открывается список. Так как нам нужно применить линейную аппроксимацию, то из представленных позиций выбираем «Линейное приближение».

Если же вы выбрали все-таки первый вариант действий с добавлением через контекстное меню, то откроется окно формата.

В блоке параметров «Построение линии тренда (аппроксимация и сглаживание)» устанавливаем переключатель в позицию «Линейная».
При желании можно установить галочку около позиции «Показывать уравнение на диаграмме». После этого на диаграмме будет отображаться уравнение сглаживающей функции.

Также в нашем случае для сравнения различных вариантов аппроксимации важно установить галочку около пункта «Поместить на диаграмму величину достоверной аппроксимации (R^2)». Данный показатель может варьироваться от до 1. Чем он выше, тем аппроксимация качественнее (достовернее). Считается, что при величине данного показателя 0,85 и выше сглаживание можно считать достоверным, а если показатель ниже, то – нет.

После того, как провели все вышеуказанные настройки. Жмем на кнопку «Закрыть», размещенную в нижней части окна.

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

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

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

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

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

      Для того, чтобы изменить тип линии тренда, выделяем её кликом правой кнопки мыши и в раскрывшемся меню выбираем пункт «Формат линии тренда…».

    После этого запускается уже знакомое нам окно формата. В блоке выбора типа аппроксимации устанавливаем переключатель в положение «Экспоненциальная». Остальные настройки оставим такими же, как и в первом случае. Щелкаем по кнопке «Закрыть».

  • После этого линия тренда будет построена на графике. Как видим, при использовании данного метода она имеет несколько изогнутую форму. При этом уровень достоверности равен 0,9592, что выше, чем при использовании линейной аппроксимации. Экспоненциальный метод лучше всего использовать в том случае, когда сначала значения быстро изменяются, а потом принимают сбалансированную форму.
  • Общий вид функции сглаживания при этом такой:

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

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

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

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

      Тем же способом, что и в предыдущий раз через контекстное меню запускаем окно формата линии тренда. Устанавливаем переключатель в позицию «Логарифмическая» и жмем на кнопку «Закрыть».

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

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

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

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

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

      Переходим в окно формата линии тренда, как уже делали не раз. В блоке «Построение линии тренда» устанавливаем переключатель в позицию «Полиномиальная». Справа от данного пункта расположено поле «Степень». При выборе значения «Полиномиальная» оно становится активным. Здесь можно указать любое степенное значение от 2 (установлено по умолчанию) до 6. Данный показатель определяет число максимумов и минимумов функции. При установке полинома второй степени описывается только один максимум, а при установке полинома шестой степени может быть описано до пяти максимумов. Для начала оставим настройки по умолчанию, то есть, укажем вторую степень. Остальные настройки оставляем такими же, какими мы выставляли их в предыдущих способах. Жмем на кнопку «Закрыть».

    Линия тренда с использованием данного метода построена. Как видим, она ещё более изогнута, чем при использовании экспоненциальной аппроксимации. Уровень достоверности выше, чем при любом из использованных ранее способов, и составляет 0,9724.

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

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

    y=0,0015*x^2-1,7202*x+507,01
    Теперь давайте изменим степень полиномов, чтобы увидеть, будет ли отличаться результат. Возвращаемся в окно формата. Тип аппроксимации оставляем полиномиальным, но напротив него в окне степени устанавливаем максимально возможное значение – 6.

  • Как видим, после этого наша линия тренда приняла форму ярко выраженной кривой, у которой число максимумов равно шести. Уровень достоверности повысился ещё больше, составив 0,9844.
  • Формула, которая описывает данный тип сглаживания, приняла следующий вид:

    Читайте также  Расчет дисперсии в Microsoft Excel

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

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

      Перемещаемся в окно «Формат линии тренда». Устанавливаем переключатель вида сглаживания в позицию «Степенная». Показ уравнения и уровня достоверности, как всегда, оставляем включенными. Жмем на кнопку «Закрыть».

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

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

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

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

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

    Функция ЛГРФПРИБЛ для аппроксимации данных таблиц в Excel

    Функция ЛГРФПРИБЛ в Excel предназначена для определения значений, на основе которых может быть построена экспоненциальная кривая, аппроксимирующая имеющиеся числовые данные, и возвращает массив значений. Для корректной работы рассматриваемой функции ее следует вводить как формулу массива.

    Методы аппроксимации табличных данных в Excel

    Функция ЛГРФПРИБЛ возвращает данные, необходимые для построения кривой, описываемой следующим уравнением:

    Если имеется две и более переменных, это уравнение переписывается следующим образом:

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

    То есть, имеем массив оснований, возводимых в степени (известные значения переменных x), и коэффициент b.

    Пример 1. В таблице приведены данные, характеризующие динамику курса доллара на протяжении 10 лет (с 2006 по 2016 год). Необходимо спрогнозировать курс доллара на 2019 год на основании имеющихся данных.

    Вид таблицы данных:

    Для расчета тренда (коэффициент, используемый для предсказания последующих значений курса) используем функцию:

    • B2:B12 – известные данные зависимой переменной (значения курса);
    • A2:A12 – известные данные независимой переменной (года).

    Для предсказания курса на 2019 год используем формулу:

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

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

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

    Вид таблицы данных:

    Определим коэффициенты достоверности аппроксимации для линейной и экспоненциальной функций с помощью следующих функций (вводить как формулы массива CTRL+SHIFT+Enter):

    Поскольку обе функции возвращают результат в виде массива данных, в котором в третьей строке первого столбца содержится искомое значение R^2, используем функцию ИНДЕКС для возврата желаемого результата.

    Чем ближе значение R^2 к 1, тем выше точность аппроксимации. Как видно, наибольшую точность обеспечивает экспоненциальная функция. Однако разница не является существенной, поэтому использование функции ЛИНЕЙН является допустимым в данном случае.

    Правила метода аппроксимации по функции ЛГРФПРИБЛ в Excel

    Функция имеет следующую синтаксическую запись:

    =ЛГРФПРИБЛ( известные_значения_y; [известные_значения_x];[конст];[статистика])

    • известные_значения_y – обязательный, принимает ссылку на диапазон ячеек или массив данных — числовые значения, которые характеризуют состояние зависимой переменной y из указанного выше уравнения;
    • [известные_значения_x] – необязательный, принимает ссылку на диапазон ячеек или массив чисел, которые являются уже известными значениями независимой переменной x. Если явно не указан, по умолчанию принимается массив значений <1;2;…N>, где N – количество элементов в массиве, характеризующем известные_значения_y ;
    • [конст] – необязательный, принимает данные логического типа, интерпретируемые следующим образом: ИСТИНА или явно не указан – функция вычисляет значение коэффициента b из приведенного выше уравнения, ЛОЖЬ – значение данного коэффициента принимается равным 1;
    • [статистика] – необязательный, принимает логические значения ИСТИНА (функция возвращает дополнительные данные на основе проведенного регрессионного анализа) или ЛОЖЬ (значение по умолчанию) – функция возвращает только значения коэффициентов m и b.
    1. Точность вычислений рассматриваемой функцией зависит от степени близости графика, построенного на основе имеющихся значений, к экспоненциальной кривой.
    2. В качестве первого или второго аргументов могут быть введены константы массивов, при этом необходимо соблюдать требования к размерностям.
    3. Если аргумент известные_значения_y указан в виде ссылки на диапазон ячеек, формирующих строку или столбец, каждая строка или столбец соответственно будут интерпретированы как отдельная переменная.
    4. Если данная функция используется для расчетов с указанием только одной переменной x, первый и второй аргументы могут быть указаны в виде ссылок на диапазоны любой формы. Если по условию имеются две и более переменных x, первый и второй аргументы должны быть указаны в виде векторов данных. Размеры массивов должны совпадать в любом случае.
    5. Если требуется определить будущие значения переменных (предсказать), можно использовать функцию РОСТ.

    Аппроксимация в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    О том, как построить подобную диаграмму, подробно рассказано в статье «Как строить графики в Excel?».

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

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

    5. Далее на вкладке «Параметры» ставим 2 галочки и нажимаем «ОК».

    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 2 =0,9963.

    Метод аппроксимации Add (Excel) Trendlines.Add method (Excel)

    Создает новую линию тренда. Creates a new trendline.

    Синтаксис Syntax

    Expression. Add (Тип, порядок, период, вперед, назад, пересечение, DisplayEquation, DisplayRSquared, имя) expression.Add (Type, Order, Period, Forward, Backward, Intercept, DisplayEquation, DisplayRSquared, Name)

    Expression (выражение ) Переменная, представляющая объект линии тренда . expression A variable that represents a Trendlines object.

    Параметры Parameters

    Имя Name Обязательный или необязательный Required/Optional Тип данных Data type Описание Description
    Type Type Необязательный Optional XlTrendlineType XlTrendlineType Тип линии тренда. The trendline type.
    Order Order Необязательный Optional Variant Variant Вариант , если типкслполиномиал. Variant if Type is xlPolynomial. Порядок линии тренда. The trendline order. Должно быть целым числом от 2 до 6 включительно. Must be an integer from 2 to 6, inclusive.
    Period Period Необязательный Optional Variant Variant Вариант , если типкслмовингавг. Variant if Type is xlMovingAvg. Период линии тренда линии тренда. The trendline period. Должно быть целым числом больше 1 и меньшим, чем число точек данных в ряду, к которым добавляется линия тренда. Must be an integer greater than 1 and less than the number of data points in the series you are adding a trendline to.
    Forward Forward Необязательный Optional Variant Variant Количество периодов (или единиц измерения на точечной диаграмме), на которые линия тренда расширяется вперед. The number of periods (or units on a scatter chart) that the trendline extends forward.
    Backward Backward Необязательный Optional Variant Variant Количество периодов (или единиц измерения на точечной диаграмме), на которые линия тренда расширяется обратно. The number of periods (or units on a scatter chart) that the trendline extends backward.
    Intercept Intercept Необязательный Optional Variant Variant Пересечение с линией тренда. The trendline intercept. Если этот аргумент опущен, то Конст автоматически задается регрессией. If this argument is omitted, the intercept is automatically set by the regression.
    DisplayEquation DisplayEquation Необязательный Optional Variant Variant Значение true , чтобы отобразить формулу линии тренда на диаграмме (в той же метке данных, что и значение R-квадрат). True to display the equation of the trendline on the chart (in the same data label as the R-squared value). Значение по умолчанию — False. The default value is False.
    DisplayRSquared DisplayRSquared Необязательный Optional Variant Variant Значение true для отображения значения аппроксимации линии тренда на диаграмме (в той же метке данных, что и формула). True to display the R-squared value of the trendline on the chart (in the same data label as the equation). Значение по умолчанию — False. The default value is False.
    Name Name Необязательно заполнять. Optional Variant Variant Название линии тренда в виде текста. The name of the trendline as text. Если этот аргумент опущен, Microsoft Excel создает имя. If this argument is omitted, Microsoft Excel generates a name.

    Возвращаемое значение Return value

    Объект линии тренда , представляющий новую линию тренда. A Trendline object that represents the new trendline.

    Пример Example

    В этом примере создается новая линейная линия тренда на диаграммы Chart1. This example creates a new linear trendline on Chart1.

    Поддержка и обратная связь Support and feedback

    Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

    Метод аппроксимации в Microsoft Excel

    ОГЛАВЛЕНИЕ

    1. АНАЛИЗ ЭКОНОМИЧЕСКИХ ЗАВИСИМОСТЕЙ. 2

    1.1. Аппроксимация экспериментальных данных. 2

    1.2. Нахождение экстремального значения функции. 7

    2. Модели линейной оптимизации в MS Excel 11

    2.1 Исследование операций. 11

    2.2. Задачи линейного программирования. 13

    2.3 Решение задач линейного программирования в MS Excel 14

    2.4 Задача о планировании производства. 19

    2.5 Двойственная задача. 23

    3. ОРГАНИЗАЦИЯ СНАБЖЕНИЯ И УПРАВЛЕНИЕ ТРУДОВЫМИ РЕСУРСАМИ 25

    3.1. Транспортная задача. 26

    3.2 Несбалансированные транспортные задачи. 29

    3.3. Задача о назначениях. 37

    4 Задачи нелинейной оптимизации. 43

    АНАЛИЗ ЭКОНОМИЧЕСКИХ ЗАВИСИМОСТЕЙ

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

    Аппроксимация экспериментальных данных

    Аппроксимацией называется подбор аналитической формулы y=f(x) для установленной из опыта функциональной зависимости y=φ(x)

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

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

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

    x x1 x2 . xn
    y y1 y2 . yn

    На основе этих данных требуется подобрать такую фун­кцию у= f(x), которая с точки зрения некоторого критерия оптимальности наилучшим образом описывала бы экспериментальную зависимость.

    Обычно задача аппроксимации распадается на две части. Сначала устанавливают вид зависимости у=f(x) и, соответственно, вид эмпирической формулы, то есть решают, является ли она линейной, квадратичной, логарифмической или какой-либо другой. После этого определяются численные значения неизвестных параметров выбранной эмпирической формулы, для которых приближение к заданной функции оказывается наилучшим.

    Для сглаживания экспериментальных зависимостей yi=φ(xi), заданных таблично, в MS Excel используются различные функции у=f(x).

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

    (1.1)

    Степень точности аппроксимации экспериментальных данных в MS Excel оценивается коэффициентом детерминации (R 2 ). Чем ближе этот коэффициент к значению 1, тем точнее приближение. Рассмотрим процедуру аппроксимации на примере.

    Пример 1.1.

    Построить и исследовать динамику роста производства продукции, используя данные:

    год производство
    17,1
    18,9
    19,7
    19,8
    19,9

    1. На основе имеющейся таблицы строим Точечную диаграмму.

    Наводим курсор на одну из точек полученного графика и из контекстного меню выбираем команду: Добавить линию тренда (рис. 1.1)

    Рис. 1.1

    2. На вкладке Тип указываем тип Логарифмическая (рис. 1.2)

    Рис 1.2

    3. На вкладке Параметры выставляем флажки для уравнения и достоверность аппроксимации (рис. 1.3).

    Рис. 1.3

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

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

    Щелчок правой кнопки мыши на линии тренда дает возможность редактировать его, подбирая другие функции для аппроксимации. Наилучшей в данном примере является полиномиальная функция, которая дает показатель достоверности R 2 =0,9917, тогда как для логарифмической функции этот показатель равен 0,867 (рис. 1.5).

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

    Упражнения

    Упр. 1.1. Построить функцию, отражающую зависимость дефицита бюджета от времени в России и США.

    Страна
    Россия 2,9 2,3 3,1 2,2 2,0 2,7 6,5 8,0 9,1
    США 2,8 2,6 4,1 6,3 5,0 5,4 5,3 3,4 3,2

    Упр. 1.2. Вложенные в производства средства дают прибыль:

    Средства
    Прибыль

    Определить зависимость прибыли от вложенных средств и вычислить прибыль для вложений, равных 10000 руб.