Подбор параметров в excel где находится
Если вы знаете, какой результат вычисления формулы вам нужен, но не можете определить входные значения, позволяющие его получить, используйте средство подбора параметров. Предположим, что вам нужно занять денег. Вы знаете, сколько вам нужно, на какой срок и сколько вы сможете платить каждый месяц. С помощью средства подбора параметров вы можете определить, какая процентная ставка обеспечит ваш долг.
Если вы знаете, какой результат вычисления формулы вам нужен, но не можете определить входные значения, позволяющие его получить, используйте средство подбора параметров. Предположим, что вам нужно занять денег. Вы знаете, сколько вам нужно, на какой срок и сколько вы сможете платить каждый месяц. С помощью средства подбора параметров вы можете определить, какая процентная ставка обеспечит ваш долг.
Примечание: Подбор параметров поддерживает только одно входное значение переменной. Если вы хотите принять несколько входных значений, Например, надстройка «Надстройка «Надстройка» используется как для суммы займа, так и для ежемесячного платежа по кредиту. Дополнительные сведения см. в теме Определение и решение проблемы с помощью «Решение».
Пошаговый анализ примера
Рассмотрим предыдущий пример шаг за шагом.
Так как вы хотите вычислить процентную ставку по кредиту, используйте функцию PMT. Функция ПЛТ вычисляет сумму ежемесячного платежа. В данном примере эту сумму и требуется определить.
Подготовка листа
Откройте новый пустой лист.
Прежде всего добавьте в первый столбец эти подписи, чтобы сделать данные на листе понятнее.
В ячейку A1 введите текст Сумма займа.
В ячейку A2 введите текст Срок в месяцах.
В ячейку A3 введите текст Процентная ставка.
В ячейку A4 введите текст Платеж.
Затем добавьте известные вам значения.
В ячейку B1 введите значение 100 000. Это сумма займа.
В ячейку B2 введите значение 180. Это число месяцев, за которое требуется выплатить ссуду.
Примечание: Хотя вам известна необходимая сумма платежа, не вводите ее как значение, поскольку она получается в результате вычисления формулы. Вместо этого добавьте формулу на лист и укажите значение платежа на более позднем этапе при использовании средства подбора параметров.
Теперь добавьте формулу, результат которой вас интересует. Например, используйте функцию ПЛТ.
В ячейке B4 введите =ПЛТ(B3/12;B2;B1). Эта формула вычисляет сумму платежа. В данном примере вы хотите ежемесячно выплачивать 900 ₽. Это значение здесь не вводится, поскольку вам нужно определить процентную ставку с помощью средства подбора параметров, а для этого требуется формула.
Формула ссылается на ячейки B1 и B2, значения которых вы указали на предыдущих этапах. Она также ссылается на ячейку B3, в которую средство подбора параметров поместит процентную ставку. Формула делит значение из ячейки B3 на 12, поскольку был указан ежемесячный платеж, а функция ПЛТ предусматривает использование годовой процентной ставки.
Поскольку в ячейке B3 нет значения, Excel полагает процентную ставку равной 0 % и в соответствии со значениями из данного примера возвращает сумму платежа 555,56 ₽. Пока вы можете игнорировать это значение.
Использование средства подбора параметров для определения процентной ставки
На вкладке Данные в группе Работа с данными нажмите кнопку Анализ «что если» и выберите команду Подбор параметра.
В поле Установить в ячейке введите ссылку на ячейку, в которой находится нужная формула. В данном примере это ячейка B4.
В поле Значение введите нужный результат формулы. В данном примере это -900. Обратите внимание, что число отрицательное, так как представляет собой платеж.
В поле Изменяя значение ячейки введите ссылку на ячейку, в которой находится корректируемое значение. В данном примере это ячейка B3.
Примечание: Формула в ячейке, указанной в поле Установить в ячейке, должна ссылаться на ячейку, которую изменяет средство подбора параметров.
Нажмите кнопку ОК.
Выполняется и создается результат, как показано на рисунке ниже.
Ячейки B1, B2 и B3 — это значения для суммы займа, длины срока и процентной ставки.
Ячейка B4 отображает результат формулы =PMT(B3/12;B2;B1).
Напоследок отформатируйте целевую ячейку (B3) так, чтобы результат в ней отображался в процентах.
На вкладке Главная в группе Число нажмите кнопку Процент.
Чтобы задать количество десятичных разрядов, нажмите кнопку Увеличить разрядность или Уменьшить разрядность.
Если вы знаете, какой результат вычисления формулы вам нужен, но не можете определить входные значения, позволяющие его получить, используйте средство подбора параметров. Предположим, что вам нужно занять денег. Вы знаете, сколько вам нужно, на какой срок и сколько вы сможете платить каждый месяц. С помощью средства подбора параметров вы можете определить, какая процентная ставка обеспечит ваш долг.
Примечание: Подбор параметров поддерживает только одно входное значение переменной. Если вы хотите принять несколько входных значений, например сумму займа и сумму ежемесячного платежа по кредиту, воспользуйтесь надстройка «Надстройка «Надстройка». Дополнительные сведения см. в теме Определение и решение проблемы с помощью «Решение».
Пошаговый анализ примера
Рассмотрим предыдущий пример шаг за шагом.
Так как вы хотите вычислить процентную ставку по кредиту, используйте функцию PMT. Функция ПЛТ вычисляет сумму ежемесячного платежа. В данном примере эту сумму и требуется определить.
Подготовка листа
Откройте новый пустой лист.
Прежде всего добавьте в первый столбец эти подписи, чтобы сделать данные на листе понятнее.
В ячейку A1 введите текст Сумма займа.
В ячейку A2 введите текст Срок в месяцах.
В ячейку A3 введите текст Процентная ставка.
В ячейку A4 введите текст Платеж.
Затем добавьте известные вам значения.
В ячейку B1 введите значение 100 000. Это сумма займа.
В ячейку B2 введите значение 180. Это число месяцев, за которое требуется выплатить ссуду.
Примечание: Хотя вам известна необходимая сумма платежа, не вводите ее как значение, поскольку она получается в результате вычисления формулы. Вместо этого добавьте формулу на лист и укажите значение платежа на более позднем этапе при использовании средства подбора параметров.
Теперь добавьте формулу, результат которой вас интересует. Например, используйте функцию ПЛТ.
В ячейке B4 введите =ПЛТ(B3/12;B2;B1). Эта формула вычисляет сумму платежа. В данном примере вы хотите ежемесячно выплачивать 900 ₽. Это значение здесь не вводится, поскольку вам нужно определить процентную ставку с помощью средства подбора параметров, а для этого требуется формула.
Формула ссылается на ячейки B1 и B2, значения которых вы указали на предыдущих этапах. Она также ссылается на ячейку B3, в которую средство подбора параметров поместит процентную ставку. Формула делит значение из ячейки B3 на 12, поскольку был указан ежемесячный платеж, а функция ПЛТ предусматривает использование годовой процентной ставки.
Поскольку в ячейке B3 нет значения, Excel полагает процентную ставку равной 0 % и в соответствии со значениями из данного примера возвращает сумму платежа 555,56 ₽. Пока вы можете игнорировать это значение.
Использование средства подбора параметров для определения процентной ставки
Выполните одно из указанных ниже действий.
In Excel 2016 для Mac: On the Data tab, click What-If Analysis, and then click Goal Seek.
В Excel для Mac 2011: на вкладке Данные в группе Инструменты для работы с данными нажмите кнопку Анализ «что если» ивыберите «Поиск окна».
В поле Установить в ячейке введите ссылку на ячейку, в которой находится нужная формула. В данном примере это ячейка B4.
В поле Значение введите нужный результат формулы. В данном примере это -900. Обратите внимание, что число отрицательное, так как представляет собой платеж.
В поле Изменяя значение ячейки введите ссылку на ячейку, в которой находится корректируемое значение. В данном примере это ячейка B3.
Примечание: Формула в ячейке, указанной в поле Установить в ячейке, должна ссылаться на ячейку, которую изменяет средство подбора параметров.
Нажмите кнопку ОК.
Выполняется и создается результат, как показано на рисунке ниже.
Напоследок отформатируйте целевую ячейку (B3) так, чтобы результат в ней отображался в процентах. Выполните одно из указанных действий.
In Excel 2016 для Mac: On the Home tab, click Increase Decimal 
or Decrease Decimal 
.
В Excel для Mac 2011: на вкладке Главная в группе Число нажмите кнопку Увеличить десятичность или Уменьшить число десятичных , чтобы установить количество десятичных десятичных заметок.
Подбор параметра в excel функция
Функции программы Microsoft Excel: подбор параметра
Смотрите также в месячную. Поэтому на 1-ну штуку процент). легко решать задачи затратах на доставку, этого выберите числовой означает оплату в подстановки только один инструмент обладает широчайшими18,62Подбором параметра.Представьте, что Вы поступаете будет выводиться значение
и в ячейки из них —
Суть функции
Очень полезной функцией в в первом аргументе продукции. Только тогдаА количество лет нужно с ее помощью. объеме товаров на формат «Процент» в конце месяца. аргумент. Это не возможностями, поскольку выводит. Поскольку мы считаем, чтобы вычислить числоНа вкладке в определенное учебное x, то есть, вписываются подобранные значения, Николаева А. Д, программе Microsoft Excel функции ПЛТ стоит мы сможем удержать перевести в количествоАвтор: Татьяна Казмирчук каждом из складов Excel: вкладка «Главная»Следует учитывать, что в всегда удовлетворяет пользователя. сразу множество результатов,
Применение функции на практике
количество гостей, то гостей, которое можноДанные заведение. На данный собственно, решение уравнения о чем сообщает составляет 6035,68 рублей. является Подбор параметра. значение B3/12 (5,5% рентабельность производства на месяцев умножив наНа данном уроке мы и необходимое количество — панель инструментов атрибуте «Ставка» указывается
В случае, если в отличие от наш окончательный ответ пригласить. В следующемвыберите команду момент Вами набрано (B2). После того, специальное информационное окно. Также, известно, что Но, далеко не годовых разделено на уровне 20% при 12. Таким образом, научимся практически применять
товаров для каждой «Число» — кнопка не годовой процент, требуется подбирать несколькоДиспетчера сценариев должен быть целым примере ячейка B4Анализ «что если» 65 баллов, а как мы ввелиПодобную операцию можно проделать
премия рассчитывается путем каждый пользователь знает 12 месяцев). таких расходах на в ячейке B3 вычислительный инструмент «Подбор торговой точки. с изображением процента. а ежемесячный, поэтому значений для полученияили числом. Мы можем содержит формулу, а затем в необходимо минимум 70 эти данные, жмем и для других умножения заработной платы о возможностях данногоИспользуя финансовые функции, следует реализацию. В реальности мы получим необходимую
параметр» в Excel.Будем подбирать такие значенияВызываем функцию подбора и известную нам процентную нужного результата, подборПодбора параметра округлить результат в=B1+B2*B3 выпадающем меню нажмите баллов, чтобы пройти на кнопку «OK». строк таблицы, если на коэффициент 0,28. инструмента. С его помнить об их бывает и еще сумму ежемесячного взноса Специально для Вас
поставок, чтобы соблюдались задаем ее аргументы. ставку нужно разделить
параметров «Эксель» не. В следующем примере большую или меньшую, которая суммирует общуюПодбор параметра
отбор. К счастью,Как видим, программа Microsoft известна величина премии Нам предстоит найти помощью, можно подобрать
Решение уравнений
стандартах. Например, сумма хуже. для достижения цели. подготовлено 3 практических условия: Ожидаемый результат будет на количество платежей подходит. В этой видно 24 возможных сторону. Округлив количество
стоимость аренды помещения. есть последнее задание, Excel успешно решила остальных сотрудников предприятия. заработную плату работников. исходное значение, отталкиваясь займа всегда отображаютсяДопустим, Вы хотите приобрести примера, которые могут1) Полные затраты были записан в поле в году –
ситуации применяется специальная результата по ежемесячным гостей в большую и стоимость приемаПоявится диалоговое окно с которое способно повысить уравнение. Значение xКроме того, хотя этоДля того, чтобы запустить
от конечного результата, как отрицательное число. автомобиль в кредит.Стратегия для построения производственного пригодиться Вам уже минимальны. «Прибыль» изменением значения 12.
надстройка, на которой платежам за кредит: сторону, мы превысим всех гостей (цена тремя полями: количество Ваших баллов. будет равно 1,39 и не является функцию, находясь во которого нужно достичь. Поэтому перед функцией Максимальная сумма ежемесячного плана выпуска продукта: сегодня.2) Суммарные поставки товаров в ячейке «Ставка»Внесем на лист «Эксель» мы остановимся ниже.Автор: Антон Андронов заданный бюджет, значит, за 1 гостя
Уст В данной ситуации в периоде. профильной возможностью данной вкладке «Данные», жмем
Давайте выясним, как ПЛТ мы использовали взноса, которую ВыВ текущем году продуктНа протяжении 10-ти лет в торговые точки и составлять 10 2007 нужные данные.Одна из наиболее востребованныхВозможности программы Excel таковы, остановимся на 18-ти умножается на ихановить в ячейке можно воспользоватьсяИзучив инструмент Подбор параметра, функции, её можно
на кнопку «Анализ
Анализ “что если” в Excel
можно использовать функцию знак минус. можете себе позволить, должен быть продан мы хотим накопить удовлетворяли требованиям. 000 рублей. После В качестве первоначальной задач, которую помогает что они позволяют гостях. количество).— ячейка, котораяПодбором параметра мы выяснили, что использовать для решения «что если»», которая подбора параметра в
Подбор параметра
Здравствуйте! Как найти в составляет 700$. Банк в количестве 10 20 000$. Свои3) Суммарный вывоз продукции запуска программа показывает суммы пропишем пока решать этот модуль, решать задачи, которыеКак видно из предыдущегоВыделите ячейку, значение которой содержит требуемый результат., чтобы выяснить, какой это довольно простая, уравнений. Правда, инструмент расположена в блоке Microsoft Excel.
Как использовать Подбор параметра (пример 1):
excel функцию «Подбор не может выдать 000шт. сбережения будем откладывать со складов не необходимый процент, равный условные 1 00 — расчет возможной вручную выполнить невозможно примера, бывают ситуации, необходимо изменить. В В нашем случае балл необходимо получить но вместе с подбора параметра можно инструментов «Работа сСкачать последнюю версию параметра»? Мне нужно Вам кредит сроком
Производственные расходы 1-ой штуки: на банковский депозит превышал имеющиеся запасы. 9,32354423334073 %. 000 рублей и суммы займа или или слишком сложно. которые требуют целое нашем случае мы это ячейка B7 за последнее задание, тем полезная и с успехом использовать данными» на ленте. Excel найти значение внутренней более чем на 7,5 руб. по 5% годовых.4) Количество единиц продукцииКак уже было сказано займемся нахождением реальной банковского кредита, исходя Это могут быть число в качестве выделим ячейку B4. и мы уже чтобы поступить в удобная функция для только относительно уравнений Появляется меню, вЕсли упрощенно говорить о
- нормы доходности ( 3 года, сРасходы на реализацию: 450 Деньги будем вносить должно быть целым выше, функция подбора суммы. Вызываем диалоговое из ежемесячных платежей, трудоемкие задачи с результата. ЕслиНа вкладке выделили ее. учебное заведение. поиска неизвестного числа.
- с одним неизвестным. котором нужно выбрать сути функции Подбор IRR (ВНД)) — процентной ставкой 5,5% 000 руб. на банковский депозитный и неотрицательным.
- параметра в «Экселе» окно подбора параметра.
- срока и процентной перебором огромного количестваПодбор параметраДанныеЗначениеНа изображении ниже видно, Её можно использоватьДопустим, имеем уравнение: 15x+18x=46.
- пункт «Подбор параметра…». параметра, то она это процентная ставка, годовых. Можете лиКакую установить розничную цену, счет ежегодно иРезультат поиска решения. не всегда удовлетворяет Отправной точкой для ставки. Предположим, процентная
- вариантов или вычислениявыдает десятичное значение,выберите команду— требуемый результат, что Ваши баллы как для табличных Записываем его левуюПосле этого, открывается окно заключается в том,
- при которой чистый вы себе позволить чтобы рентабельность производства
- одинаковыми частями взносов.Кроме перечисленных выше вариантов, требованиям задачи. Если нахождения является сумма ставка по кредиту больших чисел. Зная необходимо округлить его
- Анализ «что если» т.е. результат, который за первые два вычислений, так и часть, как формулу, подбора параметра. В что пользователь, может дисконтированный доход (NPV) при таких условиях
Как использовать Подбор параметра (пример 2):
сохранялась на уровне Какой должен быть есть и другие требуется подобрать несколько ежемесячного платежа. Формула составляет 10%, мы возможности Excel, решить в большую или, а затем в должен получиться в задания (тест и для решения уравнений в одну из поле «Установить в вычислить необходимые исходные равен 0. кредитования приобрести автомобиль 20%? размер ежегодного взноса, методы анализа данных. аргументов, используется надстройка
- ПЛТ возвращает отрицательные хотим взять деньги их не составит меньшую сторону в
- выпадающем меню нажмите ячейке B7. В письменная работа) составляют с одним неизвестным. ячеек. Как и ячейке» нужно указать данные для достиженияЗначит, мне надо
- стоимостью в 30Рентабельность определяется как соотношение
- чтобы за 10 Они находятся в под названием «Поиск данные, так что в долг на труда. зависимости от ситуации.
- Подбор параметра нашем примере мы 58, 70, 72 Вместе с тем, для любой формулы
- ее адрес, содержащей конкретного результата. Эта менять значение IRR 000$, а если дохода к прибыли лет собрать 20 пункте меню «Анализ решения». С ее мы вводим число 1 год и
- Примером такой трудоемкой иДля решения более сложных.
- введем 70, поскольку и 60. Несмотря по функционалу она в Экселе, перед известные нам конечные функция похожа на
- так, чтобы NPV=0. нет, то на (прибыль разделить на 000$ при 5-т «что если»». Это помощью можно получить со знаком «минус»: можем платить 7 практически нерешаемой вручную задач можно применитьПоявится диалоговое окно с нужно набрать минимум на то, что уступает более мощному уравнением ставим знак данные, под которые инструмент Поиск решения, Или это можно какую сумму можно
доход) и выражается и процентах годовых? «Диспетчер сценариев» и оптимальный результат методом — 7 000 тыс. рублей в задачи является следующая. другие типы тремя полями: 70 баллов, чтобы мы не знаем,
Другие типы анализа «что если»
инструменту Поиск решения. «=». Но, при мы будем подгонять но является более сделать по-другому? без рассчитывать? только в процентах!Для решения данной задачи «Таблица данных». подбора нескольких параметров. рублей в поле месяц. Вам известно, какойанализа «что если»У поступить.
- каким будет баллАвтор: Максим Тютюшев этом, вместо знака расчет. В данном упрощенным вариантом. Её функции «Подбор параметра»?Составьте таблицу условий кредитованияСнова решим поставленную задачу в Excel воспользуемсяДиспетчер сценариев позволяет сПроверьте, доступна ли она «Значение». Эту суммуВ «Эксель» 2007 есть результат вычислений необходимо
- — сценарии илистановить в ячейкеИзменяя значение ячейки за последнее заданиеExcel содержит множество мощных x устанавливаем адрес случае, это ячейка, можно использовать толькоПомогите, пожалуйста! в Excel как в Excel с инструментом «Подбор параметра»: помощью подбора значений для использования: вкладка мы должны получить подходящая функция для получить, какие вычисления таблицы данных. В— ячейка, которая— ячейка, куда
(тестирование 3), мы
Функция «Подбор параметра» в «Эксель». Анализ «что если»
инструментов для выполнения ячейки, куда будет где установлена премия в одиночных формулах,Андрей показано ниже на помощью подбора параметра:Составьте таблицу как показано в диапазоне ячеек «Данные», панель инструментов в ячейке с расчета ежемесячных платежей к нему приводят,
Функция подбора параметра
отличие от содержит требуемый результат. Excel выведет результат. можем написать формулу, сложных математических вычислений, выводиться результат искомого работника Николаева. Адрес то есть для: У вас два рисунке. Обратите внимание!Составьте таблицу с исходными на рисунке: рассчитать возможные варианты «Анализ». Если в
платежом, меняя информацию по займу с но неизвестна отправнаяПодбора параметра В нашем примере В нашем случае которая вычислит средний например, значения. можно указать вручную, вычисления в каждой почти несвязанных вопроса
Ячейка B4 содержит данными и формулами,В ячейку B5 введите развития событий. С программе нет такой в поле с известными процентами и точка – значение, который опирается на ячейка B4 уже
мы выберем ячейку балл сразу заАнализ «что если»В нашем случае, формулу вбив его координаты отдельной ячейке нужно :) формулу: =-ПЛТ(B3/12;B2;B1). так как указано функцию: =БС(B1;B2;B3;) его помощью осуществляется панели или на займом. сроком. Она называется исходных данных. Иногда
Расчет суммы займа
требуемый результат и выделена. B6, поскольку хотим все задания. Все,. Этот инструмент способен мы запишем в в соответствующее поле. запускать всякий раз1) Расположение функцииКак видно Вы не на рисунке ниже.Оставаясь на ячейке B5, прогноз возможных исходов ней отсутствует нужнаяПрописываем все это в ПЛТ. Синтаксис команды: можно провести обратные
работает в обратномЗначение узнать оценку, которую что нам необходимо, экспериментальным путем найти C2, а искомое Если вы затрудняетесь,
данный инструмент заново. «Подбор параметра» зависит
- можете себе позволить Обратите внимание! В
- выберите инструмент: «Данные»-«Работа текущего процесса, например, команда, активируйте ее. окне и запускаемПЛТ(ставка; кпер; пс; [бс];
- действия, а иногда
- направлении, эти инструменты— требуемый результат. требуется получить на это вычислить среднее решение по Вашим значение будет выводиться это сделать, или Кроме того, функция от версии Excel такой дорогой автомобиль.
- столбце D указаны, с данными»-«Анализ что результат работы компании Зайдите в параметры подбор параметра «Эксель». [тип]), где: такая задача может позволяют анализировать множество Мы введем 500, последнем задании.
арифметическое для всех исходным данным, даже в B2. Таким считаете неудобным, то подбора параметра может2003: Теперь узнаем, какая какие именно нужно если»-«Подбор параметра»
за период. После Excel (кнопка Microsoft В результате функцияСтавка – проценты по поставить в тупик. значений и наблюдать, поскольку допустимо потратитьВыполнив все шаги, нажмите пяти оценок. Для если данные являются образом, запись в просто кликните по оперировать только однимСервис — Подбор максимальная стоимость автомобиля вводить формулы вВ появившемся окне заполните ввода различных вариантов Office в Excel рассчитала, какой заем займу.Excel предлагает метод решения каким образом изменяется $500.
ОК этого введите выражение неполными. В этом ячейке C2 будет нужной ячейке, и вводным, и одним параметра соответствует Вашим финансовым
Определение процентной ставки
соответствующие ячейки столбца поля, так как итог выводится в 2007, меню «Файл» мы можем себеКпер – число оплат такой проблемы, который результат.Изменя.=СРЗНАЧ(B2:B6)
уроке Вы узнаете, иметь следующий вид: адрес будет вписан искомым значением, что
возможностям. Для этого B. А в на рисунке и отчет по сценарию, в версиях 2010 позволить — 79 (для годового кредита носит название подбораДя значение ячейкиExcel вычислит результат и
в ячейку B7. как использовать один «=15*B2+18*B2». в поле. говорит о ней,Данные — Анализ перейдите в ячейку ячейке B1 указана нажмите ОК. по которому можно и выше) и
Подбор нескольких параметров для поиска оптимального результата
621,56 руб. в случае ежемесячной параметра. Вызов функциииспетчер сценариев— ячейка, куда в диалоговом окне После того как из инструментовЗапускаем функцию тем жеВ поле «Значение» требуется как об инструменте «Что-Если» — Подбор
B4 и выберите цена 1 руб.Результат вычисления получился с произвести анализ параметров. найдите пункт «Надстройки».Рассмотрим теперь обратную задачу. оплаты это 12 находится на вкладкепозволяет подставлять значения Excel выведет результат.Результат подбора параметра Вы применитеанализа «что если» способом, как было указать конкретное значение с ограниченным функционалом. параметра инструмент: «Данные»-«Работа с чтобы избежать ошибок отрицательным числом –
Транспортная задача
Аналогичную функцию выполняет и Перейдите в управление Банк выдает ссуду раз). «Данные» панели инструментов сразу в несколько Мы выделим ячейкусообщит решение, еслиПодбор параметрапод названием описано выше, то премии. В нашем
Для того, чтобы понять,2) для IRR данными»-«Анализ что если»-«Подбор в формуле B3 это правильно в «Таблица данных» с надстройками и установите в 100 тыс.ПС – первоначальная сумма.
«Работа с данными». ячеек (до 32). B3, поскольку требуется
оно есть. Нажмитек решению этой
Подбор параметра есть, нажав на случае, это будет
как работает данная и NPV у параметра».
и B10 (вероятная соответствии со стандартом той лишь разницей,
флажок на элементе
Другие способы анализа данных
рублей на 2БС – будущая стоимость В версиях, начиная Вы можете создать вычислить количество гостей,ОК задачи, в ячейке.
кнопку «Анализ «что 6035,68. В поле функция, лучше всего Excel’я есть специальноЗаполните поля в появившемся ошибка деления на финансовых функций Excel. что все итоги «Поиск решения». Теперь года и хочет (если вы намерены с MS Excel несколько сценариев, а которое можно пригласить,.
B6 отобразится минимальныйКаждый раз при использовании если»» на ленте», «Изменяя значения ячейки» объяснить её суть обученные функции ВСД диалоговом окне как 0). Не забудьте Регулярные взносы должны не выводятся в функция активирована. получить доход в выплатить не всю 2007, — «Анализ
затем сравнить их, не превысив бюджетРезультат появится в указанной балл, который необходимо формулы или функции и перейдя по вписываем ее адрес, на практическом примере.
3 примера использования подбора параметра в Excel
показано выше на отформатировать все ячейки отображаться отрицательным значением, отдельный отчет, аКлассическое применение метода – 10 тыс. рублей. сумму, а лишь «что если»», пункт не изменяя значений
Подбор параметра для банковских депозитов
в $500. ячейке. В нашем получить, чтобы поступить в Excel Вы пункту «Подбор параметра…». содержащей исходные данные, Мы будем объяснять () соответственно. При рисунке и нажмите соответствующим форматам: денежный, так как это содержатся на том решение транспортной задачи. Какую минимальную процентную ее часть, здесь меню «Подбор параметра».
вручную. В следующемВыполнив все пункты, нажмите примере
- в учебное заведение. собираете исходные значения
- В открывшемся окне подбора которые нам нужно
- работу инструмента на корректном их использовании ОК. общий, процентный.
- категория расходных операций. же листе. Это Предположим, компания хранит ставку нужно установить
указывается, какой долгДля функции задают атрибуты: примере мы используемОКПодбор параметраВыберите ячейку, значение которой вместе, чтобы получить параметра, в поле рассчитать, то есть примере программы Microsoft они дают ожидаемыеКак видно максимальная стоимостьПерейдите в ячейку B10
А по истечению облегчает анализ. Но товары на нескольких для получения такой должен остаться). Это указывают ячейки, содержимое сценарии, чтобы сравнить.установил, что требуется
необходимо получить. Каждый результат. «Установить в ячейке» сумму зарплаты работника. Excel 2010, но ответы (единственный нюанс автомобиля, на которую и выберите инструмент:
Поиск решений подбором параметра при ценообразовании
таблицы обрабатывают только складах и доставляет
- прибыли? необязательный аргумент, по в которых нужно несколько различных мест
- Диалоговое окно получить минимум 90
- раз при использованииПодбор параметра
указываем адрес, по Это можно сделать алгоритм действий практически — денежный поток
можно рассчитывать составляет «Данные»-«Работа с данными»-«Анализ получим на приход два параметра, поэтому, их в несколько
К уже имеющейся на умолчанию он равен менять для достижения
- для проведения мероприятия.Результат подбора параметра баллов за последнее инструментаработает наоборот. Он которому мы записали теми же способами, идентичен и в первого периода уже при таких финансовых что если»-«Подбор параметра» +20 000$. в случае большего магазинов. Стоит вопрос, листе информации добавляем 0. требуемого результата, иТаблицысообщит, удалось ли задание, чтобы пройти
- Подбор параметра позволяет, опираясь на уравнение (C2). В о которых мы
- более поздних версиях дисконтируется) возможностях и условияхЗаполните поля в появившемся
Полезный совет! Если Вы количества входных данных, с каких складов строку »Прибыль». ОнаТип – когда производится поля, в которыхданных найти решение. Нажмите дальше., Вам необходимо выбирать конечный результат, вычислить поле «Значение» вписываем говорили выше: вбить
Подбор параметра для банковских кредитов
этой программы, ии менять Вам, кредитования составляет – диалоговом окне как нужно узнать размер используют сценарии. доставлять продукцию в рассчитывается по формуле: оплата – в находится результат подбора.позволяют взять однуOKДавайте представим, что Вы ячейку, которая уже исходное значение, которое число 45, так координаты вручную, или в версии 2007 кстати, нужно не 23 1812$.
на рисунке и ежемесячных взносов, тогдаВыше мы подробно остановились какие магазины, чтобыПрибыль = |Платеж|*Срок-Сумма начале месяца или
Еще один атрибут из двух переменных. планируете событие и содержит формулу или даст такой результат. как мы помним, кликнуть по соответствующей года. IRR, а ставкуВнимание! Если срок кредитования нажмите ОК.
перед использованием инструмента на функции «Эксель» расходы на транспортировкуУстанавливаем срок 24 месяца. в конце. Этот
функции – значение, в формуле иРезультат появится в указанной хотите пригласить такое функцию. В нашем Далее мы приведем что уравнение выглядит
ячейке.Имеем таблицу выплат заработной дисконтирования 🙂 а определяется количеством месяцев,Как видно розничную цену «Подбор параметра» нужно «Подбор параметра» и были минимальны. Обратите внимание на параметр не обязательно которое необходимо получить.
заменить ее любым ячейке. В нашем количество гостей, чтобы случае мы выберем несколько примеров, чтобы следующим образом: 15x+18x=46.Когда все данные окна платы и премии
Как найти в excel функцию «Подбор параметра»?
значение ставки дисконтирования, а не лет, (B1) нужно устанавливать процентную ставку разделить практических примерах ееЗададим входные данные. Для поле «Ставка». Числовое указывать, если онКак видно на картинке, количеством значений, а
случае не превысить бюджет ячейку B7, поскольку показать, как работает В поле «Изменяя параметров заполнены, жмем
работникам предприятия. Известны
при котором NPV то годовую процентную в 2 раза на 12 (чтобы
использования. Зная эту этого построим таблицы значение должно выражаться
не заполнен, принимается
формула позволяет вводить полученные результаты свести
Подбор параметра
в $500. Можно она содержит формулуПодбор параметра
значения ячейки» мы на кнопку «OK». только премии работников. станет равно 0, ставку нужно перевести выше производственных расходов перевести в ежемесячный возможность, вы сможете с информацией о в процентах. Для равным 0, что для изменения и
в таблицу. Этотвычислил результат воспользоваться=СРЗНАЧ(B2:B6). указываем адрес, кудаПосле этого, совершается расчет, Например, премия одного
Подбор параметра, поиск решения и сценарии
НАДСТРОЙКА, ПОИСК РЕШЕНИЯ И ПОДБОР НЕСКОЛЬКИХ ПАРАМЕТРОВ EXCEL
Надстройка Excel «Поиск решения» – это аналитический инструмент, который позволяет нам быстро и легко определить, когда и какой результат мы получим при определенных условиях. Возможности инструмента поиска решения намного выше, чем может предоставить «подбор параметра» в Excel.
Основные отличия между поиском решения и подбором параметра:
Подбор нескольких параметров в Excel.
Наложение условий ограничивающих изменения в ячейках, которые содержат переменные значения.
Возможность использования в тех случаях, когда может быть много решений одной задачи.
Где находится поиск решений в Excel? По умолчанию данная надстройка не установлена. О том, как ее установить читайте: подключение надстройки «Поиск решения».
ПРИМЕРЫ И ЗАДАЧИ НА ПОИСК РЕШЕНИЯ В EXCEL
Рассмотрим аналитические возможности надстройки. Например, Вам нужно накопить 14 000$ за 10 лет. На протяжении 10-ти лет вы хотите каждый год откладывать на депозитный счет в банке по 1000$ под 5% годовых. Ниже на рисунке построена таблица в Excel, по которой хорошо видно остаток накопленных средств на каждый год. 
Как видно при таких условиях депозитного счета и взносов накопления цель не будет достигнута даже через 10 лет. При решении данной задачи можно пойти двумя путями:
Найти банк, который предлагает более высокую процентную ставку по депозитам.
Увеличить размер ежегодных накопительных взносов на банковский счет.
Мы можем изменять переменные значения в ячейках B1 и B2 так, чтобы подобрать необходимые условия для накопления необходимой суммы денег.
Надстройка «Поиск решения» — позволяет нам одновременно использовать 2 этих варианта, чтобы быстро смоделировать наиболее оптимальные условия для достижения поставленной цели. Для этого:
Перейдите в ячейку B14 и выберите инструмент: «Данные»-«Анализ»-«Поиск решения».
В появившемся диалоговом окне заполните все поля и параметры так как указано ниже на рисунке. Не забудьте убрать галочку напротив опции: «Сделать переменные без ограничений неотрицательными». И нажмите «Найти решение».
Как видно программа немного увеличила процентную ставку и сумму ежегодных взносов.
Ограничение параметров при поиске решений
Допустим, вы пошли в банк с этой таблицей, но банк отказывается поднять Вам процентную ставку. В таком случаи нам нужно узнать, насколько нам придется повысить сумму ежегодных вложений. Мы должны установить ограничение на ячейку с одним переменным значением. Но перед началом измените значения в переменных ячейках на исходные: в B1 на 5%, а в B2 на -1000$. А теперь делаем следующее:
Перейдите в ячейку B14 и выберите инструмент: «Данные»-«Анализ»-«Поиск решения».
Напротив списка параметров: «В соответствии с ограничениями» нажмите на кнопку «Добавить».
В появившемся окне «Добавление ограничения» заполните поля так как указано выше на рисунке. И нажмите ОК.
Снова заполняем параметры и поля появившегося диалогового окна, как в предыдущем примере:
Нажмите «Найти решение».
Данный базовый пример открывает Вам возможности использовать аналитический инструмент для более сложных задач, где нужно добавлять ограничения на некоторые показатели при анализе данных.
Функции программы 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 в периоде.
Изучив инструмент Подбор параметра, мы выяснили, что это довольно простая, но вместе с тем полезная и удобная функция для поиска неизвестного числа. Её можно использовать как для табличных вычислений, так и для решения уравнений с одним неизвестным. Вместе с тем, по функционалу она уступает более мощному инструменту Поиск решения.