XL 2019 Indiquer l'écart en mois parmi une plage de lignes (25 000 lignes)

[tite schtroumpfette]

Bonjour,
Je traite un fichier de 25 000 lignes.
J'ai besoin de savoir sur combien de sites il y a eu plus de 1 mois de facturation.
Sur le fichier joint, les sites sont colonne H, la date de la facture colonne R.
Y-a-t-il un moyen simple de faire cela dans un TCD ?

Sur l'exemple ci-dessous, site 1 : non compté, sites 2 et 3 comptés. Résultat : 2. Je vous joins le classeur.

Nom du site	I	J	K	L	M	N	O	P	Q	Date doc.
1​										06/05/2019
1​										06/05/2019
1​										06/05/2019
1​										06/05/2019
1​										06/05/2019
2​										24/07/2019
2​										30/09/2019
2​										31/10/2019
2​										30/09/2019
2​										31/10/2019
2​										31/07/2019
3​										29/11/2018
3​										19/12/2018
3​										24/12/2018
3​										31/01/2019
3​										25/02/2019
3​										01/03/2019
3​										11/03/2019
3​										14/03/2019

 

[JHA]

Bonjour à tous,

Un début de piste avec Power Query

JHA

 

[tite schtroumpfette]

Bonjour, je ne connais pas du tout Power Query. Pouvez-vous m'expliquer rapidement comment vous avez fait ce miracle ?
Je viens de découvrir le bouton "obtenir des données"

 

[JHA]

Bonjour à tous,

Tu mets les données en mode tableau structuré (ruban: insertion/tableau)
ensuite tu vas dans donnée/A partir de tableau ou d'une plage et power query va s'ouvrir.

Autrement, dans le fichier ci-joint, tu te mets dans le tableau vert de l'onglet "Nbre de mois" puis dans le ruban tu sélectionnes "requête" puis "modifier"
Sur la droite, tu as toutes les requêtes pour aboutir au résultat.

JHA

 

[tite schtroumpfette]

Bonjour à tous,

Tu mets les données en mode tableau structuré (ruban: insertion/tableau)
ensuite tu vas dans donnée/A partir de tableau ou d'une plage et power query va s'ouvrir.

Autrement, dans le fichier ci-joint, tu te mets dans le tableau vert de l'onglet "Nbre de mois" puis dans le ruban tu sélectionnes "requête" puis "modifier"
Sur la droite, tu as toutes les requêtes pour aboutir au résultat.

JHA

Bonjour,
Je suis désolée, je ne comprend pas ce que je dois faire une fois que j'ai affiché l'éditeur Power Qwery. Je ne sais pas sur quels menus appuyer pour configurer ce que tu as fait.
Saurais-tu me donner un pas à pas ?

J'ai essayé de remplacer les colonnes de ton fichier par celles d'origine du mien (en collant juste le texte) et ça ne marche pas, ça indique qu'il y a un problème de format de date "DataFormat.Error : Désolé... Nous ne pouvons pas procéder à la conversion en un nombre."

 

[job75]

Bonjour tite schtroumpfette, JHA, le forum,

@JHA si en Essai!R6 on met 06/05/2020 le nombre de mois du site 1 reste à 1 après actualisation.

A+

 

[JHA]

Bonjour à tous,
Bonjour Job75,

Un essai avec une colonne supplémentaire et un TCD.

JHA

 

[JHA]

Bonjour à tous,
@job75

Dans le TCD, j'ai le même cas, il reste à 9 au lieu de 10.
Je n'ai pas trouvé le soucis.

JHA

 

[merinos-BernardEtang]

Salut @tite schtroumpfette ,

J'ai fait quelques vidéo simples qui montrent comment employer PQ ...

En gros il y a 2 morceaux:
- Power Query (PQ) qui trnasforme les données
- Power Pivot qui permets la création de mesures qui seront montrées dans des TCD.

A+,

Merinos

 

[job75]

Voyez le fichier joint et cette macro dans le code de la 2ème feuille :

Private Sub Worksheet_Activate()
Dim tablo, d As Object, i&, x$, dat, y$, n&, a, b, c()
With Sheets("Feuil1")
    If .FilterMode Then .ShowAllData
    tablo = .Range("H1:R" & .Range("H" & .Rows.Count).End(xlUp).Row) 'matrice, plus rapide
End With
Set d = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(tablo)
    x = CStr(tablo(i, 1))
    If x <> "" Then
        If Not d.exists(x) Then d(x) = ""
        dat = tablo(i, 11)
        If IsDate(dat) Then
            y = Month(dat) & Year(dat)
            If InStr(d(x), Chr(1) & y) = 0 Then d(x) = d(x) & Chr(1) & y
        End If
    End If
Next
n = d.Count
'---transposition---
If n Then
    a = d.keys
    b = d.items
    ReDim c(UBound(a), 1) 'base 0
    For i = 0 To UBound(a)
        c(i, 0) = a(i)
        c(i, 1) = UBound(Split(b(i), Chr(1)))
        If c(i, 1) < 1 Then c(i, 1) = ""
    Next
End If
'---restitution---
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A2]
    If n Then .Resize(n, 2) = c
    .Offset(n).Resize(Rows.Count - n - .Row + 1, 2).ClearContents 'RAZ en dessous
End With
End Sub

Elle se déclenche quand on active la feuille.

Salut merinos.

 

[merinos-BernardEtang]

Salut @job75 ,

C'est toujours super ces macros qui s'activent en arrière plan.

J'aime

Mérinos

 

[job75]

Pour tester j'ai copié la plage H2:R20 sur 28500 lignes, durées d'exécution chez moi :

- avec 3 sites => 0,16 seconde

- avec 4500 sites => 0,21 seconde.