Подбор параметра

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

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

ПРИМЕЧАНИЕ: Средство подбора параметров работает только с одним входным значением переменной. Если нужно применить несколько входных значений, например сумму кредита и сумму ежемесячного платежа по кредиту, используется надстройка "Поиск решения".

Поиск решения

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

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

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

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

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

3. Задача о назначениях. Имеется несколько должностей и соответствующее количество претендентов на эти должности. Назначение i-го претендента на j-ую должность связано с затратами С[i, j]. Требуется распределить претендентов на должности так, чтобы суммарные затраты были бы минимальными.

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

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

6. Задачи линейной алгебры. С помощью этих же методов можно решать различные системы линейных (и не только линейных) уравнений.

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

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

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

• Кроме того, может быть задано некоторое количество ограничений – условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек. Например, можно потребовать, чтобы общие затраты не превосходили 100 тыс. руб. или чтобы затраты на рекламную кампанию составляли от 10 % до 15 % от общих расходов.

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

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

Данная надстройка становится доступной при установке Microsoft Office или Microsoft Excel. Однако чтобы использовать эту надстройку в Excel, необходимо сначала загрузить ее. Для этого пользователю необходимо выполнить следующие шаги:

1. Откройте вкладку Файл и выберите пункт Параметры.

2. Выберите команду Надстройки, а затем в окне Управление выберите пункт Надстройки Excel.

3. Нажмите кнопку Перейти.

4. В окне Доступные надстройки установите флажок Поиск решения и нажмите кнопку ОК.

• Совет: если Поиск решения отсутствует в списке поля Доступные надстройки, чтобы найти надстройку, нажмите кнопку Обзор.

• Если появится сообщение о том, что надстройка "Поиск решения" не установлена на компьютере, нажмите кнопку Да, чтобы установить ее.

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