Tout savoir sur le Calcul sur Excel

La « grille principale » de 1 million de lignes et 16 000 colonnes 2016 Excel de Office, ainsi que de nombreuses autres limites augmentent considérablement la taille des feuilles de calcul que vous pouvez générer par rapport aux versions antérieures d’Excel. Une seule feuille de calcul Excel peut maintenant contenir plus de 1 000 fois plus de cellules que dans les versions antérieures.

Dans les versions antérieures d’Excel, beaucoup de gens créé lent-calcul des feuilles de calcul et des feuilles de calcul plus calculer généralement plus lentement que les petites. Avec l’introduction de la grille « Big » dans Excel 2007, performances est vraiment important. Ralentir le calcul et les tâches de manipulation de données telles que le tri et filtrage de rendre plus difficile pour les utilisateurs à se concentrer sur la tâche en cours et le manque de concentration augmente les erreurs.

Les versions récentes de Excel a introduit plusieurs fonctionnalités pour vous aider à gérer cette augmentation des capacités, telles que la possibilité d’utiliser plusieurs processeurs à la fois pour les calculs et les opérations de jeu de données courantes telles que l’actualisation, le tri et ouverture des classeurs.Calcul multithread peut réduire considérablement les temps de calcul de feuille de calcul.Toutefois, le facteur le plus important qui influence la vitesse de calcul d’Excel est encore la façon dont votre feuille de calcul est conçu et construit.

Vous pouvez modifier la plupart des feuilles de calcul lente de calcul pour calculer des dizaines ou des centaines voire des milliers de fois plus rapides.Identifier, mesurer et ensuite améliorer l’obstruction de calcul dans vos feuilles de calcul, vous pouvez accélérer le calcul.

L’importance de la vitesse de calcul

Vitesse de calcul médiocre a une incidence sur la productivité et augmente l’erreur de l’utilisateur.La productivité des utilisateurs et la possibilité de se concentrer sur une tâche se détériore comme s’allonge les temps de réponse.

Excel propose deux modes de calcul principal qui vous permettent de contrôler quand a lieu le calcul :

• Calcul automatique – les formules sont recalculées automatiquement lorsque vous apportez une modification.
• Calcul manuel – les formules sont recalculées seulement lorsque vous le demandez (par exemple, en appuyant sur 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.

Comme les temps de calcul augmente (généralement entre 1 et 10 en secondes), les utilisateurs doivent passer au calcul manuel lorsqu’ils entrent des données.Erreurs de l’utilisateur et les niveaux de nuisance commencent à augmenter, en particulier pour les tâches répétitives, et qu’il devient difficile à maintenir un train 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.

Présentation des méthodes de calcul sur Excel

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

Dépendances de calcul et de recalcul complets

Le moteur de recalcul dynamique dans Excel tente de réduire le temps de calcul en effectuant le suivi en continu à la fois les antécédents et les dépendances pour chaque formule (les cellules référencées par la formule) et toutes les modifications ont été apportées depuis le dernier calcul.Dans le nouveau calcul 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 calcul des cellules qui dépendent des 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 calcul actif en appuyant sur F9.Vous pouvez forcer un calcul complet de toutes les formules en appuyant sur Ctrl + Alt + F9, ou vous pouvez forcer une reconstruction complète des dépendances et un calcul complet en appuyant sur Maj + Ctrl + Alt + F9.

Processus de calcul

Les formules Excel qui référencent d’autres cellules peuvent être placés avant ou après les cellules référencées (référence directe ou référence arrière).C’est pourquoi Excel ne calcule pas de cellules dans un ordre fixe, ou par la ligne ou de colonne.Au lieu de cela, Excel détermine dynamiquement la séquence de calcul basée sur une liste de toutes les formules pour calculer (la chaîne de calcul) et les informations de dépendance sur chaque formule.

Excel a phases de calcul distincts :

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 non calculées et mise à jour de la chaîne de calcul.Cette phase s’exécute à chaque entrée de cellule ou de la modification, même en mode de calcul manuel.En général cela s’exécute si vite que vous ne remarquez pas il, mais dans les cas complexes, la réponse peut être lent.
3. Calcule toutes les formules.Dans le cadre de la procédure de calcul, Excel réorganise et restructure la chaîne de calcul afin d’optimiser les calculs futurs.
4. Mettre à jour les parties visibles de la fenêtre Excel.

La troisième phase s’exécute à chaque calcul ou un nouveau calcul.Excel tente de calculer chaque formule dans la chaîne de calcul à son tour, mais si une formule dépend d’une ou plusieurs formules qui n’ont pas encore été calculés, la formule est envoyée vers le bas de la chaîne doit être calculée à nouveau ultérieurement.Cela signifie qu’une formule peut être calculée plusieurs fois par le recalcul.

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

• Généralement, Excel recalcule uniquement les cellules qui ont changé et leurs dépendances.
• Excel stocke et réutilise la séquence de calcul la plus récente afin qu’il peut enregistrer la plupart du temps utilisé pour déterminer l’ordre de calcul.
• Avec plusieurs ordinateurs multicoeurs, Excel essaie d’optimiser la façon dont les calculs sont répartis entre les coeurs basées sur les résultats du calcul précédent.
• Dans une session Excel, cache Windows et Excel récemment utilisés données et des programmes pour un accès plus rapide.

Calcul des classeurs, des feuilles de calcul et des plages

Vous pouvez contrôler ce qui est calculé en utilisant les différentes méthodes de calcul Excel.

Calculer tous les classeurs ouverts

Chaque nouveau calcul calcul intégral calcule tous les classeurs ouverts, résout les dépendances au sein et entre des classeurs et des feuilles de calcul et réinitialise toutes les cellules (« Dirty ») précédemment non calculées calculée.

Calculer les feuilles de calcul sélectionnées

Vous pouvez également recalculer uniquement les feuilles de calcul sélectionnées à l’aide de MAJ + F9.Cela ne pas résoudre les dépendances entre les feuilles de calcul et ne pas réinitialiser de cellules sale calculée.

Calculer une plage de cellules

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

• Range.CalculateRowMajorOrder calcule la plage de gauche à droite et de haut en bas, en ignorant toutes les dépendances.
• Range.Calculate calcule la plage de résoudre toutes les dépendances au sein de la plage.

CalculateRowMajorOrder ne résout pas les dépendances dans la plage qui est calculée, il est généralement beaucoup plus rapidement que Range.Calculate. Toutefois, elle doit être utilisée avec précaution car il ne peut pas donner les mêmes résultats que Range.Calculate.

Range.Calculate est un des outils plus utiles dans Excel pour optimiser les performances, car vous pouvez l’utiliser à la fois et comparer la vitesse de calcul de formules.

Fonctions volatiles

Une fonction volatile est toujours recalculée à chaque recalcul même si elle ne semble pas avoir les antécédents modifiés. À l’aide de nombreuses fonctions volatiles ralentit à chaque recalcul, mais il ne fait aucune différence dans un calcul complet. Vous pouvez rendre volatile une fonction définie par l’utilisateur en incluant Application.Volatile dans le code de la fonction.

Certaines des fonctions intégrées dans Excel sont évidemment volatiles : RAND(), maintenant(), AUJOURDHUI().D’autres sont moins bien évidemment volatile : OFFSET(), CELL(), INDIRECT(), INFO().

Certaines fonctions ont jusqu’à présent été décrits comme volatile ne sont pas en fait volatiles : INDEX(), ROWS(), COLUMNS(), AREAS().

Actions volatiles

Actions volatiles sont des actions qui déclenchent un recalcul et incluent les éléments suivants :

• 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 ;
• Filtrage, masquage ou lignes ne plus masquer.
• Ouverture d’un classeur en mode automatique.Si le classeur a été calculé dernier par une autre version de Excel, ouvrir le classeur généralement entraîne un calcul complet.
• L’enregistrement d’un classeur en mode manuel si le Calculer avant d’enregistrer option est sélectionnée.

Évaluation de formule et le nom des cas

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 ;
• Permet d’entrer ou de modifier la formule à l’aide de l’Assistant fonction.
• Permet d’entrer la formule en tant qu’argument dans l’Assistant fonction.
• Permet de sélectionner la formule dans la barre de formule et appuyez sur la touche F9 (appuyez sur ÉCHAP pour annuler et revenir à 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é ;
• l s’agit d’une liste de filtre automatique et la liste déroulante critères a été activée.
• elle est marquée comme non calculée.

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

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

Excel des tables de données (ongletdonnées > groupe Outils de données > Analyse de scénarios > Table de données) ne doit pas être confondu avec la fonctionnalité de table (ongletaccueil > groupe de Styles > Format sous forme de tableau, ou, Onglet Insertion > groupe de Tables > Table).Tables de données Excel effectuer plusieurs calculs du classeur, chaque piloté par les différentes valeurs dans la table.Excel calcule d’abord le classeur normalement.Pour chaque paire de valeurs de ligne et de colonne, puis remplace les valeurs, est un recalcul monothread et stocke les résultats dans la table de données.

Recalcul de table de données toujours utilise un seul processeur.

Tables de données vous offrent un moyen pratique de calculer plusieurs variantes et afficher et comparer les résultats des variations.Utilisez l’option de calcul automatique sauf les Tables pour empêcher Excel de déclenchant automatiquement plusieurs calculs à chaque calcul, mais encore calcule toutes les formules dépendantes sauf dans les tables.

Contrôle les options de calcul

Excel dispose d’une gamme d’options qui vous permettent de contrôler le mode de que calcul.Vous pouvez modifier les options les plus fréquemment utilisées dans Excel en utilisant le groupe de calcul sous l’onglet formules , dans le ruban.

La figure 1. Groupe de calcul de l’onglet formules

Pour voir Excel plus options de calcul, sous l’onglet fichier , cliquez sur Options.Dans la boîte de dialogue Options Excel , cliquez sur l’onglet formules .

La figure 2. Options de calcul sous l’onglet Formules, dans les Options Excel

Les paramètres d’itération (Activer le calcul itératif, Nb maximal d’itérationset de nombreuses options de calcul (automatique, automatique sauf dans les tables de données, manuel, Recalculer le classeur avant de l’enregistrer) Écart maximal) fonctionne au niveau de l’application plutôt qu’au niveau du classeur (elles sont les mêmes pour tous les classeurs ouverts).

Pour obtenir des options de calcul avancé, sous l’onglet fichier , cliquez sur Options.Dans la boîte de dialogue Options Excel , cliquez sur Avancé.Sous la section formules , définissez les options de calcul.

La figure 3. Options de calcul avancé

Lorsque vous démarrez Excel, ou lorsqu’il est exécuté sans les classeurs ouverts, les paramètres d’itération et le mode de calcul initiale sont définies à partir du premier classeur sans modèle, non-macro complémentaire que vous ouvrez. Cela signifie que les paramètres de calcul dans les classeurs ouverts ultérieurement sont ignorés, même si, bien sûr, vous pouvez modifier manuellement les paramètres de Excel à tout moment. Lorsque vous enregistrez un classeur, les paramètres de calcul en cours sont stockés dans le classeur.

Calcul automatique

Mode de calcul automatique signifie que Excel recalcule automatiquement tous les classeurs ouverts à chaque modification et lorsque vous ouvrez un classeur. Généralement, lorsque vous ouvrez un classeur en mode automatique, et Excel recalcule, vous ne voyez pas le recalcul, car rien n’a changé depuis que le classeur a été enregistré.

Vous pouvez remarquer ce calcul lorsque vous ouvrez un classeur dans une version ultérieure de Microsoft Excel que vous avez utilisé la dernière fois que le classeur a été calculé (par exemple, 2016 Excel et Excel 2013).Étant donné que les moteurs de calcul Excel sont différents, Excel effectue un calcul complet lors de l’ouverture d’un classeur qui a été enregistré à l’aide d’une version antérieure d’Excel.

Calcul manuel

Mode de calcul manuel signifie que 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 qui prennent plus d’une fraction de seconde à recalculer, vous devez définir le calcul en mode manuel pour éviter les délais d’attente lorsque vous apportez des modifications.

Excel indique lorsqu’un classeur en mode manuel a besoin d’un nouveau calcul en affichant les 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 activée.

Paramètres d’itération

Si vous avez des références circulaires intentionnelles dans votre classeur, les paramètres d’itération activer vous permettent de contrôler le nombre maximal de fois où le classeur est recalculées (itérations) et les critères de convergence (écart maximal : quand arrêter).Désactivez la case itération afin que si vous avez des références circulaires accidentelles, Excel vous avertit et n’essaie pas de résoudre ces problèmes.

Propriété de ForceFullCalculation de classeur

Lorsque vous définissez cette propriété classeur à True, le recalcul de Smart d’Excel est désactivé et chaque calcul recalcule toutes les formules dans tous les classeurs ouverts.Pour certains classeurs complexes, le temps nécessaire pour construire et maintenir les arborescences de dépendances nécessaires pour le nouveau calcul actives est supérieur à la durée enregistrée par le recalcul actives.

Si votre classeur prend beaucoup trop de temps à ouvrir ou apporter de petites modifications beaucoup de temps même en mode de calcul manuel, il peut être intéressant de le ForceFullCalculation.

Si le classeur ForceFullCalculation propriété a été défini sur True, Calculer s’affiche dans la barre d’état.

Vous pouvez contrôler ce paramètre à l’aide de VBE (Alt + F11), ThisWorkbook dans l’ Explorateur de projets (Ctrl + R), affichage de la Fenêtre Propriétés (F4).

La figure 4. Définition de la propriété Workbook.ForceFullCalculation

Fabrication de classeurs : calculer plus rapidement

Utilisez les étapes et les méthodes suivantes pour rendre vos classeurs calculer plus rapidement.

Vitesse du processeur et plusieurs coeurs

Pour la plupart des versions de Microsoft Excel, un processeur plus rapide, bien entendu, permet des calculs Excel plus rapide. Le moteur de calcul multithread introduit dans Excel 2007 permet à Excel d’utiliser une excellente de systèmes multiprocesseurs, et vous pouvez vous attendre à des gains de performances significatifs avec la plupart des classeurs.

Pour les classeurs de plus grande taille, les performances de calcul des gains d’échelle de processeurs multiples quasi linéaire avec le nombre de processeurs physiques.Toutefois, hyper-threading de processeurs physiques ne produit un gain de performances.

Accélérer les calculs et en réduisant les 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 de méga-formules complexes et tableau. En général, il est préférable d’avoir plus de lignes et de colonnes et de moins de calculs complexes. Cela donne à la fois le recalcul actif et le calcul multithread dans Excel une meilleure opportunité d’optimiser les calculs. Il est également plus facile à comprendre et à déboguer. Voici quelques règles pour aider à accélérer des calculs du classeur.

Tout d’abord la règle : supprimer les calculs inutiles, dupliquées et répétées

Recherchez les calculs inutiles, dupliquées et répétées et savoir approximativement le nombre de références de cellule et de calculs sont requis pour Excel calculer le résultat de cette obstruction.Pensez comment vous pouvez obtenir le 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.

Deuxième règle : utilisez 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 ;
• Échec des fonctions VBA définies par l’utilisateur sont généralement plus lentes que les fonctions intégrées dans Excel (bien que rédigées avec soin les fonctions VBA peuvent être rapides).
• Réduire le nombre de cellules utilisées dans des fonctions telles que somme et somme.Si.Temps de calcul est proportionnelle au nombre de cellules utilisées (cellules inutilisées sont ignorées).
• Remplacez les formules matricielles lentes par des fonctions définies par l’utilisateur.

Troisième règle : faire bon usage de recalcul dynamique et calcul multithread

La meilleure utilisation vous rendre de nouveau calcul actif et multithread de calcul dans Excel, le moins de traitement doit être effectué chaque fois que Microsoft Excel recalcule, alors :

• Éviter des fonctions volatiles telles que indirects et de contrepartie dans lequel vous pouvez, sauf s’ils sont beaucoup plus efficaces que les autres solutions.(Utilisez bien conçue de décalage 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.
• Éviter les fonctions de thread unique : o PHONÉTIQUE

– CELLULE lorsque argument « adresse » ou « format » est utilisé.

– INDIRECTE

– FONCTION LIREDONNEESTABCROISDYNAMIQUE

– MEMBRECUBE

– VALEURCUBE

– PROPRIETEMEMBRECUBE

– JEUCUBE

– RANGMEMBRECUBE

– MEMBREKPICUBE

– NBENSCUBE

– ADRESSE où est indiqué le cinquième paramètre (sheet_name)

– Toute fonction de base de données (DSUM, DAVERAGE, etc.) qui fait référence à un tableau croisé dynamique

– ERREUR. TYPE DE

– LIEN HYPERTEXTE

– VBA et les fonctions COM complément défini par l’utilisateur

• • Évitez d’utiliser des tables de données et les références circulaires itératif : ces deux calculer toujours mono-thread.

Quatrième règle : temps et test 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. Heure à laquelle la formule que vous souhaitez modifier à l’aide de la macro RangeTimer .
    2. Apportez la modification.
    3. Heure à laquelle la formule modifiée en utilisant la macro RangeTimer .
    4. 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.

Montants de la période en cours

Par exemple, vous avez besoin calculer les montants de période à jour d’une colonne qui contient les numéros de 2 000.Supposons que colonne A contient les nombres, et que les colonnes B et C doivent contenir les totaux de la période en cours.

Vous pouvez écrire la formule à l’aide de somme, qui est une fonction efficace.

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

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

La figure 6. Exemple des formules de somme pour la période en cours

Copiez la formule dans B2000.

Le nombre de références de cellule sont ajoutés par la somme totale ?B1 fait référence à une cellule, et B2000 à 2 000 cellules.La moyenne est de 1 000 références par cellule, le nombre total de références est de 2 millions.Sélectionner les formules 2 000 et en utilisant la macro RangeTimer vous montre que les formules de 2 000 dans la colonne B calculent en 80 millisecondes.La plupart de ces calculs est reproduite de nombreuses fois : somme additionne A1 a2 dans chaque formule de B2:B2000.

Vous pouvez éliminer cette duplication en écrivant les formules comme suit.

C1=A1

C2=C1+A1

Copiez cette formule dans la C2000.

Maintenant le nombre de cellules références sont ajoutées au total ?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 renvoie.Il s’agit d’un facteur de 500 moins de références de cellule.

RangeTimer indique que les formules de 2 000 dans la colonne C calculent en 3.7 millisecondes par rapport aux 80 millisecondes pour la colonne B. Cette modification a un facteur d’amélioration des performances d’uniquement 80/3.7 = 22 au lieu de 500 car il y a un temps système réduit par la 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 bien, vous pouvez utiliser la fonction SIERREUR , qui est conçue pour être simple et rapide et qui est une formule unique :

B1=IFERROR(time expensive formula,0)

Nombre dynamique unique

La figure 7. Exemple de liste de données pour un compteur unique

Si vous avez une liste de 11 000 lignes de données dans la colonne A, qui change fréquemment, et vous devez créer une formule qui calcule dynamiquement le nombre d’éléments uniques dans la liste, en ignorant les espaces, Voici plusieurs solutions possibles.

• • • • • • Formules matricielles (utilisez 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 rapide qu’une formule matricielle équivalente. Cette formule prend quelques secondes 10.0 et donne un facteur d’amélioration de 13.8/10.0=1.38, qui est mieux, mais pas suffisante.
          • =SUMPRODUCT((A2:A11000<> » »)/COUNTIF(A2:A11000,A2:A11000& » »))
          • Fonctions définies par l’utilisateur. L’exemple de code suivant illustre une fonction VBA définie par l’utilisateur qui utilise le fait que l’index d’une collection doit être unique.
          • Ajout d’une colonne de formules. Si vous regardez l’exemple précédent de données, vous pouvez voir qu’il est trié (Excel prend 0,5 seconde 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 sont la même que les données de la ligne précédente. Si elle est différente, 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)

Copier la formule et ajoutez une formule pour ajouter 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.

Conclusion

Excel vous permet de gérer beaucoup plus grandes feuilles de calcul, et fournit des améliorations significatives par rapport aux versions antérieures de vitesse de calcul. Lorsque vous créez des feuilles de calcul volumineuses, il est facile pour les générer de manière qu’elles calculer lentement. Les feuilles de calcul ralenties augmentent les erreurs car les utilisateurs éprouvent des difficultés à maintenir la concentration tandis que le calcul se produit.

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.