Тема. Поиск решений средствами ппп MS Excel.

Инструмент Подбор параметра из меню Сервис позволяет найти значение аргумента, удовлетворяющее желаемому значению функции. С его помощью можно получать результаты, которые трудно или невозможно получить непосредственно. Например, вычислим квадратный корень числа, не используя специальные математические функции. Пусть на рис. 6.1 в клетку А2 вносится аргумент, а в В2 – функция вычисления квадрата от него.

Найдем квадратный корень числа 25.

Выполнение

:

Сервис - Подбор параметра.

В диалоговом окне задать следующие аргументы (рис. 6.2):

установить в ячейке В2 адрес клетки, в которой вычисляется новое значение: 25, изменяя значение ячейки А2.

 

 

Подбор параметра? х

$A$2 В2 ОТМЕНА ОК Установить в ячейке:   Значение:   Изменяя значение ячейки:

Рис. 6.2

 

 

После нажатия кнопки ОК, Excel выдает окно Результат подбор параметра (рис. 6.3), где отображаются ожидаемые результаты операции. В данном случае системе удалось подобрать аргумент, при котором результат равен 25 (в клетке А2 мы увидим число 5). Далее, если Решение найдено и пользователь согласен с ним, следует нажать кнопку ОК, если нет – выбрать кнопку Отмена.

Результат подбора параметра? х

Пауза Шаг ОТМЕНА ОК Подбор параметра ячейки В2. Решение найдено.     Подбираемое значение: 25 Текущее значение: 25,00

Рис. 6.3

Примеры решения задач:

Задача. Пусть владелец фирмы договорился с некоторым исполнителем (Петров) о выполнении разовой работы за 5000 руб. ‘чистыми’ и с другим исполнителем (Олегров) – за 300 руб. Для того, чтобы провести эти операции в бухгалтерских отчетах, следует определить исходную сумму зарплаты до изъятия налогов и других вычетов (страховых отчислений, в пенсионный фонд и т.д.).Пусть также, зарплата в фирме вычисляется по формулам таблицы, изображенной на рис.6.4.

 

	A	B	C	D	E	F	G	H
	Миним. Зарп.		Расчет зарплаты
	ФИО	Зарплата	Кол-во детей	Сумма обложения	Подоходный налог	Медицин. страховка	Пенсионный фонд	К выдаче
	Иванов			=МАКС(В4- С$1*(1+C4); 0)	=12% *D4	=1% *D4	=2% *D4	=В4-Е4 –F4-G4
	Иванов

Рис. 6.4

 

Налогом облагается не вся зарплата. Согласно законодательству, сумма обложения меньше заработка на одну минимальную зарплату. И еще по одной минимальной зарплате на каждого ребенка. Здесь же учитываются и вычеты и в медицинскую страховку пенсионный фонд (например, 1% и 2% соответственно). Чтобы избежать отрицательности суммы обложения, в клетке D4 используется МАКС(), которая обеспечивает равенство нулю суммы обложения, если она становится отрицательной.

В верхней части таблицы представлены формулы, в нижней – результат для одного штатного работника организации Иванова. Для него расчет ведется в обычном порядке, – вводится заработок (1000 руб.) и автоматически вычитается результат (886руб.).

Определить исходную зарплату Петрова и Олегрова по известной сумме “к выдаче”.

Выполнение:

Для Петрова: скопировать в строки 5 и 6 формулы из строки 4 и запустим инструмент Подбор параметра, где введем данные, изображенные на рис. 6.5. Аналогично для Олегрова (рис. 6.6).

 

Подбор параметра		Подбор параметра
Установить в ячейке: $Н$5   Значение: 5000   Изменяя значение ячейки: $B$5	Установить в ячейке: $H$6   Значение: 300   Изменяя значение ячейки: $B$6

Рис.6.5 Рис. 6.6

Т.е. Петрову нужно выписать 5854 рублей “грязными”, Олегову (маленькая зарплата и много детей) начисленная сумма и сумма на руки (300 руб.) совпадают. Полученные результаты показаны рис. 6.7.

 

	A	B	C	D	E	F	G	H
		...
	Иванов
	Петров
	Олегров

Рис.6.7

Задача. Найти сумму прибыли от начального вклада в 1000 единиц в течение нескольких лет при непостоянной ежегодной норме прибыли.

Решение представлено на рис.6.8

	А	В	С			А	В	С
	Вклад					Вклад
	Сложные проценты					Сложные проценты
	Норма	Прибыль	Сумма			Норма	Прибыль	Сумма
	0,1	=A4*B1	=B1+B4			10%
	0,15	=A5*C4	=B5+C4			15%
	0,2	=A6*C5	=B6+C5			20%
	0,3	=A7*C6	=B7+C6			30%	455,4	1973,4
		=СУММ(B4:B7)					973,4

Рис.6.8

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

На рис.6.9 изображено окно Подбор параметра и результат подбора – 1540,99. При этом чистая прибыль составит 1500, а общий остаток на счету – 3040 единиц.

 

Подбор параметра
Установить в ячейке: $B$8   Значение: 1500   Изменяя значение ячейки: $B$1
		A	B	C
		Вклад	1540,99
		Сложные проценты
		Норма	Прибыль	Сумма
		10%	154,10	1695,09
		15%	254,26	1949,35
		20%	389,87	2339,22
		30%	701,77	3040,99
			1500,00

Рис.6.9

Варианты индивидуальных заданий:

Вариант 1.

Задача. Найти сумму прибыли от начального вклада в 3000 единиц в течение нескольких лет при непостоянной ежегодной норме прибыли: 15%, 20%, 22%. Определить сколько средств следует вложить, чтобы в конце расчетного периода получилось 3500 единиц прибыли при такой же ежегодной норме прибыли в течение нескольких лет.

 

Вариант 2.

Задача. Найти сумму прибыли от начального вклада в 13000 единиц в течение нескольких лет при непостоянной ежегодной норме прибыли: 11%, 21%, 22%. Определить сколько средств следует вложить, чтобы в конце расчетного периода получилось 23500 единиц прибыли при такой же ежегодной норме прибыли в течение нескольких лет.

Вариант 3.

Задача. Найти сумму прибыли от начального вклада в 5000 единиц в течение нескольких лет при непостоянной ежегодной норме прибыли: 5%, 12%, 15%. Определить сколько средств следует вложить, чтобы в конце расчетного периода получилось 3500 единиц прибыли при такой же ежегодной норме прибыли в течение нескольких лет.

 

Вариант 4.

Задача. Найти сумму прибыли от начального вклада в 4000 единиц в течение нескольких лет при непостоянной ежегодной норме прибыли: 7%, 12%, 20%. Определить сколько средств следует вложить, чтобы в конце расчетного периода получилось 15500 единиц прибыли при такой же ежегодной норме прибыли в течение нескольких лет.

Вариант 5.

Задача. Найти сумму прибыли от начального вклада в 7000 единиц в течение нескольких лет при непостоянной ежегодной норме прибыли: 10%, 12%, 25%. Определить сколько средств следует вложить, чтобы в конце расчетного периода получилось 55000 единиц прибыли при такой же ежегодной норме прибыли в течение нескольких лет.