Le modèle de données Excel (Power Pivot) vous permet de connecter un grand ensemble de données détaillées de données réelles à un budget de niveau supérieur à l'aide de tables de jointure.
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: une 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 cocher 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.
Regarder la vidéo
- Vous disposez d'un petit ensemble de données de budget descendant
- Vous souhaitez comparer à un ensemble de données réelles ascendantes
- Les chiffres réels peuvent provenir d'un registre de factures
- Le modèle de données vous permettra de comparer ces ensembles de données de tailles différentes
- Transformez les deux ensembles de données en une table Ctrl + T
- Pour chaque champ de texte pour lequel vous souhaitez créer un rapport, créez une table de jointure
- Copiez les valeurs et supprimez les doublons
- Pour les dates, vous pouvez inclure les dates des deux tables et les convertir en fin de mois
- Faire des menuisiers des tables Ctrl + T
- Facultatif mais utile pour nommer les cinq tableaux
- Créez un tableau croisé dynamique à partir de Budget et choisissez le modèle de données
- Créer un tableau croisé dynamique en utilisant Budget et Réel à partir des tableaux d'origine
- Tous les autres champs doivent provenir des tables de jointure
- Ajouter des segments par produit
- Créer trois relations entre Budget et Joiners
- Créer trois relations de Réel à Joiners
- Demain: comment créer des relations plus facilement avec Power Pivot et les formules DAX
Transcription vidéo
Apprenez Excel à partir d'un podcast, épisode 2016 - Budget descendant vs actualités ascendantes!
Hé, je baladore tout ce livre, cliquez sur ce «i» dans le coin supérieur droit et suivez la playlist.
Hé, je vais vous interrompre, voici Bill Jelen dans 15 minutes. Je me rends compte maintenant qu'il s'agit d'un podcast incroyablement long et que vous êtes tenté de cliquer dessus, mais laissez-moi vous en donner une courte. Si vous êtes dans Excel 2013 et que vous avez déjà eu un petit tableau de budget et un énorme tableau de chiffres réels, et que vous devez les mapper ensemble, c'est une nouvelle capacité incroyable que nous avons dans Excel 2013, que peu de gens ont expliqué , et vous ne le savez probablement pas. Si c'est vous, vous êtes en 2013, et vous devez cartographier ces deux ensembles de données, prendre le temps, peut-être aujourd'hui, peut-être demain, peut-être l'ajouter à la liste de surveillance, ça vaut le coup, c'est une technique incroyable.
Bon, voici ce que nous avons, sur le côté gauche nous avons un budget, ce budget, c'est fait au plus haut niveau, de haut en bas, à droite pour chaque ligne de produits, pour chaque région, pour chaque mois, il y a un budget . Pas beaucoup d'enregistrements ici, comptez de 55, sur le côté droit, nous essayons de comparer cela aux réels. Les chiffres réels proviennent d'un registre de factures, nous avons donc la région, le produit et le revenu, mais ce sont des factures individuelles, beaucoup plus de données ici, nous sommes déjà à la moitié de l'année et j'ai déjà 423 enregistrements. Très bien, alors comment associer ces 55 à ces 423? Cela peut être difficile à faire avec RECHERCHEV, vous devez d'abord résumer, mais heureusement dans Excel 2013, le modèle de données rend cela vraiment, vraiment facile. Ce dont nous avons besoin pour permettre à cette grande table massive de communiquer avec cette petite table, ce sont des intermédiaires, je les appelle des menuisiers.De minuscules petites tables, Produit, Région et Calendrier, nous allons joindre le budget à ces trois tables, nous allons joindre le réel à ces trois tables, et miraculeusement le tableau croisé dynamique fonctionnera. Très bien, voici comment procéder.
Tout d'abord, je dois créer les menuisiers, donc je prends ce champ Produit de la colonne A, et je le copie dans la colonne F, puis Données, Supprimer les doublons, cliquez sur OK, et il nous reste une toute petite table, 1 titre 3 rangées. Même chose pour Region, prenez les régions, Ctrl + C, passez à la colonne G, Coller, Supprimer les doublons, cliquez sur OK, 3 lignes 1 en-tête, d'accord. Maintenant, pour les dates, les dates ne sont pas les mêmes, ce sont des dates de fin de mois, elles sont en fait stockées comme des dates de fin de mois et ce sont des jours de la semaine. Je vais prendre les deux listes, Ctrl + C la deuxième liste et la coller ici, Ctrl + V, puis je vais prendre la liste plus courte, la copier et la coller ci-dessous, d'accord. Et c'est vraiment ennuyeux que, même s'ils sont stockés sous forme de dates, ils apparaissent sous forme de mois et Supprimer les doublons ne les verra pas comme les mêmes.Donc, avant d'utiliser Remove Duplicates, je dois le changer en une date courte. Choisissez ces données, Données, Supprimer les doublons, cliquez sur OK, puis faites un petit tri ici pour que cela fonctionne.
Très bien, maintenant je ne veux pas rapporter par date quotidienne, donc je vais ajouter une colonne ici, une colonne de recherche qui dit Mois, et ce sera égal à EOMONTH cette date,, 0, ce qui nous fera sortir la fin du mois. Il formatera cela comme une date courte et copiera cela, d'accord. Maintenant, nous devons transformer chacun d'eux en un tableau Ctrl + T, donc à partir d'ici Ctrl + T, Ma table a des en-têtes, magnifique. Les petits, il ne se rend pas compte que ce sont des en-têtes là-haut, nous devons donc nous assurer de cocher cela et Ctrl + T, d'accord, et ils appellent ces tables Table1, Table2, Table3, des noms vraiment ennuyeux, non? Je vais donc les renommer et les appeler le BudTable, le ProdTable, RegTable, mon CalTable, puis le ActTable, d'accord.
Nous partons du tout premier tableau, et d'ailleurs nous n'allons pas utiliser PowerPivot aujourd'hui, nous allons faire tout cela avec le modèle de données. Donc, Excel 2013 ou plus récent, vous avez cette insertion, tableau croisé dynamique, nous allons cocher la case "Ajouter ces données au modèle de données", cliquez sur OK, et nous obtenons notre liste de champs avec le bouton magique Tout, qui permet Je choisis parmi les cinq tableaux du classeur, Réel, Budget, Calendrier, Produit, Région. Très bien, donc les chiffres vont provenir du tableau du budget, je vais mettre le budget là-dedans, et du tableau réel, je vais y mettre le réel, mais alors voici le truc pour le reste du tableau croisé dynamique. Tous les autres champs de texte que nous allons mettre dans la zone de ligne ou dans la zone de colonne ou en tant que segments, ils doivent provenir des menuisiers, ils doivent provenir de ces tables entre les tables.
D'accord, donc à partir de la table Calendrier, nous prendrons ce champ Mois et le placerons en haut, nous allons ignorer les autres relations pour le moment. Je vais créer les relations, mais je veux les créer toutes en même temps. Et la table Région, mettez les régions sur le côté. Je pourrais mettre les produits de côté, mais je vais en fait utiliser la table Product comme un segment, donc Analyze, Insert Slicer, encore une fois, vous devez aller à Tous si vous n'avez pas encore utilisé la table Product. Alors allez dans Tous, et vous verrez que le produit est disponible pour créer comme segment à partir des produits, comme ça. Très bien maintenant, à ce stade, nous n'avons pas créé de relations, donc tous ces chiffres sont faux. Et les relations que nous devons créer, nous devons créer 3 tableaux à partir de ce petit tableau budgétaire, un aux produits, un aux régions, un au calendrier,c'est 3 relations. Et puis, nous devons créer des relations entre la table réelle et la région Produit dans le calendrier, soit un total de 6 tables. Et oui, ce serait certainement plus facile si nous avions PowerPivot, mais nous ne le faisons pas ou supposons que nous ne le faisons pas.
Et donc je vais utiliser la méthode à l'ancienne, le dialogue Créer ici, où nous avons le tableau Budget sur la gauche, et nous allons utiliser le champ Région et le relier à la table Région, le champ Région . Très bien, 1/6 sont créés. Je vais choisir Créer, à nouveau dans le tableau Budget, nous allons au produit, puis le lier à la table Produit, au produit, cliquez sur OK. De la table Budget, le champ Date, nous allons à la table Calendrier, et le champ Destin, cliquez sur OK, nous sommes à mi-chemin, d'accord. À partir de la table Actuals, nous allons Région, à la table Region, cliquez sur OK, de la table Actuals au produit et de la table Actuals au calendrier. En fait, je vais prendre les valeurs et les faire descendre sur le côté, d'accord. Conception, mise en page du rapport, afficher sous forme tabulaire pour obtenir une vue que je préfère, répéter toutes les étiquettes d'élément, d'accord,c'est absolument incroyable! Nous avons maintenant cette toute petite table, une cinquantaine d'enregistrements dans cette table de centaines d'enregistrements, et nous avons créé une seule table pivotante grâce au modèle de données. Pour chaque endroit où nous pouvons voir le budget, nous pouvons voir le revenu, il est ventilé par région, il est ventilé par mois et il peut être divisé par produit.
Maintenant, ce concept m'est venu de Rob Collie qui exécute Power Pivot Pro, et Rob a créé beaucoup de livres là-bas, son dernier est «Power Pivot and Power BI». Je pense que celui-ci était en fait dans le livre "Power Pivot Alchemy", c'est celui que j'ai vu et j'ai dit "Eh bien ceci, même si je n'ai pas des millions de lignes à signaler via Power Pivot, c'est celui qui ont fait une ÉNORME différence dans ma vie, ayant deux ensembles de données de tailles incompatibles et ayant besoin de rapporter les deux. » Eh bien, cet exemple et bien d'autres sont dans ce livre, je finirai par obtenir le podcast du livre en entier, ce qui semble prendre deux mois et demi. Mais vous pouvez obtenir le livre entier aujourd'hui, en même temps, y aller, acheter le livre, 10 $ pour le livre électronique, 25 $ pour le livre imprimé, et vous pouvez avoir tous ces conseils à la fois.
D'accord, un très long épisode ici: nous avons un petit budget top-down et un bottom up Réel, ils sont de tailles différentes, mais en utilisant le modèle de données dans Excel 2013… Et au fait, si vous êtes en 2010, vous pourriez , en théorie, faites cela en obtenant le complément Power Pivot et suivez toutes ces étapes en 2010. Transformez les deux ensembles de données en une table Ctrl + T, puis joignez vos tables pour tout ce que vous souhaitez rapporter, dans le étiquette de ligne, ou l'étiquette de colonne, ou les segments, copiez donc ces valeurs et supprimez les doublons pour les dates. En fait, j'ai pris des valeurs des deux tables, car il y avait des valeurs uniques dans chacune, puis j'ai utilisé EOMONTH pour sortir, faire de ces tables de jointure des tables contrôlées. C'est facultatif, mais j'ai nommé les 5 tables, car plus facile lorsque vous configurez ces relations, plutôt que de s'appeler Table1,Tableau2, Tableau3.
Et donc, commencez par le tableau Budget, Insertion, Tableau croisé dynamique, cochez la case Modèle de données, puis créez un tableau croisé dynamique à l'aide du budget et du réel. Tout le reste provient des tables de jointure, donc Région et Mois dans la zone de ligne et de colonne, les segments proviennent de la table Product. Et puis nous avons dû créer 3 relations du budget aux menuisiers, 3 relations du réel aux menuisiers, et nous avons un tableau croisé dynamique incroyable. Maintenant, demain, nous examinerons l'utilisation de l'onglet Power Pivot et la création de calculs supplémentaires. Donc, tout cela est possible, c'est lorsque nous voulons insérer un champ calculé, c'est là que vous devez payer les 2 $ supplémentaires par mois pour obtenir la version Pro Plus d'Office 365.
Eh bien, merci à Rob Collie de Power Pivot Pro pour cette astuce, et merci à vous pour votre visite, nous vous reverrons la prochaine fois pour un autre netcast de!
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2016.xlsx
