Partager via

Performance Excel: Amélioration des performances de calcul

  • Article

S’applique à: Excel |Excel 2013 | Excel 2016 | VBA

La « grande grille » d’1 million de lignes et 16 000 colonnes et les nombreuses autres augmentations de limites apportées à Office Excel 2016 permettent d’accroître sensiblement la taille des feuilles de calcul qu’il est possible de créer, comparé aux versions antérieures d’Excel. Une feuille de calcul dans Excel peut contenir jusqu’à 1000 fois plus de cellules que dans les versions antérieures.

Dans les versions antérieures d’Excel, les feuilles pouvaient être longues à calculer et plus la taille de la feuille augmentait, plus le calcul demandait de temps. Avec l’introduction de la « grande grille » dans Excel 2007, les performances sont primordiales. La lenteur de calcul et les tâches de manipulation des données telles que le tri et le filtrage font qu’il est plus difficile pour les utilisateurs de se concentrer sur la tâche à effectuer, et un manque de concentration peut augmenter le risque d’erreur.

Les dernières versions d’Excel offrent plusieurs nouvelles fonctionnalités destinées à faciliter la gestion de cette augmentation de capacité, telles que la possibilité d’utiliser plusieurs processeurs simultanément pour les calculs et les opérations d’ensembles de données courantes telles que l’actualisation, le tri et l’ouverture des classeurs. Le calcul multithread peut réduire considérablement la durée de calcul des feuilles. Cependant, le facteur qui influence le plus la vitesse de calcul d’Excel reste la manière dont votre feuille de calcul est conçue et créée.

Vous pouvez modifier la plupart des feuilles de calcul qui effectuent des calculs au ralenti pour calculer des dizaines, des centaines ou des milliers de fois plus rapidement. Vous pouvez accélérer les calculs en identifiant, mesurant puis améliorant les obstacles au calcul dans vos feuilles.

Importance de la vitesse de calcul

Une vitesse de calcul médiocre affecte la productivité et augmente les erreurs de l’utilisateur. La productivité des utilisateurs et leur capacité à se focaliser sur une tâche diminuent à mesure que le temps de réponse s’allonge.

Excel utilise deux principaux modes de calcul qui vous permettent de contrôler quand les calculs sont exécutés :

  • Calcul automatique : Les formules sont recalculées automatiquement lorsque vous apportez une modification.

  • Calcul manuel : Les formules sont recalculées uniquement lorsque vous le demandez (par exemple en appuyant sur la touche F9).

Pour les durées de calcul inférieures à un dixième de seconde, les utilisateurs ont l’impression que le système répond instantanément. Ils peuvent utiliser le calcul automatique même lorsqu’ils entrent des données.

Entre un dixième de seconde et une seconde, le cheminement de pensée des utilisateurs n’est pas interrompu, bien qu’ils puissent remarquer le délai de réponse.

Quand la durée de calcul augmente (normalement, entre 1 et 10 secondes), les utilisateurs doivent basculer en mode de calcul manuel lorsqu’ils entrent des données. Les erreurs et le niveau d’agacement des utilisateurs commencent à augmenter, en particulier pour les tâches répétitives, et il leur devient difficile de rester concentrés sur leur tâche.

Avec des durées de calcul supérieures à 10 secondes, les utilisateurs perdent patience et passent généralement à d’autres tâches pendant qu’ils patientent. Cela peut poser problème lorsque le calcul fait partie d’une séquence de tâches et que l’utilisateur en perd le fil.

Présentation des méthodes de calcul dans Excel

Pour améliorer les performances de calcul dans Excel, vous devez comprendre les deux méthodes de calcul disponibles et savoir comment les contrôler.

Calcul complet et dépendances de recalculs

Le moteur de recalcul intelligent dans Excel tente de minimiser les durées de calcul en effectuant un suivi permanent des antécédents et des dépendances pour chaque formule (les cellules référencées par la formule) et de toute modification apportée depuis le dernier calcul. Lors du recalcul suivant, Excel recalcule uniquement les éléments suivants :

  • cellules, formules, valeurs ou nom ayant changé ou étant marqués comme nécessitant un recalcul ;

  • cellules qui dépendent d’autres cellules, formules, noms ou valeurs qui nécessitent un recalcul ;

  • fonctions volatiles et mises en forme conditionnelles visibles.

Excel continue de calculer les cellules qui dépendent de cellules calculées précédemment même si la valeur de la cellule calculée précédemment ne change pas lorsqu’elle est calculée.

Étant donné que dans la plupart des cas vous ne modifiez qu’une partie des données d’entrée ou quelques formules entre les calculs, ce recalcul intelligent ne prend généralement qu’une fraction du temps qui serait nécessaire pour effectuer un calcul complet de toutes les formules.

En mode de calcul manuel, vous pouvez déclencher ce recalcul intelligent en appuyant sur F9. Vous pouvez forcer un calcul complet de toutes les formules en appuyant sur Ctrl+Alt+F9 ou une recréation complète de toutes les dépendances et un calcul complet en appuyant sur Maj+Ctrl+Alt+F9.

Processus de calcul

Les formules Excel qui font référence à d’autres cellules peuvent être placées avant ou après les cellules référencées (référencement avant ou arrière). Ceci est dû au fait qu’Excel ne calcule pas les cellules dans un ordre fixe, ni par ligne ou par colonne. Au lieu de cela, Excel détermine la séquence de calcul de façon dynamique en se basant sur une liste de toutes les formules à calculer (la chaîne de calcul) et sur les informations de dépendances relatives à chaque formule.

Excel suit plusieurs phases de calcul distinctes :

  1. Générer la chaîne de calcul initiale et déterminer où commencer le calcul. Cette phase se produit lors du chargement du classeur en mémoire.

  2. Effectuer le suivi des dépendances, marquer les cellules comme non calculées et mettre à jour la chaîne de calcul. Ces opérations sont exécutées à chaque entrée ou modification de cellule, même en mode de calcul manuel. Normalement cela se déroule si vite que vous ne le remarquez pas, mais dans les cas complexes, le temps de réponse peut être long.

  3. Calculer toutes les formules. Dans le cadre du processus de calcul, Excel réordonne et restructure la chaîne de calcul afin d’optimiser les recalculs ultérieurs.

  4. Mettre à jour les parties visibles des fenêtres Excel.

La troisième phase s’exécute à chaque calcul ou recalcul. Excel tente de calculer tour à tour chaque formule de la chaîne de calcul, mais si une formule dépend d’une ou plusieurs formules n’ayant pas encore été calculées, cette formule est descendue dans la chaîne afin d’être recalculée ultérieurement. Cela signifie qu’une formule peut être calculée plusieurs fois par recalcul.

Le deuxième calcul d’un classeur s’effectue beaucoup plus rapidement que le premier. Cela se produit pour plusieurs raisons :

  • Excel recalcule en général uniquement les cellules qui ont changé, ainsi que leurs dépendances ;

  • Excel stocke et réutilise la séquence de calcul la plus récente, afin de gagner du temps lors de la détermination de la séquence de calcul ;

  • avec les ordinateurs dotés de plusieurs cœurs, Excel 2007 tente d’optimiser la façon dont les calculs sont répartis sur les cœurs en fonction des résultats du calcul précédent ;

  • dans une session Excel, Windows et Excel mettent en cache les données et programmes récemment utilisés afin d’en accélérer l’accès.

Calcul de classeurs, feuilles de calcul et plages

Vous pouvez contrôler ce qui est calculé à l’aide de différentes méthodes de calcul Excel.

Calculer tous les classeurs ouverts

Chaque recalcul et calcul complet calcule tous les classeurs ouverts actuellement et résout toutes les dépendances dans et entre les classeurs et feuilles de calcul. Toutes les cellules non-calculées (sales) sont réinitialisées.

Calculer les feuilles de calcul sélectionnées

Vous pouvez également recalculer uniquement les feuilles de calcul sélectionnées à l’aide de Maj+F9. Cela résout les dépendances de la feuille de calcul et réinitialise toutes les cellules (sale) précédemment non calculées.

Dans les versions précédentes d’Excel, le comportement était différent et sale cellules n’étaient pas définies comme calculées une fois le calcul terminé. Si les fonctions définies par l’utilisateur s’appuient sur ce comportement, ces fonctions doivent être rendues volatiles à la place, comme expliqué dans la section Fonctions volatiles de cet article.

Calculer une plage de cellules

Excel autorise également le calcul d’une plage de cellules à l’aide des méthodes VBA (Visual Basic for Applications) Range.CalculateRowMajorOrder et Range.Calculate :

  • Range.CalculateRowMajorOrder calcule la plage de gauche à droite et de bas en haut et ignore toutes les dépendances.

  • Range.Calculate calcule la plage en apportant une résolution à toutes les dépendances dans la plage.

CalculateRowMajorOrder ne résolvant pas les dépendances dans la plage calculée, elle est en règle générale beaucoup plus rapide que Range.Calculate. Toutefois, il faut l’utiliser avec précaution, car il est possible qu’elle ne donne pas le même résultat que Range.Calculate.

Range.Calculate est l’un des outils les plus utiles dans Excel pour l’optimisation des performances car elle permet de déterminer et de comparer les vitesses de calcul de différentes formules.

Pour plus d’informations, voir Performances d'Excel : Améliorations en matière de performances et de limites.

Fonctions volatiles

Une fonction volatile est toujours recalculée à chaque calcul, même s’il semble qu’aucun de ses antécédents n’ait changé. L’utilisation de nombreuses fonctions volatiles ralentit chaque recalcul mais n’a aucun impact sur les calculs complets. Vous pouvez déterminer une fonction définie par l’utilisateur en tant que volatile en incluant Application.Volatile dans le code de fonction.

Certaines des fonctions intégrées dans Excel sont évidemment volatiles :RAND(), NOW(), TODAY(). D’autres ne sont pas aussi visiblement volatiles : OFFSET(), CELL(), INDIRECT(), INFO().

Certaines fonctions ont été préalablement documentées comme volatiles, mais en fait ne le sont pas : INDEX(), ROWS(), COLUMNS(), AREAS().

Actions volatiles

Les actions volatiles sont des actions qui déclenchent un recalcul. Par exemple :

  • un clic sur un séparateur de ligne ou de colonne en mode de calcul automatique ;
  • l’insertion ou la suppression de lignes, colonnes ou cellules sur une feuille ;
  • l’ajout, la modification ou la suppression de noms définis ;
  • l’affectation d’un nouveau nom à une feuille de calcul ou la modification de la position d’une feuille en mode de calcul automatique ;
  • le filtrage, masquage ou démasquage des lignes ;
  • l’ouverture d’un classeur en mode automatique ; si le dernier calcul du classeur a été effectué par une version différente d’Excel, l’ouverture du classeur provoque généralement un calcul complet ;
  • l’enregistrement d’un classeur en mode manuel si l’optionCalculate before save est sélectionnée.

Circonstances d’évaluation de nom et de formule

Une formule ou partie d’une formule est évaluée (calculée) immédiatement, même en mode de calcul manuel, lorsque vous effectuez l’une des actions suivantes :

  • vous entrez ou modifiez la formule ;
  • Entrez ou modifiez la formule à l’aide de l’Assistant Fonction.
  • Entrez la formule comme un argument de l’Assistant Fonction.
  • Sélectionnez la formule dans la barre de formule et appuyez sur F9 (appuyez sur Échap pour annuler et rétablir la formule) ou cliquez sur Évaluation de formule.

Une formule est marquée comme non calculée lorsqu’elle fait référence à (dépend de) une cellule ou formule pour laquelle l’une des conditions suivantes est remplie :

  • elle a été entrée ;
  • elle a changé ;
  • Elle est dans la liste Filtre automatique et la liste déroulante de critères a été activée.
  • Il est marqué comme non calculé.

Une formule marquée comme non calculée est évaluée lorsque la feuille de calcul, le classeur ou l’instance d’Excel qui la contient est calculée ou recalculée.

Les circonstances qui provoquent l’évaluation d’un nom défini diffèrent de celles pour une formule dans une cellule :

  • Un nom défini est évalué chaque fois qu’une formule qui y fait référence est évaluée ; l’utilisation d’un nom dans plusieurs formules peut donc provoquer plusieurs évaluations de ce nom.
  • Les noms auxquels aucune formule ne fait référence ne sont pas calculés, même par un calcul complet.

Tableaux de données

Les tables de données Excel (onglet >Données groupe >Outils de données Groupe What-If Analysis>Data Data Table) ne doivent pas être confondues avec la fonctionnalité de tableau (onglet Accueil>groupe Styles>Format en tant que tableau ou Insertion onglet >Tables groupe >Tableau). Les tables de données Excel effectuent plusieurs recalculs du classeur, chacun piloté par les différentes valeurs de la table. Excel calcule d’abord le classeur normalement. Pour chaque paire de valeurs de ligne et colonne, il substitue alors les valeurs, effectue un recalcul mono-threaded, puis stocke les résultats dans la table de données.

Le recalcul d’une table de données toujours utilise toujours un seul processeur.

Les tables de données représentent un moyen pratique pour calculer plusieurs variantes et afficher et comparer les résultats des variantes. Utilisez l’option de calcul Automatique sauf dans les tables de données pour faire en sorte qu’Excel ne déclenche pas automatiquement les calculs multiples lors de chaque calcul mais calcule tout de même toutes les formules dépendantes à l’exception des tables.

Contrôle des options de calcul

Excel propose toute une gamme d’options qui vous permettent de contrôler la façon dont il exécute les calculs. Vous pouvez changer les options les plus fréquemment utilisées dans Excel à l’aide du groupe Calcul sous l’onglet Formules dans le Ruban.

Figure 1. Groupe Calcul sous l’onglet Formules

Options de calcul sous l’onglet Formules

Pour afficher plus d’options de calcul Excel, dans l’onglet Fichier , cliquez sur Options. Dans la boîte de dialogue Options Excel, cliquez sur l’onglet Formules.

Figure 2. Options de calcul sous l’onglet Formules dans Options Excel

Options de calcul en mode Backstage

De nombreuses options de calcul (Automatique, Automatique sauf dans les tables de données, Manuel, Recalculer le classeur avant de l’enregistrer) et les paramètres d’itération (Activer le calcul itératif, Nb maximal d’itérations, Écart maximal) opèrent au niveau de l’application plutôt qu’au niveau du classeur (ils sont identiques pour tous les classeurs ouverts).

Pour afficher les options de calcul avancées, cliquez sur l’onglet Fichier, puis sur Options. Dans la boîte de dialogue Options Excel, cliquez sur Avancées. Sous la section Formules, définissez les options de calcul.

Figure 3. Options de calcul avancées

Options de calcul avancé en mode Backstage

Lorsque vous démarrez Excel, ou lorsqu’il est en cours d’exécution sans classeur ouvert, le mode de calcul et les paramètres d’itération initiaux sont définis à partir du premier classeur que vous ouvrez (autre qu’un modèle ou une macro complémentaire). Cela signifie que les paramètres de calcul dans les classeurs ouverts ultérieurement sont ignorés bien qu’il soit possible, évidemment, de modifier manuellement les paramètres dans Excel à tout moment. Lorsque vous enregistrez un classeur, les paramètres actuels de calcul sont stockés dans le classeur.

Calcul automatique

Le mode Calcul automatique signifie qu’Excel recalcule automatiquement tous les classeurs ouverts lors de chaque modification et lorsque vous ouvrez un classeur. En règle générale, quand vous ouvrez un classeur en mode automatique et qu’Excel effectue un recalcul, celui-ci est invisible car rien n’a changé depuis l’enregistrement du classeur.

Il se peut que vous remarquiez ce calcul lorsque vous ouvrez un classeur dans une version d’Excel ultérieure à celle utilisée lors du dernier calcul du classeur (par exemple, Excel 2016 par rapport à Excel 2013). Les moteurs de calcul d’Excel étant différents, Excel effectue un calcul complet lorsqu’il ouvre un classeur qui a été enregistré à l’aide d’une version antérieure d’Excel.

Calcul manuel

Le mode Calcul manuel signifie qu’Excel recalcule tous les classeurs ouverts uniquement lorsque vous le demandez en appuyant sur F9 ou Ctrl+Alt+F9 ou lorsque vous enregistrez un classeur. Pour les classeurs dont le recalcul nécessite plus d’une fraction de seconde, vous devez sélectionner le mode de calcul manuel afin de ne pas observer un délai lorsque vous apportez des modifications.

Excel indique quand un classeur en mode manuel doit être recalculé en affichant Calculer dans la barre d’état. La barre d’état affiche également Calculer si votre classeur contient des références circulaires et l’option itération est sélectionnée.

Paramètres d’itération

Si votre classeur contient des références circulaires intentionnelles, les paramètres d’itération vous permettent de contrôler le nombre maximal de recalculs (itérations) du classeur et les critères de convergence (modification maximale : quand arrêter). Désactivez l’option d’itération de sorte que, en cas de référence circulaire accidentelle, Excel vous avertisse et ne tente pas de la résoudre.

ForceFullCalculation, propriété de classeur

Lorsque vous définissez cette propriété de classeur comme vraie, le recalcul intelligent d’Excel est désactivé et chaque recalcul recalcule toutes les formules de tous les classeurs ouverts. Pour certains classeurs complexes, le temps nécessaire pour créer et maintenir les arbres de dépendance nécessaires pour le recalcul intelligent est supérieur au temps économisé par le recalcul intelligent.

Si votre classeur prend beaucoup trop de temps pour ouvrir ou si apporter de petites modifications prend beaucoup de temps même en mode de calcul manuel, il peut être utile d’essayer ForceFullCalculation.

Calculer apparaît dans la barre d’état si la propriété du classeur ForceFullCalculation a été définie comme vraie.

Vous pouvez contrôler ce paramètre à l’aide de Visual Basic Editor (Alt + F11) en sélectionnant ThisWorkbook dans l’Explorateur de projets (Ctrl + R) et en affichant la Fenêtre de propriétés (F4).

Figure 4. Définir la propriété Workbook.ForceFullCalculation

Définir ForceFullCalculation

Accélération des calculs dans les classeurs

Utilisez les étapes et les méthodes suivantes pour accélérer le calcul de vos classeurs.

Vitesse de processeurs et cœurs multiples

Pour la plupart des versions d’Excel, un processeur plus rapide permet bien entendu à Excel d’effectuer les calculs plus rapidement. Le moteur de calcul multithread introduit dans Excel 2007 permet à Excel de tirer pleinement parti des systèmes à plusieurs processeurs et de procurer des gains de performance conséquents avec la plupart des classeurs.

Pour la plupart des classeurs plus volumineux, les gains de performance de calcul provenant de processeurs multiples évoluent de façon presque linéaire avec le nombre de processeurs physiques. Cependant, l'hyperthreading des processeurs physiques ne produit qu'un faible gain de performance.

Pour plus d’informations, voir Performances d'Excel : Améliorations en matière de performances et de limites.

Mémoire RAM

La pagination dans un fichier de pagination en mémoire virtuelle est lente. Vous devez disposer de suffisamment de RAM physique pour le système d’exploitation, pour Excel et pour vos classeurs. Si vous avez une activité de disque dur plus qu’occasionnelle durant le calcul et que vous n’exécutez pas de fonctions définies par l’utilisateur qui déclenchent une activité du disque, vous avez besoin de davantage de RAM.

Comme mentionné plus haut, les versions récentes d’Excel peuvent tirer parti d’une quantité élevée de mémoire et les versions 32 bits d’Excel 2007 et d’Excel 2010 peuvent gérer un classeur ou une combinaison de classeurs utilisant jusqu’à 2 Go de mémoire.

Les versions 32 bits d’Excel 2013 et Excel 2016 qui utilisent la fonctionnalité Prise en charge des grandes adresses (LAA) peuvent utiliser jusqu’à 3 ou 4 Go de mémoire, selon la version de Windows installée. La version 64 bits d’Excel peut gérer des classeurs plus volumineux. Pour plus d’informations, voir la section « Jeux de données volumineux, LAA et la version 64 bits d’Excel » dans Excel performances : améliorations des performances et limites.

On estime généralement qu’il faut disposer de suffisamment de RAM pour contenir le plus grand ensemble de classeurs qui doivent être ouverts simultanément, plus 1 Go ou 2 Go pour Excel et le système d’exploitation, plus de la RAM supplémentaire pour toute autre application en cours d’exécution.

Mesure de la durée de calcul

Pourque les classeurs calculent plus rapidement, vous devez pouvoir mesurez avec précision le temps de calcul. Vous avez besoin d’un minuteur qui est plus rapide et plus précis que la fonction Time de VBA. La fonction MICROTIMER() illustrée dans l’exemple de code suivant utilise les appels d’API Windows vers l’horloge système haute résolution. Elle est capable de mesurer des intervalles de temps à quelques microsecondes près. Sachez que les durées mesurées ne se répètent généralement pas exactement, pour deux raisons : d’une part, Windows est un système d’exploitation multitâche et, d’autre part, le deuxième calcul est souvent plus rapide que le premier. Pour obtenir de meilleurs résultats, chronométrez les tâches de calcul à plusieurs reprises et faites une moyenne des résultats.

Pour plus d’informations sur comment Visual Basic Editor peut considérablement affecter les performances des fonctions VBA définies par l’utilisateur, voir la section « Fonctions VBA définies par l’utilisateur plus rapides » dans Excel performances : conseils pour optimiser les obstacles aux performances.

#If VBA7 Then
    Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _
        "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _
        "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
    Private Declare Function getFrequency Lib "kernel32" Alias _
        "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare Function getTickCount Lib "kernel32" Alias _
        "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If
Function MicroTimer() As Double
'

' Returns seconds.
    Dim cyTicks1 As Currency
    Static cyFrequency As Currency
    '
    MicroTimer = 0

' Get frequency.
    If cyFrequency = 0 Then getFrequency cyFrequency

' Get ticks.
    getTickCount cyTicks1                            

' Seconds
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency 
End Function

Pour mesurer la durée de calcul, vous devez appeler la méthode de calcul appropriée. Ces sous-routines fournissent la durée de calcul pour une plage, la durée de recalcul pour une feuille ou tous les classeurs ouverts, ou la durée de calcul complète pour tous les calculs ouverts.

Copiez toutes ces sous-routines et fonctions dans un module VBA standard. Pour ouvrir l’éditeur VBA, appuyez sur Alt+F11. Dans le menu Insérer, sélectionnez Module, puis copiez le code dans le module.


Sub RangeTimer()
    DoCalcTimer 1
End Sub
Sub SheetTimer()
    DoCalcTimer 2
End Sub
Sub RecalcTimer()
    DoCalcTimer 3
End Sub
Sub FullcalcTimer()
    DoCalcTimer 4
End Sub

Sub DoCalcTimer(jMethod As Long)
    Dim dTime As Double
    Dim dOvhd As Double
    Dim oRng As Range
    Dim oCell As Range
    Dim oArrRange As Range
    Dim sCalcType As String
    Dim lCalcSave As Long
    Dim bIterSave As Boolean
    '
    On Error GoTo Errhandl

' Initialize
    dTime = MicroTimer              

    ' Save calculation settings.
    lCalcSave = Application.Calculation
    bIterSave = Application.Iteration
    If Application.Calculation <> xlCalculationManual Then
        Application.Calculation = xlCalculationManual
    End If
    Select Case jMethod
    Case 1

        ' Switch off iteration.

        If Application.Iteration <> False Then
            Application.Iteration = False
        End if
        
        ' Max is used range.

        If Selection.Count > 1000 Then
            Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
        Else
            Set oRng = Selection
        End If

        ' Include array cells outside selection.

        For Each oCell In oRng
            If oCell.HasArray Then
                If oArrRange Is Nothing Then 
                    Set oArrRange = oCell.CurrentArray
                End If
                If Intersect(oCell, oArrRange) Is Nothing Then
                    Set oArrRange = oCell.CurrentArray
                    Set oRng = Union(oRng, oArrRange)
                End If
            End If
        Next oCell

        sCalcType = "Calculate " & CStr(oRng.Count) & _
            " Cell(s) in Selected Range: "
    Case 2
        sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": "
    Case 3
        sCalcType = "Recalculate open workbooks: "
    Case 4
        sCalcType = "Full Calculate open workbooks: "
    End Select

' Get start time.
    dTime = MicroTimer
    Select Case jMethod
    Case 1
        If Val(Application.Version) >= 12 Then
            oRng.CalculateRowMajorOrder
        Else
            oRng.Calculate
        End If
    Case 2
        ActiveSheet.Calculate
    Case 3
        Application.Calculate
    Case 4
        Application.CalculateFull
    End Select

' Calculate duration.
    dTime = MicroTimer - dTime
    On Error GoTo 0

    dTime = Round(dTime, 5)
    MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _
        vbOKOnly + vbInformation, "CalcTimer"

Finish:

    ' Restore calculation settings.
    If Application.Calculation <> lCalcSave Then
         Application.Calculation = lCalcSave
    End If
    If Application.Iteration <> bIterSave Then
         Application.Iteration = bIterSave
    End If
    Exit Sub
Errhandl:
    On Error GoTo 0
    MsgBox "Unable to Calculate " & sCalcType, _
        vbOKOnly + vbCritical, "CalcTimer"
    GoTo Finish
End Sub

Pour exécuter les sous-routines dans Excel, appuyez sur Alt + F8. Sélectionnez la sous-routine souhaitée, puis cliquez sur OK.

Figure 5. La fenêtre Macro Excel montrant les minuteurs de calculs

Fenêtre Macro Excel

Identification des obstacles aux calculs et affectation de priorités

La plupart des classeurs qui sont longs à calculer ne présentent que quelques problèmes ou obstacles qui consomment la plupart des ressources de calcul. Si vous ne les avez pas encore identifiés, appliquez la méthode d’exploration décrite dans cette section pour les rechercher. Si vous les avez identifiés, vous devez mesurer la durée de calcul imputable à chaque obstacle de manière à pouvoir les éliminer en affectant un ordre de priorité à votre travail.

Méthode d’exploration pour la recherche des obstacles

Cette approche consiste à chronométrer d’abord le calcul du classeur, puis le calcul de chaque feuille, puis les blocs de formules sur les feuilles dont le calcul est lent. Effectuez chaque étape dans l’ordre et notez les durées de calcul.

Pour rechercher des obstacles à l’aide de la méthode par exploration

  1. Assurez-vous qu’un seul classeur est ouvert et qu’aucune autre tâche n’est en cours d’exécution.

  2. Spécifiez le mode de calcul manuel.

  3. Effectuez une copie de sauvegarde du classeur.

  4. Ouvrez le classeur qui contient les macros de minutage de calcul ou ajoutez ces macros à votre classeur.

  5. Vérifiez la plage utilisée en appuyant sur Ctrl+Fin sur chaque feuille tour à tour.

    Cela permet de voir où se trouve la dernière cellule utilisée. Si elle se trouve au-delà de la position attendue, supprimez les colonnes et lignes superflues et enregistrez le classeur. Pour plus d’informations, voir la section « Minimisation de la plage utilisée » dans Excel performances : conseils concernant les obstacles aux performances.

  6. Exécuter la macro FullCalcTimer.

    Le temps pour calculer toutes les formules dans le classeur est généralement le minutage le plus défavorable.

  7. Exécutez la macro RecalcTimer.

    La durée la plus favorable est généralement constatée en cas de recalcul effectué juste après un calcul complet.

  8. Calculez la volatilité du classeur comme rapport entre la durée de recalcul et la durée de calcul complet.

    Cela permet de savoir dans quelle mesure les formules volatiles et l’évaluation de la chaîne de calcul constituent des obstacles aux performances de calcul.

  9. Activez chaque feuille et exécutez la macro SheetTimer à son tour.

    Étant donné que vous venez de recalculer le classeur, cela vous donne le temps de recalcul pour chaque feuille de calcul. Cela devrait vous aident à déterminer les feuilles de calcul problème.

  10. Exécutez la macro RangeTimer sur des blocs de formules sélectionnés.

  11. Pour chaque feuille de calcul problème, divisez les colonnes ou lignes en un petit nombre de blocs.

  12. Sélectionnez chaque bloc à son tour, puis exécutez la macro RangeTimer sur le bloc.

  13. Si nécessaire, effectuez une analyse plus approfondie en sous-divisant chaque bloc en un plus petit nombre de blocs.

  14. Affectez une priorité aux obstacles.

Accélération des calculs et réduction des obstacles

Ce n’est pas le nombre de formules ou la taille d’un classeur qui consomme le temps de calcul. C’est le nombre de références de cellule et les opérations de calcul et l’efficacité des fonctions utilisées.

La plupart des feuilles de calcul étant créées en copiant des formules qui contiennent une combinaison de références absolues et relatives, elles contiennent souvent un grand nombre de formules qui contiennent des calculs et des références répétés ou dupliqués.

Évitez les méga-formules et formules matricielles complexes. En général, il vaut mieux avoir davantage de lignes et de colonnes et moins de calculs complexes. Cela procure aux fonctionnalités de recalcul intelligent et de calcul multithread dans Excel une meilleure opportunité d’optimiser les calculs. Les formules moins complexes sont également plus faciles à comprendre et à déboguer. Voici quelques règles pour vous aider à accélérer les calculs de classeurs.

Première règle : supprimer les calculs inutiles, dupliqués et répétés

Recherchez les calculs inutiles, dupliqués et répétés et évaluez approximativement le nombre de calculs et de références de cellules nécessaires à Excel pour calculer le résultat de cet obstacle. Réfléchissez à la façon dont vous pourriez parvenir au même résultat avec moins de références et de calculs.

Ceci nécessite en général d’effectuer une ou plusieurs des étapes suivantes :

  • réduire le nombre de références dans chaque formule ;

  • déplacer les calculs répétés vers une ou plusieurs cellules d’assistance, puis faire référence à ces cellules à partir des formules d’origine ;

  • utiliser des lignes et des colonnes supplémentaires afin de calculer et stocker les résultats intermédiaires, de manière à pouvoir les réutiliser dans d’autres formules.

Seconde règle : utiliser la fonction la plus efficace possible

Lorsque vous trouvez un obstacle qui implique une fonction ou des formules matricielles, déterminez s’il existe un moyen plus efficace de parvenir au même résultat. Par exemple :

  • les recherches effectuées sur des données triées peuvent être des dizaines ou des centaines de fois plus efficaces que celles effectuées sur des données non triées ;

  • les fonctions VBA définies par l’utilisateur sont souvent plus lentes que les fonctions intégrées dans Excel (bien que les fonctions VBA soigneusement écrites puissent être rapides) ;

  • réduire le nombre de cellules utilisées dans des fonctions telles que SUM et SUMIF. La durée de calcul est proportionnelle au nombre de cellules utilisées (les cellules inutilisées sont ignorées) ;

  • remplacez les formules matricielles lentes par des fonctions définies par l’utilisateur.

Troisième règle : bien utiliser le recalcul intelligent et le calcul multithread

Mieux vous utiliserez le recalcul intelligent et le calcul multithread dans Excel, moins il y aura de traitement à effectuer chaque fois qu’Excel recalcule. Par conséquent :

  • évitez dans la mesure du possible d’utiliser des fonctions volatiles telles que INDIRECT et OFFSET, à moins qu’elles ne soient beaucoup plus efficaces que les alternatives. (Une fonction OFFSET utilisée de manière efficace est souvent rapide) ;

  • limitez la taille des plages que vous utilisez dans les formules matricielles et les fonctions ;

  • scindez les formules matricielles et les méga-formules dans des colonnes et des lignes d’assistance.

  • Évitez les fonctions à fil unique

    • PHONÉTIQUE
    • CELL lorsque soit l’argument « format » ou « adress » est utilisé
    • INDIRECT
    • GETPIVOTDATA
    • CUBEMEMBER
    • CUBEVALUE
    • CUBEMEMBERPROPERTY
    • CUBESET
    • CUBERANKEDMEMBER
    • CUBEKPIMEMBER
    • CUBESETCOUNT
    • ADRESS où figure le cinquième paramètre (sheet_name)
    • Toute fonction de base de données (DSUM, DAVERAGE, et ainsi de suite) qui fait référence à un tableau croisé dynamique
    • ERROR.TYPE
    • HYPERLINK
    • Fonctions définies par l'utilisateur des modules complémentaires VBA et COM

  • Éviter l’utilisation itérative des tables de données et les références circulaires : ces deux éléments vont toujours calculer en mode thread unique.

Quatrième règle : chronométrer et tester chaque modification

Certaines des modifications que vous apportez peuvent vous surprendre, soit en n’apportant pas la réponse à laquelle vous vous attendiez, soit en donnant lieu à des calculs plus lents que prévu. Il convient donc de chronométrer et de tester chaque modification de la manière suivante :

  1. Chronométrez la formule que vous voulez modifier en utilisant la macro RangeTimer.

  2. Faites les modifications.

  3. Chronométrez la formule modifiée en utilisant la macro RangeTimer.

  4. Vérifiez que la formule modifiée donne encore une réponse correcte.

Exemples de règles

Les sections suivantes contiennent des exemples montrant comment utiliser les règles pour accélérer le calcul.

Sommes de type « Période à ce jour »

Imaginez par exemple que vous devez calculer les sommes Période à ce jour d’une colonne qui contient 2000 nombres. Supposons que la colonne A contient les nombres, et que la colonne B et la colonne C doivent contenir les totaux « période à ce jour ».

Vous pouvez écrire la formule avec SUM, qui est une fonction efficace.

  B1=SUM($A$1:$A1)
  B2=SUM($A$1:$A2)

Figure 6. Exemple de formules SUM Période à ce jour

Exemple de formule de somme « période à ce jour »

Copiez la formule jusqu’à la cellule B2000.

Quelle est la quantité de références de cellules ajoutées par SUM en tout ? B1 fait référence à une cellule et B2000 fait référence à 2000 cellules. La moyenne est de 1000 références par cellule ; par conséquent, la quantité totale de références est de 2 millions. La sélection des 2000 formules et l’utilisation de la macro RangeTimer indiquent que les 2000 formules de la colonne B sont calculées en 80 millisecondes. La plupart de ces calculs sont dupliqués plusieurs fois : SUM additionne A1 à A2 dans chaque formule de B2:B2000.

Vous pouvez éliminer cette duplication si vous écrivez les formules comme suit.

  C1=A1
  C2=C1+A1

Copiez cette formule jusqu’à la cellule C2000.

Maintenant, quelle est la quantité de références de cellules sont ajoutées en tout ? Chaque formule à l’exception de la première formule utilise deux références de cellule. Par conséquent, le total est 1999 * 2 + 1 = 3999. C’est un facteur de 500 références de cellule en moins.

RangeTimer indique que les 2000 formules de la colonne C sont calculées en 3,7 millisecondes, comparé à 80 millisecondes pour la colonne B. Cette modification offre seulement un facteur d’amélioration de 80/3,7=22 au lieu de 500 car il y a une petite surcharge par formule.

Gestion des erreurs

Si vous avez une formule gourmande en ressources de calcul pour laquelle vous souhaitez afficher le chiffre zéro comme résultat en cas d’erreur (ce qui se produit fréquemment avec les recherches de concordance exacte), plusieurs options d’écriture s’offrent à vous.

  • Vous pouvez l’écrire en tant que formule unique, dont l’exécution est lente :

    B1=IF(ISERROR(time expensive formula),0,time expensive formula)

  • Vous pouvez l’écrire en deux formules, ce qui est plus rapide :

    A1=time expensive formula

    B1=IF(ISERROR(A1),0,A1)

  • Ou vous pouvez utiliser la fonction IFERROR, qui est à la fois rapide et simple et rédigée sous la forme d’une formule unique :

    B1=IFERROR(time expensive formula,0)

Compte unique dynamique

Figure 7. Exemple de liste de données pour compte unique

Exemple de données pour compte unique

Si vous avez une liste de 11 000 lignes de données dans la colonne A qui change fréquemment et que vous avez besoin d’une formule qui calcule de manière dynamique le nombre d’éléments uniques dans la liste en ignorant les blancs, voici quelques solutions possibles.

  • Formules de tableaux (utiliser Ctrl + Maj + Entrée) ; RangeTimer indique que cela prend 13,8 secondes.

    {=SUM(IF(LEN(A2:A11000)>0,1/COUNTIF(A2:A11000,A2:A11000)))}

  • SOMMEPROD calcule généralement plus rapidement que la formule matricielle équivalente. Cette formule prend 10,0 secondes, ce qui donne un facteur d’amélioration de 13,8/10,0=1,38. C’est mieux, mais pas assez bon.

    =SUMPRODUCT((A2:A11000<>"")/COUNTIF(A2:A11000,A2:A11000&amp;""))

  • Fonctions définies par l'utilisateur L’exemple de code suivant montre une fonction VBA définie par l’utilisateur qui utilise le fait que l’index pour une collection de sites doit être unique. Pour afficher une explication de certaines techniques qui sont utilisées, consultez la section sur les fonctions définies par l’utilisateur dans la section « Utiliser les fonctions efficacement » dans Excel performances : conseils concernant les obstacles aux performances. La formule =COUNTU(A2:A11000) prend seulement 0,061 secondes. Cela donne un facteur d’amélioration de 13,8/0,061=226.

    Public Function COUNTU(theRange As Range) As Variant
    Dim colUniques As New Collection
    Dim vArr As Variant
    Dim vCell As Variant
    Dim vLcell As Variant
    Dim oRng As Range

    Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
    vArr = oRng
    On Error Resume Next
    For Each vCell In vArr
    If vCell <> vLcell Then
        If Len(CStr(vCell)) > 0 Then
             colUniques.Add vCell, CStr(vCell)
        End If
    End If
    vLcell = vCell
    Next vCell

    COUNTU = colUniques.Count
End Function

  • Ajout d’une colonne de formules Si l’on examine l’exemple de données précédent, on constate qu’il est trié (Excel met 0,5 seconde à trier les 11 000 lignes). On peut exploiter ce fait en ajoutant une colonne de formules qui vérifie si les données de cette ligne sont les mêmes que celles de la précédente. Si elles sont différentes, la formule renvoie la valeur 1. Dans le cas contraire, elle renvoie la valeur 0.

    Ajoutez cette formule à la cellule B12.

      =IF(AND(A2<>"",A2<>A1),1,0)

    Copiez la formule, puis ajoutez une formule afin d’additionner la colonne B.

      =SUM(B2:B11000)

    Un calcul complet de toutes ces formules prend 0,027 seconde. Cela donne un facteur d’amélioration de 13,8/0,027=511.

Conclusion

Excel permet de gérer de manière efficace des feuilles de calcul beaucoup plus grandes et offre des améliorations sensibles en terme de vitesse de calcul, comparé aux versions précédentes. Lors de la conception de grandes feuilles, il convient d’effectuer des choix judicieux afin de ne pas ralentir l’exécution du calcul. Une lenteur de calcul excessive augmente le risque d’erreur car il est alors très difficile pour les utilisateurs de rester concentré pendant que le calcul s’exécute.

L’application d’un ensemble de techniques basiques permet d’accélérer la plupart des feuilles de calcul par un facteur de 10 ou 100. Il est également possible d’appliquer ces techniques lors de la conception et de la création des feuilles afin de garantir une exécution rapide des calculs.

Voir aussi

Assistance et commentaires

Avez-vous des questions ou des commentaires sur Office VBA ou sur cette documentation ? Consultez la rubrique concernant l’assistance pour Office VBA et l’envoi de commentaires afin d’obtenir des instructions pour recevoir une assistance et envoyer vos commentaires.