Задачи в Эксель Примеры с Решением Информатика • Построение графика

Как сделать задачу в excel?

Пользователи Excel давно и успешно применяют программу для решения различных типов задач в разных областях.

Оптимизационные модели применяются в экономической и технической сфере. Их цель – подобрать сбалансированное решение, оптимальное в конкретных условиях (количество продаж для получения определенной выручки, лучшее меню, число рейсов и т.п.).

В Excel для решения задач оптимизации используются следующие команды:

Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».

Условие. Фирма производит несколько сортов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» — 250 рублей. «3» — 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.

Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:

  1. Количество изделий нам пока неизвестно. Это переменные.
  2. В столбец «Прибыль» внесены формулы: =200*B11, =250*В12, =300*В13.
  3. Расход сырья ограничен (это ограничения). В ячейки внесены формулы: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») и =0*B11+8*B12+6*B13 («сахар»). То есть мы норму расхода умножили на количество.
  4. Цель – найти максимально возможную прибыль. Это ячейка С14.

Активизируем команду «Поиск решения» и вносим параметры.

После нажатия кнопки «Выполнить» программа выдает свое решение.

Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
2 САМОСТОЯТЕЛЬНО Пример входного файла 10 2 34 50 43 44 23 9 39 5 38 36 При таких исходных данных ответ должен содержать 2 числа 43 и 35. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
6: РАЗБОР
Грузы массой 20-128 дают в сумме 6904 кг. Максимальное
количество необязательных грузов – 102.
4) Попытаемся заменить верхний груз (128) на груз большей массы,
т.к. ещё осталось свободное место.
6982 – 6904 = 78 кг – можно добавить.
Презентация Решение расчетных задач в MS Excel

Про решение 26-го задания в Excel — презентация онлайн

  1. Делаем таблицу со значениями матрицы А.
  2. Выделяем на этом же листе область для обратной матрицы.
  3. Нажимаем кнопку «Вставить функцию». Категория – «Математические». Тип – «МОБР».
  4. В поле аргумента «Массив» вписываем диапазон матрицы А.
  5. Нажимаем одновременно 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 -. Сообщение об успешном решении задачи

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Выделяем ячейки C2С11 и левой кнопкой мыши щёлкаем по пиктограмме , определяем современную стоимость сумм, полученных по купонным выплатам ячейкаC12. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
1: РАЗБОР
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)
Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: