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

Ссылки — один из основных инструментов при работе в Microsoft Excel. Они являются неотъемлемой частью формул, используемых в программе. Некоторые из них используются для доступа к другим документам или даже ресурсам в Интернете. Давайте узнаем, как создавать различные типы ссылочных выражений в Excel.
Создание различных типов ссылок
Следует сразу отметить, что все ссылочные выражения можно разделить на две широкие категории: те, которые предназначены для вычислений как часть формул, функций, других инструментов, и те, которые используются для перехода к указанному объекту. Последние также называют гиперссылками. Кроме того, ссылки (ссылки) делятся на внутренние и внешние. Внутренняя ссылка на выражения в книге. Чаще всего они используются для вычислений как часть формулы или аргумента функции, которые указывают на конкретный объект, содержащий обрабатываемые данные. Те, кто ссылается на место на другом листе документа, могут быть отнесены к той же категории. Все они в зависимости от свойств делятся на относительные и абсолютные.
Внешние ссылки относятся к объекту, внешнему по отношению к текущей книге. Это может быть другая книга Excel или место в ней, документ другого формата или даже веб-сайт в Интернете.
Тип создаваемого типа зависит от выбранного вами метода создания. Давайте подробнее рассмотрим различные методы.
Способ 1: создание ссылок в составе формул в пределах одного листа
Во-первых, давайте посмотрим, как создавать различные варианты ссылок для формул, функций и других инструментов вычислений Excel на одном листе. Ведь они наиболее часто используются на практике.
Простейшее ссылочное выражение выглядит так:
= A1
Обязательный атрибут выражения — знак «=». Только когда этот символ вставлен в ячейку перед выражением, он будет восприниматься как ссылка. Обязательный атрибут — это также имя столбца (в данном случае A) и номер столбца (в данном случае 1).
Выражение «= A1» означает, что данные объекта с координатами A1 вставляются в элемент, в котором он установлен.
Если заменить выражение в ячейке, где отображается результат, например, на «= B5», в него будут вставлены значения объекта с координатами B5.
вы также можете выполнять различные математические операции, используя ссылки. Например, напишем следующее выражение:
= LA1 + SI5
Нажимаем кнопку Enter. Теперь в элементе, в котором находится это выражение, будут добавлены значения, помещенные в объекты с координатами A1 и B5.
Деление, умножение, вычитание и любые другие математические операции выполняются по тому же принципу.
Чтобы написать отдельную ссылку или как часть формулы, вам вообще не нужно вводить ее с клавиатуры. Просто установите символ «=», затем щелкните левой кнопкой мыши объект, на который вы хотите сослаться. Его адрес будет отображаться в объекте, где установлен знак «равно».
Однако следует отметить, что стиль координат A1 — не единственный, который можно использовать в формулах. Параллельно Excel работает в стиле R1C1, в котором, в отличие от предыдущей версии, координаты указываются не буквами и цифрами, а исключительно цифрами.
R1C1 равен A1, а R5C2 равен B5. То есть в данном случае, в отличие от стиля А1, на первом месте находятся координаты строки, а на втором — координаты столбца.
Оба стиля имеют одинаковый эффект в Excel, но масштаб координат по умолчанию — A1. Чтобы переключиться на представление R1C1, в параметрах Excel в разделе «Формулы» установите флажок рядом с элементом «Стиль ссылки R1C1».
После этого на горизонтальной панели координат вместо букв появятся числа, а выражения в строке формул будут выглядеть как R1C1. Кроме того, выражения, написанные не путем ввода координат вручную, а путем нажатия на соответствующий объект, будут отображаться в виде модуля относительно ячейки, в которой они установлены. На изображении ниже это формула
= RC
Если вы аннотируете выражение вручную, оно примет обычный вид R1C1.
В первом случае был представлен относительный тип (= RC), а во втором (= R1C1) — абсолютный. Абсолютные ссылки относятся к определенному объекту, а относительные ссылки относятся к положению элемента относительно ячейки.
Если вернуться к стандартному стилю, относительные ссылки будут иметь вид A1, а абсолютные — $ A $ 1. По умолчанию все ссылки, созданные в Excel, являются относительными. Это выражается в том, что при копировании с использованием маркера заливки значение в них изменяется по отношению к перемещению.
- Чтобы увидеть, как это будет выглядеть на практике, обратитесь к ячейке A1. Установите символ «=» в любом элементе пустого листа и щелкните по объекту с координатами A1. После того, как адрес отобразится как часть формулы, нажмите кнопку Enter.
- Наведите курсор на нижний правый край объекта, который отображает результат обработки формулы. Курсор изменится на маркер заполнения. Удерживая левую кнопку мыши, перетащите указатель параллельно диапазону с данными, которые вы хотите скопировать.
- После завершения копирования мы видим, что значения в последующих элементах диапазона отличаются от значений в первом (скопированном) элементе. Если вы выберете ячейку, в которую мы скопировали данные, вы увидите в строке формул, что ссылка также изменилась по отношению к перемещению. Это признак его относительности.
Свойство относительности иногда очень помогает при работе с формулами и таблицами, но в некоторых случаях необходимо скопировать точную формулу без изменений. Для этого ссылку необходимо преобразовать в абсолютную.
- Для проведения преобразования достаточно приблизить символ доллара ($) к горизонтальным и вертикальным координатам ($).
- После применения индикатора заливки можно увидеть, что значение во всех последующих ячейках при копировании отображается точно так же, как в первой. Кроме того, когда вы наводите курсор на любой объект в диапазоне ниже в строке формул, вы заметите, что ссылки остаются полностью неизменными.
Помимо абсолютного и относительного, существуют еще и смешанные ссылки. В них знаком доллара отмечены только координаты столбца (пример: $ A1),
или просто координаты строки (пример: A $ 1).
Знак доллара можно ввести вручную, нажав соответствующий символ на клавиатуре ($). Он будет выделен, если вы нажмете клавишу «4» в английской заглавной раскладке клавиатуры».
Но есть более удобный способ добавить указанный символ. Вам просто нужно выбрать эталонное выражение и нажать клавишу F4. Впоследствии знак доллара появится одновременно на всех горизонтальных и вертикальных координатах. После повторного нажатия F4 ссылка преобразуется в смешанную: знак доллара останется только в координатах строки и исчезнет в координатах столбца. Повторное нажатие F4 приведет к обратному эффекту: символ доллара появится в координатах столбцов, но исчезнет в координатах строк. Также при нажатии F4 ссылка преобразуется в относительную без знака доллара. Следующее нажатие превращает его в абсолютное. И так по новому кругу.
В Excel вы можете ссылаться не только на конкретную ячейку, но и на весь диапазон. Адрес диапазона выглядит как координаты левого верхнего и правого нижнего элементов, разделенных двоеточием (:). Например, диапазон, выделенный на изображении ниже, имеет координаты A1: C5.
В результате ссылка на этот массив будет выглядеть так:
= A1: C5
Способ 2: создание ссылок в составе формул на другие листы и книги
До этого мы смотрели только акции на листе. Давайте теперь посмотрим, как ссылаться на место на другом листе или даже в книге. В последнем случае это будет уже не внутренняя ссылка, а внешняя ссылка.
Принципы создания точно такие же, как мы обсуждали выше с действиями на листе. Только в этом случае вам также потребуется указать адрес листа или книги, в которой находится ячейка или диапазон, на который вы хотите сослаться.
Чтобы указать значение на другом листе, необходимо указать его имя между знаком «=» и координатами ячейки, а затем установить восклицательный знак.
Таким образом, ссылка на ячейку на листе 2 с координатами B4 будет выглядеть так:
= Лист2! B4
Выражение можно ввести вручную с клавиатуры, но гораздо удобнее делать следующее.
- Мы устанавливаем знак «=» в элементе, который будет содержать ссылочное выражение. Затем, используя ярлык над строкой состояния, перейдите на лист, на котором расположен объект, на который вы хотите сослаться.
- После перехода выделите этот объект (ячейку или диапазон) и нажмите кнопку Enter.
- После этого произойдет автоматический возврат на предыдущий лист, но нужная нам ссылка будет сгенерирована.
Теперь попробуем разобраться, как ссылаться на предмет, найденный в другой книге. Прежде всего, вам необходимо знать, что различные функции и инструменты Excel работают по-разному с другими книгами. Некоторые из них работают с другими файлами Excel, даже когда они закрыты, в то время как другие требуют запуска этих файлов для связи.
Из-за этих характеристик тип ссылки на другие книги также отличается. Если вы встраиваете это в инструмент, который работает только с запущенными файлами, вы можете просто указать название книги, на которую вы ссылаетесь. Если вы собираетесь работать с файлом, который не будете открывать, в этом случае необходимо указать полный путь. Если вы не знаете, в каком режиме вы будете работать с файлом, или не знаете, как конкретный инструмент может работать с ним, то в этом случае, опять же, лучше всего указать полный путь. Это уж точно не будет лишним.
Если вам нужно сослаться на объект с адресом C9, расположенный на листе 2 в работающей книге с именем «Excel.xlsx», вам необходимо написать следующее выражение в элементе листа, где будет отображаться значение:
= Лист2! C9
Если вы планируете работать с закрытым документом, то, помимо прочего, вам необходимо указать путь к его расположению. Например:
= ‘D: New folderList2’! C9
Как и при создании выражения для ссылки на другой лист, когда вы связываете элемент из другой книги, вы можете вставить его вручную или сделать это, выбрав соответствующую ячейку или диапазон в другом файле.
- Вставляем символ «=» в ячейку, в которой будет найдено ссылочное выражение.
- Затем откройте книгу, на которую хотите создать ссылку, если она не запущена. Щелкаем на вашем листе в том месте, на которое вы хотите сослаться. Затем нажмите Enter.
- Автоматически возвращается к предыдущей книге. Как видите, он уже содержит ссылку на элемент файла, который мы щелкнули на предыдущем шаге. Он содержит только имя без пути.
- Но если мы закроем файл, на который ссылаемся, ссылка будет немедленно преобразована автоматически. Он представит полный путь к файлу. Итак, если формула, функция или инструмент поддерживает работу с закрытыми книгами, то благодаря преобразованию ссылочного выражения можно будет воспользоваться этой возможностью.
Как видите, вставка ссылки на элемент другого файла путем нажатия на него не только намного удобнее, чем ввод адреса вручную, но и более универсальна, так как в этом случае сама ссылка трансформируется в зависимости от того, ссылается ли книга закрыто или открыто.
Способ 3: функция ДВССЫЛ
Другой способ обратиться к объекту в Excel — использовать функцию ДВССЫЛ. Этот инструмент разработан специально для создания текстовых справочных выражений. Созданные таким образом связи также называются «сверхабсолютными», поскольку они связаны с указанной в них ячейкой даже более строго, чем типичные абсолютные выражения. Синтаксис этого оператора:
= КОСВЕННО (ссылка; a1)
«Ссылка» — это аргумент, который ссылается на ячейку в текстовом формате (заключенный в кавычки);
«A1» — необязательный аргумент, который указывает, используются ли координаты в стиле A1 или R1C1. Если значение этого аргумента — «ИСТИНА», применяется первая опция, если «ЛОЖЬ» — вторая опция. Если этот аргумент полностью опущен, по умолчанию используется адресация A1.
- Отметим элемент листа, на котором будет размещена формула. Щелкните значок «Вставить функцию».
- В мастере в блоке «Ссылки и массивы» выберите «КОСВЕННО». Щелкните «ОК».
- Откроется окно аргументов для этого оператора. В поле «Ссылка на ячейку» поместите курсор и выберите элемент листа, на который вы хотите сослаться, щелчком мыши. После того, как адрес появится в поле, «заключите» его в кавычки. Оставьте второе поле пустым («A1»). Щелкните «ОК».
- Результат обработки этой функцией отображается в выбранной ячейке.
Подробнее о преимуществах и нюансах работы с функцией КОСВЕННО можно прочитать в отдельном уроке.
Способ 4: создание гиперссылок
Гиперссылки отличаются от типов ссылок, которые мы обсуждали выше. Они служат не для «вытягивания» данных из других областей в ячейку, в которой они находятся, а для перехода при нажатии на область, к которой они относятся.
- Есть три варианта доступа к окну создания гиперссылки. По первому из них нужно выбрать ячейку, в которую будет вставлена гиперссылка, и щелкнуть по ней правой кнопкой мыши. В контекстном меню выберите опцию «Гиперссылка…».
Вместо этого после выбора элемента, в который будет вставлена гиперссылка, перейдите на вкладку «Вставка». Там на ленте нужно нажать на кнопку «Гиперссылка».
Также после выбора ячейки вы можете применить, нажав клавиши CTRL + K.
- После применения любого из этих трех параметров откроется окно «Создать гиперссылку». В левой части окна вы можете выбрать, с каким объектом вы хотите связаться:
- С местом в текущей книге;
- С новой книгой;
- С помощью веб-сайта или файла;
- Из письма.
- По умолчанию окно запускается в режиме подключения файла или веб-страницы. Чтобы связать элемент с файлом, в центральной части окна с помощью средств навигации необходимо перейти в каталог жесткого диска, где найдите нужный файл и выберите его. Это может быть книга Excel или любой другой формат файла. После этого координаты отобразятся в поле «Адрес». Затем, чтобы завершить операцию, нажмите кнопку «ОК».
Если вам необходимо установить соединение с веб-сайтом, в том же разделе окна создания гиперссылки в поле «Адрес» просто укажите адрес необходимого веб-ресурса и нажмите кнопку «ОК».
Если вам нужно указать гиперссылку на место в текущей книге, вам следует перейти в раздел «Ссылка на место в документе». Также в центральной части окна нужно указать лист и адрес ячейки, с которой вы хотите установить соединение. Щелкните «ОК».
Если вам нужно создать новый документ Excel и связать его с помощью гиперссылки на текущую книгу, вам следует перейти в раздел «Ссылка на новый документ». Затем в центральной части окна дайте ему имя и укажите его расположение на диске. Затем нажмите «ОК».
При желании вы также можете связать элемент листа по электронной почте. Для этого перейдите в раздел «Связь с электронной почтой» и введите адрес электронной почты в поле «Адрес». Щелкните «ОК».
- После вставки гиперссылки текст в ячейке по умолчанию становится синим. Это означает, что гиперссылка активна. Чтобы перейти к объекту, с которым он связан, просто дважды щелкните по нему левой кнопкой мыши.
Кроме того, гиперссылку можно создать с помощью встроенной функции, имя которой говорит само за себя — «ГИПЕРССЫЛКА».
Этот оператор имеет синтаксис:
= ГИПЕРТЕКСТОВАЯ ССЫЛКА (адрес, имя)
«Адрес» — это тема, которая указывает адрес веб-сайта в Интернете или файла на жестком диске, к которому вы хотите подключиться.
«Имя» — это текстовый аргумент, который появится в элементе листа, содержащем гиперссылку. Это необязательный аргумент. Если он отсутствует в элементе листа, будет отображен адрес объекта, на который ссылается функция.
- Выберите ячейку, в которой будет размещена гиперссылка, и щелкните значок «Вставить функцию».
- В мастере перейдите в раздел «Ссылки и массивы». Отметьте имя «ГИПЕРССЫЛКА» и нажмите «ОК».
- В окне темы в поле «Адрес» укажите адрес сайта или файла на жестком диске. В поле «Имя» введите текст, который будет отображаться в элементе листа. Щелкните «ОК».
- Далее будет создана гиперссылка.
Мы обнаружили, что в таблицах Excel есть две группы ссылок: используемые в формулах и используемые для навигации (гиперссылки). Кроме того, эти две группы делятся на множество более мелких разновидностей. Алгоритм процедуры создания зависит от конкретного типа подключения.
Мы рады, что смогли помочь вам решить проблему.