Текст урока с работающими фрагментами расчетов в файле uroki-approksimacii.xls
Как и предыдущие, этот урок с аналогичным текстом лучше смотреть не листе Excel (см. Уроки аппроксимации.xls, Лист1)
Аппроксимация в Excel проще всего реализуется с помощью программы построения трендов. Для выяснения особенностей аппроксимации возьмем какой-либо конкретный пример. Например, энтальпию насыщенного пара по книге С.Л.Ривкина и А.А.Александрова "Теплофизические свойства воды и водяного пара", М., "Энергия", 1980г. В колонке P поместим значения давления в кгс/см2, в колонке i" - энтальпию пара на линии насыщения в ккал/кг и построим график с помощью опции или кнопки "Мастер диаграмм".
Щелкнем правой кнопкой по линии на рисунке, затем левой кнопкой по опции "Добавить линию тренда" и смотрим - какие услуги предлагаются нам этой опцией в части реализации аппроксимации в Excel.
Нам предлагается на выбор пять типов аппроксимации: линейная, степенная, логарифмическая, экспоненциальная и полиноминальная. Чем они хороши и чем могут нам помочь? - Нажимаем кнопку F1, затем щелкаем по опции "Мастер ответов" и в появившееся окошко вводим нужное нам слово "аппроксимация", после чего щелкаем по кнопке "Найти". Выбираем в появившемся списке раздел "Формулы для построения линий тренда".
Получаем следующую информацию в несколько измененной нами
редакции:
Линейная:
Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:
y=a+b*x
где b - угол наклона и a - координата пересечения оси абсцисс (свободный член).
Степенная:
Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:
y=c*x^b
где c и b - константы.
Логарифмическая:
Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:
y=a+b*ln(x)
где a и b - константы.
Экспоненциальная:
Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:
y=b*Exp(k*x)
где b и k - константы.
Полиноминальная:
Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:
y=a+b1*x+b2*x^2+b3*x^3+...b6*x^6
где a, b1, b2, b3,... b6 - константы.
Снова щелкаем по линии рисунка, затем по опции "Добавить линию тренда", далее по опции "Параметры" и ставим флажки в окошках слева от записей: "показывать уравнение на диаграмме" и "поместить на диаг- рамму величину достоверности аппроксимации R^2, после чего щелкаем по кнопке OK. Пробуем все варианты аппроксимации по порядку.
Линейная аппроксимация дает нам R^2=0.9291 - это низкая достоверность и плохой результат.
Для перехода к степенной аппроксимации щелкаем правой кнопкой по линии тренда, затем левой кнопкой - по опции "Формат линии тренда", далее по опциям "Тип" и "Степенная". На этот раз получили R^2=0.999.
Запишем уравнение линии тренда в виде, пригодном для расчетов на листе Excel:
y=634.16*x^0.012
В результате имеем:
Максимальная погрешность аппроксимации получилась на уровне 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 виде:
y=1E+07*x^5-4E+06*x^4+469613*x^3-27728*x^2+1020.8*x+592.44
и сравним результат аппроксимации с исходной таблицей:
Оказалось, что R^2=1 в данном случае лишь блестящая ложь. Реально, самый лучший результат полиноминальной аппроксимации дал самый простой полином вида y=a+b1*x+b2*x^2. Но его результат хуже, чем в варианте степенной аппроксимации y=634.16*x^0.012, где максимальная погрешность аппроксимации находилась на уровне 0.23 ккал/кг. Это все, что мы можем выжать из программы построения трендов. Посмотрим, что мы можем выжать из функции Линейн. Для нее попробуем вариант степенной аппроксимации.
Примечание. Обнаруженный дефект связан с работой программы построения трендов, но не с методом МНК.