База знаний KB0204

Некоторые файлы Excel медленно работают при наличии источников данных

Проблема

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

Такая проблема может возникнуть, если в файле Excel содержится больше чем 3000 стилей ячеек. Вы можете узнать количество стилей ячеек, выполнив следующую команду VBA в Окне интерпретации:

print Activeworkbook.Styles.Count

Подробные инструкции для выполнения такой проверки:

  1. Нажмите Alt+F11, чтобы открыть редактор VBA.
  2. Перейдите в Окно интерпретации. Если его не видно, то нажмите ВидОкно интерпретации.
  3. Введите следующую команду и нажмите ВВОД
    print Activeworkbook.Styles.Count

Решение: Очистите проблемные рабочие книги

  • Скопируйте листы в новую рабочую книгу

    В Office 2013 и более поздних версиях копирование листов между рабочими книгами предотвращает стандартное накопление неиспользуемых ячеек стилей:

    • Выберите все листы в рабочей книге, удерживая SHIFT и нажав на первый и последний лист
    • Нажмите на выборку правой кнопкой мыши: Переместить или скопировать…В книгу: (новую книгу), и установите флажок Создать копиюOK
    • Сохраните новую рабочую книгу под другим названием

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

    Если ваша версия Office полностью обновлена, то это исправление, скорее всего, уже установлено. Тем не менее, вам необходимо активировать это исправление либо применив решение Fix it, предоставляемого Microsoft, либо добавив данные в реестр, следуя инструкциям в следующих статьях Microsoft:

    Если активация исправления не дала результата, примените обновление, описанное в разделе Решение упомянутых выше статей Microsoft.

  • Очистите рабочие книги с помощью средства очистки

    Используйте средство очистки, рекомендуемое Microsoft в статье KB213904. Запустите XLStyles Tool, нажмите кнопку Получить файл Excel, установите флажок Сделать все стили ячеек «Обычными», затем нажмите кнопку Обработать файл. Открыв в Excel очищенный файл, сохраните его под другим названием.

  • Удалите пользовательские стили ячеек из своей рабочей книги с помощью макроса VBA

    Откройте редактор 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. Вклад в проблему также вносят средства отчетности, которые иногда формируют неоправданно много стилей ячеек.

think-cell использует файлы cookie для улучшения функциональности, эффективности и безопасности этого веб-сайта. Если вы хотите пользоваться всеми функциями этого сайта, требуется ваше согласие. С более подробной информацией об использовании файлов cookie компанией think-cell, вашем согласии и правах на конфиденциальность данных можно ознакомиться в нашей Политике конфиденциальности.