Какие 3 вида ссылок есть в excel

Какие типы ссылок используются в 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.
Как переключаться между стилями адресации, рассказано ранее в пункте «Как изменить стиль заголовков столбцов?».

Какие бывают ссылки в Excel

Здравствуйте, дорогие читатели. Я решил выделить эту тему в отдельный пост, потому что многие не знают какие бывают ссылки в Эксель и не представляют, чем они отличаются друг от друга. Ежедневно я получаю вопросы от читателей, которые неправильно применяют ссылки на ячейки. Из-за этого, использование формул может давать ошибочные результаты. Уделите этому тексту 5 минут, чтобы потом не было неожиданных результатов вычислений.

Ссылки в Эксель

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

1. Абсолютные ссылки. Они однозначно указывают адрес ячейки, на которую ссылаются. При копировании, этот адрес не изменяется. Так обычно выглядит абсолютная ссылка в Excel: =$A$1 . В этой ссылке перед именами столбцов и строк стоит знак «$», который указывает программе не изменять эти координаты при копировании.
2. Относительные ссылки. Такие ссылки запоминают свое положение относительно источника и при копировании так же, изменяют свои координаты. Например, в ячейке А1 записано =В2 . Значит, мы ссылаемся на ячейку, которая на одну строку ниже нашей и на один столбец правее. Теперь, если скопировать эту формулу в клетку С3 , ссылка в ней будет такая: =D4 , т.е. сохранит своё относительное положение к источнику.

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

Как видите, в относительных ссылках не ставятся знаки «$», и программа не «замораживает» ни одну из координат.

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

Как изменить тип ссылки в Эксель

Изменение типа ссылки – очень простая задача, которую можно выполнить двумя путями. Как вы уже поняли, чтобы координаты не меняли своего значения при копировании, нужно устанавливать перед ними знак «$». И делается это так:

1. Вручную – дважды кликните на ячейке со ссылкой для редактирования содержимого. Проставьте «$» перед теми координатами, которые нужно «заморозить» и нажмите Enter .
2. Автоматическим перебором — установите курсор на ссылке и нажимайте F4 , пока не получите нужный вид ссылки. Каждое нажатие клавиши устанавливает в данной ссылке новый тип ссылки. Нажатие клавиши циклически изменяет варианты ссылок по кругу: Относительная — Абсолютная — Изменяются столбцы — Изменяются строки — Относительная… Я пользуюсь этим способом, и он ни разу не подводил.

Внешние ссылки в Эксель

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

1. Ссылка на ячейку на том же листе по умолчанию ставится относительной и выглядит, как набор координат. Например: =А1
2. Ссылка на ячейку на другом листе активной книги, по умолчанию тоже относительная, но содержит имя листа, на котором расположена искомая ячейка. Имя листа и адрес ячейки разделяются восклицательным знаком. Например, =Лист1!А1 .
3. Ссылка на ячейку в другом файле по умолчанию абсолютная и записывается, как комбинация: [Имя_рабочей_книги]Имя_листа!Адрес_ячейки . Например: =[Книга1.xlsx]Лист1!$А$1 . И здесь нужно сделать несколько уточнений:

1. Если целевая рабочая книга закрыта, ссылка изменяет вид: Адрес_рабочей_книги[Имя_рабочей_книги]Имя_листа!Адрес_ячейки .
2. Если имя листа или книги содержит пробелы, ссылка заключается в одинарные кавычки по такой схеме: ’[Книга 1.xlsx]Лист 1’!$А$1 .

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

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

Спасибо за прочтение, до встречи на страницах блога OfficeЛЕГКО!

Добавить комментарий Отменить ответ

2 комментариев

Пожалуй, статья не является полной (по крайней мере, на свой вопрос я в ней ответ не нашёл).
При пользовании EXCEL встретился термин «ПРОСТАЯ ССЫЛКА»! Не пойму, что он означает.
В частности, указание на необходимость /возможность использования «простой ссылки» даётся при неправильном (по меркам EXCEL, естественно) написании ОГРАНИЧЕНИЙ, используемых при применении опции «ПОИСК РЕШЕНИЯ» (буквально появляется следующее сообщение: «Ограничение должно быть числом, ПРОСТОЙ ССЫЛКОЙ или формулой с числовыми значениями»).
Исходя из специфики решаемой задачи в данном случае интерес может представлять только ПРОСТАЯ ССЫЛКА.
Буду признателен автору, если он пояснит значение этого термина (разумеется, если знает это).

Заранее спасибо. С уважением, Виктор (Московская область)

Виктор, в данном случае программа просит указать ссылку на ячейку из данной книги, в которой содержится число или результат вычисления. Не используйте в поле гиперссылки, а также вычисления с указанием ссылок на другие ячейки. Указывайте целевое значение значение напрямую, или ссылку на ячейку с этим значением, или формулу, которая не содержит ссылок на ячейки, только значения и операторы.

Основные типы ссылок на ячейки в Excel

Относительная ссылка — это ссылка вида A1 (т.е. буква столбца + номер строки).
Основная особенность таких ссылок — при протягивании или копировании формулы в другие ячейки ссылка смещается.
Другими словами, при копировании вниз ссылка A1 превратится в A2, A3 и т.д., при копировании вправо — в B1, C1 и т.д.:

В данном примере мы копируем ячейку D2 с формулой A2*B2.
При перемещении формулы вниз получаем A2 -> A3 -> A4 -> A5, B2 -> B3 -> B4 -> B5.

Смешанная ссылка в Excel

Смешанная ссылка — это ссылка вида $A1 или A$1.
Знак доллара ($) служит фиксированием столбца или строки.
Иными словами, если мы поставим $ перед буквой столбца (например, $B5), то ссылка не будет изменяться по столбцам, но будет изменяться по строкам (при протягивании формула сместится на $B5, $B6, $B7 и т.д.). Аналогично, если знак $ поставить перед номером строки (например, B$5), то ссылка не будет изменяться по строкам, но будет изменяться по столбцам (при перемещении формула сдвинется на C$5, D$5, E$5 и т.д.).
Разберем использование смешанных ссылок на построении стандартной таблицы умножения:

В данном примере любая формула таблицы является произведением значений из столбца A и строки 2.
Добавляя в формулу расчета знак $ (например, G$2*$A8) мы последовательно фиксируем столбец и строку.

Абсолютная ссылка в Excel

Абсолютная ссылка — это ссылка вида $A$1.
Её особенность в том, что она не изменяется при копировании или протягивании формулы в другие ячейки.
В данном случае знак $ ставится как перед буквой столбца, так и перед номером строки, т.е. формула полностью фиксируется.
Абсолютная ссылка часто применяется, когда необходимо умножить или разделить диапазон ячеек на одно и тоже число.
Например, перевести данные в рубли по определенному курсу, или перевести данные в тысячи/миллионы/миллиарды:

Как сделать ссылку относительной/абсолютной/смешанной?

Помимо ручного проставления знака $ в формулу ячейки, можно воспользоваться инструментами Excel.
Если выделить формулу и последовательно нажимать клавишу F4, то Excel автоматические добавляет знак $ в формулу в следующем порядке — B5 -> $B$5 -> $B5 -> B$5
В зависимости от типа ссылки которую мы хотим поставить — нажимаем несколько раз F4 и получаем требуемый результат.

Абсолютная ссылка в Excel — описание, примеры.

В любом, даже базовом пакете "Майкрософт Офис" содержится мощный табличный редактор Excel ("Эксель"). Список доступных в нем функций поистине впечатляет: начиная от возможности сортировки и фильтрации данных и заканчивая возможностью построения на их основе сводных диаграмм. И практически невозможно представить ни один хранимый в редакторе набор данных без вычисляемых посредством формул строк и столбцов.

Формулы в "Эксель"

Любая формула – это команда, содержащая указание, какие действия произвести с той или иной ячейкой. И когда пользователь сталкивается с необходимостью скопировать, «протянуть» формулу на всю строку или столбец, то понимает, что в некоторых случаях часть формулы или формула целиком должна остаться неизменной. Так пользователь узнает, что есть в Excel абсолютные и относительные ссылки. Рассмотрим эти понятия более детально.

Ссылка относительная

Запомнить, что такое относительная ссылка в Excel, проще простого. Этот вид ссылки выбирается по умолчанию и меняет свое значение при протягивании (копировании) формулы в соседние ячейки независимо от того, выполняется копирование вдоль столбцов или строк.

Рассмотрим простой пример. Создадим небольшой набор данных, состоящий из двух столбцов: «Сотрудник» и «Оклад»:

В параметрах "Экселя" зададим стиль ссылок А1 — такая адресация удобна и понятна большинству пользователей. Здесь А, В, С – имена столбцов, а строки пронумерованы. Таким образом, у ячейки с данными «Закирова Е. М.» адрес — А4. Это небольшое отступление понадобится, когда станем разбираться со ссылками в формулах.

Теперь представим, что мы хотим рассчитать на основе данных по окладу заработную плату каждого сотрудника. Поставим в ячейку С2 знак равенства и введем следующую формулу (основываясь на сведениях, что оклад составляет 40 % от зарплаты): =B2*100/40. Здесь В2 – это оклад первого сотрудника в таблице. Нажмем Enter и подведем указатель мыши к правому нижнему краю ячейки С2, дожидаясь, пока указатель не примет форму тонкого черного крестика. Удерживая нажатой левую клавишу мыши, протянем формулу вниз до конца таблицы, то есть до ячейки С7 (можно заменить данное действие двойным кликом мыши по правому нижнему краю ячейки). Столбец автоматически заполнится данными:

Формулы данных ячеек будут следующими:

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

Как видим, разобраться с тем, что такое относительная ссылка в Excel, совсем не трудно.

Перейдем к рассмотрению следующих видов ссылок.

Ссылка абсолютная

Абсолютная ссылка в Excel – следующий распространенный вид ссылок. В этом случае при копировании формулы фиксируются строка и столбец, на которые идет ссылка в формуле.

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

Введем для примера еще один набор данных – месячная надбавка к окладу, одинаковая для всех сотрудников:

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

Когда мы нажмем Enter, то увидим, что в данной ячейке зарплата правильно пересчиталась. А вот когда мы протянем формулу на всех сотрудников, то у них зарплата не пересчитается, ведь используемая относительная ссылка попыталась взять значения из G4..G8, где абсолютно ничего нет. Дабы избежать подобной ситуации, необходимо, чтобы использовалась абсолютная ссылка в Excel. Чтобы зафиксировать столбец или строку, которые изменяться при копировании формулы не должны, необходимо поставить соответственно возле имени столбца или номера строки знак доллара ($).

Изменим нашу формулу на следующую:

И когда мы ее скопируем, вся зарплата сотрудников пересчитается:

Рядом с понятием "абсолютная ссылка" в Excel всегда идет понятие ссылки смешанной.

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

Рассмотрим этот тип выражений. Смешанная ссылка – это ссылка, у которой при копировании изменяется номер столбца при неизменном номере строки или наоборот. При этом знак доллара стоит соответственно перед номером строки (А$1) либо перед номером столбца ($А1) – то есть перед тем элементом, который изменяться не будет.

Ссылка смешанная применяется намного чаще, чем истинная абсолютная ссылка. Например, даже в простом предыдущем примере мы вполне могли бы заменить формулу =(B2+$G$3)*100/40 на =(B2+G$3)*100/40 и получить тот же самый результат, ведь мы выполняли копирование формулы по вертикали, и номер столбца в любом случае остался бы неизменным. И это не говоря уж о ситуациях, когда действительно надо зафиксировать только номер строки или столбца, а остальное оставить доступным для изменения.

Интересный факт

Интересно будет узнать, что абсолютная ссылка в Excel может быть задана не только самостоятельным указанием знака доллара перед номером строки и/или столбца. "Эксель" позволяет путем нажатия клавиши F4 после указания адреса ячейки выбрать вид ссылок – при первом нажатии ссылка с относительной изменится на абсолютную, при втором – на смешанную с фиксированным номером строки, при третьем – на смешанную с фиксированным номером столбца, ну а при следующем нажатии ссылка снова примет вид относительной. Менять вид ссылки в Excel таким образом очень удобно, ведь при этом нет необходимости прибегать к смене раскладки на клавиатуре.