Bonsoir,
Sujet clos, mais une solution avec VBA.
Il y a évidemment un problème de lisibilité, et encore 3 types sont existants dans l'exemple sur les 16 réels.
Réalisé avec Excel 2010. Vérifier l'utilisation avec Excel 2003.
A te relire.
Option Explicit
Public Sub CreatePT()
Dim Ws As Worksheet
Dim iCol As Integer, nb As Integer, i As Integer
Dim myRng As Range
Dim pc As PivotCache
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim cht As Chart
Dim X As Range, Y As Range
Application.ScreenUpdating = False
' Initialisation des variables et des données
Set Ws = Worksheets("Feuil1")
For Each pt In Ws.PivotTables
pt.TableRange2.Clear
Next pt
Set myRng = Ws.Range("A1").CurrentRegion
iCol = Ws.Cells(1, Application.Columns.Count).End(xlToLeft).Column
' Création tableau croisé dynamique
Set pc = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=myRng.Address)
Set pt = pc.CreatePivotTable(TableDestination:=Ws. _
Cells(4, iCol + 2), TableName:="TCD1")
pt.AddFields RowFields:="Type", ColumnFields:="Masse"
With pt.PivotFields("Longueur_max")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Hauteur"
End With
With pt
.ColumnGrand = False
.RowGrand = False
End With
pt.PivotFields("Type").Subtotals(1) = True
pt.PivotFields("Type").Subtotals(1) = False
' Création graphique
Application.DisplayAlerts = False
On Error Resume Next
Charts("Graphique").Delete
On Error GoTo 0
Application.DisplayAlerts = True
nb = pt.PivotFields("Type").PivotItems.Count
Set cht = Charts.Add
cht.Name = "Graphique"
Set X = pt.PivotFields("Masse").DataRange
With cht
.ChartType = xlXYScatter
For i = 1 To nb
.SeriesCollection.NewSeries
.SeriesCollection(i).XValues = X
.SeriesCollection(i).Name = pt.PivotFields("Type").PivotItems(i)
Set Y = pt.PivotFields("Type").PivotItems(i).DataRange
.SeriesCollection(i).Values = Y
Next
.HasLegend = True
End With
Ws.Activate
Cells(1, iCol + 2).Select
Set Ws = Nothing: Set myRng = Nothing: Set pc = Nothing
Set pt = Nothing: Set X = Nothing: Set Y = Nothing
End Sub