Финансовые расчеты в EXCEL

От таблицы умножения к элементарным расчетам денежных потоков 

• Заполнение вертикального ряда сомножителей
• Заполнение горизонтального ряда сомножителей
• Заполнение области вычислений
• Оформление таблицы
• Смещение таблицы
• Выделение диапазона ячеек
• Управление таблицей из области ввода
• Таблица расчета процентов по вкладу
• Таблица определения влияния инфляции на стоимость денег
• Таблица определения реальной стоимости денег
• Модуль расчета реальной стоимости денег
• Таблица расчета процентов по остаткам на расчетном счете
• Таблица расчета сложного процента на сумму вклада
• Модуль расчета сложного процента на сумму вклада

 

 

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

Сначала мы рассмотрим алгоритм создания таблицы умножения  чисел от 1 до 10, а затем перейдем к таблицам, в которых производятся элементарные расчеты денежных потоков  и определение текущей и будущей стоимости денег. Таблица умножения выбрана в качестве примера из следующих соображений:  
- Принципы ее построения аналогичны принципам создания больших и сложных таблиц, применяемых для расчетов в любой сфере экономики.  
- На примере простой таблицы демонстрируются элементы "объемного табличного мышления", необходимые для выполнения электронного расчета любой операции.

Создание таблицы  умножения

Предположим, вам требуется  выполнить следующие задания:  
- за одну-две минуты составить в Excel таблицу умножения для чисел от 1 до 10;  
- за несколько секунд изменить данную таблицу таким образом, чтобы в ней вычислялись произведения чисел от 11 до 20.

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

Что должна представлять собой созданная в Excel таблица  умножения? Это таблица размером 11x11 ячеек, десять вертикально расположенных  ячеек которой содержат значения сомножителя 1, а десять ячеек, расположенных по горизонтали, - значения сомножителя 2. Остальная область таблицы - прямоугольник, стороны которого представлены этими сомножителями, - расчетная. В каждой ячейке данной области должен отображаться результат умножения чисел, находящихся на пересечении соответствующих строки (сомножитель 2) и столбца (сомножитель 1).

Таблицу можно  расположить в любой области  рабочего листа Excel, однако создавать  ее лучше, начиная с левого верхнего угла листа. Не будем отступать от этого правила. В столбце А и строке 1 разместим сомножители, а вычисления будем производить в диапазоне ячеек В2:К11.

Заполнение  вертикального ряда сомножителей

Построение таблицы  начнем с ввода данных в столбец  А, значения которого являются сомножителем 1. Ячейка А1 останется пустой. Следовательно, нам необходимо заполнить ячейки А2:А11 числами от 1 до 10. Данную операцию можно выполнить несколькими методами.

Ввод значений с клавиатуры

Это неквалифицированный  подход, который займет неоправданно много времени и не принесет нужного эффекта.

Применение  команды Прогрессия

Введите в ячейку А2 число 1 и выделите область ячеек  А2:А11. После этого вызовите команду  Правка/Заполнить/Прогрессия. В результате появится диалоговое окно Прогрессия (рис. 3.1), в котором следует отметить нужные опции и переключатели и нажать кнопку ОК. В нашем случае, для того чтобы заполнить выделенный диапазон значениями от 2 до 10, требуется установить следующие параметры:  
- в области Расположение активизировать переключатель по столбцам;  
- в области Тип выбрать переключатель арифметическая. Арифметическая прогрессия задает увеличение (уменьшение) предыдущего значения на фиксированную величину, указанную в поле Шаг;  
- в поле Шаг ввести значение 1 (то есть задать увеличение предыдущего значения на единицу);  
- в поле Предельное значение следует ввести число 10. Использование данного поля позволяет, независимо от того, какую область ячеек вы перед этим выделили, ограничить числовой ряд указанным в этом поле значением.

В результате заполнения выделенной области методом прогрессии в ячейках АЗ:А11 появятся числа от 2 до 10.  

 

Рис. 3.1. Диалоговое окно Прогрессия 

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

Применение  формул

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

Данная формула  применяется в Excel довольно часто, поэтому  советуем обратить на нее внимание. Формула быстро вводится путем выполнения следующих действий:  
1. Введите знак "=".  
2. Перейдите на одну ячейку выше, нажав клавишу [Up]. В результате после знака "=" в строке формул появится адрес ячейки А2, а контур вокруг самой ячейки приобретет вид бегущей "змейки". Адрес ячейки можно ввести, выделив ее мышью, но это дольше.  
3. Введите с клавиатуры знак "+", и "змейка" вокруг ячейки А2 исчезнет.  
4. Введите с клавиатуры цифру 1 и нажмите клавишу [Enter].

Теперь скопируйте формулу из ячейки A3 в диапазон ячеек А4:А11.

ПРИМЕЧАНИЕ

Недостаток  метода прогрессии состоит  в том, что при  изменении первого  члена прогрессии остальные не изменяются. Если же для заполнения столбца сомножителей воспользоваться  формулой, то при  вводе в ячейку А1 числа 10 автоматически произойдет перерасчет всей таблицы умножения, так как изменится диапазон сомножителей (теперь это будут числа от 11 до 20).

Ввод значения в несколько ячеек одновременно

Оптимальным методом  заполнения области вертикального  ряда сомножителей является одновременное заполнение диапазона ячеек одинаковой информацией (см. раздел "Создание ссылок на ячейки области ввода" главы 2). При его использовании нет необходимости заносить единицу в ячейку А2. Выделите диапазон ячеек А2:А11, введите формулу     =А1 + 1

а затем нажмите  комбинацию клавиш [Ctrl+Enter].

ПРИМЕЧАНИЕ

Данный  метод может использоваться только в случае, когда ячейка А1 не содержит текстовой  информации. Иначе  формула выдаст ошибку #ЗНАЧ!.

Заполнение  горизонтального ряда сомножителей

Теперь введем значения в строку 1, где должны находиться элементы сомножителя 2. Воспользуемся  описанным ранее методом заполнения ячеек одинаковыми формулами. Выделите диапазон В1:К1, введите формулу  
=А1 + 1 а затем нажмите комбинацию клавиш [Ctrl+Enter].

Заполнение  области вычислений

Наша задача - выбрать  и ввести в ячейку В2 оптимальную  формулу, которая задает умножение  первых сомножителей. На первый взгляд кажется, что таковой является следующая  формула:  
=А2*В1

Операцию  ввода формулы нужно проделать 100 раз, чтобы заполнить все 100 ячеек области вычислений. Самым простым методом снижения трудоемкости данной операции является копирование. Результаты копирования содержимого ячейки В2 в область B2:D4 показаны на рис. 3.2.

 

Рис. 3.2. Формула из ячейки В2 скопирована в другие ячейки таблицы умножения 

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

Абсолютная и относительная  ссылки

Ссылка на ячейку может  быть относительной, абсолютной и смешанной. До сих пор мы пользовались относительными ссылками. Фактически они задают смещение ячейки, на которую производится ссылка, относительно ячейки, в которой эта ссылка указывается. По этой причине при копировании адрес ячейки, на которую производится ссылка, изменяется таким образом, чтобы смещение осталось прежним. Это очень полезное свойство, и именно благодаря ему мы имеем возможность при копировании формул избежать ручной работы. Чтобы вы лучше поняли, как работают относительные ссылки в электронных таблицах, приведем пример. Предположим, что в ячейку В2 введена такая формула:  
=А1 Она указывает на ячейку, находящуюся на одну ячейку выше и левее ячейки В2 (то есть на ячейку А1). После копирования содержимого ячейки В2 в ячейку С 4 формула в ячейке С4 уже будет указывать не на ячейку А1, а на ячейку ВЗ (то есть на ячейку, расположенную на одну ячейку выше и левее С4). Таким образом, при копировании содержимого ячейки в любое место рабочего листа расположенная в ней формула будет ссылаться не на конкретную ячейку, а на ячейку, находящуюся на некотором расстоянии от ячейки с формулой. Пунктирные линии на рис. 3.3 указывают, куда переадресуется ссылка при копировании формулы из ячейки В2 в ячейки С4 и D6.

 

Рис. 3.3.Изменение  ссылки при копировании формулы  с относительной ссылкой на ячейку А1 из ячейки B2 в ячейки С4 и D6 

Если же произвести копирование этой формулы в любую  ячейку строки 1 или столбца А, то в ячейке и строке формул появится следующая надпись:  
=#ССЫЛКА!

Она означает, что дана ссылка на несуществующее место рабочего листа.

ПРИМЕЧАНИЕ

На  рис. 3.3 в нижней части окна Excel находится панель Рисование, с помощью которой были нанесены стрелки.

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

Признаком абсолютной ссылки является знак доллара ($). Наличие  двух таких знаков означает, что в какое бы место рабочего листа мы ни копировали формулу, она не изменится:  
= $А$1

Это продемонстрировано на рис. 3.4, где формула с абсолютной ссылкой на ячейку А1 копируется из ячейки В2 в ячейки С4 и D6.

 

Рис. 3.4. При  копировании формулы с абсолютной ссылкой ссылка не изменяется 

Широкие возможности  предоставляют смешанные ссылки. Это ссылки с одним знаком доллара - перед именем столбца или перед  номером строки. Например, если в  ячейке В2 имеется формула  
=А$1

то после  копирования ее в любое место рабочего листа изменится лишь название столбца, а строка 1 будет присутствовать в формуле всегда. Таким образом, в данном случае мы имеем абсолютную ссылку на строку и относительную - на столбец.

Аналогично, если скопировать в другое место  ячейку В2 с формулой  
=$А1

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

Приведенные ниже рисунки иллюстрируют применение в ячейке В2 абсолютной ссылки на строку (рис. 3.6) и абсолютной ссылки на столбец (рис. 3.5). При копировании данной формулы в ячейки С4 и D6 получаются разные формулы.

 

Рис. 3.5. Результат  копирования в ячейки С4 и D6 формулы  из ячейки В2, которая включает относительную  ссылку на строку и абсолютную ссылку на столбец ячейки А1