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

 

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

Изменение типа ссылки производится циклически, в результате последовательных нажатий функциональной клавиши [F4] в то время, когда курсор ввода находится в тексте ссылки. Если, например, в ячейке В2 имеется ссылка на ячейку А1. то при каждом нажатии клавиши [F4] ее вид в строке формул будет изменяться: А1 - $A$1 - A$1 - $А1 - А1 - $A$1 и т. д.

Ввод формулы  со смешанной ссылкой в область  вычислений

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

Эту формулу можно смело копировать в другие ячейки - вычисления в таблице будут производиться правильно. При копировании в первом компоненте формулы изменяется только номер строки (=$A2, $АЗ, $А4 и т. д.), а ссылка на столбец, в котором находятся элементы сомножителя 1, остается постоянной. Во втором компоненте, наоборот, изменяется имя столбца (=С$1, D$1, Е$1 и т. д.), а ссылка на строку, где находятся элементы сомножителя 2, остается постоянной. Для ввода формулы умножения в область вычислений таблицы воспользуемся методом заполнения ячеек одинаковыми данными. Оптимальной в этом случае является такая последовательность действий:  
1. Выделите диапазон ячеек В2:К11.  
2. Введите с клавиатуры знак "=" и нажмите клавишу [Left], чтобы создать ссылку на ячейку А2.  
3. Три раза нажмите функциональную клавишу [F4] (создание абсолютной ссылки на столбец А).  
4. Введите знак "*" (умножить) и нажмите клавишу [Up] для создания ссылки на ячейку В1.  
5. Два раза нажмите функциональную клавишу [F4] (создание абсолютной ссылки на строку 1).  
6. Нажмите комбинацию клавиш [Ctrl+Enter].

Таблица умножения  с формулами и результатами расчетов представлена на рис. 3.7 и 3.8.

Оформление  таблицы

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

 

Рис. 3.7. Таблица  умножения (с формулами; начальное  значение и шаг изменения сомножителей равны 1) 

 

Рис. 3.8. Таблица  умножения (с данными; начальное  значение и шаг изменения сомножителей равны 1) 

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

Итак, с заданием, поставленным в начале главы, мы справились. Однако разработанная нами таблица обладает рядом недостатков. В частности, она предоставляет слишком ограниченные возможности в плане управления диапазоном сомножителей. Изменения можно вносить только в ячейку А1.

Давайте несколько  усложним задачу. Предположим, что в  таблице нужен другой шаг изменения  сомножителей как по горизонтали (скажем, 0,3), так и по вертикали (к примеру, 0,2). К тому же нам нужно изменить начальные значения сомножителей (например, по вертикали начать не с единицы, а с пяти, по горизонтали-с трех). При этом желательно, чтобы обновление результатов происходило моментально.

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

В верхней  части рабочего листа над таблицей умножения разместим область  ввода. Она будет состоять из шести  строк:  
- название области ввода;  
- начальное значение для сомножителей, расположенных по вертикали;  
- начальное значение для сомножителей, расположенных по горизонтали;  
- шаг изменения сомножителей, расположенных по вертикали;  
- шаг изменения сомножителей, расположенных по горизонтали;  
- название области вычислений.

Смещение  таблицы

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

Добавление  ячеек

Самый трудоемкий метод - это смещение области таблицы вниз с добавлением ячеек в верхнюю часть рабочего листа. Реализуется он следующим образом:  
1. Выделите ячейки А1:К6 (диапазон, отвечающий области ввода).  
2. Нажмите правую кнопку мыши и в контекстном меню активизируйте команду Добавить ячейки.  
3. В открывшемся диалоговом окне Добавление ячеек (рис. 3.9) выберите переключатель ячейки, со сдвигом вниз и нажмите кнопку ОК.

ПРИМЕЧАНИЕ

Данное  диалоговое окно также  вызывается посредством  команды Вставка/ Ячейки.

Вставка строк

Второй способ (менее  трудоемкий) - это добавление шести  строк сверху:  
1. Выделите мышью строки 1-6.  
2. Нажмите правую кнопку мыши и в контекстном меню выберите команду Добавить ячейки.

Рис. 3.9. Диалоговое окно Добавление ячеек

Перетягивание таблицы

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

Выделение диапазона  ячеек

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

Использование клавиш перемещения

Выделите ячейку, находящуюся  в любом углу таблицы. Удерживая  клавишу [Shift], нажмите необходимые  клавиши перемещения по рабочему листу ([Left], [Right], [Up], [Down]) и выделите нужную прямоугольную область.

Для быстрого перемещения по рабочему листу можно  использовать комбинации клавиш [Ctrl+Home] и [Ctrl+End], что позволяет из любого места рабочего листа моментально  попасть либо в начало рабочего листа, то есть в ячейку А1, либо в конец  таблицы умножения.

Пользуясь данным методом, вы можете быстро выделить всю  таблицу умножения, независимо от того, в какой области рабочего листа  находится курсор. Вот как это  делается:  
1. Воспользовавшись комбинацией клавиш [Ctrl+Home], переместите табличный курсор в начало рабочего листа.  
2. Нажмите комбинацию клавиш [Shift+Ctrl+End], в результате чего табличный курсор переместится в конец таблицы (в ячейку К.11) и таблица будет выделена.

Или сделайте наоборот:  
1. Применив комбинацию клавиш [Ctrl+End], переместите табличный курсор в конец таблицы умножения.  
2. Воспользовавшись комбинацией клавиш [Shift+Ctrl+Home], переместите табличный курсор в начало таблицы.

Использование мыши

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

Существует  также способ выделения ячеек  с помощью мыши и клавиши [Shift]:  
1. Поместите табличный курсор в любую из четырех угловых ячеек выделяемой области.  
2. Нажмите клавишу [Shift] и, удерживая ее нажатой, выполните щелчок мышью на ячейке, которая расположена в противоположном углу области.

Использование режима выделения

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

ПРИМЕЧАНИЕ

Управлять режимом выделения  можно также при  помощи кнопки ВДЛ, находящейся в строке состояния.

Управление  таблицей из области ввода

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

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

Занесите  в область ввода следующие  данные:  
1. В ячейку А1 - текст Область ввода.  
2. В ячейку А2 - текст Начальное значение по вертикали. В ячейке D2 будет задаваться начальное значение для сомножителей 1, расположенных в столбце А.  
3. В ячейку A3 - текст Начальное значение по горизонтали. В ячейке D3 будет задаваться начальное значение для сомножителей 2, расположенных в строке 7.  
4. В ячейку А4 - текст Шаг по вертикали. В ячейке D4 находится значение, на которое будет увеличиваться каждый следующий сомножитель 1 в столбце А.  
5. В ячейку А5 - текст Шаг по горизонтали. В ячейке D5 находится значение, на которое будет увеличиваться каждый следующий сомножитель 2 в строке 7.  
6. В ячейку А6 - текст Область вычислений.  
7. В диапазон ячеек D2:D5 - значение 1. Ввод этих простых числовых значений позволит при последующем редактировании формул в диапазонах сомножителей не вносить ошибок.

Рис. 3.10. Таблица  умножения (с областью ввода и  формулами, в которых изменились адреса ссылок)

Объединение ячеек

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

Чтобы объединить несколько ячеек в одну, выполните  следующие действия:  
1. Выделите диапазон ячеек, в который будет помещена надпись.  
2. Нажав правую кнопку мыши, активизируйте контекстное меню и выберите в нем команду Формат ячеек.  
3. В появившемся диалоговом окне Формат ячеек перейдите на вкладку Выравнивание (рис. 3.] 1).  
4. Отметьте опцию Объединение ячеек и нажмите кнопку ОК или клавишу [Enter].

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

Рис. 3.11. Вкладка  Выравнивание диалогового окна Формат ячеек

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

Выделение несмежных диапазонов ячеек

Чтобы создать шесть  отдельных объединенных ячеек в  области ввода, необязательно вызывать диалоговое окно Формат ячеек шесть  раз - форматирование всех этих ячеек  можно выполнить сразу благодаря  тому, что программа Excel позволяет выделять на рабочем листе несмежные области. Сначала выделите диапазон A1:D1, а затем нажмите клавишу [Ctrl] и, удерживая ее в таком состоянии, отметьте посредством мыши диапазоны ячеек А2:С2, АЗ:СЗ, А4:С4, А5:С5 и А6:К6. Теперь вызовите диалоговое окно Формат ячеек и активизируйте опцию объединение ячеек.