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

Leguyl

XLDnaute Occasionnel
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.
 

Pièces jointes

  • Analytique (Power Query).xlsm
    62.3 KB · Affichages: 29
Dernière édition:

job75

XLDnaute Barbatruc
Bonjour Leguyl,

Solution VBA avec cette macro dans la feuille "Firmes" :
VB:
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+
 

Pièces jointes

  • Analytique VBA(1).xlsm
    72.4 KB · Affichages: 14

Leguyl

XLDnaute Occasionnel
Bonjour Leguyl,


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

VB:
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

XLDnaute Occasionnel
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
 

Pièces jointes

  • Analytique (Power Query).xlsm
    65.3 KB · Affichages: 15

Hasco

XLDnaute Barbatruc
Repose en paix
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.
 

Pièces jointes

  • Analytique (Power Query).xlsm
    64.7 KB · Affichages: 10
Dernière édition:

Leguyl

XLDnaute Occasionnel
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

XLDnaute Occasionnel
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

XLDnaute Accro
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
 

Pièces jointes

  • Analytique (Power Query 3).xlsm
    70.3 KB · Affichages: 17

Statistiques des forums

Discussions
311 720
Messages
2 081 896
Membres
101 833
dernier inscrit
sandra25