Создание и редактирование формул

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

Ссылки на ячейки других листов и книг

Если при создании формулы требуется использовать ссылки на ячейки других листов и книг, следует перейти на другой лист текущей книги или в другую книгу и выделить там необходимую ячейку. Например, в формуле в ячейке D2 таблицы на рис. 16.10 использована ячейка В2 листа Курсы текущей книги.

Рис. 16.10. Ссылки на ячейки других листов

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

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

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

Например, при копировании ячейки D2 ( рис. 16.11) на нижерасположенные ячейки, в ячейке D3 будет формула =В3*С3, в ячейке D4 будет формула =В4*С4 и т. д.

Рис. 16.11. Копирование формул

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

Рис. 16.12. Использование абсолютных ссылок

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

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

Абсолютными ссылками по умолчанию являются имена ячеек.

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

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

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

Например, в ячейке Е2 таблицы на рис. 16.12 достаточно было ввести смешанную ссылку G$2.

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

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

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

Трехмерные ссылки могут быть использованы для создания ссылок на ячейки на других листах, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, ДИСП, ДИСПА, ДИСПР и ДИСПРА.

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