Относительные и абсолютные ссылки

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

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

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

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

Чтобы ссылка на ячейку была абсолютной, после указания ссылки на ячейку следует нажать клавишу F4. Ссылку можно преобразовать из относительной в абсолютную и при редактировании ячейки с формулой. К заголовкам столбца и строки в адресе ячейки следует добавить служебный символ $. Например, для того чтобы ссылка на ячейку А20 стала абсолютной, необходимо ввести $А$20.

Ссылка может быть не только относительной или абсолютной, но и смешанной.

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

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

Использование трехмерных ссылок

Трехмерные ссылки используются при выполнении действий с данными из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. При этом в формулу включаются все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ('Комплект1:Комплект5'!D14) суммирует все значения, содержащиеся в ячейке D14 на всех листах в диапазоне от Комплект1 до Комплект5 включительно.

 

5.4.Использование функций

Математические вычисления

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

Простая сумма

Для простейшего суммирования используют функцию СУММ.

Выборочная сумма

Иногда необходимо суммировать не весь диапазон, а только ячейки, отвечающие некоторым условиям (критериям). В этом случае используют функцию СУММЕСЛИ.

Синтаксис функции

СУММЕСЛИ(А;В;С),

где A – диапазон вычисляемых ячеек;

В – критерий в форме числа, выражения или текста, определяющего суммируемые ячейки;

С – фактические ячейки для суммирования.

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

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

Округление

Округление чисел особенно часто требуется при денежных расчетах. Например, цену товара в рублях, как правило, нельзя устанавливать с точностью более двух знаков после запятой. Если же в результате вычислений получается большее число десятичных разрядов, требуется округление. В противном случае накапливание тысячных и десятитысячных долей рубля приведет в итоге к ошибкам в вычислениях.

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

Наиболее часто используют функции ОКРУГЛ, ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ.

Синтаксис функции ОКРУГЛ

ОКРУГЛ(А;В),

где A – округляемое число;

В – число знаков после запятой (десятичных разрядов), до которого округляется число.

Синтаксис функций ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ точно такой же, что и у функции ОКРУГЛ.

Функция ОКРУГЛ при округлении отбрасывает цифры меньшие 5, а цифры большие 5округляет до следующего разряда. Функция ОКРУГЛВВЕРХ при округлении любые цифры округляет до следующего разряда. Функция ОКРУГЛВНИЗ при округлении отбрасывает любые цифры.

Возведение в степень

Для возведения в степень используют функцию СТЕПЕНЬ.

Синтаксис функции

СТЕПЕНЬ(А;В),

где A – число, возводимое в степень;

В – показатель степени, в которую возводится число.

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

Статистические вычисления

Статистические функции используют при анализе данных. Использование большинства функций этой категории требует знания математической статистики и теории вероятностей.

Расчет средних значений

В самом простом случае для расчета среднего арифметического значения используют функцию СРЗНАЧ.

Синтаксис функции

СРЗНАЧ(А),

где A – список от 1 до 30 элементов, среднее значение которых требуется найти. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Если в диапазон, для которого рассчитывают среднее значение, попадают данные, существенно отличающиеся от остальных, расчет простого среднего арифметического может привести к неправильным выводам. В этом случае следует использовать функцию УРЕЗСРЕДНЕЕ. Эта функция вычисляет среднее, отбрасывая заданный процент данных с экстремальными значениями.

Синтаксис функции

УРЕЗСРЕДНЕЕ(А;В),

где A – список от 1 до 30 элементов, среднее значение которых требуется найти. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются;

В – доля данных, исключаемых из вычислений.

Доля данных, исключаемых из вычислений указывается в процентах от общего числа данных. Например, доля 10 % означает, что из данных, содержащих 20 значений, отбрасываются 2 значения: одно наибольшее, другое – наименьшее.

Функция СРЗНАЧЕСЛИ возвращает среднее значение (среднее арифметическое) всех ячеек в диапазоне, которые соответствуют данному условию.

Синтаксис функции

СРЗНАЧЕСЛИ(А, В, [С]),

где А - одна или несколько ячеек для вычисления среднего, включающих числа или имена, массивы или ссылки, содержащие числа;

В - условие в форме числа, выражения, ссылки на ячейку или текста, которое определяет ячейки, используемые при вычислении среднего;

С – необязательный параметр, фактическое множество ячеек для вычисления среднего. Если этот параметр не указан, используется параметр А.

Нахождение крайних значений

Для нахождения крайних (наибольшего или наименьшего) значений в множестве данных используют функции МАКС и МИН.

Синтаксис функции МАКС:

МАКС(А),

где A – список от 1 до 30 элементов, среди которых требуется найти наибольшее значение. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Функция МИН имеет такой же синтаксис, что и функция МАКС.

Функции МАКС и МИН только определяют крайние значения, но не показывают, в какой ячейке эти значения находятся.

В тех случаях, когда требуется найти не самое большое (самое маленькое) значение, а значение, занимающее определенное положение в множестве данных (например, второе или третье по величине), следует использовать функции НАИБОЛЬШИЙ или НАИМЕНЬШИЙ.

Синтаксис функции НАИБОЛЬШИЙ:

НАИБОЛЬШИЙ(А; В),

где A – список от 1 до 30 элементов, среди которых требуется найти значение. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются;

В – позиция (начиная с наибольшей) в множестве данных. Если требуется найти второе значение по величине, то указывается позиция 2, если третье, то позиция 3 и т. д.

Функция НАИМЕНЬШИЙ имеет такой же синтаксис, что и функция НАИБОЛЬШИЙ.

Расчет количества ячеек

Для определения количества ячеек, содержащих числовые значения, можно использовать функцию СЧЕТ.

Синтаксис функции:

СЧЕТ(А),

где A – список от 1 до 30 элементов, среди которых требуется определить количество ячеек, содержащих числовые значения. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Можно также определять количество ячеек, отвечающих заданным условиям. Для этого используют функцию СЧЕТЕСЛИ.

Синтаксис функции:

СЧЕТЕСЛИ(А;В),

гдеА – диапазон проверяемых ячеек;

В – критерий в форме числа, выражения или текста, определяющего суммируемые ячейки;

Можно найти количество ячеек со значениями, отвечающими заданному условию.

Функции даты и времени

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