Как сделать задачу в excel?
Пользователи Excel давно и успешно применяют программу для решения различных типов задач в разных областях.
Оптимизационные модели применяются в экономической и технической сфере. Их цель – подобрать сбалансированное решение, оптимальное в конкретных условиях (количество продаж для получения определенной выручки, лучшее меню, число рейсов и т.п.).
В 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» производить не стоит.
Грузы массой 20-128 дают в сумме 6904 кг. Максимальное
количество необязательных грузов – 102.
4) Попытаемся заменить верхний груз (128) на груз большей массы,
т.к. ещё осталось свободное место.
6982 – 6904 = 78 кг – можно добавить.
Про решение 26-го задания в Excel — презентация онлайн
- Делаем таблицу со значениями матрицы А.
- Выделяем на этом же листе область для обратной матрицы.
- Нажимаем кнопку «Вставить функцию». Категория – «Математические». Тип – «МОБР».
- В поле аргумента «Массив» вписываем диапазон матрицы А.
- Нажимаем одновременно Shift+Ctrl+Enter — это обязательное условие для ввода массивов.
Как вы знаете, прошлый год был объявлен годом русского языка, а кто знает, чему посвящен 2008 год? Это год семьи. Вот мы и будем сегодня играть в «дочки-матери». Представим, что вся ваша группа – большая семья Ивановых. В этой семье проживают: мама, папа, бабушка, дедушка, три сына (старший, средний и младший) и три дочери (старшая, средняя и младшая).
Линейное программирование в Excel
В Excel 2007 для включения пакета анализа надо нажать перейти в блок Параметры Excel, нажав кнопку в левом верхнем углу, а затем кнопку «Параметры Excel» внизу окна:
Далее в открывшемся списке нужно выбрать Надстройки, затем установить курсор на пункт Поиск решения, нажать кнопку Перейти и в следующем окне включить пакет анализа.
Рассмотрим подробно использование MS Excel на примере решения следующей задачи.
Фабрика «GRM pic» выпускает два вида каш для завтрака — «Crunchy» и «Chewy». Используемые для производства обоих продуктов ингредиенты в основном одинаковы и, как правило, не являются дефицитными. Основным ограничением, накладываемым на объем выпуска, является наличие фонда рабочего времени в каждом из трех цехов фабрики.
а) Сформулировать модель линейного программирования, максимизирующую общий доход фабрики за месяц.
Ввод исходных данных
Создание экранной формы и ввод исходных данных
Экранная форма для решения в MS Excel представлена на рисунке 1.
Ввод зависимостей из формальной постановки задачи в экранную форму
Для ввода зависимостей определяющих выражение для целевой функции и ограничений используется функция MS Excel СУММПРОИЗВ , которая вычисляет сумму попарных произведений двух или более массивов.
Одним из самых простых способов определения функций в MS Excel является использование режима «Вставка функций» , который можно вызвать из меню «Вставка» или при нажатии кнопки fx (рисунок 2) на стандартной панели инструментов.
Рисунок 2
Формулы, описывающие ограничения модели (1)
Левая часть ограничения | Формула Excel |
10x1+4x2 или B3×B13+C3×C13 | =СУММПРОИЗВ(B4:C4;B13:C13)) |
3x1+2x2 или B3×B14+C3×C14 | =СУММПРОИЗВ(B4:C4;B14:C14)) |
2x1+5x2 или B3×B15+C3×C15 | =СУММПРОИЗВ(B4:C4;B15:C15) |
Дальнейшие действия производятся в окне «Поиск решения» , которое вызывается из меню «Сервис» (рис.5):
· введите адрес целевой ячейки $D$6 или сделайте одно нажатие левой клавиши мыши на целевую ячейку в экранной форме ¾ это будет равносильно вводу адреса с клавиатуры;
· введите направление оптимизации ЦФ, щелкнув один раз левой клавишей мыши по селекторной кнопке «максимальному значению».
Ввод ограничений и граничных условий
Задание ячеек переменных
В окно «Поиск решения» в поле «Изменяя ячейки» впишите адреса $B$4:$С$4 . Необходимые адреса можно вносить в поле «Изменяя ячейки» и автоматически путем выделения мышью соответствующих ячеек переменных непосредственно в экранной форме.
Задание граничных условий для допустимых значений переменных
Окно «Поиск решения» после ввода всех необходимых данных задачи (1) представлено на рис. 5.
Если при вводе условия задачи возникает необходимость в изменении или удалении внесенных ограничений или граничных условий, то это делают, нажав кнопки «Изменить» или «Удалить» (см. рис. 5).
Решение задачи
Установка параметров решения задачи
Запуск задачи на решение
Запуск задачи на решение производится из окна «Поиск решения» путем нажатия кнопки «Выполнить» .
После запуска на решение задачи ЛП на экране появляется окно «Результаты поиска решения» с сообщением об успешном решении задачи, представленном на рис. 8.
Рис. 8 -. Сообщение об успешном решении задачи
4) Первые 100 товаров – самые дорогие, на них будет скидка. На
101 товар скидка не действует. Т.к. числа начинаются со 2-й
строчки, 101-й товар находится в строчке 102.
Его цена – 9000.
Примеры решения задач в excel
Одним из самых простых способов определения функций в MS Excel является использование режима «Вставка функций» , который можно вызвать из меню «Вставка» или при нажатии кнопки fx (рисунок 2) на стандартной панели инструментов.
Рисунок 2
Формулы, описывающие ограничения модели (1)
Левая часть ограничения | Формула Excel |
10x1+4x2 или B3×B13+C3×C13 | =СУММПРОИЗВ(B4:C4;B13:C13)) |
3x1+2x2 или B3×B14+C3×C14 | =СУММПРОИЗВ(B4:C4;B14:C14)) |
2x1+5x2 или B3×B15+C3×C15 | =СУММПРОИЗВ(B4:C4;B15:C15) |