Сохранять значения внешних связей в excel что это значит

Обновлено: 03.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 и выше.

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

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

Существует много способов сослаться на ячейку с другого листа, или книги. Я расскажу Вам самый простой:

Откройте все файлы – свод и исходники
Кликните по ячейке, в которую нужно вставить ссылку, и введите знак равно (как вы это делаете, когда создаете формулы).
Выберите файл с исходным значением и кликните на этом значении. В строке формул появится ссылка на это значение.
Если к этому значению нужно прибавить другие из остальных источников – допишите «+» и повторяйте п.3-4 до полного заполнения.
Когда все ссылки на ячейки сделаны, нажмите Enter , будет просчитан сводный результат.

Внешняя ссылка имеет специфический внешний вид: имя файла в квадратных скобках, имя листа, восклицательный знак, координаты ячейки:

Когда Вы закроете исходные файлы, к ссылке слева добавится еще и адрес файла-исходника:

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

Не перемещайте файлы-исходники, т.к. это разорвет связи, и внешние ссылки работать больше не будут.

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

Управление внешними ссылками

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

Обновить – принудительное обновление всех ссылок
Изменить – указать новую книгу Excel, в которой располагаются подключенные данные
Открыть – открывает документ-источник
Разорвать связь – удаляет внешние ссылки из этой связи, заменяет их на значение
Состояние – обновляет статус доступности файлов с данными
Запрос на обновление связей – укажите программе, нужно ли обновлять данные при открытии файла

Если у Вас большой файл с несколькими листами, может понадобиться много времени на поиски внешних ссылок. Так как быстро найти внешние ссылки в документе Excel? Предложу рецепт. Все подобные ссылки имеют общий элемент – открывающая и закрывающая квадратные скобки, в которые записано имя файла-исходника. В других случаях такие скобки используются редко. Нажмите Ctrl+F для открытия окна поиска, в поле «Найти» введите квадратную скобку и нажмите «Найти все». В результатах поиска точно будут все внешние ссылки.

Применение внешних ссылок – самый простой и очевидный способ собрать данные из нескольких таблиц в одну. Но если данных достаточно много, этот метод неприменим. Тогда пользуемся инструментом «Консолидация».

Этот инструмент упрощает сведение информации в одну таблицу данных, это удачный способ автоматизации процесса.

Рассмотрим его работу на примере. У нас есть 3 файла с данными о продажах трех подразделений компании. Нужно собрать общие показатели по организации, и сделать это быстро, ведь информация нужна «на вчера».

Давайте выполнять. Открываем все файлы-исходники и свод. Активируем «Свод». Ищем на ленте Данные – Работа с данными – Консолидация . Откроется окно подключения внешних данных.

Теперь пошагово выполняем:

В поле «Функция» выбираем «Сумма» (или что-то другое, если нужно)
В поле «Ссылка» указываем ссылку на данные в первом файле-источнике (вместе с шапкой). Лучше всего, указать столбцы полностью, например A:D . Тогда не нужно будет переопределять диапазон при добавлении новых данных.
Нажимаем «Добавить», ссылка появляется в поле «Список диапазонов»
Повторяйте п. 2-3, пока не добавите ссылки на все файлы-исходники

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

Если исходные таблицы изменились, можно переформировать свод. Для этого повторно откройте окно «Консолидация» и нажмите Ок.

Такой вот простой инструмент, решающий одну из самых противных задач в Эксель. Я предпочитаю применять именно его, ручная работа по внесению ссылок – не для меня. Рекомендую и Вам пользоваться. Хотя, более удачным решением будет применение сводной таблицы (когда это возможно), или создание собственной VBA-программы с детальными настройками сведения.

На этом оставлю Вас экспериментировать с Консолидацией, и приступаю к написанию поста о защите данных в Эксель. Читайте, эта информация сейчас, как никогда актуальна. До встречи!

Внешняя ссылка - это ссылка или ссылка на ячейку или диапазон на листе в другой книге Excel или ссылка на определенное имя в другой книге.

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

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

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

Преимущества использования внешних ссылок или внешних ссылок в Excel:

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

Как создать внешние ссылки в Excel?

Давайте создадим внешнюю ссылку в книге с примером,

Предположим, у нас есть пять человек, которые должны проверить 100 вопросов, и они должны пометить свои ответы как правильные или неправильные. У нас есть три разные рабочие тетради. В рабочей книге 1 мы должны собрать все данные, которые названы как отчеты, тогда как в рабочей книге 2, которая названа правильной, которая содержит данные, помеченные ими как правильные, и в рабочей книге 3, которая названа как неправильные, есть данные неправильных значений.

Взгляните на рабочую книгу 1 или рабочую книгу отчета:

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

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

Как найти внешние ссылки в Excel?

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

Найти внешние ссылки в Excel - Пример № 1

У нас есть книга «Отчет», и нам нужно найти внешние ссылки в этой книге Excel.

Нажмите Ctrl + F, и появится диалоговое окно «Найти и заменить».

Нажмите « Параметры» в правой нижней части диалогового окна.

В поле « Найти» введите «* .xl *» (расширение других рабочих книг или внешних ссылок - * .xl * или * .xlsx).

В поле «Внутри» выберите « Рабочая книга» .

И в поле «Посмотри в поле» выберите « Формулы» .

Он отображает все внешние ссылки в этой книге.

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

Найти внешние ссылки в Excel - Пример № 2

Вторая процедура из опции Изменить ссылки .

На вкладке « Данные » есть раздел соединений, где мы можем найти опцию «Редактировать ссылки». Нажмите на ссылку Изменить.

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

Примечание. По умолчанию этот параметр остается отключенным и активируется только в том случае, если наша рабочая книга имеет внешние ссылки на него.

Объяснение внешних ссылок в Excel

Как объяснялось ранее, зачем нам внешние ссылки на наш лист? Быстрый ответ будет: мы не можем хранить большие объемы данных в одной книге. В этом сценарии нам нужно хранить данные в разных таблицах и ссылаться на значения в основной книге.

Теперь, почему нам нужно найти внешние ссылки в книге?

Иногда нам нужно обновить или удалить наши ссылки, чтобы изменить или обновить значения. В таком случае нам сначала нужно найти внешние ссылки.

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

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

То, что нужно запомнить

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

Нажмите «Включить содержимое», затем продолжите.