Macro sur 145 000 lignes Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 13'489
Appréciations reçues : 752
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR
Téléchargements : Mes applications

Message par Steelson » 13 décembre 2019, 08:41

Il faut combiner les 2 !

avec ceci j'ai 3 secondes !
Sub version_v3()
Dim DataRange As Variant
Dim Irow As Long
Dim Myvar As Variant
Dim Dico As Object
Set Dico = CreateObject("Scripting.Dictionary")
Dico.CompareMode = 1
  
depart = Now
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

    DataRange = Worksheets("Feuil1").Range("AL2:AL" & Range("B" & Rows.Count).End(xlUp).Row).Value
    For i = 2 To Cells(Rows.Count, "B").End(xlUp).Row
        If Not Dico.exists(Cells(i, "T").Value) Then DataRange(i - 1, 1) = 1 Else DataRange(i - 1, 1) = 0
        Dico(Cells(i, "T").Value) = 1
    Next
    Worksheets("Feuil1").Range("AL2:AL" & Range("B" & Rows.Count).End(xlUp).Row).Value = DataRange
    
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox Format(Now - depart, "hh:mm:ss")
    
End Sub
https://www.cjoint.com/c/ILnh5F7UPGw

Mais j'aime beaucoup l'idée de Baboutz de mettre les formules dans un tableau et de le coller, ce qui évite en effet à excel de mouliner à tout bout de champ.

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 13'489
Appréciations reçues : 752
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR
Téléchargements : Mes applications

Message par Steelson » 13 décembre 2019, 10:31

Désolé, je n'avais pas été au bout des possibilités ...

1 seconde de traitement :
Sub version_v4()
Dim critere As Variant, resultat As Variant, i As Long
Dim Dico As Object
Set Dico = CreateObject("Scripting.Dictionary")
Dico.CompareMode = 1
  
depart = Now

    critere = Range("T2:T" & Range("B" & Rows.Count).End(xlUp).Row).Value
    ReDim resultat(1 To UBound(critere), 1 To 1)
    For i = 1 To UBound(critere)
        If Not Dico.exists(critere(i, 1)) Then resultat(i, 1) = 1 Else resultat(i, 1) = 0
        Dico(critere(i, 1)) = 1
    Next
    Range("AL2:AL" & Range("B" & Rows.Count).End(xlUp).Row).Value = resultat
    
MsgBox Format(Now - depart, "hh:mm:ss")
    
End Sub
2 membres du forum aiment ce message.

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
Avatar du membre
Baboutz
Membre fidèle
Membre fidèle
Messages : 412
Appréciations reçues : 32
Inscrit le : 19 avril 2019
Version d'Excel : 2016 FR

Message par Baboutz » 13 décembre 2019, 10:44

Impressionnant Steelson, belle combinaison !

Je ne connait quasiment pas la fonction Dico ainsi que d'autres que tu as utilisé, je vais me pencher là-dessus pour apprendre et comprendre.
Merci !
Avatar du membre
VDMICHEL
Membre fidèle
Membre fidèle
Messages : 473
Appréciations reçues : 3
Inscrit le : 13 novembre 2013
Version d'Excel : 2003

Message par VDMICHEL » 13 décembre 2019, 15:22

Bonjour Baboutz, eriic, Steelson et curulis57,

WOW, je me sens comme un jour de Noël!

J'ai testé la V2 de Steelson sur un fichier de 33 500 lignes. Bon pour 22 secondes. ;;)

Si j'ai bien compris, la macro donne le résultat de la formule SI(NB.SI($T$1:$T2;$T2)=1;1;0) jusqu'à la dernière ligne non vide.

J'ai mis la macro dans un module, et pour contre vérifier j'ai écris la formule en AM2 pour comparer les résultats de la macro, et à ma grande surprise la formule c'est étendue jusqu'à la dernière ligne non vide sans que j'active la macro!

Est-ce normal?

Est-ce possible aussi d'ajouter cette formule à la macro :SI([@[DESCR_LOGEMENT]]="0000000";0;SI(NB.SI($X$1:X2;$X2)>1;0;1))

Merci pour votre précieuse générosité.
Avatar du membre
Baboutz
Membre fidèle
Membre fidèle
Messages : 412
Appréciations reçues : 32
Inscrit le : 19 avril 2019
Version d'Excel : 2016 FR

Message par Baboutz » 13 décembre 2019, 15:46

Pourquoi ne pas avoir pris directement la version 4, la plus rapide ? :scritch:

Il doit être possible en effet de rajouter cette formule, mais il faudrait nous préciser où exactement ? Quelle plage de cellule ?
Avatar du membre
VDMICHEL
Membre fidèle
Membre fidèle
Messages : 473
Appréciations reçues : 3
Inscrit le : 13 novembre 2013
Version d'Excel : 2003

Message par VDMICHEL » 13 décembre 2019, 16:13

Bonjour Baboutz,

Ok, je viens d'apprendre que le fichier est en mode tableaux, d'où le drag automatique de tout à l'heure!

La seconde formule en question est
SI([@[DESCR_LOGEMENT]]="0000000";0;SI(NB.SI($X$1:X2;$X2)>1;0;1))
. Et doit se retrouver dans la colonne AK.

Le DESCR_LOGEMENT est le titre qui correspond à la colonne G, les X sont vraiment les colonne X.

Merci encore pour votre temps.
Modifié en dernier par VDMICHEL le 13 décembre 2019, 18:01, modifié 1 fois.
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 13'489
Appréciations reçues : 752
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR
Téléchargements : Mes applications

Message par Steelson » 13 décembre 2019, 17:58

Ce genre de formule
SI(NB.SI($X$1:$X2;$X2)>1;0;1)
se simplifie en utilisant justement dico.scripting. Car il s'agit juste de savoir si une valeur est déjà sortie ou non. Car c'est cela qui est surtout chronophage.

Qui se lance ? c'est l'occasion de tester !

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
Avatar du membre
VDMICHEL
Membre fidèle
Membre fidèle
Messages : 473
Appréciations reçues : 3
Inscrit le : 13 novembre 2013
Version d'Excel : 2003

Message par VDMICHEL » 13 décembre 2019, 18:12

Bonjour Steelson,

la formule exacte est :
SI($G1="000000";0;SI(NB.SI($X$1:X2;$X2)>1;0;1))
pour la colone AK2 jusqu'à la dernière ligne non vide.

Merci beaucoup.
Avatar du membre
VDMICHEL
Membre fidèle
Membre fidèle
Messages : 473
Appréciations reçues : 3
Inscrit le : 13 novembre 2013
Version d'Excel : 2003

Message par VDMICHEL » 13 décembre 2019, 18:21

oups, j'ai ajouté l'absolu devant le G
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'309
Appréciations reçues : 374
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 13 décembre 2019, 19:17

Re,

si tu es en Tableau, y'a-t'il toujours un intérêt à mettre une macro ?
Par définition un tableau s'étend tout seul en mettant formules et formats.
eric
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message