База знаний KB0204
  2. Ресурсы
  3. База знаний
  4. KB0204

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

Проблема

Если файл Excel содержит ссылки на данные Excel (см. 21. Ссылки на данные Excel) и слишком много стилей ячеек, то может начаться очень медленная прокрутка и 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. Вклад в проблему также вносят средства отчетности, которые иногда формируют неоправданно много стилей ячеек.

Поделиться