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

Скачать пример рабочей книги

Загрузите образец книги

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

Ссылка на другой лист - создание формулы

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

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

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

Щелкните ячейку с нужным вам значением.

Нажмите Enter или щелкните галочку в строке формул.

Теперь ваша формула отобразится с правильной суммой в ячейке C7.

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

Sheet_name! Cell_address

Например:

1	= Северный! C10

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

Например:

1	= "Северный офис"! C10

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

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

Ссылка на другой лист - КОСВЕННАЯ функция

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

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

Поэтому в приведенной выше формуле мы использовали функцию КОСВЕННО для ссылки на имя листа в ячейке B7, например: «Северный».

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

1	= КОСВЕННО ("'" & Северное & "! C10")

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

Затем мы можем скопировать эту формулу в C8 и C9 - и имя листа «Северный» будет заменено на «Южный» и «Западный» по мере того, как формула будет скопирована.

Ссылка на другой лист - формула массива

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

Например:

Выберите C8: E8

Нажмите знак равенства, а затем щелкните лист, содержащий данные источника.

Выделите соответствующие ячейки исходных данных.

Нажмите Входить , чтобы ввести формулу в целевой рабочий лист.

Вы заметите, что ячейки содержат диапазон (C10: E10), но в каждом соответствующем столбце будет отображаться только значение из соответствующего столбца в исходной книге.

Ссылка на имя диапазона

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

В приведенном выше примере общие значения западного офиса в строке 10 называются Western.

Щелкните на листе "Головной офис", выделите нужные ячейки и нажмите знак равенства на клавиатуре.

Введите название диапазона, который вы создали, например: Western.

Нажмите Входить.

Будет создана формула массива.

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

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

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

Щелкните ячейку, в которую вы хотите поместить исходные данные, например: C12.

Нажмите клавишу равенства на клавиатуре и щелкните ячейку «Источник» в другой книге.

Нажмите Входить.

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

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

[Workbook_name] Sheet_name! Cell_address

Например:

1	= [SalesFigures.xlsx] Северный! 10 канадских долларов

Вы заметите, что ссылка на ячейку сделана абсолютной. Это означает, что вы НЕ МОЖЕТЕ перетащить его в столбцы D и E, если вы не удалите абсолют.

Щелкните ячейку, а затем щелкните в строке формул и щелкните адрес ячейки в строке формул.

Нажимайте F4 до тех пор, пока не будет удален абсолют.

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

Перетащите формулу в столбцы D и E.

Ссылка на другой лист в Google Документах

Связывание листов с формулами в Документах Google работает так же, как и в Excel.

Щелкните ячейку, в которую хотите поместить формулу, а затем щелкните ячейку источника, в которой хранится ваше значение.

Нажмите Входить.

Теперь ваша формула отобразится с правильной суммой в ячейке C7.

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

Sheet_name! Cell_address

Например:

1	= Северный! C10

Перетащите формулу, чтобы заполнить столбцы D и E, а затем повторите процесс для всех листов.

Ссылка на другой лист с помощью формулы массива в Excel

Формула массива также будет работать таким же образом.

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

Щелкните исходный лист и выделите нужные ячейки.

Нажмите Сдвиг+Входить.

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

Ссылка на другую книгу в Документах Google

Если вы хотите связать файлы Google Таблиц вместе, вам необходимо использовать функцию IMPORTRANGE.

Откройте исходный файл таблицы Google, чтобы скопировать URL-адрес файла.

Например

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

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

Вставьте URL-адрес, скопированный из исходного листа Google, в формулу.

Закройте кавычки

Например:

1	= IMPORTRANGE ("https://docs.google.com/spreadsheets/d/1kJ9ECcuwO65ayFXCobxuUSwx9MnPSDXypiyPWoHc59E/edit#gid=1711678147"

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

Например

«Северный! C10»

Ваша полная формула будет выглядеть, как в примере ниже

1	= IMPORTRANGE ("https://docs.google.com/spreadsheets/d/1kJ9ECcuwO65ayFXCobxuUSwx9MnPSDXypiyPWoHc59E/edit#gid=1711678147", "Северный! C10")

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

Щелкните Разрешить доступ.

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

СОВЕТ: перетащите формулу в нужные ячейки и измените адрес ячеек в строке формул!