XL 2019 Tri et Traitement plus rapide

Créer une discussion
Répondre

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
Titof06

Titof06

XLDnaute Occasionnel
Bonjour,

Je reviens vers vous, car je dois trier des informations (secteurs) par rapport à un numéro de fournisseur (fourn.).

Mon fichier pèse plus de 52 Mo et encore, et j'en ai enlevé la moitié.
Il y a 574 000 lignes, et il va grossir progressivement.
Voici le lien WeTransfer : https://we.tl/t-4kxiFgP7Lv
valable jusqu'au 13/12/2025

Voilà mon souci, qui n'en est pas un réel, puisque cela fonctionne, mais je souhaiterais diminuer le temps de traitement.

Pour chaque numéro de fournisseur, je dois extraire les "secteurs" de l'onglet "Entrees", enlever les doublons et les trier.

Dans l'onglet "Data", on a :
- en colonne A : Tous les fournisseurs, triés par ordre croissant
- en colonne C : Chaque fournisseur, mis un après l'autre
- en colonne D : Les Secteurs du Fournisseur de la Colonne C, trié par ordre croissant

On peut voir qu'un fournisseur peut avoir un ou plusieurs secteurs.

Le traitement actuel fonctionne, mais pour plus de 400 fournisseurs, c'est très long.

Si quelqu'un a une idée pour réduire le temps, je l'en remercie par avance.

POUR LE TEST dans la ligne "For l = 2 to ", j'ai mis "10" pour limiter le traitement; au lieu de "nbr_lig_data_A" qui est pour tous les fournisseurs.

Je vous remercie pour le temps que vous allez m'accorder et vous souhaite une agréable journée,

Titof06
 
Solution
turbule
Bonjour

En effet, votre code doit être très long. Lorsque vous bouclez sur centaines de milliers de lignes dans Excel, ça prends du temps.

Pour gagner du temps, il faut faire la boucle dans un Tableau qui lui est stocké en mémoire vive. J'ai optimisé un peu plus en créant un dictionnaire pour stocker vos paires fournisseur / secteur.

VB: 
Sub Extract()

Dim wb As Workbook
Dim ws As Worksheet, wsC As Worksheet
Dim lo As ListObject
Dim tb()
Dim dict As Object
Dim i As Long
Dim cleFournisseurSecteur As String
Dim colFournisseur As Long, colSecteur As Long
Dim ligneDestination As Long


Set wb = ThisWorkbook
Set wsC = wb.Worksheets("Data")
Set ws = wb.Worksheets("Entrees")
Set lo = ws.ListObjects("TabEntree")


tb = lo.DataBodyRange...
Trier par date Trier par votes
Titof06 à dit:
Bonjour,

Je reviens vers vous, car je dois trier des informations (secteurs) par rapport à un numéro de fournisseur (fourn.).

Mon fichier pèse plus de 52 Mo et encore, et j'en ai enlevé la moitié.
Il y a 574 000 lignes, et il va grossir progressivement.
Voici le lien WeTransfer : https://we.tl/t-4kxiFgP7Lv
valable jusqu'au 13/12/2025

Voilà mon souci, qui n'en est pas un réel, puisque cela fonctionne, mais je souhaiterais diminuer le temps de traitement.

Pour chaque numéro de fournisseur, je dois extraire les "secteurs" de l'onglet "Entrees", enlever les doublons et les trier.

Dans l'onglet "Data", on a :
- en colonne A : Tous les fournisseurs, triés par ordre croissant
- en colonne C : Chaque fournisseur, mis un après l'autre
- en colonne D : Les Secteurs du Fournisseur de la Colonne C, trié par ordre croissant

On peut voir qu'un fournisseur peut avoir un ou plusieurs secteurs.

Le traitement actuel fonctionne, mais pour plus de 400 fournisseurs, c'est très long.

Si quelqu'un a une idée pour réduire le temps, je l'en remercie par avance.

POUR LE TEST dans la ligne "For l = 2 to ", j'ai mis "10" pour limiter le traitement; au lieu de "nbr_lig_data_A" qui est pour tous les fournisseurs.

Je vous remercie pour le temps que vous allez m'accorder et vous souhaite une agréable journée,

Titof06
Cliquez pour agrandir...
Bonjour,

Ce qui ralenti énormément l’exécution sont les "Select".
Explique ce que tu souhaites obtenir.

A+
 
Vote positif 0 Vote négatif
Bonjour

En effet, votre code doit être très long. Lorsque vous bouclez sur centaines de milliers de lignes dans Excel, ça prends du temps.

Pour gagner du temps, il faut faire la boucle dans un Tableau qui lui est stocké en mémoire vive. J'ai optimisé un peu plus en créant un dictionnaire pour stocker vos paires fournisseur / secteur.

VB: 
Sub Extract()

Dim wb As Workbook
Dim ws As Worksheet, wsC As Worksheet
Dim lo As ListObject
Dim tb()
Dim dict As Object
Dim i As Long
Dim cleFournisseurSecteur As String
Dim colFournisseur As Long, colSecteur As Long
Dim ligneDestination As Long


Set wb = ThisWorkbook
Set wsC = wb.Worksheets("Data")
Set ws = wb.Worksheets("Entrees")
Set lo = ws.ListObjects("TabEntree")


tb = lo.DataBodyRange


' Trouver les colonnes
    colFournisseur = lo.ListColumns("N° Four").Index
    colSecteur = lo.ListColumns("Sect.").Index
    
    ' Créer un dictionnaire pour stocker les paires uniques
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' Parcourir le tableau et ajouter les paires uniques
    For i = 1 To UBound(tb, 1)
        ' Créer une clé unique combinant fournisseur et secteur
        cleFournisseurSecteur = tb(i, colFournisseur) & "|" & tb(i, colSecteur)
        
        ' Ajouter seulement si la paire n'existe pas déjà
        If Not dict.Exists(cleFournisseurSecteur) Then
            dict.Add cleFournisseurSecteur, Array(tb(i, colFournisseur), tb(i, colSecteur))
        End If
    Next i
    
    ' Écrire les résultats dans la feuille Data
    wsC.Range("F1").Value = "Fournisseur"
    wsC.Range("G1").Value = "Secteur"
    
    ligneDestination = 2
    Dim cle As Variant
    For Each cle In dict.Keys
        wsC.Cells(ligneDestination, 6).Value = dict(cle)(0) ' Fournisseur
        wsC.Cells(ligneDestination, 7).Value = dict(cle)(1) ' Secteur
        ligneDestination = ligneDestination + 1
    Next cle
    
    MsgBox dict.Count & " paires uniques extraites !", vbInformation
    
End Sub
'Explications:

'Dictionary - stocke les paires uniques (clé = "Fournisseur|Secteur")
'Clé composite - combine fournisseur et secteur avec un séparateur |
'dict.Exists - vérifie si la paire existe déjà
'Écriture dans Data - écrit les résultats à partir de f

Vous pouvez télécharger votre fichier https://we.tl/t-SFBCLQZw1N
 
Vote positif 0 Vote négatif
Solution
Bonjour Tito, Cathodique, Turbule,
Peut être une autre approche.
Pourquoi ne pas faire un TCD avec simplement :
1765363251463.png

et on obtient très rapidement :
1765363298926.png

Je ne poste pas de PJ, trop long.
 
Dernière édition:
Vote positif 0 Vote négatif
sylvanu à dit:
Bonjour Tito, Cathodique, Turbule,
Peut être une autre approche.
Pourquoi ne pas faire un TCD avec simplement :
Regarde la pièce jointe 1225667
et on obtient très rapidement :
Regarde la pièce jointe 1225668
Je ne poste pas de PJ, trop long.
Cliquez pour agrandir...
Bonjour sylvanu,

Je vous remercie pour votre solution, mais après je dois extraire ces informations dans plusieurs tableaux différents, à des fins de statistiques.

Avec un TCD, trop compliqué pour moi d'extraire ces données.

Je vous remercie pour le temps passé et vous souhaite une agréable fin de journée,

Titof06
 
Vote positif 0 Vote négatif
turbule à dit:
Bonjour

En effet, votre code doit être très long. Lorsque vous bouclez sur centaines de milliers de lignes dans Excel, ça prends du temps.

Pour gagner du temps, il faut faire la boucle dans un Tableau qui lui est stocké en mémoire vive. J'ai optimisé un peu plus en créant un dictionnaire pour stocker vos paires fournisseur / secteur.

VB: 
Sub Extract()

Dim wb As Workbook
Dim ws As Worksheet, wsC As Worksheet
Dim lo As ListObject
Dim tb()
Dim dict As Object
Dim i As Long
Dim cleFournisseurSecteur As String
Dim colFournisseur As Long, colSecteur As Long
Dim ligneDestination As Long


Set wb = ThisWorkbook
Set wsC = wb.Worksheets("Data")
Set ws = wb.Worksheets("Entrees")
Set lo = ws.ListObjects("TabEntree")


tb = lo.DataBodyRange


' Trouver les colonnes
    colFournisseur = lo.ListColumns("N° Four").Index
    colSecteur = lo.ListColumns("Sect.").Index
   
    ' Créer un dictionnaire pour stocker les paires uniques
    Set dict = CreateObject("Scripting.Dictionary")
   
    ' Parcourir le tableau et ajouter les paires uniques
    For i = 1 To UBound(tb, 1)
        ' Créer une clé unique combinant fournisseur et secteur
        cleFournisseurSecteur = tb(i, colFournisseur) & "|" & tb(i, colSecteur)
       
        ' Ajouter seulement si la paire n'existe pas déjà
        If Not dict.Exists(cleFournisseurSecteur) Then
            dict.Add cleFournisseurSecteur, Array(tb(i, colFournisseur), tb(i, colSecteur))
        End If
    Next i
   
    ' Écrire les résultats dans la feuille Data
    wsC.Range("F1").Value = "Fournisseur"
    wsC.Range("G1").Value = "Secteur"
   
    ligneDestination = 2
    Dim cle As Variant
    For Each cle In dict.Keys
        wsC.Cells(ligneDestination, 6).Value = dict(cle)(0) ' Fournisseur
        wsC.Cells(ligneDestination, 7).Value = dict(cle)(1) ' Secteur
        ligneDestination = ligneDestination + 1
    Next cle
   
    MsgBox dict.Count & " paires uniques extraites !", vbInformation
   
End Sub
'Explications:

'Dictionary - stocke les paires uniques (clé = "Fournisseur|Secteur")
'Clé composite - combine fournisseur et secteur avec un séparateur |
'dict.Exists - vérifie si la paire existe déjà
'Écriture dans Data - écrit les résultats à partir de f

Vous pouvez télécharger votre fichier https://we.tl/t-SFBCLQZw1N
Cliquez pour agrandir...
Bonjour turbule,

Je vous remercie, cela fonctionne parfaitement et à une vitesse pas possible pour moi...moins de 5 secondes !

Je vais devoir l'adapter à mes autres "tris" car je dois faire 6 tris du même type avec d'autres critères, mais avec tout autant de lignes.
Juste une petite question complémentaire, puis-je garder le même code et affecter d'autres colonnes ?
En prenant mon exemple, j'ai des fournisseurs et des secteurs.
Je dois faire la même chose avec les mêmes fournisseurs et des familles, et ainsi de suite avec 3 autres critères.
Si je garde "colFournisseur = lo.ListColumns("N° Four").Index", est-ce que je peux créer, par exemple "colFamille = lo.ListColumns("Famil.").Index" ?
De même remplacer dans votre code "colSecteur" par "colFamille"
Et l'affecter en
"wsC.Cells(ligneDestination, 8).Value = dict(cle)(1) ' Famille"
"wsC.Range("H1").Value = "Famille"

Je ne savais pas que les "SELECT" ralentissaient la macro, j'en prends bonne note.

Je vous remercie encore souhaite une agréable fin de journée,

Titof06
 
Vote positif 0 Vote négatif
dysorthographie à dit:
bonjours,
personnellement je vois pas pourquoi 4 colonnes alors que tu en a besoin que de 2?

N° Fourn.N° FournSecteur
Cliquez pour agrandir...
Bonjour dysorthographie,

En fait, j'ai besoin de plusieurs choix séparément.
- N° Fourn et Sect.
- N° Fourn et Fam.
- N° Fourn et Sous Fam.
- etc... plus 3 autres.

Votre solution fonctionne parfaitement.

Je vais la tester sur les 574 000 lignes.

Je vous remercie de votre temps passé pour moi et vous souhaite une agréable journée,

Titof06
 
Vote positif 0 Vote négatif
Titof06 à dit:
Bonjour turbule,

Je vous remercie, cela fonctionne parfaitement et à une vitesse pas possible pour moi...moins de 5 secondes !

Je vais devoir l'adapter à mes autres "tris" car je dois faire 6 tris du même type avec d'autres critères, mais avec tout autant de lignes.
Juste une petite question complémentaire, puis-je garder le même code et affecter d'autres colonnes ?
En prenant mon exemple, j'ai des fournisseurs et des secteurs.
Je dois faire la même chose avec les mêmes fournisseurs et des familles, et ainsi de suite avec 3 autres critères.
Si je garde "colFournisseur = lo.ListColumns("N° Four").Index", est-ce que je peux créer, par exemple "colFamille = lo.ListColumns("Famil.").Index" ?
De même remplacer dans votre code "colSecteur" par "colFamille"
Et l'affecter en
"wsC.Cells(ligneDestination, 8).Value = dict(cle)(1) ' Famille"
"wsC.Range("H1").Value = "Famille"

Je ne savais pas que les "SELECT" ralentissaient la macro, j'en prends bonne note.

Je vous remercie encore souhaite une agréable fin de journée,

Titof06
Cliquez pour agrandir...
Oui vous pouvez décliner le code pour les autres critères. Il faut juste que le nom dans lo.ListColumns("Famil.") corresponde parfaitement au nom de votre colonne.
 
Vote positif 0 Vote négatif
une méthode "oldschool" avec une filtré avancée

VB: 
Sub M_Filtre_Avance()

     t = Timer
     Application.ScreenUpdating = False

     With Sheets("Data")
          .Range("A1:Z1").EntireColumn.Clear     'RAZ colonnes A:Z

          '****************************** Traiter "N°Four" **************************************
          .Range("A1").Value = "N° Four"
          Range("TabEntree[#All]").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("A1"), Unique:=True
          With .Range("A1").CurrentRegion
               .EntireColumn.AutoFit
               .Sort .Range("A1"), Header:=xlYes
          End With

          '****************************** Traiter "N°Four" & secteur *******************************
          .Range("C1:E1").Value = Array("N° Four", "Sect.", "Sect.Nom")
          Range("TabEntree[#All]").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("C1:E1"), Unique:=True
          With .Range("C1").CurrentRegion
               .EntireColumn.AutoFit
               .Interior.Color = xlNone
               .Sort .Range("A1"), xlAscending, .Range("B1"), , xlAscending, Header:=xlYes
          End With

          '****************************** Traiter "N°Four" & famille *******************************
          .Range("G1:I1").Value = Array("N° Four", "Fam.", "Fam.Nom")
          Range("TabEntree[#All]").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("G1:I1"), Unique:=True
          With .Range("G1").CurrentRegion
               .EntireColumn.AutoFit
               .Interior.Color = xlNone
               .Sort .Range("A1"), xlAscending, .Range("B1"), , xlAscending, Header:=xlYes
          End With

     End With
     MsgBox "prêt en " & Format(Timer - t, "0.0\s")
End Sub
 
Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

Raziel abel
  • Question Question
XL 2021 Calcul jours, TCD et outil de pilotage via TCD
Réponses
6
Affichages
520
Raziel abel
Raziel abel
D
  • Question Question
Microsoft 365 Calculer et actualiser une moyenne de délais de traitement
Réponses
3
Affichages
439
goube
goube
J
  • Question Question
Microsoft 365 Naviguer dans un tableau pour copier des données dans un modèle et copier l'un derrière l'autre
Réponses
11
Affichages
436
AtTheOne
AtTheOne
oguruma
EXCEL :: TRIER - TRIER.PAR - SOMMEPROD - CHOISIR - INDEX - SOMME.SI - NB.SI - DECALER - SEQUENCE : Tris dynamiques, regroupements et somme
Réponses
0
Affichages
438
oguruma
oguruma
G
  • Question Question
Microsoft 365 Macro VBA - Excel
Réponses
12
Affichages
723
Dranreb
Dranreb
C
  • Résolu(e)
XL 2019 VBA Tri personnalisé
Réponses
10
Affichages
808
chris
chris
L
  • Question Question
XL 2019 aligner textbox userform et valeur de cellule
Réponses
1
Affichages
463
fanfan38
fanfan38
M
  • Résolu(e)
XL 2016 Recopier des cellules en valeur après un clic
Réponses
10
Affichages
801
Merlin258413
M
G
  • Question Question
Microsoft 365 Avoir des classeurs maitres et les procédures dans un autre classeur.
2
Réponses
19
Affichages
2 K
GClaire
G
P
  • Résolu(e)
XL 2016 Tournoi de volley - Tri par terrain
2
Réponses
21
Affichages
2 K
Phil22
P
G
  • Question Question
XL 2016 Trier par ligne
Réponses
6
Affichages
729
JHA
J
M
  • Résolu(e)
Microsoft 365 Prendre les 10 1ère valeur d'une plage en fonction de 2 critères
2 3
Réponses
43
Affichages
3 K
merinos-BernardEtang
merinos-BernardEtang
J
  • Question Question
Microsoft 365 macro qui ouvre des fichiers excel de type "analyze in excel" généré par power bi et met à jour un filtre "mois" sur plusieurs tcd
Réponses
0
Affichages
585
JJ69
J
A
  • Question Question
XL 2019 Noms et numéros automatique
Réponses
2
Affichages
537
Arckang
A
S
  • Résolu(e)
XL 2016 Tri et copié coller de valeur selon date et varibale en automatique
2
Réponses
29
Affichages
2 K
StanislasC
S
A
  • Résolu(e)
Microsoft 365 Formule indirect?
Réponses
4
Affichages
710
AlexiaC
A
Z
  • Question Question
XL 2010 tableau pour comparer deux colonnes et écrire le résultat dans une troisième colonne
2
Réponses
28
Affichages
2 K
zephir94
Z
O
  • Question Question
XL 2016 Problématique écritures comptables - formule Excel et Macro VBA simple
Réponses
9
Affichages
2 K
fanfan38
fanfan38
S
  • Question Question
XL 2016 Associer une commande fournisseur à une commande client
Réponses
0
Affichages
1 K
SPAL
S
P
  • Résolu(e)
XL 2019 Tri et sélection de cellules à partir d'une base textuelle
Réponses
2
Affichages
809
pistachacha
P
Retour