1. В табличном процессоре ms Excel сформировать расчетную таблицу для следующего условия:
На участок строящейся дороги необходимо вывезти 20000 м3 каменных материалов. В районе строительства имеются три карьера с запасами 8000 м3, 9000 м3, 10000м3. Для погрузки материалов используются экскаваторы, имеющие производительность 250 м3 в смену в карьерах 1 и 2 и 500 м3 в смену в карьере 3. Эти карьеры обеспечивают каменными материалами также ряд других строящихся объектов. На погрузку материалов для рассматриваемого участка выделен для экскаваторов лимит 60 машино-смен с правом использования его по усмотрению строителей. Транспортные затраты на перевозку материалов характеризуются следующими показателями: для перевозки 10000 м3 материалов из карьера 1 требуется 1000 автомобиле-смен, из карьера 2 – 1350, из карьера 3 – 1700. Найти оптимальный план перевозок, обеспечивающий минимальные транспортные затраты.
2. С помощью функции «Поиск решения» решить задачу.
3. Сохраните выполненное задание.
4. Представьте результат решения на проверку преподавателю.
Методические указания
1. Откройте редактор электронных таблиц MS Excel . Сохраните новую рабочую книгу с именем Задание 5 .
2. Введите условие задачи по образцу
3. В ячейках B6:D6 введем предположительный объем вывозимого материала из карьеров
4. В ячейку D8 введем формулу для расчета общего объема вывоза материала.
5. В ячейке B9 вычислим затраты машино-смен на перевозку добываемого материала и скопируем эту формулу в ячейки С9 и D9
6. В ячейке D10 введем формулу для расчета общих расходов на транспорт.
7. В ячейку B12 введем формулу расчета количества машино-смен для перевозки добываемого материала.
8. В ячейку D13 введем формулу расчета общего количества машино-смен.
9. При помощи функции «Поиск решения» минимизируем транспортные расходы, и найдем количество добываемого материала в каждом карьере, для этого:
- выделим ячейку;
- в меню Сервис выполните команду Поиск решения;
- в открывшемся окне в поле Установить целевую ячейку указана ячейка, в которой находится целевая функция;
- установите переключатель в строку Равной минимальному значению;
- в поле Изменяя ячейки введите адреса ячеек, в которых указан предположительный объем вывозимого материала из карьеров;
- нажмите кнопку Добавить введем необходимые ограничения, указанные в условии задачи. Затем нажмите кнопку ОК;
- в открывшемся окне нажмите кнопку параметры и установите галочки в полях Линейная модель и Неотрицательные значения
- нажмите кнопку ОК, затем нажмите кнопку Выполнить и ОК.
10. Сохраните полученное решение.
Задание 6. Решение задачи по построению оптимального плана распределения работ
1. В табличном процессоре MS Excel сформировать расчетную таблицу для следующего условия:
Пять человек с номерами M1, М2, ..., М5 способны выполнить пять заданий с номерами Т1, Т2, ..., Т5. В силу разной квалификации на выполнение этих заданий им потребуется различное время. Как следует распределить людей по заданиям, чтобы минимизировать время выполнения? Время выполнения (в часах) приведено таблице.
2. С помощью функции «Поиск решения» решить задачу.
3. Сохраните выполненное задание.
4. Представьте результат решения на проверку преподавателю.
Методические указания
Откройте редактор MS Excel . Создайте таблицу по образцу
В первой таблице указаны время выполнения каждой работы определенным сотрудником, во второй таблице введены бинарные переменные, которые указывают, как распределены работы между сотрудниками. При помощи функции «Поиск решения» распределим работы так, чтобы суммарное время выполнения работ было минимальным.
Для проверки условия «один рабочий – одна работа» в ячейках B15:F15 и G10:G14 введем контрольные суммы.
Для нахождения времени выполнения распределенных работ выделим ячейку G16 и введем целевую функцию.
При помощи функции Поиск решения найдем способ распределения работ, так чтобы время выполнения этих работ было минимальным:
Выделим ячейку G16, в меню Сервис выполнить команду «Поиск решения». В открывшемся окне в поле установить целевую ячейку указана ячейка G16, установите переключатель в поле минимальному значению, в поле Изменяя ячейки указать диапазон B10:F14.
Добавим ограничения:
Нажмите кнопку Добавить. в открывшемся окне в поле Ссылки на ячейки указать диапазон ячеек B10:F14, в следующем списке выберем оператор сравнения двоичные. Нажмите кнопку Добавить.
Введем ограничение на контрольные суммы по строкам. Нажмите кнопку Добавить.
Введем ограничение на контрольные суммы по столбцам. Нажмите кнопку ОК.
В открывшемся окне нажмите кнопку Параметры. Установите галочку в поле «Линейная модель» . Нажмите кнопку ОК. Нажмите Кнопку Выполнить и кнопку ОК.
Сохраните выполненное задание.
Задание 7. Решение задачи по распределению участников олимпиады