Filtre TCD en fonction valeur des cellules

Bonjour à tous,

Je cherche à filtrer automatiquement la première colonne de mon TCD en fonction des valeurs qui se trouvent dans les cases A1 ; A2 ; A3.. Je complète ces cases à la main.

Dans la première colonne du TCD, il y a plus de 1000 valeurs donc le segment n'est pas approprié. De plus les valeurs à filtrer peuvent changer chaque semaine.

J'ai commencé à enregistrer une macro avec des valeurs précises mais je n'arrive pas à la modifier pour avoir les valeurs d'une case.

Avez-vous des idées ?

Merci d'avance,

Gilles

Sub filtreTCD()
'

    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "[Chantier].[N°].[N°]").VisibleItemsList = Array("[Chantier].[N°].&[]", "", "", "" _
        , "", "", "", "", "[Chantier].[N°].&[2016C02164]", "[Chantier].[N°].&[2018C00416]", _
        "[Chantier].[N°].&[2018C02063]", "[Chantier].[N°].&[2018C02071]", _
        "[Chantier].[N°].&[2018C02077]", "[Chantier].[N°].&[2018C02124]", _
        "[Chantier].[N°].&[2018C02128]")
End Sub

Bonjour et bienvenue,

Merci de joindre un petit fichier pour une aide adaptée du forum.

Cdlt.

Bonjour à tous

@Lige : connais-tu les segments où un fait tout d'un clic ou deux ?

Bonjour,

merci pour votre réponse.

Voici un exemple de mon fichier ou il faut s'imaginer que la liste des numéros (Colonne N°) est très longue

j'ai trouvé un bout de code qui fonctionne parfaitement avec le fichier test :

Private Sub Worksheet_Activate()
' Déclaration des variables
Dim rCell As Range
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim x As String
    ' Optimisation procédure (gel affichage)
   Application.ScreenUpdating = False
    '------------------------------------------------------
   ' Initialisation des variables
   Set pt = Me.PivotTables("Tableau croisé dynamique2")
    Set pf = pt.PivotFields("N°")
    '------------------------------------------------------

        ' Calcul manuel
       pt.ManualUpdate = True
        ' Eléments uniques conservés
       pt.PivotCache.MissingItemsLimit = xlMissingItemsDefault
        ' Acualisation TCD
       pt.RefreshTable

    '------------------------------------------------------
   ' Suppression filtres existants
   pf.ClearAllFilters
    '------------------------------------------------------
   ' Filtre sur dates de la plage s.range (E3:I3)
   For Each pi In pf.PivotItems
        x = CStr(pi.Name)
        Set rCell = [s.range].Find _
                    (what:=x, _
                     LookIn:=xlValues, _
                     lookat:=xlWhole)
        On Error Resume Next
        If rCell Is Nothing Then pi.Visible = False
        On Error GoTo 0
    Next pi
    '------------------------------------------------------
   Application.ScreenUpdating = True
    With pt
        ' Tri
       .PivotFields("N°").AutoSort _
                Order:=xlAscending, _
                field:="N°"
        ' Calcul
       .ManualUpdate = False
    End With
    '------------------------------------------------------
   ' RAZ variables
   Set rCell = Nothing
    Set pf = Nothing: Set pt = Nothing

End Sub

Par contre dans mon fichier original qui est un cube OLAP, je rencontre une erreur :

Qui vient de cette ligne :

 pt.PivotCache.MissingItemsLimit = xlMissingItemsDefault

Après quelques recherches, je pense que

 MissingitemsLimit 

n'est pas compatible avec les cubes OLAP

Avez-vous une idée pour contourner cette erreur ?

Merci d'avance,

Gilles

bonjour

ne jamais faire de VBA, surtout pas sur un TCD !

avec une colonne de pointage (que j'ai décomposée en 2 pour plus de lisibilité)

la liste des n° est sur la feuille du TCD, il faudra la mettre ailleurs ou en colonne A comme tu l'avais fait

pense à toujours "mettre sous forme de Tableau"

ceci facilite toutes les formules, et ça remplit tout seul les colonnes calculées en moins d'1/1000 secondes même si tu as 10 000 lignes

et en plus tu n'as pas à déclarer des plages de longueur infinie

amitiés à tous

Merci pour ta réponse,

Le soucis c'est que c'est un fichier test avec un tableau

Mais dans mon fichier original, c'est une extraction direct d'un cube OLAP donc impossible d'avoir accès à un tableau. J'ai directement le TCD avec 10 000 lignes ou je veux filtrer certaines valeurs (comme pour le fichier test).

C'est impossible de mettre sous forme de tableau. C'est pour ca que j'ai commencé à regarder un code VBA qui fonctionne mais pas pour les cubes OLAP..

Merci,

Bonjour,

La proposition VBA était en effet pour un TCD classique, non OLAP.

Pour ma part en VBA, je ne sais pas faire…

Tu travailles avec Excel 2016 et tu peux certainement travailler tes données en amont avec Récupérer et transformer(Power Query ) :

1 table "Données" et une 1 table "A Filter" et les fusionner pour créer le TCD.

Maintenant, sans fichier, il sera difficile d'intervenir.

Tu pourras aussi nous dire, quelle est la nécessité d'un TCD OLAP !?

Cdlt.

re

bonne idée de Jean-Eric : Power Query

mais avant d'en arriver là, essaye de créer un onglet miroir simple :

• dans un onglet, en A1 taper =
• aller dans l'extraction du cube, cliquer en A1
• valider
• étendre cette formule vers la droite et vers le bas

ton onglet est maintenant une image de ton original

la méthode est basique, mais simple et claire

tu peux la mettre sous forme de tableau

tu peux lui ajouter des colonnes de pointage

tu peux faire un TCD, un filtre etc.

pas de VBA

bon travail

Bonjour à tous

La syntaxe est différente avec le sources cube y compris pour les segments.

Il faudrait faire un essai sous PowerPivot car pas d''autre accès à un cube pour ma part.

Mais PoweQuery est à mon avis une meilleure piste...

Pour expliquer, toutes les données sont dans un ERP et via les cubes OLAP excel, je peux récupérer ces données via des TCD et faire des statistiques.

L'objectif est de savoir combien d'heure les personnes se pointent sur un N° de compte d'imputation afin de les croiser.

Sauf qu'il existe des milliers de N° de compte d'imputation et je voudrais pouvoir filtrer facilement en rentrant seulement quelques numéros dans des cases.

En tout cas merci pour vos réponses, je vais essayer la méthode de Jean-Eric cela me semble une très bonne idée.

re

tu dois bien avoir accès au fichier plat ???

par ex dans Excel, ou en csv/txt ou dans le modèle Power Pivot ?

je ne connais pas d'ERP capable de sortir des TCD "secs" dans Excel

re,

Je ne sais pas peut être que je m'y prends mal mais quand je veux charger mes données je suis obligé d'avoir un TCD. Je n'arrive pas à accéder aux données plat à part si je vais dans l'ERP et que je fais une extraction spécifique de ce que j'ai besoin. (beaucoup plus fastidieux)

Je clique sur Connexions existantes :

Ensuite je choisis le cube que j'ai besoin :

Puis après on me propose seulement la forme TCD :

Après je peux récupérer les données dont j'ai besoin en utilisant le TCD

re

on va faire un fichier plat à partir d'un TCD !

A/

• charger ton TCD par extraction;
• mettre tous les champs voulus, dans la case "Lignes" (laisser vides les cases colonne, filtre et valeurs )
• menu TCD, présentation sous forme tabulaire
• désactiver les totaux et sous-totaux

B/

• copier la feuille (ou juste la zone contenant le tableau)
• sur un autre onglet, faire collage spécial valeurs

maintenant on fait ce qu'on veut

B/ est à refaire à chaque actualisation, en 4 secondes chrono

essaye

dans 5 minutes ça marche

découverte à l'instant : on peut faire un coup de Power Query sur un TCD !

tu peux remplacer B/ par Données/obtenir d'Excel

Bonjour

A mon avis beaucoup plus simple : faire un TCD avec un seul champ en valeur et double cliquer sur le total général duplique la source sous forme de tableau structuré... mais non dynamique. Edit : pas vu le coup de la Source TCD, donc à creuser.

C'est quel ERP ?

PowerQuery peut se connecter aux cubes issus de SQL Server Analysis Services Database