Letysite.ru

IT Новости с интернет пространства
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Решить задачу оптимизации в excel

Решение задач оптимизации в помощью электронных таблиц Excel

Как организовать дистанционное обучение во время карантина?

Помогает проект «Инфоурок»

Решение задач оптимизации с помощью электронных таблиц Excel

Учитель информатики и ИКТ

Кабанова Татьяна Витальевна

Приморского района Санкт-Петербурга

Тема. Решение задач оптимизации в Excel .

Тип урока: обобщение и систематизация знаний.

образовательные — обобщение и систематизация знаний по теме «Обработка числовой информации»

закрепление знаний об общих принципах работы табличного процессора Microsoft Excel;

практическое применение изученного материала, приобретение навыков в составлении таблиц разного типа;

развитие умения выбирать наиболее оптимальную структуру таблицы, создать и оформить таблицу;

формирование представления о вычислениях в электронной таблице как наиболее важных в изучении информатики и широко применяемых на практике.

развивающие — продолжить развитие логического мышления, глубины и гибкости ума.

развитие познавательного интереса, речи и внимания учащихся;

развитие способности логически рассуждать;

формирование информационной культуры и потребности приобретения знаний;

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

развивать у школьников теоретическое мышление, формирование операционного мышления, направленного на выбор оптимальных решений.

воспитательные — продолжать воспитывать информационную культуру, общечеловеческие качества личности школьника

воспитание творческого подхода к работе, желания экспериментировать;

воспитание трудолюбия, чувства уважения к науке;

продолжить воспитывать культуру общения;

продолжить формировать чувство долга, настойчивости, дисциплинированность; продолжить формирование творческих, исследовательских качеств учащихся;

продолжить воспитывать эстетический вкус.

Организационный этап. (Цель: настроить учащихся на работу на уроке)

Сегодня мы продолжаем изучение темы «Обработка числовой информации». На предыдущих занятиях мы узнали основные информационные единицы электронной таблицы, типы и форматы данных, используемых в Excel , основные функций, используемых при записи формул, общие правила подготовки электронной таблицы, а так же графические возможности табличного процессора, учились решать задачи с использованием ЭТ.

Сегодня цель нашего урока: закрепить и обобщить знания, полученные по этой теме, развивать умение применять полученные знания для решения задач из различных предметных областей.

II . Актуализация знаний и фронтальный опрос

– Итак, что мы с вами знаем и умеем делать: создавать редактировать и формировать табличный документ, выполнять вычисления по формулам, применять относительные и абсолютные ссылки, использовать функции. Давайте вспомним и повторим то, что нам уже знакомо.

Вопросы

Ответ

Как называется документ, созданный в электронных таблицах?

Что является основным элементом электронной таблицы?

Что не может включать в себя формула в электронных таблицах?

Как записывают в формуле адрес ячейки, если необходимо отменить принцип относительной адресации при переносе формулы?

Перед именем столбца и номером строки ставится символ $

Какой формат числа вы примените для отображения:

Времени начала уроков?

Дней рождения знакомых?

Порядковых номеров в списке?

Каким будет результат вычислений в ячейке С1?

Какие виды адресации ячеек вы знаете?

Относительная, абсолютная, смешанная.

В каких случаях необходимо использовать абсолютные адреса ячеек в формулах?

Для указания фиксированного адреса ячейки.

В ячейке электронной таблицы С5 записана формула =B5*А5. Какая формула будет получена из нее при копировании в ячейку С6?

Дан фрагмент электронной таблицы:

Значение ячейки С1 вычисляется по формуле = В1+ $A$1. Чему будет равно после копирования формулы значение в ячейке С3?

Перечислите области деятельности человека, к которым можно отнести использование возможностей табличного процессора MS Excel ?

Это – наука, производство, бухгалтерия, торговля, статистика, экология

Таким образом, можно сделать вывод: области применения электронных таблиц очень разнообразны, без них не может обойтись практически ни один современный специалист.

III. Изложение нового материала

Человек, совершая те или иные деяния, стремился вести себя таким образом, чтобы результат, достигаемый как следствие некоторого поступка, оказался в определенном смысле наилучшим. Двигаясь из одного пункта в другой, он стремился найти кратчайший среди возможных путь. Строя жилище, он искал такую его геометрию, которая при наименьшем расходе топлива, обеспечивала приемлемо комфортные условия существования. Занимаясь строительством кораблей, он пытался придать им такую форму, при которой вода оказывала бы наименьшее сопротивление. Можно легко продолжить перечень подобных примеров.

Задачи на отыскание оптимального решения называются задачами оптимизации. Применяемые в процессе оптимизации методы получили название методов оптимизации. При постановке и решении задач оптимизации возникают два вопроса: что и как оптимизировать?

Ответ на первый вопрос получается как результат глубокого изучения проблемы, которую предстоит решить. Выявляется тот параметр, который определяет степень совершенства решения возникшей проблемы. Этот параметр обычно называют целевой функцией или критерием качества. Далее устанавливается совокупность величин, которые определяют целевую функцию. Наконец, формулируются все ограничения, которые должны учитываться при решении задачи. После этого строится математическая модель, заключающаяся в установлении аналитической зависимости целевой функции от всех аргументов и аналитической формулировки сопутствующих задаче ограничений. Далее приступают к поиску ответа на второй вопрос.

Понятие «наилучший, оптимальный» может быть выражено количественными критериями – минимум затрат, минимум времени, максимум прибыли и т.д. Для решения таких задач в ЭТ используется надстройка Поиск решения.

При решении задач оптимизации с помощью MS Excel применяют алгоритм:

Технология решения задач оптимизации в Excel

Постановка задачи оптимизации

Основной целью экономики является рациональное функционирование хозяйствующих субъектов или, иначе говоря, оптимальная деятельность при ограниченных ресурсах. Поэтому в экономической области существует широкий класс задач оптимизации, или, как их еще называют, экстремальных задач. В задачах оптимизации вычисляются значения параметров некоторой функции

при которых она принимает экстремальное значение (максимальное или минимальное) и при условии, что на эти параметры наложены ограничения.
Эту функцию называют целевой функцией, а набор количественных значений между переменными, выражающих определенные требования к параметрам экономической задачи в виде уравнений или неравенств называют системой ограничений.
Совокупность соотношений, содержащих целевую функцию и ограничения на ее аргументы, называют математической моделью экономической задачи оптимизации.
Если целевая функция линейна и на ее аргументы наложены линейные ограничения, то такую задачу оптимизации называют задачей линейного программирования.
Существуют различные методы решения задач линейного программирования. В MS Excel для этой цели предназначен инструмент Поиск решения. Принцип его работы заключается в использовании итерационного способа подбора параметров целевой функции в сочетании с градиентным методом. Применение этого инструмента позволяет решать задачи оптимизации с высокой точностью. Технологическая последовательность решения задачи включает следующие шаги:

1. На основе постановки задачи и уяснения ее экономической сути, разрабатывается математическая модель, аналитически представляющая целевую функцию и функции ограничений.

2. Ввод исходных данных и формул, реализующих математическую модель в электронную таблицу.

3. Настройка параметров инструмента Поиск решения и его применение для решения задачи.

Оптимальный план выпуска продукции

Покажем последовательность решения задачи линейного программирования на простом примере.

Пример . Фирма производит два вида мороженого: сливочное и шоколадное. Для изготовления мороженого используются два исходных продукта: молоко и наполнители, расходы которых на 1 кг готового продукта и их суточные запасы приведены в таблице.

Суточный спрос на сливочное мороженое превышает спрос на шоколадное не более чем на 100 кг. Кроме того известно, что спрос на шоколадное мороженое не превышает 350 кг в сутки. Отпускная цена 1 кг сливочного мороженого 16 ден. ед., шоколадного – 14 ден. ед.

Читать еще:  Утилиты для оптимизации андроид

Требуется определить в каком количестве мороженого каждого вида должна производить фирма, чтобы доход от реализации продукции был максимальным.

Шаг 1 – разработка математической модели

Введем обозначения: x1 – суточный объем производства сливочного мороженого, х2 — суточный объем производства шоколадного мороженого. Исходя из условия задачи целевая функция будет иметь вид

Решение оптимизационных задач в excel.

Для решения задач оптимизации широкое променение находят различные средства Excel.

Основной командой для решения оптимизационных задач в Excel является команда Сервис/Подбор параметра. Эта команда определяет неизвестную величину, приводящую к требуемому результату.

Если команда Подбор параметра отсутствует в меню Сервис, выполните команду Сервис/Надстройка и установите флажок Пакет анализа в окне диалога Надстройка

Для работы с командой Подбор параметра необходимо подготовить лист, чтобы в листе находились:

формула для расчета;пустая ячейка для искомого значения; другие величины, которые используются в формуле.

Ссылка на пустую ячейку должна обязательно присутствовать в формуле, так как именно она является переменной, значение которой ищет Excel. Во время подбора параметра в переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи.

Такой процесс называется итерацией, и продолжается он до тех пор, пока редактор не выполнит 100 попыток или не найдет решения, лежащее в пределах точности 0,001 от точного значения (настройка этих параметров осуществляется с помощью команды Сервис/Параметры, вкладка Вычисления)

Оптимизация с помощью команды Подбор параметров выполняется так:

1. Создайте лист, например, с формулой =B1*B2 в ячейке B3, пустой (переменной) ячейкой (B2) и другими данными (B1), которые могут понадобиться при вычислениях. Например, необходимо определить количество книг по цене 23,75 грн., которые необходимо продать, чтобы объем продаж составил 10000,00 грн.

2. Выделите ячейку листа (B3), в которой содержится формула (эта ячейка появится в поле Установить в ячейке в окне диалога Подбор параметра). Выполните команду Сервис/Подбор параметра. Открывается окно диалога Подбор параметра..

3. Введите в текстовое поле Значение число, соответствующее объему продаж — 10000. Переместите курсор в текстовом поле Изменяя значения ячейки. Выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра. Выделенная ячейка (B2) выделяется на листе рамкой. Нажмите кнопку ОК, чтобы найти решение.

После завершения итерационного цикла в окне диалога Результат подбора параметра появляется сообщение, а результат заносится в ячейку листа. Решение показывает, что для достижения объема продаж 10000 грн. необходимо продать 421 книгу по цене 23,75 грн. Для закрытия окна диалога Результат подбора параметра щелкните на кнопке ОК.

Команда Поиск решения

Для решения сложных задач, требующих применения линейного и нелинейного программирования, а также методов исследования операций применяется надстройка — Поиск решения. Чтобы использовать надстройку Поиск решения не обязательно знать методы программирования и исследования операций, но необходимо определять, какие задачи можно решать этими методами.

Пользователь должен уметь с помощью диалоговых окон надстройки Поиск решения правильно сформулировать условия задачи, и если решение существует, то “Поиск решения” отыщет его. В основе надстройки лежат итерационные методы.

В том случае, когда оптимизационная задача содержит несколько переменных величин, для анализа сценария необходимо воспользоваться надстройкой Поиск решения. “Поиск решения” позволяет использовать одновременно большое количество изменяемых ячеек (до 200) и задавать ограничения для изменяемых ячеек.

Общие свойства, которые характерны для задач, решаемых с помощью надстройки Поиск решения:

Существует единственная целевая ячейка, содержащая формулу, значение которой должно быть сделано максимальным, минимальным или же равным, какому-то конкретному значению.

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

Может быть задано некоторое количество ограничений — условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек.

Первым шагом при работе с командой Поиск решения является создание специализированного листа. Для этого необходимо создать целевую ячейку, в которую вводится основная формула.

Кроме того, лист может включать другие значения и формулы, использующие значения целевой и переменных ячеек. Формула в целевой ячейке должна опираться в вычислениях на значения переменных ячеек.

После того, как задача оптимизации будет подготовлена на листе, можно приступать к работе.

1. Выделите на листе целевую ячейку, в которую введена формула.

2. Выполните команду Сервис/Поиск решения. Открывается окно диалога Поиск решения. Поскольку была выделена ячейка, в текстовом поле «Установить целевую ячейку» появится правильная ссылка на ячейку. В группе «Равной» переключатель по умолчанию устанавливается в положение «Максимальному значению».

3. Перейдите к полю Изменяя ячейки и введите переменные ячейки листа

4. Добавьте ограничения на переменные в изменяемых ячейках. Для ввода ограничений нажмите кнопку Добавить, чтобы задать первое ограничение в окне диалога, затем можно ввести второе, третье и т.д.

5. Когда оптимизационная задача будет готова к выполнению, можно нажать кнопку Выполнить для получения ответа. Появится окно диалога с описанием результатов процесса оптимизации.

6. Чтобы отобразить найденное решение в ячейках листа, установите переключатель Сохранить найденное решение и нажмите кнопку ОК. Найденная максимальная величина помещается в целевую ячейку, а переменные ячейки заполняются оптимальными значениями переменных, которые удовлетворяют установленным ограничениям.

Диспетчер сценариев «что – если»

При работе с командами Подбор параметра и Поиск решения не существует удобного способа сравнения результатов вычислений – при каждом изменении данных предыдущее значение пропадает.

Чтобы устранить эти ограничения, разработчики Excel создали Диспетчер сценариев, помогающий работать с несколькими моделями «что – если». Командой Сервис/Сценарии можно создавать новые и просматривать существующие сценарии для решения задач, и отображать консолидированные отчеты.

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

Создание сценариев происходит следующим образом:

Выполните команду Сервис/Сценарии. Открывается изображение окна диалога Диспетчер сценариев.

Нажмите кнопку Добавить, чтобы создать первый сценарий. Откроется окно диалога Добавление сценария.

Введите Лучший вариант (или любое другое имя) в поле Название сценария, затем с помощью окон диалога введите изменяемые ячейки. Когда этот сценарий будет готов, введите следующий.

Нажмите кнопку Добавить, чтобы создать второй сценарий. Введите название Худший вариант. После завершения создания двух сценарием можно приступить к просмотру результатов.

Закройте окно диалога Диспетчер сценариев кнопкой Закрыть.

Excel сохраняет сценарии вместе с листом текущей книги, и просмотр их командой Сервис /Сценарии возможен только при открытии данного листа. Просмотр сценария выполняется следующим образом:

Выполните команду Сервис/Сценарии. Открывается окно диалога:

Выберите из списка сценарий для просмотра.

Нажмите кнопку Вывести. Excel заменяет содержимое ячеек листа значениями из сценария и отображает результаты на листе.

Выберите из списка другие сценарии и воспользуйтесь кнопкой Вывести для сравнения результатов моделей «что – если». После завершения нажмите кнопку Закрыть. Значения последнего активного сценария остаются в ячейках листа.

Читать еще:  Apache скачать linux

Создание отчетов по сценарию

Сравнивать различные сценарии можно, переходя от сценария к сценарию с помощью кнопки показать в окне диалога Диспетчер сценариев, но иногда возникает необходимость в создании отчета с обобщенной информацией о различных сценариях листа.

Эту задачу можно выполнить с помощью кнопки Отчет в окне диалога Диспетчер сценариев. Созданный сводный отчет будет автоматически отформатирован и скопирован на новый лист текущей книги.

Создание отчета по сценарию происходит следующим образом:

Выполните команду Сервис/Сценарии. Откроется окно диалога Диспетчер сценариев.

Нажмите кнопку Отчет. Открывается окно диалога Отчет по сценарию, в котором предлагается выбрать ячейки, входящие в отчет, а также его тип. Отчет типа структура представляет собой форматированную таблицу, которая выводится на отдельном листе. Отчет сводная таблица является специальной таблицей, которую можно настраивать за счет перестановки столбцов и строк.

Статьи к прочтению:

Excel. Задачи оптимизации. Часть 1

Похожие статьи:

Цель работы: познакомиться с приемами решения задач линейной алгебры. Типичными задачами линейной алгебры являются задачи, связанные с решением систем…

Задача 155. Для решения этой задачи достаточно понимания материала листа определений. Если кто-то из слабых детей запутался, можно предложить…

Оптимизация смен рабочих с помощью Поиска решений в EXCEL

Решим задачу об оптимизации смен рабочих с помощью Поиска решений MS EXCEL 2010. В качестве примера разберем задачу из сборника «Методы оптимизации управления и принятия решений» авторы Зайцев М.Г. и Варюхин С.Е. (2008г.). Задача 2.43 «Проблема мастера».

Сборник задач «Методы оптимизации управления и принятия решений» довольно популярен в качестве задачника для Вузов, поэтому ссылки на него встречаются повсеместно в интернете. Приятно, что для каждой задачи имеется ответ в конце книги — можно сравнить получившееся решение с решением, которое найдено авторами с помощью Поиска решения (в году так 2000-м, я полагаю).

Условия задачи (оригинальные)

Мастер должен выбрать несколько фрезеровщиков из 10 (Р1,Р2..Р10) для изготовления 8 видов деталей (Д1,Д2. Д8) для партии продукции:

так, чтобы сократить общие затраты рабочего времени. Среднее количество деталей, которое каждый рабочий может обработать за смену, дано в таблице:

а. Определить оптимальное распределение рабочих по операциям, принимая во внимание, что рабочие Р5 и Р9 не могут изготавливать детали Д5 и Д2 соответственно.b. Каков самый короткий срок выполнения этого заказа?

Разбор условия задачи

Скажу честно, но условия задачи не кажутся мне четко сформулированными. Вот мои комментарии:

1) Рабочих называют сначала фрезеровщиками, затем просто рабочими. Зачем?

2) Используется понятие «операции», то есть подразумевается некая определенная последовательность действий. То есть можно подумать, что сначала делается операция Д1 (изготовление детали 1), затем Д2 и т.д. Чтобы выяснить это, обратимся к ответу:

То есть нет никаких последовательных операций, есть просто 8 типов деталей, которые нужно изготовить. Причем один рабочий изготавливает за смену или дольше только один вид деталей, затем, если есть необходимость, то переходит к другому типу. Понятие «операции» не помогает студенту понять условие, а лишь его запутывает.

3) В ответе целая и дробные части смены отделены точкой, но как известно, в России разделителем является запятая. Кроме того, очевидно, что дробная часть смены приведет к дробному количеству изготовленных деталей (это как то непонятно). Как следствие, в задаче планируется складывать 63,111 и 48,888 деталей, чтобы выполнить заказ. Что не очень логично.

4) В условии используется » среднее количество деталей, которое каждый рабочий может обработать за смену». Как будто, эту задачу планируется решать статистическими методами с использованием некой функции распределения.

5) Зачем сказано, что «рабочие Р5 и Р9 не могут изготавливать детали Д5 и Д2»? Просто поставьте ноль, никакого смысла в этом условии нет.

Кроме безобидных неточностей в формулировках условия в формулировке задачи отсутствуют 2 дополнительных ограничений, без которых вышеуказанный ответ не получить. Разберемся в этом, решив задачу.

Решение

Если вы не знакомы с Поиском решения, то для начала прочитайте статью Поиск решения MS EXCEL. Знакомство .

Чтобы построить модель для Поиска решения (ПР) в EXCEL нужно определить 3 ее составляющих:

  • целевая функция (то, что мы будем оптимизировать, например, минимизировать трудозатраты), это формула в одной ячейке ( ячейку выделим красным );
  • переменные модели, это то что будет изменять ПР в ходе поиска ( ячейки выделим зеленым );
  • ограничения модели, например, Заказ должен быть выполнен ( ячейки выделим синим ).

Создадим в файле примера на листе а вспомогательную таблицу (загрузка рабочих по сменам) и заполним ее данными из вышеуказанного решения.

Теперь рассчитаем, сколько же деталей будет изготовлено при такой загрузке рабочих.

Оказывается, в решении опечатка. При данном ответе не будет выполнен заказ — основное условие задачи. Оказывается, деталь Д6 также изготавливает рабочий Р4. После добавления нужного количества смен, получим правильный ответ — теперь заказ выполнен.

Вернемся к таблице загрузки рабочих по сменам и еще раз внимательно посмотрим на решение, приведенное в задачнике.

Оказывается, количество смен у каждого рабочего составляет от 1 до 4 (ровно)! Почему? Потому что, это дополнительное ограничение, про которое ничего не сказано в условии задачи. Получим это решение. Для этого создадим модель.

Будем минимизировать общее количество смен всех рабочих (см. формулу в красной ячейке). В качестве ограничений потребуем выполнение заказа и загрузку каждого рабочего от 1 до 4 смен. Переменными являются ячейки, содержащие количества смен рабочих.

Нажав кнопку Найти решение получим именно тот ответ, который дан в задачнике. Чтобы понять, откуда взялось ограничение про загрузку каждого рабочего от 1 до 4 смен, удалим эти ограничения и снова запустим Поиск решения. Вот ответ:

Как видим, общее количество смен всех рабочих уменьшилось (35,78 против 37,23). Так зачем авторам потребовались эти дополнительные ограничения? Дело в том, что без этих ограничений мы получим тривиальный ответ, который очевиден и без Поиска решения! Сравните загрузку рабочих с их производительностью:

Максимальная производительность выделена цветом и поразительно совпадает с найденным решением. На самом деле решение очевидно: чтобы сократить загрузку рабочих — возьми самых производительных (по типам деталей) и загрузи их! Этот факт приводит к мысли, что либо задача некорректно сформулирована, либо неправильно решена. Будем грешить на решение.

Перед тем как решить задачу другим способом, ответим на второй вопрос задачи: «Каков самый короткий срок выполнения этого заказа?» (см. файл примера, лист б )

Чтобы вычислить самый короткий срок выполнения заказа — просто уберем 2 доп. ограничения о количестве смен (от 1 до 4) в окне Поиска решения и изменим целевую функцию в красной ячейке J31 . Теперь будем МИНимизировать МАКСимальную длительность смен рабочих ( =МАКС(J21:J30) ).

При этом считаем, что все 10 рабочих работают независимо друг от друга (на разных станках), поэтому время выполнения заказа будет равно максимальному количеству смен у одного из рабочих. Полученный ответ 3,89 совпадает с ответом в задачнике (менее 4-х дней).

Читать еще:  Самые оптимизированные игры на пк

Альтернативное решение

Теперь попробуем переформулировать условие задачи.

Во-первых, предположим, что каждый рабочий за 8-ми часовую смену обязан сделать все 8 типов деталей (от Д1 до Д8). Т.е., в 1-й час каждый рабочий делает Д1, во 2-й час каждый рабочий делает Д2 и т.д. Производительность дана в таблице.

Во-вторых, заказ нужно сделать за 1 смену.

Вопрос: каких рабочих нужно взять, чтобы выполнить заказ минимальным количеством рабочих?

При такой формулировке задачи мы избавляемся от нецелых смен и дробных деталей (каждый рабочий работает 1 смену). Также мы не получаем тривиального решения.

Если для выполнения заказа мы берем рабочего, то ему присваивается значение 1, если нет, то 0.

Поиск решения (по прежнему линейная задача) находит, что достаточно взять 7 рабочих, чтобы выполнить заказ. В этом случае, правда, заказ будет несколько перевыполнен по некоторым типам деталей.

Также можно усложнить задачу, вычислив, каких именно рабочих требуется взять, чтобы излишки по заказу были минимальны.

Решение задачи оптимизации в Excel (пример)

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

Шаг 1. Выделить на листе Excel ячейки для всех параметров задачи, снабдить их содержательными пояснениями и заполнить значения внешних параметров. От того, что выбирается в качестве строк и столбцов матриц, зависит удобство написания формул. На рис. 3 представлено возможное расположение параметров (выделены цветом) и пояснений для задачи об оптимальном планировании производства.

Рис. 3. Расположение параметров задачи.

Шаг 2. В ячейки независимых параметров заносятся начальные значения (например, нули), а в ячейки зависимых параметров заносятся определяющие их формулы. В нашем примере в ячейки B7:B9 (вектор плана X) заносятся нули, в ячейку D12 (прибыль Pr) – формула

А в ячейки F12:J12 (вектор расхода С) – матричная формула

В результате вычислений все эти формулы сразу дадут нули.

Шаг 3. Вызывается программа «Поиск решения». Вызов решения осуществляется по-разному в Excel 2003, 2007 и 2010.

Excel 2003

В меню Сервис программы Excel выбирается строка Поиск решения. Если её там не оказывается, выбирается строка Надстройки… (рис. 4 слева) и в появившемся окне Надстройки устанавливается флажок Поиск решения (рис. 4 справа). После нажатия кнопки ОК будет автоматически установлена программа Solver, и её нужно вызвать из меню Сервис/Поиск решения.

Рис. 4. Установка и вызов программы «Поиск решения» в Excel 2003.

Excel 2007/2010

Во вкладке Данные ленты инструментов программы Excel 2007/2010 выбирается инструмент Поиск решения (рис. 5). Если его там не оказывается, в Excel 2007 следует нажать кнопку меню Office (рис. 6 слева), а в Excel 2010 – меню Файл (рис. 6 справа), и выбрать пункт Параметры.

Рис. 5. Вызов программы «Поиск решения» в Excel 2007/2010.

Рис. 6. Открытие параметров в Excel 2007/2010.

В появившемся окне Параметры Excel (рис. 7 слева) нужно выбрать раздел Надстройки, в нём выбрать Поиск решения и нажать кнопку Перейти. В появившемся затем окне Надстройки (рис. 7 справа) нужно установить флажок Поиск решения и нажать кнопку ОК. После этого во вкладке Данные появится нужный инструмент.

Рис. 7. Установка программы «Поиск решения» в Excel 2007/2010.

После его вызова появится окно Поиск решения (рис. 8 для Excel 2003/2007 или рис. 9 для Excel 2010; по сути, эти интерфейсы программы «Поиск решения» практически совпадают), в котором нужно выполнить перечисленные ниже действия.

Рис. 8. Окно программы «Поиск решения» в Excel 2003/2007.

Рис. 9. Окно программы «Поиск решения» в Excel 2010.

1. В поле «Установить целевую ячейку» («Оптимизировать целевую функцию») нужно указать адрес ячейки целевого параметра (в нашем примере – прибыли Pr). Как и всюду в Excel, адрес указывается щелчком по соответствующей ячейке в таблице.

2. Указать критерий оптимизации, выбрав переключатель максимального или минимального значения (для задачи максимизации или минимизации, соответственно), или установить нужное значение целевого параметра (при решении уравнения). Мы устанавливаем переключатель максимума.

3. В поле «Изменяемые ячейки/переменные» указать все ячейки изменяемых (независимых) внутренних параметров. В нашем примере это вектор плана производства X.

4. Добавить ограничения. Для этого нажмите кнопку Добавить и в появившемся окне (рис. 10) выберите операцию (£, ≥, = или цел) и укажите её операнды (слева – ссылка на ячейку или диапазон параметра, справа – число или ссылку). На рисунке показано добавление ограничение нашей задачи С £ В (адреса диапазонов см. на рис. 1).

Рис. 8. Добавление ограничения.

Для добавления новых ограничений можно в этом же окне нажать кнопку Добавить. Добавив все ограничения, закройте окно нажатием кнопки ОК и вернитесь в окно Поиск решения. Для нашего примера оно может выглядеть, как на рис. 11.

Рис. 11. Окно с моделью задачи оптимизации.

5. Введённые в этом окне данные называются моделью задачи оптимизации. Дополнительные параметры оптимизации можно установить с помощью кнопки Параметры. Для решения задачи нажмите кнопку Выполнить (Найти решение).

6. Результаты решения сразу появятся в таблице и в новом окне (рис. 12), которое в нашем случае сообщает об успешном решении задачи оптимизации. Нажав кнопку ОК, можно вернуться к исходному состоянию таблицы (без оптимального решения), если установлен флажок «Восстановить исходные значения», или к таблице, в которой сохранено найденное оптимальное решение.

Рис. 12. Результат работы программы Solver.

7. Сохранить модель. Excel помнит последнюю модель оптимизации, поэтому при работе с разными моделями (для одной или для разных задач) необходимо запомнить построенную модель, чтобы вернуться к ней, когда потребуется. Для этого нужно открыть окно Поиск решения (в нём откроется модель, см. рис. 11) и нажать в нём кнопку Параметры и в появившемся окне нажать кнопку Сохранить модель… (рис. 13 слева) или сразу нажать кнопку Загрузить/сохранить (рис. 9).

Рис. 13. Сохранение модели оптимизационной задачи.

В окне Сохранить модель (рис. 13 справа) нужно указать адрес, начиная с которого в столбик расположатся данные модели. Они займут три ячейки плюс по одной ячейки на каждое ограничение. В нашем случае это будет 6 ячеек. Нужно предусмотреть также заголовок модели. На рис. 14 слева показан, как выглядит модель в нашем примере (мы дали ей имя Модель 1), а справа – формулы, стоящие в этих ячейках.

Рис. 14. Сохранённая модель задачи.

В первой строке сохраняется критерий оптимизации и его оптимальное значение. Во второй – адреса изменяемых переменных (функция СЧЁТ выдаёт их количество). В следующих ячейках – ограничения. В последней ячейке – список параметров, указанных в окне Параметры поиска решения (рис. 13 слева).

Чтобы в дальнейшем использовать одну из сохранённых моделей, нужно в окне Параметры поиска решения (рис. 13 слева) нажать кнопку Загрузить модель… и в появившемся окне указать весь диапазон ячеек, занятых нужной моделью.

Задание 2. Индивидуальные варианты

В каждом варианте задаётся только количество видов продукции и видов используемых комплектующих. Названия этих видов вы должны придумать сами. Числовые значения векторов Р и В, а также матрицы R, могут быть заданы произвольно (но правдоподобно).

Ссылка на основную публикацию
Adblock
detector