- •В. М. Паклина, е. М. Паклина Подготовка документов средствами Microsoft Office 2007
- •Содержание
- •Введение
- •Режимы курсора
- •Относительные и абсолютные адреса
- •Форматирование таблиц
- •Лабораторная работа № 1 Формулы, функции и диаграммы в процессоре Microsoft Office Excel 2007
- •Лабораторная работа №2 Адресация ячеек
- •Лабораторная работа №3 Функции счётесли() и суммесли()
- •Индивидуальные задания
- •Лабораторная работа № 4 Построение графиков функций
- •Индивидуальные задания
- •Лабораторная работа № 5 Сортировка, фильтры и промежуточные итоги
- •Лабораторная работа № 6 Сводные таблицы
- •Лабораторная работа № 7 Решение систем линейных уравнений
- •Индивидуальные задания
- •Лабораторная работа № 8 Решение задач оптимизации
- •Математическая модель задачи
- •Решение задачи в ms Excel
- •Отчёт по результатам.
- •Электронная таблица в режиме формул
- •Электронная таблица в режиме значений
- •Задания для самостоятельной работы: Задачи часть 1 (откройте файл задачи.Xls)
- •1. Решение задач на определение прибыли
- •2. Решение задач на определение структуры производства
- •3. Решение транспортных задач
- •4. Решение задач на определение состава смеси
- •Задачи часть 2 Самостоятельно решите следующие задачи
- •Индивидуальные задания
- •Лабораторная работа № 9 Макросы
- •Список литературы
Индивидуальные задания
Решите систему линейных уравнений:
а) методом Крамера;
b) с помощью обратной матрицы.
Сделайте проверку.
1 |
|
9 |
|
2 |
|
10 |
|
3 |
|
11 |
|
4 |
|
12 |
|
5 |
|
13 |
|
6 |
|
14 |
|
7 |
|
15 |
|
8 |
|
16 |
|
17 |
|
19 |
|
18 |
|
20 |
|
Лабораторная работа № 8 Решение задач оптимизации
Задачи оптимизации занимают очень важное место в бизнесе, производстве, прогнозировании. Условно эти задачи можно разделить на следующие категории:
транспортная задача – минимизация расходов на транспортировку товаров;
задача о назначениях – составление штатного расписания с минимизацией денежных затрат на заработную плату или времени выполнения работ;
задачи оптимизации производства – максимизация выпуска товаров при ограничениях на сырье для производства этих товаров.
Прежде, чем искать оптимальное решение задачи необходимо построить ее математическую модель, т.е. осуществить перевод условия и решения на четкий язык математических отношений.
Задача оптимизации в общем виде формулируется следующим образом.
Найти значения переменных x1, x2, … , xn, такие, что целевая функция f(x1, x2, … , xn) примет максимальное, минимальное или заданное значения при ограничениях вида g(x1, x2, … , xn).
Таким образом, задача оптимизации содержит три основных компонента:
переменныеx1, x2, … , xn– определяемые величины;
целевая функция– это цель, записанная математически в виде функции от переменных, принимающая максимальное, минимальное или заданное значения;
ограничения– условия или соотношения, которым должны удовлетворять переменные.
MSExcelпредоставляет возможность решения оптимизационных задач с помощью надстройкиПоиск решения. При этом после создания математической модели на рабочем листеExcelсоздается табличная модель, где в отдельных ячейках содержаться переменные решения, в отдельные ячейки записаны формулы, по которым будут вычисляться целевая функция и функции ограничений.
Продемонстрируем эту возможность на примере решения следующих задач:
Пример 1.Цех выпускает детали А и В. На производство детали А рабочий тратит 3 часа, на производство детали В - 2 часа. От реализации детали А предприятие получает прибыль 80 ден. ед., В - 60 ден. ед. Цех должен выпустить не менее 100 штук деталей А и не менее 200 штук деталей В. Сколько деталей каждого вида надо выпустить для получения наибольшей прибыли, если фонд рабочего времени составляет 900 человеко-часов.
Математическая модель задачи
Обозначим за x1 и x2 количество изделий А и В в оптимальном плане производства.
Решение задачи в ms Excel
Модели всех задач на оптимизацию состоят из следующих элементов:
1. Переменные - неизвестные величины, которые нужно найти при решении задачи.
2. Целевая функция - величина, которая зависит от переменных и является целью, ключевым показателем эффективности или оптимальности модели.
3. Ограничения - условия, которым должны удовлетворять переменные.
В качестве переменных х1 и х2 будем использовать ячейки E2 и E3 соответственно. Для значения целевой функции будем использовать ячейку E9:
Далее выбираем пункт меню Данные/Поиск решения:
Перед нами открывается диалоговое окно Поиск решения. В нём указываем, что нам необходимо установить ячейку $E$9 максимальному значению, изменяя ячейки $E$2:$E$3. Далее нажимаем кнопку Добавить для добавления ограничений. И добавляем следующие ограничения:
ограничения по фонду рабочего времени
ограничения по минимальному плану производства
количество изделий должно быть целым числом
После ввода каждого ограничения нажимаем кнопку Добавить. После ввода последнего ограничения нажимаем кнопку OK. И диалоговое окно Поиск решения принимает следующий вид:
Нажимаем кнопку Выполнить. И перед нами открывается диалоговое окно Результаты поиска решения:
Выбираем создание отчёта по результатам. Отчеты по устойчивости и пределам не создаются при использовании целочисленных ограничений на переменные. После нажатия кнопки OK в рабочей книге появляется новый лист с названием Отчет по результатам 1 содержащий отчёт по результатам, и получаем следующие результаты:
Деталь |
Затраты времени на производсво одной детали, ч. |
Прибыль от реализации одной детали, ден. ед. |
Минимальный план выпуска, штук |
Оптимальный план производства, штук | |||
А |
3 |
80 |
100 |
100 | |||
В |
2 |
60 |
200 |
300 | |||
|
|
|
|
| |||
Фонд рабочего времени, человеко-часов |
|
| |||||
составляет |
|
900 |
|
| |||
задействовано |
|
900 |
|
| |||
|
|
|
|
| |||
Максимальная прибыль от реализации, ден. ед. |
26000 |