Performance Excel: Amélioration des performances de calcul (2023)

  • Article
  • 29 minutes de lecture

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

La «grande grille» d’1million de lignes et 16000 colonnes et les nombreuses autres augmentations de limites apportées à Office Excel2016 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 Excel2007, 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 à 10secondes, 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, Excel2007 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;

    (Video) Améliorer la performance par l'analyse de données de qualité

  • 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 la combinaison de touches Maj+F9. Cette option ne résout pas les dépendances entre les feuilles de calcul et ne réinitialise pas les cellules «sales» telles que calculées.

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’option Calculate 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 tableaux de données Excel (onglet Données > groupe Outils de données > Analyse de scénarios > Table de données) ne doivent pas être confondus avec la fonctionnalité de tableau (onglet Accueil > groupe Styles > Mettre sous forme de tableau, ou onglet Insérer > groupe Tableaux > 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

Performance Excel: Amélioration des performances de calcul (1)

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 dans l’onglet Formules dans Options Excel

Performance Excel: Amélioration des performances de calcul (2)

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.

Figure3. Options de calcul avancé

Performance Excel: Amélioration des performances de calcul (3)

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 à Excel2013). 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.

(Video) TRS & TRG I Taux de Rendement Synthétique et Global I Outil de Lean Manufacturing

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

Performance Excel: Amélioration des performances de calcul (4)

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’à 2Go de mémoire.

Les versions 32 bits d’Excel 2013 et Excel 2016 qui utilisent la fonctionnalité LAA (Large Address Aware) peuvent utiliser jusqu'à 3 ou 4 Go de mémoire, selon la version de Windows qui est 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 1Go ou 2Go 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 IfFunction 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 1End SubSub SheetTimer() DoCalcTimer 2End SubSub RecalcTimer() DoCalcTimer 3End SubSub FullcalcTimer() DoCalcTimer 4End SubSub 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 SubErrhandl: On Error GoTo 0 MsgBox "Unable to Calculate " & sCalcType, _ vbOKOnly + vbCritical, "CalcTimer" GoTo FinishEnd 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 de Macro Excel affichant les minuteries de calcul

Performance Excel: Amélioration des performances de calcul (5)

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.

    (Video) TRS / OEE Help ! Notre mesure c'est n'importe quoi !

  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.

    (Video) L'indicateur de performance TRS / OEE الشرح بالدارجة المغربية

  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 formule de somme «période à ce jour»

Performance Excel: Amélioration des performances de calcul (6)

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 2millions. 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 80millisecondes. 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,7millisecondes, comparé à 80millisecondes 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

Figure7. Exemple de liste de données pour compte unique

Performance Excel: Amélioration des performances de calcul (7)

Si vous avez une liste de 11000 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,0secondes, 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,061secondes. 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.CountEnd 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,5seconde à trier les 11000 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,027seconde. 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

  • Performances d' Excel: Améliorations en matière de performances et de limites
  • Performances d'Excel: Conseils pour optimiser les obstacles aux performances
  • Portail pour développeurs Excel

Assistance et commentaires

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

FAQs

How do I fix Excel performance issues? ›

10 Tips to Handle Slow Excel Spreadsheets
  1. Avoid Volatile Functions (you must).
  2. Use Helper Columns.
  3. Avoid Array Formulas (if you can).
  4. Use Conditional Formatting with Caution.
  5. Use Excel Tables and Named Ranges.
  6. Convert Unused Formulas to Values.
  7. Keep All Referenced Data in One Sheet.

What to do when Excel is stuck calculating? ›

Excel hangs up on loading with "Calculate" showing.
  1. Please update your Office to the latest version to check if problem persists.
  2. If your Office version is 15.41, You may try open file in safe mode.
  3. If it works normally in safe mode please try to disable add-ins by the following steps;
Jan 4, 2018

What is performance analysis in Excel? ›

Analyzing performance data in Excel using array formula and dynamic range name methods. The recording and analysis of performance data is the quintessential spreadsheet application. In every company, probably in every department, performance data is collected in Excel.

How do you calculate performance? ›

Performance is calculated by dividing your Total Count by Run Time and comparing it to your Ideal Run Rate or Performance = (Total Count / Run Time) / Ideal Run Rate.

How do you calculate performance scale? ›

The process of calculating an average numeric rating is to get the total of all section ratings. Then, this total is divided by the number of sections in the performance document. So, if there were four sections in the document, the calculator would divide the total number of numeric ratings by four.

What is the fastest way to improve Excel skills? ›

Following are 10 ways to improve your Excel skills in 2022:
  1. Master keyboard shortcuts. Excel comes with a slew of keyboard shortcuts to help you work faster and more productively. ...
  2. Conditional formatting. ...
  3. Macros. ...
  4. PivotTables. ...
  5. Charts. ...
  6. Filters/ AutoFilter. ...
  7. COUNTIF. ...
  8. Flash Fill.
Jun 14, 2022

What affects Excel performance? ›

The number of records (rows), fields (columns), and formulas can slow down performance considerably. Every time you add new records, then press the Enter key—or use features such as Sort, Format cells, or Insert/Delete Columns or Rows—Excel recalculates all those formulas.

Will increasing RAM improve Excel performance? ›

If Excel needs more RAM than your PC have, yes, adding RAM will speed up your Excel. If not, adding more RAM will not have any noticeable effects.

Why is Excel not performing calculations? ›

When Excel formulas are not updating automatically, most likely it's because the Calculation setting has been changed to Manual instead of Automatic. To fix this, just set the Calculation option to Automatic again.

Why does Excel take so long to recalculate? ›

Poor calculation speed further affects productivity and enhances user error. The reasons why your Excel is calculating formulas slowly may be you are using volatile functions in excess or not keeping all referenced data in one sheet. So, avoid volatile functions to get rid of Excel's slow calculating problem.

How do I speed up calculating 4 processors in Excel? ›

Click "Advanced" on the left side of the Options window and scroll down to the "Formulas" section. Place a check mark next to "Enable multi-threaded calculation," and click the radio button next to "Use all processors on this computer." Click "OK" to close the window.

What are the 3 performance elements? ›

Effective performance management systems typically include the following three broad elements: goal setting, performance review and a performance improvement process.

What are examples of performance data? ›

Examples include reported percent of work physically completed, quality and technical performance measures, start and finish dates of scheduled activities, number of change requests, number of defects, actual costs, actual duration, etc.”

How do you explain performance analysis? ›

What is Performance Analysis? Performance Analysis is a specialised discipline that provides athletes and coaches with objective information that helps them understand performance. This process is underpinned by systematic observation, which provides valid, reliable and detailed information relating to performance.

What is a performance measure example? ›

Examples of Performance Measurements

Tracking the liquidity of funds administered by the finance department. Tracking the amount of inventory maintained by the materials management department. Tracking the amount of scrap produced in the production department.

What are the three 3 examples of performance measurement systems? ›

Examples include balanced scorecards, ISO standards and industry dashboards. Key performance indicators (KPIs) are at the heart of any system of performance measurement and target-setting. When properly used, they are one of the most powerful management tools available to growing businesses.

What are 5 performance measures? ›

There are five specific types of measures that have been identified, defined and will be applied throughout Iowa state government: input, output, efficiency, quality and outcome.

What is a good performance score? ›

This is more often than not a 5 point rating scale (5– Outstanding, 4– Exceeds Expectations, 3- Meets Expectations, 2- Needs Improvement, 1- Unacceptable).

What are the 5 basic Excel skills? ›

Five basic Excel skills to include in your resume
  • Data recording. Basic Excel users must know how to navigate and record data. ...
  • Formulas. There are some formulas you need to know for basic Excel skills: ...
  • Charts and graphs. ...
  • Data organization. ...
  • Pivot tables.
Jul 26, 2021

What are the top 5 soft skills to use Excel? ›

Top five soft skills to excel in your career
  • Communication. In most jobs strong communication skills are vital. ...
  • Problem-solving. Employers highly value people who can resolve issues quickly and effectively. ...
  • Initiative. ...
  • Critical thinking. ...
  • Teamwork.

How do I stop Excel from being stuck? ›

Manually perform safe mode troubleshooting
  1. If you are on Windows 11 or Windows 10, choose Start > All apps > Windows System > Run. Type Excel /safe in the Run box, and then click OK.
  2. If you are on Windows 8 or Windows 8.1, click Run in the Apps menu, type Excel /safe in the Run box, and then click OK.

How do I stop Excel from calculating processors? ›

If uninstalling and reinstalling your Office doesn't work, we advise turning off the automatic calculation by following the steps below:
  1. Click on the File tab.
  2. Select Options.
  3. Choose Formulas.
  4. Under Calculation options, select Manual.
  5. Click on OK.
Nov 22, 2017

Videos

1. Indicateurs de performances Service production - PART1 - مؤشرات قياس الأداء
(youmar consulting)
2. Video N°215 COMMENT DETERMINER UN INDICATEUR DE PERFORMANCE
(FmecaT)
3. 10 Excel Tricks to Improve Performance - Workbook Included
(TeachExcel)
4. Excel #31: Tableau de bord pour visualiser les indicateurs de performance du service commercial.
(Hassan EL BAHI)
5. 10 Indicateurs pour mesurer la performance de votre Supply Chain / Gestion de stock
(AbcSupplyChain FR)
6. Tableau de bord de gestion des performances agricoles | Microsoft Excel sans VBA | Tutoriel vidéo #1
(Other Level’s)
Top Articles
Latest Posts
Article information

Author: Jonah Leffler

Last Updated: 01/19/2023

Views: 6266

Rating: 4.4 / 5 (65 voted)

Reviews: 88% of readers found this page helpful

Author information

Name: Jonah Leffler

Birthday: 1997-10-27

Address: 8987 Kieth Ports, Luettgenland, CT 54657-9808

Phone: +2611128251586

Job: Mining Supervisor

Hobby: Worldbuilding, Electronics, Amateur radio, Skiing, Cycling, Jogging, Taxidermy

Introduction: My name is Jonah Leffler, I am a determined, faithful, outstanding, inexpensive, cheerful, determined, smiling person who loves writing and wants to share my knowledge and understanding with you.