Vous n'avez pas Power Pivot? Ça n'a pas d'importance. La plupart de Power Pivot est intégré à Excel 2013 et encore plus à Excel 2016. Aujourd'hui, notre conseil de Ash est de joindre des tableaux dans un tableau croisé dynamique.
Tous les mercredis pendant sept semaines, je présente l'un des conseils préférés d'Ash Sharma. Ash est chef de produit dans l'équipe Excel. Son équipe vous propose des tableaux croisés dynamiques et bien d'autres bonnes choses. Aujourd'hui, la fonctionnalité préférée d'Ash est de joindre plusieurs ensembles de données à l'aide des relations et du modèle de données.
Supposons que votre service informatique vous donne l'ensemble de données indiqué dans les colonnes A: D. Il existe des champs pour le client et le marché. Vous devez combiner certains marchés en régions. Chaque client appartient à un secteur. La région et le secteur ne figurent pas dans les données d'origine, mais vous disposez de tables de recherche pour fournir ces informations.
Normalement, vous aplatiriez les données en utilisant RECHERCHEV pour extraire les données des tableaux orange et jaune vers le tableau bleu. Mais puisque le champ clé n'est pas sur le côté gauche de chaque table, vous devrez soit basculer vers INDEX et MATCH, soit réorganiser les tables de recherche.
À partir d'Excel 2013, vous pouvez laisser les tables de recherche là où elles se trouvent et les combiner dans le rapport de tableau croisé dynamique lui-même.
Pour que cette technique fonctionne, les trois tableaux doivent être formatés en tant que tableau. Sélectionnez une cellule dans chaque jeu de données et choisissez Accueil, Mettre en forme comme tableau ou appuyez sur Ctrl + T. Les trois tableaux seront initialement appelés Tableau1, Tableau2 et Tableau3. J'utilise l'onglet Conception des outils de tableau du ruban et renommez chaque table. Je change également la couleur de chaque table. Dans cet exemple, le tableau bleu s'appelle Data. Le tableau orange est RegionTable. Le tableau jaune est SectorTable.
Remarque
Certains vous diront que vous devriez utiliser des noms geek comme Fact, TblSector et TblRegion. Si quelqu'un vous ennuie comme ça, volez simplement son protecteur de poche et faites-lui savoir que vous préférez les noms à consonance anglaise.
Pour renommer une table, tapez un nouveau nom dans la zone située sur le côté gauche de l'onglet Création d'outils de tableau. Les noms de table ne doivent pas contenir d'espaces.
Une fois les trois tables définies, dirigez-vous vers l'onglet Données et cliquez sur Relations.
Dans la boîte de dialogue Gérer les relations, cliquez sur Nouveau. Dans la boîte de dialogue Créer une relation, spécifiez que le champ Client de la table de données est lié au champ client de SectorTable. Cliquez sur OK.
Définissez une autre nouvelle relation entre le champ Market dans les champs Data et RegionTable. Après avoir défini les deux relations, vous les verrez dans la boîte de dialogue Gérer les relations.
Félicitations: vous venez de créer un modèle de données dans votre classeur. Il est temps de créer un tableau croisé dynamique.
Sélectionnez la cellule vide dans laquelle vous souhaitez que votre tableau croisé dynamique apparaisse. Par défaut, la boîte de dialogue Créer un tableau croisé dynamique choisira Utiliser le modèle de données de ce classeur. L'emplacement du tableau croisé dynamique correspondra par défaut à la cellule que vous avez choisie. Cliquez sur OK.
La liste Champs de tableau croisé dynamique répertorie les trois tables. Utilisez le triangle à gauche d'une table pour développer le nom de la table pour afficher les champs.
Développez la table de données. Sélectionnez le champ Revenu. Il se déplacera automatiquement vers la zone Valeurs. Développez le SectorTable. Choisissez le champ Secteur. Il se déplacera vers la zone Lignes. Développez la RegionTable. Faites glisser le champ Région vers la zone Colonnes. Vous aurez maintenant un tableau croisé dynamique résumant les données des trois tableaux.
Remarque
Dans chaque livre que j'ai écrit avant aujourd'hui, j'utilise une technique différente pour rédiger ce rapport. Après avoir défini les trois tableaux, je choisis la cellule A1 et Insérer, tableau croisé dynamique. Je coche la case Ajouter ces données au modèle de données. Dans la liste Champs du tableau croisé dynamique, sélectionnez Tous en haut de la liste. Choisissez des champs pour le rapport, puis définissez les relations après coup. La technique décrite ci-dessus semble plus fluide et implique en fait un tout petit peu de planification à l'avance. Les personnes qui utilisent Option Explicit dans leur code VBA aimeraient certainement cette méthode.
Les relations dans le modèle de données donnent l'impression qu'Excel ressemble plus à Access ou à SQL Server, mais avec toute la bonté d'Excel.
J'adore demander à l'équipe d'Excel leurs fonctionnalités préférées. Chaque mercredi, je partagerai l'une de leurs réponses. Merci à Ash Sharma pour cette idée.
Excellente pensée du jour
J'ai demandé à mes amis Excel Master leurs conseils sur Excel. Pensée d'aujourd'hui à méditer:
"Ne cherchez pas si vous êtes en couple"
John Michaloudis
