Методические указания. Методические рекомендации по выполнению практических работ бакалаврам по направлению Агроинженерия на втором курсе для студентов очного (офо) и на третьем курсе для студентов заочного (зфо) и заочного сокращенного (зсфо) курсов обучения
 Скачать 1.06 Mb.
|
|
2.5.8. Практическая работа № 8 Решение производственных задач с использованием надстройки Поиск решения MS Excel Цель занятия изучить надстройку Excel Поиск решения, научиться применять ее для решения различных производственных и экономических задач. Краткие теоретические сведения Общие свойства, которые характерны для задач, решаемых с помощью надстройки Поиск решения существует единственная целевая ячейка, содержащая формулу, значение которой должно быть сделано максимальным, минимальным или же равным какому-то конкретному значению формула в этой целевой ячейке содержит ссылки наряд изменяемых ячеек, и поиск решения заключается в том, чтобы подобрать такие значения переменных в изменяемых ячейках, которые бы обеспечили оптимальное значение для формулы в целевой ячейке может быть задано некоторое количество ограничений – условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек. Надстройка Поиск решения запускается командой Данные / Анализ / Поиск решения. Если Поиск решения на Ленте отсутствует, нужно выполнить команду Кнопка Office / Параметры Excel / Надстройки / Перейти, ив открывшемся диалоговом окне поставить флажок у названия надстройки. Задания и порядок их выполнения Задание 1. В театре имеются билеты трех категорий детские, для взрослых и льготные. Вычислить такое оптимальное количество проданных театральных билетов разных категорий, чтобы суммарная стоимость всех проданных билетов достигла 24 000 руб, при выполнении следующих ограничений цена детских билетов равна 60 руб цена билетов для взрослых равна 100 руб цена льготных билетов равна 90 руб билетов каждой категории можно продать не более 100 шт в театре может быть аншлаг (полное заполнение зала, но лишних билетиков нет. Выполнение составить, с учетом данных условий и ограничений, математическую модель задачи 000 24 z 90 y 100 x 60 ; где x, y, z – количество проданных детских, взрослых и льготных билетов соответственно подготовить лист Excel, ввести формулы и данные (табл. 16): Таблица 16 – Лист Excel для задания 1 A B C D 1 Категория Цена, руб. Количество Стоимость 2 Детские 60 = СУММ(В2:С2) 3 Взрослые 100 = СУММ(В3:С3) 4 Льготные 90 = СУММ(В4:С4) 5 Общая стоимость проданных билетов = СУММ) выполнить команду Данные / Анализ / Поиск решения в диалоговом окне Поиск решения заполнить поля, исходя из условий и математической модели задачи Установить целевую ячейку $D$5; Равной значению 24 000; Изменяя ячейки $С$2:$С$4; Ограничения (заполняются после нажатия Добавить ♦ $С$2:$С$4 >= 0; ♦ $С$2:$С$4 = целое ♦ $С$2:$С$4 <= 100; нажать Выполнить по окончании просчета проверить правильность решения. Задание 2. Некий завод производит изделия трех категорий А, В и С. Суммарный объем производства в смену – не более 300 изделий. Необходимо определить, при этом ограничении, а также при условии, что в смену должно производиться не менее 30 штук любого изделия, количество выпускаемых изделий каждой категории таким образом, чтобы получить максимальный доход. Цена изделий А, В и Си рублей соответственно. Задание 3. Произвести прогнозирование аукционных продаж, используя исходные данные, приведенные в таблице 17 (данные приведены в том виде, как они должны быть внесены в лист Excel). Таблица 17 – Лист Excel для расчетов аукционных продаж A B C D E F G 1 Лоты нач h n заяв P тек n дн ΔP ср 2 Серьги 50 000 10 000 3 Кулон 60 000 12 000 4 Перстень 95 000 15 000 5 Кольцо 70 000 13 000 6 Подвеска 78 000 14 000 7 Среднее нач – стартовая цена h – шаг увеличения цены n заяв – число заявок тек – текущая цена n дн – количество дней на аукционе ΔP ср – среднее увеличение цены вдень определить для первого лота, сколько должно быть подано заявок в течение шести дней продаж, чтобы среднее увеличение цены вдень составляло 15 000 руб определить количество заявок и дней продажи (по каждому лоту, которые обеспечат среднее задень возрастание цены (по всем лотам, равное 360 000 рублей со следующими ограничениями количество заявок по любому лоту должно находиться в пределах от 3 до 12; количество дней продажи – от 3 до 10; значения количеств заявок и количеств дней должны быть целыми числами. Выполнение ввести в ячейку Е формулу =В2+С2*D2 и скопировать ее маркером автозаполнения в ячейки ЕЕ ввести в ячейку G2 формулу =(Е2–В2)/F2 и скопировать ее маркером автозаполнения в ячейки G2: G6; ввести в ячейку В формулу =СРЗНАЧ(В2:В6) и скопировать ее маркером автозаполнения в ячейки С скопировать полученную таблицу на другой лист Excel; применить Поиск решения в соответствии с условиями заданий (каждое задание выполнять на своем листе. Задание 4. Смета расходов на реализацию производственных проектов приведена в таблице 18. Определить, каковы должны быть расходы на маркетинг и рекламу, чтобы общий бюджет проектов составил 10 000 000 руб. Производственные расходы всех проектов неизменны, расходы на маркетинг должны быть не менее 120 000 руб, а расходы на рекламу – менее 80 000 руб. на один проект. Кроме того, бюджет любого проекта не должен превышать суммы 1,2 млн руб. Таблица 18 – Смета расходов на реализацию проектов A B С D E 1 Номер проекта Расходы Стоимость проекта производство маркетинг реклама 2 1 850 150 3 2 751 800 4 3 579 650 5 4 845 600 6 5 900 130 7 6 681 250 8 7 753 140 9 8 951 750 10 9 654 320 11 10 369 850 Задание 5. Есть три емкости 3, 5 и 7 литров. Сих помощью нужно до краев наполнить бак емкостью 53 литра (не переполняя его и не оставляя воды в емкостях. Найти сколько раз нужно использовать каждую емкость, чтобы суммарное количество использования всех емкостей было минимальным. При этом каждую емкость нужно использовать не менее 2 раз. Контрольные вопросы 1. Для какого типа задач обычно используется надстройка Excel Поиск решения 2. Какой командой вызывается Поиск решения и что делать, если надстройка отсутствует на Ленте 3. С чего начинается решение любой оптимизационной задачи 48 2.5.9. Практическая работа № 9 Решение транспортной задачи с использованием надстройки Поиск решения Цель занятия изучение особенностей реализации алгоритма применения надстройки Поиск решения для решения задач оптимизации транспортных перевозок (транспортной задачи. Краткие теоретические сведения Одной из типичных задач, решаемых с помощью Поиска решения, является задача минимизации затратна перевозку продукции с нескольких складов (предприятий) нескольким потребителям. Задача считается сбалансированной, если сумма запасов продукции всех пунктов отправления равна суммарной потребности всех пунктов потребления. Если равенства нетто можно сбалансировать задачу, введя фиктивных потребителей или производителей. Предварительным этапом решения транспортной задачи равно как и любой другой задачи, решаемой с применением Поиска решения, является создание математической модели. Кроме транспортной, похожим образом Поиск решения применяется для решения Задачи о назначениях и некоторых других. Задания и порядок их выполнения Задание 1. Решить транспортную задачу по минимизации суммарных расходов на перевозку грузов с четырех предприятий пяти потребителям, если известны стоимости перевозки единицы груза (от каждого каждому, а также объемы производств и потреблений всех участников процесса (данные – в таблице 19, в ячейках серого цвета – стоимость перевозок единицы груза. Таблица 19 – Данные для решения транспортной задачи Предприятие Пункт потребления Объем производства 1 2 3 4 5 1 1,5 2 1,75 2,25 2,25 200 2 2,5 2 1,75 1 1,5 150 3 2 1,5 1,5 1,75 1,75 225 4 2 0,5 1,75 1,75 1,75 175 Объем потребления 100 200 50 250 150 = Выполнение составить математическую модель задачи, которая, в соответствии с условиями задачи, будет включать в себя следующие уравнения и неравенства (а) неизвестными величинами в задаче являются объемы перевозок, которые должны быть целыми и положительными целое ij x (б) суммарные расходы по перевозке равны сумме произведений стоимости перевозки единицы груза на объем перевозки и должны быть минимальными ; min 4 1 i 5 1 j ij x ij c ) x ( L (в) суммарный объем перевозок с каждого предприятия одному потребителю равен объему потребления потребителя (г) суммарный объем перевозок с одного предприятия всем потребителям равен объему производства предприятия ; 4 1 i 5 1 j , j b ij убедиться в сбалансированности задачи суммарный объем потребления суммарному объему производства ; 5 1 j j b 4 1 i заполнить лист книги Excel в соответствии с образцом (рис. 2): диапазоны ячеек В и G9:G12 заполняются при помощи маркера автозаполнения, после того как в ячейки В и G9 введены формулы ф =СУММ(В9:В12) и ф =СУММ(В9:F9) соответственно, формула ф =СУММПРОИЗВ(В3:F6;В9:F12) вводится в ячейку В выделить курсором целевую ячейку В выполнить команду Данные / Анализ / Поиск решения Рисунок 2 – Вид листа Excel для решения транспортной задачи в диалоговом окне Поиск решения заполнить поля в соответствии с условиями задачи и ее математической моделью (заполнять поля можно выделяя курсором ячейки или диапазоны ячеек Установить целевую ячейку В Равной минимальному значению Изменяя ячейки В Ограничения (заполняются после нажатия Добавить ♦ В >= 0 – условие (а 51 ♦ В = целое – условие (а ♦ $В$13:$F$13=$В$14:$F$14 – условие (в ♦ $G$9:$G$12=$H$9:$H$12 – условие (г. нажать кнопку Выполнить после завершения автоматического просчета и заполнения процессором ячеек В проверить правильность решения (должны выполниться все заданные ограничения, при этом в ячейках допускается наличие величин, отличающихся от целых наименее сохранить файл в рабочей папке. Задание 2. Решить задачу по минимизации суммарных расходов по транспортировке грузов си предприятий м потребителям данные – в таблице 20): Таблица 20 – Данные для транспортной задачи задания 2 Предприятия Пункты потребления Объем производства 1 2 3 4 1 5,5 1,5 7,5 6,5 30 2 1,5 4,5 8,5 1,5 40 3 4,5 6,5 3,5 3,5 10 4 2,5 3,5 1,5 4,5 20 5 3,5 7,5 9,5 1,5 10 Объем потребления 20 40 30 20 = Задание 3. Решить задачу наилучшего распределения рабочей силы (Задача о назначениях, то есть распределения с наименьшими расходами на оплату труда, если известны стоимости выполнения каждой работы каждым рабочим (таблица 21), и каждый рабочий может быть занят только на одной работе. Таблица 21 – Расценки выполнения работ для задания 3 Стоимости выполнения работы Рабочие 1 2 3 4 1 1,5 4,3 6,1 3,5 2 9,2 10,1 7,4 9,6 3 4,7 5,9 11,2 7,8 4 8,3 7,4 8,6 5,3 Выполнение составить математическую модель задачи 4 1 j , 4 1 i , 0 ij x ; целое ij x ; min 4 1 i 4 1 j ij x ij c ) x ( L ; 4 1 i 4 1 j , 1 ij x ; 4 1 i 4 1 j , 1 ij заполнить лист Excel данными (табл. 21) и формулами, в соответствии с математической моделью, а затем произвести Поиск решения, аналогично заданию 1; Задание 4. Решить задачу задания 3, если она несбалансиро- ванна, то есть количество работ будет на одну больше, чем работников табл. 22): Таблица 22 – Расценки выполнения работ для задания 4 Стоимости выполнения работы Рабочие 1 2 3 4 5 1 1,5 4,3 6,1 3,5 2,8 2 9,2 10,1 7,4 9,6 5,6 3 4,7 5,9 11,2 7,8 1,9 4 8,3 7,4 8,6 5,3 3,2 Выполнение добавить фиктивного пятого рабочего с расценками всех работ равными 0; далее решать сбалансированную задачу (5 5) аналогично заданиями (в ответе получится, что одна работа должна выполняться фиктивным рабочим, то есть останется не сделанной. Контрольные вопросы 1. Что такое транспортная задача 2. В чем заключается математическая модель транспортной задачи и как она воплощается на листе Excel? 3. Сбалансированность транспортной задачи и возможность ее решения при невыполнении этого условия. 53 2.5.10. Практическая работа № 10 Решение задач средствами MS Excel Цель занятия повторение и закрепление пройденного материала по теме Информационные технологии для работы с числовой информацией. Краткие теоретические сведения Для решения оптимизационных задач удобно использовать средства анализа MS Excel «Что-если». Средство Подбор параметра используется для обратного решения задачи – известно, какой результат должна возвращать формула, ноне известно, при каком значении входного параметра этот результат можно получить. Средство Подбор параметра как разрешает такую обратную задачу. Средство Поиск решения, как и Подбор параметра, также предназначено для решения обратных задач. Однако в отличие от Подбора параметра здесь не ограничено количество изменяемых исходных данных, и можно налагать ограничения на переменные решаемой задачи. Таблица данных позволяет с помощью одного оператора вычислить, отобразить и сравнить несколько результатов вычисления определенных формул для различных входных значений, от которых зависят эти формулы. Задания и порядок их выполнения Задание 1. Необходимо представить в виде таблицы суммы дивидендов по акциям, если их стоимость – 7 564 рубля, процентная ставка составляет 0,0132, а количество акций изменяется от 25 000 до 300 000 с шагом 25 000. Сумма дивидендов получается приумножении перечисленных величин друг на друга. Задание 2. Решить задачу из задания 1, если стоимость акций тоже изменяется от 7 500 до 10 000 руб. с шагом 500 руб. Задание 3. Определить длины сторон a, b и h прямоугольного бака объемом 2 куб. м, минимизируя длину сварного шва, которая определяется по формуле L = 2·(a + 2·b) + h. Объем бака находится как произведение его сторон. Задание 4. Решить задачу по минимизации суммарных расходов по транспортировке грузов с трех предприятий четырем потребителям (данные по себестоимости и объемам – в таблице 23). Таблица 23 – Данные для транспортной задачи задания 4 Предприятия Пункты потребления Объем производства 1 2 3 4 1 2 2 2,25 1,75 200 2 1,5 2 1,75 1,75 300 3 2 1,25 1 2,25 350 Объем потребления 225 275 250 100 = Задание 5. Решить Задачу о назначениях – задачу распределения работ по исполнителям с наименьшими расходами на оплату труда, если известны стоимости выполнения работ каждым рабочим табл. 24), и каждый рабочий занят только на одной работе. Таблица 24 – Данные для задачи о назначениях задания 5 Стоимости выполнения работы Рабочие 1 2 3 4 1 1,5 4,3 6,1 3,5 2 9,2 10,1 7,4 9,6 3 4,7 5,9 11,2 7,8 4 8,3 7,4 8,6 5,3 5 5,4 6,1 4,5 8,2 Задание 6. Для нормальной работы школы необходимо три уборщицы, три повара, завхоз, пять лаборантов, двадцать учителей, завуч, замдиректора, директор. Совет попечителей школы решил, что повар должен получать в 1,5 раза больше уборщицы завхоз – в 3 раза больше уборщицы учитель – на 30 $ больше, чем завхоз лаборант – в 2 раза больше уборщицы замдиректора на 40 $ больше повара завуч – в 4 раза больше уборщицы директор – на 20 $ больше завуча. Определить зарплаты сотрудников, если месячный фонд зарплаты школы составляет 8 000 $. Контрольные вопросы 1. Средства анализа «что-если» в Excel. 55 2.5.11. Практическая работа № 11 Примы работы с системой MathCAD. Простейшие вычисления. Переменные и функции Цель занятия изучение приемов работы с системой MathCAD, получение навыков простейших вычислений, знакомство с переменными и функциями. Краткие теоретические сведения Документ в системе MathCAD – это чистый лист бумаги, на котором можно размещать блоки трех основных типов математические выражения, текстовые фрагменты и графические области. Текстовая область – область, в которой пишется текст, применяемый для соответствующего оформления документа. Все выражения, набранные в этой области, воспринимаются системой как текст, и к ним не применяются математические действия. Для вставки текстовой области требуется выбрать соответствующую команду Вставить / Область текста. Форматирование текста производится при помощи меню Формат и (или) соответствующей панели, которая появляется при вставке области текста. Расположение нетекстовых блоков в документе имеет принципиальное значение – слева направо и сверху вниз именно в таком порядке система просматривает документ и производит вычисления. Математическая область – область, в которой выполняются все расчеты. Она создается автоматически при наборе любого выражения в документе MathCAD. Любое слово, набранное в данной области, воспринимается системой как переменная или функция, к которой следует применять различные математические операции. Курсор в математической области имеет вид синего уголка и показывает, своим охватом, к какой фрагменту уравнения будет применено действие. Для того чтобы расширить охват курсора, нужно нажать клавишу Пробел. При вставке функций или знаков математических действий в строке ввода появляется квадратик, называемый местозаполнителем. В MathCAD переменной может быть как буква, таки слово. Имя переменной может содержать цифры и знаки, но начинаться имя любой переменной должно только с буквы. Если для имени переменных требуются буквы греческого алфавита, то для их вставки следует воспользоваться кнопками панели Греческие буквы. Для того чтобы использовать переменную в расчетах, ей нужно присвоить значение при помощи оператора «:=» (двоеточие со знаком равно. Данный оператор набирается нес клавиатуры, а вставляется в документ нажатием кнопки «:=» на панели Арифметика. Переменной можно присвоить значение не только отдельным числом, но и целым математическим выражением. В этом случае система вычислит выражение, а полученный результат занесет в переменную. При вычислении MathCAD просматривает весь документ слева направо и сверху вниз. Поэтому вводить и присваивать значения переменных нужно выше или левее того места, где эти переменные используются. Функция – выражение, согласно которому проводятся вычисления с аргументами и определяется его числовое значение. Написание функций в MathCAD отличается от переменных наличием круглых скобок после имени. Между именем и скобками не должно быть ни пробелов, ни знаков. Скобки лучше вводить с клавиатуры. Если функция зависит от нескольких переменных, и они не заданы заранее, то при определении функции все их нужно указать в скобках, через запятую. Если какие-то переменные заданы ранее, то при определении функции их в скобках не указывают. Но, как ив случае с уравнениями, задавать переменные нужно выше и (или) левее определяемой функции. Расчет функции производится при помощи оператора «=», а значения не заданных заранее переменных указываются в скобках, в том же порядке, как и при определении функции. Переменными у функции могут быть и заранее заданные другие функции. Задания и порядок их выполнения Задание 1. Создать путем вставки области текста (Вставить / Область текста) заголовок практической работы. Шрифт – по умолчанию, размер шрифта – 14 пт, выравнивание и начертание – как в образце ПРАКТИЧЕСКАЯ РАБОТА № 7. Введение в математический пакет MathCAD. Выполнил студент гр. С [Ф.И.О.] Задание 2. Ввести и вычислить математические выражения ; 2 4 5 3 ) g ; 6 5 10 ) f ; 4 3 5 4 ) e ; 3 10 3 ) d ; 6 5 10 ) c ; 4 5 3 ) b ; 4 5 3 ) a ; 4 3 3 4 5 ) l ; 6 4 2 3 ) k ; 13 1 29 2 ) j ; 13 1 13 1 775 1 0 ) i ; 5 4 3 ) h ; 4 05 0 2 04 0 2 05 0 2 05 0 14 3 ) p ; ! 2 4 10 ) o ; ! 5 ) n ; 4 8 4 3 3 34 ) m ; 500 1 4 10 2 1000 0001 0 3 10 1 300 1 1 ) r ; 2 3 3 0 28 1 018 0 6 10 5 1 ) q ; 2 0 775 13 1 22 0 1 1 1 1 1 ) t ; 07 0 8 615 1 3 10 82 6 36 1 1 2 2 11 004 0 ) s ; 25 0 05 0 253 0 4 124 0 21 1 3 0 1 0 ) v ; 66 0 04 6 92 3 33 0 85 0 886 8 1 3844 0 1 1 ) u ; 4 ) 7654 8 5 log( ) x ; 5 0 85 0 2 0 615 1 3 10 82 6 1 1 1 17 75 16 ) w ) 1 ( ехр 1 10 1 5 0 ) 28 0 exp( 2 33 4 3 exp ) z ; 3 2 e 5 0 tan 3 2 cos ) y Выполнение для того чтобы рассчитать в документе MathCAD какое-либо математическое уравнение, нужно набрать его с клавиатуры или при помощи кнопок панели Арифметика, а затем нажать «=»; в десятичных дробях, целая и десятичная часть отделяются друг от друга точкой, а не запятой для ввода обычных дробей нужно сначала нажать знака потом ввести числитель и знаменатель MathCAD сам расставляет скобки там, где это необходимо, главное – правильно выбрать охват курсором также автоматически круглые скобки превращаются в квадратные при повторном использовании в выражении при вычислении факториала от сложных выражений сначала вводится выражение, потом оно целиком охватывается курсором и нажимается знак факториала на панели Арифметика на панели Арифметика есть всего пять стандартных тригонометрических и логарифмических функций, остальные вставляются командой Вставить / Функции при вставке функции log с панели Арифметика происходит вставка десятичного логарифма, чтобы превратить его в обычный нужно, поставив курсор внутри скобок, нажать запятую экспоненту в MathCAD можно рассчитать введя с клавиатуры «ехр( )» или вставкой операторах е с панели Арифметика. Задание 2. Присвоить значения переменным при помощи следующих выражений и вывести получившиеся результаты x r G : r G ; 43 3 y x : 2 _ es Pr ; 5 0 D : r G ; x : 0 D ; 66 0 04 6 92 3 33 0 85 0 886 8 1 3844 0 1 Выполнение для расчета результата нужно еще раз ввести имя переменной и вставить знак «=»; для ввода индекса переменной (например, 0 у 0 D ) нужно после ввода D нажать точку и только после этого вводить индекс переменные, зависящие от других переменных, должны располагаться ниже, чем они, или, если водной строке, то правее Задание 3. Рассчитать значение следующих функций а) ; 1 : D ; 4 : ; 4 : ; 3 : h ; 17 : n где 1 5 б) ; 2 : ; 05 0 : ; 2 : A ; 5 0 : ; 2 : y где в) 2 : n ; 5 : ; 3 : ; 1 : I ; 314 8 : R ; 15 где Выполнение все переменные, не входящие в скобки, должны быть заданы выше своих функций для вывода результата нужно ввести имя функции, вставив значения переменных в скобки, и, например 2 , 05 0 , 2 Fn =. Контрольные вопросы 1. Чем отличаются в MathCAD переменные и функции 2. Как ввести в имени переменной (функции) нижний индекс 3. Как задается переменная (функция) и как она вычисляется 4. Всели переменные нужно задавать выше введенной функции |