Avantages du pivot par modèle de données

Lors de la création d'un tableau croisé dynamique dans Excel, dans la toute première boîte de dialogue, où l'on nous demande de définir la plage initiale et de choisir un emplacement pour insérer le tableau croisé dynamique, il y a une case à cocher discrète mais très importante ci-dessous - Ajouter ces données au modèle de données (Ajoutez ces données au modèle de données) et, un peu plus haut, l'interrupteur Utiliser le modèle de données de ce livre (Utilisez le modèle de données de ce classeur):

Malheureusement, de nombreux utilisateurs qui connaissent depuis longtemps les tableaux croisés dynamiques et les utilisent avec succès dans leur travail ne comprennent parfois pas vraiment la signification de ces options et ne les utilisent jamais. Et en vain. Après tout, la création d'un tableau croisé dynamique pour le modèle de données nous offre plusieurs avantages très importants par rapport au tableau croisé dynamique Excel classique.

Cependant, avant d'examiner ces "petits pains" de près, comprenons d'abord ce qu'est, en fait, ce modèle de données ?

Qu'est-ce qu'un modèle de données

Modèle de données (en abrégé MD ou DM = Data Model) est une zone spéciale à l'intérieur d'un fichier Excel où vous pouvez stocker des données tabulaires - une ou plusieurs tables liées, si vous le souhaitez, les unes aux autres. En fait, il s'agit d'une petite base de données (cube OLAP) intégrée dans un classeur Excel. Par rapport au stockage classique des données sous forme de tableaux réguliers (ou intelligents) sur des feuilles d'Excel lui-même, le Modèle de Données présente plusieurs avantages non négligeables :

• Les tableaux peuvent contenir jusqu'à 2 milliards de lignes, et une feuille Excel peut en contenir un peu plus d'un million.
• Malgré la taille gigantesque, le traitement de telles tables (filtrage, tri, calculs sur celles-ci, résumé de construction, etc.) est effectué très vite Beaucoup plus rapide qu'Excel lui-même.
• Avec les données du modèle, vous pouvez effectuer des calculs supplémentaires (si vous le souhaitez, très complexes) en utilisant langage DAX intégré.
  
• Toutes les informations chargées dans le modèle de données sont très fortement compressé en utilisant un archiveur intégré spécial et augmente plutôt modérément la taille du fichier Excel d'origine.
  

Le modèle est géré et calculé par un complément spécial intégré à Microsoft Excel – PowerPivotdont j'ai déjà écrit. Pour l'activer, dans l'onglet promoteur cliquez Compléments COM (Développeur — Compléments COM) et cochez la case appropriée :

Si des onglets promoteur (Développeur)vous ne pouvez pas le voir sur le ruban, vous pouvez l'allumer à travers Fichier – Options – Configuration du ruban (Fichier — Options — Personnaliser le ruban). Si dans la fenêtre ci-dessus dans la liste des compléments COM vous n'avez pas Power Pivot, alors il n'est pas inclus dans votre version de Microsoft Office 🙁

Sur l'onglet Power Pivot qui apparaît, il y aura un gros bouton vert clair Gestion (Faire en sorte), en cliquant sur ce qui ouvrira la fenêtre Power Pivot au-dessus d'Excel, où nous verrons le contenu du modèle de données du livre actuel :

Une remarque importante en cours de route : un classeur Excel ne peut contenir qu'un seul modèle de données.

Charger des tables dans le modèle de données

Pour charger des données dans le modèle, nous transformons d'abord la table en un raccourci clavier dynamique "intelligent". Ctrl+T et donnez-lui un nom convivial sur l'onglet Constructeur (Conception). C'est une étape obligatoire.

Ensuite, vous pouvez utiliser l'une des trois méthodes parmi lesquelles choisir :

• Appuyez sur le bouton Ajouter au modèle (Ajouter au modèle de données) languette PowerPivot languette Accueil (Accueil).
  
• Choisir les équipes Insérer – Tableau croisé dynamique (Insérer - Tableau croisé dynamique) et cochez la case Ajouter ces données au modèle de données (Ajouter ces données au modèle de données). Dans ce cas, en fonction des données chargées dans le Modèle, un tableau croisé dynamique est également immédiatement construit.
  
• Dans l'onglet Avancé Données (Date) cliquez sur le bouton À partir du tableau/plage (À partir du tableau/plage)pour charger notre table dans l'éditeur Power Query. Ce chemin est le plus long, mais, si vous le souhaitez, vous pouvez effectuer ici un nettoyage, une modification et toutes sortes de transformations de données supplémentaires, dans lesquelles Power Query est très puissant.

  Ensuite, les données peignées sont téléchargées dans le modèle par la commande Accueil — Fermer et charger — Fermer et charger dans… (Accueil — Fermer&Charger — Fermer&Charger vers…). Dans la fenêtre qui s'ouvre, sélectionnez l'option Créez simplement une connexion (Créer uniquement une connexion) et, surtout, cochez Ajouter ces données au modèle de données (Ajouter ces données au modèle de données).
  

Nous construisons un résumé du modèle de données

Pour créer un modèle de données récapitulatif, vous pouvez utiliser l'une des trois approches suivantes :

• appuie sur le bouton tableau récapitulatif (Tableau croisé dynamique) dans la fenêtre Power Pivot.
• Sélectionner les commandes dans Excel Insérer – Tableau croisé dynamique et passer en mode Utiliser le modèle de données de ce livre (Insérer - Tableau croisé dynamique - Utiliser le modèle de données de ce classeur).
• Choisir les équipes Insérer – Tableau croisé dynamique (Insérer - Tableau croisé dynamique) et cochez la case Ajouter ces données au modèle de données (Ajouter ces données au modèle de données). Le tableau "intelligent" actuel sera chargé dans le modèle et un tableau récapitulatif sera créé pour l'ensemble du modèle.

Maintenant que nous avons compris comment charger des données dans le modèle de données et créer un résumé dessus, explorons les avantages et les avantages que cela nous donne.

Avantage 1 : Relations entre les tables sans utiliser de formules

Un résumé régulier ne peut être construit qu'à partir des données d'une table source. Si vous en avez plusieurs, par exemple, ventes, liste de prix, annuaire des clients, registre des contrats, etc., vous devrez d'abord collecter les données de toutes les tables en une seule à l'aide de fonctions telles que VLOOKUP (VLOOKUP), INDEX (INDICE), PLUS EXPOSÉ (RENCONTRE), ÉTÉ MINCE (SUMIFS) etc. C'est long, fastidieux et pousse votre Excel dans une « pensée » avec une grande quantité de données.

Dans le cas d'un résumé du Modèle de Données, tout est beaucoup plus simple. Il suffit de configurer des relations entre les tables une fois dans la fenêtre Power Pivot - et c'est fait. Pour cela, dans l'onglet PowerPivot appuie sur le bouton Gestion (Faire en sorte) puis dans la fenêtre qui apparaît – le bouton Vue graphique (Vue schématique). Il reste à faire glisser les noms de colonnes (champs) communs (clés) entre les tables pour créer des liens :

Après cela, dans le résumé du modèle de données, vous pouvez jeter dans la zone de résumé (lignes, colonnes, filtres, valeurs) tous les champs de toutes les tables associées - tout sera lié et calculé automatiquement :

Avantage 2 : compter les valeurs uniques

Un tableau croisé dynamique régulier nous donne la possibilité de choisir l'une des nombreuses fonctions de calcul intégrées : somme, moyenne, comptage, minimum, maximum, etc. Dans le résumé du modèle de données, une fonction très utile est ajoutée à cette liste standard pour compter les nombre de valeurs uniques (valeurs non répétitives). Avec son aide, par exemple, vous pouvez facilement compter le nombre d'articles uniques de marchandises (gamme) que nous vendons dans chaque ville.

Clic droit sur le champ – commande Options de champ de valeur et sur l'onglet Opération Selectionnez Nombre d'éléments différents (Compte distinct):

Avantage 3 : Formules DAX personnalisées

Parfois, vous devez effectuer divers calculs supplémentaires dans des tableaux croisés dynamiques. Dans les résumés réguliers, cela se fait à l'aide de champs et d'objets calculés, tandis que le résumé du modèle de données utilise des mesures dans un langage DAX spécial (DAX = Data Analysis Expressions).

Pour créer une mesure, sélectionnez dans l'onglet PowerPivot Command Mesures - Créer une mesure (Mesures — Nouvelle mesure) ou faites simplement un clic droit sur le tableau dans la liste des champs croisés dynamiques et sélectionnez Ajouter une mesure (Ajouter une mesure) dans le menu contextuel :

Dans la fenêtre qui s'ouvre, définissez :

• Nom de la tableoù la mesure créée sera stockée.
• Nom de la mesure – n'importe quel nom que vous comprenez pour le nouveau champ.
• Description - optionnel.
• Laits en poudre – la chose la plus importante, car ici soit nous entrons manuellement, soit nous cliquons sur le bouton fx et sélectionnez une fonction DAX dans la liste, qui devrait calculer le résultat lorsque nous jetons ensuite notre mesure dans la zone Valeurs.
• Dans la partie inférieure de la fenêtre, vous pouvez immédiatement définir le format numérique de la mesure dans la liste Catégories.
  

Le langage DAX n'est pas toujours facile à comprendre car il ne fonctionne pas avec des valeurs individuelles, mais avec des colonnes et des tableaux entiers, c'est-à-dire qu'il nécessite une certaine restructuration de la pensée après les formules classiques d'Excel. Cependant, cela en vaut la peine, car la puissance de ses capacités de traitement de grandes quantités de données est difficile à surestimer.

Avantage 4 : Hiérarchies de champs personnalisées

Souvent, lors de la création de rapports standard, vous devez lancer les mêmes combinaisons de champs dans des tableaux croisés dynamiques dans une séquence donnée, par exemple Année-Trimestre-Mois-Jourou Catégorie-Produitou Pays-Ville-Client etc. Dans le résumé du modèle de données, ce problème est facilement résolu en créant votre propre hiérarchies — ensembles de champs personnalisés.

Dans la fenêtre Power Pivot, passez en mode graphique avec le bouton Vue graphique languette Accueil (Accueil - Vue Diagramme), sélectionnez avec Ctrl les champs souhaités et faites un clic droit dessus. Le menu contextuel contiendra la commande Créer une hiérarchie (Créer une hiérarchie):

La hiérarchie créée peut être renommée et glissée avec la souris dans les champs requis, de sorte que plus tard, en un seul mouvement, ils puissent être jetés dans le résumé :

Avantage 5 : Pochoirs personnalisés

Poursuivant l'idée du paragraphe précédent, dans le résumé du modèle de données, vous pouvez également créer vos propres ensembles d'éléments pour chaque champ. Par exemple, à partir de la liste complète des villes, vous pouvez facilement créer un ensemble de celles qui se trouvent dans votre zone de responsabilité. Ou rassemblez uniquement vos clients, vos marchandises, etc. dans un ensemble spécial.

Pour cela, dans l'onglet Analyse de tableau croisé dynamique dans la liste déroulante Champs, éléments et ensembles il y a des commandes correspondantes (Analyser — Champs, Items & Sets - Créer un ensemble basé sur des éléments de ligne/colonne):

Dans la fenêtre qui s'ouvre, vous pouvez sélectivement supprimer, ajouter ou modifier la position de n'importe quel élément et enregistrer l'ensemble résultant sous un nouveau nom :

Tous les ensembles créés seront affichés dans le panneau Champs de tableau croisé dynamique dans un dossier séparé, d'où ils peuvent être librement glissés vers les zones de lignes et de colonnes de tout nouveau tableau croisé dynamique :

Avantage 6 : Masquer sélectivement les tableaux et les colonnes

Bien que ce soit un petit avantage, mais très agréable dans certains cas. En cliquant avec le bouton droit sur le nom du champ ou sur l'onglet table dans la fenêtre Power Pivot, vous pouvez sélectionner la commande Masquer de la boîte à outils client (Masquer des outils client):

La colonne ou le tableau masqué disparaîtra du volet Liste des champs du tableau croisé dynamique. C'est très pratique si vous devez masquer à l'utilisateur certaines colonnes auxiliaires (par exemple, calculées ou des colonnes avec des valeurs clés pour créer des relations) ou même des tables entières.

Avantage 7. Exploration avancée

Si vous double-cliquez sur n'importe quelle cellule de la zone de valeurs dans un tableau croisé dynamique ordinaire, Excel affiche sur une feuille séparée une copie du fragment de données source impliqué dans le calcul de cette cellule. C'est une chose très pratique, officiellement appelée Drill-down (on dit généralement « échec »).

Dans le résumé du modèle de données, cet outil pratique fonctionne plus subtilement. En vous tenant sur n'importe quelle cellule avec le résultat qui nous intéresse, vous pouvez cliquer sur l'icône avec une loupe qui apparaît à côté (elle s'appelle Tendances Express) puis sélectionnez n'importe quel champ qui vous intéresse dans n'importe quelle table associée :

Après cela, la valeur actuelle (Modèle = Explorateur) ira dans la zone de filtre, et le résumé sera construit par bureaux :

Bien sûr, une telle procédure peut être répétée plusieurs fois, en fouillant systématiquement vos données dans la direction qui vous intéresse.

Avantage 8 : Convertir les fonctions Pivot en Cube

Si vous sélectionnez une cellule dans le résumé du modèle de données, puis sélectionnez dans l'onglet Analyse de tableau croisé dynamique Command Outils OLAP - Convertir en formules (Analyser — Outils OLAP — Convertir en formules), le résumé complet sera automatiquement converti en formules. Désormais, les valeurs de champ dans la zone ligne-colonne et les résultats dans la zone de valeur seront extraits du modèle de données à l'aide des fonctions de cube spéciales : CUBEVALUE et CUBEMEMBER :

Techniquement, cela signifie que maintenant nous n'avons pas affaire à un résumé, mais à plusieurs cellules avec des formules, c'est-à-dire que nous pouvons facilement faire toutes les transformations avec notre rapport qui ne sont pas disponibles dans le résumé, par exemple, insérer de nouvelles lignes ou colonnes au milieu du rapport, effectuez des calculs supplémentaires dans le résumé, organisez-les de la manière souhaitée, etc.

Dans le même temps, la connexion avec les données sources demeure bien sûr et à l'avenir, ces formules seront mises à jour lorsque les sources changeront. La beauté!

• Analyse plan-fait dans un tableau croisé dynamique avec Power Pivot et Power Query
• Tableau croisé dynamique avec en-tête multiligne
• Créer une base de données dans Excel à l'aide de Power Pivot