Удалить связи в excel

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

Описание проблемы

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

К сожалению, если книга-источник была удалена/перемещена или переименована, то связь нарушится. Также связь будет потеряна если вы переместите конечный файл (содержащий ссылку). Если вы передадите только конечный файл по почте, то получатель тоже не сможет обновить связи.

При нарушении связи, ячейки со ссылками на другие книги будут содержать ошибки #ССЫЛКА.

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

Один из способов решения данной проблемы – разрыв связи. Если в файле только одна связь, то сделать это довольно просто:

  1. Перейдите на вкладку Данные.
  2. Выберите команду Изменить связи в разделе Подключения.
  3. Нажмите Разорвать связь.

ВАЖНО! При разрыве связи все формулы ссылающиеся на книгу-источник будут преобразованы в значения! Отмена данной операции невозможна!

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

Для удобства, можно воспользоваться макросом, который разорвет связи со всеми книгами. Макрос входит в состав надстройки VBA-Excel. Чтобы им воспользоваться необходимо:

  1. Перейти на вкладку VBA-Excel.
  2. В меню Связи выбрать команду Разорвать все связи.

Код на VBA

Код макроса удаляющего все связи с книгой представлен ниже. Можете скопировать его в свой проект.

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

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

  1. Выделите диапазон данных.
  2. Перейдите на вкладку VBA-Excel (доступна после установки).
  3. В меню Связи выберите команду Разорвать связи в выделенных ячейках.

Источник: micro-solution.ru

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

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

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

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

Разрыв связи

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

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

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

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

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

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

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

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

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

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

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

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

Нажмите кнопку ОК.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Источник: support.office.com

Удалить связи в excel

Модератор форума: _Boroda_, Manyasha, SLAVICK, китин
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Excel не разрывает связь с внешней книгой (Формулы/Formulas)

Excel не разрывает связь с внешней книгой

rogert Дата: Среда, 10.09.2014, 11:54 | Сообщение № 1

Учусь фин. моделированию

Ответить

igrtsk Дата: Среда, 10.09.2014, 12:08 | Сообщение № 2

Инструктор по применению лосей в кавалерийских частях РККА

Ответить

rogert Дата: Среда, 10.09.2014, 12:14 | Сообщение № 3

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

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

Учусь фин. моделированию

Ответить

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

igrtsk Дата: Среда, 10.09.2014, 12:55 | Сообщение № 4

Инструктор по применению лосей в кавалерийских частях РККА

Ответить

rogert Дата: Среда, 10.09.2014, 13:00 | Сообщение № 5

Учусь фин. моделированию

Ответить

gling Дата: Среда, 10.09.2014, 13:53 | Сообщение № 6
Саня Дата: Среда, 10.09.2014, 14:13 | Сообщение № 7

на листе 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, выделить, проверки данных – сначала всех, потом, если их будет несколько, по одному) Саня

Сообщение на листе 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

Источник: www.excelworld.ru

Трюк №13. Удаление несуществующих ссылок на книги Excel

О, несуществующие ссылки! Вы открываете рабочую книгу и видите сообщение с просьбой обновить ссылки — но у вас нет никаких ссылок! Как же можно обновить то, что не существует?

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

Сначала необходимо узнать, присутствуют ли в книге реальные внешние ссылки (существующие), о которых вы просто забыли. Если вы не уверены, есть ли такие, начните с поиска в самом очевидном месте — в формулах. Для этого удостоверьтесь, что никакие другие книги не открыты и выполните поиск по ключевой фразе [*] в формулах на всех листах. Закройте все остальные книги, чтобы гарантировать, что все ссылки в формулах будут содержать [*], где звездочка представляет собой символ шаблона.

Когда вы удостоверитесь, что в формулах ссылок нет, нужно будет убедиться, что допустимые ссылки не прячутся где-либо еще. Для этого нужно начать из глубины книги Excel, содержащей несуществующие ссылки. Выберите команду Вставка → Имя → Присвоить (Insert → Name → Define). Пролистайте список имен, щелкая и выделяя их по очереди и просматривая формулу в поле Формула (Refers to) внизу. Удостоверьтесь, что ни одно из имен не ссылается на другую рабочую книгу.

Другой возможный источник ссылок — это ваши диаграммы. У них может проявиться та же проблема, которую мы только что рассматривали. Необходимо проверить, что все диапазоны данных и метки оси X (X-axis) диаграммы не ссылаются на внешние книги. И снова вам решать, являются ли найденные ссылки правильными.

Ссылки скрываются и в объектах, например, текстовых полях, автофигурах и т. д. Объекты могут ссылаться на внешние рабочие книги. Самый простой способ найти объекты — выбрать любую ячейку на листе и затем выбрать команду Правка → Перейти (Edit → Go To). В диалоговом окне Переход (Go To) щелкните на кнопке Выделить (Special), установите флажок Объекты (Objects) и щелкните на кнопке ОК. Будут выбраны все объекты на листе. Это необходимо выполнять на копии вашей рабочей книги. Когда все объекты будут выделены, вы сможете удалить, сохранить, закрыть и снова открыть вашу копию, чтобы проверить, решена ли проблема.

И, наконец, последнее неочевидное место, где можно найти реальные ссылки — это скрытые листы, которые вы могли создать и забыть о них. Отобразите такие листы, выбрав команду Формат → Лист → Отобразить (Format → Sheet → Unhide). Если пункт Отобразить (Unhide) во вложенном меню Лист (Sheet) неактивен, это означает, что у вас нет скрытых листов. (Если вы уверены, что в книге есть листы, не появляющиеся в этом меню, обратитесь за подробностями к разделу «Трюк № 5. Как скрыть лист, чтобы его невозможно было отобразить».)

Теперь, когда вы устранили возможность появления реальных ссылок, настало время удалить несуществующие. Откройте рабочую книгу с несуществующими ссылками и выберите команду Правка → Связи (Edit → Links). Иногда можно просто выделить ненужную ссылку, щелкнуть кнопку Изменить (Change Source) и связать ссылку с самой собой. Но чаще всего появляется сообщение, что одна из формул содержит ошибку, и это сделать невозможно.

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

Создайте настоящую связь между двумя книгами, открыв обе. Перейдите к проблемной книге и в любой ячейке любого листа введите =. Теперь щелкните любую ячейку в хорошей книге и нажмите клавишу Enter, чтобы создать настоящую внешнюю ссылку на другую книгу. Сохраните обе книги, но не закрывайте их. В книге с несуществующими ссылками выберите команду Правка → Связи (Edit → Links) и щелкните кнопку Изменить (Change Source), чтобы увидеть все ссылки на новую рабочую книгу, с которой вы только что намеренно создали новую связь. Снова сохраните вашу рабочую книгу и удалите ячейку, в которой создали настоящую внешнюю ссылку. И еще раз сохраните файл.

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

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

Открыв проблемный лист, выделите некоторый диапазон ячеек (область приблизительно 10 х 10 ячеек) и затем выберите команду Правка → Очистить → Все (Edit → Clear → All). (Вы уверены, что сохранили копию книги?) Сохраните, закройте и заново откройте лист. Если приглашение обновить ссылки не появилось, вы нашли проблему, и теперь необходимо переделать этот блок ячеек. Если приглашение открылось, продолжайте очищать ячейки, пока при очередном открытии приглашение не будет выведено. После этого измените проблемные ячейки.

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

Источник: excel2010.ru

Удалить связи в excel
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Excel не разрывает связь с внешней книгой (Формулы/Formulas)

Excel не разрывает связь с внешней книгой
rogert Дата: Среда, 10.09.2014, 11:54 | Сообщение № 1

Учусь фин. моделированию

Ответить

igrtsk Дата: Среда, 10.09.2014, 12:08 | Сообщение № 2

Инструктор по применению лосей в кавалерийских частях РККА

Ответить

rogert Дата: Среда, 10.09.2014, 12:14 | Сообщение № 3

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

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

Учусь фин. моделированию

Ответить

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

igrtsk Дата: Среда, 10.09.2014, 12:55 | Сообщение № 4

Инструктор по применению лосей в кавалерийских частях РККА

Ответить

rogert Дата: Среда, 10.09.2014, 13:00 | Сообщение № 5

Учусь фин. моделированию

Ответить

gling Дата: Среда, 10.09.2014, 13:53 | Сообщение № 6
Саня Дата: Среда, 10.09.2014, 14:13 | Сообщение № 7

на листе 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, выделить, проверки данных – сначала всех, потом, если их будет несколько, по одному) Саня

Сообщение на листе 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

Источник: www.excelworld.ru

Разорвать связи листа с помощью VBA

Каким VBA скриптом можно разорвать все ссылки активного листа на соседние листы той же книги и превратить их в значения? Тоисть если в формуле ячейки задействована ссылка на другой лист превращать эту формулу в значения

2 ответа 2

Можно проверять наличие имени листа в формуле. Вариант не очень хороший.

Пример. В книге есть лист кекс

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

Проверять имя листа с восклицательным знаком (кекс!)? Да. Но этого мало: если имя листа имеет пробелы, в формуле оно окружено апострофами (‘вкусные кексы’!) и само имя отделено от восклицательного знака.

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

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

If c.HasFormula Then – проверяем только ячейки с формулами.

Три фрагмента кода, обведенные “рамками” – формирование списка адресов ячеек, в которых удалены формулы. Эти пять строк могут быть безболезненно исключены из макроса.

Cells(10, 10) – указать ссылку за пределами используемого диапазона листа или вывести список на отдельный лист.

Но и тут возможен сюрприэ

Маловероятно, но возможно. Для полной безопасности перед удалением формулы ставим еще одну проверку:

Т.е. если в формуле обнаружен фрагмент с именем листа, заменяем его на что-то неестественное для имени, пробуем вычислить формулу. И если ошибки нет, значит, ложная тревога – фрагмент, похожий на ссылку, присутствует только в текстовой строке. Важно именно менять, а не удалять имя (замена на “”): при удалении останется ссылка на ячейки активного листа и IsError=False – формула со ссылкой на другой лист не удалится.

И это еще не все

После дополнения IsError возможна еще одна ошибка в определении нужной формулы: в формуле в текстовой строке присутствует фрагмент с именем другого листа и сама формула по каким-либо причинам выдает ошибку.

Вернемся к первому примеру с листом кекс:

Наша созданная конструкция честно проверит все три условия: увидит формулу в ячейке; найдет в ней имя листа с восклицательным знаком; проверит, что ошибка есть – и преобразует формулу в результат ее работы!

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

Но это костыль. А правильно: не допускать в работе формул появления ошибок.

Источник: ru.stackoverflow.com

Excel восстановить

Left Arrow

Как сделать нумерацию в эксель

Right Arrow

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

Adblock
detector