Задача: Расчет размера пенсионных накоплений

2.3 Задача: Расчет размера пенсионных накоплений

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

Использование средства подбора параметра

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

Рис. 6 - Таблица для расчета размера пенсионных накоплений

В этой таблице указаны возраст, начиная с которого в пенсионный фонд вносятся платежи (А2), величина ежемесячного взноса (В2), период отчислений, рассчитанный по формуле

=60-А2

то есть предполагается, что речь идет о мужчине, который выйдет на пенсию в 60 лет (С2), а также величина процентной ставки (D2).

Сумма накоплений рассчитывается с помощью функции по следующей формуле:

=БС(D2;C2;-B2*12; 0;1)

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

БС (ставка; кпер: плата; нз: тип)

Аргументы функции: ставка — размер процентной ставки за период; кпер - общее число периодов выплат годовой ренты; плата - выплата, производимая в каждый период (это значение не может меняться на протяжении всего времени выплат), причем обычно плата состоит из основного платежа и платежа по процентам; нз — текущая стоимость или общая сумма всех будущих платежей, начиная с настоящего момента (по умолчанию — 0); тип — число, которое определяет, когда должна производиться выплата (0 — в конце периода, задается по умолчанию, 1 — в начале периода).

Формула имеет такой вид, так как предполагается, что проценты начисляются не ежемесячно, а в начале, следующего года за предыдущий год. Допустим, необходимо определить, в каком возрасте будущему пенсионеру надо начинать выплаты, чтобы потом получить прибавку к пенсии в размере 1000 руб. Для этого следует выделить ячейку, отведенную для представления результата (в данном случае F2), и вызвать команду “Сервис » Подбор параметра”. Когда появится диалоговое окно Подбор параметра (рис. 7), адрес выделенной ячейки будет автоматически вставлен в поле “Установить в ячейке”. Нужно указать в поле “Значение” целевое значение - 1000. Нужно поместить курсор ввода в поле “Изменяя значение ячейки” и выделить ячейку А2, после чего ее адрес отобразится в указанном поле.

Рис. 7 Диалоговое окно ”Подбор параметра” с заданными параметрами

Примечание: При использовании функции подбора параметра необходимо, чтобы ячейка с целевым значением содержала ссылку на ячейку с изменяемым значением.

После выполнения всех установок нужно нажать кнопку К, и поиск нужного значения будет начат. Результат вычисления отобразится в диалоговом окне “Результат подбора параметра”, а также в исходной таблице (рис, 8). После нажатия кнопки 0К полученные значения будут вставлены в таблицу.

Рис. 8 - Результаты подбора параметра

Если поиск нужного значения продолжается слишком долго, прервать его на время можно с помощью кнопки “Пауза”. Кнопка “Шаг” позволяет просмотреть промежуточные результаты вычисления.

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

1. Выбрать целевую ячейку, то есть ячейку с формулой, результат которой нужно подобрать.

2. Вызвать команду “Сервис » Подбор параметра”. В поле “Установить в ячейке” появившегося диалогового окна будет отображаться адрес целевой ячейки.

3. Задать в поле “Значение” значение, которое должна содержать целевая ячейка

4. Указать в поле “Изменяя значение ячейки” адрес ячейки, значение которой необходимо установить таким, чтобы в целевой ячейке получить заданное значение.

5. Нажать кнопку ОК, и нужный параметр будет подобран в диалоговом окне “Результат подбора параметра”. По окончании этого процесса в нем отобразятся результаты.

6. Нажать кнопку ОК, если вы хотите заменить значения в ячейках на рабочем листе новыми, или кнопку “Отмена” в противном случае.

2.4 Применение функции подбора параметра при работе с диаграммами

Средство подбора параметра применяется и при работе с диаграммами. Как это делается, показано в следующем примере.

На основании данных о суммах выручки от продажи изделий в три региона нужно построить с помощью мастера диаграмм гистограмму (рис. 8) В интерактивном режиме с использованием мыши нужно настроить высоту полосы «Среднее», и посмотреть, как Ехсе1 изменит высоту полосы «Северск» для получения целевого значения. Далее необходимо дважды щелкнуть на последней полосе (один раз - для выбора ряда, а второй — для выбора полосы из ряда), которая представляет средние значения, и увеличить ее высоту путем перетаскивания маркеров размеров.

Когда кнопка мыши будет отпущена, откроется диалоговое окно “Подбор параметра. В поле “Установить в ячейке” появится имя ячейки В5, а в поле “Значение” — число, соответствующее последнему значению, которое отображалось в поле подсказки. Курсор ввода будет находиться в поле “Изменяя значение ячейки”, поэтому остается лишь ввести в данное поле значение В2. Нужно щелкнуть на кнопке ОК, после чего появится диалоговое окно “Результат подбора параметра”, в котором будет содержаться нужная информация. Щелкнуть на кнопке ОК для возврата на рабочий лист. В ячейках уже содержатся новые значения и что в соответствии с ними настроена высота полос гистограммы (рис. 9).

Рисунок 8 - Гистограмма “Выручка от продажи изделий в трех регионах”

 

Рисунок 9 - Гистограмма “Выручка от продажи изделий в трех регионах” c применением подбора параметров

2.5 Задача: Вычисление радиуса описанной окружности по трем сторонам треугольника с помощью подбора параметра

Вычисляем радиус описанной окружности R по формуле:

=a*b*c_/(4*S)

где а = 2, b = 4, с_ = 5 – стороны треугольника

S – площадь треугольника

Площадь треугольника S вычисляется по формуле Герона:

= КОРЕНЬ(p*(p-a)*(p-b)*(p-c_))

где а = 2, b = 4, с_ = 5 – стороны треугольника

р – полупериметр треугольника

Полупериметр треугольника p вычисляем по формуле:

=(a+b+c_)/2

Пример вычисления радиуса описанной окружности в программе Excel показан на рисунке 10

Рисунок 10 - Вычисление радиуса описанной окружности по трем сторонам треугольника

Итак, радиус описанной окружности R по трем сторонам треугольника а, b, с вычислен. Если зафиксировать длины сторон b и с, то можно считать, что R вычисляется как функция а. Но Ехсе1 дает возможность решить обратную задачу: по заданному R вычислить а. При этом не нужно решать вручную громоздкую задачу отыскания а как функции R. Формул на рабочем листе для этой цели вполне достаточно. Например, надо определить величину а при R = 3. Для это нужно выделить ячейку В9, в которой вычисляется R. В меню нужно выбрать "Сервис/ Подбор параметра". Выводится диалоговое окно "Подбор параметра". Поле "Установить в ячейке:" уже содержит адрес выделенной ячейки B9. В поле "Значение:" нужно ввести 3. В поле "Изменяя значение ячейки:" нужно ввести адрес ячейки В2, содержащей величину стороны а (если щелкнуть мышью по этой ячейке, то в поле ввода окажется адрес $В$2). После нажатия кнопки "ОК" выводится новое окно "Результаты подбора параметра". Если увеличить разрядность числа в ячейке B9, то можно увидеть, что R достигло значения 2.9999172. При этом а = 1.515753171.

Результаты выполнения показаны на рисунке 11

Рисунок 11 - Вычисление радиуса описанной окружности по трем сторонам треугольника с помощью подбора параметра

Единственное ли значение a соответствует R = 3? На этот вопрос изложенный метод подбора параметра не дает ответа.

Заключение

Список используемой литературы

 

1.  Справка MS Excel

2.  Microsoft Office Excel 2003. Учебный курс / В. Кузьмин, - СПб.: Питер: Издательская группа BHV, 2004. – 493 с.

3.  Excel 2003. Эффективный самоучитель / В.В. Серогородский, - СПб.: Наука и техника, 2005. – 400 с.

4.  Excel: Сборник примеров и задач / С.М. Лавренов, - М.: Финансы и статистика, 2003. – 336 с.