Pivot table, wrong output
Bonjour a tous,
je suis entrain de travailler sur des datas. a partir d'un tableau de data je souhaite creer des graphs en passant par les pivots tables. cependant impossible d'arriver a mes fins.. Si quelqu'un a des conseils/astuces.
a partir d'un tableau de donnees comme celui en piece jointe (template). j'aimerais faire une pivot table et depuis cette pivot table creer facilement des graphs dynamiques et personalisables (tout l'interet des pivots tables).
cependant des que j'essaie de creer des pivot tables, je n'arrive pas a avoir ce que je veux.
En piece jointe le fichier excel avec parmis les sheets:
Data= sheet contenant mes donnees
Pivot table= un exemple de pivot table que vous pouvez manipuler
output souhaite= des exemples de courbes que j'aimerais faire
Merci d'avance pour votre aide.
ps:excusez moi pour l'orthographe et la ponctuation, je suis sur un clavier qwerty sans ponctuation francaise.
Bonjour,
Ton tableau comporte principalement des données textes (pas numériques).
Ensuite, quelles opérations veux-tu effectuer (nombre de, somme de, etc...)
Merci de nous apporter quelques éclaircissements, en te rappelant que sur les versions Excel françaises, c'est la virgule, le séparateur décimal !...
Cdlt.
Bonjour a tous,
merci pour vos retours.
Concernant les points et virgules, ici le separateur des decimales est le point et la virgule est un separateur de milliers.
Je souhaite creer des graphs avec pour chaque courbe, chaque point correspondant a la valeur d'un des champs de test (ETH,VDCPU, VDH, CDL, ..). et chaque courbe correspond ensuite a un des filtres (Release, platform, ...)
donc pour resume plusieurs graphs, pour chaque graphe on fixe toutes les colonnes avec du test sauf une qui varie et les points correspondent aux donnes sous les colonnes (ETH, VDCPU, ..)
si vous regardez sous l'onglet output shouaite vous avez quelques exemples des output souhaite.
Merci d'avance de votre help.
Bonjour,
Ton fichier en retour avec des valeurs numériques.
Tu peux donc créer tes TCDs et tes graphiques.
Cdlt.
Bonjour Jean-Eric,
Merci de votre aide,
cependant j'ai maintenant la somme total pour chaque platform et je n'ai pas encore l'output souhaite.
il faudrait plutot quelque chose comme:
pout tel release, testmode et receive mode on a un tableau avec sur deux colonnes: les deux platforms (spear,lces) et sur les lignes: ETH2, VDCPU2,...
de cette maniere je peux je pense finir avec des graphs comme dans l'onglet Output Souhaite.
Merci de votre aide.
Bonsoir à tous,
Pour traiter les données de la feuille "data", j'ai remplacé les valeurs des colonnes F, H, J, L, N, P, R et T par du numérique.
J'ai traité uniquement la colonne "Release"
J'ai créé la Feuil1 manuellement pour la restitution
Option Explicit
Sub test()
Dim a, w(), e, i As Long, j As Long, n As Long, dico As Object
Set dico = CreateObject("Scripting.Dictionary")
dico.CompareMode = 1
With Sheets("Data").Range("a1").CurrentRegion
a = Application.Index(.Value, Evaluate("row(1:" & _
.Rows.Count & ")"), Array(1, 2, 3, 4, 5, 6, 8, 10, 12, 14, 16, 18, 20))
For i = 2 To UBound(a, 1)
If Not dico.exists(a(i, 3)) Then
Set dico(a(i, 3)) = _
CreateObject("Scripting.Dictionary")
dico(a(i, 3))(a(1, 2)) = _
Application.Index(a, 1, Array(2, 6, 7, 8, 9, 10, 11, 12, 13))
End If
If Not dico(a(i, 3)).exists(a(i, 2)) Then
dico(a(i, 3))(a(i, 2)) = _
Application.Index(a, i, Array(2, 6, 7, 8, 9, 10, 11, 12, 13))
Else
w = dico(a(i, 3))(a(i, 2))
For j = 6 To UBound(a, 2)
w(j - 4) = w(j - 4) + a(i, j)
Next
dico(a(i, 3))(a(i, 2)) = w
End If
Next
End With
Application.ScreenUpdating = False
With Sheets("Feuil1").Range("a1")
.Parent.UsedRange.Cells.Clear
For Each e In dico.keys
With .Offset(n)
.Value = e
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
With .Font
.Bold = True
.Name = "calibri"
.Size = 14
End With
End With
n = n + 1
With .Offset(n).Resize(dico(e).Count, UBound(Application.Index(dico(e).items, 0, 0), 2))
.Value = Application.Index(dico(e).items, 0, 0)
With .Font
.Name = "calibri"
.Size = 10
End With
.VerticalAlignment = xlCenter
.Borders(xlInsideVertical).Weight = xlThin
.BorderAround Weight:=xlThin
With .Rows(1)
.HorizontalAlignment = xlCenter
With .Offset(, 1).Resize(, .Columns.Count - 1)
.Interior.ColorIndex = 36
.BorderAround Weight:=xlThin
End With
End With
With .Columns(1)
.HorizontalAlignment = xlCenter
With .Offset(1).Resize(.Rows.Count - 1)
.Interior.ColorIndex = 40
.BorderAround Weight:=xlThin
End With
End With
End With
n = n + dico(e).Count + 1
Next
End With
Application.ScreenUpdating = True
Set dico = Nothing
End Subgms821, est-ce le résultat attendu
klin89
Re
Voilà le traitement s'effectue désormais sur les 3 colonnes "Release" "TestMode" et "ReceiveMode"
Option Explicit
Sub test()
Dim a, w(), e, v, i As Long, j As Byte, col As Byte, n As Long, dico As Object
Set dico = CreateObject("Scripting.Dictionary")
dico.CompareMode = 1
With Sheets("Data").Range("a1").CurrentRegion
a = Application.Index(.Value, Evaluate("row(1:" & _
.Rows.Count & ")"), Array(1, 3, 2, 4, 5, 6, 8, 10, 12, 14, 16, 18, 20))
For i = 2 To UBound(a, 1)
If Not dico.exists(a(i, 2)) Then
Set dico(a(i, 2)) = _
CreateObject("Scripting.Dictionary")
For col = 3 To 5
Set dico(a(i, 2))(a(1, col)) = CreateObject("Scripting.Dictionary")
dico(a(i, 2))(a(1, col)).CompareMode = 1
dico(a(i, 2))(a(1, col))(a(1, col)) = _
Application.Index(a, 1, Array(col, 6, 7, 8, 9, 10, 11, 12, 13))
Next
End If
For col = 3 To 5
If Not dico(a(i, 2))(a(1, col)).exists(a(i, col)) Then
dico(a(i, 2))(a(1, col))(a(i, col)) = _
Application.Index(a, i, Array(col, 6, 7, 8, 9, 10, 11, 12, 13))
Else
w = dico(a(i, 2))(a(1, col))(a(i, col))
For j = 6 To UBound(a, 2)
w(j - 4) = w(j - 4) + a(i, j)
Next
dico(a(i, 2))(a(1, col))(a(i, col)) = w
End If
Next
Next
End With
'restitution et mise en forme
Application.ScreenUpdating = False
With Sheets("Feuil1").Range("a1")
.Parent.UsedRange.Cells.Clear
For Each e In dico.keys
With .Offset(n)
.Value = e
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 15
.BorderAround Weight:=xlThin
With .Font
.Bold = True
.Name = "calibri"
.Size = 14
End With
End With
n = n + 1
For Each v In dico(e).keys
With .Offset(n).Resize(dico(e)(v).Count, _
UBound(Application.Index(dico(e)(v).items, 0, 0), 2))
.Value = Application.Index(dico(e)(v).items, 0, 0)
With .Font
.Name = "calibri"
.Size = 10
End With
.VerticalAlignment = xlCenter
.Borders(xlInsideVertical).Weight = xlThin
.BorderAround Weight:=xlThin
With .Rows(1)
.HorizontalAlignment = xlCenter
With .Offset(, 1).Resize(, .Columns.Count - 1)
.Interior.ColorIndex = 36
.BorderAround Weight:=xlThin
End With
End With
With .Columns(1)
.HorizontalAlignment = xlCenter
With .Offset(1).Resize(.Rows.Count - 1)
.Interior.ColorIndex = 40
.BorderAround Weight:=xlThin
End With
End With
End With
n = n + dico(e)(v).Count + 1
Next
Next
End With
Application.ScreenUpdating = True
Set dico = Nothing
End Subklin89
Re
Voilà le traitement s'effectue désormais sur les 3 colonnes "Release" "TestMode" et "ReceiveMode"
Option Explicit Sub test() Dim a, w(), e, v, i As Long, j As Byte, col As Byte, n As Long, dico As Object Set dico = CreateObject("Scripting.Dictionary") dico.CompareMode = 1 With Sheets("Data").Range("a1").CurrentRegion a = Application.Index(.Value, Evaluate("row(1:" & _ .Rows.Count & ")"), Array(1, 3, 2, 4, 5, 6, 8, 10, 12, 14, 16, 18, 20)) For i = 2 To UBound(a, 1) If Not dico.exists(a(i, 2)) Then Set dico(a(i, 2)) = _ CreateObject("Scripting.Dictionary") For col = 3 To 5 Set dico(a(i, 2))(a(1, col)) = CreateObject("Scripting.Dictionary") dico(a(i, 2))(a(1, col)).CompareMode = 1 dico(a(i, 2))(a(1, col))(a(1, col)) = _ Application.Index(a, 1, Array(col, 6, 7, 8, 9, 10, 11, 12, 13)) Next End If For col = 3 To 5 If Not dico(a(i, 2))(a(1, col)).exists(a(i, col)) Then dico(a(i, 2))(a(1, col))(a(i, col)) = _ Application.Index(a, i, Array(col, 6, 7, 8, 9, 10, 11, 12, 13)) Else w = dico(a(i, 2))(a(1, col))(a(i, col)) For j = 6 To UBound(a, 2) w(j - 4) = w(j - 4) + a(i, j) Next dico(a(i, 2))(a(1, col))(a(i, col)) = w End If Next Next End With 'restitution et mise en forme Application.ScreenUpdating = False With Sheets("Feuil1").Range("a1") .Parent.UsedRange.Cells.Clear For Each e In dico.keys With .Offset(n) .Value = e .VerticalAlignment = xlCenter .HorizontalAlignment = xlCenter .Interior.ColorIndex = 15 .BorderAround Weight:=xlThin With .Font .Bold = True .Name = "calibri" .Size = 14 End With End With n = n + 1 For Each v In dico(e).keys With .Offset(n).Resize(dico(e)(v).Count, _ UBound(Application.Index(dico(e)(v).items, 0, 0), 2)) .Value = Application.Index(dico(e)(v).items, 0, 0) With .Font .Name = "calibri" .Size = 10 End With .VerticalAlignment = xlCenter .Borders(xlInsideVertical).Weight = xlThin .BorderAround Weight:=xlThin With .Rows(1) .HorizontalAlignment = xlCenter With .Offset(, 1).Resize(, .Columns.Count - 1) .Interior.ColorIndex = 36 .BorderAround Weight:=xlThin End With End With With .Columns(1) .HorizontalAlignment = xlCenter With .Offset(1).Resize(.Rows.Count - 1) .Interior.ColorIndex = 40 .BorderAround Weight:=xlThin End With End With End With n = n + dico(e)(v).Count + 1 Next Next End With Application.ScreenUpdating = True Set dico = Nothing End Subklin89
Hello Klin, merci de ton aide!
w(j - 4) = w(j - 4) + a(i, j)run-time error '13': type mismatch.
sais-tu ce que je devrais faire?
Bonjour,
Pas de retour sur ma proposition du 27 mai ?
Cdlt.
Bonjour,
Pas de retour sur ma proposition du 27 mai ?
Cdlt.
Bonjour Jean-Eric je suis entrain d'etudier votre solution qui a l'air tres interessante! Je reviens vite vers vous pour vous donnez un retour.
Merci beaucoup de votre aide!
Votre solution fonctionne vraiment bien merci beaucoup!
Je suis entrain de "jouer" avec les pivots table pour atteindre les graphs que je souhaite, cependant c'est vraiment bien fait, merci beaucoup!
Je reviendrais vers vous si j'ai besoin de plus amples informations!
bonjour Jean-Eric,
J'avais un peu laisse tombe ce projet, cependant je m'y remet en ce moment et j'aimerais avoir quelques clarifications:
Les Data sont ordonnes de la maniere suivante 0.112345. dans le code nous prenons le nombre apres la premiere virgule. A quelle endroit dans le code vous prenez le nombre juste apres la premiere virgule?
Nous avons desormais un nouveau set de data de la maniere suivante: 0,average,min,max. Nous souhaiterions ajouter en plus de la colonne amount une colonne Min et Max avec dedans les donnes presentes apres les virgules. Je suppose que l'on fait cela dans la fonction fnConvert? Auriez vous du temps a m'accorder par mail ou sur ce forum pour m'aider sur ce sujet?
Merci beaucoup!
Bonne journee,
Bonjour,
1 - Il y a un module Functions qui intègre la fonction fnConvert : Elle convertit une valeur texte en nombre décimal (voir aide VBA sur Val)
2 - Pour la suite, envoie un nouveau fichier (sur base de ce que j'avais fait) avec des explications claires.
A te relire
Cdlt.
Bonjour Jean-Eric,
En piece jointe tu peux trouver ton template avec en plus deux autres colonnes sur la feuille "Pivot Table". Une nouvelle colonne Min, une autre Max, et la precedente colonne qui s'apellait Amount est desormais Average.
Si on regarde dans le feuille Data, au niveau de la release 15, nous avons un nouveau format de Data.
Example : 0,10899.998,9500.0,11400.0
la virgule est toujours utilise en temps que separateur de la maniere suivante:
premier nombre, Average, Min, Max
Je souhaiterais donc que lors du process des datas, les datas Average aillent dans la bonne colonne, de meme pour Min et Max.
J'ai egalement une seconde amelioration que je souhaiterais faire:
Dans les feuilles "perPlatform" et "PerRelease" nous avons des pivot tables avec le graphique relie. lorsque nous modifions les variables le nouveau graphique est genere. Je souhaite garder cela, mais en plus, creer deux nouvelle feuilles(une pour le perPlatform et l'autre pour le perRelease), avec dedans tout les graphs possible du pivot table.
Je ne sais pas si je suis claire, n'hesitez pas a me redire.
Merci beaucoup pour votre aide.
Bonjour,
Tu as 2 propositions :
1 - VBA tel que précédemment
2 - Récupérer et transformer (Power Query, natif Excel 2016).
Il faudrait envisager la solution Power Query !...
Bon, commence d'abord pour valider les résultats.
A te relire.
Cdlt.
Bonjour Jean-eric,
Entre la precedente et la nouvelle version la Description n'est pas dans le meme ordre? Nous avions avant ETH puis VDCPU puis VDH.. et desormais nous avons ETH puis NMH puis NML ... ou pouvons nous changer ca pour remettre comme avant?
Concernant la nouvelle feuille "Power Query" C'est deux pivot table sur la meme feuille (les deux presente dans perPlatform et perRelease).
J'ai lu un peu ce qu'est Power Query et je ne comprends pas vraiment comment je peux appliquer cela a ma situation? je souhaite en realite avoir tout les graphs possible sur une nouvelle feuille.
Merci de ton aide!
Bonjour,
Je te propose 2 solutions, te demande de valider les résultats et tu me parles de mise en forme des données !...
Les résultats sont ils corrects ?
On verra la mise en forme après.
Pour la solution Power Query, dans la mesure tu ne maîtrises pas VBA, c'est certainement la voie la plus simple (avec cependant un certain apprentissage). Quitte à apprendre, autant aller au plus simple.
Je te renvoie le fichier avec ton souhait pour les libellés (il suffisait de sélectionner la ligne et de la déplacer dans le TCD !...)
A te relire.
Cdlt.
Bonjour Jean-Eric,
Avec les donnes mise en forme je peux maintenant dire que les resultats sont corrects
Tout cela est correct. Je suis entrain de regarder la feuille "power query". A partir des deux tableaux, avec Power Qwery je peux generer tout les graphs possible?
Par exemple pour tel platform, je souhaite avoir un graph par Release, ... De meme pour chaque release je souhaite avoir un graph par platform!
Cela est donc possible avec Power Query?
Thank you,