Функции программы Microsoft Excel: подбор параметра
Очень полезной функцией в программе Microsoft Excel является Подбор параметра. Но, далеко не каждый пользователь знает о возможностях данного инструмента. С его помощью, можно подобрать исходное значение, отталкиваясь от конечного результата, которого нужно достичь. Давайте выясним, как можно использовать функцию подбора параметра в Microsoft Excel.
Суть функции
Если упрощенно говорить о сути функции Подбор параметра, то она заключается в том, что пользователь, может вычислить необходимые исходные данные для достижения конкретного результата. Эта функция похожа на инструмент Поиск решения, но является более упрощенным вариантом. Её можно использовать только в одиночных формулах, то есть для вычисления в каждой отдельной ячейке нужно запускать всякий раз данный инструмент заново. Кроме того, функция подбора параметра может оперировать только одним вводным, и одним искомым значением, что говорит о ней, как об инструменте с ограниченным функционалом.
Применение функции на практике
Для того, чтобы понять, как работает данная функция, лучше всего объяснить её суть на практическом примере. Мы будем объяснять работу инструмента на примере программы Microsoft Excel 2010, но алгоритм действий практически идентичен и в более поздних версиях этой программы, и в версии 2007 года.
Имеем таблицу выплат заработной платы и премии работникам предприятия. Известны только премии работников. Например, премия одного из них — Николаева А. Д, составляет 6035,68 рублей. Также, известно, что премия рассчитывается путем умножения заработной платы на коэффициент 0,28. Нам предстоит найти заработную плату работников.
Для того, чтобы запустить функцию, находясь во вкладке «Данные», жмем на кнопку «Анализ «что если»», которая расположена в блоке инструментов «Работа с данными» на ленте. Появляется меню, в котором нужно выбрать пункт «Подбор параметра…».
После этого, открывается окно подбора параметра. В поле «Установить в ячейке» нужно указать ее адрес, содержащей известные нам конечные данные, под которые мы будем подгонять расчет. В данном случае, это ячейка, где установлена премия работника Николаева. Адрес можно указать вручную, вбив его координаты в соответствующее поле. Если вы затрудняетесь, это сделать, или считаете неудобным, то просто кликните по нужной ячейке, и адрес будет вписан в поле.
В поле «Значение» требуется указать конкретное значение премии. В нашем случае, это будет 6035,68. В поле «Изменяя значения ячейки» вписываем ее адрес, содержащей исходные данные, которые нам нужно рассчитать, то есть сумму зарплаты работника. Это можно сделать теми же способами, о которых мы говорили выше: вбить координаты вручную, или кликнуть по соответствующей ячейке.
Когда все данные окна параметров заполнены, жмем на кнопку «OK».
После этого, совершается расчет, и в ячейки вписываются подобранные значения, о чем сообщает специальное информационное окно.
Подобную операцию можно проделать и для других строк таблицы, если известна величина премии остальных сотрудников предприятия.
Решение уравнений
Кроме того, хотя это и не является профильной возможностью данной функции, её можно использовать для решения уравнений. Правда, инструмент подбора параметра можно с успехом использовать только относительно уравнений с одним неизвестным.
Допустим, имеем уравнение: 15x+18x=46. Записываем его левую часть, как формулу, в одну из ячеек. Как и для любой формулы в Экселе, перед уравнением ставим знак «=». Но, при этом, вместо знака x устанавливаем адрес ячейки, куда будет выводиться результат искомого значения.
В нашем случае, формулу мы запишем в C2, а искомое значение будет выводиться в B2. Таким образом, запись в ячейке C2 будет иметь следующий вид: «=15*B2+18*B2».
Запускаем функцию тем же способом, как было описано выше, то есть, нажав на кнопку «Анализ «что если»» на ленте», и перейдя по пункту «Подбор параметра…».
В открывшемся окне подбора параметра, в поле «Установить в ячейке» указываем адрес, по которому мы записали уравнение (C2). В поле «Значение» вписываем число 45, так как мы помним, что уравнение выглядит следующим образом: 15x+18x=46. В поле «Изменяя значения ячейки» мы указываем адрес, куда будет выводиться значение x, то есть, собственно, решение уравнения (B2). После того, как мы ввели эти данные, жмем на кнопку «OK».
Как видим, программа Microsoft Excel успешно решила уравнение. Значение x будет равно 1,39 в периоде.
Изучив инструмент Подбор параметра, мы выяснили, что это довольно простая, но вместе с тем полезная и удобная функция для поиска неизвестного числа. Её можно использовать как для табличных вычислений, так и для решения уравнений с одним неизвестным. Вместе с тем, по функционалу она уступает более мощному инструменту Поиск решения.
Подбор параметра в Excel и примеры его использования
«Подбор параметра» — ограниченный по функционалу вариант надстройки «Поиск решения». Это часть блока задач инструмента «Анализ «Что-Если»».
В упрощенном виде его назначение можно сформулировать так: найти значения, которые нужно ввести в одиночную формулу, чтобы получить желаемый (известный) результат.
Где находится «Подбор параметра» в Excel
Известен результат некой формулы. Имеются также входные данные. Кроме одного. Неизвестное входное значение мы и будем искать. Рассмотрим функцию «Подбора параметров» в Excel на примере.
Необходимо подобрать процентную ставку по займу, если известна сумма и срок. Заполняем таблицу входными данными.
Процентная ставка неизвестна, поэтому ячейка пустая. Для расчета ежемесячных платежей используем функцию ПЛТ.
Когда условия задачи записаны, переходим на вкладку «Данные». «Работа с данными» — «Анализ «Что-Если»» — «Подбор параметра».
В поле «Установить в ячейке» задаем ссылку на ячейку с расчетной формулой (B4). Поле «Значение» предназначено для введения желаемого результата формулы. В нашем примере это сумма ежемесячных платежей. Допустим, -5 000 (чтобы формула работала правильно, ставим знак «минус», ведь эти деньги будут отдаваться). В поле «Изменяя значение ячейки» — абсолютная ссылка на ячейку с искомым параметром ($B$3).
После нажатия ОК на экране появится окно результата.
Чтобы сохранить, нажимаем ОК или ВВОД.
Функция «Подбор параметра» изменяет значение в ячейке В3 до тех пор, пока не получит заданный пользователем результат формулы, записанной в ячейке В4. Команда выдает только одно решение задачи.
Решение уравнений методом «Подбора параметров» в Excel
Функция «Подбор параметра» идеально подходит для решения уравнений с одним неизвестным. Возьмем для примера выражение: 20 * х – 20 / х = 25. Аргумент х – искомый параметр. Пусть функция поможет решить уравнение подбором параметра и отобразит найденное значение в ячейке Е2.
В ячейку Е3 введем формулу: = 20 * Е2 – 20 / Е2.
А в ячейку Е2 поставим любое число, которое находится в области определения функции. Пусть это будет 2.
Запускам инструмент и заполняем поля:
«Установить в ячейке» — Е3 (ячейка с формулой);
«Значение» — 25 (результат уравнения);
«Изменяя значение ячейки» — $Е$2 (ячейка, назначенная для аргумента х).
Найденный аргумент отобразится в зарезервированной для него ячейке.
Решение уравнения: х = 1,80.
Функция «Подбор параметра» возвращает в качестве результата поиска первое найденное значение. Вне зависимости от того, сколько уравнение имеет решений.
Если, например, в ячейку Е2 мы поставим начальное число -2, то решение будет иным.
Примеры подбора параметра в 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 рублей:
- Ячейка с формулой расчета прибавки к пенсии активна – вызываем команду «Подбор параметра». Заполняем поля в открывшемся меню.
- Нажимаем ОК – получаем результат подбора.
Чтобы получить прибавку в 2000 руб., необходимо ежемесячно переводить на накопительную часть пенсии по 1000 рублей с 41 года.
Подбор параметра, поиск решения и сценарии
На данном уроке мы научимся практически применять вычислительный инструмент «Подбор параметр» в Excel. Специально для Вас подготовлено 3 практических примера, которые могут пригодиться Вам уже сегодня.
ПОДБОР ПАРАМЕТРА ДЛЯ БАНКОВСКИХ ДЕПОЗИТОВ
На протяжении 10-ти лет мы хотим накопить 20 000$. Свои сбережения будем откладывать на банковский депозит по 5% годовых. Деньги будем вносить на банковский депозитный счет ежегодно и одинаковыми частями взносов. Какой должен быть размер ежегодного взноса, чтобы за 10 лет собрать 20 000$ при 5-т и процентах годовых?
Для решения данной задачи в Excel воспользуемся инструментом «Подбор параметра»:
Составьте таблицу как показано на рисунке: 
В ячейку B5 введите функцию: =БС(B1;B2;B3;).
Оставаясь на ячейке B5, выберите инструмент: «Данные»-«Работа с данными»-«Анализ что если»-«Подбор параметра».
В появившемся окне заполните поля, так как на рисунке и нажмите ОК.
Результат вычисления получился с отрицательным числом – это правильно в соответствии со стандартом финансовых функций Excel. Регулярные взносы должны отображаться отрицательным значением, так как это категория расходных операций. А по истечению 10 лет мы получим на приход +20 000$.
Полезный совет! Если Вы нужно узнать размер ежемесячных взносов, тогда перед использованием инструмента «Подбор параметра» нужно процентную ставку разделить на 12 (чтобы перевести в ежемесячный процент).
А количество лет нужно перевести в количество месяцев умножив на 12. Таким образом, в ячейке B3 мы получим необходимую сумму ежемесячного взноса для достижения цели.
ПОИСК РЕШЕНИЙ ПОДБОРОМ ПАРАМЕТРА ПРИ ЦЕНООБРАЗОВАНИИ
Стратегия для построения производственного плана выпуска продукта:
В текущем году продукт должен быть продан в количестве 10 000шт.
Производственные расходы 1-ой штуки: 7,5 руб.
Расходы на реализацию: 450 000 руб.
Какую установить розничную цену, чтобы рентабельность производства сохранялась на уровне 20%?
Рентабельность определяется как соотношение дохода к прибыли (прибыль разделить на доход) и выражается только в процентах!
Снова решим поставленную задачу в Excel с помощью подбора параметра:
Составьте таблицу с исходными данными и формулами, так как указано на рисунке ниже. Обратите внимание! В столбце D указаны, какие именно нужно вводить формулы в соответствующие ячейки столбца B. А в ячейке B1 указана цена 1 руб. чтобы избежать ошибок в формуле B3 и B10 (вероятная ошибка деления на 0). Не забудьте отформатировать все ячейки соответствующим форматам: денежный, общий, процентный.
Перейдите в ячейку B10 и выберите инструмент: «Данные»-«Работа с данными»-«Анализ что если»-«Подбор параметра»
Заполните поля в появившемся диалоговом окне как на рисунке и нажмите ОК.
Как видно розничную цену (B1) нужно устанавливать в 2 раза выше производственных расходов на 1-ну штуку продукции. Только тогда мы сможем удержать рентабельность производства на уровне 20% при таких расходах на реализацию. В реальности бывает и еще хуже.
ПОДБОР ПАРАМЕТРА ДЛЯ БАНКОВСКИХ КРЕДИТОВ
Допустим, Вы хотите приобрести автомобиль в кредит. Максимальная сумма ежемесячного взноса, которую Вы можете себе позволить, составляет 700$. Банк не может выдать Вам кредит сроком более чем на 3 года, с процентной ставкой 5,5% годовых. Можете ли вы себе позволить при таких условиях кредитования приобрести автомобиль стоимостью в 30 000$, а если нет, то на какую сумму можно рассчитывать?
Составьте таблицу условий кредитования в Excel как показано ниже на рисунке. Обратите внимание! Ячейка B4 содержит формулу: =-ПЛТ(B3/12;B2;B1).
Как видно Вы не можете себе позволить такой дорогой автомобиль. Теперь узнаем, какая максимальная стоимость автомобиля соответствует Вашим финансовым возможностям. Для этого перейдите в ячейку B4 и выберите инструмент: «Данные»-«Прогноз»-«Анализ что если»-«Подбор параметра».
Заполните поля в появившемся диалоговом окне как показано выше на рисунке и нажмите ОК.
Как видно максимальная стоимость автомобиля, на которую можно рассчитывать составляет при таких финансовых возможностях и условиях кредитования составляет – 23 1812$.
Внимание! Если срок кредитования определяется количеством месяцев, а не лет, то годовую процентную ставку нужно перевести в месячную. Поэтому в первом аргументе функции ПЛТ стоит значение B3/12 (5,5% годовых разделено на 12 месяцев).
Используя финансовые функции, следует помнить об их стандартах. Например, сумма займа всегда отображаются как отрицательное число. Поэтому перед функцией ПЛТ мы использовали знак минус.
Подбор параметра
Подбор параметра также является частью блока задача анализа «чтоесли». Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата, можно воспользоваться средством «Подбор параметра» на вкладке Данные в группе Работа с данными
.
.При подборе параметра Microsoft Excel изменяет значение в одной конкретной ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвращает нужный результат.
Например, средство «Подбор параметра» можно использовать для изменения процентной ставки в ячейке B3 до тех пор, пока выплаты по кредиту не станутравными 1500,00р (рис. 52).
Рисунок 52. Подбор параметра
Целевая ячейка должна содержать формулу. Изменяемая ячейка-параметр должна прямо или косвенно зависеть от исходной формулы и должна содержать обязательно значение, а не формулу. Влияние не обязательно должно быть непосредственным, т.е. ячейка-параметр может не присутствовать в формуле в качестве аргумента.
Порядок действий по подбору параметра.
Выделить целевую ячейку В4 (рис. 53).
На вкладке Данные в группе Работа с данными 
выберите команду Подбор параметра.
В полеУстановить в ячейке указать целевую ячейку В4.
В полеЗначение установить значение, которое необходимо получить в целевой ячейке 1500
В полеИзменяязначениеячейки указать ссылку на ячейку-параметр В3, ОК.
Диалоговое окно Результат подбора параметра
Если выполняется сложная задача подбора параметра, можно нажать кнопку Пауза и прервать вычисление, затем нажать кнопку Шаг и после просмотра результата вычисления нажать кнопкуПродолжить
ОК – для замены значений рабочего листа в соответствии с новым значением целевой ячейки
Отмена – для сохранения прежних величин.
В результате вычисления получим решение – 15%(рис. 53)
Рисунок 53. Результат подбора параметра
Часто известен желаемый результат формулы, но не известны входные значения для его достижения. Подбором параметра можно варьировать значения в указанной входной ячейке, пока формула, зависящая от этой ячейки, не возвратит ожидаемый результат.
Точность и множественность решений
По умолчанию команда Подбор параметра прекращает вычисления при выполнении 100 итераций или при получении результата в пределах 0,001 от заданного целевого значения. Для достижения большей точности в окне Параметры Excel(рис. 54)на вкладке Формулы- установить флажокВключить итеративные вычисления и установить число итераций и погрешность.
Рисунок 54. Установка точности вычислений
Графический подбор параметра
Excel предоставляет способ подбора параметра с помощью манипулирования графиками.
Пример графического подбора параметра.
Таблица отображает прогнозируемый объем продаж фирмы. Известно, что объем продаж за первый, 2012 год составил 250000 у.е. Его хотят довести до 10000000. Предположительным коэффициентом роста был 1,40. Как видно из таблицы и графика при таком коэффициенте объем продаж составит лишь немногим более 7000000 у.е.
Для нахождения желаемого коэффициента необходимо выделить последний столбик диаграммы и изменить его значение до 10000000 (или насколько возможно).
В появившемся диалоговом окне Подбор параметра ввести в поле Изменяя значение ячейки адрес ячейки B1. Программа вычислит значение нового коэффициента, а диаграмма изменится автоматически (рис. 55)
Рисунок 55. Графический подбор параметра
Купец купил 138 аршин черного и синего сукна за 540 руб. Спрашивается, сколько аршин купил он того и другого, если синее стоило 5 руб. за аршин, а черное 3 руб.? (табл. 28)