XL 2019 somme plus rapide avec array

Sheldor

XLDnaute Occasionnel
Supporter XLD
Bonjour,
je me lance petit à petit dans ce monde étrange des arrays pour avoir des procédures plus rapides et j'avoue que la gymnastique est difficile..

je fais la somme de valeur en fonction de critères et je les redistribue en fonction d'un numéro de référence, la pièce jointe expliquera mieux qu'un grand texte ici.

J'ai fais le script en vba mais sur des gros jeux de données c'est long à tourner du coup si quelqu'un pouvait m'aiguiller sur les arrays pour faire la même chose ce serait merveilleux

merci beaucoup
 

Pièces jointes

  • temp_somme_array.xlsm
    23.5 KB · Affichages: 13

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour Sheldor,
Ne serait ce pas plus rapide en formule simple comme en PJ avec :
VB:
=SOMME.SI.ENS(C2:$C$200;A2:$A$200;I2;B2:$B$200;"a")
Quel taille fait votre fichier pour qu'une simple formule soit plus longue que du VBA ?
 

Pièces jointes

  • temp_somme_array.xlsm
    25.3 KB · Affichages: 1

Sheldor

XLDnaute Occasionnel
Supporter XLD
Bonjour Sylvanu et merci pour ta réponse,
je travaille sur des fichiers qui contiennent environ 100 000 cases remplies et j'ai pris l'habitude de ne jamais utiliser de formule car l'accumulation ralentirait trop le fichier mais ce n'est peut être pas une bonne stratégie.
Je pourrais mettre la formule par vba et récupérer la valeur, je vais réfléchir à ça

merci beaucoup !
 

Cousinhub

XLDnaute Barbatruc
Bonjour,
Et l'option Tableau Croisé Dynamique, y as-tu pensé?
C'est ce qui se fait de plus véloce, s'il n'y a que des calculs de ce genre...
A voir (ici, j'ai fait un filtre de page, mais on peut mettre en colonne, pour avoir tous les totaux...
 

Pièces jointes

  • temp_somme_array-1.xlsm
    27.3 KB · Affichages: 2

Sheldor

XLDnaute Occasionnel
Supporter XLD
Bonjour Cousinhub et merci pour ton message
du coup là ce qui me manquerait c'est d'avoir tous les numéros de ref , par exp le 8 ne sort pas dans le tcd parce qu'il n'y avait pas de poids.
J'ai simplifié mon problème pour que ce soit "lisible" mais j'ai encore plusieurs colonnes qui vont se remplir à droite en provenance d'autres sources et par exemple pour le 8 j'aurais des valeurs dans d'autre colonnes.
mais merci beaucoup, effectivement je ne pense pas assez au TCD, d'ailleurs j'ai découvert les segments récemment et j'adore

bon après midi
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonsoir à tous,

Pour ce que j'en ai compris.
Par macro, array et dictionary.
Le fichier comporte environ 50 000 lignes de données. les références vont de 1 à 10 000.

Le code dans module1 :
VB:
Sub test()
Dim der&, min&, max&, dico, t, i&, x

   If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
   der = Cells(Rows.Count, "a").End(xlUp).Row
   t = Range("a2:c" & der).Value
   min = Application.min(Range("a2:a" & der))
   max = Application.max(Range("a2:a" & der))
   Set dico = CreateObject("scripting.dictionary")
   dico.CompareMode = TextCompare
   For i = min To max: dico.Add i, "": Next
   For i = 1 To UBound(t)
      If t(i, 2) = "a" Then dico(t(i, 1)) = IIf(dico(t(i, 1)) = "", 0, dico(t(i, 1))) + t(i, 3)
   Next i
   ReDim r(1 To max, 1 To 2): i = 0
   For Each x In dico.Keys: i = i + 1: r(i, 1) = x: r(i, 2) = dico(x): Next
   Range("i2:j" & der).Clear: i = 0
   Range("i2").Resize(dico.Count, 2) = r
   Range("i2").Resize(dico.Count, 2).Borders.LineStyle = xlContinuous
End Sub
 

Pièces jointes

  • Sheldor- Somme Array- v1.xlsm
    953.6 KB · Affichages: 5

Sheldor

XLDnaute Occasionnel
Supporter XLD
bonjour mapomme,
merci beaucoup pour ton message, puis-je abuser et te demander de m'expliquer cette partie du code ?

For i = 1 To UBound(t)
If t(i, 2) = "a" Then dico(t(i, 1)) = IIf(dico(t(i, 1)) = "", 0, dico(t(i, 1))) + t(i, 3)
Next i
ReDim r(1 To max, 1 To 2): i = 0
For Each x In dico.Keys: i = i + 1: r(i, 1) = x: r(i, 2) = dico(x): Next
Range("i2:j" & der).Clear: i = 0

c'est exactement ce que je voudrais apprendre à faire mais je manque de connaissance pour comprendre ce qui se passe
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour @Sheldor :),

Voici le fichier demandé avec les commentaires (il y en a plus que demandé - d'autres pourraient être intéressés).
  • J'ai simplifié le remplissage de dico => point 6 en utilisant Val (plus lisible que le IIF)
  • J'ai modifié la création de r => point 7 en considérant que le minimum n'est pas forcément 1 mais bien la valeur min.
nota : me demander si un point n'est pas clair.
 

Pièces jointes

  • Sheldor- Somme Array- v1(comm).xlsm
    956.2 KB · Affichages: 3
Dernière édition:

Sheldor

XLDnaute Occasionnel
Supporter XLD
bonjour mapomme,
merci infiniment d'avoir pris le temps d'écrire toutes ces précieuses informations, ça m'aide beaucoup.

j'aurais qq questions,
-est ce que dico.CompareMode = TextCompare signifie ne pas distinguer la casse?
-pour r je n'ai pas compris pourquoi i = min - 1 plutôt que i = min
-j'ai pas du tout compris Range("i2:j" & der).Clear: i = 0
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Re,
est ce que dico.CompareMode = TextCompare signifie ne pas distinguer la casse?
Oui c'est cela.
pour r je n'ai pas compris pourquoi i = min - 1 plutôt que i = min

r est un tableau avec le premier indice qui varie de min à max.
Quand on commence à remplir ce tableau r, le premier indice doit être égal à min.
Dans la boucle de remplissage qui suit, on incrémente i pour écrire sur la prochaine ligne i de r.

Si on avait initialisé i à min, la première fois qu'on passe dans la boucle, on aurait i = min+1.
Or l'indice de la première ligne de r est bien min et pas min-1.
Donc juste avant la boucle, on initialise i à min-1.
La première fois qu'on passe dans la boucle, on fait i =i +1 soit (min -1 +1) qui vaut min et on remplit bien r à sa première ligne.

j'ai pas du tout compris Range("i2:j" & der).Clear: i = 0
On efface les précédents résultats depuis la ligne 2 jusqu'à la ligne der (pour les colonnes I à J) .
En fait vous avez raison, il faudrait effacer depuis la ligne 2 jusqu'à la dernière ligne de la feuille par :
Range("i2:j" & Rows.Count).Clear
Quant au :i = 0, c'est une coquille! On peut le supprimer.
 

Discussions similaires

Statistiques des forums

Discussions
313 257
Messages
2 096 609
Membres
106 682
dernier inscrit
faferrand