Подбор параметра в excel
Обновлено: 04.07.2024
С помощью средств анализа "что если" в Excel вы можете экспериментировать с различными наборами значений в одной или нескольких формулах, чтобы изучить все возможные результаты.
Например, можно выполнить анализ "что если" для формирования двух бюджетов с разными предполагаемыми уровнями дохода. Или можно указать нужный результат формулы, а затем определить, какие наборы значений позволят его получить. В Excel предлагается несколько средств для выполнения разных типов анализа.
Обратите внимание на то, что в этой статье приведен только обзор инструментов. Подробные сведения о каждом из них можно найти по ссылкам ниже.
Анализ "что если" — это процесс изменения значений в ячейках, который позволяет увидеть, как эти изменения влияют на результаты формул на листе.
В Excel предлагаются средства анализа "что если" трех типов: сценарии, таблицы данных и подбор параметров. В сценариях и таблицах данных берутся наборы входных значений и определяются возможные результаты. Таблицы данных работают только с одной или двумя переменными, но могут принимать множество различных значений для них. Сценарий может содержать несколько переменных, но допускает не более 32 значений. Подбор параметров отличается от сценариев и таблиц данных: при его использовании берется результат и определяются возможные входные значения для его получения.
Помимо этих трех средств можно установить надстройки для выполнения анализа "что если", например надстройку Поиск решения. Эта надстройка похожа на подбор параметров, но позволяет использовать больше переменных. Вы также можете создавать прогнозы, используя маркер заполнения и различные команды, встроенные в Excel.
Для более сложных моделей можно использовать надстройку Пакет анализа.
Использование сценариев для учета множества разных переменныхСценарий — это набор значений, которые сохраняются в Excel и могут автоматически подставляться в ячейки на листе. Вы можете создавать и сохранять различные группы значений на листе, а затем переключиться на любой из этих новых сценариев, чтобы просмотреть другие результаты.
Предположим, у вас есть два сценария бюджета: для худшего и лучшего случаев. Вы можете с помощью диспетчера сценариев создать оба сценария на одном листе, а затем переключаться между ними. Для каждого сценария вы указываете изменяемые ячейки и значения, которые нужно использовать. При переключении между сценариями результат в ячейках изменяется, отражая различные значения изменяемых ячеек.
1. Изменяемые ячейки
2. Ячейка результата
1. Изменяемые ячейки
2. Ячейка результата
Если у нескольких человек есть конкретные данные в отдельных книгах, которые вы хотите использовать в сценариях, вы можете собрать эти книги и объединить их сценарии.
После создания или сбора всех нужных сценариев вы можете создать сводный отчет по сценариям, в который включаются данные из этих сценариев. В отчете по сценариям все данные отображаются в одной таблице на новом листе.
Примечание: В отчетах по сценариям автоматический пересчет не выполняется. Изменения значений в сценарии не будут отражается в уже существующем сводном отчете. Вам потребуется создать новый сводный отчет.
Использование подбора параметров для получения нужного результатаЕсли вы знаете нужный результат формулы, но не знаете, какое входные значения требуется для получения этого результата, используйте функцию "Поиск окна". Предположим, что вам нужно занять денег. Вы знаете, сколько вам нужно, на какой срок и сколько вы сможете выплачивать каждый месяц. С помощью средства подбора параметров вы можете определить, какая процентная ставка вам подойдет.
Ячейки B1, B2 и B3 — это значения для суммы займа, длины срока и процентной ставки.
Ячейка B4 отображает результат формулы =PMT(B3/12;B2;B1).
Примечание: В средстве поиска окна можно ввести только одно значение переменной. Если вы хотите определить несколько входных значений, например сумму займа и сумму ежемесячного платежа по кредиту, используйте надстройка "Надстройка "Найти решение". Дополнительные сведения о надстройки "Решение" см. в разделе Подготовка прогнозов и расширенных бизнес-моделей ипо ссылкам в разделе См. также.
Использование таблиц данных для просмотра влияния переменных в формулеЕсли у вас есть формула с одной или двумя переменными либо несколько формул, в которых используется одна общая переменная, вы можете просмотреть все результаты в одной таблице данных. С помощью таблиц данных можно легко и быстро проверить несколько возможностей. Поскольку используются всего одна или две переменные, результат можно без труда прочитать или опубликовать в табличной форме. Если для книги включен автоматический пересчет, данные в таблицах данных сразу же пересчитываются, и вы всегда видите свежие данные.
Ячейка B3 содержит входные значения.
Ячейки C3, C4 и C5 являются значениями, Excel заменяются на основе значения, введенного в ячейку B3.
В таблицу данных нельзя помещать больше двух переменных. Для анализа большего количества переменных используйте сценарии. Несмотря на то что переменных не может быть больше двух, можно использовать сколько угодно различных значений переменных. В сценарии можно использовать не более 32 различных значений, зато вы можете создать сколько угодно сценариев.
При подготовке прогнозов вы можете использовать Excel для автоматической генерации будущих значений на базе существующих данных или для автоматического вычисления экстраполированных значений на основе арифметической или геометрической прогрессии.
Вы можете заполнить ряд значений, которые соответствуют простому линейному или экспоненциальному тренду роста, с помощью ручки заполнения или команды Ряд. Для расширения сложных и нелинейных данных можно использовать функции или средство регрессионного анализа надстройки "Надстройка анализа".
В средстве подбора параметров можно использовать только одну переменную, а с помощью надстройки Поиск решения вы можете создать обратную проекцию для большего количества переменных. Надстройка "Поиск решения" помогает найти оптимальное значение для формулы в одной ячейке листа, которая называется целевой.
Над решением работает группа ячеек, связанных с формулой в целевой ячейке. "Решение" изменяет значения изменяемых ячеек, которые вы указываете (регулируемые ячейки), чтобы получить результат, который вы указываете из формулы целевой ячейки. Ограничения можно применять для ограничения значений, которые можно использовать в модели, а ограничения могут ссылаться на другие ячейки, влияющие на формулу целевой ячейки.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
На данном уроке мы научимся практически применять вычислительный инструмент «Подбор параметр» в 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.
Описание
Использует номер_индекса, чтобы выбрать и вернуть значение из списка аргументов-значений. Функция ВЫБОР позволяет выбрать одно значение из списка, в котором может быть до 254 значений. Например, если первые семь значений — это дни недели, то функция ВЫБОР возвращает один из дней при использовании числа от 1 до 7 в качестве аргумента "номер_индекса".
Синтаксис
Аргументы функции ВЫБОР описаны ниже.
Номер_индекса — обязательный аргумент. Номер выбираемого аргумента-значения. Номер индекса должен быть числом от 1 до 254, формулой или ссылкой на ячейку, содержащую число в диапазоне от 1 до 254.
Если номер_индекса равен 1, то функция ВЫБОР возвращает значение1; если он равен 2, возвращается значение2 и так далее.
Если номер_индекса представляет собой дробь, то он усекается до меньшего целого.
Значение1,значение2. — аргумент "значение1" является обязательным, следующие за ним — нет. От 1 до 254 аргументов-значений, из которых функция ВЫБОР, используя номер индекса, выбирает значение или выполняемое действие. Аргументы могут быть числами, ссылками на ячейки, определенными именами, формулами, функциями или текстом.
Замечания
Если номер_индекса является массив, то при выполнении функции ВЫБОР вычисляется каждое значение.
Аргументы-значения функции ВЫБОР могут быть как ссылками на интервал, так и отдельными значениями.
которая возвращает значение, вычисленное на основе значений в интервале ячеек B1:B10.
В этом примере сначала вычисляется функция ВЫБОР, которая возвращает ссылку на интервал B1:B10. Затем вычисляется функция СУММ, причем в качестве аргумента используется интервал B1:B10, то есть результат функции ВЫБОР.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Если вы знаете, какой результат вычисления формулы вам нужен, но не можете определить входные значения, позволяющие его получить, используйте средство подбора параметров. Предположим, что вам нужно занять денег. Вы знаете, сколько вам нужно, на какой срок и сколько вы сможете платить каждый месяц. С помощью средства подбора параметров вы можете определить, какая процентная ставка обеспечит ваш долг.
Если вы знаете, какой результат вычисления формулы вам нужен, но не можете определить входные значения, позволяющие его получить, используйте средство подбора параметров. Предположим, что вам нужно занять денег. Вы знаете, сколько вам нужно, на какой срок и сколько вы сможете платить каждый месяц. С помощью средства подбора параметров вы можете определить, какая процентная ставка обеспечит ваш долг.
Примечание: Подбор параметров поддерживает только одно входное значение переменной. Если вы хотите принять несколько входных значений, Например, надстройка "Надстройка "Надстройка" используется как для суммы займа, так и для ежемесячного платежа по кредиту. Дополнительные сведения см. в теме Определение и решение проблемы с помощью "Решение".
Пошаговый анализ примера
Рассмотрим предыдущий пример шаг за шагом.
Так как вы хотите вычислить процентную ставку по кредиту, используйте функцию 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: на вкладке Главная в группе Число нажмите кнопку Увеличить десятичность или Уменьшить число десятичных , чтобы установить количество десятичных десятичных заметок.
Читайте также: