Macro traitement extraction avec TCD + Graphique

Bonjour.

Etant débutant en programmation VBA, je me heurte à beaucoup de difficultés pour faire une macro.

OBJECTIF

Le but de la macro est d'avoir des statistiques sur les incidents de paiements avec les différents clients d'une banque d'investissement donnée ainsi que les actions mises en place.

FONCTIONNEMENT SOUHAITE

On effectue une extraction sous forme de tableau sur les incidents de paiements avec un logiciel interne de l’entreprise. On copie cette extraction et on le colle dans la feuille « BASE D EXTRACTION» du classeur. Afin de formater les données et avoir des noms d’étiquettes en français, j’ai déjà bidouillé une macro qui fait ce traitement et qui donne le tableau de la feuille « conso ».

La macro générale doit faire automatiquement la traduction de « BASE D EXTRACTION » vers « CONSO ». Par ailleurs les TCD et graphiques qui seront fabriqués à partir du tableau « CONSO » devront être mis à jour automatiquement.

Vous verrez qu'il y a un champ Business Line dans Conso ou Base d'extraction. Il faut faire attention à Business Line car en cours d'année 2008, le nom de certains Business Line ont changé. Dans les stats, les DEPARTMENT doivent compter les Business Line selon leur ancien nom et selon leur nouveau nom. La macro doit déjà le faire et ces noms sont répertoriés dans la feuille "Paramétres".

SI vous voulez avoir des détails sur comment j'ai fabriqué le tableau Conso, vous pouvez-lire le tableau suivant; sinon vous pouvez directement passer à la suite.

Création de la feuille « Conso » à partir de la « base d’extraction »

A partir de la feuille d’extraction, on a crée les différentes colonnes :

• « Business line » (l’entité concerné)
• Claim date (date de plainte)
• Claim month (date (mois) de plainte)
• Date (pour filtre TCD)
• Année (pour filtre TCD)
• Trimestre (pour filtre TCD)
• Glissant (pour filtre TCD)
• Total impact financier (coût de l’incident)
• Department (le département concerné)
• Closure Date (date de réponse ou résolution de l’incident)
• Incidents dans le mois (pour chaque incident, cette case vaut 1. Ainsi dans le TCD, la somme nous permettra de connaître le nb d’incidents).
• Dont impact financier (incident ayant un impact financier : 1 si l’incident a un impact, 0 sinon)
• Dont incident high : Si la colonne Impact vaut « H » dans la feuille « BASE D EXTRACTION », alors dans la feuille active (« Conso ») la colonne dont incident high vaut 1, 0 sinon
• Delais moyen de resolution : dans la feuille « BASE D EXTRACTION » , colonne U : « Closure period number of day ST » si rien est renseigné, alors la valeur vaut « NA ». Sinon si une valeur est renseigné, il faut prendre en compte cette valeur.
• « Incident non cloturé » Dans la feuille « BASE EXTRACTION », si la colonne « Closure Date » n’est pas renseigné, alors la colonne vaut 0, sinon si la colonne est renseigné, la colonne dans Conso, vaut 1.
• « Gravitée » : l’information est copiée de la colonne « »P » de la « BASE D EXTRACTION »
• « Client » : l’information est copiée de la colonne « AF » de la « BASE D EXTRACTION »
• « Nature » est extraite de la colonne « H » de « BASE D EXTRACTION »
• « Nature de l’action » est extraite de la colonne « Z » de « BASE D EXTRACTION »
• « Chanel » est extraite de la colonne « B » de « BASE D EXTRACTION »

PREMIER BUT

1) Faire un TCD avec comme filtre de rapport : Année, Trimestre, Date, Année Glissante. On doit pouvoir obtenir des rapports selon que l’on choisisse année, trimestre, …

2) En ligne, on doit avoir le mois de dépôt de plainte

3) En colonne on souhaite avoir :

- Incidents dans le mois

o Dont impact financier total en (k€)

- Délais moyen de résolution (j)

Un graphe représentant :

• en abscisse : les dates (selon qu’on choisisse année, trimestre, ….)
• en ordonnée : pour chaque mois, deux histogrammes représentant chaqu’un le nombre d’incidents dans le mois et la somme en k€ + une courbe représentant les délais moyen de résolution (en jours) pour chaque mois

On peut faire soit un TCD ou plusieurs. Mais en choisissant année, trimestre, année ou autre, le graphe doit être mis à jour automatiquement.

Le délai moyen de saisie est obtenu par la différence entre le Date of Input (dans la feuille « BASE D EXTRACTION») et le claim date (dans la feuille « BASE D EXTRACTION »). J'ai essayé de faire une colonne pour ce délai, mais j'obtiens pas le délai convenable.

Premier TCD

J'ai essayé de faire un premier TCD dans la feuille Synthèse, mais j'ai des problèmes au niveau date de résolution et année glissante.De plus j'arrive pas à trier par date croissante.

Voici la première chose que j'aimerais obtenir dans la feuille synthèse en automatique en collant l'extract dans BASE D EXTRACTION:

Deuxième TCD

Faire un TCD, représentant en colonne, la moyenne de délai de saisie, le nombre d’incidents, le délai de saisie le plus long, le délai de saisi le plus court, tout ceci par Département et par mois.

Voici ce que j'aimerais obtenir à partir de l'extract:

Là j'ai même un peu de mal à faire le bon TCD.

RESUME

En résumé, j'aimerais que vous m'aidiez à automatiser la création des TCD et graphique dès que je colle l'extract dans "BASE D EXTRACTION". De plus j'ai aussi besoin d'aide pour créer les bon TCD à partir de la feuille CONSO que j'ai crée.

Après il restera 2,3 bricoles mais si vous pouvez me débloquer sur ces éléments je vous en serais grandement reconnaissant.

Je vous remercie d'avance de votre aide et de votre explication détaillée.

Apparemment j'ai inspiré personne.

Dans ce cas, je me permets de poser une question plus généraliste.

J'ai essayé de créer un TCD en codant en VBA mais cela foncionne pas.

Comment dois-je faire pour que en collant un extract dans la feuille BASE D EXTRACTION (de taille variable ou plage source dynamique), mon TCD soit crée et mis à jour automatiquement ?

Merci.

Bonsoir,

Si j'ai regardé ton fichier. Le TCD à réaliser est bien celui de la feuille synthèse si j'ai bien compris

Mais pourquoi vouloir faire par macro ?

Les données de la feuille Conso sont toujours effacées ou pas ?

A te relire

OUI.

A partir d'un logiciel spécifique, on fait un extract sous forme de tableau qu'on colle dans la feuille BASE D EXTRACTION.

A partir de cette feuille, la feuille CONSO est remplie. Le tableau de la feuille CONSO, c'est un petit formatage du tableau BASE D EXTRACTION.

De la feuille CONSO, le TCD est réalisé dans la feuille SYNTHESE. (On pourrait supprimer la feuille CONSO et faire le TCD directement à partir de la feuille BASE D EXTRACTION mais on m'a dit que 2,3 colonnes sont rajoutés dans CONSO et qui servent de champs dans le TCD)

L'intérêt de la macro, c'est que l'extract est de taille variable: c'est à dire au fur et à mesure que le temps avance, les incidents s'accumulent. Donc on est obligé de resélectionner les champs à chaque fois pour faire un rapport.

De plus la macro sert aussi à faire des calculs de date, par exemple le délai de saisie qui est une soustraction de date en jours ouvres. Par la suite, on désire faire aussi d'autre TCD.

En plus, on souhaite avoir les stats, selon l'année, le trimestre ou sur une période de plusieurs mois données et aussi année glissante. Je vois mal comment faire sans macro.

J'espère t'avoir éclairci les choses...

C'est ça la raison.

Re,

Pour la construction du TCD essaie ce code :

Sub TCD()
'Macro Dan le 09/07/2011 poru Flyemirates
Application.ScreenUpdating = False
Sheets("Synthèse").UsedRange.Delete
Sheets("Conso").UsedRange.Name = "table"
With Sheets("Synthèse")
    .PivotTableWizard SourceType:=xlDatabase, SourceData:="table", TableDestination:="R1C1", TableName:="TCD1"
    With .PivotTables("TCD1")
        .SmallGrid = False
        .AddFields RowFields:="Data", ColumnFields:="Claim Month", PageFields:=Array("Date", "Trimestre", "Année")
        With .PivotFields("Delais moyen de résolution(j)")
        .Orientation = xlDataField
        .Name = " Delais moyen de résolution(j)"
        End With
            With .PivotFields("total impact financier (K€)")
                .Orientation = xlDataField
                .Name = " total impact financier (K€)"
            End With
            With .PivotFields("Incidents dans le mois")
                .Orientation = xlDataField
                .Name = " Incidents dans le mois"
            End With
    End With
End With
Application.CommandBars("PivotTable").Visible = False
End Sub

Pour le graphique, ma version d'excel ne me permet pas d'obtenir le même résultat que toi. Toutefois, je te propose d'utiliser l'enregistreur automatique de macros d'excel et de coller ici le code qui te sera donné.

Je verrai pour le simplifier

A te relire

Merci DAN de m'avoir répondu, c'est sympa de ta part.

Ton code construit bien le TCD, c'est super.

Au niveau du champ délai de résolution, c'est pas celui là qu'il faut prendre. Il faut calculer un champ délai de saisie et pas vraiment délai de résolution, à partir de certaines colonnes de BASE D EXTRACTION: ça je suis en train de le faire même si ça patine un peu. Si je trouve pas la solution, je reviendrais vers toi.

J'ai essayé de comprendre cette macro et j'ai quelques questions:

- Application.ScreenUpdating = False : sur le net j'ai vu que c'est une instruction d'optimisation. Juste avant la fin de la routine, il faut aussi rajouter Application.ScreenUpdating = False ?

- quand je réexécute la macro, j'obtiens l'erreur: Une des formules de cette feuille de calcul contient une ou plusieurs références externes non valides. Quelle serait la raison ?

De plus cela fausse mon graphique:

- Au niveau du champ délai de résolution, c'est pas celui là qu'il faut prendre. Il faut calculer un champ délai de saisie et pas vraiment délai de résolution, à partir de certaines colonnes de BASE D EXTRACTION: ça je suis en train de le faire même si ça patine un peu. Si je trouve pas la solution, je reviendrais vers toi.

Ton code construit bien le TCD, c'est super.

Pour le graphique, voici le code que j'ai obtenu:

Sub Insertion_Graphique()
'
' Insertion_Graphique Macro
' Macro d'insertion de graphique
'

'
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "='Synthèse'!$A$7"
    ActiveChart.SeriesCollection(1).Values = "='Synthèse'!$B$7:$G$7"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "='Synthèse'!$A$8"
    ActiveChart.SeriesCollection(2).Values = "='Synthèse'!$B$8:$G$8"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).Name = "='Synthèse'!$A$9"
    ActiveChart.SeriesCollection(3).Values = "='Synthèse'!$B$9:$G$9"
    ActiveChart.SeriesCollection(3).XValues = "='Synthèse'!$B$6:$G$6"
    ActiveChart.Legend.Select
    ActiveSheet.ChartObjects("Graphique 2").Activate
    ActiveChart.Legend.LegendEntries(1).Select
    ActiveSheet.ChartObjects("Graphique 2").Activate
    ActiveChart.SeriesCollection(1).ChartType = xlLine
End Sub

Voici ce que j'obtiens pour le graphique:

Comme tu peux le constater, j'ai essayé de trier les dates en croissant mais je n'ai pas réussi. Quellle manip faut-il faire?

Par ailleurs il faut que le graphique s'adapte, selon que l'on a 4, 6 ou 10 mois automatiquement (ce qui n'est pas le cas), qu'on soit en année glissant, etc...

J'attends avec impatience ta réponse et te souhaite un bon week-end.

Re,

Application.ScreenUpdating = False

Cela met en suspend le rafraichissement écran et t'évite de visualiser ce qui se passe lors de l'exécution.

Exemple lorsque tu sélectionnes un onglet pour faire des opérations, si tu ne mets pas cette instruction tu verras le scintillement de l'écran à chaque sélection des onglets.

Exemple de test à faire

Sub test()
Sheets(1).select
Sheets(2).select
Sheets(1).select
end sub

Ensuite rajoute Application.ScreenUpdating = False juste après sub test() et relance le code.

Au niveau du champ délai de résolution, c'est pas celui là qu'il faut prendre.

C'est celui dont tu parles dans ton premier message et aussi qui se trouve dans le TCD.

Comme tu peux le constater, j'ai essayé de trier les dates en croissant mais je n'ai pas réussi. Quellle manip faut-il faire

Normal puisque ta colonne "claim month" n'est pas au format de date pour certaines date. Il vaudrait mieux prendre "claim date".

Pour le graphique je vais voir.

Amicalement

Merci pour le screen updating.

En réalité, je souhaite pas avoir délai de résolution, car il n'est pas très pertinent pour l'analyse souhaité.

Au lieu de ça, je souhaite un champ qui s'appelle délai de saisie. On l'obtient à partir des colonnes E ("Claim Date") et AH ("Date of input") de BASE D EXTRACTION. Le but c'est de calculer l'écart en jours ouvrés entre Claim Date et Date of Input qui sont tous les deux en format date.

D'ailleurs j'ai fais un post à part:

https://forum.excel-pratique.com/excel/vba-formule-networkdays-t24476.html

Comme tu peux le constater, j'ai essayé de trier les dates en croissant mais je n'ai pas réussi. Quellle manip faut-il faire

Normal puisque ta colonne "claim month" n'est pas au format de date pour certaines date. Il vaudrait mieux prendre "claim date".

Pour le graphique je vais voir.

Amicalement

Je souhaite avoir les mois en abscisse , trié dans l'ordre et apparemment c'est possible, mais je ne sais pas comment.

Au niveau du champ délai de résolution, c'est pas celui là qu'il faut prendre.

C'est celui dont tu parles dans ton premier message et aussi qui se trouve dans le TCD.

Finalement, j'ai décidé de garder délai de résolution.

Par contre je n'arrive pas à faire une macro pour le graphique, quel que soit le TCD.

Parallèlement, je code aussi pour d'autre TCD, à partir de base d'extraction, et ça avance petit à petit.

re,

Je n'ai pas ta version d'excel mais essaie avec ce code

Sub Graph()
'Macro Dan pour flyemirates
Dim plage As Range
Dim ch As Chart
Application.ScreenUpdating = False
On Error Resume Next
Sheets("Synthèse").ChartObjects(1).Delete
On Error GoTo 0
Sheets("Synthèse").PivotTables("TCD1").PivotSelect "", xlDataAndLabel
Set plage = Selection
    Set ch = Charts.Add
    With ch
        .ChartType = xlColumnClustered
        .SetSourceData Source:=plage, PlotBy:=xlRows
        .Location Where:=xlLocationAsObject, Name:="Synthèse"
    End With
End Sub

Pour appeler le code juste après la macro TCD, mets CALL GRAPH dans la macro TCD juste avant le END SUB

Si ok, n'oublie pas de cloturer le fil en cliquant sur le v Vert.

Amicalement

Merci Dan.

Grâce à ton dernier apport, j'ai pu compléter ma macro (et merci aussi l'enregistreur).

@+ et bonne nuit surtout