Некоторые файлы Excel медленно работают при наличии источников данных
Если файл Excel содержит ссылки на данные Excel (см. Ссылки на данные Excel) и слишком много стилей ячеек, то может начаться очень медленная прокрутка и Excel не будет реагировать на действия пользователя.
Такая проблема может возникнуть, если в файле Excel содержится больше чем 3000 стилей ячеек. Вы можете узнать количество стилей ячеек, выполнив следующую команду VBA в Окне интерпретации :
Подробные инструкции для выполнения такой проверки:
- Нажмите Alt + F11 , чтобы открыть редактор VBA.
- Перейдите в Окно интерпретации . Если его не видно, то нажмите Вид → Окно интерпретации .
- Введите следующую команду и нажмите ВВОД
print Activeworkbook.Styles.Count
Решение: Очистите проблемные рабочие книги
- Скопируйте листы в новую рабочую книгу
- Выберите все листы в рабочей книге, удерживая SHIFT и нажав на первый и последний лист
- Нажмите на выборку правой кнопкой мыши: Переместить или скопировать… → В книгу: (новую книгу) , и установите флажок Создать копию → OK
- Сохраните новую рабочую книгу под другим названием
В Office 2007 и 2010 при копировании листа в новую рабочую книгу происходит копирование всех неиспользуемых стилей ячеек. Для предотвращения такого поведения, сперва активируйте следующее исправление:
Если ваша версия Office полностью обновлена, то это исправление, скорее всего, уже установлено. Тем не менее, вам необходимо активировать это исправление либо применив решение Fix it , предоставляемого Microsoft, либо добавив данные в реестр, следуя инструкциям в следующих статьях Microsoft:
- Microsoft Office 2007: KB2553085
- Microsoft Office 2010: KB2598127, которое было выпущено в рамках обновления Office 2010 от апреля 2012 г.
Если активация исправления не дала результата, примените обновление, описанное в разделе Решение упомянутых выше статей Microsoft.
Используйте средство очистки, рекомендуемое Microsoft в статье KB213904. Запустите XLStyles Tool, нажмите кнопку Получить файл Excel , установите флажок Сделать все стили ячеек «Обычными» , затем нажмите кнопку Обработать файл . Открыв в Excel очищенный файл, сохраните его под другим названием.
Откройте редактор VBA, нажав Alt + F11 . Выберите рабочую книгу и нажмите кнопку Вставка → Модуль . Затем вставьте один из следующих макросов и запустите его, нажав F5 : Макрос DeleteCustomStyles() из рабочей книги удалит все пользовательские стили ячеек.
Sub DeleteCustomStyles() Dim st As Style Dim i, i_cust As Long i_cust = 0 For i = (ActiveWorkbook.Styles.Count) To 1 Step -1 With ActiveWorkbook.Styles(i) If .BuiltIn = False Then On Error Resume Next .Locked = False On Error GoTo 0 If Not .Locked Then .Delete i_cust = i_cust + 1 End If End If End With If (i Mod 100) = 0 Then Debug.Print i Next MsgBox "Styles deleted: " & i_cust & " styles" & Chr(13) & "Styles left: " & ActiveWorkbook.Styles.Count End SubМакрос Remove_Styles() удалит из рабочей книги только неиспользуемые пользовательские стили ячеек. Он похож на макрос, приведенный выше, однако, на его выполнение может уйти очень много времени. Макрос был предоставлен командой поддержки Microsoft Office.
Option Explicit Dim st_array() As String Dim i_x As Long Sub Remove_Styles() Dim stname As String Dim ustname As String Dim uc As Range Dim retval As Boolean Dim ust As Style Dim sh As Worksheet i_x = 0 For Each sh In ActiveWorkbook.Worksheets For Each uc In sh.UsedRange stname = uc.Style.Name retval = Check_Array(stname) If retval = False Then ReDim Preserve st_array(i_x) st_array(i_x) = stname i_x = i_x + 1 End If Next uc Next sh For Each ust In ActiveWorkbook.Styles If ust.BuiltIn = False Then ustname = ust.Name retval = Delete_Styles(ustname) On Error Resume Next If retval = True Then ust.Delete On Error GoTo 0 End If Next ust End Sub Function Delete_Styles(stylename As String) As Boolean Delete_Styles = True Dim i_y As Long For i_y = 0 To i_x - 1 If st_array(i_y) = stylename Then Delete_Styles = False Next i_y End Function Function Check_Array(stylename As String) As Boolean Check_Array = False Dim i_y As Long For i_y = 0 To i_x - 1 If st_array(i_y) = stylename Then Check_Array = True Next i_y End FunctionЛучшим решением в таком случае будет — предотвратить вставку неиспользуемых стилей ячеек, но для этого Microsoft необходимо исправить программную ошибку в Excel 2007 и более поздних версиях. Компания Microsoft не предоставила исправление, так как на текущий момент считает, что ошибка не вызывает достаточно значимых последствий для бизнеса. Если у вашей компании есть контракт на поддержку Microsoft Office и вы хотите обратиться в Microsoft за исправлением, вы можете указать номер обращения Microsoft 113012810172527.
Пояснение
Некоторые рабочие книги .xlsx содержат большое число (несколько десятков тысяч) стилей ячеек из-за программных ошибок и проблем в архитектуре кода Excel. Вклад в проблему также вносят средства отчетности, которые иногда формируют неоправданно много стилей ячеек.
Почему долго сохраняется файл excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Почему долго сохраняется файл excel
Отчет сохраняется по 40 минут а формируется за 1 минуту. Строк конечно много около 3500 строи 5 столбцов. Подскажите что можно сделать!
баян. в поиск.телепатов здесь нет
моксель помоему называется утилита. посмотрите ее.
убери автовысоту строк
(4) Ага. Оно самое.)
// (4)+: может пригодится кому.
Процедура ОбработкаЯчейкиТаблицы(Расшифровка, ФСО, Таблица, ТабАдрес) Экспорт
Перем тЗнач,тТхт,тФайл,тКаталог,тТабДок,тКонвертор,тКнига;
// вот єтот кусок позволит в какую-нито ячейку (например серенькую такую с текстом «сохр.в EXCEL») запихнуть
// расшифровку, представляющую из себя СЗ, в котором есть значение «*» (или сразу имя сохраняемого EXCEL-файла) с
// представлением «Сохранить в этот EXCEL-файл!» — выбирать имя EXCEL-файла и сохранять в него отчет во-первых
// быстро, а во-вторых корректно (а то моксель при дефолтном сохранении иногда корячит числа), а в-третьих,
// при повторном тырке в такую ячейку в качестве EXCEL-файла для выбора «вспоминается» ранее выбранный файл
// сам всунул в глобальник один раз и с тех пор не парюсь.
Если ТипЗначенияСтр(Расшифровка)=»СписокЗначений» Тогда
тЗнач=Расшифровка.Получить(«Сохранить в этот EXCEL-файл!»);
Если (ТипЗначенияСтр(тЗнач)=»Строка»)И(ПустоеЗначение(тЗнач)=0) Тогда
тФайл=СтрЗаменить(тЗнач,»*»,»»); тКаталог=»»; ФСО=Найти(тФайл,»\»);
Пока ФСО>0 Цикл тКаталог=тКаталог+Лев(тФайл,ФСО); тФайл=Сред(ФСО,ФСО+1); ФСО=Найти(тФайл,»\»); КонецЦикла;
Если ПустоеЗначение(тКаталог)=1 Тогда тКаталог=КаталогПользователя() КонецЕсли; ФСО=0;
Если ФС.ВыбратьФайл(1,тФайл,тКаталог,»XLS-файл (Книга EXCEL) для сохранения (экспорта) отчета:»,
«XLS-файлы|*.xls»,»xls»,177)=0 Тогда
тТхт=»XLS-Файл для сохранения не выбран — СОХРАНЕНИЕ НЕ ВЫПОЛНЕНО. «;
Сообщить(тТхт,»!!»); Предупреждение(тТхт,11); Возврат;
КонецЕсли;
тФайл=тКаталог+тФайл;
Пока ФС.СуществуетФайл(тФайл)=1 Цикл
тТхт=»[«+тФайл+»] уже существует»;
Если Вопрос(тТхт+» — ПЕРЕЗАПИСАТЬ. «,»Да+Нет+Отмена»,11)<>«Да» Тогда
тТхт=тТхт+» — СОХРАНЕНИЕ НЕ ВЫПОЛНЕНО. «;
Сообщить(тТхт,»!!»); Предупреждение(тТхт,11); Возврат;
Иначе ФС.УдалитьФайл(тФайл) КонецЕсли;
КонецЦикла;
тТабДок=СоздатьОбъект(«ТабличныйДокумент»); тТабДок.ЗагрузитьИзТаблицы(Таблица);
тКонвертор=СоздатьОбъект(«КонвертерExcel»); тКнига=тКонвертор.Создать();
тКнига.ПараметрыКонвертера.РаспознаватьЧисловыеЗначения=1;
тКнига.Листы.Добавить(«Таблица из 1с»,тТабДок); тКнига.Записать(тФайл);
тТабДок=»»; тКнига=»»; тКонвертор=»»;
тЗнач=СоздатьОбъект(«СписокЗначений»); Расшифровка.Выгрузить(тЗнач);
тЗнач.Установить(«Сохранить в этот EXCEL-файл!»,тФайл);
Таблица.Область(ТабАдрес).Расшифровка(тЗнач); Возврат;
КонецЕсли;
КонецЕсли;
// а тут всё остальное.
КонецПроцедуры //ОбработкаЯчейкиТаблицы
(6)+: у меня вообще-то этот кусок допилен до возможности добавлять таблцу в выбранную книгу на новый лист или заменой существующего листа. но этот шаг падаван должен сделать сам. 🙂
НАРОД Спасибо ОГРОМНОЕ за отклики на мою проблему, очень приятно что помогаете. сейчас буду пробывать, а результатах напишу))
Почему долго сохраняется файл excel
Наличие огромного количества форматирования существенно тормозит работу Excel
Представьте себе таблицу 65000 строк и 50 столбцов. У каждой ячейки отличаются цвета, уникальный шрифт и размер текста. Как думаете, будет по такому файлу курсор перемещаться шустро? Если у вас не MainFrame, то вряд ли.
Старайтесь оформлять только то, что нужно оформить. Не применяйте форматирование ко всему столбцу или строке, если можно выделить только необходимое количество ячеек. Ну а если попался такой «форматированный» файл, то в Excel есть замечательная кнопка — Очистить Форматы
Тормозить Excel может и из-за проблем с принтером
А точнее, если не подключен тот принтер, который в редакторе выбран по-умолчанию. Но эта проблема характерна для Экселя 2007 и старше. При этом запуск файла происходит очень медленно. Исправляется это выбором нового работающего принтера или включением не работающего. Или установкой по-умолчанию виртуального принтера «Microsoft XPS Document Writer»
Наличие скрытых объектов
Скрытые объекты могут попасть в таблицу в результате копирования данных из другого файла. Причем объект переносится с нулевыми размерами, из-за чего, при выделении его не видно. При большом их количестве, это может существенно затормозить работу.
Открываем проблемный файл и убеждаемся, что можем найти и выделить скрытые объекты. Для этого нажимаем Alt+F11, откроется редактор Microsoft Visual Basic. Нажимаем F4, в выпадающем списке выбираем ЭтаКнига Workbook и в поле DisplayDrawingObjects ставим значение -4104 -xlDisplayShapes. Далее, на Листе Экселя выполняем Главная — Найти и выделить — Выделение группы ячеек — Объекты, нажимаем ОК и потом клавишу Delete. Естественно, лучше сделать копию файла и потренироваться на нем.
Перегруженность формулами
Если документ большой, с несколькими листами и по некоторым из них проводится агрегация данных, то вычисление на слабых машинах, ноутбуках и нетбуках будет проходить медленно и долго. Если документ «свой» и его можно переделать, то желательно заменить неиспользуемые формулы — значениями. Делается это так. Выделяете необходимый диапазон ячеек, щелкаете правой кнопкой мыши и выбираете Копировать.
И так же на этот выделенный диапазон щелкаете ПКМ, выбираете Специальная вставка и указываете Значения. Таким образом, уменьшается количество формул, по которым Excel должен проводить расчеты.
Так же не забывайте использовать более быстрые формулы:
Используйте ЕСЛИОШИБКА, она отрабатывает быстрее комбинации ЕСЛИ + ЕОШ
Используйте — для конвертации логических значений в ноли и единицы.
Предпочтительней использовать комбинацию ИНДЕКС + ПОИСКПОЗ вместо ВПР
МАКС(А1;0) работает быстрее ЕСЛИ(А1>0;A1;0)
Долгое открытие из-за большого размера
Причем, часто бывает, что количество данных в таблицах, пропорционально не соответствует размеру файла. Последний, чаще всего больше. Такое может быть, когда файл побывал или находится в Общем доступе. При установке этого режима, Excel ведет журнал изменений, куда попадают все изменения за период, указанный в параметрах.
Отключить или поменять период, можно в Excel 2007 и старше, во вкладке Рецензирование — Доступ к книге. И на вкладке Подробнее выставляете значения какие нужно.
Если документ сохранен в формате xlsx или xls, то можно сохранить его в формат xlsb. И скорость, с какой будет открываться файл, приятно удивит. Увеличение скорости достигается за счет того, что все данные в этом файле сохраняются в бинарном формате, более «родном» для Excel, который требует меньше ресурсов для разбора и преобразования документа. Плюс ко всему этому, размер файла станет меньше.
Опять же, если документ сохранен в формате xlsx и долго открывается, можно открыть его с помощью любого архиватора (например winrar) и поудалять папки revisions и worksheets. Первая хранит все изменения, при включенном режиме Общий доступ. Вторая папка содержит изображения. Данные операции проводите на копии файла. Кстати, если в документе имеются изображения, не забывайте их сжимать. Делается это через вкладку Формат — группа Изменить — Сжатие рисунков и указать предпочтительное качество. Поможет уменьшить размеры изображений, что положительно отразится на размере файла.
Медленно запускаться файл может в Excel 2003 и 2007, если документ открывается с сетевого ресурса. Но при копировании на локальный компьютер — запуск происходит быстро. Лечится это созданием отсутствующих разделов и добавлением значения:
HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Excel\Security\FileValidation (для 2007)
HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\11.0\Excel\Security\FileValidation (для 2003)
Enableonload=dword:00000000
Данный параметр отключает функцию Office File Validation, которая отвечает за проверку двоичного формата файла и проверяет, соответствуют ли он формату файла MS Office.
Включаем\Отклчючаем многопоточные вычисления
Данный параметр находится в Настройки — Дополнительно — вкладка Формулы. При наличии нескольких ядер процессора нужно включить данный пункт и указать количество потоков равное количеству ядер. Если же приложением тормозило и до этого с включенным параметром, то отключите. В идеале, эта настройка ускоряет работу при работе с формулами.
Надеюсь, после всех манипуляций, файл у вас больше не будет долго открываться, а Excel не будет тормозить при запуске.
И ниже, очень интересное и подробное видео с комментариями, как ускорить работу Excel