Как установить связь между листами в excel

Обновлено: 05.10.2024

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

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

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

Создание связей между рабочими книгами

Открываем обе рабочие книги в Excel
В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+С)
Переходим в конечную книгу, щелкаем правой кнопкой мыши по ячейке, куда мы хотим поместить связь. Из выпадающего меню выбираем Специальная вставка
В появившемся диалоговом окне Специальная вставка выбираем Вставить связь.

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

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

Прежде чем создавать связи между таблицами

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

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

Автоматические вычисления. Исходная книга должна работать в режиме автоматического вычисления (установлено по умолчанию). Для переключения параметра вычисления перейдите по вкладке Формулы в группу Вычисление. Выберите Параметры вычислений –> Автоматически.

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

Обновление связей

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

В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Обновить.

Разорвать связи в книгах Excel

Разрыв связи с источником приведет к замене существующих формул связи на значения, которые они возвращают. Например, связь =[Источник.xlsx]Цены!$B$4 будет заменена на 16. Разрыв связи нельзя отменить, поэтому прежде чем совершить операцию, рекомендую сохранить книгу.

Перейдите по вкладке Данные в группу Подключения. Щелкните по кнопке Изменить связи. В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Разорвать связь.

Вам также могут быть интересны следующие статьи

8 комментариев

Спасибо! очень полезный материал!

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

Команда контент-менеджеров wikiHow тщательно следит за работой редакторов, чтобы гарантировать соответствие каждой статьи нашим высоким стандартам качества.

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

Перейдите на целевой лист. Список листов отображается внизу таблицы. Щелкните по листу, который будет связан с другим листом.

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

Также эту формулу можно ввести вручную. Она должна выглядеть так: = ! , где вместо подставьте имя исходного листа.

Например, если вы извлекаете данные из ячейки D12 на листе Лист1, формула будет выглядеть так: =Лист1!D12 .

Нажмите ↵ Enter на клавиатуре. Формула будет активирована, а вы перейдете на целевой лист. Теперь целевая ячейка связана с исходной ячейкой и автоматически извлекает из нее данные. Каждый раз, когда меняется значение в исходной ячейке, значение в целевой ячейке будет обновлено.

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

Связанные таблицы очень удобно использовать для обработки большого объема информации. Располагать всю информацию в одной таблице, к тому же, если она не однородная, не очень удобно. С подобными объектами трудно работать и производить по ним поиск. Указанную проблему как раз призваны устранить связанные таблицы, информация между которыми распределена, но в то же время является взаимосвязанной. Связанные табличные диапазоны могут находиться не только в пределах одного листа или одной книги, но и располагаться в отдельных книгах (файлах). Последние два варианта на практике используют чаще всего, так как целью указанной технологии является как раз уйти от скопления данных, а нагромождение их на одной странице принципиально проблему не решает. Давайте узнаем, как создавать и как работать с таким видом управления данными.

Создание связанных таблиц

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

Способ 1: прямое связывание таблиц формулой

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

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

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

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

Способ 2: использование связки операторов ИНДЕКС — ПОИСКПОЗ

Но что делать, если перечень сотрудников в табличных массивах расположен не в одинаковом порядке? В этом случае, как говорилось ранее, одним из вариантов является установка связи между каждой из тех ячеек, которые следует связать, вручную. Но это подойдет разве что для небольших таблиц. Для массивных диапазонов подобный вариант в лучшем случае отнимет очень много времени на реализацию, а в худшем – на практике вообще будет неосуществим. Но решить данную проблему можно при помощи связки операторов ИНДЕКС – ПОИСКПОЗ. Посмотрим, как это можно осуществить, связав данные в табличных диапазонах, о которых шел разговор в предыдущем способе.

Способ 3: выполнение математических операций со связанными данными

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

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

Способ 4: специальная вставка

Связать табличные массивы в Excel можно также при помощи специальной вставки.

Способ 5: связь между таблицами в нескольких книгах

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

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

Разрыв связи между таблицами

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

Способ 1: разрыв связи между книгами

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

Способ 2: вставка значений

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В исходной вкладке с данными (Лист1), выделите ячейки, на которые вы хотите сослаться.
Скопируйте ячейки (Ctrl/команда + С, или щелкните правой кнопкой мыши и выберите копия).
Перейти на другую вкладку (Sheet2) и нажмите на ячейку (или ячейки), где вы хотите разместить ссылки.

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

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

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

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

У вас будет = SUM (Лист1 C3: C7)где функция SUM суммирует содержимое ячеек C3: C7 в Sheet1. Нажмите Войти завершить формулу.

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

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

Откройте оба документа Excel.
Во втором файле (Мастер службы поддержки), выберите ячейку и введите символ равенства знак равно
Переключиться на оригинальный файл (Интернет Tech Советы) и нажмите на ячейку, на которую хотите сослаться.
Нажмите Войти завершить формулу.

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

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

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

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

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

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