Здравствуйте, дорогие читатели. Я решил выделить эту тему в отдельный пост, потому что многие не знают какие бывают ссылки в Эксель и не представляют, чем они отличаются друг от друга. Ежедневно я получаю вопросы от читателей, которые неправильно применяют ссылки на ячейки. Из-за этого, использование формул может давать ошибочные результаты. Уделите этому тексту 5 минут, чтобы потом не было неожиданных результатов вычислений.
Ссылки в Эксель
Давайте разберемся какие типы ссылок бывают в программе. Сразу уточню, для записи в одной ячейке, тип используемой ссылки не имеет значения. Но если вы будете копировать формулу в другое место, использование разных ссылок даёт разные результаты. Так вот, ссылки на ячейки бывают трёх видов:
- Абсолютные ссылки. Они однозначно указывают адрес ячейки, на которую ссылаются. При копировании, этот адрес не изменяется. Так обычно выглядит абсолютная ссылка в Excel: =$A$1. В этой ссылке перед именами столбцов и строк стоит знак «$», который указывает программе не изменять эти координаты при копировании.
- Относительные ссылки. Такие ссылки запоминают свое положение относительно источника и при копировании так же, изменяют свои координаты. Например, в ячейке А1 записано =В2. Значит, мы ссылаемся на ячейку, которая на одну строку ниже нашей и на один столбец правее. Теперь, если скопировать эту формулу в клетку С3. ссылка в ней будет такая: =D4. т. е. сохранит своё относительное положение к источнику.
Относительные ссылки в Excel удобно использовать, когда нужно применить одну и ту же формулу к списку данных. Тогда при копировании формула берет исходные данные из нужного пункта списка автоматически.
Как видите, в относительных ссылках не ставятся знаки «$», и программа не «замораживает» ни одну из координат.
Я в практике одинаково часто использую все три типа ссылок, поскольку они позволяют решать разные задачи при копировании формул. При этом, все они используются постоянно.
Как изменить тип ссылки в Эксель
Изменение типа ссылки – очень простая задача, которую можно выполнить двумя путями. Как вы уже поняли, чтобы координаты не меняли своего значения при копировании, нужно устанавливать перед ними знак «$». И делается это так:
- Вручную – дважды кликните на ячейке со ссылкой для редактирования содержимого. Проставьте «$» перед теми координатами, которые нужно «заморозить» и нажмите Enter .
- Автоматическим перебором — установите курсор на ссылке и нажимайте F4. пока не получите нужный вид ссылки. Каждое нажатие клавиши устанавливает в данной ссылке новый тип ссылки. Нажатие клавиши циклически изменяет варианты ссылок по кругу: Относительная — Абсолютная — Изменяются столбцы — Изменяются строки — Относительная… Я пользуюсь этим способом, и он ни разу не подводил.
Внешние ссылки в Эксель
Ссылки могут указывать на ячейки на том же листе, на другом листе той же книги, на листе другой книги. Эти ссылки имеют свои особенности:
- Ссылка на ячейку на том же листе по умолчанию ставится относительной и выглядит, как набор координат. Например: =А1
- Ссылка на ячейку на другом листе активной книги. по умолчанию тоже относительная. но содержит имя листа, на котором расположена искомая ячейка. Имя листа и адрес ячейки разделяются восклицательным знаком. Например, =Лист1!А1 .
- Ссылка на ячейку в другом файле по умолчанию абсолютная и записывается, как комбинация: [Имя_рабочей_книги]Имя_листа! Адрес_ячейки. Например: =[Книга1.xlsx]Лист1!$А$1. И здесь нужно сделать несколько уточнений:
- Если целевая рабочая книга закрыта, ссылка изменяет вид: Адрес_рабочей_книги[Имя_рабочей_книги]Имя_листа! Адрес_ячейки .
- Если имя листа или книги содержит пробелы, ссылка заключается в одинарные кавычки по такой схеме: ’[Книга 1.xlsx]Лист 1’!$А$1 .
Как видите, ссылки на другие ячейки могут выглядеть достаточно сложно. Не беспокойтесь, вам не придётся набирать их вручную, программа всё сделает сама, вам нужно лишь кликнуть на ячейку, чтобы сослаться на нее. Подробно об этом я рассказал в статье о правилах написания формул .
сколько ссылок нужно для тиц 10