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

[grhum29]

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...e-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]

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 :

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+

 

[BOISGONTIER]

Bonsoir,

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

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

 

[grhum29]

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]

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]

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

 

[chris]

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]

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.

 

[chris]

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...