Si vos Données source ne sont pas dans Excel, la première étape sera de les importer. Excel peut importer de nombreuses sources de Données différentes. Et avec l’introduction de Power Query et Power Pivot dans les versions plus récentes, le nombre de sources qu’Excel peut gérer a considérablement augmenté.

Power Query et Power Pivot

Il y a quelques années, Microsoft a apporté la Business Intelligence (BI) à un public plus large avec une suite d’outils. Bien qu’ils aient évolué au fil du temps, les principaux outils liés à Excel sont Power Query et Power Pivot.

Power Query est un moteur d’extraction, de transformation et de chargement (ETL). Il est intégré à des versions plus modernes d’Excel et il est également intégré à d’autres applications de BI autonomes telles que Power BI de Microsoft. Power Query a remplacé les assistants d’importation hérités du groupe Obtenir des Données externes sous l’onglet Données du ruban. Ces assistants hérités étaient les parties d’extraction et de chargement d’ETL. Il y avait une transformation limitée qui pouvait être effectuée, mais la plupart du temps, ils lisaient des Données externes et les chargeaient sur une feuille de calcul. Power Query a beaucoup plus de capacités de transformation, et j’en discuterai plus tard dans ce chapitre.Des livres entiers ont été écrits sur Power Query, donc je ne ferai qu’effleurer la surface ici.

Power Pivot est un complément pour Excel qui vous permet de créer des modèles de Données plus sophistiqués qu’avec Excel seul. Vous pouvez définir des hiérarchies de Données, écrire des formules plus avancées à l’aide du langage DAX et définir des indicateurs de performance clés pour vos Données, entre autres. Power Pivot dépasse le cadre de ce livre, mais il est souvent associé à Power Query, il est donc bon de comprendre la distinction.

Fichiers texte

Les fichiers texte sont disponibles en deux versions principales: une largeur délimitée et une largeur fixe. Les fichiers délimités ont un caractère spécial, comme une virgule ou une tabulation, qui sépare chaque champ d’une ligne (les lignes sont séparées par des sauts de ligne). Chaque ligne d’un fichier de largeur fixe a le même nombre de caractères. Et chaque champ dans une ligne a le même nombre de caractères que ce champ dans une autre ligne. Lorsque les Données ne remplissent pas un champ, des espaces sont utilisés pour que le champ suivant commence au même endroit pour chaque ligne. La figure 4 montre les mêmes Données formatées en largeur délimitée et fixe.

Figure 4: Données formatées en largeur délimitée et fixe

Chaque format a ses bons et ses mauvais points. Les fichiers à largeur fixe sont plus volumineux pour contenir la même quantité de Données en raison de tous les espaces. Même si vous ne pouvez pas les voir, les caractères non imprimables doivent encore être stockés. L’inconvénient de délimité est que parfois le caractère spécial existe dans les Données, créant une confusion sur ce que sont les Données et ce qu’est un séparateur de champ. Il existe des normes pour traiter le caractère de délimitation dans les Données, mais tous les programmes n’utilisent pas la même norme. Généralement, si vous mettez des guillemets doubles autour des champs avec un caractère de délimitation dans les Données et que vous utilisez deux guillemets lorsque le caractère guillemet double est dans les Données, la plupart des programmes les ouvriront sans problème.

Pour importer un fichier à largeur fixe, procédez comme suit:

1. Créez un classeur vierge dans Excel.
2. Choisissez Données ➪ Obtenir des Données ➪ À partir d’un fichier ➪ À partir de texte / CSV à partir du ruban comme illustré à la figure 2.

Figure 2: La liste déroulante Obtenir les Données sur le ruban

    3.  Accédez au fichier nommé ImportFixed.txt pour l’importer et l’ouvrir.

Excel affiche comment il interprète le fichier. Dans ce cas, Excel signale qu’il utilise un délimiteur personnalisé et qu’il n’y a rien dans la zone de texte du délimiteur. Je ne sais pas pourquoi il ne choisit pas l’option Largeur fixe que vous pouvez voir dans la figure 4, mais je soupçonne que passer un délimiteur de chaîne vide est le code secret pour une largeur fixe.

Figure 3: Les choix du délimiteur d’importation

4. Cliquez sur le bouton Charger pour transférer les Données dans la feuille de calcul. La figure 5 montre le tableau qu’il crée et le volet Office Requêtes et connexions.

Figure 4: Le fichier texte dans un tableau

Les étapes pour ouvrir un fichier délimité sont les mêmes. Nous espérons que Power Query devinera correctement que le délimiteur est une virgule, comme dans la figure 6. Vous pouvez importer des fichiers texte dans Excel en utilisant Fichier ➪ Ouvrir à partir du ruban.

Cela lancera l’assistant d’importation de texte hérité, illustré à la figure 7. Si vous préférez les assistants hérités pour les fichiers texte ou tout autre type de fichier, vous pouvez les rajouter au ruban. Sur le ruban, allez dans Fichier ➪ Options ➪ Données et cochez à côté de tous les assistants d’importation hérités auxquels vous souhaitez avoir accès. La figure 8 montre la boîte de dialogue Options Excel dans laquelle vous pouvez effectuer ces sélections.

Les importations Power Query présentent deux grands avantages par rapport aux méthodes héritées. Tout d’abord, vous avez beaucoup plus d’options pour transformer les Données lors de l’importation. Je discute de la transformation des Données dans Power Query plus loin dans ce chapitre. Le deuxième avantage est qu’il crée une table modifiable dans Excel qui est liée à la source.Avec les méthodes héritées, vous importez les Données et elles sont déconnectées du fichier source. Si vous remplacez le fichier source par un fichier mis à jour, vous devez le réimporter. Avec Power Query, vous actualisez simplement la table et les Données sont mises à jour. Cela rend la mise à jour d’un tableau de bord beaucoup plus simple.

Figure 6: L’écran d’importation pour délimité par des virgules

Figure 7: L’assistant d’importation de texte hérité

Figure 8: Inclure les assistants d’importation hérités sur le ruban option dans Excel

Au bas de l’écran d’importation, vous pouvez cliquer sur le bouton Transformer les Données pour ouvrir l’éditeur Power Query. Encore une fois, je vais discuter de certains principes de base de la transformation plus loin dans ce chapitre. Le bouton Charger est une liste déroulante qui inclut les options Charger et Charger vers. L’option Charger est la valeur par défaut et crée le tableau sur une feuille de calcul. L’option Charger vers affiche la boîte de dialogue Importer les Données, comme illustré à la figure 9.

Figure 9: La boîte de dialogue Importer des Données

La première option consiste à envoyer les Données à une table comme le fait Load. Vous pouvez également envoyer les Données directement à un tableau croisé dynamique ou un graphique croisé dynamique, ce qui est une bonne option si vous avez l’intention de faire pivoter les Données de toute façon. Une autre option consiste à créer la connexion au fichier mais à ne pas importer les Données avant plus tard. Enfin, il existe une case à cocher pour ajouter ces Données au modèle de Données, un sujet dont je discuterai dans la section suivante.

Fichiers Excel

Vous pouvez également importer d’autres fichiers Excel à l’aide de Power Query. Le processus est très similaire à l’importation de fichiers texte. Comme avec les fichiers texte, importer des fichiers Excel au lieu de simplement les ouvrir ou de copier et coller le contenu présente l’avantage de pouvoir remplacer le fichier source et actualiser les Données pour rationaliser les mises à jour. La figure 10 montre une partie d’un fichier Excel avec des Données de transaction de vente.

Figure 10: Données de transaction de vente dans un fichier Excel

Pour importer un fichier Excel, choisissez Données ➪ Obtenir des Données ➪ À partir d’un fichier ➪ À partir d’un classeur à partir du ruban, comme illustré à la figure 11.

Recherchez et sélectionnez le fichier pour afficher l’écran Power Query Navigator illustré à la figure 12. L’écran Navigator affiche les éléments importables dans le fichier. Dans cet exemple, le classeur contient deux feuilles de calcul, un tableau Excel et une plage nommée. Chaque type d’élément importable a une icône distincte afin que vous puissiez dire de quel type il s’agit. Lorsqu’un élément est sélectionné, les Données d’aperçu sont affichées à droite.

Figure 12: Importez un fichier Excel à partir du ruban.

Figure 13: La fenêtre du navigateur Power Query

Power Pivot a correctement reconnu cette relation comme un à plusieurs, ce qui signifie que pour chaque enregistrement dans Vendeurs, il existe de nombreux enregistrements associés dans tblSales.

Maintenant qu’une relation a été établie, vous pouvez créer un tableau croisé dynamique basé sur le modèle de Données. Fermez Power Pivot et revenez à Excel. Sélectionnez Insérer ➪ Tableaux ➪ Tableau croisé dynamique sur le ruban pour afficher la boîte de dialogue Créer un tableau croisé dynamique. Choisissez l’option Utiliser le modèle de Données de ce classeur comme illustré à la figure 19.

Le tableau croisé dynamique résultant, illustré à la figure 20, inclut les deux ensembles de Données dans la liste des champs. Vous pouvez sélectionner des champs dans l’un ou l’autre des tableaux et Excel comprend maintenant la relation entre eux. Par exemple, faites glisser le champ LastName de Vendeurs vers la zone Rows et le champ Amount de tblSales vers la zone Values ​​pour créer un tableau croisé dynamique comme celui illustré à la figure 21.

Figure 19: créer un tableau croisé dynamique basé sur le modèle de Données.

Figure 20: Un tableau croisé dynamique basé sur le modèle de Données affiche tous les ensembles de Données.

Power Pivot est un outil très puissant dans Excel et cette section n’est qu’une brève introduction à ses fonctionnalités. Je discuterai d’autres aspects tout au long du reste de ce chapitre, mais si vous voulez approfondir tout ce que Power Pivot a à offrir, un livre séparé qui lui est consacré est probablement en ordre.

Figure 21: Un tableau croisé dynamique additionnant les ventes par nom de famille du vendeur