Как разорвать связи в файле Эксель?

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

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

Что такое связи в 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
Предположу, что большинство такими ссылками не удивишь. Такие ссылки так же принято называть связыванием книг. Поэтому как только создается такая ссылка на вкладке Данные в группе Запросы и подключения активируется кнопка Изменить связи. Там же, как несложно догадаться, их можно изменить. В большинстве случаев ни использование связей, ни их изменение не доставляет особых проблем. Даже если в книге источники были изменены значения ячеек, то при открытии книги со связью эти изменения будут так же автоматом обновлены. Но если книгу-источник переместили или переименовали — при следующем открытии книги со ссылками на неё Excel покажет сообщение о недоступных связях в книге и запрос на обновление этих ссылок:

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

Так же изменение связей доступно непосредственно из вкладки Данные

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

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

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

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

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

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

Чтобы правильно использовать приведенный код, необходимо скопировать текст кода выше, перейти в редактор VBA( Alt + F11 ) -создать стандартный модуль(Insert —Module) и в него вставить скопированный текст. После чего вызвать макросы( Alt + F8 ), выбрать FindErrLink и нажать выполнить.
Есть пара нюансов:
1. Прежде чем искать ненужную связь необходимо определить её ссылку: Данные -Изменить связи. Запомнить имя файла и записать в этой строке внутри кавычек:
Const sToFndLink$ = «*продажи 2018*»
Имя файла можно записать не полностью, все пробелы и другие символы можно заменить звездочкой дабы не ошибиться. Текст внутри кавычек должен быть в нижнем регистре. Например, на картинках выше есть связь с файлом «Продажи 2018.xlsx», но я внутри кода записал «*продажи 2018*» — будет найдена любая связь, в имени которой есть «продажи 2018».
2. Код ищет проверки данных только на активном листе
3. Код только выделяет все найденные ячейки(обычное выделение), он ничего сам не удаляет.
4. Если надо подсветить ячейки цветом — достаточно убрать апостроф(‘) перед строкой
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. Появится сообщение об ошибке вроде «Ошибка в части содержимого в Книге . «. Соглашаемся. Появится еще одно окно с перечислением ошибочного содержимого. Нажимаем закрыть.

После этого связи должны быть удалены.

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

Ячейки связи между листами и книгами в Excel

Изучение этого сэкономит много времени и путаницы в долгосрочной перспективе.

Зачем связывать данные ячейки в Excel

Возможность ссылаться на данные на разных листах является ценным навыком по нескольким причинам.

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

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

Во-вторых, этот прием позволит избежать дублирования одних и тех же чисел в нескольких таблицах. Это уменьшит ваше рабочее время и возможность совершать ошибки в расчетах.

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

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

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

• В Sheet2 введите символ равенства (=) в клетку.
• Перейти на другую вкладку (Лист1) и щелкните ячейку, на которую вы хотите сослаться.
• Нажмите Войти завершить формулу.

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

Например, = Лист1! C3, где Лист1 это имя листа, C3 является ли ячейка, на которую вы ссылаетесь, и восклицательный знак (!) используется как разделитель между ними.

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

Примечание. Если имя листа содержит пробелы (например, Лист 1), то при вводе ссылки в ячейку необходимо указать имя в одинарных кавычках. подобно = ’Лист 1 ′! C3, Поэтому иногда проще и надежнее позволить Excel написать для вас справочную формулу.

Как связать диапазон ячеек

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

Чтобы связать несколько ячеек в Excel, выполните следующие действия.

• В исходной вкладке с данными (Лист1), выделите ячейки, на которые вы хотите сослаться.
• Скопируйте ячейки (Ctrl/команда + С, или щелкните правой кнопкой мыши и выберите копия).
• Перейти на другую вкладку (Sheet2) и нажмите на ячейку (или ячейки), где вы хотите разместить ссылки.

• Щелкните правой кнопкой мыши по ячейке (ям) и выберите Специальная паста…

• В левом нижнем углу меню выберите Вставить ссылку,

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

Как связать ячейку с функцией

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

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

У вас будет = SUM (Лист1 C3: C7)где функция SUM суммирует содержимое ячеек C3: C7 в Sheet1. Нажмите Войти завершить формулу.

Как связать ячейки из разных файлов Excel

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

• Откройте оба документа Excel.
• Во втором файле (Мастер службы поддержки), выберите ячейку и введите символ равенства знак равно
• Переключиться на оригинальный файл (Интернет Tech Советы) и нажмите на ячейку, на которую хотите сослаться.
• Нажмите Войти завершить формулу.

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

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

Станьте профессиональным пользователем Microsoft Excel

Связывание ячеек между листами — это только один из примеров того, как вы можете фильтровать данные в Excel и организовывать свои электронные таблицы. Проверьте некоторые другие Советы и рекомендации по Excel что мы собрали вместе, чтобы помочь вам стать продвинутым пользователем.

Какие еще изящные лайфхаки Excel вы знаете и используете? Знаете ли вы какие-либо другие творческие способы связать ячейки в Excel? Поделитесь ими с нами в разделе комментариев ниже.

Как разорвать связи в файле Эксель?

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

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

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

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

Создание нового внедренного объекта

1. Щелкните в документе место, куда следует поместить внедренный объект.

2. Выберите команду Объект в меню Вставка, а затем — вкладку Создание.

3. В списке Тип объекта выберите тип создаваемого объекта.

4. Для отображения внедренного объекта в виде значка (это удобно, если документ предназначен для просмотра в электронном виде) установите флажок В виде значка.

· В списке Тип объекта отображаются только установленные на данном компьютере программы, поддерживающие связанные и внедренные объекты.

· При выборе типа объекта Лист Microsoft Excel в документ вставляется вся книга. В документе одновременно отображается только один лист. Для отображения другого листа дважды щелкните объект Microsoft Excel, а затем выберите нужный лист.

Создание связанного или внедренного объекта из существующего файла

1. Щелкните в документе место, куда следует поместить связанный или внедренный объект.

2. Выберите команду Объект в меню Вставка, а затем — вкладку Создание из файла.

3. В поле Имя файла введите имя файла, из которого будет создан связанный или внедренный объект, или нажмите кнопку Обзор для выбора файла из списка.

4. Для создания связанного объекта установите флажок Связь с файлом.

Если флажок Связь с файлом не был установлен, будет создан внедренный объект.

5. Для отображения связанного объекта в виде значка установите флажок В виде значка.

Примечание. Команду Объект (меню Вставка) нельзя использовать для вставки графических и некоторых других типов файлов. Для вставки графических файлов в Word предназначена команда Рисунок (меню Вставка).

Разрыв связи со связанным объектом

1. Выберите команду Связи в меню Правка.

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

3. Нажмите кнопку Разорвать связь.

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

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

• Выделите внедренный объект, который следует изменить.
• В меню Правка выберите команду Объектимя объекта (например, Объект Лист), а затем — команду Преобразовать.
• Для преобразования внедренного объекта к типу, указанному в списке Тип объекта, выберите Преобразовать в.
• Для открытия внедренного объекта как имеющего тип, указанный в списке Тип объекта, без изменения типа внедренного объекта выберите Активизировать как.

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

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

Отображение в документе внедренного объекта, а не его значка

1. Выделите значок.
2. В меню Правка выберите команду Объектимя объекта (например: Объект Лист), а затем — команду Преобразовать.
3. Снимите флажок В виде значка.

Большие объекты в документе выглядят обрезанными

В некоторых случаях большие связанные и внедренные объекты обрезаются справа или снизу, даже если на странице достаточно места для размещения всего объекта. Это является следствием преобразования объекта в формат метафайла Windows (WMF), для которого определены максимально допустимые высота и ширина. Объекты, размеры которых превышают заданные максимальные размеры, выглядят обрезанными в документе Word.

Чтобы не допустить обрезки связанного или внедренного объекта, выполните одно из следующих действий:

• В исходной программе уменьшите размер данных, используемых для создания связанного или внедренного объекта. Например, уменьшите размеры шрифта и столбца.
• В Word вставьте (со связыванием или без) данные как форматированный или неформатированный текст. Для этого выберите команду Специальная вставка в меню Правка, выберите переключатель Вставить или Связать, а затем — параметр Текст в формате RTF или Неформатированный текст в списке Как.

Коды полей: LINK

Связывает данные, скопированные из другого приложения, с исходным файлом с помощью OLE. Word вставляет поле LINK при вставке данных, скопированных из другого приложения, в документ Word с помощью команды Специальная вставка (меню Правка).

Тип данных. Например, для диаграммы Microsoft Excel ИмяКласса имеет значение «ExcelChart». Word определяет эти сведения из исходного приложения.

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

» C :\ MSOffice \ Excel \ Rfp \ Budget . xls «

Фрагмент указанного файла или элемента, например диапазон ячеек в книге Microsoft Excel или закладка в документе Word.

Автоматически обновляет поле LINK; если требуется использовать обновление по запросу, удалите этот ключ.

Вставляет связанный объект как точечный рисунок.

Сохраняет в документе не сам рисунок, а только ссылку на него. Таким образом можно уменьшить размер документа.

Вставляет связанный объект как текст в формате HTML.

Вставляет связанный объект как рисунок.

Вставляет связанный объект в формате RTF.

Вставляет связанный объект в текстовом формате.

Пример

В следующем примере в документ Word вставляется диапазон ячеек из рабочей книги Microsoft Excel. Ключ a указывает, что обновление данных в документе Word должно осуществляться автоматически по мере их изменения в рабочей книге Microsoft Excel.

Коды полей: EMBED

Вставка объекта, созданного другим приложением, поддерживающим OLE. Word вставляет поле EMBED при вставке объектов (например, объектов Microsoft Excel) с помощью команды Объект (меню Вставка), команды Специальная вставка (меню Правка) или кнопки на панели инструментов.

Поле EMBED недоступно в диалоговом окне Поле (меню Вставка) и не может быть вставлено пользователем. Однако пользователю предоставляется возможность изменять ключи в существующем поле EMBED.

Имя приложения-контейнера, например Microsoft Excel. Этот элемент нельзя изменить.

Восстанавливает исходные размеры внедренного объекта при обновлении поля. Чтобы сохранить установленные размеры или обрезку внедренного объекта, удалите этот ключ (при наличии).

Применяет установленные ранее размеры или обрезку к обновленному внедренному объекту. Чтобы сохранить установленные размеры или обрезку внедренного объекта, не удаляйте этот ключ.

Пример

Следующее поле отображает объект Microsoft Graph, внедренный в документ.

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Создание связи между таблицами Excel

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

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

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

Создание связей между рабочими книгами

1. Открываем обе рабочие книги в Excel
2. В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+С)
3. Переходим в конечную книгу, щелкаем правой кнопкой мыши по ячейке, куда мы хотим поместить связь. Из выпадающего меню выбираем Специальная вставка
4. В появившемся диалоговом окне Специальная вставка выбираем Вставить связь.

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

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

Прежде чем создавать связи между таблицами

Прежде чем вы начнете распространять знания на свои грандиозные идеи, прочитайте несколько советов по работе со связями в Excel:

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

Автоматические вычисления. Исходная книга должна работать в режиме автоматического вычисления (установлено по умолчанию). Для переключения параметра вычисления перейдите по вкладке Формулы в группу Вычисление. Выберите Параметры вычислений –> Автоматически.

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

Обновление связей

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

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

Разорвать связи в книгах Excel

Разрыв связи с источником приведет к замене существующих формул связи на значения, которые они возвращают. Например, связь =[Источник.xlsx]Цены!$B$4 будет заменена на 16. Разрыв связи нельзя отменить, поэтому прежде чем совершить операцию, рекомендую сохранить книгу.

Перейдите по вкладке Данные в группу Подключения. Щелкните по кнопке Изменить связи. В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Разорвать связь.

Вам также могут быть интересны следующие статьи

• Как сравнить два столбца в Excel — методы сравнения данных Excel
• Формулы таблиц Excel
• Функция СЖПРОБЕЛЫ в Excel с примерами использования
• Четыре способа использования ВПР с несколькими условиями
• Что если отобразить скрытые строки в Excel не работает
• Седьмой урок обучающего курса — Основы Excel — Управление несколькими рабочими листами
• Пятый урок курса по основам Excel — Печать в программе
• Шестой урок онлайн курса по основам Excel — Управление рабочим листом
• Четвертый урок курса по основам Excel — Изменение ячеек
• Третий урок курса по основам Excel — Форматирование рабочих листов

7 комментариев

Спасибо! очень полезный материал!

Пожалуйста, исправьте опечатку:
«В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+V)»
Думаю должно быть «Ctrl+С»

Разорвать связь Word-Excel

Чтобы отправить ответ, вы должны войти или зарегистрироваться

Сообщений [ 8 ]

1 Тема от Ольга 28.05.2010 23:29:32

• Ольга
• рядовой
• Неактивен

• Зарегистрирован: 28.05.2010
• Сообщений: 3

Тема: Разорвать связь Word-Excel

Доброй ночи.
Создан шаблон документа в Word и БД в Excel. (Копировать-Спецвставка-Текст в кодировке Юникод-связать). После заполнения всех полей шаблона, он сохраняется в определённой папке через Сохранить как, но связи с БД остаются. Я пробовала макрорекордером записать макрос для разрыва связей, но у меня ничего не вышло, несмотря на то, что пользовалась клавиатурой, а не мышом.
Не могли бы Вы написать код такого макроса? Очень надо.
Помогите, пожалуйста.
С уважением,
Ольга.

2 Ответ от viter.alex 29.05.2010 08:33:20

• 
• viter.alex
• Модератор
• Неактивен

• Откуда: Харьков,
• Зарегистрирован: 21.12.2009
• Сообщений: 884
• Поблагодарили: 140

Re: Разорвать связь Word-Excel

Есть два варианта:

Выделить весь документ и нажать Shift+Ctrl+F9

кнопка Office→Подготовить→Изменить ссылки на файлы. Выбрать все ссылки и «Разорвать связь»

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

3 Ответ от Ольга 29.05.2010 11:36:18

• Ольга
• рядовой
• Неактивен

• Зарегистрирован: 28.05.2010
• Сообщений: 3

Re: Разорвать связь Word-Excel

viter.alex, спасиибо большое.
Получилось записать макрорекордером с Shift+Ctrl+F9; у меня там вычисляемых полей не было, кроме макроса Untaco, поэтому всё нормально прокатывает, и Untaco на это не реагирует (что есть очень хорошо).
По п. 2 кнопка Office→Подготовить→Изменить ссылки на файлы. Выбрать все ссылки и «Разорвать связь», у меня 2003 и чего-то я это не нашла. Есть через меню Правка — Связи — разорвать связь; вот это-то у меня макрорекордером не получилось, хотя писала через клавиатуру (там ещё все связи надо было выделить). КАК ЭТО можно записать, написать-то я НЕ смогу сама.

С уважением,
Ольга.
З.Ы.
И ещё будет вопрос про Untaco, но он не принципиальный и задам попозже.

4 Ответ от viter.alex 29.05.2010 12:13:27

• 
• viter.alex
• Модератор
• Неактивен

• Откуда: Харьков,
• Зарегистрирован: 21.12.2009
• Сообщений: 884
• Поблагодарили: 140

Re: Разорвать связь Word-Excel

Да, в 2003 п.2 делается через меню Правка→Связи…
К сожалению, макрорекордером это не записывается. Могу предложить такой макрос, который затронет только связанные объекты.

Также посмотрите вот эту тему, может почерпнёте что полезное.

5 Ответ от Ольга 29.05.2010 13:16:16

• Ольга
• рядовой
• Неактивен

• Зарегистрирован: 28.05.2010
• Сообщений: 3

Re: Разорвать связь Word-Excel

Спасибо БОЛЬШОЕ.
По ссылке ходила — то. что там знаю (кроме макроса, разумеется ).

Можно вопрос про Untaco?
Он всё очень хорошо делает, НО, если сумма, допустим, 50 000,00 — он расшифровывает правильно (Пятьдесят тысяч рублей 00 копеек), а потом после Сохранить как и входа в сохранённый документ оказывется (Пятьдесят тысяч рублей), уже без 00 копеек («Обновить» тоже не помогает). Там, где копейки не 00 — всё прекрасно, а происходит такое именно с 00. Я уж и в настройки лазила, и так и сяк, но вот.
Вобщем-то это не принципиально — сойдёт и так, но ПОЧЕМУ?
Наверное, этот вопрос надо задавать самому профессору Орлову, но, может быть, Вы на него сможете ответить?

6 Ответ от viter.alex 29.05.2010 22:04:59

• 
• viter.alex
• Модератор
• Неактивен

• Откуда: Харьков,
• Зарегистрирован: 21.12.2009
• Сообщений: 884
• Поблагодарили: 140

Re: Разорвать связь Word-Excel

Я посмотрел код Untaco, но разбираться в нём нет времени, да и комментариев там практически нет. Обратитесь к автору.

7 Ответ от Ольга666 26.12.2011 07:59:11

• Ольга666
• рядовой
• Неактивен

• Зарегистрирован: 26.12.2011
• Сообщений: 1

Re: Разорвать связь Word-Excel

Здраввствуйте!
Народ,помогите!
Уже все форумы перештудировала, не могу найти ответ(
Ситуация такая. есть файл Учсуд,в нём есть ссылка на временный документ, который закрепился в связях. Временный документ смысловой нагрузки, походу, не несёт, ибо был создан, когда сотрудник заходил оп удалёнке на сервер и делал там свои дела в этом файле. И эти временные файлы закрепились в связях. Руками не удаляются через «Изменить связи».
Начальство жаждет видеть документ без #ССЫЛКА!, а я не знаю,как эти 2 связи разорвать(
Помогите!

8 Ответ от srg4 26.12.2011 15:02:28

• srg4
• рядовой
• Неактивен

• Зарегистрирован: 26.12.2011
• Сообщений: 1

Re: Разорвать связь Word-Excel

Здраввствуйте!
Народ,помогите!
Уже все форумы перештудировала, не могу найти ответ(
Ситуация такая. есть файл Учсуд,в нём есть ссылка на временный документ, который закрепился в связях. Временный документ смысловой нагрузки, походу, не несёт, ибо был создан, когда сотрудник заходил оп удалёнке на сервер и делал там свои дела в этом файле. И эти временные файлы закрепились в связях. Руками не удаляются через «Изменить связи».
Начальство жаждет видеть документ без #ССЫЛКА!, а я не знаю,как эти 2 связи разорвать(
Помогите!

Ольга, Вы, наверное, студентка.
Попробуйте файл открыть в ExceL 2007 и действуйте: Формулы -> Диспетчер имен -> Удалить все имеющиеся старые связи. Работайте на копии. По ссылкам консультируйтесь у составителей таблицы — какие ненужные, а какие необходимо оставить.

Сообщений [ 8 ]

Чтобы отправить ответ, вы должны войти или зарегистрироваться

Похожие темы

• Разорвать связь с файлом — окончательно
• Связь Word и excel
• Связь документов Word и Excel
• Связь таблицы excel и документа word
• Импорт переменной, связь Word и Excel с помощью макроса
• Word2010 не разрывает связь с Excel
• Связь таблиц екселя с таблицей Word
• Особенности таблицы Word и ее связь с кодом VBA

Разорвать связь Word-Excel

Когда-то давно люди пересели с пишущих машинок за клавиатуру компьютера. И приветствовал их Microsoft Word. На портале о Microsoft Office Word вы узнаете про: как перенести печать в ворд. И дал он им жирный шрифт, полужирный, а еще курсив, множество стилей, таблиц и рисунков. Теперь те времена уже вошли в историю, а Word уходить не собирается. Наш сайт о Microsoft Office Word даст ответ про: программа — преобразователь в текстовый файл. Наоборот он все растет, расширяется, обзаводится новыми функциями и версиями.

Хотите доступную и понятную информацию по всем тонкостям работы с этим текстовым редактором? Наш сайт о Microsoft Office Word даст ответ про: почему в ворд паде вордовский дукумент открывается символами. Приходите на форум сайта Ворд Эксперт, в котором обсуждаются все вопросы и проблемы, связанные с различными версиями Word. Наш сайт о Microsoft Office Word даст ответ про: ворд 2010 автозаполнение.

Если вы просто супер профессионал, то сможете предложить свои наработки в соответствующем разделе, если хотите совершенствовать свои знания, вас научат писать макросы, выбрать наиболее удобные настройки для тех операций, что часто делаете, предложат списать готовые решения или дать заявку. На портале о Microsoft Office Word вы узнаете про: при вставке подложки из файла в ворде фон стал черным.

Форум отличается от многих компьютерных форумов прежде всего тем, что на Ворд Эксперт ориентация идет на простого или продвинутого пользователя, а не на горстку профи, которые общаются на птичьем языке. На портале о Microsoft Office Word вы узнаете про: как сделать в ворд 2007 чтобы предложение начсиналось с заглавной буквы. Все понятно, доступно и наглядно. А если какой-либо информации по редактору вам все-таки не хватает, оставляйте пожелание в соответствующем разделе. На портале о Microsoft Office Word вы узнаете про: программа перевода pdf формата в word.

Как разорвать связи в файле Эксель?

Урок № 7.
РАБОЧАЯ КНИГА EXCEL. СВЯЗЬ ТАБЛИЦ,

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

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

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

ХОД РАБОТЫ

ЗАДАНИЕ 1. На заполните и оформите таблицу со­гласно рисунку:

Для чисел в ячейках, содержащих даты проведения занятий, задайте формат Дата (Код: Д.ММ).
Оценки за 1 четверть вычислите по формуле как среднее ариф­метическое текущих оценок, используя функцию СРЗНАЧ.

ЗАДАНИЕ 2. Сохраните таблицу в личном каталоге рабочего диска под именем jurnal.xks.

ЗАДАНИЕ 3. Создайте аналогичные листы для предметов алгебра и геометрия, для чего:
3.1.Скопируйте таблицу Литература на следующий лист, ис­пользуя команды меню: Правка – Переместить/скопировать

После выполнения команды появится лист .
3.2. Скопируйте таблицу еще раз, используя те же команды меню.
После выполнения этой команды появится лист .

ЗАДАНИЕ 4. Переименуйте листы: в , в , в .

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

ЗАДАНИЕ 5. На листах и в таблицах соответственно измените названия предметов, текущие оценки, даты.

Связь рабочих листов

Excel позволяет использовать в таблице данные с других лис­тов и из других таблиц. Связь между двумя листами достигается за счет введения в один лист формулы связи со ссылкой на ячейку на другом листе.

ЗАДАНИЕ 6. На создайте таблицу – Ведомость итоговых оценок за 1 четверть, для чего:
6.1. Переименуйте в Лист
6.2.Заполните таблицу ссылками на соответствующие ячейки других листов:

• В ячейку А2 занесите формулу = Литература!А2

Литература! — ссылка на другой лист, символ ! обязателен А2 — адрес ячейки на листе , используется относительная адресация.

• Размножьте формулу на последующие 5 ячеек столбца А и соответствующие ячейки столбца В.

В ведомости заполнятся колонки № и Фамилия учащегося.

• В ячейку СЗ занесите формулу =Литература! L3
• Размножьте формулу на последующие 4 ячейки столбца.

Столбец заполнился оценками за i четверть по литературе. Таким образом будет установлена связь между листом — и листом .

ЗАДАНИЕ 7. Удалите листы, которые не будут использоваться, в рабочей книге (3-16).
Для удаления листа выберите команду Удалить из контекстно-зависимого для ярлычков. Для удаления сразу несколь­ких рабочих листов предварительно выделите их при нажатой клавише .
Пока информация рабочего листа занимает один экран, дос­таточно одного окна. Если это не так, то можно открыть несколь­ко окон и одновременно отслеживать на экране разные области рабочего файла.
В нашем примере это также удобно сделать, расположив в разных окнах разные рабочие листы.

ЗАДАНИЕ 8. Проверьте правильность заполнения таблицы.
8.1. Откройте для просмотра еще одно окно. Выполните команды меню: Окно — Новое окно.
8.2. В новом окне выберите рабочий лист .
8.3. Выполните команды меню: Окно — Упорядочить окна — Упо­рядочить , (*) каскадом.

Экран после выполнения указанных действий примет вид, представленный на рисунке.

Активным всегда является только одно из окон. Для активизации другого окна нужно щелкнуть по нему.

ЗАДАНИЕ 9. Проверьте, как работает связь таблиц.
9.1. На листе Руслановой Галине исправьте две текущие оценки 3 на 4.
Внимание! Изменилась итоговая оценка Руслановой Галины за 1 четверть, как на листе , так и на листе .
9.2. Исправьте текущие оценки Руслановой Галине опять на 3.
Таким образом, связь между различными листами одной ра­бочей книги действует.

Связь между файлами

Связь между двумя файлами достигается за счет введения в — один файл формулы связи со ссылкой на ячейку в другом файле. Файл, который получает данные из другого, называется файлом назначения, а файл, который отдает данные, — файлом-источни­ком.
Как только связь устанавливается, Excel копирует величину из ячейки в файле-источнике в ячейку — файла назначения. Величи­на в ячейке назначения автоматически обновляется.

ЗАДАНИЕ 10. Осуществите связь между листами разных рабочих книг. Заполните столбец в Ведомости оценками по литературе, взяв их из файла jurnal.xls.
10. 1. Очистите от оценок столбец Литература, используя ко­манды меню: Правка – Очистить содержимое.
10.2.В ячейку СЗ занесите формулу: =’A:PETROV[jurnal1.хls]Литература ‘!L3,

‘A:PETROV[jurnal1.хls]Литература ‘!L3 — путь к файлу jurnal.xls и листу . Обязательно в «». Имя файла обязательно в квадратных скобках. В пути должен быть указан ваш рабочий диск и ваш личный каталог.
10.3. Размножьте формулу на последующие 4 ячейки столбца.
Столбец заполнился оценками по литературе, т.е., связь установлена.

ЗАДАНИЕ 11. Самостоятельно заполните ведомость 1 четверть по предметам алгебра и геометрия.

ЗАДАНИЕ 12. Раскройте еще два окна и разместите в них листы и , упорядочив окна каскадом.

ЗАДАНИЕ 13. Закройте все окна, кроме окна . Разверните это окно на весь экран.

ЗАДАНИЕ 14. На листе напечатайте список учеников, которые закончили 1 четверть с оценкой 5,4,3 по предмету:
14.1. На листе . В ячейку А10 введите текст: «Получили оценку 5:»
14.2. Скопируйте это текст в ячейки А17 и А24.
14.3. В ячейке А17 измените текст на: «Получили оценку 4:», а в ячейке А24 на: «Получили оценку 3:».
14.4. С использованием Автофильтра выберите записи с итоговой оценкой 5 за 1 четверть.
14.5. Выделите фамилии учеников и скопируйте их в 11-ю строку в столбце В.
14.6. С ячеек с фамилиями, которые были только что скопированы, снимите обрамление и фон.
14.7.Аналогичные действия произведите для учеников, которые получили оценку 3 и 4.
14.8.Отмените Автофильтр, выполнив команду: Данные – Фильтр – Автофильтр.
В результате всех действий лист будет иметь вид:

ЗАДАНИЕ 15. Сохраните таблицу на диске в личном каталоге под именем work7.xls.

ЗАДАНИЕ 16. Распечатайте лист , предваритель­но сняв колонтитулы. Меню: Файл — Печать, (*) — выделенные листы.

ЗАДАНИЕ 17. Подведите итоги.
Проверьте:
знаете ли вы, что такое:

умеете ли вы:

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