Как проверить связи в excel с другим файлом
Перейти к содержимому

Как проверить связи в excel с другим файлом

  • автор:

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

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

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

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

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

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

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

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

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

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

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

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

Разорвать все связи в Excel

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

Код на VBA

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

Sub UnlinkWorkBooks() Dim WbLinks Dim i As Long Select Case MsgBox("Все ссылки на другие книги будут удалены из этого файла, а формулы, ссылающиеся на другие книги будут заменены на значения." & vbCrLf & "Вы уверены, что хотите продолжить?", 36, "Разорвать связь?") Case 7 ' Нет Exit Sub End Select WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) If Not IsEmpty(WbLinks) Then For i = 1 To UBound(WbLinks) ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLinks Next Else MsgBox "В данном файле отсутствуют ссылки на другие книги.", 64, "Связи с другими книгами" End If End Sub

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

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

Удалить связи в выделенных ячейках

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

Надстройка
VBA-Excel

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

Как проверить связи в excel с другим файлом

Какие же есть признаки того, что в открываемой книге есть ссылки на другие книги Excel?

При использовании настроек программы по умолчанию, при условии, что внешние файлы-источники закрыты, при открытии файла сразу выводится ПРЕДУПРЕЖДЕНИЕ СИСТЕМЫ БЕЗОПАСНОСТИ [SECURITY WARNING] о том, что автоматическое обновление ссылок отключено.

extlink0.png

И, если воспользоваться предлагаемой кнопкой Включить содержимое [Enable Content], то данные будут обновлены.

Если при открытии предупреждение не появляется, то следует посмотреть настройки программы:

  1. Выбрать Файл [File], затем Параметры [Options].
  2. Выделить раздел Центр управления безопасностью [Trust Center], нажать кнопку Параметры центра управления безопасностью [Trust Center Settings].
  3. Выбрать Внешнее содержимое [External Content] и выбрать нужный вариант в списке Параметры безопасности для связей в книге [Security settings for Workbook Links]:

extlink1.png

Обратить внимание на доступность команды на вкладке Данные [Data], в группе Подключения [Connections], выбрать Изменить связи [Edit Links]. Если команда доступна, значит, есть источники и их можно увидеть, изменить и т.д. (подробнее в статье Ссылки на другие книги Excel ).

Где же те ячейки, в которых используются ссылки на внешние файлы Excel?

Ответ на этот вопрос можно получить с помощью команды Поиск [Find], попасть в которое можно быстро нажав клавиши Ctrl+F:

extlink2.png

  • в поле Найти [Find what] ввести квадратную скобку [
  • нажать кнопку Параметры [Options], чтобы настроить поиск более точным
  • в списке Искать [Within] выбрать в книге [Workbook]
  • в списке Область поиска [Look in] должно быть формулы [Formulas]

Воспользовавшись командой Найти всё [Find All], сформируется внизу окна список всех адресов ячеек, где есть ссылки на внешние файлы, а так же будет выполнен переход к первой найденной ячейке этого списка:

extlink3.png

Оставьте комментарий!

На сообщение “Как увидеть есть ли ссылки на другие файлы, и где они находятся в книге Excel?” комментариев 10

  1. Nikolya :
    26.08.2014 (17:14) Ну наконец-то я узнал ответ на свой давно мучивший вопрос. А ответ, как обычно, прост!
  2. Leonid :
    28.08.2014 (11:31) Каждый вариант хорош по-своему
  3. Вика :
    05.12.2014 (14:05) спасибо большое
  4. Evgen :
    13.12.2014 (02:08) Наткнулся случайно на Ваш блог. Теперь стану постоянно просматривать. Надеюсь, не разочаруете и дальше
  5. Георг :
    14.02.2015 (17:57) Нет ли команды, которая сама выделит ячейки, где ссылки на другие листы/файлы?
  6. Suzanna :
    09.05.2015 (10:36) Спасибо, мне, как не очень опытному пользователю, это очень помогло
  7. Фрилансер :
    27.08.2015 (19:23) теперь и я знаю где можно разорвать не нужные линки
  8. Robert :
    26.10.2015 (14:23) Thanks for sharing. Always good to find a real expert.
  9. Ирина_Вик :
    03.11.2015 (13:42) познавательно и самое основное
  10. Robert :
    19.10.2017 (20:58) спасибо, а то я про окно поиска не догадался

Публикации

  • Нарастающий итог в Power Query
  • Отключение “типов данных” в Power Query
  • 2 способа создания отчетов по данным модели Power Pivot
  • Функция Text.Format
  • Удаление пустых строк и столбцов в Power Query

Страницы

  • Анонсы расписания
  • Видео и Статьи
  • Вопрос-Ответ
  • Контакты
  • Об авторе
  • Обучение в программе Microsoft Excel
    • Корпоративное обучение
    • Диаграммы
    • Надстройки Power
    • Новое в Excel 2013
    • Работа с данными ячеек
    • Работа с табличными массивами
    • Форматирование
    • Формулы и функции

    Комментарии

    • Светлана к записи Почему даты в фильтре не группируются?
    • Эркинжон к записи Как увидеть скрытые имена в файле?
    • Ольга Кулешова к записи Преобразование текста в число и наоборот
    • Ольга Кулешова к записи Отключение “типов данных” в Power Query
    • Ольга Кулешова к записи 2 способа создания отчетов по данным модели Power Pivot

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

    Важно: Это средство недоступно в Office на компьютерах под управлением Windows RT. Inquire is only available in the Office профессиональный плюс and Приложения Microsoft 365 для предприятий editions. Чтение Excel 2010 с помощью Power Pivot не работает в некоторых версиях Excel 2013.Хотите узнать, какая у вас версия Office?

    Хороший способ проверить связи между листами — воспользоваться командой Workbook Relationship (Связи книги) в Excel. Если на вашем компьютере установлен Microsoft Office профессиональный плюс 2013, вы можете воспользоваться этой командой, находящейся на вкладке Inquire (Запрос), чтобы быстро построить схему, отображающую связи книг между собой. Если вкладка Inquire (Запрос) не отображается на ленте Excel, см. раздел Включение надстройки Spreadsheet Inquire (Запрос электронной таблицы).

    Команда

    1. Откройте файл, в котором необходимо проанализировать связи книги.
    2. Выберите Inquire (Запрос) >Workbook Relationship (Связи книги).

    Откроется схема связей книги, отображающая связи открытой книги с другими книгами. Кроме того, отобразятся связи с внешними источниками данных, такими как базы данных Access, XML-файлы и HTML-страницы. Наведите указатель мыши на любой узел схемы, чтобы просмотреть расположение книги и дату ее последнего изменения.

    Схема связей книги

    На рисунке выше:

    • имя текущей книги («Отчет 2011–10 (версия 1).xlsb») отображается полужирным шрифтом;
    • выбранный узел книги («Отклонения.xls») выделен ярко-зеленым цветом;
    • книга «Отчет о прибылях и убытках 2.xls» выделен ярко-желтым цветом. Это означает, что связи с данными других книг могут быть не обновлены.

    На рисунке ниже изображена связь книги «Корпоративные затраты за 3 квартал.xlsx» с книгой «Продажи с начала года.xlsx.» Во всплывающем окне «Продажи с начала года.xlsx» можно просмотреть сведения о расположении файла и дате его последнего изменения.

    Отображение сведений о связях книги во всплывающем окне

    Сведения о связанных листах см. в статье Просмотр связей между листами. Подробнее о функциях вкладки «Диагностика» см. в статье Возможности средства диагностики электронных таблиц.

    Найти скрытые связи

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

    Если нажать Продолжить, то ссылки обновлены не будут и в ячейках будут оставлены значения на момент последнего сохранения. Происходит это потому, что ссылки хранятся внутри самой книги и так же там хранятся значения этих ссылок. Если же нажать Изменить связи (Change Source) , то появится окно изменения связей, где можно будет выбрать каждую связь и указать правильное расположение нужного файла:
    Изменение/удаление связей
    Так же изменение связей доступно непосредственно из вкладки Данные (Data) . Там же связи можно разорвать, т.к. как правило связи редко нужны на продолжительное время(ведь они неизбежно увеличивают размер файла, особенно, если связей много). Чтобы разорвать связи необходимо перейти на вкладку Данные (Data) -группа Запросы и подключения (Queries & Coonections)Изменить связи (Edit Links) (появится тоже самое окно, что показано выше). Выделить нужные связи и нажать Разорвать связь (Break Link) . При этом все ячейки с формулами, содержащими связи, будут преобразованы в значения, вычисленные этой формулой при последнем обновлении. Данное действие нельзя будет отменить — только закрытием книги без сохранения.
    Но иногда возникают ситуации, когда вроде все связи разорваны всеми доступными методами, но запрос на обновление каких-то связей все равно появляется. Вот для поиска этих мифических связей и предназначена команда MulTEx Найти скрытые связи , т.к. она ищет связи не только внутри формул, где их разрывает стандартно сам Excel, но и среди других возможных мест их нахождения:
    Найти скрытые связи

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

    Искать только если имя источника содержит — если флажок установлен, то необходимо в поле ниже ввести слово или словосочетание, которое необходимо найти внутри ссылки/связи. В этом случае будут отобраны только те связи, внутри которых есть подобное слово/словосочетание. Необходимо для случаев, когда необходимо целенаправленно отыскать только связи, ссылающиеся на определенную книгу или папку.
    Например, чтобы отобрать ссылки на книгу с именем » Отчет за 2-е полугодие 2018 » необходимо задать в поле текст: *[Отчет за 2-е полугодие 2018.xls*]* . Звездочка после xls не случайна — это избавляет от необходимости определять конкретное расширение для книги(xlsm, xlsx,xlsb,xls и т.п.).
    Если необходимо отобрать ссылки на любые книги из папки » Маркетинг «, текст необходимо задать такой *\Маркетинг\*

    Просматривать связи:

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

    После нахождения связи:
    выбирается действие вывода результата

    • выделить ячейки со связями- будут выделены обычным выделением все ячейки, в которых так или иначе присутствуют найденных связи. После этого с выделенными ячейками можно будет делать любые действия, доступные для ячеек: залить цветом, удалить содержимое, изменить параметры и т.д.
      Примечание: Если связь в ячейке присутствует не напрямую, а через именованный диапазон, то такая ячейка не будет определена. Для того, чтобы найти такие связи лучше использовать вывод на лист.
    • выделить ячейки цветом — все ячейки, в которых так или иначе присутствуют найденных связи, будут закрашены выбранным цветом
      Примечание: Если связь в ячейке присутствует не напрямую, а через именованный диапазон, то такая ячейка не будет определена. Для того, чтобы найти такие связи лучше использовать вывод на лист.
    • вывести список ячеек и связей на отдельный лист — будет создана новая книга с одним листом, в котором списком будут выведены все найденные связи с указанием:
      • Имя листа — лист, где содержится ссылка, если ссылка является частью формулы, проверки данных или условного форматирования. Если связь содержится внутри именованного диапазона, то в это поле записывается область действия имени: [Книга], если область действия книги и имя листа, если конкретный лист.
      • Адрес ячейки — ячейка, в которой связь. В случае с именованным диапазоном — выводится имя диапазона
      • Формула — формула листа, проверки данных, условного форматирования или именованного диапазона
      • Тип — тип объекта, в котором обнаружена связь: формула, проверка данных, условное форматирование или именованный диапазон

      Расскажи друзьям, если статья оказалась полезной:

      Видеоинструкции по использованию надстройки MulTEx

      Поделитесь своим мнением

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

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *