XL 2019 somme plus rapide avec array

[Sheldor]

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

 

[sylvanu]

Bonjour Sheldor,
Ne serait ce pas plus rapide en formule simple comme en PJ avec :

=SOMME.SI.ENS(C2:$C$200;A2:$A$200;I2;B2:$B$200;"a")

Fonction SOMME.SI.ENS - Support Microsoft

Comment utiliser la fonction SOMME.SI.ENS dans Excel, l'une des fonctions mathématiques et trigonométriques d'Excel, pour additionner les arguments qui répondent à plusieurs critères.

support.microsoft.com

Quel taille fait votre fichier pour qu'une simple formule soit plus longue que du VBA ?

 

[Sheldor]

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]

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...

 

[Sheldor]

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

 

[Cousinhub]

Re-,
Si un moment ou un autre, cette réf 8 apparaît avec un poids non nul, alors elle sera mise à jour dans le TCD...
Ou alors, je ne comprends pas tout?
Peut-être aussi...

 

[mapomme]

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 :

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

 

[Sheldor]

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]

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.

 

[Sheldor]

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]

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.