XL 2016 Filtre de plusieurs dates et moyenne

[vonox]

Bonjour à tous,

Je suis bloqué sur le traitement de données du fichier joint.

Je souhaite avoir le délai moyen entre deux dates pour chaque famille produit (F1, F2, F6). J'ai plusieurs centaines de lignes par famille. La difficulté est que pour une date D0 (initiale) il y a parfois plusieurs dates D2 (finale). Je souhaite ne conserver que la dernière date D2 (la plus récente).

Et ensuite par famille toujours, avoir la moyenne délais entre les 2 dates des centaines voire milliers de lignes.

Pourriez-vous me guider ?

D'avance merci

 

[chris]

Bonjour

Comme cela avec écart calculé entre D0 et D2 max puis D2 max et D3 max ?

Lib Famille	Ecart moyen
Autre	37,79027778
F1	77,76336634
F2	60,86330935
F6	69,30107527

 

[vonox]

Oui ! Quelle a été la méthode ?

Vous avez réussi à retirer les dates D2 en trop ? (1 date D0 peut avoir plusieurs dates D2 et il faut supprimer les dates anciennes pour ne garder que la plus récente D2 pour chaque D0)

 

[chris]

RE

Ci-joint la requête Powerquery dont les étapes sont

• décroisement des colonnes D2 et D3
• typage des colonnes de dates
• recroisement de D2 et D3 sur la base du max de chacune ces 2 dates pour chaque couple Lib Famille, D0
• ajout d'un colonne calculant D2-D0 et d'une autre D3-D2
• suppression de D0, D2 et D3
• décroisement des 2 colonnes calculées
• calcul de la Moyenne de ces nombres de jours par Lib Famille

Plus long à écrire qu'à faire, lol

Si la source évolue, Données, Actualiser Tout

 

[job75]

Bonsoir vonox, chris,

D'après ce que je comprends pour chaque famille il faut 2 résultats : l'écart moyen calculé sur D2 et l'écart moyen calculé sur D3.

Est-ce bien cela ?

A+

 

[vonox]

Bonsoir Job75,

Alors ma problématique est d'avoir le délai moyen (entre D0 et D2 sans D3) pour chaque famille. Du coup 3 résultats au total, 1 par famille.

Ma première difficulté est de nettoyer les D2 (il peut y avoir plusieurs D2 pour une seule D0 et il faut garder la date D2 la plus. récente alors).

Ma deuxième difficulté est d'avoir ensuite le délai moyen global par famille.

Merci Chris, je vais analyser votre fichier et essayer de le comprendre

 

[chris]

RE

Alors ma problématique est d'avoir le délai moyen (entre D0 et D2 sans D3) pour chaque famille. Du coup 3 résultats au total, 1 par famille.

Ajout d'un tableau avec moyenne limitée aux Ecarts D0 avec max de D2 (donc sans D3) et donc requête plus simple

On peut filtrer Autres si inutile

 

[job75]

Du coup 3 résultats au total, 1 par famille.

Je ne comprends rien, soyez plus clair.

 

[job75]

Bonjour vonox, chris, le forum,

Voyez le fichier joint et la macro dans le code de la feuille "Eésultat" :

Private Sub Worksheet_Activate()
Dim d As Object, dd As Object, tablo, resu(), i&, x, y$, z, a, b, s, n&, nn&
Set d = CreateObject("Scripting.Dictionary")
Set dd = CreateObject("Scripting.Dictionary")
tablo = [Tableau1] 'matrice, plus rapide, sur tableau structuré
ReDim resu(1 To UBound(tablo), 1 To 5) 'tableau des résultats à 5 colonnes
For i = 1 To UBound(tablo)
    x = tablo(i, 1)
    If IsDate(x) Then
        x = CDbl(x)
        y = x & " " & tablo(i, 4) 'concatène date et famille
        z = tablo(i, 2)
        If IsDate(z) Then z = CDbl(z): If z > d(y) Then d(y) = z
        z = tablo(i, 3)
        If IsDate(z) Then z = CDbl(z): If z > dd(y) Then dd(y) = z
    End If
Next i
'---sur D2---
If d.Count Then
    a = d.keys: b = d.items
    d.RemoveAll 'RAZ
    For i = 0 To UBound(a)
        s = Split(a(i)) 's(0) la date, s(1) la famille
        If Not d.exists(s(1)) Then
            n = n + 1
            d(s(1)) = n 'mémorise la ligne
            resu(n, 1) = s(1)
        End If
        nn = d(s(1)) 'récupère la ligne
        resu(nn, 2) = resu(nn, 2) + b(i) - Val(s(0)) 'somme des écarts sur D2
        resu(nn, 4) = resu(nn, 4) + 1 'comptage en 4ème colonne
    Next i
End If
'---sur D3---
If dd.Count Then
    a = dd.keys: b = dd.items
    For i = 0 To UBound(a)
        s = Split(a(i)) 's(0) la date, s(1) la famille
        If Not d.exists(s(1)) Then
            n = n + 1
            d(s(1)) = n 'mémorise la ligne
            resu(n, 1) = s(1)
        End If
        nn = d(s(1)) 'récupère la ligne
        resu(nn, 3) = resu(nn, 3) + b(i) - Val(s(0)) 'somme des écarts sur D3
        resu(nn, 5) = resu(nn, 5) + 1 'comptage en 5ème colonne
    Next i
End If
'---moyennes---
If n Then
    For i = 1 To n
        resu(i, 2) = resu(i, 2) / resu(i, 4)
        resu(i, 3) = resu(i, 3) / resu(i, 5)
    Next i
End If
'---restitution---
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A2] '1ère cellule de destination
    If n Then .Resize(n, 3) = resu
    .Offset(n).Resize(Rows.Count - n - .Row + 1, 3).ClearContents 'RAZ en dessous
End With
End Sub

Elle se déclenche automatiquement quand on active la feuille.

Elle est très rapide car elle utilise des tableaux VBA et 2 Dictionary, chez moi 0,11 seconde.

Pour les moyennes du délai D2 les résultats sont les mêmes que ceux de chris.

A+