XL 2016 Filtre de plusieurs dates et moyenne

vonox

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

Pièces jointes

  • filtres dates.xlsb
    385.6 KB · Affichages: 8

vonox

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

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

Pièces jointes

  • Filtres dates_PQ.xlsb
    393 KB · Affichages: 4
Dernière édition:

vonox

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

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

Pièces jointes

  • Filtres dates_PQ.xlsb
    396.1 KB · Affichages: 5

job75

XLDnaute Barbatruc
Bonjour vonox, chris, le forum,

Voyez le fichier joint et la macro dans le code de la feuille "Eésultat" :
VB:
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+
 

Pièces jointes

  • filtres dates(1).xlsb
    397.9 KB · Affichages: 7

Discussions similaires