Как связать ячейки в Excel

Обновлено: 20.11.2024

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

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

Зачем связывать данные ячеек в Excel

Умение ссылаться на данные на разных листах является ценным навыком по нескольким причинам.

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

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

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

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

Как связать две отдельные ячейки

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

На Листе 2 введите в ячейку символ равенства (=).
Перейдите на другую вкладку (Лист1) и щелкните ячейку, на которую хотите создать ссылку.
Нажмите Enter, чтобы завершить формулу.

Теперь, если щелкнуть ячейку на Листе2, вы увидите, что Excel записывает путь для вас в строке формул.

Например, =Лист1!C3, где Лист1 – это имя листа, C3 – ячейка, на которую вы ссылаетесь, а восклицательный знак (!) используется в качестве разделителя между ними.

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

Примечание. Если имя листа содержит пробелы (например, Лист 1), вам необходимо поместить имя в одинарные кавычки при вводе ссылки в ячейку. Нравится =’Лист 1’!C3. Вот почему иногда проще и надежнее позволить Excel написать справочную формулу за вас.

Как связать диапазон ячеек

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

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

На исходной вкладке с данными (Лист1) выделите ячейки, на которые вы хотите сослаться.
Скопируйте ячейки (Ctrl/Command + C или щелкните правой кнопкой мыши и выберите "Копировать").
Перейдите на другую вкладку (Лист2) и щелкните ячейку (или ячейки), в которую вы хотите поместить ссылки.

Щелкните правой кнопкой мыши по ячейкам и выберите "Специальная вставка...".

В левом нижнем углу меню выберите "Вставить ссылку".

Когда вы нажимаете на новые связанные ячейки на Листе 2, вы можете увидеть ссылки на ячейки из Листа 1 на вкладке формул. Теперь всякий раз, когда вы меняете данные в выбранных ячейках на Листе 1, автоматически изменяются данные в связанных ячейках на Листе 2.

Как связать ячейку с функцией

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

Допустим, вам нужно написать функцию СУММ на Листе2, которая будет ссылаться на ряд ячеек из Листа1. Для этого перейдите на Лист2 и щелкните ячейку, в которую вы хотите поместить функцию. Напишите функцию как обычно, но когда дело доходит до выбора диапазона ячеек, перейдите на другой лист и выделите их, как описано выше.

Вы получите =СУММ(Лист1!C3:C7), где функция СУММ суммирует содержимое ячеек C3:C7 на Листе1. Нажмите Enter, чтобы завершить формулу.

Как связать ячейки из разных файлов Excel

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

Откройте оба документа Excel.
Во втором файле (Help Desk Geek) выберите ячейку и введите символ равенства (=).
Переключитесь на исходный файл (Интернетовые технические советы) и щелкните ячейку, на которую хотите создать ссылку.
Нажмите Enter, чтобы завершить формулу.

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

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

Стать профессиональным пользователем Microsoft Excel

Связывание ячеек между листами — это только один пример того, как вы можете фильтровать данные в Excel и упорядочивать электронные таблицы. Ознакомьтесь с некоторыми другими советами и рекомендациями по Excel, которые мы собрали, чтобы помочь вам стать опытным пользователем.

Какие еще полезные лайфхаки для Excel вы знаете и используете? Знаете ли вы какие-либо другие творческие способы связать ячейки в Excel? Поделитесь ими с нами в разделе комментариев ниже.

Аня – внештатный писатель по технологиям. Родом из России, в настоящее время она работает удаленным работником и цифровым кочевником. Имея образование в области журналистики, лингвистики и технического перевода, Аня не могла представить свою жизнь и работу без ежедневного использования современных технологий. Всегда ища новые способы облегчить свою жизнь и образ жизни, не зависящий от местоположения, она надеется поделиться своим опытом в качестве техно- и интернет-зависимого в своих письмах. Прочитать полную биографию Ани

Понравился ли вам этот совет? Если это так, загляните на наш собственный канал на YouTube, где мы рассказываем о Windows, Mac, программном обеспечении и приложениях, а также предлагаем множество советов по устранению неполадок и видео с практическими рекомендациями. Нажмите кнопку ниже, чтобы подписаться!

Узнайте, как связать ячейки Link на одном или разных листах Excel.

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

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

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

Лист, предоставляющий данные, называется исходным листом.
Лист, содержащий ссылку, называется зависимым листом.
Фактическая ссылка называется внешней ссылкой.

В качестве простого примера рассмотрим следующее.

Открыты два рабочих листа: Фактические данные за месяц и Годовой бюджет.

Рабочий лист Годовой бюджет содержит следующую внешнюю ссылку: =MonthlyActuals!$B$6:$B$7.

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

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

Связывание диапазона ячеек

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

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

Вариант 1. Использование вставки ссылки

<р>1. В исходном листе выберите нужные ячейки.

<р>2. Скопируйте выбранные данные, например. CTRL + C или щелкните правой кнопкой мыши, Копировать.

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

<р>4. Чтобы вставить ссылку, выполните одно из следующих действий:

Щелкните правой кнопкой мыши место, куда вы хотите вставить ссылку, и выберите в контекстном меню пункт «Вставить ссылку».
На вкладке "Главная" в группе "Буфер обмена" нажмите стрелку под параметром "Вставить" и выберите "Вставить ссылку".

<р>5. Данные будут вставлены в виде ссылки на исходный лист.

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

Вариант 2. Создайте ссылку вручную

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

<р>1. На зависимом листе выберите ячейку для хранения связанных данных, а затем введите «равно» (=).

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

<р>3. Нажмите ВВОД.

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

Идентификация связанных ячеек

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

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

Был ли этот блог полезен? Дайте нам знать в комментариях ниже.

Если вам понравился этот пост, ознакомьтесь с похожими постами ниже.

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

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

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

Зачем связывать данные электронной таблицы?

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

Как работает связывание данных в электронной таблице

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

Синтаксис формулы ссылки: =SheetName ! CellReference и указывает рабочий лист и ячейку. В приведенном ниже примере мы ввели =Toronto!B6 в целевой лист, чтобы отобразить единицы, проданные из электронной таблицы Торонто.

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

Как создать ссылку на рабочий лист

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

✦ Связать данные рабочего листа — первый метод ✦

Начать на целевом листе и закончить на исходном листе.

На целевом листе щелкните ячейку, которая будет содержать формулу ссылки, и введите знак равенства , но НЕ нажимайте клавишу ВВОД (рис. 1).
На исходном листе щелкните ячейку с данными для связывания (рис. 2) и нажмите клавишу ВВОД. Excel возвращается к целевому листу и отображает связанные данные. Excel создает формулу ссылки с относительными ссылками на ячейки: =Paris!B6 .

✦ Связать данные рабочего листа — метод второй ✦

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

В исходном листе скопируйте* данные, которые нужно связать. Как и в приведенном выше примере, мы вводим значение ячейки B6 из рабочего листа Paris.
На целевом листе щелкните ячейку, в которую вы хотите ввести формулу ссылки, и выберите «Вставить» ⇒ «Вставить ссылку» на вкладке «Главная» — см. рис. 3. (Старые версии Excel: «Правка» ⇒ «Специальная вставка» ⇒ «Вставить ссылку»).
На целевом рабочем листе отображается значение формулы, а формула ссылки отображается в строке формул (рис. 4). С помощью этого метода Excel создает формулу с абсолютной ссылкой на ячейку: =Paris!$B$6 .
Вернитесь к исходному листу и нажмите клавишу ESC, чтобы удалить анимированную рамку.

* Способы копирования: 1) сочетание клавиш Ctrl + C , 2) контекстное меню, 3) кнопка копирования на вкладке "Главная" ленты.

✦ Написание формул вручную — способ 3 ✦

Вводить формулы вручную несложно: знак равенства, имя листа, восклицательный знак и ссылка на ячейку: =SheetName ! Ссылка на ячейку. Если вам нужно ввести несколько формул, откройте файл Блокнота, введите формулу, а затем скопируйте, вставьте и измените ее.

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

В связывающих формулах пробелы или специальные символы должны быть заключены в одинарные кавычки; например ='Розничные цены'!B6 . (Excel автоматически заключает имена рабочих листов C или R в одинарные кавычки.)

Как связать диапазон ячеек

Чтобы связать диапазон ячеек, выберите ячейки и нажмите «Копировать». На целевом листе щелкните ячейку, в которой должна располагаться верхняя левая ячейка диапазона, и нажмите «Вставить» ⇒ «Вставить ссылку» на вкладке «Главная».

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

Связывание с рабочими листами в разных рабочих книгах

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

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

Перемещение листов с внешними ссылками на ячейки

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

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

Убедитесь, что автоматический расчет включен

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

Excel 2007 и более новые версии

Первый способ: в разделе "Расчет" на ленте Excel щелкните маленькую стрелку рядом с параметрами расчета и выберите "Автоматически", как показано на рисунке.

Второй способ. Нажмите «Файл» (или кнопку «Excel») и нажмите «Параметры Excel» в нижней части окна. Слева нажмите "Формулы" и установите флажок "Автоматически" в разделе "Параметры расчета".

Excel 2003 и более ранние версии

В верхней строке меню нажмите "Инструменты", а затем "Параметры" .
Перейдите на вкладку "Расчет" и нажмите "Автоматически" в разделе "Расчет".

Защитите формулы ссылок в своих электронных таблицах

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

Предупреждение безопасности Microsoft Office

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

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

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

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

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

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

Готовы начать?

Вы бы предпочли посмотреть это руководство? Нажмите кнопку воспроизведения ниже!

Как связать ячейки в одной книге

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

Здесь мы нажмем на ячейку C4, в которой мы хотим отобразить продажи за квартал 1. Давайте введем знак равенства, и теперь нам нужно открыть электронную таблицу с информацией, которую мы хотим показать в этой ячейке. . Итак, спускаемся сюда и нажимаем на лист «Продажи-Запад», где собраны все детали продаж западного региона по отдельным кварталам.

Поскольку мы хотим показать только итоги за 1 квартал, мы нажмем ячейку C8, нажмем Enter, и готово!

Ячейка C4 в электронной таблице Totals-West показывает данные из электронной таблицы Sales-West.

Если мы сейчас щелкнем ячейку C4, мы увидим формулу этой ссылки, созданной Excel. Там стоит знак равенства, после чего Excel ставит в одинарные кавычки имя таблицы, из которой мы берем данные — Sales-West. Затем идет восклицательный знак, за которым следуют координаты ячейки, содержащей информацию, которую мы используем, то есть C8.

Как скопировать формулу ссылки в остальные кварталы

Одна вещь, о которой стоит упомянуть. Есть небольшая хитрость, которая поможет вам в кратчайшие сроки перенести данные из других кварталов из электронной таблицы "Продажи-Запад" в остальные ячейки этой строки.

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

Конечно, формулы со ссылками работают динамически, что означает, что если мы внесем изменения в электронную таблицу «Продажи-Запад», например, мы изменим показатель продаж Джона за 1 квартал, общий объем продаж за квартал 1 обновится, и это изменение отразится на листе, содержащем ссылку – «Итоги-Запад».

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

Как связать ячейки в разных таблицах, расположенных в отдельных файлах Excel

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

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

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

Вам будет предоставлен список открытых файлов. Выберите «Продажи-Запад», чтобы просмотреть эту книгу. Здесь мы хотим использовать данные за 1 квартал, поэтому щелкните ячейку C4, нажмите Enter, и поехали!

Excel вернулся к файлу Sales-Totals и включил значение Q1 из файла Sales-West в ячейку C4, как мы и хотели.

Давайте быстро проверим.

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

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

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

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

И мы можем повторить эти шаги, чтобы перенести данные из файла «Продажи-Восток» в файл с итоговыми значениями. Так.

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

Не упустите прекрасную возможность узнать:

Если вы нашли этот урок полезным, поставьте нам лайк и посмотрите другие уроки от EasyClick Academy. Узнайте, как использовать Excel быстро и легко!

Вы впервые пользуетесь EasyClick? Мы будем более чем рады приветствовать вас в нашем онлайн-сообществе. Нажмите кнопку «Подписаться» и присоединяйтесь к EasyClickers!

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