Типы адресации в Microsoft Excel

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

I. Адресация одной ячейки. Ячейка на пересечении столбца А и строки 3 имеет адрес А3. Всего на листе может быть 65536 строк и 256 столбцов. Столбцы нумеруются A, …, Z, AA, AB, …, IV.

В Excel существуют следующие типы ссылок на ячейки. Отличия типов ссылок становятся заметными при переносе или копировании ячейки со ссылками.

1. Абсолютные ссылки.

Абсолютные ссылки не меняются при переносе или копировании ячейки со ссылками. Перед заголовком столбца и номера строки ячейки ставится знак доллара $. Примеры абсолютных ссылок $A$1, $B$67.

	A	B
	1	2
		=$A$1+$B$1
		=$A$1+$B$1

2. Относительные ссылки.

При переносе или копировании ячейки с относительными ссылками, ссылки меняются, сохраняя пространственное соотношение с ячейками, на которые они ссылаются. Относительная ссылка представляет адрес ячейки. Примеры относительных ссылок A2, CD45.

	A	B
	1	2
		=A1+B1
	1	2
		=A4+B4

3. Смешанные ссылки.

В смешанных ссылках либо перед заголовком столбца, либо номером строки ставится знак доллара. Этот параметр не меняется при переносе или копировании ячейки со ссылками, как абсолютная ссылка, а параметр, перед которым знак доллара отсутствует – меняется, сохраняя пространственное соотношение, как относительная ссылка. Примеры смешанных ссылок T$2, $AC5.

	A	B	C
	1	2
		=A$1+$B1
		2
			=B$1+$B4

Задача. Формулу из ячейки B2 скопировали в ячейку C3. Какое значение имеет формула в ячейке C3?

Ответ.С3: =B2+$B$1*B$2; B2: 7; C3: 21.

Задача. В ячейке B2 вычисляется сумма двух ячеек. Формулу из ячейки B2 скопировали в ячейку C3. Зависимость между ячейками изображена на рисунке.

	A	B	C

Какая формула записана в ячейке B2?

Ответ. Формула в B2: =A2+$B1.

4. Трехмерные ссылки (объемные ссылки).

Трехмерная ссылка используется для ссылки на ячейку, находящуюся на другом листе, и состоит из двух частей: названия листа и абсолютной (относительной, смешанной) ссылки на ячейку, разделенных восклицательным знаком, например:

Лист1!B1.

Если название листа содержит пробелы, знаки пунктуации, то название листа в ссылке заключается в апострофы, например:

'Лист 1'!B1.

5. Внешние ссылки.

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

[Книга1]Лист1!B1.

Если название книги или листа содержит пробелы, знаки пунктуации, то вся часть ссылки до восклицательного знака заключается в апострофы, например:

'[Книга 1.xls]Лист 1'!B1.

Расширение файла книги можно не указывать. Если книга закрыта, то необходимо указать полный путь к файлу книги до квадратных скобок с названием книги, например:

'C:\MyDocs\[Книга 1.xls]Лист 1'!B1.

II. Адресация связных ячеек (диапазона). Диапазон определяется адресами верхней левой и нижней правой ячеек.

Например, три последовательные ячейки А1, В1, С1 можно адресовать как А1:С1.

Возможно задание диапазонов с использованием трехмерных ссылок. Например, адресация диапазона Лист1:Лист3!B1 задает все ячейки B1 с листа Лист1 по лист Лист3, а адресация диапазонов Лист1:Лист3!C1:D9 задает диапазон C1:D9 на листах Лист1-Лист3.

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

III. Адресация несвязных ячеек. Непоследовательные ячейки перечисляются через точку с запятой. Например, ячейки А1, А3, В3, С3 можно адресовать как А1; А3:С3.

10.6.4. Присвоение имен ячейкам
и диапазонам в Microsoft Excel

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

Существует два типа имен ячеек и диапазонов:

1) на уровне листа;

2) на уровне книги.

Чтобы присвоить имя ячейке или диапазону на уровне листа необходимо выполнить следующие действия:

1) выделить ячейку или диапазон ячеек;

2) выбрать пункт меню Вставка | Имя | Присвоить;

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

4) в поле Формула будет записана ссылка на ячейку или диапазон;

5) нажать кнопку Добавить, чтобы ввести еще имена ячеек или диапазонов, или кнопку Ok, чтобы закрыть окно.

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

Чтобы присвоить имя формуле или константе необходимо выполнить те же действия, что и при задании имени на уровне книги, но на шаге 4 в поле Формула необходимо записать формулу или константу, например «=25%».

Присвоенные имена, именованные формулы и константы используются в формулах. При использовании имен на уровне листа на другом листе необходимо записать название листа, знак восклицания и имя, как в трехмерной ссылке. Например, Лист1!Итог.