Ссылка Excel на другой лист или книгу (внешняя ссылка)
В этом кратком руководстве объясняются основы внешней ссылки в Excel и показано, как ссылаться на другой лист и книгу в формулах.
При расчете данных в Excel вы часто можете оказаться в ситуации, когда вам нужно извлечь данные из другого рабочего листа или даже из другого файла Excel. Вы можете это сделать? Конечно вы можете. Вам просто нужно создать связь между рабочими листами (в одной рабочей книге или в разных рабочих книгах), используя то, что называется внешней ссылкой на ячейку или ссылкой.
Внешняя ссылка в Excel — это ссылка на ячейку или диапазон ячеек за пределами текущего рабочего листа. Основное преимущество использования внешней ссылки Excel заключается в том, что всякий раз, когда ссылочные ячейки на другом листе изменяются, значение, возвращаемое внешней ссылкой на ячейку, автоматически обновляется.
Хотя внешние ссылки в Excel очень похожи на ссылки на ячейки, между ними есть несколько важных отличий. В этом руководстве мы начнем с основ и покажем, как создавать различные типы внешних ссылок, с подробными инструкциями, снимками экрана и примерами формул.
Как сослаться на другой лист в Excel
Чтобы сослаться на ячейку или диапазон ячеек на другом рабочем листе в той же книге, поместите имя рабочего листа с восклицательным знаком (!) перед адресом ячейки.
Другими словами, в ссылке Excel на другой рабочий лист вы используете следующий формат:
Ссылка на отдельную ячейку:
имя_листа!Cell_address
Например, чтобы сослаться на ячейку A1 в Sheet2, введите Лист2!A1.
Ссылка на диапазон ячеек:
имя_листа!Первая_ячейка:Last_cell
Например, чтобы сослаться на ячейки A1:A10 на Листе2, введите Лист2!A1:A10.
Примечание. Если имя рабочего листа содержит пробелы или неалфавитные символы, его необходимо заключить в одинарные кавычки. Например, внешняя ссылка на ячейку A1 на листе с именем Основные этапы проекта следует читать следующим образом: «Вехи проекта»!A1.
В реальной формуле, которая умножает значение в ячейке A1 в ‘Основные этапы проекта’ лист на 10, ссылка на лист Excel выглядит так:
=’Вехи проекта’!A1*10
Создание ссылки на другой лист в Excel
При написании формулы, которая ссылается на ячейки на другом листе, вы, конечно, можете ввести имя этого другого листа, а затем восклицательный знак и ссылку на ячейку вручную, но это будет медленным и подверженным ошибкам способом.
Лучше указать ячейку (ячейки) на другом листе, на которую вы хотите ссылаться в формуле, и позволить Excel позаботиться о правильном синтаксисе ссылки на ваш лист. Чтобы Excel вставил ссылку на другой лист в формулу, сделайте следующее:
- Начните вводить формулу либо в ячейке назначения, либо в строке формул.
- Когда дело доходит до добавления ссылки на другой лист, переключитесь на этот лист и выберите ячейку или диапазон ячеек, на которые вы хотите сослаться.
- Закончите вводить формулу и нажмите клавишу Enter, чтобы завершить ее.
Например, если у вас есть список показателей продаж на листе Продажи и вы хотите рассчитать налог на добавленную стоимость (19%) для каждого продукта на другом листе с именем НДСдействуйте следующим образом:
- Начните вводить формулу =19%* в ячейку B2 на листе. НДС.
- Перейти на лист Продажи, и нажмите там на ячейку B2. Excel немедленно вставит внешнюю ссылку на эту ячейку, как показано на следующем снимке экрана:
- Нажмите Enter, чтобы завершить формулу.
Примечание. При добавлении ссылки Excel на другой лист с использованием описанного выше метода Microsoft Excel по умолчанию добавляет относительную ссылку (без знака $). Итак, в приведенном выше примере вы можете просто скопировать формулу в другие ячейки столбца B на листе. НДСссылки на ячейки будут скорректированы для каждой строки, и у вас будет правильно рассчитан НДС для каждого продукта.
Аналогичным образом вы можете ссылаться на диапазон ячеек на другом листе. Единственное отличие состоит в том, что вы выбираете несколько ячеек на исходном листе. Например, чтобы узнать сумму продаж в ячейках B2:B5 на листе Продаживы должны ввести следующую формулу:
=СУММ(Продажи!B2:B5)
Вот как вы ссылаетесь на другой лист в Excel. А теперь давайте посмотрим, как можно ссылаться на ячейки из другой книги.
Как сослаться на другую книгу в Excel
В формулах Microsoft Excel внешние ссылки на другую книгу отображаются двумя способами в зависимости от того, открыта или закрыта исходная книга.
Внешняя ссылка на открытую книгу
Когда исходная книга открыта, внешняя ссылка Excel включает в себя имя книги в квадратных скобках (включая расширение файла), за которым следует имя листа, восклицательный знак (!), а также указанная ячейка или диапазон ячеек. Другими словами, для ссылки на открытую книгу используется следующий формат ссылки:
[Workbook_name]имя_листа!Cell_address
Например, вот внешняя ссылка на ячейки B2:B5 на листе Ян в рабочей тетради под названием Продажи.xlsx:
[Sales.xlsx]Январь!B2:B5
Если вы хотите, скажем, вычислить сумму этих ячеек, формула со ссылкой на рабочую книгу будет выглядеть следующим образом:
=СУММ([Sales.xlsx]Январь!B2:B5)
Внешняя ссылка на закрытую книгу
Когда вы ссылаетесь на другую книгу в Excel, эта книга не обязательно должна быть открыта. Если исходная книга закрыта, необходимо добавить полный путь к внешней ссылке.
Например, чтобы сложить ячейки B2:B5 в Ян лист из Продажи.xlsx рабочая книга, которая находится в отчеты папку на диске D, вы пишете следующую формулу:
=СУММ(D:\Отчеты\[Sales.xlsx]Январь!B2:B5)
Вот разбивка эталонных частей:
- Путь к файлу. Он указывает на диск и каталог, в котором хранится ваш файл Excel (Д:\Отчеты\ в этом примере).
- Имя рабочей книги. Он включает расширение файла (.xlsx, .xls или .xslm) и всегда заключен в квадратные скобки, например [Sales.xlsx] в приведенной выше формуле.
- Имя листа. Эта часть внешней ссылки Excel включает в себя имя листа, за которым следует восклицательный знак, в котором находится указанная ячейка (ячейки) (Ян! в этом примере).
- Ссылка на ячейку. Он указывает на фактическую ячейку или диапазон ячеек, на который ссылается ваша формула.
Если вы создали ссылку на другую книгу, когда эта книга была открыта, а после этого закрыли исходную книгу, ссылка на внешнюю книгу автоматически обновится, чтобы включить весь путь.
Примечание. Если либо имя книги, либо имя листа, либо и то, и другое содержат пробелы или любые неалфавитные символы, необходимо заключить путь в одинарные кавычки. Например:
=СУММ(‘[Year budget.xlsx]Ян’!B2:B5)
=СУММ(‘[Sales.xlsx]Январь продаж’!B2:B5)
=СУММ(‘D:\Отчеты\[Sales.xlsx]Январь продаж’!B2:B5)
Создание ссылки на другую книгу в Excel
Как и в случае создания формулы Excel, которая ссылается другой лист, вам не нужно вручную вводить ссылку на другую книгу. Просто переключитесь на другую книгу при вводе формулы и выберите ячейку или диапазон ячеек, на которые вы хотите сослаться. Microsoft Excel позаботится обо всем остальном:
Заметки:
- При создании ссылки на другую книгу путем выбора ячеек в ней Excel всегда вставляет абсолютные ссылки на ячейки. Если вы собираетесь копировать только что созданную формулу в другие ячейки, обязательно удалите знак доллара ($) из ссылок на ячейки, чтобы превратить их в относительные или смешанные ссылки, в зависимости от ваших целей.
- Если выбор ячейки или диапазона в рабочей книге, на которую указывает ссылка, не приводит к автоматическому созданию ссылки в формуле, скорее всего, эти два файла открыты в разных экземплярах Excel. Чтобы проверить это, откройте Диспетчер задач и посмотрите, сколько запущено экземпляров Microsoft Excel. Если их несколько, разверните каждый экземпляр, чтобы просмотреть, какие файлы в нем вложены. Чтобы устранить проблему, закройте один файл (и экземпляр), а затем снова откройте его из другого файла.
Ссылка на определенное имя в той же или другой книге
Чтобы сделать внешнюю ссылку Excel более компактной, вы можете создать определенное имя на исходном листе, а затем ссылаться на это имя с другого листа, который находится в той же книге или в другой книге.
Создание имени в Excel
Чтобы создать имя в Excel, выберите все ячейки, которые вы хотите включить, а затем либо перейдите к Формулы вкладка > Определенные имена группу и нажмите кнопку «Определить имя» или нажмите Ctrl + F3 и нажмите Новый.
в Новое имя введите любое имя, которое вы хотите (помните, что пробелы не допускаются в именах Excel), и проверьте, отображается ли правильный диапазон в Относится к поле.
Например, вот как мы создаем имя (Jan_sales) для ячеек B2:B5 в Ян лист:
После создания имени вы можете использовать его во внешних ссылках в Excel. Формат таких справок намного проще, чем формат Excel. ссылка на лист а также ссылка на книгу обсуждалось ранее, что облегчает понимание формул со ссылками на имена.
Примечание. По умолчанию имена Excel создаются для уровня рабочей книги, обратите внимание на Сфера поле на скриншоте выше. Но вы также можете сделать конкретное имя уровня рабочего листа, выбрав соответствующий лист из Сфера выпадающий список. Для ссылок Excel очень важна область действия имени, поскольку она определяет местоположение, в котором имя распознается.
Рекомендуется всегда создавать имена на уровне рабочей книги (если у вас нет особой причины не делать этого), поскольку они значительно упрощают создание внешних ссылок Excel, как показано в следующих примерах.
Ссылка на имя на другом листе в той же книге
Чтобы сослаться на глобальное имя уровня книги в той же книге, вы просто вводите это имя в аргументе функции:
знак равноФункция(имя)
Например, чтобы найти сумму всех ячеек в пределах Jan_sales имя, которое мы создали минуту назад, используйте следующую формулу:
=СУММ(Продажи_Янв)
Чтобы сослаться на локальное имя уровня рабочего листа на другом листе в той же книге, вам необходимо указать перед именем имя листа, за которым следует восклицательный знак:
знак равноФункция(имя_листа!имя)
Например:
=СУММ(Янв!Ян_продажи)
Если имена листов содержат пробелы или однобуквенные символы, не забудьте заключить их в одинарные кавычки, например:
=СУММ(‘Отчет за январь’!Jan_Sales)
Ссылка на имя в другой книге
Ссылка на имя уровня рабочей книги в другой рабочей книге состоит из имени рабочей книги (включая расширение), за которым следует восклицательный знак, и определенного имени (именованного диапазона):
знак равноФункция(Workbook_name!имя)
Например:
=СУММ(Продажи.xlsx!Jan_sales)
Чтобы сослаться на имя уровня рабочего листа в другой книге, имя листа, за которым следует восклицательный знак, также должно быть включено, а имя книги должно быть заключено в квадратные скобки. Например:
=СУММ([Sales.xlsx]Ян!Ян_продажи)
При ссылке на именованный диапазон в закрытой книге не забудьте указать полный путь к файлу Excel, например:
=СУММ(‘C:\Documents\Sales.xlsx’!Jan_sales)
Как создать ссылку на имя в Excel
Если вы создали несколько разных имен на листах Excel, вам не нужно запоминать все эти имена наизусть. Чтобы вставить ссылку на имя Excel в формулу, выполните следующие действия:
- Выберите ячейку назначения, введите знак равенства (=) и начните вводить формулу или расчет.
- Когда дело доходит до части, где вам нужно вставить ссылку на имя Excel, выполните одно из следующих действий:
- Если вы ссылаетесь на имя уровня книги из другой книги, переключитесь на эту книгу. Если имя находится на другом листе в той же книге, пропустите этот шаг.
- Если вы делаете ссылку на имя уровня рабочего листа, перейдите к этому конкретному листу либо в текущей, либо в другой книге.
- Нажмите F3, чтобы открыть Прошлое имя диалоговом окне, выберите имя, на которое вы хотите сослаться, и нажмите OK.
- Завершите ввод формулы или расчета и нажмите клавишу Enter.
Теперь, когда вы знаете, как создать внешнюю ссылку в Excel, вы можете воспользоваться этой замечательной возможностью и использовать данные из других листов и книг в своих расчетах. Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
