Использование формул

Константа представляет собой готовое (невычисляемое) значение. Например, константами являются: дата «10.10.2015», число «210» и текст «Прибыль за квартал». Выражение, или его значение, константами не являются.

Если в формуле не содержится ссылок на другие ячейки (например, формула имеет вид =30+70+110), то значение в такой ячейке изменяется только после изменения формулы вручную.

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

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

Арифметические операторы

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

Знак операции	Описание
+	Сложение.
-	Вычитание.
*	Умножение.
/	Деление.

Операторы сравнения

Используются для сравнения двух значений. Результатом сравнения является логическое значение: True или False.

Знак операции	Описание
=	Равно.
<	Меньше.
>	Больше.
<=	Меньше или равно.
>=	Больше или равно.
<>	Больше или меньше (не равно).

Логические операторы

Используются для объединения нескольких условий.

Знак операции	Описание
&	Объединение текстовых условий. Является эквивалентом функции Concatenate.

Особенности использования в формуле ссылок на ячейки листов отчета:

• Единичная ячейка. Указывается адрес ячейки листа, например: B8;

• Столбец. Для использования в формуле всех ячеек одного столбца укажите через двоеточие его наименование, например: B:B. Для использования в формуле всех ячеек нескольких столбцов укажите через двоеточие их наименования, например: A:C;

• Строка. Для использования в формуле всех ячеек одной строки укажите через двоеточие ее номер, например: 5:5. Для использования в формуле всех ячеек нескольких строк укажите через двоеточие их номера, например: 1:5;

• Диапазон ячеек. Для использования в формуле диапазона ячеек укажите через двоеточие сначала адрес верхней левой ячейки, с которой начинается диапазон, а затем адрес нижней правой ячейки, на которой заканчивается диапазон, например:

C7:D15

Где:

• C7. Адрес верхней левой ячейки;

• D15. Адрес нижней правой ячейки.

В качестве диапазона ячеек можно указывать именованный диапазон;

• Комбинированный диапазон ячеек. Для описания области, состоящей из нескольких непересекающихся диапазонов, включая единичные ячейки, укажите через точку с запятой «;» адреса прямоугольных областей и отдельных ячеек. Например:

C7:D15;A1:B4;B8

Где:

• C7:D15 и A1:B4. Диапазоны ячеек;

• B8. Единичная ячейка;

• Ссылка на ячейки другого листа. Для использования в формуле ячейки или диапазона ячеек с другого листа укажите перед диапазоном название листа в кавычках с восклицательным знаком.

Ссылка на лист вставляется по следующему алгоритму:

1. • при переходе на другой лист в позицию курсора вставляется наименование листа. Если был выделен терм, то наименование листа вставляется на место выделенного терма. Выделение на активном листе отсутствует;

   • производится выбор ячейки или диапазона на листе - в позицию курсора ввода вставляются ссылки на ячейку или диапазон. Ячейка или диапазон выделяется пунктирной рамкой;

   • смена активного листа приводит к корректировке наименования листа в полученном терме, ссылка на диапазон при этом не корректируется, ей соответствует выделение на каждом активируемом листе. Изменение границ выделенного диапазона на активном листе приводит к корректировке ссылки на диапазон в полученном терме;

   • при смене активного листа курсор ввода остается в редакторе ячейки либо в строке формул, в зависимости от того, где он находился до этого;

   • если ввод формулы осуществляется с помощью редактора ячейки, и при вводе формулы активирован другой лист либо вставлен терм, то при переходе обратно на текущий лист редактор восстанавливается, курсор ввода располагается после добавленного терма;

   • после окончания ввода формулы активным становится лист, на котором осуществлялся ввод формулы.

Редактирование формулы при активации другого листа прекращается, если:

• курсор ввода располагается после любого терма;

• находится внутри терма;

• выделено несколько символов, не являющихся термом: адрес ячейки (A0) и наименование листа со знаком восклицания (“Лист1”!).

При активации других листов во время ввода формулы подсвечивается текущий лист. Ссылки на другие листы в формуле и на листе не подсвечиваются цветом. Примеры ссылок:

="Лист1"!A3

Значение ячейки A3 с листа 1.

=Sum("Лист2"!A:A)

Сумма значений столбца A c листа 2.

=Sum("Лист3"!C31;A31;E31)

Сумма комбинированного диапазона: значение для ячейки С31 берется с листа 3, значения для ячеек A31 и E31 берутся с текущего листа.

=Sum("Лист2"!B31:D31)

Сумма диапазона значений с листа 2.

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

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

Вводимый/редактируемый адрес ячейки или диапазона и границы соответствующей ячейки или диапазона будут обозначаться одним цветом.

Примечание. Если в формулах требуется использовать циклические ссылки, то необходимо на вкладке «Формулы» панели «Параметры» активировать флажок «Итеративные вычисления». По умолчанию такие формулы не вычисляются и в ячейках будет отображена ошибка «Обнаружены циклические ссылки». Примеры циклических ссылок: для ячейки A0 задана формула «=С11+ B9 + A0»; для трех ячеек A0 (=A1 + 1), A1 (=A2 + 1), A2 (=A0 + 1).

При создании ссылок в регламентных отчётах доступно использование двух стилей ссылок:

• А1. При использовании стиля A1 столбцы обозначаются буквами, а строки - цифрами;

• R1C1. При использовании стиля R1C1 строки (row) и столбцы (column) обозначаются цифрами и ссылки на ячейки в формулах задаются при помощи смещения от текущей ячейки. Например, ссылка R[-1]C[2] ссылается на ячейку, которая находится на одну строку выше и на два столбца правее текущей ячейки.

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

В отчёте можно использовать только один стиль ссылок.

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

При создании формул можно использовать виды ссылки:

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

• абсолютная ссылка ячейки в формуле, например, $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. Доступна для стиля A1;

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

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

• стиль А1. При использовании стиля А1 для указания абсолютных ссылок используется специальный символ «$». Для фиксации ссылки в ячейке или строке формул:

• введите символ «$» перед той частью ссылки, которую необходимо зафиксировать;

• нажмите клавишу F4: при однократном нажатии будет зафиксирована ячейка, при повторном нажатии будет зафиксирована строка, при тройном нажатии будет зафиксирован столбец, при четверном нажатии ссылка станет снова относительной. Фиксируется ячейка, рядом с которой находится курсор, выделите всю формулу для фиксации всех ссылок в ней.

=$A1

При копировании данной ссылки столбец «A» всегда будет зафиксирован. Относительная часть ссылки, т.е. номер строки, будет меняться, в соответствии с той строкой, в которую будет скопирована ссылка (=$A2, =$A3 и т.д);

• стиль R1C1. При использовании стиля R1C1 для указания абсолютных ссылок необходимо указать номер строки/столбца после букв «R» или «C». Относительные номера строк/столбцов указываются в квадратных скобках и соответствуют смещению относительно текущей ячейки. Для фиксации ссылки в ячейке или строке формул:

• укажите номера строк/столбцов в квадратных скобках;

• нажмите клавишу F4: при однократном нажатии будет зафиксирована ячейка, при повторном нажатии будет зафиксирована строка, при тройном нажатии будет зафиксирован столбец, при четверном нажатии ссылка станет снова относительной. Фиксируется ячейка, рядом с которой находится курсор, выделите всю формулу для фиксации всех ссылок в ней.

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

=R[-3]C0

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

Особенности использования ссылок

• Абсолютная ссылка =$A$0, составленная с использованием стиля А1, аналогична ссылке =R0C0, составленной с использованием стиля R1C1.

• Ссылка =RC1, составленная с использованием стиля R1C1, аналогична ссылке на ячейку, расположенной в столбце RC и первой строке, составленной с использованием стиля А1.

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

Для добавления в формулу встроенной функции вызовите мастер функций. Доступны следующие категории функций:

• Математические;

• Ссылки и массивы;

• Поиск;

• Дата и время;

• Текстовые;

• Работа с отчетом;

• Финансовые;

• Статистические;

• Логические;

• Python;

• Java.

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

1. Используя мастер функций или клавиатуру, введите функцию в ячейку.

2. Выделите диапазон ячеек (массив), в который будет размещен результат вычисления, начиная с ячейки, содержащей формулу.

3. Нажмите клавишу F2, а затем — сочетание клавиш CTRL+SHIFT+ENTER.

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

Если при указании диапазона ячеек (шаг 2) была выделена область больше, чем возвращаемый массив, то в лишних ячейках будет отображено значение «#Н/Д»; если была выделена область меньше, то будет отображена только часть массива.

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

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

Для использования в формуле функции Fore введите в ячейку после знака равенства: <Идентификатор модуля>.<Наименование функции>[(<Значения параметров функции>)].

Например, пользовательская функция в модуле с идентификатором «MODULE» имеет вид:

Для использования пользовательской функции введите в выбранную ячейку выражение:

=MODULE.My(50)

В результате вычисления в выбранной ячейке отобразится значение «100».

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

Формула может быть создана несколькими способами:

• непосредственно в ячейке. Для этого в редактируемой ячейке выполните действия:

1. Введите знак равенства. Если данный знак уже есть в ячейке, пропустите данный шаг.

2. Сформируйте формулу, используя числа, операторы, ссылки на ячейки и функции. При вводе функции доступно её автозавершение.

3. Для сохранения и расчета формулы нажмите клавишу ENTER;

• в строке формул;

• при помощи мастера функций. Для этого нажмите кнопку «Вставить функцию», расположенную в строке формул, или нажмите сочетание клавиш SHIFT+F3;

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

1. Скопируйте ячейку с формулой.

2. Вставьте формулу в ячейку или диапазон ячеек.

Для отмены ввода формулы нажмите клавишу ESC.

Формула может быть отредактирована:

• непосредственно в ячейке. Для этого в редактируемой ячейке:

1. Отредактируйте формулу, используя числа, операторы, ссылки на ячейки и функции. При вводе функции доступно её автозавершение.

2. Для сохранения и расчета формулы нажмите клавишу ENTER.

• в строке формул;

• при помощи мастера функций. Для этого нажмите кнопку «Вставить функцию», расположенную в строке формул, или нажмите сочетание клавиш SHIFT+F3.

Редактирование формулы, возвращающей массив, доступно только в основной ячейке.

Для отмены ввода формулы нажмите клавишу ESC.

Для просмотра промежуточного значения формулы:

1. Выделите ячейку с формулой.

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

3. Нажмите клавишу F9. Вместо выделенной части формулы будет отображен промежуточный результат. Если выделен массив, будет отображён набор значений.

4. Сохраните:

• промежуточный результат в формуле. Нажмите клавишу ENTER, промежуточный результат заменит выделенную часть формулы;

• формулу. Нажмите клавишу ESC, выделенная часть формулы будет возвращена обратно.

Для использования подсказок при вводе формул перейдите на вкладку «Формулы» панели «Настройка» и установите флажок «Автозавершение формул».

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

Для передвижения по списку используйте клавиши перемещения вверх и вниз. Для выбора функции используйте клавишу TAB или двойной щелчок мыши.

Для настройки индикации ячеек, содержащих ошибки, перейдите на вкладку «Формулы» панели «Настройка» и установите флажок «Индикация ошибок».

Примечание. Если выделено несколько ячеек с ошибками, идентификатор ошибки отображается для верхней левой ячейки диапазона. Для нескольких ячеек доступны команды «Пропустить ошибку» и «Параметры проверки ошибок».