Подбор параметра является частью блока задач, который иногда называют инструментами анализа "что-если". Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата, можно воспользоваться средством Подбор параметра выбрав одноименную команду в меню Сервис. При подборе параметра Microsoft Excel изменяет значение в одной конкретной ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвращает нужный результат.
Средство Подбор параметра позволяет получить требуемое значение в определенной ячейке, которую называют целевой, путем изменения значения (параметра) другой ячейки, которую называют влияющей. При этом целевая ячейка обязательно должна прямо или косвенно ссылаться на ячейку с изменяемым значением.
Математическая суть задачи состоит в решении уравнения f(x) = а, где функция f(x) описывается заданной формулой, х — искомый параметр, а — требуемый результат формулы.
При выполнении этой операции следует иметь в виду, что:
· подбор параметра может выполняться только для ячейки, содержащей формулу;
· ячейка, которая будет изменяться при подборе, должна, наоборот, содержать значение, а неформулу.
Для решения какой-либо задачи с использованием Подбора параметра необходимо выполнить следующие действия:
1. Выделить ячейку, содержащую формулу, для которой нужно подобрать такое значение одного из аргументов этой формулы, при котором значение, вычисленное по формуле, будет соответствовать заданному вами (в поле Значение диалогового окна Подбор параметра см пункты 2, 3 и 4).
2. Выполнить команду Сервис ► Подбор параметра.
Рис. 2. Диалоговое окно Подбор параметра
3. В открывшемся окне диалога Подбор параметра (рис. 2) в поле Установить в ячейке ввести ссылку на ячейку, содержащую формулу (по умолчанию в это поле вводится адрес текущей ячейки).
4. В поле Значение ввести значение, которое нужно получить по заданной формуле.
5. В поле Изменяя ячейку ввести ссылку на ячейку, содержащую значение изменяемого (искомого) параметра. Нажать кнопку ОК.
Пример. Дано уравнение y = х2 + 3х - 2
где y — требуемый результат формулы; x — искомый параметр.
Определить такое значение параметра x, при котором y будет равно 20.
1. Введите в свободную ячейку, например, А2 указанную формулу, соблюдая правила Excel для записи формул. В формуле сделайте ссылку на ячейку, в которой условно находится параметр x. Пусть это будет ячейка В2. По правилам Excel формула в ячейке А2 должна иметь вид =B2^2+3*B2-2
2. Выполните команду Сервис ► Подбор параметра.
3. В поле Установить в ячейке укажите А2 (адрес ячейки, содержащей формулу).
4. В поле Значение введите требуемое значение, в данном примере это 20.
5. В поле Изменяя значение ячейки укажите В2 (адрес ячейки, в которой должен находиться параметр x).
После выполнения команды в изменяемой ячейке появится значение параметра x, при котором результат формулы равняется заданной величине.
Подбор параметра можно выполнять графически, перетаскивая точки данных на диаграмме. Для этого сначала нужно построить график функции y = х2 + 3х – 2.
Удалите данные из ячейки В2. Заполните диапазон ячеек В2:В12 данными как показано на рис. 4. С помощью маркера автозаполнения скопируйте формулу из ячейки А2 в нижележащие ячейки до А12 включительно.
По данным диапазона ячеек А1:А12 постройте график с маркерами и введите соответствующий заголовок (y = х2 + 3х – 2).
Рис. 3.
Наведите указатель мыши на точку со значением близким к 20, например, на точку со значением 26. Добейтесь, чтобы указатель мыши приобрел вид вертикальной двухсторонней стрелки (↕) и перетащите маркер данных в диаграмме вниз так чтобы в окне рядом с маркером появилось значение 20 (см. рис. 3).
Как только вы отпустите кнопку мыши появится диалоговое окно Подбор параметра. При этом поля Установить в ячейке и Значение уже будут заполнены соответствующими данными. В поле Изменяя ячейку введите ссылку на ячейку В11, так как данные в ячейке А11 зависят от данных в ячейке В11 (см. рис. 4).
Рис. 4.
После нажатия кнопки ОК откроется окно Результат подбора параметра (рис.5). Текущее значение с подбираемым решением совпадают приближенно в связи с конечной точностью выполнения вычислений.
Рис. 5.
Важно! При подборе параметра результат вычисляется на основе изменения только одной ячейки. Если требуется найти решение путем изменения значений нескольких ячеек, используют надстройку Поиск решения.
Пример. Рассмотрим решение следующей задачи методом подбора параметра.
Предположим, что вас просят дать в долг 10000 руб. и обещают вернуть через год 2000 руб., через два года – 4000 руб., через три года – 7000 руб. при какой годовой процентной ставке эта сделка будет выгодна?
Введите данные в ячейки таблицы Excel как показано ниже на рис. 6.
Рис. 6.
Первоначально в ячейку В7 введите произвольный процент, например 3%.
В ячейку В8 введите формулу =НПЗ(В7;В3:В5)
После этого выполните команду Сервис ► Подбор параметра и заполните открывшееся диалоговое окно Подбор параметра как показано ниже на рис. 7..
Рис. 7 .
В поле Установить в ячейке дайте ссылку на ячейку В8, в которой вычисляется чистый текущий объем вклада. В поле Значение укажите размер ссуды (10000). В поле Изменяя значение ячейки дайте ссылку на ячейку В7, в которой вычисляется годовая процентная ставка. После нажатия кнопки ОК средство подбора параметров определит, при какой годовой процентной ставке чистый текущий объем вклада равен 10000 руб. Результат вычисления выводится в ячейку В7.
Вывод: если банки предлагают большую годовую процентную ставку, то предлагаемая сделка не выгодна.
Задание:
Методом подбора параметра определить при какой годовой процентной ставке выгодно дать в долг на следующих условиях: вас просят дать в долг Р руб. и обещают вернуть через год – Р1 руб. , через два года – Р2 руб. и т.д., наконец, через п лет - Рп. руб. Конкретные данные приведены в таблице 2.
