Performances d'Excel: Conseils pour éliminer les obstacles aux performancesExcel performance: Tips for optimizing performance obstructions

S’applique à: Excel |Excel 2013 | Office 2016 | Visual StudioApplies to: Excel | Excel 2013 | Office 2016 | VBA

Suivez ces conseils relatifs à la suppression des obstacles aux nombreuses et fréquentes performances dans Excel.Follow these tips for optimizing many frequently occurring performance obstructions in Excel.

Découvrez comment améliorer les performances de types références ainsi que les liens.Learn how to improve performance related to types of references and links.

N’utilisez pas le référencement de transfert et le référencement vers l’arrièreDo not use forward referencing and backward referencing

Pour augmenter la clarté et éviter les erreurs, créez vos formules afin que qu’ils ne réfèrent pas au transfert (à droite ou en dessous) à d’autres formules ou aux cellules.To increase clarity and avoid errors, design your formulas so that they do not refer forward (to the right or below) to other formulas or cells. 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é.Forward referencing usually does not affect calculation performance, except in extreme cases for the first calculation of a workbook, where it might take longer to establish a sensible calculation sequence if there are many formulas that need to have their calculation deferred.

Réduire l’utilisation de références circulaires avec itérationMinimize use of circular references with iteration

Calculer des références circulaires avec itérations est lent, car plusieurs calculs sont nécessaires, et ces calculs sont mono-filaire.Calculating circular references with iterations is slow because multiple calculations are needed, and these calculations are single-threaded. 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.Frequently you can "unroll" the circular references by using algebra so that iterative calculation is no longer needed. 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.For example, in cash flow and interest calculations, try to calculate the cash flow before interest, calculate the interest, and then calculate the cash flow including the interest.

Excel calcule les références circulaires feuille-par-feuille sans considérant des dépendances.Excel calculates circular references sheet-by-sheet without considering dependencies. Par conséquent, vous obtenez généralement un calcul lent si vos références circulaires recouvrent plus d’une feuille de calcul.Therefore, you usually get slow calculation if your circular references span more than one worksheet. 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.Try to move the circular calculations onto a single worksheet or optimize the worksheet calculation sequence to avoid unnecessary calculations.

Avant de commencer les calculs itératifs, Excel doit recalculer le classeur afin d’identifier les références circulaires et leurs dépendants.Before the iterative calculations start, Excel must recalculate the workbook to identify all the circular references and their dependents. Ce processus est égal à deux ou trois itérations du calcul.This process is equal to two or three iterations of the calculation.

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.After the circular references and their dependents are identified, each iteration requires Excel to calculate not only all the cells in the circular reference, but also any cells that depend on the cells in the circular reference chain, together with volatile cells and their dependents. 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é.If you have a complex calculation that depends on cells in the circular reference, it can be faster to isolate this into a separate closed workbook and open it for recalculation after the circular calculation has converged.

Il est important de réduire le nombre de cellules dans le calcul circulaire et le temps de calcul pris par ces cellules.It is important to reduce the number of cells in the circular calculation and the calculation time that is taken by these cells.

Éviter les liens entre classeurs lorsqu’il est possible ; ils peuvent être lents, être facilement incorrectes et pas toujours facile à rechercher et corriger.Avoid inter-workbook links when it is possible; they can be slow, easily broken, and not always easy to find and fix.

L’utilisation de classeurs moins volumineux est généralement mieux, mais pas toujours, que d’utiliser de nombreux classeurs plus petits.Using fewer larger workbooks is usually, but not always, better than using many smaller workbooks. 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.Some exceptions to this might be when you have many front-end calculations that are so rarely recalculated that it makes sense to put them in a separate workbook, or when you have insufficient RAM.

Essayez d’utiliser des références de cellule directes simple qui travaillent sur des classeurs fermés.Try to use simple direct cell references that work on closed workbooks. En procédant ainsi, vous pouvez éviter le recalcul detous vos classeurs liées lorsque vous recalculezn’importe quel classeur.By doing this, you can avoid recalculating all your linked workbooks when you recalculate any workbook. 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.Also, you can see the values Excel has read from the closed workbook, which is frequently important for debugging and auditing the workbook.

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.If you cannot avoid using linked workbooks, try to have them all open instead of closed, and open the workbooks that are linked to before you open the workbooks that are linked from.

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.Using many worksheets can make your workbook easier to use, but generally it is slower to calculate references to other worksheets than references within worksheets.

Réduire la plage utiliséeMinimize the used range

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.To save memory and reduce file size, Excel tries to store information about only the area on a worksheet that was used. Cette option est appelée publication utilisée.This is called the used range. 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.Sometimes various editing and formatting operations extend the used range significantly beyond the range that you would currently consider used. Cela peut provoquer des obstacles de performances et des obstacles taille de fichier.This can cause performance obstructions and file-size obstructions.

Vous pouvez vérifier la plage utilisée visible sur une feuille de calcul à l’aide de Ctrl + Fin.You can check the visible used range on a worksheet by using Ctrl+End. 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.Where this is excessive, you should consider deleting all the rows and columns below and to the right of your real last used cell, and then saving the workbook. Créez une copie de sauvegarde en premier.Create a backup copy first. 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.If you have formulas with ranges that extend into or refer to the deleted area, these ranges will be reduced in size or changed to #N/A.

Autoriser les données supplémentairesAllow for extra data

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.When you frequently add rows or columns of data to your worksheets, you need to find a way of having your Excel formulas automatically refer to the new data area, instead of trying to find and change your formulas every time.

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.You can do this by using a large range in your formulas that extends well beyond your current data boundaries. 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.However, this can cause inefficient calculation under certain circumstances, and it is difficult to maintain because deleting rows and columns can decrease the range without you noticing.

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.Starting in Excel 2007, you can use structured table references, which automatically expand and contract as the size of the referenced table increases or decreases.

Cela présente plusieurs avantages :This solution has several advantages:

  • Moins d’inconvénients de performances existent que les alternatives des plages référençant et dynamiques d’une colonne entière.Fewer performance disadvantages exist than the alternatives of whole column referencing and dynamic ranges.

  • Il est facile de disposer de plusieurs tableaux de données sur une feuille de calcul.It is easy to have multiple tables of data on a single worksheet.

  • Les formules qui sont également incorporées dans le tableau développent et contractent avec les données.Formulas that are embedded in the table also expand and contract with the data.

Vous pouvez également utiliser des références de colonne et ligne entièresAlternatively, use whole column and row references

Une autre approche consiste à utiliser une référence de colonne entière, par exemple $A:$ A.An alternative approach is to use a whole column reference, for example $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.This reference returns all the rows in Column A. Therefore, you can add as much data as you want, and the reference will always include it.

Cette solution comporte des avantages et des inconvénients :This solution has both advantages and disadvantages:

  • 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.Many Excel built-in functions (SUM, SUMIF) calculate whole column references efficiently because they automatically recognize the last used row in the column. 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.However, array calculation functions like SUMPRODUCT either cannot handle whole column references or calculate all the cells in the column.

  • De nombreuses fonctions Excel intégrées 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.User-defined functions do not automatically recognize the last-used row in the column and, therefore, frequently calculate whole column references inefficiently. Toutefois, il est facile de programmer des fonctions définies par l’utilisateur afin qu’elles reconnaissent la ligne utilisée en dernier.However, it is easy to program user-defined functions so that they recognize the last-used row.

  • Il est difficile d’utiliser des références de colonne entière lorsque vous avez plusieurs tableaux de données dans une feuille de calcul.It is difficult to use whole column references when you have multiple tables of data on a single worksheet.

  • 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.In Excel 2007 and later versions, array formulas can handle whole-column references, but this forces calculation for all the cells in the column, including empty cells. Cela peut être ralentir le calcul, en particulier pour 1 millions de lignes.This can be slow to calculate, especially for 1 million rows.

Vous pouvez également utiliser des plages dynamiquesAlternatively, use dynamic ranges

À 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.By using the OFFSET or INDEX and COUNTA functions in the definition of a named range, you can make the area that the named range refers to dynamically expand and contract. Par exemple, créez un nom défini à l’aide de formules suivantes :For example, create a defined name using one of the following formulas:

  =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.When you use the dynamic range name in a formula, it automatically expands to include new entries.

À 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.Using the INDEX formula for a dynamic range is generally preferable to the OFFSET formula because OFFSET has the disadvantage of being a volatile function that will be calculated at every recalculation.

Les performances baissent, car la fonctionNBVALà l’intérieur de la formule de plage dynamique doit examiner plusieurs lignes.Performance decreases because the COUNTA function inside the dynamic range formula must examine many rows. 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:You can minimize this performance decrease by storing the COUNTA part of the formula in a separate cell or defined name, and then referring to the cell or name in the dynamic range:

 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.You can also use functions such as INDIRECT to construct dynamic ranges, but INDIRECT is volatile and always calculates single-threaded.

Les plages dynamiques comprennent les avantages et inconvénients suivants :Dynamic ranges have the following advantages and disadvantages:

  • Les plages dynamiques fonctionnent également pour limiter le nombre de calculs effectués par des formules de tableau.Dynamic ranges work well to limit the number of calculations performed by array formulas.

  • Utiliser plusieurs plages dynamiques au sein d’une seule colonne nécessite des fonctions spéciales de comptage.Using multiple dynamic ranges within a single column requires special-purpose counting functions.

  • Utiliser plusieurs plages dynamiques peut réduire les performances.Using many dynamic ranges can decrease performance.

Améliorer le temps de calcul rechercheImprove lookup calculation time

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.In Office 365 version 1809 and later, Excel's VLOOKUP, HLOOKUP, and MATCH for exact match on unsorted data is much faster than ever before when looking up multiple columns (or rows with HLOOKUP) from the same table range.

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.That said, for earlier Excel versions, Lookups continue to be frequently significant calculation obstructions. Heureusement, il existe plusieurs façons d’améliorer le temps de calcul de recherche.Fortunately, there are many ways of improving lookup calculation time. 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.If you use the exact match option, the calculation time for the function is proportional to the number of cells scanned before a match is found. Pour les recherches sur les grandes plages, cette fois peut être significative.For lookups over large ranges, this time can be significant.

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.Lookup time using the approximate match options of VLOOKUP, HLOOKUP, and MATCH on sorted data is fast and is not significantly increased by the length of the range you are looking up. Les caractéristiques sont la même que la recherche binaire.Characteristics are the same as binary search.

Comprendre les options de rechercheUnderstand lookup options

Assurez-vous de bien comprendre les options de type correspondance et de type plage de recherche dans MATCH, RECHERCHEV, etRECHERCHEH.Ensure that you understand the match-type and range-lookup options in MATCH, VLOOKUP, and HLOOKUP.

L'exemple de code suivant illustre la syntaxe de la fonction MATCH.The following code example shows the syntax for the MATCH function. Pour plus d’informations, voir la méthodeMATCHde l’objetWorksheetFunction.For more information, see the Match method of the WorksheetFunction object.

  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).Matchtype=1 returns the largest match less than or equal to the lookup value when the lookup array is sorted ascending (approximate match). Si la matrice de recherche n’est pas triée par ordre croissant, CORRESPONDANCE renverra une réponse incorrecte.If the lookup array is not sorted ascending, MATCH will return an incorrect answer. L’option par défaut est correspondance approximative triée par ordre croissant.The default option is approximate match sorted ascending.

  • Matchtype= 0 demandes une correspondance exacte et part du principe que les données ne sont pas triées.Matchtype=0 requests an exact match and assumes that the data is not sorted.

  • 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).Matchtype=-1 returns the smallest match greater than or equal to the lookup value if the lookup array is sorted descending (approximate match).

L'exemple de code suivant illustre la syntaxe des fonctionsRECHERCHEV etRECHERCHEH.The following code example shows the syntax for the VLOOKUP and HLOOKUP functions. Pour plus d’informations, voir les méthodesRECHERCHEVet RECHERCHEHde l’objetWorksheetFunction.For more information, see the VLOOKUP and HLOOKUP methods of the WorksheetFunction object.

  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).Range-lookup=TRUE returns the largest match less than or equal to the lookup value (approximate match). Il s’agit de l’option par défaut.This is the default option. La plage de tableau doit être triée par ordre croissant.Table array must be sorted ascending.

  • Range-lookup=FALSE demandes une correspondance exacte et part du principe que les données ne sont pas triées.Range-lookup=FALSE requests an exact match and assumes the data is not sorted.

Évitez d’exécuter les recherches sur des données non triées autant que possible, car il est lent.Avoid performing lookups on unsorted data where possible because it is slow. 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.If your data is sorted, but you want an exact match, see Use two lookups for sorted data with missing values.

Utiliser INDEXE et CORRESPONDANCES ou OFFSET au lieu de RECHERCHEVUse INDEX and MATCH or OFFSET instead of VLOOKUP

Essayez d’utiliser les fonctionsINDEXE etCORRESPONDANCEà la place de RECHERCHEV.Try using the INDEX and MATCH functions instead of VLOOKUP. 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.Although VLOOKUP is slightly faster (approximately 5 percent faster), simpler, and uses less memory than a combination of MATCH and INDEX, or OFFSET, the additional flexibility that MATCH and INDEX offer often enables you to significantly save time. Par exemple, vous pouvez stocker le résultat d’une exacte CORRESPONDANCE dans une cellule et réutiliser dans plusieurs instructionsINDEXE.For example, you can store the result of an exact MATCH in a cell and reuse it in several INDEX statements.

La fonctionINDEXErapide et une fonction non-volatile, ce qui accélère le recalcul.The INDEX function is fast and is a non-volatile function, which speeds up recalculation. 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.The OFFSET function is also fast; however, it is a volatile function, and it sometimes significantly increases the time taken to process the calculation chain.

Il est facile de convertirRECHERCHEV à INDEXE et CORRESPONDANCE.It is easy to convert VLOOKUP to INDEX and MATCH. Les deux phrases suivantes renvoient la même réponse:The following two statements return the same answer:

 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 recherchesSpeed up lookups

Car les recherches de correspondance exacte peuvent être lentes, considérez les options suivantes pour améliorer les performances:Because exact match lookups can be slow, consider the following options for improving performance:

  • Utiliser une feuille de calcul.Use one worksheet. Il est plus rapide de conserver les recherches et les données de la même feuille.It is faster to keep lookups and data on the same sheet.

  • Lorsque vous pouvez TRIER les données en premier (TRIER est rapide) et utiliser la méthode correspondance approximative.When you can, SORT the data first (SORT is fast), and use approximate match.

  • Lorsque vous devez utiliser une liste de choix correspondance exacte, restreindre la plage de cellules doit être analysée à un minimum.When you must use an exact match lookup, restrict the range of cells to be scanned to a 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.Use tables and structured references or dynamic range names rather than referring to a large number of rows or columns. Vous pouvez quelque fois recalculer une limite de plage inférieure et une limite de plage supérieure pour la recherche.Sometimes you can pre-calculate a lower-range limit and upper-range limit for the lookup.

Utiliser deux recherches des données triées contenant les valeurs manquantesUse two lookups for sorted data with missing values

Deux correspondances approximatives sont sensiblement plus rapides qu’une correspondance exacte pour une recherche de plus de quelques lignes.Two approximate matches are significantly faster than one exact match for a lookup over more than a few rows. (Le point de rentabilité concerne 20-10 lignes.)(The breakeven point is about 10-20 rows.)

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 you can sort your data but still cannot use approximate match because you cannot be sure that the value you are looking up exists in the lookup range, you can use this formula:

  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.The first part of the formula works by doing an approximate lookup on the lookup column itself.

  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:You can check if the answer from the lookup column is the same as the lookup value (in which case you have an exact match) by using the following formula:

  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.If this formula returns True, you have found an exact match, so you can do the approximate lookup again, but this time, return the answer from the column you want.

  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 ».If the answer from the lookup column did not match the lookup value, you have a missing value, and the formula returns "notexist".

N’oubliez pas que si vous recherchez une valeur inférieure à la plus petite valeur dans la liste, vous recevez une erreur.Be aware that if you look up a value smaller than the smallest value in the list, you receive an error. Vous pouvez gérer cette erreur à l’aide SIERREUR, ou en ajoutant une petite valeur test à la liste.You can handle this error by using IFERROR, or by adding a small test value to the list.

Utilisez la fonction SIERREUR pour les données non triées avec les valeurs manquantesUse IFERROR function for unsorted data with missing values

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.If you must use exact match lookup on unsorted data, and you cannot be sure whether the lookup value exists, you often must handle the #N/A that is returned if no match is found. Depuis Excel 2007, vous pouvez utiliser la fonctionSIERREUR qui est simple et rapide.Beginning with Excel 2007, you can use the IFERROR function, which is both simple and fast.

  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.In earlier versions, a simple but slow way is to use an IF function that contains two lookups.

  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.You can avoid the double exact lookup if you use exact MATCH once, store the result in a cell, and then test the result before doing an 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.If you cannot use two cells, use COUNTIF. Il est généralement plus rapide qu’une recherche de correspondance exacte.It is generally faster than an exact match lookup.

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

Utiliser INDEX et CORRESPONDANCE pour les recherches de correspondance exacte sur plusieurs colonnesUse MATCH and INDEX for exact match lookups on multiple columns

Vous pouvez souvent réutiliser unCORRESPONDANCEstockée exacte autant de fois.You can often reuse a stored exact MATCH many times. 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.For example, if you are doing exact lookups on multiple result columns, you can save time by using one MATCH and many INDEX statements rather than many VLOOKUP statements.

Ajouter une colonne supplémentaire pour le CORRESPONDANCE pour stocker le résultat (stored_row) et pour chaque colonne résultat utiliser comme suit :Add an extra column for the MATCH to store the result (stored_row), and for each result column use the following:

  INDEX(Lookup_Range,stored_row,column_number)

Vous pouvez également utiliser RECHERCHEV dans une formule matricielle.Alternatively, you can use VLOOKUP in an array formula. (Des formules de tableau doivent être entrées à l’aide de Ctrl +-Maj + Entrée.(Array formulas must be entered by using Ctrl+-Shift+Enter. Excel ajoute le {et} vous montre qu’il s’agit d’une formule de tableau).Excel will add the { and } to show you that this is an array formula).

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

Utiliser un INDEX pour un ensemble de lignes ou des colonnes continuesUse INDEX for a set of contiguous rows or columns

Vous pouvez également revenir le nombre de cellules à partir de l’opération d’un recherche.You can also return many cells from one lookup operation. 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).To look up several contiguous columns, you can use the INDEX function in an array formula to return multiple columns at once (use 0 as the column number). Vous pouvez également utiliser la fonctionINDEX pour renvoyer plusieurs lignes en même temps.You can also use the INDEX function to return multiple rows at one time.

  {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.This returns column A to column J from the stored row created by a previous MATCH statement.

Utilisez CORRESPONDANCE pour renvoyer un bloc de cellules rectangulaireUse MATCH to return a rectangular block of cells

Vous pouvez utiliser les fonctionsCORRESPONDANCE et OFFSETpour renvoyer un bloc de cellules rectangulaire.You can use the MATCH and OFFSET functions to return a rectangular block of cells.

Utiliser INDEX et CORRESPONDANCE pour la recherche à deux dimensionsUse MATCH and INDEX for two-dimensional lookup

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.You can efficiently do a two-dimensional table lookup by using separate lookups on the rows and columns of a table by using an INDEX function with two embedded MATCH functions, one for the row and one for the column.

Utiliser une plage sous-ensemble pour une recherche index multipleUse a subset range for multiple-index lookup

Dans les feuilles de calcul volumineuses, vous devrez fréquemment rechercher à l’aide de plusieurs index, par exemple, vous recherchez des volumes de produit dans un(e) pays/région.In large worksheets, you may frequently need to look up by using multiple indexes, such as looking up product volumes in a country. Pour ce faire, vous pouvez concaténer les index et effectuer la recherche à l’aide des valeurs de recherche concaténées.To do this, you can concatenate the indexes and perform the lookup by using concatenated lookup values. Toutefois, il s’agit inefficace pour deux raisons:However, this is inefficient for two reasons:

  • La concaténation de chaînes est une opération de calculs intensifs.Concatenating strings is a calculation-intensive operation.

  • La recherche couvrira une grande plage.The lookup will cover a large range.

Il est souvent plus efficace pour calculer une plage sous-ensemble pour la recherche (par exemple, à la première et dernière ligne du pays et puis recherche de produit au sein de cette plage sous-ensemble).It is often more efficient to calculate a subset range for the lookup (for example, by finding the first and last row for the country, and then looking up the product within that subset range).

Tenir compte des options pour la recherche en 3DConsider options for three-dimensional lookup

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.To look up the table to use in addition to the row and the column, you can use the following techniques, focusing on how to make Excel look up or choose the table.

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.If each table that you want to look up (the third dimension) is stored as a set of named structured tables, range names, or as a table of text strings that represent ranges, you might be able to use the CHOOSE or INDIRECT functions.

  • UtiliserCHOISIR et les noms de plages peuvent être une méthode efficace.Using CHOOSE and range names can be an efficient method. CHOISIR n’est pas volatile, mais c’est mieux adapté pour un nombre de taille relativement modeste de tableaux en charge.CHOOSE is not volatile, but it is best-suited to a relatively small number of tables. Cet exemple utilise de manière dynamiqueTableLookup_Value pour choisir le nom de la plage (TableName1, TableName2, ...) à utiliser pour le tableau de choix.This example dynamically uses TableLookup_Value to choose which range name (TableName1, TableName2, ...) to use for the lookup table.

      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.The following example uses the INDIRECT function and TableLookup_Value to dynamically create the sheet name to use for the lookup table. Cette méthode présente l’avantage d’être simple et capable de gérer un grand nombre de tableaux.This method has the advantage of being simple and able to handle a large number of tables. É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.Because INDIRECT is a volatile single-threaded function, the lookup is single-thread calculated at every calculation even if no data has changed. Utiliser cette méthode est lente.Using this method is slow.

      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.You could also use the VLOOKUP function to find the name of the sheet or the text string to use for the table, and then use the INDIRECT function to convert the resulting text into a range.

      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.Another technique is to aggregate all your tables into one giant table that has an additional column that identifies the individual tables. Vous pouvez ensuite utiliser les techniques pour une recherche index multiple illustrée dans les exemples précédents.You can then use the techniques for multiple-index lookup shown in the previous examples.

Utiliser la recherche génériqueUse wildcard lookup

Les fonctionsCORRESPONDANCE, RECHERCHEV, et RECHERCHEH vous permettent d’utiliser les caractères génériques ?The MATCH, VLOOKUP, and HLOOKUP functions allow you to use the wildcard characters ? (un seul caractère) et ** * ** (un nombre quelconque de caractères ou aucun caractère) sur des correspondances exactes par ordre alphabétique.(any single character) and * (no character or any number of characters) on alphabetical exact matches. Il peut arriver que vous puissiez utiliser cette méthode pour éviter plusieurs correspondances.Sometimes you can use this method to avoid multiple matches.

Optimiser les formules de tableau et SOMMEPRODUITOptimize array formulas and SUMPRODUCT

Les formules de tableaux et les fonctionsSOMMEPRODsont puissantes, mais vous devez les gérer avec soin.Array formulas and the SUMPRODUCT function are powerful, but you must handle them carefully. Une seule formule matricielle peut nécessiter plusieurs calculs.A single array formula might require many calculations.

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.The key to optimizing the calculation speed of array formulas is to ensure that the number of cells and expressions that are evaluated in the array formula is as small as 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.Remember that an array formula is a bit like a volatile formula: if any one of the cells that it references has changed, is volatile, or has been recalculated, the array formula calculates all the cells in the formula and evaluates all the virtual cells it needs to do the calculation.

Pour optimiser la vitesse de calcul de formules matricielles:To optimize the calculation speed of array formulas:

  • Prendre des expressions et des références de plage des formules de tableau en lignes et des colonnes distinctes d’assistance.Take expressions and range references out of the array formulas into separate helper columns and rows. Cela en fait une meilleure utilisation du processus de recalcul intelligent dans Excel.This makes much better use of the smart recalculation process in Excel.

  • Ne pas faire référence aux lignes complètes, ou plusieurs lignes et colonnes dont vous avez besoin.Do not reference complete rows, or more rows and columns than you need. 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.Array formulas are forced to calculate all the cell references in the formula even if the cells are empty or unused. 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.With 1 million rows available starting in Excel 2007, an array formula that references a whole column is extremely slow to calculate.

  • À 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.Starting in Excel 2007, use structured references where you can to keep the number of cells that are evaluated by the array formula to a minimum.

  • Dans les versions antérieures à Excel 2007, utilisez des noms de plage dynamique autant que possible.In versions earlier than Excel 2007, use dynamic range names where possible. Bien que qu’ils soient volatiles, il est judicieux, car ils réduisent la taille des plages.Although they are volatile, it is worthwhile because they minimize the size of the ranges.

  • Soyez prudent avec les formules matricielles qui font référence à une ligne et une colonne : cela force le calcul d’une plage rectangulaire.Be careful with array formulas that reference both a row and a column: this forces the calculation of a rectangular range.

  • UtilisezSUMPRODUCT si possible, il est légèrement plus rapide que la formule de tableau équivalent.Use SUMPRODUCT if possible; it is slightly faster than the equivalent array formula.

Tenir compte des options pour l’utilisation de somme pour les formules à plusieurs conditionsConsider options for using SUM for multiple-condition array formulas

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.You should always use the SUMIFS, COUNTIFS, and AVERAGEIFS functions instead of array formulas where you can because they are much faster to calculate. Excel 2016 présente les fonctions rapidesMAXIFS et MINIFS.Excel 2016 introduces fast MAXIFS and MINIFS functions.

Dans les versions antérieures à Excel 2007, les formules matricielles sont souvent utilisées pour calculer une somme avec plusieurs conditions.In versions earlier than Excel 2007, array formulas are often used to calculate a sum with multiple 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.This is relatively easy to do, especially if you use the Conditional Sum Wizard in Excel, but it is often slow. Généralement, il existe des manières beaucoup plus rapides d’obtenir le même résultat.Usually there are much faster ways of getting the same result. 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.If you have only a few multiple-condition SUMs, you may be able to use the DSUM function, which is much faster than the equivalent array formula.

Si vous devez utiliser des formules de tableau, quelques bonnes méthodes de les accélérer comme les suivantes:If you must use array formulas, some good methods of speeding them up are as follows:

  • Utiliser des noms de plage dynamique ou des références structurées tableau pour réduire le nombre de cellules.Use dynamic range names or structured table references to minimize the number of cells.

  • 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.Split out the multiple conditions into a column of helper formulas that return True or False for each row, and then reference the helper column in a SUMIF or array formula. 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.This might not appear to reduce the number of calculations for a single array formula; however, most of the time it enables the smart recalculation process to recalculate only the formulas in the helper column that need to be recalculated.

  • Vous pouvez considérer la concaténation de toutes les conditions dans une seule condition, puis en utilisant SUMIF.Consider concatenating together all the conditions into a single condition, and then using 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.If the data can be sorted, count groups of rows and limit the array formulas to looking at the subset groups.

Définir les priorités condition multiple SUMIFS, COUNTIFS, et les autres fonctions de famille IFSPrioritize multiple-condition SUMIFS, COUNTIFS, and other IFS family functions

Ces fonctions évaluent chacune des conditions de gauche à droite de ces opérations.These functions evaluate each of the conditions from left to right in turn. 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.Therefore, it is more efficient to put the most restrictive condition first, so that subsequent conditions only need to look at the smallest number of rows.

Tenez compte des options pour l’utilisation de SOMMEPRODUCT pour les formules à plusieurs conditionsConsider options for using SUMPRODUCT for multiple-condition array formulas

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.Starting in Excel 2007, you should always use the SUMIFS, COUNTIFS, and AVERAGEIFS functions, and in Excel 2016 MAXIFS and MINIFS functions, instead of SUMPRODUCT formulas where possible.

Dans les versions antérieures, il existe quelques avantages à l’utilisation des formules matriciellesSUMPRODUCT au lieu de SUM :In earlier versions, there are a few advantages to using SUMPRODUCT instead of SUM array formulas:

  • SUMPRODUCT ne doit pas être des entrées de matrice à l’aide de Ctrl + Maj + Entrée.SUMPRODUCT does not have to be array-entered by using Ctrl+Shift+Enter.

  • SUMPRODUCT est généralement légèrement plus rapide (5 à 10 %).SUMPRODUCT is usually slightly faster (5 to 10 percent).

Vous pouvez utiliser es formules à plusieurs conditions SUMPRODUCTcomme les suivantes:You can use SUMPRODUCT for multiple-condition array formulas as follows:

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

Dans cet exemple, Condition1 et Condition2 sont les expressions conditionnelles comme $A$1:$A$10000<=$Z4.In this example, Condition1 and Condition2 are conditional expressions such as $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.Because conditional expressions return True or False instead of numbers, they must be coerced to numbers inside the SUMPRODUCT function. Vous pouvez le faire à l’aide de deux signes moins (--), ou en ajoutant 0 (+ 0), ou en multipliant par 1 (x1).You can do this by using two minus signs (--), or by adding 0 (+0), or by multiplying by 1 (x1). Utiliser** -- ** est légèrement plus rapide + 0 ou x1.Using -- is slightly faster than +0 or 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.Note that the size and shape of the ranges or arrays that are used in the conditional expressions and range to sum must be the same, and they cannot contain entire columns.

Vous pouvez également directement multiplier les termes du contrat à l’intérieur SUMPRODUCT plutôt que de les séparer par des virgules:You can also directly multiply the terms inside SUMPRODUCT rather than separate them by commas:

  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.This is usually slightly slower than using the comma syntax, and it gives an error if the range to sum contains a text value. 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.However, it is slightly more flexible in that the range to sum may have, for example, multiple columns when the conditions have only one column.

Utiliser SUMPRODUCT pour multiplier et ajouter des plages et des tableauxUse SUMPRODUCT to multiply and add ranges and arrays

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.In cases like weighted average calculations, where you need to multiply a range of numbers by another range of numbers and sum the results, using the comma syntax for SUMPRODUCT can be 20 to 25 percent faster than an array-entered SUM.

  {=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.These three formulas all produce the same result, but the third formula, which uses the comma syntax for SUMPRODUCT, takes only about 77 percent of the calculation time that the other two formulas need.

N’oubliez pas la matrice potentielle et les obstacles de fonction de calcul Be aware of potential array and function calculation obstructions

Le moteur de calcul dans Excel est optimisé pour exploiter les formules et fonctions qui font référence à des plages.The calculation engine in Excel is optimized to exploit array formulas and functions that reference ranges. Toutefois, certaines dispositions inhabituelles de ces formules et fonctions peuvent parfois, mais pas toujours, causer des calculs accrus de manière considérable.However, some unusual arrangements of these formulas and functions can sometimes, but not always, cause significantly increased calculation time.

Si vous trouvez un obstacle de calcul qui implique des formules et des fonctions de plage, vous devez ressembler pour les éléments suivants:If you find a calculation obstruction that involves array formulas and range functions, you should look for the following:

  • Références qui se chevauchent partiellement.Partially overlapping references.

  • 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.Array formulas and range functions that reference part of a block of cells that are calculated in another array formula or range function. Cette situation peut se produire fréquemment dans les analyses de série temps.This situation can frequently occur in time series analysis.

  • 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.One set of formulas referencing by row, and a second set of formulas referencing the first set by column.

  • Un vaste ensemble de formules de tableau à une ligne couvrant un bloc de colonnes, avec les fonctionsSUM fonctions au pied de chaque colonne.A large set of single-row array formulas covering a block of columns, with SUM functions at the foot of each column.

Utiliser des fonctions de manière efficaceUse functions efficiently

Les fonctions étendent sensiblement la puissance d’ Excel, mais la manière dans lequel vous utilisez peut influencer souvent la durée de calcul.Functions significantly extend the power of Excel, but the way in which you use them can often affect calculation time.

Évitez les fonctions à fil uniqueAvoid single-threaded functions

La plupart des fonctions Excel plus natives fonctionnent parfaitement avec multithread.Most native Excel functions work well with multi-threaded calculation. Toutefois, autant que possible, évitez d’utiliser les fonctions de threads unique suivantes :However, where possible, avoid using the following single-threaded functions:

  • Les fonctions VBA et automatisation définies par l’utilisateur (UDF), mais à base de XLL peuvent être multi-threadVBA and Automation user-defined functions (UDFs), but XLL-based UDFs can be multi-threaded
  • PHONÉTIQUEPHONETIC
  • CELL lorsque soit l’argument « format » ou « adress » est utiliséCELL when either the "format" or "address" argument is used
  • INDIRECTINDIRECT
  • GETPIVOTDATAGETPIVOTDATA
  • CUBEMEMBERCUBEMEMBER
  • CUBEVALUECUBEVALUE
  • CUBEMEMBERPROPERTYCUBEMEMBERPROPERTY
  • CUBESETCUBESET
  • CUBERANKEDMEMBERCUBERANKEDMEMBER
  • CUBEKPIMEMBERCUBEKPIMEMBER
  • CUBESETCOUNTCUBESETCOUNT
  • ADRESS où figure le cinquième paramètre (le sheet_name) ADDRESS where the fifth parameter (the sheet_name) is given
  • Toute fonction de base de données (DSUM, DAVERAGE, et ainsi de suite) qui fait référence à un tableau croisé dynamiqueAny database function (DSUM, DAVERAGE, and so on) that refers to a PivotTable
  • ERROR.TYPEERROR.TYPE
  • HYPERLINKHYPERLINK

Utiliser des tableaux pour les fonctions qui traitent des plagesUse tables for functions that handle ranges

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.For functions like SUM, SUMIF, and SUMIFS that handle ranges, the calculation time is proportional to the number of used cells you are summing or counting. Les cellules inutilisées ne sont pas examinées, afin que les références de colonne entière sont relativement efficaces, mais il est préférable pour vous d’assurer que vous n’incluez pas plus de cellules que nécessaire.Unused cells are not examined, so whole column references are relatively efficient, but it is better to ensure that you do not include more used cells than you need. Utilisez des tableaux ou calculez les plages de sous-ensemble ou des plages dynamiques.Use tables, or calculate subset ranges or dynamic ranges.

Réduisez les fonctions volatilesReduce volatile functions

Les fonctions volatiles peuvent ralentir le recalcul car ils augmentent le nombre de formules qui doivent être recalculées à chaque calcul.Volatile functions can slow recalculation because they increase the number of formulas that must be recalculated at each calculation.

Vous pouvez souvent réduire le nombre de fonctions volatiles à l’aide deINDEX au lieu de OFFSET, et CHOISIR au lieu de INDIRECT.You can often reduce the number of volatile functions by using INDEX instead of OFFSET, and CHOOSE instead of INDIRECT. Toutefois, OFFSET est une fonction rapide et peut souvent être utilisée de manière créative qui donne un calcul rapide.However, OFFSET is a fast function and can often be used in creative ways that give fast calculation.

Utilisez les fonctions définies par l’utilisateur C ou C++ Use C or C++ user-defined functions

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).User-defined functions that are programmed in C or C++ and that use the C API (XLL add-in functions) generally perform faster than user-defined functions that are developed by using VBA or Automation (XLA or Automation add-ins). Pour plus d’informations, voir développement d’ Excel 2010 XLLs.For more information, see Developing Excel 2010 XLLs.

Les performances de fonctions VBA définies par l’utilisateur sont sensible à la manière dont vous programmez et les appelez.The performance of VBA user-defined functions is sensitive to how you program and call them.

Utilisez les fonctions VBA définies par l’utilisateur plus rapideUse faster VBA user-defined functions

Il est généralement plus rapide d’utiliser les calculs formule Excel et les fonctions de feuille de calcul que pour utiliser les fonctions VBA définies par l’utilisateur.It is usually faster to use the Excel formula calculations and worksheet functions than to use VBA user-defined functions. 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.This is because there is a small overhead for each user-defined function call and significant overhead transferring information from Excel to the user-defined function. Mais les fonctions bien conçues et appelées définies par l’utilisateur peuvent être beaucoup plus rapides que les formules complexes.But well-designed and called user-defined functions can be much faster than complex array formulas.

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.Ensure that you have put all the references to worksheet cells in the user-defined function input parameters instead of in the body of the user-defined function, so that you can avoid adding Application.Volatile unnecessarily.

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.If you must have many formulas that use user-defined functions, ensure that you are in manual calculation mode, and that the calculation is initiated from 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).VBA user-defined functions calculate much more slowly if the calculation is not called from VBA (for example, in automatic mode or when you press F9 in manual mode). C’est particulièrement vrai lorsque Visual Basic Editor (Alt + F11) est ouvert ou a été ouvert dans la session actuelle d’ Excel.This is particularly true when the Visual Basic Editor (Alt+F11) is open or has been opened in the current Excel session.

Vous pouvez intercepter F9 et rediriger vers une sous-routine de calcul VBA comme suit.You can trap F9 and redirect it to a VBA calculation subroutine as follows. Ajouter cette sous-routine au moduleThisworkbook.Add this subroutine to the Thisworkbook module.

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

Ajouter cette sous-routine à un module standard.Add this subroutine to a standard module.

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

Les fonctions définies par l’utilisateur dans les compléments d’Automation (Excel 2002 et les versions ultérieures) n’entraînent pas la charge liée à Visual Basic Editor, car elles n’utilisent pas l’éditeur intégré.User-defined functions in Automation add-ins (Excel 2002 and later versions) do not incur the Visual Basic Editor overhead because they do not use the integrated editor. 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.Other performance characteristics of Visual Basic 6 user-defined functions in Automation add-ins are similar to VBA functions.

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.If your user-defined function processes each cell in a range, declare the input as a range, assign it to a variant that contains an array, and loop on that. 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.If you want to handle whole column references efficiently, you must make a subset of the input range, dividing it at its intersection with the used range, as in this example.

  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.If your user-defined function is using worksheet functions or Excel object model methods to process a range, it is generally more efficient to keep the range as an object variable than to transfer all the data from Excel to the user-defined function.

  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é.If your user-defined function is called early in the calculation chain, it can be passed as uncalculated arguments. À 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 :Inside a user-defined function, you can detect uncalculated cells by using the following test for empty cells that contain a formula:

  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.A time overhead exists for each call to a user-defined function and for each transfer of data from Excel to 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.Sometimes one multi-cell array formula user-defined function can help you minimize these overheads by combining multiple function calls into a single function with a multi-cell input range that returns a range of answers.

Réduire la plage de cellules qui font référence à SUM et SUMIFMinimize range of cells that SUM and SUMIF reference

Les fonctions Excel SUM et SUMIFsont fréquemment utilisées sur un grand nombre de cellules.The Excel SUM and SUMIF functions are frequently used over a large number of cells. 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.Calculation time for these functions is proportionate to the number of cells covered, so try to minimize the range of cells that the functions are referencing.

Utiliser le caractère générique SUMIF, COUNTIF, SUMIFS, COUNTIFS, et les autres fonctions IFSUse wildcard SUMIF, COUNTIF, SUMIFS, COUNTIFS, and other IFS functions

N’utilisez pas le caractère générique ?You can use the wildcard characters ? (un seul caractère) et ** *** (aucun caractère ou n’importe quel nombre de caractères) dans les critères de plages par ordre alphabétique dans le cadre des fonctionsSUMIF, COUNTIF, SUMIFS, COUNTIFSet les autres IFS.(any single character) and * (no character or any number of characters) in the criteria for alphabetical ranges as part of the SUMIF, COUNTIF, SUMIFS, COUNTIFS, and other IFS functions.

Choisissez la méthode pour les arguments période-à-date et SUMs cumulativesChoose method for period-to-date and cumulative SUMs

Il existe deux méthodes pour effectuer les arguments période-à-date et SUMs cumulatives.There are two methods of doing period-to-date or cumulative SUMs. 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:Suppose the numbers that you want to cumulatively SUM are in column A, and you want column B to contain the cumulative sum; you can do either of the following:

  • 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.You can create a formula in column B such as =SUM($A$1:$A2) and drag it down as far as you need. 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.The beginning cell of the SUM is anchored in A1, but because the finishing cell has a relative row reference, it automatically increases for each row.

  • 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.You can create a formula such as =$A1 in cell B1 and =$B1+$A2 in cell B2 and drag it down as far as you need. Cette méthode calcule la cellule cumulée par l’ajout nombre de cette ligne à l’ancienne cumulée SUM.This calculates the cumulative cell by adding this row's number to the previous cumulative 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.For 1,000 rows, the first method makes Excel do about 500,000 calculations, but the second method makes Excel do only about 2,000 calculations.

Calculer les sommes sous-ensembleCalculate subset sums

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.When you have multiple sorted indexes to a table (for example, Site within Area) you can often save significant calculation time by dynamically calculating the address of a subset range of rows (or columns) to use in the SUM or SUMIF function.

Pour calculer l’adresse d’une plage sous-ensemble de lignes ou colonnes :To calculate the address of a subset range of row or columns:

  1. Compter le nombre de lignes pour chaque bloc sous-ensemble.Count the number of rows for each subset block.

  2. Ajouter des chiffres de façon cumulative pour chaque bloc détermine son début de la ligne.Add the counts cumulatively for each block to determine its start row.

  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.Use OFFSET with the start row and count to return a subset range to the SUM or SUMIF that covers only the subset block of rows.

Utiliser le sous-total pour les listes filtréesUse SUBTOTAL for filtered lists

Utilisez la fonctionSUBTOTALpour les listes filtréesSUM.Use the SUBTOTAL function to SUM filtered lists. La fonctionSUBTOTAL est utile car, contrairement àSUM, elle ignore les opérations suivantes :The SUBTOTAL function is useful because, unlike SUM, it ignores the following:

  • Lignes masquées résultant d’une liste de filtrage.Hidden rows that result from filtering a list. En commençant dans Excel 2003, vous pouvez également permettre àSUBTOTALd’ignorer toutes les lignes masquées, pas seulement les lignes simplement filtrées.Starting in Excel 2003, you can also make SUBTOTAL ignore all hidden rows, not just filtered rows.

  • Autres fonctionsSUBTOTAL.Other SUBTOTAL functions.

Utilisez la fonction AGGRATEUse the AGGREGATE function

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).The AGGREGATE function is a powerful and efficient way of calculating 19 different methods of aggregating data (such as SUM, MEDIAN, PERCENTILE and 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.AGGREGATE has options for ignoring hidden or filtered rows, error values, and nested SUBTOTAL and AGGREGATE functions.

Évitez d’utiliser DfonctionsAvoid using DFunctions

L’appellation DFunctionsDSUM, DCOUNT, DAVERAGE, et ainsi de suite sont sensiblement plus rapide que les formules matricielles équivalentes.The DFunctions DSUM, DCOUNT, DAVERAGE, and so on are significantly faster than equivalent array formulas. 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.The disadvantage of the DFunctions is that the criteria must be in a separate range, which makes them impractical to use and maintain in many circumstances. En commençant dans Excel 2007, vous devez utiliser les fonctionsSUMIFS, COUNTIFS, et AVERAGEIFS au lieu de l’appellation DFunctions.Starting in Excel 2007, you should use SUMIFS, COUNTIFS, and AVERAGEIFS functions instead of the DFunctions.

Créer des macros VBA plus rapidesCreate faster VBA macros

Utilisez les recommandations suivantes pour créer des macros VBA plus rapides.Use the following tips to create faster VBA macros.

Désactivez toute l’option, sauf les essentiels pendant l’exécution du codeTurn off everything but the essentials while code is running

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.To improve performance for VBA macros, explicitly turn off the functionality that is not required while your code executes. Souvent, un recalcul ou un renouvellement une fois que votre code s’exécute suffit et peut améliorer les performances.Often, one recalculation or one redraw after your code runs is all that is necessary and can improve performance. Une fois que votre code s’exécute, restaurer la fonctionnalité à son état d’origine.After your code executes, restore the functionality to its original state.

Les fonctionnalités suivantes peuvent généralement être désactivées tandis que votre macro VBA exécute :The following functionality can usually be turned off while your VBA macro executes:

  • Application.ScreenUpdating désactiver la mise à jour de l’écran.Application.ScreenUpdating Turn off screen updating. SiApplication.ScreenUpdating est défini sur faux, Excel ne renouvelle pas l’écran.If Application.ScreenUpdating is set to False, Excel does not redraw the screen. 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.While your code runs, the screen updates quickly, and it is usually not necessary for the user to see each update. Mettre à jour l’écran une seule fois, une fois que le code s’exécute, améliore les performances.Updating the screen once, after the code executes, improves performance.

  • Application.DisplayStatusBar désactivez la barre d’état.Application.DisplayStatusBar Turn off the status bar. Si Application.DisplayStatusBar est défini sur faux, Excel n’affiche pas la barre d’état.If Application.DisplayStatusBar is set to False, Excel does not display the status bar. 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.The status bar setting is separate from the screen updating setting so that you can still display the status of the current operation even while the screen is not updating. Toutefois, si vous n’avez pas besoin afficher l’état de chaque opération, la désactivation de la barre d’état pendant l’exécution de votre code également améliore les performances.However, if you do not need to display the status of every operation, turning off the status bar while your code runs also improves performance.

  • Application.Calculation changez pour le calcul manuel.Application.Calculation Switch to manual calculation. Si Application.Calculation est défini sur xlCalculationManual, Excel calcule uniquement le classeur lorsque l’utilisateur établit explicitement le calcul.If Application.Calculation is set to xlCalculationManual, Excel only calculates the workbook when the user explicitly initiates the calculation. En mode de calcul automatique, Excel détermine quand calculer.In automatic calculation mode, Excel determines when to calculate. Par exemple, chaque fois que une valeur de cellule liée à une formule est modifiée, Excel recalcule la formule.For example, every time a cell value that is related to a formula changes, Excel recalculates the formula. 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.If you switch the calculation mode to manual, you can wait until all the cells associated with the formula are updated before recalculating the workbook. Par un recalcul du classeur en cas de nécessité uniquement lorsque votre code s’exécute, vous pouvez améliorer les performances.By only recalculating the workbook when necessary while your code runs, you can improve performance.

  • Application.EnableEvents désactivez les événements.Application.EnableEvents Turn off events. Si Application.EnableEvents est défini sur faux, Excel ne déclenche pas d’événements.If Application.EnableEvents is set to False, Excel does not raise events. 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.If there are add-ins listening for Excel events, those add-ins consume resources on the computer as they record the events. 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.If it is not necessary for the add-in to record the events that occur while your code runs, turning off events improves performance.

  • ActiveSheet.DisplayPageBreaks désactivez les sauts de page.ActiveSheet.DisplayPageBreaks Turn off page breaks. Si ActiveSheet.DisplayPageBreaks est défini sur faux, Excel n’affiche pas les sauts de page.If ActiveSheet.DisplayPageBreaks is set to False, Excel does not display page breaks. Il n’est pas nécessaire de recalculer les sauts de page dans le cadre de l’exécution de votre code et de calculer les sauts de page après que le code exécute et améliore les performances.It is not necessary to recalculate page breaks while your code runs, and calculating the page breaks after the code executes improves performance.

Important

Une fois que votre code s’exécute, restaurez la fonctionnalité à son état d’origine.Remember to restore this functionality to its original state after your code executes.

L’exemple suivant montre les fonctionnalités dont vous pouvez désactiver tandis que votre macro VBA exécute.The following example shows the functionality that you can turn off while your VBA macro executes.

  ' 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érationRead and write large blocks of data in a single operation

Optimiser votre code en réduisant explicitement le nombre de fois données est transféré entre Excel et votre code.Optimize your code by explicitly reducing the number of times data is transferred between Excel and your 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.Instead of looping through cells one at a time to get or set a value, get or set the values in the entire range of cells in one line, using a variant containing a two-dimensional array to store values as needed. Les exemples de code suivants comparent ces deux méthodes.The following code examples compare these two methods.

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.The following code example shows non-optimized code that loops through cells one at a time to get and set the values of cells A1:C10000. Ces cellules ne contiennent pas de formules.These cells do not contain formulas.

  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.The following code example shows optimized code that uses an array to get and set the values of cells A1:C10000 all at the same time. Ces cellules ne contiennent pas de formules.These cells do not contain formulas.

  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 ExcelUse .Value2 rather than .Value or .Text when reading data from an Excel range

  • . Texte renvoie la valeur mise en forme d’une cellule..Text returns the formatted value of a cell. Cela est lent, cela peut retourner ### si l’utilisateur effectue un zoom avant et vous pouvez perdre la précision.This is slow, can return ### if the user zooms, and can lose precision.
  • . Valeur renvoie une devise VBA ou une variable date VBA si la plage a été mis en forme en tant que Date ou Devise..Value returns a VBA currency or VBA date variable if the range was formatted as Date or Currency. Cela est lent, cela peut perdre en précision et peut provoquer des erreurs lors de l’appel des fonctions de feuille de calcul.This is slow, can lose precision, and can cause errors when calling worksheet functions.
  • . Valeur2 rapide et ne modifie pas les données récupérées à partir d’ Excel..Value2 is fast and does not alter the data being retrieved from Excel.

Évitez les objets de sélection et d’ activationAvoid selecting and activating objects

Les objets de sélection et d’activation sont plus un traitement intensif que référencer directement des objets.Selecting and activating objects is more processing intensive than referencing objects directly. En référençant un objet comme un Plage ouForme directement, vous pouvez améliorer les performances.By referencing an object such as a Range or a Shape directly, you can improve performance. Les exemples de code suivants comparent les deux méthodes.The following code examples compare the two methods.

L’exemple de code suivant montre un code non optimisé qui sélectionne chaque forme sur la feuille active et transforme le texte « Hello ».The following code example shows non-optimized code that selects each Shape on the active sheet and changes the text to "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 ».The following code example shows optimized code that references each Shape directly and changes the text to "Hello".

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

Utilisez ces optimisations des performances VBA supplémentairesUse these additional VBA performance optimizations

Voici une liste des optimisations des performances supplémentaires que vous pouvez utiliser dans votre code VBA:The following is a list of additional performance optimizations you can use in your VBA code:

  • Renvoyer des résultats en attribuant une matrice directement à un Plage.Return results by assigning an array directly to a Range.

  • 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.Declare variables with explicit types to avoid the overhead of determining the data type, possibly multiple times in a loop, during code execution.

  • 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.For simple functions that you use frequently in your code, implement the functions yourself in VBA instead of using the WorksheetFunction object. Pour plus d’informations, voirutiliser les fonctions définies par l’utilisateur plus rapides VBA.For more information, see Use faster VBA user-defined functions.

  • Utilisez la méthodeRange.SpecialCellspermettant de l’étendue vers le bas du nombre de cellules avec lesquelles votre code interagit.Use the Range.SpecialCells method to scope down the number of cells with which your code interacts.

  • Vous pouvez des gains de performances si vous avez implémenté vos fonctionnalités à l’aide de l’API C dans le SDK XLL.Consider the performance gains if you implemented your functionality by using the C API in the XLL SDK. Pour plus d’informations, consultez la documentation pour les Excel 2010 XLL SDK.For more information, see the Excel 2010 XLL SDK Documentation.

Vous pouvez considérer les performances et la taille des formats de fichier ExcelConsider performance and size of Excel file formats

En commençant dans Excel 2007, Excel contient un large éventail de formats de fichier par rapport aux versions antérieures.Starting in Excel 2007, Excel contains a wide variety of file formats compared to earlier versions. 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.Ignoring the Macro, Template, Add-in, PDF, and XPS file format variations, the three main formats are XLS, XLSB, and XLSX.

  • Format XLSXLS format

    Le format XLS est le même format que les versions antérieures.The XLS format is the same format as earlier versions. Lorsque vous utilisez ce format, vous êtes limité à 256 colonnes et 65 536 lignes.When you use this format, you are restricted to 256 columns and 65,536 rows. Lorsque vous enregistrez un classeur Excel 2007 ou Excel 2010 au format XLS, Excel exécute une vérification de la compatibilité.When you save an Excel 2007 or Excel 2010 workbook in XLS format, Excel runs a compatibility check. 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.File size is almost the same as earlier versions (some additional information may be stored), and performance is slightly slower than earlier versions. N’importe quelle optimisation multi-thread Qu' Excel utilise pour un ordre de calcul cellule n’est pas enregistrée au format XLS.Any multi-threaded optimization Excel does with respect to cell calculation order is not saved in the XLS format. 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.Therefore, calculation of a workbook can be slower after saving the workbook in the XLS format, closing, and re-opening the workbook.

  • Format XLSBXLSB format

    XLSB est le format binaire en commençant dans Excel 2007.XLSB is the binary format starting in Excel 2007. Structuré sous forme de dossier compressé contenant de nombreux fichiers binaires.It is structured as a compressed folder that contains many binary files. Il est beaucoup plus compact que le format XLS, mais la quantité de compression varie selon le contenu du classeur.It is much more compact than the XLS format, but the amount of compression depends on the contents of the workbook. 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.For example, ten workbooks show a size reduction factor ranging from two to eight with an average reduction factor of four. En commençant dans Excel 2007, les performances ouvrantes et enregistrement sont uniquement légèrement moins rapides que le format XLS.Starting in Excel 2007, opening and saving performance is only slightly slower than the XLS format.

  • Format XLSXXLSX format

    XLSX est au format XML en commençant dans Excel 2007 et démarre le format par défaut dans Excel 2007.XLSX is the XML format starting in Excel 2007, and is the default format starting in 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).The XLSX format is a compressed folder that contains many XML files (if you change the file name extension to .zip, you can open the compressed folder and examine its contents). 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.Typically, the XLSX format creates larger files than the XLSB format (1.5 times larger on average), but they are still significantly smaller than the XLS files. Attendez-vous à des heures d’ouverture et d’enregistrement soit légèrement plus de fichiers XLSB.You should expect opening and saving times to be slightly longer than for XLSB files.

Ouvrir, fermer et enregistrer des classeursOpen, close, and save workbooks

Il peut sembler qu’ouvrir, fermer et enregistrer des classeurs soit beaucoup plus lent à les calculer.You may find that opening, closing, and saving workbooks is much slower than calculating them. Parfois, cela est dû à votre classeur volumineux, mais il peut également avoir d’autres raisons.Sometimes this is just because you have a large workbook, but there can also be other reasons.

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.If one or more of your workbooks open and close more slowly than is reasonable, it might be caused by one of the following issues.

  • Fichiers temporairesTemporary files

    Les fichiers temporaires peuvent s’accumuler dans votre répertoire Temp\Windows\(dans Windows 95, Windows 98 et Windows ME), ou votre \Documents et paramètres\nom d’utilisateur\paramètres locaux\ Répertoire TEMP (dans Windows 2000 et Windows XP).Temporary files can accumulate in your \Windows\Temp directory (in Windows 95, Windows 98, and Windows ME), or your \Documents and Settings\User Name\Local Settings\Temp directory (in Windows 2000 and Windows XP). Excel crée ces fichiers pour le classeur et les contrôles sont utilisés par les classeurs ouverts.Excel creates these files for the workbook and for controls that are used by open workbooks. Les programmes d’installation de logiciels également créent des fichiers temporaires.Software installation programs also create temporary files. Si Excel cesse de répondre pour une raison quelconque, vous devrez peut-être supprimer ces fichiers.If Excel stops responding for any reason, you might need to delete these files.

    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.Too many temporary files can cause problems, so you should occasionally clean them out. However, if you have installed software that requires that you restart your computer, and you have not yet done so, you should restart before deleting the temporary files.

 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.An easy way to open your temp directory is from the Windows Start menu: Click Start, and then click Run. Dans la zone de texte, tapez %temp%, puis cliquez sur OK.In the text box, type %temp%, and then click OK.

  • Suivi des modifications dans un classeur partagéTracking changes in a shared workbook

    Le suivi des modifications dans un classeur partagé pousse votre taille de fichier du classeur à augmenter rapidement.Tracking changes in a shared workbook causes your workbook file-size to increase rapidly.

  • Fichier swap fragmentéFragmented swap file

    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.Be sure that your Windows swap file is located on a disk that has a lot of space and that you defragment the disk periodically.

  • Classeur avec structure protégée par mot de passeWorkbook with password-protected structure

    Un classeur qui contient sa structure protégée par mot de passe (Outils menu > Protection > Protéger le classeur > entrez le mot de passe facultatif) s’ouvre et ferme beaucoup plus lentement qu’un seul protégé sans le mot de passe facultatif.A workbook that has its structure protected with a password (Tools menu > Protection > Protect Workbook > enter the optional password) opens and closes much slower than one that is protected without the optional password.

  • Problèmes de la plage utiliséeUsed range problems

    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.Oversized used ranges can cause slow opening and increased file size, especially if they are caused by hidden rows or columns that have non-standard height or width. Pour plus d’informations sur les problèmes de la plage utilisée, voir Réduire la plage utilisée.For more information about used range problems, see Minimize the used range.

  • Grand nombre de contrôles sur les feuilles de calculLarge number of controls on worksheets

    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.A large number of controls (check boxes, hyperlinks, and so on) on worksheets can slow down opening a workbook because of the number of temporary files that are used. 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).This might also cause problems opening or saving a workbook on a WAN (or even a LAN). Si vous rencontrez ce problème, vous devez envisager la refonte de votre classeur.If you have this problem, you should consider redesigning your workbook.

  • Grand nombre de liens vers d’autres classeursLarge number of links to other workbooks

    Si possible, ouvrez les classeurs qui vous soyez un lien avant d’ouvrir le classeur contenant les liens.If possible, open the workbooks that you are linking to before you open the workbook that contains the links. Il est souvent plus rapide d’ouvrir un classeur que de lire les liens à partir d’un classeur fermé.Often it is faster to open a workbook than to read the links from a closed workbook.

  • Paramètres du scanner antivirusVirus scanner settings

    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.Some virus scanner settings can cause problems or slowness with opening, closing, or saving, especially on a server. Si vous pensez que c’est peut-être le problème, essayez d’éteindre temporairement le scanner antivirus.If you think that this might be the problem, try temporarily switching the virus scanner off.

  • Ralentir le calcul cause le ralentissement d’ouverture et d’enregistrementSlow calculation causing slow open and save

    Dans certaines circonstances, Excel recalcule votre classeur lorsque celui-ci s’ouvre ou enregistre.Under some circumstances, Excel recalculates your workbook when it opens or saves it. 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).If the calculation time for your workbook is long and is causing a problem, ensure that you have calculation set to manual, and consider turning off the calculate before save option (Tools > Options > Calculation).

  • Fichiers de barre d’outils (.xlb)Toolbar files (.xlb)

    Vérifiez la taille de votre fichier de barre d’outils.Check the size of your toolbar file. Un fichier de barre d’outils standard est compris entre 10 Ko et 20 Ko.A typical toolbar file is between 10 KB and 20 KB. Vous pouvez rechercher vos fichiers XLB en recherchant *.xlb à l’aide de Windows search.You can find your XLB files by searching for *.xlb by using Windows search. Chaque utilisateur dispose d’un fichier XLB unique.Each user has a unique XLB file. Ajouter, modifier ou personnaliser les barres d’outils permet d’augmenter la taille de votre fichier toolbar.xlb.Adding, changing, or customizing toolbars increases the size of your toolbar.xlb file. La suppression du fichier supprime toutes vos personnalisations de barre d’outils (en le renommant « toolbar.OLD » est préférable).Deleting the file removes all your toolbar customizations (renaming it "toolbar.OLD" is safer). Un nouveau fichier XLB est créé la prochaine fois que vous ouvrez Excel.A new XLB file is created the next time you open Excel.

Utilisez ces optimisations des performances supplémentairesMake additional performance optimizations

Vous pouvez apporter des améliorations aux performances dans les sections suivantes.You can make performance improvements in the following areas.

  • PivotTablesPivotTables

    Les tableaux croisés dynamiques fournissent un moyen efficace pour synthétiser de grandes quantités de données.PivotTables provide an efficient way to summarize large amounts of data.

    • Totaux comme résultat final.Totals as final results. 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.If you need to produce totals and subtotals as part of the final results of your workbook, try using PivotTables.

    • Totaux en tant que résultats intermédiaires.Totals as intermediate results. 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 :PivotTables are a great way to produce summary reports, but try to avoid creating formulas that use PivotTable results as intermediate totals and subtotals in your calculation chain unless you can ensure the following conditions:

    • le tableau croisé dynamique a été actualisé correctement durant le calcul ;The PivotTable has been refreshed correctly during the calculation.

    • Le tableau croisé dynamique n’a pas été modifié afin que les informations soient toujours visibles.The PivotTable has not been changed so that the information is still visible.

    Si vous voulez toujours utiliser des tableaux croisés dynamiques en tant que résultats intermédiaires, utilisez la fonctionGETPIVOTDATA.If you still want to use PivotTables as intermediate results, use the GETPIVOTDATA function.

  • Formats et validation des données conditionnelsConditional formats and data validation

    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.Conditional formats and data validation are great, but using a lot of them can significantly slow down calculation. 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é.If the cell is displayed, every conditional format formula is evaluated at each calculation and when the display of the cell that contains the conditional format is refreshed. 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.The Excel object model has a Worksheet.EnableFormatConditionsCalculation property so that you can enable or disable the calculation of conditional formats.

  • Noms définisDefined names

    Les noms définis sont un des fonctionnalités plus puissantes dans Excel, mais ils prennent un temps de calcul supplémentaire.Defined names are one of the most powerful features in Excel, but they do take additional calculation time. 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.Using names that refer to other worksheets adds an additional level of complexity to the calculation process. Par ailleurs, essayez d’éviter les noms imbriquées (noms qui font référence à d’autres noms).Also, you should try to avoid nested names (names that refer to other names).

    É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.Because names are calculated every time a formula that refers to them is calculated, you should avoid putting calculation-intensive formulas or functions in defined names. 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.In these cases, it can be significantly faster to put your calculation-intensive formula or function in a spare cell somewhere and refer to that cell instead, either directly or by using a name.

  • Formules qui sont utilisées uniquement de manière occasionnelleFormulas that are used only occasionally

    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.Many workbooks contain a significant number of formulas and lookups that are concerned with getting the input data into the appropriate shape for the calculations, or are being used as defensive measures against changes in the size or shape of the data. 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.When you have blocks of formulas that are used only occasionally, you can copy and paste special values to temporarily eliminate the formulas, or you can put them in a separate, rarely opened workbook. É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.Because worksheet errors are often caused by not noticing that formulas have been converted to values, the separate workbook method may be preferable.

  • Utiliser une mémoire insuffisanteUse enough memory

    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.The 32-bit version of Excel can use up to 2 GB of RAM or up to 4 GB of RAM for Large Address Aware 32-bit versions of Excel 2013 and 2016. Toutefois, l’ordinateur qui exécute Excel nécessite également de ressources de mémoire.However, the computer that is running Excel also requires memory resources. 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.Therefore, if you only have 2 GB of RAM on your computer, Excel cannot take advantage of the full 2 GB because a portion of the memory is allocated to the operating system and other programs that are running. Pour optimiser les performances d’ Excel sur un ordinateur 32 bits, nous vous recommandons que l’ordinateur ait moins 3 Go de RAM.To optimize the performance of Excel on a 32-bit computer, we recommend that the computer have at least 3 GB of RAM.

    La version 64 bits d’ Excel n’a pas de limite de 2 Go et jusqu’à 4 Go.The 64-bit version of Excel does not have a 2 GB or up to 4 GB limit. 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.For more information, see the "Large data sets and the 64-bit version of Excel" section in Excel performance: Performance and limit improvements.

ConclusionConclusion

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.This article covered ways to optimize Excel functionality such as links, lookups, formulas, functions, and VBA code to avoid common obstructions and improve performance.

Voir aussiSee also

Assistance et commentairesSupport and feedback

Avez-vous des questions ou des commentaires sur Office VBA ou sur cette documentation ?Have questions or feedback about Office VBA or this 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.Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.