Power Pivot: Traiter de grandes quantités de données dans Excel
Contenu
EN BREF
Power Pivot permet de dépasser la limite d'environ 1 million de lignes d'Excel en gérant des volumes de données bien supérieurs, comme un tableau de 1,1 million de lignes, directement dans le modèle de données intégré. Cet outil établit des relations entre plusieurs tableaux sans recourir à des formules complexes, garantissant ainsi des analyses rapides et stables même avec des données massives.
À retenir:
- Power Pivot gère des millions de lignes, dépassant la limite standard d'Excel.
- Il permet de relier plusieurs tableaux via un modèle de données relationnel.
- Les performances des calculs et tableaux croisés dynamiques restent stables.
- L'outil est disponible dans Office Professional Plus et Excel 365 ProPlus.
- L'activation nécessite une vérification dans les options des modules complémentaires COM.
Frédéric Jordan
Frédéric Jordan est exp. dipl. en gestion des organisations et il travaille dans le bureau de conseil en organisation et en management Jordan Consulting.
Aides de travail appropriées
Comment utiliser Power Pivot pour traiter des millions de lignes dans Excel ?
L’outil Excel Power Pivot
On disait donc souvent autrefois qu'Excel n'était pas adapté aux grandes quantités de données. Tel n’est plus le cas: avec l'outil Excel Power Pivot, vous pouvez en effet traiter rapidement d'énormes quantités de données et les résumer dans un tableau croisé dynamique ou un graphique. Mais ce n'est pas tout: si vous travaillez avec plusieurs tableaux, le modèle de données intégré vous permet de les relier entre eux par des relations. Vous pouvez ainsi les évaluer ensemble sans devoir recourir à des formules complexes comme REFERENCE.
Disponibilité et activation de Power Pivot
Power Pivot ne se retrouve malheureusement pas dans toutes les versions d'Excel. La première version compatible est Excel 2010. Si vous utilisez encore cette version, il vous suffit de télécharger et d'installer l'add-in du même nom.
Pour les versions plus récentes d'Excel, il n'est certes pas nécessaire de télécharger un add-in séparé, mais l'inclusion ou non de Power Pivot dans son intégralité dépend de la licence. Les utilisateurs d'Office Professional Plus, d'Office 365 ProPlus ou d'une licence Excel individuelle peuvent utiliser toutes les fonctionnalités de Power Pivot. Pour les autres licences, il se peut que vous puissiez accéder au modèle de données Power Pivot, mais que vous ne puissiez pas le modifier vous-même dans la fenêtre éponyme.
Si Power Pivot est disponible dans votre version d'Excel, vous devez d'abord l'activer dans les options. Pour ce faire, procédez comme suit:
Etape par étape: Activation de l'onglet Power Pivot
- Ouvrez les options Excel via Fichier → Options et passez à la rubrique Add-Ins.
- Sélectionnez l’entrée COM-Add-Ins sous la fenêtre à côté de Gérer et cliquez sur Aller.
- Une boîte de dialogue apparaît, dans laquelle devrait figurer l’entrée Microsoft Power Pivot for Excel (le libellé exact peut légèrement varier selon la version). Cochez la case correspondante et confirmez en cliquant sur OK.
Créer le modèle de donnée
Toutes les données gérées par Power Pivot font partie de ce que l'on appelle le modèle de données. Il se compose d'un ou de plusieurs tableaux qui peuvent être en relation les uns avec les autres. Il peut également contenir des évaluations telles que des champs calculés et des indicateurs de performance clés.
Si vous avez activé Power Pivot avec succès, vous pouvez consulter et modifier le modèle de données en cliquant sur le bouton Gérer de l'onglet Power Pivot. Vous pouvez également utiliser le bouton Ajouter au modèle de données pour insérer un tableau du classeur Excel directement dans le modèle de données.
Recommandations de produits
Etape par étape: Lire un tableau
L'exemple suivant montre comment créer un modèle de données à partir de 2 tableaux et le préparer pour un tableau croisé dynamique.
- Placez le pointeur de cellule dans le tableau et cliquez sur le bouton Ajouter au modèle de données dans l'onglet Power Pivot.
- Placez le pointeur de cellule dans le tableau et cliquez sur le bouton Ajouter au modèle de données dans l’onglet Power Pivot.
- La fenêtre Power-Pivot s’ouvre. Passez à cette fenêtre si elle ne passe pas automatiquement au premier plan.
Power Pivot offre la possibilité d'importer un grand nombre de sources de données. Il s'agit notamment de la plupart des bases de données et systèmes cloud courants, mais aussi évidemment de simples fichiers Excel et de texte.
Dans notre exemple, un tableau de commandes doit être importé en plus du tableau de produits. Il contient les postes de toutes les factures des 9 dernières années et comporte près de 1,1 million de lignes. Un tableau Excel ne pourrait pas contenir une telle quantité de données, mais ce n'est pas un problème pour Power Pivot. Pour importer les postes de facture dans le tableau des produits, procédez de la manière suivante:
Etape par étape: Lire des données externes
- Dans l’onglet Accueil de la fenêtre Power Pivot, cliquez sur Récupérer des données externes → A partir d’autres sources.
- Dans l’Assistant d’importation de tableaux, sélectionnez la source Fichier texte et cliquez sur Suivant.
- A l’aide du bouton Parcourir, sélectionnez le fichier Commandes.txt.
- Définissez le point virgule comme séparateur de colonne.
- Activez l’option Utiliser la première ligne comme en-tête de colonne.
- Vous avez maintenant la possibilité de limiter la quantité de données lues en utilisant des filtres ou en désactivant certaines colonnes. Dans ce cas, nous avons toutefois besoin de toutes les données disponibles. Cliquez donc simplement sur Terminer.
Checklist pratique
- Vérifier que la licence Excel (ProPlus ou 365) inclut Power Pivot.
- Activer le module complémentaire COM dans les options d'Excel.
- Ouvrir l'onglet Power Pivot pour accéder à la fenêtre de gestion.
- Importer les premiers tableaux dans le modèle de données via le bouton dédié.
- Ajouter des données externes (bases de données, fichiers texte) via l'assistant d'importation.
- Définir les séparateurs de colonnes et les en-têtes lors de l'importation.
- Créer des relations entre les différentes tables du modèle.
- Utiliser des champs calculés ou des indicateurs de performance clés si nécessaire.
- Générer un tableau croisé dynamique basé sur le modèle de données.
- Valider la rapidité des calculs avec des volumes de données importants.
Conclusion au sujet de Power Pivot
Les fonctions de Power Pivot permettent de classer et de relier entre elles de grandes quantités de données. Des tableaux qui sont normalement séparés peuvent être reliés entre eux. Excel comprend alors comment les données s'articulent entre elles. Power Pivot traite en outre beaucoup plus de données que les tableaux Excel normaux. Les analyses restent rapides et stables. Power Pivot fait ainsi d'Excel un outil qui structure clairement de grandes quantités de données et fournit des indicateurs fiables.
FAQ: Power Pivot
Quelle est la différence entre Excel standard et Power Pivot ?
Excel standard est limité à environ 1 million de lignes et peut devenir lent avec des calculs complexes. Power Pivot utilise un moteur de données en mémoire qui gère des millions de lignes, permet de créer des relations entre tableaux et assure des performances rapides.
Power Pivot est-il disponible dans toutes les versions d'Excel ?
Non. Power Pivot nécessite au minimum Excel 2010 avec un add-in, ou des versions plus récentes comme Office Professional Plus ou Excel 365 ProPlus. Certaines licences d'entreprise peuvent limiter l'accès aux fonctionnalités de modification.
Comment importer des données externes dans Power Pivot ?
Depuis l'onglet Accueil de la fenêtre Power Pivot, cliquez sur « Récupérer des données externes » puis choisissez la source (fichier texte, base de données, etc.). Suivez l'assistant pour configurer les séparateurs et les filtres avant de terminer l'importation.
Peut-on utiliser Power Pivot sans connaître les formules complexes ?
Oui, Power Pivot permet de relier des tableaux entre eux via un modèle de données, éliminant le besoin de formules complexes comme RECHERCHEV pour croiser les informations. Les relations sont gérées visuellement.
