Comment Créer des colonnes calculées dans Power Pivot dans Excel - Guide Pratique

Apprenez à créer des colonnes calculées dans Power Pivot pour étendre votre modèle de données avec des formules personnalisées en DAX. Cette technique avancée permet des calculs dynamiques sur des colonnes entières sans saisie manuelle, améliorant la précision des rapports et l'efficacité du modèle.

Pourquoi c'est important

Les colonnes calculées rationalisent la logique métier complexe dans les modèles de données, éliminant les formules répétitives et améliorant les performances sur les grands ensembles de données. Cette compétence est essentielle pour construire des solutions BI professionnelles.

Prérequis

•Compréhension fondamentale des formules et fonctions Excel
•Power Pivot activé dans Excel (Données > Obtenir et transformer > À partir d'autres sources > À partir de Power Pivot)
•Familiarité avec la syntaxe DAX (Data Analysis Expressions)
•Données déjà chargées dans une table Power Pivot

Instructions étape par étape

Ouvrir la fenêtre Power Pivot

Accédez à l'onglet Données > Groupe Outils de données > Gérer le modèle de données (ou Power Pivot > Gérer). Cela ouvre l'éditeur Power Pivot avec vos tables de données.

Sélectionner la table cible

Cliquez sur la table où vous souhaitez ajouter la colonne calculée dans la fenêtre Power Pivot. Assurez-vous de voir clairement toutes les colonnes existantes.

Ajouter une nouvelle colonne

Cliquez avec le bouton droit sur la zone d'en-tête de colonne ou faites défiler vers la droite jusqu'à la première colonne vide. Cliquez sur 'Ajouter une colonne'.

Entrer la formule DAX

Tapez votre formule DAX dans la barre de formule en haut (par exemple, =[Ventes]*[Quantité]). Appuyez sur Entrée ; Power Pivot applique automatiquement la formule à toutes les lignes.

Renommer et formater la colonne

Cliquez avec le bouton droit sur l'en-tête de colonne > Renommer pour lui donner un nom significatif. Définissez le type de données si nécessaire, puis enregistrez votre modèle.

Méthodes alternatives

Utiliser la barre de formule en vue grille Power Pivot

Cliquez directement sur une cellule dans la colonne vide et tapez la formule DAX sans clic droit. C'est plus rapide pour les utilisateurs expérimentés en DAX.

Créer des mesures au lieu de colonnes calculées

Pour les valeurs agrégées, utilisez des Mesures (Données > Gérer > Design > Nouvelle mesure) plutôt que des colonnes calculées pour améliorer les performances.

Astuces et conseils

✓Utilisez des noms de colonnes significatifs sans espaces ; si nécessaire, mettez le nom entre crochets : [Nom de colonne].
✓Référencez les colonnes d'autres tables avec la syntaxe [NomTable].[NomColonne] pour les calculs entre tables.
✓Les colonnes calculées sont calculées au moment de l'actualisation ; évitez-les pour les données qui changent fréquemment.
✓Utilisez le menu Format (Design > Format) pour définir les types de données et les formats numériques appropriés.
✓Testez d'abord votre formule DAX sur un petit ensemble de données avant de l'appliquer à de grandes tables.

Astuces avancées

★Utilisez IF() et des conditions imbriquées pour les calculs conditionnels : =[Montant]*IF([Statut]="Payé",1,0) pour marquer efficacement les enregistrements.
★Exploitez la fonction RELATED() pour extraire des valeurs d'autres tables : =RELATED([TableVentes].[Région]) pour les recherches dimensionnelles.
★Créez des colonnes d'assistance pour les calculs intermédiaires afin d'améliorer la lisibilité des formules dans les modèles complexes.
★Surveillez l'impact sur la taille du fichier ; les colonnes calculées augmentent la taille du modèle.

Résolution de problèmes

La formule retourne une erreur #NAME? ou #ERROR

Vérifiez que tous les noms de colonnes et de tables sont correctement orthographiés et entre crochets. Vérifiez la syntaxe des fonctions DAX.

La colonne calculée apparaît vide ou affiche la même valeur pour toutes les lignes

Assurez-vous que la formule référence les bonnes colonnes et ne contient pas d'instructions IF sans valeur else. Recalculez le modèle : Ctrl+Shift+F9.

Les performances se dégradent considérablement après l'ajout d'une colonne calculée

Convertissez-la en Mesure si une agrégation est nécessaire. Supprimez les colonnes calculées inutiles et consolidez les formules.

Impossible de voir la nouvelle colonne calculée dans les tableaux croisés ou les rapports

Actualisez l'intégralité du classeur (Données > Actualiser tout) et assurez-vous que la colonne n'est pas masquée dans la liste des champs.

Formules Excel associées

Questions fréquentes

Quelle est la différence entre une colonne calculée et une mesure dans Power Pivot ?

Les colonnes calculées stockent les valeurs calculées pour chaque ligne, augmentant la taille du fichier mais permettant le filtrage au niveau des lignes. Les mesures agrègent les valeurs dynamiquement, consommant moins de mémoire. Utilisez les colonnes pour les attributs dimensionnels et les mesures pour les faits.

Puis-je utiliser des fonctions Excel comme VLOOKUP ou INDEX/MATCH dans les colonnes calculées Power Pivot ?

Non, Power Pivot utilise exclusivement DAX. Utilisez les fonctions RELATED() ou LOOKUPVALUE() DAX à la place pour les recherches entre tables.

Comment modifier ou supprimer une colonne calculée après l'avoir créée ?

Cliquez avec le bouton droit sur l'en-tête de colonne dans Power Pivot > Modifier la colonne pour modifier la formule. Pour supprimer, cliquez avec le bouton droit > Supprimer la colonne.

Pourquoi ma colonne calculée affiche-t-elle des valeurs différentes de ma formule Excel ?

Les fonctions DAX se comportent différemment des fonctions Excel en raison de l'évaluation du contexte. Vérifiez que vous utilisez des fonctions compatibles DAX.

Une colonne calculée peut-elle référencer d'autres colonnes calculées de la même table ?

Oui, mais évitez les références circulaires. Les colonnes calculées sont évaluées dans l'ordre de définition, donc référencez uniquement les colonnes définies avant.