Создание ссылок в Excel

Наверное, каждый пользователь Microsoft Excel довольно часто создает ссылки, потому что программа постоянно использует формулы, и ссылки нужны для работы с ними. Кроме того, ссылки (так называемые «ссылки» по-другому) используются для просмотра определенных страниц в Интернете, доступа к другим документам или внешним источникам. Итак, давайте узнаем, как именно создавать ссылки в Excel.

Разновидности ссылок

Ссылки делятся на две основные категории:

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

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

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

Поэтому метод создания ссылок следует выбирать в зависимости от типа создаваемой ссылки. Мы подробнее рассмотрим все варианты ниже.

Как создавать ссылки на одном листе

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

Самая простая ссылка — это просто указание адреса ячейки в виде: = B2.

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

В нашем случае выражение «= B2» означает, что в ячейке D3 (на ее месте может быть любая другая ячейка), где мы написали эту формулу, будет «подтянуто» значение из ячейки с адресом B2.

Поскольку ячейка D3 относится к B2, если мы изменим значение на B2, содержимое D3 изменится автоматически.

Благодаря таким простым подключениям у нас есть возможность выполнять различные арифметические операции. Например, введите выражение в ячейку D3: = A5 + B2.

После ввода этого выражения нажмите клавишу Enter, чтобы получить результат вычисления, который включает ячейки A5 и B2.

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

Чтобы пользоваться формулами было еще удобнее, нет необходимости каждый раз вручную вводить координаты нужной ячейки. Мы просто ставим знак «равно» в начале выражения, затем щелкаем левой кнопкой мыши по тем ячейкам (при необходимости, выбираем диапазоны ячеек), которые должны участвовать в вычислениях.

Обратите внимание, что в Excel есть два стиля связывания:

• в более привычной форме A1
• в формате R1C1, когда координаты представлены буквами и цифрами, но только числами. Кроме того, первая указывает номер строки, а вторая — номер столбца.

Чтобы изменить стиль адресов, сделайте следующее:

1. Зайдите в меню «Файл”.
2. В списке слева нажмите «Параметры» внизу окна.
3. Откроется окно с параметрами программы, где выбираем раздел «Формулы». Здесь, в блоке «Работа с формулами», мы можем поставить галочку напротив опции «R1C1 connection style», если вам нужен только аналогичный стиль, или снять галочку, если мы хотим вернуться к более привычному виду. Сделав выбор, нажмите ОК, чтобы сохранить настройки.

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

Теперь давайте сосредоточимся на опции связи R1C1. Вот как выглядит наш пример с такими ссылками.

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

Но если набрать выражение вручную, все станет нагляднее.

Дело в том, что в первом случае отображалась относительная форма ссылок (= R [2] C [-3] + R [-1] C [-2]), тогда как при ручном вводе ссылки представлены в абсолютном выражение (= R5C1 + R2C2).

Разница между данными типа заключается в том, что абсолютные ссылки относятся к положению конкретного объекта независимо от ячейки с данным выражением, тогда как относительные ссылки относятся к положению объектов относительно последней ячейки, в которую записано наше выражение.

При обычном способе отображения ссылок относительные ссылки выглядят как просто буква с цифрой (A1), в то время как в абсолютных ссылках перед именем столбца и номером строки добавляется знак «$» ($ A $ 1).

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

Давайте посмотрим, как это работает, на примере ячейки B1.

1. Например, мы выбираем ячейку D1 и записываем в нее выражение, относящееся к ячейке B1. Выглядит это так: = B1.
2. После ввода формулы нажмите клавишу Enter, чтобы получить результат.
3. Теперь переместите указатель мыши в правый нижний угол ячейки, как только его вид изменится на крест, удерживая левую кнопку мыши и растягивая формулу вниз.
4. Готово, формула копируется в остальные ячейки.
5. Теперь, если мы поместим курсор в ячейку D2, мы увидим, что он относится к B2, а не к B1. Это означает, что ссылка была изменена на смещение (+1). Так, например, ячейка D3 относится к B3 (+2) и т.д.

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

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

1. вы можете исправить адрес ячейки, используя символ «$» перед именем столбца и номером строки.
2. Теперь, если мы снова растянем формулу до нижних строк, мы легко заметим, что значения в нижних ячейках такие же, как в первой ячейке. Дело в том, что из-за ссылки в виде абсолютного адреса ячейки при копировании формулы никаких изменений по смещению не производилось.

Но это еще не все. Помимо относительных и абсолютных ссылок, существуют также смешанные ссылки. В таких ссылках вы можете «заморозить» координаты столбца, и в этом случае символ «$» помещается только перед именем столбца. Например, = $ B1.

Или мы можем исправить только определенную строку, поэтому нам нужно поставить знак «$» перед порядковым номером соответствующей строки. Например, = B $ 1.

Мы можем вручную ввести символ «$», найдя его на клавиатуре. Он печатается в английской раскладке с использованием комбинации Shif + 4.

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

• находясь в формуле, просто выберите адрес нужной ячейки, затем нажмите «F4», после чего знак «$» сразу появится в координатах обеих ячеек.
• если вы снова нажмете «F4», символ «$» останется только перед адресом строки.
• нажав клавишу F4 еще раз, мы изменим отображение смешанной ссылки, оставив знак «$» только перед координатами столбца.
• если мы хотим удалить смешанное соединение, нажмите клавишу «F4» еще раз”.

Ссылка на диапазон ячеек

Помимо ссылки на конкретную ячейку в Excel, вы можете ссылаться на диапазон ячеек, координаты которых включают адрес верхней левой ячейки и нижней правой ячейки, а также знак «:» между ними. Например, координаты A1: C6 соответствуют следующему диапазону на следующем изображении:

Следовательно, ссылка на этот диапазон записывается следующим образом: = A1: C6.

Создание ссылки на другой лист

В приведенных выше примерах мы рассматривали только ссылки на листе. Теперь посмотрим, как создавать ссылки на другие листы.

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

Например, ссылка на ячейку C5 на листе 2 выглядит так: = Sheet2! C5.

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

1. Перейдите в нужную ячейку, напишите знак «=», затем щелкните имя нужного листа в нижней части окна программы.
2. Мы окажемся на втором листе, где также щелкнем по ячейке, которую хотим добавить к нашему выражению.
3. Нажимаем клавишу Enter, что вернет нас к исходному листу с уже готовым результатом.

Внешняя ссылка на другую книгу

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

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

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

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

Итак, допустим, нам нужно создать ссылку на ячейку B5, которая находится на листе в открытой книге «References.xlsx». В этом случае нам нужно написать такое выражение: = [Links.xlsx] Sheet3! B5.

Ссылка на закрытый файл выглядит немного иначе: = ‘С: \ Folder1 \ [Links.xlsx] Sheet3’! B5.

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

1. Перейдите в ячейку, в которую мы собираемся добавить выражение. Ставим знак “=”.
2. Переключитесь на открытую книгу, содержащую ячейку, к которой мы хотим обратиться. Щелкаем по желаемому листу, а затем по нужной ячейке.
3. После нажатия Enter мы вернемся к исходной книге с окончательным результатом для данного выражения.
4. Если нет необходимости, книгу, на которую мы ссылались, можно закрыть. Ссылка изменит внешний вид, и к ней будет добавлен путь к документу. Следовательно, в случаях, когда функция, формула или другой инструмент могут работать с закрытыми документами, такое автоматическое исправление ссылок будет чрезвычайно полезным.

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

Использование оператора ДВССЫЛ

Помимо использования формул в Excel, вы можете использовать специальный оператор INDIRECT для создания ссылки. Формула оператора выглядит так:

= ДВССЫЛ (Ссылка_ячейки; A1), где:

1. Cell_reference — это аргумент, содержащий адрес ячейки (указанный в кавычках).
2. «A1» — определяет стиль используемых координат:
   • «True» — для стиля А1
   • «Ложь» — для стиля R1C1
   • если значение пусто, будет применен стиль A1.

Теперь попробуем применить функцию на практике.

1. Выберите нужную ячейку и щелкните значок «Вставить функцию» (рядом со строкой формул).
2. Откроется окно мастера функций. Щелкните текущую категорию и выберите в раскрывающемся списке строку «Ссылки и массивы”.
3. В списке предложенных операторов выберите КОСВЕННЫЙ и нажмите кнопку ОК.
4. Мы увидим окно редактирования аргументов функции, после их компиляции нажимаем ОК.
   • поместите курсор в область «Cell_reference», затем вручную напишите адрес ячейки, на которую мы собираемся ссылаться. Вы также можете вместо того, чтобы вводить координаты вручную, просто щелкните нужный элемент.
   • в поле «A1» мы можем написать одно из двух значений, описанных выше, или оставить его пустым.
5. Результат появится в выбранной ячейке в соответствии с указанными нами настройками.

Создание гиперссылок

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

1. Чтобы создать гиперссылку, вам нужно перейти в специальное окно, в котором вы сможете ее создать. Сделать это можно несколькими способами:
   • щелкните правой кнопкой мыши ячейку, в которую мы собираемся вставить гиперссылку, и в появившемся списке щелкните элемент «Ссылка…”.
   • отмечаем нужную ячейку, переходим во вкладку «Вставка», где нажимаем кнопку «Ссылка”.
   • или вы можете использовать сочетание клавиш CTRL + K, чтобы вставить гиперссылку.
2. Появится окно, позволяющее настроить гиперссылку. На выбор предлагаются следующие позиции:
   • файл или веб-страницу (по умолчанию);
   • новый документ;
   • вставить в документ;
   • эл адрес;
3. Попробуем связать ячейку с документом. Для этого в основной части окна откройте папку с нужным файлом и отметьте его. В качестве документа можно использовать файлы с расширением «xls» («xlsx») и других форматов. Выбрав нужный документ, нажмите ОК.
4. В ситуациях, когда вам нужно создать ссылку на страницу в Интернете, выбрав тот же элемент, напишите ссылку на веб-страницу в поле «Адрес», затем нажмите OK.
5. Если вам нужно создать гиперссылку на определенную позицию в текущем документе, выберите пункт «Поместить в документ». В основной области окна отметьте нужный лист и точные координаты ячейки, с которой хотите связать. Как обычно, нажмите OK, когда закончите.
6. В тех случаях, когда вам нужно создать ссылку на новый документ Excel, выберите соответствующий элемент. Затем находим название новой книги, выбираем место для сохранения и нажимаем ОК.
7. В определенных ситуациях необходимо связать элемент книги с сообщением электронной почты. В этом может помочь элемент «Электронная почта». Здесь, в поле «Адрес электронной почты», введите требуемый адрес электронной почты соответственно, затем нажмите «ОК.
8. Как только мы выбрали тип гиперссылки, заполнили необходимые поля и нажали ОК, в выбранной ячейке появится активная синяя ссылка, щелкнув по которой мы перейдем к объекту, связанному с ней.

Использование функции ГИПЕРССЫЛКА

Кроме того, программа Excel предлагает возможность создать гиперссылку с помощью функции, которая называется «ГИПЕРССЫЛКА». Формула оператора выглядит так:

= ГИПЕРТЕКСТОВАЯ ССЫЛКА (Адрес; Имя), где:

• Фактически, «Адрес» — это конкретный адрес страницы в Интернете или путь к файлу, с которым нужно связать выбранный элемент.
• «Имя» — это текстовое значение, отображаемое в выбранном элементе, при нажатии гиперссылка активируется.

Как пользоваться оператором ГИПЕРССЫЛКА”:

1. Выберите нужную ячейку и нажмите кнопку «Вставить функцию”.
2. В категории «Ссылки и массивы» выберите оператор «ГИПЕРССЫЛКА» и нажмите «ОК.
3. Введите аргументы функции и нажмите ОК.
   • в поле «Адрес» укажите путь к файлу или конкретный адрес страницы в Интернете.
   • в поле «Имя» впишите значение текста, который будет отображаться в выбранной ячейке.
4. Получаем в выбранной ячейке активную ссылку, которая ведет на веб-страницу, адрес которой мы указали.

Заключение

Таким образом, Excel использует два типа ссылок в зависимости от цели. Некоторые из них используются в формулах и функциях, а другие (гиперссылки) позволяют получить доступ к желаемым объектам в виде других документов, веб-страниц или электронных писем. В зависимости от типа выбранной ссылки меняется алгоритм действий по созданию запрашиваемой ссылки.