L'une des choses que je mentionne le plus à propos de PowerPivot pour Excel est la possibilité d'ajouter des tables de recherche à vos ensembles de données. La plupart du temps, les données avec lesquelles vous travaillez ne comportent pas tous les champs nécessaires à votre analyse. Par exemple, vous pouvez avoir un champ de date mais vous devez regrouper vos données par trimestre. Vous pouvez écrire une formule, mais il est plus facile de créer une table de recherche simple dans l'environnement PowerPivot.
Vous pouvez également utiliser cette table de recherche pour un autre groupe, tel que le nom du mois et le premier / deuxième semestre. En termes d’entreposage de données, vous créez en fait une table de dimension de date. Dans cet article, je vais vous donner quelques exemples de tables de dimensions pour améliorer votre projet PowerPivot pour Excel.
Nouvelle table de dimension de texte (Lookup)
Considérons une table avec les données de commande. Supposons que la table comporte des champs pour le client, la date de la commande, le total de la commande et le type de commande. Nous allons nous concentrer sur le champ Type de commande. Supposons que le champ du type d'ordre comporte des valeurs telles que:
- Netbooks
- Ordinateurs de bureau
- Moniteurs
- Projecteurs
- Des imprimantes
- Des scanners
- Caméras digitales
- Appareils photo reflex numériques
- Caméras de cinéma
- Caméscopes
- Téléphones de bureau
- Téléphones intelligents
- PDA
- Accessoires Téléphones Portables
En réalité, vous auriez des codes pour ceux-ci, mais pour que cet exemple reste simple, supposons qu'il s'agisse des valeurs réelles dans le tableau des commandes. Avec PowerPivot pour Excel, vous pourrez facilement regrouper vos commandes par type de commande.
Et si vous vouliez un groupe différent? Par exemple, supposons que vous ayez besoin d’une catégorie de groupe, telle que ordinateurs, appareils photo et téléphones. La table d'ordre n'a pas de champ de catégorie, mais vous pouvez facilement la créer en tant que table de recherche dans PowerPivot pour Excel. L’échantillon complet de la table de recherche est ci-dessous dans Tableau 1 .
Voici les étapes:
- Étape 1: Vous avez besoin d'une liste distincte du champ type pour votre table de recherche. Ce sera votre champ de recherche. À partir de votre ensemble de données, créez une liste distincte de valeurs à partir du champ Type de commande. Entrez la liste distincte des types dans un classeur Excel. Label la colonne Type .
- Étape 2: Dans la colonne à côté de votre colonne de recherche (Type), ajoutez le nouveau champ sur lequel vous souhaitez grouper. Dans notre exemple, ajoutez une colonne avec une étiquette appelée Catégorie .
- Étape 3: Pour chaque valeur de votre liste de valeurs distincte (types dans cet exemple), ajoutez les valeurs correspondantes. Catégorie valeurs. Dans notre exemple simple, entrez soit Des ordinateurs , Appareils photo ou Téléphones dans le Catégorie colonne.
- Étape 4: Copier le Type et Catégorie table de données dans votre presse-papiers.
- Étape 5: Ouvrez le classeur Excel avec les données de commande dans PowerPivot pour Excel. Lancez la fenêtre PowerPivot. Cliquez sur Coller, ce qui amènera votre nouvelle table de recherche. Donnez un nom à la table et assurez-vous de vérifier Utilisez la première ligne comme en-tête de colonne. Cliquez sur D'accord. Vous avez créé une table de consultation dans PowerPivot.
- Étape 6: Créer une relation entre le Type domaine dans le Ordre table et le Catégorie champ dans la table de recherche. Clique sur le Conception ruban et choisissez Créer une relation. Faites les sélections dans le Créer une relation dialogue et cliquez Créer.
Lorsque vous créez un tableau croisé dynamique dans Excel à partir des données PowerPivot, vous pourrez regrouper vos nouveaux Catégorie champ. N'oubliez pas que PowerPivot pour Excel ne prend en charge que les jointures internes. Si vous avez un type d'ordre manquant dans votre table de recherche, tous les enregistrements correspondants pour ce type seront absents de tout tableau croisé dynamique basé sur les données PowerPivot. Vous devrez vérifier cela de temps en temps.
Date Dimension (Lookup) Table
La table de consultation Date sera probablement nécessaire dans la plupart de vos projets PowerPivot pour Excel. La plupart des ensembles de données ont un type de champ de date. Il existe des fonctions pour calculer l'année et le mois.
Toutefois, si vous avez besoin du texte du mois ou du trimestre, vous devez écrire une formule complexe. Il est beaucoup plus facile d'inclure une table de dimension Date (référence) et de la faire correspondre au numéro du mois dans votre jeu de données principal. Vous devrez ajouter une colonne à votre table de commandes pour représenter le numéro du mois à partir du champ Date de la commande. La formule DAX pour mois dans notre exemple est = MOIS (Date de commande). Cela retournera un nombre entre 1 et 12 pour chaque enregistrement. Notre table de dimension fournira des valeurs alternatives, qui renvoient au numéro du mois. Cela vous apportera de la souplesse dans votre analyse. Le tableau de dimensions de date de l'échantillon complet est présenté ci-dessous Tableau 2 .
La dimension de date ou la table de consultation comprendra 12 enregistrements. La colonne mois aura les valeurs 1 - 12. Les autres colonnes incluront le texte mensuel abrégé, le texte mensuel complet, le trimestre, etc. Voici les étapes:
- Étape 1: Copiez le tableau du tableau 2 ci-dessous et collez-le dans PowerPivot. Vous pouvez créer cette table dans Excel mais je vous fais gagner du temps. Vous devriez pouvoir coller directement à partir des données sélectionnées ci-dessous si vous utilisez Internet Explorer ou Edge. PowerPivot prend la mise en forme du tableau lors de mes tests. Si vous utilisez un autre navigateur, vous devrez peut-être d'abord coller dans Excel, puis le copier à partir d'Excel pour sélectionner le format du tableau.
- Étape 2: Ouvrez le classeur Excel avec les données de commande dans PowerPivot pour Excel. Lancez la fenêtre PowerPivot. Cliquez sur Coller ce qui amènera votre table de correspondance copiée à partir du tableau ci-dessous ou d'Excel. Donnez un nom à la table et assurez-vous de vérifier Utilisez la première ligne comme en-tête de colonne. Cliquez sur D'accord. Vous avez créé une table de consultation de date dans PowerPivot.
- Étape 3: Créer une relation entre le Mois domaine dans le Ordre table et le Numéro du mois champ dans la table de recherche. Clique sur le Conception ruban et choisissez Créer une relation. Faites les sélections dans le Créer une relation dialogue et cliquez Créer.
De nouveau, avec l'ajout d'une dimension de date, vous pourrez grouper les données de votre tableau croisé dynamique en utilisant l'une des valeurs différentes de la table de recherche de date. Le regroupement par trimestre ou le nom du mois sera un jeu d'enfant.
Exemples de tables de dimension (Lookup)
Tableau 1
| Type | Catégorie |
| Netbooks | Ordinateur |
| Ordinateurs de bureau | Ordinateur |
| Moniteurs | Ordinateur |
| Projecteurs et écrans | Ordinateur |
| Imprimantes, scanners et fax | Ordinateur |
| Configuration de l'ordinateur et service | Ordinateur |
| Accessoires d'ordinateurs | Ordinateur |
| Caméras digitales | Caméra |
| Appareils photo reflex numériques | Caméra |
| Caméras de cinéma | Caméra |
| Caméscopes | Caméra |
| Accessoires Caméras & Caméscopes | Caméra |
| Téléphones domestiques et professionnels | Téléphone |
| Téléphones à écran tactile | Téléphone |
| Smartphones et PDA | Téléphone |
Tableau 2
| Numéro du mois | MonthTextShort | MonthTextFull | Trimestre | Semestre |
| 1 | Jan | janvier | Q1 | H1 |
| 2 | fév | février | Q1 | H1 |
| 3 | Mar | Mars | Q1 | H1 |
| 4 | avr | avril | Q2 | H1 |
| 5 | Peut | Peut | Q2 | H1 |
| 6 | Juin | juin | Q2 | H1 |
| 7 | juil | juillet | Q3 | H2 |
| 8 | Aug | août | Q3 | H2 |
| 9 | SEP | septembre | Q3 | H2 |
| 10 | oct | octobre | Q4 | H2 |
| 11 | nov | novembre | Q4 | H2 |
| 12 | déc | décembre | Q4 | H2 |
