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

Опубликовано 05 Янв 2014
Рубрика: Справочник Excel | 19 комментариев

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Таблица Excel со статистическими данными.

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

Зависимость общего выпуска металлоконструкций от количества переработанных уголков

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

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

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

Окно MS Excel "Линия тренда" вкладка "Тип"

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

Окно MS Excel "Линия тренда" вкладка "Параметры"

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

Аппроксимация табличной зависимости прямой линией

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

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

Линейная, степенная, логарифмическая, экспоненциальная и полиномиальная аппроксимации

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

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

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

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

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

Окно MS Excel "Формат ряда данных" вкладка "Y-погрешности"

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

Окно MS Excel "Формат планок погрешностей" вкладка "Вид"

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

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

Аппроксимация табличной зависимости логарифмической кривой

Итоги.

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

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

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

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

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

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

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

P.S. (04.06.2017)

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

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

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

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

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

excel-ili-predlagaemaya-metodika

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

Другие статьи автора блога

На главную

Статьи с близкой тематикой

Отзывы

19 комментариев на «Аппроксимация в Excel»

  1. Рустем Рашитович 16 мая 2014 15:14

    Подробно не проверял. Но смотрится качественнее других.

  2. Геннадий 23 Янв 2015 02:20

    Все понятно, кроме одного. Как можно, израсходовав 60 ТОНН уголков, выпустить 498 ТОНН металлоконструкций ? Может быть лучше сказать " единиц " металлоконструкций ? Иначе какие-то фокусы получаются! 22.01.15

  3. Александр Воробьев 23 Янв 2015 22:23

    Геннадий, Вы, наверное, не прочли статью полностью...

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

    Если честно, то я даже не знаю, что Вам ответить, чтобы не обидеть. Получается — Вы не поняли статью.

  4. Виктор 29 Мар 2015 16:05

    А я не понимаю, почему у Геннадия возникли вопросы.

    Это, конечно, приятно, что статья на конкретном примере. Но ведь совершенно не важно, зависимость чего от чего рассматривается. Хоть тугрики от фурзиков :) .

    Я вообще сюда зашёл по запросу поиска аппроксимации в Excel :) .

    Александр, спасибо, познавательно!

  5. Татьяна 12 Апр 2015 12:01

    Добрый день, Александр.

    Огромная благодарность Вам за все статьи.

    Что касается именно данной темы, не могу найти в Excel 2010, где устанавливаются планки погрешности.

    Может подскажите?

  6. Александр Воробьев 12 Апр 2015 19:24

    Татьяна, добрый день.

    Щелкаете на линии графика и выбираете вверху: работа с диаграммами — макет — анализ — планки погрешностей. Так в Excel 2007. В Excel 2010 должно быть также или очень похоже.

  7. Александр 20 Сен 2015 20:26

    Спасибо. Всё чётко и понятно.

  8. Ольга 29 Янв 2016 12:51

    Спасибо огромное!!!

    Ясно и понятно.

  9. Владимир 30 Апр 2016 15:15

    Эффективна аппроксимация в Excel, когда вместо значений аргумента (или аргумента и функции) используется какая-нибудь простая функция от него. (Пример можно найти на arhiuch.ru)

  10. SPSS 01 Июл 2016 23:58

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

    (spss-statistics.ru)

  11. Михаил 04 Авг 2016 23:53

    Как расчитывается величина достоверности аппроксимации? формула для ручного расчета есть?

  12. Александр Воробьев 05 Авг 2016 14:27

    Михаил, откройте справку Excel и посмотрите: «Формулы для вычисления линий тренда» — «Значение R-квадрат»...

  13. Сергей 23 Дек 2016 01:34

    Спасибо Александр!Заинтересовался.Заданный аналитически профиль(лодка,одна из ватерлиний,парабола) перевёл в таблицу,по десятку точек построил график.Степенную линию тренда удалось

    построить,когда убрал точку (0;0).Точно восстановлена

    аналитическая формула,R^2=1.Можно и нужно работать.

    С наступающим Новым годом!Здоровья и счастья.Отдельно

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

  14. Ольга 04 Дек 2017 13:40

    Спасибо, за статью! Доступным языком и с картинками.

  15. Иван 19 Дек 2017 16:59

    Доброго времени суток. Статья понятна. Огромное спасибо. Хотелось бы увидеть продолжение, а именно

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

    С Уважением,

    Иван.

  16. Александр Воробьев 19 Дек 2017 18:27

    Иван, здравствуйте.

    Об аппроксимации функции НЕСКОЛЬКИХ НЕЗАВИСИМЫХ переменных можете прочитать в статье «Прогнозирование в Excel».

  17. Андрей 21 Фев 2018 01:19

    Александр, здравствуйте.

    У меня есть данные о количестве мутаций в парах удаленных родственников в зависимости от количества поколений разделяющих этих родственников (35 пар). На графике выбирая «линию тренда» получаю одно значение R-квадрат.А если иду в «пакет анализа» и выбираю «регрессия» получаю другое значение R-квадрат. Почему? Есть еще несколько связанных вопросов, не могли бы Вы уделить мне время, чтобы разобраться? За вознаграждение, естественно. Очень надеюсь на Вашу помощь. Андрей

  18. Александр Воробьев 21 Фев 2018 09:28

    Андрей, здравствуйте.

    Аппроксимирующие функции разные получаете? (формулы)

    Формулу по которой считается R-квадрат знаете?

    Проверьте на простых примерах: 3-4 значения каждым из методов расчета и вручную по формуле.

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

  19. Андрей 14 Июн 2021 23:26

    Александр, огромное спасибо.

    Перебрал несколько сайтов с пояснениями. Ваше самое доходчивое. Особенно про «правую кнопку».

    Единственно смутило, у меня Excel 2007, и там картинка с выбором типа линии не такая, как в Вашем примере. Но это не принципиально.

Ваш отзыв



  • Подписчики: 9,1 тыс.

    Подписка закрыта 01.12.2022
  • Посетители: 2,1 млн