Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
ПРИКЛАДНЫЕ РАСЧЕТЫ В MS EXC Часть 2.doc
Скачиваний:
2
Добавлен:
01.07.2025
Размер:
14 Мб
Скачать
☆

Предварительная аппроксимация и сглаживание данных

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

Оценка эффективности различных методов численного дифференцирования и интегрирования

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

Ya=0,1+sin(3*X)

с нанесенным шумом в виде: Δ=0,1*(r-0,5),

где r – случайное число в диапазоне 0 – 1.

Таким образом, исходные данные представлялись зависимостью Υ=Υa(Χ) + Δ. При этом набор значений аргумента X был далек от арифметической прогрессии.

На рис.2.24 приведены результаты аппроксимации исходных зашумленных данных полиномом 4-ой степени:

Производные и интегралы от аппроксимирующего полинома имеют вид:

Реализация методов численного дифференцирования представлена на рис. 2.25

Р еализация методов численного интегрирования представлена на рис. 2.26

Для оценки эффективности рассмотренных методов численного дифференцирования и интегрирования, проводилось сравнение результатов численного расчета с результатами аналитического дифференцирования и интегрирования исходной незашумленной зависимости Ya=0,1+sin(3*X):

,

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

2.10 Поиск оптимального решения

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

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

Первая часть таблицы содержит справочную информацию, а вторая планируемые показатели. Ниже располагается расчетная часть таблицы, в которой изменяемые ячейки (B18:D20) содержат искомые параметры, значения которых будут варьироваться. Целевая ячейка (F21) содержит функционал, значение которого необходимо минимизировать. Расчетные формулы представлены в копии таблицы на втором плане рисунка.

Для выполнения оптимизационных расчетов необходимо вызвать надстройку Поиск решения и произвести установку основных параметров оптимизации (Рис.2.30):

  • Установить целевую ячейку F21;

  • Установить переключатель Равной: в положение минимальному значению;

  • Установить изменяемые ячейки B18:D20.

Значения в ячейках B18:D20 задаются в первом приближении. Желательно (но не обязательно) задавать значения близкие окончательному решению.

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

  1. Указать, что значения в изменяемых ячейках должны быть целыми (B18:D20=целое);

  2. Запретить для изменяемых ячеек отрицательные значения (B18:D20>=0);

  3. Ввести ограничения по количеству используемых судов каждого типа (B21<=D4), (C21<=E4), (E21<=F4);

  4. У становить ограничения по объему перевозок для каждой линии (E18>=D13), (E19>=E13), (E20>=F13).

П ервые три пункта задания ограничений представляются естественными. По последнему пункту следует заметить, что практически реализовать точное выполнение условий (E18=D13), (E19=E13), (E20=F13) вряд ли возможно. Поэтому допускается выполнение последних рейсов с некоторым недогрузом. Главное – обеспечить выполнение планируемых показателей (D13, E13, F13).

Для тонкой настройки оптимизации используется панель Параметры поиска решения (Рис.2.30) вызываемая кнопкой Параметры окна Поиск решения.

После настройки надстройки Поиск решения нажимают кнопку Выполнить.

Время необходимое для получения окончательного решения (от нескольких секунд до нескольких минут) зависит от параметров настройки и сложности задачи.

По завершению вычислительного процесса появляется диалоговое окно Результаты поиска решения (Рис 2.31). Если результаты решения Вас удовлетворяют установите переключатель в положение Сохранить найденное решение. Для повторного запуска пакета установите – Восстановить исходные значения и попытайтесь откорректировать первое приближение.

Для сохранения протокола работы надстройки укажите тип отчета - Результаты.

Рассмотрим пример проведения оптимизационных расчетов связанных с организацией закупок различных изделий исходя из минимума суммарных издержек. Учитывается, что в зависимости от количества закупаемых изделий каждого типа (n) покупателю предоставляется скидка. Исходная информация и расчетная таблица представлены на рис.2.32.

Формулы в ячейках таблицы:

B(19)  =СУММ(B16:B18); С(19), D(19)  копированием B(19);

E(16)  =ЕСЛИ(B16<=100;B4;ЕСЛИ(B16>=200;B4*(100-$G4)/100;B4*(100-$F4)/100));

E(17), E(18), F(16), F(17), F(18), G(16), G(17), G(18)  копированием E(16);

H(16)  ==E16*B16+F16*C16+G16*D16; H(17), H(18)  копированием H(16);

H(18)  =СУММ(H16:H18).

Н астройки надстройки Поиск решения:

Целевая ячейка – $H$19;

Переключатель в положении: “Равной минимальному значению”;

Изменяемые ячейки –$B$16:$D$18;

Ограничения:

$B$16:$D$18=целое, $B$16:$D$18>=0, $B$19=$C$10, $C$19=$C$11, $D$19=$C$12.

Результаты оптимизации:

Фирма

Изделие 1

Изделие 2

Изделие 3

Цена

изделия 1

Цена

изделия 2

Цена

изделия 3

Суммарные затраты

ААА

111

0

83

114

150

125

23029,00

БББ

89

106

13

115

137,75

130

26526,50

ВВВ

100

144

24

118,75

123,5

128

32731,00

Всего

300

250

120

82286,50