Лабораторная работа №7 Тема: Использование средства Подбор параметров

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

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

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

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

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

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

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

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

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

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

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

	А	В	С	D
1	Решение корней уравнения ax+ by+cz=d
2
3	3	a	4	x
4	3	b	3	y
5	2	c	5	z
6	=(А3*С3)+(А4*С4)+(А5*С5)	d

Рис. 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. Использование средства Подбор параметров для выполнения банковских расчётов.