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

Зачем нужна функция

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

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

Использование функции

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

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

Итак, алгоритм действий следующий:

1. Переходим на вкладку «Данные», где нажимаем на кнопку «Что насчет анализа» в группе инструментов «Прогноз». В выпадающем списке выберите «Подбор параметров» (в предыдущих версиях кнопка могла находиться в группе «Работа с данными”).
2. На экране появится окно для выбора параметра, который необходимо заполнить:
   • в значение поля «Установить в ячейке» пишем адрес с известными нам конечными данными, т.е это ячейка с суммой скидки. Вместо того, чтобы вводить координаты вручную, вы можете просто нажать на нужную ячейку в самой таблице. При этом курсор должен находиться в соответствующем поле для ввода информации.
   • В качестве значения указываем известную нам сумму скидки — 560 руб.
   • В поле «Изменить значение ячейки» вручную или щелчком мыши указываем координаты ячейки (должны участвовать в формуле расчета суммы скидки), где планируем отображать начальное значение.
   • нажмите OK, когда будете готовы.
3. Программа произведет расчеты и отобразит результат в небольшом окне, которое можно закрыть, нажав кнопку ОК. Также найденные значения автоматически появятся в указанных ячейках таблицы.
4. Точно так же мы можем вычислить цену без скидки для других товаров, если знаем точную сумму скидки для каждого из них.

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

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

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

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

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

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

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

Для сохранения нажмите OK или ENTER.

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

Читайте также: Как быстро посчитать количество символов в ячейке Excel

Решение уравнений методом «Подбора параметров» в Excel

Функция поиска цели идеально подходит для решения уравнений с неизвестным. Например, возьмем выражение: 20 * х — 20 / х = 25. Аргумент х и есть искомый параметр. Пусть функция поможет решить уравнение, выбрав параметр и отобразив найденное значение в ячейке E2.

В ячейку Е3 введите формулу: = 20 * Е2 — 20 / Е2.

А в ячейку E2 помещаем любое число, которое находится в диапазоне определения функции. Пусть будет 2.

Запустите инструмент и заполните поля:

«Вставить в ячейку» — Е3 (ячейка с формулой);

«Значение» — 25 (результат уравнения);

«Изменить значение ячейки» — $E$2 (ячейка, назначенная аргументу x).

Результат функции:

Найденный аргумент появится в отведенной для него ячейке.

Решение уравнения: х = 1,80.

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

Если, например, мы зададим в ячейке E2 начальное число -2, то решение будет другим.

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

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

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

Известные варианты:

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

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

Первая инвестиция является желаемой стоимостью. В ячейке В4 (коэффициент накопления) — формула = (1+В3)^В2.

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

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

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

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

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

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

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

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

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

Функция выбора параметра работает корректно, если:

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

Как использовать Подбор параметра (пример 1):

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

На картинке ниже вы можете видеть, что ваша оценка за первые два задания (тест и письмо) составляет 58, 70, 72 и 60. Хотя мы не знаем, какой будет оценка за последнее задание (тест 3), мы можем напишите формулу, вычисляющую средний балл по всем заданиям сразу. Все, что нам нужно, это вычислить среднее арифметическое всех пяти оценок. Для этого введите выражение =СРЗНАЧ(B2:B6) в ячейку B7. После того, как вы примените Fitting к этой задаче, в ячейке B6 будет отображаться минимальный балл, который вы должны набрать, чтобы поступить в учебное заведение.

1. Выберите ячейку, для которой вы хотите получить значение. Каждый раз, когда вы используете инструмент поиска, вы должны выбрать ячейку, которая уже содержит формулу или функцию. В нашем случае мы выберем ячейку B7, поскольку она содержит формулу =СРЗНАЧ(B2:B6).
2. На вкладке «Данные» выберите «Анализ «что, если»», а затем нажмите «Настройка» в раскрывающемся меню.
3. Появится диалоговое окно с тремя полями:
   • Установить в ячейке — ячейка, содержащая желаемый результат. В нашем случае это ячейка B7, и мы ее уже выделили.
   • Значением является желаемый результат, т.е результат, который должен быть в ячейке B7. В нашем примере мы вводим 70, потому что вы должны набрать минимум 70, чтобы войти.
   • Изменив значение ячейки — ячейка, в которой Excel будет отображать результат. В нашем случае мы выберем ячейку B6, потому что хотим знать оценку, которую хотим получить за последнее задание.
4. После выполнения всех шагов нажмите ОК.
5. Excel рассчитает результат, и в диалоговом окне результатов выбора параметра будет сообщено о решении, если таковое имеется. Нажмите «ОК.
6. Результат отобразится в указанной ячейке. В нашем примере Целевое руководство установило минимальный балл 90 для последней задачи, которую нужно выполнить.

Как использовать Подбор параметра (пример 2):

Давайте представим, что вы планируете мероприятие и хотите пригласить как можно больше гостей, чтобы уложиться в бюджет в 500 долларов. Вы можете использовать Goal Seek, чтобы рассчитать количество гостей, которых вы можете пригласить. В следующем примере ячейка B4 содержит формулу =B1+B2*B3, которая суммирует общую стоимость аренды комнаты и стоимость размещения всех гостей (цена за гостя, умноженная на количество).

1. Выберите ячейку, значение которой вы хотите изменить. В нашем случае мы выберем ячейку B4.
2. На вкладке «Данные» выберите «Анализ «что, если»», а затем нажмите «Настройка» в раскрывающемся меню.
3. Появится диалоговое окно с тремя полями:
   • Установить в ячейке — ячейка, содержащая желаемый результат. В нашем примере ячейка B4 уже выделена.
   • Значение – желаемый результат. Мы ставим 500, так как допустимо потратить 500 долларов.
   • Изменив значение ячейки — ячейка, в которой Excel будет отображать результат. Мы выделим ячейку B3, потому что нам нужно рассчитать количество гостей, которых мы можем пригласить, не превышая наш бюджет в 500 долларов.
4. После выполнения всех шагов нажмите ОК.
5. Диалоговое окно «Результат выбора параметров» сообщит вам, было ли найдено решение. Нажмите «ОК.
6. Результат отобразится в указанной ячейке. В нашем случае Фитинг рассчитал результат 18,62. Поскольку мы подсчитываем количество гостей, наш окончательный ответ должен быть целым числом. Мы можем округлить результат в большую или меньшую сторону. Округляя количество гостей, мы превысим заданный бюджет, а значит остановимся на 18 гостях.

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

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

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

Например, нам нужно решить уравнение: 7x+17x-9x=75.

1. Пишем выражение в пустой ячейке, а символ x заменяем адресом ячейки, значение которой нужно найти. В итоге формула выглядит так: = 7*Д2 + 17*Д2-9*Д2.
2. Нажимаем Enter и получаем результат в виде цифры 0, что вполне логично, так как нам нужно только вычислить значение ячейки D2, которая в нашем уравнении равна «x.
3. Как описано в первой части статьи, во вкладке «Данные» нажмите на кнопку «Что насчет анализа» и выберите «Выбор параметра”.
4. Заполните параметры в появившемся окне:
   • В значении поля «Поместить в ячейку» указываем координаты ячейки, где мы писали уравнение (т.е. B4).
   • В значении, согласно уравнению, пишем число 75.
   • В поле «Изменить значения ячеек» введите координаты ячейки, для которой вы хотите найти значение. В нашем случае это D2.
   • Когда все будет готово, нажмите ОК.
5. Как и в рассмотренном выше примере, будут произведены расчеты и получен результат, на что указывает небольшое окошко.
6. Таким образом, мы смогли решить уравнение и найти значение x, которое оказалось равным числу 5.

Другие типы анализа «что если»

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

• Менеджер скриптов позволяет заменять значения сразу в нескольких ячейках (до 32). Вы можете создать несколько сценариев, а затем сравнить их, не изменяя значения вручную. В следующем примере мы используем сценарии для сравнения нескольких разных мест проведения мероприятия.
• Таблицы данных позволяют взять одну из двух переменных в формуле и заменить ее любым количеством значений, а затем свести результаты в таблицу. Этот инструмент очень мощный, поскольку он отображает сразу много результатов, в отличие от диспетчера сценариев или поиска параметров. В следующем примере показаны 24 возможных исхода ежемесячных платежей по кредиту: