[VBA-E]Ligne calculée en fin de tableau dynamique

Version imprimable

25/09/2006, 00h13
AliochaBada

[VBA-E]Ligne calculée en fin de tableau dynamique

Salut le forum Developez ;)

Un total calculé sur un résultat de champ de tableau dynamique me pose probleme. J'ai lu moult articles sur les champs et les éléments calculés des TCD, mais je n'y trouve pas la solution.

Mon TCD contient :
des lignes de produits,
des colonnes de N° Semaines,
des datas avec :
la quantité de produits commandés par semaine
le coût en Euros du produit commandés par semaine
le total colonne semaine de quantité et coût euros de produits

J'aimerais qu'en fin de TCD sous le total colonne, vienne se rajouter un Total mensuel extrapolé qui ferait :
(total colonne coût semaine*52)/12

La fonction des TCD, insertion champ calculé me pose probleme : je n'arrive pas à la restreindre à la zone total ; je ne peux pas la mettre ailleurs que dans les datas, et du coup il me renvoie le mensuel extrapolé de chaque ligne de produits, ce qui rend le TCD illisible...

Et si je rajoute à la main les formules correspondantes, elles sont écrasées des que mon TCD est actualisé...

Y a t'il un moyen de rajouter en fin de TCD des champs calculés à partir des totaux de TCD?

Là je vais essayer qque chose en VBA, mais avec mon peu d'experience, je vais pondre un truc du genre : à chaque ouverture ou actualisation du TCD, recherche la ligne total et dessous rajoute les lignes contenant les formules blabla....
Un peu lourd quoi....

Quelqu'un aurait il une solution plus élégante à mon probleme?

D'avance merci :P
25/09/2006, 09h37
ouskel'n'or
A défaut d'une réponse plus orthodoxe... Avec VBA97, je n'ai pas trouvé d'autre solution que de faire une copie du TCD puis un "Collage spécial -> Valeurs seules", afin de pouvoir effectuer des calculs sur une nouvelle ligne du TCD.
Code:

1 2 3 cells(1,1).CurrentRegion.Copy 'copie du tcd Range("O1").PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:= false, Transpose:=False 'collage "ailleurs"...
... pour copier les valeurs sur un autre emplacement

Citation:

DernièreLigne = cells(1,1).currentcegion.end(xlDown).Row
'et
DernièreColonne = cells(1,1).currentregion.end(xlToRight).Column
'... te donnent des indications pour positionner correctement ta formule.

A défaut... etc

Merci ouskel'n'or ;)

Bon ok c'est une solution bidouille aussi, mais j'avais pas pensé au copier coller sur une autre feuille :
avantage : en collant avec liaison de conserver le dynamisme du tableau initial
desavantge : aucune mise en forme possible puisque la structure change à chaque update du tableau

Autre solution bidouille : rajouter du code pour ajout des lignes voulues en fin de tableau
avantage : transparent pour l'utilisateur, updaté en meme temps que le tableau
desavantage : faut s'y connaitre un poil en VBA table Pivot pour utiliser les bons objets et faire ce qu'on veut
de diou qu'est ce que c'est lourd pour faire un truc si simple

Exemple de bidouille pour qui veut :
Code:

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 Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable) 'Recherche de la ligne 'Total' càd der lig du tcd Dim ligFinTCD As Integer ActiveSheet.Range("A:A").Find(What:="Total", After:=ActiveSheet.Range("A7"), LookIn:=xlValues, LookAt:=xlWhole).Activate ligFinTCD = ActiveCell.Row 'Effacement des données précentes ActiveSheet.Range(Cells(ligFinTCD + 1, 1), Cells(ligFinTCD + 5, colSEMAINES.Count + 4)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Clear 'Insertion des lignes 'Mensuel extrapolé' ActiveSheet.Rows(ligFinTCD).Select Selection.Copy ActiveSheet.Rows(ligFinTCD + 1).Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Range("A7").Activate ActiveSheet.Range("A" & ligFinTCD + 1).Value = "Mensuel extrapolé" 'Boucle sur NB de colonne Semaines pour implementation du mensuel extrapolé Dim colSem As Long For colSem = 5 To colSEMAINES.Count + 4 Step 2 If ActiveSheet.Cells(ligFinTCD, colSem).Value <> "" Then ActiveSheet.Cells(ligFinTCD + 1, colSem).Value = (ActiveSheet.Cells(ligFinTCD, colSem).Value * 52) / 12 End If Next colSem End Sub
Si quelqu'un a une solution plus nette que nos deux bidouilles, pourquoi pas à partir de la fonction existante des PivotTables d'Excel, je suis preneuse,

merci

et merci encore Ouskel'n'or de m'avoir répondu :P

26/09/2006, 09h36
ouskel'n'or

Citation:

Envoyé par AliochaBada

Autre solution bidouille : rajouter du code pour ajout des lignes voulues en fin de tableau
avantage : transparent pour l'utilisateur, updaté en meme temps que le tableau

J'ai peur que ta solution soit plus lourde que ma bidouille à moi ;)
Si tu colles les valeurs sur places et que tu utilises "Application.screenupdating = false" avant d'exécuter tes manipes, que tu le remets à true après, l'utilisateur ne verra rien mais cela évitera la recherche d'un mot (Total) et de tout ce qui l'accompagne.
Ceci dit, bidouille pour bidouille... Mais je préfère ma bidouille à moi :mouarf:
27/09/2006, 22h24
AliochaBada

Attends, tu veux dire qu'avec le copier coller sur place, je garde la structure du tableau dynamique avec la mise à jour des données, et le groupage et degroupage des lignes pour afficher ou masquer le detail?

Ta solution permet ca?

Si oui, alors clairement je vote pour ta solution et j'erase mon code ;)
28/09/2006, 09h17
ouskel'n'or

Non, je ne veux pas dire ça. Ce que je veux dire, si ton TCD est créé par une macro, tu peux également automatiser la suite - Copie sur place et insertion de la formule qui va bien à l'endroit qui va bien ;)
Sinon, ce serait trop beau... Malheureusement les miracles, ça n'existe que pour les miraculés :mouarf:
28/09/2006, 09h41
SilkyRoad

bonjour

un exemple de tableau serait le bien venu afin de mieux visualiser ce que souhaites obtenir.

bonne journée
michel
30/09/2006, 01h17
AliochaBada

Bonjour Ouskel, bonjour SilkyRoad,

OK, mon tableau n'est pas géré par macro mais par lien sur le fichier source.

Même si le code est lourd, il permet de garder les fonctions de mises à jour et de dynamisme des champs. Je le garde pour l'instant, en attendant d'arriver à generer les TCD par macro.
Promis je m'y mets et je teste la methode copie sur place.

SilkyRoda, j'ai mis une version allegée de mon tableau et de son fichier source. Dans le vrai, ma source est dans un fichier separé de 15000 lignes.

Le code VBA permet de :
conserver la mise en forme (largeur des colonnes, bordures, alignements)
d'inscrire les 2 lignes de formules en dessous du total permettant de generer les chiffres à partir de la ligne total.

Ca fonctionne, c'est pas trop lent, mais qu'est ce que c'est compliqué pour faire un truc aussi simple ...

Une solution plus élégante?

EDIT : le fichier pour illustrer le probleme se trouve maintenant plus bas et integre la solution de SilkyRoad

bonjour

Voici une autre mèthode que tu pourrais adapter afin d'extraire les données du TCD et ajouter des lignes personnalisées sous chaque Item (les numéros de semaine dans l'exemple)

Code:

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
Dim pvTable As PivotTable
Dim PvIt As PivotItem
Dim x As Double
Dim Ligne As Integer, Colonne As Integer
 
'Définit le TCD à explorer dans la Feuil3
Set pvTable = Sheets("Feuil3").PivotTables("TCD CRL")
On Error Resume Next
 
'Boucle sur les items (les semaines dans cet exemple)
For Each PvIt In pvTable.PivotFields("Sem").PivotItems
    'numero de semaine
    x = PvIt.Caption
 
    'Récupère la derniere ligne du TCD
    Ligne = Sheets("Feuil3").Range(PvIt.DataRange.Address). _
            End(xlDown).Row
    'Récupére la 1ere colonne du TCD pour la semaine cible
    Colonne = Sheets("Feuil3").Range(PvIt.DataRange.Address).Column
 
    'Insère les données sous le TCD
    Sheets("Feuil3").Cells(Ligne + 6, 1) = "Hebdomadaire"
    Sheets("Feuil3").Cells(Ligne + 6, Colonne + 1) = _
                    pvTable.GetData("'Cost' 'SEM' '" & x & "'")
    Sheets("Feuil3").Cells(Ligne + 6, Colonne) = _
                    pvTable.GetData("'NB' 'SEM' '" & x & "'")
 
Next

bonne journée
michel

Bonjour et merci beaucoup SilkyRoad,

Grace à ton code
- j'ai maintenant un code propre qui fonctionne bien, vite, nickel quoi ; je peux maintenant tranquillement le mettre dans les mains d'un utilisateur
- j'ai pu contourner un probleme qui s'annoncait epineux sur mon champ calculé qui calculait la moyenne des 3 derniers totaux semaines (il prend maintenant bien en compte les semaines non affichées car total à 0)
- j'ai appris plein de trucs sur comment manipuler en VBA un Tableau croisé dynamique

Je l'ai adapté en me servant pour la ligne total par semaine du total géré par le TCD lui même.
J'ai rajouté mes lignes total mensuel extrapolé et total mensuel lissé (moyenne des 3 dernieres semaines)
Enfin tout va bien, je vais pouvoir continuer avec plus dans le ciboulot sur les tableaux croisés dynamiques en VBA :P

Merci tout plein

Le tout beau code avec le tout beau fichier :

Code:

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
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
 
Dim pvTable As PivotTable
Dim PvIt As PivotItem
Dim x As Double
Dim Ligne As Integer, Colonne As Integer, ColonneEnd As Integer, LigneEnd As Integer
 
'Définit le TCD à explorer dans la Feuil3
Set pvTable = Sheets("Feuil3").PivotTables("TCD CRL")
On Error Resume Next
 
'Adressage actualisé du TCD
    'Récupère la derniere ligne du TCD
        Sheets("Feuil3").Range("E9").Select
        Range(Selection, Selection.End(xlDown)).Select
        LigneEnd = Selection.Rows.Count + 8
 
    'Recupere la derniere colonne du TCD
        Sheets("Feuil3").Range("A9").Select
        Range(Selection, Selection.End(xlToRight)).Select
        ColonneEnd = Selection.Columns.Count
 
    'Effacer les données implementées precedemment
        Sheets("Feuil3").Range(Cells(LigneEnd + 1, 1), Cells(LigneEnd + 1, ColonneEnd)).Select
        Range(Selection, Seletion.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Clear
 
    'Mise en forme de la ligne Mensuel Extrapolé
        Sheets("Feuil3").Rows(LigneEnd).Select
        Selection.Copy
        Sheets("Feuil3").Rows(LigneEnd + 1).Select
        Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
 
     'Mise en forme de la ligne Mensuel Lissé
        Sheets("Feuil3").Rows(LigneEnd).Select
        Selection.Copy
        Sheets("Feuil3").Rows(LigneEnd + 2).Select
        Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
 
'Boucle sur les items (les semaines dans cet exemple)
    For Each PvIt In pvTable.PivotFields("Sem").PivotItems
    'numero de semaine
        x = PvIt.Caption
      'Récupère la derniere ligne du TCD
        Ligne = Sheets("Feuil3").Range(PvIt.DataRange.Address). _
                End(xlDown).Row
    'Récupére la 1ere colonne du TCD pour la semaine cible
        Colonne = Sheets("Feuil3").Range(PvIt.DataRange.Address).Column
    'Insère les données Mensuel Extrapolé sous le TCD
        Dim MensExtraVar As Long
        Sheets("Feuil3").Cells(Ligne + 1, 1) = "Mensuel extrapolé"
        MensExtraVar = pvTable.GetData("'Cost' 'SEM' '" & x & "'")
        Sheets("Feuil3").Cells(Ligne + 1, Colonne + 1) = (MensExtraVar * 52) / 12
    'Insere les données Mensuel lissé sous le TCD
        Dim MensP1 As Long
        Dim MensP2 As Long
        Dim MensP3 As Long
        Dim MensLissVar As Long
        Sheets("Feuil3").Cells(Ligne + 2, 1) = "Mensuel lissé"
        MensP1 = pvTable.GetData("'Cost' 'SEM' '" & x & "'")
        MensP2 = pvTable.GetData("'Cost' 'SEM' '" & x - 1 & "'")
        MensP3 = pvTable.GetData("'Cost' 'SEM' '" & x - 2 & "'")
        MensLissVar = Application.WorksheetFunction.Average(MensP1, MensP2, MensP3)
        Sheets("Feuil3").Cells(Ligne + 2, Colonne + 1) = MensLissVar
    Next
 
End Sub

EDIT : Fichier attaché supprimé

01/10/2006, 16h29
AliochaBada

Salut, c'est encore moi avec mon tableau dynamique en VBA.

SilkyRoad m'a proposé une super solution pour rajouter des lignes personnalisees en fin de tableau, calculant à partir des champs du TCD.
Mais j'ai un souci une fois que je le relie au fichier source réel.
Je crois qu'il n'arrive pas systematiquement à localiser le champ du TCD à partir duquel je veux calculer.

Pour etre plus clair, j'ai un souci avec

Code:

MensExtraVar = pvTable.GetData("'Cost' 'SEM' '" & x & "'")

x represente un champ du field SEM, et prend successivement la valeur de 1 à 52.
Et bien dans mon fichier, il arrive à traduire cette expression que quand 12<x. 8O
Quand x<12, la fenetre espion de VBA me dit que mon nom de champ est ambigu....

Franchement, j'ai du mal là où est son probleme? La seule explication que je vois c'est qu'il ne sait pas si X depend du champ fiel SEMAINE ou MOIS.

Comment est ce que je peux lui preciser que x appartient à SEMAINE ?

J'ai fouillé le WEB toute la soirée, et je ne trouve rien sur GETDATA dans VBA, je trouve que des choses sur LIREDONNEETABLEAUDYNAMIQUE dans Excel.

Quelqu'un pour venir à mon aide?;)
01/10/2006, 17h16
SilkyRoad
bonjour

je ne comprend pas le soucis car dans ton fichier joint Hier à 22h21, cela fonctionne au moins jusqu'à la semaine 16.

LIREDONNEETABLEAUDYNAMIQUE est utilisé comme formule dans Excel
Code:

1 2 Sheets("Feuil3").Range("L27") = _ Sheets("Feuil3").PivotTables("TCD CRL").GetData("'Cost' 'SEM' '16'")
est la même chose que la formule

Code:

=LIREDONNEESTABCROISDYNAMIQUE($A$9:$D$22;"16 Cost")

michel
01/10/2006, 17h37
AliochaBada

1 pièce(s) jointe(s)

J'esperais que tu allais passer par là ;)

Dans mon fichier test allegé les semaines commencaient à 9.
Dans mon fichier reel, les semaines commencent à 1.

J'ai reussi à reproduire l'erreur en rajoutant 6 lignes dans mon fichier source (fond jaune dans la feuille REQUEST bordure ROUGE sur les colonnes suspectes).
- j'ai rajouté d'abord 2 lignes en changeant juste la valeur semaine que je mets à 1 >>>> ca fonctionne nickel dans le TCD
- j'ai rajouté ensuite 4 lignes en changeant la valeur semaine et la valeur REAL DELIVERY MONTH que je mets à 1 >>>> la valeur calculée en fin de TCD ne se mets pas à jour pour les SEMAINES 1. La fenetre espionne m'indique un nom d'élément ambigu.

Si j'arrivais à lui dire que le ' & x & ' est à chercher dans le field SEM, je crois que ca resoudrait le probleme, mais je ne sais pas comment lui indiquer ca.
Si je fais la même operation dans EXCEL avec LIREDONNEES...., ca fonctionne pourtant bien, il sait retrouver la bonne cible...

Grrrr, et il n'y a pas grand chose sur le WEB sur les commandes VBA des TCD...

MErci d'avoir repondu ;) je continue à chercher, je poste si je trouve...
07/10/2006, 00h43
AliochaBada

Salut, toujours pas de solution pour mon probleme de boucle dans un tableau dynamique, à cause d'une definition ambigue de la variable.
J'ai identifié la cause de l'erreur, mais je ne trouve pour l'instant pas de solution....

Comment adresser plus précisement ma variable, afin que le code identifie à coup sûr independamment du nb d'occurence de sa valeur dans d'autres champ du tcd, le champ sur lequel boucler?

Mon tableau dynamique contient :
2 champs de page : Année (2006), Mois (de 1 à 12)
des champs de lignes : categories, item, prix unitaire
des champs de colonnes : N° de semaine (de 1 à 52)
des champs de données : quantité, coût
une ligne finale de total : total quantité, total coût

La boucle s'effectue sur les N° de semaine, enregistrés dans une variable (x), et calcule à partir du total 'coût' 'semaine' 'x', un mensuel extrapolé etc...

Mn probleme : VBA n'arrive pas à identifier clairement la variable X lorsque sa valeur est comprise entre 1 et 12, càd que par exemple quand :
x=1
il ne sait pas si :
1 est le mois de mon champ de page
1 est le numéro de la semaine

Si je supprime le champ de page Mois, le champ de calcul se fait normalement.
Si les valeurs des Semaines sont différentes de celles de Mois, le champ de calcul se fait normalement.
Sinon, lorsque la valeur de x existe aussi dans le champ de page Mois, la variable reste vide, et le calcul ne se fait donc pas.

SilkyROad, si tu repasses par là, regarde juste le fichier bugg dans le post ci dessus, j'ai rajouté des lignes dans la source contenant des valeurs de Mois identiques à celles de N° de Semaines.

Je remercie d'avance pour l'aide qu'on peut m'apporter...

J'ai fini par trouver comment préciser l'appartenance d'un champ de données à un champ de field lorsque leur nom peut preter à confusion avec un autre champ du TCD, et en particulier quand l'un des champ est dénommé par une variable (encore merci SilkyRoad ;)).

Voilà la syntaxe si quelqu'un la cherche :

Code:

MensExtraVar = pvTable.GetData("'Cost' 'SEM' 'SEM'['" & x & "']")

Le code entier corrigé :

Code:

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
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
 
'-- 1. PREPARATION
 
    '---- a. Déclaration des variables
 
        Dim pvTable As PivotTable
        Dim PvIt As PivotItem
        Dim x As Double
        Dim Ligne As Integer, Colonne As Integer, ColonneEnd As Integer, LigneEnd As Integer
        Dim MensExtraVar As Long
        Dim MensP1 As Long, MensP2 As Long, MensP3 As Long, MensLissVar As Long
 
    '---- b. Adressage actualisé du TCD
        'Définition du TCD
            Set pvTable = Sheets("TCD Supplier 2006").PivotTables("TCD S")
        'Definition de la derniere ligne du TCD
            Sheets("TCD Supplier 2006").Range("E9").Select
            Range(Selection, Selection.End(xlDown)).Select
            LigneEnd = Selection.Rows.Count + 8
        'Definition de la derniere colonne du TCD
            Sheets("TCD Supplier 2006").Range("A9").Select
            Range(Selection, Selection.End(xlToRight)).Select
            ColonneEnd = Selection.Columns.Count
 
'-- 2. MISE EN FORME DU TCD
 
    '---- a. Effacement des données précédentes
 
        Sheets("TCD Supplier 2006").Range(Cells(LigneEnd + 1, 1), Cells(LigneEnd + 50, ColonneEnd)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Clear
 
    '---- b. Mise en forme du TCD lui même
 
    '---- c. Mise en forme des lignes calculées
 
        'Mise en forme de la ligne Mensuel Extrapolé
            Sheets("TCD Supplier 2006").Rows(LigneEnd).Select
            Selection.Copy
            Sheets("TCD Supplier 2006").Rows(LigneEnd + 1).Select
            Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Sheets("TCD Supplier 2006").Cells(LigneEnd + 1, 1) = "Mensuel extrapolé"
 
        'Mise en forme de la ligne Mensuel Lissé
            Sheets("TCD Supplier 2006").Rows(LigneEnd + 2).Select
            Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
            Sheets("TCD Supplier 2006").Cells(LigneEnd + 2, 1) = "Mensuel lissé"
 
'-- 3. BOUCLE SUR SEM DES LIGNES CALCULEES EN FIN DE TCD
 
    On Error Resume Next
    For Each PvIt In pvTable.PivotFields("SEM").PivotItems
 
    '---- a. Adressage de l'occurence
        'Numero de Semaine
            x = PvIt.Caption
        'Adressage de la colonne ciblée
            Colonne = Sheets("TCD Supplier 2006").Range(PvIt.DataRange.Address).Column
 
    '---- b. Calculs des lignes de fin de TCD
        'Ligne calculée Mensuel Extrapolé
            MensExtraVar = pvTable.GetData("'Cost' 'SEM' 'SEM'['" & x & "']")
            Sheets("TCD Supplier 2006").Cells(LigneEnd + 1, Colonne + 1) = (MensExtraVar * 52) / 12
        'Ligne calculée Mensuel Lissé
            MensP1 = (MensExtraVar * 52) / 12
            MensP2 = ((pvTable.GetData("'Cost' 'SEM' 'SEM'['" & x - 1 & "']")) * 52) / 12
            MensP3 = ((pvTable.GetData("'Cost' 'SEM' 'SEM'['" & x - 2 & "']")) * 52) / 12
            MensLissVar = Application.WorksheetFunction.Average(MensP1, MensP2, MensP3)
            Sheets("TCD Supplier 2006").Cells(LigneEnd + 2, Colonne + 1) = MensLissVar
 
    Next
 
End Sub