Как получить доступ к ячейке, расположенной на другом листе текущей книги в Excel

Обновлено: 21.11.2024

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

Откройте книгу, содержащую внешнюю ссылку (целевую книгу), и книгу, содержащую данные, на которые вы хотите связать (исходную книгу).

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

Тип = (знак равенства).

Если вы хотите использовать функцию, например SUM, введите имя функции и открывающую скобку. Например, =СУММ(.

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

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

Примечание. Если вы выбрали несколько ячеек, например =[SourceWorkbook.xlsx]Sheet1!$A$1:$A$10, и используете текущую версию Microsoft 365, вы можете просто нажать клавишу ВВОД, чтобы подтвердить формулу как динамическую. формула массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, нажав CTRL+SHIFT+ENTER. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

Excel вернет вас в целевую книгу и отобразит значения из исходной книги.

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

=[SourceWorkbook.xlsx]Лист1!$A$1

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

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

Тип = (знак равенства).

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

Нажмите F3, выберите имя, на которое хотите сослаться, и нажмите Enter.

Примечание. Если именованный диапазон ссылается на несколько ячеек и у вас установлена текущая версия Microsoft 365, вы можете просто нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, нажав CTRL+SHIFT+ENTER. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

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

Откройте целевую книгу и исходную книгу.

В целевой книге выберите «Формулы» > «Определенные имена» > «Определить имя».

В диалоговом окне "Новое имя" в поле "Имя" введите имя диапазона.

В поле Относится к удалите содержимое и оставьте курсор в поле.

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

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

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

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

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

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

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

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

Когда исходный код открыт, внешняя ссылка включает имя рабочей книги в квадратных скобках ([ ]), за которым следует имя рабочей таблицы, восклицательный знак (!) и ячейки, от которых зависит формула. Например, следующая формула добавляет ячейки C10:C25 из книги с именем Budget.xls.

Внешняя ссылка

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

Внешняя ссылка

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

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

Внешняя ссылка

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

Тип = (знак равенства).

Если вы хотите использовать функцию, например SUM, введите имя функции и открывающую скобку. Например, =СУММ(.

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

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

Примечание. Если вы выбрали несколько ячеек (=Лист1!A1:A10) и используете текущую версию Microsoft 365, вы можете просто нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, нажав CTRL+SHIFT+ENTER. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

Excel вернется к исходному листу и отобразит значения из исходного листа.

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

Откройте книгу, содержащую внешнюю ссылку (целевую книгу, также называемую книгой формул), и книгу, содержащую данные, на которые вы хотите связать (исходную книгу, также называемую книгой данных).

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

Нажмите Ctrl+C или выберите Главная > Буфер обмена > Копировать.

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

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

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

Чтобы использовать ссылку в формуле, введите = перед ссылкой, выберите функцию, введите (, а затем введите ) после ссылки.

Создать ссылку на лист в той же книге

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

Тип = (знак равенства).

Если вы хотите использовать функцию, например SUM, введите имя функции и открывающую скобку. Например, =СУММ(.

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

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

Excel вернется к исходному листу и отобразит значения из исходного листа.

Нужна дополнительная помощь?

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

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

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

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

Ссылка на рабочий лист в формуле имеет восклицательный знак (!) после имени листа.

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

Ссылка на другую книгу

Вы также можете ссылаться на ячейки из совершенно разных файлов.

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

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

Значение из другой книги отображается на листе.

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

БЕСПЛАТНЫЙ краткий справочник

Бесплатно для распространения с нашей благодарностью; мы надеемся, что вы рассмотрите наше платное обучение.

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

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

Данные из одной или нескольких смежных ячеек на листе.

Данные, содержащиеся в разных областях рабочего листа.

Данные на других листах в той же книге.

Эта формула:

И возвращает:

Значение в ячейке C2.

Ячейки от A1 до F4

Значения во всех ячейках, но вы должны нажать Ctrl+Shift+Enter после ввода формулы.

Примечание. Эта функция не работает в Excel для Интернета.

Ячейки с именами "Активы и обязательства"

Значение в ячейке с именем "Обязательство" вычитается из значения в ячейке с именем "Актив".

Диапазоны ячеек с именами Week1 и Week2

Сумма значений диапазонов ячеек с именами Week1 и Week 2 в виде формулы массива.

Ячейка B2 на Листе2

Значение в ячейке B2 на Листе2.

Нажмите на ячейку, в которую хотите ввести формулу.

В строке формул введите = (знак равенства).

Выполните одно из следующих действий:

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

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

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

Нажмите F3, выберите имя в поле "Вставить имя" и нажмите "ОК".

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

Выполните одно из следующих действий:

Если вы создаете ссылку в одной ячейке, нажмите Enter.

Если вы создаете ссылку в формуле массива (например, A1:G4), нажмите Ctrl+Shift+Enter.

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

Примечание. Если у вас установлена текущая версия Microsoft 365, вы можете просто ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введя формулу в верхнюю левую ячейку выходного диапазона, а затем нажав CTRL+SHIFT+ENTER для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

Вы можете ссылаться на ячейки, которые находятся на других листах в той же книге, добавляя имя рабочего листа, а затем восклицательный знак (!) в начале ссылки на ячейку. В следующем примере функция листа с именем СРЗНАЧ вычисляет среднее значение для диапазона B1:B10 на листе с именем Маркетинг в той же книге.

<р>1. Относится к рабочему листу под названием "Маркетинг"

<р>2. Относится к диапазону ячеек от B1 до B10 включительно

<р>3. Отделяет ссылку на рабочий лист от ссылки на диапазон ячеек

Нажмите на ячейку, в которую хотите ввести формулу.

В строке формул введите = (знак равенства) и нужную формулу.

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

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

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

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

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

Упростите создание ссылок на ячейки между листами и книгами. Команда «Связать ячейки» автоматически вставляет правильный синтаксис.

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

Нажмите Ctrl+C или перейдите на вкладку "Главная" и в группе "Буфер обмена" нажмите "Копировать" .

Нажмите Ctrl+V или перейдите на вкладку "Главная", в группе "Буфер обмена" нажмите "Вставить" .

По умолчанию кнопка "Параметры вставки" появляется при вставке скопированных данных.

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

Выполните одно из следующих действий:

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

Чтобы включить в ссылку больше или меньше ячеек, перетащите угол границы.

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

Нажмите F3, выберите имя в поле "Вставить имя" и нажмите "ОК".

Нажмите клавишу ВВОД или, чтобы ввести формулу массива, нажмите клавиши CTRL+SHIFT+ВВОД.

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

Выполните одно из следующих действий:

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

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

На вкладке "Формулы" в группе "Определенные имена" щелкните стрелку рядом с пунктом "Определить имя" и выберите "Применить имена".

В поле "Применить имена" выберите одно или несколько имен, а затем нажмите "ОК".

Выберите ячейку, содержащую формулу.

В строке формул выберите ссылку, которую нужно изменить.

Нажмите F4 для переключения между типами ссылок.

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

Нажмите на ячейку, в которую хотите ввести формулу.

В строке формул введите = (знак равенства).

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

Выполните одно из следующих действий:

Если вы создаете ссылку в одной ячейке, нажмите Enter.

Если вы создаете ссылку в формуле массива (например, A1:G4), нажмите Ctrl+Shift+Enter.

Вы можете ссылаться на ячейки, которые находятся на других листах в той же книге, добавляя имя рабочего листа, а затем восклицательный знак (!) в начале ссылки на ячейку.В следующем примере функция листа с именем СРЗНАЧ вычисляет среднее значение для диапазона B1:B10 на листе с именем Маркетинг в той же книге.

<р>1. Относится к рабочему листу под названием "Маркетинг"

<р>2. Относится к диапазону ячеек от B1 до B10 включительно

<р>3. Отделяет ссылку на рабочий лист от ссылки на диапазон ячеек

Нажмите на ячейку, в которую хотите ввести формулу.

В строке формул введите = (знак равенства) и нужную формулу.

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

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

Выполните одно из следующих действий:

Чтобы включить в ссылку больше или меньше ячеек, перетащите угол границы.

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

Нажмите клавишу ВВОД или, чтобы ввести формулу массива, нажмите клавиши CTRL+SHIFT+ВВОД.

Выберите ячейку, содержащую формулу.

В строке формул выберите ссылку, которую нужно изменить.

Нажмите F4 для переключения между типами ссылок.

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

Нужна дополнительная помощь?

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

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

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

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

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

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

Нажмите на ячейку, в которую вы хотите ввести ссылку на другую ячейку.
Введите в ячейку знак равенства (=).
Щелкните ячейку на том же листе, на который вы хотите создать ссылку, и имя ячейки будет автоматически введено после знака равенства. Нажмите Enter, чтобы создать ссылку на ячейку.

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

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

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

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

Например, у нас есть электронная таблица, содержащая два рабочих листа с названиями "Данные" и "Расчеты". На листе Расчеты мы хотим сослаться на ячейку из листа Данные. Мы щелкаем вкладку «Рабочий лист данных», затем щелкаем ячейку B3, в результате чего в строке формул отображается «= Data! B3» для ячейки, содержащей ссылку. Данные, отображаемые на листе Расчеты, отражают данные в ячейке B3 на листе данных и изменяются при изменении ячейки B3.

Добавить две ячейки

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

В новом листе введите два значения в ячейки A1 и A2. В этом примере мы введем значение 5 в ячейку A1 и 6 в ячейку A2.
Нажмите ячейку C1, чтобы выбрать ее. В этой ячейке находится наша формула.
Нажмите внутри строки формул и введите =, чтобы начать писать формулу.

Нажмите на ячейку A1, чтобы автоматически вставить ссылку на ячейку в формулу.

Нажмите ячейку B1, чтобы автоматически вставить ссылку на ячейку в формулу.

Нажмите Enter . Ячейка C1, содержащая вашу формулу, автоматически обновит свое значение суммой 5 и 6.

Теперь, если вы измените значения в ячейках A1 или B1, значение в ячейке C1 обновится автоматически.

Не нужно щелкать ячейки, чтобы вставить ссылку на ячейку в формулу. При желании, выбрав ячейку C1, введите =A1+B1 в строке формул и нажмите клавишу ВВОД .

Добавить диапазон ячеек

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

Например, вы можете добавить диапазон значений с помощью функции СУММ(). В этом примере показано, как можно суммировать всю строку или столбец значений, указав диапазон между двумя ссылками на ячейки.

Читайте также: