не разрывается связь в excel что делать

Разрыв связи с внешним ресурсом в Excel

При разрыве связи с исходной книгой (внешним источником) все формулы, использующие значения в этой книге, преобразуются в свои текущие значения. Например, если разорвать связь с внешней ссылкой =СУММ([Budget.xls]Годовой! C10:C25), формула СУММ заменяется вычисляемой величиной, каким бы ни была формула. Кроме того, так как это действие нельзяотменить, вы можете сохранить версию конечной книги в качестве резервной копии.

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

Разрыв связи

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

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

В списке Источник выберите связь, которую требуется разорвать.

Чтобы выбрать несколько связанных объектов, щелкните каждый из них, удерживая нажатой клавишу CTRL.

Чтобы выделить все связи, нажмите клавиши CTRL+A.

Щелкните элемент Разорвать.

Удаление имени определенной ссылки

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

На вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.

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

Щелкните имя, чтобы выделить его.

Нажмите кнопку Удалить.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Устранение неполадок в связях между таблицами

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

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

Сообщение. Связи не были обнаружены

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

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

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

В сводную таблицу добавлены несвязанные поля, однако сообщение не выдается

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

Отсутствует допустимая связь между таблицами

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

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

При автоматическом обнаружении созданы неверные связи

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

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

Источник

Исправление недействительных связей с данными

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

Важно: связанный объект гиперссылки — это не одно и то же. Следующая процедура не позволит исправить неправиленные гиперссылки. Дополнительные информацию о гиперссылках см. в теме «Создание и изменение гиперссылки».

Исправление неправиленной ссылки

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

Откройте книгу, которая содержит неверную связь.

На вкладке «Данные» нажмите кнопку «Изменить связи».

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

В поле «Исходный файл» выберите неправиленную ссылку, которую вы хотите исправить.

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

Выберите команду Смена источника.

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

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

Нажмите кнопку Закрыть.

Удаление неявной ссылки

При разрыве связи все формулы, которые ссылаются на исходный файл, преобразуются в их текущее значение. Например, если формула =СУММ ([Budget.xls]Годовой! C10:C25) — 45, после того как связь не будет нарушена, формула будет преобразована в 45.

Откройте книгу, которая содержит неверную ссылку.

На вкладке «Данные» нажмите кнопку «Изменить связи».

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

В поле «Исходный файл» выберите ненужную ссылку, которую нужно удалить.

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

Щелкните элемент Разорвать.

Нажмите кнопку Закрыть.

Важно: связанный объект гиперссылки — это не одно и то же. Следующая процедура не позволит исправить неправиленные гиперссылки. Подробнее о гиперссылках: создание, изменение и удаление гиперссылки

Исправление неправиленной ссылки

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

Откройте книгу, которая содержит неверную связь.

В меню Правка выберите пункт Связи.

Если книга не содержит ссылок, команда «Ссылки» недоступна.

В поле «Исходный файл» щелкните неправиленную ссылку, которую нужно исправить.

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

Выберите команду Смена источника.

Выполните одно из следующих действий:

Исправление неправиленной ссылки на лист в другой книге

В диалоговом окне «Открытие» найдите книгу и нажмите кнопку «Изменить».

Исправление неправиленной ссылки на книгу или другой документ Office

Введите новое имя или расположение документа в поле «Изменить ссылки на текстовое поле» и нажмите кнопку «ОК».

Отключение автоматического обновления связанных данных

Откройте книгу, которая содержит неверную связь.

В меню Правка выберите пункт Связи.

Если книга не содержит ссылок, команда «Ссылки» недоступна.

В поле «Исходный файл» щелкните неправиленную ссылку, которую нужно исправить.

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

Удаление неявной ссылки

При разрыве связи все формулы, ссылаясь на исходный файл, преобразуются в их текущее значение. Например, если формула =СУММ ([Budget.xls]Годовой! C10:C25) — 45, после того как связь не будет нарушена, формула будет преобразована в 45.

Откройте книгу, которая содержит неверную связь.

В меню Правка выберите пункт Связи.

Если книга не содержит ссылок, команда «Ссылки» недоступна.

В поле «Исходный файл» щелкните ненужную ссылку, которую нужно удалить.

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

Источник

Как в Excel разорвать связи

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

Что такое связи в Excel

Связи в Excel очень часто используются вместе с такими функциями, как ВПР, чтобы получить информацию из другой книги. Она может иметь вид специальной ссылки, которая содержит адрес не только ячейки, но и книги, в которой данные расположены. В результате, такая ссылка имеет приблизительно такой вид: =ВПР(A2;'[Продажи 2018.xlsx]Отчет’!$A:$F;4;0). Или же, для более простого представления, представить адрес в следующем виде: ='[Продажи 2018.xlsx]Отчет’!$A1. Разберем каждый из элементов ссылки этого типа:

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

Суть проблемы

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

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

Кроме этого, можно отредактировать связи через соответствующую кнопку, расположенную на вкладке «Данные». О том, что связь нарушена, пользователь может также узнать по ошибке #ССЫЛКА, которая появляется тогда, когда эксель не может получить доступ к информации, расположенной по определенному адресу из-за того, что сам адрес недействительный.

Как разорвать связь в Эксель

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

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

Как разорвать связь со всеми книгами

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

Код на VBA

Если же нет возможности активировать это дополнение, можно создать макрос самостоятельно. Для этого необходимо открыть редактор Visual Basic, нажав на клавиши Alt + F11, и в поле ввода кода записать следующие строки.

Select Case MsgBox(«Все ссылки на другие книги будут удалены из этого файла, а формулы, ссылающиеся на другие книги будут заменены на значения.» & vbCrLf & «Вы уверены, что хотите продолжить?», 36, «Разорвать связь?»)

If Not IsEmpty(WbLinks) Then

For i = 1 To UBound(WbLinks)

ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLinks

MsgBox «В данном файле отсутствуют ссылки на другие книги.», 64, «Связи с другими книгами»

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

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

После этого все связи в выделенном наборе ячеек будут удалены.

Что делать, если связи не разрываются

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

Также можно воспользоваться функцией «Проверка данных», чтобы узнать, нет ли ненужных ссылок. Обычно связи остаются, если используется тип проверки данных «Список». Но что же делать, если ячеек много? Неужели необходимо последовательно проверять каждую из них? Конечно, нет. Ведь это займет очень много времени. Поэтому нужно воспользоваться специальным кодом, чтобы значительно сэкономить его.
Option Explicit

‘ Author : The_Prist(Щербаков Дмитрий)

‘ Профессиональная разработка приложений для MS Office любой сложности

‘ Проведение тренингов по MS Excel

‘ WebMoney — R298726502453; Яндекс.Деньги — 41001332272872

‘и записать сюда ключевые слова в нижнем регистре(часть имени файла)

‘звездочка просто заменяет любое кол-во символов, чтобы не париться с точным названием

Const sToFndLink$ = «*продажи 2018*»

Dim rr As Range, rc As Range, rres As Range, s$

‘определяем все ячейки с проверкой данных

On Error Resume Next

Set rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

If rr Is Nothing Then

MsgBox «На активном листе нет ячеек с проверкой данных», vbInformation, «www.excel-vba.ru»

‘проверяем каждую ячейку на предмет наличия связей

‘на всякий случай пропускаем ошибки — такое тоже может быть

‘но наши связи должны быть без них и они точно отыщутся

On Error Resume Next

‘нашли — собираем все в отдельный диапазон

If LCase(s) Like sToFndLink Then

If rres Is Nothing Then

Set rres = Union(rc, rres)

‘если связь есть — выделяем все ячейки с такими проверками данных

If Not rres Is Nothing Then

‘ rres.Interior.Color = vbRed ‘если надо выделить еще и цветом

Необходимо в редакторе макросов сделать стандартный модуль, а потом туда вставить этот текст. После этого вызвать окно макросов с помощью комбинации клавиш Alt + F8, а потом выбрать наш макрос и кликнуть по кнопке «Выполнить». При использовании этого кода есть несколько моментов, которые надо учитывать:

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

Источник