Алексей Гладкий - Excel. Трюки и эффекты

Механизм формул является одним из основных инструментов, используемых в программе Microsoft Excel. В данном разделе мы рассмотрим несколько трюков, которые можно выполнять с использованием формул.

В Excel реализована возможность выполнения арифметических действий с датами. Для этого следует использовать функцию ДАТА. Например, при использовании формулы =ДАТА (2 0 0 4; 4; 11) – ДАТА (2 0 0 4; 3; б) в соответствующей ячейке будет получен результат 36.

В процессе работы часто возникают ситуации, когда необходимо суммировать значения ячеек, которые хранятся на разных листах. Для этого в формулу требуется включить названия соответствующих листов. Например, при использовании формулы =СУММ (Лист2: Лист3 !С1: С7) будет рассчитана сумма значений, которые хранятся на листах Лист2 и Лист3 в диапазоне С1:С7.

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

1. В ячейку А1 ввести формулу =А1+С1.

2. После того как отобразится окно с сообщением об ошибке, нажать в данном окне кнопку ОК либо Отмена (в данном случае не имеет значения).

3. Войти в режим настройки программы (для этого справа на панели быстрого доступа необходимо нажать кнопку с треугольником и в раскрывающемся списке выбрать пункт Другие команды).

4. Перейти в раздел Формулы.

5. Установить флажок Включить итеративные вычисления, а в поле Предельное число итераций ввести значение 1.

6. Нажать кнопку ОК.

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

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

Range(«B2»).Formula = Range(«A1»).Formula

Также для этого можно использовать код

Range(«B2»).Value = Range(«A1»).Formula

или

Range(«B2») = Range(«A1»).Formula

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

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

Однако с помощью VBA можно присвоить ячейке имя таким образом, что оно не будет отображаться в списке имен. Для достижения такого эффекта можно использовать следующий код:

Names.Add Name:="Секрет", RefersTo:="=Лист1!$A$5", Visible:=False

При использовании данного кода ячейке А5, которая расположена на листе Лист1, будет присвоено имя Секрет. Это имя не будет отображаться в списке имен, но его можно использовать в формулах вместо адреса ячейки. Например, сумму ячеек А1 и А5 можно задать так: =А1+А5, а можно – с использованием скрытого имени: =А1+Секрет.

Совет

При создании макроса рекомендуется задать сочетание клавиш (например, Ctrl+Shift+A) для его быстрого вызова.

К аналогичному результату приводит использование следующего кода:

Range(«A7»).Name = «Защита»

Range(«Защита»).Name.Visible = False

В данном случае ячейке А7, которая расположена на текущем листе, присвоено скрытое имя Защита.

Для быстрого суммирования значений всех ячеек какого-либо столбца или строки предназначены специальные формулы. Например, чтобы получить сумму всех ячеек столбца А, нужно воспользоваться формулой =СУММ (А: А), а для суммирования всех ячеек строки 1 – формулой =СУММ (1:1). Если же необходимо получить сумму всех ячеек столбцов А, В и С, то формула будет выглядеть следующим образом: =СУММ (А: С). Для суммирования всех ячеек, которые расположены в строках 1, 2 и 3, применяется следующая формула: =СУММ (1:3).