Как сделать гиперссылку на другой лист или файл Excel

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

Вы могли не знать, но существует разница между ссылкой на лист и совершенно другой файл в Excel.

А также есть несколько небольших моментов которые необходимо знать, если вы пользуетесь ссылками.

Но ничего сложного там нет, не переживайте.

Когда вы дочитаете статью — вы будете понимать все что касается гиперссылок в Excel.

Итак, приступим!

Ссылка на ячейку

Это знают все кто когда-либо работал в Excel.

Так выглядит ссылка на ячейку:

=A1  

Если в первой ячейке столбика А находилось значение «100», то при ссылке =A1, вставленной в ячейку столбика В вы получите то же значение и в В.

Ссылка на ячейку в другом листе

В таком случае, ссылка будет выглядеть так:

Лист!Ячейка

Допустим, нам нужно сделать ссылку на А2, которая находится на листе с именем Лист2:

= Лист2!A2

Ссылка на диапазон будет выглядеть так:

Лист!Диапазон:Диапазон

Вот пример:

= Лист2!A2:C11

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

Если лист имеет пробелы в названии — просто заключите название в одинарные кавычки.

Вот пример:

='Объем продаж'!A2

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

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

Итак, как же создавать ссылки наиболее безопасно и удобно?

Автоматическое создание ссылки на другой лист в той же рабочей книге

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

Таким образом, вы исключите ошибки связанные, например, с синтаксисом и так далее.

А как создать ссылку на диапазон ячеек в другом листе?

Пошаговая инструкция:

1. Выберите ячейку;
2. Поставьте равно;
3. Выберите лист;
4. Выделите диапазон ячеек (или ячейку) на которые нужно сослаться;
5. Подтвердите.

Готово!

А как использовать такие ссылки в расчетах?

Вот пример:

• Прописываем =сумм;

• Прописываем нужное название листа;
• А также диапазон;

• Подтверждаем.

Итак, допустим в вашей формуле есть несколько аргументов. В первом вы использовали ссылку на ячейку из другого листа. А теперь, во втором аргументе, хотите использовать ссылку на ячейку текущего листа. Как это сделать?

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

Большой плюс Excel в том, что если вы скопируете формулу и поместите её на другом листе, он автоматически пропишет правильно эту самую формулу.

Это может быть сложно, но давайте рассмотрим пример.

Допустим мы ссылаемся на:

=SalesData!A1 

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

=SalesData!A2  

Формула относительна и Excel автоматически исправляет формулу на правильную.

Если вы хотите сделать так, что формула не изменялась, пропишите перед столбиком и номером строки доллар:

=SalesData!$A$1  

Доллар сообщает Excel о том, что формула является абсолютной и корректировать её не нужно.

Ну с этим все понятно скажете вы, а как же сделать ссылку на другой файл?

Как сделать гиперссылку на другой файл в Excel

В случае когда вы ссылаетесь на другой файл Excel, вам нужно знать открытый это файл или нет.

Рассмотрим несколько примеров.

Ссылка на ячейку в другом файле Excel

Итак, нам нужно будет указать имя файла, листа, и адрес ячейки.

Вот пример

='[Имя_файла]Лист!Адрес_ячейки  

Допустим у нас есть таблица Excel.xlsx, а в нем есть лист Продажи

Вот пример использования функции:

=[Excel.xlsx]Продажи!A1  

Если в названии есть пробелы или символы (№;%:# и так далее) не забывайте использовать одинарные кавычки.

Пример:

='[Excel.xlsx]Продажи!A1   ='[Excel.xlsx]Продажи'!A1   ='[Excel.xlsx]Продажи'!A1  

Автоматически создаем ссылку на другой файл

Тут тоже самое — лучше пользоваться автоматическими методами.

Это позволит избежать большого количества сделанных ошибок по неосторожности.

Как создать ссылку на файл?

Пошаговая инструкция:

• Начните вводить формулу (=сумм или =sum в англ. версии Excel);
• Укажите название файла;
• Выберите лист и диапазон;

• Подтвердите.

Готово! Ссылка создана.

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

Бывают конечно, ситуации, когда нужно сделать так, чтобы ссылка изменялась. В таком случае, вам нужно удалить знаки доллара из формулы.

Ссылка на ячейку в закрытом файле Excel

Итак, в прошлом примере, файл был открыт и нам достаточно было указать его название в ссылке. Таким образом, Excel сразу понимал что нам нужно. Но как быть если файл не открыт? Ведь в таком случае мы не можем указать просто его название.

Ответ прост — вместо имени файла, нам нужно указать полный путь до этого файла.

Вот пример:

='C:\Users\Excel\Desktop\[Example File.xlsx]Лист1'!$A$1  

Что используется в формуле:

• Полный путь до файла Excel;
• Название файла;
• Название листа;
• И адрес ячейки.

Но проще всего сделать так: открыть файл, сделать ссылку на него, а после закрыть. Если вы сделаете именно так, Excel в ссылке автоматически вставит путь до файла, на который мы ссылаемся.

Изменение ссылки после переноса файла в другую директорию

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

Но если вы куда-либо переместите файл, на который ссылаетесь. Ссылка «сломается». Просто потому, что не сможет найти файл по указанному пути.

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

Также будет открыто следующее окно:

Если Excel сам не обновит ссылки, вам придется сделать это вручную. Нажав кнопку «Обновить ссылки» и указав новое расположение файла.

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

Ссылка на именованный диапазон

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

А если, допустим, много где пришлось использовать этот именованный диапазон, а теперь нужно внести какие-то изменения — как только вы измените сам именованный диапазон, сразу же изменятся и результаты всех вычислений.

Как его создать?

Пошаговая инструкция:

• Выделите диапазон ячеек и щелкните на «Формулы»;

• Далее — «Присвоить имя»;

• Присвойте ему имя в открывшемся окне.

• В параметре «Область» укажите «Книга»;

• Проверьте правильно ли прописан диапазон;

• Подтвердите.

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

Вот пример (диапазон называется SalesData):

=СУММ(SalesData)  

Но что если вам необходимо вычислять что-либо на других листах или файлах, можно ли сделать так?

Конечно же — да.

Просто делайте все так же, как мы рассматривали в предыдущих разделах.

Ссылка на именованный диапазон в другом файле Excel

Все довольно просто. Перед указанием именованного диапазона укажите имя файла (если он открыт).

Вот пример:

=СУММ(ExampleFile.xlsx!SalesData)  

В названии файла есть пробел? Заключите его в кавычки, пример ниже:

=СУММ('Example File.xlsx'!SalesData)  

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

=СУММ('[Example File.xlsx]Sheet1'!SalesData)

Итак, ранее я уже говорил — лучше всего создавать именованный диапазон сразу на уровне файла, но это все равно на ваше усмотрение.

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

=СУММ('C:\Users\Excel\Desktop\Example File.xlsx'!SalesData)  

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

Как не запутаться в именованных диапазонах

Если вы очень часто создаете именованные диапазоны — вы можете легко запутаться в них. В таком случае работать станет крайне сложно. Что же делать?

Рассмотрим пример.

Допустим, у вас есть именованный диапазон, вы хотите использовать его в функции.

Как это сделать?

Пошаговая инструкция:

• Выделите ячейку и введите формулу до того аргумента, где нужно использовать именованный диапазон;

• Далее нажмите F3, откроется окно со всеми существующими именованными диапазонами.

• И выберите тот, который нужно вставить.

Готово! Диапазон вставлен в функцию.

Так можно делать только с теми именованными диапазонами, которые вы создали в текущем листе/файле Excel. Если вы хотите сослаться на диапазон из другого файла — вам придется сделать это вручную.

Вот и все, я рассказал вам все важные моменты.

Надеюсь, вам помогла эта статья.