XL 2010 Problèmes filtre automatique multiple sur plus de 10 000 lignes

grhum29

XLDnaute Junior
Bonjour,

Je travaille actuellement sur un fichier de plus de 300 000 lignes et je rencontre un problème sur les filtres automatiques dans un tableau croisé dynamique.

Pour affiner mes données, il faut que je filtre mes données uniquement sur quelques valeurs mais du fait du trop grand nombre de lignes, j'ai un message qui affiche que "Ce champ contient plus de 10 000 élements. Seuls les 10 000 premiers éléments sont affichés.".

J'ai trouvé une solution de contournement sur le site de Microsoft (https://support.microsoft.com/fr-fr/help/295971/not-all-items-are-displayed-in-the-autofilter-pivottable-list) mais celle-ci ne répond pas à ma problématique car dans mon cas, j'ai plusieurs données à filtrer et aucune n’apparaît dans les 10 000 lignes du filtre...

Avez-vous une solution pour contourner cela?

Merci de vos éclairages.

Bonne journée.
 

job75

XLDnaute Barbatruc
Bonjour grhum27,

Chez moi sur Excel 2013 le filtre automatique affiche au maximum 10 000 valeurs.

Dans une ListBox il n'y a pas de limite, voyez le fichier joint avec 100 000 valeurs uniques à filtrer.

Le code de l'UserForm :
Code:
Private Sub Label3_Click()
Dim d As Object, i&, P As Range, t, affiche As Range
Set d = CreateObject("Scripting.Dictionary")
With ListBox1
    For i = 0 To .ListCount - 1
        If .Selected(i) Then d(.List(i, 0)) = ""
    Next
End With
Set P = [A2:A300001]
t = P 'matrice, plus rapide
For i = 1 To UBound(t)
    If d.exists(t(i, 1)) Then Set affiche = Union(IIf(affiche Is Nothing, P(i), affiche), P(i))
Next
Application.ScreenUpdating = False
P.Rows.Hidden = True 'masque toute les lignes
If Not affiche Is Nothing Then affiche.Rows.Hidden = False 'affiche les valeurs sélectionnées
Application.ScreenUpdating = True
End Sub

Private Sub UserForm_Initialize()
Dim t, d As Object, i&, a, b
t = [A2:A300001] 'matrice, plus rapide
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(t)
    d(t(i, 1)) = d(t(i, 1)) + 1 'comptage
Next
a = d.keys: b = d.items
ReDim t(UBound(a), 1)
For i = 0 To UBound(t)
    t(i, 0) = a(i)
    t(i, 1) = b(i)
Next
ListBox1.List = t
End Sub
A+
 

Fichiers joints

BOISGONTIER

XLDnaute Barbatruc
Bonsoir,

Code:
Private Sub ComboBox1_GotFocus()
  Set d = CreateObject("Scripting.Dictionary")
  For Each c In Range([A4], [A65000].End(xlUp))
    If Not d.Exists(c.Value) Then d(c.Value) = ""
  Next c
  temp = d.keys
  tri temp, LBound(temp), UBound(temp)
Me.ComboBox1.List = temp
End Sub

Private Sub ComboBox1_Change()
   [A3].AutoFilter field:=1, Criteria1:=ComboBox1
End Sub
Version comboBox Intuitif

Code:
Dim a()
Private Sub ComboBox1_GotFocus()
  Set d = CreateObject("Scripting.Dictionary")
  For Each c In Range([A4], [A65000].End(xlUp))
    If Not d.Exists(c.Value) Then d(c.Value) = ""
  Next c
  a = d.keys
  Tri a, LBound(a), UBound(a)
  Me.ComboBox1.List = a
End Sub

Private Sub ComboBox1_Change()
  If Me.ComboBox1 <> "" Then
    Set d1 = CreateObject("Scripting.Dictionary")
    tmp = UCase(Me.ComboBox1) & "*"
    For Each c In a
      If UCase(c) Like tmp Then d1(c) = ""
    Next c
    Me.ComboBox1.List = d1.keys
    Me.ComboBox1.DropDown
  End If
End Sub

Private Sub ComboBox1_click()
   [A3].AutoFilter field:=1, Criteria1:=ComboBox1
End Sub



Boisgontier
http://boisgontierjacques.free.fr
 

Fichiers joints

Dernière édition:

grhum29

XLDnaute Junior
Bonsoir Job75 et BOISGONTIER,

Tout d'abord merci pour les réponses.

Pouvez-vous me dire si vos solutions peuvent s'appliquer directement sur le tableau croisé dynamique (ce qui m'arrangerait fortement... :) )

J'ai l'impression que c'est plutôt sur la feuille base contenant les données que vos macros interviennent.

Merci de vos retours

Bonne soirée
 

chris

XLDnaute Barbatruc
Bonjour

300 000 items différents dans un TCD laisse penser que le TCD n'en est pas réellement un.

Peux-tu expliquer quelles colonnes tu as en source et ce que tu en fait en TCD.
 

grhum29

XLDnaute Junior
Bonjour Chris,

Pour illustrer mes propos, je mets en pièce jointe un exemple de ce que je souhaite faire.

Sur la feuille « BASE_APPELS » se trouve toutes les données concernant les appels émis, reçus… (Dans le fichier original, il y a plus de 300 000 lignes.

Sur la feuille « TCD_TEST » se trouve le fameux TCD. Sur ce TCD, je souhaite filtrer les données sur le champ « No Appelé » pour avoir uniquement les données liées à ces numéros.

Le problème est que j’ai plus de 10 000 « No Appelé » différents et que les numéros sur lesquels je souhaite filtrer ne sont pas dans les 10 000 premiers…

J’espère avoir été clair dans mes explications.

Je ne vois pas trop comment faire sinon par passer par des macros ce qui ne m’arrange pas vraiment…

Merci de votre aide.

Bonne soirée
 

Fichiers joints

chris

XLDnaute Barbatruc
Re

Une possibilité à essayer :
ajouter une colonne à la source avec une plage de numéros (sur les 5 premiers chiffres par exemple)
Placer ce champ en zone de filtre et l'utiliser en pré filtrage des numéros
 

job75

XLDnaute Barbatruc
Bonsoir JB, chris,
Pouvez-vous me dire si vos solutions peuvent s'appliquer directement sur le tableau croisé dynamique (ce qui m'arrangerait fortement... :) )
Il faut bien sûr adapter les codes VBA pour traiter les données du TCD.

L'une des propriétés des TCD c'est de fournir des listes sans doublon, cela simplifie le travail.

Voyez ce fichier (2) avec un TCD de 100 000 lignes : le filtrage via la ListBox ne pose aucun problème.

Bonne nuit.
 

Fichiers joints

chris

XLDnaute Barbatruc
Bonsoir

J'ai testé avec un segment : sur l'exemple de job75, le segment affiche bien de 1 à 100 000 et permet la multi sélection.
C'est un peu moins cool que les cases à cocher du Listbox mais peut éviter le VBA et si on affiche dans le segments des rangées de 10 numéros par exemple, c'est utilisable sans souci...
 

Créez un compte ou connectez vous pour répondre

Vous devez être membre afin de pouvoir répondre ici

Créer un compte

Créez un compte Excel Downloads. C'est simple!

Connexion

Vous avez déjà un compte? Connectez vous ici.

Haut Bas