Le 14 septembre 2017, nous avons publié une version actualisée de notre Politique de confidentialité. En utilisant video2brain.com vous vous engagez à respecter ces documents mis à jour. Veuillez donc prendre quelques minutes pour les consulter.

Excel 2016 : Analyse de données avec Power Pivot

Mettre en place des relations supplémentaires

TESTEZ LINKEDIN LEARNING GRATUITEMENT ET SANS ENGAGEMENT

Tester maintenant Afficher tous les abonnements
Apprenez à créer des relations supplémentaires entre tables et à écrire vos premières formules DAX de textes et de relations. Vous verrez également comment éviter certains pièges.
10:19

Transcription

Nous allons maintenant aller un petit peu plus en profondeur dans les relations, et surtout voir comment créer celles-ci avec des tables bien évidemment autres qu'une table de date, et surtout, on va introduire la création de formules DAX, donc rappelez-vous que DAX est le langage des formules de Power Pivot, qui veut dire « Data Analysis Expression ». Et non seulement on va faire des formules DAX, mais des formules DAX qui lient des tables entre elles, afin au fait, de tuer RechercheV, tout simplement. Alors, pour introduire le sujet, on va d'abord retourner dans la Vue de diagramme. Alors, dans la Vue de diagramme, on sait, certes, qu'on a une relation entre cette table système qu'est calendrier, donc son niveau qu'elle se génère en Anglais. Et on a ici la table « SourcePrincipale ». Un collègue qui verrait la démonstration que j'ai fait précédemment, donc imaginez un collègue imaginaire, nous dirait, mais finalement ça sert à rien, parce que ce que t'as fait, les tableaux croisés dynamiques classiques le font tout aussi bien. Alors, ce serait erroné comme argumentation parce que que déjà, Power Pivot peut gérer plus d'un million de lignes très facilement. Et notre collègue, s'il gérait ces tas dans ses feuilles Excel, il serait coincé au-delà de plus d'un million de ventes. Maintenant, par contre, on peut dire à notre collègue, tu sais, je peux faire ce que fais RechercheV aussi, mais simplement, le temps de calcul va être beaucoup plus rapide de RechercheV, et ceux qui connaissent RechercheV avec 200 ou 300 mille lignes, savent qu'il faut souvent attendre une dizaine de minutes pour que les calculs soient mis à jour. Eh bien, vous allez voir qu'avec Power Pivot, c'est pas le cas. Donc là, ce que l'on veut, c'est un tableau croisé dynamique qui va nous montrer les noms des clients et une statistique des ventes de ces clients. Or, les noms des clients sont dans la table « Clients » ici, dans le champ « Contact », et les ventes sont là-dedans. Alors, ce qui lie ces deux tables entre elles, c'est le numéro de client. Donc là, on a une colonne numéro de client qui identifie chaque client de façon unique par un petit numéro très simple, et là, pareil, chaque client dont le nom est dans « Contact », est identifié par une colonne qui contient le numéro de client de façon unique. Et pour faire la liaison entre les deux en théorie, il suffit de glisser un champ sur l'autre, et c'est comme ça qu'on crée des relations manuellement dans Power Pivot. Mais parfois, il arrive ce genre de message. Et là, c'est celui qui a préparé la table des clients qui a fait un mauvais boulot. Il a probablement fait une erreur de saisie humaine. Ça veut dire qu'il y a un des clients qui a un numéro de client à double. Donc là, on va aller vérifier ça. On va réduire Power Pivot, on va aller ouvrir ici dans nos fichiers d'exercices, dans le « Chapitre 04 », et on va ouvrir donc le fichier Excel « SourceAImporterDansSharePoint ». Et effectivement, on a le « Isoz, Vincent », qu'on avait rajouté précédemment qui a le même numéro de client que « Ohm ». Donc là, on va supprimer « Isoz, Vincent », on va enregistrer ce fichier, on va le fermer. On retourne dans Power Pivot, on fait une mise à jour de la table « Clients ». Et maintenant, on va glisser à nouveau « Numéro_Client » sur « Clients ». Sachez qu'on pourrait faire l'opération inverse. En fait, peut importe dans quel ordre on glisse, Power Pivot détecte automatiquement normalement ce qu'il doit faire. Donc là, en glissant, effectivement il crée une relation à nouveau de type 1 à plusieurs, donc souvenez-vous que l'étoile veut dire plusieurs ou multiples, parce que un client a fait plusieurs ventes. Maintenant, si on retourne dans Excel, et que j'enlève par exemple ici les jours, parce que je trouve pas ça joli, eh bien, je dois pouvoir, là-dedans, dans la liste des tables, prendre ici la table des clients, glisser les noms des contacts, par exemple au dessus des années, et là automatiquement, que fait le tableau croisé dynamique ? Il voit que le table des « Clients » est liée à la table des « Ventes », il voit quel client est connecté à quelle vente, et pour le coup, il nous crée ici un résumé parfait, où par chaque client, on sait quel chiffre d'affaire on a eu par année et par mois. Bon, après le fait de réduire les champs tous d'un coup ou un par un, c'est les techniques classiques des tableaux croisés dynamiques. Bon maintenant, ça, c'est bien, mais, il y a encore autre chose que notre collègue imaginaire va nous demander. On va retourner dans Power Pivot, et ici, on va considérer cette fois-ci la table des bonus. Notre collègue nous dit, c'est bien joli, mais dans le tableau croisé dynamique, j'aimerais que tu rapatries les bonus sur les prix totaux, et donc que tu fasses un calcul de remise. À nouveau, ce qu'on faisait à l'époque avec RechercheV, simplement, n'oubliez pas que Power Pivot va toujours pouvoir gérer des millions de lignes et beaucoup plus rapidement que RechercheV. Et en plus, on n'est toujours qu'aux notions de base, parce que on peut aller beaucoup plus loin. Donc, gardez ça en tête, on n'est qu'aux notions de base. Alors, voyons comment procéder. Ici, entre la table des ventes et la table des remises, je me doute que la clé, ça va être à nouveau les numéros de clients, puisqu'on voit bien que ici, il y a un champ qui s'appelle « Kunde/Customer/Client ». Je vais glisser l'un vers l'autre, peu importe le sens, parce que Power Pivot va détecter. Et là donc, on a maintenant vraiment un modèle visuel de base de données relationnelle qui commence, on pourrait dire, à avoir un joli aspect, techniquement parlant. À nouveau, on voit qu'on a une relation un à plusieurs, parce que un client a une remise et donc, il a plusieurs ventes donc, ou achats si vous voulez, ça dépend du point de vue dans lequel on se met. Et donc, la relation a été correctement construite. Petite parenthèse, si je supprimais la relation, sachez qu'il suffit de faire un clic gauche sur la relation, et d'appuyer sur la touche Suppr du clavier. Vous pouvez également double-cliquer sur la relation, après quoi, une boite de dialogue s'ouvre, et là, c'est simplement que vous avez les détails sur la relation, mais il n'y a pas de bouton Suppr. Dans l'onglet Conception, il y a le bouton pour créer une relation avec l'assistant, mais pas le supprimer. Si je fais un clic droit sur la relation, j'ai ici Supprimer, Modifier la relation, donc Modifier la relation, on retrouve la même option que celle que l'on a lorsqu'on double-clique, et il y a une option que l'on étudiera pas dans cette formation, qui s'appelle ici, Marquer comme inactive, qui est pour le cas où on gère plusieurs relations en même temps. Bon bref, revenons à nos moutons. Donc, j'ai lié la table des remises à la table principale, mais maintenant, j'ai besoin de faire une formule pour calculer les prix totaux incluant le bonus. On retombe dans la vue des tables, on va retourner ici dans la table principale, et là, on y va. Déjà, je vais aller contrôler les bonus dans la table des « RemisesClients ». Donc si je vais là, je dis ah, les bonus sont à gauche. Donc visiblement, il y a un problème, c'est-à-dire pas avec le bon typage. Donc là, je prends Type, j'aimerais évidemment un nombre décimal de type pourcentage, donc Nombre décimal, et là, il me dit qu'il ne peut pas, et c'est normal, le symbole « % » le dérange, et vous allez me dire, mais pourquoi il reconnaît pas que c'est du décimal, ben, c'est des point au lieu de virgules. Alors là, ce que l'on va faire, c'est qu'on va créer une nouvelle colonne qu'on va appeler « sng_Bonus », comme ceci. Et puis ici, on va faire l'usage d'une fonction DAX qui est « SUBSTITUTE », qui veut dire substituer, donc pour info, toutes les fonctions DAX sont en Anglais. Et là, je vais dire, tu substitues par rapport à ce qu'il y a dans cette colonne, tu me cherches, donc « ; », l'ancien texte qui est un « . », et tu le remplaces, donc « ; » par un nouveau texte qui sera une « , » puisque les paramètres régionaux de cet ordinateur, ce sont des virgules pour remplacer la partie des décimales des nombres entiers par la partie de ce qui est après la « , ». Donc là, je valide par Entrée, je vois qu'il a fait le remplacement. Ici maintenant, j'essaie de me mettre en nombre décimal, il n'y arrive pas. En fait, c'est parce que le « % » le dérange. Donc je refais ici un substitut en lui disant pour le coup, tu me cherches le « % », et tu le remplaces par rien. Donc, c'est la même syntaxe que dans Excel, simplement que tout est en Anglais. On valide. Et maintenant on est bons. Il suffit simplement de diviser le tout par 100, et de mettre le résultat en « % ». Voilà, déjà là, on a fait du DAX de nettoyage de fonction texte. Avec un cas classique, on s'imagine que « Bonus » était géré avec une entreprise Suisse, les Suisses mettent des points au lieu de virgules par exemple, alors qu'en France, on met des virgules pour séparer donc, la partie des nombres entiers, dizaines, centaines, de la partie décimale fractionnaire. Maintenant, on peut aller dans la « SourcePrincipale », et dans la « SourcePrincipale », on va dans notre colonne, où il est sensé avoir le « PrixTotalAvecRabais ». Donc là, à nouveau, d'abord, on fait une formule simple qui est la « [Quantité] », et comme vous pouvez le voir, je clique sur le « Prix par pièce », « (1- [Rabais%]) », Entrée*. donc pour l'instant, je reste dans la table elle-même, donc il n'y aucun problème, et là, ce qui va devenir intéressant, c'est que là, je vais rapatrier les bonus. Donc, on va faire « *(1- », et maintenant comment va-t-on chercher les bonus ? Ben, l'équivalent de la fonction RechercheV dans Power Pivot, c'est la fonction Related. Donc, on va chercher Related, et là, je vais lui dire « SCVRemisesClients[Bonus] ». On ferme, on valide. Il calcule en correspondance, mais comme vous pouvez le voir, il y a une erreur, c'est parce qu'il faut faire attention à prendre le bon bonus. Maintenant, souvenez-vous en, j'en ai deux. J'ai celui qui a les points, qui est pas bon, et j'ai celui qui a été nettoyé, qui s'appelle « sng_Bonus ». Donc on prend « sng_Bonus », on valide. Maintenant, comme on peut le voir, les calculs sont bons, on peut retourner dans notre tableau croisé dynamique, on peut maintenant aller regarder ici dans la table principale, voilà, qu'on a la colonne qui s'appelle donc « sng_PrixTotalAvecRabais » qui est ici. On peut l'ajouter dans la zone de Valeurs, et voilà, on a maintenant une somme basée sur des calculs de deux tableaux différents.

Excel 2016 : Analyse de données avec Power Pivot

Abordez l'analyse de données avec Excel et Power Pivot. Importez des données de différentes sources, créez des mesures et des indicateurs de performance clés intégrés, etc.

3h27 (33 vidéos)
Aucun commentaire n´est disponible actuellement
 
Logiciel :
Spécial abonnés
Date de parution :8 sept. 2017

Votre formation est disponible en ligne avec option de téléchargement. Bonne nouvelle : vous ne devez pas choisir entre les deux. Dès que vous achetez une formation, vous disposez des deux options de consultation !

Le téléchargement vous permet de consulter la formation hors ligne et offre une interface plus conviviale. Si vous travaillez sur différents ordinateurs ou que vous ne voulez pas regarder la formation en une seule fois, connectez-vous sur cette page pour consulter en ligne les vidéos de la formation. Nous vous souhaitons un excellent apprentissage avec cette formation vidéo.

N'hésitez pas à nous contacter si vous avez des questions !