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

Ссылка на ячейку другого документа

Ссылка на ячейку другого документа Contents

Index 🔎︎

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

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

Создание ссылки на ячейку в том же документе

1. Open a new, empty spreadsheet. By default, it has only a single sheet named Sheet1. Add a second sheet clicking on + button to the left of the sheet tab in the bottom (it will be named Sheet2 by default).

2. Для примера введите в ячейку А1 листа1 следующую формулу:

   =Лист2. A1

3. Щёлкните в нижней части окна вкладку Лист 2. Установите курсор в ячейке A1 открывшегося листа и введите текст или число.

4. В ячейке А1 листа1 появится то же содержимое. Если изменить содержимое ячейки «Лист2.A1», то содержимое ячейки «Лист1.A1» также изменится.

When referencing a sheet with name containing spaces, use single quotes around the name: =’Sheet with spaces in name’.A1

The example uses Calc formula syntax. It is also possible to use Excel A1 or R1C1 formula syntax; this is configured on Formula options page.

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

org/HowToSection» dir=»auto»>

1. Выберите Файл — Открыть для загрузки существующего документа электронных таблиц.

2. Для открытия нового документа с электронной таблицей выберите Файл — Открыть. Установите курсор в ячейке, в которую требуется вставить внешние данные, и введите знак равенства, означающий начало ввода формулы.

3. Перейдите к загруженному документу. Щёлкните по ячейке, содержащей данные, которые требуется вставить в новый документ.

4. Вернитесь к новой электронной таблице. В строке ввода теперь видно, каким образом с помощью LibreOffice Calc была добавлена ссылка на формулу.

   Ссылка на ячейку другого документа содержит имя этого документа в одинарных кавычках, затем символ «решетка» (#), имя листа другого документа, после которого стоит точка и имя ячейки.

5. Подтвердите формулу, щёлкнув значок с зелёной галочкой.

6. If you drag the box in the lower right corner of the active cell to select a range of cells, LibreOffice automatically inserts the corresponding references in the adjacent cells. As a result, the sheet name is preceded with a «$» sign to designate it as an absolute reference.

Обратите внимание, что имя другого документа в этой формуле имеет вид URL. Это означает, что аналогично можно ввести URL-адрес из Интернет.

Please support us!

Ссылка на веб‑страницу, адрес электронной почты, номер телефона или лист в Numbers на Mac

Можно превратить текст или определенные объекты (фигуры, линии, изображения, рисунки, текстовые блоки, уравнения, группу объектов и анимированные рисунки) в ссылку, которая будет выполнять одно из следующих действий:

• переходить к другому листу;

• открывать веб-сайт или электронное письмо;

• звонить по номеру телефона.

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

Добавление ссылки

1. Удерживая клавишу Control, нажмите объект, текстовый блок или выбранный текст, который Вы хотите преобразовать в ссылку, выберите «Добавить ссылку», затем выберите место назначения («Веб-страница», «E-mail», «Номер телефона», «Страница» или «Лист»).

2. Укажите объект, на который ведет ссылка.

   • Веб‑страница. Открывает веб-страницу в браузере. В поле «Показывать» введите текст, который будут видеть читатели. Например, можно отображать доменное имя, а не весь веб-адрес. В поле «Ссылка» введите URL-адрес веб-страницы.

   • E‑mail. Открывает новое электронное письмо с адресом получателя в поле «Кому». В поле «Показывать» введите текст, который будут видеть читатели. Например, можно отображать только имя получателя, а не весь адрес электронной почты. Укажите тему в поле «Тема» или оставьте это поле пустым.

   • Номер телефона. Вызывает номер телефона. В поле «Номер» введите номер телефона. В поле «Показывать» введите текст, который будут видеть читатели. Например, можно показать название компании или имя человека, которым принадлежит номер.

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

3. Чтобы проверить место назначения, нажмите кнопку «Открыть ссылку», «Составить e‑mail», «Позвонить» или «Перейти к листу». Либо нажмите лист, чтобы закрыть редактор ссылок.

Текст ссылки подчеркивается, а рядом со связанным объектом появляется кнопка ссылки (в виде кривой стрелки).

Редактирование или удаление ссылки

1. Нажмите текст ссылки или кнопку ссылки связанного объекта либо дважды нажмите ссылку в ячейке таблицы.

2. В редакторе ссылок внесите изменения или нажмите «Удалить».

   При удалении ссылки ее текст остается, но удаляется форматирование, а ссылка перестает быть активной.

3. По завершении нажмите лист, чтобы закрыть редактор ссылок.

Включение или выключение автоматического форматирования веб-адресов, адресов электронной почты и номеров телефонов

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

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

1. Выберите меню Numbers > «Настройки» (меню Numbers расположено у верхнего края экрана).

2. Нажмите «Автокоррекция» вверху окна «Настройки».

3. В разделе «Форматирование» выполните любое из указанных ниже действий.

   • Адреса веб-сайтов и адреса электронной почты. Установите или снимите флажок «Автоматически добавлять ссылки на веб‑страницы и адреса e‑mail».

   • Номера телефонов. Установите или снимите флажок «Автоматически добавлять ссылки на номера телефонов».

Чтобы ссылки оставались активными, но не отображалось их форматирование, выберите «Автоматически добавлять ссылки на веб‑страницы и адреса e‑mail» или «Автоматически добавлять ссылки на номера телефонов» и снимите флажок «Автоматически применять стиль ссылки».

Эта настройка применяется к новому тексту, введенному во всех электронных таблицах Numbers. Существующие ссылки не затрагиваются этим изменением.

См. такжеКопирование и вставка текста в Numbers на MacВыбор текста и размещение точки вставки в Numbers на MacДобавление текста в Numbers на Mac

Функция Excel HYPERLINK для быстрого создания и редактирования нескольких ссылок

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

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

Гиперссылка контекстное меню или сочетание клавиш Ctrl + K. Если вы планируете вставить много одинаковых или похожих ссылок, самый быстрый способ — использовать формулу гиперссылки, которая упрощает создание, копирование и редактирование гиперссылок в Excel.

• Функция Excel HYPERLINK — синтаксис и основное использование
• Как использовать ГИПЕРССЫЛКУ в Excel — примеры формул
  • Создание гиперссылок на разные элементы
  • Vlookup и вставить гиперссылку на первое совпадение
• Как редактировать несколько формул гиперссылок одновременно
• ГИПЕРССЫЛКА Excel не работает — причины и решения

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

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

Эта функция доступна во всех версиях Excel 365–2000.

В Excel Online функцию ГИПЕРССЫЛКИ можно использовать только для веб-адресов (URL-адресов).

Синтаксис функции ГИПЕРССЫЛКА следующий:

HYPERLINK(link_location, [friendly_name])

Где:

• Link_location (обязательно) — это путь к открываемой веб-странице или файлу.

  Link_location может быть предоставлен как ссылка на ячейку , содержащую ссылку, или текстовая строка , заключенная в кавычки, которая содержит путь к файлу, хранящемуся на локальном диске, путь UNC на сервере или URL-адрес на Интернет или интранет.

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

• Friendly_name (необязательно) — это текст ссылки (также известный как текст перехода или текст привязки), который будет отображаться в ячейке. Если опущено, link_location отображается как текст ссылки.

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

Щелчок по ячейке с формулой гиперссылки открывает файл или веб-страницу, указанную в аргументе link_location .

Ниже вы можете увидеть простейший пример формулы гиперссылки Excel, где A2 содержит friendly_name , а B2 содержит link_location :

=HYPERLINK(B2, A2)

Результат может выглядеть примерно так :

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

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

Как создавать ссылки на листы, файлы, веб-страницы и другие элементы

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

Гиперссылка на другой рабочий лист

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

=ГИПЕРССЫЛКА("#Лист2!A1", "Лист2")

Приведенная выше формула создает гиперссылку с текстом перехода «Лист2», которая открывает Лист2 в текущей книге.

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

=HYPERLINK("#'Прайс-лист'!A1", "Прайс-лист" )

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

=ГИПЕРССЫЛКА("#A1", "Перейти к ячейке A1")

Гиперссылка на другую книгу

Чтобы создать гиперссылку на другую книгу, необходимо указать полный путь к целевой книге в в следующем формате:

«Диск:\Папка\Workbook. xlsx»

Например:

=HYPERLINK("D:\Source data\Book3.xlsx", "Book3")

На посадку на конкретном листе и даже в конкретной ячейке используйте такой формат:

«[Drive:\Folder\Workbook.xlsx]Sheet!Cell»

Например, чтобы добавить гиперссылку с названием «Книга3», которая открывает Лист2 в Книге3, хранящейся в папке Исходные данные на диске D, используйте эта формула:

=ГИПЕРССЫЛКА("[D:\Source data\Book3.xlsx]Лист2!A1", "Книга3")

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

=ГИПЕРССЫЛКА("Исходные данные\Book3.xlsx", "Book3")

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

Гиперссылка на именованный диапазон

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

«[Drive:\Folder\Workbook. xlsx]Sheet!Name »

Например, чтобы вставить ссылку на диапазон с именем «Source_data», хранящийся на Листе 1 в Книге 1, используйте следующую формулу:

=ГИПЕРССЫЛКА("[D:\Excel files\Book1.xlsx]Sheet1!Source_data","Исходные данные")

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

=ГИПЕРССЫЛКА("[D:\Excel files\Book1.xlsx]Source_data","Source data")

Гиперссылка для открытия файла, хранящегося на жестком диске

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

«Диск:\Папка\Имя_файла.расширение»

Например, чтобы открыть документ Word с именем Прайс-лист , который хранится в папке Word files на диске D, используйте следующую формулу:

=HYPERLINK("D:\Word files\Price list.docx","Price list")

Гиперссылка на закладку в документе Word

Чтобы сделать гиперссылку на определенное место в документе Word, вложите документ путь в [квадратные скобки] и используйте закладка , чтобы определить местоположение, к которому вы хотите перейти.

Например, следующая формула добавляет гиперссылку на закладку с именем Subscription_prices в Price list.docx:

=HYPERLINK("[D:\Word files\Price list.docx]Subscription_prices","Price list" )

Гиперссылка на файл на сетевом диске

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

«\\Server_name\Folder\File_name.extension»

Приведенная ниже формула создает гиперссылку под названием «Прайс-лист», которая открывает рабочую книгу Price list.xlsx , хранящуюся на SERVER1 в папке Svetlana :

5

5 =ГИПЕРССЫЛКА("\\СЕРВЕР1\Светлана\Прайс-лист.xlsx", "Прайс-лист")

Чтобы открыть файл Excel на конкретном рабочем листе , заключите путь к файлу в [квадратные скобки] и включите имя листа, за которым следует восклицательный знак (!) и ссылочная ячейка:

=ГИПЕРССЫЛКА("[\\СЕРВЕР1\Светлана\Прайс-лист. xlsx]Лист4!А1", "Прайс-лист")

Гиперссылка на веб-страницу

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

=ГИПЕРССЫЛКА("https://www.ablebits.com","Перейти к Ablebits.com")

Приведенная выше формула вставляет гиперссылку , под названием «Перейти на Ablebits.com», который открывает домашнюю страницу нашего веб-сайта.

Гиперссылка для отправки электронного письма

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

«mailto:email_address»

Например:

=HYPERLINK("mailto:[email protected]","Drop нам письмо»)

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

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

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

Но что, если вы хотите не только получить совпадающее значение, но и перейти к позиции этого значения в исходном наборе данных, чтобы просмотреть другие детали в той же строке? Это можно сделать с помощью функции ГИПЕРССЫЛКА Excel с некоторой помощью ЯЧЕЙКИ, ИНДЕКС и ПОИСКПОЗ.

Общая формула для создания гиперссылки на первое совпадение выглядит следующим образом:

ГИПЕРССЫЛКА(«#»&ЯЧЕЙКА(«адрес», ИНДЕКС( return_range , MATCH( lookup_value , lookup_range ,0))), ИНДЕКС( диапазон_возврата , ПОИСКПОЗ( искомое_значение, искомый_диапазон ,0)))

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

Со значением поиска в ячейке E2, списком поставщиков (диапазон поиска) в A2:A10 и списком продуктов (диапазон возврата) в C2:C10 формула принимает следующий вид:

=ГИПЕРССЫЛКА("#"&ЯЧЕЙКА("адрес", ИНДЕКС($C$2:$C$10, ПОИСКПОЗ($E2,$A$2:$A$10,0))), ИНДЕКС($C$2:$ C$10, MATCH($E2,$A$2:$A$10,0)))

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

Если вы работаете с длинными строками данных, может быть удобнее, чтобы гиперссылка указывала на первую ячейку в строке, где найдено совпадение. Для этого вы просто устанавливаете диапазон возврата в первой комбинации INDEX MATCH для столбца A (в этом примере $A$2:$A$10):

=ГИПЕРССЫЛКА("#"&ЯЧЕЙКА("адрес", ИНДЕКС($A$2:$A$10, ПОИСКПОЗ($E2,$A$2:$A$10,0))), ИНДЕКС($C$2:$ C$10, MATCH($E2,$A$2:$A$10,0)))

Эта формула приведет вас к первому вхождению искомого значения («Адам») в наборе данных:

Как это формула работает

Те из вас, кто знаком с формулой ИНДЕКС ПОИСКПОЗ как более универсальной альтернативой ВПР Excel, вероятно, уже поняли общую логику.

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

INDEX( return_range , MATCH( lookup_value , lookup_range , 0))

Вы можете найти полную информацию о том, как работает эта формула, перейдя по ссылке выше. Ниже мы опишем ключевые моменты:

• Функция ПОИСКПОЗ определяет положение « Адам » (значение поиска) в диапазоне A2:A10 (диапазон поиска) и возвращает 3.
• Результат ПОИСКПОЗ передается аргументу row_num функции ИНДЕКС, предписывая вернуть значение из 3
  rd ряд в диапазоне C2:C10 (диапазон возврата). А функция ИНДЕКС возвращает « лимона ».

Таким образом, вы получите аргумент friendly_name вашей формулы гиперссылки.

Теперь давайте разработаем link_location , то есть ячейку, на которую должна указывать гиперссылка. Чтобы получить адрес ячейки, вы используете функцию CELL(«address», [reference]) с INDEX MATCH как ссылка . Чтобы функция HYPERLINK знала, что целевая ячейка находится на текущем листе, соедините адрес ячейки с символом решетки («#»).

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

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

нескольких формул гиперссылок за один раз с помощью функции Excel Заменить все .

Допустим, вы хотите заменить старый URL-адрес вашей компании (old-website.com) новым (new-website.com) во всех гиперссылках на текущем листе или во всей книге. Чтобы это сделать, выполните шаги, описанные ниже:

1. Нажмите Ctrl + H, чтобы открыть вкладку Заменить диалогового окна Найти и заменить .
2. В правой части диалогового окна нажмите кнопку Параметры .
3. В поле Find what введите текст, который вы хотите изменить (в данном примере — old-website.com).
4. В раскрывающемся списке В пределах выберите Лист или Рабочая книга в зависимости от того, хотите ли вы изменить гиперссылки только на текущем листе или на всех листах текущей книги.
5. В раскрывающемся списке Искать в выберите Формулы .
6. В качестве дополнительной меры предосторожности сначала нажмите кнопку Найти все , и Excel отобразит список всех формул, содержащих искомый текст:
   
7. Просмотрите результаты поиска, чтобы убедиться, что вы хотите изменить все найденные формулы. Если вы это сделаете, перейдите к следующему шагу, в противном случае уточните поиск.
8. В поле Заменить на введите новый текст (в данном примере — «new-website.com»).
9. Нажмите кнопку Заменить все . Excel заменит указанный текст во всех найденных гиперссылках и сообщит, сколько изменений было внесено.
   
10. Нажмите кнопку Close , чтобы закрыть диалоговое окно. Сделанный!

Аналогичным образом вы можете одновременно редактировать текст ссылки (friendly_name) во всех формулах гиперссылки. При этом убедитесь, что текст, который нужно заменить в friendly_name , не появляется нигде в link_location чтобы не нарушать формулы.

Наиболее распространенная причина, по которой формула гиперссылки не работает (и первое, что вы должны проверить!) — это несуществующий или неверный путь в аргументе link_location . Если это не так, проверьте следующие две вещи:

1. Если место назначения ссылки не открывается при нажатии на гиперссылку, убедитесь, что расположение ссылки указано в правильном формате. Примеры формул для создания различных типов гиперссылок можно найти здесь.
2. Если вместо текста ссылки появится ошибка типа ЗНАЧ! или N/A появляется в ячейке, скорее всего, проблема связана с аргументом friendly_name вашей формулы гиперссылки.

   Обычно такие ошибки возникают, когда friendly_name возвращается какой-либо другой функцией (функциями), например, в нашем ВПР и гиперссылкой на первый пример совпадения. В этом случае в ячейке формулы появится ошибка #Н/Д, если искомое значение не будет найдено в таблице поиска. Чтобы предотвратить такие ошибки, вы можете рассмотреть возможность использования функции ЕСЛИОШИБКА для отображения пустой строки или некоторого удобного для пользователя текста вместо значения ошибки.

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

Практическая рабочая тетрадь для скачивания

Примеры формул гиперссылок Excel (файл .xlsx)

Вас также может заинтересовать

Как сделать ссылку на другой лист или книгу в Excel (с примерами)

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

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

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

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

Но волнуйтесь… ничего особенного!

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

Начнем!

Это руководство охватывает:

Ссылка на ячейку на том же листе

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

Например, если я нахожусь в ячейке B1 и хочу сослаться на ячейку A1, формат будет таким:

 =A1 

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

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

Если вам нужно сослаться на другой лист в той же книге, вам необходимо использовать следующий формат:

 Имя_листа!Адрес_ячейки 

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

Поэтому, если вам нужно сослаться на ячейку A1 на листе 1, вам нужно использовать следующую ссылку:

 =Лист1!A1 

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

 Имя_листа! Первая_ячейка: Последняя_ячейка 

Итак, если вы хотите сослаться на диапазон A1: C10 на другом листе в той же книге, вам нужно использовать ссылку ниже:

 = Лист1! A1: C10 

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

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

Если у вас есть пробелы или неалфавитные символы (например, @, !, #, — и т. д.), вам нужно использовать имя в одинарных кавычках.

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

 = «Данные о продажах»! A1 

И в случае, если имя листа — Данные о продажах, затем, чтобы сослаться на ячейку A1 на этом листе, вам нужно использовать следующую ссылку:

 ='Sales-Data'!A1 

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

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

Позвольте мне показать вам лучший способ создания ссылок на ячейки в Excel.

 Также читайте: Как суммировать несколько листов в Excel? 

Автоматическое создание ссылки на другой лист в той же книге

Гораздо лучший способ создать ссылку на ячейку на другом листе — просто указать Excel ячейку/диапазон, на который вы хотите создать ссылку, и позволить Excel создать ее самостоятельно.

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

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

1. Выберите ячейку в текущей книге, где вам нужна ссылка
2. Введите формулу, пока вам не понадобится ссылка (или знак равенства, если вы просто нужна ссылка)
3. Выберите лист, на который вам нужно сослаться
4. Выберите ячейку/диапазон, на который вы хотите сослаться
5. Нажмите Enter, чтобы получить результат формулы (или продолжить работу над формулой)

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

Например, если у вас есть данные в ячейке A1:A10 на листе с именем Данные о продажах, и вы хотите получить сумму этих значений на текущем листе, выполните следующие действия:

1. Введите следующую формулу на текущем листе (где нужен результат): =Sum(
2. Выберите лист «Данные о продажах».
3. Выберите диапазон, который вы хотите добавить (A1:A10). Как только вы это сделаете, Excel автоматически создаст ссылку на этот диапазон (вы можете увидеть это в строке формул)
4. Нажмите клавишу ввода.

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

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

Еще одна вещь, которую вам нужно знать при создании ссылок путем выбора листа, а затем ячейки/диапазона, заключается в том, что Excel всегда будет создавать относительную ссылку (т. е. ссылки со знаком n0 $). Это означает, что если я скопирую и вставлю формулу (одну со ссылкой на другой лист) в какую-то другую ячейку, она автоматически скорректирует ссылку.

Вот пример, поясняющий относительные ссылки.

Предположим, я использую следующую формулу в ячейке A1 на текущем листе (для ссылки на ячейку A1 в имени листа SalesData)

 =SalesData!A1 

Теперь, если я скопирую эту формулу и вставлю в ячейку A2, формула изменится на:

 =SalesData!A1 

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

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

 =SalesData!$A$1 

Знак доллара перед номером строки и столбца блокирует эти ссылки чтобы эти не менялись.

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

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

Как создать ссылку на другую книгу в Excel

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

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

Итак, давайте рассмотрим различные форматы внешних ссылок на другую книгу в различных сценариях.

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

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

Ниже приведен формат, который необходимо использовать при ссылке на внешнюю открытую книгу

 ='[FileName]SheetName!CellAddress 

Предположим, у вас есть книга «ExampleFile. xlsx» и вы хотите сослаться на ячейку A1 в Sheet1 из эта рабочая тетрадь.

Ниже приведена ссылка для этого:

 =[ExampleFile.xlsx]SalesData!A1 

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

Ниже приведены примеры, в которых имена должны быть заключены в одинарные кавычки:

 = '[Example File.xlsx]SalesData'!A1
='[ExampleFile.xlsx]Данные о продажах'!A1
='[Пример файла.xlsx]Данные о продажах'!A1 

Как создать ссылку на другую рабочую книгу (автоматически)

Опять же, хотя хорошо знать формат, лучше не вводить его вручную.

Вместо этого просто направьте Excel в правильном направлении, и он создаст эти ссылки для вас. Это намного быстрее с гораздо меньшими шансами на ошибку.

Например, если у вас есть некоторые данные в ячейке A1: A10 в книге с именем «Файл примера» на листе с именем «Данные о продажах», и вы хотите получить сумму этих значений на текущем листе, следующее будет шаги:

1. Введите следующую формулу на текущем листе (где вам нужен результат): =Sum(
2. Перейдите в книгу «Пример файла»
3. Выберите лист «Данные о продажах».
4. Выберите нужный диапазон чтобы добавить (A1:A10). Как только вы это сделаете, Excel автоматически создаст ссылку на этот диапазон (вы можете видеть это в строке формул)
5. Нажмите клавишу ввода.

Это мгновенно создаст формулу с правильными ссылками.

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

Если вы хотите изменить это, вам нужно изменить ссылки вручную.

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

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

Но когда это закрыто, Excel не имеет ни малейшего понятия, где вы ищете ячейки/диапазон, на который вы ссылались.

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

Ниже приведена ссылка на ячейку A1 на листе Sheet1 в книге Example File. Поскольку этот файл не открыт, это также относится к месту, где файл сохранен.

 ='C:\Users\sumit\Desktop\[Example File.xlsx]Sheet1'!$A$1 

Приведенный выше справочник состоит из следующих частей:

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

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

Влияние изменения местоположения файла на ссылки

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

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

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

Когда вы нажмете «Обновить», появится другое приглашение, в котором вы можете выбрать параметры для редактирования ссылок (которое покажет вам диалоговое окно ниже)

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

Ссылка на определенное имя (в той же или внешней книге)

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

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

Например, проще использовать =SalesData вместо =[Example File.xlsx]Sheet1′!$A$1:$A$10

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

Вот шаги для создания именованного диапазона для диапазона ячеек:

1. Выберите все ячейки, которые вы хотите включить в именованный диапазон
2. Перейдите на вкладку «Формулы» группа «Определенные имена»)
3. В диалоговом окне «Новое имя» дайте этому диапазону имя (в этом примере я использую имя SalesData). Помните, что в имени не должно быть пробелов.
4. Сохраняйте область как рабочую книгу (если только у вас нет веской причины сделать ее на уровне листа)
5. Убедитесь, что параметр Относится к диапазону указан правильно.
6. Нажмите OK.

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

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

 =СУММ(Данные о продажах) 

И что, если вы хотите использовать этот именованный диапазон в других рабочих листах или даже другие книги?

Можно!

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

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

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

Ссылка на определенное имя на том же рабочем листе или в рабочей книге

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

Например, если я хочу получить сумму всех ячеек в именованном диапазоне, который мы создали (SaledData), я могу использовать следующую формулу:

 =СУММ(SaledData) 

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

Если вы хотите использовать его на другом листе (скажем, Лист2), вам нужно использовать следующую формулу:

 =СУММ(Лист1!$A$1:$A$10) 

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

 =СУММ('Лист 1'!$A$1:$A$10) 

Ссылка на определенное имя в другой книге (открытой или закрытой)

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

Например, если у вас есть книга Excel с именем ExampleFile.xlsx и именованный диапазон с именем SalesData, вы можете использовать приведенную ниже формулу, чтобы получить сумму этого диапазона из другой книги:

 =СУММ(ExampleFile .xlsx!SalesData) 

Если в имени файла есть пробелы, их необходимо использовать в одинарных кавычках.

 =SUM('Example File.xlsx'!SalesData) 

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

Ниже приведен пример ссылки на именованный диапазон на уровне листа:

 =SUM('[Example File.xlsx]Sheet1'!SalesData) 

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

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

 =SUM('C:\Users\sumit\Desktop\Example File. xlsx'!SalesData) 

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

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

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

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

Предположим, вы создали именованный диапазон SalesData, который хотите использовать в формуле для СУММИРОВАНИЯ всех значений в именованном диапазоне.

Вот шаги для этого:

1. Выберите ячейку, в которую вы хотите ввести формулу.
2. Введите формулу в то место, где вам нужно вставить именованный диапазон
3. Нажмите клавишу F3 на клавиатуре. Откроется диалоговое окно «Вставить имя» со списком всех созданных вами имен.
4. Дважды щелкните имя, которое вы хотите вставить.