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

Если известен результат, который требуется вычислить при помощи формулы, но неизвестны значения, которые необходимо ввести для получения этого результата, можно воспользоваться средством «Подбор параметра». С его помощью можно, например, изменять процентную ставку в ячейке B3 до тех пор, пока размер платежа в ячейке B4 не станет равен 900,00 руб.

Рис. 8.5

Например, предположим, что вы хотите узнать какую максимальную ссуду под закладную можно взять на 30 лет при процентной ставке 6,5 процента и условии, что месячные выплаты не будут превышать $2000 (рис.1). Чтобы использовать команду Подбор параметра для получения ответа на этот вопрос выполните описания, которые приводятся ниже:

1. Решите задачу с каким-нибудь начальным значением параметра. Например, в задаче о максимальной ссуде при размере ссуды в $500 000 месячные выплаты превышают $2000.

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

3. На вкладке Данные в группе Средства обработки данных выберите команду Анализ «что-если», а затем выберите в списке пункт Подбор параметра.

4. В окне диалога Подбор параметра оставьте без изменений значение в поле Установить в ячейке (там должна содержаться абсолютная ссылка на активную ячейку).

5. В поле Значение введите максимальное значение, которое вы хотите получить в качестве результата формулы, в данном случае – 2000.(Введите число со знаком «минус», потому что вы будите выплачивать деньги, а не получать).

6. В поле Изменяя значения ячейки введите ссылку или щелкните в листе на ячейке, значения которой является неизвестным. В данном случае это ячейка В1. (если ячейке В1 назначено имя, например Ссуда, в поле Изменяя значение ячейки можно ввести это имя.)

Рис. 8.6

7. Нажмите кнопку ОК или клавишу Enter, Excel выведет окно диалога Результат подбора параметра. Ответ на наш вопрос находиться в ячейке, заданной ранее в поле Изменяя значения ячейки. В данном примере результат 316422,64 появляется в ячейке В1 (рис.7).

8. Чтобы сохранить это значение, нажмите кнопку ОК в окне диалога Результат подбора параметра. Для восстановления значения, которое было в ячейке В1 до использования команды Подбор параметра, нажмите кнопку Отмена.

Рис. 8.7

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

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

Практические задания:

1. Рабочему листу дайте имя «Параметры параллелепипеда»

2. Дан прямоугольный параллелепипед с рёбрами x, y, z.

3. В ячейки A1, A2, A3, A5, A7, A9, A11 введите соответственно тексты x, y, z, V, S, d, Vш.

4. Ячейкам B1, B2, B3, B9 дайте имена x, y, z, d.

5. В ячейки B1, B2, B3 введите значения x, y, z.

Вариант	x	y	z

6. В ячейке B5 вычислите объём данного параллелепипеда по формуле V=xyz.

7. В ячейке B7 вычислите площадь поверхности данного параллелепипеда по формуле S=2(xy+yz+xz).

8. В ячейке B9 вычислите длину диагонали данного параллелепипеда по формуле d=КОРЕНЬ(x^2+y^2+z^2).

9. В ячейке B11 вычислите объем шара, диаметром которого является диагональ данного параллелепипеда, по формуле Vш =(d^3)/6.

10. Скопируйте лист дважды. Первой копии дайте имя «защита», а второй-«Подбор параметра».

11. Вернитесь к листу «Параметры параллелепипеда»: скройте 7 и 8 строки. Скройте формулы в ячейках B5 и B11. Скройте этот лист.

12. Перейдите на лист «Защита»: создайте на нём какой-либо объект WordArt. Защитите весь этот лист, кроме объекта WordArt, столбца A и ячейки B9 (введите пароль)ю

13. Перейдите к листу «Подбор параметра»: вычислите длину ребра y по известному d. Вычислите длину ребра x по известному S.

14. Защитите структуру текущей книги (без ввода пароля).

Вариант	S

Вариант	d

Дата добавления: 2015-05-26