Функция Microsoft Excel: поиск решения

Инструмент «Поиск решения» в Excel

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

Как включить «Поиск решений» в Excel

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

  1. Запустите Excel. Лучше заранее открыть в нём какой-либо документ. Чтобы сделать это просто нажмите два раза по файлу XLSX или XLS. Также нужный файл можно перенести в рабочую область программы.
  2. Далее нажмите на кнопку «Файл» в верхней левой части окна.

Обратите внимание на левое меню приложения. Там нужно будет воспользоваться кнопкой «Параметры».

  • Будет открыто отдельное окошко со всеми настройками Excel. Вам нужно перейти в раздел «Надстройки», что расположен в левом меню.
  • В поле «Управление» поставьте значение «Надстройки Excel». Оно должно там стоять по умолчанию. Нажмите «Перейти».

    Здесь установите галочку у пункта «Поиск решения» и нажмите «Ок».

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

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

    Подготовка таблицы

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

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

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

    1. Рядом с таблицей выделим несколько ячеек в одном столбце. Желательно от основной таблицы отступить несколько столбцов.
    2. Залейте ячейки цветом для удобства их дальнейшего определения. Чтобы это сделать, нажмите по иконке заливки в основной панели (левая часть) и выберите там наиболее удобный цвет для заливки.
    3. Над выделенными ячейками создайте заголовок «Коэффициенты» или назовите его как будет удобно.

    Подробно про то, как создать заголовок в Excel мы писали в отдельной статье.

    Далее вам нужно будет создать связь между целевой и искомой ячейками с помощью специальных формул. В данном случае нужно выделить ячейку с общим бюджетом для премий. Туда пишется формула: «=C10*$G$3». C10 – это ячейка с общей заработной платой сотрудников, а $G$3 – адрес ячейки с коэффициентом, которую вы создавали ранее. У вас могут быть другие адреса ячеек, не забывайте об этом.

    В строку с формулами не нужно вводить общий бюджет для премий. Он вводится на другом этапе!

    Работа с инструментом «Поиск решения»

    Когда таблица полностью готова к работе, вам осталось только воспользоваться функцией «Поиск решения»:

    1. Выделите ту ячейку, в которую вы ранее вводили формулу для дальнейшей обработки.
    2. В верхней части программы нажмите на блок «Данные», а затем выберите «Поиск решения», который расположен в правой части верхней строки. В некоторых версиях Excel он может не иметь текстового обозначения, а быть обозначенным просто в виде вопросительного знака.

  • Будет открыто окошко для внесения пользовательских данных. У поля «Оптимизировать целевую функцию» нажмите на иконку в виде таблички.
  • Откроется строка, куда нужно вписать параметры поиска решения. В данном случае нужно будет выделить ячейку, куда вы прописывали специальную формулу из предыдущего заголовка. Программа сама её оптимизирует под конкретную задачу. После этого потребуется снова кликнуть по иконке таблицы, чтобы вернуться в редактор настроек.
  • Затем поставьте маркер у пункта «Значения», чтобы вписать нужное число. В данном случае это будет 30 000 – наш бюджет, закладываемый на премию сотрудникам.
  • Теперь пропишите в «Изменяя значения переменных» адрес ячейки, в которой должен находится коэффициент. Умножением на него заработной платы мы получим подробный расчёт величины премии для каждого сотрудника.
  • Далее воспользуйтесь кнопкой «Добавить», которая расположена в левой части окошка.
  • Откроется окошко добавления ограничений. В нашем случае ограничением является искомая ячейка с коэффициентом.
  • Затем нужно будет выбрать знак для операции. Программа предлагает несколько знаков: «меньше или равно», «больше или равно», «равно», «целое число», «бинарное» и другие. В нашем случае разумнее всего будет выбрать «больше или равно».
  • В следующее поле «Ограничение» укажите число «0». Если вы хотите добавить какое-то дополнительное ограничение, то придётся нажать на иконку в виде таблички.
  • Заполнив все данные жмите на «Ок», чтобы параметры применились.
  • Теперь в окошке с настройками поставьте галочку у пункта «Сделать переменные без ограничений отрицательными».
  • Работу скрипта можно настроить под некоторые свои нужды, например, сделать так, чтобы для каждого сотрудника была какая-то максимальная премия, больше которой она не может быть даже если условия задачи этого позволяют. Также дополнительные настройки позволяют избежать возможные ошибки в вычислениях и работе скрипта. Можете попробовать запустить его и без них, но тогда есть вероятность появления ошибок в работе макроса. Параметры задаются по следующей инструкции:

    1. В окошке «Параметры поиска решения» нажмите на кнопку «Параметры», чтобы открыть интерфейс дополнительных настроек решения.
    2. Здесь можно задать уточнения для разных типов данных, например, вычисление целостности процента, различных пределов поиска решения и т.д.
    3. Задав эти параметры нажмите на кнопку «Ок».

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

    Помимо показа результатов Excel предложит вам сохранить данное решение, точнее формулу, чтобы применять её в других документах. Чтобы принять решение, просто отметьте маркером нужный вариант. Вне зависимости от выбранного вами ранее решения потребуется установить галочку у пункта «Вернуться в диалоговое окно параметров».

    Если была допущена ошибка

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

    1. Вернитесь в окошко «Параметры поиска решения». Для этого просто нажмите на иконку в виде вопроса, которая расположена во вкладке «Данные».
    2. В открывшемся окне проверьте наличие ошибки в задаваемых параметрах. Если она была найдена, то исправьте её.
    3. Также иногда помогает изменение метода решения. Откройте выпадающее меню у соответствующего пункта в окне. И выберите один из представленных методов решения. Всего доступно: «Поиск решения нелинейных задач методом ОПГ», «Поиск решения линейных задач симплекс-методом» и «Эволюционный поиск решения».
    4. Когда закончите кликните по кнопке «Найти решение».

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

    Используем поиск решений в Excel 2010 для решения сложных задач

    Автор: Леонид Радкевич · Опубликовано 21.12.2013 · Обновлено 06.12.2016

    Значительная часть задач, которые решаются с помощью электронных таблиц, предполагают, что для обнаружения нужного результата у пользователя уже есть хоть какие-то исходные данные. Однако Exсel 2010 располагает необходимыми инструментами, с помощью которых можно решить эту задачу наоборот – подобрать нужные данные, чтобы получить необходимый результат.

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

    Итак – начинаем с установки данной надстройки (поскольку самостоятельно она не появится). К счастью сейчас сделать это можно достаточно просто и быстро – открываем меню «Сервис», а уже в нем «Надстройки»

    Останется только в графе «Управление» указать «Надстройки Excel», а после нажать кнопочку «Перейти».

    После этого несложного действия кнопка активации «Поиска решения» будет отображаться в «Данных». Как и показано на картинке

    Давайте рассмотрим, как правильно используется поиск решений в Excel 2010, на нескольких простых примерах.

    Пример первый.

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

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

    В первую очередь необходимо быстро составить (если ее еще нет) таблицу, где будут хранится исходные формулы и данные, согласно которым и можно будет получить желаемый результат. Для нас этот результат – суммарная величина премии. А сейчас внимание – целевая ячейка С8 должна быть с помощью формул связана с искомой изменяемой ячейкой под адресом Е2. Это критично. В примере мы связываем их используя промежуточные формулы, которые и отвечают за высчитывание премии каждому сотруднику (С2:С7).

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

    Под «1» обозначена наша целевая ячейка. Она может быть только одна.

    «2» — это возможные варианты оптимизации. Всего можно выбрать «Максимальное», «Минимальное» или «Конкретное» возможные значения. И если вам необходимо именно конкретное значение, то его нужно указать в соответствующей графе.

    «3» — изменяемых ячеек может быть несколько (целый диапазон или же отдельно указанные адреса). Ведь именно с ними и будет работать Excel, перебирая варианты так, чтобы получилось значение, заданное в целевой ячейке.

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

    «5» — кнопка перехода к интерактивным вычислениям на основе заданной нами программы.

    Но теперь вернемся к возможности изменять наше задание, воспользовавшись кнопкой «Добавить». Данный этап является довольно ответственным (не менее чем построение формул), поскольку именно ограничение позволяют получить правильный результат на выходе. Здесь все сделано максимально удобно, так что задать их вы сможете не только для всего диапазона сразу, но и для определенных ячеек.

    Для этого можно использовать ряд определенных (и знакомых всем пользователям Excel 2010) знаков «=», «>=», « 3 досок, а модель «В» — на 1 м 3 больше (то есть – 4). От своих поставщиков вы за неделю получаете максимум 1700 м 3 досок. При этом модель «А» создается за 12 минут работы станка, а «В» — за 30 минут. Всего в неделю станок может работать не более 160 часов.

    Вопрос – сколько всего изделий (и какой модели), должна выпускать фирма за неделю, чтобы получить максимально возможную прибыль, если полочка «А» дает 60 рублей прибыли, а «В» — 120?

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

    Любым удобным способом запускаем наш «Поиск решений», вводим данные, производим настройку.

    Итак, рассмотрим то, что мы имеем. В целевой ячейке F7 содержится формула, которая и рассчитает прибыль. Параметр оптимизации устанавливаем на максимум. Среди изменяемых ячеек у нас значится «F3:G3». Ограничения – все обнаруженные значения должны быть целыми числами, неотрицательными, общее количество потраченного машинного времени не превышает отметку 160 (наша ячейка D9), количество сырья не превышает 1700 (ячейка D8).

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

    Активируем программу, и она подготавливает решение.

    Впрочем, это не единственное решение и у вас вполне может выскочить другой результат. Это может произойти даже в том случае, если все данные были указаны верно и ошибок в формулах тоже не было

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

    Наш верхний параметр отвечает за точность. Чем он меньше, тем выше точность и в нашем случае это значительно повышает шансы получить целое число. Второй параметр («Игнорировать целочисленные ограничения») и дает ответ на вопрос, как мы смогли получить такой ответ с тем, что в запросе явно указали целое число. «Поиск решений» просто проигнорировал это ограничение в связи с тем, что так ему сказали расширенные настройки.

    Так что будьте предельно внимательны в будущем.

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

    Итак, строительная компания дает заказ на перевозку песка, который берется от 3 поставщиков (карьеров). Его необходимо доставить 5 разным потребителям (которыми выступают строительные площадки). Стоимость доставки груза включена в себестоимость объекта, так что наша задача обеспечить доставку груза на стройплощадки с минимальными затратами.

    Мы имеем – запас песка в карьере, потребность стройплощадок в песке, затрату на транспортировку «поставщик-потребитель».

    Необходимо найти схему оптимальной перевозки груза (куда и откуда), при которой общая затрата на перевозку была бы минимальной.

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

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

    Впрочем, не будем забывать, что достаточно часто транспортные задачи могут быть усложнены некоторыми дополнительными ограничителями. Допустим, возникло осложнение на дороге и теперь из карьера 2 просто технически невозможно доставить груз на стройплощадку 3. Чтобы учесть это, необходимо просто дописать дополнительное ограничение «$D$13=0». И если теперь запустить программу, то результат будет иным

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

    Вот и все по данному вопросу.

    Мы выполнили поиск решений в Excel 2010 — для решения сложных задач

    Вернуться в начало статьи Используем поиск решений в Excel 2010 для решения сложных задач

    • Следующая публикация Как выбрать электронную книгу
    • Предыдущая публикация Используем автофильтр в Excel

    Как сделать таблицу в Excel

    Автор: Леонид Радкевич · Published 10.06.2012 · Last modified 09.12.2017

    Построение графиков в Excel 2010

    Автор: Леонид Радкевич · Published 10.04.2013 · Last modified 06.12.2016

    Складской учет в Excel

    Автор: Леонид Радкевич · Published 13.05.2013 · Last modified 06.12.2016

    Функция Microsoft Excel: поиск решения

    Мощным средством анализа данных Excel является надстройка Solver (Поиск решения) . С ее помощью можно определить, при каких значениях указанных влияющих ячеек формула в целевой ячейке принимает нужное значение (минимальное, максимальное или равное какой-либо величине). Для процедуры поиска решения можно задать ограничения, причем не обязательно, чтобы при этом использовались те же влияющие ячейки. Для расчета заданного значения применяются различные математические методы поиска. Вы можете установить режим, в котором полученные значения переменных автоматически заносятся в таблицу. Кроме того, результаты работы программы могут быть оформлены в виде отчета.
    Программа Поиск решений (в оригинале Excel Solver) – дополнительная надстройка табличного процессора MS Excel, которая предназначена для решения определенных систем уравнений, линейных и нелинейных задач оптимизации, используется с 1991 года.
    Размер задачи, которую можно решить с помощью базовой версии этой программы, ограничивается такими предельными показателями:

    • количество неизвестных (decision variable) – 200;
    • количество формульных ограничений (explicit constraint) на неизвестные – 100;
    • количество предельных условий (simple constraint) на неизвестные – 400.

    Разработчик программы Solver компания Frontline System уже давно специализируется на разработке мощных и удобных способов оптимизации, встроенных в среду популярных табличных процессоров разнообразных фирм-производителей (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3).
    Высокая эффективность их применения объясняется интеграциею программы оптимизации и табличного бизнес-документа. Благодаря мировой популярности табличного процессора MS Excel встроенная в его среду программа Solver есть наиболее распространенным инструментом для поиска оптимальных решений в сфере современного бизнеса.
    По умолчанию в Excel надстройка Поиск решения отключена. Чтобы активизировать ее в Excel 2007 , щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel , а затем выберите категорию Надстройки . В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти . В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК .

    В Excel 2003 и ниже выберите команду Сервис/Надстройки , в появившемся диалоговом окне Надстройки установите флажок Поиск решения и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке Да. (Возможно, вам понадобится установочный компакт-диск Office).

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

    2. Выделите целевую ячейку, которая должна принять необходимое значение, и выберите команду:
    — В Excel 2007 Данные/Анализ / Поиск решения ;
    — В Excel 2003 и ниже Tools > Solver (Сервис > Поиск решения). Поле Set Target Cell (Установить целевую ячейку) открывшегося диалогового окна надстройки Solver (Поиск решения) будет содержать адрес целевой ячейки.
    3. Установите переключатели Equal To (Равной), задающие значение целевой ячейки, — Мах (максимальному значению), Min (минимальному значению) или Value of (значению). В последнем случае введите значение в поле справа.
    4. Укажите в поле By Changing Cells (Изменяя ячейки), в каких ячейках программа должна изменять значения в поисках оптимального результата.
    5. Создайте ограничения в списке Subject to the Constraints (Ограничения). Для этого щелкните на кнопке Add (Добавить) и в диалоговом окне Add Constraint (Добавление ограничения) определите ограничение.

    6. Щелкните на кнопке на кнопке Options (Параметры), и в появившемся окне установите переключатель Неотрицательные значения (если переменные должны быть позитивными числами), Линейная модель (если задача, которую вы решаете, относится к линейным моделям)

    8. Когда появится диалоговое окно Solver Results (Результаты поиска решения), выберите переключатель Keep Solve Solution (Сохранить найденное решение) или Restore Original Values (Восстановить исходные значения).
    9. Щелкните на кнопке ОК.

    Параметры средства Поиск решения
    Максимальное время — служит для ограничения времени, отпущенного на поиск решения задачи. В этом поле можно ввести время в секундах, не превышающее 32 767 (примерно девять часов); значение 100, используемое по умолчанию, вполне приемлемо для решения большинства простых задач.

    Предельное число итераций — управляет временем решения задачи путем ограничения числа вычислительных циклов (итераций).
    Относительная погрешность — определяет точность вычислений. Чем меньше значение этого параметра, тем выше точность вычислений.
    Допустимое отклонение — предназначен для задания допуска на отклонение от оптимального решения, если множество значений влияющей ячейки ограничено множеством целых чисел. Чем больше значение допуска, тем меньше времени требуется на поиск решения.
    Сходимость — применяется только к нелинейным задачам. Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость, поиск прекращается.
    Линейная модель — служит для ускорения поиска решения путем применения к задаче оптимизации линейной модели. Нелинейные модели предполагают использование нелинейных функций, фактора роста и экспоненциального сглаживания, что замедляет вычисления.
    Неотрицательные значения — позволяет установить нулевую нижнюю границу для тех влияющих ячеек, для которых не было задано соответствующее ограничение в диалоговом окне Добавить ограничение.
    Автоматическое масштабирование — используется, когда числа в изменяемых ячейках и в целевой ячейке существенно различаются.
    Показывать результаты итераций — приостанавливает поиск решения для просмотра результатов отдельных итераций.
    Загрузить модель — после щелчка на этой кнопке отрывается одноименное диалоговое окно, в котором можно ввести ссылку на диапазон ячеек, содержащих модель оптимизации.
    Сохранить модель — служит для отображения на экране одноименного диалогового окна, в
    котором можно ввести ссылку на диапазон ячеек, предназначенный для хранения модели оптимизации.
    Оценка линейная — выберите этот переключатель для работы с линейной моделью.
    Оценка квадратичная — выберите этот переключатель для работы с нелинейной моделью.
    Разности прямые — используется в большинстве задач, где скорость изменения ограничений относительно невысока. Увеличивает скорость работы средства Поиск решения.
    Разности центральные — используется для функций, имеющих разрывную производную. Данный способ требует больше вычислений, однако его применение может быть оправданным, если выдано сообщение о том, что получить более точное решение не удается.
    Метод поиска Ньютона — требует больше памяти, но выполняет меньше итераций, чем в методе сопряженных градиентов.
    Метод поиска сопряженных градиентов — реализует метод сопряженных градиентов, для которого требуется меньше памяти, но выполняется больше итераций, чем в методе Ньютона. Данный метод следует использовать, если задача достаточно большая и необходимо экономить память или если итерации дают слишком малое отличие в последовательных приближениях.

    Функция Microsoft Excel: поиск решения

    Методика решения задачи симплекс-методом с
    использованием Microsoft Excel

    Алгоритм получения решения задачи симплекс-методом с использованием офисного приложения Microsoft Excel рассмотрим на примере 2.2.1. Математическая модель задачи имеет следующий вид:

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

    Ввод исходных данных задачи

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

    Экранная форма для ввода условий задачи имеет следующий вид
    (рис. 2.2.2):

    В ячейках В4:С4 находятся значения коэффициентов целевой функции; в массиве В6:С8 –коэффициенты левой части ограничений; в столбце Е6:Е8значения правой части ограничений. Ячейки В2:С2соответствуют переменным задачи, а в ячейке Е2будет отображаться значение целевой функции. Сюда необходимо ввести формулу, по которой это значение рассчитывается, то есть . Для этого курсор ставится в ячейку и далее набирается следующее выражение: .

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

    Значение целевой функции также можно рассчитать, используя надстройку «Мастер функций», а именно, функцию «СУММПРОИЗВ». Для этого необходимо выполнить следующие действия:

    поставить курсор в поле Е2;

    выбрать на панели инструментов кнопку ;

    в окне «Категория» выбрать «Математические». В окне «Выберите функцию» «СУММПРОИЗВ» (рис. 2.2.3) и нажать «ОК»;

    Ввести аргументы функции: в строку «Массив 1» выражение В2:С2, а в строку «Массив 2» выражение В4:С4 (можно, выделять соответствующие массивы с помощью мыши) (рис. 2.2.4) и нажать «ОК»;

    После этого в ячейке Е2 появится текущее значение целевой функции, вычисленное по введенной формуле. Оно равно нулю, так как переменные в данный момент равны нулю.

    Аналогично в ячейки D6:D8вводятся формулы для расчета левых частей ограничений (рис. 2.2.5):

    Для ячейкиD6формула имеет вид ,а ее реализация в ячейке: или =СУММПРОИЗВ(В2:C2; В6:C6).

    Для ячейкиD7формула имеет вид ,а ее реализация в ячейке: или = СУММПРОИЗВ(В2:C2; В7:C7).

    Для ячейкиD8формула имеет вид ,а ее реализация в ячейке: или = СУММПРОИЗВ(В2:C2; В8:C8).

    Как видно, формулы для расчета левой части ограничений отличаются друг от друга только именем второго массива (строчки коэффициентов ограничения), первый же массив (массив значений переменных) один и тот же. Поэтому можно ввести формулу один раз, а затем скопировать ее, сделав абсолютную ссылку на массив переменных В2:C2.

    Для того, чтобы сделать абсолютную ссылку на определенный столбец, необходимо поставить символ $, перед буквой, обозначающей имя столбца. Например $В2:$C2.Чтобы зафиксировать строку, символ $, ставится перед номером строки: В$2:C$2.Если необходимо сделать абсолютную ссылку на конкретную ячейку (ячейки), символ $ ставится и перед именем столбца и перед номером строки: $В$2:$C$2.

    Абсолютную ссылку на ячейку (ячейки) можно сделать, нажав клавишу F4, когда курсор находится в поле имени ячейки. При однократном нажатии клавиши будет сделана абсолютная ссылка на массив или ячейку ($В$2: $C$2). Если клавишу нажать дважды, будет сделана абсолютная ссылка на номер строки (В$2: C$2). При следующем нажатии клавиши ссылка будет сделана на имя столбца ($В2: $C2).

    При данном способе реализации симплекс-метода достаточно сделать ссылку лишь на соответствующую строку: В$2: C$2. В то же время допустима и абсолютная ссылка на конкретный массив ячеек: $В$2: $C$2.

    Таким образом, для ячейки D6формула будет иметь вид или = СУММПРОИЗВ(В$2: C$2;В6:C6) (в случае абсолютной ссылки на массив = СУММПРОИЗВ($В$2: $C$2;В6:C6)).

    Затем эту формулу необходимо скопировать в ячейки D7иD8.Копировать формулу можно с помощью клавиш «Ctrl-Insert» копировать и клавиш «Shift-Insert» вставить. Другой способ копирования формул поставить курсор в ячейку, содержащую формулу и протянуть ее за правый нижний угол на все ячейки, в которые ее необходимо скопировать.

    После этого экранная форма условий задачи будет иметь вид (рис. 2.2.6).

    Для получения решения задачи используется надстройка «Поиск решения», которая находится в меню «Сервис».

    В диалоговом окне «Поиск решения» (рис. 2.2.7) необходимо выполнить следующие действия:

    Поставить курсор в поле «Установить целевую ячейку» и ввести адрес ячейки, в которой находится формула для расчета значения целевой функции (можно сделать ссылку на ячейку мышью). В примере это ячейка E2.

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

    Поставить курсор в поле «Изменяя ячейки» и ввести адрес массива, в котором находятся значения переменных. В примере это В2:C2.Адрес можно внести также с помощью выделения мышью соответствующих ячеек.

    В окне «Ограничения» выбрать кнопку «Добавить», после чего появится окно «Добавление ограничения» (рис. 2.6.8).

    В поле «Ссылка на ячейку» ввести адрес ячейки, в которой содержится левая часть ограничения. (Это можно сделать путем выделения мышью соответствующей ячейки на экране). В поле знака открыть список предлагаемых знаков и выбрать нужный. В поле «Ограничения» ввести адрес ячейки, содержащей правую часть ограничений. В примере первое ограничение: D6

    Функция Microsoft Excel: поиск решения

    Ограничения для выпуска продукции

    Создание модели в Excel

    1. Открыть табличный процессор Excel.

    Подготовить начальную таблицу для размещения описаний задачи, исходных данных, ограничений, коэффициентов целевой функции, место для проведения вычислений и сохранения результатов. Начальная таблица, как она будет выглядеть в Excel , представлена на рис. 1. Столбец D введен для занесения результатов в ячейки D 5: D 10 . В этих ячейках будут отображаться результаты вычислений, т.е. сколько на самом деле будет задействовано единиц каждого вида ресурса для выпуска всей номенклатуры продукции. Добавить в таблицу новые обозначения, которые понадобятся для ввода начальных значений и вывода результатов. Для этой цели:

    · в строке 11 (ячейки E 11: G 11 ) ввести коэффициенты для целевой функции, ее название — «Прибыль от реализации единицы продукции»;

    · в строке 12 создать заголовок – «Значения Xj при решении задачи», ячейки E 12: G 12 понадобятся для ввода формул;

    · ячейку E 13 можно выделить, в которой будет формироваться результат, поэтому в строке 13 сделана запись – «Конечная прибыль от реализации продукции», это и есть значение целевой функции.

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

    Таблица 2. Перечень формул для установки в ячейках таблицы Excel

    =$E$13*E 6 +$F$13*F 6 +$G$13*G 6

    =$E$13*E 7 +$F$13*F 7 +$G$13*G 7

    =$E$13*E 8 +$F$13*F 8 +$G$13*G 8

    =$E$13*E 9 +$F$13*F 9 +$G$13*G 9

    =$E$13*E 10 +$F$13*F 10 +$G$13*G 10

    3. Работа с надстройкой Excel – Поиск решения

    · Вызвать окно: Поиск решения (рис. 1), нажать на кнопку .

    · Ввести начальные значения Xj в ячейки E 13: G 13 . Например, по двадцать единиц каждого вида изделия.

    · Выделить курсором ячейку E 14 с целевой функцией.

    · Выбрать команду в меню Сервис-Поиск решения.

    · В открывшемся диалоговом окне – Поиск решения, заполнить окна: Изменяя ячейки и Ограничения (можно проверить в диалоговом окне факт установки курсора на целевой ячейке, если требуется, то ее положение можно изменить). На рис. 3 показано всплывающее диалоговое окно Поиск решения, в котором проведены все перечисленные подготовительные действия.

    · Установить диапазон ячеек в строке всплывающего окна: Изменяя ячейки, в которых будет отображаться результат с количеством номенклатуры Xj изделий. В рассматриваемом примере, это будут ячейки E 13: G 13 , которые должны быть фиксированными (перед координатами ячее ставится знак $).

    · Отметить селекторную кнопку: Равной максимальному значению, т.к. определяется максимальное использование ресурсов.

    · В окно с наименованием Ограничения последовательно ввести все ограничения для уравнений модели. В данном примере ячейки C 5: C 10 содержат количество деталей на складе, которые потребуются для выпуска всей номенклатуры продукции, а в ячейки D 5: D 10 были внесены формулы модели по каждому виду комплектующей, следовательно, суммарное количество используемых деталей не должно превышать величину, указанную в правой части уравнения. Для ввода ограничений, необходимо нажать на кнопку . После выполненного действия будет открыто диалоговое окно с наименованием Добавление ограничений, которое показано на рис. 4. В окне видно, что вычисляемое значение в ячейке D 5 должно быть меньше или равно установленной величины в ячейке C 5 .

    · Если требуется ввести еще ограничения, то нажать на кнопку , в противном случае нажать на кнопку .

    · Ввести ограничения на выпуск номенклатуры продукции в ячейки E 13: G 13 (в примере всего три вида продукции). Так, в качестве примера, на рис. 5 показано диалоговое окно для добавления ограничений, в котором указано, что вычисляемое значение в ячейке G 13 должно быть более 1 единицы (это условие записано в исходной таблице для изделия – Компьютеры).

    · Ввести ограничения на форму представление результатов. В данной постановке задачи подразумевается, что количество изделий не может быть дробной величиной, а должны отображаться только целыми числами, следовательно, при выполнении расчетов, это обстоятельство необходимо учитывать. На рис. 6 показано диалоговое окно для добавления ограничений, в котором для ячейки E 13 (в ней отображается количество единиц изделия), установлено условие ‘ цел’, что означает целочисленное решение. В раскрывающемся списке выбирается необходимое условие.

    • Установить параметры для оптимизационной задачи, для чего в диалоговом окне: Поиск решения, нажать на кнопку . После того, как откроется диалоговое окно с наименованием Параметры поиска решения, представленное на рис. 7.
    • В окне установить пометку: Линейная модель, и закрыть кнопкой ОК.

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

    Программа формирует три типа отчетов: Результаты, Устойчивость и Пределы. Если отметить любой из них или все вместе, а затем вызвать, то можно провести анализ исходных данных и конечных результатов, об этом будет сказано ниже. В том случае, если в окне Результаты поиска решения появится сообщение: « Ошибка в модели. Проверьте правильность значений в ячейках и ограничениях», то отчеты получить невозможно, а следует открыть окно Поиск решения (рис. 4), и проверить правильность установки знаков ограничений, наименования ячеек, в которых должна быть вычислена целевая функция и установлены начальные значения изменяемых ячеек. На рис. 9 показан лист Excel , который содержит исходные данные и результаты решения оптимизационной задачи методом линейного программирования по заданным начальным значениям и тем условиям, которые были установлены, в соответствии с постановкой задачи.

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

    · Принятие решения о производственной программе может быть направлено на то, что следует изменить некоторые условия, например, предприятие производитель имеет обязательства перед потребителем о поставке в текущем периоде 15 телевизоров. Следовательно, условие в ячейке E 13 должно быть изменено (вместо 1 следует установить 15).

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

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

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

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

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

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

    Вопросы для самопроверки

    1. Каким образом представляют условия постановки задачи с многими неизвестными?

    1. Почему в условия решения задачи вводят дополнительные переменные вида Xi > 0?
    2. Как вызвать инструментальное средство для решения оптимизационных задач Поиск решения?
    3. Какие шаги следует предпринять, чтобы ввести параметры уравнений и ограничений в окне Поиск решения?

    Решение задачи линейного программирования в Excel

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

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

    Рассмотрим линейное программирование в Excel на примере задачи, ранее решенной графическим методом.

    Задача. Николай Кузнецов управляет небольшим механическим заводом. В будущем месяце он планирует изготавливать два продукта (А и В), по которым удельная маржинальная прибыль оценивается в 2500 и 3500 руб., соответственно. Изготовление обоих продуктов требует затрат на машинную обработку, сырье и труд. На изготовление каждой единицы продукта А отводится 3 часа машинной обработки, 16 единиц сырья и 6 единиц труда. Соответствующие требования к единице продукта В составляют 10, 4 и 6. Николай прогнозирует, что в следующем месяце он может предоставить 330 часов машинной обработки, 400 единиц сырья и 240 единиц труда. Технология производственного процесса такова, что не менее 12 единиц продукта В необходимо изготавливать в каждый конкретный месяц. Необходимо определить количество единиц продуктов А и В, которые Николай доложен производить в следующем месяце для максимизации маржинальной прибыли.

    Скачать заметку в формате Word, пример в формате Excel

    1. Воспользуемся математической моделью построенной в упомянутой заметке. Вот эта модель:

    Максимизировать: Z = 2500 * х1 + 3500 *х2

    При условии, что: 3 * х1 + 10 * х2 ≤ 330

    2. Создадим экранную форму и введем в нее исходные данные (рис. 1).

    Рис. 1. Экранная форма для ввода данных задачи линейного программирования

    Обратите внимание на формулу в ячейке С7. Это формула целевой функции. Аналогично, в ячейки С16:С18 введены формулы для расчета левой части ограничений.

    3. Проверьте, если у вас установлена надстройка «Поиск решения» (рис. 2), пропустите этот пункт.

    Рис. 2. Надстройка Поиск решения установлена; вкладка «Данные», группа «Анализ»

    Если надстройки «Поиск решения» вы на ленте Excel не обнаружили, щелкните на кнопку Microsoft Office, а затем Параметры Excel (рис. 3).

    Рис. 3. Параметры Excel

    Выберите строку Надстройки, а затем в самом низу окна «Управление надстройками Microsoft Excel» выберите «Перейти» (рис. 4).

    Рис. 4. Надстройки Excel

    В окне «Надстройки» установите флажок «Поиск решения» и нажмите Ok (рис. 5). (Если «Поиск решения» отсутствует в списке поля «Надстройки», чтобы найти надстройку, нажмите кнопку Обзор. В случае появления сообщения о том, что надстройка для поиска решения не установлена на компьютере, нажмите кнопку Да, чтобы установить ее.)

    Рис. 5. Активация надстройки «Поиск решения»

    После загрузки надстройки для поиска решения в группе Анализ на вкладке Данные становится доступна команда Поиск решения (рис. 2).

    4. Следующим этапом заполняем окно Excel «Поиск решения» (рис. 6)

    Рис. 6. Заполнение окна «Поиск решения»

    В поле «Установить целевую ячейку» выбираем ячейку со значением целевой функции – $C$7. Выбираем, максимизировать или минимизировать целевую функцию. В поле «Изменяя ячейки» выбираем ячейки со значениями искомых переменных $C$4:$D$4 (пока в них нули или пусто). В области «Ограничения» с помощью кнопки «Добавить» размещаем все ограничения нашей модели. Жмем «Выполнить». В появившемся окне «Результат поиска решения» выбираем все три типа отчета (рис. 7) и жмем Ok. Эти отчеты нужны для анализа полученного решения. Подробнее о данных, представленных в отчетах, можно почитать здесь.

    Рис. 7. Выбор типов отчета

    На основном листе появились значения максимизированной целевой функции – 130 000 руб. и изменяемых параметров х1 = 10 и х2 = 30. Таким образом, для максимизации маржинального дохода Николаю в следующем месяце следует произвести 10 единиц продукта А и 30 единиц продукта В.

    Если вместо окна «Результат поиска решения» появилось что-то иное, Excel`ю найти решение не удалось. Проверьте правильность заполнения окна «Поиск решения». И еще одна маленькая хитрость. Попробуйте уменьшить точность поиска решения. Для этого в окне «Поиск решения» щелкните на Параметры (рис. 8.) и увеличьте погрешность вычисления, например, до 0,001. Иногда из-за высокой точности Excel не успевает за 100 итераций найти решение. Подробнее о параметрах поиска решения можно почитать здесь.