Performances d'Excel: Conseils pour éliminer les obstacles aux performances

S’applique à: Excel |Excel 2013 | Office 2016 | Visual Studio

Suivez ces conseils relatifs à la suppression des obstacles aux nombreuses et fréquentes performances dans Excel.

Optimiser les références et les liens

Découvrez comment améliorer les performances de types références ainsi que les liens.

N’utilisez pas le référencement de transfert et le référencement vers l’arrière

Pour améliorer la clarté et éviter les erreurs, concevez vos formules afin qu’elles ne fassent pas référence (à droite ou en dessous) à d’autres formules ou cellules. Le référencement Transfert généralement n’affecte pas les performances de calcul à l’exception dans les cas extrêmes pour le calcul d’un classeur, où il peut prendre plus de temps pour établir une séquence de calcul délicate s’il existe de nombreuses formules souhaitant disposer du premier leur calcul différé.

Réduire l’utilisation de références circulaires avec itération

Calculer des références circulaires avec itérations est lent, car plusieurs calculs sont nécessaires, et ces calculs sont mono-filaire. Vous pouvez fréquemment «dérouler» les références circulaires à l’aide d’algèbre afin que le calcul itératif ne soit plus nécessaire. Par exemple, dans le flux de trésorerie et les calculs d’intérêt, essayez de calculer le flux de trésorerie avant intérêt, calculez le montant des intérêts, puis calculez le flux de trésorerie, y compris l’intérêt.

Excel calcule les références circulaires feuille-par-feuille sans considérant des dépendances. Par conséquent, vous obtenez généralement un calcul lent si vos références circulaires recouvrent plus d’une feuille de calcul. Essayez de déplacer les calculs circulaires sur une feuille de calcul ou d’optimiser la séquence de calcul de feuille de calcul pour éviter les calculs inutiles.

Avant de commencer les calculs itératifs, Excel doit recalculer le classeur afin d’identifier les références circulaires et leurs dépendants. Ce processus est égal à deux ou trois itérations du calcul.

Après avoir identifié des références circulaires et leurs dépendants, chaque itération nécessite qu’ Excel calcule non seulement toutes les cellules de la référence circulaire, mais également toutes les cellules qui dépendent des cellules de la chaîne de référence circulaire, ainsi que les cellules volatiles et leurs dépendants. Si vous avez un calcul complexe dépendant des cellules dans la référence circulaire, il peut être plus rapide pour cela de l’isoler dans un classeur fermé distinct et l’ouvrir pour le recalcul après que le calcul circulaire ait convergé.

Il est important de réduire le nombre de cellules dans le calcul circulaire et le temps de calcul pris par ces cellules.

Éviter les liens entre classeurs

Éviter les liens entre classeurs lorsqu’il est possible ; ils peuvent être lents, être facilement incorrectes et pas toujours facile à rechercher et corriger.

L’utilisation de classeurs moins volumineux est généralement mieux, mais pas toujours, que d’utiliser de nombreux classeurs plus petits. Il peut avoir quelques exceptions à ceci lorsque vous avez de nombreux calculs frontaux qui sont recalculées donc il est rarement logique de les insérer dans un classeur distinct ou lorsque vous avez de la RAM insuffisante.

Essayez d’utiliser des références de cellule directes simple qui travaillent sur des classeurs fermés. En procédant ainsi, vous pouvez éviter le recalcul detous vos classeurs liées lorsque vous recalculezn’importe quel classeur. Par ailleurs, vous pouvez afficher les valeurs Qu' Excel a lu du classeur fermé, ce qui est souvent important pour le débogage et l’audit du classeur.

Si vous ne pouvez éviter d’utiliser des classeurs liés, essayez de tous les ouvrir et ouvrez les classeurs de destination avant les classeurs sources.

Réduire les liens entre les feuilles de calcul

Le recours à de nombreuses feuilles de calcul peut simplifier l’utilisation de votre classeur, mais le calcul de références à d’autres feuilles est généralement plus long que le calcul de références dans la même feuille.

Réduire la plage utilisée

Pour gagner de la mémoire et réduire la taille de fichier, Excel tente de stocker des informations sur la zone uniquement sur une feuille de calcul que vous avez utilisée. Cette option est appelée publication utilisée. Il peut arriver que la modification différentes et les opérations de mise en forme prolongent la plage utilisée considérablement au-delà de la plage que vous devez envisager actuellement utilisée. Cela peut provoquer des obstacles de performances et des obstacles taille de fichier.

Vous pouvez vérifier la plage utilisée visible sur une feuille de calcul à l’aide de Ctrl + Fin. Où il s’agit excessive, vous devez envisager la suppression de toutes les lignes et colonnes dessous et à droite de votre vraie dernière cellule utilisée et l’enregistrement du classeur. Créez une copie de sauvegarde en premier. Si vous avez des formules avec des plages qui s’étendent ou font référence à la zone supprimée, ces plages seront de taille réduites ou remplacées par # N/A.

Autoriser les données supplémentaires

Lorsque vous ajoutez fréquemment des lignes ou des colonnes de données à vos feuilles de calcul, vous devez trouver un moyen de faire en sorte que vos formules Excel fassent automatiquement référence à la nouvelle zone de données, plutôt que de devoir rechercher et modifier vos formules à chaque fois.

Vous pouvez le faire à l’aide d’une grande plage dans vos formules qui s’étendent bien au-delà des limites de vos données en cours. Toutefois, cela peut provoquer un calcul inefficace dans certaines circonstances, et il est difficile de gérer, car la suppression des lignes et colonnes peuvent réduire la plage sans que vous avez remarquée.

Utiliser des références structurées de tableau (recommandé)

En commençant dans Excel 2007, vous pouvez utiliser des références structurées tableau, développer et réduire à mesure que la taille de la table référencée augmente ou diminue automatiquement.

Cela présente plusieurs avantages :

• Moins d’inconvénients de performances existent que les alternatives des plages référençant et dynamiques d’une colonne entière.

• Il est facile d’avoir plusieurs tables de données dans une seule feuille de calcul.

• Les formules qui sont également incorporées dans le tableau développent et contractent avec les données.

Vous pouvez également utiliser des références de colonne et ligne entières

Une autre approche consiste à utiliser une référence de colonne entière, par exemple $A:$ A. Cette référence renvoie toutes les lignes dans la colonne A. Par conséquent, vous pouvez ajouter autant de données que vous voulez, et elles seront toujours incluses dans la référence.

Cette solution comporte des avantages et des inconvénients :

• De nombreuses fonctions Excel intégrées (SUM, SUMIF) calculent les références de la colonne entière de manière efficace, car elles reconnaissent automatiquement la dernier ligne utilisée dans la colonne. Toutefois, les fonctions de calcul de tableau telles queSUMPRODUCTne peuvent pas traiter les références de colonne entière ou calculer toutes les cellules dans la colonne.

• Les fonctions définies par l’utilisateur ne reconnaissent pas automatiquement la dernière ligne utilisée dans la colonne et, par conséquent, calculent fréquemment des références de colonne entière de manière inefficace. Toutefois, il est facile de programmer des fonctions définies par l’utilisateur afin qu’elles reconnaissent la ligne utilisée en dernier.

• Il est difficile d’utiliser des références de colonnes entières lorsque vous avez plusieurs tables de données dans une seule feuille de calcul.

• Dans Excel 2007 et les versions ultérieures, des formules de tableau peuvent gérer les références de colonnes entières mais cela force le calcul pour toutes les cellules dans la colonne, y compris les cellules vides. Cela peut être ralentir le calcul, en particulier pour 1 millions de lignes.

Vous pouvez également utiliser des plages dynamiques

À l’aide des fonctionsFFSET ou INDEX et COUNTAdans la définition d’une plage nommée, vous pouvez rendre la zone à laquelle la plage nommée fait référence pour développer de manière dynamique et contracter. Par exemple, créez un nom défini à l’aide de formules suivantes :

  =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
  =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)+ROW(Sheet1!$A$1) - 1,1)

Lorsque vous utilisez le nom de plage dynamique dans une formule, il se développe automatiquement pour inclure les nouvelles entrées.

À l’aide de la formuleINDEXpour une plage dynamique est généralement préférable à la formuleOFFSET, car OFFSET a l’inconvénient d’une fonction volatile en cours qui sera calculée à chaque recalcul.

Les performances baissent, car la fonctionNBVALà l’intérieur de la formule de plage dynamique doit examiner plusieurs lignes. Vous pouvez réduire cette baisse de performances en stockant les COUNTA dans le cadre de la formule dans une cellule séparée ou un nom défini et puis en faisant référence à la cellule ou le nom de la plage dynamique:

 Counts!z1=COUNTA(Sheet1!$A:$A)
 OffsetDynamicRange=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1)
 IndexDynamicRange=Sheet1!$A$1:INDEX(Sheet1!$A:$A,Counts!$Z$1+ROW(Sheet1!$A$1) - 1,1)

Vous pouvez également utiliser les fonctions comme INDIRECT pour construire des plages dynamiques, mais INDIRECT est volatile et calcule toujours un fil unique.

Les plages dynamiques comprennent les avantages et inconvénients suivants :

• Les plages dynamiques fonctionnent également pour limiter le nombre de calculs effectués par des formules de tableau.

• Utiliser plusieurs plages dynamiques au sein d’une seule colonne nécessite des fonctions spéciales de comptage.

• Utiliser plusieurs plages dynamiques peut réduire les performances.

Améliorer le temps de calcul recherche

Dans Office 365 version 1809 et versions ultérieure, Excel RECHERCHEV, RECHERCHEH et CORRESPONDANCE pour une correspondance exacte sur les données non triées est beaucoup plus rapides que jamais lorsque vous recherchez plusieurs colonnes (ou lignes avec la fonction RECHERCHEH) à partir de la même plage du tableau.

Cela étant dit, pour les versions antérieures d’ Excel, les recherches sont toujours des obstacles au calcul de manière fréquente et significative. Heureusement, il existe plusieurs façons d’améliorer le temps de calcul de recherche. Si vous utilisez l’option de correspondance exacte, le temps de calcul de la fonction est proportionnel au nombre de cellules analysées avant de trouver une correspondance. Pour les recherches sur les grandes plages, cette fois peut être significative.

La recherche d’heure en utilisant les options de correspondance approximative RECHERCHEV, RECHERCHEH, et CORRESPONDANCE sur les données triées de manière rapide n’est pas considérablement augmenté par la longueur de la plage vous recherchez vers plus haut. Les caractéristiques sont la même que la recherche binaire.

Comprendre les options de recherche

Assurez-vous de bien comprendre les options de type correspondance et de type plage de recherche dans MATCH, RECHERCHEV, etRECHERCHEH.

L'exemple de code suivant illustre la syntaxe de la fonction MATCH. Pour plus d’informations, voir la méthodeMATCHde l’objetWorksheetFunction.

  MATCH(lookup value, lookup array, matchtype)

• Matchtype=1 renvoie la plus grande correspondre inférieure ou égale à la valeur de recherche lorsque la matrice de recherche est triée par ordre croissant (correspondance approximative). Si la matrice de recherche n’est pas triée par ordre croissant, CORRESPONDANCE renverra une réponse incorrecte. L’option par défaut est correspondance approximative triée par ordre croissant.

• Matchtype= 0 demandes une correspondance exacte et part du principe que les données ne sont pas triées.

• Matchtype=1 renvoie la plus grande correspondre supérieure ou égale à la valeur de recherche lorsque la matrice de recherche est triée par ordre croissant (correspondance approximative).

L'exemple de code suivant illustre la syntaxe des fonctionsRECHERCHEV etRECHERCHEH. Pour plus d’informations, voir les méthodesRECHERCHEVet RECHERCHEHde l’objetWorksheetFunction.

  VLOOKUP(lookup value, table array, col index num, range-lookup)
  HLOOKUP(lookup value, table array, row index num, range-lookup)

• Range-lookup=TRUE renvoie la plus grande correspondre inférieure ou égale à la valeur de recherche lorsque la matrice de recherche est triée par ordre croissant (correspondance approximative). Il s’agit de l’option par défaut. La plage de tableau doit être triée par ordre croissant.

• Range-lookup=FALSE demandes une correspondance exacte et part du principe que les données ne sont pas triées.

Évitez d’exécuter les recherches sur des données non triées autant que possible, car il est lent. Si vos données sont triées, mais que vous souhaitez une correspondance exacte, consultezUtiliser deux des recherches des données triées contenant les valeurs manquantes.

Utiliser INDEXE et CORRESPONDANCES ou OFFSET au lieu de RECHERCHEV

Essayez d’utiliser les fonctionsINDEXE etCORRESPONDANCEà la place de RECHERCHEV. Bien que RECHERCHEV est légèrement plus rapide (environ 5 pour cent plus vite) et plus simples et utilise moins de mémoire qu’une combinaison de CORRESPONDANCE et INDEX, ou OFFSET, la possibilité qu’offrent souvent CORRESPONDANCE et INDEXE vous permet de gagner du temps considérablement. Par exemple, vous pouvez stocker le résultat d’une exacte CORRESPONDANCE dans une cellule et réutiliser dans plusieurs instructionsINDEXE.

La fonctionINDEXErapide et une fonction non-volatile, ce qui accélère le recalcul. La fonctiondécalage est également rapide; toutefois, c’est une fonction volatile, et elle augmente parfois considérablement le temps nécessaire pour traiter la chaîne de calcul.

Il est facile de convertir RECHERCHEV en INDEX et MATCH. Les deux phrases suivantes renvoient la même réponse:

 VLOOKUP(A1, Data!$A$2:$F$1000,3,False)

 INDEX(Data!$A$2:$F$1000,MATCH(A1,$A$1:$A$1000,0),3)

Accélérer les recherches

Car les recherches de correspondance exacte peuvent être lentes, considérez les options suivantes pour améliorer les performances:

• Utiliser une feuille de calcul. Il est plus rapide de conserver les recherches et les données sur la même feuille.

• Lorsque vous pouvez TRIER les données en premier (TRIER est rapide) et utiliser la méthode correspondance approximative.

• Lorsque vous devez utiliser une liste de choix correspondance exacte, restreindre la plage de cellules doit être analysée à un minimum. Utilisez des tableaux et des références structurées ou noms de plages dynamique plutôt que faire référence à un grand nombre de lignes ou des colonnes. Vous pouvez quelque fois recalculer une limite de plage inférieure et une limite de plage supérieure pour la recherche.

Utiliser deux recherches des données triées contenant les valeurs manquantes

Deux recherches de concordance approximative sont sensiblement plus rapides à effectuer qu’une recherche de concordance exacte dès lors que la recherche concerne plus que quelques lignes. (Le point de rentabilité concerne 20-10 lignes.)

Si vous pouvez trier vos données, mais ne pouvez toujours pas utiliser correspondance approximative, car vous ne pouvez pas être certain que la valeur que vous recherchez existe dans la plage de recherche, vous pouvez utiliser cette formule:

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val, _
      VLOOKUP(lookup_val, lookup_array, column, True), "notexist")

La première partie de la formule fonctionne en effectuant une recherche approximative sur la colonne de recherche.

  VLOOKUP(lookup_val ,lookup_array,1,True)

Vous pouvez vérifier si la réponse à partir de la colonne de recherche est identique à la valeur de recherche (en pareil cas vous avez une correspondance exacte) à l’aide de la formule suivante:

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val,

Si cette formule renvoie la valeur vrai, vous avez trouvé une correspondance exacte, de sorte que vous pouvez effectuer la recherche approximative nouveau, mais cette fois, renvoyer la réponse à partir de la colonne que vous souhaitez.

  VLOOKUP(lookup_val, lookup_array, column, True)

Si la réponse à partir de la colonne de recherche ne correspond pas à la valeur de recherche, une valeur manquante, et que la formule renvoie « notexist ».

N’oubliez pas que si vous recherchez une valeur inférieure à la plus petite valeur dans la liste, vous recevez une erreur. Vous pouvez gérer cette erreur à l’aide SIERREUR, ou en ajoutant une petite valeur test à la liste.

Utilisez la fonction SIERREUR pour les données non triées avec les valeurs manquantes

Si vous devez utiliser la recherche correspondance exacte sur les données non triées et que vous ne pouvez pas être certain que la valeur recherchée existe, vous devez souvent gérer le # N/A qui est renvoyée si aucune correspondance n’est trouvée. Depuis Excel 2007, vous pouvez utiliser la fonctionSIERREUR qui est simple et rapide.

  IF IFERROR(VLOOKUP(lookupval, table, 2 FALSE),0)

Dans les versions antérieures, une manière simple mais lente consiste à utiliser une fonctionIF qui contient deux recherches.

  IF(ISNA(VLOOKUP(lookupval,table,2,FALSE)),0,_
      VLOOKUP(lookupval,table,2,FALSE))

Vous pouvez éviter la double recherche exacte en utilisant une fonction MATCH exacte une fois, en stockant le résultat dans une cellule, puis en testant le résultat avant d’exécuter une fonction INDEX.

  In A1 =MATCH(lookupvalue,lookuparray,0)
  In B1 =IF(ISNA(A1),0,INDEX(tablearray,A1,column))

Si vous ne pouvez pas utiliser deux cellules, utilisez COUNTIF. Il est généralement plus rapide qu’une recherche de correspondance exacte.

  IF (COUNTIF(lookuparray,lookupvalue)=0, 0, _
      VLOOKUP(lookupval, table, 2 FALSE))

Utiliser INDEX et CORRESPONDANCE pour les recherches de correspondance exacte sur plusieurs colonnes

Vous pouvez souvent réutiliser unCORRESPONDANCEstockée exacte autant de fois. Par exemple, si vous effectuez des recherches exactes sur plusieurs colonnes de résultat, vous pouvez gagner du temps en utilisant une instructionCORRESPONDANCE et plusieurs instructionsINDEX plutôt que de plusieurs instructions RECHERCHEV.

Ajouter une colonne supplémentaire pour le CORRESPONDANCE pour stocker le résultat (stored_row) et pour chaque colonne résultat utiliser comme suit :

  INDEX(Lookup_Range,stored_row,column_number)

Vous pouvez également utiliser RECHERCHEV dans une formule matricielle. (Les formules de tableau doivent être entrées à l’aide de Ctrl+-Maj+Entrée. Excel ajoute { et } pour vous indiquer qu’il s’agit d’une formule matricielle).

  {VLOOKUP(lookupvalue,{4,2},FALSE)}

Utiliser un INDEX pour un ensemble de lignes ou des colonnes continues

Vous pouvez également revenir le nombre de cellules à partir de l’opération d’un recherche. Pour rechercher plusieurs colonnes adjacentes, vous pouvez utiliser la fonctionINDEXdans une formule matricielle pour renvoyer plusieurs colonnes en une seule fois (utiliser 0 en tant que le numéro de colonne). Vous pouvez également utiliser la fonctionINDEX pour renvoyer plusieurs lignes en même temps.

  {INDEX($A$1:$J$1000,stored_row,0)}

Cela ramène la colonne A à la colonne J de la ligne stockée créée par une précédente déclarationcorrespondance.

Utilisez CORRESPONDANCE pour renvoyer un bloc de cellules rectangulaire

Utilisez les fonctions MATCH et OFFSET pour renvoyer un bloc rectangulaire de cellules.

Utiliser INDEX et CORRESPONDANCE pour la recherche à deux dimensions

Vous pouvez efficacement effectuer une recherche de tableau à deux dimensions en utilisant les recherches distincts sur les lignes et colonnes d’un tableau à l’aide d’une fonctionINDEXEavec deux fonctions incorporéesCORRESPONDANCE: une pour la ligne et l’autre pour la colonne.

Utiliser une plage sous-ensemble pour une recherche index multiple

Dans les feuilles de calcul volumineuses, vous devrez peut-être souvent rechercher en utilisant plusieurs index, par exemple pour rechercher des volumes de produits dans un pays/une région. Pour ce faire, vous pouvez concaténer les index et effectuer la recherche à l’aide des valeurs de recherche concaténées. Toutefois, il s’agit inefficace pour deux raisons:

• La concaténation de chaînes est une opération de calculs intensifs.

• La recherche couvrira une grande plage.

Il est souvent plus efficace de calculer une plage de sous-ensembles pour la recherche (par exemple, en recherchant la première et la dernière ligne pour le pays/la région, puis en recherchant le produit dans cette plage de sous-ensembles).

Tenir compte des options pour la recherche en 3D

Pour rechercher le tableau à utiliser en plus de la ligne et de la colonne, vous pouvez appliquer les techniques suivantes axées sur la façon dont Excel recherche ou choisit le tableau.

Si chaque tableau que vous souhaitez rechercher (la troisième dimension) est stocké comme un ensemble de nommé des tableaux structurés, noms de plages, ou sous forme de tableau de chaînes de texte qui représentent des plages, vous pourrez peut-être utiliser les fonctionsCHOISIR ou INDIRECT.

• UtiliserCHOISIR et les noms de plages peuvent être une méthode efficace. CHOISIR n’est pas volatile, mais c’est mieux adapté pour un nombre de taille relativement modeste de tableaux en charge. Cet exemple utilise de manière dynamiqueTableLookup_Value pour choisir le nom de la plage (TableName1, TableName2, ...) à utiliser pour le tableau de choix.

    INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3), _
    MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
  

• L’exemple suivant utilise la fonctionINDIRECT et TableLookup_Value pour créer de manière dynamique le nom de feuille à utiliser pour la tableau de choix. Cette méthode présente l’avantage d’être simple et capable de gérer un grand nombre de tableaux. Étant donné que INDIRECT est une fonction volatile à thread unique, la recherche est calculée de manière unique à chaque calcul même si aucune donnée n’a été modifiée. Utiliser cette méthode est lente.

    INDEX(INDIRECT("Sheet" & TableLookup_Value & "!$B$2:$Z$1000"), _
    MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
  

• Vous pourriez également utiliser la fonction VLOOKUP pour rechercher le nom de la feuille ou la chaîne de texte à utiliser pour le tableau, puis la fonction INDIRECT pour convertir le texte résultant en une plage.

    INDEX(INDIRECT(VLOOKUP(TableLookup_Value,TableOfTAbles,1)),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
  

Une autre méthode consiste à agréger toutes vos tableaux dans un tableau géant qui contient une colonne supplémentaire qui identifie les tableaux individuels. Vous pouvez ensuite utiliser les techniques pour une recherche index multiple illustrée dans les exemples précédents.

Utiliser la recherche générique

Les fonctions MATCH, RECHERCHEV et RECHERCHEH vous permettent d’utiliser les caractères génériques ? (n’importe quel caractère unique) et * (aucun caractère ou nombre de caractères) sur les correspondances exactes alphabétiques. Il peut arriver que vous puissiez utiliser cette méthode pour éviter plusieurs correspondances.

Optimiser les formules de tableau et SOMMEPRODUIT

Les formules de tableaux et les fonctionsSOMMEPRODsont puissantes, mais vous devez les gérer avec soin. Une seule formule matricielle peut nécessiter plusieurs calculs.

Il est essentielle pour optimiser la vitesse de calcul de formules matricielles pour vous assurer que le nombre de cellules et les expressions sont évaluées dans la formule matricielle est aussi petit que possible. N’oubliez pas qu’une formule matricielle est un peu comme une formule volatile : si l’une des cellules qui la référence a changé, est volatile ou a été recalculée, la formule de tableau calcule toutes les cellules dans la formule et évalue toutes les cellules virtuelles il doit effectuer le calcul.

Pour optimiser la vitesse de calcul de formules matricielles:

• Prendre des expressions et des références de plage des formules de tableau en lignes et des colonnes distinctes d’assistance. Cela en fait une meilleure utilisation du processus de recalcul intelligent dans Excel.

• Ne pas faire référence aux lignes complètes, ou plusieurs lignes et colonnes dont vous avez besoin. Les formules de tableau sont forcées pour calculer toutes les références de cellule dans la formule, même si les cellules sont vides ou inutilisées. Avec 1 millions de lignes disponibles en commençant dans Excel 2007, une formule matricielle qui fait référence à une colonne entière est extrêmement lente à calculer.

• À compter d’Excel 2007, utilisez dans la mesure du possible des références structurées afin de limiter le nombre de cellules évaluées par la formule matricielle.

• Dans les versions antérieures à Excel 2007, utilisez des noms de plage dynamique autant que possible. Bien que qu’ils soient volatiles, il est judicieux, car ils réduisent la taille des plages.

• Soyez prudent avec les formules matricielles qui font référence à une ligne et une colonne : cela force le calcul d’une plage rectangulaire.

• UtilisezSUMPRODUCT si possible, il est légèrement plus rapide que la formule de tableau équivalent.

Tenir compte des options pour l’utilisation de somme pour les formules à plusieurs conditions

Vous devez toujours utiliser les fonctionsSUMIFS, COUNTIFS, etAVERAGEIFSau lieu de l’endroit où vous pouvez, car ils sont beaucoup plus rapides pour calculer des formules de tableau. Excel 2016 présente les fonctions rapidesMAXIFS et MINIFS.

Dans les versions antérieures à Excel 2007, les formules matricielles sont souvent utilisées pour calculer une somme avec plusieurs conditions. Il s’agit une opération relativement simple à faire, en particulier si vous utilisez l’Assistant Somme Conditionnelle dans Excel, mais il est souvent lent. Généralement, il existe des manières beaucoup plus rapides d’obtenir le même résultat. Si vous avez uniquement quelques SUMS à condition multiple, il est possible que vous puissiez utiliser la fonctionDSUMqui est beaucoup plus rapide que la formule de tableau équivalent.

Si vous devez utiliser des formules de tableau, quelques bonnes méthodes de les accélérer comme les suivantes:

• Utiliser des noms de plage dynamique ou des références structurées tableau pour réduire le nombre de cellules.

• Séparer les deux actions plusieurs conditions dans la colonne de formules d’assistance permettant de renvoyer vrai ou faux pour chaque ligne, puis référencer la colonne d’assistance dans un SUMIF ou d’une matrice de formule. Cela peut ne pas s’afficher pour réduire le nombre de calculs à une seule formule matricielle ; Toutefois, la plupart du temps il permet au processus de recalcul intelligent uniquement les formules dans la colonne d’assistance qui ont besoin d’être recalculées.

• Vous pouvez considérer la concaténation de toutes les conditions dans une seule condition, puis en utilisant SUMIF.

• Si les données peuvent être triées, comptez les groupes de lignes et limitez les formules de tableau pour rechercher les groupes de sous-ensemble.

Définir les priorités condition multiple SUMIFS, COUNTIFS, et les autres fonctions de famille IFS

Ces fonctions évaluent chacune des conditions de gauche à droite de ces opérations. Par conséquent, il est plus efficace de placer la condition la plus restrictive tout d’abord, afin que les conditions suivantes doivent uniquement examiner le plus petit nombre de lignes.

Tenez compte des options pour l’utilisation de SOMMEPRODUCT pour les formules à plusieurs conditions

En commençant dans Excel 2007, vous devez toujours utiliser les fonctionsSUMIFS,COUNTIFS, etAVERAGEIFSet dans Excel 2016 les fonctionsMAXIFS etMINIFS, au lieu des formulesSUMPRODUCTautant que possible.

Dans les versions antérieures, il existe quelques avantages à l’utilisation des formules matriciellesSUMPRODUCT au lieu de SUM :

• SUMPRODUCT ne doit pas être des entrées de matrice à l’aide de Ctrl + Maj + Entrée.

• SUMPRODUCT est généralement légèrement plus rapide (5 à 10 %).

Utilisez SUMPRODUCT pour les formules de tableau à plusieurs conditions comme suit :

  SUMPRODUCT(--(Condition1),--(Condition2),RangetoSum)

Dans cet exemple, Condition1 et Condition2 sont les expressions conditionnelles comme $A$1:$A$10000<=$Z4. Étant donné que les expressions conditionnelles renvoientvraioufauxau lieu des nombres, ils doivent être forcées en nombres à l’intérieur de la fonctionSUMPRODUCT. Vous pouvez le faire à l’aide de deux signes moins (--), ou en ajoutant 0 (+ 0), ou en multipliant par 1 (x1). Utiliser -- est légèrement plus rapide + 0 ou x1.

Notez que la taille et la forme des plages ou des tableaux utilisés dans les expressions conditionnelles et la plage à additionner doivent être identiques et ne peuvent pas contenir de colonnes entières.

Vous pouvez également directement multiplier les termes du contrat à l’intérieur SUMPRODUCT plutôt que de les séparer par des virgules:

  SUMPRODUCT((Condition1)*(Condition2)*RangetoSum)

C’est généralement légèrement plus lent que l’utilisation de la syntaxe de la virgule et elle génère une erreur si la plage à additionner contient une valeur de texte. Toutefois, il est légèrement plus flexible dans la plage à additionner peut avoir, par exemple, plusieurs colonnes lorsque les conditions n'ont qu’une seule colonne.

Utiliser SUMPRODUCT pour multiplier et ajouter des plages et des tableaux

Dans les scénarios tels que les calculs de moyenne pondérée, où il est nécessaire de multiplier une plage de nombres par une autre et d’additionner les résultats, l’utilisation de la syntaxe avec virgule pour SUMPRODUCT peut être de 20 à 25 pour cent plus rapide qu’une SUM entrée dans un tableau.

  {=SUM($D$2:$D$10301*$E$2:$E$10301)}
  =SUMPRODUCT($D$2:$D$10301*$E$2:$E$10301)
  =SUMPRODUCT($D$2:$D$10301,$E$2:$E$10301)

Ces trois formules produisent toutes le même résultat, mais la troisième formule qui utilise la syntaxe de la virgule pour SUMPRODUCT, prend uniquement environ 77 % du temps de calcul dont ont besoin les deux autres formules.

N’oubliez pas la matrice potentielle et les obstacles de fonction de calcul

Le moteur de calcul dans Excel est optimisé pour exploiter les formules et fonctions qui font référence à des plages. Toutefois, certaines dispositions inhabituelles de ces formules et fonctions peuvent parfois, mais pas toujours, causer des calculs accrus de manière considérable.

Si vous trouvez un obstacle de calcul qui implique des formules et des fonctions de plage, vous devez ressembler pour les éléments suivants:

• Références qui se chevauchent partiellement.

• Les formules et les fonctions de plage qui font référence à une partie d’un bloc de cellules sont calculées dans une autre formule de tableau ou plage de fonction. Cette situation peut se produire fréquemment dans les analyses de série temps.

• Une combinaison de formules référençant par ligne et une seconde série de formules faisant référence à la première par colonne.

• Un vaste ensemble de formules de tableau à une ligne couvrant un bloc de colonnes, avec les fonctionsSUM fonctions au pied de chaque colonne.

Utiliser des fonctions de manière efficace

Les fonctions étendent sensiblement la puissance d’ Excel, mais la manière dans lequel vous utilisez peut influencer souvent la durée de calcul.

Évitez les fonctions à fil unique

La plupart des fonctions Excel plus natives fonctionnent parfaitement avec multithread. Toutefois, autant que possible, évitez d’utiliser les fonctions de threads unique suivantes :

• Les fonctions VBA et automatisation définies par l’utilisateur (UDF), mais à base de XLL peuvent être multi-thread
• 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 (le 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

Utiliser des tableaux pour les fonctions qui traitent des plages

Pour les fonctions telles que SUM, SUMIF, et SUMIFS qui gèrent les plages, le temps de calcul est proportionnel au nombre de cellules utilisées que vous totalisez ou comptez. Les cellules inutilisées ne sont pas examinées, de sorte que les références de colonnes entières sont relativement efficaces, mais il est préférable de s’assurer que vous n’incluez pas plus de cellules utilisées que nécessaire. Utilisez des tableaux ou calculez les plages de sous-ensemble ou des plages dynamiques.

Réduisez les fonctions volatiles

Les fonctions volatiles peuvent ralentir le recalcul car ils augmentent le nombre de formules qui doivent être recalculées à chaque calcul.

Vous pouvez souvent réduire le nombre de fonctions volatiles à l’aide deINDEX au lieu de OFFSET, et CHOISIR au lieu de INDIRECT. Toutefois, OFFSET est une fonction rapide et peut souvent être utilisée de manière créative qui donne un calcul rapide.

Utilisez les fonctions définies par l’utilisateur C ou C++

Les fonctions définies par l’utilisateur qui sont programmés en C ou C++ et qui utilisent l’API C (fonctions de compléments XLL) effectuent généralement plus rapidement que les fonctions définies par l’utilisateur développées à l’aide de VBA ou automatisation (XLA ou automatisation des compléments). Pour plus d’informations, voir Développement de XLL Excel 2010.

Les performances de fonctions VBA définies par l’utilisateur sont sensible à la manière dont vous programmez et les appelez.

Utilisez les fonctions VBA définies par l’utilisateur plus rapide

Il est généralement plus rapide d’utiliser les calculs de formule Excel et les fonctions de feuille de calcul que d’utiliser des fonctions VBA définies par l’utilisateur. Cela est dû à une petite surcharge pour chaque appel de fonctions définies par l’utilisateur et une surcharge importante au transfert d’informations à partir d’ Excel pour la fonction définie par l’utilisateur. Mais les fonctions bien conçues et appelées définies par l’utilisateur peuvent être beaucoup plus rapides que les formules complexes.

Assurez-vous d’avoir placé toutes les références aux cellules de feuilles de calcul dans les paramètres d’entrée de la fonction définie par l’utilisateur plutôt que dans le corps de cette fonction, de manière à éviter d’ajouter Application.Volatile inutilement.

Si vous devez disposer de nombreuses formules qui utilisent des fonctions définies par l’utilisateur, assurez-vous que vous êtes en mode de calcul manuel et que le calcul est établi à partir de VBA. Les fonctions VBA définies par l’utilisateur calculent beaucoup plus lentement si le calcul n’estpas appelé à partir de VBA (par exemple, en mode automatique ou lorsque vous appuyez sur F9 en mode manuel). C’est particulièrement vrai lorsque Visual Basic Editor (Alt + F11) est ouvert ou a été ouvert dans la session actuelle d’ Excel.

Vous pouvez intercepter F9 et rediriger vers une sous-routine de calcul VBA comme suit. Ajouter cette sous-routine au moduleThisworkbook.

  Private Sub Workbook_Open()
      Application.OnKey "{F9}", "Recalc"
  End Sub

Ajouter cette sous-routine à un module standard.

  Sub Recalc()
      Application.Calculate
      MsgBox "hello"
  End Sub

Les fonctions définies par l’utilisateur dans les compléments Automation (Excel 2002 et versions ultérieures) n’entraînent pas la surcharge de Visual Basic Editor, car elles n’utilisent pas l’éditeur intégré. Les autres caractéristiques de performances de fonctions définies par l’utilisateur Visual Basic 6 dans des compléments Automation sont similaires à des fonctions VBA.

Si votre fonction définie par l’utilisateur traite chaque cellule dans une plage, déclarez l’entrée comme une plage, affectez-la à une variante qui contient une matrice et en boucle sur celle-ci. Si vous souhaitez traiter efficacement les références de colonne entière, vous devez le faire avec un sous-ensemble de la plage d’entrée, la divisant à son croisement avec la plage utilisée, comme dans cet exemple.

  Public Function DemoUDF(theInputRange as Range)
      Dim vArr as Variant
      Dim vCell as Variant
      Dim oRange as Range
      Set oRange=Union(theInputRange, theRange.Parent.UsedRange)
      vArr=oRange
      For Each vCell in vArr
          If IsNumeric(vCell) then DemoUDF=DemoUDF+vCell
      Next vCell
  End Function

Si votre fonction définie par l’utilisateur utilise des fonctions de feuille de calcul ou des méthodes du modèle objet Excel pour traiter une plage, il est généralement plus efficace de conserver la plage en tant que variable d’objet que de transférer toutes les données d’Excel vers la fonction définie par l’utilisateur.

  Function uLOOKUP(lookup_value As Variant, lookup_array As Range, _
                   col_num As Variant, sorted As Variant, _
                   NotFound As Variant)
      Dim vAnsa As Variant
      vAnsa = Application.VLookup(lookup_value, lookup_array, _
                                  col_num, sorted)
      If Not IsError(vAnsa) Then
          uLOOKUP = vAnsa
      Else
          uLOOKUP = NotFound
      End If
  End Function

Si votre fonction définie par l’utilisateur est appelée début de la chaîne de calcul, il peut être passé en tant qu’arguments non calculé. À l’intérieur d’une fonction définie par l’utilisateur, vous pouvez détecter les cellules non calculées à l’aide de test suivant pour les cellules vides qui contiennent une formule :

  If ISEMPTY(Cell.Value) AND Len(Cell.formula)>0 then

Il existe une surcharge de temps pour chaque appel à une fonction définie par l’utilisateur et pour chaque transfert de données à partir d’ Excel vers VBA. Parfois, une fonction formule de tableau multicellulaire définie par l’utilisateur peut vous aider à réduire les frais généraux en combinant plusieurs appels de fonction dans une fonction avec une plage d’entrée multicellulaire qui renvoie une plage de réponses.

Réduire la plage de cellules qui font référence à SUM et SUMIF

Les fonctions Excel SUM et SUMIFsont fréquemment utilisées sur un grand nombre de cellules. L’heure de calcul de ces fonctions est proportionnellement au nombre de cellules couverts, respectez réduire la plage de cellules auxquelles les fonctions font référence.

Utiliser le caractère générique SUMIF, COUNTIF, SUMIFS, COUNTIFS, et les autres fonctions IFS

Utilisez les caractères génériques ? (n’importe quel caractère unique) et * (aucun caractère ou nombre de caractères) dans les critères des plages alphabétiques dans le cadre des fonctions SUMIF, COUNTIF, SUMIFS, COUNTIFS et d’autres fonctions IFS .

Choisissez la méthode pour les arguments période-à-date et SUMs cumulatives

Il existe deux méthodes pour effectuer les arguments période-à-date et SUMs cumulatives. Supposons que les nombres que vous voulez de façon cumulative SUM sont dans la colonne A et que vous souhaitez la colonne B pour qu’elles contiennent la somme cumulée ; vous pouvez effectuez une des opérations suivantes:

• Vous pouvez créer une formule dans la colonne B comme =SUM($A$1:$A2) et faites-la glisser vers le bas autant que nécessaire. La cellule de départ de la SUM est ancrée dans A1, mais étant donné que la cellule finition comporte une référence de ligne relative, elle augmente automatiquement pour chaque ligne.

• Vous pouvez créer une formule dans la cellule B1 comme =$A1 et =$B1+$A2 dans la cellule B2 puis faites-la glisser vers le bas autant que nécessaire. Cette méthode calcule la cellule cumulée par l’ajout nombre de cette ligne à l’ancienne cumulée SUM.

Pour les 1 000 lignes, la première méthode permet à Excel d’effectuer des calculs d’environ 500 000, mais la deuxième méthode permet d’effectuer des calculs d’environ 2 000.

Calculer les sommes sous-ensemble

Lorsque vous avez plusieurs indexes triés à un tableau (par exemple, Site au sein de zone) vous pouvez souvent gagner du temps de calcul significative en calcul dynamique de l’adresse d’une plage sous-ensemble de lignes ou colonnes auxquelles vous voulez utiliser dans la fonctionSUM ou SUMIF.

Pour calculer l’adresse d’une plage sous-ensemble de lignes ou colonnes :

1. Compter le nombre de lignes pour chaque bloc sous-ensemble.

2. Ajouter des chiffres de façon cumulative pour chaque bloc détermine son début de la ligne.

3. Utilisez OFFSET avec la quantité et la ligne de départ afin de renvoyer une plage de sous-ensembles à la fonction SUM ou SUMIF qui couvre uniquement le bloc de sous-ensembles de lignes.

Utiliser le sous-total pour les listes filtrées

Utilisez la fonctionSUBTOTALpour les listes filtréesSUM. La fonctionSUBTOTAL est utile car, contrairement àSUM, elle ignore les opérations suivantes :

• Lignes masquées résultant d’une liste de filtrage. En commençant dans Excel 2003, vous pouvez également permettre àSUBTOTALd’ignorer toutes les lignes masquées, pas seulement les lignes simplement filtrées.

• Autres fonctionsSUBTOTAL.

Utilisez la fonction AGGRATE

La fonction AGGREGATE est un moyen efficace et performant de calcul de 19 différentes méthodes permettant une agrégation de données (par exemple, SUM, MEDIAN, PERCENTILE et LARGE). AGGREGATE dispose deux options pour ignorer les lignes, les valeurs d’erreurs masquées, filtrées et imbriquées sous-total et les fonctionsAGGREGATE.

Évitez d’utiliser Dfonctions

L’appellation DFunctionsDSUM, DCOUNT, DAVERAGE, et ainsi de suite sont sensiblement plus rapide que les formules matricielles équivalentes. L’inconvénient de l’appellation DFunctions est que les critères doivent être dans une plage distincte, ce qui les rend impraticables pour l’utilisation et la maintenance dans de nombreuses circonstances. En commençant dans Excel 2007, vous devez utiliser les fonctionsSUMIFS, COUNTIFS, et AVERAGEIFS au lieu de l’appellation DFunctions.

Créer des macros VBA plus rapides

Utilisez les recommandations suivantes pour créer des macros VBA plus rapides.

Désactivez toute l’option, sauf les essentiels pendant l’exécution du code

Pour améliorer les performances pour les macros VBA, désactiver explicitement la fonctionnalité n’est pas nécessaire lorsque votre code s’exécute. Souvent, un recalcul ou un renouvellement une fois que votre code s’exécute suffit et peut améliorer les performances. Une fois que votre code s’exécute, restaurer la fonctionnalité à son état d’origine.

Les fonctionnalités suivantes peuvent généralement être désactivées tandis que votre macro VBA exécute :

• Application.ScreenUpdating désactiver la mise à jour de l’écran. SiApplication.ScreenUpdating est défini sur faux, Excel ne renouvelle pas l’écran. Tandis que votre code s’exécute, l’écran se met à jour rapidement, et il n’est généralement pas nécessaire pour l’utilisateur d’afficher chaque mise à jour. Mettre à jour l’écran une seule fois, une fois que le code s’exécute, améliore les performances.

• Application.DisplayStatusBar désactivez la barre d’état. Si Application.DisplayStatusBar est défini sur faux, Excel n’affiche pas la barre d’état. Le paramètre barre d’état est distinct de l’écran de la mise à jour paramètre afin que vous puissiez toujours afficher l’état de l’opération actuelle même lorsque l’écran n’est pas mis à jour. Toutefois, si vous n’avez pas besoin d’afficher la status de chaque opération, la désactivation de la barre d’status pendant l’exécution de votre code améliore également les performances.

• Application.Calculation changez pour le calcul manuel. Si Application.Calculation est défini sur xlCalculationManual, Excel calcule uniquement le classeur lorsque l’utilisateur établit explicitement le calcul. En mode de calcul automatique, Excel détermine quand calculer. Par exemple, chaque fois que une valeur de cellule liée à une formule est modifiée, Excel recalcule la formule. Si vous changez le mode de calcul manuel, vous pouvez patienter jusqu'à ce que toutes les cellules associées à la formule soient mises à jour avant de recalculer le classeur. Par un recalcul du classeur en cas de nécessité uniquement lorsque votre code s’exécute, vous pouvez améliorer les performances.

• Application.EnableEvents désactivez les événements. Si Application.EnableEvents est défini sur faux, Excel ne déclenche pas d’événements. S’il existe des compléments à votre écoute pour les événements d’ Excel, ces compléments peuvent être gourmandes en ressources sur l’ordinateur pendant qu’ils enregistrent les événements. S’il n’est pas nécessaire pour le complément d’enregistrer les événements qui se produisent lorsque votre code s’exécute, la désactivation d’événements améliore les performances.

• ActiveSheet.DisplayPageBreaks désactivez les sauts de page. Si ActiveSheet.DisplayPageBreaks est défini sur faux, Excel n’affiche pas les sauts de page. Il n’est pas nécessaire de recalculer les sauts de page pendant l’exécution de votre code, et le calcul des sauts de page après l’exécution du code améliore les performances.

Importante

Une fois que votre code s’exécute, restaurez la fonctionnalité à son état d’origine.

L’exemple suivant montre les fonctionnalités dont vous pouvez désactiver tandis que votre macro VBA exécute.

  ' Save the current state of Excel settings.
  screenUpdateState = Application.ScreenUpdating
  statusBarState = Application.DisplayStatusBar
  calcState = Application.Calculation
  eventsState = Application.EnableEvents
  ' Note: this is a sheet-level setting.
  displayPageBreakState = ActiveSheet.DisplayPageBreaks 

  ' Turn off Excel functionality to improve performance.
  Application.ScreenUpdating = False
  Application.DisplayStatusBar = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  ' Note: this is a sheet-level setting.
  ActiveSheet.DisplayPageBreaks = False

  ' Insert your code here.

  ' Restore Excel settings to original state.
  Application.ScreenUpdating = screenUpdateState
  Application.DisplayStatusBar = statusBarState
  Application.Calculation = calcState
  Application.EnableEvents = eventsState
  ' Note: this is a sheet-level setting
  ActiveSheet.DisplayPageBreaks = displayPageBreaksState

Lire et écrire des blocs de données volumineux dans une seule opération

Optimiser votre code en réduisant explicitement le nombre de fois données est transféré entre Excel et votre code. Au lieu d’une boucle dans les cellules de manière individuelle pour obtenir ou définir une valeur, obtenir ou définir les valeurs dans la plage de cellules dans une seule ligne, à l’aide d’une variante contenant un tableau à deux dimensions pour stocker les valeurs entière. Les exemples de code suivants comparent ces deux méthodes.

L’exemple de code suivant montre un code non optimisé permet de parcourir les cellules une à une pour obtenir et définir les valeurs des cellules A1:C10000. Ces cellules ne contiennent pas de formules.

  Dim DataRange as Range
  Dim Irow as Long
  Dim Icol as Integer 
  Dim MyVar as Double 
  Set DataRange=Range("A1:C10000") 

  For Irow=1 to 10000 
      For icol=1 to 3
          ' Read the values from the Excel grid 30,000 times.
          MyVar=DataRange(Irow,Icol) 
          If MyVar > 0 then 
              ' Change the value.
              MyVar=MyVar*Myvar 
              ' Write the values back into the Excel grid 30,000 times.
              DataRange(Irow,Icol)=MyVar
          End If 
      Next Icol 
  Next Irow

L’exemple de code suivant montre un code non optimisé permet de parcourir les valeurs de cellules pour obtenir et définir les valeurs des cellules A1:C10000 en même temps. Ces cellules ne contiennent pas de formules.

  Dim DataRange As Variant
  Dim Irow As Long 
  Dim Icol As Integer 
  Dim MyVar As Double 
  ' Read all the values at once from the Excel grid and put them into an array.
  DataRange = Range("A1:C10000").Value2 

  For Irow = 1 To 10000 
      For Icol = 1 To 3 
          MyVar = DataRange(Irow, Icol) 
          If MyVar > 0 Then 
              ' Change the values in the array.
              MyVar=MyVar*Myvar 
              DataRange(Irow, Icol) = MyVar 
          End If 
      Next Icol 
  Next Irow 
  ' Write all the values back into the range at once.
  Range("A1:C10000").Value2 = DataRange 

Utilisez cette option. Valeur2 plutôt que. Valeur ou. Texte lors de la lecture des données à partir d’une plage Excel

• . Texte renvoie la valeur mise en forme d’une cellule. Cela est lent, cela peut retourner ### si l’utilisateur effectue un zoom avant et vous pouvez perdre la précision.
• . Valeur renvoie une devise VBA ou une variable date VBA si la plage a été mis en forme en tant que Date ou Devise. Cela est lent, cela peut perdre en précision et peut provoquer des erreurs lors de l’appel des fonctions de feuille de calcul.
• . Valeur2 rapide et ne modifie pas les données récupérées à partir d’ Excel.

Évitez les objets de sélection et d’ activation

Les objets de sélection et d’activation sont plus un traitement intensif que référencer directement des objets. En référençant un objet comme un Plage ouForme directement, vous pouvez améliorer les performances. Les exemples de code suivants comparent les deux méthodes.

L’exemple de code suivant montre un code non optimisé qui sélectionne chaque forme sur la feuille active et transforme le texte « Hello ».

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).Select
      Selection.Text = "Hello"
  Next i

L’exemple de code suivant montre un code non optimisé qui sélectionne chaque forme sur la feuille active et transforme le texte en « Hello ».

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).TextEffect.Text = "Hello"
  Next i

Utilisez ces optimisations des performances VBA supplémentaires

Voici une liste des optimisations des performances supplémentaires que vous pouvez utiliser dans votre code VBA:

• Renvoyer des résultats en attribuant une matrice directement à un Plage.

• Déclarer les variables avec types explicites pour éviter la charge liée à déterminer le type de données, lesquelles peuvent être incluses plusieurs fois dans une boucle pendant l’exécution de code.

• Pour des fonctions simples que vous utilisez fréquemment votre code implémenter les fonctions vous-même dans VBA au lieu d’utiliser l’objetWorksheetFunction. Pour plus d’informations, voirutiliser les fonctions définies par l’utilisateur plus rapides VBA.

• Utilisez la méthodeRange.SpecialCellspermettant de l’étendue vers le bas du nombre de cellules avec lesquelles votre code interagit.

• Vous pouvez des gains de performances si vous avez implémenté vos fonctionnalités à l’aide de l’API C dans le SDK XLL. Pour plus d’informations, voir la Documentation du kit de développement logiciel SDK XLL Excel 2010.

Vous pouvez considérer les performances et la taille des formats de fichier Excel

En commençant dans Excel 2007, Excel contient un large éventail de formats de fichier par rapport aux versions antérieures. En ignorant les variantes de format fichier Macro, le Modèle, le Complément, PDF et XPS, les trois formats principales sont XLS, XLSB et XLSX.

• Format XLS

  Le format XLS est le même format que les versions antérieures. Lorsque vous utilisez ce format, vous êtes limité à 256 colonnes et 65 536 lignes. Lorsque vous enregistrez un classeur Excel 2007 ou Excel 2010 au format XLS, Excel exécute une vérification de la compatibilité. La taille de fichier est presque identique à des versions antérieures (quelques informations supplémentaires pouvant être stockée) et les performances sont légèrement plus lentes que les versions antérieures. N’importe quelle optimisation multi-thread Qu' Excel utilise pour un ordre de calcul cellule n’est pas enregistrée au format XLS. Par conséquent, le calcul d’un classeur peut être plus lent après enregistrement du classeur au format XLS, fermer puis rouvrir le classeur.

• Format XLSB

  XLSB est le format binaire en commençant dans Excel 2007. Il est structuré sous la forme d’un dossier compressé qui contient de nombreux fichiers binaires. Il est beaucoup plus compact que le format XLS, mais la quantité de compression dépend du contenu du classeur. Par exemple, dix classeurs affichent un facteur de réduction de taille comprise entre deux à huit avec un facteur de réduction moyenne sur quatre. En commençant dans Excel 2007, les performances ouvrantes et enregistrement sont uniquement légèrement moins rapides que le format XLS.

• Format XLSX

  XLSX est au format XML en commençant dans Excel 2007 et démarre le format par défaut dans Excel 2007. Le format XLSX est un dossier compressé contenant plusieurs fichiers XML (si vous modifiez l’extension de nom de fichier .zip, vous pouvez ouvrir le dossier compressé et examiner son contenu). En règle générale, le format XLSX crée des fichiers plus volumineux que le format XLSB (1,5 fois plus grande en moyenne), mais ils sont toujours considérablement inférieures aux fichiers XLS. Attendez-vous à des heures d’ouverture et d’enregistrement soit légèrement plus de fichiers XLSB.

Ouvrir, fermer et enregistrer des classeurs

Il peut sembler qu’ouvrir, fermer et enregistrer des classeurs soit beaucoup plus lent à les calculer. Parfois, cela est dû à votre classeur volumineux, mais il peut également avoir d’autres raisons.

Si les durées d’ouverture et de fermeture de vos classeurs sont déraisonnablement élevées, ce peut être dû à l’un des facteurs suivants.

• Fichiers temporaires

  Les fichiers temporaires peuvent s’accumuler dans votre répertoire \Windows\Temp (dans Windows 95, Windows 98 et Windows ME) ou dans votre répertoire \Documents and Settings\User Name\Local Settings\Temp (dans Windows 2000 et Windows XP). Excel crée ces fichiers pour le classeur et les contrôles sont utilisés par les classeurs ouverts. Les programmes d’installation de logiciels également créent des fichiers temporaires. Si Excel cesse de répondre pour une raison quelconque, vous devrez peut-être supprimer ces fichiers.

  Trop grand nombre de fichiers temporaires peuvent entraîner des problèmes, vous devez donc parfois les nettoyer. Toutefois, si vous avez installé le logiciel qui nécessite que vous redémarrez votre ordinateur et vous ne l’avez pas encore fait, vous devez redémarrer avant de supprimer les fichiers temporaires.

 Une méthode simple pour ouvrir votre annuaire temp% consiste à partir du Windows Démarrer menu : cliquez sur Démarrer, puis cliquez sur exécuter. Dans la zone de texte, tapez %temp%, puis cliquez sur OK.

• Suivi des modifications dans un classeur partagé

  Le suivi des modifications dans un classeur partagé pousse votre taille de fichier du classeur à augmenter rapidement.

• Fichier swap fragmenté

  Assurez-vous que votre fichier d’échange Windows se trouve sur un disque disposant d’une quantité élevée d’espace libre et veillez à défragmenter ce disque régulièrement.

• Classeur avec structure protégée par mot de passe

  Un classeur dont la structure est protégée par un mot de passe (le menu >OutilsProtection>Du classeur> entrez le mot de passe facultatif) s’ouvre et se ferme beaucoup plus lentement qu’un classeur protégé sans mot de passe facultatif.

• Problèmes de la plage utilisée

  La taille des plages utilisées peuvent entraîner une ouverture lente et une taille de fichier accrues, en particulier si elles sont provoquées par les lignes ou les colonnes ayant une hauteur ou la largeur non standard. Pour plus d’informations sur les problèmes de la plage utilisée, voir Réduire la plage utilisée.

• Grand nombre de contrôles sur les feuilles de calcul

  Un grand nombre de contrôles (cases à cocher, des liens hypertexte et ainsi de suite) sur les feuilles de calcul peut ralentir l’ouverture d’un classeur en raison du nombre de fichiers temporaires utilisés. Cela peut également entraîner des problèmes ouverture ou l’enregistrement d’un classeur sur un réseau étendu (ou même un réseau LAN). Si vous rencontrez ce problème, vous devez envisager la refonte de votre classeur.

• Grand nombre de liens vers d’autres classeurs

  Si possible, ouvrez les classeurs qui vous soyez un lien avant d’ouvrir le classeur contenant les liens. Il est souvent plus rapide d’ouvrir un classeur que de lire les liens à partir d’un classeur fermé.

• Paramètres du scanner antivirus

  Certains paramètres scanner antivirus peuvent entraîner des problèmes ou avec des ralentissements à ouvrir, fermer ou l’enregistrement, en particulier sur un serveur. Si vous pensez que c’est peut-être le problème, essayez d’éteindre temporairement le scanner antivirus.

• Ralentir le calcul cause le ralentissement d’ouverture et d’enregistrement

  Dans certaines circonstances, Excel recalcule votre classeur lorsque celui-ci s’ouvre ou enregistre. Si le temps de calcul pour votre classeur est long et pose un problème, assurez-vous que le calcul soit défini surmanuelet envisagez de désactiver l’optioncalculer avant enregistrer ( Outils>Options>calcul).

• Fichiers de barre d’outils (.xlb)

  Vérifiez la taille de votre fichier de barre d’outils. Un fichier de barre d’outils standard est compris entre 10 Ko et 20 Ko. Vous pouvez rechercher vos fichiers XLB en recherchant *.xlb à l’aide de Windows search. Chaque utilisateur dispose d’un fichier XLB unique. Ajouter, modifier ou personnaliser les barres d’outils permet d’augmenter la taille de votre fichier toolbar.xlb. La suppression du fichier supprime toutes vos personnalisations de barre d’outils (en le renommant « toolbar.OLD » est préférable). Un nouveau fichier XLB est créé la prochaine fois que vous ouvrez Excel.

Utilisez ces optimisations des performances supplémentaires

Vous pouvez apporter des améliorations aux performances dans les sections suivantes.

• PivotTables

  Les tableaux croisés dynamiques fournissent un moyen efficace pour synthétiser de grandes quantités de données.

  • Totaux comme résultat final. Si vous avez besoin de produire des totaux et sous-totaux dans le cadre de résultat final de votre classeur, essayez d’utiliser des tableaux croisés dynamiques.

  • Totaux en tant que résultats intermédiaires. Les tableaux croisés dynamiques sont très utiles lorsqu’il s’agit de générer des rapports de synthèse, mais évitez de créer des formules qui utilisent des tableaux croisés dynamiques comme totaux et sous-totaux intermédiaires dans votre chaîne de calcul, à moins de pouvoir garantir les conditions suivantes :

  • le tableau croisé dynamique a été actualisé correctement durant le calcul ;

  • Le tableau croisé dynamique n’a pas été modifié afin que les informations soient toujours visibles.

  Si vous voulez toujours utiliser des tableaux croisés dynamiques en tant que résultats intermédiaires, utilisez la fonctionGETPIVOTDATA.

• Formats et validation des données conditionnels

  Les mises en forme conditionnelles et la validation des données sont parfaits, mais à l’aide d’un grand nombre d'entre eux peuvent ralentir considérablement le calcul. Si la cellule est affichée, chaque formule mise en forme conditionnelle est évaluée à chaque calcul et l’affichage de la cellule qui contient la mise en forme conditionnelle est actualisé. Le modèle objet Excel comporte une propriétéWorksheet.EnableFormatConditionsCalculationafin que vous puissiez activer ou désactiver le calcul de mises en forme conditionnelles.

• Noms définis

  Les noms définis sont un des fonctionnalités plus puissantes dans Excel, mais ils prennent un temps de calcul supplémentaire. L’utilisation de noms qui font référence à d’autres feuilles de calcul ajoute un niveau de complexité supplémentaires pour le processus de calcul. Par ailleurs, essayez d’éviter les noms imbriquées (noms qui font référence à d’autres noms).

  Étant donné que les noms sont calculés chaque fois une formule fait référence à celles-ci est calculée, vous devez éviter de placer des calculs intensifs formules ou fonctions dans les noms définis. Dans ce cas, il peut être sensiblement plus rapide pour mettre vos calculs intensifs formule ou une fonction dans une cellule rechange quelque part et faire référence à cette cellule à la place, directement ou en utilisant un nom.

• Formules qui sont utilisées uniquement de manière occasionnelle

  De nombreux classeurs contiennent un nombre significatif de formules et recherches qui concernent le moment où nous recevons des données d’entrée dans la forme appropriée pour les calculs ou sont utilisées comme mesures de défense contre les modifications dans la taille ou la forme des données. Lorsque plusieurs blocs de formules sont utilisées uniquement de manière occasionnelle, vous pouvez copier et coller des valeurs spéciales pour temporairement éviter les formules, qui peuvent être placées dans un classeur distinct, rarement ouvert. Étant donné que les erreurs de feuille de calcul sont souvent dues au fait de ne pas avoir remarqué que les formules ont été converties en valeurs, la méthode classeur distinct peut être préférable.

• Utiliser une mémoire insuffisante

  La version 32 bits d’Excel permettent de 2 Go de RAM ou vers le haut pour 4 Go de RAM pour les versions grand adresse conscients 32 bits d’Excel 2013 et 2016. Toutefois, l’ordinateur qui exécute Excel nécessite également de ressources de mémoire. Par conséquent, si vous n’avez que 2 Go de RAM sur votre ordinateur, Excel ne peuvent pas bénéficier de 2 Go complet, car une partie de la mémoire est allouée pour le système d’exploitation et d’autres programmes en cours d’exécution. Pour optimiser les performances d’ Excel sur un ordinateur 32 bits, nous vous recommandons que l’ordinateur ait moins 3 Go de RAM.

  La version 64 bits d’ Excel n’a pas de limite de 2 Go et jusqu’à 4 Go. Pour plus d’informations, voir la section « jeux de données volumineux et la version 64 bits d’Excel » dans Excel performances : améliorations des performances et limites.

Conclusion

Cet article a couvert les méthodes pour optimiser la fonctionnalité Excel tels que des liens, les recherches, formules, fonctions et code VBA pour éviter les obstacles courants et améliorer les performances.

Voir aussi

• Performance Excel: Amélioration des performances de calcul
• Performances d' Excel: Améliorations en matière de performances et de limites
• Portail pour développeurs Excel

Assistance et commentaires

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