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

Министерство образования Российской Федерации УФИМСКИЙ ГОСУДАРСТВЕННЫЙ АВИАЦИОННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

по использованию программной среды «Microsoft Excel» для расчета эмпирических моделей

в курсовом и дипломном проектировании

Уфа 2003

Составители: М.А. Анфёров, С.Р.Шехтман

УДК 621.9:519.673 ББК 32.973.26–018:34.5(07)

Методические указания по использованию программной среды «Microsoft Excel» для расчета эмпирических моделей в курсовом и дипломном проектировании / Уфимск. гос. авиац. техн. ун-т; Сост.: М.А. Анфёров., С.Р. Шехтман.– Уфа, 2003.– 26с.

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

Предназначены для студентов всех форм обучения по направлению бакалаврской подготовки 552900 – Технология, оборудование и автоматизация машиностроительных производств, а также по направлениям подготовки дипломированных специалистов: 657800 – «Кон- структорско-технологическое обеспечение машиностроительных производств» (специальность 120100 – «Технология машиностроения») и 651400 – «Машиностроительные технологии и оборудование» (специальность 120700 – «Машины и технология высокоэффективных процессов обработки материалов»)

Табл.2. Ил.11. Библиогр.: 4 назв.

Рецензенты: канд. техн. наук, доц. Патрушев Г.А., канд. техн. наук, доц. Черников П.П.

© Уфимский государственный авиационный технический университет, 2003

Содержание

3

Введение

Высокий уровень современных информационных технологий позволил автоматизировать интеллектуальную деятельность человека в самых различных областях (кадровый или бухгалтерский учет, управление хозяйственной деятельностью, делопроизводство и др.) путем использования компьютерных систем, реализуемых на различных платформах и операционных средах. Наряду со специализированными программными продуктами, ориентированными на решение конкретных задач («AutoCAD» – конструирование, «ТехноПро» – технологическое проектирование, «ArhiCAD» – создание архитектурных проектов и др.), широкое распространение получили программные приложения, используемые во всех, в прямом смысле этого слова, областях человеческой деятельности. К разряду этих программных продуктов можно смело отнести разработки фирмы Microsoft:

•«Excel» – среда вычислений и инженерно-научного анализа на базе электронныхтаблиц,

•«Access» – среда создания и управления базами данных,

•«PowerPoint»– среда создания и демонстрации презентаций,

•«Word» – текстовый процессор,

функционирующие в среде операционной системы из семейства Windows, с возможностью взаимной передачи разрабатываемых объектов (например, таблиц из «Excel» в «Word» или «Access», рисунков или текста «Word» в «Excel» и т.д.). Возможность визуального программирования в этих средах средствами языка «Visual Basic» позволяет создавать современный интерфейс пользователя и расширять за счет макросов функциональные возможности разрабатываемых проектов, что повышает ихкоммерческую и научную привлекательность.

Настоящие методические указания раскрывают методику решения одной из многих инженерных и научных задач в среде «Microsoft Excel» – расчет параметров эмпирической модели, аппроксимирующей результаты экспериментальных статистических данных при анализе и выявлении широкого спектра закономерностей в технике, технологии, экономики и др. Предполагается, что студенты владеют основными навыками работы с персональным компьютером в операционной среде Widows’98/2000/NT/XP, а также с приложением «Microsoft Excel», приобретенными при изучении дисциплины «Информатика».

4

1. Теоретические положения

1.1. Моделирование экспериментальных зависимостей

Классификация математических моделей по способу их получения предполагает наличие двух классов – теоретических и эмпирическихмоделей /1/.

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

Эмпирические модели строятся путем статистических исследований внешних функциональных проявлений описываемого объекта, т.е. они являются функциональными моделями, связывающими группы параметров: управляющих(входных) – X, выходных–Y, состояния – Z с учетом внешних параметров Q посредством областей адекватности /2/. На этапе исследований получают статистические выборки множества испытаний (событий), каждому из которых соответствует точка в пространстве значений аргументов (параметров X) и точка в пространстве значений выходных параметров или параметров состояния. Далее интуитивно определяют несколько альтернативных форм

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

1 Для упрощения рассматриваются стационарные модели, исключающие параметр времени.

5

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

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

Предполагается, что величины x и y связаны функциональной зависимостью:

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

ментальных точек и отразили их графически в координатной плоскости x0y (см. рис.1, а). Экспериментальные точки в этом случае расположатся не совсем правильным образом — дадут некоторый «разброс», т. е. обнаружатся случайные отклонения от видимой общей закономерности. Это связано с неизбежными при всяком опыте ошибками измерения, влиянием внешнихвозмущающихфакторов, которые не могут быть в полной мере учтены и не всегда выявлены, исключением из модели другихуправляющихпараметров.

Возникает вопрос, как по этим, экспериментальным данным наилучшим образом воспроизвести зависимость y от x ? Известно, что через любые n точек с координатами (xi , yi ) всегда можно провести кривую, выражаемую аналитически полиномом степени (n – 1), так, чтобы она в точности прошла через каждую из точек (кривая 1 на рис.1, б). Однако такое решение вопроса обычно не является удовле-

6

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

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

2

Рис. 1

ные данные. Сам тип кривой (2) выбираетсяпутем анализавнешнеговида зависимости. Это может быть прямолинейная зависимость y=ax+b или криволинейная, хорошо описываемая в большинстве случаев полиномом второй степени y=ax2 +bx+c. Если речь идет о периодической функции, то часто ее можно представить несколькими гармониками тригонометрического ряда.

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

7

ких числовых параметров при определенном виде зависимости (2) и рассматривается в настоящихметодическихуказаниях.

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

где i — номер опыта; xi – значение аргумента; yi – соответствующее значение

c, ... Требуется так выбрать эти параметры, чтобы кривая y=ϕ(x) в ка-

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

Решение этой задачи, как и любой задачи выравнивания или сглаживания, зависит от того, что именно принято считать «наилучшим». Можно, например, считать «наилучшим» такое взаимное расположение кривой и экспериментальных точек, при котором максимальное расстояние между ними обращается в минимум; можно потребовать, чтобы в минимум обращалась сумма абсолютных величин отклонений точек от кривой и т. д. При каждом из этих требований мы получим свое решение задачи, свои значения параметров a, b, c, ... Однако общепринятым при решении подобных задач является так называемый метод наименьших квадратов, при котором требование наилучшего согласования кривой (2) и экспериментальных точек сводится к тому, чтобы сумма квадратов отклонений экспериментальныхточек от сглаживающей кривой обращалась в минимум.

Метод наименьших квадратов имеет перед другими методами сглаживания существенные преимущества: во-первых, он приводит к сравнительно простому математическому способу определения параметров a, b, c, ... ; во-вторых, он допускает довольно веское теоретическое обоснование с вероятностной точки зрения /3/.

Перейдем к задаче определения параметров a, b, c, ... методом наименьших квадратов, которые требуется выбрать так, чтобы сумма квадратов отклонений yi от ϕ( xi) была минимальна. Запишем y как

Требуется выбрать а, b, с, ... так, чтобы выполнялось условие1:

i=1

Найдем значения а, b, с, ... , обращающие левую часть выражения (4) в минимум. Для этого продифференцируем ее по а, b, с, ... и приравняем производные нулю2:

Система уравнений (5) содержит столько же уравнений, сколько неизвестных а, b, с, ... Решить систему (5) в общем виде нельзя; для этого необходимо задаться конкретным видом функции (2).

Рассмотрим часто встречающийся на практике случай: когда функция (2) выражается полиномом второй степени (параболой). В этом случае имеем

y = ϕ(x; a, b, c) = a x2+ b x + c ,

∂ϕ∂a = x2 ( ∂ϕ∂a )i = x2i ∂ϕ∂b = x ( ∂ϕ∂b )i = xi

∂ϕ∂c = 1 ( ∂ϕ∂c )i = 1 .

Подставляя в уравнения (5) получаем

1 Величина n соответствует смысловомузначению в табл. 1.

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

9