Test comparatif de vitesse
Nous avons présenté dans le dernier message quatre formules différentes pour aboutir au même résultat, la dernière étant une formule matricielle.
Fort de ce que m’avaient dit des contacts chez Microsoft, j’avais déclaré que la formule matricielle était moins performante en vitesse que les solutions à base de SommeProd().
J’ai fait un premier essai comparatif, avec 5.000 fois la formule, appliquée à une liste de 10.000 combinaisons aléatoires de poste et département.
Je suis parvenu, pour les trois premières formules, à des temps entre 2’30" et 2’40". Et, oh surprise, à un temps de 1’12" pour la formule matricielle.
Là, j’étais quand même surpris que cette dernière soit deux fois plus rapide. J’ai alors réalisé que, pour les trois premières formules, ma macro entrait la formule directement dans les 5.000 cellules alors que – pour éviter que la colonne entière ait une formule matricielle unique – ma macro entrait la formule matricielle en G2, puis la copiait et la collait dans le reste de la colonne.
J’ai donc modifié ma macro pour que – dans les quatre cas – elle effectue ce copier/coller. Les quatre temps de calcul se sont alors tous situés entre 1’12" et 1’18".
Remarque – Excel calcule deux fois plus rapidement cette formule collée 4.999 fois que la même formule entrée dans les 5.000 cellules. En effet, quand une formule est collée, Excel ne « l’entre » pas dans chaque cellule mais marque ces cellules comme ayant « la même formule que … ». Et cela a un impact sensible sur le temps de calcul.
J’ai ensuite modifié la macro pour que – dans les quatre cas – elle effectue le copier/coller, et ce sur 10.000 cellules. Voici le résultat des courses :
Formule représentée en H2 : 2’38".
Formule représentée en H3 : 2’37".
Formule représentée en H4 : 2’29".
Formule représentée en H5 : 2’29".
Sans ce copier/coller, H2 prend 5’17", H3 prend 5’12" et H4 prend 4’58".
En conclusion, la troisième formule (avec le SommeProd() et « -- ») et la formule matricielle sont un peu plus rapides – mais seulement d’environ 5% - que les deux premières formules.
Fort de ce que m’avaient dit des contacts chez Microsoft, j’avais déclaré que la formule matricielle était moins performante en vitesse que les solutions à base de SommeProd().
J’ai fait un premier essai comparatif, avec 5.000 fois la formule, appliquée à une liste de 10.000 combinaisons aléatoires de poste et département.
Je suis parvenu, pour les trois premières formules, à des temps entre 2’30" et 2’40". Et, oh surprise, à un temps de 1’12" pour la formule matricielle.
Là, j’étais quand même surpris que cette dernière soit deux fois plus rapide. J’ai alors réalisé que, pour les trois premières formules, ma macro entrait la formule directement dans les 5.000 cellules alors que – pour éviter que la colonne entière ait une formule matricielle unique – ma macro entrait la formule matricielle en G2, puis la copiait et la collait dans le reste de la colonne.
J’ai donc modifié ma macro pour que – dans les quatre cas – elle effectue ce copier/coller. Les quatre temps de calcul se sont alors tous situés entre 1’12" et 1’18".
Remarque – Excel calcule deux fois plus rapidement cette formule collée 4.999 fois que la même formule entrée dans les 5.000 cellules. En effet, quand une formule est collée, Excel ne « l’entre » pas dans chaque cellule mais marque ces cellules comme ayant « la même formule que … ». Et cela a un impact sensible sur le temps de calcul.
J’ai ensuite modifié la macro pour que – dans les quatre cas – elle effectue le copier/coller, et ce sur 10.000 cellules. Voici le résultat des courses :
Formule représentée en H2 : 2’38".
Formule représentée en H3 : 2’37".
Formule représentée en H4 : 2’29".
Formule représentée en H5 : 2’29".
Sans ce copier/coller, H2 prend 5’17", H3 prend 5’12" et H4 prend 4’58".
En conclusion, la troisième formule (avec le SommeProd() et « -- ») et la formule matricielle sont un peu plus rapides – mais seulement d’environ 5% - que les deux premières formules.
rédigé par Hervé Thiriez |
2 commentaire(s)
2 Commentaire(s):
Très très intéressant, merci beaucoup. Je peux conserver mes formules matricielles, beaucoup plus explicites à mon avis.
Ai-je le même problème si je sélectionne une zone, je saisie ma formule dans la première cellule et je valide en faisant Ctrl-Entrée ?
J'imagine que oui puisque il n'y a pas de copier/coller.
By
Anonyme, sur 9:56 AM
Intéressant en effet.
J'obtiens des résultats différents:
L'ordre des résultats est conservé, mais avec des écarts beaucoup plus significatifs:
pour 5000 lignes:
formule matricielle (n°4): 31s
formule sommeprod -- (n°3): 33s (+6%)
formules sommeprod *1 (n°1 & 2): 37s (+19%)
+19% c'est beaucoup comme écart.
Ces temps correspondent exclusivement aux temps de calcul. Les écarts en % sont constants si je multiplie les calculs par 2 (10000 lignes).
Sont exclus des temps mesurés le remplissage des cellules
Sauf erreur, j'en conclus:
i/ la formule matricielle (la 4) est définitivement plus performante
ii/ les formules avec les *1 sont 20% plus lentes et doivent être évitées.
VBE:
Sub Mesure_Temps()
Application.Calculation = xlCalculationManual
Dim debut As Date, fin As Date, Duree As Date
Range("G10:J5000").ClearContents
Range("B2") = "c"
Range("G2").Copy
Range("G10:G5000").PasteSpecial
Application.CutCopyMode = False
debut = Time
Range("G10:G5000").Calculate
fin = Time
Duree = fin - debut
'RESULTAT EN SECONDES EN CELLULE H2
Range("H2").Value = Duree * 86400
'... idem pour les 3 autres formules
Range("G10:k10000").ClearContents
Application.Calculation = xlCalculationAutomatic
End Sub
Ici, seule la rapidité de calcul est prise en compte.
Maintenant, si au lieu d'un copy/paste on utilise un remplissage de type :
Range("J10:J5000").FormulaArray = "=SUM((R2C2:R10000C2=R5C5)*(R2C3:R10000C3=R5C6))"
debut = Time
Range("B2") = "m"
Range("j:j").Calculate
fin = Time
Duree = fin - debut
Range("k4").Value = Duree * 86400
ce qui revient à n'utiliser qu'une seule très grosse matrice de 5000 lignes (4990 dans mon cas) alors les résultats sont très différents : ils sont beaucoup plus rapides:
19 secondes pour le calcul des 5000 lignes au lieu de 31 secondes, soit 39% d'amélioration par rapport au calcul des 5000 formules matricielles recopiées séparément.
MAIS je note également avec cette grosse formule matricielle de 5000 lignes une instabilité de mon excel qui ne m'inspire rien de bon.
Ma conclusion: dans tous les cas, la formule matricielle est beaucoup plus performante en terme de temps de calcul:
5% de gain pour calculer 5000 formules matricielle simples
40% de gain pour calculer 1 formule matricielle de 5000 lignes
à suivre ...
By
Anonyme, sur 10:44 PM
Enregistrer un commentaire
<< Accueil