Эксель формула подбор параметра

Уравнения и задачи на подбор параметра в Excel

Часто нам нужно предварительно спрогнозировать, какие будут результаты вычислений при определенных входящих параметрах. Например, если получить кредит на закупку товара в банке с более низкой процентной ставкой, а цену товара немного повысить – существенно ли возрастет прибыль при таких условиях?

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

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

Данный инструмент следует применять для анализа данных с одним неизвестным (или изменяемым) условием. Например:

Решим данную задачу встроенными вычислительными инструментами Excel для анализа данных:

В результате мы получили правильное значение 3.

Получили максимально точный результат: 2*3+1=7

Второй пример использования подбора параметра для уравнений

Немного усложним задачу. На этот раз формула выглядит следующим образом:

Обратите внимание! В первом примере мы получили максимально точный результат, а во втором – максимально приближенный.

Это простые примеры быстрого поиска решений формул с помощью Excel. Сегодня каждый школьник знает, как найти значение x. Например:

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

По умолчанию инструмент выполняет 100 повторений (итераций) с точностью 0.001. Если нужно увеличить количество повторений или повысить точность вычисления измените настройки: «Файл»-«Параметры»-«Формулы»-«Параметры вычислений»:

Таким образом, если нас не устраивает результат вычислений, можно:

Используя эти способы настроек можно существенно облегчить и ускорить процесс поиска максимально точного решения.

О подборе нескольких параметров в Excel узнаем из примеров следующего урока.

Источник

Функции программы Microsoft Excel: подбор параметра

Функции программы 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

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

Местоположение

В широком смысле подбор параметра есть поиск решения уравнения с одной неизвестной при помощи большого количества итераций. Отличие этого инструмента от поиска решения в том, что используется только один аргумент.

Многие пользователи задают вопрос: где находится подбор параметра? Найти эту функцию не сложно. На Панели управления ищете вкладку Данные, затем в блоке Работа с данными нажимаете кнопку Анализ «что-если» и из выпадающего списка выбираете необходимый инструмент.

Использование

Окно подбора имеет несколько полей:

Важно! Отметим несколько моментов, на которые стоит обратить внимание при работе с этим инструментом:

Рассмотрим примеры применения подбора параметра.

Задача 1.

Найти решение уравнения с одной неизвестной 2*x^2 — x/3=12

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

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

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

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

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

Задача 2.

Рассчитать процентную ставку по кредиту в 10000$ сроком на два с половиной года.

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

Чтобы посчитать сумму платежа, воспользуемся встроенной функцией excel – ПЛТ. Она состоит из процентной ставки, периода выплат и величины кредита. Значением процента задаемся произвольно.

Вызываете функцию подбор параметра и заполняете форму, при этом платеж будет составлять 400$. Поскольку это финансовая формула, то не забывайте знак минус.

В итоге получаете следующие результаты:

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

Жми «Нравится» и получай только лучшие посты в Facebook ↓

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

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

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

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

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

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

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

Когда условия задачи записаны, переходим на вкладку «Данные». «Работа с данными» – «Анализ «Что-Если»» – «Подбор параметра».

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

Чтобы сохранить, нажимаем ОК или ВВОД.

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

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

Функция «Подбор параметра» идеально подходит для решения уравнений с одним неизвестным. Возьмем для примера выражение: 20 * х – 20 / х = 25. Аргумент х – искомый параметр. Пусть функция поможет решить уравнение подбором параметра и отобразит найденное значение в ячейке Е2.

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

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

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

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

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

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

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

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

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

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

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

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

Начальные инвестиции – искомая величина. В ячейке В4 (коэффициент наращения) – формула =(1+B3)^B2.

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

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

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

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

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

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

Функция «Подбор параметра» работает правильно, если:

Источник

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

Подбор параметра в MS EXCEL

​Смотрите также​x2=4​ функции ПЛТ стоит​ нажмите ОК.​ 10 лет мы​ Например, имеется уравнение​», а затем​ вычисления формулы. Вместо​ процентная ставка обеспечит​В поле​180​ нужно занять денег.​Подбор параметра подбирает значения​ ПЛТ() или использовать​2. Необходимо найти​ чтобы полученное значение​ ячейку ​Обычно при создании формулы​Решение:​ значение B3/12 (5,5%​Как видно розничную цену​ получим на приход​ 2*a+3*b=x, заданы параметры​ нажмите​

​ этого добавьте формулу​ ваш долг.​Установить в ячейке​. Это число месяцев,​ Вы знаете, сколько​

Простейший пример

​ только для 1​ соответствующую формулу.​ только 1 значение,​ х стало равно целевому​

​ пользователь задает значения​Заполните ячейку B2 формулой​ годовых разделено на​​ (B1) нужно устанавливать​​ +20 000$.​​ а=1, b=2, требуется​​Подбор​ на лист и​Примечание:​введите ссылку на​​ за которое требуется​​ вам нужно, на​

​ параметра. Если Вам​​Введем в ячейку​​ изменяя 1 ячейку.​ X (или примерно равно​ последовательно вводить значения и​ параметров и формула​ как показано на​ 12 месяцев).​ в 2 раза​Полезный совет! Если Вы​

​ найти x (2*1+3*2=8).​параметров.​​ укажите значение платежа​​ Подбор параметров работает только​ ячейку, в которой​​ выплатить ссуду.​​ какой срок и​

​ нужно найти решение​

​B6​ Если требуется найти​ с заданной точностью).​

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

​ от нескольких параметров,​​ориентировочную сумму займа,​ 1 конкретное значение (или​Еще пример. Пусть дана структура​ текущее совпало с​ Например, имеется уравнение​Выберите встроенный инструмент: «Данные»-«Работа​ помнить об их​ на 1-ну штуку​ ежемесячных взносов, тогда​ позволяет решить обратную​Установить в ячейке​ этапе при использовании​ значением переменной. Если​ В данном примере​ Хотя вам известна необходимая​ платить каждый месяц.​ то используйте инструмент​ например 100 000 руб.,​ оптимальное значение), изменяя​ цены договора: Собственные​ Х целевым. Однако,​ 2*a+3*b=x, заданы параметры​ с данными»-«Анализ что​ стандартах. Например, сумма​ продукции. Только тогда​ перед использованием инструмента​ задачу: подобрать такое​введите ссылку на​ средства подбора параметров.​ требуется принять несколько​ это ячейка B4.​ сумма платежа, не​ С помощью средства​ Поиск решения. Точность​ срок на который​ значения в НЕСКОЛЬКИХ​ расходы, Прибыль, НДС.​ часто зависимости в​ а=1, b=2, требуется​ если»-«Подбор параметра» и​ займа всегда отображаются​ мы сможем удержать​ «Подбор параметра» нужно​ значение параметра, при​ ячейку, в которой​Теперь добавьте формулу, результат​

​ входных значений, например​В поле​ вводите ее как​ подбора параметров вы​ подбора параметра можно​ мы хотим взять​ ячейках, то используйте​Известно, что Собственные расходы​ формулах достаточно сложны и​ найти x (2*1+3*2=8).​ снова заполните его​ как отрицательное число.​ рентабельность производства на​ процентную ставку разделить​ котором уравнение возвращает​ находится нужная формула.​ которой вас интересует.​

Калькуляция, подбираем значение прибыли

​ сумма по ссуде​Значение​ значение, поскольку она​

​ можете определить, какая​ задать через меню​ кредит введем в​ Поиск решения.​ составляют 150 000​ без Подбора параметра параметр будет​​ Инструмент Подбор параметра​​ параметрами как на​ Поэтому перед функцией​ уровне 20% при​ на 12 (чтобы​​ желаемый целевой результат​​ В данном примере​ Например, используйте функцию​ и сумма ежемесячного​введите нужный результат​​ получается в результате​​ процентная ставка обеспечит​

​ Кнопка офис/ Параметры​ ячейку​​3. Уравнение должно​​ руб., НДС 18%,​ подобрать сложно.​ позволяет решить обратную​ рисунке (в этот​​ ПЛТ мы использовали​​ таких расходах на​ перевести в ежемесячный​ X. Например, при​

​ это ячейка B4.​ ПЛТ.​ платежа для займа,​ формулы. В данном​ вычисления формулы. Вместо​ ваш долг.​ Excel/ Формулы/ Параметры​B7​ иметь решение, в​ а Целевая стоимость​Примечание​

​ значение параметра, при​

​Сравните 2 результата вычисления:​Часто нам нужно предварительно​
​ бывает и еще​А количество лет нужно​ такое значение параметра​
​Значение​=ПЛТ(B3/12;B2;B1)​ решения. Дополнительные сведения​ Обратите внимание, что​ на лист и​ Подбор параметров работает только​ единственности найденного решения​ кредиту введем в​ является зависимость стоимости​
​ руб. (ячейка​ линейным, т.е. при​ котором уравнение возвращает​Обратите внимание! В первом​ спрогнозировать, какие будут​ хуже.​ перевести в количество​ b, при котором​введите нужный результат​. Эта формула вычисляет​ см. в статье​ число отрицательное, так​ укажите значение платежа​ с одним входным​ Подбор параметра не​ ячейку​ от прибыли. Если​

​С13​​ заданных a и​ желаемый целевой результат​ примере мы получили​ результаты вычислений при​

Подбор суммы кредита

​ средства подбора параметров.​​ несколько входных значений;​​Иными словами, инструмент Подбор​ для расчета суммы​ то положительной прибыли​ Подберем такое значение​ которое ему удовлетворяет.​ такое значение параметра​​ – максимально приближенный.​​ кредит на закупку​ взноса, которую Вы​ мы получим необходимую​​ занятие, поэтому в​​ число отрицательное, так​ 900 ₽. Это значение​Рассмотрим предыдущий пример шаг​Изменяя значение ячейки​​Теперь добавьте формулу, результат​​ Например, в качестве​

​ параметра позволяет сэкономить​ ежемесячного платежа в​ бы у нас​ Прибыли (​

​ ячейку, в которой​ Например, используйте функцию​ и суммы ежемесячного​ сравнению с ручным​B9​​ т.к. расходы больше​​), при котором Стоимость​ для решения таких​ (ответ b=5). Подбирать параметр​​ с помощью Excel.​​ процентной ставкой, а​ не может выдать​​На данном уроке мы​В поле​ определить процентную ставку​ рассчитать ставку процента​ находится корректируемое значение.​

​ В данном примере​В ячейке B4 введите​ используется надстройка «Поиск​Примечание:​Чтобы найти сумму займа​

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

​ например, если решать​​ т.е. значение ячейки​ и создан). Если​ занятие, поэтому в​ знает, как найти​ повысить – существенно​ более чем на​ плана выпуска продукта:​ вычислительный инструмент «Подбор​введите ссылку на​ подбора параметров, а​ достижения цели, используется​ это ячейка B3.​=ПЛТ(B3/12;B2;B1)​ решения». Дополнительные сведения​ Мы стараемся как можно​ соответствующую заданным выплатам​ уравнение x2+4=0, то​ Расхождение (​ пытаться, например, решать​ MS EXCEL имеется​

​ значение x. Например:​ ли возрастет прибыль​ 3 года, с​В текущем году продукт​ параметр» в Excel.​ ячейку, в которой​ для этого требуется​ функция ПЛТ. Функция​Примечание:​. Эта формула вычисляет​ см. в статье​ оперативнее обеспечивать вас​ 1800 руб./мес., делаем​ очевидно, что не​С14​ с помощью Подбора​ инструмент Подбор параметра.​x=(7-1)/2​ при таких условиях?​

​ процентной ставкой 5,5%​

​ должен быть продан​ Специально для Вас​ находится корректируемое значение.​ формула.​ ПЛТ позволяет рассчитать​ Формула в ячейке,​ сумму платежа. В​ определение и устранение​ актуальными справочными материалами​ следующее:​ удастся подобрать такое​) равно 0.​ параметра квадратное уравнение​В MS EXCEL 2007-2010​Excel в своих алгоритмах​При разных поставленных подобных​ годовых. Можете ли​ в количестве 10​ подготовлено 3 практических​

​ В данном примере​​Формула ссылается на ячейки​ сумму ежемесячного платежа.​ указанной в поле​ данном примере вы​ проблемы с помощью​ на вашем языке.​на вкладке Данные в​ х, чтобы x2+4=0​В структуре цены в​ (имеет 2 решения),​ Подбор параметра находится​ инструментов анализа данных​ задачах, результаты вычислений​ вы себе позволить​ 000шт.​

Пошаговый анализ примера

​ примера, которые могут​ это ячейка B3.​

​ B1 и B2,​ В этом примере​Установить в ячейке​ хотите ежемесячно выплачивать​ поиска решения.​ Эта страница переведена​ группе Работа с​Примечание​ ячейке​ то инструмент решение​ на вкладке Данные,​

Подготовка листа

​ использует более простой​

​ могут завесить от​ при таких условиях​Производственные расходы 1-ой штуки:​ пригодиться Вам уже​Примечание:​

​ значения которых вы​ сумма ежемесячного платежа​​, должна ссылаться на​​ 900 ₽. Это значение​

​Рассмотрим предыдущий пример шаг​ автоматически, поэтому ее​​ данными выберите команду​​: В файле примера​

​С9​ найдет, но только​​ группа Работа с​​ метод – подстановки.​

​ одного или нескольких​ кредитования приобрести автомобиль​​ 7,5 руб.​​ сегодня.​

​ Формула в ячейке,​ указали на предыдущих​

​ — это искомая​ ячейку, которую изменяет​​ здесь не вводится,​​ за шагом.​

​ текст может содержать​ Анализ «что-если?», а​​ приведен алгоритм решения​​(Цена продукции) введена​ одно. Причем, он​ данным.​

​ Он подставляет вместо​​ изменяемых условий. В​ стоимостью в 30​Расходы на реализацию: 450​На протяжении 10-ти лет​ указанной в поле​ этапах. Она также​ цель.​ средство подбора параметров.​ поскольку вам нужно​Так как вы хотите​ неточности и грамматические​ затем выберите в​

​ Квадратного уравнения с​ формула Собственные расходы​ найдет, то которое​Найдем значение параметра b​

​ x разные значения​​ зависимости от типа​​ 000$, а если​ 000 руб.​ мы хотим накопить​Установить в ячейке​ ссылается на ячейку​Откройте новый пустой лист.​Нажмите кнопку​ определить процентную ставку​ рассчитать ставку процента​ ошибки. Для нас​ списке пункт Подбор​ использованием Подбора параметра.​

​ + Прибыль (=С7+С8).​ ближе к начальному​ в уравнении 2*а+3*b=x,​ и анализирует, насколько​ прогноза в Excel​ нет, то на​Какую установить розничную цену,​ 20 000$. Свои​, должна ссылаться на​ B3, в которую​Прежде всего добавьте в​ОК​ с помощью средства​ займа, необходимую для​ важно, чтобы эта​ параметра…;​

​Предположим, что нам необходимо​ Стоимость договора (ячейка​ значению (т.е. задавая​ при котором x=21,​ результат вычислений отклоняется​ следует использовать соответствующий​ какую сумму можно​ чтобы рентабельность производства​ сбережения будем откладывать​ ячейку, которую изменяет​

Использование средства подбора параметров для определения процентной ставки

​ средство подбора параметров​​ первый столбец эти​​.​​ подбора параметров, а​​ достижения цели, используется​​ статья была вам​​в поле Установить введите​​ определить максимальную сумму​​С11​

​ разные начальные значения,​​ параметр а=3.​​ от условий указанных​ инструмент для анализа​ рассчитывать?​ сохранялась на уровне​ на банковский депозит​

​ средство подбора параметров.​​ поместит процентную ставку.​​ подписи, чтобы сделать​Выполняется подбор параметров,​ для этого требуется​ функция ПЛТ. Функция​ полезна. Просим вас​ ссылку на ячейку,​ кредита, которую мы​

​) вычисляется как Цена​​ можно найти оба​​Подготовим исходные данные.​ в параметрах инструмента.​ данных.​Составьте таблицу условий кредитования​ 20%?​

​ по 5% годовых.​​Нажмите кнопку​ Формула делит значение​​ данные на листе​​ результат которого показан​ формула.​ ПЛТ позволяет рассчитать​

​ уделить пару секунд​​ содержащую формулу. В​​ можем себе позволить​
​ продукции + НДС​ корня уравнения). Решим​Значения параметров а и​

​ Как только будет,​Данный инструмент следует применять​ в Excel как​Рентабельность определяется как соотношение​

​ Деньги будем вносить​​ОК​​ из ячейки B3​​ понятнее.​​ на рисунке ниже.​​Формула ссылается на ячейки​​ сумму ежемесячного платежа.​

​ достигнут результат вычисления​ для анализа данных​ показано ниже на​ дохода к прибыли​ на банковский депозитный​.​ на 12, поскольку​В ячейку A1 введите​Напоследок отформатируйте целевую ячейку​ B1 и B2,​ В этом примере​ ли она вам,​ это ячейка​ Пусть нам известна​Конечно, можно подобрать значение​ (уравнение имеет 2​ ячейках​ с максимальной точностью,​ с одним неизвестным​

​ рисунке. Обратите внимание!​​ (прибыль разделить на​ счет ежегодно и​Выполняется подбор параметров,​ был указан ежемесячный​ текст​ (B3) так, чтобы​ значения которых вы​ сумма ежемесячного платежа​ с помощью кнопок​B9​ сумма ежемесячного платежа​ вручную, для чего​ решения: x1=1 и​B8 B9​

Пошаговый анализ примера

​ процесс подстановки прекращается.​ (или изменяемым) условием.​

​ Ячейка B4 содержит​ доход) и выражается​ одинаковыми частями взносов.​ результат которого показан​ платеж, а функция​Сумма займа​ результат в ней​ указали на предыдущих​ — это искомая​ внизу страницы. Для​;​

Подготовка листа

​ необходимо уменьшить значение​ x2=-3). Если в​. В ячейке​По умолчанию инструмент выполняет​ Например:​

​ формулу: =-ПЛТ(B3/12;B2;B1).​ только в процентах!​​ Какой должен быть​​ на приведенном ниже​

​ ПЛТ предусматривает использование​.​​ отображался в процентах.​​ этапах. Она также​

​ цель.​ удобства также приводим​​введите искомый результат в​​ руб./мес.), а также​

​ прибыли на величину​ изменяемой ячейке введем​​B10​​ 100 повторений (итераций)​

​2x+1=7​Как видно Вы не​

​Снова решим поставленную задачу​ размер ежегодного взноса,​​рисунке.​​ годовой процентной ставки.​

​В ячейку A2 введите​На вкладке​​ ссылается на ячейку​​Откройте новый пустой лист.​ ссылку на оригинал​ поле Значение. В​

​ B3, в которую​Прежде всего добавьте в​ (на английском языке).​ данном примере он​

​ кредиту (7,02%) и​​ Однако, как говорилось​​ то Подбор параметра​ (т.е. уравнение 2*а+3*b=x). Целевое​ Если нужно увеличить​нам известно значение y,​ такой дорогой автомобиль.​ помощью подбора параметра:​ лет собрать 20​ (B3) так, чтобы​ нет значения, Excel​Срок в месяцах​в группе​ средство подбора параметров​

Использование средства подбора параметров для определения процентной ставки

​ максимальная стоимость автомобиля​ данными и формулами,​

​ и процентах годовых?​ процентах. Выполните одно​​ равной 0 % и​​В ячейку A3 введите​​нажмите кнопку​​ Формула делит значение​ данные на листе​​ вам нужен, но​​ ячейки введите ссылку​

​Процент​​ из ячейки B3​​ понятнее.​ не можете определить​ на ячейку, значение​В EXCEL существует функция​ случае поможет инструмент​

​ чем к 1).​​введенодля информации.​​ вычислений»:​ вычисляемый формулой.​ возможностям. Для этого​ на рисунке ниже.​ в Excel воспользуемся​ действий.​ значениями из данного​

​Процентная ставка​​.​​ на 12, поскольку​В ячейку A1 введите​ входные значения, позволяющие​ которой нужно подобрать.​ ПЛТ() для расчета​

​ Подбор параметра.​​ Если в изменяемой​Выделите ячейку с формулой​​Таким образом, если нас​​Решим данную задачу встроенными​ перейдите в ячейку​ Обратите внимание! В​

​ инструментом «Подбор параметра»:​​В Excel 2016 для​​ примера возвращает сумму​
​.​Чтобы задать количество десятичных​ был указан ежемесячный​
​ текст​

​ его получить, используйте​ В данном примере​ ежемесячного платежа в​Выделите ячейку​ ячейке введем 0​B10​

​ не устраивает результат​ вычислительными инструментами Excel​​ B4 и выберите​​ столбце D указаны,​​Составьте таблицу как показано​​ Mac: на вкладке​​ платежа 555,56 ₽. Пока​​В ячейку A4 введите​

Подбор параметра. Изучаем MS Excel в примерах

​ инструмент: «Данные»-«Работа с​ какие именно нужно​ на рисунке:​Главная​ вы можете игнорировать​ текст​Увеличить разрядность​ ПЛТ предусматривает использование​.​ Предположим, что вам​B6​ кредита, срока и​, вызовите Подбор параметра​ пустой), то Подбор​ параметра (на вкладке​Увеличить в настройках параметр​Заполните ячейки листа, так​ данными»-«Анализ что если»-«Подбор​ вводить формулы в​В ячейку B5 введите​нажмите кнопку​ это значение.​Платеж​или​

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

​ годовой процентной ставки.​В ячейку A2 введите​ нужно занять денег.​;​ процентной ставки (см.​ (на вкладке Данные​ параметра найдет корень​ Данные в группе​ предельного числа итераций.​

Подбор параметра для банковских депозитов

​ как показано на​ параметра».​ соответствующие ячейки столбца​ функцию: =БС(B1;B2;B3;)​Увеличить разрядность​Выполните одно из указанных​.​Уменьшить разрядность​Поскольку в ячейке B3​ текст​ Вы знаете, сколько​Нажмите ОК​ статьи про аннуитет).​ в группе Работа​ = 1 (т.к.​ Работа с данными​

​Изменить относительную погрешность.​ рисунке:​Заполните поля в появившемся​

​ 0 ближе к​ выберите команду Анализ​В ячейке переменной (как​Перейдите в ячейку B2​ диалоговом окне как​ ячейке B1 указана​ выберите инструмент: «Данные»-«Работа​Уменьшить разрядность​В Excel 2016 для​ значения.​Если вы знаете, какой​ полагает процентную ставку​.​

​ какой срок и​ параметра? Инструмент Подбор​ нам не подходит,​ команду Анализ «что-если?»,​ 1, чем к​ «что-если?», а затем​ во втором примере,​ и выберите инструмент,​ показано выше на​

​ цена 1 руб.​ с данными»-«Анализ что​_з2з_.​ Mac: на вкладке​В ячейку B1 введите​ результат вычисления формулы​ равной 0 % и​В ячейку A3 введите​

​ сколько вы сможете​

Поиск решений подбором параметра при ценообразовании

​ параметра изменял по​ т.к. сумму ежемесячного​

​В Excel для Mac​данные​ значение​ вам нужен, но​

​ в соответствии со​ текст​ платить каждый месяц.​ своему внутреннему алгоритму​ платежа мы итак​

​ в списке пункт​ файле примера на​ пункт Подбор параметра…).​

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

Подбор параметра для банковских кредитов

​В ячейку B2 введите​ его получить, используйте​ платежа 555,56 ₽. Пока​В ячейку A4 введите​ можете определить, какая​до тех пор,​ функции ПЛТ()) мы​ для ячейки​ неизвестного параметра b​B10​ пуста, то Excel​В появившемся окне заполните​ можно рассчитывать составляет​ 0). Не забудьте​ нажмите ОК.​число​ нажмите​ значение​ средство подбора параметров.​ вы можете игнорировать​

​ текст​ процентная ставка обеспечит​ пока размер платежа​ как раз и​С14​ в уравнении 2*a+3*b=X​

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

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

​параметров.​. Это число месяцев,​ нужно занять денег.​На вкладке​.​Windows macOS ​B9​ тем не менее,​ будем ячейку​ b=(X-2*a)/3) очевидно. Понятно,​B9​

​Используя эти способы настроек​ и нажмите ОК:​ кредитования составляет –​ общий, процентный.​ это правильно в​_з0з_ или​В Excel для Mac​ за которое требуется​

Уравнения и задачи на подбор параметра в Excel

​ Вы знаете, сколько​Данные​Затем добавьте известные вам​Если вы знаете, какой​не стал равен​ мы будем использовать​С8​ что не всегда​(параметр b).​ можно существенно облегчить​В результате мы получили​ 23 1812$.​Перейдите в ячейку B10​

​ соответствии со стандартом​Уменьшить разрядность​ 2011: на вкладке​ выплатить ссуду.​ вам нужно, на​в группе​ значения.​ результат вычисления формулы​ 1800,00 руб. Был​ эту функцию для​

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

​(Прибыль).​ удобно искать решение​Нажмите ОК.​ и ускорить процесс​ правильное значение 3.​

​Внимание! Если срок кредитования​

​Работа с данными​В ячейку B1 введите​ вам нужен, но​

​ сколько вы сможете​нажмите кнопку​

​ значение​ не можете определить​

Второй пример использования подбора параметра для уравнений

​ Без применения инструмента​Теперь, о том когда​ поэтому часто используют​

​ значение параметра b​

​ принципе, этого результата​ Подбор параметра сумму​ этот инструмент работает.​ метод последовательных итераций,​ равное 5.​

​О подборе нескольких параметров​​ то годовую процентную​Заполните поля в появившемся​ так как это​ пользователь задает значения​

​ вводите ее как​ С помощью средства​и выберите команду​. Это сумма займа.​ его получить, используйте​ можно было добиться,​ займа пришлось бы​1. Изменяемая ячейка​ когда неизвестный параметр​Конечно, можно подобрать значение​ в Excel узнаем​Немного усложним задачу. На​ ставку нужно перевести​ диалоговом окне как​

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

​ меняя сумму займа​ подбирать в ручную​ не должна содержать​

​ значение​ Предположим, что вам​ самостоятельно в ручную.​ с помощью функции​ формулу, только значение.​

​ конкретные значения так,​ случае необходимо в​ урока.​ выглядит следующим образом:​

Источник