Исправление недействительных связей с данными
Если книга содержит ссылку на данные в книге или другом файле, перемещенного в другое место, вы можете исправить эту ссылку, обновив путь к исходный файл. Если вам не удалось найти документ, на который вы изначально ссылались, или нет доступа к нему, можно отключить в Excel обновление ссылки, отключив автоматическое обновление или удалив ссылку.
Важно: связанный объект гиперссылки — это не одно и то же. Следующая процедура не позволит исправить неправиленные гиперссылки. Дополнительные информацию о гиперссылках см. в теме «Создание и изменение гиперссылки».
Исправление неправиленной ссылки
Внимание: Это действие нельзя отменить. Перед началом этой процедуры может потребоваться сохранить резервную копию книги.
- Откройте книгу, которая содержит неверную связь.
- На вкладке «Данные» нажмите кнопку «Изменить связи». Команда «Изменить связи» недоступна, если книга не содержит ссылок.
- В поле «Исходный файл» выберите неправиленную ссылку, которую вы хотите исправить.
Примечание: Чтобы исправить несколько ссылок, щелкните каждую из , удерживая нажатой .
Удаление неявной ссылки
При разрыве связи все формулы, которые ссылаются на исходный файл, преобразуются в их текущее значение. Например, если формула =СУММ([Budget.xls]Годовой! C10:C25) — 45, после того как связь не будет нарушена, формула будет преобразована в 45.
- Откройте книгу, которая содержит неверную ссылку.
- На вкладке «Данные» нажмите кнопку «Изменить связи». Команда «Изменить связи» недоступна, если книга не содержит ссылок.
- В поле «Исходный файл» выберите ненужную ссылку, которую нужно удалить.
Примечание: Чтобы удалить несколько ссылок, щелкните каждую из , удерживая нажатой кнопку мыши.
Важно: связанный объект гиперссылки — это не одно и то же. Следующая процедура не позволит исправить неправиленные гиперссылки. Подробнее о гиперссылках: создание, изменение и удаление гиперссылки
Исправление неправиленной ссылки
Внимание: Это действие нельзя отменить. Перед началом этой процедуры может потребоваться сохранить резервную копию книги.
- Откройте книгу, которая содержит неверную связь.
- В меню Правка выберите пункт Связи. Если книга не содержит ссылок, команда «Ссылки» недоступна.
- В поле «Исходный файл» щелкните неправиленную ссылку, которую нужно исправить.
Примечание: Чтобы исправить несколько ссылок, щелкните каждую из , удерживая нажатой .
| Задача | Необходимые действия |
| Исправление неправиленной ссылки на лист в другой книге | В диалоговом окне «Открытие» найдите книгу и нажмите кнопку «Изменить». |
| Исправление неправиленной ссылки на книгу или другой документ Office | Введите новое имя или расположение документа в поле «Изменить ссылки на текстовое поле» и нажмите кнопку «ОК». |
Отключение автоматического обновления связанных данных
- Откройте книгу, которая содержит неверную связь.
- В меню Правка выберите пункт Связи. Если книга не содержит ссылок, команда «Ссылки» недоступна.
- В поле «Исходный файл» щелкните неправиленную ссылку, которую нужно исправить.
Примечание: Чтобы исправить несколько ссылок, щелкните каждую из , удерживая нажатой .
Удаление неявной ссылки
При разрыве связи все формулы, ссылаясь на исходный файл, преобразуются в их текущее значение. Например, если формула =СУММ([Budget.xls]Годовой! C10:C25) — 45, после того как связь не будет нарушена, формула будет преобразована в 45.
- Откройте книгу, которая содержит неверную связь.
- В меню Правка выберите пункт Связи. Если книга не содержит ссылок, команда «Ссылки» недоступна.
- В поле «Исходный файл» щелкните ненужную ссылку, которую нужно удалить.
Разрыв связи с внешним ресурсом в Excel
- На вкладке Данные в группе Подключения нажмите кнопку Изменить связи.
Примечание: Отсутствие команды Изменить связи означает, что файл не содержит связанных данных.
- Чтобы выбрать несколько связанных объектов, щелкните каждый из них, удерживая нажатой клавишу CTRL.
- Чтобы выделить все связи, нажмите клавиши CTRL+A.
Удаление имени определенной ссылки
Если для ссылки использовался определенный имя, имя не удаляется автоматически. Чтобы удалить имя, с помощью следующих действий:
- На вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.
- В диалоговом окне Диспетчер имен щелкните имя, которое нужно изменить.
- Щелкните имя, чтобы выделить его.
- Нажмите кнопку Удалить.
- Нажмите кнопку ОК.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как разорвать связи в Excel
При выполнении сложных расчетов приходится использовать данные из других книг, тем самым создавая связь. При отправке таких файлов по почте, у получателя «слетают» ссылки. Как этого избежать описано ниже.
Описание проблемы
Когда в формуле вы указываете ссылку на другую книгу, то Excel образует с ней связь. Эта связь будет прекрасно работать и обновляться автоматически в том случае, когда открыты обе книги.
К сожалению, если книга-источник была удалена/перемещена или переименована, то связь нарушится. Также связь будет потеряна если вы переместите конечный файл (содержащий ссылку). Если вы передадите только конечный файл по почте, то получатель тоже не сможет обновить связи.
При нарушении связи, ячейки со ссылками на другие книги будут содержать ошибки #ССЫЛКА.
Как разорвать связь
Один из способов решения данной проблемы — разрыв связи. Если в файле только одна связь, то сделать это довольно просто:
- Перейдите на вкладку Данные.
- Выберите команду Изменить связи в разделе Подключения.
- Нажмите Разорвать связь.
ВАЖНО! При разрыве связи все формулы ссылающиеся на книгу-источник будут преобразованы в значения! Отмена данной операции невозможна!
Как разорвать связь со всеми книгами
Для удобства, можно воспользоваться макросом, который разорвет связи со всеми книгами. Макрос входит в состав надстройки VBA-Excel. Чтобы им воспользоваться необходимо:
- Перейти на вкладку VBA-Excel.
- В меню Связи выбрать команду Разорвать все связи.
Код на VBA
Код макроса удаляющего все связи с книгой представлен ниже. Можете скопировать его в свой проект.
Sub UnlinkWorkBooks() Dim WbLinks Dim i As Long Select Case MsgBox("Все ссылки на другие книги будут удалены из этого файла, а формулы, ссылающиеся на другие книги будут заменены на значения." & vbCrLf & "Вы уверены, что хотите продолжить?", 36, "Разорвать связь?") Case 7 ' Нет Exit Sub End Select WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) If Not IsEmpty(WbLinks) Then For i = 1 To UBound(WbLinks) ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLinks Next Else MsgBox "В данном файле отсутствуют ссылки на другие книги.", 64, "Связи с другими книгами" End If End Sub
Как разорваться связи только в выделенном диапазоне
Иногда в книге имеется много связей и есть опасения, что при удалении связи можно удалить лишнюю. Чтобы этого избежать с помощью надстройки можно удалить связи только в выделенном диапазоне. Для этого:
- Выделите диапазон данных.
- Перейдите на вкладку VBA-Excel (доступна после установки).
- В меню Связи выберите команду Разорвать связи в выделенных ячейках.
Надстройка
VBA-Excel
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.
Задание связей между таблицами в модели данных
Таблицы в модели данных могут иметь несколько связей. Это может быть случай, если вы намеренно создаете дополнительные связи между уже связанными таблицами или импортируете таблицы, которые уже имеют несколько связей, определенных в исходном источнике данных.
Хотя существует несколько связей, в качестве активной текущей связи, которая обеспечивает навигацию по данным и способ вычисления, служит только одна связь. Любые дополнительные связи между парой таблиц считаются неактивными.
Вы можете удалить существующие связи между таблицами, если уверены, что они не нужны, но следует помнить, что это может привести к ошибкам в таблицах или формулах, ссылаемых на эти таблицы. Результаты в стеблях могут неожиданно изменяться после удаления или отключения связи.
Начните с Excel
- Щелкните Данные>Отношения.
- В диалоговом окне Управление связями выберите одну связь из списка.
- Нажмите кнопку Удалить.
- В диалоговом окне предупреждения убедитесь, что вы хотите удалить связь, и нажмите кнопку ОК.
- В диалоговом окне Управление связями нажмите кнопку Закрыть.
Начните с Power Pivot
- Щелкните Главная >схемы.
- Щелкните правой кнопкой мыши линию связи, соединяющую две таблицы, и выберитеудалить . Чтобы выбрать несколько связей, щелкните каждую из них, удерживая нажатой кнопку CTRL.
- В диалоговом окне предупреждения убедитесь, что вы хотите удалить связь, и нажмите кнопку ОК.
- Связи существуют в модели данных. Excel также создает модель при импорте нескольких таблиц или при их подмыве. Вы можете специально создать модель данных для использования в качестве основы для отчетов в сбитых, сводных и power View. Дополнительные сведения см. в статье Создание модели данных в Excel.
- Вы можете откатить удаление в Excel закрыть управление связями и нажать кнопку Отменить. Если вы удаляете связь в Power Pivot, отменить удаление связи нельзя. Связь можно создать повторно, но для выполнения этой действия требуется полный пересчет формул в книге. Поэтому всегда проверяйте перед удалением связи, используемой в формулах. Подробные сведения см. в обзоре связей.
- Функция DATA Analysis Expression (DAX) RELATED использует связи между таблицами для анализа связанных значений в другой таблице. После удаления связи будут возвращены другие результаты. Дополнительные сведения см. в функции RELATED.
- Создание и удаление связей не только изменяет результаты в pivotTable и формулах, но и приводит к повторному пересчету книги, что может занять некоторое время.