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

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

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

Сущность функции

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

Применение функции на практике

Для того, чтоб осознать, как работает данная функция, идеальнее всего разъяснить её сущность на практическом примере. Мы будем разъяснять работу инструмента на примере программки Microsoft Excel 2010, но метод действий фактически схож и в наиболее поздних версиях данной для нас программки, и в версии 2007 года.

Имеем таблицу выплат зарплаты и премии работникам компании. Известны лишь премии работников. К примеру, премия 1-го из их — Николаева А. Д, составляет 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 в периоде.

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

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

Кроме данной для нас статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

Отблагодарите создателя, поделитесь статьей в соц сетях.

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

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

Обычно при разработке формулы юзер задает значения характеристик и формула (уравнение) возвращает итог. К примеру, имеется уравнение 2*a+3*b=x, заданы характеристики а=1, b=2, требуется отыскать x (2*1+3*2=8). Инструмент Подбор параметра дозволяет решить оборотную задачку: подобрать такое значение параметра, при котором уравнение возвращает хотимый мотивированной итог X. К примеру, при a=3, требуется отыскать такое значение параметра b, при котором X равен 21 (ответ b=5). Подбирать параметр вручную — кислое занятие, потому в MS EXCEL имеется инструмент Подбор параметра .

В MS EXCEL 2007-2010 Подбор параметра находится на вкладке Данные, группа Работа с данным .

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

Найдем значение параметра b в уравнении 2*а+3*b=x , при котором x=21 , параметр а= 3 .

Подготовим начальные данные.

Значения характеристик а и b введены в ячейках B8 и B9 . В ячейке B10 введена формула =2*B8+3*B9 (т.е. уравнение 2*а+3*b=x ). Целевое значение x в ячейке B11 введено для инфы.

Выделите ячейку с формулой B10 и вызовите Подбор параметра (на вкладке Данные в группе Работа с данными изберите команду Анализ «что-если?» , а потом изберите в перечне пункт Подбор параметра …) .

В качестве мотивированного значения для ячейки B10 укажите 21, изменять будем ячейку B9 (параметр b ).

Инструмент Подбор параметра подобрал значение параметра b равное 5.

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

Примечание : Уравнение 2*а+3*b=x является линейным, т.е. при данных a и х существует лишь одно значение b , которое ему удовлетворяет. Потому инструмент Подбор параметра работает (конкретно для решения таковых линейных уравнений он и сотворен). Если пробовать, к примеру, решать при помощи Подбора параметра квадратное уравнение (имеет 2 решения), то инструмент решение отыщет, но лишь одно. При этом, он отыщет, то которое поближе к исходному значению (т.е. задавая различные исходные значения, можно отыскать оба корня уравнения). Решим квадратное уравнение x^2+2*x-3=0 (уравнение имеет 2 решения: x1=1 и x2=-3). Если в изменяемой ячейке введем -5 (изначальное значение), то Подбор параметра отыщет корень = -3 (т.к. -5 поближе к -3, чем к 1). Если в изменяемой ячейке введем 0 (либо оставим ее пустой), то Подбор параметра отыщет корень = 1 (т.к. 0 поближе к 1, чем к -3). Подробности в файле примера на листе Простой .

Очередной путь нахождения неведомого параметра b в уравнении 2*a+3*b=X — аналитический. Решение b=(X-2*a)/3) разумеется. Понятно, что не постоянно комфортно находить решение уравнения аналитическим методом, потому нередко употребляют способ поочередных итераций, когда неведомый параметр подбирают, задавая ему определенные значения так, чтоб приобретенное значение х сделалось равно мотивированному X (либо приблизительно равно с данной точностью).

Интересно почитать: Финансовые функции в excel с примерами

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

Еще пример. Пусть дана структура цены контракта: Собственные расходы, Прибыль, НДС.

Понятно, что Собственные расходы составляют 150 000 руб., НДС 18%, а Мотивированная стоимость контракта 200 000 руб. (ячейка С13 ). Единственный параметр, который можно поменять, это Прибыль. Подберем такое значение Прибыли ( С8 ), при котором Стоимость контракта равна Мотивированной, т.е. значение ячейки Расхождение ( С14 ) равно 0.

В структуре цены в ячейке С9 (Стоимость продукции) введена формула Собственные расходы + Прибыль ( =С7+С8 ). Стоимость контракта (ячейка С11 ) рассчитывается как Стоимость продукции + НДС (= СУММ(С9:C10) ).

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

Выделите ячейку С14 , вызовите Подбор параметра (на вкладке Данные в группе Работа с данными изберите команду Анализ «что-если?» , а потом изберите в перечне пункт Подбор параметра …). В качестве мотивированного значения для ячейки С14 укажите 0, изменять будем ячейку С8 (Прибыль).

Сейчас, о том когда этот инструмент работает. 1. Дифференцируемая ячейка не обязана содержать формулу, лишь значение.2. Нужно отыскать лишь 1 значение, изменяя 1 ячейку. Если требуется отыскать 1 конкретное значение (либо среднее значение), изменяя значения в НЕСКОЛЬКИХ ячейках, то используйте Поиск решения.3. Уравнение обязано иметь решение, в нашем случае уравнением является зависимость цены от прибыли. Если мотивированная стоимость была бы равна 1000, то положительной прибыли бы у нас отыскать не удалось, т.к. расходы больше 150 тыс. Либо к примеру, если решать уравнение x2+4=0, то разумеется, что не получится подобрать такое х, чтоб x2+4=0

Примечание : В файле примера приведен метод решения Квадратного уравнения с внедрением Подбора параметра.

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

Представим, что нам нужно найти наивысшую сумму кредита , которую мы можем для себя дозволить взять в банке. Пусть нам известна сумма каждомесячного платежа в рублях (1800 руб./мес.), также процентная ставка по кредиту (7,02%) и срок на который мы желаем взять кредит (180 мес).

В EXCEL существует функция ПЛТ() для расчета каждомесячного платежа в зависимости от суммы кредита, срока и процентной ставки (см. статьи про аннуитет ). Но эта функция нам не подступает, т.к. сумму каждомесячного платежа мы итак знаем, а вот сумму кредита (параметр функции ПЛТ() ) мы как раз и желаем отыскать. Но, тем не наименее, мы будем употреблять эту функцию для решения нашей задачки. Без внедрения инструмента Подбор параметра сумму займа пришлось бы подбирать в ручную при помощи функции ПЛТ() либо употреблять подобающую формулу.

Введем в ячейку B 6 приблизительную сумму займа, к примеру 100 000 руб., срок на который мы желаем взять кредит введем в ячейку B 7 , % ставку по кредиту введем в ячейку B8, а формулу =ПЛТ(B8/12;B7;B6) для расчета суммы каждомесячного платежа в ячейку B9 (см. файл примера ).

Чтоб отыскать сумму займа подобающую данным выплатам 1800 руб./мес., делаем последующее:

на вкладке Данные в группе Работа с данными изберите команду Анализ «что-если?» , а потом изберите в перечне пункт Подбор параметра …;
в поле Установить введите ссылку на ячейку, содержащую формулу. В данном примере — это ячейка B9 ;
введите разыскиваемый итог в поле Значение . В данном примере он равен -1800 ;
В поле Изменяя значение ячейки введите ссылку на ячейку, значение которой необходимо подобрать. В данном примере — это ячейка B6 ;
Нажмите ОК

Что все-таки сделал Подбор параметра ? Инструмент Подбор параметра изменял по собственному внутреннему методу сумму в ячейке B6 до того времени, пока размер платежа в ячейке B9 не стал равен 1800,00 руб. Был получен итог — 200 011,83 руб. В принципе, этого результата можно было достигнуть, меняя сумму займа без помощи других в ручную.

Подбор параметра подбирает значения лишь для 1 параметра. Если Для вас необходимо отыскать решение от нескольких характеристик, то используйте инструмент Поиск решения . Точность подбора параметра можно задать через меню Клавиша кабинет/ Характеристики Excel/ Формулы/ Характеристики вычислений . Вопросцем о единственности отысканного решения Подбор параметра не занимается, возможно выводится 1-ое подходящее решение.

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

Подбор параметра в excel функция

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

Интересно почитать: Использование функции впр в excel примеры

Примечание: Подбор характеристик поддерживает лишь одно входное значение переменной. Если вы желаете принять несколько входных значений, К примеру, надстройка «Надстройка «Надстройка» употребляется как для суммы займа, так и для каждомесячного платежа по кредиту. Доп сведения см. в теме Определение и решение задачи при помощи «Решение».

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

Разглядим предшествующий пример шаг за шагом.

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

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

Откройте новейший пустой лист.

До этого всего добавьте в 1-ый столбец эти подписи, чтоб создать данные на листе понятнее.

В ячейку 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) так, чтоб итог в ней отображался в процентах.

На вкладке Основная в группе Число нажмите клавишу Процент.

Чтоб задать количество десятичных разрядов, нажмите клавишу Прирастить разрядность либо Уменьшить разрядность.

Примечание: Подбор характеристик поддерживает лишь одно входное значение переменной. Если вы желаете принять несколько входных значений, к примеру сумму займа и сумму каждомесячного платежа по кредиту, воспользуйтесь надстройка «Надстройка «Надстройка». Доп сведения см. в теме Определение и решение задачи при помощи «Решение».

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

Разглядим предшествующий пример шаг за шагом.

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

Откройте новейший пустой лист.

До этого всего добавьте в 1-ый столбец эти подписи, чтоб создать данные на листе понятнее.

В ячейку A1 введите текст Сумма займа.

В ячейку A2 введите текст Срок в месяцах.

В ячейку A3 введите текст Процентная ставка.

В ячейку A4 введите текст Платеж.

Потом добавьте известные для вас значения.

В ячейку B1 введите значение 100 000. Это сумма займа.

В ячейку B2 введите значение 180. Это число месяцев, за которое требуется выплатить ссуду.

Сейчас добавьте формулу, итог которой вас интересует. К примеру, используйте функцию ПЛТ.

Интересно почитать: Функция суммеслимн в excel

Внедрение средства подбора характеристик для определения процентной ставки

Сделайте одно из обозначенных ниже действий.

In Excel 2016 для Mac: On the Data tab, click What-If Analysis, and then click Goal Seek.

В Excel для Mac 2011: на вкладке Данные в группе Инструменты для работы с данными нажмите клавишу Анализ «что если» иизберите «Поиск окна».

Нажмите клавишу ОК.

Производится и создается итог, как показано на рисунке ниже.

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

In Excel 2016 для Mac: On the Home tab, click Increase Decimal or Decrease Decimal .

В Excel для Mac 2011: на вкладке Основная в группе Число нажмите клавишу Прирастить десятичность либо Уменьшить число десятичных , чтоб установить количество десятичных десятичных заметок.

Как и где употребляют функцию «Подбор параметра» в Excel

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

Что за функция, для чего нужна?

Способ предназначен для случаев, когда необходимо отыскать неведомое значение в одиночной формуле, исходя из уже известного результата. То все есть составные части формулы (итог и входные данные) известны, при всем этом решение не быть может сформулировано вполне из-за отсутствия 1-го из входных характеристик.

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

Внедрение функции

В Microsoft Excel подобные задачки решаются при помощи обычного меню. Подбор параметра в Excel 2007, 2010, 2013 и новее находится во вкладке «Данные», в группе «Работы с данными».

Последующий шаг – наполнение полей в показавшемся окне. 1-ое поле создано для данной формулы, 2-ое для мотивированного значения, в третье для адреса элемента вывода (наиболее тщательно это будет разобрано дальше на определенных примерах).

3-ий шаг это доказательство операции и вывод результата.

Пример использования функции

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

Найти — какая будет процентная ставка (по займу). Входными данными являются срок (36) и сумма (150000). Для начала их необходимо показать в табличном представлении.

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

Во всплывающем окошке в поле «Установить в ячейке» обязана быть указана ссылка ячейки, в которой содержится основная формула (B4). В текстовое поле «Значение» нужно ввести предположительную сумму каждомесячного платежа. Например, -5 000 (символ «минус» обозначает, что валютная сумма будет отдана). В 3-ем поле «Изменяя значение ячейки» – следует списать ссылку табличного элемента, в которой будет выведен разыскиваемый параметр ($B$3).

Опосля клика по кнопочке «ОК» в новеньком окне отобразится итог подсчета.

Для доказательства операции следует кликнуть по соответственной кнопочке.

Функция подбора неведомого параметра будет перебирать значение искомого элемента до момента получения результата формулы. Команда выдаст лишь одно решение.

Решение уравнений

Подбор параметра также употребляют, если необходимо отыскать какое-либо из значений в данном уравнении. В качестве примера воспользуемся последующим выражением: 2*а+3*b=x, где x=21, а=3, неведомая переменная — b.

Для начала необходимо заполнить таблицу.

Характеристики а и b следует вводить в ячейки B2 и B3 соответственно. Табличный элемент B4 отведен для формулы =2*B2+3*B3. Переменная x в ячейке B5 указана в качестве примечания.

Нужно выделить ячейку, в которой вписана формула (B4) и вызвать мотивированную функцию по определению параметра ({инструкция} вызова тщательно изложена в прошлом примере).

Потом вписать во 2-ое поле (значение) итог (21), а в третье адресок ячейки B3, так как конкретно она будет изменяться.

Подтвердить действие кликом по соответственной кнопочке.

В итоге выполнения команды переменной b подобралось значение 5.

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

Для закрепления материала решим очередное уравнение – 15*x+18*x=46. Для начала необходимо записать формулу в ячейку B2. Заместо x нужно указать ссылку на табличный элемент, где будет отображен итог, в данном случае A2.

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

Во всплывающем окне, в первом верхнем текстовом поле необходимо вписать ссылку ячейки, содержащей формулу (B2). Во 2-м поле — число из уравнения опосля знака равно, другими словами 46. В 3-ем поле обязана быть ссылка на ячейку со значением x, в данном случае это A2.

Опосля того как все поля заполнены, необходимо подтвердить операцию. На дисплее в новеньком всплывающем окне отобразиться правильное решение уравнения. Значение x будет равно 1,39393939393939.

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