Préparation des données source pour le tableau croisé dynamique

Avoir les données au bon format est une étape cruciale dans la création d'un tableau croisé dynamique robuste et sans erreur. Si ce n'est pas fait correctement, vous pouvez finir par avoir beaucoup de problèmes avec votre tableau croisé dynamique.

Qu'est-ce qu'une bonne conception pour les données source pour le tableau croisé dynamique ?

Jetons un coup d'œil à un exemple de bonnes données source pour un tableau croisé dynamique.

Voici ce qui en fait une bonne conception de données source :

• La première ligne contient des en-têtes qui décrivent les données dans les colonnes.
• Chaque colonne représente une catégorie de données unique. Par exemple, la colonne C contient uniquement les données sur les produits et la colonne D et les données mensuelles uniquement.
• Chaque ligne est un enregistrement qui représenterait une instance de la transaction ou de la vente.
• Les en-têtes de données sont uniques et ne sont répétés nulle part dans l'ensemble de données. Par exemple, si vous avez des chiffres de ventes pour quatre trimestres dans une année, vous ne devez PAS tous les nommer comme ventes. Au lieu de cela, donnez à ces en-têtes de colonne des noms uniques tels que Sales Q1, Sales Q2, etc.
  • Si vous n'avez pas de titres uniques, vous pouvez toujours créer un tableau croisé dynamique et Excel les rendrait automatiquement uniques en ajoutant un suffixe (tel que Sales, Sales2, Sales3). Cependant, ce serait une façon terrible de préparer et d'utiliser un tableau croisé dynamique.

Pièges courants à éviter lors de la préparation des données sources

• Il ne devrait pas y avoir de colonnes vides dans les données source. Celui-ci est facile à repérer. Si vous avez une colonne vide dans les données source, vous ne pourrez pas créer de tableau croisé dynamique. Il affichera une erreur comme indiqué ci-dessous.
• Il ne devrait pas y avoir de cellules/lignes vides dans les données source. Bien que vous puissiez créer avec succès un tableau croisé dynamique malgré des cellules ou des lignes vides, de nombreux effets secondaires peuvent vous ronger plus tard dans la journée.
  • Par exemple, disons que vous avez une cellule vide dans la colonne des ventes. Si vous créez un tableau croisé dynamique en utilisant ces données et placez le champ de vente dans la zone des colonnes, cela vous montrera le COUNT et non la SUM. C'est parce qu'Excel interprète la colonne entière comme contenant des données textuelles (juste à cause d'une seule cellule vide).
• Appliquez le format approprié aux cellules des données source. Par exemple, si vous avez des dates (qui sont stockées sous forme de numéros de série dans le backend d'Excel), appliquez l'un des formats de date acceptables. Cela vous aiderait à créer le tableau croisé dynamique et à utiliser la date comme l'un des critères pour résumer, regrouper et trier les données.
  • Si vous avez quelques secondes, essayez ceci. Mettez en forme les dates de votre tableau croisé dynamique sous forme de nombres, puis créez un tableau croisé dynamique à l'aide de ces données. Maintenant, dans le tableau croisé dynamique, sélectionnez le champ de date et voyez ce qui se passe. Il le placera automatiquement dans la zone des valeurs. C'est parce que votre tableau croisé dynamique ne sait pas que ce sont des dates. Il les interprète comme des nombres.
• N'incluez pas de totaux de colonnes, de totaux de lignes, de moyennes, etc., dans les données source. Une fois que vous avez le tableau croisé dynamique, vous pouvez facilement les obtenir plus tard.
• Créez toujours un tableau Excel, puis utilisez-le comme source pour un tableau croisé dynamique. C'est plus une bonne pratique qu'un piège. Votre tableau croisé dynamique fonctionnerait parfaitement avec des données source qui ne sont pas non plus un tableau Excel. L'avantage d'Excel Table est qu'il peut ajuster les données en expansion. Si vous ajoutez plus de lignes à l'ensemble de données, vous n'avez pas besoin d'ajuster les données source encore et encore. Vous pouvez simplement actualiser le tableau croisé dynamique et il prendrait automatiquement en compte les nouvelles lignes ajoutées aux données source.

Exemples de conceptions de données source incorrectes

Jetons un coup d'œil à quelques mauvais exemples de conceptions de données sources.

Mauvaise conception de données source - Exemple 1

Il s'agit d'un moyen courant de conserver les données, car elles sont faciles à suivre et à comprendre. Il y a deux problèmes avec cet arrangement de données :

• Vous n'obtenez pas l'image complète. Par exemple, vous pouvez voir que les ventes pour Mid West au premier trimestre sont de 2924300. Mais s'agit-il d'une seule vente ou de plusieurs ventes. Si vous avez chaque enregistrement disponible dans une ligne séparée, vous pouvez faire une meilleure analyse.
• Si vous allez de l'avant et créez un tableau croisé dynamique en utilisant cela (ce que vous pouvez), vous obtiendrez différents champs pour différents trimestres. Quelque chose comme indiqué ci-dessous :

Mauvaise conception de données source - Exemple 2

Cette représentation des données peut être bien reçue par la direction et le public des présentations PowerPoint, mais elle ne convient pas à la création d'un tableau croisé dynamique.

Encore une fois, c'est le genre de résumé que vous pouvez facilement créer à l'aide d'un tableau croisé dynamique. Ainsi, même si vous souhaitez éventuellement un tel aspect pour vos données, conservez les données source dans un format prêt pour Pivot et créez cette vue à l'aide du tableau croisé dynamique.

Mauvaise conception de données source - Exemple 3

Il s'agit à nouveau d'une sortie qui peut facilement être obtenue à l'aide d'un tableau croisé dynamique. Mais il ne peut pas être utilisé pour créer un tableau croisé dynamique.

Il y a des cellules vides dans l'ensemble de données et les trimestres sont répartis sous forme d'en-têtes de colonne.

De plus, la région est spécifiée en haut, alors qu'elle devrait faire partie de chaque enregistrement.

[ÉTUDE DE CAS] Conversion de données mal formatées en données source prêtes pour le tableau croisé dynamique

Parfois, vous pouvez obtenir un ensemble de données qui ne convient pas pour être utilisé comme données source pour le tableau croisé dynamique. Dans un tel cas, vous n'aurez peut-être pas d'autre choix que de convertir les données dans un format de données convivial Pivot.

Voici un exemple de mauvaise conception de données :

Vous pouvez maintenant utiliser les fonctions Excel ou la requête pivot pour convertir ces données dans un format pouvant être utilisé comme données source pour le tableau croisé dynamique.

Voyons comment fonctionnent ces deux méthodes.

Méthode 1 : Utilisation de formules Excel

Voyons comment utiliser les fonctions Excel pour convertir ces données dans un format prêt pour le tableau croisé dynamique.

• Créez un en-tête de colonne unique pour toutes les catégories de l'ensemble de données d'origine. Dans cet exemple, il s'agirait de Region, Quarter et Sales.
• Dans la cellule sous l'en-tête Region, utilisez la formule suivante : =INDEX($A$2:$A$5,ROUNDUP(ROWS($A$2:A2)/COUNTA($B$1:$E$1),0))
  • Faites glisser la formule vers le bas et elle répétera toutes les régions.
• Dans la cellule sous l'en-tête Trimestre, utilisez la formule suivante : =INDEX($B$1:$E$1,ROUNDUP(MOD(ROWS($A$2:A2),COUNTA($B$1:$E$1)+0.1) ,0))
  • Faites glisser la formule vers le bas et elle répétera tous les trimestres.
• Dans l'en-tête sous Ventes, utilisez la formule suivante : =INDEX($B$2:$E$5,MATCH(G2,$A$2:$A$5,0),MATCH(H2,$B$1:$E$1,0 ))
  • Faites-le glisser vers le bas pour obtenir toutes les valeurs. Cette formule utilise les données Region et Quarter comme valeurs de recherche et renvoie la valeur des ventes de l'ensemble de données d'origine.

Vous pouvez maintenant utiliser ces données résultantes comme données source pour le tableau croisé dynamique.

Cliquez ici pour télécharger le fichier d'exemple.

Méthode 2 : Utilisation de Power Query

Power Query a une fonctionnalité qui peut facilement convertir ce type de données au format de données Pivot ready.

Si vous utilisez Excel 2016, les fonctionnalités de Power Query seraient disponibles dans l'onglet Données du groupe Obtenir et transformer. Si vous utilisez Excel 2013 ou des versions antérieures, vous pouvez l'utiliser comme complément.

Voici un excellent guide sur l'installation de Power Query par Jon d'Excel Campus.

Encore une fois, étant donné que vous avez les données formatées comme indiqué ci-dessous :

Voici les étapes pour convertir les données source au format prêt pour le tableau croisé dynamique :

• Convertissez les données dans un tableau Excel. Sélectionnez le jeu de données et accédez à Insertion -> Tables -> Table.
• Dans la boîte de dialogue Insérer un tableau, assurez-vous que la plage correcte est sélectionnée et cliquez sur OK. Cela convertira les données tabulaires en un tableau Excel.
• Dans Excel 2016, accédez à Données -> Obtenir et transformer -> À partir de la table.
  • Si vous utilisez le complément Power Query dans une version antérieure, accédez à Power Query -> Données externes -> À partir de la table.
• Dans l'éditeur de requête, sélectionnez les colonnes dont vous souhaitez annuler le pivotement. Dans ce cas, ce sont ceux des quatre trimestres. Pour sélectionner toutes les colonnes, maintenez la touche Maj enfoncée puis sélectionnez la première colonne puis la dernière colonne.
• Dans l'éditeur de requête, accédez à Transformer -> N'importe quelle colonne -> Annuler le pivotement des colonnes. Cela convertira les données de la colonne au format convivial du tableau croisé dynamique.
• Power Query donne des noms génériques aux colonnes. Remplacez ces noms par ceux que vous voulez. Dans ce cas, remplacez Attribut par Quarter et Value par Sales.
• Dans l'éditeur de requête, accédez à Fichier -> Fermer et charger. Cela fermera la boîte de dialogue Éditeur Power Query et créera une feuille de calcul distincte qui contiendra les données avec des colonnes non pivotées.

Maintenant que vous savez comment préparer les données source pour le tableau croisé dynamique, vous êtes prêt à exceller dans le monde des tableaux croisés dynamiques.

Voici d'autres didacticiels sur les tableaux croisés dynamiques qui pourraient vous être utiles :

• Comment actualiser le tableau croisé dynamique dans Excel.
• Utilisation des trancheurs dans le tableau croisé dynamique Excel - Guide du débutant.
• Comment regrouper des dates dans des tableaux croisés dynamiques dans Excel.
• Comment regrouper des nombres dans un tableau croisé dynamique dans Excel.
• Cache Pivot dans Excel - Qu'est-ce que c'est et comment l'utiliser au mieux.
• Comment filtrer les données dans un tableau croisé dynamique dans Excel.
• Comment ajouter et utiliser un champ calculé de tableau croisé dynamique Excel
• Comment appliquer une mise en forme conditionnelle dans un tableau croisé dynamique dans Excel
• Comment remplacer les cellules vides par des zéros dans les tableaux croisés dynamiques Excel