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

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

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

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

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

Но волнуйтесь … ничего сумасшедшего!

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

Давайте начнем!

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

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

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

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

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

Если вам нужно сослаться на другой лист в той же книге, вам необходимо использовать следующий формат:
Sheet_name! Cell_address
Во-первых, у вас есть имя листа, за которым следует восклицательный знак, за которым следует ссылка на ячейку.

Поэтому, если вам нужно обратиться к ячейке A1 на листе 1, вам нужно использовать следующую ссылку:
= Лист1! A1
А если вы хотите сослаться на диапазон ячеек на другом листе, вам нужно использовать следующий формат:
Имя_листа! Первая_ячейка: Последняя_ячейка
Итак, если вы хотите сослаться на диапазон A1: C10 на другом листе в той же книге, вам необходимо использовать приведенную ниже ссылку:
= Лист1! A1: C10
Обратите внимание, что я показал вам только ссылку на ячейку или диапазон. На самом деле вы бы использовали их в формулах. Но формат упомянутых выше ссылок останется прежним.

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

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

Например, если вы хотите сослаться на ячейку A1 на листе с названием «Данные о продажах», воспользуйтесь следующей ссылкой:
= "Данные о продажах"! A1
И в случае, если имя листа — Данные продаж, то для ссылки на ячейку A1 на этом листе вам необходимо использовать приведенную ниже ссылку:
= "Данные о продажах"! A1
Когда вы ссылаетесь на лист в той же книге, а затем меняете имя рабочего листа, вам не нужно беспокоиться о том, что ссылка сломается. 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)
= Данные о продажах! A1
Теперь, если я скопирую эту формулу и вставлю ее в ячейку A2, формула изменится на:
= Данные о продажах! 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. Нажмите на опцию Define Name (она находится в группе Defined Names)
4. В диалоговом окне «Новое имя» дайте этому диапазону имя (в этом примере я использую имя SalesData). Помните, что в названии не должно быть пробелов
5. Сохраните область действия как рабочую книгу (если у вас нет веских причин сделать ее на уровне листа)
6. Убедитесь, что ссылка на диапазон верна.
7. Щелкните ОК.

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

Например, если я хочу получить сумму всех этих ячеек в диапазоне SalesData, вы можете использовать следующую формулу:
= СУММ (SalesData)
А что, если вы хотите использовать этот именованный диапазон в других листах или даже в других книгах?

Ты можешь!

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

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

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

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

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

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

Если вы хотите использовать его на другом листе (скажем, Sheet2), вам нужно использовать следующую формулу:
= СУММ (Лист1! $ A $ 1: $ A $ 10)
А если в имени листа есть пробелы или буквенно-цифровые символы, вам придется заключить имя листа в одинарные кавычки.
= СУММ ('Лист 1'! $ A $ 1: $ A $ 10)

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

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

Например, если у вас есть книга Excel с именем ExampleFile.xlsx и именованный диапазон с именем SalesData, вы можете использовать приведенную ниже формулу, чтобы получить сумму этого диапазона из другой книги:
= СУММ (ExampleFile.xlsx! SalesData)
Если в имени файла есть пробелы, их нужно заключить в одинарные кавычки.
= СУММ ('Пример File.xlsx'! SalesData)
Если у вас есть именованные диапазоны на уровне листа, вам необходимо указать имя книги, а также рабочий лист при ссылке на него из внешней книги.

Ниже приведен пример ссылки на именованный диапазон на уровне листа:
= СУММ ('[Пример файла.xlsx] Sheet1'! SalesData)
Как я уже упоминал выше, всегда лучше создавать именованные диапазоны на уровне книги, если только у вас нет веских причин для создания первого уровня таблицы.

Если вы ссылаетесь на именованный диапазон в закрытой книге, вам также необходимо указать путь к файлу. Ниже приведен пример этого:
= СУММ ('C: \ Users \ sumit \ Desktop \ Example File.xlsx'! SalesData)
Когда вы создаете ссылку на именованный диапазон в открытой книге, а затем закрываете книгу, Excel автоматически изменяет ссылку и добавляет путь к файлу.

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

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

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

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

Вот как это сделать:

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

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

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

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

Надеюсь, вы нашли этот урок полезным.