ВЕСТНИК
Хабаровской государственной академии экономики и права

Иван Арисов,

Конэкагропромбанк

ИСПОЛЬЗОВАНИЕ ЭЛЕКТРОННОЙ ТАБЛИЦЫ EXCEL ДЛЯ АВТОМАТИЗАЦИИ ВЫЧИСЛЕНИЙ

Часть 1

Excel это – электронная таблица. Откройте эту программу, и увидите поле, разбитое на ячейки – собственно таблицу. В эти ячейки при помощи клавиатуры можно вводить числа, текст, даты, быстро формируя какой-либо табличный документ, допустим приходную накладную. Кроме того, в ячейки таблицы можно вводить формулы (или функции, но о них ниже). Начинается запись формулы со знака “=”. Введите в ячейку: “=2+7”, нажмите Enter – в ячейке появится число 9.

Благодаря этому, Excel обладает очень интересным свойством – способности автоматизировать вычисления.

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

Разберёмся со ссылками. Любая ячейка таблицы лежит на пересечении какого-либо столбца и какой-либо строки. Строки, при этом, пронумерованы, а столбцы обозначены буквами латинского алфавита. Таким образом, чтобы для обозначения ячеек используются двухмерные координаты (запись A1 соответствует самой верхней и левой ячейке, B1 – ячейке правее, и т.д.). Подобным образом можно задавать не только одиночные ячейки, но и некоторые их скопления. Знак “:” используется для задания прямоугольных областей. Так запись A1:С2 соответствует прямоугольной группе ячеек из 3-х столбцов и 2-х строк. Указанные ячейки лежат в верхнем левом и нижнем правом углах области соответственно. Для простого перечисления нескольких ячеек используется знак “;”. Запись A1;C2 соответствует просто двум ячейкам.

В записи координат можно опускать идентификаторы строк или столбцов. Запись A:C означает, что мы имеем в виду область ячеек, ограниченную столбцами A и C включительно. В полученную область входят все строки нашей таблицы, начиная с первой.

Итак, в ячейку C1 введите, скажем, “=A1+B1”. Нажмите Enter. При этом нет необходимости вводить координаты-идентификаторы ячеек прямо с клавиатуры. Удобнее после ввода “=” щелкать левой кнопкой мыши на искомую ячейку и её идентификатор будет автоматически вноситься в текст формулы.

Обратите внимание: формула, введённая в ячейку, отражается в строке формул в верхней части экрана, если эта ячейка в настоящий момент выделена. Именно в этой строке и удобнее всего редактировать формулы, применяя обычные приёмы текстового редактора.

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

Итак, мы вплотную подошли к автоматизации вычислений с помощью Excel. Вы скажете: "Какая автоматизация? Это можно без труда проделать на обычном калькуляторе!"

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

Чуть ниже мы проведём небольшое, так сказать, умозрительное соревнование между электронной таблицей и калькулятором, но сначала освоим ещё один полезный приём – копирование формул.

Копировать формулы необходимо, чтобы не вводить их вручную каждый раз, при применении одной и той же формулы для разных исходных данных. Формулу можно скопировать несколькими способами. Первый – классический для всех Windows-приложений (откуда надо “Скопировать”, куда надо “Вставить”).

Скопировать формулу можно и следующим образом. Выделите ячейку с формулой, подведите курсор к правому нижнему её углу (там есть небольшой квадратный маркер), курсор при этом изменит крестообразную форму на более тонкую, нажмите левую кнопку мыши, и двигая мышь, распространите появившуюся рамку на все ячейки, куда вы желаете скопировать исходную формулу, отпустите кнопку мыши. Это удобный способ, но он не подходит для копирования формул в несмежные ячейки.

Если нам всё же необходимо скопировать формулу в произвольно отстоящую от исходной ячейку, воспользуемся третьим способом. Выделим нужную ячейку, подведём курсор мыши к любой части рамки вокруг неё (курсор примет форму стрелки), нажмём и будем удерживать клавишу Ctrl на клавиатуре, “Схватим” левой кнопкой и “перетащим” в нужное место рамку. Отпустим левую кнопку мыши, а затем – и клавишу Ctrl. Формула будет скопирована. Способ, в принципе, аналогичен первому. Разница только в наборе манипуляций.

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

Чтобы использовать метод копирования формул для автоматизации вычислений, проведем эксперимент: В ячейке C1 сейчас находится суммирующая формула. Введите в A1 и B1, например, 5 и 6 соответственно. В результате, C1 примет значение 11. Теперь в ячейки A2 и B2 введите 10 и 20. Скопируйте любым из известных способов формулу из ячейки C1 в C2. Обратите внимание, что в ячейке C2 отражается число 30. Таким образом, формула была не только скопирована, но и скорректирована соответствующим образом для суммирования следующей пары ячеек. Все дело в том, что ссылки на ячейки (или на группы ячеек) в Excel бывают двух видов – абсолютные и относительные.

Те ссылки на ячейки, что мы использовали до сих пор, являются относительными. В формуле с относительными ссылками для каждой ячейки – аргумента определяется её смещение по вертикали и горизонтали относительно результирующей ячейки. Это означает, что формула “=A1+B1” введённая в ячейку C1 должна суммировать ячейки, находящиеся относительно нее левее на две позиции (A1) и на одну позицию (B1) соответственно. При копировании формулы в другую произвольную ячейку, аргументы будут выбираться именно посредством заданного смещения относительно результирующей ячейки.

Особенность абсолютной ссылки заключается в том, что при её копировании она продолжает воспринимать как аргумент ту же ячейку что и до копирования. Записывается абсолютная ссылка путём добавления слева от координат-идентификаторов ячеек знака “$”. Если записать формулу в ячейке C1 в виде “=$A$1+$B$1”, то при любых перемещениях формулы она будет ссылаться на одни и те же ячейки. Знак “$”, блокирующий корректировку формулы по одному из направлений, можно приписывать как к двум, так и любой координате ячейки. Так, если слева от обозначения столбца “$” проставлен, а слева от номера строки – нет, то перемещения формулы по вертикали будет сопровождаться корректировкой, а по горизонтали – не будет.

Удобство использования абсолютных и относительных ссылок покажем на примере. Допустим, известны размеры доходов, плановых и фактических (столбцы B и D), приносимых каждым из 8 филиалов банка. (Названия филиалов в столбце A, он скрыт за ненадобностью). Необходимо рассчитать долю каждого филиала в общем доходе банка. Вычисления, при этом будут выглядеть так:

Итак, для каждого столбца с данными о доходах филиалов находим общую сумму доходов. Это реализовано при помощи функции суммирования. Поясним, что эта функция вычисляет сумму ячеек, входящих в указанный интервал. Набирать с клавиатуры данную функцию нет необходимости – достаточно выделить ячейки с B3 по B11 и нажать кнопку “Автосумма” на панели управления в верхней части экрана. Один раз подобным образом созданную функцию во второй столбец, как мы знаем, можно просто скопировать. Далее, в ячейку C3 вводим формулу вычисления доли. Всё просто – часть делится на целое и домножается на 100. Обратите внимание на сочетание абсолютных и относительных ссылок. Благодаря этому, данную формулу можно просто скопировать во все остальные ячейки в обоих столбцах, где должны находиться доли филиалов в общих доходах. При этом в процессе копирования, формулы будут автоматически корректироваться нужным образом.

Видим, что грамотное копирование формул позволяет значительно ускорить вычисления. Поэтому, в ряде случаев, производить вычисления в Excel гораздо быстрее и удобнее, чем на калькуляторе.

Продолжение следует

ВЕСТНИК Хабаровской государственной академии экономики и права

Главная