Тема 5. Вычисление значений функции. Средство анализа «Подбор параметра» (2 часа)

Модель любого элементарного экономического процесса представляет собой систему некоторых функций, имеющих один или несколько параметров. Задачи экономического анализа и планирования сводятся к исследованию этих функций и к изучению влияния различных параметров этих функций на эффективность экономического процесса.

Для решения задачнахождения параметров, дающих заданное значение функции, в программе MS Excel является функция«Подбор параметра». Это часть блока задач инструмента «Анализ «Что-Если»».Это простой и легкий в использовании инструмент, предназначенный для вычисления одного из входных значений (параметров) некоторой функции для того, чтобы формула возвращала требуемый результат. Если Вы знаете, какой должен быть результат, программа сама найдет значение одной из нескольких ячеек, необходимое для достижения этого результата.

В упрощенном виде его назначение можно сформулировать так: найти значения, которые нужно ввести в одиночную формулу, чтобы получить желаемый (известный) результат. Функция подбора параметра используется совместно с формулами.

Для применения данной функции необходимо ввести на лист MS Excel все входные параметры и выделить пустую ячейку для неизвестного входного параметра. В отдельную ячейку необходимо ввести формулу, зависящую от входных параметров, включая неизвестный параметр, т.е. в формуле будут содержаться ссылки на ячейки, содержащие входные данные, и на пустую ячейку. Далее вызывается функция подбора параметра (пункт горизонтального меню Данные / Анализ "что-если" / Подбор параметра). В всплывшем окне в поле ввода "Установить в ячейке" указывают ссылку на ячейку с формулой, в поле ввода "Значение" вводят конкретное ожидаемое числовое значение функции, а в поле ввода "Изменяя значение ячейки" указывают ссылку на пустую ячейку, в которой ожидается появление значения неизвестного параметра (рис.31).

Рис. 31. Окно функции подбора параметра

Обязательным условием применения функции подбора параметра является требование, чтобы в формуле была непосредственная или опосредованная (через другие формулы) ссылка на пустую ячейку, которая указана в поле "Изменяя значение ячейки"!

Пример 5.1. За какой срок денежный вклад увеличится вдвое при ставке 10% годовых по схеме простых процентов?

Решение. Наша задача – адекватно перенести все данные на лист MS Excel.Процентная ставка – 10% годовых. Коэффициент наращения A(t) = 2. Введем эти исходные данные в ячейки В2 и В3 соответственно. Ячейку В4 зарезервируем для результирующих данных, т.е. для срока вклада. Формула для расчетов: A(t) = 1 + i*t или 1 + i*t – A(t) = 0. Введем вторую формулу в ячейку В6: = 1 + В2 * В4 – В3. Введенные данные представлены на рисунке 32.

Рис. 32. Компьютерная модель примера

После завершения ввода формулы в ячейке В6 отобразится выражение #ЗНАЧ!, т.к. в качестве операнда в ячейку В4 введен знак "?", а не число. Введем туда временно любое числовое значение, например1 (или 0), тогда в ячейке В6 отобразится значение –0,9. Далее для нахождения срока вклада используем функцию подбора параметра. Заполним всплывшее окно, как показано на рисунке 1. После нажатия кнопки "Ok" в ячейке В6 отобразится значение 10, это соответствует ответу на вопрос, что денежный вклад увеличится вдвое при ставке 10 % годовых по схеме простых процентов через 10лет.

Пример 5.2. Какую сумму должен внести инвестор сегодня под 14 простых годовых процентов, чтобы накопить 210тыс. руб: 1) за год; 2) за 2 года?

Решение. Мы построим одну модель для ответа на все три вопроса и будем последовательно менять значения в ячейке В3, содержащей срок вклада. Первоначально введем туда значение 1, соответствующее 1 году. В ячейку В4 в качестве суммы инвестиций также введем значение 1, так как мы не знаем истинного значения этой ячейки, и в процессе решения задачи с использованием функции подбора параметра оно буден найдено. Нам осталось ввести в ячейку В7 формулу зависимости накопленной суммы от суммы инвестиций, котораябудет иметь вид: =B4*(1+B3*B2). В ячейку D7 введем значение 210, чтобы помнить, что мы должны иметь в ячейке В7.

Как вы уже обратили внимание, большинство задач можно решить, зная формулу вычисления наращенной суммы!

Модель задачи готова и имеет вид, представленный на рисунке 33.

Рис. 33. Модель примера 9 без решения

Вызовем функцию подбора параметра (пункт горизонтального меню Данные / Анализ "что-если" / Подбор параметра), где в появившемся окне заполним: Установить в ячейке В7 Значение 210, Изменяя значениеячейки В4. MS Excel сообщит, что решение найдено, и, после нажатия клавиши Enter, оно отобразится на листе (рис. 34).

Рис. 34. Решение примера 2 (1) с использованием функции Подбор параметра

Далее меняем значение в ячейке В3 с 1 на 2 и повторяем операцию подбора параметра. Результат решения представлен на рисунке 35.

Рис. 35. Решение примера 2 (2)

Пример 5.3.Найти корень уравнения e^(x+5)+x =0.

Решение. Выделим на листеExcel две ячейки – одну для переменной х, другую – для функции (рис. 36). Введем в ячейку А3для переменной х любое значение, например, 1. В ячейку В3введем значение функции, где в качестве х указываем ячейку А3. Далее вызываем функцию подбора параметра (пункт меню Данные / Анализ "что-если" / Подбор параметра), где в появившемся окне укажем: Установить в ячейке В3Значение0, Изменяя значениеячейкиА3. MS Excel сообщит, что решение найдено, и после нажатия клавиши Enterв ячейке В3отобразится решение с некоторой допустимой погрешностью (рис. 36).

Рис. 36. Компьютерная модель примера 3

Примечание: Данное уравнение имеет один корень, что далеко не всегда так. Примеры на поиск двух и более корней будут рассмотрены в следующей главе.