Excel 2010 Amélioration des performances de calcul Le rendement
Une mauvaise vitesse de calcul influe sur la productivité et augmente l'erreur de l'utilisateur. La productivité des utilisateurs et la capacité de se concentrer sur une tâche se détériore avec le temps de réponse allonge.
Excel a deux principaux modes de calcul, qui vous permettent de contrôler lorsque le calcul se produit:
Calcul automatique - Les formules sont recalculés automatiquement lorsque vous effectuez un changement.
Calcul manuel - Les formules sont recalculés uniquement lorsque vous demandez (par exemple, en appuyant sur F9).
Pour les temps de calcul inférieur à environ un dixième de seconde, les utilisateurs estiment que le système répond instantanément. Ils peuvent utiliser le calcul automatique, même quand ils entrent dans les données.
Entre un dixième de seconde et une seconde, les utilisateurs peuvent garder avec succès un train de pensée va, bien qu'ils remarqueront le délai de réponse.
Comme le temps de calcul augmente, les utilisateurs doivent passer au calcul manuel lors de leur entrée de données.
Entre 1 et 10 secondes, les utilisateurs passeront probablement au calcul manuel. erreurs de l'utilisateur et les niveaux de gêne commencent à augmenter, en particulier pour les tâches répétitives, et il devient difficile de maintenir un train de pensée.
Pour les temps de calcul de plus de 10 secondes, les utilisateurs deviennent impatients et passer généralement à d'autres tâches pendant qu'ils attendent. Cela peut causer des problèmes lorsque le calcul est l'une d'une séquence de tâches et l'utilisateur perd la trace.
Pour améliorer les performances de calcul dans Excel, vous devez comprendre les méthodes de calcul disponibles et comment les contrôler.
Calcul complet et dépendances recalcul
Les cellules, les formules, les valeurs ou les noms qui ont changé ou sont marqués comme ayant besoin recalcul.
Les cellules dépendantes d'autres cellules, les formules, les noms ou les valeurs qui ont besoin recalcul.
fonctions volatiles et les formats conditionnels.
Excel continue le calcul des cellules qui dépendent des cellules calculées précédemment, même si la valeur de la cellule précédemment calculée ne change pas quand il est calculé.
Processus de calcul
formules Excel qui font référence à d'autres cellules peuvent être mises avant ou après les cellules référencées (faisant référence vers l'avant ou vers l'arrière référencement). En effet, Excel ne calcule pas les cellules dans un ordre fixe, ou par ligne ou colonne. Au lieu de cela, Excel détermine dynamiquement la séquence de calcul basée sur une liste de toutes les formules de calcul (la chaîne de calcul) et les informations de dépendance sur chaque formule.
Excel a des phases de calcul distinctes:
Construire la chaîne de calcul initiale et déterminer où commencer à calculer. Cette phase se produit lorsque le classeur est chargé en mémoire.
dépendances de piste, des cellules de drapeau comme non calculées, et mettre à jour la chaîne de calcul. Cette phase exécute à chaque entrée de cellule ou de changement, même en mode de calcul manuel. Ordinairement, il exécute si vite que vous ne le remarquez pas.
Calculer toutes les formules. Dans le cadre du processus de calcul, Excel réorganise et restructure la chaîne de calcul pour optimiser les recalculs futures.
La deuxième fois que vous calculez un classeur est souvent nettement plus rapide que la première fois. Cela se produit pour plusieurs raisons:
Excel recalcule habituellement des cellules qui ont changé, et leurs personnes à charge.
Excel stocke et réutilise la séquence de calcul la plus récente afin qu'il puisse sauver la plupart du temps utilisé pour déterminer la séquence de calcul.
Dans une session Excel, les deux cache Microsoft Windows et Excel a récemment utilisé les données et programmes pour un accès plus rapide.
Figure 1. Groupe de calcul sur l'onglet Formules
Figure 2. Options de calcul sur l'onglet Formules dans Options Excel
De nombreuses options de calcul (automatique. Automatique, sauf pour les tables de données. Manuel. Classeur Recalculer avant d'enregistrer) et les paramètres d'itération (Activer le calcul itératif. Itérations maximum. Changement maximum) fonctionnent au niveau de l'application au lieu d'au niveau du classeur (ce sont les mêmes pour tous les classeurs ouverts).
Pour trouver des options de calcul avancées, cliquez sur l'onglet Fichier, puis cliquez sur Options. Dans la boîte de dialogue Options Excel, cliquez sur Avancé. Dans la section Formules options de calcul de jeu.
Figure 3. Options de calcul avancées
Lorsque vous démarrez Excel, ou quand il est en cours d'exécution sans classeurs ouverts, le mode de calcul initial et les paramètres itération sont définis à partir du premier non-modèle, non-add-dans le classeur que vous ouvrez. Cela signifie que les paramètres de calcul dans les classeurs ouverts plus tard sont ignorés, même si, bien sûr, vous pouvez modifier manuellement à tout moment les paramètres dans Excel. Lorsque vous enregistrez un classeur, les paramètres de calcul actuels sont stockés dans le classeur.
Calcul automatique
Mode de calcul automatique signifie que Excel recalcule automatiquement tous les classeurs ouverts à chaque changement, et lorsque vous ouvrez un classeur. Habituellement, lorsque vous ouvrez un classeur en mode automatique recalcule et Excel, vous ne voyez pas le recalcul parce que rien n'a changé depuis le classeur a été enregistré.
Calcul manuel
mode de calcul manuel signifie que Excel recalcule tous les classeurs ouverts uniquement lorsque vous demandez en appuyant sur F9 ou CTRL + ALT + F9, ou lorsque vous enregistrez un classeur. Pour les classeurs qui prennent plus d'une fraction de seconde à recalculer, vous devez régler le calcul en mode manuel pour éviter un retard irritant lorsque vous apportez des modifications.
Paramètres Iteration
Si vous avez des références circulaires intentionnelles dans votre classeur, les paramètres d'itération vous permettent de contrôler le nombre maximal de fois que le classeur est recalculé (itérations) et les critères de convergence (changement maximal: quand arrêter). Habituellement, vous devez désactiver la case d'itération de sorte que si vous avez des références accidentelles circulaires, Excel vous avertit et ne pas essayer de les résoudre.
Vitesse du processeur et plusieurs Cores
Pour la plupart des versions d'Excel, un processeur plus rapide sera, bien sûr, permettre le calcul Excel plus rapide.
Paging à un fichier d'échange virtuel mémoire est lente. Vous devez avoir assez de RAM physique pour le système d'exploitation, Excel et vos classeurs. Si vous avez plus d'activité du disque dur de temps en temps pendant le calcul, et vous n'êtes pas en cours d'exécution des fonctions définies par l'utilisateur qui déclenchent l'activité du disque, vous avez besoin de plus de RAM.
Une règle simple pour le calcul efficace est d'avoir suffisamment de RAM pour tenir le plus grand ensemble de classeurs que vous devez avoir ouvert en même temps, plus de 256 Mo ou 512 Mo pour Excel et le système d'exploitation, plus de RAM supplémentaire pour toutes les autres applications en cours d'exécution. Vous pouvez vérifier la quantité de mémoire que Excel utilise dans le Gestionnaire des tâches de Windows.
Figure 4. Gestionnaire des tâches Windows montrant l'utilisation de la mémoire Excel
Mesure du temps de calcul
La gestion des erreurs
Si vous avez une formule de calcul à forte intensité où vous voulez que le résultat soit affiché comme zéro en cas d'erreur (cela se produit fréquemment avec les recherches de correspondance exacte), vous pouvez écrire de plusieurs façons.
Vous pouvez l'écrire comme une formule unique, qui est lent:
Vous pouvez l'écrire comme deux formules, ce qui est rapide:
Nombre dynamique unique
Figure 7. Liste des Exemple de données pour le nombre unique
Si vous avez une liste de 11.000 lignes de données dans la colonne A, qui change fréquemment, et vous avez besoin d'une formule qui calcule dynamiquement le nombre d'éléments uniques dans la liste, en ignorant des blancs, voici plusieurs solutions possibles.
Formules de tableau
Vous pouvez le faire avec la formule de tableau suivant (CTRL + SHIFT + ENTRER).
RangeTimer indique que cela prend 13,8 secondes.
SUMPRODUCT
SUMPRODUCT calcule généralement plus rapide que d'une formule matricielle équivalente.
Cette formule prend 10,0 secondes. Cela donne un facteur d'amélioration de 13,8 / 10,0 = 1,38. ce qui est mieux, mais pas assez bon.
Fonctions définies par l'utilisateur
Cette formule, = COUNTU (A2: A11000). prend seulement 0,061 secondes. Cela donne un facteur d'amélioration de 13,8 / 0,061 = 226.
Ajout d'une colonne de formules
Si vous regardez l'exemple précédent des données, vous pouvez voir qu'il est triée (Excel prend 0,5 secondes pour trier les 11.000 lignes). Vous pouvez exploiter cette situation en ajoutant une colonne de formules qui vérifie si les données de cette ligne est la même que les données de la ligne précédente. Si elle est différente, la formule renvoie 1. Dans le cas contraire, elle retourne 0.
Ajouter cette formule à la cellule B2.
En utilisant un ensemble de techniques simples, vous pouvez accélérer les feuilles de calcul plus lent par un facteur de 10 ou 100. Vous pouvez également appliquer ces techniques que vous concevoir et de créer des feuilles de calcul afin qu'ils calculent rapidement.
Cet article technique a été produit en partenariat avec A23 Consulting.
Allison Bokone, Microsoft Corporation, est un écrivain de programmation dans l'équipe du Bureau.
Chad Rothschiller, Microsoft Corporation, est un gestionnaire de programme dans l'équipe du Bureau.