В excel не удается обновить одну или несколько связей в книге excel
Обновлено: 08.07.2024
Связь – это очень полезная возможность Excel. Ведь очень часто пользователям приходится использовать информацию из других файлов. Но в некоторых ситуациях они способны скорее нанести вред, чем принести пользу. Ведь, к примеру, если отправлять эти файлы по почте, ссылки оказываются нерабочими. Сегодня мы более подробно поговорим о том, что сделать для избежания такой проблемы.
Что такое связи в Excel
- [Продажи 2018.xlsx]. Этот фрагмент содержит ссылку на файл, из которого нужно достать информацию. Его также называют источником.
- Отчет. Это мы использовали следующее имя, но это не название, которое должно обязательно быть. В этом блоке содержится название листа, в каком надо находить информацию.
- $A:$F и $A1 – адрес ячейки или диапазона с данными, которые содержатся в этом документе.
Собственно, процесс создания ссылки на внешний документ и называется связыванием. После того, как мы прописали адрес ячейки, содержащейся в другом файле, изменяется содержимое вкладки «Данные». А именно – становится активной кнопка «Изменить связи», с помощью которой пользователь может отредактировать имеющиеся связи.
Суть проблемы
Тут перед пользователем появляется два возможных варианта, как действовать этой ситуации. Он может нажать «Продолжить» и тогда изменения не обновятся, или же нажать кнопку «Изменить связи», с помощью которой он может обновить их вручную. После того, как мы нажмем эту кнопку, появится дополнительное окно, в котором можно будет изменить связи, указав, где находится правильный файл в данный момент и как он называется.
Как разорвать связь в Эксель
Один из самых простых методов решения описанной выше ситуации в случае, если не получается обновить местонахождение связанного файла самостоятельно – удалить саму связь. Особенно просто это сделать, если в документе содержится только одна связь. Для этого надо выполнить следующую последовательность шагов:
- Открываем меню «Данные».
- Находим раздел «Подключения», и там – опцию «Изменить связи».
- После этого нажимаем на «Разорвать связь».
Если собираетесь отправлять эту книгу по почте другому человеку, настоятельно рекомендуется это сделать до этого. Ведь после удаления связей все значения, которые содержатся в другом документе, будут автоматически загружены в файл, использованы в формулах, и вместо адреса ячейки информация в соответствующих ячейках будет просто трансформирована в значения.
Как разорвать связь со всеми книгами
Но если количество связей становится слишком большим, вручную их удалять может занять немало времени. Чтобы решить эту проблему за один раз, можно воспользоваться специальным макросом. Он находится в аддоне VBA-Excel. Нужно его активировать и перейти на одноименную вкладку. Там будет находиться раздел «Связи», в котором нам надо нажать на кнопку «Разорвать все связи».
Код на VBA
Если же нет возможности активировать это дополнение, можно создать макрос самостоятельно. Для этого необходимо открыть редактор Visual Basic, нажав на клавиши Alt + F11, и в поле ввода кода записать следующие строки.
If Not IsEmpty(WbLinks) Then
For i = 1 To UBound(WbLinks)
ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLinks
Как разорвать связи только в выделенном диапазоне
Время от времени количество связей очень большое, и пользователь боится, что после удаления какой-то из них не получится вернуть все назад, если какая-то была лишней. Но это проблема, которую легко не допустить. Для этого нужно выбрать диапазон, в котором осуществить удаление связей, а потом удалить их. Для этого надо выполнить следующую последовательность действий:
- Выделить тот набор данных, в котором надо вносить изменения.
- Устанавливаем дополнение VBA-Excel, после чего переходим на соответствующую вкладку.
- Далее находим меню «Связи» и нажимаем на кнопку «Разорвать связи в выделенных диапазонах».
После этого все связи в выделенном наборе ячеек будут удалены.
Что делать, если связи не разрываются
Все описанное выше звучит хорошо, но на практике всегда возникают какие-то нюансы. Например, может случиться ситуация, когда связи не разрываются. В этом случае все равно появляется диалоговое окно, что не получается автоматически обновить связи. Что же делать в этой ситуации?
- Сначала надо проверить, не содержится ли какая-то информация в именованных диапазонах. Для этого надо нажать на комбинацию клавиш Ctrl + F3 или же открыть вкладку «Формулы» – «Диспетчер имен». Если же имя к файлу указано полное, то нужно просто его отредактировать или же вовсе убрать. Перед тем, как удалять именованные диапазоны, необходимо скопировать файл в какое-то другое место, чтобы можно было вернуться к изначальному варианту, если были совершены неправильные действия.
- Если не получается решить проблему с помощью удаления имен, то можно проверить условное форматирование. Ссылка на ячейки в другой таблице может содержаться в правилах условного форматирования. Для этого надо найти соответствующий пункт на вкладке «Главная», а потом нажать на кнопку «Управление файлами».
Обычно Excel не дает возможности давать адрес других книг в условном форматировании, но это делается, если ссылаться на именованный диапазон с отсылкой на другой файл. Обычно даже после удаления связи ссылка остается. Нет никакой проблемы в том, чтобы убрать такую связь, потому что связь по факту нерабочая. Следовательно, ничего плохого не произойдет, если убрать ее.
Также можно воспользоваться функцией «Проверка данных», чтобы узнать, нет ли ненужных ссылок. Обычно связи остаются, если используется тип проверки данных «Список». Но что же делать, если ячеек много? Неужели необходимо последовательно проверять каждую из них? Конечно, нет. Ведь это займет очень много времени. Поэтому нужно воспользоваться специальным кодом, чтобы значительно сэкономить его.
Option Explicit
Dim rr As Range, rc As Range, rres As Range, s$
On Error Resume Next
If rr Is Nothing Then
On Error GoTo 0
For Each rc In rr
On Error Resume Next
On Error GoTo 0
If LCase(s) Like sToFndLink Then
If rres Is Nothing Then
Set rres = Union(rc, rres)
If Not rres Is Nothing Then
Необходимо в редакторе макросов сделать стандартный модуль, а потом туда вставить этот текст. После этого вызвать окно макросов с помощью комбинации клавиш Alt + F8, а потом выбрать наш макрос и кликнуть по кнопке «Выполнить». При использовании этого кода есть несколько моментов, которые надо учитывать:
Обычно после того, как выполнить действия, описанные в вышеприведенной инструкции, ненужных связей уже не должно оставаться. Но в случае наличия некоторых из них в документе и вашей неспособности по тем или иным причинам их убрать (типичный пример – защищенность данных в листе), то можно воспользоваться другой последовательностью действий. Эта инструкция справедлива лишь для версий 2007 и выше.
Нам не удалось создать новый или найти существующий раздел!
Вот такая проблема при установке виндовс 10. Пробовал почти все. Форматировал, удалял, создавал.
Ошибка при установке любой версии Windows: "Нам не удалось создать новый или найти существующий раздел"
Добрый день. Хочу у вас поинтересоваться, при установке любой из версии Виндовс (7 или 10) выходит.
Не удалось отобразить один или несколько элементов activex
Периодически выскакивает ошибка. Не удалось отобразить один или несколько элементов activex . Искал.
Не удалось отобразить один или несколько элементов activex
Здравсьтвуйте, не могу зайти в службы через панель управления, администрирование. Появляется окно -.
КостяФедореев, спасибо!
А не подскажете еще, пожалуйста, если при открытии книги возникает ошибка (книги открываются в цикле, копируется ячейка, вставляется в другую книгу), как сделать так, чтобы в случае ошибки при открытии, цикл не прерывался, а просто переходил на i+1 - к открытию следующей? в помощь
Ошибка при открытии книги или при внесении информации?
Если при открытии лучше сделать по другому.
Vlad999, зависает сильно с On Error Resume Next.
Именно при открытии, после чего еще большая часть кода, как бы сделать, чтобы сразу переходить на i+1
Добавлено через 10 минут
Vlad999, все, кажется, разобралась, добавила после: if err=0 then. else err.clear)
Нам не удалось создать новый раздел
Привет Короче. Привезли новый ПК, всё подключил, стал устанавливать Винду. Захожу в раздел.
Нам не удалось найти драйверы чтобы получить драйвер запоминающего устройства
Всем привет, собрали ПК, подключили ЖД и ССД. Начали устанавливать виндовс 10 с флешки, но в окне.
Сохранить одну или несколько картинок в PDF
столкнулся с дилемой. Требуется сохранить одну или несколько картинок в PDF (не кричите громко я.
Что такое связи в Excel и как их создать
Иногда при работе с различными отчетами приходится создавать связи с другими книгами(отчетами). Чаще всего это используется в функциях вроде ВПР (VLOOKUP) для получения данных по критерию из таблицы, расположенной в другой книге. Так же это может быть и простая ссылка на ячейки другой книги. В итоге ссылки в таких ячейках выглядят следующим образом:
=ВПР( A2 ;'[Продажи 2018.xlsx]Отчет'!$A:$F;4;0)
или
='[Продажи 2018.xlsx]Отчет'!$A1
- [Продажи 2018.xlsx] - обозначает книгу, в которой итоговое значение. Такие книги так же называют источниками
- Отчет - имя листа в этой книге
- $A:$F и $A1 - непосредственно ячейка или диапазон со значениями
Как разорвать связи
Как правило связи редко нужны на продолжительное время, т.к. они неизбежно увеличивают размер файла, особенно, если связей много. Исключениями являются случаи, когда связи делаются на некие общие файлы, расположенные на сетевом диске и заполняемые разными подразделениями, а в итоговый файл связями просто забираются конкретные значения из этого файла. Чтобы разорвать связи необходимо перейти на вкладку Данные -группа Данные и подключения -Изменить связи:
Выделить нужные связи и нажать Разорвать связь. При этом все ячейки с формулами, содержащими связи, будут преобразованы в значения вычисленные этой формулой при последнем обновлении. Данное действие нельзя будет отменить - только закрытием книги без сохранения.
Так же связи внутри формул разрываются, если формулы просто заменить значениями -Копируем нужные ячейки -Правая кнопка мыши -Специальная вставка -Значения. Формулы в ячейках будут заменены результатами их вычислений, а все связи будут удалены.
Более подробно про замену формул значениями можно узнать из статьи: Как удалить в ячейке формулу, оставив значения?
Что делать, если связи не разрываются
Но иногда возникают ситуации, когда вроде все формулы во всех ячейках уже заменены на значения, но запрос на обновление каких-то связей все равно появляется. В этом случае есть парочка рекомендаций для поиска и удаления этих мифических связей:
Это вопрос не про Excel. Откатить состояние можно только на сетевом диске, если администратором домена установлена эта настройка: Свойства файла -> Предыдущие версии (вкладка)
В файлах xlsm нет связей, но есть формулы и макросы. Если они не работают как положено - их кто-то поменял.
Ключ к решению: своевременно бэкапить нужные файлы/нанять компетентного специалиста.
ну почему же, бывают там и связи, когда информация берется из других источников данных, в т.ч. из других файлов excel.
Но там скорее всего с формулами накосячили, хотя, может, и со связями.
И ответ верный - откатить бэкап. Нет бэкапа? Сравнивать новый и старый файлы и разбираться.
Если файл разделен на часть с данными и часть их обрабатывающую - сделать копию старого и обновить данные.
Или перенести логику в испорченный файл, при необходимости скорректировав формулы.
Чтобы это сделать, нужно:
- иметь оба файла
- понимать логику обработки данных
- разбираться в Excele на базовом уровне, а не на уровне "создать список"
- время и зарплата
Антон, ага, посмотрел и не нашёл (меню) как включить.
Но у ТС, скорее всего, другое.
Начать с не совсем понятного
документ открывался с одной вкладки, теперь он открывается с другой вкладки
С высокой вероятностью или вкладку потерли, или формулы покоцали, или связи таки поломали, а если там еще и VBA был на автостарте какой-нибудь.
В любом случае, без докуемента это гадание, а с документом - нужно четко представлять его логику, т.е. или сравнивать с идентичным работающим, или работать с пользователем, что в любом случае выходит за рамки тостера.
John Smith, ок, благодарю. понимаю, что ставить диагноз по телефону и лечить дело неблагодарное.Но задам чуть более конкретный вопрос по другой проблеме, но возникшей на фоне основной.
На скриншоте я обвел кнопку, на которую раньше, если навести курсор и ПКМ, то появлялось окно и я мог выбрать гиперссылку и тд. Теперь же это окно ЛКМ активно, но выдает ошибку, а ПКМ не реагирует вообще. Защита листа неактивна, если что.
Спасибо
Читайте также: