Tw-city.info

IT Новости
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

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

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

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

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

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

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

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

  1. Переходим во вкладку “Данные”, в которой нажимаем на кнопку “Анализ “что если” в группе инструментов “Прогноз”. В раскрывшемся списке выбираем “Подбор параметра” (в ранних версиях кнопка может находиться в группе “Работа с данными”).
  2. На экране появится окно для подбора параметра, которе нужно заполнить:
    • в значении поля “Установить в ячейке” пишем адрес с финальными данными, которые нам известны, т.е. это ячейка с суммой скидки. Вместо ручного ввода координат можно просто щелкнуть по нужной ячейке в самой таблице. При этом курсор должен быть в соответствующем поле для ввода информации.
    • В качестве значения указываем сумму скидки, которая нам известна – 560 руб.
    • В поле “Изменяя значение ячейки” вручную или посредством клика мышью указываем координаты ячейки (должна участвовать в формуле расчета суммы скидки), в которой планируем вывести начальное значение.
    • по готовности нажимаем OK.
  3. Программа выполнит расчеты и выдаст результат в небольшом окошке, которое можно закрыть, нажав кнопку OK. Также найденные значения автоматически появятся в заданных ячейках таблицы.
  4. Аналогичным образом можно посчитать цену без скидки для других товаров, если нам известна точная сумма скидки по каждому из них.

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

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

Например, нам нужно решить уравнение: 7x+17x-9x=75 .

  1. Пишем выражение в свободной ячейке, заменив символ x на адрес ячейки, значение которой нужно найти. В итоге формула выглядит так: =7*D2+17*D2-9*D2 .
  2. Щелкаем Enter и получаем результат в виде числа , что вполне логично, так как нам только предстоит вычислить значение ячейки D2, которе и является “иксом” в нашем уравнении.
  3. Как было описано в первом разделе статьи, во вкладке “Данные” нажимаем кнопку “Анализ “что если” и выбираем “Подбор параметра”.
  4. В появившемся окошке заполняем параметры:
    • В значении поля “Установить в ячейке” указываем координаты ячейки, в которой мы написали уравнение (т.е. B4).
    • В значении, согласно уравнению, пишем число 75.
    • В поле “Изменяя значения ячейки” указываем координаты ячейки, значение которой нужно найти. В нашем случае – это D2.
    • Когда все готово, нажимаем OK.
  5. Как и в примере, рассмотренном выше, будут произведены вычисления и получен результат, о чем будет свидетельствовать небольшой окошко.
  6. Таким образом, нам удалось решить уравнение и найти значение x, которое оказалось равным числу 5.

Заключение

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

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

Чтобы применить средство Подбор параметра, выполните команду Данные → Работа с данными → Подбор параметра. Откроется одноименное диалоговое окно, в котором надо заполнить все поля ввода, а затем щелкнуть на кнопке ОК. В результате появится диалоговое окно Результат подбора параметра.

Диалоговое окно Подбор параметра очень просто в использовании — в нем надо заполнить всего три поля ввода: Установить в ячейке, Значение и Изменяя значение ячейки, которые показаны на рис. 1.4.

Рис. 1.4. Диалоговое окно Подбор параметра

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

  1. В поле ввода Установить в ячейке введите адрес или просто, когда курсор будет находиться в этом поле, щелкните на ячейке, содержащей формулу, для результата вычисления которой вы хотите задать значение.
  2. В поле ввода Значение введите число, которое вы хотите увидеть в ячейке, указанной в поле Установить в ячейке.
  3. В поле ввода Изменяя значение ячейки введите адрес или просто щелкните на ячейке, содержащей числовое значение, которое вы хотите определить. Формула в ячейке, указанная в поле Установить в ячейке, обязательно должна прямо или опосредованно (через другие формулы) ссылаться на ячейку, которую вы указали в поле Изменяя значение ячейки.
Читать еще:  Как открыть excel в двух окнах

Заполнив все три поля ввода диалогового окна Подбор параметра, для начала работы данного средства щелкните в этом окне на кнопке ОК. После этого появится диалоговое окно Результат подбора параметра, которое сообщит, что решение найдено. Обратите внимание на два числа, отображаемые в этом окне как Подбираемое значение и Текущее значение.

Подбираемое значение, — это то значение, которое вы указали в поле Значение диалогового окна Подбор параметра, а Текущее значение — то значение, которое Excel смогла добиться от формулы (указанной в поле Установить в ячейке диалогового окна Подбор параметра) при подборе параметра, заданного в поле Изменяя значение ячейки того же окна Подбор параметра. Если числа Подбираемое значение и Текущее значение совпадают, это означает, что Excel действительно нашла решение задачи.

Для примера рассмотрим рабочий лист, показанный на рис. 1.5, где в ячейке А1 содержится значение градусов по Фаренгейту, а в ячейке А2 записана формула =ПРЕОБР(А1;»Р»;»С»), преобразующая значение градусов по Фаренгейту в значение градусов по Цельсию. Введите значение 100 в ячейку А1 и вы получите значение 37,8 в ячейке А2. Допустим, теперь вы хотите узнать, сколько градусов по Фаренгейту составляют 20 градусов по Цельсию.

Рис. 1.5. Преобразование значения температуры по Фаренгейту в значение температуры по Цельсию

Чтобы удовлетворить свое любопытство, вы должны выполнить такие действия.

  1. Выберите команду Данные → Работа с данными → Подбор параметра. Откроется диалоговое окно Подбор параметра.
  2. В поле ввода Установить в ячейке введите А2 или щелкните на ячейке А2.
  3. В поле ввода Значение введите число 20.
  4. В поле ввода Изменяя значение ячейки введите А1 или щелкните на ячейке А1.
  5. Щелкните на кнопке ОК.

После этих действий откроется диалоговое окно Результат подбора параметра, где оба значения, Подбираемое значение и Текущее значение, будут равняться числу 20. Таким образом, Excel найдет искомое решение, которое будет отображаться в ячейке А1 как число 68.

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

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

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

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

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

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

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

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

  1. Установить в ячейке – место, куда необходимо вставить ссылку на формулу.
  2. Значение – сюда вводится числовое значение, которое необходимо получить в ходе расчетов.
  3. Изменяя значение ячейки – ссылка на число, которое и будет решением задачи.

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

  1. Конечный результат должен быть выражен в виде формулы.
  2. Ссылка на изменяемую ячейку должна быть абсолютной, то есть содержать значки доллара.
  3. При работе с финансовыми функциями число должно быть отрицательным. Это нужно для получения корректного решения.

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

Задача 1.

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

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

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

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

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

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

Задача 2.

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

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

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

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

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

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

Читать еще:  Как растянуть ячейку в excel

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

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

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

Суть функции

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

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

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

Имеем таблицу выплат заработной платы и премии работникам предприятия. Известны только премии работников. Например, премия одного из них — Николаева А. Д, составляет 6035,68 рублей. Также, известно, что премия рассчитывается путем умножения заработной платы на коэффициент 0,28. Нам предстоит найти заработную плату работников.

Для того, чтобы запустить функцию, находясь во вкладке «Данные», жмем на кнопку «Анализ «что если»», которая расположена в блоке инструментов «Работа с данными» на ленте. Появляется меню, в котором нужно выбрать пункт «Подбор параметра…».

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

В поле «Значение» требуется указать конкретное значение премии. В нашем случае, это будет 6035,68. В поле «Изменяя значения ячейки» вписываем ее адрес, содержащей исходные данные, которые нам нужно рассчитать, то есть сумму зарплаты работника. Это можно сделать теми же способами, о которых мы говорили выше: вбить координаты вручную, или кликнуть по соответствующей ячейке.

Когда все данные окна параметров заполнены, жмем на кнопку «OK».

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

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

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

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

Допустим, имеем уравнение: 15x+18x=46. Записываем его левую часть, как формулу, в одну из ячеек. Как и для любой формулы в Экселе, перед уравнением ставим знак «=». Но, при этом, вместо знака x устанавливаем адрес ячейки, куда будет выводиться результат искомого значения.

В нашем случае, формулу мы запишем в C2, а искомое значение будет выводиться в B2. Таким образом, запись в ячейке C2 будет иметь следующий вид: «=15*B2+18*B2».

Запускаем функцию тем же способом, как было описано выше, то есть, нажав на кнопку «Анализ «что если»» на ленте», и перейдя по пункту «Подбор параметра…».

В открывшемся окне подбора параметра, в поле «Установить в ячейке» указываем адрес, по которому мы записали уравнение (C2). В поле «Значение» вписываем число 45, так как мы помним, что уравнение выглядит следующим образом: 15x+18x=46. В поле «Изменяя значения ячейки» мы указываем адрес, куда будет выводиться значение x, то есть, собственно, решение уравнения (B2). После того, как мы ввели эти данные, жмем на кнопку «OK».

Как видим, программа Microsoft Excel успешно решила уравнение. Значение x будет равно 1,39 в периоде.

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

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

Анализ “что если” в Excel

Excel содержит множество мощных инструментов для выполнения сложных математических вычислений, например, Анализ “что если”. Этот инструмент способен экспериментальным путем найти решение по Вашим исходным данным, даже если данные являются неполными. В этом уроке Вы узнаете, как использовать один из инструментов анализа “что если” под названием Подбор параметра.

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

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

Читать еще:  Настройка поиск решения в excel

Как использовать Подбор параметра (пример 1):

Представьте, что Вы поступаете в определенное учебное заведение. На данный момент Вами набрано 65 баллов, а необходимо минимум 70 баллов, чтобы пройти отбор. К счастью, есть последнее задание, которое способно повысить количество Ваших баллов. В данной ситуации можно воспользоваться Подбором параметра, чтобы выяснить, какой балл необходимо получить за последнее задание, чтобы поступить в учебное заведение.

На изображении ниже видно, что Ваши баллы за первые два задания (тест и письменная работа) составляют 58, 70, 72 и 60. Несмотря на то, что мы не знаем, каким будет балл за последнее задание (тестирование 3), мы можем написать формулу, которая вычислит средний балл сразу за все задания. Все, что нам необходимо, это вычислить среднее арифметическое для всех пяти оценок. Для этого введите выражение =СРЗНАЧ(B2:B6) в ячейку B7. После того как Вы примените Подбор параметра к решению этой задачи, в ячейке B6 отобразится минимальный балл, который необходимо получить, чтобы поступить в учебное заведение.

  1. Выберите ячейку, значение которой необходимо получить. Каждый раз при использовании инструмента Подбор параметра, Вам необходимо выбирать ячейку, которая уже содержит формулу или функцию. В нашем случае мы выберем ячейку B7, поскольку она содержит формулу =СРЗНАЧ(B2:B6).
  2. На вкладке Данные выберите команду Анализ “что если”, а затем в выпадающем меню нажмите Подбор параметра.
  3. Появится диалоговое окно с тремя полями:
    • Установить в ячейке – ячейка, которая содержит требуемый результат. В нашем случае это ячейка B7 и мы уже выделили ее.
    • Значение – требуемый результат, т.е. результат, который должен получиться в ячейке B7. В нашем примере мы введем 70, поскольку нужно набрать минимум 70 баллов, чтобы поступить.
    • Изменяя значение ячейки – ячейка, куда Excel выведет результат. В нашем случае мы выберем ячейку B6, поскольку хотим узнать оценку, которую требуется получить на последнем задании.
  4. Выполнив все шаги, нажмите ОК.
  5. Excel вычислит результат и в диалоговом окне Результат подбора параметра сообщит решение, если оно есть. Нажмите ОК.
  6. Результат появится в указанной ячейке. В нашем примере Подбор параметра установил, что требуется получить минимум 90 баллов за последнее задание, чтобы пройти дальше.

Как использовать Подбор параметра (пример 2):

Давайте представим, что Вы планируете событие и хотите пригласить такое количество гостей, чтобы не превысить бюджет в $500. Можно воспользоваться Подбором параметра, чтобы вычислить число гостей, которое можно пригласить. В следующем примере ячейка B4 содержит формулу =B1+B2*B3, которая суммирует общую стоимость аренды помещения и стоимость приема всех гостей (цена за 1 гостя умножается на их количество).

  1. Выделите ячейку, значение которой необходимо изменить. В нашем случае мы выделим ячейку B4.
  2. На вкладке Данные выберите команду Анализ “что если”, а затем в выпадающем меню нажмите Подбор параметра.
  3. Появится диалоговое окно с тремя полями:
    • Установить в ячейке – ячейка, которая содержит требуемый результат. В нашем примере ячейка B4 уже выделена.
    • Значение – требуемый результат. Мы введем 500, поскольку допустимо потратить $500.
    • Изменяя значение ячейки – ячейка, куда Excel выведет результат. Мы выделим ячейку B3, поскольку требуется вычислить количество гостей, которое можно пригласить, не превысив бюджет в $500.
  4. Выполнив все пункты, нажмите ОК.
  5. Диалоговое окно Результат подбора параметра сообщит, удалось ли найти решение. Нажмите OK.
  6. Результат появится в указанной ячейке. В нашем случае Подбор параметра вычислил результат 18,62. Поскольку мы считаем количество гостей, то наш окончательный ответ должен быть целым числом. Мы можем округлить результат в большую или меньшую сторону. Округлив количество гостей в большую сторону, мы превысим заданный бюджет, значит, остановимся на 18-ти гостях.

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

Другие типы анализа “что если”

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

  • Диспетчер сценариев позволяет подставлять значения сразу в несколько ячеек (до 32). Вы можете создать несколько сценариев, а затем сравнить их, не изменяя значений вручную. В следующем примере мы используем сценарии, чтобы сравнить несколько различных мест для проведения мероприятия.
  • Таблицыданных позволяют взять одну из двух переменных в формуле и заменить ее любым количеством значений, а полученные результаты свести в таблицу. Этот инструмент обладает широчайшими возможностями, поскольку выводит сразу множество результатов, в отличие от Диспетчера сценариев или Подбора параметра. В следующем примере видно 24 возможных результата по ежемесячным платежам за кредит:
Ссылка на основную публикацию
ВсеИнструменты 220 Вольт
Adblock
detector
×
×