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

Данные в Excel хранятся в ячейках − на пересечении столбцов и строк. Каждая ячейка имеет название, соответствующее заголовку столбца и строки, например А1 или В4. Это название определяется как ссылка на ячейку. Ссылкой однозначно определяется ячейка или группа ячеек листа, в которых содержатся данные, которые нужно использовать в качестве аргументов формулы. С помощью ссылок можно использовать в формуле данные, находящиеся в различных местах рабочего листа, а также использовать значение одной и той же ячейки в нескольких формулах.

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

Ссылки на ячейки используют заголовки соответствующих строк и столбцов рабочего листа. В Excel столбцы помечены латинскими буквами (A, B, C, D,... Z; AA, AB, AC, AD,... AZ; BA, BB, BC, и т.д.), а строки пронумерованы. Такие ссылки называются ссылками типа A1. По умолчанию в Microsoft Excel используются именно эти ссылки. Чтобы указать ссылку на ячейку, введите букву заголовка столбца, а затем номер строки. Например: D50 является ссылкой на ячейку, расположенную на пересечении столбца D с 50-й строкой. Чтобы сослаться на диапазон ячеек, введите ссылку на верхний левый угол диапазона, поставьте двоеточие (:), а затем – ссылку на правый нижний угол диапазона.

Например:

ссылка на диапазон ячеек столбца A с 10-й строки по 20-ю будет иметь вид A10: A20;

ссылка на все ячейки в 5-й строке − 5:5;

на все ячейки между 5-й и 10-й строками включительно − 5:10;

все ячейки в столбце H − H:H.

В Excel используются также ссылки в стиле R1C1, в котором и столбцы, и строки листа пронумерованы. Этот стиль наиболее полезен при вычислении позиции строки и столбца в макросах, а также при отображении относительных ссылок. В стиле R1C1 после буквы R (row – строка) указывается номер строки ячейки, после буквы C (column – столбец) − номер столбца. Ссылка R1C1 указывает на ячейку, которая находится в первой строке (R1) и в первом столбце (C1), т е на ячейку А1.

В стиле R1C1 приведенные ранее примеры ссылок будут иметь вид:

ссылка на диапазон ячеек столбца A с 10-й строки по 20-ю будет иметь вид R10C1: R20C1;

ссылка на все ячейки в 5-й строке − R5;

на все ячейки между 5-й и 10-й строками включительно − R5:R10;

все ячейки в столбце H − C8.

ссылка на активную ячейку выводится на экран в поле имени,

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

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

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

Например, ячейка B2 содержит формулу = B5+C8, т. е. первый операнд находится на три ячейки ниже в том же столбце, а второй операнд находится на 6 строк ниже и один столбец правее ячейки B2. При копировании данной формулы и вставке ее в ячейку С3, ссылки в ней снова будет указывать на ячейки, расположенные: первая - на три ячейки ниже в том же столбце, вторая - на 6 строк ниже и один столбец правее ячейки С3. Так, если формула из ячейки B2 копируется в ячейку С3, то формула примет вид =С6 + D9, а если ско-пировать содержимое В2 в B3, то в ячейке B3 формула примет вид = B6+C9.

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

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

Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, используют абсолютные ссылки. Абсолютная ссылка всегда указывает на одну и ту же ячейку, независимо от расположения формулы, её содержащей. Для создания абсолютной ссылки на ячейку необходимо поставить знак доллара ($) перед той частью ссылки, которая не должна изменяться. Например, если в A1 находится формула =$B$5+$C$8, то при копировании содержимого ячейки A1 в ячейку В2 или A3 в этих ячейках также будет находиться формула =$B$5+$C$8, что говорит о том, что исходные данные всегда будут браться из ячеек В5 и С8.

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

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

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

Рис. 20. Смешанная ссылка

В стиле R1C1 абсолютная ссылка R2C2 эквивалентна абсолютной ссылке $В$2 для формата A1. Если ячейка A3 является текущей, то относительная ссылка R[1]C[1] указывает на ячейку, расположенную на одну строку ниже и на один столбец правее, т. е. на ячейку B4, а ссылка R[-2]C указывает на ячейку, расположенную на две строки выше и в том же столбце, т. е. на ячейку А1. В режиме R1C1 в квадратных скобках указывается относительное смещение. В скобках после R указано смещение в строках (row), в скобках после C указано смещение в столбцах. В общем случае направления вниз и вправо считаются положительными, вверх и влево – отрицательными.

Если включен классический вид и в ячейке с адресом A1 находится формула «=B3» (B3 – относительная ссылка), то после переключения в вид R1C1 она примет вид «=R[2]C[1]».

Если включен классический вид и в ячейке с адресом A1 находится формула «=$B$3» ($B$3 – абсолютная ссылка), то после переключения в вид R1C1 она примет вид «=R3C2». Квадратных скобок нет, то есть указано не относительно смещение по отношению к положению формула, а абсолютное смещение по отношению к всему листу.

Пример: пусть в ячейке D3 нужно получить произведение чисел, находящихся в ячейках А2(второй ряд, первая колонка) и B1 (первый ряд, вторая колонка). Это может быть записано одним из следующих способов:

Таблица. Произведение чисел.

Вид ссылок	Стиль А1	Стиль R1C1
относительный	=A2 * B1	=R[-1]C[-3] * R[-2]C[-2]
абсолютный	=$A$2 * $B$1	=R2C1 * R1C2
смешанный	=$A2 * B$1	=R[-1]C1 * R1C[-2]
	=A$2 * $B1	=R2C[-3] * R[-2]C

Дата добавления: 2014-01-07