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

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

 

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

Читайте также: «Как удалить ячейки в Excel”

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

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

Также все ссылки делятся на внутренние и внешние и используются в разных случаях:

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

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

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

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

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

 

 

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

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

 

 

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

 

 

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

 

 

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

 

 

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

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

Обращаем ваше внимание на то, что в Excel предусмотрено два стиля ссылок:

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

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

1. Перейдите в меню «Файл”.

    

   

    

2. В боковом списке слева нажмите пункт «Настройки» внизу окна.

    

   

    

3. Откроется окно с параметрами программы, где выбираем раздел «Формулы». Здесь, в блоке «Работа с формулами», мы можем установить флажок рядом с параметром «Стиль ссылки R1C1», если нам нужен этот стиль, или снять его, если мы хотим вернуться к более привычному виду. Сделав выбор, нажмите OK, чтобы сохранить настройки.

    

   

    

Несмотря на разницу в отображении, оба стиля в программе абсолютно равнозначны, хотя программа изначально выбрала ссылки в формате А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.

 

 

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

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

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

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

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

 

 

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

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

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

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

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

 

 

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

1. Перейдите в нужную ячейку, наберите знак «=», затем нажмите на название нужного листа внизу окна программы.

    

   

    

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

    

   

    

3. Нажимаем клавишу Enter, что вернет нас к исходному листу с окончательным результатом.

    

   

    

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

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

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

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

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

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

 

 

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

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

1. Переходим в ячейку, куда планируем добавить выражение. Ставим знак “=”.

    

   

    

2. Мы переключаемся на открытую книгу, содержащую ячейку, на которую мы хотим сослаться. Щелкаем по нужному листу, а затем по нужной ячейке.

    

   

    

3. После нажатия Enter мы вернемся к исходной книге с окончательным результатом для данного выражения.

    

   

    

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

    

   

    

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

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

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

= НЕПРЯМОЙ (Cell_reference, A1), где:

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

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

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

    

   

    

2. Откроется окно Мастера функций. Нажимаем на текущую категорию и в открывшемся списке выбираем строку «Ссылки и массивы”.

    

   

    

3. В предложенном списке операторов выберите ДВССЫЛ и нажмите кнопку ОК.

    

   

    

4. Мы увидим окно для редактирования аргументов функции, после заполнения которого нажимаем ОК.
   • поместите курсор в область «Link_to_cell», затем вручную введите адрес ячейки, на которую мы планируем ссылаться. Также можно вместо того, чтобы вводить координаты вручную, просто кликнуть по нужному пункту.
   • в поле «А1» можем написать одно из двух указанных выше значений, либо оставить его пустым.

      

     

      

5. Результат появится в выбранной ячейке в соответствии с указанной нами конфигурацией.

    

   

    

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

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

1. Для создания гиперссылки необходимо перейти в специальное окно, позволяющее ее создать. Вы можете сделать это разными способами:
   • кликаем правой кнопкой мыши в ячейке, куда планируем вставить гиперссылку, и в появившемся списке кликаем по пункту «Ссылка…”.

      

     

      

   • выделяем нужную ячейку, переключаемся на вкладку «Вставка», где нажимаем кнопку «Связать”.

      

     

      

   • или вы можете использовать CTRL+K, чтобы вставить гиперссылку.
2. Появится окно, позволяющее настроить гиперссылку. На выбор предлагаются следующие позиции комплекта:
   • файл или веб-страница (по умолчанию);
   • новый документ;
   • разместить в документе
   • эл адрес;
3. Давайте попробуем связать ячейку с документом. Для этого в основной части окна откройте папку с нужным файлом и отметьте его. Документом могут служить файлы с расширением «xls» («xlsx») или других форматов. Выбрав нужный документ, нажмите кнопку ОК.

    

   

    

4. В ситуациях, когда вам необходимо создать ссылку на страницу в Интернете, при выборе того же элемента введите ссылку на веб-страницу в поле «Адрес», а затем нажмите кнопку ОК.

    

   

    

5. Когда вам нужно создать гиперссылку на определенное место в текущем документе, выберите пункт «Поместить в документ». В основной области окна отметьте нужный лист и координаты ячейки, с которой хотите создать ссылку. Как обычно, когда вы закончите, нажмите OK.

    

   

    

6. В тех случаях, когда нам необходимо создать ссылку на новый документ Excel, выбираем соответствующий пункт. Затем мы создаем имя для новой книги, выбираем место для сохранения и нажимаем «ОК.

    

   

    

7. В определенных ситуациях необходимо связать что-то в книге с письмом. В этом может помочь пункт «Электронная почта». Здесь в поле «Адрес электронной почты» пишем, соответственно, нужный адрес электронной почты, после чего нажимаем ОК.

    

   

    

8. После того, как мы выбрали тип гиперссылки, заполнили необходимые поля и нажали «ОК», в выбранной ячейке появится синяя активная ссылка, нажатие на которую приведет к переходу к связанному с ней объекту.

    

   

    

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

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

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

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

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

1. Выберите нужную ячейку и нажмите кнопку «Вставить функцию”.

    

   

    

2. В категории «Ссылки и массивы» выберите оператор «ГИПЕРССЫЛКА» и нажмите «ОК.

    

   

    

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

      

     

      

4. Получаем в выбранной ячейке активную ссылку, которая ведет на веб-страницу, адрес которой мы указали.

    

   

    

Заключение

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