Excel 2016 : Analyse de données avec Power Pivot

Contourner les limitations des relations à plusieurs multiples

TESTEZ LINKEDIN LEARNING GRATUITEMENT ET SANS ENGAGEMENT

Tester maintenant Afficher tous les abonnements
Découvrez une technique de création de relation à plusieurs multiples, bien que Power Pivot n'est pas censé les gérer.
08:09

Transcription

Pour clore maintenant la découverte des relations avec Power Pivot, on va traiter un dernier cas qui est un piège courant, où une difficulté plutôt courante que Power Pivot ne gère pas sans bricolage, car en fait, c'est une manipulation qu'on devrait faire dans Power Query. Alors, voyons de quoi il s'agit. Nous avons ici la table des ventes, qu'on commence à bien reconnaître, et on avait également importé une table des chiffres d'affaire, et on souhaiterait dans le tableau croisé dynamique que nous avons construit précédemment, afficher avec les noms des clients, et le chiffre d'affaire que nous avons fait avec eux, les objectifs de chiffre d'affaire qui sont dans la table ici. Alors, allons voir cette table en faisait là, Aller à. On voit qu'on a une colonne « Clients », donc on se dit chouette, on pourra lier ici avec la colonne « Clients », on voit qu'on a une colonne « Mois », et là on se dit : ah, mais parce que c'est les objectifs pour un mois donné, donc on se dit, ah, ben oui, mais bon, là, est-ce qu'on a une colonne « Mois » ? Si on va regarder, ici, oui, on en a une, mais qui est pas écrite de la même manière. Bon, alors là, il commence à avoir une première complication, et on a une colonne « Année », qui elle, devrait pas poser de problème, parce que si on va regarder ici à droite, on a les « Date paiement (année) », qui sont déjà présentes. Alors maintenant, vous allez dire bon, il y a qu'à construire la colonne de « Mois », et ensuite, de créer des relations une par une. Eh bien, non. Parce que comme dans tous les logiciels de bases de données relationnelles, du moins, ceux que je connais, donc je vais pas dire tous, mais un paquet, on ne peut pas créer de relations conjointes multiples, en glissant des relations une par une. Il faut pouvoir glisser les trois champs d'un coup, vers les trois champs cibles, parce que là, ce que l'on a, c'est « Clients », « Mois », « Année », que l'on aimerait glisser vers un potentiel champ qui serait donc là-dedans, et qui s'appelle « Numéro_Client », mois et année aussi. Alors, comme on ne peut pas faire ça, il va falloir tricher. Et toute l'astuce, c'est en fait, de créer une colonne fictive. Alors, allons-y. On va commencer par créer une colonne fictive qui va servir de clé de liaison entre les deux tables, en commençant à créer cette colonne fictive dans la table des chiffres d'affaire. On pourrait tout à fait commencer dans l'autre, ça n'a aucune importance. Donc, je vais faire un clic droit sur la table des objectifs de chiffres d'affaire, Aller à, et puis là, on va créer une nouvelle colonne, qui va s'appeler « fk », qui veut dire « foreign key », qui est une tradition en informatique pour dire clé étrangère. Ça veut dire que c'est une clé qui va servir de liaison, mais qui au fait, est, on va dire la source d'une table maître, enfin peu importe, vous pouvez la nommer comme vous voulez, et puis ici, je vais l'appeler « ClientMoisAnnee ». Alors là, déjà, mon idée, c'est d'associer en créant une clé fictive, le numéro de client, et là, je vais choisir de transformer, je trouve que c'est plus rapide, et également ça optimise Power Pivot de transformer les noms des mois en des chiffres, et également de prendre l'année, et d'en faire une seule sorte de clé d'identifiant. Alors, allons-y, je vais d'abord commencer à transformer les mois en des nombres. Donc, on écrit une formule, qui est =DATEVALUE, qui est une formule classique qui existe aussi dans Excel, et puis là, pour transformer ce mois en un nombre, il faut créer un date fictive, et moi, je prends toujours le premier du mois, qu'on concatène au nom du mois, voilà. Et qu'on concatène à un espace qui, lui-même, est concaténé à l'année correspondante. Ceci, si on le valide, en fait, ça donne simplement une date correspondant au mois et à l'année comme vous pouvez le voir, « Novembre », « 1/11 », etc. Et puis, on va en extraire le mois avec la fonction MONTH, qui veut dire mois, tout simplement, si on traduit de l'Anglais au Français. Donc là, pour le coup, ben, on a les numéros des mois. Maintenant, on veut concaténer ça avec les numéros de clients, donc je me mets au début. Mais l'ordre est arbitraire, j'aurais pu mettre les clients à la fin. Et je vais concaténer ceci avec les années à la fin. Voilà, comme ceci. Et on valide par Entrée, et pour le coup, on a une sorte de code. On peut tout à fait évidemment mettre ceci en nombre entier, ou le garder en tant que texte, peu importe. Donc là, je vais le garder par exemple en tant que nombre entier, passé un petit peu plus optimal en termes de calcul. Maintenant, dans la source principale, on va faire de même. C'est-à-dire on va créer une clé primaire que je vais mettre arbitrairement à la fin, que je vais appeler « pk », qui veut dire « primary key », pour clé primaire en Français. Et on va l'appeler aussi « ClientMoisAnnee ». Et là, l'idée c'est donc, une fois que l'on crée cette colonne, d'aller aussi faire une formule, on va chercher donc les clients, voilà, « Numéro_Client », qu'on concatène évidemment via la fonction MONTH à la colonne, attention, c'est mal exprimé. On extrait le mois de la colonne « Date paiement », qui est donc ici. Ensuite, que l'on concatène idem, à l'année, donc c'est « YEAR », parce que c'est en Anglais, de évidemment, à nouveau, la « Date paiement ». Donc à nouveau, je pourrais prendre ici « Date paiement (année) », « Date paiement (trimestre) », mais je préfère prendre la colonne d'origine, au cas où vous auriez oublié comment on avait créé ces colonnes-là, ou que vous auriez manqué cette étape. On valide par Entrée. Donc pour le coup, on a aussi donc cette colonne avec cette fameuse clé fictive, je vais transformer ça aussi en nombre entier. Maintenant, on retourne dans la vue diagramme, et puis, on va donc associer les clés fictives, c'est-à-dire, là, je vais glisser cette clé primaire sur la clé étrangère correspondante. Voilà, donc il a construit la relation. Maintenant, on peut retourner en vue de données, et je peux rapatrier pour le coup, dans une nouvelle colonne que je vais renommer en mettant ici, « C »... « sng », parce que ce'est un nombre décimal, « sng_CAObjectif », et on va rapatrier donc ici le chiffre d'affaire en faisant un RELATED, et donc, on va aller chercher « PQYChiffreAffaire[CA] », on ferme la parenthèse, on valide par Entrée. On se doute bien que pas tous les clients ont un chiffre d'affaire comme objectif comme vous pouvez le voir. Et d'ailleurs que même pour un client donné, ils n'ont pas des objectifs à tous les mois, donc ce que l'on voit ici est parfaitement correct. Vous me direz, c'est bizarre, on dirait qu'il y a moins de données que dans la table des chiffres d'affaire. C'est pas bizarre, au fait, c'est le cas, il y en a moins. Alors, vous allez me dire pourquoi ? En fait, la raison, elle est simple, c'est que si on retourne dans la table des chiffres d'affaire, c'est que il y a des objectifs pour certains mois où on n'a fait aucune vente avec les clients. C'est pour ça qu'il n'y a pas tous les chiffres qui sont dans la table des ventes. Maintenant, si on retourne dans le tableau croisé dynamique, eh bien, on peut donc aller afficher ce chiffre d'affaire objectif, vous voyez qu'il est maintenant ici. Et je peux donc aller l'ajouter par exemple à la suite des champs de la zone Valeurs, et on se retrouve bien donc ici, avec un chiffre d'affaire objectif, et là par exemple, on peut voir que pour le client « Alembert, Jean le Rond », pour 2003 et le mois d'Août, il y avait par exemple un objectif. Là, par exemple, il y avait un autre objectif pour « Galileo, Galilée », en 2001 Septembre, et ainsi de suite. Donc, on a magnifiquement, sans utiliser Power Query, contourné le fait qu'on ne puisse pas faire des relations multiples, en fusionnant plusieurs colonnes via une clé fictive, et en faisant cela dans les deux tables quand vous voulez à l'origine liées, et donc ensuite, en faisant une liaisons simple, et en utilisant la fonction RELATED. Donc, voilà pour un superbe exemple d'application des relations dans Power Pivot, et pour clore ce sujet sur les notions fondamentales des relations.

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 !