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

Опубликовано в Использование ссылок

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

Ссылки на ячейки используют заголовки соответствующих строк и столбцов. В Excel столбцы помечены латинскими буквами (А, В, С,..., Z; АА, АВ,..., AZ; ВА, ВВ и т.д.), а строки пронумерованы. Такие ссылки называются ссылками типа А1. Ссылаться можно как на отдельные ячейки, так и на диапазоны ячеек.

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

=СУММ(А1:А4)

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

=СУММ(А1:А4;С1:С4)

В Excel различают относительные и абсолютные ссылки.

Относительные ссылки (вида А1) автоматически изменяются в случае копирования формулы из одной ячейки в другую и используются в новых формулах по умолчанию.

Примечание

При перемещении ячейки с формулой относительные ссылки не изменяются.

Абсолютные ссылки (вида $А$1) имеют, кроме имени столбца и номера строки, специальный символ — доллар ($), который фиксирует часть ссылки и оставляет ее неизменной при копировании этой формулы в другую ячейку. Обычно в таких ячейках содержатся значения констант, используемых при вычислениях.

Например, требуется заполнить столбец Цена, руб. путем умножения соответствующих значений столбца Цена, $ на курс доллара, значение которого находится в ячейке D1. Для этого:

1. Устанавливаем курсор в ячейку D3 и нажимаем знак равенства (=).

2. Щелкаем по ячейке D1, вводим знак умножения (*), а затем щелкаем по ячейке С3. В результате получим формулу:

=D1*C3.

3. Теперь с помощью символа $ зафиксируем ссылку на ячейку D1, чтобы при копировании формулы в остальные ячейки столбца ссылка на эту ячейку не изменялась. Таким образом, формула примет вид:

= $D$1*C3.

Примечание

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

4. Скопируем формулу в остальные ячейки столбца Цена, руб. Как видно в строке формул, ссылка на ячейку столбца Цена, $ изменилась соответствующим образом, а на ячейку с курсом доллара — осталась неизменной.

Если требуется зафиксировать в ссылке только строку или только столбец, в котором находится используемая ячейка (в частности, столбец А или строку 1), то ссылка примет вид $А1 или А$1 соответственно. Так, в приведенном выше примере можно вместо абсолютной ссылки $D$1 использовать ссылку D$1, т.к. копирование производится лишь по строкам, а столбец не изменяется.

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

Например, чтобы просуммировать данные, расположенные в ячейке А2 листа Лист2 и ячейке А3 листа Лист3, нужно в результирующей ячейке ввести следующую формулу:

=Лист2!А2+Лист3! А3

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

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

=В1+[Та6лица2.х1$х]Лист1 !$В$1

Если книга закрыта, внешняя ссылка содержит полный путь:

■В1+'G5:\Исходные_данные\[Таблица2.х15х]Лист1'!$В$1

Ссылки можно вводить как вручную, так и с помощью мыши.Чтобы создать внешнюю ссылку с помощью мыши:

1. Откройте книгу, на данные которой хотите сослаться.

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

3. Если нужно, введите вычисления, предшествующие ссылке.

4. Перейдите в книгу  и выделите ячейку, на которую хотите сослаться.

5. Вернитесь в исходную книгу Таблица! и нажмите Enter.

В результате внешняя ссылка будет содержать краткий путь:

=В1+ [Таблица2.х15х]Лист1 !$В$1

После закрытия книги Таблица2 ссылка преобразуется и будет содержать полный путь к файлу:

=В1+'G:\Исходные_данные\[Таблица2.х15х]Лист1 '!$В$1

Примечание

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