XL 2019 Accélérer le traitement de données par rapport à une formule.

[Loïc DUBOIS]

Bonjour tout le monde,

J'espère que vous allez bien ?

Voici mon problème : je travaille actuellement avec des fichiers très gros (les bases de données brutes comprennent entre 400k et 950k lignes). J'utilise une formule très basique pour compter selon des critères (sur une seconde feuille) :
=NB.SI.ENS('Sheet 1'!B:B;Feuil1!A2;'Sheet 1'!E:E;Feuil1!D2;'Sheet 1'!H:H;Feuil1!G2) --> pour la colonne N dans "feuil1"
=NB.SI.ENS('Sheet 1'!$B:$B;feuil1!$A2;'Sheet 1'!$E:$E;feuil1!$D2;'Sheet 1'!$H:$H;feuil1!$G2;'Sheet 1'!$K:$K;feuil1!O$1) --> pour colonne O dans "feuil1".

Il est très facile d'appliquer cette formule sur toutes mes lignes mais mon jeu de donnée est trop conséquent et excel n'arrive pas à gérer une si grande base.

J'aimerais donc savoir s'il y a un moyen de contourner cela. En effet, excel crache très souvent après avoir attendu 3h+ pour que ma formule soit appliqué à 38000 lignes. Par exemple, est ce qu'en VBA les ressources utilisées sont moindres et donc le temps pour compter mes données serait plus faible ?

Je vous joins un fichier test.

Merci d'avance,

 

[Loïc DUBOIS]

Voici le fichier

 

[Cousinhub]

Bonsoir,
Ce soir, je ne pourrai te répondre...
Cependant, je pense que la solution la plus adaptée est via l'utilisation de Power Query.
Si personne ne t'a répondu d'ici demain soir, je verrai ce qu'il est possible de faire
Bon courage, et bonne soirée

 

[laurent950]

Bonsoir @Loïc DUBOIS, Le Forum

Avec ce code le résultat devrait être instantané pour les colonnes N, O

Ps : j'ai aussi fait le code pour l'ensemble des formules : colonnes N, O, P, Q, R, S, T
J'ai rendu le code très compacte pas plus de lignes que celui de se poste est toujours
Ultra rapide. si vous en avez besoin

Option Explicit
Sub CorrigeErrRefEtendu()
Application.ScreenUpdating = False
Dim t As Single
't = Timer
' Feuil 1 Résultat : Colonne N et O
Dim FRes As Worksheet
    Set FRes = Worksheets("feuil1")
Dim ResPlage As Range
    Set ResPlage = FRes.Range(FRes.Cells(2, 1), FRes.Cells(FRes.Cells(1048576, 1).End(xlUp).Row, FRes.Cells(1, 16384).End(xlToLeft).Column))
Dim Tres()
    ReDim Tres(1 To ResPlage.Rows.Count, 1 To 2) ' Ecriture Via VAriable tableau Plus Rapide que Par Variable Objet Range
Dim Dic1 As Object
Set Dic1 = CreateObject("scripting.dictionary") ' Colonne N
Dim Dic2 As Object
Set Dic2 = CreateObject("scripting.dictionary") ' Colonne 0
Dim Val As Range
For Each Val In ResPlage.Resize(ResPlage.Rows.Count, 1)
    Dic1.Add Val.Text & "-" & Val.Offset(, 3).Text & "-" & Val.Offset(, 6).Text, 0
    Dic2.Add Val.Text & "-" & Val.Offset(, 3).Text & "-" & Val.Offset(, 6).Text & "-" & FRes.Cells(1, 15), 0
Next
'
' -----------------------------------------------------------------------------------------------------------------------------------------------
' Feuil base : Sheet 1
Dim FBase As Worksheet
    Set FBase = Worksheets("Sheet 1")
Dim Tbase() As Variant
    Tbase = FBase.Range(FBase.Cells(2, 1), FBase.Cells(FBase.Cells(1048576, 1).End(xlUp).Row, FBase.Cells(1, 16384).End(xlToLeft).Column))
'
' -----------------------------------------------------------------------------------------------------------------------------------------------
' Resultat ' Colonne N
Dim Cle As Variant
Dim i As Long
For Each Cle In Dic1.keys
    If Dic1.Exists(Cle) Then
        For i = LBound(Tbase, 1) To UBound(Tbase, 1)
            If Tbase(i, 2) & "-" & Tbase(i, 5) & "-" & Tbase(i, 8) = Cle Then
                Dic1.Item(Cle) = Dic1.Item(Cle) + 1
            End If
        Next i
    End If
Next
For Each Val In ResPlage.Resize(ResPlage.Rows.Count, 1)
    'Val.Offset(, 13) = Dic1.Item(Val.Text & "-" & Val.Offset(, 3).Text & "-" & Val.Offset(, 6).Text) ' ..... Ecriture Range Long
    Tres(Val.Row - 1, 1) = Dic1.Item(Val.Text & "-" & Val.Offset(, 3).Text & "-" & Val.Offset(, 6).Text) ' .. Ecriture Variable Tableau Rapide
Next
i = Empty
' ---------------------------------------------------------
' Resultat ' Colonne O
For Each Cle In Dic2.keys
    If Dic2.Exists(Cle) Then
        For i = LBound(Tbase, 1) To UBound(Tbase, 1)
            If Tbase(i, 2) & "-" & Tbase(i, 5) & "-" & Tbase(i, 8) & "-" & Tbase(i, 11) = Cle Then
                Dic2.Item(Cle) = Dic2.Item(Cle) + 1
            End If
        Next i
    End If
Next
For Each Val In ResPlage.Resize(ResPlage.Rows.Count, 1)
    'Val.Offset(, 14) = Dic2.Item(Val.Text & "-" & Val.Offset(, 3).Text & "-" & Val.Offset(, 6).Text & "-" & FRes.Cells(1, 15)) ' ..... Ecriture Range Long
    Tres(Val.Row - 1, 2) = Dic2.Item(Val.Text & "-" & Val.Offset(, 3).Text & "-" & Val.Offset(, 6).Text & "-" & FRes.Cells(1, 15)) ' .. Ecriture Variable Tableau Rapide
Next
' resultat via Variable tableau Rapide
    FRes.Cells(2, 14).Resize(UBound(Tres, 1), UBound(Tres, 2)) = Tres
'MsgBox Timer - t
Application.ScreenUpdating = True
End Sub

 

[ChTi160]

Bonsoir le Fil
je suis nul en formule Lol
Donc je ne comprends pas ( encore ) la méthode à appliquer lol
la formule est :

=NB.SI.ENS('Sheet 1'!B:B;Feuil1!A2;'Sheet 1'!E:E;Feuil1!D2;'Sheet 1'!H:H;Feuil1!G2)

je comprends qu'il y a en cellules
Feuil1!A2 Feuil1!D2 Feuil1!G2 des valeurs qui doivent servir a faire une comparaison avec les Colonnes B,E,H
Pourrais-tu me dire ce qu'il faut faire et un exemple de quelques lignes de ce que tu veux comme résultat.
édit ! ou alors la Feuil1 contient les données et seules les colonnes O et N sont à remplir ?
Merci par avance
Jean marie

 

[ChTi160]

re
Voir le Fichier
d'après ce que j'ai Compris Lol
j'ai créer deux Tableau structurés ,
supprimé les Formules des Colonnes O à T
j'ai crée un Bouton en feuille "Feuil1"
a voir donc ! a tester pour voir le résultat au Niveau gain de temps .

ne pas hésiter , si cela correspond ,à demander des commentaires Lol
Bonne fin de Soirée
Jean marie

 

[Loïc DUBOIS]

Merci beaucoup pour toutes vos réponses,

Je vais tester tout cela et je reviens vers vous.

Loïc

 

[Loïc DUBOIS]

Je reviens vers vous.

Il s'avère que pour vos deux solutions, j'ai le même problème :

Apparemment, ce qui cloche concerne le "scripting.dictionary".

Je travaille sur Mac, est-ce pour cela ?

Merci d'avance,

Loïc

 

[job75]

Bonjour à tous,

Je travaille sur Mac, est-ce pour cela ?

Oui sur MAC on ne peut pas utiliser le Dictionary.

Pour tester j'ai recopié le tableau de la 1ère feuille sur 1 080 000 lignes.

Les 18 formules en colonnes N et O de la 2ème feuille se recalculent en 1,6 seconde chez moi.

La macro du post #4 s'exécute en 15 secondes.

Il est probable que VBA sera plus rapide s'il y a beaucoup de formules.

A+

 

[Loïc DUBOIS]

Bonjour à tous,

Oui sur MAC on ne peut pas utiliser le Dictionary.

Pour tester j'ai recopié le tableau de la 1ère feuille sur 1 080 000 lignes.

Les 18 formules en colonnes N et O de la 2ème feuille se recalculent en 1,6 seconde chez moi.

La macro du post #4 s'exécute en 15 secondes.

Il est probable que VBA sera plus rapide s'il y a beaucoup de formules.

A+

Merci beaucoup pour ta réponse.

Malheureusement, je n'ai pas accès à un pc windows...

Comment peut-on adapter le code pour que cela fonctionne sur Mac ?

Merci d'avance,

Loïc

 

[job75]

Comment peut-on adapter le code pour que cela fonctionne sur Mac ?

Au lieu du Dictionary il faut utiliser des collections.

Laurent950 pourra vous faire ça.

 

[Cousinhub]

Bonsoir,
Je suppose que les quelques lignes (9) de l'onglet contenant les formules n'étaient que pour l'exemple...
Et que en réalité, il doit y avoir bien plus de "réunions" différentes, comportant tout autant de votes, et avec une multitude de dates différentes...
Si ce nombre de lignes n'est pas appelé à changer, alors, VBA (hormis Dictionary) pourrait être la solution.
Par contre, il me semble que dans l'univers "MAC", Power Query n'est disponible qu'à partir des versions 365...
Si tu peux vérifier pour 2019???
Car le code ressemblerait à ceci :

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Pivot = Table.Pivot(Source, List.Distinct(Source[voteDecision]), "voteDecision", "fund", List.Count),
    Total_Nb_Votes = Table.AddColumn(Pivot, "Nb Votes", each List.Sum({[for], [abstain], [n], [against]}), type number)
in
    Total_Nb_Votes

et te donnerait autant de lignes que de nombres de réunions différentes...
Bonne soirée

 

[Loïc DUBOIS]

Bonsoir,
Je suppose que les quelques lignes (9) de l'onglet contenant les formules n'étaient que pour l'exemple...
Et que en réalité, il doit y avoir bien plus de "réunions" différentes, comportant tout autant de votes, et avec une multitude de dates différentes...
Si ce nombre de lignes n'est pas appelé à changer, alors, VBA (hormis Dictionary) pourrait être la solution.
Par contre, il me semble que dans l'univers "MAC", Power Query n'est disponible qu'à partir des versions 365...
Si tu peux vérifier pour 2019???
Car le code ressemblerait à ceci :

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Pivot = Table.Pivot(Source, List.Distinct(Source[voteDecision]), "voteDecision", "fund", List.Count),
    Total_Nb_Votes = Table.AddColumn(Pivot, "Nb Votes", each List.Sum({[for], [abstain], [n], [against]}), type number)
in
    Total_Nb_Votes

et te donnerait autant de lignes que de nombres de réunions différentes...
Bonne soirée

Bonsoir CousinHub,

Merci pour ton commentaire,
J'ai effectivement accès à Power query. En revanche, je m'en ai jamais servi. Peux-tu m'expliquer les étapes à suivre pour insérer ce code ?

Merci d'avance,

Loïc

 

[Cousinhub]

Re,
Comme il n'y a pas de tableau structuré (TS) dans ton fichier, on va le faire en direct-live..
Il n'y a aucun risque, mais fais les essais dans une copie de ton fichier.
Je te mets des captures d'écran (mais pour PC, à voir pour MAC?)
Tu cliques sur la première cellule de ta plage (ici, c'est A1, mais dans ton fichier???)
Puis, menu "Données", tu cliques sur "A partir de Tableau ou d'une Plage"

Ta plage va se transformer en TS, et se nommer "Tableau1"
Puis l'éditeur Power Query va s'ouvrir, et tu verras tout ton tableau.
Maintenant, tout va se passer dans cet éditeur
Tu sélectionnes la colonne contenant les votes (nommée "voteDecision" dans ton exemple)
Ruban "Transformer", tu cliques sur "Pivoter la colonne"

Tu auras cette boîte :

Et tu valides
Tu auras un certain nombres de colonne supplémentaires sur la droite :
Tu sélectionnes les nouvelles colonnes (tu cliques sur la 1ère nouvelle colonne, puis en maintenant la touche "Shift" appuyée, clic sur la dernière)
Puis ruban "Ajouter une colonne", tu cliques sur "Standard", "Ajouter"

Tu auras la somme des votes...
Bon courage

 

[Cousinhub]

Re-,
Oublié de dire que pour obtenir ton tableau, tu cliques sur "Fichier/Fermer et Charger", et tu sélectionnes la destination... (nouvelle feuille....)