Блог

Главная - Вопросы по программированию - Странное поведение в Excel со сводной таблицей

Странное поведение в Excel со сводной таблицей

#excel #vba #pivot-table

#excel #vba #сводная таблица

Вопрос:

Сначала я надеюсь, что вы сможете понять мой английский 🙂

И я заранее благодарю вас за то, что прочитали мою проблему.

У меня проблемы с одной из моих книг (Excel 2013).

Я не могу дать вам источник, но я попытаюсь объяснить.

Сначала у меня был TCD, построенный на ListObject, набор записей заполняет Listobject, который был источником моей сводной таблицы. Но иногда было слишком много строк для обработки listobject…

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

Иногда Excel не позволяет мне добавлять определенное сводное поле в мою сводную таблицу, назовем его «SEQ», в нем говорится, что слишком много уникальных элементов.

Но если я удваиваю клик по общему итогу моей сводной таблицы, он детализирует мне 260 тыс. строк, которые создали сводный кэш на новом листе (это тот же результат моего sql-запроса). И если я создам новую сводную таблицу в этом диапазоне, тогда все в порядке, и я могу добавить поле SEQ.

Это странно ! Я обнаружил это ограничение в сводной таблице: максимальное количество уникальных элементов в сводной таблице — 1 048 576. Но у меня не так много строк, и только 67 тыс.

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

Надеюсь, я был достаточно ясен.

Редактировать: вот некоторый код, как указано

Объявлено в доступном модуле :

Public cnn As New ADODB.Connection

Public objcmd As New ADODB.Command

В функции, которая извлекает данные и создает PT :

 Req = "SELECT * FROM TABLE"
objcmd.CommandText = Req
Set RS = objcmd.Execute
Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set objPivotCache.Recordset = RS

With objPivotCache
    .CreatePivotTable TableDestination:=Range("A8"), TableName:="TCDDirect1"
End With

 With Sheets("Données").PivotTables("TCDdirect1").PivotFields("ETATR")
     .Orientation = xlRowField
     .Position = 1
 End With
 With Sheets("Données").PivotTables("TCDdirect1").PivotFields("FAMR")
     .Orientation = xlRowField
     .Position = 2
 End With
 With Sheets("Données").PivotTables("TCDdirect1").PivotFields("SEQ")  <---- Program stop here in some cases
     .Orientation = xlRowField
     .Position = 3
 End With
 With Sheets("Données").PivotTables("TCDdirect1").PivotFields("LIBELLé")
     .Orientation = xlRowField
     .Position = 4
 End With

Комментарии:

1. I switched to copying my Recordset in a pivotcache Как вы создаете набор записей и копируете его в сводный кэш? Можем ли мы увидеть какой-нибудь код для начала?

2. Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal) Set objPivotCache.Recordset = RS With objPivotCache .CreatePivotTable TableDestination:=Range("A8"), TableName:="TCDdonnées" End With

3. Я не могу заставить код работать, извиняюсь за плохую презентацию.

4. Набор записей заполняется подключением adodb

5. Все в порядке, если я скопирую свой набор записей на лист, а затем построю свою сводную таблицу в этом диапазоне. Но если я не копирую свой набор записей на лист и не использую его для заполнения сводного кэша, то это нормально для большинства полей, но не для некоторых (например, SEQ). Для тех, кто говорит, что в Excel слишком много уникальных элементов… но это не так ! Если я посчитаю свой сводный кэш, у меня будет 260 тыс. строк, как у меня может быть слишком много уникальных элементов?

Ответ №1:

Используя Set objPivotCache = ActiveWorkbook .Сводные кеши.Создайте (xlExternal) вместо набора objPivotCache = ActiveWorkbook .Сводные кеши.Add(SourceType:=xlExternal) решает проблему… Я точно не знаю, почему… надеюсь, это кому-то поможет!