Подбор параметров в Excel 2010

Где находится «Подбор параметра» в Excel

Известен результат некой формулы. Имеются также входные данные. Кроме одного. Неизвестное входное значение мы и будем искать. Рассмотрим функцию «Подбора параметров» в Excel на примере.

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

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

Когда условия задачи записаны, переходим на вкладку «Данные». «Работа с данными» — «Анализ «Что-Если»» — «Подбор параметра».

В поле «Установить в ячейке» задаем ссылку на ячейку с расчетной формулой (B4). Поле «Значение» предназначено для введения желаемого результата формулы. В нашем примере это сумма ежемесячных платежей. Допустим, -5 000 (чтобы формула работала правильно, ставим знак «минус», ведь эти деньги будут отдаваться). В поле «Изменяя значение ячейки» — абсолютная ссылка на ячейку с искомым параметром ($B$3).

После нажатия ОК на экране появится окно результата.

Чтобы сохранить, нажимаем ОК или ВВОД.

Функция «Подбор параметра» изменяет значение в ячейке В3 до тех пор, пока не получит заданный пользователем результат формулы, записанной в ячейке В4. Команда выдает только одно решение задачи.



Примеры подбора параметра в Excel

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

Пример 1. Метод подбора начальной суммы инвестиций (вклада).

Известные параметры:

  • срок – 10 лет;
  • доходность – 10%;
  • коэффициент наращения – расчетная величина;
  • сумма выплат в конце срока – желаемая цифра (500 000 рублей).

Внесем входные данные в таблицу:

Начальные инвестиции – искомая величина. В ячейке В4 (коэффициент наращения) – формула =(1+B3)^B2.

Вызываем окно команды «Подбор параметра». Заполняем поля:

После выполнения команды Excel выдает результат:

Чтобы через 10 лет получить 500 000 рублей при 10% годовых, требуется внести 192 772 рубля.

Пример 2. Рассчитаем возможную прибавку к пенсии по старости за счет участия в государственной программе софинансирования.

Входные данные:

  • ежемесячные отчисления – 1000 руб.;
  • период уплаты дополнительных страховых взносов – расчетная величина (пенсионный возраст (в примере – для мужчины) минус возраст участника программы на момент вступления);
  • пенсионные накопления – расчетная величина (накопленная за период участником сумма, увеличенная государством в 2 раза);
  • ожидаемый период выплаты трудовой пенсии – 228 мес.;
  • желаемая прибавка к пенсии – 2000 руб.

С какого возраста необходимо уплачивать по 1000 рублей в качестве дополнительных страховых взносов, чтобы получить прибавку к пенсии в 2000 рублей:

  1. Ячейка с формулой расчета прибавки к пенсии активна – вызываем команду «Подбор параметра». Заполняем поля в открывшемся меню.
  2. Нажимаем ОК – получаем результат подбора.

Чтобы получить прибавку в 2000 руб., необходимо ежемесячно переводить на накопительную часть пенсии по 1000 рублей с 41 года.

Функция «Подбор параметра» работает правильно, если:

  • значение желаемого результата выражено формулой;
  • все формулы написаны полностью и без ошибок.

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

Поиск решения осуществляется в Excel методом итераций (от лат. iteratio – повторение, последовательное приближение). Порядок работы при этом следующий:

1) в изменяемой ячейке задать начальное (приближенное) значение искомого параметра;

2) в целевой ячейке ввести формулу для расчета результата;

3) выполнить команду меню Сервис→Подбор параметра, ввести в открывшемся окне необходимые данные и нажать OK.

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

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

Пример 1. Для перевозки груза весом 400 т был подан состав из 5 крытых вагонов и 7 платформ. Каков должен быть вес груза в вагоне каждого типа, если на погрузку установлены следующие ограничения: вес груза в вагонах одного типа должен быть одинаковым, а вес груза на платформе в 2 раза больше, чем в крытом вагоне?

Введем на листе Excel исходные данные и соответствующие формулы (рис. 1). Затем установим курсор в целевую ячейку (ячейку с результирующей формулой) C4 и выполним команду Сервис→Подбор параметра, в открывшемся окне введем необходимые данные (рис. 2). После нажатия на кнопку OK в ячейках C2, C3 и C4 получаем результат (рис. 3).

а б

Рис. 1. Вид рабочего листа Excel для примера 1
(а – обычный режим просмотра, б – режим показа формул)

Рис. 2. Вид окна Подбор параметра для примера 1

Рис. 3. Вид окна Результат подбора параметра для примера 1

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

Общие рекомендации по нахождению корней уравнений можно сформулировать следующим образом:

1) построить таблицу значений или график функции и определить, на каком интервале изменения аргумента функция меняет знак;

2) определить начальное (приближенное) значение искомого корня уравнения и задать его во входной (изменяемой) ячейке;

3) ввести решаемое уравнение (формулу) в целевую ячейку (т. е. ячейку результата);

4) ввести необходимые данные в меню Сервис→Подбор параметра.

Пример 2. Рассмотрим пример решения трансцендентного (т. е. содержащего неалгебраические функции) уравнения x3=ex.

Для удобства преобразуем это уравнение к виду x3 – ex= 0.

Для определения начального приближения корня протабулируем функцию и построим ее график, например, на отрезке (рис. 4).

Из таблицы значений функции и графика следует, что функция меняет знак на отрезке xЄ, значит, на этом отрезке находится корень функции. Для уточнения его значения выберем в качестве начального приближения корня любую границу этого отрезка, например, 1,5. Введем это значение в ячейку A14. Для удобства присвоим ей имя x. В ячейку B14 введем формулу – левую часть решаемого уравнения, т. е. =x^3-EXP(x). Затем, находясь в этой ячейке, выполним команду Сервис→Подбор параметра и в диалоговом окне зададим необходимые параметры (см. рис. 4). При этом в окошке «Значение» вводится правая часть решаемого уравнения (в нашем случае 0).

Рис. 4. Вычисление корня уравнения

Установки в окне «Подбор параметра» на рис. 4 поручают Excel перебирать значения в ячейке x (A14) до тех пор, пока зависящее от x выражение, записанное в ячейке B14, не достигнет заданного значения 0.

По окончании процесса поиска получим значение корня x=1,85694 (рис. 5).

Рис. 5. Вид окна Результат подбора параметра для примера 2

Второй пример использования подбора параметра для уравнений

Немного усложним задачу. На этот раз формула выглядит следующим образом:

x2=4

Решение:

  1. Заполните ячейку B2 формулой как показано на рисунке:
  2. Выберите встроенный инструмент: «Данные»-«Работа с данными»-«Анализ что если»-«Подбор параметра» и снова заполните его параметрами как на рисунке (в этот раз значение 4):
  3. Сравните 2 результата вычисления:

Обратите внимание! В первом примере мы получили максимально точный результат, а во втором – максимально приближенный.

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

x=(7-1)/2

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

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

По умолчанию инструмент выполняет 100 повторений (итераций) с точностью 0.001. Если нужно увеличить количество повторений или повысить точность вычисления измените настройки: «Файл»-«Параметры»-«Формулы»-«Параметры вычислений»:

Таким образом, если нас не устраивает результат вычислений, можно:

  1. Увеличить в настройках параметр предельного числа итераций.
  2. Изменить относительную погрешность.
  3. В ячейке переменной (как во втором примере, A3) ввести приблизительное значение для быстрого поиска решения. Если же ячейка будет пуста, то Excel начнет с любого числа (рандомно).

Используя эти способы настроек можно существенно облегчить и ускорить процесс поиска максимально точного решения.

О подборе нескольких параметров в Excel узнаем из примеров следующего урока.

все уроки

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

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

Примером может служить решение квадратного уравнения, которое в общем виде задается выражением «Y(x) = ax2 + bx + c».

Для того, чтобы построенная электронная таблица позволяла бы находить решения подобных уравнений с любыми коэффициентами, лучше вынести коэффициенты в отдельные ячейки, а в формулах для вычисления значений функции использовать ссылки на эти ячейки. Впрочем, это дело вкуса. Можно при составлении формулы использовать значения коэффициентов, а не ссылки на них.
Чтобы оценить примерные границы отрезков и количество корней, можно использовать табличное задание значений функции, т.е. задать несколько значений переменной и вычислить соответствующие значения функции. Опять же, для того, чтобы можно было моделировать расчеты для квадратных уравнений с различными коэффициентами, шаг табулирования лучше задать в отдельной ячейке. Начальное значение переменной можно будет изменять путем ввода в ячейку «А6». Для вычисления следующего значения в ячейку «А7» введена формула «=А6+$B$4», т.е. использована абсолютная ссылка на ячейку с шагом табулирования.
Далее с помощью маркера заполнения формируется ряд формул для вычисления последующих значений переменной, в приведенном примере используется 20 значений.
Вводится формула для вычисления значения функции (для рассматриваемого примера в ячейку «В6») и формируется ряд аналогичных формул для остальных ячеек. В формуле использованы абсолютные ссылки на ячейки с коэффициентами уравнения.
По построенной таблице строится точечная диаграмма.
Если начальное значение Х и шаг выбраны неудачно, и на диаграмме нет пересечений с осью абсцисс, то можно ввести другие значения и добиться нужного результата.
Можно было бы найти решение уже на этом шаге, но для этого понадобилось бы гораздо больше ячеек и шаг, равный заданной точности вычислений (0,001). Чтобы не создавать громоздких таблиц, далее используется «Подбор параметра» из группы «Прогноз» на вкладке «Данные». Предварительно необходимо выделить место под начальные значения переменной (корней в примере два) и соответствующие значения функции. В качестве «х1» выбирается первое из значений, дающих наиболее близкое к нулю значение функции (в примере 0,5). В ячейку L6 введена формула для вычисления функции. В окне подбора параметра необходимо указать для какой ячейки (L6), какое значение (0) нужно получить, и в какой ячейке для этого изменять значения (К6).
Для поиска второго корня необходимо ввести второе из значений, дающих наиболее близкое к нулю значение функции (в примере 9,5), и повторить подбор параметра для ячейки L9 (в ячейку скопирована формула из ячейки L6).
Предложенное оформление коэффициентов функции в отдельные ячейки позволяет без изменения формул решать другие подобные уравнения.
Подбор параметра имеется и в более ранних версиях программы.

Добавить комментарий

Закрыть меню