Performances d'Excel 2010: Amélioration des performances de calcul
Résumé : cet article explique
comment améliorer les performances de calcul des classeurs. Il fait
partie d’une série de trois articles axés sur les techniques à votre
disposition pour améliorer les performances dans Excel lors de la
conception et de la création de feuilles de calcul.
Pour plus d’informations sur la façon d’améliorer les performances dans Excel, voir Performances d'Excel 2010 : Améliorations en matière de performances et de limites et Performances d'Excel 2010 : Conseils pour éliminer les obstacles aux performances.
Dernière modification : lundi 11 avril 2011
S’applique à : Excel | Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA
Dans cet article
Vue d’ensemble
Importance de la vitesse de calcul
Présentation des méthodes de calcul dans Excel
Calcul de classeurs, feuilles de calcul et plages
Contrôle des options de calcul
Accélération des calculs dans les classeurs
Identification des obstacles aux calculs et affectation de priorités
Conclusion
À propos des auteurs
Ressources supplémentaires
Sommaire
La « grande grille » d’1 million de lignes et 16 000 colonnes et les nombreuses autres augmentations de limites apportées à Microsoft Office Excel 2007 et Microsoft Excel 2010 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 2007 ou Excel 2010 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.
Excel 2007 et Excel 2010 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 les feuilles de sorte que leurs calculs s’exécutent plusieurs dizaines, centaines, voire milliers de fois plus rapidement. Cet article explique comment accélérer le calcul en identifiant, mesurant puis améliorant les obstacles au calcul dans vos feuilles.
Une faible vitesse de calcul affecte la productivité et accroît le risque d’erreur de la part des utilisateurs. 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.
À mesure que la durée de calcul augmente, les utilisateurs doivent basculer en mode de calcul manuel lorsqu’ils entrent des données.
Entre 1 et 10 secondes, les utilisateurs passeront probablement en mode de calcul manuel. 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 conserver leur cheminement de pensée.
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.
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. Ensuite, 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.
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 normal, vous pouvez déclencher ce recalcul intelligent en appuyant sur la touche 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 :
-
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.
-
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. Elles sont généralement si rapides que l’utilisateur ne les remarque pas.
-
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.
La troisième phase se produit lors de 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 est susceptible d’être recalculée à plusieurs reprises. Dans Excel 2000, une chaîne de calcul distincte est conservée pour chaque feuille de calcul et les feuilles sont calculées par ordre alphabétique. À compter d’Excel 2002, il existe une seule chaîne de calcul globale, ce qui permet de bénéficier de vitesses de calcul plus élevées pour la plupart des classeurs.
Pour plus d’informations et une description détaillée du processus de calcul dans Excel, voir Recalcul dans Microsoft Excel 2002 (éventuellement en anglais).
Le deuxième calcul d’un classeur s’effectue beaucoup plus rapidement que le premier, 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 et Excel 2010 tentent 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, Microsoft Windows et Excel mettent en cache les données et programmes récemment utilisés afin d’en accélérer l’accès.
Vous pouvez contrôler ce qui est calculé en utilisant les 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.
Calculer les feuilles de calcul sélectionnées
Vous pouvez également recalculer uniquement les feuilles de calcul sélectionnées à l’aide de la combinaison de touches Maj+F9. Cela résout uniquement les dépendances intra-feuille pour les feuilles calculées.
Calculer une plage de cellules
Excel autorise également le calcul d’une plage de cellules à l’aide de la méthode VBA (Visual Basic for Applications) Range.Calculate. Le comportement de Range.Calculate a changé de manière significative dans les différentes versions d’Excel :
-
Excel 2000
Range.Calculate calcule de gauche à droite et de bas en haut et ignore toutes les dépendances. -
Excel 2002 et Excel 2003
Range.Calculate résout les dépendances dans la plage calculée. -
Excel 2007 et Excel 2010
Ces versions ont les deux méthodes Range Calculate. Range.Calculate fonctionne comme dans Excel 2002 et Excel 2003, mais Range.CalculateRowMajorOrder fonctionne comme dans Excel 2000. CalculateRowMajorOrder ne résolvant pas les dépendances dans la plage calculée, elle est en règle générale beaucoup plus rapide. Toutefois, il convient de l’utiliser avec prudence car elle peut ne pas donner les mêmes résultats que Range.Calculate. Pour plus d’informations, voir Performances d'Excel 2010 : Améliorations en matière de performances et de limites.Range.Calculate est l’un des outils les plus utiles dans Excel pour l’optimisation des performances car il permet de déterminer et de comparer les vitesses de calcul de différentes formules.
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 rendre une fonction définie par l’utilisateur 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 sont moins évidemment volatiles : OFFSET(), CELL(), INDIRECT(), INFO().
Certaines fonctions ayant été précédemment documentées comme volatiles 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, le masquage ou l’annulation du masquage de lignes dans Excel 2003, Excel 2007 ou Excel 2010 ;
-
l’ouverture d’un classeur en mode de calcul 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’option Recalcul avant l’enregistrement 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 ;
-
vous entrez ou modifiez la formule à l’aide de l’Assistant Fonction ;
-
vous entrez la formule en tant qu’argument dans l’Assistant Fonction ;
-
vous sélectionnez la formule dans la barre de formule et vous appuyez sur F9 (appuyez sur Échap pour annuler et rétablir la formule) ou vous 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 ;
-
elle est marquée comme non calculée.
Une formule marquée comme non calculée est évaluée lorsque la plage, 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.
Tables de données
Il convient de ne pas confondre les tables de données Excel (sous l’onglet Données, dans le groupe Outils de données, cliquez sur Analyse de scénarios, puis sur Table de données) avec la fonctionnalité de tableau (sous l’onglet Accueil, dans le groupe Styles, cliquez sur Mettre sous forme de tableau, ou, sous l’onglet Insertion, dans le groupe Tableaux, cliquez sur 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. Ensuite, pour chaque paire de valeurs de ligne et colonne, il substitue les valeurs, recalcule, puis stocke les résultats dans la table de données.
Les tables de données constituent un moyen commode de calculer plusieurs variantes et d’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.
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 2010 à l’aide du groupe Calcul sous l’onglet Formules dans le Ruban.

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

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é. Sous la section Formules, définissez les options de calcul.

Pour les versions d’Excel antérieures à Excel 2007, sélectionnez Options dans le menu Outils, puis cliquez sur l’onglet Calcul, qui contient toutes les options de calcul.
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 de calcul actuels sont stockés dedans.
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 2007 plutôt qu’Excel 2003). 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 agaçant lorsque vous apportez des modifications.
Excel indique quand un classeur en mode manuel doit être recalculé en affichant Calculer dans la barre d’état. Celle-ci affiche également Calculer si votre classeur contient des références circulaires et que l’option d’itération est activée. Calculer s’affiche également si le nombre de dépendances est trop élevé. Ces limites de dépendances ont été augmentées sensiblement dans Excel 2007 et Excel 2010. Pour plus d’informations, voir Performances d'Excel 2010 : Améliorations en matière de performances et de limites.
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). Il est généralement préférable de désactiver 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.
Cette section illustre les étapes et méthodes à appliquer pour accélérer le calcul des classeurs.
Vitesse de processeurs et nombre de cœurs
Pour la plupart des versions d’Excel, un processeur plus rapide permet bien entendu à Excel d’effectuer les calculs plus rapidement.
À compter d’Excel 2007, il existe de nouvelles fonctionnalités de prise en charge des systèmes à plusieurs processeurs. 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 performances conséquents avec la plupart des classeurs.
Dans Excel 2010, d’autres nouvelles fonctionnalités prennent en charge les systèmes à plusieurs processeurs.
Pour plus d’informations, voir Performances d'Excel 2010 : Améliorations en matière de performances et de limites.
RAM
La pagination vers un fichier d’échange de 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 Excel 2007 et Excel 2010 peuvent gérer un classeur ou une combinaison de classeurs utilisant jusqu’à 2 Go de mémoire de classeur. La version 64 bits d’Excel peut gérer des classeurs encore plus grands. Pour plus d’informations, voir la section « Grands ensembles de données et Excel 64 bits » dans Performances d'Excel 2010 : Améliorations en matière de performances et de 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 256 Mo ou 512 Mo pour Excel et le système d’exploitation, plus de la RAM supplémentaire pour toute autre application en cours d’exécution. Vous pouvez vérifier la quantité de mémoire utilisée par Excel dans le Gestionnaire des tâches de Windows.

Pour plus d’informations, voir la section « Grands ensembles de données et Excel 64 bits » dans Performances d'Excel 2010 : Améliorations en matière de performances et de limites.
Mesure de la durée de calcul
Pour accélérer le calcul des classeurs, vous devez pouvoir mesurer précisément la durée de calcul. Il faut un minuteur plus rapide et plus précis que la fonction VBA Time. La fonction MICROTIMER() illustrée dans l’exemple de code suivant utilise des appels d’API Windows au minuteur 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 la façon dont Visual Basic Editor peut améliorer sensiblement les performances des fonctions VBA définies par l’utilisateur, voir la section Accélération des fonctions VBA définies par l’utilisateur dans Performances d'Excel 2010 : Conseils pour éliminer les obstacles aux performances.
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 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.
Vous devez copier toutes ces sous-routines et fonctions dans un module VBA standard. Pour ouvrir l’éditeur VBA, appuyez sur Alt+F11. Dans le menu Insertion, 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.Calculation = 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 2010, appuyez sur Alt+F8 ou cliquez sur Lire la macro. Sélectionnez la sous-routine souhaitée, puis cliquez sur Exécuter.

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
-
Assurez-vous qu’un seul classeur est ouvert et qu’aucune autre tâche n’est en cours d’exécution.
-
Spécifiez le mode de calcul manuel.
-
Effectuez une copie de sauvegarde du classeur.
-
Ouvrez le classeur qui contient les macros de minutage de calcul ou ajoutez ces macros à votre classeur.
-
Vérifiez la plage utilisée en appuyant sur Ctrl+Fin pour 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 Limitation de la plage utilisée dans Performances d'Excel 2010 : Conseils pour éliminer les obstacles aux performances.
-
Exécutez la macro FullCalcTimer.
La durée de calcul de toutes les formules du classeur est généralement la durée la plus défavorable.
-
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.
-
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.
-
Activez chaque feuille et exécutez la macro SheetTimer sur chacune d’elles.
Étant donné que vous venez de recalculer le classeur, cela vous donne la durée de recalcul pour chaque feuille et devrait vous permettre d’identifier les feuilles à problème.
-
Exécutez la macro RangeTimer sur les blocs de formules sélectionnés.
-
Pour chaque feuille à problème, divisez les colonnes et les lignes en un petit nombre de blocs.
-
Sélectionnez tout à tour chaque bloc et exécutez la macro RangeTimer sur le bloc.
-
Si nécessaire, effectuez une analyse plus approfondie en sous-divisant chaque bloc en un plus petit nombre de blocs.
-
-
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 plus de temps de calcul, mais le nombre de références de cellules et d’opérations de calcul, ainsi que 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 2010 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 ensuite à 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) ;
-
limitez le nombre de cellules utilisées dans les 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 : utiliser le recalcul intelligent à bon escient
Mieux vous utiliserez le recalcul intelligent 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.
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 :
-
Chronométrez la formule que vous souhaitez modifier à l’aide de la macro RangeTimer.
-
Apportez la modification.
-
Chronométrez la formule modifiée à l’adresse de la macro RangeTimer.
-
Vérifiez que la formule modifiée donne encore la réponse correcte.
Exemples de règles
Les sections suivantes fournissent des exemples d’application des règles d’accélération de 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. Supposez que la colonne A contient les nombres et que les colonnes B et C doivent contenir les totaux Période à ce jour.
Vous pourriez écrire une formule faisant appel à SUM, qui est une fonction efficace.
B1=SUM($A$1:$A1) B2=SUM($A$1:$A2)

Copiez ensuite 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 à de nombreuses reprises : SUM ajoute A1 à A2 dans chaque formule de B2:B2000.
Vous pouvez éliminer cette duplication en écrivant les formules comme suit.
C1=A1 C2=C1+A1
Ensuite, copiez cette formule jusqu’à la cellule C2000.
Maintenant, quelle est la quantité de références de cellules ajoutées en tout ? Chaque formule, à l’exception de la première, utilise deux références de cellules. Par conséquent le total est de 1999*2+1=3999, ce qui donne 500 fois moins de références de cellules.
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)
-
À compter d’Excel 2007, 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

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 matricielles
Vous pouvez recourir à la formule matricielle suivante (utilisez Ctrl+Maj+Entrée).
{=SUM(IF(LEN(A2:A11000)>0,1/COUNTIF(A2:A11000,A2:A11000)))}
RangeTimer indique que cette tâche prend 13,8 secondes.
SUMPRODUCT
SUMPRODUCT calcule généralement plus rapidement qu’une formule matricielle équivalente.
=SUMPRODUCT((A2:A11000<>"")/COUNTIF(A2:A11000,A2:A11000&""))
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.
Fonctions définies par l’utilisateur
L’exemple de code suivant illustre une fonction VBA définie par l’utilisateur qui exploite le fait que l’index d’une collection doit être unique. Pour obtenir une explication de certaines techniques utilisées, voir la section relative aux fonctions définies par l’utilisateur dans la section Utilisation efficace des fonctions dans Performances d'Excel 2010 : Conseils pour éliminer les obstacles aux performances.
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
Cette formule, =COUNTU(A2:A11000), ne prend que 0,061 seconde, ce qui donne un facteur d’amélioration de 13,8/0,061=226.
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)
Ensuite, copiez la formule vers le bas, puis ajoutez une formule afin d’additionner la colonne B.
=SUM(B2:B11000)
Un calcul complet de toutes ces formules prend 0,027 seconde, ce qui donne un facteur d’amélioration de 13,8/0,027=511.
Excel 2010 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. 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.
Charles Williams a fondé Decision Models en 1996 afin de proposer des conseils d’experts, des solutions de prise en charge des décisions et des outils basés sur Microsoft Excel et sur les bases de données relationnelles. Charles est l’auteur de FastExcel, ensemble bien connu d’outils de performances et de profileurs de performances pour Excel, ainsi que le co-auteur de Name Manager, utilitaire populaire pour la gestion des noms définis. Pour plus d’informations sur les méthodes et performances de calcul Excel, sur l’utilisation de la mémoire et les fonctions VBA définies par l’utilisateur, voir le site Web Decision Models (éventuellement en anglais).
Cet article technique a été produit en partenariat avec A23 Consulting (éventuellement en anglais).
Allison Bokone, Microsoft Corporation, est programmatrice au sein de l’équipe Office.
Chad Rothschiller, Microsoft Corporation, est responsable de programme au sein de l’équipe Office.