Эксель ссылка на другой лист: Работа со ссылками в Excel

Добавление гиперссылок, закладок и почтовых ссылок в Excel

Автор Глеб Захаров На чтение 7 мин. Просмотров 2.7k. Опубликовано 08.07.2019

Задумывались ли вы, как добавить гиперссылки, закладки или почтовые ссылки в Excel? Ответы прямо здесь.

Следующие шаги применимы к Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 для Mac, Excel 2016 для Mac, Excel для Mac 2011 и Excel Online.

Содержание

1. Что такое гиперссылки, закладки и почтовые ссылки?
2. Откройте диалоговое окно Вставить гиперссылку
3. Как открыть диалоговое окно «Вставить гиперссылку» с помощью ленты
4. Добавить гиперссылку в Excel
5. Добавить гиперссылку на веб-страницу
6. Добавить гиперссылку в файл Excel
7. Создать закладку на тот же лист Excel
8. Как создать закладку на тот же лист
9. Создать закладку на другой лист
10. Вставьте ссылку Mailto в файл Excel
11. Удалить гиперссылку, не удаляя текст привязки

Во-первых, давайте уточним, что мы подразумеваем под каждым термином.

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

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

Ссылка mailto – это ссылка на адрес электронной почты. Выбор ссылки mailto открывает новое окно сообщения в программе электронной почты по умолчанию и вставляет адрес электронной почты в строку «Кому» сообщения.

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

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

Комбинация клавиш для открытия диалогового окна Вставить гиперссылку : Ctrl + K на ПК или команда + K на Mac.

1. На листе Excel выберите ячейку, которая будет содержать гиперссылку.
2. Введите слово, которое будет действовать как текст привязки, например «Таблицы» или «June_Sales.xlsx», и нажмите Enter .
3. Выберите ячейку с текстом привязки во второй раз.
4. Нажмите и удерживайте клавишу Ctrl (в Windows) или клавишу Команда ⌘ (в Mac).
5. Нажмите и отпустите клавишу K , чтобы открыть диалоговое окно «Вставить гиперссылку».

1. На листе Excel выберите ячейку, которая будет содержать гиперссылку.
2. Введите слово, которое будет действовать как текст привязки, например «Таблицы» или «June_Sales.xlsx», и нажмите Enter .
3. Выберите ячейку с текстом привязки во второй раз.
4. Выберите Вставить . (В Excel 2011 для Mac перейдите в меню Вставка .)
5. Выберите Гиперссылка или Ссылка > Вставить ссылку в группе ссылок. Откроется диалоговое окно «Вставить гиперссылку».

Вот как настроить гиперссылку для перехода на веб-страницу или в файл Excel.

1. Откройте диалоговое окно Вставить гиперссылку , используя один из методов, описанных выше.
2. Откройте вкладку Существующий файл или веб-страница .
3. В строке Адрес введите полный URL-адрес.
4. Выберите ОК , чтобы завершить гиперссылку и закрыть диалоговое окно.

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

Примечание. Этот параметр недоступен в Excel Online.

1. Откройте диалоговое окно Вставить гиперссылку .
2. Откройте вкладку Существующий файл или веб-страница .
3. Выберите Найти файл , чтобы открыть диалоговое окно «Ссылка на файл».
4. Найдите, чтобы найти имя файла Excel, выберите файл и выберите ОК . Имя файла добавляется в строку «Адрес» в диалоговом окне «Вставить гиперссылку».
5. Выберите ОК , чтобы завершить гиперссылку и закрыть диалоговое окно.

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

Создать закладку на тот же лист Excel

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

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

Как создать закладку на тот же лист

В следующем примере создается закладка в другом месте на том же листе Excel.

1. Введите имя в ячейке, которое будет использоваться в качестве текста привязки для закладки, и нажмите Enter .
2. Выберите эту ячейку, чтобы сделать ее активной.
3. Откройте диалоговое окно Вставить гиперссылку .
4. Откройте вкладку Разместить в этом документе (или нажмите кнопку Разместить в этом документе в Excel Online).
5. В текстовом поле Введите ссылку на ячейку введите ссылку на ячейку в другом месте на том же листе, например, «Z100».
6. Выберите ОК , чтобы завершить закладку и закрыть диалоговое окно.

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

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

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

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

1. Откройте многослойную книгу Excel или добавьте дополнительные листы в один файл листа.
2. На одном из листов введите имя в ячейку, которое будет использоваться в качестве текста привязки для закладки.
3. Выберите эту ячейку, чтобы сделать ее активной.
4. Откройте диалоговое окно
   Вставить гиперссылку .
5. Выберите вкладку Поместить в этот документ (или нажмите кнопку Поместить в этот документ в Excel Online).
6. Введите ссылку на ячейку в поле Введите ссылку на ячейку .
7. В поле Или выберите место в этом документе , выберите имя листа назначения. Безымянные листы идентифицируются как Sheet1, Sheet2, Sheet3 и так далее.
8. Выберите ОК , чтобы завершить закладку и закрыть диалоговое окно.

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

Выберите закладку, и курсор активной ячейки переместится на ссылку на ячейку на листе, введенном для закладки.

Вставьте ссылку Mailto в файл Excel

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

1. Введите имя в ячейку, которое будет служить якорным текстом для ссылки mailto, и нажмите Enter .
2. Выберите эту ячейку, чтобы сделать ее активной.
3. Откройте диалоговое окно Вставить гиперссылку .
4. Выберите вкладку Адрес электронной почты (или нажмите кнопку Адрес электронной почты в Excel Online).
5. В поле Адрес электронной почты введите адрес электронной почты человека, который получит письмо. Этот адрес вводится в строку Кому нового сообщения электронной почты при выборе ссылки.
6. Под строкой Тема введите тему сообщения. Этот текст вводится в строку темы в новом сообщении. Эта опция недоступна в Excel Online.
7. Выберите ОК , чтобы завершить ссылку на почту и закрыть диалоговое окно.

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

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

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

1. Наведите указатель мыши на гиперссылку, которую нужно удалить. Стрелка указателя должна измениться на символ руки.
2. Щелкните правой кнопкой мыши текст привязки гиперссылки, чтобы открыть контекстное меню.
3. Выберите Удалить гиперссылку .

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

Трюк №16. Проверка данных на основе списка на другом листе Excel — MS Excel

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

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

Способ 1. Именованные диапазоны

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

Выделите ячейку, в которой должен будет появиться раскрывающийся список, а затем выберите команду Данные → Проверка (Data → Validation). В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) введите =MyRange. Щелкните на кнопке ОК. Поскольку вы использовали именованный диапазон, ваш список (хотя он и находится на другом листе) теперь можно использовать как список проверки.

Способ 2. Функция ДВССЫЛ

Функция ДВССЫЛ (INDIRECT) позволяет ссылаться на ячейку, содержащую текст, представляющий адрес ячейки. Эту ячейку можно использовать как локальную ссылку, даже если она получает данные из другого листа. Можно применять эту возможность для связи с листом, где расположен список.

Предположим, список находится на листе Sheetl в диапазоне $А$1:$А$8. Щелкните любую ячейку на другом листе, где должен появиться этот список проверки (список выборки). Затем выберите команду Данные → Проверка (Data → Validation) и в поле Тип данных (Allow) выберите пункт Список (List). В поле Источник (Source) введите следующий код: =INDIRECT("Sheetl!$А$1:$А$8"), в русской версии Excel =ДВССЫЛ("Sheetl!$A$1:$A$8"). Удостоверьтесь, что флажок Список допустимых значений (In-Cell) установлен, и щелкните на кнопке ОК. Список на листе Sheetl должен появиться в раскрывающемся списке проверки.

Если имя листа, на котором расположен список, содержит пробелы, необходимо использовать следующий синтаксис функции ДВССЫЛ (INDIRECT): =INDIRECT("'Sheetl'!$А$1:$А$8"), в русской версии Excel =ДВССЫЛ("'Sheetl'!$А$1:$А$8"). Различие заключается в том, что здесь после первой кавычки стоит один апостроф, а второй апостроф находится перед восклицательным знаком.
[stextbox id=»warning»]Полезно всегда использовать одиночные апострофы, независимо от того, содержит имя пробелы или нет. С апострофами вы все так же сможете ссылаться на листы с именами без пробелов, и это также упростит внесение изменений позже.[/stextbox]

Преимущества и недостатки обоих способов

У именованных диапазонов и функции ДВССЫЛ (INDIRECT) есть преимущества и недостатки. Преимущество использования именованного диапазона заключается в том, что изменение названия листа не повлияет на список проверки. Это подчеркивает недостаток функции ДВССЫЛ (INDIRECT): любое изменение названия листа не будет автоматически в ней отражаться. Преимущество функции ДВССЫЛ (INDIRECT): когда из именованного диапазона будет удалена первая ячейка или строка либо последняя ячейка или строка, то именованный диапазон вернет ошибку #REF!. В этом недостаток именованного диапазона — если удалить из него ячейки или строки, изменения не повлияют на список проверки.

Примеры Excel Функция ГИПЕРССЫЛКА для создания динамических гиперссылок

Функция ГИПЕРССЫЛКА в Excel возвращает ярлык или гиперссылку на конкретный объект, которым может быть веб-страница, файл, сохраненный в постоянной памяти ПК, группа ячеек на листе в Рабочая тетрадь Эксель.



Функция ГИПЕРССЫЛКА и особенности ее аргументов

Функция ГИПЕРССЫЛКА упрощает доступ к объектам, которые являются как частями Excel (ячейками, листами книги), так и частями других программных продуктов (блокнот, файл Word) или страницами на Интернет. Эта функция имеет следующую запись синтаксиса:

=ГИПЕРССЫЛКА(Расположение_ссылки,[Дружественное_имя])

Описание 2-х параметров аргументов функции:

• Местоположение_ссылки — текстовое значение, соответствующее имени открываемого объекта и полному пути доступа к нему. Этот параметр является обязательным. Адрес может указывать на конкретную часть документа, например, ячейку или диапазон ячеек, закладку в текстовом редакторе Word. Путь может содержать данные о пути к файлу в файловой системе ПК (например, «C:\Users\soulp\Documents») или URL-адрес страницы в Интернете.
• [Friendly_name] — это текстовое значение, которое будет отображаться как текст гиперссылки. Отображается синим цветом с подчеркнутым текстом.

Примечания:

1. Параметры address и [Friendly_name] принимают значения в виде текстовой строки в кавычках («адрес», «имя») или ссылок на ячейки, содержащие адрес и имя объекта соответственно.
2. В Excel Online (онлайн-версия программы Excel для работы через веб-интерфейс) функцию ГИПЕРССЫЛКА можно использовать только для создания гиперссылок на веб-объекты, поскольку браузерные приложения не имеют доступа к файловым системам устройства.
3. Если в ячейке, на которую ссылается параметр [имя], было установлено значение кода ошибки #ЗНАЧ!, в тексте созданной гиперссылки также будет отображаться «#ЗНАЧ!».
4. Чтобы выбрать ячейку, содержащую гиперссылку, без навигации по ней, необходимо навести указатель мыши на нужную ячейку, нажать и не отпускать левую кнопку мыши, пока курсор не изменит форму на «+». Затем можно отпустить кнопку мыши, нужная ячейка будет выделена.
5. В онлайн-версии Excel, чтобы выделить ячейку, содержащую гиперссылку, необходимо переместить курсор так, чтобы он выглядел как обычная стрелка. Для перехода по гиперссылке необходимо навести курсор непосредственно на ее текст. В этом случае курсор будет выглядеть как рука.
6. Для создания гиперссылки на интернет-ресурс можно использовать следующую запись: =ГИПЕРССЫЛКА(«http://www.bing.com/»,»Поисковая система BING»).
7. Гиперссылку на документ, хранящийся в файловой системе ПК, можно создать следующим образом: =ГИПЕРССЫЛКА(«C:\Users\soulp\Downloads\document_2″,»Ссылка на документ_2»). При переходе по этой ссылке появляется диалоговое окно с предупреждением о возможном наличии вредоносного ПО в файле. Чтобы открыть файл, необходимо нажать кнопку «ОК» в этом диалоговом окне. Если по указанному пути нет файла, появится соответствующее уведомление.
8. Чтобы создать ссылку на другой лист в книге Excel, вы можете использовать аналогичную запись: =HYPERLINK(«[Book1.xlsx]Лист2!A1″,»Лист2»). При нажатии на эту ссылку откроется Лист2, а фокус будет установлен на ячейку А1.
9. Гиперссылку можно вставить с помощью визуального интерфейса пользователя (соответствующий пункт контекстного меню, кнопка на панели задач).



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

Как создать ссылку на файл в Excel? Пример 1. Бухгалтер предприятия выполняет различные расчеты и хранит таблицы данных в Excel в одной книге (Accounting.xlsx), содержащей несколько листов. Для удобства было принято решение создать отдельный лист с оглавлением в виде гиперссылок на каждый из имеющихся листов.

На новом листе создадим следующую таблицу:

Для создания гиперссылки используем формулу:

Описание аргументов функции:

• «[Example_1.xlsx] Profit! A1» — полный адрес ячейки А1 листа «Прибыль» книги «Пример_1.xlsx»;
• «Прибыль» — текст, который будет отображать ссылку.

Аналогичным образом создайте гиперссылки для других страниц. В результате получаем:

Динамическая ГИПЕРССЫЛКА на Excel

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

Исходная таблица:

Для создания ссылки на ресурс https://www.xe.com/currencyconverter/ в ячейку D7 введите следующую формулу:

Описание параметров:

• https://www .xe.com/currencyconverter/ — URL-адрес нужного сайта;
• «Курсы валют» — текст, отображаемый в ссылке.

В результате получаем:

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

Отправка электронной почты через Excel ГИПЕРССЫЛКА

Пример 3. Сотрудник предприятия испытывает трудности с использованием функции ЕСЛИ в Excel. Для решения проблемы в одном из документов есть готовая форма для отправки письма. Отправка письма происходит по клику на гиперссылку. Рассмотрим, как устроена эта форма отправки писем.

Форма имеет следующий вид:

Значения ячеек B3 и B4 могут быть изменены по усмотрению пользователя (в зависимости от причины обращения в службу поддержки). Ячейка B5 содержит функцию:

CONCATENATE – эта функция выполняет конкатенацию (конкатенацию текстовых строк, принимаемых в качестве параметров).

Описание параметров:

• «mailto:» — команда для отправки писем;
• B2 — ячейка со службами поддержки по электронной почте;
• «?subject=» — команда для написания темы письма;
• ENCODEURL(B3) — функция, преобразующая текст письма с учетом кодировки URL;
• «&body=» — команда для написания текста письма;
• ENCODEURL(B4) — текст письма в кодировке URL.

Ячейка B6 выполняет следующие функции:

Описание параметров:

• B5 — URL-команда для отправки письма, содержащая тему и текст письма;
• «Отправить письмо» — название гиперссылки.

Скачать примеры Функция ГИПЕРССЫЛКА для создания в Excel

При нажатии на ссылку откроется почтовый клиент по умолчанию, например, Outlook (но в данном случае стандартный клиент Windows).

Внешняя ссылка в Excel // Как создать и как найти // PerfectXL

Содержание

1. Ссылка на ячейку в другой книге
2. Ссылка на диапазон в другой книге
3. Ссылка на именованный диапазон в другая книга
4. Как найти внешние ссылки в Excel

Как создать ссылку на ячейку в другой книге в Excel

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

Пример

В этом примере у нас есть рабочая книга, которая содержит доход от фруктов в месяц (отдельная вкладка для каждого месяца): «fruit-rev-21», и еще одна рабочая книга, в которой мы хотим суммировать доход на весь год: «tot-rev-21». Мы хотели бы заполнить последний данными из первого.

Мы начнем в нашей рабочей книге назначения с общего дохода от фруктов в январе, выбрав ячейку «B2» и введя «=»

Затем мы сосредоточимся на исходной книге («фрукты -2021″), и мы выберем ячейку «D8», содержащую общий доход на листе, содержащем данные за январь:

Теперь все, что вам нужно сделать, это нажать ENTER, чтобы создать ссылку и отобразить значение в целевом файле («tot-rev-2021»).

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

Чтобы создать ссылку на диапазон ячеек в другой книге, мы выполняем почти те же шаги, но на этот раз мы выберем ячейку назначения «B3» ( доход от фруктов за февраль) и введите: ‘=SUM(‘

Как и раньше, мы вернемся к исходной книге, но на этот раз выберем вкладку «Февраль» и диапазон ячеек «D2:D7»

Еще раз мы нажмем ENTER, и в ячейке «B3» в нашей целевой книге будет отображаться результат суммы диапазона ячеек из другой электронной таблицы («fruit-rev-2021»):

Внешняя ссылка на именованный диапазон

Метод создания внешней ссылки на объект с определенным именем аналогичен описанным выше методам. Все зависит от характера вашего именованного объекта.

Мы продолжим заполнение листа «tot-rev-2021». В нашей исходной книге («fruit-rev-2021») у нас есть два именованных диапазона. Первая — это ячейка, содержащая общий доход от фруктов в марте («tot_rev_fruit_mar_21»), а вторая — диапазон, содержащий все итоги по фруктам за апрель («tot_rev_per_fruit_apr_21»).

Пример 1: ссылка на ячейку с определенным именем в другой книге

В нашей рабочей книге («tot-rev-2021») мы выбираем ячейку «B4» и вводим: «=»

Затем мы возвращаемся к нашей исходной книге («fruit-rev-2021») и нажимаем «F3», чтобы вызвать список доступных именованных диапазонов.

После выбора соответствующего именованного диапазона во всплывающем окне «Вставить имя» нажмите «ОК», и определенное имя появится в вашей целевой книге. Нажмите ENTER, чтобы опубликовать его значение.

Пример 2. Ссылка на именованный диапазон в другой рабочей книге

В целевой рабочей книге («tot-rev-2021») мы выбираем ячейку «B5» для дохода за апрель и вводим: «=СУММ(‘

Затем мы переключаемся на нашу исходную книгу («fruit-rev-2021») и снова нажимаем «F3», чтобы открыть всплывающее окно со списком определенных имен в этой электронной таблице. выберите лист, содержащий именованный диапазон. Вы можете выбрать его из диалогового окна в любом месте электронной таблицы!

Теперь выберите соответствующее имя и нажмите «ОК», чтобы именованный диапазон появился в вашей целевой книге. Нажмите ENTER, чтобы опубликовать значение.

Обратите внимание на характер связанного имени

Если вы ссылаетесь на именованный диапазон (или диапазон ячеек, если на то пошло) и не включаете его в формулу (вы вводите только ‘=’ ), ваша целевая книга опубликует весь диапазон. См. пример ниже.

Как найти внешние ссылки в Excel

Теперь изменим перспективу. Допустим, вы работаете с нашим листом назначения («tot-rev-2021»), но создали его некоторое время назад или даже не создавали сами и хотите знать, содержит ли он какие-либо внешние ссылки.

Если вы перейдете в Данные > Редактировать ссылки… вы получите обзор исходных файлов, на которые ссылается ваша рабочая книга. В нашем случае это всего один файл: ‘fruit-rev-2021.xlsx’

Итак, теперь мы знаем, какие источники используются в нашем файле, но все же мы не знаем, ГДЕ в нашем файле ссылка или ссылки к этим источникам относятся. К сожалению, сам Excel не может ответить вам на этот вопрос.