Comment améliorer Sommeprod()

BOISGONTIER · 31 Mars 2013

Bonsoir,

Comment améliorer Sommeprod() lorsque cette fonction

- travaille sur des champs de taille importante
- est recopiée x1000 fois

Sur l'exemple en PJ, avec une fonction perso matricielle, on passe d'un temps de recalcul de 3 sec à 0,05 sec pour 4.000 lignes

=SOMMEPROD((dates=A2)*(numero=B2))

ou

=CombienFois(numero; dates)

Code:

Function CombienFois(champ, champcritere)
  Application.Volatile
  Set mondico = CreateObject("scripting.dictionary")
  a = champ
  b = champcritere
  For i = 1 To UBound(a)
    temp = a(i, 1) & " " & b(i, 1)
    mondico(temp) = mondico(temp) + 1
  Next i
  Dim retour()
  ReDim retour(LBound(b) To UBound(b))
  For i = LBound(b) To UBound(b)
    temp = a(i, 1) & " " & b(i, 1)
    retour(i) = mondico(temp)
  Next i
  CombienFois = Application.Transpose(retour)
End Function

JB
Formules Matricielles

Modeste geedee · 31 Mars 2013

Re : Comment améliorer Sommeprod()

Bonsour®

Merci Jacques pour ce rappel à privilégier l'utilisation de ("scripting.dictionary")dans de nombreux cas matricielles.

pour info :
(merci Misange pour la "résurrection" du site de LL)

Ce lien n'existe plus

Ces formules ne représentent pas la meilleure réponse aux problèmes posés. Dans de nombreux cas il est préférable de recourir à des fonctions VBA ou à des formules "normales".
Les formules matricielles sont en effet de gros consommateurs de mémoire vive et de temps de calcul. Elles permettent néanmoins d'obtenir certains résultats apparemment impossibles à obtenir par de simples formules, sans macro.

Ce lien n'existe plus

david84 · 3 Avril 2013

Re : Comment améliorer Sommeprod()

Bonjour,

SOMMEPROD étant une fonction faite avant tout pour faire la somme des produits, autant la comparer dans ce cadre.

Ci-joint 2 tests entre SOMMEPROD et 2 fonctions personnalisées utilisant Dictionary et la trame initiale de Jacques.

Attention toutefois à comparer ce qui est comparable : outre le fait qu'une fonction personnalisée bien construite sera généralement plus rapide qu'une fonction matricielle "généraliste" (puisque pensée et construite pour ce problème spécifique), le mode de validation de ces fonctions personnalisées (entrée de la formule en une seule fois dans l'ensemble de la plage d'un côté, validation de SOMMEPROD cellule par cellule de l'autre) ne permet pas à mon avis de jauger la vitesse de traitement de SOMMEPROD.

Constatons simplement que les fonctions personnalisées sont plus rapides et plus adaptées dans ce contexte précis.
A+

Comment améliorer Sommeprod()

BOISGONTIER

XLDnaute Barbatruc

Pièces jointes

Modeste geedee

XLDnaute Barbatruc

david84

XLDnaute Barbatruc

Pièces jointes

Discussions similaires