Не разрывается связь в excel что делать
Перейти к содержимому

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

  • автор:

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

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

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

Исправление неработающие ссылки

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

  1. Откройте книгу, содержащую неработающую ссылку.
  2. На вкладке Данные щелкните Изменить ссылки. Команда Изменить ссылки недоступна, если книга не содержит ссылок.
  3. В поле Исходный файл выберите неработающую ссылку, которую нужно исправить.

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

и щелкните каждую ссылку.

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

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

  1. Откройте книгу, содержащую неработающую ссылку.
  2. На вкладке Данные щелкните Изменить ссылки. Команда Изменить ссылки недоступна, если книга не содержит ссылок.
  3. В поле Исходный файл выберите неработающую ссылку, которую нужно удалить.

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

и щелкните каждую ссылку.

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

Изменить ссылки

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

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

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

ТБД. Удаление имени определенной ссылки

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

  1. На вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.
  2. В диалоговом окне Диспетчер имен щелкните имя, которое нужно изменить.
  3. Щелкните имя, чтобы выделить его.
  4. Нажмите кнопку Удалить.
  5. Нажмите кнопку ОК.

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

Через стандартную команду «Разорвать связь» в меню данные, ничего не происходит, связь с внешней книгой как висела так и висит. И из списка связей в том же меню данные — не уходит, т.е. вообще ничего не происходит когда щелкаешь «разорвать связь».

Через стандартную команду «Разорвать связь» в меню данные, ничего не происходит, связь с внешней книгой как висела так и висит. И из списка связей в том же меню данные — не уходит, т.е. вообще ничего не происходит когда щелкаешь «разорвать связь». rogert

Сообщение Через стандартную команду «Разорвать связь» в меню данные, ничего не происходит, связь с внешней книгой как висела так и висит. И из списка связей в том же меню данные — не уходит, т.е. вообще ничего не происходит когда щелкаешь «разорвать связь». Автор — rogert
Дата добавления — 10.09.2014 в 11:54

Группа: Проверенные
Ранг: Обитатель
Сообщений: 314
Замечаний: 0% ±

Excel 2016
Это происходит на разных машинах или на одном ПК?
Это происходит на разных машинах или на одном ПК? igrtsk

Сообщение Это происходит на разных машинах или на одном ПК? Автор — igrtsk
Дата добавления — 10.09.2014 в 12:08

Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Замечаний: 40% ±

Excel 2013
Цитата igrtsk, 10.09.2014 в 12:08, в сообщении № 2
Это происходит на разных машинах или на одном ПК?

На разных машинах одно и тоже

Цитата igrtsk, 10.09.2014 в 12:08, в сообщении № 2
Это происходит на разных машинах или на одном ПК?

На разных машинах одно и тоже rogert

Цитата igrtsk, 10.09.2014 в 12:08, в сообщении № 2
Это происходит на разных машинах или на одном ПК?

На разных машинах одно и тоже Автор — rogert
Дата добавления — 10.09.2014 в 12:14

Группа: Проверенные
Ранг: Обитатель
Сообщений: 314
Замечаний: 0% ±

Excel 2016
Тогда без вашего файла не разобраться
Тогда без вашего файла не разобраться igrtsk
Сообщение Тогда без вашего файла не разобраться Автор — igrtsk
Дата добавления — 10.09.2014 в 12:55
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Замечаний: 40% ±

Excel 2013
Цитата igrtsk, 10.09.2014 в 12:55, в сообщении № 4
Тогда без вашего файла не разобраться
Цитата igrtsk, 10.09.2014 в 12:55, в сообщении № 4
Тогда без вашего файла не разобраться
К сообщению приложен файл: RBK_1408_CF_Rep.xlsx (37.4 Kb)
Цитата igrtsk, 10.09.2014 в 12:55, в сообщении № 4
Тогда без вашего файла не разобраться

держите! Автор — rogert
Дата добавления — 10.09.2014 в 13:00
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2525
Замечаний: 0% ±

2010

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

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

Сообщение отредактировал gling — Среда, 10.09.2014, 14:02

Сообщение У меня такое было из за именованных диапазонов. Попробуйте убрать именованные диапазоны и разорвать связи. Сам попробовал не получилось. Автор — gling
Дата добавления — 10.09.2014 в 13:53

Группа: Друзья
Ранг: Ветеран
Сообщений: 1068
Замечаний: 0% ±

XL 2016

на листе Catalog
в ячейках:
C48:C49,E49,E52,C52,C54:C55,E54:E55,E58,C58,E74,C74,C66,E66

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

как быстро найдено:
1. открываем копию книгу винраром
2. находим и удаляем папку «externalLinks»

3. закрываем архиватор
4. открываем файл в Excel
5. Excel ругается, но потом восстанавливает данные, выдавая следующее:
Удаленное свойство: Проверка данных из части /xl/worksheets/sheet7.xml
6. открываем изначальный файл, переходим на 7-й лист и ищем там проверки данных (Ctrl+G, выделить, проверки данных — сначала всех, потом, если их будет несколько, по одному)

на листе Catalog
в ячейках:
C48:C49,E49,E52,C52,C54:C55,E54:E55,E58,C58,E74,C74,C66,E66

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

как быстро найдено:
1. открываем копию книгу винраром
2. находим и удаляем папку «externalLinks»

3. закрываем архиватор
4. открываем файл в Excel
5. Excel ругается, но потом восстанавливает данные, выдавая следующее:
Удаленное свойство: Проверка данных из части /xl/worksheets/sheet7.xml
6. открываем изначальный файл, переходим на 7-й лист и ищем там проверки данных (Ctrl+G, выделить, проверки данных — сначала всех, потом, если их будет несколько, по одному) Саня

К сообщению приложен файл: 9135855.jpg (43.2 Kb)

Сообщение на листе Catalog
в ячейках:
C48:C49,E49,E52,C52,C54:C55,E54:E55,E58,C58,E74,C74,C66,E66

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

как быстро найдено:
1. открываем копию книгу винраром
2. находим и удаляем папку «externalLinks»

3. закрываем архиватор
4. открываем файл в Excel
5. Excel ругается, но потом восстанавливает данные, выдавая следующее:
Удаленное свойство: Проверка данных из части /xl/worksheets/sheet7.xml
6. открываем изначальный файл, переходим на 7-й лист и ищем там проверки данных (Ctrl+G, выделить, проверки данных — сначала всех, потом, если их будет несколько, по одному) Автор — Саня
Дата добавления — 10.09.2014 в 14:13

Невозможно разорвать связи с другой книгой

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

  • [Продажи 2018.xlsx] — обозначает книгу, в которой итоговое значение. Такие книги так же называют источниками
  • Отчет — имя листа в этой книге
  • $A:$F и $A1 — непосредственно ячейка или диапазон со значениями

Если закрыть книгу, на которую была создана такая ссылка, то ссылка сразу изменяется и принимает более «длинный» вид:
=ВПР( A2 ;’C:\Users\Дмитрий\Desktop\[Продажи 2018.xlsx]Отчет’!$A:$F;4;0)
=’C:\Users\Дмитрий\Desktop\[Продажи 2018.xlsx]Отчет’!$A1
Предположу, что большинство такими ссылками не удивишь. Такие ссылки так же принято называть связыванием книг. Поэтому как только создается такая ссылка на вкладке Данные (Data) в группе Запросы и подключения (Queries & Coonections) активируется кнопка Изменить связи (Edit Links) . Там же, как несложно догадаться, их можно изменить. В большинстве случаев ни использование связей, ни их изменение не доставляет особых проблем. Даже если в книге источники были изменены значения ячеек, то при открытии книги со связью эти изменения будут так же автоматом обновлены. Но если книгу-источник переместили или переименовали — при следующем открытии книги со ссылками на неё Excel покажет сообщение о недоступных связях в книге и запрос на обновление этих ссылок:
Запрос на обновление связей
Если нажать Продолжить , то ссылки обновлены не будут и в ячейках будут оставлены значения на момент последнего сохранения. Происходит это потому, что ссылки хранятся внутри самой книги и так же там хранятся значения этих ссылок. Если же нажать Изменить связи (Change Source) , то появится окно изменения связей, где можно будет выбрать каждую связь и указать правильное расположение нужного файла:
Изменение связей
Так же изменение связей доступно непосредственно из вкладки Данные (Data)

Изменение связей

Как разорвать связи
Как правило связи редко нужны на продолжительное время, т.к. они неизбежно увеличивают размер файла, особенно, если связей много. Поэтому чаще всего связь создается только для единовременно получения данных из другой книги. Исключениями являются случаи, когда связи делаются на общий файл, который ежедневного изменяется и дополняется различными сотрудниками и подразделениями, а в итоговом файле необходимо использовать именно актуальные данные этого файла.
Если решили разорвать связь, необходимо перейти на вкладку Данные (Data) -группа Запросы и подключения (Queries & Coonections)Изменить связи (Edit Links) :

Выделить нужные связи и нажать Разорвать связь (Break Link) . При этом все ячейки с формулами, содержащими связи, будут преобразованы в значения вычисленные этой формулой при последнем обновлении. Данное действие нельзя будет отменить — только закрытием книги без сохранения.
Так же связи внутри формул разрываются, если формулы просто заменить значениями -выделяем нужные ячейки -копируем их -не снимая выделения жмем Правую кнопку мышиСпециальная вставка (Paste Special)Значения (Values) . Формулы в ячейках будут заменены результатами их вычислений, а все связи будут удалены.
Более подробно про замену формул значениями можно узнать из статьи: Как удалить в ячейке формулу, оставив значения?

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

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

Редактировать правила в УФ

  • проверьте нет ли каких-либо связей в именованных диапазонах:
    нажмите сочетание клавиш Ctrl + F3 или перейдите на вкладку Формулы (Formulas)Диспетчер имен (Name Manager)
    Читать подробнее про именованные диапазоны
    Если в каком-либо имени есть ссылка с полным путем к какой-то книге(вроде такого ‘[Продажи 2018.xlsx]Отчет’!$A1 ), то такое имя надо либо изменить, либо удалить. Кстати, некоторые имена в итоге могут выдавать ошибку #ССЫЛКА! (#REF!) — к ним тоже стоит присмотреться. Имена с ошибками ничего хорошего как правило не делают.
    Настоятельно рекомендую перед удалением имен создать резервную копию файла, т.к. неверное удаление таких имен может повлечь неправильную работу файла даже в случае, если сами ссылки возвращали в итоге ошибочное значение.
  • если удаление лишних имен не дает эффекта — проверьте условное форматирование:
    вкладка Главная (Home)Условное форматирование (Conditional formatting)Управление правилами (Manage Rules) . В выпадающем списке проверить каждый лист и условия в нем:

    Может случиться так, что условие было создано с использованием ссылки на другие книги. Как правило Excel запрещает это делать, но если ссылка будет внутри какого-то именованного диапазона — то диапазон такой можно будет применить в УФ, но после его удаления в самом УФ это имя все равно остается и генерирует ссылку на файл-источник. Такие условия можно удалять без сомнений — они все равно уже не выполняются как положено и лишь создают «пустую» связь.

  • Так же не помешает проверить наличие лишних ссылок и среди проверки данных(Что такое проверка данных). Как правило связи могут быть в проверке данных с типом Список. Но как их отыскать, если проверка данных распространена на множество ячеек?
    Находим все ячейки с проверкой данных: выделяем одну любую ячейку на листе -вкладка Главная (Home) -группа Редактирование (Editing)Найти и выделить (Find & Select)Выделить группу ячеек (Go to Special) . Отмечаем Проверка данных (Data validation)Всех (All) . Жмем Ок. После этого можно выделить все эти ячейки каким-либо цветом, чтобы удобнее было потом просматривать. Но такой метод выделит ВСЕ ячейки с проверками данных, а не только ошибочные.
    Конечно, если вариантов кроме как найти руками нет и ячеек немного – просто заходим в проверку данных каждой ячейки(выделяем эту ячейку -вкладка Данные (Data)Проверка данных (Data validation) ) и смотрим, есть ли там проблемная формула со ссылками на другие книги.
    Можно поступить более кардинально – после того как выделили все ячейки с проверкой данных идем на вкладку Данные (Data)Проверка данных (Data validation) и для всех ячеек в поле Тип данных (Allow) выбираем Любое значение (Any value) . Это удалит все формулы из проверки данных всех ячеек.
    Но если ни удаление всех проверок данных, ни проверка каждой ячейки не подходит — я предлагаю коротенький код, который отыщет все такие ссылки быстрее и сэкономит время:

Option Explicit '--------------------------------------------------------------------------------------- ' Author : The_Prist(Щербаков Дмитрий) ' Профессиональная разработка приложений для MS Office любой сложности ' Проведение тренингов по MS Excel ' https://www.excel-vba.ru ' info@excel-vba.ru ' WebMoney - R298726502453; Яндекс.Деньги - 41001332272872 ' Purpose: '--------------------------------------------------------------------------------------- Sub FindErrLink() 'надо посмотреть в Данные -Изменить связи ссылку на файл-иточник 'и записать сюда ключевые слова в нижнем регистре(часть имени файла) 'звездочка просто заменяет любое кол-во символов, чтобы не париться с точным названием 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" Exit Sub End If On Error GoTo 0 'проверяем каждую ячейку на предмет наличия связей For Each rc In rr 'на всякий случай пропускаем ошибки - такое тоже может быть 'но наши связи должны быть без них и они точно отыщутся s = "" On Error Resume Next s = rc.Validation.Formula1 On Error GoTo 0 'нашли - собираем все в отдельный диапазон If LCase(s) Like sToFndLink Then If rres Is Nothing Then Set rres = rc Else Set rres = Union(rc, rres) End If End If Next 'если связь есть - выделяем все ячейки с такими проверками данных If Not rres Is Nothing Then rres.Select ' rres.Interior.Color = vbRed 'если надо выделить еще и цветом End If End Sub

Option Explicit ‘————————————————————————————— ‘ Author : The_Prist(Щербаков Дмитрий) ‘ Профессиональная разработка приложений для MS Office любой сложности ‘ Проведение тренингов по MS Excel ‘ https://www.excel-vba.ru ‘ info@excel-vba.ru ‘ WebMoney — R298726502453; Яндекс.Деньги — 41001332272872 ‘ Purpose: ‘————————————————————————————— Sub FindErrLink() ‘надо посмотреть в Данные -Изменить связи ссылку на файл-иточник ‘и записать сюда ключевые слова в нижнем регистре(часть имени файла) ‘звездочка просто заменяет любое кол-во символов, чтобы не париться с точным названием 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» Exit Sub End If On Error GoTo 0 ‘проверяем каждую ячейку на предмет наличия связей For Each rc In rr ‘на всякий случай пропускаем ошибки — такое тоже может быть ‘но наши связи должны быть без них и они точно отыщутся s = «» On Error Resume Next s = rc.Validation.Formula1 On Error GoTo 0 ‘нашли — собираем все в отдельный диапазон If LCase(s) Like sToFndLink Then If rres Is Nothing Then Set rres = rc Else Set rres = Union(rc, rres) End If End If Next ‘если связь есть — выделяем все ячейки с такими проверками данных If Not rres Is Nothing Then rres.Select ‘ rres.Interior.Color = vbRed ‘если надо выделить еще и цветом End If End Sub

  • Прежде чем искать ненужную связь необходимо определить её ссылку: Данные (Data) -группа Запросы и подключения (Queries & Coonections)Изменить связи (Edit Links) . Запомнить имя файла и записать в этой строке внутри кавычек:
    Const sToFndLink$ = «*продажи 2018*»
    Имя файла можно записать не полностью, все пробелы и другие символы можно заменить звездочкой дабы не ошибиться. Текст внутри кавычек должен быть в нижнем регистре. Например, на картинках выше есть связь с файлом «Продажи 2018.xlsx» , но я внутри кода записал «*продажи 2018*» — будет найдена любая связь, в имени которой есть «продажи 2018» .
  • Код ищет проверки данных только на активном листе
  • Код только выделяет все найденные ячейки(обычное выделение), он ничего сам не удаляет
  • Если надо подсветить ячейки цветом — достаточно убрать апостроф(‘) перед строкой
    rres.Interior.Color = vbRed ‘если надо выделить еще и цветом

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

  1. Обязательно делаем резервную копию файла, связи в котором никак не хотят разрываться
  2. Открываем файл при помощи любого архиватора(WinRAR отлично справляется, но это может быть и другой, работающий с форматом ZIP)
  3. В архиве перейти в папку xl ->externalLinks
  4. Сколько связей содержится в файле, столько файлов вида externalLink1.xml и будет внутри. Файлы просто пронумерованы и никаких сведений о том, к какому конкретному файлу относится эта связь на поверхности нет. Чтобы узнать какой файл .xml к какой связи относится надо зайти в папку «_rels» и открыть там каждый из имеющихся файлов вида externalLink1.xml.rels . Там и будет содержаться имя файла-источника.
  5. Если надо удалить только связь на конкретный файл — удаляем только те externalLink1.xml.rels и externalLink1.xml , которые относятся к нему. Если удалить надо все связи — удаляем все содержимое папки externalLinks
  6. Закрываем архив
  7. Открываем файл в Excel. Появится сообщение об ошибке вроде «Ошибка в части содержимого в Книге . «. Соглашаемся. Появится еще одно окно с перечислением ошибочного содержимого. Нажимаем закрыть.

После этого связи должны быть удалены.
Если и это не помогло — скорее всего «битая» связь связана с ошибкой самого файла и лучшим решением будет перенести все данные в новый файл.

Статья помогла? Поделись ссылкой с друзьями!

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *