Skip to main content

PowerPivot pour Excel - Table de consultation dans l'entrepôt de données

Power Pivot - Pourquoi faut-il abandonner la fonction RechercheV() ? (Mars 2025)

Power Pivot - Pourquoi faut-il abandonner la fonction RechercheV() ? (Mars 2025)
Anonim

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

TypeCatégorie
NetbooksOrdinateur
Ordinateurs de bureauOrdinateur
MoniteursOrdinateur
Projecteurs et écransOrdinateur
Imprimantes, scanners et faxOrdinateur
Configuration de l'ordinateur et serviceOrdinateur
Accessoires d'ordinateursOrdinateur
Caméras digitalesCaméra
Appareils photo reflex numériquesCaméra
Caméras de cinémaCaméra
CaméscopesCaméra
Accessoires Caméras & CaméscopesCaméra
Téléphones domestiques et professionnelsTéléphone
Téléphones à écran tactileTéléphone
Smartphones et PDATéléphone

Tableau 2

Numéro du moisMonthTextShortMonthTextFullTrimestreSemestre
1JanjanvierQ1H1
2févfévrierQ1H1
3MarMarsQ1H1
4avravrilQ2H1
5PeutPeutQ2H1
6JuinjuinQ2H1
7juiljuilletQ3H2
8AugaoûtQ3H2
9SEPseptembreQ3H2
10octoctobreQ4H2
11novnovembreQ4H2
12décdécembreQ4H2