XL 2013 [Analytique] Comment faire ceci avec Power Query... ou VBA ?

[Leguyl]

Bonjour à tou(te)s

Depuis hier soir, je teste des trucs avec Power Query mais bien que cela semble plus simple de manipuler des données qu'avec VBA, je ne maîtrise pas encore totalement la chose.

J'ai mon tableau principal dans la première feuille, une seconde feuille avec le cumul mensuel (merci aux intervenants dans mon premier poste) et dans une troisième, le résultat par firme sur toute la durée du chantier (d'octobre 2016 jusqu'à mai 2019). J'aimerais avoir des colonnes supplémentaires avec les résultats, toujours par firme mais remontant d'un mois à chaque colonne, jusqu'au début du chantier.

Possible de faire ça ? Et si pas possible avec Power Query, possible avec VBA ?

Merci.

 

[job75]

Bonjour Leguyl,

Solution VBA avec cette macro dans la feuille "Firmes" :

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [C6]) Is Nothing Then Exit Sub
Dim dat, tablo, resu(), d As Object, i&, firme$, n&
dat = [C6]
If Not IsDate(dat) And dat <> "" Then [C6] = ""
tablo = Sheets("Tableau").ListObjects(1).Range.Resize(, 8) 'matrice, plus rapide
ReDim resu(1 To UBound(tablo), 1 To 3)
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
For i = 2 To UBound(tablo)
    If IsDate(tablo(i, 3)) And tablo(i, 3) <= dat Then
        firme = tablo(i, 5)
        If Not d.exists(firme) Then n = n + 1: d(firme) = n: resu(n, 1) = firme
        If IsNumeric(tablo(i, 7)) Then resu(n, 2) = resu(n, 2) + CDbl(tablo(i, 7))
        If IsNumeric(tablo(i, 8)) Then resu(n, 3) = resu(n, 3) + CDbl(tablo(i, 8))
    End If
Next
'---restitution---
With ListObjects(1).Range.Rows(2)
    If n Then
        .Cells(1).Resize(n, 3) = resu
        .Cells(1).Resize(n, 3).Sort .Cells(1), xlAscending, Header:=xlYes 'tri sur les firmes
        .Cells(1, 4).Resize(n) = "=RC[-1]-RC[-2]"
    End If
    .Offset(n).Resize(Rows.Count - n - .Row + 1).EntireRow.Delete 'RAZ en dessous
End With
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub

C'est très voisin de la macro de la feuille "Cumul mensuel" que je vous ai déjà donnée.

A+

 

[Laurent78]

Bonjour,
un truc du genre cf fichier joint (TCD avec source PowerQuery) ?
a+
Laurent

 

[Leguyl]

Bonjour Leguyl,


Solution VBA avec cette macro dans la feuille "Firmes" :

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, [C6]) Is Nothing Then Exit Sub

Dim dat, tablo, resu(), d As Object, i&, firme$, n&

dat = [C6]

If Not IsDate(dat) And dat <> "" Then [C6] = ""

tablo = Sheets("Tableau").ListObjects(1).Range.Resize(, 8) 'matrice, plus rapide

ReDim resu(1 To UBound(tablo), 1 To 3)

Set d = CreateObject("Scripting.Dictionary")

d.CompareMode = vbTextCompare 'la casse est ignorée

For i = 2 To UBound(tablo)

    If IsDate(tablo(i, 3)) And tablo(i, 3) <= dat Then

        firme = tablo(i, 5)

        If Not d.exists(firme) Then n = n + 1: d(firme) = n: resu(n, 1) = firme

        If IsNumeric(tablo(i, 7)) Then resu(n, 2) = resu(n, 2) + CDbl(tablo(i, 7))

        If IsNumeric(tablo(i, 8)) Then resu(n, 3) = resu(n, 3) + CDbl(tablo(i, 8))

    End If

Next

'---restitution---

With ListObjects(1).Range.Rows(2)

    If n Then

        .Cells(1).Resize(n, 3) = resu

        .Cells(1).Resize(n, 3).Sort .Cells(1), xlAscending, Header:=xlYes 'tri sur les firmes

        .Cells(1, 4).Resize(n) = "=RC[-1]-RC[-2]"

    End If

    .Offset(n).Resize(Rows.Count - n - .Row + 1).EntireRow.Delete 'RAZ en dessous

End With

With UsedRange: End With 'actualise la barre de défilement verticale

End Sub

C'est très voisin de la macro de la feuille "Cumul mensuel" que je vous ai déjà donnée.


A+

Bonjour job75,

En effet. J'ai tenté d'arriver à mes fins en partant de votre macro précédente mais sans arriver à quelque chose de correct.

Personnellement, la solution que vous proposez ici me va très bien, on voit le pro du VBA.

Taper une date d'observation ne va pas tuer les utilisateurs finaux.

Une fois de plus, merci beaucoup à vous.

Bonjour,

un truc du genre cf fichier joint (TCD avec source PowerQuery) ?

a+

Laurent

Bonjour Laurent,

J'avais déjà tenté cette approche mais l'on m'a fait remarquer que l'affichage des colonnes supplémentaires se fait non pas dans l'ordre antichronologique (ou calendaire inverse) mais dans l'ordre alphanumérique - alphabétique

Merci quand même de votre intervention

 

[Laurent78]

je n'avais pas fait attention.
Corrigé dans le fichier joint. En fait, il faut conserver les dates dans PowerQuery (en transformant la date en 1er jour du mois).
Ainsi le TCD à bien des dates en colonnes en non plus du texte. Il faut ensuite appliquer la bonne mise en forme personnalisée (aaaa mmmm). Et on peut trier comme on le souhaite les colonnes. Si je ne dis pas de bêtise ...
Cdlt
Laurent

 

[Hasco]

Bonjour,

J'arrive un peu tard, j'ai mis du temps à comprendre comment le faire sous PowerQuery.
voici le résultat obtenu (à vérifier)

Cordialement

Edit: Il y a sûrement moyen de l'améliorer en créant une requête "Différences" qui soit source commune aux requêtes fusionnées. Si cela convient et que j'ai le temps cet après-midi j’essaierai de le faire.

 

[Leguyl]

Merci Laurent et Roblochon, je regarde vos propositions immédiatement.

 

[Hasco]

Re bonjour,

ci joint version 2 remaniée

Bon après-midi au frais

 

[Leguyl]

Merci bien Roblochon. Je regarderai ça demain à tête reposée. Là, je viens de rentrer et je suis cuit...

Bonne soirée à tous.

 

[Leguyl]

Re bonjour,

ci joint version 2 remaniée

Bon après-midi au frais

Bonsoir,

Je viens à peine d'avoir un peu de temps pour tester vos propositions (quelle journée de fou !).

La seconde est parfaite, c'est tout à fait ce qu'il me fallait. Merci à vous et merci aux autres intervenants également. Toutes les solutions proposées me permettent d'analyser comment vous vous y prenez et ainsi m'améliorer moi même... et ce n'est pas rien.

Bonne soirée à tous

 

[Leguyl]

Bonjour à tous,

J'ai parlé trop vite. Ça marchait parfaitement avec les données du premier chantier mais si je réinitialise le tableau principal et y importe les données d'un autre chantier, les éventuelles colonnes "Année Mois" supplémentaires du tableau Power Query "Firmes & Mensuel" ne s'affichent pas automatiquement.

 

[Amilo]

Bonjour à tous, Leguyl,
Voici une proposition dans le dernier onglet "Firmes & Mois",
J'ai utilisé uniquement la requête "Table1" pour arriver au résultat de la requête "Table1(2)"
Cordialement

 

[Leguyl]

Merci Amilo, on dirait bien que ça marche Je regarderai ça plus en détail ce soir.

Bonne fin de journée.