Поиск решения в Экселе

Включение функции

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

Чтобы включить Поиск решений в Microsoft Excel 2010 и более поздних версиях, перейдите на вкладку «Файл». Для версии 2007 года вы должны нажать на кнопку Microsoft Office в верхнем левом углу окна. В открывшемся окне перейдите в раздел «Настройки».

В окне параметров нажмите «Дополнения». После перехода в нижней части окна напротив параметра «Управление» выберите значение «Надстройка Excel» и нажмите кнопку «Перейти».

Открывается дополнительное окно. Ставим галочку напротив названия нужной нам надстройки — «Поиск решения». Нажмите на кнопку «ОК».

После этого на ленте Excel во вкладке «Данные» появится кнопка для запуска функции «Найти решения».

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

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

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

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

Читайте также: Аппроксимация функции одной переменной методом наименьших квадратов с дополнительными условиями

Основные параметры поиска решений

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

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

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

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

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

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

Пример использования поиска решений

Теперь перейдем к самой функции.

1) Выполните следующие действия, чтобы включить «Поиск решений:

• нажмите «Параметры Excel», а затем выберите вкладку «Надстройки»;
• в поле «Управление» выберите значение «Надстройка Excel» и нажмите кнопку «Перейти»;
• в поле «Доступные надстройки» установите флажок «Поиск решения» и нажмите «ОК.

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

Не забудьте ввести формулы. Стоимость заказа рассчитывается как «Оплата за 1 изделие» умножается на «Количество переданных в работу заготовок». Чтобы узнать «Время выполнения заказа», нужно разделить «Количество переданных в работу заготовок» на «Производительность».

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

4) Заполните параметры «Поиск решения» и нажмите «Найти решение».

Общая стоимость 1000 изделий рассчитывается как сумма затрат количества изделий у каждого работника. Эта ячейка (E13) является целевой функцией. D9:D12 — сменные ячейки. «Поиск решений» определяет их оптимальные значения такие, чтобы целевая функция достигала минимума при заданных ограничениях.

В нашем примере действуют следующие ограничения:

• общее количество предметов 1000 штук ($D$13 = $D$3);
• количество пустых полей, переданных в работу, является целым числом и больше нуля или равно нулю ($D$9:$D$12 = целое число, $D$9:$D$12 > = 0);
• количество дней меньше или равно 30 ($F$9:$F$12 <= $D$6, или как в примере в ячейке F13 установите функцию MAX(F9:F12) и установите лимит $F$13 <= $D$6).

5) Наконец, проверьте полученные данные на соответствие заданному целевому значению. Если что-то не сходится, нужно пересмотреть исходные данные, указанные формулы и ограничения.

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

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

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

Задача линейного программирования в общем случае формулируется следующим образом:

Определить максимум (минимум) целевой функции Fmax(min) для заданной системы ограничений (2) и граничных условий (3):

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

Целевая ячейка — это ячейка, в которой вы хотите найти максимальное, минимальное или заданное значение.

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

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

Для запуска процедуры поиска разрешения необходимо:

1. В меню «Данные» выберите «Найти решение». Откроется диалоговое окно Поиск решения (рис. 12 рис. 12.11).

Рис. 12.1. Диалоговое окно Поиск решения

1. В поле «Введите целевую ячейку» введите ссылку на ячейку, в которой вы хотите получить максимальное, минимальное или заданное значение.
2. В поле Изменить ячейки введите ссылки на изменяемые ячейки. (Если вы нажмете кнопку «Принять», Solver самостоятельно решит, какие ячейки изменить).
3. Чтобы установить ограничения, нажмите кнопку Добавить.
4. В открывшемся диалоговом окне необходимо: (Рис. 12.2 Рис. 12.2)

• в поле Ссылка на ячейку введите ссылку на ячейку, содержащую формулу, определяющую ограничение; формула должна прямо или косвенно зависеть от одной или нескольких заменяемых ячеек;
• во втором поле выберите оператор ограничения (>,<,= и т д);
• в поле Лимит введите значение лимита.

1. Чтобы установить следующее ограничение, нажмите кнопку «Добавить» и повторите шаг 5.
2. Когда все ограничения установлены, нажмите OK, чтобы вернуться в диалоговое окно «Найти решение.

Рис. 12.2 диалоговое окно «Добавить ограничение

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

Если известно, что решаемая задача является линейной (то есть зависимости между переменными линейны), следует активировать режим линейной модели: процесс решения значительно ускорится.

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

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

После завершения процедуры решения в диалоговом окне «Результаты решения» можно выполнить одно из следующих действий:

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

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

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

Чтобы загрузить модель позже, нажмите кнопку «Загрузить модель» в диалоговом окне «Параметры поиска решений». (Диалоговое окно «Параметры поиска решений» открывается при нажатии кнопки «Параметры» в диалоговом окне «Инструменты» > «Поиск решений).

Найденные решения (изменяющиеся значения ячеек) можно сохранить в виде сценария. Для этого вам нужно:

1. В диалоговом окне «Результаты поиска решения» выберите «Сохранить сценарий.
2. В поле Имя сценария введите имя сценария. Вы можете просмотреть сценарии, выбрав «Данные» > «Инструменты данных» > «Анализ «что, если»» > «Управление сценариями» > «Сценарии.

Solve Finder может генерировать три типа отчетов на основе результатов успешного завершения процедуры решения.

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

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

Типы отчетов:

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

Рассмотрите возможность использования процессора Excel для решения LLP на примерах.

Задача 1. Планирование производства

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

МП производит товаров х1, х2, х3, х4 и получает от продажи каждого соответственно прибыль 60, 70, 120, 130 руб. Затраты на производство приведены в таблице.

Работа	1	1	1	1	16
Товар	6	5	4	1	110
Финансы	4	6	10	1. 3	100

Определение:

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

решение проблемы с помощью Excel состоит из 4 шагов:

1. Создание математической модели задачи ЛП.
2. Создать форму для ввода условий задачи, ввести в нее исходные данные и зависимости от математической модели.
3. Ввод данных из формы в окне Excel Поиск решения в меню «Данные.
4. Задайте параметры поиска и решите задачу.

Создание математической модели задачи

Создадим математическую модель процесса по описанию задачи:

функция целевой прибыли.

Ограничения модели:

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

Чтобы решить эту проблему, используя MS Excel, мы создадим новую рабочую книгу под названием «Линейное программирование» и переименуем первый рабочий лист в «Производственная задача.

Создание формы

• Скомпилируйте такую ​​форму:

1	Переменная	х7	х2	х3	х4	Формула	В разводе	Святой член
2	Важность
3	Коэф. CF	60	70	120	130	=СУММПРОИЗВ(B$2:E$2;B3:E3)	Макс
4	Работа	1	1	1	1	=СУММПРОИЗВ(B$2:E$2;B4:E4)		16
5	Товар	6	5	4	1	=СУММПРОИЗВ(B$2:E$2;B5:E5)		110
6	Финансы	4	6	10	1. 3	=СУММПРОИЗВ(B$2:E$2;B6:E6)		100

• Регистрация в ячейках B3:E3 коэффициентов целевой функции F (1), в ячейках B4:E6 коэффициентов из системы ограничений (2) и в ячейках H4:H6 — свободных членов из системы (2).
• Ввод формул с fx — мастер функций.

Чтобы ввести формулу в целевую ячейку (целевую функцию): щелкните левой кнопкой мыши по ячейке F3, затем по значку fx Function Wizard на панели инструментов, в появившемся окне «Function Wizard Step 1» выберите вкладку «Math», выберите функцию СУММПРОИЗВ, нажмите клавишу OK, в окне «Мастер функций, шаг 2» в поле Массив 1 введите B2: E2 (ячейки, где x1..x4 будут варьироваться), в поле Массив 2 введите B3: E3 (коэффициенты целевой функции к цифровому фильтру).

Примечание. Вы можете ввести B2:E2 не с клавиатуры, а поместить курсор в окно Array 1, затем провести курсором с нажатой левой кнопкой мыши по ячейкам B2:E2, названия ячеек будут введены в самом окне. Сделайте то же самое с полем Массив 2.

Нажмите клавишу ОК, формула 60×1+70×2+120×3+130×4 будет записана в ячейку F3 в виде СУММПРОИЗВ (B2:E2; B3:E3).

Чтобы не вводить формулы в другие ячейки, измените тип адресации ячеек B2:E2 с относительной на абсолютную $B$2:$E$2, поместив курсор перед нужным адресом B2 и нажав функциональную клавишу F4, затем повторите эти шаги для адреса E2. Формула будет иметь следующий вид:

СУММПРОИЗВ($B$2:$E$2,B3:E3)

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

В результате копирования мы увидим следующие формулы:

• в ячейке F4 — СУММПРОИЗВ($B$2:$E$2;B4:E4),
• в ячейке F5 — СУММПРОИЗВ($B$2:$E$2;B5:E5),
• в ячейке F6 — СУММПРОИЗВ($B$2:$E$2;B6:E6).

Ограничение параметров при поиске решений

Допустим, вы пришли в банк с этой таблицей, но банк отказывается повышать вам процентную ставку. В таких случаях нам нужно выяснить, насколько мы хотим увеличить сумму ежегодных инвестиций. Нам нужно установить границу ячейки с одним значением переменной. Но прежде чем начать, измените значения в ячейках переменных на исходные: в B1 на 5% и в B2 на -1000$. А теперь делаем следующее:

1. Перейдите в ячейку B14 и выберите инструмент: «Данные» — «Анализ» — «Поиск решения».
2. На противоположной стороне списка параметров: «В соответствии с ограничениями» нажмите кнопку «Добавить».
3. В появившемся окне «Добавить ограничение» заполните поля, как указано выше на рисунке. И нажмите ОК.
4. Заполните параметры и поля в появившемся диалоговом окне, как и в предыдущем примере:
5. Нажмите «Найти решение».

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