характеристика область применения технология использования инструмента подбор параметра
Функции программы 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 в периоде.
Изучив инструмент Подбор параметра, мы выяснили, что это довольно простая, но вместе с тем полезная и удобная функция для поиска неизвестного числа. Её можно использовать как для табличных вычислений, так и для решения уравнений с одним неизвестным. Вместе с тем, по функционалу она уступает более мощному инструменту Поиск решения.
Помимо этой статьи, на сайте еще 12283 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Подбор параметра в EXCEL
history 18 ноября 2012 г.
Простейший пример
Подготовим исходные данные.
В качестве целевого значения для ячейки B10 укажите 21, изменять будем ячейку B9 (параметр b ).
Инструмент Подбор параметра подобрал значение параметра b равное 5.
Конечно, можно подобрать значение вручную. В данном случае необходимо в ячейку B9 последовательно вводить значения и смотреть, чтобы х текущее совпало с Х целевым. Однако, часто зависимости в формулах достаточно сложны и без Подбора параметра параметр будет подобрать сложно .
Калькуляция, подбираем значение прибыли
Еще пример. Пусть дана структура цены договора: Собственные расходы, Прибыль, НДС.
Известно, что Собственные расходы составляют 150 000 руб., НДС 18%, а Целевая стоимость договора 200 000 руб. (ячейка С13 ). Единственный параметр, который можно менять, это Прибыль. Подберем такое значение Прибыли ( С8 ), при котором Стоимость договора равна Целевой, т.е. значение ячейки Расхождение ( С14 ) равно 0.
В структуре цены в ячейке С9 (Цена продукции) введена формула Собственные расходы + Прибыль ( =С7+С8 ). Стоимость договора (ячейка С11 ) вычисляется как Цена продукции + НДС (= СУММ(С9:C10) ).
Конечно, можно подобрать значение вручную, для чего необходимо уменьшить значение прибыли на величину расхождения без НДС. Однако, как говорилось ранее, зависимости в формулах могут быть достаточно сложны. В этом случае поможет инструмент Подбор параметра .
Теперь, о том когда этот инструмент работает. 1. Изменяемая ячейка не должна содержать формулу, только значение.2. Необходимо найти только 1 значение, изменяя 1 ячейку. Если требуется найти 1 конкретное значение (или оптимальное значение), изменяя значения в НЕСКОЛЬКИХ ячейках, то используйте Поиск решения.3. Уравнение должно иметь решение, в нашем случае уравнением является зависимость стоимости от прибыли. Если целевая стоимость была бы равна 1000, то положительной прибыли бы у нас найти не удалось, т.к. расходы больше 150 тыс. Или например, если решать уравнение x2+4=0, то очевидно, что не удастся подобрать такое х, чтобы x2+4=0
Примечание : В файле примера приведен алгоритм решения Квадратного уравнения с использованием Подбора параметра.
Подбор суммы кредита
В EXCEL существует функция ПЛТ() для расчета ежемесячного платежа в зависимости от суммы кредита, срока и процентной ставки (см. статьи про аннуитет ). Но эта функция нам не подходит, т.к. сумму ежемесячного платежа мы итак знаем, а вот сумму кредита (параметр функции ПЛТ() ) мы как раз и хотим найти. Но, тем не менее, мы будем использовать эту функцию для решения нашей задачи. Без применения инструмента Подбор параметра сумму займа пришлось бы подбирать в ручную с помощью функции ПЛТ() или использовать соответствующую формулу.
Чтобы найти сумму займа соответствующую заданным выплатам 1800 руб./мес., делаем следующее:
Иными словами, инструмент Подбор параметра позволяет сэкономить несколько минут по сравнению с ручным перебором.
Функция – подбор параметра в excel
Microsoft Office Excel позволяет решать математические задачи, системы уравнений, находить значения функций и создавать их графики. При использовании специальных инструментов и формул можно легко и быстро найти решение поставленной задачи. Сегодня рассмотрим подбор параметра в excel.
Местоположение
В широком смысле подбор параметра есть поиск решения уравнения с одной неизвестной при помощи большого количества итераций. Отличие этого инструмента от поиска решения в том, что используется только один аргумент.
Многие пользователи задают вопрос: где находится подбор параметра? Найти эту функцию не сложно. На Панели управления ищете вкладку Данные, затем в блоке Работа с данными нажимаете кнопку Анализ «что-если» и из выпадающего списка выбираете необходимый инструмент.
Использование
Окно подбора имеет несколько полей:
Важно! Отметим несколько моментов, на которые стоит обратить внимание при работе с этим инструментом:
Рассмотрим примеры применения подбора параметра.
Задача 1.
Найти решение уравнения с одной неизвестной 2*x^2 — x/3=12
В одной ячейке запишем правую часть с иксом, а во второй – произвольное число, которое попадает в область определения. Это нужно для того, чтобы программа могла начать поиск неизвестной.
Запускаете уже известную функцию. В первом поле делаете ссылку на формулу, во втором записываете левую часть исходного уравнения и в конце делаете ссылку на произвольное число.
После подсчета программа выдает результат в отдельном диалоговом окне и на рабочем листе.
Если поставить отрицательное число для начала работы программы, то и конечное значение будет другим.
Отсюда следует, что функция подбирает первое решение поставленной задачи, при этом истинным решением уравнения может быть множество значений. Все зависит от точки первоначального отсчета.
Задача 2.
Рассчитать процентную ставку по кредиту в 10000$ сроком на два с половиной года.
Запишем исходные данные в таблицу.
Чтобы посчитать сумму платежа, воспользуемся встроенной функцией excel – ПЛТ. Она состоит из процентной ставки, периода выплат и величины кредита. Значением процента задаемся произвольно.
Вызываете функцию подбор параметра и заполняете форму, при этом платеж будет составлять 400$. Поскольку это финансовая формула, то не забывайте знак минус.
В итоге получаете следующие результаты:
Как видите, существует несколько областей применения функции подбор параметра. Важно помнить, что для поиска решения можно использовать только один аргумент и желаемая величина должна быть выражена в виде формулы.
Жми «Нравится» и получай только лучшие посты в Facebook ↓
Анализ данных посредством инструмента «Подбор параметра»
МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ
При решении экономических и финансовых задач достаточно часто решение задачи приобретает вид математической модели (формулы), построенной на основе выявленных связей между изучаемыми элементами явления (результативного показателя) и самим явлением в целом. Изменяя составляющие элементы, можно проводить анализ изменения явления в целом. Таким образом, изучаются свойства и значения функции (явления) в тех или иных условиях. Этот анализ позволяет проводить инструмент MS Excel Подбор параметра. Рассмотрим возможности его применения на конкретных примерах.
Нахождение значения аргумента функции, соответствующего определённому значению функции
Использование функции Подбор параметра для нахождения значения аргумента функции, соответствующего заданному значению функции может быть представлено таким образом: поиск определенного результата для ячейки с помощью подбора значения другой ячейки.
Например, одна ячейка содержит формулу, в которой есть ссылки на другую ячейку.
Значение в ячейке С1 представляет собой среднее арифметическое значение в ячейках А1 и В1:
Допустим, что для целей исследования необходимо найти значение, которое должна принять ячейка А1, для того чтобы ячейка С1 приняла значение 855.
Безусловно, можно самостоятельно путём перебора значений в ячейке А1 достичь необходимый результат. Однако, в целях минимизации затрат времени следует воспользоваться функцией Подбор параметра.
Для этого необходимо:
1) выполнить команду Подбор параметра из меню Данные > Анализ «что-если».
В результате появится запрос Подбор параметра:
2) в поле Установить в ячейке ввести ссылку или имя ячейки, содержащую формулу, для которой следует подобрать параметр. Автоматически в поле Установить в ячейке отображается имя ячейки, которая была активной на момент выполнения команды Подбор параметра. Кнопка свёртывания окна диалога, расположенная справа от поля, позволяет временно убрать диалоговое окно с экрана, чтобы было удобнее выделить диапазон на листе. Выделив диапазон, следует нажать кнопку для вывода на экран диалогового окна.
3) в поле Значение ввести число, которое должно возвращать формула с искомым значением параметра. Например, 855.
4) в поле Изменяя значение ячейки указать ссылку на ячейку, содержащую параметр, значение которого требуется подобрать для получения требуемого результата. На эту ячейку прямо или косвенно должна ссылаться формула, содержащаяся в ячейке, адрес которой указан в поле Установить в ячейке. В нашем случае это А1.
В итоге диалоговое окно примет следующий вид:
5) нажать кнопку ОК для закрытия диалогового окна. После выполнения этого действия появляется запрос Результат подбора параметра, а искомое значение параметра отображается в ячейке А1:
Использование функции Подбор параметра для нахождения значения аргумента функции при изменении вида ее графика
Допустим, что для решения поставленной задачи нам предстоит проанализировать построенный в Ms Excel график функции y = 3x-5 в диапазоне аргумента от –3 до 6.
1) в ячейки А1-А10 ввести значения от –3 до 6 с шагом 1; в ячейку В1 – ввести формулу 
и путём перетаскивания маркера заполнения скопировать эту формулу на ячейки В2-В10. В результате соответствующий участок листа примет следующий вид:
2) выделив диапазон В1-В10, выберите тип диаграммы «График» на вкладке Вставить в группе Диаграммы.
3) На вкладке Макет в группе Подписи нажмите кнопку Подписи данных, а затем выберите нужный параметр отображения.
4) На вкладке Конструктор в группе Данные нажмите Выбрать данные.
5) В появившемся окне Выбор источника данных выберите Подписи горизонтальной оси. Задайте диапазон подписей оси диапазон А1-А10.
В результате должен быть построен график функции:
Далее предположим, что необходимо узнать значение аргумента данной функции, при котором значение самой функции будет равно 0.
Чтобы решить эту задачу с помощью построенного графика и функции Подбор параметра необходимо:
1) щелчком левой кнопки мыши на графике выделить ряд данных, содержащий маркер данных, который нужно изменить,
а затем выделить щелчком сам маркер
2) в меню Данные > Анализ выбрать функцию Поиск решения
3) если значение маркера данных получено из формулы, появится диалоговое окно Подбор параметра:
4) в поле Оптимизировать целевую функцию отображается ссылка на ячейку, содержащую формулу, в поле Значения – требуемая величина. Так, в данном случае следует указать ячейку В6 и значение «0» соответственно и нажать кнопку ОК.
При поиске решения можно изменять только одну ячейку.
При этом исходное значение аргумента в ряде данных сменится на значение, полученное в результате поиска решения 1,666667 (рис. 11. рис. 11.11).
Решение уравнений
Поиск решения позволяет находить одно значение аргумента, соответствующее заданному значению функции (например, 0). Однако часто функция может принимать одно значение при нескольких значениях аргументов. То есть уравнение может иметь несколько корней. Например, функция 
может принимать значение 0 при двух значениях аргументов.
Однако, функция Поиск решения найдет только один корень уравнения – самый близкий к значению в ячейке, указанной в поле Оптимизировать целевую функцию.
Так, если попытаться решить указанное выше уравнение с помощью Ms Excel и встроенной в него функции Подбор параметра, то исходные данные можно представить в следующем виде:
Выполнив команду Подбор параметра из меню Данные > Анализ «Что-если», необходимо заполнить поля диалогового окна следующим образом:
В результате найденным корнем уравнения будет значение 2,121343 в ячейке А4 (рис. 11. рис. 11.14).
Однако, это не единственный корень. В этом можно убедиться, решив уравнение или построив график функции .
Для построения графика следует:
В результате должен быть построен график функции:
Из графика видно, что уравнение 
имеет 2 корня, к тому же эти корни примерно равны –2 и 2. Одни корень 2,121343 нам уже известен.
Для поиска второго корня можно поступить двояко, используя пункт А или Б методических указаний ниже:
А. Изменим значение, например, в ячейке С12 (более близкое к ожидаемому корню). Выделим ячейку D12 и выполним команду Подбор параметра из меню Данные > Анализ «Что-если». Заполним поля запроса:
Щелчком левой кнопки мыши на графике выделим ряд данных, содержащий маркер данных, близкий ко второму корню:
Сделаем активной ячейку D11 со значением функции, равным 9 и в меню Данные > Анализ «что-если» выберем Подбор параметра. Заполним поле Изменяя значение ячейки запроса:
Следует обратить внимание, что значения корня, полученные в п.А и п.Б имеют несущественное отличие. Это вызвано следующим обстоятельством. По умолчанию команда Подбор параметра прекращает итерационные вычисления, когда выполняется 100 итераций, либо при получении результата, который находится в пределах 0,001 от заданного целевого значения. Если нужна большая точность, можно изменить используемые по умолчанию параметры командой Параметры меню Файл > Формулы. Затем на вкладке Параметры вычислений в поле Предельное число итераций введите значение больше 100, а в поле Относительная погрешность – значение меньше 0,001.
Если Ms Excel выполняет сложную задачу подбора параметра, можно нажать кнопку Пауза в окне запроса Результат подбора параметра и прервать вычисления, а затем нажать кнопку Шаг, чтобы просмотреть результаты каждой последовательной итерации. Когда Вы решаете задачу в пошаговом режиме, в этом окне запроса появляется кнопка Продолжить. Нажмите ее, когда решите вернуться в обычный режим подбора параметра.
Задание
Решить уравнение с использованием инструмента Excel Подбор параметра двумя способами:
Удостовериться с помощью построения графика в количестве корней уравнения. Определить все корни.