Как сделать перекрестную ссылку на ячейки между электронными таблицами Microsoft Excel

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

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

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

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

Таким образом, ссылка на ячейку B3 относится к ячейке на пересечении столбца B и строки 3.

При ссылке на ячейки на других листах этой ссылке на ячейку предшествует имя другого листа. Например, ниже приведена ссылка на ячейку B3 в названии листа «Январь».

Восклицательный знак (!) Отделяет имя листа от адреса ячейки.

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

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

Введите знак равенства (=) в ячейку, щелкните вкладку «Лист», а затем щелкните ячейку, на которую вы хотите сделать перекрестную ссылку.

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

Как сослаться на другой файл Excel

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

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

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

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

В этом примере вы можете увидеть знаки доллара ($) среди адресов ячейки. Это абсолютная ссылка на ячейку ( Узнайте больше об абсолютных ссылках на ячейки ).

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

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

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

Хранение данных в одной книге, если это возможно, более надежно.

Как сделать перекрестную ссылку на диапазон ячеек в функции

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

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

В следующем примере функция SUM суммирует значения из диапазона B2: B6 на листе с именем Sales.

Как использовать определенные имена для простых перекрестных ссылок

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

Более того, это имя уникально для всех таблиц в этом файле Excel.

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

Это более значимая альтернатива стандартной ссылке, подобной этой:

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

Щелкните в поле «Имя» в верхнем левом углу, введите имя, которое хотите назначить, и нажмите клавишу «Ввод».

При создании определенных имен вы не можете использовать пробелы. Поэтому в этом примере слова были объединены в имени и разделены заглавной буквой. Вы также можете разделить слова с помощью символов, таких как дефис (-) или подчеркивание (_).

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

Затем вы можете использовать кнопки вверху для редактирования и удаления указанных имен.

Как отформатировать данные в виде таблицы

При работе с обширным списком связанных данных использование функции «Формат как таблицы» в Excel может упростить обращение к данным в ней.

Нажмите на ячейку в списке, перейдите на вкладку «Главная», нажмите кнопку «Формат таблицы» и выберите стиль.

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

Затем вы можете присвоить значимое имя вашей таблице на вкладке «Дизайн».

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

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

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

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

Как использовать функцию VLOOKUP для динамических ссылок

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

Однако что, если ячейка, на которую вы ссылаетесь, может измениться при вставке новых строк или кто-то отсортирует список?

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

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

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

Функция VLOOKUP просматривает первый столбец таблицы и затем возвращает информацию из указанного столбца справа.

Следующая функция VLOOKUP ищет идентификатор сотрудника, введенный в ячейку A2 в приведенном выше списке, и возвращает дату, объединенную из столбца 4 (четвертый столбец таблицы).

Ниже приведена иллюстрация того, как эта формула выполняет поиск в списке и возвращает правильную информацию.

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

Примечание. VLOOKUP — невероятно полезная формула, и в этой статье мы только коснулись ее значения. Вы можете узнать больше о том, как использовать VLOOKUP из нашей статьи на эту тему .

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

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

Как привязать файл к ячейке в excel

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