Работа с данными

Константа представляет собой готовое (невычисляемое) значение. Например, константами являются: дата «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. Единичная ячейка;

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

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

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

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

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

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

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

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

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

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

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

• выделено несколько символов, не являющихся термом: адрес ячейки (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.

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

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

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

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

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

В формулах можно использовать смешанные стили, это не влияет на результат расчета.

Пример:

=$A$0+R[0]C[-1]

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

Примечание. Ссылки на строки/столбцы всегда формируются в стиле A1. При экспорте ссылки, представленные в формате R1C1, будут преобразованы в формат A1.

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

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

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

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

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

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

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

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

=$A1

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

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

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

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

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

=R[-3]C0

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

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

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

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

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

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

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

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

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

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

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

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

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

2. Активируйте возможность «Специальная вставка». Поставьте флажок «Формулы ячеек».

3. Нажмите кнопку «OK».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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