В Microsoft Excel часто приходится ссылаться на ячейки на других листах или даже в разных файлах Excel. Сначала это может показаться немного сложным и запутанным, но как только вы поймете, как это работает, это не так уж и сложно.
В этой статье мы рассмотрим, как ссылаться на другой лист в том же файле Excel и как ссылаться на другой файл Excel. Мы также расскажем о том, как ссылаться на диапазон ячеек в функции, как упростить задачу с помощью определенных имен и как использовать ВПР для динамических ссылок.
Оглавление
Как сослаться на другой лист в том же файле Excel
Базовая ссылка на ячейку записывается в виде буквы столбца, за которой следует номер строки.
Таким образом, ссылка на ячейку B3 относится к ячейке на пересечении столбца B и строки 3.
При ссылке на ячейки на других листах этой ссылке на ячейку предшествует имя другого листа. Например, ниже приведена ссылка на ячейку B3 на листе с названием «Январь».
=January!B3
Восклицательный знак (!) Отделяет имя листа от адреса ячейки.
Если имя листа содержит пробелы, вы должны заключить имя в одинарные кавычки в ссылке.
='January Sales'!B3
Чтобы создать эти ссылки, вы можете ввести их прямо в ячейку. Однако проще и надежнее позволить Excel написать за вас справочную информацию.
Введите знак равенства (=) в ячейку, щелкните вкладку «Лист», а затем щелкните ячейку, на которую необходимо создать перекрестную ссылку.
По мере того как вы это делаете, Excel записывает для вас ссылку на панель формул.
Нажмите Enter, чтобы заполнить формулу.
Как сослаться на другой файл Excel
Вы можете ссылаться на ячейки другой книги, используя тот же метод. Просто убедитесь, что у вас открыт другой файл Excel, прежде чем начинать вводить формулу.
Введите знак равенства (=), переключитесь на другой файл и щелкните ячейку в этом файле, на которую хотите сослаться. Когда закончите, нажмите Enter.
Заполненная перекрестная ссылка содержит имя другой книги, заключенное в квадратные скобки, за которым следуют имя листа и номер ячейки.
=[Chicago.xlsx]January!B3
Если имя файла или листа содержит пробелы, вам необходимо заключить ссылку на файл (включая квадратные скобки) в одинарные кавычки.
='[New York.xlsx]January'!B3
В этом примере вы можете увидеть знаки доллара ($) среди адресов ячейки. Это абсолютная ссылка на ячейку (узнайте больше об абсолютных ссылках на ячейки).
При ссылке на ячейки и диапазоны в разных файлах Excel по умолчанию ссылки делаются абсолютными. При необходимости вы можете изменить это на относительную ссылку.
Если вы посмотрите на формулу, когда ссылка на книгу закрыта, она будет содержать полный путь к этому файлу.
Хотя создание ссылок на другие книги несложно, они более подвержены проблемам. Пользователи, создающие или переименовывающие папки и перемещающие файлы, могут нарушить эти ссылки и вызвать ошибки.
По возможности надежнее хранить данные в одной книге.
Как сделать перекрестную ссылку на диапазон ячеек в функции
Ссылка на одну ячейку достаточно полезна. Но вы можете захотеть написать функцию (например, SUM), которая ссылается на диапазон ячеек на другом листе или книге.
Запустите функцию как обычно, а затем щелкните лист и диапазон ячеек — так же, как вы это делали в предыдущих примерах.
В следующем примере функция СУММ суммирует значения из диапазона B2: B6 на листе с именем Sales.
=SUM(Sales!B2:B6)
Как использовать определенные имена для простых перекрестных ссылок
В Excel вы можете присвоить имя ячейке или диапазону ячеек. Это более значимо, чем адрес ячейки или диапазона, когда вы на них смотрите. Если вы используете много ссылок в своей электронной таблице, присвоение им имен может значительно упростить просмотр того, что вы сделали.
Более того, это имя уникально для всех листов в этом файле Excel.
Например, мы могли бы назвать ячейку «ChicagoTotal», и тогда перекрестная ссылка будет выглядеть так:
=ChicagoTotal
Это более значимая альтернатива такой стандартной ссылке:
=Sales!B2
Создать определенное имя легко. Начните с выбора ячейки или диапазона ячеек, которым вы хотите присвоить имя.
Щелкните поле имени в верхнем левом углу, введите имя, которое хотите назначить, и нажмите клавишу ВВОД.
При создании определенных имен вы не можете использовать пробелы. Поэтому в этом примере слова были объединены в имя и разделены заглавной буквой. Вы также можете разделять слова такими символами, как дефис (-) или подчеркивание (_).
В Excel также есть диспетчер имен, который упрощает отслеживание этих имен в будущем. Щелкните Формулы> Диспетчер имен. В окне диспетчера имен вы можете увидеть список всех определенных имен в книге, где они находятся и какие значения они хранят в настоящее время.
Затем вы можете использовать кнопки вверху для редактирования и удаления этих определенных имен.
Как отформатировать данные в виде таблицы
При работе с обширным списком связанных данных использование функции Excel Format as Table может упростить способ ссылки на данные в ней.
Возьмите следующую простую таблицу.
Его можно отформатировать в виде таблицы.
Щелкните ячейку в списке, перейдите на вкладку «Главная», нажмите кнопку «Форматировать как таблицу», а затем выберите стиль.
Убедитесь, что диапазон ячеек правильный и что в вашей таблице есть заголовки.
Затем вы можете присвоить своей таблице осмысленное имя на вкладке «Дизайн».
Затем, если нам нужно суммировать продажи Чикаго, мы могли бы ссылаться на таблицу по ее имени (с любого листа), за которым следует квадратная скобка ([) to see a list of the table’s columns.
Select the column by double-clicking it in the list and enter a closing square bracket. The resulting formula would look something like this:
=SUM(Sales[Chicago])
Вы можете увидеть, как таблицы могут упростить обращение к данным для функций агрегирования, таких как SUM и AVERAGE, по сравнению со стандартными ссылками на листы.
Эта таблица небольшая для демонстрации. Чем больше таблица и чем больше листов у вас в книге, тем больше преимуществ вы увидите.
Как использовать функцию VLOOKUP для динамических ссылок
Все ссылки, использованные в примерах до сих пор, были привязаны к определенной ячейке или диапазону ячеек. Это замечательно и часто бывает достаточно для ваших нужд.
Однако что, если ячейка, на которую вы ссылаетесь, может измениться при вставке новых строк или некоторых