Les budgets sont établis au plus haut niveau - revenus par ligne de produits par région par mois. Les chiffres réels s'accumulent lentement au fil du temps - facture par facture, article par article. Comparer le petit fichier Budget aux volumineuses données réelles a toujours été une douleur. J'adore cette astuce de Rob Collie, alias PowerPivotPro.com.
Pour configurer l'exemple, vous disposez d'un tableau de budget de 54 lignes: 1 ligne par mois par région et par produit.
Le fichier de facture est au niveau du détail: 422 lignes jusqu'à présent cette année.
Il n'y a pas de RECHERCHEV dans le monde qui vous permettra de faire correspondre ces deux ensembles de données. Mais, grâce à Power Pivot (alias le modèle de données dans Excel 2013+), cela devient facile.
Vous devez créer de minuscules petites tables que j'appelle «joiners» pour relier les deux ensembles de données plus grands.
Dans mon cas, Produit, Région et Date sont en commun entre les deux tables. La table Product est une minuscule table à quatre cellules. Idem pour la région. Créez chacun de ceux-ci en copiant les données d'une table et en utilisant Supprimer les doublons.
La table de calendrier sur la droite était en fait plus difficile à créer. Les données budgétaires comportent une ligne par mois, tombant toujours à la fin du mois. Les données de facturation indiquent les dates quotidiennes, généralement les jours de la semaine. J'ai donc dû copier le champ Date des deux ensembles de données dans une seule colonne, puis supprimer les doublons pour m'assurer que toutes les dates sont représentées. J'ai ensuite =TEXT(J4,"YYYY-MM")créé une colonne Mois à partir des dates quotidiennes.
Si vous ne disposez pas du complément Power Pivot complet, vous devez créer un tableau croisé dynamique à partir du tableau Budget et cochez la case Ajouter ces données au modèle de données.
Comme indiqué dans le conseil précédent, lorsque vous ajoutez des champs au tableau croisé dynamique, vous devrez définir six relations. Bien que vous puissiez le faire avec six visites dans la boîte de dialogue Créer une relation, j'ai lancé mon complément Power Pivot et utilisé la vue de diagramme pour définir les six relations.
Voici la clé pour faire tout ce travail: Vous êtes libre d'utiliser les champs numériques de Budget et de Réel. Mais si vous souhaitez afficher la région, le produit ou le mois dans le tableau croisé dynamique, ils doivent provenir des tables de jointure!
Voici un tableau croisé dynamique avec des données provenant de cinq tableaux. La colonne A provient du menuisier de la région. La ligne 2 provient du joint de calendrier. Le segment de produit provient du menuisier de produit. Les chiffres du budget proviennent de la table Budget et les chiffres réels de la table Facture.
Cela fonctionne car les tables de jointure appliquent des filtres aux tables Budget et Réel. C'est une belle technique et montre que Power Pivot n'est pas uniquement destiné au big data.
