Использование средства Подбор параметра для получения требуемого результата

Основные понятия

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

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

 

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

Известно:

· сколько денег вам требуется,

· на какой срок необходимо их занять

· сколько вы можете платить каждый месяц.

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

В этом случае для определения приемлемой процентной ставки можно воспользоваться средством Подбор параметра.

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

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

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

Рассмотрим простой пример. Нам необходимо выяснить, какую заработную плату нужно начислить Иванову И.И., чтобы на руки он получил 42000 рублей (Рисунок 9.12)

Рисунок 9.12. Пример исходной таблицы

В ячейке С3 введена формула: =В3-В3*$ E $3.

Формула ссылается на две ячейки: В3 и E 3. При этомячейка E 3 содержит значение налоговой ставки, которое не может быть изменено. Следовательно, для получения нужного результата в ячейке С3 мы можем изменять только содержимое ячейки В3.

Вопрос состоит в следующем: какое значение в ячейке В3 приведет к результату 42000 в ячейке С3?

Для ответа на этот вопрос выполните следующие действия:

1. На вкладке Данные в группе Работа с данными нажмите на кнопку Анализ «что-если» и выберите команду Подбор параметра.

2. В окне диалога Подбор параметра задайте следующие значения (Рисунок 9.13):

a. В поле Установить в ячейке: укажите адрес результирующей ячейки, содержащей расчетную формулу. В нашем случае это ячейка С3.

b. В поле Значение: введите величину, которую вы хотите получить в качестве результата вычисления формулы. В нашем случае это 42000.

c. В поле Изменяя значение ячейки: введите адрес той ячейки, на которую ссылается результирующая ячейка и значение в которой должно быть изменено для достижения необходимого результата. В нашем случае это ячейка B3.

3. Нажмите кнопку ОК.

Рисунок 9.13.  Подбор параметра для ячейки С3

4. MS Excel отобразит окно Результат подбора параметра содержащее отчет о выполненной работе (Рисунок 9.14). При этом в исходной таблице будет изменено значение ячейки-параметра. Если вас устраивает результат работы MS Excel - нажмите кнопку ОК. Если вы хотите вернуться к исходному значению – нажмите кнопку Отмена.

Рисунок 9.14. Результат подбора параметра