Разорвать связи в excel
Обновлено: 07.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 и выше.
Исправление неправиленной ссылки
Внимание: Это действие нельзя отменить. Перед началом этой процедуры может потребоваться сохранить резервную копию книги.
Откройте книгу, которая содержит неверную связь.
На вкладке "Данные" нажмите кнопку "Изменить связи".
Команда "Изменить связи" недоступна, если книга не содержит ссылок.
Выберите команду Смена источника.
Перейдите к расположению файла, содержащего связанные данные.
Выберите новый исходный файл и нажмите кнопку "Изменить источник".
Удаление неявной ссылки
При разрыве связи все формулы, которые ссылаются на исходный файл, преобразуются в их текущее значение. Например, если формула =СУММ([Budget.xls]Годовой! C10:C25) — 45, после того как связь не будет нарушена, формула будет преобразована в 45.
Откройте книгу, которая содержит неверную ссылку.
На вкладке "Данные" нажмите кнопку "Изменить связи".
Команда "Изменить связи" недоступна, если книга не содержит ссылок.
В поле "Исходный файл" выберите ненужную ссылку, которую нужно удалить.
Примечание: Чтобы удалить несколько ссылок, щелкните каждую из , удерживая нажатой кнопку мыши.
Щелкните элемент Разорвать.
Исправление неправиленной ссылки
Внимание: Это действие нельзя отменить. Перед началом этой процедуры может потребоваться сохранить резервную копию книги.
Откройте книгу, которая содержит неверную связь.
В меню Правка выберите пункт Связи.
Если книга не содержит ссылок, команда "Ссылки" недоступна.
Выберите команду Смена источника.
Выполните одно из следующих действий:
Необходимые действия
Исправление неправиленной ссылки на лист в другой книге
В диалоговом окне "Открытие" найдите книгу и нажмите кнопку "Изменить".
Исправление неправиленной ссылки на книгу или другой документ Office
Введите новое имя или расположение документа в поле "Изменить ссылки на текстовое поле" и нажмите кнопку "ОК".
Отключение автоматического обновления связанных данных
Откройте книгу, которая содержит неверную связь.
В меню Правка выберите пункт Связи.
Если книга не содержит ссылок, команда "Ссылки" недоступна.
Нажмите Вручную.
Удаление неявной ссылки
При разрыве связи все формулы, ссылаясь на исходный файл, преобразуются в их текущее значение. Например, если формула =СУММ([Budget.xls]Годовой! C10:C25) — 45, после того как связь не будет нарушена, формула будет преобразована в 45.
Откройте книгу, которая содержит неверную связь.
В меню Правка выберите пункт Связи.
Если книга не содержит ссылок, команда "Ссылки" недоступна.
В поле "Исходный файл" щелкните ненужную ссылку, которую нужно удалить.
Примечание: Чтобы удалить несколько ссылок, щелкните каждую из , удерживая нажатой кнопку мыши.
Сначала расскажем что такое объединенные ячейки в Excel и как их сделать. Тут нет ничего сложно, достаточно выделить две или более ячеек и выбрать команду на ленте Главная -> Выравнивание -> Объединить и поместить в центре.
Важно отметить, что Excel при объединении ячеек, которые содержат значения, оставит значение только той ячейки, которая находится в верхнем левом углу выбранного диапазона. Остальные же значения будут удалены. Однако, с помощью нашей надстройки, данное недоразумение можно избежать с помощью специальной команды.
Вы можете объединять ячейки как по горизонтали, так и по вертикали. Также можно объединить области, содержащие боле одной строки и столбца.
Способы объединения ячеек
Если внимательней посмотреть на команду объединения ячеек, то можно увидеть выпадающее меню, которое в свою очередь содержит следующие дополнительные действия:
- Объединить по строкам - позволяет выбрать диапазон ячеек, который содержит несколько строк. В этом случае Excel будет создавать объединенные ячейки по одной на каждой строке
- Объединить ячейки - объединяет выделенные ячейки в одну, но при этом не выравнивает текст в ячейке.
- Отменить объединение ячеек - позволяет разбить объединенную ячейку на несколько.
Какие проблемы возникают при использовании объединенных ячеек
Как уже говорили, пользоваться объединенными ячейками стоит аккуратно, так как они ограничивают функционал Excel и могут в дальнейшем принести неприятности. Если все-таки решили пользоваться объединенными ячейками, всегда помните о следующих моментах:
- Если диапазон содержит объединенные ячейки, то пользоваться сортировкой и фильтрацией в этом диапазоне будет невозможно.
- Также невозможно будет преобразовать такой диапазон в таблицу (форматировать как таблицу).
- Также можно забыть об автоматическом выравнивании ширины или высоты ячейки. Например если имеется объединенная ячейка A1:B1, то выравнять ширину столбца A уже не получится.
- Если Вы пользуетесь горячими клавишами для навигации, например переходите в начало и конец таблицы путем сочетания клавиш Ctrl + стрелка вверх и вниз, то переход не удастся, и курсор будет "упираться" в объединенные ячейки.
- Если вы выделяете столбцы (или строки) с помощью горячих клавиш Ctrl (Shift) +Пробел, то при наличии объединенных ячеек, вы не сможете выделить 1 столбец (или строку).
Как найти все объединенные ячейки и разъединить их
Чтобы разъединить все ячейки и удалить объединенные, достаточно выполнить следующие действия:
- Выделить все ячейки на листе. Это можно сделать путем нажатия сочетания клавиш Ctrl + A или кликнуть на черный треугольник между заголовками строк и столбцов на листе.
- Нажать на команду Главная -> Выравнивание -> Объединить и поместить в центре если она выделена. Если же она не выделена, значит выбранный лист не содержит объединенных ячеек.
Если же необходимо найти все объединенные ячейки в книге, то это можно сделать через панель поиска. Для этого выполните следующие шаги:
Альтернативы использования объединенных ячеек
Как правило объединенные ячейки применяют для целей выравнивания текста. Однако мало кто знает, что текст можно выровнять и без объединения ячеек и потери функционала. Для этого можно использовать обычное форматирование ячеек:
- Выделите диапазон ячеек, который хотите отцентрировать. При этом сам текст должен содержаться в левой верхней ячейке.
- Выберите команду формат ячеек или нажмите сочетание клавиш Ctrl + 1.
- В диалоговом окне Формат ячеек перейдите на вкладку Выравнивание.
- В выпадающем списке выравнивания по горизонтали выберите значение по центру выделения и нажмите OK.
Еще один способ избежать объединения ячеек - это использовать надписи. В них также можно вставлять текст и выбирать направление текста по вертикали. Способ конечно также накладывает много ограничений, но в каких-то случаях имеет место быть.
Что такое связи в Excel и как их создать
Иногда при работе с различными отчетами приходится создавать связи с другими книгами(отчетами). Чаще всего это используется в функциях вроде ВПР (VLOOKUP) для получения данных по критерию из таблицы, расположенной в другой книге. Так же это может быть и простая ссылка на ячейки другой книги. В итоге ссылки в таких ячейках выглядят следующим образом:
=ВПР( A2 ;'[Продажи 2018.xlsx]Отчет'!$A:$F;4;0)
или
='[Продажи 2018.xlsx]Отчет'!$A1
- [Продажи 2018.xlsx] - обозначает книгу, в которой итоговое значение. Такие книги так же называют источниками
- Отчет - имя листа в этой книге
- $A:$F и $A1 - непосредственно ячейка или диапазон со значениями
Как разорвать связи
Как правило связи редко нужны на продолжительное время, т.к. они неизбежно увеличивают размер файла, особенно, если связей много. Исключениями являются случаи, когда связи делаются на некие общие файлы, расположенные на сетевом диске и заполняемые разными подразделениями, а в итоговый файл связями просто забираются конкретные значения из этого файла. Чтобы разорвать связи необходимо перейти на вкладку Данные -группа Данные и подключения -Изменить связи:
Выделить нужные связи и нажать Разорвать связь. При этом все ячейки с формулами, содержащими связи, будут преобразованы в значения вычисленные этой формулой при последнем обновлении. Данное действие нельзя будет отменить - только закрытием книги без сохранения.
Так же связи внутри формул разрываются, если формулы просто заменить значениями -Копируем нужные ячейки -Правая кнопка мыши -Специальная вставка -Значения. Формулы в ячейках будут заменены результатами их вычислений, а все связи будут удалены.
Более подробно про замену формул значениями можно узнать из статьи: Как удалить в ячейке формулу, оставив значения?
Что делать, если связи не разрываются
Но иногда возникают ситуации, когда вроде все формулы во всех ячейках уже заменены на значения, но запрос на обновление каких-то связей все равно появляется. В этом случае есть парочка рекомендаций для поиска и удаления этих мифических связей:
Читайте также: