Как обновить связи в excel
На этом шаге мы закончим рассматривать принципы работы со связанными объектами.
Редактирование связанного объекта
Редактирование связанной информации выполняется в исходном приложении. Простейший способ открытия исходного приложения - двойной щелчок на связанной информации в документе Word :
Рис.1. Открытие приложения Excel для редактирования связанной таблицы
Для открытия исходного приложения можно также воспользоваться кнопкой Открыть источник диалогового окна Связи (команда Связи меню Правка ):
Рис.2. Диалоговое окно Связи
Обновление связи
По умолчанию (рисунок 2) задано автоматическое обновление связи (активизируется переключатель автоматически в области Способ обновления связи диалогового окна Связи ), т.е. при изменении информации в исходном документе (таблице Excel ) она обновляется и в документе Word , если он открыт. Связи обновляются автоматически и при открытии документа Word . Автоматическое обновление данных можно отменить в диалоговом окне Связи , которое открывается при вызове одноименной команды меню Правка редактора Word . Диалоговое окно содержит список всех связей активного документа Word . Чтобы обновление выполнялось только при нажатии кнопки Обновить , нужно выделить в списке связь и установить переключатель по запросу .
Примечание . Чтобы выделить несколько связей, необходимо при их выборе удерживать нажатой клавишу Ctrl . После активизации переключателя по запросу связи обновляются только при нажатии кнопки Обновить в диалоговом окне Связи .
Команда Связи меню Правка недоступна, если документ не содержит связанной информации.
Чтобы предотвратить обновление связанной информации, следует заблокировать связь. Для этого необходимо, находясь в диалоговом окне Связи , выделить связь или связи, установить опцию Не обновлять связь и нажать кнопку ОК . Блокировка связи не означает ее разрыв. Для ее отмены достаточно выключить опцию Не обновлять связь в окне Связи и нажать кнопку ОК .
Разрыв связи
Для разрыва связи предназначена кнопка Разорвать связь диалогового окна Связи . После нажатия этой кнопки необходимо подтвердить разрыв связи в открывшемся окне запроса. После разрыва связи связанная информация остается в документе Word , однако она уже не может обновляться. Восстановить связь нельзя!
Изменение связи
Связь может быть разорвана и при переименовании или удалении документа-источника. В этом случае необходимо повторно установить связь с документом-источником или установить связь с другим документом. Чтобы изменить или восстановить связь, надлежит нажать кнопку Сменить источник диалогового окна Связи . В результате откроется диалоговое окно Сменить источник - стандартное окно открытия документов системы Windows , где нужно выбрать документ, с которым восстанавливается связь:
Рис.3. Диалоговое окно Сменить источник
Если нужный документ отсутствует в списке, следует выбрать другой диск или папку.
На следующем шаге мы рассмотрим пример внедрения объекта в документ Word .
При создании внешних ссылок на другие книги (также называемые связями) вы можете управлять тем, обновляются ли они. Это важно, так как если кто-то другой изменяет значение связанной ячейки, Excel не обновляет ссылку и не отображает новое значение, если вы не сообщаете об этом.
Книга, которая содержит связи, называется книгой назначения, а книга, с которую она связана, называется книгой-источником. Вы можете связать несколько исходных книг с одной книгой.
1. Destination workbook contains the external reference (link).
2. Внешняя ссылка (или ссылка) — это ссылка на ячейку или диапазон в книге-источнике.
3. Книга-источник содержит связанную ячейку или диапазон и фактическое значение, возвращаемого в 9-й книге.
Примечание: Количество книг, на которые можно ссылаться, не ограничивается, но обычно лучше использовать как можно меньше ссылок. В противном случае могут возникнуть проблемы с производительностью при пересчете формул. Кроме того, можно перекрестно связывать книги, где они выступают в качестве источника и назначения, но это может запутать и затруднить управление.
Когда исходные и назначения книг открываются на одном компьютере, связи обновляются автоматически. Когда конечная книга открыта, а книга-источник не открыта, в области сведений о документе возможно появление запроса на обновление связей. Можно задать режим вывода этого запроса, а также режим обновления всех связей без вывода запроса. Вы также можете обновить только определенные ссылки, если книга содержит несколько ссылок.
В следующих разделах рассматриваются наиболее распространенные варианты изменения связей.
Обновление всех связей в книге вручную или без нихЗакроем все исходные книги. Если одна книга-источник будет открыта, а остальные закрыты, обновления не будут одинаковыми.
Откройте конечную книгу.
Чтобы обновить связи, на панели доверия нажмите кнопку Обновить. Закройте планку доверия, если вы не хотите обновлять связи (на правом — X).
Частичное обновление связей с другими книгами вручнуюОткройте книгу, содержащую связи.
Перейдите в >запросы & подключения >ссылки.
Из списка Источник выберите связанный объект, который необходимо изменить.
Вы можете выбрать отдельные книги, нажав CTRL+щелчок, или все их с помощью CTRL+A.
Изменение книги-источника одной внешней ссылки без влияния на другие внешние ссылки в конечной книгеНайдите книгу, которую вы хотите использовать в качестве нового источника для внешней ссылки, и обратите внимание на ее расположение.
В книге назначения выберите ячейку с внешней ссылкой, которую вы хотите изменить.
В строка формул наймем ссылку на другую книгу, например C:\Reports\[Budget.xlsx], и замените ее расположением новой книги-источника.
Управление запросом на обновление ссылок при запускеВы можете решить, обновляются ли ссылки в этой книге при открытии файла в соответствии с настройками пользователя, не обновляются при открытии файла или обновляются автоматически без запроса пользователя.
Предупреждение: Этот режим влияет на всех пользователей книги. Если отключить обновление связей и выдачу запроса, пользователи этой книги не будут знать, что данные устарели.
Перейдите в >запросы & подключения >ссылки.
Выберите один из следующих трех вариантов:
Разрешим пользователям отображать оповещение или нет
Не показывать оповещение и не обновлять автоматические ссылки
Не отображайте ссылки на оповещения и обновления.
Параметры автоматического и ручного обновления. Для ссылок на формулы всегда установлено автоматическое обновление.
Даже если указать, что Excel не должен спрашивать, следует ли обновлять ссылки, вы по-прежнему будете уведомлены об их неработаете.
Открыв диалоговое окно Изменение связей (Запросы > данных & connections > Изменениесвязей), вы сможете работать с существующими ссылками несколькими вариантами. Вы можете выбрать отдельные книги, нажав CTRL+щелчок, или все их с помощью CTRL+A.
Обновление значений
При этом будут обновлены все выбранные книги.
Изменить источник
Этот параметр удобен, если нужно наказать существующие ссылки на другой источник. Например, у вас может быть книга за предыдущий год, и вам нужно будет навести указатель на новую книгу, когда начинается новый год. Если выбрать изменить источник, запустится диалоговое окно проводника, в котором можно найти новую книгу. Если книга состоит из двух листов, вам будет предложено указать, на какой из них должна указывать ссылка. Просто щелкните нужный лист и нажмите кнопку ОК.
Примечание: Вы можете наказать книгу на себя, выбрав ее в диалоговом оке Изменение источника. В этом случае любая формула будет отсоединяться от исходной исходной книги.
Open Source
Разрыв связи
Важно: При разрыве связей с источником все формулы, использующие источник, заменяются на их текущее значение. Например, ссылка =СУММ([Budget.xlsx]Годовой! C10:C25) будет преобразовано в сумму значений в книге-источнике. Так как это действие нельзя отменить, может потребоваться сначала сохранить версию файла.
На вкладке Данные в группе Запросы & Connections нажмитекнопку Изменить связи.
В списке Источник выберите связь, которую требуется разорвать.
Вы можете выбрать отдельные книги, нажав CTRL+щелчок, или все их с помощью CTRL+A.
Щелкните элемент Разорвать.
Если ссылка использовала определенное имя, оно не удаляется автоматически, поэтому вы также можете удалить его.
Чтобы удалить имя:
Если используется диапазон внешних данных, параметр запроса может также использовать данные из другой книги. Может потребоваться проверить и удалить эти типы связей.
На вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.
В столбце Имя выберите имя, которое следует удалить, и нажмите кнопку Удалить.
Проверка состояния
В области Изменение связей просто отображается уведомление о том, является ли связанная книга допустимым источником. Должно отображаться "ОК",но если этого не происходит, необходимо проверить исходные книги. Во многих случаях при вырезании ссылки может быть перемещена или удалена книга-источник. Если книга по-прежнему существует, вы можете использовать параметр Изменить источник, чтобы перессылка книги.
Можно ли заменить одну формулу ее вычисляемой величиной?
Да. Формулы в Excel, вместо которых вводится их значение, удаляются без возможности восстановления. Если вы случайно заменяете формулу значением и хотите ее восстановить, нажмите кнопку Отменить сразу после ввода или вжатия значения.
Нажмите CTRL+C, чтобы скопировать формулу.
Нажмите ALT+E+S+V, чтобы вировать формулу в качестве ее значения, или перейдите на главная> буфер обмена> в > Вэтом > в буфер обмена .
Что делать, если я не подключен к источнику?
Я не хочу заменять текущие данные новыми
Я пытался обновить его в прошлый раз, но это занимает слишком много времени
Кто-то другой создал книгу, и я не знаю, почему я вижу этот запрос
Щелкните Не обновлятьи обратитесь к владельцу книги. Вы также можете выяснить, какие связи находятся в книге. На вкладке Данные в группе Запросы & Connections нажмитекнопку Изменить связи.
Я отвечаю на запрос одинаково каждый раз и не хочу видеть его снова
Можно ответить на запрос и запретить его вывод для этой книги в будущем.
Не отображать запрос и обновлять связи автоматически
Этот параметр влияет на все книги, которые открываются на компьютере. Пользователи, которые откроют книгу на другом компьютере, не будут затронуты.
Откройте меню Файл, выберите раздел Параметры, а затем — Дополнительно.
В разделе Общие снимите флажок Запрашивать об обновлении автоматических связей. Если этот флажок снят, связи будут обновляться автоматически (без выдачи запроса).
Одинаковый запрос для всех пользователей этой книги
Предупреждение: Этот режим влияет на всех пользователей книги. Если отключить обновление связей и выдачу запроса, пользователи этой книги не будут знать, что данные устарели.
Перейдите в > запросы & подключения > Изменить ссылки > запросана запуск и выберите нужный параметр.)
Примечание: При наличии разорванных связей будет появляться оповещение об этом.
Что делать, если я использую запрос с параметрами?
Закройте конечную книгу.
Откройте конечную книгу.
Связь с параметрическим запросом нельзя обновить без открытия книги-источника.
Почему я не могу выбрать параметр "Вручную" в качестве параметра обновления для определенной внешней ссылки?
Для ссылок на формулы всегда установлено автоматическое.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Рис. 1.12. Влияющие ячейки
Чтобы наглядно увидеть, на какие другие ячейки влияет значение какой-либо из ячеек, следует установить на нее табличный курсор и на вкладке Формулы ленты инструментов нажать кнопку Зависимые ячейки. В результате от ячейки с формулой отойдут стрелочки, указывающие на зависимые ячейки (рис. 1.13). Необходимо иметь в виду, что связи показываются только с теми ячейками, на которые впрямую влияет значение выбранной ячейки. Связь не отображается в случае косвенного влияния, когда первая ячейка влияет на вторую, а вторая влияет на третью. В этом случае первая ячейка косвенно влияет на значение в третьей ячейке, но связь в таком случае не отображается.
Рис. 1.13. Зависимые ячейки
Подобные связи можно отобразить сразу для нескольких ячеек, построив таким образом своеобразную карту взаимосвязей ячеек таблицы. При этом можно одновременно отображать связи разного тина (и влияющие, и зависимые ячейки).
Чтобы убрать с экрана отображенные связи, на вкладке Формулы ленты инструментов достаточно нажать кнопку Убрать стрелки. В результате будут скрыты все отображенные ранее связи. В том случае, если требуется скрыть связи только определенного типа (иллюстрирующие влияющие связи или зависимые), следует щелкнуть мышкой по стрелочке, расположенной рядом с кнопкой Убрать стрелки, и в появившемся меню выбрать, какие именно стрелки необходимо убрать (рис. 1.14).
Рис. 1.14. Сокрытие ненужных стрелок
Если ваша версия Excel на английском языке, то вам стоить посетить курсы, которые открыла школа английского языка и несомненными преимуществами которой являются следующие: соответствие школьной программе обучения, простота, наглядность и доступность изложения материала, большое количество и разнообразие упражнений для практики, наличие упражнений разной степени сложности, универсальность использования вне зависимости от базового учебника, соответствие возрастным особенностям и возможностям учащихся.
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Связь между таблицами Excel – это формула, которая возвращает данные с ячейки другой рабочей книги. Когда вы открываете книгу, содержащую связи, Excel считывает последнюю информацию с книги-источника (обновление связей)
Межтабличные связи в Excel используются для получения данных как с других листов рабочей книги, так и с других рабочих книг Excel. К примеру, у вас имеется таблица с расчетом итоговой суммы продаж. В расчете используются цены на продукт и объем продаж. В таком случае имеет смысл создать отдельную таблицу с данными по ценам, которые будут подтягиваться с помощью связей первой таблицы.
Когда вы создаете связь между таблицами, Excel создает формулу, которая включает в себя имя исходной книги, заключенную в скобки [], имя листа с восклицательным знаком на конце и ссылку на ячейку.
Создание связей между рабочими книгами
- Открываем обе рабочие книги в Excel
- В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+С)
- Переходим в конечную книгу, щелкаем правой кнопкой мыши по ячейке, куда мы хотим поместить связь. Из выпадающего меню выбираем Специальная вставка
- В появившемся диалоговом окне Специальная вставка выбираем Вставить связь.
Есть еще один, более простой, вариант создания связи между таблицами. В ячейку, куда мы хотим вставить связь, ставим знак равенства (так же как и для обычной формулы), переходим в исходную книгу, выбираем ячейку, которую хотим связать, щелкаем Enter.
Вы можете использовать инструменты копирования и автозаполнения для формул связи так же, как и для обычных формул.
Прежде чем создавать связи между таблицами
Прежде чем вы начнете распространять знания на свои грандиозные идеи, прочитайте несколько советов по работе со связями в Excel:
Делайте легко отслеживаемые связи. Не существует автоматического поиска всех ячеек, содержащих ссылки. Поэтому, используйте определенный формат, чтобы быстро идентифицировать связи с другими таблицами, в противном случае, документ, содержащий ссылки, может разрастись до такой степени, что его будет трудно поддерживать.
Автоматические вычисления. Исходная книга должна работать в режиме автоматического вычисления (установлено по умолчанию). Для переключения параметра вычисления перейдите по вкладке Формулы в группу Вычисление. Выберите Параметры вычислений –> Автоматически.
Избегайте циклические ссылки. Циклические связи – когда две рабочие книги содержат ссылки друг на друга – могут быть причиной медленного открытия и работы файла.
Обновление связей
Для ручного обновления связи между таблицами, перейдите по вкладке Данные в группу Подключения. Щелкните по кнопке Изменить связи.
В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Обновить.
Разорвать связи в книгах Excel
Разрыв связи с источником приведет к замене существующих формул связи на значения, которые они возвращают. Например, связь =[Источник.xlsx]Цены!$B$4 будет заменена на 16. Разрыв связи нельзя отменить, поэтому прежде чем совершить операцию, рекомендую сохранить книгу.
Перейдите по вкладке Данные в группу Подключения. Щелкните по кнопке Изменить связи. В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Разорвать связь.
Вам также могут быть интересны следующие статьи
7 комментариев
Спасибо! очень полезный материал!
Читайте также: