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

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

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

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

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

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

Например, в формуле в ячейке D2 в таблице на рис. 6.13 использована ячейка А4 листа Курс текущей книги.

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

При переходе к ячейке другой книги, имя файла в квадратных скобках автоматически добавляется к ссылке на ячейку. Например, в формуле в ячейке D2 в таблице на рис. 6.14 использована ячейка А4 листа Курс файла ЦБР.xlsx.

Рис. 6.14.Ссылка на ячейку другой книги

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

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

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

В некоторых случаях использование относительных ссылок недопустимо. Например, в таблице на рис. 6.16 при копировании ячейки D2 на нижерасположенные ячейки ссылка на ячейки В2 и С2 должны изменяться, а ссылка на ячейку А20 должна оставаться неизменной.

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

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

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

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

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

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

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

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

1. Выделите ячейку, в которую нужно ввести функцию.

2. Введите = (знак равенства), имя функции, а затем открывающую круглую скобку.

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

4. Удерживая нажатой клавишу Shift, выделите ярлык последнего листа, на который необходимо создать ссылку.

5. Выделите диапазон ячеек, на которые нужно создать ссылку.

6. Подтвердите ввод формулы.

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