Lumpics lumpics.ru

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

Ссылки — один из главных инструментов при работе в Microsoft Excel. Они являются неотъемлемой частью формул, которые применяются в программе. Иные из них служат для перехода на другие документы или даже ресурсы в интернете. Давайте выясним, как создать различные типы ссылающихся выражений в Экселе.

Создание различных типов ссылок

Сразу нужно заметить, что все ссылающиеся выражения можно разделить на две большие категории: предназначенные для вычислений в составе формул, функций, других инструментов и служащие для перехода к указанному объекту. Последние ещё принято называть гиперссылками. Кроме того, ссылки (линки) делятся на внутренние и внешние. Внутренние – это ссылающиеся выражения внутри книги. Чаще всего они применяются для вычислений, как составная часть формулы или аргумента функции, указывая на конкретный объект, где содержатся обрабатываемые данные. В эту же категорию можно отнести те из них, которые ссылаются на место на другом листе документа. Все они, в зависимости от их свойств, делятся на относительные и абсолютные.

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

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

Способ 1: создание ссылок в составе формул в пределах одного листа

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

Простейшее ссылочное выражение выглядит таким образом:

=A1

Ссылка A1 в Microsoft Excel

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

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

Если мы заменим выражение в ячейке, где выводится результат, например, на «=B5», то в неё будет подтягиваться значения из объекта с координатами B5.

Ссылка B5 в Microsoft Excel

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

=A1+B5

Клацнем по кнопке Enter. Теперь, в том элементе, где расположено данное выражение, будет производиться суммирование значений, которые размещены в объектах с координатами A1 и B5.

Суммирование с помощью ссылок в Microsoft Excel

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

Чтобы записать отдельную ссылку или в составе формулы, совсем не обязательно вбивать её с клавиатуры. Достаточно установить символ «=», а потом клацнуть левой кнопкой мыши по тому объекту, на который вы желаете сослаться. Его адрес отобразится в том объекте, где установлен знак «равно».

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

Выражение R1C1 равнозначно A1, а R5C2B5. То есть, в данном случае, в отличие от стиля A1, на первом месте стоят координаты строки, а столбца – на втором.

Оба стиля действуют в Excel равнозначно, но шкала координат по умолчанию имеет вид A1. Чтобы её переключить на вид R1C1 требуется в параметрах Excel в разделе «Формулы» установить флажок напротив пункта «Стиль ссылок R1C1».

Установка стиля ссылок R1C1 в Microsoft Excel

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

=R[2]C[-1]

Microsoft Excel работает в режиме R1C1

Если же записать выражение вручную, то оно примет обычный вид R1C1.

Ссылка R1C1 записанная вручную в Microsoft Excel

В первом случае был представлен относительный тип (=R[2]C[-1]), а во втором (=R1C1) – абсолютный. Абсолютные линки ссылаются на конкретный объект, а относительные – на положение элемента, относительно ячейки.

Если вернутся к стандартному стилю, то относительные линки имеют вид A1, а абсолютные $A$1. По умолчанию все ссылки, созданные в Excel, относительные. Это выражается в том, что при копировании с помощью маркера заполнения значение в них изменяется относительно перемещения.

  1. Чтобы посмотреть, как это будет выглядеть на практике, сошлемся на ячейку A1. Устанавливаем в любом пустом элементе листа символ «=» и клацаем по объекту с координатами A1. После того, как адрес отобразился в составе формулы, клацаем по кнопке Enter.
  2. Относительная ссылка в Microsoft Excel
  3. Наводим курсор на нижний правый край объекта, в котором отобразился результат обработки формулы. Курсор трансформируется в маркер заполнения. Зажимаем левую кнопку мыши и протягиваем указатель параллельно диапазону с данными, которые требуется скопировать.
  4. Маркер заполнения в Microsoft Excel
  5. После того, как копирование было завершено, мы видим, что значения в последующих элементах диапазона отличаются от того, который был в первом (копируемом) элементе. Если выделить любую ячейку, куда мы скопировали данные, то в строке формул можно увидеть, что и линк был изменен относительно перемещения. Это и есть признак его относительности.
Относительная ссылка изменилась в Microsoft Excel

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

  1. Чтобы провести преобразование, достаточно около координат по горизонтали и вертикали поставить символ доллара ($).
  2. Абсолютная ссылка в Microsoft Excel
  3. После того, как мы применим маркер заполнения, можно увидеть, что значение во всех последующих ячейках при копировании отображается точно такое же, как и в первой. Кроме того, при наведении на любой объект из диапазона ниже в строке формул можно заметить, что линки осталась абсолютно неизменными.
Абсолютная ссылка скопирована в Microsoft Excel

Кроме абсолютных и относительных, существуют ещё смешанные линки. В них знаком доллара отмечены либо только координаты столбца (пример: $A1),

Смешанная ссылка с закрепленными координатами столбца в Microsoft Excel

либо только координаты строки (пример: A$1).

Смешанная ссылка с закрепленными координатами строки в Microsoft Excel

Знак доллара можно вносить вручную, нажав на соответствующий символ на клавиатуре ($). Он будет высвечен, если в английской раскладке клавиатуры в верхнем регистре кликнуть на клавишу «4».

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

В Excel сослаться можно не только на конкретную ячейку, но и на целый диапазон. Адрес диапазона выглядит как координаты верхнего левого его элемента и нижнего правого, разделенные знаком двоеточия (:). К примеру, диапазон, выделенный на изображении ниже, имеет координаты A1:C5.

Диапазон ячеек в Microsoft Excel

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

=A1:C5

Урок: Абсолютные и относительные ссылки в Майкрософт Эксель

Способ 2: создание ссылок в составе формул на другие листы и книги

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

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

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

Так линк на ячейку на Листе 2 с координатами B4 будет выглядеть следующим образом:

=Лист2!B4

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

  1. Устанавливаем знак «=» в элементе, который будет содержать ссылающееся выражение. После этого с помощью ярлыка над строкой состояния переходим на тот лист, где расположен объект, на который требуется сослаться.
  2. Переход на другой лист в Microsoft Excel
  3. После перехода выделяем данный объект (ячейку или диапазон) и жмем на кнопку Enter.
  4. Выделение ячейки на другом листе в Microsoft Excel
  5. После этого произойдет автоматический возврат на предыдущий лист, но при этом будет сформирована нужная нам ссылка.
Ссылка на ячейку на другом листе в Microsoft Excel

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

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

Если нужно сослаться на объект с адресом C9, расположенный на Листе 2 в запущенной книге под названием «Excel.xlsx», то следует записать следующее выражение в элемент листа, куда будет выводиться значение:

=[excel.xlsx]Лист2!C9

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

='D:\Новая папка\[excel.xlsx]Лист2'!C9

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

  1. Ставим символ «=» в той ячейке, где будет расположено ссылающееся выражение.
  2. Знак равно в Microsoft Excel
  3. Затем открываем книгу, на которую требуется сослаться, если она не запущена. Клацаем на её листе в том месте, на которое требуется сослаться. После этого кликаем по Enter.
  4. Выделение ячейки в другой книге в Microsoft Excel
  5. Происходит автоматический возврат к предыдущей книге. Как видим, в ней уже проставлен линк на элемент того файла, по которому мы щелкнули на предыдущем шаге. Он содержит только наименование без пути.
  6. Ссылка на ячейку на ячейку в другой книге без полного пути в Microsoft Excel
  7. Но если мы закроем файл, на который ссылаемся, линк тут же преобразится автоматически. В нем будет представлен полный путь к файлу. Таким образом, если формула, функция или инструмент поддерживает работу с закрытыми книгами, то теперь, благодаря трансформации ссылающегося выражения, можно будет воспользоваться этой возможностью.
Ссылка на ячейку на ячейку в другой книге с полным путем в Microsoft Excel

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

Способ 3: функция ДВССЫЛ

Ещё одним вариантом сослаться на объект в Экселе является применение функции ДВССЫЛ. Данный инструмент как раз и предназначен именно для того, чтобы создавать ссылочные выражения в текстовом виде. Созданные таким образом ссылки ещё называют «суперабсолютными», так как они связаны с указанной в них ячейкой ещё более крепко, чем типичные абсолютные выражения. Синтаксис этого оператора:

=ДВССЫЛ(ссылка;a1)

«Ссылка» — это аргумент, ссылающийся на ячейку в текстовом виде (обернут кавычками);

«A1» — необязательный аргумент, который определяет, в каком стиле используются координаты: A1 или R1C1. Если значение данного аргумента «ИСТИНА», то применяется первый вариант, если «ЛОЖЬ» — то второй. Если данный аргумент вообще опустить, то по умолчанию считается, что применяются адресация типа A1.

  1. Отмечаем элемент листа, в котором будет находиться формула. Клацаем по пиктограмме «Вставить функцию».
  2. Переход в Мастер функций в Microsoft Excel
  3. В Мастере функций в блоке «Ссылки и массивы» отмечаем «ДВССЫЛ». Жмем «OK».
  4. Переход в окно аргументов функции ДВССЫЛ в Microsoft Excel
  5. Открывается окно аргументов данного оператора. В поле «Ссылка на ячейку» устанавливаем курсор и выделяем кликом мышки тот элемент на листе, на который желаем сослаться. После того, как адрес отобразился в поле, «оборачиваем» его кавычками. Второе поле («A1») оставляем пустым. Кликаем по «OK».
  6. Окно аргументов функции ДВССЫЛ в Microsoft Excel
  7. Результат обработки данной функции отображается в выделенной ячейке.
Результат обработки функции ДВССЫЛ в Microsoft Excel

Более подробно преимущества и нюансы работы с функцией ДВССЫЛ рассмотрены в отдельном уроке.

Урок: Функция ДВССЫЛ в Майкрософт Эксель

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

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

  1. Существует три варианта перехода к окну создания гиперссылок. Согласно первому из них, нужно выделить ячейку, в которую будет вставлена гиперссылка, и кликнуть по ней правой кнопкой мыши. В контекстном меню выбираем вариант «Гиперссылка…». Переход в окно создания гиперссылки через контекстное меню в Microsoft Excel

    Вместо этого можно, после выделения элемента, куда будет вставлена гиперссылка, перейти во вкладку «Вставка». Там на ленте требуется щелкнуть по кнопке «Гиперссылка».

    Переход в окно создания гиперссылки через кнопку на ленте в Microsoft Excel

    Также после выделения ячейки можно применить нажатие клавиш CTRL+K.

  2. После применения любого из этих трех вариантов откроется окно создания гиперссылки. В левой части окна существует возможность выбора, с каким объектом требуется связаться:
    • С местом в текущей книге;
    • С новой книгой;
    • С веб-сайтом или файлом;
    • С e-mail.
  3. Выбор объекта вставки в окне вставки гиперссылки вВыбор объекта вставки в окне вставки гиперссылки в Microsoft Excel Microsoft Excel
  4. По умолчанию окно запускается в режиме связи с файлом или веб-страницей. Для того, чтобы связать элемент с файлом, в центральной части окна с помощью инструментов навигации требуется перейти в ту директорию жесткого диска, где расположен нужный файл, и выделить его. Это может быть как книга Excel, так и файл любого другого формата. После этого координаты отобразятся в поле «Адрес». Далее для завершения операции следует нажать на кнопку «OK». Вставка ссылки на другой файл в окне вставки гиперссылки в Microsoft Excel

    Если имеется потребность произвести связь с веб-сайтом, то в этом случае в том же разделе окна создания гиперссылки в поле «Адрес» нужно просто указать адрес нужного веб-ресурса и нажать на кнопку «OK».

    Вставка ссылки на сайт в окне вставки гиперссылки в Microsoft Excel

    Если требуется указать гиперссылку на место в текущей книге, то следует перейти в раздел «Связать с местом в документе». Далее в центральной части окна нужно указать лист и адрес той ячейки, с которой следует произвести связь. Кликаем по «OK».

    Вставка ссылки на место в текущем документе в окне вставки гиперссылки в Microsoft Excel

    Если нужно создать новый документ Excel и привязать его с помощью гиперссылки к текущей книге, то следует перейти в раздел «Связать с новым документом». Далее в центральной области окна дать ему имя и указать его местоположение на диске. Затем кликнуть по «OK».

    Вставка ссылки на новый документ в окне вставки гиперссылки в Microsoft Excel

    При желании можно связать элемент листа гиперссылкой даже с электронной почтой. Для этого перемещаемся в раздел «Связать с электронной почтой» и в поле «Адрес» указываем e-mail. Клацаем по «OK».

  5. Вставка ссылки на электронную почту в окне вставки гиперссылки в Microsoft Excel
  6. После того, как гиперссылка была вставлена, текст в той ячейке, в которой она расположена, по умолчанию приобретает синий цвет. Это значит, что гиперссылка активна. Чтобы перейти к тому объекту, с которым она связана, достаточно выполнить двойной щелчок по ней левой кнопкой мыши.
Переход по гиперссылке в Microsoft Excel

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

Данный оператор имеет синтаксис:

=ГИПЕРССЫЛКА(адрес;имя)

«Адрес» — аргумент, указывающий адрес веб-сайта в интернете или файла на винчестере, с которым нужно установить связь.

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

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

Урок: Как сделать или удалить гиперссылки в Экселе

Мы выяснили, что в таблицах Excel существует две группы ссылок: применяющиеся в формулах и служащие для перехода (гиперссылки). Кроме того, эти две группы делятся на множество более мелких разновидностей. Именно от конкретной разновидности линка и зависит алгоритм процедуры создания.

Обсудить в TelegramНаш Telegram каналТолько полезная информация
Автор статьи Вы на сайте: Статья обновлена: . Автор: Максим Тютюшев

Вам помогли мои советы?

Получить ответ на Email
Уведомить о

25 ответов
По рейтингу
Новые Старые
Межтекстовые Отзывы
Посмотреть все комментарии
Иван
24 декабря 2018 14:54

Здравствуйте. Подскажите, как в Excel(1) можно сделать внешнюю ссылку на ячейку в другом Excel(2) файле, чтобы при добавлении строк над этой ячейкой в (2), ссылка также сдвигалась на добавленное количество строк вниз?

Аноним
27 мая 2020 12:20

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

Анна
1 апреля 2022 15:35

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

павел
14 декабря 2017 12:27

Добрый день! Подскажите, а как создать гиперссылку на несколько файлов? Т.е. мне нужно в ячейке создать 4 ссылки на 4 файла, чтобы на одну ссылку нажал, этот файл открылся, на другую другой.

Алена
27 декабря 2017 09:40

Здравствуйте! Спасибо за статью, очень полезно.
НО вот у меня не получается. Не могу разобраться в причине. Делаю согласно данной стать. Ссылка на сайт сохраняется, но когда на нее нажимаешь выходит ошибка в екселе «не удается открыть … Не удается загрузить нужные данные. Сайт этой ссылки рабочий, активный и открытый для всех.
Как можно решить проблему?

comment_image_reloaded_76885797
алена
28 декабря 2017 20:38
Ответить на  Максим Тютюшев

Спасибо за совет))) в настройках интернета видимо блок стоял. везде галочки наставила и все работает))

Денис
31 января 2018 14:26

Если у ячейки тип «текстовый», ссылка на ячейку будет работать?
У меня ссылка не работает, например A1=5 пишу в A3 «=A1», и не работает, значение остается «A1».
И A1 и A3 -> текстовый.

Денис
31 января 2018 15:05

В Дополнение:
Как сделать формулу: вывод содержимого ячеек с заданными адресами?
Просто например в A1 содержимое текста и строк из A3, A4, A10.
Спасибо.

Денис
31 января 2018 15:26
Ответить на  Денис

Нашёл такую функцию, пока работает:
=СЦЕПИТЬ(A1;» «;A3;» «;A4;» «;A10)

Денис
31 января 2018 15:28

Точнее, в ячейке A1:
=СЦЕПИТЬ(A3;» «;A4;» «;A10)

Денис
12 февраля 2018 16:51

Добрый день! Подскажите как можно сделать ссылки на папки с фото. Есть таблица с номерами в столбце В , в столбце С хотим сделать ссылки на диск D на котором находятся одноимённые папки. Объём таблицы большой прописывать каждый путь не вариант. Спасибо!

Аноним
14 февраля 2018 07:18

Всё верно папки разные и им присвоены номера, которые перечислены в столбце В. В общем смысл создания гиперссылок понятен, надеялся на автоматическую подстановку в адрес гиперссылки ячеек из столбца. В любом случае спасибо!

Аноним
15 марта 2018 11:00

Добрый день. Могу ли я сделать ссылку на конкретное слово или можно только на целую ячейку

Иван
18 марта 2018 10:15

Добрый день! Можно ли в ячейке с текстом сделать ссылку на ячейку с цифрой. Т.е. в ячейке идет текст, затем переменная цифра, затем текст. Или это можно сделать только с помощью макросов?

Аноним
2 февраля 2019 15:50

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

Аноним
19 октября 2023 16:28

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

Виктор Бухтеев
20 октября 2023 11:31
Ответить на  Аноним

Здравствуйте, Аноним. Дело в том, что книги и листы в Microsoft Excel как раз привязываются к названиям, поэтому их и нужно указывать в формулах, чтобы ссылаться. К сожалению, исправить эту ситуацию без каких-то сложных макросов и ручного программирование через встроенное средство вряд ли получится. Самый простой выход — не переименовывать листы, а каждый раз работать с новыми, скрывая при этом временно ненужные. Однако этот вариант может вам и не подойти.

Задать вопрос