Fr. Mark du Kansas a envoyé la question Excel de cette semaine:
Les tableaux croisés dynamiques Excel fonctionnent mieux lorsque les données sont décomposées en le plus grand nombre d'enregistrements possible, mais ce n'est pas toujours le moyen le plus intuitif de charger les données dans Excel. Par exemple, il est intuitif de charger des données comme la figure 1, mais la meilleure façon d'analyser les données est comme dans la figure 2.
Tout d'abord, je vais passer en revue la manière moins que parfaite d'Excel de traiter plusieurs champs de données. Deuxièmement, je vais faire une démonstration d'une macro simple et rapide pour convertir la figure 1 en figure 2.
Si vos données ressemblent à la figure 1, au cours de l'étape 3 sur 4 de l'Assistant Tableau croisé dynamique, il est possible de faire glisser les quatre champs quart dans la zone de données du tableau croisé dynamique, comme illustré à droite.
Voici le résultat loin d'être parfait. Par défaut, Excel a les multiples champs de données descendant la page. Vous pouvez cliquer sur le bouton gris "Données" et le faire glisser vers le haut et vers la droite pour que les quarts traversent la page. Cependant, il vous manque des totaux pour chaque région et les totaux trimestriels sembleront toujours déplacés.
Alors, le P. Mark a frappé le clou sur la tête en disant que les données doivent vraiment être au format de la figure 2 pour pouvoir les analyser correctement. Vous trouverez ci-dessous une macro qui déplacera rapidement les données au format de la figure 1 de la feuille Sheet1 vers la feuille Sheet2 au format de la figure 2. Cette macro n'est pas assez générale pour fonctionner avec n'importe quel ensemble de données. Cependant, il devrait être relativement facile de le personnaliser en fonction de votre situation particulière.
Public Sub TransformData() ' Copyright 1999.com Sheets("Sheet2").Select Range("A1").CurrentRegion.Clear Sheets("Sheet1").Select Range("A1:B1").Copy Destination:=Sheets("Sheet2").Range("A1") Sheets("Sheet2").Select Range("C1").Value = "Qtr" Range("D1").Value = "Sales" Sheets("Sheet1").Select FinalRow = Range("A16000").End(xlUp).Row NextRow = 2 LastRow = FinalRow ' Loop through the data columns For i = 3 To 6 ThisCol = Mid("ABCDEFGHIJK", i, 1) ' Copy the left columns from sheet1 to sheet2 Range("A2:B" & FinalRow).Copy Destination:= _ Sheets("Sheet2").Range("A" & NextRow) ' Copy the header from ThisCol to column C Range(ThisCol & "1").Copy Destination:= _ Sheets("Sheet2").Range("C" & NextRow & ":C" & LastRow) ' Copy the data for this quarter to column D Range(ThisCol & "2:" & ThisCol & FinalRow).Copy _ Destination:=Sheets("Sheet2").Range("D" & NextRow) NextRow = LastRow + 1 LastRow = NextRow + FinalRow - 2 Next i Sheets("Sheet2").Select End Sub
Après avoir exécuté cette macro, les données seront dans le format plus facile à analyser illustré à la figure 2 ci-dessus. Désormais, lorsque vous utilisez ces données pour un tableau croisé dynamique, vous avez un contrôle total sur les données comme d'habitude.
