Лабораторная работа №7

Средство Подбор параметров находит такое значение параметра, которое обеспечит требуемое значение, вычисленное по формуле, зависящей от этого параметра. Средство Подбор параметраприменяется тогда, когда вы знаете значение, которое должна возвращать формула, но не знаете входное значение для формулы, обеспечивающеежелаемый результат вычислений. Другими словами, это средство применяете в случае, если на рабочем листе имеется одна ячейка с числовым значением, а другая — с формулой, зависящей от значения в первой ячейке, и необходимо подобрать такое значение в первой ячейке, чтобы в ячейке с формулой получилось заданное вами значение. Например, в ячейке А1записана оптовая цена некоторого товара (595 руб.), а в ячейке А2записана формула =ОКРУГЛ(А1*(А1*8,8%);2),которая вычисляет розничную цену этого же товара, увеличивая его оптовую цену на 8,8%.Теперь необходимо узнать, какова оптовая цена другого товара, если его розничная цена равна 1099руб. Подборпараметра быстро определит, что в этом случае оптовая цена составляет 1010руб.

Чтобы применить средство Подбор параметра,на вкладке Данные в группе Работа с данными нажмите кнопку Анализ «что-если» и выберите Подбор параметра (рис. 1).

Рис. 1. Открытие средства Подбор параметра

Откроется одноименное диалоговое окно, в котором надо заполнить все поля ввода, а затем щелкнуть на кнопке ОК. В результате появится диалоговое окно Подбор параметра (рис 2).

Рис.2. Окно Подбор параметра

1. В поле ввода Установить в ячейке введите адрес ячейки, содержащей формулу, для результата вычислений которой вы хотите получить значение.

2. В поле ввода Значение введите число, которое должно получиться в ячейке, указанной в поле Установить в ячейке.

3. В поле ввода Изменяя значение ячейки введите адрес ячейки, содержащей числовое значение, которое вы хотите определить

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

1. Вычисление корней алгебраических уравнений.

Алгебраическое выражение ax+ by+cz=d по значениям переменных a, b, c, x, y и z вычисляет значение переменной d. Имея значения любых шести переменных, с помощью средства Подбор параметра можно вычислить значение седьмой переменной.

Подготовьте рабочий лист, содержащий следующие данные (рис. 3):

Рис. 3. Таблица данных для вычисления корней уравнения

1.1. Подбор параметров для вычисления переменной С.

Значения переменных:

а=1; b=2; d=12; x=1; y=2; z=1, найти значение переменной С.

Введите следующие значения в указанные ячейки: А3: 1, А4: 2, С3: 1, С4: 2, С5: 1.

1) Вызовите окно Подбор параметров.

2) В поле ввода Установить в ячейке введите имя ячейки, содержащей формулу (в данном примере А6), щёлкнув кнопкой по нужной ячейке.

3) В поле ввода Значение введите 12 (значение переменной d).

4) В поле ввода Изменяя значения в ячейке введите имя ячейки, содержащей значения переменной с (в данном примере А5). Нажмите ОК. При данном наборе переменных в результате вычисления уравнения получится 12, если переменная с=7.

1.2. Подбор параметров для вычисления переменной z.

Значения переменных:

а=2; b=4; с=3; d=65; x=5; y=7, найти значение переменной z.

Введите следующие значения в указанные ячейки: А3: 2, А4: 4, А5: 3, С3: 5, С4: 7.

1) Вызовите окно Подбор параметров.

2) В поле ввода Установить в ячейке введите имя ячейки, содержащей формулу (в данном примере А6), щёлкнув кнопкой по нужной ячейке.

3) В поле ввода Значение введите 65 (значение переменной d).

4) В поле ввода Изменяя значения в ячейке введите имя ячейки, содержащей значения переменной z (в данном примере С5). Нажмите ОК. При данном наборе переменных в результате вычисления уравнения получится 65, если переменная z=9.

1.3. Самостоятельно подберите параметры для вычисления переменной а при значениях переменных:

b=4; с=2; d=84; x=4; y=7; z =9, найти значение переменной а.

2. Использование средства Подбор параметров для выполнения банковских расчётов.

2.1. Кредит на покупку квартиры

Создайте таблицу, содержащую следующие данные (рис. 4):

Рис. 4. Таблица данных для расчёта кредита

В ячейку В5 вводится финансовая функция ПЛТ, вычисляющая платёж. В этой функции аргумент Ставка задает ежемесячную процентную ставку по кредиту (поэтому в нашей формуле этот аргумент равен В4/12), аргумент Кпер — количество периодов погашения кредита (ячейка В3), аргумент Пс — сумма кредита (ячейка В2).