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

Данная посвящена приемам создания небольших электронных таблиц. Однако используемые при этом приемы рассматриваются здесь в несколько ином разрезе, нежели в предыдущей главе. Если ранее больше внимания уделялось форматированию таблицы и работе с результатами вычислений, то теперь речь пойдет в основном о принципах создания таблицы. Кроме того, здесь рассказывается о том, как модифицировать готовые таблицы в другие таблицы, выполняющие аналогичные расчеты, и о том, как превратить созданные таблицы в небольшие автономные расчетные модули.
Сначала мы рассмотрим алгоритм создания таблицы умножения чисел от 1 до 10, а затем перейдем к таблицам, в которых производятся элементарные расчеты денежных потоков и определение текущей и будущей стоимости денег. Таблица умножения выбрана в качестве примера из следующих соображений:
— Принципы ее построения аналогичны принципам создания больших и сложных таблиц, применяемых для расчетов в любой сфере экономики.
— На примере простой таблицы демонстрируются элементы "объемного табличного мышления", необходимые для выполнения электронного расчета любой операции.
Создание таблицы умножения
Предположим, вам требуется выполнить следующие задания:
— за одну-две минуты составить в Excel таблицу умножения для чисел от 1 до 10;
— за несколько секунд изменить данную таблицу таким образом, чтобы в ней вычислялись произведения чисел от 11 до 20.
Так как предполагается создание автоматической таблицы, то недостаточно просто заполнить ее результатами умножения. Мы должны ввести в ячейки соответствующие формулы умножения. Другими словами, неверно выполнив первое задание, даже если мы уложимся в отведенное для этого время, мы не сможем справиться со следующим заданием. Поскольку для решения задачи выделено мало времени, мы должны найти оптимальный метод решения.
Что должна представлять собой созданная в Excel таблица умножения? Это таблица размером 11×11 ячеек, десять вертикально расположенных ячеек которой содержат значения сомножителя 1, а десять ячеек, расположенных по горизонтали, — значения сомножителя 2. Остальная область таблицы — прямоугольник, стороны которого представлены этими сомножителями, — расчетная. В каждой ячейке данной области должен отображаться результат умножения чисел, находящихся на пересечении соответствующих строки (сомножитель 2) и столбца (сомножитель 1).
Таблицу можно расположить в любой области рабочего листа Excel, однако создавать ее лучше, начиная с левого верхнего угла листа. Не будем отступать от этого правила. В столбце А и строке 1 разместим сомножители, а вычисления будем производить в диапазоне ячеек В2:К11.
Заполнение вертикального ряда сомножителей
Построение таблицы начнем с ввода данных в столбец А, значения которого являются сомножителем 1. Ячейка А1 останется пустой. Следовательно, нам необходимо заполнить ячейки А2:А11 числами от 1 до 10. Данную операцию можно выполнить несколькими методами.
Ввод значений с клавиатуры
Это неквалифицированный подход, который займет неоправданно много времени и не принесет нужного эффекта.
Применение команды Прогрессия
Введите в ячейку А2 число 1 и выделите область ячеек А2:А11. После этого вызовите команду Правка/Заполнить/Прогрессия. В результате появится диалоговое окно Прогрессия , в котором следует отметить нужные опции и переключатели и нажать кнопку ОК. В нашем случае, для того чтобы заполнить выделенный диапазон значениями от 2 до 10, требуется установить следующие параметры:
— в области Расположение активизировать переключатель по столбцам;
— в области Тип выбрать переключатель арифметическая. Арифметическая прогрессия задает увеличение (уменьшение) предыдущего значения на фиксированную величину, указанную в поле Шаг;
— в поле Шаг ввести значение 1 (то есть задать увеличение предыдущего значения на единицу);
— в поле Предельное значение следует ввести число 10. Использование данного поля позволяет, независимо от того, какую область ячеек вы перед этим выделили, ограничить числовой ряд указанным в этом поле значением.
В результате заполнения выделенной области методом прогрессии в ячейках АЗ:А11 появятся числа от 2 до 10.
Приведенный метод заполнения вертикального ряда сомножителей является одним из самых оптимальных с точки зрения временных затрат, однако он имеет недостаток, на который будет указано ниже.
Применение формул
Следующий метод — это ввод формулы суммирования, которая задает приращение числа на определенную величину. В нашем случае значение каждой последующей ячейки нужно увеличивать на единицу. По сути этот метод аналогичен прогрессии. Поэтому можно воспользоваться формулой арифметической прогрессии с шагом, равным 1. Таким образом, в ячейку A3 следует ввести формулу
=А2+1
Данная формула применяется в Excel довольно часто, поэтому советуем обратить на нее внимание. Формула быстро вводится путем выполнения следующих действий:
1. Введите знак "=".
2. Перейдите на одну ячейку выше, нажав клавишу [Up]. В результате после знака "=" в строке формул появится адрес ячейки А2, а контур вокруг самой ячейки приобретет вид бегущей "змейки". Адрес ячейки можно ввести, выделив ее мышью, но это дольше.
3. Введите с клавиатуры знак "+", и "змейка" вокруг ячейки А2 исчезнет.
4. Введите с клавиатуры цифру 1 и нажмите клавишу [Enter].
Теперь скопируйте формулу из ячейки A3 в диапазон ячеек А4:А11.
ПРИМЕЧАНИЕ

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

Ввод значения в несколько ячеек одновременно
Оптимальным методом заполнения области вертикального ряда сомножителей является одновременное заполнение диапазона ячеек одинаковой информацией ("Создание ссылок на ячейки области ввода"). При его использовании нет необходимости заносить единицу в ячейку А2. Выделите диапазон ячеек А2:А11, введите формулу
=А1+1
а затем нажмите комбинацию клавиш [Ctrl+Enter].
ПРИМЕЧАНИЕ

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

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

=А1+1

а затем нажмите комбинацию клавиш [Ctrl+Enter].
Заполнение области вычислений
Наша задача — выбрать и ввести в ячейку В2 оптимальную формулу, которая задает умножение первых сомножителей. На первый взгляд кажется, что таковой является следующая формула:
=А2*В1
Операцию ввода формулы нужно проделать 100 раз, чтобы заполнить все 100 ячеек области вычислений. Самым простым методом снижения трудоемкости данной операции является копирование. Результаты копирования содержимого ячейки В2 в область B2:D4 показаны на.

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