База знаний KB0156

KB0156. Не удается вставить данные в таблицу think-cell или формат теряется

Симптомы

У вас есть данные в книге Excel (.xlsx), и вы хотите скопировать и вставить их во внутреннюю таблицу диаграммы think-cell. При вставке данных возникает одна из следующих проблем.

  • Вы видите сообщение: «Microsoft Excel не удается вставить данные».
  • Вставляются только пустые ячейки.
  • Формат даты или чисел утерян, например 1/1/2012 превращается в 40909 или $3.5 превращается в 3.5.
  • После вставки значения процентов отображаются неправильно. На диаграммах 100 % макет диаграммы может измениться.
  • Цвета ячейки не отображаются на диаграмме, хотя вы установили флажок Использовать функцию «Заполнять сверху» Excel.

Эти проблемы также могут возникнуть при использовании ссылок на данные Excel.

Проблема 1

Все описанные выше проблемы, могут возникнуть, если книга .xlsx содержит более ~65 490 стилей ячейки. Вы можете узнать количество стилей ячеек, выполнив следующую команду VBA в Окне интерпретации:
print Activeworkbook.Styles.Count

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

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

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

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

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

    • Выберите все листы в рабочей книге: hold SHIFT удерживайте клавишу 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

Лучшим решением в таком случае будет — предотвратить вставку неиспользуемых стилей ячеек, но для этого Microsoft необходимо исправить программную ошибку в Excel 2007 и более поздних версиях. Компания Microsoft не предоставила исправление, так как на текущий момент считает, что ошибка не вызывает достаточно значимых последствий для бизнеса. Если у вашей компании есть контракт на поддержку Microsoft Office и вы хотите обратиться в Microsoft за исправлением, вы можете указать номер обращения Microsoft 113012810172527, 114021711190574 (Excel 2013).

Пояснение 1

Формат файла, используемый при вставке данных в таблицу think-cell или ее переносе в источник данных Excel (формат файла Biff12/.xlsb), поддерживает приблизительно стилей ячейки 65 490. При переносе данных из рабочей книги .xlsx с большим числом стилей ячейки в таблицу think-cell теряется форматирование или вставка оказывается недоступна.

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

  • Копирование листа в новую рабочую книгу в Excel 2007 или 2010:
    Нажмите на вкладку листа правой кнопкой мыши → Переместите или копируйте…В книгу: (новая книга) и установите флажок «Создать копию».
  • Копирование и вставка ячеек (даже одной ячейки) в другую рабочую книгу в отдельном процессе Excel или во внутренней таблице think-cell

    • Чтобы запустить отдельный процесс Excel, нажмите правой кнопкой мыши на символ Excel на панели задач и нажмите Microsoft Excel 20… Запуск отдельного процесса Excel
    • В Excel 2013 и более поздних версиях удерживайте клавишу ALT нажатой, чтобы запустить отдельный процесс. В появившемся окне Вы хотите запустить новый экземпляр Excel? выберите вариант Да.
    • Проблема возникает, потому что таблица think-cell основана на листе Excel в отдельном процессе Excel.

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

Проблема 2

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

Эта проблема возникает при копировании данных между отдельными процессами Excel, если стиль настраиваемой таблицы с одинаковым именем существует в исходном и целевом файлах. Так как при копировании и вставке также копируются все стили настраиваемой таблицы, последующие попытки копирования и вставки оказываются неудачными. Эту проблему можно воспроизвести и без надстройки think-cell.

Воспроизведение ошибки без think-cell

  1. Откройте два процесса Excel. Если приложение Excel уже открыто и вы создали новую книгу с помощью сочетания клавиш Ctrl+N, вторая книга размещается в том же процессе Excel.

    • Чтобы запустить отдельный процесс Excel, щелкните правой кнопкой мыши символ Excel на панели задач и нажмите Microsoft Excel 20… Запуск отдельного процесса Excel
    • В Excel 2013 и более поздних версиях удерживайте клавишу ALT нажатой, чтобы запустить отдельный процесс. В появившемся окне Вы хотите запустить новый экземпляр Excel? выберите вариант Да.
  2. В рабочей книге, открытой в первом процессе Excel, перейдите в раздел ExcelГлавная СтилиФорматировать как таблицуСоздать стиль таблицы …. При этом будет вставлен стиль настраиваемой таблицы «Стиль таблицы 1».
  3. Скопируйте и вставьте ячейку из книги, содержащей «Стиль таблицы 1» в другую книгу во втором процессе Excel. В первый раз все будет нормально.
  4. Еще раз скопируйте и вставьте ячейку из книги, содержащей «Стиль таблицы 1» в другую книгу во втором процессе Excel. Во второй раз появится сообщение об ошибке Microsoft Excel не удается вставить данные.

Решение 2

  • Эта проблема не возникает в Excel 2007 и более ранних версиях.
  • В Excel 2010 проблема по-прежнему возникает (на ноябрь 2015 г.). Если у вашей компании есть контракт на поддержку Microsoft Office и вы хотите обратиться в Microsoft за исправлением, вы можете указать номер обращения Microsoft 113012810172527, 114021711190574 (Excel 2013).

    Пока же вы можете использовать следующие временные решения.

    • Вставьте данные в таблицу think-cell как текст:

      Таблица данных think-cellВставитьПодробнее (маленькая треугольная кнопка рядом с кнопкой «Вставить») → Специальная вставка …ТекстОК.
    • Нажмите кнопку Очистить форматирование всех ячеек во внутренней таблице think-cell. При этом из таблицы будут удалены все стили настраиваемой таблицы, а последующие команды вставки будут работать по крайней мере один раз.
  • Корпорация Microsoft исправила эту проблему в Excel 2013. Установите обновление KB2881014 из набора обновлений Microsoft Office от 10 июня 2014 Г. или установите исправление KB2881042.

Рассказать