Excel отключить автоматическое обновление ссылок в Excel
Обновлено: 06.07.2024
Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.
Обзор
В этой пошаговой статье описывается, как управлять сообщением при запуске об обновлении связанных книг в Microsoft Office Excel.
При открытии книги, содержащей ссылки на ячейки в других книгах, может появиться следующее сообщение: Эта книга содержит ссылки на другие источники данных. Если вы обновите ссылки, Excel попытается получить последние данные. Если вы не обновляете, Excel использует предыдущую информацию. Вы можете нажать «Обновить» или «Не обновлять».
По умолчанию Excel отображает это сообщение. Вы можете контролировать, будет ли он отображаться, и будет ли Excel обновлять ссылки вручную или автоматически. Чтобы внести эти изменения, используйте следующие методы.
- Независимо от выбранных параметров Excel по-прежнему отображает сообщение, если книга содержит недействительные или неработающие ссылки.
- Чтобы найти информацию о ссылках в книге в Excel 2010 и более поздних версиях, выберите "Редактировать ссылки" в группе "Запросы и подключения" на вкладке "Данные".
Кроме того, следующие параметры применяются только в том случае, если рабочая книга, содержащая основные данные, закрыта. Если книга с основными данными уже открыта, когда вы открываете книгу, содержащую ссылки, ссылки обновляются.
Автоматическое обновление без сообщений
Чтобы скрыть сообщение и автоматически обновить ссылки при открытии книги в Excel, выполните следующие действия:
- Если флажок Запрашивать обновление автоматических ссылок снят, ссылки обновляются автоматически. Кроме того, не появляется сообщение.
- Этот параметр применяется только к текущему пользователю и влияет на каждую книгу, которую открывает текущий пользователь. Это не влияет на других пользователей тех же книг.
Обновление вручную и без сообщений
Если вы делитесь этой книгой с другими людьми, у которых нет доступа к источникам обновленных ссылок, вы можете отключить обновление и запрос на обновление. Чтобы скрыть сообщение и оставить ссылки (не обновленными) до тех пор, пока вы не решите их обновить, выполните следующие действия:
В Excel выберите «Редактировать ссылки» в группе «Запросы и подключения» на вкладке «Данные».
Нажмите Запрос на запуск.
Нажмите кнопку "Не отображать предупреждение и не обновлять автоматические ссылки".
Если вы решите не обновлять ссылки и не получать сообщения, пользователи рабочей книги не узнают, что данные устарели. Этот выбор влияет на всех пользователей книги. Однако этот выбор применим только к этой конкретной книге.
Чтобы обновить ссылки вручную, выполните следующие действия:
Не отображать оповещения и ссылки для обновления
Если в книге выбран параметр Не отображать ссылки предупреждений и обновлений, этот выбор игнорируется. Если человек, открывающий книгу, установил флажок Запрашивать обновление автоматических ссылок, появится сообщение. Если нет, ссылки автоматически обновляются.
Немногие события в Excel вызывают большее разочарование, чем оповещение «эта книга содержит ссылки на один или несколько внешних источников» и невозможность найти/разрушить вызывающие это внешние ссылки. В этом руководстве я расскажу вам о пяти шагах, которые помогут решить проблему.
В видео, которое следует за письменными инструкциями, я исправляю проблему, используя реальную ситуацию из версии 1.10 базовой модели приобретения отеля Майкла в нашей библиотеке моделей Excel для недвижимости.
Хотите научиться создавать финансовые модели недвижимости институционального качества самостоятельно и с нуля? Присоединяйтесь к нашей программе обучения финансовому моделированию недвижимости: The A.CRE Accelerator.
В этом руководстве я покажу вам, как навсегда удалить раздражающее предупреждение "Эта книга содержит ссылки".
Почему это происходит? Книги Excel с внешними ссылками
Если вы когда-либо открывали файлы Excel, которыми с вами поделился коллега, посредник или источник в Интернете, вы, вероятно, сталкивались с сообщением «эта книга содержит ссылки на один или несколько внешних источников, которые могут быть небезопасно». Это связано с тем, что рабочая книга, которую вы пытаетесь открыть, имеет ссылку на другую рабочую книгу, расположенную либо на вашем компьютере, либо на локальном сервере, либо на компьютере другого пользователя, либо на удаленном сервере.
Чаще всего эти ссылки являются безопасными и были непреднамеренно добавлены в книгу.Обычно это происходит, когда пользователь заимствует логику из отдельной книги при создании книги, которую вы сейчас открываете, и при этом непреднамеренно оставляет одну или несколько ссылок на первую книгу. Таким образом, вы можете открыть рабочую книгу у брокера, который при моделировании конкретной сделки ссылался на данные в отдельной рабочей книге, не понимая, как избежать создания внешних ссылок.
Ознакомьтесь с нашей публикацией "Оптимальные методы финансового моделирования недвижимости", чтобы узнать, как избежать случайного создания внешних ссылок
В других случаях ссылка создается намеренно, но без намерения причинить вред. Например, организация может хранить данные в центральной рабочей книге Excel, с которой другие рабочие книги затем взаимодействуют через ссылки. Чтобы привести пример недвижимости, фирма REPE может хранить данные о форвардной кривой доходности в отдельном файле Excel. Каждый раз, когда открывается фирменная модель недвижимости фирмы, она автоматически извлекает данные кривой доходности из отдельного файла Excel, содержащего эти данные.
В редких случаях внешняя ссылка создается намеренно, но со злым умыслом. Внешние ссылки в этих файлах могут извлекать информацию или код в вашу книгу, которые оставляют на вашем компьютере вредоносное, шпионское ПО или что-то еще. Хотя я никогда не сталкивался с такой ситуацией за свою более чем 18-летнюю карьеру в сфере недвижимости, открыв тысячи рабочих тетрадей, которыми мне поделились другие, это все же случается. Таким образом, чтобы предупредить вас о такой возможности, Excel дает возможность «Обновлять» или «Не обновлять» любые внешние ссылки в книге каждый раз, когда вы открываете файл.
Когда удалять (т.е. прерывать) внешние ссылки
Итак, когда вы должны удалить или, на жаргоне Excel, «сломать» эти внешние ссылки? По моему опыту, вы всегда должны удалять внешние ссылки, если только вы не были явно уведомлены о том, что Рабочая книга содержит важные внешние ссылки.
В сфере недвижимости это обычно означает, что ваша компания (или вы) намеренно добавили внешние ссылки для расширения функциональности модели. В противном случае внешняя ссылка, скорее всего, была включена непреднамеренно и может быть удалена без ущерба для функциональности модели.
Как удалить (т.е. сломать) внешние ссылки?
Так как же разорвать внешние ссылки и, таким образом, удалить надоедливое предупреждение «эта книга содержит ссылки»? Выполните следующие действия, чтобы удалить внешние ссылки в модели и навсегда устранить эту проблему:
Я. Выберите «Не обновлять»
Это важно. Хотя подавляющее большинство этих внешних ссылок не являются вредоносными, на всякий случай лучше не обновлять ссылку; это означает, что Excel фактически не будет пытаться извлечь данные из этой внешней ссылки.
Поэтому, когда появляется предупреждение "Эта книга содержит ссылки", обязательно выберите "Не обновлять".
II. Удалить внешние ссылки в формулах ячеек
Наиболее вероятные места, где можно найти непреднамеренные внешние ссылки, — это формулы в реальных ячейках. Их не всегда легко найти, но их легче всего удалить. Это связано с тем, что в Excel есть встроенная функция автоматического разрыва этих внешних ссылок.
Чтобы разорвать ссылку внутри формулы в ячейке:
- Перейдите на ленту "Данные".
- В разделе "Запросы и подключения" выберите "Изменить ссылки".
- Появится диалоговое окно "Редактировать ссылки" со списком внешних ссылок.
- Выберите каждую из ссылок и нажмите кнопку "Разорвать ссылку" для каждой.
Если в формуле ячейки содержится внешняя ссылка на источник, эта ссылка исчезнет из списка. Если при выполнении этого шага из списка будут удалены все ссылки, то все готово!
Но, скорее всего, вы уже знали об этом шаге и поэтому нашли эту запись в блоге. Описанные выше действия не помогли решить проблему, поэтому давайте перейдем к шагу III.
III. Удалить графики/диаграммы с внешними ссылками
Иногда создатели модели копируют график или диаграмму из одной модели и вставляют в другую. Они делают это, не осознавая, что ссылки внутри графика/диаграммы указывают на исходный график/диаграмму. В результате эти графики/диаграммы бесполезны и должны быть удалены.
Чтобы проверить, содержит ли график/диаграмма внешние ссылки:
- Выберите график/диаграмму.
- Перейдите на ленту "Дизайн" под заголовком "Инструменты для работы с диаграммами".
- В разделе "Данные" выберите "Выбрать данные".
- Есть как данные серий (список слева), так и данные меток (список справа). Выберите каждый из них и нажмите "Изменить".
- Откроется диалоговое окно "Редактировать серию" с полем "Значения серии".
- Проверьте, указывают ли значения на путь к файлу (например, C://users/jbird/…) или на именованный диапазон, не включенный в книгу.
- Если ряд указывает на что-либо из вышеперечисленного, вам следует удалить диаграмму/график.
Выполните описанные выше шаги для каждого графика/диаграммы в рабочей книге.
Обратите внимание, что встроенная в Excel функция "разрывать внешние ссылки" фактически заменяет ссылки на пути к файлам на графиках/диаграммах. Следствием этого является то, что ссылки должным образом разорваны, но диаграмма/график больше не будет обновляться на основе изменений, внесенных в модель. Поэтому, если вам нужны данные из диаграммы/графика, вам придется воссоздать их, используя данные из фактической рабочей книги.
< бр />
Удалить графики/диаграммы, содержащие нежелательные внешние ссылки
IV. Удалить именованные диапазоны или именованные ячейки, указывающие на внешние ссылки
Если встроенная в Excel функция разрыва ссылок не работает, скорее всего, это связано с тем, что существует именованный диапазон или ячейка, содержащая внешнюю ссылку. Excel не может автоматически разорвать эти внешние ссылки, поэтому вам придется найти и удалить их самостоятельно.
Чтобы найти и удалить внешние ссылки из именованных диапазонов и ячеек:
- Перейдите на ленту "Формулы".
- В разделе "Определенные имена" выберите "Диспетчер имен".
- Нажмите на заголовок "Относится к", чтобы отсортировать по имени.
- Выберите каждый именованный диапазон/ячейку, которая ссылается на путь к файлу (например, C:\, D:\, F:\ и т. д.)
- Нажмите "Удалить", чтобы навсегда удалить именованный диапазон, содержащий внешнюю ссылку.
- Совет: используйте SHIFT + ЩЕЛЧОК, чтобы выбрать и удалить сразу несколько элементов.
- После удаления всех именованных диапазонов, указывающих на внешнюю ссылку, нажмите "Закрыть".
- Перейдите на ленту "Данные".
- В разделе "Запросы и подключения" выберите "Редактировать ссылки" (если кнопка "Редактировать ссылки" неактивна, все готово!)
- В диалоговом окне "Редактировать ссылки" проверьте, отображаются ли ссылки в списке.
- Если ссылки все еще находятся в списке, выберите каждую из них и нажмите кнопку "Разорвать ссылку" для каждой из них.
После выполнения 10 и 11 выше, если все внешние ссылки были удалены из книги, список в диалоговом окне «Редактировать ссылки» будет пустым. Если кнопка «Разорвать ссылку» по-прежнему не удаляет одну или несколько ссылок, перейдите к следующему шагу.
< бр />
Наиболее распространенная причина постоянных предупреждений о ссылках на обновления
В. Удалить правила условного форматирования с помощью внешних ссылок
Следующее место для поиска внешних ссылок — правила условного форматирования. Этот шаг не менее (если не более) сложный, чем предыдущий, но есть один или два способа сделать это проще:
- Выберите первый рабочий лист (т. е. крайнюю левую вкладку) в рабочей книге.
- Нажмите на серый треугольник между столбцом A и строкой 1.
- Перейти на вкладку "Главная".
- В разделе "Стили" выберите раскрывающийся список "Условное форматирование".
- В раскрывающемся списке "Условное форматирование" нажмите "Управление правилами".
- Откроется диалоговое окно «Диспетчер правил условного форматирования».
- В диалоговом окне вверху есть раскрывающийся список "Показать правила форматирования для:", а ниже список правил для текущей книги.
- Если для текущей книги существуют правила, удалите все правила, в которых «Формула:» содержит ссылку на путь к файлу внешней ссылки или именованный диапазон внешней ссылки (например, =$C$17= «Скидка», $C$17). >'c:\user\…' и т. д.)
- После того как все правила на этом листе со ссылками на внешние ссылки будут удалены, перейдите к следующему листу, используя раскрывающееся меню "Показать правила форматирования для:" в том же диалоговом окне.
- Пройдитесь по каждому листу, удалив все правила условного форматирования с внешними ссылками.
- Нажмите «Закрыть», чтобы закрыть диалоговое окно «Диспетчер правил условного форматирования».
- Перейдите на ленту "Данные".
- В разделе "Запросы и подключения" выберите "Редактировать ссылки" (если кнопка "Редактировать ссылки" неактивна, все готово!)
- В диалоговом окне "Редактировать ссылки" проверьте, отображаются ли ссылки в списке.
- Если ссылки все еще находятся в списке, выберите каждую из них и нажмите кнопку "Разорвать ссылку" для каждой из них.
- Нажмите "Закрыть", чтобы закрыть диалоговое окно "Редактировать ссылки".
- Сохранить книгу
При закрытии диалогового окна «Редактировать ссылки» кнопка «Данные>Запросы и подключения>Редактировать ссылки» теперь должна быть неактивна. Это означает, что вы успешно удалили все внешние ссылки, и оповещение «эта книга содержит ссылки на один или несколько внешних источников» больше не будет появляться.
Если удаление именованных диапазонов не помогло, удаление правил условного форматирования с внешними ссылками должно помочь
Что делать, если описанное выше не помогло решить проблему?
Если выполнение описанных выше действий не устранило проблему, по моему опыту, происходит одно из двух. Либо вы пропустили именованный диапазон, либо правило условного форматирования, содержащее внешнюю ссылку. Это довольно распространено, когда у вас есть десятки (или сотни) именованных диапазонов или правил условного форматирования. Поэтому вернитесь к шагам IV и V еще раз для подтверждения.
Если вы уверены, что удалили все именованные диапазоны и правила условного форматирования, указывающие на внешние ссылки, и если рабочая книга поддерживает макросы (т. е. путь к файлу заканчивается на .xlsm, xlsb, xlm, xlb и т. д.) , то вам может потребоваться просмотреть каждый макрос, чтобы убедиться, что внешняя ссылка не используется.
Кроме того, я читал на некоторых форумах, что после неудачных попыток повторное сохранение файла в виде файла Excel другого типа (например, переход с xls на xlsx или с xlsx на xlsm) решает проблему. Я никогда не тестировал и не нуждался в тестировании этой техники. Но если ничего не помогло, попробуйте.
И если ничего не работает, вам придется либо нажимать «Не обновлять» при каждом открытии файла, либо перестраивать модель.
Видеоучебник – Как навсегда удалить оповещение «Обновить ссылки» в файлах Excel
В дополнение к письменным инструкциям, приведенным выше, я создал видеоруководство о том, как отключить оповещение об обновлении ссылок в Excel. Если у вас есть вопросы об этой методике или я что-то упустил, дайте мне знать!
Об авторе: Спенсер Бертон родился и вырос на северо-западе США и имеет почти 20-летний опыт работы в сфере жилой и коммерческой недвижимости. За свою карьеру он застраховал коммерческую недвижимость на сумму 30 миллиардов долларов в некоторых из крупнейших институциональных фирм по недвижимости в мире. В настоящее время он является главой отдела инвестиций в недвижимость и членом команды основателей Stablewood Properties. Спенсер имеет степень бакалавра международных отношений Университета штата Флорида и степень магистра финансов в сфере недвижимости Корнельского университета.
В Excel вы можете связать значения ячеек с ячейками из другой книги. Когда оба файла открыты, значения в связанной книге изменяются по мере обновления значений в исходной книге. Допустим, у вас есть значение Файл 2.xlsx со значением 20 в ячейке A1 и Файл 1.xlsx, в котором ячейка A1 связана с ячейкой A1 из файла 2. xlsx.
Когда вы меняете значения в исходном файле и открываете целевой файл, вам будет предложено подтвердить, хотите ли вы обновить значения. Это параметр по умолчанию в Excel, который можно изменить для автоматического обновления всех ссылок при открытии файла.
Если вы хотите, чтобы значение автоматически обновлялось при открытии файла книги 1.xlsx, выполните следующие действия:
<р>1. Измените значение в исходном файле (например, измените ячейку A1 с 11 на 20), чтобы вы могли увидеть изменения позже в файле 1.xlsx.
В результате вы включили автоматическое обновление ссылок, а также отключили подсказку при открытии файла. Итак, если теперь вы снова откроете связанную книгу (файл 1. xlsx), значение в ячейке A1 будет автоматически обновлено значением ячейки A1 в файле 2.xlsx (20).
Файлы Google Таблиц могут ссылаться только на внешние URL-адреса, поэтому ссылки обновляются в режиме реального времени.
Когда мы запускаем книгу Excel с внешними ссылками, существующими на открытом листе, появится сообщение об обновлении ссылки и попросит нас обновить ссылки, и мы можем нажать «Обновить», «Не обновлять» или «Справка», чтобы сделать больше операций, или мы можем нажать кнопку закрытия, чтобы закрыть его напрямую. В любом случае, мы должны сначала закрыть это сообщение, иначе оно всегда будет плавать на текущем листе, и мы не сможем выполнять какие-либо операции на этом листе. И когда мы снова открываем эту книгу, сообщение все равно появляется, нам приходится повторять вышеуказанные операции снова и снова. Иногда это бесполезно и очень раздражает. На самом деле, если мы хотим игнорировать это сообщение, мы можем каким-то образом отключить это сообщение. Это руководство поможет вам решить эту проблему.
Подготовьте книгу с внешними ссылками, например, источник данных из другой книги в функции ВПР. См. пример ниже.
И при открытии этой книги появляется всплывающее сообщение со ссылкой на обновление.
Если мы хотим игнорировать его при открытии книги, мы можем отключить его с помощью следующих методов.
Способ 1: отключить сообщение об обновлении ссылки с помощью редактирования ссылки
Шаг 1. Нажмите «Данные» на ленте, затем нажмите «Изменить ссылки» в группе «Подключение».
Шаг 2. Во всплывающем диалоговом окне «Редактировать ссылки» внешняя ссылка отображается и выбирается по умолчанию. Нажмите кнопку «Приглашение при запуске» внизу.
Шаг 3. На экране запроса при запуске установите флажок «Не отображать предупреждения и ссылки на обновление».
Шаг 4. Нажмите «ОК», подтвердите и закройте предыдущее диалоговое окно «Редактировать ссылки».
Шаг 5. Закройте эту книгу. Обратите внимание, что при закрытии этой книги появляется всплывающее окно «Хотите сохранить изменения в «Book2.xlsx»?». Обязательно нажмите «Сохранить», иначе описанная выше операция не будет сохранена.
Шаг 6. Повторно откройте эту книгу. Убедитесь, что на этот раз сообщение со ссылкой на обновление не появляется. Этот метод также хорошо работает для предупреждений.
Способ 2: отключить обновление ссылок с помощью параметра Excel
Шаг 1. Нажмите «Файл» на ленте и выберите «Параметры» в меню.
Шаг 2. В окне параметров Excel нажмите «Дополнительно» на левой панели.
Шаг 3. Перетащите полосу прокрутки, пока не отобразятся общие параметры. По умолчанию установлен флажок «Запрашивать обновление автоматических ссылок». Снимите флажок и нажмите OK, чтобы сохранить операцию.
Шаг 4. Закройте книгу и снова откройте ее. Убедитесь, что на этот раз сообщение со ссылкой на обновление исчезло.
Способ 3. Отключение ссылок обновления с помощью кода VBA
Шаг 1. На текущем видимом листе щелкните правой кнопкой мыши вкладку имени листа, чтобы открыть меню управления листами.Выберите Просмотреть код, появится окно Microsoft Visual Basic для приложений.
Или вы можете войти в окно Microsoft Visual Basic для приложений через Developer->Visual Basic. Вы также можете одновременно нажать клавиши Alt + F11, чтобы открыть его.
Шаг 2. Введите приведенный ниже код. Это может предотвратить появление сообщения со ссылкой на обновление при открытии книги.
Шаг 3. Сохраните код и снова откройте книгу. Убедитесь, что сообщение об обновлении ссылки исчезло, но вместо него отображается предупреждение системы безопасности.
В этом посте рассказывается, как найти и заменить сразу несколько значений с помощью макроса VBA или формулы в Excel. Как сделать множественный поиск и замену в Excel. Предположим, что у вас есть несколько ячеек, содержащих .
В этом посте показано, как разрешить комментарии на защищенном листе в Excel. Вы можете легко вставлять комментарии в ячейки обычного рабочего листа в Excel, но если вы хотите вставить комментарий в рабочий лист, который .
В этом посте показано, как переключиться с нижнего регистра на верхний регистр в Excel. и я собираюсь показать вам два разных способа преобразования текста в верхний регистр с помощью формулы или макроса VBA в Excel 2013, Excel.
В этом посте показано, как скрыть чередующиеся строки или столбцы в Excel или как скрыть каждую третью, четвертую, пятую строку или столбец в Excel. Если вы хотите скрыть каждую вторую строку на текущем листе, как .
В этом посте показано, как использовать макрос VBA для сохранения файла Excel и перезаписи любого существующего файла без запроса, чтобы вы получили маленькое окно с сообщением, что файл уже существует do .
В этом посте рассказывается, как подсчитать количество ячеек, содержащих нечетные или четные числа в диапазоне ячеек, с помощью формулы в Excel 2013/2016. Как подсчитать ячейки, содержащие нечетные числа, с помощью .
В этом посте рассказывается, как подсчитать количество ячеек, содержащих отрицательные числа в диапазоне ячеек, с помощью формулы в Excel 2013/2016. Вы можете подсчитать количество отрицательных чисел в своих данных с помощью простых функций.
В этом сообщении вы узнаете, как подсчитать количество ячеек, которые не пусты или не пусты, в ячейках заданного диапазона, используя формулу в Excel 2013/2016. Как подсчитать количество ячеек, которые не являются пустыми в определенном диапазоне .
В этом посте рассказывается, как подсчитать количество ячеек меньше определенного числового значения в ячейках заданного диапазона, используя формулу в Excel 2013/2016. Как подсчитать ячейки, которые меньше определенного .
Читайте также: