Goal seek excel как использовать
На этом шаге мы рассмотрим параметры этого метода и пример применения .
Метод GoalSeek (Подбор параметра) подбирает значение параметра (неизвестной величины), являющееся решением уравнения с одной переменной. Предполагается, что уравнение приведено к виду: правая часть является постоянной, не зависящей от параметра, который входит только в левую часть уравнения. Вручную метод GoalSeek выполняется с помощью команды Сервис | Подбор параметра (Tools | Goal Seek) . Метод GoalSeek вычисляет корень, используя метод последовательных приближений, результат выполнения которого, вообще говоря, зависит от начального приближения. Поэтому для корректности нахождения корня надо позаботиться о корректном указании этого начального приближения.
| Аргумент | Назначение |
|---|---|
| Объект | Ячейка, в которую введена формула, являющаяся правой частью решаемого уравнения. В этой формуле роль параметра (неизвестной величины) играет ссылка на ячейку, указанную в аргументе ChangingCell |
| Goal | Значение левой части решаемого уравнения, не содержащей параметра |
| ChangingCell | Ссылка на ячейку, отведенную под параметр (неизвестную величину). Значение, введенное в данную ячейку до активизации метода GoalSeek , рассматривается как начальное приближение к искомому корню |
Точность, с которой находится корень и предельно допустимое число итераций, используемых для нахождения корня, устанавливается свойствами MaxChange и MaxIterations объекта Application . Например, определение корня с точностью до 0,0001 максимум за 1000 итераций устанавливается инструкцией:
Вручную эти величины устанавливаются на вкладке Вычисления (Calculation) диалогового окна Параметры (Options) , вызываемого командой Сервис | Параметры (Tools | Options) .
- Ячейку A1 отведем под неизвестную. Команда Подбор параметра (Goal Seek) находит корень уравнения методом последовательных приближений, результат которого зависит от начального приближения к корню. Команда Подбор параметра (Goal Seek) воспринимает значение, первоначально введенное в ячейку A1 , за начальное приближение. Будем считать, что начальное приближение к корню равно 0. Введем 0 в ячейку A1 . В методе GoalSeek аргумент ChangingCell отвечает за ссылку на ячейку, отведенную под неизвестную. В данном случае аргументу ChangingCell присваиваем Range ("A1") .
- Ячейку А2 отведем под левую часть уравнения. При решении уравнения с помощью команды Подбор параметра (Goal Seek) уравнение надо преобразовать к такому виду, чтобы в правой его части содержалась только постоянная, не зависящая от неизвестной, которая должна входить только в его правую часть. В данном случае в ячейку А2 введем формулу =А1^2 (рисунок 1).
Рис.1. Ввод данных на рабочем листе при решении нелинейного уравнения
В методе GoalSeek диапазон, к которому применяется метод, отвечает за ссылку на ячейку, отведенную под левую часть уравнения, содержащую неизвестную. В данном случае метод GoalSeek применяется к диапазону Range ("A2") .
If you know the result that you want from a formula, but are not sure what input value the formula needs to get that result, use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long you want to take to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you will need to secure in order to meet your loan goal.
If you know the result that you want from a formula, but are not sure what input value the formula needs to get that result, use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long you want to take to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you will need to secure in order to meet your loan goal.
Note: Goal Seek works only with one variable input value. If you want to accept more than one input value; for example, both the loan amount and the monthly payment amount for a loan, you use the Solver add-in. For more information, see Define and solve a problem by using Solver.
Step-by-step with an example
Let's look at the preceding example, step-by-step.
Because you want to calculate the loan interest rate needed to meet your goal, you use the PMT function. The PMT function calculates a monthly payment amount. In this example, the monthly payment amount is the goal that you seek.
Prepare the worksheet
Open a new, blank worksheet.
First, add some labels in the first column to make it easier to read the worksheet.
In cell A1, type Loan Amount.
In cell A2, type Term in Months.
In cell A3, type Interest Rate.
In cell A4, type Payment.
Next, add the values that you know.
In cell B1, type 100000. This is the amount that you want to borrow.
In cell B2, type 180. This is the number of months that you want to pay off the loan.
Note: Although you know the payment amount that you want, you do not enter it as a value, because the payment amount is a result of the formula. Instead, you add the formula to the worksheet and specify the payment value at a later step, when you use Goal Seek.
Next, add the formula for which you have a goal. For the example, use the PMT function:
In cell B4, type =PMT(B3/12,B2,B1). This formula calculates the payment amount. In this example, you want to pay $900 each month. You don't enter that amount here, because you want to use Goal Seek to determine the interest rate, and Goal Seek requires that you start with a formula.
The formula refers to cells B1 and B2, which contain values that you specified in preceding steps. The formula also refers to cell B3, which is where you will specify that Goal Seek put the interest rate. The formula divides the value in B3 by 12 because you specified a monthly payment, and the PMT function assumes an annual interest rate.
Because there is no value in cell B3, Excel assumes a 0% interest rate and, using the values in the example, returns a payment of $555.56. You can ignore that value for now.
Use Goal Seek to determine the interest rate
On the Data tab, in the Data Tools group, click What-If Analysis, and then click Goal Seek.
In the Set cell box, enter the reference for the cell that contains the formula that you want to resolve. In the example, this reference is cell B4.
In the To value box, type the formula result that you want. In the example, this is -900. Note that this number is negative because it represents a payment.
In the By changing cell box, enter the reference for the cell that contains the value that you want to adjust. In the example, this reference is cell B3.
Note: The cell that Goal Seek changes must be referenced by the formula in the cell that you specified in the Set cell box.
Goal Seek runs and produces a result, as shown in the following illustration.
Cells B1, B2, and B3 are the values for the loan amount, term length, and interest rate.
Cell B4 displays the result of the formula =PMT(B3/12,B2,B1).
Finally, format the target cell (B3) so that it displays the result as a percentage.
On the Home tab, in the Number group, click Percentage.
Click Increase Decimal or Decrease Decimal to set the number of decimal places.
If you know the result that you want from a formula, but are not sure what input value the formula needs to get that result, use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long you want to take to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you will need to secure in order to meet your loan goal.
Note: Goal Seek works only with one variable input value. If you want to accept more than one input value, for example, both the loan amount and the monthly payment amount for a loan, use the Solver add-in. For more information, see Define and solve a problem by using Solver.
Step-by-step with an example
Let's look at the preceding example, step-by-step.
Because you want to calculate the loan interest rate needed to meet your goal, you use the PMT function. The PMT function calculates a monthly payment amount. In this example, the monthly payment amount is the goal that you seek.
Prepare the worksheet
Open a new, blank worksheet.
First, add some labels in the first column to make it easier to read the worksheet.
In cell A1, type Loan Amount.
In cell A2, type Term in Months.
In cell A3, type Interest Rate.
In cell A4, type Payment.
Next, add the values that you know.
In cell B1, type 100000. This is the amount that you want to borrow.
In cell B2, type 180. This is the number of months that you want to pay off the loan.
Note: Although you know the payment amount that you want, you do not enter it as a value, because the payment amount is a result of the formula. Instead, you add the formula to the worksheet and specify the payment value at a later step, when you use Goal Seek.
Next, add the formula for which you have a goal. For the example, use the PMT function:
In cell B4, type =PMT(B3/12,B2,B1). This formula calculates the payment amount. In this example, you want to pay $900 each month. You don't enter that amount here, because you want to use Goal Seek to determine the interest rate, and Goal Seek requires that you start with a formula.
The formula refers to cells B1 and B2, which contain values that you specified in preceding steps. The formula also refers to cell B3, which is where you will specify that Goal Seek put the interest rate. The formula divides the value in B3 by 12 because you specified a monthly payment, and the PMT function assumes an annual interest rate.
Because there is no value in cell B3, Excel assumes a 0% interest rate and, using the values in the example, returns a payment of $555.56. You can ignore that value for now.
Use Goal Seek to determine the interest rate
Do one of the following:
In Excel 2016 for Mac: On the Data tab, click What-If Analysis, and then click Goal Seek.
In Excel for Mac 2011: On the Data tab, in the Data Tools group, click What-If Analysis, and then click Goal Seek.
In the Set cell box, enter the reference for the cell that contains the formula that you want to resolve. In the example, this reference is cell B4.
In the To value box, type the formula result that you want. In the example, this is -900. Note that this number is negative because it represents a payment.
In the By changing cell box, enter the reference for the cell that contains the value that you want to adjust. In the example, this reference is cell B3.
Note: The cell that Goal Seek changes must be referenced by the formula in the cell that you specified in the Set cell box.
Goal Seek runs and produces a result, as shown in the following illustration.
Finally, format the target cell (B3) so that it displays the result as a percentage. Follow one of these steps:
In Excel 2016 for Mac: On the Home tab, click Increase Decimal or Decrease Decimal .
In Excel for Mac 2011: On the Home tab, under Number group, click Increase Decimal or Decrease Decimal to set the number of decimal places.
Excel очень эффективен, когда у вас есть все данные, необходимые для ваших расчетов.
Но было бы неплохо, если бы решить для неизвестных переменных?
С Goal Seek и надстройкой Solver это возможно. И мы покажем вам, как. Продолжайте читать для полного руководства о том, как решить для единственной ячейки с Поиск цели или более сложное уравнение с Решателем.
Как использовать поиск цели в Excel
Поиск цели уже встроен в Excel. Это под Данные вкладка, в Что, если анализ меню:
Для этого примера мы будем использовать очень простой набор чисел. У нас есть три четверти продаж и годовая цель. Мы можем использовать поиск цели, чтобы выяснить, какие цифры должны быть в 4 квартале, чтобы достичь цели.
Как видите, текущий общий объем продаж составляет 114 706 единиц. Если мы хотим продать 250 000 к концу года, сколько нам нужно продать в 4 квартале? Excel Goal Seek расскажет нам.
Вот, как использовать Goal Seek, шаг за шагом:
- Нажмите Данные> Что-если анализ> Поиск цели. Вы увидите это окно:
- Поместите «равную» часть вашего уравнения в Установить ячейку поле. Это число, которое Excel попытается оптимизировать. В нашем случае это общее количество продаж в ячейке B5.
- Введите значение вашей цели в Ценить, оценивать поле. Мы ищем в общей сложности 250 000 проданных единиц, поэтому мы разместим «250 000» в этой области.
- Скажите Excel, какую переменную нужно найти в Изменяя клетку поле. Мы хотим увидеть, какими должны быть наши продажи в 4 квартале. Поэтому мы скажем Excel решить для ячейки D2. Когда все будет готово, это будет выглядеть так:
- Удар Хорошо решить для вашей цели. Когда все выглядит хорошо, просто нажмите Хорошо. Excel сообщит вам, когда Goal Seek найдет решение.
- Нажмите Хорошо снова, и вы увидите значение, которое решает ваше уравнение в ячейке, которую вы выбрали для Изменяя клетку.
В нашем случае решение составляет 135 294 единицы. Конечно, мы могли бы просто найти это, вычтя промежуточную сумму из годовой цели. Но поиск цели также может быть использован в ячейке, уже есть данные в нем. И это более полезно.
Обратите внимание, что Excel перезаписывает наши предыдущие данные. Это хорошая идея запустить поиск цели на копию ваших данных. Также рекомендуется записать скопированные данные, полученные с помощью Goal Seek. Вы не хотите путать это с текущими, точными данными.
, но это не так уж и впечатляет. Давайте взглянем на инструмент, который гораздо интереснее: надстройка Solver.
Короче Солвер это как многомерная версия Goal Seek. Он принимает одну переменную цели и корректирует ряд других переменных, пока не получит желаемый ответ.
Он может определить максимальное значение числа, минимальное значение числа или точное число.
И это работает в рамках ограничений, поэтому, если одна переменная не может быть изменена или может изменяться только в пределах указанного диапазона, Солвер примет это во внимание.
Это отличный способ найти несколько неизвестных переменных в Excel. Но найти и использовать это не просто.
Давайте взглянем на загрузку надстройки Solver, а затем рассмотрим, как использовать Solver в Excel 2016.
Как загрузить надстройку Solver
В Excel по умолчанию нет Солвера. Это надстройка, так что, как и другие мощные функции Excel
, вы должны загрузить его первым. К счастью, он уже на вашем компьютере.
Голова к Файл> Параметры> Надстройки. Затем нажмите на Идти рядом с Управление: надстройки Excel.
Если в этом раскрывающемся списке указано что-то отличное от «Надстройки Excel», вам нужно изменить его:
В появившемся окне вы увидите несколько вариантов. Убедитесь, что поле рядом с Solver Add-In проверен и ударил Хорошо.
Теперь вы увидите решающее устройство кнопка в Анализ группа из Данные вкладка:
Если вы уже использовали пакет анализа данных
, вы увидите кнопку Анализ данных. Если нет, Солвер появится сам.
Теперь, когда вы загрузили надстройку, давайте посмотрим, как ее использовать.
Как использовать Солвер в Excel
Любое действие Солвера состоит из трех частей: цель, переменные ячейки и ограничения. Мы пройдемся по каждому из этапов.
- Нажмите Данные> Солвер. Вы увидите окно Solver Parameters ниже. (Если вы не видите кнопку Solver, см. Предыдущий раздел о том, как загрузить надстройку Solver.)
- Установите цель своей ячейки и сообщите Excel свою цель. Цель находится в верхней части окна Солвера и состоит из двух частей: ячейка цели и выбор максимизации, минимизации или определенного значения.
Если вы выберете Максимум, Excel скорректирует ваши переменные, чтобы получить максимально возможное число в вашей целевой ячейке. Min наоборот: Солвер минимизирует количество целей. Ценность позволяет указать конкретный номер для поиска Солвера. - Выберите ячейки переменных, которые Excel может изменить. Ячейки переменной устанавливаются с помощью Изменяя переменные ячейки поле. Нажмите стрелку рядом с полем, затем нажмите и перетащите, чтобы выбрать ячейки, с которыми Солвер должен работать. Обратите внимание, что это все клетки это может варьироваться. Если вы не хотите, чтобы ячейка менялась, не выбирайте ее.
- Установите ограничения для нескольких или отдельных переменных. Наконец, мы подошли к ограничениям. Здесь Солвер действительно силен. Вместо того, чтобы менять любую ячейку переменной на любое желаемое число, вы можете указать ограничения, которые должны быть выполнены. Подробнее см. Раздел о том, как установить ограничения ниже.
- Как только вся эта информация будет на месте, нажмите Решать чтобы получить ответ. Excel обновит ваши данные, чтобы включить новые переменные (поэтому мы рекомендуем сначала создать копию ваших данных).
Вы также можете создавать отчеты, которые мы кратко рассмотрим в нашем примере Solver ниже.
Как установить ограничения в Солвере
Вы могли бы сказать Excel, что одна переменная должна быть больше 200. При попытке использовать разные значения переменных Excel не пойдет ниже 201 с этой конкретной переменной.
Чтобы добавить ограничение, нажмите добавлять Кнопка рядом со списком ограничений. Вы получите новое окно. Выберите ячейку (или ячейки) для ограничения в Ссылка на ячейку поле, затем выберите оператора.
Вот доступные операторы:
- (больше или равно)
- ИНТ (должно быть целым числом)
- бункер (должно быть 1 или 0)
- AllDifferent
AllDifferent немного сбивает с толку. Он указывает, что каждая ячейка в диапазоне, который вы выбираете для Ссылка на ячейку должен быть другой номер. Но это также указывает, что они должны быть между 1 и количеством ячеек. Поэтому, если у вас есть три ячейки, вы получите цифры 1, 2 и 3 (но не обязательно в этом порядке)
Наконец, добавьте значение для ограничения.
Важно помнить, что вы можете выбрать несколько ячеек для сотовой ссылки. Например, если вы хотите, чтобы шесть переменных имели значения больше 10, вы можете выбрать их все и сказать Solver, что они должны быть больше или равны 11. Вам не нужно добавлять ограничение для каждой ячейки.
Вы также можете использовать флажок в главном окне Solver, чтобы убедиться, что все значения, для которых вы не указали ограничения, являются неотрицательными. Если вы хотите, чтобы ваши переменные становились отрицательными, снимите этот флажок.
Пример решателя
Чтобы увидеть, как все это работает, мы будем использовать надстройку Solver для быстрого расчета. Вот данные, с которых мы начинаем:
В нем у нас есть пять разных работ, каждая из которых платит разные ставки. У нас также есть количество часов, которое теоретический работник работал на каждой из этих работ в течение данной недели. Мы можем использовать надстройку Solver, чтобы узнать, как максимизировать общую заработную плату, сохраняя определенные переменные в рамках некоторых ограничений.
Вот ограничения, которые мы будем использовать:
- Нет работы может упасть ниже четырех часов.
- Работа 2 должна быть больше восьми часов.
- Работа 5 должна быть менее чем за одиннадцать часов.
- Общее количество отработанных часов должно быть равно 40.
Это может быть полезно, чтобы написать ваши ограничения, как это, прежде чем использовать Solver.
Вот как мы настроили это в Solver:
Во-первых, обратите внимание, что Я создал копию таблицы поэтому мы не перезаписываем исходный, который содержит наше текущее рабочее время.
И, во-вторых, убедитесь, что значения в ограничениях больше-меньше один выше или ниже чем то, что я упомянул выше. Это потому, что нет вариантов больше или меньше, чем. Есть только больше или равно или меньше или равно.
Давайте хит Решать и посмотрим, что получится.
Солвер нашел решение! Как видно из приведенного выше окна, наши доходы увеличились на 130 долларов США. И все ограничения были выполнены.
Чтобы сохранить новые значения, убедитесь, что Keep Solver Solution проверен и ударил Хорошо.
Если вам нужна дополнительная информация, вы можете выбрать отчет в правой части окна. Выберите все отчеты, которые вы хотите, скажите Excel, хотите ли вы, чтобы они были изложены (я рекомендую это), и нажмите Хорошо.
Отчеты создаются на новых листах в вашей рабочей тетради и дают вам информацию о процессе, который надстройка Solver прошла, чтобы получить ваш ответ.
В нашем случае отчеты не очень захватывающие, и там не так много интересной информации. Но если вы запустите более сложное уравнение Солвера, вы можете найти некоторую полезную отчетную информацию в этих новых рабочих листах. Просто нажмите на + Кнопка на стороне любого отчета, чтобы получить больше информации:
Solver Advanced Options
Если вы не очень разбираетесь в статистике, вы можете игнорировать расширенные параметры Солвера и просто запустить ее как есть. Но если вы выполняете большие и сложные вычисления, вы можете посмотреть на них.
Наиболее очевидным является метод решения:
Вы можете выбрать между GRG Nonlinear, Simplex LP и Evolutionary. Excel предоставляет простое объяснение того, когда вы должны использовать каждый из них. Лучшее объяснение требует некоторых знаний статистики
Чтобы настроить дополнительные параметры, просто нажмите Опции кнопка. Вы можете сообщить Excel о целочисленной оптимальности, установить временные ограничения вычислений (полезно для массивных наборов данных) и настроить методы расчета GRG и Evolutionary при выполнении своих вычислений.
Поиск цели и решатель: вывод Excel на новый уровень
Теперь, когда вы знакомы с основами поиска неизвестных переменных в Excel, для вас открыт совершенно новый мир расчета электронных таблиц.
Goal Seek может помочь вам сэкономить время, ускорив некоторые вычисления, а Solver значительно расширит возможности вычислений в Excel
Это просто вопрос освоения с ними. Чем больше вы их используете, тем полезнее они станут.
Используете ли вы Goal Seek или Solver в своих таблицах? Какие еще советы вы можете дать, чтобы получить лучшие ответы из них? Поделитесь своими мыслями в комментариях ниже!
Очень полезной функцией в программе Microsoft Excel является Подбор параметра. Но, далеко не каждый пользователь знает о возможностях данного инструмента. С его помощью, можно подобрать исходное значение, отталкиваясь от конечного результата, которого нужно достичь. Давайте выясним, как можно использовать функцию подбора параметра в Microsoft Excel.
Суть функции
Если упрощенно говорить о сути функции Подбор параметра, то она заключается в том, что пользователь, может вычислить необходимые исходные данные для достижения конкретного результата. Эта функция похожа на инструмент Поиск решения, но является более упрощенным вариантом. Её можно использовать только в одиночных формулах, то есть для вычисления в каждой отдельной ячейке нужно запускать всякий раз данный инструмент заново. Кроме того, функция подбора параметра может оперировать только одним вводным, и одним искомым значением, что говорит о ней, как об инструменте с ограниченным функционалом.
Применение функции на практике
Для того, чтобы понять, как работает данная функция, лучше всего объяснить её суть на практическом примере. Мы будем объяснять работу инструмента на примере программы Microsoft Excel 2010, но алгоритм действий практически идентичен и в более поздних версиях этой программы, и в версии 2007 года.
Для того, чтобы запустить функцию, находясь во вкладке «Данные», жмем на кнопку «Анализ «что если»», которая расположена в блоке инструментов «Работа с данными» на ленте. Появляется меню, в котором нужно выбрать пункт «Подбор параметра…».
После этого, открывается окно подбора параметра. В поле «Установить в ячейке» нужно указать ее адрес, содержащей известные нам конечные данные, под которые мы будем подгонять расчет. В данном случае, это ячейка, где установлена премия работника Николаева. Адрес можно указать вручную, вбив его координаты в соответствующее поле. Если вы затрудняетесь, это сделать, или считаете неудобным, то просто кликните по нужной ячейке, и адрес будет вписан в поле.
В поле «Значение» требуется указать конкретное значение премии. В нашем случае, это будет 6035,68. В поле «Изменяя значения ячейки» вписываем ее адрес, содержащей исходные данные, которые нам нужно рассчитать, то есть сумму зарплаты работника. Это можно сделать теми же способами, о которых мы говорили выше: вбить координаты вручную, или кликнуть по соответствующей ячейке.
Когда все данные окна параметров заполнены, жмем на кнопку «OK».
После этого, совершается расчет, и в ячейки вписываются подобранные значения, о чем сообщает специальное информационное окно.
Подобную операцию можно проделать и для других строк таблицы, если известна величина премии остальных сотрудников предприятия.
Решение уравнений
Кроме того, хотя это и не является профильной возможностью данной функции, её можно использовать для решения уравнений. Правда, инструмент подбора параметра можно с успехом использовать только относительно уравнений с одним неизвестным.
Допустим, имеем уравнение: 15x+18x=46. Записываем его левую часть, как формулу, в одну из ячеек. Как и для любой формулы в Экселе, перед уравнением ставим знак «=». Но, при этом, вместо знака x устанавливаем адрес ячейки, куда будет выводиться результат искомого значения.
В нашем случае, формулу мы запишем в C2, а искомое значение будет выводиться в B2. Таким образом, запись в ячейке C2 будет иметь следующий вид: «=15*B2+18*B2».
Запускаем функцию тем же способом, как было описано выше, то есть, нажав на кнопку «Анализ «что если»» на ленте», и перейдя по пункту «Подбор параметра…».
Как видим, программа Microsoft Excel успешно решила уравнение. Значение x будет равно 1,39 в периоде.
Изучив инструмент Подбор параметра, мы выяснили, что это довольно простая, но вместе с тем полезная и удобная функция для поиска неизвестного числа. Её можно использовать как для табличных вычислений, так и для решения уравнений с одним неизвестным. Вместе с тем, по функционалу она уступает более мощному инструменту Поиск решения.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Читайте также: