Урок 4. Виды аппроксимации в Excel
Текст урока с работающими фрагментами расчетов в файле uroki-approksimacii.xls
Как и предыдущие, этот урок с аналогичным текстом лучше смотреть не листе Excel (см. Уроки аппроксимации.xls, Лист1)
Аппроксимация в Excel проще всего реализуется с помощью программы построения трендов. Для выяснения особенностей аппроксимации возьмем какой-либо конкретный пример. Например, энтальпию насыщенного пара по книге С.Л.Ривкина и А.А.Александрова «Теплофизические свойства воды и водяного пара», М., «Энергия», 1980г. В колонке P поместим значения давления в кгс/см2, в колонке i» — энтальпию пара на линии насыщения в ккал/кг и построим график с помощью опции или кнопки «Мастер диаграмм».
Щелкнем правой кнопкой по линии на рисунке, затем левой кнопкой по опции «Добавить линию тренда» и смотрим — какие услуги предлагаются нам этой опцией в части реализации аппроксимации в Excel.
Нам предлагается на выбор пять типов аппроксимации: линейная, степенная, логарифмическая, экспоненциальная и полиноминальная. Чем они хороши и чем могут нам помочь? — Нажимаем кнопку F1, затем щелкаем по опции «Мастер ответов» и в появившееся окошко вводим нужное нам слово «аппроксимация», после чего щелкаем по кнопке «Найти». Выбираем в появившемся списке раздел «Формулы для построения линий тренда».
Получаем следующую информацию в несколько измененной нами
Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:
где b — угол наклона и a — координата пересечения оси абсцисс (свободный член).
Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:
где c и b — константы.
Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:
где a и b — константы.
Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:
где b и k — константы.
Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:
где a, b1, b2, b3. b6 — константы.
Снова щелкаем по линии рисунка, затем по опции «Добавить линию тренда», далее по опции «Параметры» и ставим флажки в окошках слева от записей: «показывать уравнение на диаграмме» и «поместить на диаг- рамму величину достоверности аппроксимации R^2, после чего щелкаем по кнопке OK. Пробуем все варианты аппроксимации по порядку.
Линейная аппроксимация дает нам R^2=0.9291 — это низкая достоверность и плохой результат.
Для перехода к степенной аппроксимации щелкаем правой кнопкой по линии тренда, затем левой кнопкой — по опции «Формат линии тренда», далее по опциям «Тип» и «Степенная». На этот раз получили R^2=0.999.
Запишем уравнение линии тренда в виде, пригодном для расчетов на листе Excel:
В результате имеем:
Максимальная погрешность аппроксимации получилась на уровне 0.23 ккал/кг. Для аппроксимации экспериментальных данных такой результат был бы чудесным, но для аппроксимации справочной таблицы это не слишком хороший результат. Поэтому попробуем проверить другие варианты аппроксимации в Excel посредством программы построения трендов.
Логарифмическая аппроксимация дает нам R^2=0.9907 — несколько хуже, чем по степенному варианту. Экспоненнта в том варианте, который предлагает программа построения трендов, вообще не подошла — R^2=0.927.
Полиноминальная аппроксимация со степенью 2 (это y=a+b1*x+b2*x^2) обеспечила R^2=0.9896. При степени 3 получили R^2=0.999, но с явным искажением аппроксимируемой кривой, в особенности при P>0.07 кгс/см2. Наконец, пятая степень нам дает R^2=1 — это, как утверждается, максимально тесная связь между исходными данными и их аппроксимацией.
Перепишем уравнение полинома в пригодном для расчетов на листе Excel виде:
и сравним результат аппроксимации с исходной таблицей:
Оказалось, что R^2=1 в данном случае лишь блестящая ложь. Реально, самый лучший результат полиноминальной аппроксимации дал самый простой полином вида y=a+b1*x+b2*x^2. Но его результат хуже, чем в варианте степенной аппроксимации y=634.16*x^0.012, где максимальная погрешность аппроксимации находилась на уровне 0.23 ккал/кг. Это все, что мы можем выжать из программы построения трендов. Посмотрим, что мы можем выжать из функции Линейн. Для нее попробуем вариант степенной аппроксимации.
Примечание. Обнаруженный дефект связан с работой программы построения трендов, но не с методом МНК.
Обобщенный МНК
Метод наименьших квадратов допускает широкое обобщение. Вместо минимизации суммы квадратов остатков можно минимизировать некоторую положительно определенную квадратичную форму от вектора остатков , где — некоторая симметрическая положительно определенная весовая матрица. Обычный МНК является частным случаем данного подхода, когда весовая матрица пропорциональна единичной матрице. Как известно из теории симметрических матриц (или операторов) для таких матриц существует разложение . Следовательно, указанный функционал можно представить следующим образом , то есть этот функционал можно представить как сумму квадратов некоторых преобразованных «остатков». Таким образом, можно выделить класс методов наименьших квадратов — LS-методы (Least Squares).
Доказано (теорема Айткена), что для обобщенной линейной регрессионной модели (в которой на ковариационную матрицу случайных ошибок не налагается никаких ограничений) наиболее эффективными (в классе линейных несмещенных оценок) являются оценки т. н. обобщенного МНК (ОМНК, GLS — Generalized Least Squares)
— LS-метода с весовой матрицей, равной обратной ковариационной матрице случайных ошибок: .
Можно показать, что формула ОМНК-оценок параметров линейной модели имеет вид
Ковариационная матрица этих оценок соответственно будет равна
Фактически сущность ОМНК заключается в определенном (линейном) преобразовании (P) исходных данных и применении обычного МНК к преобразованным данным. Цель этого преобразования — для преобразованных данных случайные ошибки уже удовлетворяют классическим предположениям.
Взвешенный МНК
В случае диагональной весовой матрицы (а значит и ковариационной матрицы случайных ошибок) имеем так называемый взвешенный МНК (WLS — Weighted Least Squares). В данном случае минимизируется взвешенная сумма квадратов остатков модели, то есть каждое наблюдение получает «вес», обратно пропорциональный дисперсии случайной ошибки в данном наблюдении: . Фактически данные преобразуются взвешиванием наблюдений (делением на величину, пропорциональную предполагаемому стандартному отклонению случайных ошибок), а к взвешенным данным применяется обычный МНК.
Взвешенный метод наименьших квадратов
- Сделаем предположение, что данное неравенство будет верным для n , т.е. n ∑ i = 1 n ( x i ) 2 — ∑ i = 1 n x i 2 > 0 – справедливо.
- Теперь докажем справедливость при n + 1 , т.е. что ( n + 1 ) ∑ i = 1 n + 1 ( x i ) 2 — ∑ i = 1 n + 1 x i 2 > 0 , если верно n ∑ i = 1 n ( x i ) 2 — ∑ i = 1 n x i 2 > 0 .
Взвешенный метод наименьших квадратов (WLS), также известный как взвешенная линейная регрессия, является обобщением обыкновенный метод наименьших квадратов и линейная регрессия в котором ошибки ковариационная матрица разрешено отличаться от единичная матрица.WLS также является специализацией обобщенный метод наименьших квадратов в котором указанная выше матрица диагональ.
Метод наименьших квадратов в Excel. Регрессионный анализ
- диапазон известных значений для Y (в данном случае данные для товарооборота);
- диапазон x1, …xn, т. е. величины торговых площадей;
- и известные, и неизвестные значения x, для которого нужно выяснить размер товарооборота (информацию об их расположении на рабочем листе см. далее).
Конст — логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если Конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом. Если аргумент Конст имеет значение ЛОЖЬ, то b полагается равным 0 и значения a подбираются так, чтобы выполнялось соотношение y=ax.
Полезные сведения → Как объединить ячейки → Как вставить значения → Аргументы функции → Работа с форматами → Функция ЕСЛИ → Как удалить пробелы → Функция впр vlookup→ Работа с таблицами
Графическая иллюстрация метода наименьших квадратов (мнк).
На графиках все прекрасно видно. Красная линия – это найденная прямая y = 0.165x+2.184, синяя линия – это , розовые точки – это исходные данные.
Для чего это нужно, к чему все эти аппроксимации?
Я лично использую для решения задач сглаживания данных, задач интерполяции и экстраполяции (в исходном примере могли бы попросить найти занчение наблюдаемой величины y при x=3 или при x=6 по методу МНК). Но подробнее поговорим об этом позже в другом разделе сайта.
Чтобы при найденных а и b функция принимала наименьшее значение, необходимо чтобы в этой точке матрица квадратичной формы дифференциала второго порядка для функции была положительно определенной. Покажем это.
Дифференциал второго порядка имеет вид:
То есть
Следовательно, матрица квадратичной формы имеет видпричем значения элементов не зависят от а и b .
Покажем, что матрица положительно определенная. Для этого нужно, чтобы угловые миноры были положительными.
Угловой минор первого порядка . Неравенство строгое, так как точки несовпадающие. В дальнейшем это будем подразумевать.
Угловой минор второго порядка
Докажем, что методом математической индукции .
Проверим справедливость неравенства для любого значения n, например для n=2.
Получили верное неравенство для любых несовпадающих значений и .
Предполагаем, что неравенство верное для n.
– верное.
Докажем, что неравенство верное для n+1.
То есть, нужно доказать, что исходя из предположения что – верное.
Поехали.
Выражение в фигурных скобках положительно по предположению пункта 2), а остальные слагаемые положительны, так как представляют собой квадраты чисел. Этим доказательство завершено.
Вывод : найденные значения а и b соответствуют наименьшему значению функции , следовательно, являются искомыми параметрами для метода наименьших квадратов.
Постановка задачи на конкретном примере
Предположим, имеются два показателя X и Y. Причем Y зависит от X. Так как МНК интересует нас с точки зрения регрессионного анализа (в Excel его методы реализуются с помощью встроенных функций), то стоит сразу же перейти к рассмотрению конкретной задачи.
Итак, пусть X — торговая площадь продовольственного магазина, измеряемая в квадратных метрах, а Y — годовой товарооборот, определяемый в миллионах рублей.
Требуется сделать прогноз, какой товарооборот (Y) будет у магазина, если у него та или иная торговая площадь. Очевидно, что функция Y = f (X) возрастающая, так как гипермаркет продает больше товаров, чем ларек.
Аппроксимация в 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!
Окончательный результат диаграммы представлен на следующем снимке экрана.
Как работает стандартное отклонение в Excel
Добрый день!
В статье я решил рассмотреть, как работает стандартное отклонение в Excel с помощью функции СТАНДОТКЛОН. Я просто очень давно не описывал и не комментировал статистические функции, а еще просто потому что это очень полезная функция для тех, кто изучает высшую математику.
А оказать помощь студентам – это святое, по себе знаю, как трудно она осваивается.
В реальности функции стандартных отклонений можно использовать для определения стабильности продаваемой продукции, создания цены, корректировки или формирования ассортимента, ну и других не менее полезных анализов ваших продаж.
В Excel используются несколько вариантов этой функции отклонения:
- Функция СТАНДОТКЛОНА – вычисляется отклонение по выборке текстовых и логических значений. При этом ложные логические и текстовые значения формула приравнивает к 0, а 1 будут равняться только истинные логические значения;
- Функция СТАНДОТКЛОН.В – производит оценку стандартного отклонения по выборке, при этом текстовые и логические значения игнорирует;
- Функция СТАНДОТКЛОН.Г – делает оценку отклонения по некой генеральной совокупности и как в предыдущей функции игнорируются текстовые и логические значения;
- Функция СТАНДОТКЛОНПА – также вычисляет по генеральной совокупности стандартное отклонение, но с учетом текстовых и логических значений. Равняться 1 будут только истинные логические значения, а ложные логические и текстовые значения будут приравнены к 0.
Математическая теория
Для начала немножко о теории, как математическим языком можно описать функцию стандартного отклонения для применения ее в Excel, для анализа, к примеру, данных статистики продаж, но об этом дальше. Предупреждаю сразу, буду писать очень много непонятных слов… )))), если что ниже по тексту смотрите сразу практическое применение в программе.
Что же собственно делает стандартное отклонение? Оно производит оценку среднеквадратического отклонения случайной величины Х относительно её математического ожидания на основе несмещённой оценки её дисперсии. Согласитесь, звучит запутанно, но я думаю учащиеся поймут о чём собственно идет речь!
Теперь можно дать определение и стандартному отклонению – это анализ среднеквадратического отклонения случайной величины Х сравнительно её математической перспективы на основе несмещённой оценки её дисперсии. Формула записывается так: Отмечу, что все две оценки предоставляются смещёнными. При общих случаях построить несмещённую оценку не является возможным. Но оценка на основе оценки несмещённой дисперсии будет состоятельной.
Практическое воплощение в Excel
Ну а теперь отойдём от скучной теории и на практике посмотрим, как работает функция СТАНДОТКЛОН. Я не буду рассматривать все вариации функции стандартного отклонения в Excel, достаточно и одной, но в примерах. А для примера рассмотрим, как определяется статистика стабильности продаж.
Для начала посмотрите на орфографию функции, а она как вы видите, очень проста:
=СТАНДОТКЛОН.Г(_число1_;_число2_; ….), где:
Число1, число2, … — являют собой генеральную совокупность значений и имеют только числовые значения или же ссылки на них. Формула поддерживает до 255 числовых значений.
Теперь создадим файл примера и на его основе рассмотрим работу этой функции.
Так как для проведения аналитических вычислений необходимо использовать не меньше трёх значений, как в принципе в любом статистическом анализе, то и я взял условно 3 периода, это может быть год, квартал, месяц или неделя. В моем случае – месяц.
Для наибольшей достоверности рекомендую брать как можно большое количество периодов, но никак не менее трёх. Все данные в таблице очень простые для наглядности работы и функциональности формулы.
Для начала нам необходимо посчитать среднее значение по месяцам. Будем использовать для этого функцию СРЗНАЧ и получится формула: =СРЗНАЧ(C4:E4). Теперь собственно мы и можем найти стандартное отклонение с помощью функции СТАНДОТКЛОН.Г в значении которой нужно проставить продажи товара каждого периода.
Получится формула следующего вида: =СТАНДОТКЛОН.Г(C4;D4;E4). Ну вот и сделана половина дел. Следующим шагом мы формируем «Вариацию», это получается делением на среднее значение, стандартного отклонения и результат переводим в проценты.
Получаем такую таблицу: Ну вот основные расчёты окончены, осталось разобраться как идут продажи стабильно или нет. Возьмем как условие что отклонения в 10% это считается стабильно, от 10 до 25% это небольшие отклонения, а вот всё что выше 25% это уже не стабильно.
Для получения результата по условиям воспользуемся логической функцией ЕСЛИ и для получения результата напишем формулу:
=ЕСЛИ(H4