Учебное пособие: Использование возможностей Microsoft Excel в решении производственных задач

Пояснения к алгоритму:

* Поскольку ширина и длина листа могут изменяться не непрерывно, а с определенным шагом, то алгоритмически проще варьировать количеством таких шагов (их кратностью) несколько преобразовав формулу для определения соответственно ширины и длины шага (см. пп4,5)

** Шаг между заготовками в ряду при углах косого раскроя менее 60° увеличивается за счет того, что заготовки из соседних рядов при величине перемычки равной a1 начинают накладываться друг на друга.

*** Дополнительную заготовку можно разместить в нижнем ряду в том случае, если длина ряда окажется меньше длины полосы на величину диаметра с учетом перемычки

При проведении занятия необходимо средствами Excel решить следующие задачи:

11.  Используя алгоритм, приведенный выше, составить электронную таблицу, позволяющую рассчитать целевую функцию для произвольного диаметра D варьируя размерами листа и углом косого раскроя.

12.  Проанализировать влияние угла раскроя на величину коэффициента использования металла, ширину концевого отхода и обобщенную целевую функцию при значения диаметра заготовки D=141 мм и D=120 мм, построив соответствующие графики.

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

14.  Найти оптимальный раскрой листа для значения диаметра заготовки, заданного преподавателем.

Методы решения с использованием Excel:

Для решения задач оптимизации в Excel используют уже изученную нами надстройку Поиск решения, диалоговое окно которой вызывается по команде Сервис-Поиск решения (см. работу 3). Здесь мы поясним дополнительные возможности настройки алгоритма поиска решения. Окно настройки вызывают нажатием экранной клавиши Параметры диалогового окна Поиск решения.

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

Максимальное время - Служит для ограничения времени, отпускаемого на поиск решения задачи. В поле можно ввести время (в секундах) не превышающее 32767; значение 100, используемое по умолчанию, подходит для решения большинства простых задач.

Итерации - Служит для управления временем решения задачи, путем ограничения числа промежуточных вычислений. В поле можно ввести время (в секундах) не превышающее 32767; значение 100, используемое по умолчанию, подходит для решения большинства простых задач.

Точность - Служит для задания точности, с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 (нуля) до 1. Чем меньше введенное число, тем меньше точность. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации.

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

Сходимость - Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость, поиск прекращается. Сходимость применяется только к нелинейным задачам, условием служит дробь из интервала от 0 (нуля) до 1. Лучшую сходимость характеризует большее количество десятичных знаков ѕ например, 0,0001 ѕ это меньшее относительное изменение, чем 0,01. Лучшая сходимость требует больше времени на поиск оптимального решения.

Линейная модель - Служит для ускорения поиска решения линейной задачи оптимизации или линейной аппроксимации нелинейной задачи.

Показывать результаты итераций - Служит для приостановки поиска решения для просмотра результатов отдельных итераций.

Автоматическое масштабирование - Служит для включения автоматической нормализации входных и выходных значений, качественно различающихся по величине например, максимизация прибыли в процентах по отношению к вложениям, исчисляемым в миллионах рублей.

Значения не отрицательны - Позволяет установить нулевую нижнюю границу для тех влияющих ячеек, для которых она не была указана в поле Ограничение диалогового окна Добавить ограничение.

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

Линейная - Служит для использования линейной экстраполяции вдоль касательного вектора.

Квадратичная - Служит для использования квадратичной экстраполяции, которая дает лучшие результаты при решении нелинейных задач.

Производные - Служит для указания метода численного дифференцирования (прямые или центральные производные) который используется для вычисления частных производных целевых и ограничивающих функций.

Прямые - Используется для гладких непрерывных функций.

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

Метод - Служит для выбора алгоритма оптимизации (метод Ньютона или сопряженных градиентов) для указания направление поиска.

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

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

Загрузить модель - Служит для отображения на экране диалогового окна Загрузить модель, в котором можно задать ссылку на область ячеек, содержащих загружаемую модель.

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

Дополнительно, к использованным ранее тригонометрическим функциям, функции ПИ () и функции РАДИАНЫ (), в данной задаче будут полезны следующие функции:

ЦЕЛОЕ (число): округляет число вниз до ближайшего целого.

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

ЕСЛИ (логическое_выражение; значение_если_истина; значение_если_ложь): служит для получения в ячейке результата, зависящего от некоторых условий.

Аргумент логическое_выражение служит для записи условия, в котором сравниваются числа, функции, формулы. Любое логическое выражение должно содержать по крайней мере один оператор сравнения, который определяет отношение между элементами логического выражения. Такими операторами могут быть: > (больше), < (меньше), = (равно), <= (меньше либо равно), >= (больше либо равно),<> (не равно). В качестве аргументов логического выражения можно использовать числа, ссылки не другие ячейки, другие функции, а также формулы.

Аргументы значение_если_истина и значение_если_ложь представляют собой числа или формулы для вычисления чисел, которые должны использоваться для расчета значений в ячейке если значение логического выражения является соответственно истиной, либо ложью.

Последовательность выполнения

66.  Запустить EXCEL

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

68.  Создать рядом с основной таблицу для варьирования величиной угла раскроя в пределах от 55 до 90 градусов с шагом в 5 градусов.

69.  Определить значения коэффициента использования металла, целевой функции и ширины концевого отхода в зависимости то угла раскроя для D=141 при iВ=iL=5. Результаты поместить во вспомогательную таблицу на Лист2 (копируйте значения, а не формулы).

70.  Повторить вычисления для D=120 при iВ=iL=5. Результаты также поместить на Лист2.

71.  Построить графики изменения полученных величин. Вид графиков, которые должны получиться приведены в приложении 2.

72.  Задать необходимые параметры для поиска оптимального решения. Целевая ячейка - в которой вычисляется F. Изменяемые ячейки - те в которых помещены значения, iB, iL,a. Ограничения: 0£a£90, 0£iВ£10, 0£iL£10, iB, iL - целые. Рекомендуемы параметры окна настройки поиска решения приведены на рисунке выше.

73.  Найти оптимальное решение для D=141 и D=120 мм. При поиске решения следует иметь ввиду, что целевая функция является многоэкстремальной. Для таких функций невозможно гарантировать получение глобального экстремума из любой начальной точки. Поэтому используется метод пробных начальных точек. Выбирают несколько начальных точек в различных областях пространства управляемых параметров. Производят оптимизацию из каждой начальной точки и за глобальный оптимум принимают наилучшее из полученных решений. В качестве начальных пробных точек в данной задаче рекомендуется использовать следующие:

74.  Найти оптимальное решение для значения диаметра, предложенное преподавателем.

75.  Построить для найденных оптимальных размеров листа графики изменения коэффициента использования металла, целевой функции и ширины концевого отхода на листе 3.

76.  Завершить работу, сохранив ее в файле work7. xls.

77.  Запустить EXCEL, вернуться к документу work5. xls и предъявить его преподавателю.

Приложение 1: Внешний вид таблицы и результаты вычислений (для справки)

Приложение 2:

Страницы: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13