XL 2013 Graphiques combinés : comparaison de 2 années sur le même graphique

[denoan]

Bonjour à tous,

Je me lance .

Après avoir fait pas mal de recherches et avoir obtenu un semblant de résultat, je fais appel à votre expérience et expertise concernant la manipulation de données et la génération de graphique pour essayer de trouver une solution plus simple si elle existe.
L'objectif est de faire évoluer le reporting actuel et ainsi pouvoir comparer deux années sur le même graphique : l'année n et l'année n-1.
L'histogramme empilé est un bon choix pour visualiser chaque série de données (données relatives à 4 services différents).
J'ai fait le choix de combiner cela avec des courbes pour bien voir l'évolution du total pour chacune des années.

Dans le fichier joint, onglet OK 2, j'ai pu obtenir visuellement ce que je souhaitais mais la manière de formater les données dans la table pour obtenir ce résultat visuel est assez compliquée à maintenir. Comme il s'agit de reporting mensuel, j'ai 6 graphiques de ce type à mettre à jour.
Mode de fonctionnement actuel pour la mise à jour du reporting :
Extractions brutes Excel intégrées dans une base access.
Je lance des requêtes qui me donnent des résultats que je copie/colle dans mon support ppt (graphique/ clic droit / modifier les données / copie des nouvelles données).
C'est donc facile à maintenir (voir onglet fonctionnement actuel).

Le tableau proposé dans l'onglet OK 2 est bien plus complexe à maintenir avec risque d'erreur. Il y a des cellules/lignes vides, des #N/A à mettre...
Les requêtes access ne me donneront pas des résultat sous ce format. Automatiser la mise en forme des données sera complexe également.
La mise à jour devra se faire manuellement (des tonnes de copier coller en veillant à ne pas se tromper : compliqué et chronophage


Auriez-vous d'autres idées, solutions, une autre manière plus simple de mettre en forme les données pour avoir le même type de résultat?
J'ajoute mon fichier en PJ. L'onglet OK 2 est celui qui se rapproche le plus de ce que je souhaite (niveau graphique).
Mon niveau est plutôt basique en Excel mais ce sujet va me permettre de me perfectionner.

Merci d'avance à tous pour votre aide .
Bon Week end et à bientôt.

 

[chris]

Bonjour

Soit tu modifies la disposition de tes données sinon sur 2013 tu as la possibilité d'installer l'add on POwerQuery (intégré à Excel à partir de la version 2016) pour décroiser tes tableaux sources et les combiner afin de faire un GCD (graphique croisé dynamique)

 

[denoan]

Bonsoir Chris,

Merci beaucoup pour ton retour. Je vais regarder tout ça à tête reposée.
Effectivement je n'avais pas pensé aux graphiques croisés dynamique .

A très vite et encore merci pour tes idées.

 

[denoan]

Bonjour

Soit tu modifies la disposition de tes données sinon sur 2013 tu as la possibilité d'installer l'add on POwerQuery (intégré à Excel à partir de la version 2016) pour décroiser tes tableaux sources et les combiner afin de faire un GCD (graphique croisé dynamique)

Bonjour Chris,

J'ai regardé ce que tu me conseillais, ça correspond tout à fait à au résultat visuel que je souhaite. Il faudrait donc que je demande au service informatique de m'installer l'add-on pour me lancer. Je ne sais pas utiliser Power Query … Tu as pris les deux tableaux de l'onglet Données pour faire les TCD et le GCD?
Une fois l'add on installé (j'espère qu'ils accepteront) j'essaierai de reproduire ce que tu as fait. J'espère y arriver .

La 1ère solution que tu proposes est de modifier la disposition de mes données. J'ai cherché et hormis la solution où je dois laisser des lignes vide et mettre des #N/A, je ne vois pas d'autres façons simples de faire . Crois-tu qu'il y en ait une autre ? Je vais également continuer à chercher du côté de cette pise au cas où le service informatique me refuserait l'installation de l'add-on...

Je vous tiens informés.

 

[chris]

RE

Non tu peux disposer sur 3 colonnes toutes les données et faire les TCD/GCD comme je les ai faits

Mais POwerQuery facilite beaucoup les choses et n'est pas très compliqué à apprendre pour des choses simples

 

[denoan]

RE

Non tu peux disposer sur 3 colonnes toutes les données et faire les TCD/GCD comme je les ai faits

Mais POwerQuery facilite beaucoup les choses et n'est pas très compliqué à apprendre pour des choses simples

Regarde la pièce jointe 1037399

Merci Chris pour ton retour et tes explications.
Sans Power Query, j'ai essayé de faire avec trois colonnes mais j'ai un souci de classement des dates... Dans le TCD, je n'arrive pas à les classer de façon chronologique et à juxtaposer les mois de l'année n à l'année n-1 (peut-être faut-il utiliser les filtres chronologiques que je ne maîtrise pas encore ) du coup le GCD ne correspond pas du tout au résultat que je souhaite (le résultat attendu correspond à celui que tu as généré).

Sinon, on a pu m'installer Power Query, je dois maintenant apprendre à m'en servir . En lançant l'éditeur j'ai pu voir les 2 requêtes qui m'affichent l'équivalent de ce que tu as mis en Screenshot. Si j'ai bien compris elles correspondent aux deux tables de l'onglet données. Y'a plus qu'à s'y mettre pour reproduire ce que tu as fait .
En m'aidant de quelques tutos j'espère y arriver .

Je reviens vers vous pour vous dire si j'ai réussi.

Bonne journée.

 

[chris]

RE

Pour décroiser tu peux regarder le principe ici Ce lien n'existe plus

On renomme d'abord la colonne somme dans chaque table puis chacune des tables est décroisée, on combine ensuite les 2 tables dans la requête An_N-1 par Accueil, Ajouter des requêtes
On renomme enfin la colonne Attribut

C'est très simple...

 

[denoan]

RE

Pour décroiser tu peux regarder le principe ici Ce lien n'existe plus

On renomme d'abord la colonne somme dans chaque table puis chacune des tables est décroisée, on combine ensuite les 2 tables dans la requête An_N-1 par Accueil, Ajouter des requêtes
On renomme enfin la colonne Attribut

C'est très simple...

Bonjour Chris,

Je suis contente car j'ai réussi à reproduire les requêtes sur Power Query puis à les combiner come indiqué dans ta réponse .

Par contre, quand je génère le TCD, et bien, je n'arrive pas à trier les dates pour les mettre comme attendu (comme toi : trier dans un premier temps par mois puis mettre les années).
Dois-je mettre un filtre chronologique? Je vois que dans ton TCD il y a deux conditions qui apparaissent : <01/07/2017 et >02/06/2019...

J'ai vérifié ta requête POQ et je ne pense pas m'être trompée dans la mienne. J'ai les mêmes colonnes.
Je n'arrive pas à comprendre comment tu as pu faire apparaître le champ de TCD Année. Moi je ne le vois pas...

J'ai dû rater quelque chose mais je n'arrive pas à trouver quoi.... Je te remercie d'avance pour tes explications Chris.

Bonne journée à toi et encore merci car j'apprends plein de trucs surtout en essayant de tout refaire depuis le début. C'est super !

 

[chris]

Bonjour

C'est un regroupement de dates dans le TCD.

Place le champ date en étiquette de lignes puis clic droit sur une date, Grouper, Sélectionner Mois et Années.
Intervertir dans les étiquettes de lignes Mois et Années

 

[denoan]

Bonjour

C'est un regroupement de dates dans le TCD.

Place le champ date en étiquette de lignes puis clic droit sur une date, Grouper, Sélectionner Mois et Années.
Intervertir dans les étiquettes de lignes Mois et Années

Merci Chris pour cette réponse rapide.

J'ai réussi à réorganiser les date en faisant comme tu me l'as indiqué puis j'ai remis dans l'ordre les mois manuellement (je ne sais pas s'il y a plus rapide).

J'ai généré le GCD combiné (histo empilé et courbes). Il ne me reste plus qu'à comprendre pourquoi mes courbes somme n'apparaissent pas sur le graphe (la légende apparaît mais les courbes ne sont pas tracées). J'approche du but .

Petite question, en mettant simplement les tables à jour, est-ce que tout se met à jour automatiquement? Requêtes, TCD et GCD?

Encore merci pour ton aide .

 

[denoan]

Entre temps j'ai essayé uns mise à jour des données dans la table de l'année n-1 et après actualisation du TCD c'est bien pris en compte .

 

[chris]

Re

Dans mon TCD les dates sont bien chronologiques sans que j'ai eu à classer...

Pour les courbes, je fais d'abord un histo et ensuite sélectionne le série somme 1 et change, puis idem pour somme 2 (les trucs tout faits marchent rarement)

Par sécurité utiliser Données, Actualiser Tout