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

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

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

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

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

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

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

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

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

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

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

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

• Единичная ячейка. Указывается адрес ячейки листа, например: 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 или двойной щелчок мыши.

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

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