Какие типы ссылок используются в Excel?
Благодаря ссылкам данные, находящиеся в разных частях листа, могут использоваться в нескольких формулах одновременно.
Ссылка – это указание на ячейку или диапазон ячеек листа со сведениями о расположении значений или данных, которые требуется использовать в формуле. Также ссылки могут принимать и другие значения: например вот ссылка на киберфорум, на котором ведут обсужденияи диалоги начинающие программисты и системные администраторы.
При этом можно задавать ссылки на ячейки, расположенные на других листах той же книги или других книг. Эти ссылки на ячейки других книг называются связями.
Стиль ссылок A1
По умолчанию программа Excel использует стиль ссылок A1, который основан на буквенных именах столбцов и номерах строк.
- А5 – ячейка на пересечении столбца A и строки 5.
- В5:В10 – диапазон ячеек: столбец В, строки 510.
- С10:D10 – диапазон ячеек: строка 10, столбцы СD.
- 10:10 – все ячейки в строке 10.
- 5:10 – все ячейки в строках с 5 по 10.
- О:О – все ячейки в столбце О.
- Р:V – все ячейки в столбцах с Р по V.
- A10:E20 – диапазон ячеек: столбцы АE, строки 1020.
- Лист2!А1 – ячейка на другом листе данной книги.
Стиль ссылок R1C1
В программе Excel также используется стиль ссылок, при котором нумеруются как строки, так и столбцы. Данный стиль, как правило, используется при вычислении положения столбцов и строк в макросах (макрокоманда, используется для автоматического выполнения операций). При этом положение столбца указывается буквой С и его номером, а строка – буквой R и ее номером.
Например, адрес ячейки R10С15 указывает на адрес на пересечении строки 10 и столбца 15.
Как переключаться между стилями адресации, рассказано ранее в пункте «Как изменить стиль заголовков столбцов?».
- R[2]C – относительная ссылка на ячейку, расположенную на две строки выше и в том же столбце.
- R[2]C[2] – относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее.
- R2C2 – абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце.
- R[1] – относительная ссылка на строку, расположенную выше текущей ячейки.
- R – абсолютная ссылка на текущую строку.
Использование относительных и абсолютных ссылок
По умолчанию ссылка на ячейку является относительной. Например, если вы ссылаетесь на ячейку A2 из ячейки C2, вы указываете адрес ячейки в том же ряду (2), но отстоящей на два столбца влево (C минус A). Формула с относительной ссылкой изменяется при копировании из одной ячейки в другую. Например, вы можете скопировать формулу =A2+B2 из ячейки C2 в C3, при этом формула в ячейке C3 сдвинется вниз на один ряд и превратится в =A3+B3.
Если необходимо сохранить исходный вид ссылки на ячейку при копировании, ее можно зафиксировать, поставив перед названиями столбца и строки знак доллара ($). Например, при копировании формулы =$A$2+$B$2 из C2 в D2 формула не изменяется. Такие ссылки называются абсолютными.
В некоторых случаях ссылку можно сделать «смешанной», поставив знак доллара перед указателем столбца или строки для «блокировки» этих элементов (например, $A2 или B$3). Чтобы изменить тип ссылки на ячейку, выполните следующее.
Текущая ссылка (описание):
Новая ссылка
$A$1 (абсолютный столбец и абсолютная строка)
$A$1 (абсолютная ссылка)
A$1 (относительный столбец и абсолютная строка)
C$1 (смешанная ссылка)
$A1 (абсолютный столбец и относительная строка)
$A3 (смешанная ссылка)
A1 (относительный столбец и относительная строка)
C3 (относительная ссылка)
Типы адресации в Microsoft Excel
При записи формул приходится ссылаться на ячейки, в которых хранятся исходные значения. Каждая ячейка имеет адрес (ссылку). Возможны следующие способы адресации ячеек.
I. Адресация одной ячейки. Ячейка на пересечении столбца А и строки 3 имеет адрес А3. Всего на листе может быть 65536 строк и 256 столбцов. Столбцы нумеруются A, …, Z, AA, AB, …, IV.
В Excel существуют следующие типы ссылок на ячейки. Отличия типов ссылок становятся заметными при переносе или копировании ячейки со ссылками.
Абсолютные ссылки не меняются при переносе или копировании ячейки со ссылками. Перед заголовком столбца и номера строки ячейки ставится знак доллара $. Примеры абсолютных ссылок $A$1, $B$67.
При переносе или копировании ячейки с относительными ссылками, ссылки меняются, сохраняя пространственное соотношение с ячейками, на которые они ссылаются. Относительная ссылка представляет адрес ячейки. Примеры относительных ссылок A2, CD45.
В смешанных ссылках либо перед заголовком столбца, либо номером строки ставится знак доллара. Этот параметр не меняется при переносе или копировании ячейки со ссылками, как абсолютная ссылка, а параметр, перед которым знак доллара отсутствует – меняется, сохраняя пространственное соотношение, как относительная ссылка. Примеры смешанных ссылок T$2, $AC5.
Задача. Формулу из ячейки B2 скопировали в ячейку C3. Какое значение имеет формула в ячейке C3?
Задача. В ячейке B2 вычисляется сумма двух ячеек. Формулу из ячейки B2 скопировали в ячейку C3. Зависимость между ячейками изображена на рисунке.
Трехмерная ссылка используется для ссылки на ячейку, находящуюся на другом листе, и состоит из двух частей: названия листа и абсолютной (относительной, смешанной) ссылки на ячейку, разделенных восклицательным знаком, например:
Если название листа содержит пробелы, знаки пунктуации, то название листа в ссылке заключается в апострофы, например:
Внешняя ссылка используется для ссылки на ячейку в другой книге. Внешняя ссылка состоит из названия книги в квадратных скобках и трехмерной ссылки, например:
Если название книги или листа содержит пробелы, знаки пунктуации, то вся часть ссылки до восклицательного знака заключается в апострофы, например:
Расширение файла книги можно не указывать. Если книга закрыта, то необходимо указать полный путь к файлу книги до квадратных скобок с названием книги, например:
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
При записи формул удобно ссылаться на часто используемые ячейки не по ссылке, а по имени, например, Ставка_налога.
Чтобы присвоить имя ячейке или диапазону на уровне листа необходимо выполнить следующие действия:
2) выбрать пункт меню Вставка | Имя | Присвоить;
3) в открывшемся окне Присвоение имени в поле Имя ввести имя ячейки или диапазона, причем имя должно начинаться как трехмерная ссылка с названия листа и знака восклицания (!); первый символ имени должен быть буквой или знаком подчеркивания, остальные символы имени могут быть буквами, цифрами, точками или знаками подчеркивания; регистр не учитывается;
4) в поле Формула будет записана ссылка на ячейку или диапазон;
5) нажать кнопку Добавить, чтобы ввести еще имена ячеек или диапазонов, или кнопку Ok, чтобы закрыть окно.
Чтобы присвоить имя ячейке и диапазону на уровне книги необходимо выполнить те же действия, но на шаге 3 при задании имени не указывать название листа.
Чтобы присвоить имя формуле или константе необходимо выполнить те же действия, что и при задании имени на уровне книги, но на шаге 4 в поле Формула необходимо записать формулу или константу, например «=25%».
Присвоенные имена, именованные формулы и константы используются в формулах. При использовании имен на уровне листа на другом листе необходимо записать название листа, знак восклицания и имя, как в трехмерной ссылке. Например, Лист1!Итог.
[expert_bq отличие от обычных ссылок на ячейки, он позволяет формуле быть активной в случае добавления и удаления в диапазоне данных. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Также в Экселе имеется функция «ГИПЕРССЫЛКА», которая значительно расширяет возможности создания гиперссылок. При записи функции необходимо будет указать адрес, и имя, которое будет отображаться в ячейке (ГИПЕРССЫЛКА(адрес;[имя])). «Имя» не обязательный параметр, и в качестве имени можно указать адрес ячейки, из которой будет подставляться значение.
Вставить ссылку в Excel — Активные ссылки в Excel или как URL сделать гиперссылкой — Как в офисе.
Для удаления гиперссылки в Экселе необходимо нажать на нее правой кнопкой мыши и в появившемся меню выбрать «Удалить гиперссылку». После этого в ячейке останется только текст. Изменить гиперссылку можно также нажав на нее правой кнопкой мыши, и выбрав пункт «Изменить гиперссылку. ». При этом появится окошко «Изменить гиперссылку», как при ее создании, где можно будет внести исправления.
Типы ссылок в MS Excel
Ссылки в MS Excel являются основополагающей темой, без досконального изучения которой работа в табличном процессоре по его прямому назначению не возможна в принципе.
Ссылки бывают: относительные, абсолютные и смешанные. Поскольку ссылка состоит из двух частей для однозначного позиционирования (ссылка на строку и ссылка на столбец), то смешанные ссылки просто являют собой на половину абсолютную ссылку, а на половину относительную (либо по столбцу либо по строке).
Рассмотрим пример с уровнем дохода определенной группы лиц.
Для того, чтобы посчитать общий доход, необходимо сложить зарплату и дополнительный доход. Т.е. в необходимую ячейку ввести знак «=», а потом мышкой щелкнуть на те ячейки, которые необходимо сложить.
Это самый простой пример использования относительной ссылки. Если воспользоваться функций авто заполнения (потянуть за квадратик в правом нижнем углу ячейки), то доход посчитается для остальных работников.
Как можно убедиться из рисунка выше для Иры расчет общего дохода осуществляется путем сложения ячеек B6 и С6, хотя изначально нами была введена другая формула.
Это и есть относительные ссылки, т.е. ссылки передвигаются вместе с ячейкой, в которой введена формула.
Понимание работы различных типов ссылок в Excel является ключевым фактором при построении формул. Например, в той же функции ВПР вы получите ошибку, если не зафиксируете абсолютной ссылкой диапазон в котором нужно будет осуществлять поиск.
Теперь рассмотри, тот же пример, однако, добавим абсолютных ссылок. Просто переведем полученный доход в доллары США.
Если пользоваться только относительными ссылками, нам бы пришлось записать курс доллара напротив каждого имени сотрудника и делить общий доход, но, поскольку курс один для всех тиражировать его лишено всякого смысла, лучше воспользоваться абсолютной ссылкой. Абсолютная ссылка ссылается на первоначальную ячейку вне зависимости от места нахождения самой формулы.
Автозаполнение было произведено и по вертикали и по горизонтали, однако ссылка на ячейку B1 не изменилась. Для того, чтобы при введении формулы ссылку из относительной переделать в абсолютную необходимо поставить знак доллара перед обозначением строки и столбца (всего 2 знака доллара). Его также можно поставить просто нажав функциональную клавишу F4 на клавиатуре предварительно установив курсор на обозначение ячейки в формуле, либо вручную с помощью Shift+4 в английской раскладке клавиатуры.
Изучив относительные и абсолютные ссылки перейдем к изучению смешанных. Смешанная ссылка по сути является на половину абсолютной на половину относительной. Обратите внимание, что имеется ввиду ссылка на одну ячейку. В заданиях выше использовалась формула, в которой присутствовала относительная ссылка и абсолютная, однако это формула с двумя типами ссылок, а никак не относительная ссылка. Смешанная ссылка обозначается одним знаком доллара в зависимости от того, что необходимо зафиксировать, если она будет абсолютной по строке, значит знак доллара необходимо поставить перед цифрой, если по столбцу – перед буквой соответственно. Как и в случае с абсолютными ссылками знак доллара можно поставить просто нажав несколько раз функциональную клавишу F4, предварительно установив курсор на обозначение ячейки в строке формул.
Рассчитаем в качестве примера общий доход сотрудников в разных валютах.
В примерах выше сумма была зафиксирована по столбцу, чтобы при автозаполнении формулы ячейка по горизонтали не изменялась, в то же время, по вертикали она должна изменяться. И прямо противоположная ситуация с курсом валют, поскольку они записаны в строку, то необходимо ее зафиксировать. Таким образом введя всего одну формулу удалось сделать расчёт сразу для трех столбцов.
В качестве, закрепляющего знания, урока можно порекомендовать простые задания:
Просто введите формулу и попытайтесь предугадать значения, если сделать автозаполнение данной формулы по направлениям: вверх, вниз, влево, вправо, а потом сделать такое заполнение.