Monsieur Excel
Pour tout savoir faire sur Excel !

05 avril 2008

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.

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 Anonymous Aigle34, 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 Anonymous Anonyme, sur 10:44 PM  

Enregistrer un commentaire

<< Accueil