Discussion :

[favst]

Bonjour à tous,

Je bloque un peu sur un problème d'extraction de données que je voudrai automatiser. Je vous explique :

Dans mon classeur j'ai une première feuille avec une très grosse base de donnée comptable, mise à jour tous les mois. Dans une seconde feuille, j'ai un TCD lié à ma base de donnée.

Mon TCD me présente les informations sous la forme suivantes :

Clients	Services	Mois	Revenues	Couts
Client 1	ServiceA	201701	10000	7000
Client 1	ServiceA	201702	10500	10000
Client 1	ServiceA	201703	11500	11000
Client 1	Service B	201701	5000	4000
Client 1	Service B	201702	5000	4000
Client 1	Service B	201703	5000	4000
Client 2	Service B	201701	50000	40000
Client 2	Service B	201702	51000	40000
Client 2	Service B	201703	50000	40000
Client 2	Service C	201701	500	700
Client 2	Service C	201702	400	700
Client 2	Service C	201703	700	700

Je voudrai dans une autre feuille, permettre à l'utilisateur de choisir un mois, par exemple "201701", et lui présenter en fonction du mois choisi les 10 plus gros Revenue du mois. En affichant les informations relatives à ce Revenue, c'est à dire la ligne du TCD correspondant. Nous aurions donc sur cette feuille un tableau de 11 lignes (avec les entêtes) et 5 colonnes.



' Pour le moment j'ai bidouillé un truc qui ne marche pas très bien :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
Sub CopyTCD()
 
  Dim Lig     As Long
  Dim Col     As String
  Dim NbrLig  As Long
  Dim NumLig  As Long
 
'Je prend en compte qu'il existe déjà un tableau "Tableau1" dans ma feuille
 
   Range("Tableau1[[#Headers],[Customer]]").Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlToRight)).Select
   Application.CutCopyMode = False
   Selection.ClearContents
 
' J'efface mon tableau
 
  Col = "D"                 ' colonne de la donnée non vide à tester
  NumLig = 3
  Range(Cells(4, 2), Cells(500, 7)).ClearContents      'J'efface toutes les données 
  With Sheets("GM ")   
  NbrLig = .Cells(65536, Col).End(xlUp).Row
  For Lig = 22 To NbrLig
    If .Cells(Lig, Col).Value = Cells(2, 2).Value Then        'Une liste déroulante dans B2 me permet de choisir le mois
      .Cells(Lig, Col).EntireRow.Copy
      NumLig = NumLig + 1
      Cells(NumLig, 1).Select
      ActiveSheet.Paste         'Je copie mon TCD en fonction du mois choisi
 
    End If
  Next
  End With
 
   'Creation d'un Tableau
 
   Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$3:$G$99"), , xlYes).Name = _        'Bug si il y a plus de 99 lignes dans mon TCD mais si je change 99 en autre chose cela ne fonctionne plus
        "Tableau1"
 
    Range("Tableau1[[#Headers],[Colonne1]]").Select
    ActiveCell.FormulaR1C1 = "Customer"                                'Je renome mais colonnes
    Range("Tableau1[[#Headers],[Colonne2]]").Select
    ActiveCell.FormulaR1C1 = "BU"
    Range("Tableau1[[#Headers],[Colonne3]]").Select
    ActiveCell.FormulaR1C1 = "Month"
    Range("Tableau1[[#Headers],[Colonne4]]").Select
    ActiveCell.FormulaR1C1 = "Income"
    Range("Tableau1[[#Headers],[Colonne5]]").Select
    ActiveCell.FormulaR1C1 = "COST"
    Range("Tableau1[[#Headers],[Colonne6]]").Select
    ActiveCell.FormulaR1C1 = "GM"
 
     'Tri du Tableau en fonction du revenu (décroissant)
 
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Top 10 Monthly Analyse").ListObjects("Tableau1"). _
        Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Top 10 Monthly Analyse").ListObjects("Tableau1"). _
        Sort.SortFields.Add Key:=Range("Tableau1[[#All],[Income]]"), SortOn:= _
        xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Top 10 Monthly Analyse").ListObjects("Tableau1" _
        ).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
    'Filtre sur les TOP 10 de clients
 
    ActiveSheet.ListObjects("Tableau1").Range.AutoFilter Field:=4, Criteria1:= _
        "10", Operator:=xlTop10Items
 
End Sub

Un grand merci par avance aux personnes qui pourront m'aider !
Bonne journée à tous

[riaolle]

Bonjour,
Avant même de lire ton code, sais-tu que le TCD permet de faire un tri sur les 10 plus gros revenus ?

Néanmoins, un commentaire sur ton code :
Evite au maximum d'utiliser Select/Selection/Activate/ActiveSheet ...
Pour supprimer un tableau :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 Range("Tableau1[[#Headers],[Customer]]").Select
 Range(Selection, Selection.End(xlDown)).Select
 Range(Selection, Selection.End(xlToRight)).Select
 Application.CutCopyMode = False
 Selection.ClearContents

Mais

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("Tableau1[#All]").ClearContents

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Range("Tableau1[[#Headers],[Colonne1]]").Select
 ActiveCell.FormulaR1C1 = "Customer"

Mais

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("Tableau1[[#Headers],[Colonne1]]").FormulaR1C1 = "Customer"

[favst]

Hello,

Merci pour cette réponse rapide, je vais faire les modifications du code


Je connais le filtre du TCD mais je voudrais faire en sorte que l'utilisateur n'ait pas à manier le TCD. La seule manip à faire serait de choisir un mois.

[riaolle]

Oui, mais le filtre s'actualise automatiquement, i.e. :
Tu créés une filtre pour les mois.
Tu définis le filtre sur les valeurs une fois.
Quand tu changes la valeur du mois grâce au filtre, le filtre sur les 10 premières valeurs est adapté.

[favst]

Ok, mais du coup il y a surement quelque chose que je ne comprend pas. Car quand je fais mon filtre TOP 10 mon TCD m'affiche bien les TOP 10 mais le top 10 comprenant tous les mois. C'est a dire par exemple :

Mon TOP 10 :

201701 1 000 000
201701 999 000
201702 900 000
201702 800 000
201702 700 000
201703 600 500
201702 500 000
201703 450 000
201702 400 000
201701 300 000

Et quand je fais un filtre sur le mois, prenons le 201701 alors mon top 10 devient dans le cas ci-dessus un Top 3 :

201701 1 000 000
201701 999 000
201701 300 000

Alors que je souhaite avoir le top 10 du mois de janvier...

[riaolle]

Est-ce qu'il y a bien au moins 10 revenus différents pour le mois de janvier (avant de mettre le filtre TOP 10, filtre sur le mois de janvier et regarde combien il y a de lignes)?
Que se passe-t-il pour d'autres mois?
Peux-tu envoyer une capture d'écran de ce que tu fais, stp?

[favst]

Voici ci-dessous les captures d'écran. Le résultat revient au même peu importe l'ordre d'application des filtres

Une partie du tableau sans filtre :


Tableau avec filtre sur le mois de janvier :


Tableau avec filtre sur TOP 10 et tous les mois :


Tableau avec filtre sur TOP10 et filtre sur le mois de janvier:

[riaolle]

C'est un filtre comme cela qu'il faut :

Tu peux éventuellement avoir une colonne avec le mois pour avoir un aperçu, mais il ne faut pas filtrer sur cette colonne.

[favst]

Comment obtenir un filtre comme cela dans mon cas ?
Actuellement j'ai un filtre comme ci-dessous :


Qui vient de mon TCD :

[78chris]

Bonjour

Si tu gardes mois en étiquette de ligne, pour pouvoir filtrer les 10 premiers en + du mois, il faut régler l'option de TCD : "Autoriser plusieurs filtres par champ" dans l'onglet Totaux et filtres des options.

[riaolle]

Comment obtenir un filtre comme cela dans mon cas ?

Tu ajoutes ton filtre comme cela :

(tu peux garder MONTH comme etiquettes de lignes et avoir un filtre comme je te le propose. Mais, alors, n'impose pas de filtre sur MONTH qui est en etiquettes de lignes).

[favst]

Merci pour votre aide, mais décidément je rame !
J'ai coché l'option "autoriser plusieurs filtres par champ". Maintenant je souhaite garder le champ "Month" en étiquette et l'ajouter en filtre mais lorsque je choisi le champ "Month" et que je le positionne sur "Filtres" alors qu'il est déjà dans la partie "Lignes" le champ "Month" disparaît de la catégorie "Ligne" et vice versa.

[riaolle]

Autant pour moi, en effet on ne peut pas avoir le même champ à la fois en etiquette de ligne et en filtre
Soit tu n'en as pas beosin en étiquettes de ligne et tu le mets seulement en filtre, soit tu le mets seulement en etiquette de ligne et tu fais comme 78chris te dit.

[favst]

Merci pour vos conseils cependant il semble qu'avec un TCD je ne puisse pas obtenir le résultat souhaité. C'est à dire :
- Filtrer sur le mois
- Filtrer sur le top 10 des incomes (et avoir le TOP 10 du mois sélectionné)

Tout en simplifiant le processus au maximum pour l'utilisateur.

Je vais donc essayer de repartir sur l'extraction de donnée et la création d'un simple tableau sur une autre feuille à l'aide de macros.

Voici mon code pour le moment.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Sub CopyTCD()
 
  Dim Lig     As Long
  Dim Col     As String
  Dim NbrLig  As Long
  Dim NumLig  As Long
 
  Range("Tableau1[#All]").ClearContents
 
  Col = "D"                 ' colonne de la donnée non vide à tester
  NumLig = 3
  Range(Cells(4, 2), Cells(500, 7)).ClearContents
  With Sheets("GM BU since JAN-2017")     ' feuille source
  NbrLig = .Cells(65536, Col).End(xlUp).Row
  For Lig = 22 To NbrLig
    If .Cells(Lig, Col).Value = Cells(2, 2).Value Then
      .Cells(Lig, Col).EntireRow.Copy
      NumLig = NumLig + 1
      Cells(NumLig, 1).Select
      ActiveSheet.Paste
 
    End If
  Next
  End With
 
   'Creation d'un Tableau
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$3:$G$99"), , xlYes).Name = _
        "Tableau1"
 
    Range("Tableau1[[#Headers],[Colonne1]]").FormulaR1C1 = "Customer"
    Range("Tableau1[[#Headers],[Colonne2]]").FormulaR1C1 = "BU"
    Range("Tableau1[[#Headers],[Colonne3]]").FormulaR1C1 = "Month"
    Range("Tableau1[[#Headers],[Colonne4]]").FormulaR1C1 = "Income"
    Range("Tableau1[[#Headers],[Colonne5]]").FormulaR1C1 = "COST"
    Range("Tableau1[[#Headers],[Colonne6]]").FormulaR1C1 = "GM"
 
     'Tri_Tab Macro
 
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Top 10 Monthly Analyse").ListObjects("Tableau1"). _
        Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Top 10 Monthly Analyse").ListObjects("Tableau1"). _
        Sort.SortFields.Add Key:=Range("Tableau1[[#All],[Income]]"), SortOn:= _
        xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Top 10 Monthly Analyse").ListObjects("Tableau1" _
        ).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
    'Top Customer
 
    ActiveSheet.ListObjects("Tableau1").Range.AutoFilter Field:=4, Criteria1:= _
        "10", Operator:=xlTop10Items
 
End Sub

J'ai plusieurs problèmes,
Le premier, je n'ai pas trouvé d'astuce "intelligente" pour la première utilisation de la macro. (Le cas ou la feuille est vide, il n'y a que le bouton qui déclenche la macro).J'ai donc inséré manuellement un tableau "tableau1" que j'efface par la suite au démarrage de ma macro. De manière à effacer le tableau à chaque utilisation de la macro.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

  Range("Tableau1[#All]").ClearContents

Le second, la fonction utilisée pour créer un tableau à partir des données sélectionnées ne prend pas en compte que le nombre de ligne du tableau peut être variable (je pense )

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$3:$G$99"), , xlYes).Name = _
        "Tableau1"

Dernièrement, je ne comprend pas pourquoi mon filtre TOP 10 ne fonctionne pas correctement et oubli systématiquement mon Client TOP1 (plus gros income) et mon Client TOP2 (second plus gros montant).

Merci pour votre aide et votre patience !

[riaolle]

Ici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Range(Selection, Selection.End(xlDown)).Select
 Range(Selection, Selection.End(xlToRight)).Select
 ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$3:$G$99"), , xlYes).Name = _
 "Tableau1"

Tu dis que tu veux faire un tableau à partir de la plage de cellules B3:G99. Il faut que tu l'adaptes avec le nombre de lignes du tableau. Pour obtenir la dernière ligne non vide de la colonne A :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Dim derLig As Long
derLig = Sheets("ma feuille").Cells(Rows.Count, 1).End(xlUp).Row

Puis, pour la création du tableau :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Range(Selection, Selection.End(xlDown)).Select
 Range(Selection, Selection.End(xlToRight)).Select
 ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$3:$G$" & derLig), , xlYes).Name = _
 "Tableau1"

[Gorzyne]

Salut

tu as une solution efficace consistant à convertir ton TCD en tableau VBA puis à aller chercher ce qui t'intéresse dedans avec des coordonnées i, j
Si tu va aller sur cette solution je peux te donner les élements de syntaxes nécessaires,

Gorz

[78chris]

Bonjour

Merci pour vos conseils cependant il semble qu'avec un TCD je ne puisse pas obtenir le résultat souhaité. C'est à dire :
- Filtrer sur le mois
- Filtrer sur le top 10 des incomes (et avoir le TOP 10 du mois sélectionné)

Tout en simplifiant le processus au maximum pour l'utilisateur.

Si on peut !

Il suffit de cocher l'option comme déjà dit et d'avoir prédéfini le filtre 10 premiers sur le champ en valeurs, ceci pour le premier champ du TCD donc Client : quel que soit le mois choisi le top s'adapte et ne présente que les 10 lignes des plus gros .

Tu peux, si Excel 2010 ou plus récent, offrir un filtre par segment, ce qui est très convivial...

[Gorzyne]

t'embête pas avec les filtres. Tu convertis ton TCD en tableau VBA standard et tu seras en terrain familier

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
'indiquer ici la feuille qui contient le TCD
Set mytcd = ThisWorkbook.Sheets("MYTCD")
'mettre ici le nom du tableau croisé dynamique
Set pvt = mytcd.PivotTables("Tableau croisé dynamique1")
 
'raffraichissement du tableau
'attention petite subtilité l'instruction suivante ne fonctionne pas si jamais votre feuille est protégée même si l'option UserInterfaceOnly = True
pvt.PivotCache.Refresh
 
 
'on crée le tableau VBA correspondant au TCD
Set mytab = mytcd.Range(pvt.TableRange1.Address)

de rien
Gorz

[favst]

Un grand merci, je pense que la solution consistant à convertir mon TCD en tableau VBA pourrait répondre à mes besoins. Peux tu m'expliquer plus en détail comment je dois faire pour créer ce tableau dans une nouvelle feuille ?

Merci !

[78chris]

Bonjour

t'embête pas avec les filtres. Tu convertis ton TCD en tableau VBA standard et tu seras en terrain familier...

Sauf que le filtre est permanent et dynamique tandis qu'il faut faire tourner le VBA à chaque changement de mois.

J'appelle cela réinventer l'eau tiède... mais chacun fait comme il veut...