Каким образом в ms excel задается направление оптимизации цф
Пользователи Excel давно и успешно применяют программу для решения различных типов задач в разных областях.
Excel – это самая популярная программа в каждом офисе во всем мире. Ее возможности позволяют быстро находить эффективные решения в самых разных сферах деятельности. Программа способна решать различного рода задачи: финансовые, экономические, математические, логические, оптимизационные и многие другие. Для наглядности мы каждое из выше описанных решение задач в Excel и примеры его выполнения.
Решение задач оптимизации в Excel
Оптимизационные модели применяются в экономической и технической сфере. Их цель – подобрать сбалансированное решение, оптимальное в конкретных условиях (количество продаж для получения определенной выручки, лучшее меню, число рейсов и т.п.).
В Excel для решения задач оптимизации используются следующие команды:
- Подбор параметров («Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра») – находит значения, которые обеспечат нужный результат.
- Поиск решения (надстройка Microsoft Excel; «Данные» - «Анализ») – рассчитывает оптимальную величину, учитывая переменные и ограничения. Перейдите по ссылке и узнайте как подключить настройку «Поиск решения».
- Диспетчер сценариев («Данные» - «Работа с данными» - «Анализ «что-если»» - «Диспетчер сценариев») – анализирует несколько вариантов исходных значений, создает и оценивает наборы сценариев.
Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».
Условие. Фирма производит несколько сортов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» - 250 рублей. «3» - 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.
Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:
На основании этих данных составим рабочую таблицу:
- Количество изделий нам пока неизвестно. Это переменные.
- В столбец «Прибыль» внесены формулы: =200*B11, =250*В12, =300*В13.
- Расход сырья ограничен (это ограничения). В ячейки внесены формулы: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») и =0*B11+8*B12+6*B13 («сахар»). То есть мы норму расхода умножили на количество.
- Цель – найти максимально возможную прибыль. Это ячейка С14.
Активизируем команду «Поиск решения» и вносим параметры.
После нажатия кнопки «Выполнить» программа выдает свое решение.
Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.
Решение финансовых задач в Excel
Чаще всего для этой цели применяются финансовые функции. Рассмотрим пример.
Условие. Рассчитать, какую сумму положить на вклад, чтобы через четыре года образовалось 400 000 рублей. Процентная ставка – 20% годовых. Проценты начисляются ежеквартально.
Оформим исходные данные в виде таблицы:
Так как процентная ставка не меняется в течение всего периода, используем функцию ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).
- Ставка – 20%/4, т.к. проценты начисляются ежеквартально.
- Кпер – 4*4 (общий срок вклада * число периодов начисления в год).
- Плт – 0. Ничего не пишем, т.к. депозит пополняться не будет.
- Тип – 0.
- БС – сумма, которую мы хотим получить в конце срока вклада.
Вкладчику необходимо вложить эти деньги, поэтому результат отрицательный.
Для проверки правильности решения воспользуемся формулой: ПС = БС / (1 + ставка) кпер . Подставим значения: ПС = 400 000 / (1 + 0,05) 16 = 183245.
Решение эконометрики в Excel
Для установления количественных и качественных взаимосвязей применяются математические и статистические методы и модели.
Дано 2 диапазона значений:
Значения Х будут играть роль факторного признака, Y – результативного. Задача – найти коэффициент корреляции.
Для решения этой задачи предусмотрена функция КОРРЕЛ (массив 1; массив 2).
Решение логических задач в Excel
В табличном процессоре есть встроенные логические функции. Любая из них должна содержать хотя бы один оператор сравнения, который определит отношение между элементами (=, >, =, Пример задачи. Ученики сдавали зачет. Каждый из них получил отметку. Если больше 4 баллов – зачет сдан. Менее – не сдан.
- Ставим курсор в ячейку С1. Нажимаем значок функций. Выбираем «ЕСЛИ».
- Заполняем аргументы. Логическое выражение – B1>=4. Это условие, при котором логическое значение – ИСТИНА.
- Если ИСТИНА – «Зачет сдал». ЛОЖЬ – «Зачет не сдал».
Решение математических задач в Excel
Средствами программы можно решать как простейшие математические задачки, так и более сложные (операции с функциями, матрицами, линейными уравнениями и т.п.).
Условие учебной задачи. Найти обратную матрицу В для матрицы А.
- Делаем таблицу со значениями матрицы А.
- Выделяем на этом же листе область для обратной матрицы.
- Нажимаем кнопку «Вставить функцию». Категория – «Математические». Тип – «МОБР».
- В поле аргумента «Массив» вписываем диапазон матрицы А.
- Нажимаем одновременно Shift+Ctrl+Enter - это обязательное условие для ввода массивов.
Возможности Excel не безграничны. Но множество задач программе «под силу». Тем более здесь не описаны возможности которые можно расширить с помощью макросов и пользовательских настроек.
СКАЧАТЬ: metody-optimizacii.rar [308,62 Kb] (cкачиваний: 116)
Лабораторная работа №1
"Решение задач линейного программирования с использованием Microsoft Excel”
ЦЕЛЬ РАБОТЫ: Приобретение навыков решения задач линейного программирования (ЛП) в табличном редакторе Microsoft Excel.
1 ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ
1.1 Использование Microsoft Excel для решения задач ЛП
Для того чтобы решить задачу ЛП в табличном редакторе Microsoft Excel, необходимо выполнить следующие действия.
1. Ввести условие задачи:
a) создать экранную форму для ввода условия задачи:
• переменных,
• целевой функции (ЦФ),
• ограничений,
• граничных условий;
b) ввести исходные данные в экранную форму:
• коэффициенты ЦФ,
• коэффициенты при переменных в ограничениях,
• правые части ограничений;
c) ввести зависимости из математической модели в экранную форму:
• формулу для расчета ЦФ,
• формулы для расчета значений левых частей ограничений;
d) задать ЦФ (в окне "Поиск решения"):
• целевую ячейку,
• направление оптимизации ЦФ;
e) ввести ограничения и граничные условия (в окне "Поиск решения"):
• ячейки со значениями переменных,
• граничные условия для допустимых значений переменных,
• соотношения между правыми и левыми частями ограничений.
2. Решить задачу:
a) установить параметры решения задачи (в окне "Поиск решения");
b) запустить задачу на решение (в окне "Поиск решения");
c) выбрать формат вывода решения (в окне "Результаты поиска решения").
1.2 Одноиндексные задачи ЛП
Рассмотрим пример нахождения решения для следующей одноиндексной задачи ЛП:
(1.1)
1.2.1. Ввод исходных данных
Создание экранной формы и ввод в нее условия задачи
Экранная форма для ввода условий задачи (1) вместе с введенными в нее исходными данными представлена на рисунке1.
Рисунок 1 – Экранная форма задачи
Дальнейшие действия производятся в окне "Поиск решения", которое вызывается из меню "Сервис" (рис. 2):
• поставьте курсор в поле "Установить целевую ячейку";
• введите адрес целевой ячейки $G$7 или сделайте одно нажатие левой клавиши мыши на целевую ячейку в экранной форме это будет равносильно вводу адреса с клавиатуры;
• введите направление оптимизации ЦФ, щелкнув один раз левой клавишей мыши по селекторной кнопке "максимальному значению".
Рисунок 2 – Окно "Поиск решения" задачи
Рисунок 3 – Экранная форма задачи после ввода всех необходимых формул
1.3 Целочисленное программирование
При целочисленном программировании мы получим те же результаты, так как в строке значение у нас уже целые коэффициенты.
1.4 Двухиндексные задачи ЛП
Двухиндексные задачи ЛП вводятся и решаются в Excel аналогично одноиндексным задачам. Специфика ввода условия двухиндексной задачи ЛП состоит лишь в удобстве матричного задания переменных задачи и коэффициентов ЦФ.
Рисунок 4 – Ограничения и граничные условия задачи
Рисунок 5 – Экранная форма после получения решения задачи
1.5 Задачи с булевыми переменными
Частным случаем задач с целочисленными переменными являются задачи, в результате решения которых искомые переменные могут принимать только одно из двух значений: 0 или 1. Такие переменные в честь предложившего их английского математика Джорджа Буля называют булевыми. На рис.6 представлена экранная форма с решением некоторой двухиндексной задачи с булевыми переменными.
Рисунок 6 – Решение двухиндексной задачи с булевыми переменными
В задаче с булевыми переменными в окне "Поиск решения" добавить граничные условия, имеющие смысл ограничения значений переменных по их единичной верхней границе.
Рисунок 7 – Добавление условия единичной верхней границы значений переменных двухиндексной задачи с булевыми переменными.
1. Каковы основные этапы решения задач ЛП в MS Excel?
Для того чтобы решить задачу ЛП в табличном редакторе MicrosoftExcel, необходимо выполнить следующие действия.
1. Ввести условие задачи:
a) создать экранную форму для ввода условия задачи:
• переменных,
• целевой функции (ЦФ),
• ограничений,
• граничных условий;
b) ввести исходные данные в экранную форму:
• коэффициенты ЦФ,
• коэффициенты при переменных в ограничениях,
• правые части ограничений;
c) ввести зависимости из математической модели в экранную форму:
• формулу для расчета ЦФ,
• формулы для расчета значений левых частей ограничений;
d) задать ЦФ (в окне "Поиск решения"):
• целевую ячейку,
• направление оптимизации ЦФ;
e) ввести ограничения и граничные условия (в окне "Поиск решения"):
• ячейки со значениями переменных,
• граничные условия для допустимых значений переменных,
• соотношениямежду правыми и левыми частями ограничений.
2. Решить задачу:
a) установить параметры решения задачи (в окне "Поиск решения");
b) запустить задачу на решение (в окне "Поиск решения");
c) выбрать формат вывода решения (в окне "Результаты поиска решения").
2. Каков вид и способы задания формул для целевой ячейки и ячеек левых частей ограничений?
Формулу для расчета ЦФ (1.2) можно записать как сумму произведений каждой из ячеек, отведенных для значений переменных задачи (B3, C3, D3, E3), на соответствующую ячейку, отведенную для коэффициентов ЦФ (B6, C6, D6, E6), то есть
=СУММПРОИЗВ(B$3:E$3;B6:E6)
Существует другой способ задания функций в Excel с помощью режима "Вставка функций", который можно вызвать из меню "Вставка" или при нажатии кнопки " "на стандартной панели инструментов. Так, например, формулу (1.4) можно задать следующим образом:
курсор в поле F6;
• нажав кнопку " ",вызовите окно"Мастер функций – шаг 1 из 2";
• выберите в окне "Категория" категорию "Математические";
• в окне "Функция" выберитефункцию СУММПРОИЗВ;
• в появившемся окне "СУММПРОИЗВ" в строку "Массив 1" введите выражение B$3:E$3, а в строку "Массив 2" – выражение B6:E6 (рис.1.3);
• после ввода ячеек в строки "Массив 1" и "Массив 2" в окне "СУММПРОИЗВ" появятся числовые значения введенных массивов (см. рис.1.3), а в экранной форме в ячейке F6 появится текущее значение, вычисленное по введенной формуле, то есть 0 (так как в момент ввода формулы значения переменных задачи нулевые).
Левые части ограничений задачи (1.1) представляют собой сумму произведений каждой из ячеек, отведенных для значений переменных задачи (B3, C3, D3, E3), на соответствующую ячейку, отведенную для коэффициентов конкретного ограничения (B10, C10, D10, E10 – 1-е ограничение; B11, C11, D11, E11 – 2-е ограничение и B12, C12, D12, E12 – 3-е ограничение).
3. В чем смысл использования символа $ в формулах MS Excel?
Символ $ перед номером строки 3 означает, что при копировании этой формулы в другие места листа Excel номер строки 3 не изменится.
4. В чем различие использования в формулах MS Excel символов ; и : ?
Символ: означает, что в формуле будут использованы все ячейки, расположенные между ячейками, указанными слева и справа от двоеточия. Символ;используется для разделения массивов.
5. Почему при вводе формул в ячейки ЦФ и левых частей ограничений в них отображаются нулевые значения?
В экранной форме в ячейке F6 появится текущее значение, вычисленное по введенной формуле, то есть 0 (так как в момент ввода формулы значения переменных задачи нулевые).
6. Каким образом в MS Excel задается направление оптимизации ЦФ?
В окне поиска решения.
7. Какие ячейки экранной формы выполняют иллюстративную функцию, а какие необходимы для решения задачи?
Ячейки, которые не используются для вычисления ограничений или ЦФ (например, заголовки или ячейки со знаками равенства) выполняют иллюстративную функцию.
12. Объясните смысл параметров, задаваемых в окне "Параметры поиска решения".
• Параметр "Максимальное время" служит для назначения времени (в секундах), выделяемого на решение задачи. В поле можно ввести время, не превышающее 32 767 секунд (более 9 часов).
• Параметр "Предельное числоитераций"служит для управления временем решения задачи путем ограничения числа промежуточных вычислений. В поле можно ввести количество итераций, не превышающее 32 767.
• Параметр "Относительная погрешность" служит для задания точности, с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 до 1. Чем меньше количество десятичных знаков во введенном числе, тем ниже точность. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации.
• Параметр "Допустимое отклонение" служит для задания допуска на отклонение от оптимального решения в целочисленных задачах. При указании большего допуска поиск решения заканчивается быстрее.
• Параметр "Сходимость" применяется только при решении нелинейных задач.
• Установка флажка "Линейная модель" обеспечивает ускорение поиска решения линейной задачи за счет применение симплекс-метода.
• Подтвердите установленные параметры нажатием кнопки "OK".
13. Каковы особенности решения в MS Excel целочисленных задач ЛП?
Добавилось требование целочисленности значений всех переменных. В этом случае в поиске решения добавляем требование целочисленности функции.
14. Каковы особенности решения в MS Excelдвухиндексных задач ЛП?
Двухиндексные задачи ЛП вводятся и решаются в Excel аналогично одноиндексным задачам. Специфика ввода условия двухиндексной задачи ЛП состоит лишь в удобстве матричного задания переменных задачи и коэффициентов ЦФ.
15. Каковы особенности решения в MS Excel задач ЛП с булевыми переменными?
Частным случаем задач с целочисленными переменными являются задачи, в результате решения которых искомые переменные могут принимать только одно из двух значений: 0 или 1. В окне "Поиск решения" добавить граничные условия, имеющие смысл ограничения значений переменных по их единичной верхней границе (рис.1.19).
b) ввести исходные данные в экранную форму:
· коэффициенты при переменных в ограничениях,
· правые части ограничений;
c) ввести зависимости из математической модели в экранную форму:
· формулу для расчета ЦФ,
· формулы для расчета значений левых частей ограничений;
d) задать ЦФ (в окне "Поиск решения"):
· направление оптимизации ЦФ;
e) ввести ограничения и граничные условия (в окне "Поиск решения"):
· ячейки со значениями переменных,
· граничные условия для допустимых значений переменных,
· соотношениямежду правыми и левыми частями ограничений.
2. Решить задачу:
a) установить параметры решения задачи (в окне "Поиск решения");
b) запустить задачу на решение (в окне "Поиск решения");
c) выбрать формат вывода решения (в окне "Результаты поиска решения").
2 Каков вид и способы задания формул для целевой ячейки и ячеек левых частей ограничений?
Зависимость для ЦФ
В ячейку F6, в которой будет отображаться значение ЦФ, необходимо ввести формулу, по которой это значение будет рассчитано. Согласно значение ЦФ определяется выражением
. | (1.2) |
Используя обозначения соответствующих ячеек в Exceл, формулу для расчета ЦФ можно записать как сумму произведений каждой из ячеек, отведенных для значений переменных задачи (B3, C3, D3, E3), на соответствующую ячейку, отведенную для коэффициентов ЦФ (B6, C6, D6, E6), то есть
. | (1.3) |
Чтобы задать формулу (1.3) необходимо в ячейку F6 ввести следующее выражение и нажать клавишу "Enter"
=СУММПРОИЗВ(B$3:E$3;B6:E6), | (1.4) |
где символ $ перед номером строки 3 означает, что при копировании этой формулы в другие места листа Excel номер строки 3 не изменится;
символ : означает, что в формуле будут использованы все ячейки, расположенные между ячейками, указанными слева и справа от двоеточия (например, запись B6:E6 указывает на ячейки B6, C6, D6 и E6). После этого в целевой ячейке появится 0 (нулевое значение) (рис.1.2).
3 В чем смысл использования символа $ в формулах MS Excel?
Символ $ перед номером строки 3 означает, что при копировании этой формулы в другие места листа Excel номер строки 3 не изменится;
В чем различие использования в формулах MS Excel символов ; и :?
Символ : означает, что в формуле будут использованы все ячейки, расположенные между ячейками, указанными слева и справа от двоеточия (например, запись B6:E6 указывает на ячейки B6, C6, D6 и E6).
Почему при вводе формул в ячейки ЦФ и левых частей ограничений в них отображаются нулевые значения?
Так как при вводе формулы в ячейках нулевые значения
Каким образом в MS Excel задается направление оптимизации ЦФ?
направление оптимизации ЦФ задается нажатием один раз левой клавишей мыши по селекторной кнопке "максимальному значению".
ПРИМЕРНЫЕ ВОПРОСЫ НА ЗАЩИТЕ РАБОТЫ
Каковы основные этапы решения задач ЛП в MS Excel?
1. Ввести условие задачи:
a) создать экранную форму для ввода условия задачи:
· целевой функции (ЦФ),
b) ввести исходные данные в экранную форму:
· коэффициенты при переменных в ограничениях,
· правые части ограничений;
c) ввести зависимости из математической модели в экранную форму:
· формулу для расчета ЦФ,
· формулы для расчета значений левых частей ограничений;
d) задать ЦФ (в окне "Поиск решения"):
· направление оптимизации ЦФ;
e) ввести ограничения и граничные условия (в окне "Поиск решения"):
· ячейки со значениями переменных,
· граничные условия для допустимых значений переменных,
· соотношениямежду правыми и левыми частями ограничений.
2. Решить задачу:
a) установить параметры решения задачи (в окне "Поиск решения");
b) запустить задачу на решение (в окне "Поиск решения");
c) выбрать формат вывода решения (в окне "Результаты поиска решения").
2 Каков вид и способы задания формул для целевой ячейки и ячеек левых частей ограничений?
Зависимость для ЦФ
В ячейку F6, в которой будет отображаться значение ЦФ, необходимо ввести формулу, по которой это значение будет рассчитано. Согласно значение ЦФ определяется выражением
. | (1.2) |
Используя обозначения соответствующих ячеек в Exceл, формулу для расчета ЦФ можно записать как сумму произведений каждой из ячеек, отведенных для значений переменных задачи (B3, C3, D3, E3), на соответствующую ячейку, отведенную для коэффициентов ЦФ (B6, C6, D6, E6), то есть
. | (1.3) |
Чтобы задать формулу (1.3) необходимо в ячейку F6 ввести следующее выражение и нажать клавишу "Enter"
=СУММПРОИЗВ(B$3:E$3;B6:E6), | (1.4) |
где символ $ перед номером строки 3 означает, что при копировании этой формулы в другие места листа Excel номер строки 3 не изменится;
символ : означает, что в формуле будут использованы все ячейки, расположенные между ячейками, указанными слева и справа от двоеточия (например, запись B6:E6 указывает на ячейки B6, C6, D6 и E6). После этого в целевой ячейке появится 0 (нулевое значение) (рис.1.2).
3 В чем смысл использования символа $ в формулах MS Excel?
Символ $ перед номером строки 3 означает, что при копировании этой формулы в другие места листа Excel номер строки 3 не изменится;
Поиск решений является частью блока задач, который иногда называют анализ "что-если" (Анализ «что-если». Процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул на листе, например, изменение процентной ставки, используемой в таблице амортизации для определения сумм платежей.). Процедура поиска решения позволяет найти оптимальное значение формулы (Формула. Совокупность значений, ссылок на другие ячейки, именованных объектов, функций и операторов, позволяющая получить новое значение. Формула всегда начинается со знака равенства (=).) содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения (Ограничения. Ограничения на значения изменяемых ячеек, конечных ячеек или других ячеек, прямо или косвенно связанных друг с другом, задаваемые при постановке задачи.). Эти ограничения могут ссылаться на другие влияющие ячейки.
Процедуру поиска решения можно использовать для определения значения влияющей ячейки, которое соответствует экстремуму зависимой ячейки — например можно изменить объем планируемого бюджета рекламы и увидеть, как это повлияет на проектируемую сумму расходов.
Цель занятия. Изучение технологии поиска решения для задач оптимизации (минимизации, максимизации).
Задание 1. Минимизация фонда заработной платы фирмы.
Пусть известно, что для нормальной работы фирмы требуется 5. 7 курьеров, 8. 10 младших менеджеров, 10 менеджеров, 3 заведующих отделами, главный бухгалтер, программист, системный аналитик, генеральный директор фирмы.
Общий месячный фонд зарплаты должен быть минимален. Необходимо определить, какими должны быть оклады сотрудников фирмы, при условии, что оклад курьера не должен быть меньше 1400 р.
В качестве модели решения этой задачи возьмем линейную модель. Тогда условие задачи имеет вид
N1* A1*x+N2* (А2* х + В2) + . . . + N8* ( А8*х+ В8) = Минимум,
где Ni — количество работников данной специальности;
х — зарплата курьера;
Аi и Вi — коэффициенты заработной платы сотрудников фирмы.
Порядок работы
1. Запустите редактор электронных таблиц Microsoft Excel и откройте созданный в предыдущей работе файл «Штатное расписание».
Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2».
2. В меню Сервис активизируйте команду Поиск решения (рис.1).
3. В окне Установить целевую ячейку укажите ячейку F14, содержащую модель — суммарный фонд заработной платы.
Поскольку необходимо минимизировать общий месячный фонд зарплаты, активизируйте кнопку равный — Минимальному значению.
В окне Изменяя ячейки укажите адреса ячеек, в которых будет отражено количество курьеров и младших менеджеров, а также зарплата курьера — $E$6:$E$7;$D$3 (при задании ячеек Е6, Е7 и D3 держите нажатой клавишу [Ctrl]).
Рис. 1. Задание условий для минимизации фонда заработной платы
Рис. 2. Добавление ограничений для минимизации фонда заработной
Используя кнопку Добавить в окнах Поиск решения и Добавление ограничений, опишите все ограничения задачи: количество курьеров изменяется от 5 до 7, младших менеджеров от 8 до 10, а зарплата курьера > 1400 (рис.2). Ограничения наберите в виде
Активизировав кнопку Параметры, введите параметры поиска, как показано на рис. 12.3.
Окончательный вид окна Поиск решения приведен на рис. 1.
Запустите процесс поиска решения нажатием кнопки Выполнить. В открывшемся диалоговом окне Результаты поиска решения задайте опцию Сохранить найденное решение (рис. 4).
Решение задачи приведено на рис. 5. Оно тривиально: чем меньше сотрудников и чем меньше их оклад, тем меньше месячный фонд заработной платы.
Рис.3. Задание параметров поиска решения по минимизации фонда
Рис. 4. Сохранение найденного при поиске решения
Рис..5. Минимизация фонда заработной платы
Сырье | Нормы расхода сырья | Запас сырья |
А | В | С |
Сырье1 | ||
Сырье 2 | ||
Сырье 3 | ||
Прибыль |
Задание.2. Составление плана выгодного производства.
Фирма производит несколько видов продукции из одного и того же сырья — А, В и С. Реализация продукции А дает прибыль 10 р., В — 15 р. и С — 20 р. на единицу изделия.
Продукцию можно производить в любых количествах, поскольку известно, что сбыт обеспечен, но ограничены запасы сырья. Необходимо определить, какой продукции и сколько надо произвести, чтобы общая прибыль от реализации была максимальной.
Нормы расхода сырья на производство продукции каждого вида приведены в табл.1.
Порядок работы
1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.
2. Создайте расчетную таблицу как на рис.6. Введите исходные данные и формулы в электронную таблицу. Расчетные формулы имеют такой вид:
Расход сырья 1 = (количество сырья 1) * (норма расхода сырья А) + (количество сырья 1) * (норма расхода сырья В) + (количество сырья 1) * (норма расхода сырья С).
Значит, в ячейку F5 нужно ввести формулу = В5 * $В$9 + С5 * $С$9 + D5 * $D$9.
Обратите внимание, что значения количества сырья каждого вида пока не известны и будут подобраны в процессе решения задания (ячейки B9:D9 пока пустые).
(Общая прибыль по А) = (прибыль на ед. изделий А) * (количество А), следовательно в ячейку В10 следует ввести формулу = В8 * В9.
Итоговая общая прибыль = (Общая прибыль по А) + (Общая прибыль по В) + (Общая прибыль по С), значит в ячейку E10 следует ввести формулу = СУММ(В10:D10).
Рис. 6. Исходные данные для Задания 2
3. В меню Сервис активизируйте команду Поиск решения и введите параметры поиска, как указано на рис. 7.
В качестве целевой ячейки укажите ячейку «Итоговая общая прибыль» (E10), в качестве изменяемых ячеек — ячейки количества сырья — (B9:D9).
Не забудьте задать максимальное значение суммарной прибыли и указать ограничения на запас сырья:
а также положительные значения количества сырья А, В, С> = 0.
Установите параметры поиска решения (рис.8). Для этого кнопкой Параметры откройте диалоговое окно Параметры поиска решения, установите параметры по образцу, задайте линейную модель расчета (Линейность модели).
Рис.7. Задание условий и ограничений для поиска решений
4. Кнопкой Выполнить запустите Поиск решения. Если вы сделали все верно, то решение будет как на рис. 9.
Рис..8. Задание параметров поиска решения
Рис. 9. Найденное решение максимизации прибыли при заданных ограничениях
5. Сохраните созданный документ под именем «План производства».
Выводы. Из решения видно, что оптимальный план выпуска предусматривает изготовление 5,56 кг продукции В и 22,22 кг продукции С. Продукцию А производить не стоит. Полученная прибыль при этом составит 527,78 р.
ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ
Используя файл «План производства» (см. задание 2), определить план выгодного производства, т.е. какой продукции и сколько необходимо произвести, чтобы общая прибыль от реализации была максимальной.
Выберите нормы расхода сырья на производство продукции каждого вида и ограничения по запасам сырья из таблицы соответствующего варианта (5 вариантов):
Основной целью экономики является рациональное функционирование хозяйствующих субъектов или, иначе говоря, оптимальная деятельность при ограниченных ресурсах. Поэтому в экономической области существует широкий класс задач оптимизации, или, как их еще называют, экстремальных задач. В задачах оптимизации вычисляются значения параметров некоторой функции
при которых она принимает экстремальное значение (максимальное или минимальное) и при условии, что на эти параметры наложены ограничения.
Эту функцию называют целевой функцией, а набор количественных значений между переменными, выражающих определенные требования к параметрам экономической задачи в виде уравнений или неравенств называют системой ограничений.
Совокупность соотношений, содержащих целевую функцию и ограничения на ее аргументы, называют математической моделью экономической задачи оптимизации.
Если целевая функция линейна и на ее аргументы наложены линейные ограничения, то такую задачу оптимизации называют задачей линейного программирования.
Существуют различные методы решения задач линейного программирования. В MS Excel для этой цели предназначен инструмент Поиск решения. Принцип его работы заключается в использовании итерационного способа подбора параметров целевой функции в сочетании с градиентным методом. Применение этого инструмента позволяет решать задачи оптимизации с высокой точностью. Технологическая последовательность решения задачи включает следующие шаги:
1. На основе постановки задачи и уяснения ее экономической сути, разрабатывается математическая модель, аналитически представляющая целевую функцию и функции ограничений.
2. Ввод исходных данных и формул, реализующих математическую модель в электронную таблицу.
3. Настройка параметров инструмента Поиск решения и его применение для решения задачи.
Оптимальный план выпуска продукции
Покажем последовательность решения задачи линейного программирования на простом примере.
Пример . Фирма производит два вида мороженого: сливочное и шоколадное. Для изготовления мороженого используются два исходных продукта: молоко и наполнители, расходы которых на 1 кг готового продукта и их суточные запасы приведены в таблице.
Суточный спрос на сливочное мороженое превышает спрос на шоколадное не более чем на 100 кг. Кроме того известно, что спрос на шоколадное мороженое не превышает 350 кг в сутки. Отпускная цена 1 кг сливочного мороженого 16 ден. ед., шоколадного – 14 ден. ед.
Требуется определить в каком количестве мороженого каждого вида должна производить фирма, чтобы доход от реализации продукции был максимальным.
Шаг 1 – разработка математической модели
Введем обозначения: x1 – суточный объем производства сливочного мороженого, х2 - суточный объем производства шоколадного мороженого. Исходя из условия задачи целевая функция будет иметь вид
Читайте также: