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

«Подбор параметра» — ограниченный по функционалу вариант надстройки «Поиск решения». Это часть блока задач инструмента «Анализ «Что-Если»».

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

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

Известен итог некоторой формулы. Имеются также входные данные. Не считая 1-го. Неведомое входное значение мы и будем находить. Разглядим функцию «Подбора характеристик» в 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.

Функция «Подбор параметра» возвращает в качестве поискового результата 1-ое отысканное значение. Вне зависимости от того, сколько уравнение имеет решений.

Если, к примеру, в ячейку Е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 рублей:

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

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

Подбор параметра в Excel — Функция подбора параметра

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

Механизм работы функции

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

Направьте внимание! Действие избранной функции касается только одной ячейки. Соответственно, при попытке отыскать первоначальное значение для остальных окошек придется проводить все деяния поновой по тому же принципу. Потому что функция Excel способна оперировать всего только одним значением, то ее считают опцией с ограниченными способностями.

Индивидуальности внедрения функции: пошаговый обзор с разъяснением на примере карточки продуктов

Чтоб поведать подробнее о том, как работает «Подбор параметра», воспользуемся программкой Microsoft Excel 2016 года. Если у вас установлена наиболее поздняя либо ранешняя версия приложения, в таком случае могут некординально различаться только некие этапы, при всем этом принцип деяния остается таковым же.

1. У нас имеется таблица с списком продуктов, в которой известен лишь процентный показатель скидки. Будем находить стоимость и получившуюся сумму. Для этого перебегаем во вкладку «Данные», в разделе «Прогноз» находим инструмент «Анализ, что, если», кликаем по функции «Подбор параметра».
2. Когда возникло всплывающее окошко, в поле «Установить в ячейке» прописываем подходящий адресок ячейки. В нашем случае это сумма скидки. Чтоб длительно не прописывать его и временами не поменять раскладку клавиатуры, делаем клик по подходящей ячейке. Значение автоматом отобразится в подходящем поле. Напротив поля «Значение» указываем сумму скидки (300 рублей).

1. В поле «Изменение значения ячейки» прописывается тот адресок, в котором планируем выводить первоначальное значение цены на продукт. Подчеркиваем, что это окошко обязано конкретно участвовать в формуле расчетов. Опосля убеждаемся, что все значения проставлены правильно, жмем клавишу «ОК». Для получения начального числа пытайтесь применять ячейку, которая состоит в таблице, так легче будет составлять формулу.
2. В итоге получаем итоговую стоимость продукта с расчетом всех скидок. Программка автоматом рассчитывает необходимое значение и указывает его во всплывающем окошке. Не считая этого, значения продублируются и в таблицу, а конкретно в ту ячейку, которая была выбрана для выполнения расчетов.

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

Решение уравнения при помощи подбора характеристик

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

1. У нас есть уравнение: х+16=32. Нужно осознать, какое число скрывается за неведомым «х». Соответственно, будем отыскивать его при помощи функции «Подбор параметра». Для начала прописываем в ячейку наше уравнение, за ранее поставив символ «=». При этом заместо «х» устанавливаем адресок ячейки, в которой покажется неведомое. В конце введенной формулы символ равенства не ставим, по другому у нас отобразиться «ЛОЖЬ» в ячейке.
2. Перебегаем к запуску функции. Для этого действуем аналогичным образом, как и в предыдущем методе: во вкладке «Данные» находим блок «Прогноз». Тут кликаем на функции «Анализ, что, если», а потом перебегаем к инструменту «Подбор параметра».
3. В показавшемся окне в поле «Установить значение» прописываем адресок той ячейки, в которой у нас обозначено уравнение. Другими словами это окошко «К22». В поле «Значение», в свою очередь, прописываем число, которому равно уравнение – 32. В поле «Изменяя значение ячейки» вводим адресок, куда будет вписываться неведомое. Подтверждаем свое действие нажатием на клавишу «ОК».
4. Опосля нажатия на клавишу «ОК» покажется новое окно, в котором верно прописано, что значение для данного примера найдено. Смотрится это последующим образом:

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

Совет! Но применение функции «Подбор параметра» в Microsoft Excel по отношению к уравнениям нерационально, потому что резвее решить обыкновенные выражения с неведомым без помощи других, а не методом поиска подходящего инструмента в электрической книжке.

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

Функция "Подбор параметра" в "Эксель". Анализ "что если"

Способности программки Excel таковы, что они разрешают решать задачки, которые вручную выполнить нереально либо очень трудно. Это могут быть трудозатратные задачки с перебором большого количества вариантов либо вычисления огромных чисел. Зная способности Excel, решить их не составит труда.

Функция подбора параметра

Примером таковой трудозатратной и фактически нерешаемой вручную задачки является последующая. Для вас понятно, какой итог вычислений нужно получить, какие вычисления к нему приводят, но неведома отправная точка – значение начальных данных. Время от времени можно провести оборотные деяния, а время от времени таковая задачка может поставить в тупик.

You will be interested: How dangerous is the new coronavirus?

Excel дает способ решения таковой задачи, который носит заглавие подбора параметра. Вызов функции находится на вкладке «Данные» панели инструментов «Работа с данными». В версиях, начиная с MS Excel 2007, — «Анализ «что если»», пункт меню «Подбор параметра».

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

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

Расчет суммы займа

Одна из более нужных задач, которую помогает решать этот модуль, — расчет вероятной суммы займа либо банковского кредита, исходя из каждомесячных платежей, срока и процентной ставки. Представим, процентная ставка по кредиту составляет 10%, мы желаем взять средства в долг на 1 год и можем платить 7 тыс. рублей в месяц.

В «Эксель» 2007 есть пригодная функция для расчета каждомесячных платежей по займу с известными процентами и сроком. Она именуется ПЛТ. Синтаксис команды:

ПЛТ(ставка; кпер; пс; [бс]; [тип]), где:

• Ставка – проценты по займу.
• Кпер – число оплат (для годичного кредита в случае каждомесячной оплаты это 12 раз).
• ПС – начальная сумма.
• БС – будущая стоимость (если вы хотят выплатить не всю сумму, а только ее часть, тут указывается, какой долг должен остаться). Это необязательный аргумент, по дефлоту он равен 0.
• Тип – когда делается оплата (выдача денег по какому-нибудь обязательству) – в начале месяца либо в конце. Этот параметр не непременно указывать, если он не заполнен, принимается равным 0, что значит оплату в конце месяца.

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

Внесем на лист «Эксель» 2007 нужные данные. В качестве начальной суммы пропишем пока условные 1 00 000 рублей и займемся нахождением настоящей суммы. Вызываем диалоговое окно подбора параметра. Отправной точкой для нахождения является сумма каждомесячного платежа. Формула ПЛТ возвращает отрицательные данные, так что мы вводим число со знаком «минус»: — 7 000 рублей в поле «Значение». Эту сумму мы должны получить в ячейке с платежом, меняя информацию в поле с займом.

Прописываем все это в окне и запускаем подбор параметра «Эксель». В итоге функция высчитала, какой заем мы можем для себя дозволить — 79 621,56 руб.

Определение процентной ставки

Разглядим сейчас оборотную задачку. Банк выдает ссуду в 100 тыс. рублей на 2 года и желает получить доход в 10 тыс. рублей. Какую минимальную процентную ставку необходимо установить для получения таковой прибыли?

К уже имеющейся на листе инфы добавляем строчку »Прибыль». Она рассчитывается по формуле:

Устанавливаем срок 24 месяца. Направьте внимание на поле «Ставка». Числовое значение обязано выражаться в процентах. Для этого изберите числовой формат «Процент» в Excel: вкладка «Основная» — панель инструментов «Число» — клавиша с изображением процента.

Вызываем функцию подбора и задаем ее аргументы. Ожидаемый итог будет записан в поле «Прибыль» конфигурацией значения в ячейке «Ставка» и составлять 10 000 рублей. Опосля пуска программка указывает нужный процент, равный 9,32354423334073 %.

Подбор нескольких характеристик для поиска рационального результата

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

Проверьте, доступна ли она для использования: вкладка «Данные», панель инструментов «Анализ». Если в программке нет таковой панели либо на ней отсутствует подходящая команда, активируйте ее. Зайдите в характеристики Excel (клавиша Microsoft Office в Excel 2007, меню «Файл» в версиях 2010 и выше) и найдите пункт «Надстройки». Перейдите в управление надстройками и установите флаг на элементе «Поиск решения». Сейчас функция активирована.

Транспортная задачка

Традиционное применение способа – решение транспортной задачки. Представим, компания хранит продукты на нескольких складах и доставляет их в несколько магазинов. Стоит вопросец, с каких складов доставлять продукцию в какие магазины, чтоб расходы на транспортировку были малы.

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

Будем подбирать такие значения поставок, чтоб соблюдались условия:

1) Полные издержки были малы.

2) Суммарные поставки продуктов в торговые точки удовлетворяли требованиям.

3) Суммарный вывоз продукции со складов не превосходил имеющиеся припасы.

4) Количество единиц продукции обязано быть целым и неотрицательным.

Итог поиска решения.

Остальные методы анализа данных

Не считая вышеперечисленных вариантов, есть и остальные способы анализа данных. Они находятся в пт меню «Анализ «что если»». Это «Диспетчер сценариев» и «Таблица данных».

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

Аналогичную функцию делает и «Таблица данных» с той только различием, что все итоги не выводятся в отдельный отчет, а содержатся на том же листе. Это упрощает анализ. Но таблицы обрабатывают лишь два параметра, потому, в случае большего количества входных данных, употребляют сценарии.

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