Шубин С.И. Применение программы Поиск Решения Microsoft Excel для решения задач по курсу Детали машин и основы конструирования - файл n1.doc

Шубин С.И. Применение программы Поиск Решения Microsoft Excel для решения задач по курсу Детали машин и основы конструирования
Скачать все файлы (639.5 kb.)

Доступные файлы (1):
n1.doc640kb.16.02.2014 15:46скачать

n1.doc

Министерство транспорта Российской Федерации

Федеральное агентство железнодорожного транспорта

ГОУ ВПО «Дальневосточный государственный

университет путей сообщения»


Кафедра «Детали машин»



С.И. Шубин

Применение программы «Поиск Решения» Microsoft Excel для решения задач по курсу

« Детали машин и основы конструирования»
Методические указания по использованию компьютерной программы

в курсе «Детали машин и основы конструирования»


Хабаровск

Издательство ДВГУПС


2005

УДК 621.81:004.9(075.8)

ББК К42я73

Ш 951
Рецензент:
Кандидат технических наук, доцент кафедры

«Системы автоматизированного проектирования»

Дальневосточного государственного университета путей сообщения

Т.С. Красовская


Шубин, С. И.

Ш 951 Применение программы «Поиск Решения» Microsoft Excel для                решения задач по курсу «Детали машин и основы конструирова-               ния»: метод. указ. по использованию компьютерной программы в                курсе «Детали машин и основы конструирования» / С. И. Шубин. –                Хабаровск : Изд-во ДВГУПС, 2005 – 17 с. : ил.

Методические указания соответствуют образовательному стандарту дисциплины «Детали машин и основы конструирования» подготовки дипломированного специалиста направления 657600 «Подвижной состав железных дорог». В методических указаниях представлены порядок действий при использовании программы «Поиск Решения» Microsoft Excel.

На конкретном примере рассматривается нахождение экстремума целевой функции с помощью программы « Поиск Решения» Microsoft Excel.

Методические указания рекомендованы студентам ИТПС всех специальностей и форм обучения при выполнении расчетно-графических заданий и курсового проекта по курсу «Детали машин и основы конструирования».
Отпечатано с авторского оригинала

УДК 621.81:004.9(075.8)


ББК К42я73

© ГОУ ВПО «Дальневосточный государственный


университет путей сообщения» (ДВГУПС), 2005

Введение
В настоящее время все шире применяется автоматизированное проектирование (САПР).

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

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

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

Одним из инструментов оптимизации является программа «Поиск Решения» Microsoft Excel.

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

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

1) систему уравнений, описывающих взаимосвязь параметров;

2) систему ограничений;

3) систему условий.

Назначаются:

1. Целевая ячейка, называемая также целью, или целевой функцией, – это ячейка модели рабочего листа, для которой нужно найти максимальное, минимальное или заданное значение,

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

Ограничения могут быть наложены на целевую ячейку и изменяемые ячейки.

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

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

Программа «Поиск Решения» Microsoft Excel позволяет анализировать оптимизационные задачи трех типов:

1. Линейные.

2. Нелинейные.

3. Целочисленные.

Линейные и нелинейные оптимизационные задачи отображают зависимости между элементами задачи в виде формул на рабочем листе.

Целочисленные задачи возникают при наложении ограничения целочисленности на любой элемент задачи, решаемой с помощью «Поиска Решения».
2. Работа с «Поиском Решения» Microsoft Excel
«Поиск Решения» можно запустить, выбрав в меню Сервис - Поиск Решения(Tools-Solver).Если этого пункта в меню нет, то его следует загрузить, выполнив команду меню Сервис - Надстройки (Tools - Add Ins).В открывшемся диалоге следует поставить флажок Поиск Решения (Solver Add-In).После запуска откроется диалоговое окно Поиск Решения (Solver Parameters) (рис. 1).




Рис. 1. Диалоговое окно Поиск Решения
В поле «Установить Целевую Ячейку» (Set Target Cell) задается адрес ячейки, значение которой необходимо оптимизировать. С помощью опций в левой части диалога можно задать условие нахождения соответственно максимума, минимума или определенного ее значения. В поле «Изменяя Ячейки» (By Changing Cells) называются адреса ячеек, значения которых будут варьироваться в процессе поиска решения. В списке Ограничения (Subjects to the Constrains) указываются дополнительные условия, которые необходимо учитывать при поиске решения. Для того чтобы задать новое ограничение, следует нажать кнопку «Добавить» (Add).

Кнопка «Удалить» (Delete) удаляет ограничение из списка, а кнопка «Изменить» (Change) дает возможность вносить изменения в параметры. «Поиск Решения» может и самостоятельно определить изменяемые ячейки на основании целевой ячейки. Для этого необходимо нажать кнопку «Предложить». Можно задать до 200 изменяемых ячеек. Как правило, окно «Изменяя Ячейки» содержит одну ссылку на интервал ячеек или несколько ссылок на несмежные ячейки, разделенных точками с запятыми. Ограничения (рис. 2.) могут задавать верхние и нижние пределы для значения в любой ячейке модели, включая целевую ячейку и изменяемые ячейки. В одной задаче можно определить по два ограничения для каждой изменяемой ячейки (верхний и нижний предел) и до 100 дополнительных ячеек. Ограничения целочисленности можно наложить только на изменяемые ячейки.



Рис. 2. Диалоговое окно Добавление ограничения
С помощью кнопки «Параметры» (Options) можно воспользоваться возможностью подбора допустимого отклонения (рис. 3).



Рис. 3. Диалоговое окно Параметры
Нажатием кнопки «Выполнить» (Solve) начинается процедура решения задачи.

Время решения задачи зависит от следующих факторов.

1. Количества изменяемых ячеек.

2. Размера и сложности рабочего листа.

3. Сложности самой задачи.

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

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

Когда Excel находит решение, то в диалоговом окне Результаты Поиска Решения появится запись «Решение найдено». Все ограничения и условия оптимальности выполнены» (рис. 4).




Рис. 4. Результаты поиска решения
В этом случае с результатами можно поступить по одному из следующих вариантов.

1. Сохранить решение.

2. Восстановить исходные значения.

3. Сохранить решение в виде именованного сценария при помощи Диспетчера Сценариев.

4. Посмотреть любой из встроенных отчетов «Поиска Решения».

Технические подходы, применяемые программой «Поиск Решения» Microsoft Excel на различных этапах процесса решения задачи, можно изменить при помощи окон, расположенных в нижней части диалогового окна Параметры (Options). Установки, принимаемые для этих параметров по умолчанию, подходят почти для всех типов задач (рис. 3).

Если решение окажется слишком далеким от ожидаемого, необходимо еще раз запустить программу с другими значениями изменяемых ячеек.
3. Пример работы с «Поиском Решения» Microsoft Excel           при решении задач по курсу
«Детали машин и основы конструирования»

Рассмотрим применение программы «Поиск Решения» Microsoft Excel на примере.

Необходимо спроектировать одноступенчатый цилиндрический редуктор, схема которого представлена на рис. 5, таким образом, чтобы габариты редуктора были минимальными.
Исходные данные: – вращающий момент на выходном валу.


Рис. 5. Схема редуктора

В качестве целевой функции выберем межосевое расстояние – , искомых переменных – твердость материала зубчатых колес – , – передаточное число и коэффициент ширины зуба относительно межосевого расстояния – . Зависимость между элементами задачи выражается в виде формулы:
, (1)
где – приведенный модуль упругости материала зубчатых колес; – коэффициент концентрации нагрузки; – допускаемое контактное напряжение:
, (2)
где – предел выносливости материала по контактным напряжениям; – коэффициент безопасности; – коэффициент долговечности.
Введем ограничения:
1. Нагрузка, допускаемая по контактной прочности зубьев, определяется в основном твердостью материала.

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

Предел выносливости по контактным напряжениям для этих материалов . Коэффициент безопасности .

2. Режим нагрузки – постоянный, коэффициент долговечности .

3. Рекомендуемые значения передаточного числа – .

4. Коэффициент концентрации нагрузки для представленной схемы редуктора, можно выразить:
, (3)
где – коэффициент ширины зуба относительно диаметра делительной окружности шестерни.
, (4)
. (5)

Система условий – минимум значения межосевого расстояния. Задача полностью сформулирована.

Рабочий лист, где помещена задача для «Поиска Решения» Microsoft Excel, представлен на рис. 6.

Для проверки программы зададимся фиксированными значениями параметров – и определим значение межосевого расстояния – . Это значение может служить величиной для сравнения с тем, которое получится в результате параметрической оптимизации.




Рис. 6. Рабочий лист MS Excel с исходными данными
Структура рабочего листа представлена в табл. 1. В диалоговом окне Поиска решения определим адрес целевой ячейки, активизируем опцию Минимальное значение, зададим адреса независимых переменных и введем ограничения (рис. 7). Запустим процедуру решения задачи с помощью программы Поиск Решения Microsoft Excel.

Поскольку данная задача является многовариантной, важно определиться с начальными (стартовыми) значениями величин, которые принимаются как постоянные (твердость, передаточное число и т. д.). Желательно принимать средние значения из предложенного диапазона изменения.

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

Исходные данные Таблица 1



Параметр

Обозначение

Размерность

Формула или значение

1

Твердость материала





163

2

Передаточное число





1,25

3

Коэффициент ширины зуба относительно межосевого расстояния





0,125

4

Коэффициент ширины зуба

относительно диаметра

делительной окружности





= (C2 + 1)*C3*0,5

5

Вращающий момент

на выходном валу





300

6

Коэффициент

концентрации нагрузки





= C4^2*0,04 + 1

7

Приведенный модуль упругости





210000000000

8

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





= (C1*2 + 70)*1000000

9

Коэффициент долговечности при расчете на контактную прочность





1

10

Коэффициент безопасности при расчете на контактную прочность





1,1

11

Допускаемые

контактные напряжения





= (C8/C10)*C9

12

Межосевое расстояние





=((C7*C5*C6)/(C11^2)/(C2^2)/ /C3)^0,33*(C2 + 1)*0,75




Рис. 7. Диалоговое окно Поиска решения с внесенной информацией

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

Рабочий лист с результатами вычислений приведен на рис. 11 и в табл. 2.

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

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



Рис. 9. Отчет по устойчивости


Рис. 10. Отчет по пределам



Рис. 11. Рабочий лист с результатами вычислений

Результаты вычислений Поиска Решений Таблица 2



Параметр

Обозначение

Размерность

Формула или значение

1

Твердость материала





350

2

Передаточное число





1,868114828

3

Коэффициент ширины зуба

относительно

межосевого расстояния





0,5

4

Коэффициент ширины зуба

относительно диаметра

делительной окружности





0,717028707

5

Вращающий момент

на выходном валу





300

6

Коэффициент

концентрации нагрузки





1,020565207

7

Приведенный модуль упругости





210000000000

8

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





770000000

Окончание табл. 2





9

Коэффициент долговечности при расчете на контактную прочность





1

10

Коэффициент безопасности при расчете на контактную прочность





1,1

11

Допускаемые

контактные напряжения





700000000

12

Межосевое расстояние





0,09371443




Рис. 12. Рабочий лист с окончательными результатами вычислений


Таблица 3

Окончательные результаты вычислений



Параметр

Обозначение

Размерность

Формула или значение

1

Твердость материала





310

2

Передаточное число





1,8

3

Коэффициент ширины зуба

относительно

межосевого расстояния





0,5

4

Коэффициент ширины зуба

относительно диаметра

делительной окружности





0,7

5

Вращающий момент

на выходном валу





300

6

Коэффициент

концентрации нагрузки





1,0196

7

Приведенный модуль упругости





210000000000

8

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





690000000

9

Коэффициент долговечности при расчете на контактную прочность





1

10

Коэффициент безопасности при расчете на контактную прочность





1,1

11

Допускаемые

контактные напряжения





627272727,3

12

Межосевое расстояние





0,10076793


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

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

СОДЕРЖАНИЕ

ВведениЕ 3

1. решение задач с помощью
«Поиск решения» microsoft excel 4


2. РАБОТА С «ПОИСКОМ РЕШЕНИЯ» MICROSOFT EXCEL 4

3. ПРИМЕР РАБОТЫ С «ПОИСКОМ ПЕШЕНИЯ» MICROSOFT EXCEL

ПРИ РЕШЕНИИ ЗАДАЧ ПО КУРСУ
«дЕТАЛИ МАШИН И ОСНОВЫ КОНСТРУИРОВНИЯ» 7

Учебное издание
Шубин Сергей Иванович

Применение программы «Поиск решения» Microsoft excel

для решения задач по курсу

«Детали машин и основы конструирования»
Методические указания
Технический редактор С.С. Заикина
Отпечатано методом прямого репродуцирования

—————–––––––––————————————————————————

План 2005 г. Поз. 1.12. ИД № 05247 от 2.07.2001 г.

Сдано в набор 16.03.2005 г. Подписано в печать 19.10.2005 г.
Формат 60841/16. Бумага тип. № 2. Гарнитура «Arial». Печать RISO.

Усл. печ. л. 0,9. Зак. 247. Тираж 50 экз. Цена р.

————––––––––—————————————————————————
Издательство ДВГУПС

680021, г. Хабаровск, ул. Серышева, 47.



Учебный текст
© perviydoc.ru
При копировании укажите ссылку.
обратиться к администрации