Технология вычислений в Excel

1. Открыть новый лист книги, дать ему название по теме лабораторной работы.

2. В начале листа записать условие задачи.

3.

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

Рис. 1. Постановка задачи

4. Решение задачи можно получить четырьмя способами. Первый способ будет соответствовать прямому вычислению по формулам, как это представлено в решении задачи. Второй способ будет рассчитан на использование финансовой функции БС. Третий способ предоставит сразу наращенную итоговую сумму с помощью БС. А четвертый способ сделает расчеты наращенной суммы за 4 года с помощью формулы.

5. Для реализации первого способа нужно выполнить следующие действия:

· Выпишем на листе поясняющий текст, обозначающий номер способа и расчетные формулы подобно тому, как это сделано на рис. 2. Обратим внимание на тот факт, что в данном случае для каждого отдельного периода требуется вычисления будущего значения единой суммы [формула (7)]. Поскольку делаются по годам 4 вклада и по каждому вкладу производятся начисления процентов двумя способами, следует подготовить для заполнения таблицу, состоящую из 4 строчек и 3 столбиков. В первый столбик занесем текущее значение количества периодов начислений процентов, во второй и третий столбики – наращенную стоимость для каждого вклада способами пренумерандо и постнумерандо.

· Заполняем первый столбик. Можно произвести его методом автозаполнения. Для этого нужно заполнить первую ячейку K15, дать команду: Правка/Заполнить/Прогрессия. В открывшемся диалоговом окне прогрессия выбрать тип прогрессии – арифметическая, величину шага – -1 и предельное значение 1, а также отметить, что идет заполнение столбиков.

·

Заполняем второй столбик. Для этого в первую ячейку L15 выписываем расчетную формулу. В нашем случае в соответствии с месторасположением исходных и расчетных данных она имеет вид: =$E$7*(1+$E$8)^K15. В ячейке E7 расположена величина ежегодного платежа P. Эта величина остается неизменной, поэтому указан абсолютный адрес P. Процентная ставка расположена в ячейке E8, она также неизменна – задана абсолютным адресом. Число платежных периодов берется из ячейки K15, оно меняется и потому его адрес – относительный. Это означает, что этот относительный адрес при копировании формулы автоматически изменится в соответствии с его относительным расположением.

Рис. 2. Окончательный результат выполнения задания

· Выполнение формулы приводит к результату в ячейке L15, показанному на рис. 2.

· Ячейки L16 – L18 заполняются с использованием средств Excel, называемых Автозаполнение формулами. Ее особенность заключается в реализации необходимости копирования ссылок на другие ячейки (относительные адреса). Для этого делаем активной ячейку L15, наведем курсор на правый угол ячейки. Когда маркер заполнения проявится в виде черного крестика, перетащим его вниз вплоть до ячейки L18. Убедимся в том, что формула будет правильной для любого числа периодов.

· Третий столбик заполняется аналогично. Разница только в том, что проценты на последний вклад, сделанный в конце 4-го периода, не начисляются, и поэтому в ячейке М15 мы выставим значение Р. Формулу запишем в М16, она соответственно примет вид =$E$7*(1+$E$9)^K16 (обратите внимание, что сменился адрес процентной ставки, т. к. она другая), а М17 – М18 – пересчитаем способом Автозаполнение формулами.

· Итоговые наращенные суммы получим Автосуммированием. Для этого выделим второй ряд, начиная с L15 по L19, а затем щелкнем на кнопке S, расположенной на панели пиктографического меню. Такую же работу проделаем для третьего ряда.

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

· Для расчета результата финансовых операций Excel курсор устанавливается в ячейку для ввода формулы, использующей встроенную финансовую функцию (ФФ); если ФФ вызывается в продолжение ввода другой формулы, данный пункт опускается. Таким образом, сделаем активной ячейку Р15.

· Осуществляется вызов Мастера функций с помощью команды Вставка/Функция или нажатием кнопки f_x на панели инструментов.

·

Выполняется выбор категории Финансовые (Рис. 3), а в списке Функция - конкретная функция БС данной категории.

Рис. 3. Окно Мастера функций для выбора финансовой функции Б3

· Следует обратить на сообщения, которые появляются в нижней части окна – они содержат синтаксис функции и описание результата выполнения.

· После щелчка на кнопке Ok имя функции заносится в строку формул вместе со скобками, ограничивающими список параметров. Текстовый курсор устанавливается между этими скобками. Открывается диалоговое окно для ввода параметров, представленное на рис. 4.

Рис. 4. Окно для ввода параметров

· Слева вверху напротив командной строки появляется поле, предоставляющее получить доступ к функциям Excel. Открыть этот список можно кнопкой ▼. В поля ввода диалогового окна можно вводить как ссылки на адреса ячеек, содержащих собственно значения аргументов, так и сами значения аргументов. Поля ввода заканчиваются кнопками свертывания палитры формул. К ним следует прибегать, когда необходимо указать адреса данных, находящихся за пределами диалогового окна (после указания адреса, который указывается щелчком по полю адреса, нажатием клавиши Enter происходит возврат в диалоговое окно).

· В ходе ввода параметров функции изменяется вид записи функции в командной строке. Если название параметра указано полужирным шрифтом, параметр является обязательным и соответствующее поле должно быть заполнено. Параметры, название которых приводятся обычным шрифтом, можно опустить. Для нашей функции – это Нз и Тип. В нижней части окна приводится краткое описание функции, а также назначение изменяемого параметра.

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

· Завершение ввода аргументов и запуск расчета значения встроенной функции выполняется нажатием кнопки Ok.

· При правильном вводе параметров строка формул должна приобрести вид: =БС ($E$8;O15;;-$E$7;1).

· Произвести Автозаполнение формулами и Автосуммирование, как это указано для первого варианта.

· Третий столбец формируется аналогично второму за исключением формулы. Она должна иметь вид: = БС ($E$9;O16;;-$E$7;0).

7) Третий способ должен предоставить сразу наращенную итоговую сумму с помощью БС. Для этого выписывается соответствующий поясняющий текст, а затем в ячейках с предполагаемыми результатами записываются формулы:

· = БС (E8;E12;-E7;;1)

· = БС (E9;E12;-E7;;0)

Следует убедиться, что ответы во всех вариантах одинаковые.

· Четвертый способ выполнить самостоятельно

8) Проанализируем результаты. Анализ решения задачи показывает, что все варианты приводят к одному и тому же результату. А вариант с 26 % предпочтительнее.

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

Эти величины легко определить по таблице 1, в которой приводятся расчеты для наиболее распространенных методов начисления процентов в году.

Таблица 1. Расчет основных величин при внутригодовом учете процента

Метод начисления процентов	Общее число периодов начисления процентов	Ставка процента за период начисления, %
Ежегодный	n	i
полугодовой	n×2	i/2
квартальный	n×4	i/4
Месячный	n×12	i/12
Ежедневный	n×365	i/365