Excel долго обновляет связи

Обновлено: 23.01.2025

Зависает (виснет, тормозит) Excel 2013 2016 при редактировании любых ячеек

Случается так, что Excel начинает зависать, тормозить и глючить. Причин может быть много. В данной заметке я рассмотрю одну из многих причин, касающуюся в частности Excel 2013 версии. Описанная здесь проблема, возможно, наблюдается и в других версия Excel: 2007, 2010, а так же в 2016, не факт, но возможно.

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

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

Второй способ, на мой взгляд самый простой, заключен в отключении одной полезной функции (но из-за которой глючит) в 2013 версии Microsoft Office, которая называется «Автозавершение значений ячеек».

Вот как объясняется эта функция на сайте разработчика: «Если несколько первых знаков, введенных в ячейку, совпадают со знаками записи, уже имеющейся в этом столбце, Excel автоматически подставляет недостающую часть записи. В Excel автоматическое завершение выполняется только для тех записей, которые содержат текст или комбинацию текста и чисел. Эта функция не используется для записей, полностью состоящих из чисел, значений даты или времени».

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

Для отключения этой функции заходим в настройки «Файл — Параметры — Дополнительно» и снимаем две галочки что на рисунке выделены красным прямоугольником.

Можно отключить просто одну галку «Автозавершение значений ячеек», вторая галка сама сделается неактивной.

Опишу решение. (Как решил у себя) Тормозили файлы выгруженные из 1С, но тормозили те, которые пользователи выгружали в формате Xls 97-2003. Если открывать эти файлы в Офисе2016 то через какое-то время (порой и сразу) Excel начинал тупить и съедать проц (40-70%) Решили проблему сохранением файлов в новом формате, причем всех. Если из 10 открытых файлов есть хотя бы 1 файл созданный из 1С в формате 97-2003 то висели все открытые Excel-файлы.

Проверьте, нам помогло. УПП и платформа 8.3.10.2561

Как только отодвигаю эту задвижку и становятся видны листы (TDSheet), так перестают тормозть файлы.

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

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

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

1. Destination workbook contains the external reference (link).

2. Внешняя ссылка (или ссылка) — это ссылка на ячейку или диапазон в книге-источнике.

3. Книга-источник содержит связанную ячейку или диапазон и фактическое значение, возвращаемого в 9-й книге.

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

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

В следующих разделах рассматриваются наиболее распространенные варианты изменения связей.

Обновление всех связей в книге вручную или без них

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

Откройте конечную книгу.

Чтобы обновить связи, на панели доверия нажмите кнопку Обновить. Закройте планку доверия, если вы не хотите обновлять связи (на правом — X).

Частичное обновление связей с другими книгами вручную

Откройте книгу, содержащую связи.

Перейдите в >запросы & подключения >ссылки.

Из списка Источник выберите связанный объект, который необходимо изменить.

Вы можете выбрать отдельные книги, нажав CTRL+щелчок, или все их с помощью CTRL+A.

Изменение книги-источника одной внешней ссылки без влияния на другие внешние ссылки в конечной книге

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

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

В строка формул наймем ссылку на другую книгу, например C:\Reports\[Budget.xlsx], и замените ее расположением новой книги-источника.

Управление запросом на обновление ссылок при запуске

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

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

Перейдите в >запросы & подключения >ссылки.

Выберите один из следующих трех вариантов:

Разрешим пользователям отображать оповещение или нет

Не показывать оповещение и не обновлять автоматические ссылки

Не отображайте ссылки на оповещения и обновления.

Параметры автоматического и ручного обновления. Для ссылок на формулы всегда установлено автоматическое обновление.

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

Открыв диалоговое окно Изменение связей (Запросы > данных & connections > Изменениесвязей), вы сможете работать с существующими ссылками несколькими вариантами. Вы можете выбрать отдельные книги, нажав CTRL+щелчок, или все их с помощью CTRL+A.

Обновление значений

При этом будут обновлены все выбранные книги.

Изменить источник

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

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

Open Source

Разрыв связи

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

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

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

Вы можете выбрать отдельные книги, нажав CTRL+щелчок, или все их с помощью CTRL+A.

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

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

Чтобы удалить имя:

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

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

В столбце Имя выберите имя, которое следует удалить, и нажмите кнопку Удалить.

Проверка состояния

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

Можно ли заменить одну формулу ее вычисляемой величиной?

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

Нажмите CTRL+C, чтобы скопировать формулу.

Нажмите ALT+E+S+V, чтобы вировать формулу в качестве ее значения, или перейдите на главная> буфер обмена> в > Вэтом > в буфер обмена .

Что делать, если я не подключен к источнику?

Я не хочу заменять текущие данные новыми

Я пытался обновить его в прошлый раз, но это занимает слишком много времени

Кто-то другой создал книгу, и я не знаю, почему я вижу этот запрос

Щелкните Не обновлятьи обратитесь к владельцу книги. Вы также можете выяснить, какие связи находятся в книге. На вкладке Данные в группе Запросы & Connections нажмитекнопку Изменить связи.

Я отвечаю на запрос одинаково каждый раз и не хочу видеть его снова

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

Не отображать запрос и обновлять связи автоматически

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

Откройте меню Файл, выберите раздел Параметры, а затем — Дополнительно.

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

Одинаковый запрос для всех пользователей этой книги

Перейдите в > запросы & подключения > Изменить ссылки > запросана запуск и выберите нужный параметр.)

Примечание: При наличии разорванных связей будет появляться оповещение об этом.

Что делать, если я использую запрос с параметрами?

Закройте конечную книгу.

Откройте конечную книгу.

Связь с параметрическим запросом нельзя обновить без открытия книги-источника.

Почему я не могу выбрать параметр "Вручную" в качестве параметра обновления для определенной внешней ссылки?

Для ссылок на формулы всегда установлено автоматическое.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Здравствуйте! Существуют ли способы ускорения обновления связей между Word и Excel (неформатированный текст)? Также при замене пути будет ли увеличиваться время обновления? Для смены источника использую этот макрос.
внешняя ссылка

2 Ответ от serega 05.03.2012 21:36:04

Вот немного гневное мое письмо в адрес разработчиков MS WORD.

Даю ссылку на их форум, может наша активность подтолкнет их к хоть каким действиям.

3 Ответ от serega 05.03.2012 21:47:13

ReadyFor4GB_20090422 -- для определения Win7x32 полных 4 GB

Ramdisk -- очень простая, но зато рабочая прога для создания Вирт диска. Рекомендую ее, т.к. разрекламировны много красивых прог , много умеюших (например, SuperSpeed.RamDisk.Plus.v10.0.1) , но у меня без вопросов запустился именно Ramdisk
R:\Временные файлы Интернета\Content.Word\ - обрати внимание на букву диска -- именно туда тебе надо перенести "Временные файлы Интернета" в Свойствах обозревателя. Это заставить Word пересенести туда все свои временные файлы. Когда ты увидишь, как за 15 минут какой-нить

WRF.tmp из нулевого размера вырастает до 300 МБ, ты поймешь что такое настоящая ненависть.

4 Ответ от serega 05.03.2012 21:54:00

Надеюсь, помог. Благодарен вашему форуму, т.к. после нескольких лет активного использования связей Word+Excel нашел именно здесь наводку на опцию в Параметрах "Показывать коды полей" . Кстати, в известном макросе "Замена связей" какая-то ошибка или недоработка-- он меняет связи хорошо, но какой элемент в докуенте его останавливает и около 20 связей после него приходится менять в ручную.