Создание связи между таблицами Excel
Связь между таблицами Excel — это формула, которая возвращает данные из ячейки в другой книге. Когда вы открываете книгу, содержащую ссылки, Excel считывает последнюю информацию из исходной книги (обновление ссылок)
Связи между таблицами в Excel используются для извлечения данных из других листов в книге и из других книг Excel. Например, предположим, что у нас есть таблица с расчетом общей суммы продаж. В расчетах используются цены и продажи продуктов. В этом случае имеет смысл создать отдельную таблицу с ценовыми данными, которые будут отображаться по ссылкам в первой таблице.
При создании связи между таблицами Excel создает формулу, которая включает имя исходной книги, заключенное в квадратные скобки , имя листа с восклицательным знаком в конце и ссылку на ячейку.
Создание связей между рабочими книгами
- Открытие обеих книг в Excel
- В исходной книге выберите ячейку, которую нужно связать, и скопируйте ее (сочетание клавиш Ctrl + C)
- Перейдите в целевую книгу, щелкните правой кнопкой мыши ячейку, в которой мы хотим разместить ссылку. В раскрывающемся меню выберите Специальная вставка
- В появившемся диалоговом окне «Специальная вставка» выберите «Вставить ссылку.
Есть еще один более простой вариант создания связи между таблицами. В ячейку, в которую мы хотим вставить ссылку, мы ставим знак равенства (как и для обычной формулы), переходим в исходную книгу, выбираем ячейку, которую хотим связать, нажимаем Enter.
вы можете использовать инструменты копирования и автозаполнения для связывания формул так же, как и для обычных формул.
Прежде чем создавать связи между таблицами
Прежде чем вы начнете распространять информацию о своих грандиозных идеях, вот несколько советов по работе с отношениями в Excel:
Налаживайте связи, за которыми легко следить. Автоматический поиск всех ячеек, на которые есть ссылки, отсутствует. Поэтому используйте определенный формат для быстрого определения отношений с другими таблицами, в противном случае документ, содержащий ссылки, может разрастись до такой степени, что его будет трудно поддерживать.
Автоматические расчеты. Исходная книга должна быть в автоматическом режиме расчета (установлен по умолчанию). Чтобы изменить вариант расчета, перейдите на вкладку «Формулы» в группе «Расчет». Выберите Параметры расчета -> Автоматически.
Избегайте циклических ссылок. Петли, когда две книги содержат ссылки друг на друга, могут привести к медленному открытию и запуску файла.
Обновление связей
Чтобы вручную обновить связь между таблицами, перейдите на вкладку «Данные» в группе «Подключения». Нажмите кнопку «Изменить ссылки».
В появившемся диалоговом окне «Редактировать ссылки» выберите интересующую вас ссылку и нажмите кнопку «Обновить».
Разорвать связи в книгах Excel
При разрыве ссылки на источник существующие формулы ссылок заменяются возвращаемыми значениями. Например, ссылка = [Source.xlsx] прайс! $ B $ 4 будет заменена на 16. Выход не может быть отменен, поэтому я рекомендую вам сохранить книгу перед выполнением операции.
Перейдите на вкладку «Данные» в группе «Подключения». Нажмите кнопку «Изменить ссылки». В появившемся диалоговом окне «Редактировать ссылки» выберите интересующую вас ссылку и нажмите кнопку «Разорвать ссылку».
Управление обновлением внешних ссылок (связей)
Если вы создаете внешние ссылки на другие книги (также называемые ссылками), вы можете контролировать, когда и когда они будут обновляться. Это важно, потому что, если кто-то другой изменит значение связанной ячейки, Excel не обновит ссылку и не отобразит новое значение, если вы не хотите сообщать его.
Книга, содержащая ссылки, называется целевой книгой, а книга, с которой она связана, называется исходной книгой. Вы можете связать несколько исходных книг с одной целевой книгой.
1. Целевая книга включает внешнюю ссылку (Ссылка).
2. Внешняя ссылка (или ссылка) — это ссылка на ячейку или диапазон в исходной книге.
3. Исходная книга содержит связанную ячейку или диапазон и фактическое значение, возвращаемое в целевую книгу.
Примечание. Хотя количество книг, на которые вы можете ссылаться, не ограничено, обычно рекомендуется проверять несколько как можно чаще. В противном случае при пересчете формул могут возникнуть проблемы с производительностью. Также можно скрещивать книги, каждая из которых выступает в качестве источника и назначения, но это может сбивать с толку, и с этим трудно справиться.
Если исходная и конечная книги открыты на одном компьютере, ссылки обновляются автоматически. Когда целевая книга открыта, а исходная книга не открыта, вам может быть предложено обновить ссылки в области сведений о документе. Вы можете установить режим вывода этого запроса, а также способ обновления всех ссылок без выполнения вывода запроса. Вы также можете обновлять определенные ссылки, только если в книге их несколько.
В следующих разделах обсуждаются наиболее распространенные варианты управления обновлениями ссылок.
Закройте все исходные книги. Если одна исходная книга открыта, а другие закрыты, обновления не будут одинаковыми.
Откройте целевую книгу.
Чтобы обновить ссылки, на панели «Уровень доверия» нажмите кнопку «Обновить». Если вы не хотите обновлять ссылки (найдите X в правой части экрана), закройте панель управления безопасностью.
Откройте книгу, содержащую ссылки.
Перейдите в Данные> Запросы и соединения> Редактировать ссылки.
В списке «Источник» выберите связанный объект, который нужно изменить.
Вы можете выбрать отдельные книги, удерживая клавишу CTRL, или любую из них, используя сочетание клавиш CTRL + A.
Нажмите кнопку «Обновить значения.
запросы и подключения> Редактировать ссылки» />
Найдите книгу, которую хотите использовать в качестве нового источника для внешней ссылки, и запомните ее местоположение.
В целевой книге выберите ячейку внешней ссылки, которую вы хотите изменить.
В панель формул находит ссылку на другую книгу, например, K: report [Budget xlsx], и заменяет ее местоположением новой исходной книги.
Вы можете решить, хотите ли вы обновлять ссылки в этой книге при открытии файла на основе предпочтений пользователя, не обновлять его при открытии файла или автоматически, не спрашивая пользователя.
Предупреждение: этот режим влияет на всех пользователей книги. Если вы отключите обновление ссылок и запросы запросов, пользователи этой книги не узнают, что данные устарели.
Перейдите в Данные> Запросы и соединения> Редактировать ссылки.
Нажмите кнопку «Запрашивать обновление ссылок.
Выберите один из следующих трех вариантов:
Разрешить пользователям выбирать оповещение
Не показывать предупреждения и не обновлять автоматические ссылки
Не показывать предупреждения и ссылки на обновления.
Режим автоматического или ручного обновления: ссылки на формулы всегда устанавливаются на «автоматический».
Даже если вы указали, что Excel не должен запрашивать обновление ссылок, вы все равно будете получать уведомления о неработающих ссылках.
Когда вы открываете диалоговое окно «Редактировать ссылки» (Запрос данных> & Подключения> Редактировать ссылки), у вас есть несколько вариантов работы с существующими ссылками. Вы можете выбрать отдельные книги, удерживая клавишу CTRL, или любую из них, используя сочетание клавиш CTRL + A.
запросы и подключения> Редактировать ссылки» />
Это обновит все выбранные книги.
Этот параметр полезен, если вы хотите указать существующие ссылки на другой источник. Например, вы можете использовать книгу прошлого года, и когда вы начинаете новый год, вам нужно было указать на новую книгу. Если вы выберете «Изменить источник», откроется диалоговое окно «Проводник», позволяющее получить доступ к новой исходной книге. Если книга содержит более одного листа, вам будет предложено указать, какой из них нужно связать, просто щелкнув нужный лист и нажав OK.
Примечание. Книгу можно перенаправить на себя, выбрав ее в диалоговом окне «Редактировать источник». Это свяжет все ссылки на формулы с исходной книгой.
Откроется исходная книга.
Важно: при разрыве ссылок на источник все формулы, использующие этот источник, заменяются текущим значением. Например, ссылка = СУММ ([бюджет. Xlsx] годовой! C10: C25) будет преобразована в сумму значений в исходной книге. Поскольку это действие нельзя отменить, может потребоваться сначала сохранить версию файла.
На вкладке «Данные» в группе «Запросы и подключения» щелкните «Изменить отношения.
В списке «Источник» выберите ссылку, которую хотите разорвать.
Вы можете выбрать отдельные книги, удерживая клавишу CTRL, или любую из них, используя сочетание клавиш CTRL + A.
Щелкните Остановить.
Если ссылка использует определенное имя, оно не удаляется автоматически, поэтому вы также можете удалить его.
Чтобы удалить имя, выполните следующие действия.
Если вы используете диапазон внешних данных, параметр запроса также может использовать данные из другой книги. Возможно, вам потребуется проверить и удалить эти типы ссылок.
На вкладке «Формулы» в группе «Определенные имена» нажмите кнопку «Диспетчер имен.
В столбце «Имя» выберите имя, которое нужно удалить, и нажмите кнопку «Удалить.
Он просто отображает уведомление в области, где были изменены ссылки, независимо от того, действительна ли связанная книга для источника. Он должен отображаться как «ОК», но если нет, вам нужно проверить исходную книгу. Во многих случаях исходную книгу можно переместить или удалить, перерезав ссылку. Если книга все еще существует, вы можете повторно связать книгу с помощью параметра «Изменить источник .
Могу ли я заменить одну формулу вычисленным значением?
Да. Формулы в Excel вместо ввода их значения удаляются безвозвратно. Если вы случайно заменили формулу значением и хотите сбросить формулу, нажмите кнопку Отмена сразу после ввода или вставки значения.
Нажмите CTRL + C, чтобы скопировать формулу.
Нажмите Alt + E + S + V, чтобы вставить формулу как значение, или перейдите на вкладку «Главная»> «Буфер обмена»> «Вставить»> «Вставить значения.
Что делать, если вы не подключены к источнику?
Нажмите кнопку «Не обновлять». Обновление из источника невозможно, если к нему нет связи. Например, если исходная книга находится в сети и нет сетевого подключения.
Я не хочу, чтобы текущие данные были перезаписаны новыми данными
Нажмите кнопку «Не обновлять.
Последнее обновление заняло слишком много времени
Нажмите кнопку «Не обновлять». Если вам не нужно получать текущие данные, вы можете сэкономить время, не обновляя все ссылки. Открыв книгу, на вкладке «Данные» в группе «Подключения» щелкните «Изменить ссылки», а затем обновите ссылки, указав только нужные исходные файлы.
Кто-то другой создал книгу, и я не знаю, почему я вижу этот запрос
Нажмите не обновлять и свяжитесь с владельцем книги. Вы также можете выполнить поиск по ссылкам в книге. На вкладке «Данные» в группе «Запросы и подключения» щелкните «Изменить отношения.
Я могу так же ответить на приглашение и больше не хочу его видеть
Вы можете ответить на запрос и предотвратить его появление в этой книге в будущем.
Не отображать подсказки и автоматически обновлять ссылки
Этот параметр влияет на все открытые книги на вашем компьютере. Пользователи, открывающие книгу на другом компьютере, не пострадают.
Перейдите в> Параметры файла> Дополнительно.
В разделе Общие снимите флажок Попросить обновить автоматические ссылки. Если этот флажок снят, ссылки будут обновляться автоматически (без запроса).
Одинаковая просьба для всех пользователей этой книги
Предупреждение: этот режим влияет на всех пользователей книги. Если вы отключите обновление ссылок и запросы запросов, пользователи этой книги не узнают, что данные устарели.
Перейдите в раздел «Данные»> «Запросы и подключения»> «Редактировать ссылки»> «Выбрать сообщение», затем выберите нужный вариант.
Примечание. Если есть неработающие ссылки, вы увидите уведомление о них.
Что произойдет, если я использую параметризованный запрос?
Нажмите кнопку «Не обновлять».
Закройте целевую книгу.
Откройте целевую книгу.
Щелкните кнопку Обновить.
Невозможно обновить ссылку параметрического запроса, не открыв исходную книгу.
Почему я не могу выбрать ручной вариант для обновления определенной внешней ссылки?
Ссылки на формулы всегда устанавливаются автоматически».
Как изменить связи в Excel
Внешняя ссылка в Excel — это ссылка на ячейку (или диапазон ячеек) в другой книге. На изображениях ниже вы видите книги трех дивизионов (Север, Средний и Юг).
Создание внешней ссылки
Чтобы создать внешнюю ссылку, следуйте инструкциям ниже:
- Откройте все три документа.
- В книге «Компания» выделите ячейку B2 и введите знак равенства «=».
- На вкладке «Вид» нажмите кнопку «Сменить окна» и выберите «Север».
Оповещения
Закройте все документы. Внесите изменения в книги отдела. Снова закройте все документы. Откройте файл «Компания».
- Чтобы обновить все ссылки, нажмите кнопку «Включить контент).
- Чтобы ссылки не обновлялись, нажмите кнопку X.
Примечание. Если вы видите другое предупреждение, нажмите «Обновить» или «Не обновлять).
Чтобы открыть диалоговое окно «Изменить ссылки», на вкладке «Данные» в группе «Подключения» щелкните символ «Изменить ссылки).
- Если вы еще не обновили свои ссылки, вы можете обновить их здесь. Выберите книгу и нажмите кнопку «Обновить значения», чтобы обновить ссылки на эту книгу. Обратите внимание, что статус изменится на ОК.
