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

Leguyl · 18 Juin 2019

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 · 18 Juin 2019

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+

Laurent78 · 18 Juin 2019

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

Leguyl · 18 Juin 2019

job75 à dit:

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.

Laurent78 à dit:
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 · 18 Juin 2019

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 · 18 Juin 2019

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 · 18 Juin 2019

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

Hasco · 18 Juin 2019

Re bonjour,

ci joint version 2 remaniée

Bon après-midi au frais

Leguyl · 18 Juin 2019

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 · 19 Juin 2019

Roblochon à dit:
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 · 20 Juin 2019

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 · 20 Juin 2019

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 · 20 Juin 2019

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

Bonne fin de journée.

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

Leguyl

XLDnaute Occasionnel

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

Laurent78

XLDnaute Occasionnel

Pièces jointes

Leguyl

XLDnaute Occasionnel

Laurent78

XLDnaute Occasionnel

Pièces jointes

Hasco

XLDnaute Barbatruc

Pièces jointes

Leguyl

XLDnaute Occasionnel

Hasco

XLDnaute Barbatruc

Pièces jointes

Leguyl

XLDnaute Occasionnel

Leguyl

XLDnaute Occasionnel

Leguyl

XLDnaute Occasionnel

Amilo

XLDnaute Accro

Pièces jointes

Leguyl

XLDnaute Occasionnel

Discussions similaires