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

С помощью команды Подбор параметра (Goal Seek) меню Сервис можно вычислить неизвестное    значение     определенного     параметра,     удовлетворяющего   итоговому желаемому результату. Допустим,  требуется узнать, какую максимальную ссуду вам по средствам взять в   банке,  если  срок  погашения  равен 30  годам,  процентная  ставка составляет 6,5%, а размер ежемесячных выплат не превышает $2000. Чтобы ответить на этот   вопрос,   прежде   всего   необходимо   составить    формулу   расчета   и   решить поставленную задачу при каком-либо начальном значении параметра. Например, на листе, изображенном на рис. 18.19, мы рассчитали, что размер ссуды в $500 000 не обеспечивает соблюдения условий, поскольку платить ежемесячно придется больше чем $2000.

Рис. 18.19. Найдем максимальную величину ссуды при фиксированном размере ежемесячных выплат с помощью команды Подбор параметра

Теперь рассмотрим процесс работы с командой Подбор параметра:

1.   Прежде  всего следует  присвоить имена  ячейкам  В1:В4.  Для  этого  выделите весь диапазон А1:В4, в  меню Вставка выберите команду  Имя ? Создать. Установите флажок В столбце Слева (Left Column) и нажмите кнопку ОК.

2.   Выделите ячейку с формулой, в данном случае ячейку В4.

3.   В меню Сервис выберите команду Подбор параметра (Goal Seek), чтобы вывести на экран одноименное диалоговое окно, показанное на рис. 18.20.

Рис. 18.20. Чтобы найти решение простого уравнения, заполните поля диалогового окна Подбор параметра

4.   Оставьте без изменения содержимое поля Установить в ячейке  (Set  Cell). (Здесь должна находиться ссылка на ячейку, содержащую  формулу.) В поле Значение (То Value) введите  ожидаемую величину  результата,  в  данном  случае  это  -2000.  (Знак минус перед числом означает, что вы будете платить, а не получать.)

5.   В поле Изменяя значение ячейки (By Changing Cell) введите ссылку или щелкните на   той   ячейке   рабочего   листа,   в    которой   требуется   определить   значение   для выполнения условий задачи. В нашем примере это ячейка В1. Если ячейкам назначены имена, то вместо абсолютной ссылки здесь лучше указать имя ячейки (в данном случае ссуда).

6.   Нажмите  кнопку  OК  или  клавишу Enter.  Откроется  диалоговое  окно  Результат подбора параметра (Goal Seek Status), показанное на рис. 18.21. Искомая величина будет  видна в   ячейке,  определенной   ранее  в   поле  Изменяя  значение  ячейки. Интересующий нас ответ на задачу — $316 422,64 — появится в ячейке В1.

7.   Чтобы  принять  ответ и  сохранить  его  в   ячейке  В1,  щелкните  на   кнопке  0К  в диалоговом окне  Результат  подбора  параметра.  Если  нужно  изменить  условия задачи, нажмите кнопку Отмена (Cancel) и введите новые опорные значения.

Рис. 18.21. В диалоговом окне Результат подбора параметра отображается информация о результате поиска решения

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

Если   программа   выполняет  сложный   расчет,   вы  всегда  вправе   прервать  процесс вычисления, нажав кнопку Пауза (Pause) в  диалоговом  окне Результат подбора параметра,  а  затем  щелкнуть  на  кнопке  Шаг  (Step)  и  перейти  в   пошаговый режим вычислений. Каждый раз, нажимая кнопку Шаг, вы будете выполнять расчет очередной итерации и видеть на экране промежуточный результат. При работе в пошаговом режиме в диалоговом окне появляется дополнительная кнопка Продолжить (Continue). Щелчок на ней возвращает к обычному (фоновому) режиму подбора параметра.

Замечания о точности и множественности решений

Предположим,  на  чистом  листе  вы  ввели в   ячейку  А1  формулу   =А2^2,  а  затем воспользовались командой Подбор параметра для  нахождения значения в  ячейке А1, при котором значение в  ячейке А2  станет равным 4. (Другими словами, в  диалоговом окне Подбор параметра в  поле Установить в ячейке находится ссылка $А$1, в  поле Значение — число 4 и ссылка $А$2 — в поле Изменяя значение ячейки.) Результат, продемонстрированный на  рис.  18.22,  может  вас   несколько  удивить —  ближайшее решение,  которое  предлагает  Excel   для  вычисления квадратного   корня  из  4,  равно 2,000023!

Рис. 18.22. Результат, возвращаемый командой Подбор параметра, при поиске квадратного корня из четырех

Но это вполне естественно исходя из природы итерационного процесса.  По умолчанию выполняемые вычисления прекращаются, как только количество итераций доходит до 100 или  при  получении  результата  с  относительной  погрешностью  0,001.  Если  требуется большая  точность,   нужно  изменить  эти  параметры,  открыв  вкладку  Вычисления (Calculation)  диалогового  окна   Параметры  и   установив   новые   значения   в    поля Предельное число итераций (Maximum Iteration)  и  Относительная погрешность (Maximum Change).

СМОТРИ ТАКЖЕ  

Более подробная информация о параметрах вычислений находится в главе 12 «Создание формул».

И   второе  предупреждение.   Будьте   внимательны  при   работе   с   командой   Подбор параметра.  Дело  в   том,  что  программа  находит  только  одно  решение  задачи  и  останавливает  вычисления, как  только  его  обнаруживает. Ведь  в   рассмотренном  выше примере  для  числа  4  существует два решения,  два квадратных корня:  +2  и  -2.  В ситуациях,  подобных  этой,  доманда  Подбор  параметра  предлагает  решение  с  тем  же знаком, что и начальное значение. Например, если начать  вычисления с числа -1, мы получим в качестве ответа значение -1,999917, а не +2,000023.

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий