Как использовать решатель solver в calc

Решение экономических задач в LibreOffice Calc

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

Как пример рассмотрим транспортную задачу, решение которой будем искать с помощью программы LibreOffice Calc.

Для строительства четырех объектов используется кирпич, изготавливаемый на трех заводах. Ежедневно каждый из заводов может изготовить 100, 150 и 50 условных единиц кирпича (предложение поставщиков). Потребности в кирпиче на каждом из строящихся объектов ежедневно составляют 75, 80, 60 и 85 условных единиц (спрос потребителей). Тарифы перевозок одной условной единицы кирпича с каждого из заводов к каждому из строящихся объектов задаются матрицей транспортных расходов С.

С =

Требуется составить такой план перевозок кирпича к строящимся объектам, при котором общая стоимость перевозок будет минимальной.

Для решения транспортной задачи на персональном компьютере с использованием CALC необходимо ввести все исходные данные в ячейки листа Calc.

Затем формируем элементы математической модели

1. Заполняем ячейки блока «Матрица перевозок» (C14:F16) числом 0,01.

2. Используем «автосуммирование» для заполнения блока «Фактически реализовано» (Например — для ячейки Н14 – SUM=(С14:F14)).

3. Используем также «автосуммирование» для заполнения блока «Фактически получено» (Например – для ячейки С18 – SUM=(C14:C16)).

Далее формируем целевую функцию.

Заполняем блок «Транспортные расходы по потребителям». Для этого используем формулу =SUM(C6:C8*C14:C16).

Например для ячейки С21 — выделяем первый столбец блока «Матрица транспортных расходов» (столбец C6:C8) – нажимаем клавиши Shift + * – выделяем первый столбец блока «Матрица перевозок» (столбец C14:C16) – активируем строку формул – нажимаем одновременно три клавиши CTRL + SHIFT + ENTER.

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

После заполнения таблицы можно приступать к решению задачи. Для этого используем функцию «Решатель». Запускаем программу Сервис-Решатель… И настраиваем ее.

Целевая ячейка «Итог» ($H$21).

Результат ставим на «Минимум».

Изменяя ячейки – выбираем диапазон «Матрица перевозок» ($C$14:$F$16).

Выставляем Ограничительные условия:

Ссылка на ячейку «Фактически реализовано» ($H$14:$H$16), операция <=, значение «Предложение поставщиков» ($H$6:$H$8).

Ссылка на ячейку «Фактически получено» ($C$18:$F$18), операция >=, значение «спрос потребителей» ($C$10:$F$10).

Ссылка на ячейку «Фактически реализовано» ($H$14:$H$16), операция >=, значение 0.

Решатель

Opens the Solver dialog. A solver allows you to solve mathematical problems with multiple unknown variables and a set of constraints on the variables by goal-seeking methods.

Доступ к этой команде

Choose Tools — Solver .

Solver settings

The dialog settings are retained until you close the current document.

Target Cell

Enter or click the cell reference of the target cell. This field takes the address of the cell whose value is to be optimized.

Optimize results to

Maximum: Try to solve the equation for a maximum value of the target cell.

Minimum: Try to solve the equation for a minimum value of the target cell.

Value of: Try to solve the equation to approach a given value of the target cell.

Enter the value or a cell reference in the text field.

By Changing Cells

Enter the cell range that can be changed. These are the variables of the equations.

Limiting Conditions

Add the set of constraints for the mathematical problem. Each constraint is represented by a cell reference (a variable), an operator, and a value.

Cell reference: Enter a cell reference of the variable.

Click the Shrink button to shrink or restore the dialog. You can click or select cells in the sheet. You can enter a cell reference manually in the input box.

Operator: Select an operator from the list. Use Binary operator to restrict your variable to 0 or 1. Use the Integer operator to restrict your variable to take only integer values (no decimal part).

Value: Enter a value or a cell reference. This field is ignored when the operator is Binary or Integer.

Remove button: Click to remove the row from the list. Any rows from below this row move up.

You can set multiple conditions for a variable. For example, a variable in cell A1 that must be an integer less than 10. In that case, set two limiting conditions for A1.

Options

The Solver Options dialog let you select the different solver algorithms for either linear and non-linear problems and set their solving parameters.

Solve

Click to solve the problem with the current settings. The dialog settings are retained until you close the current document.

Решение уравнений с помощью решателя

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

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

Можно определить ряд условий, устанавливающих ограничения для некоторых ячеек. Например, можно установить следующее ограничение: одна из переменных или ячеек не должна быть больше другой переменной или определенного значения. Также можно ввести следующее ограничение: одна или более переменные должны быть целыми числами (значения без знаков после запятой) или двоичными числами (разрешены только значения 1 и 0).

Using Non-Linear solvers

Regardless whether you use DEPS or SCO, you start by going to Tools — Solver and set the Cell to be optimized, the direction to go (minimization, maximization) and the cells to be modified to reach the goal. Then you go to the Options and specify the solver to be used and if necessary adjust the according parameters.

There is also a list of constraints you can use to restrict the possible range of solutions or to penalize certain conditions. However, in case of the evolutionary solvers DEPS and SCO, these constraints are also used to specify bounds on the variables of the problem. Due to the random nature of the algorithms, it is highly recommended to do so and give upper (and in case «Assume Non-Negative Variables» is turned off also lower) bounds for all variables. They don’t have to be near the actual solution (which is probably unknown) but should give a rough indication of the expected size (0 ≤ var ≤ 1 or maybe -1000000 ≤ var ≤ 1000000).

Bounds are specified by selecting one or more variables (as range) on the left side and entering a numerical value (not a cell or a formula) on the right side. That way you can also choose one or more variables to be Integer or Binary only.

В open office calc: сервис / поиск решения.

Цель работы: Изучение возможностей пакета Ms Excel при решении задач линейного программирования. Приобретение навыков решения задач линейного программирования.

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

В задачи целочисленного программирования добавляется ограничение, что всеxi должны быть целыми.

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

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

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

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

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

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

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

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

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

2. Пример.Решить задачу линейного программирования:

Пусть значения x1, x2, x3, x4 хранятся в ячейках A1:A4, a значение функции L — в ячейке С1 = =5*A1-2*A3.

С2 = -5*A1 — A2 + 2*A3
С3 = -А1 +А3 + А4
С4 = -3*А1 + 5*А4.

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

Выполним команду из главного вкладка «Данные»Поиск решения (рис. 6.1).

В Open Office Calc: Сервис / Поиск решения.

Назначение основных кнопок и окон диалогового окна Поиск решения:

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

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

Рис. 6.1

Устремим целевую функцию в ячейке C1 к минимуму. Для этого введем в поле Установить целевую функцию значение С1 и установим опцию равной минимальному значению.

В поле Изменяя ячейки необходимо указать адреса ячеек, в которых хранятся изменяемые значения. В нашем случае это ячейки А1:А4.

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

Рис. 6.2

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

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

Щелчок по кнопке Добавить вводить очередное ограничение, находясь в окне Добавить ограничение.

В нашем случае окно будет иметь вид, изображенный на рис. 6.3. Щелчок по кнопке Выполнить начнет процесс решения задачи, завершится который появлением диалогового окна, изображенного на рис. 6.4.

Рис. 6.3

Рис. 6.4

Щелчок по кнопке OK приведет к появлению в ячейке С1 значения целевой функции L, а в ячейках A1:A4 — значений переменных x1-x4, при которых целевая функция достигает минимального значения.

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

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

Увеличение погрешности вычислений

В Open Office Calc: