Excel-Leistung: Tipps für das Entfernen von LeistungshindernissenExcel performance: Tips for optimizing performance obstructions

Gilt für: Excel | Excel 2013 | Office 2016 | VBAApplies to: Excel | Excel 2013 | Office 2016 | VBA

Befolgen Sie diese Tipps zur Optimierung vieler häufig auftretender Leistungshindernisse in Excel.Follow these tips for optimizing many frequently occurring performance obstructions in Excel.

Erfahren Sie, wie Sie die Leistung im Zusammenhang mit Verweistypen und Links verbessern.Learn how to improve performance related to types of references and links.

Vorwärts-und rückwärts Verweisung nicht verwendenDo not use forward referencing and backward referencing

Um die Übersichtlichkeit zu verbessern und Fehler zu vermeiden, sollten Sie Ihre Formeln so entwerfen, dass Sie nicht nach vorn (rechts oder unten) auf andere Formeln oder Zellen verweisen.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. Die vorwärts Verweisung wirkt sich in der Regel nicht auf die Berechnungsleistung aus, außer in extremen Fällen für die erste Berechnung einer Arbeitsmappe, bei der es länger dauern kann, eine sinnvolle Berechnungs Sequenz einzurichten, wenn es viele Formeln gibt, deren Berechnung verzögert werden muss.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.

Minimieren der Verwendung von Zirkelverweisen mit IterationMinimize use of circular references with iteration

Das Berechnen von Zirkelverweisen mit Iterationen ist langsam, da mehrere Berechnungen erforderlich sind und diese Berechnungen mit einem einzelnen Thread ausgeführt werden.Calculating circular references with iterations is slow because multiple calculations are needed, and these calculations are single-threaded. Häufig können Sie die Zirkelbezüge mithilfe von Algebra "aufrollen", damit keine iterative Berechnung mehr erforderlich ist.Frequently you can "unroll" the circular references by using algebra so that iterative calculation is no longer needed. Versuchen Sie beispielsweise in Cashflow-und Zinsberechnungen den Cashflow vor Zinsen zu berechnen, die Zinsen zu berechnen und dann den Cashflow einschließlich der Zinsen zu berechnen.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.

In Excel werden Zirkelbezüge aus dem Datenblatt ohne Berücksichtigung von Abhängigkeiten berechnet.Excel calculates circular references sheet-by-sheet without considering dependencies. Demzufolge erfolgt die Berechnung meist langsam, wenn sich Ihre Zirkelverweise über mehrere Arbeitsblätter erstrecken.Therefore, you usually get slow calculation if your circular references span more than one worksheet. Versuchen Sie, die Zirkelverweise auf ein einzelnes Arbeitsblatt zu verschieben, oder optimieren Sie die Arbeitsblattberechnungsfolge zum Vermeiden unnötiger Berechnungen.Try to move the circular calculations onto a single worksheet or optimize the worksheet calculation sequence to avoid unnecessary calculations.

Vor Beginn der iterativen Berechnungen muss Excel die Arbeitsmappe neu berechnen, um alle Zirkelverweise und deren Abhängige zu bestimmen.Before the iterative calculations start, Excel must recalculate the workbook to identify all the circular references and their dependents. Dieser Vorgang entspricht zwei oder drei Iterationen der Berechnung.This process is equal to two or three iterations of the calculation.

Nach Bestimmen der Zirkelverweise und Abhängigen erfordert jede Iteration, dass Excel nicht nur alle Zellen im Zirkelverweis, sondern auch sämtliche Zellen, die von den Zellen in der Zirkelverweiskette abhängen, sowie veränderliche Zellen und deren Abhängige berechnet.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. Wenn Sie eine komplexe Berechnung haben, die von Zellen im Zirkelverweis abhängt, kann es schneller sein, diesen in einer gesonderten geschlossenen Arbeitsmappe zu isolieren und für die Neuberechnung zu öffnen, nachdem die Zirkelberechnung konvergiert ist.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.

Es ist wichtig, die Anzahl der Zellen im Zirkelverweis und die Berechnungsdauer dieser Zellen zu verringern.It is important to reduce the number of cells in the circular calculation and the calculation time that is taken by these cells.

Vermeiden von Verknüpfungen zwischen Arbeitsmappen, wenn möglich; Sie können langsam, leicht gebrochen und nicht immer leicht zu finden und zu beheben sein.Avoid inter-workbook links when it is possible; they can be slow, easily broken, and not always easy to find and fix.

Das Arbeiten mit wenigen größeren Arbeitsmappen ist normalerweise, aber nicht immer, besser als das Arbeiten mit vielen kleineren Arbeitsmappen.Using fewer larger workbooks is usually, but not always, better than using many smaller workbooks. Einige Ausnahmen können sein, wenn Sie viele Front-End-Berechnungen haben, die so selten neu berechnet werden, dass es sinnvoll ist, Sie in eine separate Arbeitsmappe einzufügen oder wenn Sie nicht genügend RAM haben.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.

Arbeiten Sie am besten mit einfachen direkten Zellbezügen, die bei geschlossenen Arbeitsmappen funktionieren.Try to use simple direct cell references that work on closed workbooks. Auf diese Weise können Sie die Neuberechnung aller Ihrer verknüpften Arbeitsmappen vermeiden, wenn Sie eine beliebige Arbeitsmappe neu berechnen.By doing this, you can avoid recalculating all your linked workbooks when you recalculate any workbook. Außerdem können Sie die Werte sehen, die Excel aus der geschlossenen Arbeitsmappe gelesen hat, was häufig für das Debuggen und die Überwachung der Arbeitsmappe wichtig ist.Also, you can see the values Excel has read from the closed workbook, which is frequently important for debugging and auditing the workbook.

Wenn verknüpfte Arbeitsmappen nicht vermieden werden können, öffnen Sie alle, anstatt sie geschlossen zu halten, und öffnen Sie die Arbeitsmappen, die die Verknüpfung herstellen, bevor Sie die Arbeitsmappen öffnen, mit denen die Verknüpfung hergestellt wird.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.

Das Verwenden zahlreicher Arbeitsblätter kann die Verwendung Ihrer Arbeitsmappe vereinfachen, doch im Allgemeinen erfolgt die Berechnung von Verweisen auf andere Arbeitsblätter langsamer als die von Verweisen innerhalb von Arbeitsblättern.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.

Minimieren des verwendeten BereichsMinimize the used range

Um Arbeitsspeicher zu sparen und die Dateigröße zu reduzieren, versucht Excel, Informationen zu nur dem Bereich eines Arbeitsblatts zu speichern, das verwendet wurde.To save memory and reduce file size, Excel tries to store information about only the area on a worksheet that was used. Dieser Bereich wird als verwendeter Bereich bezeichnet.This is called the used range. Mitunter wird bei verschiedenen Bearbeitungs- und Formatierungsvorgängen der verwendete Bereich beträchtlich über den Bereich hinaus ausgedehnt, der eigentlich als verwendet betrachtet wird.Sometimes various editing and formatting operations extend the used range significantly beyond the range that you would currently consider used. Dies kann Hindernisse bei Leistung und Dateigröße verursachen.This can cause performance obstructions and file-size obstructions.

Sie können den sichtbaren verwendeten Bereich auf einem Arbeitsblatt mithilfe von STRG + Ende überprüfen.You can check the visible used range on a worksheet by using Ctrl+End. Wenn dies übertrieben ist, sollten Sie alle Zeilen und Spalten unterhalb und rechts von ihrer tatsächlichen zuletzt verwendeten Zelle löschen und dann die Arbeitsmappe speichern.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. Erstellen Sie zuerst eine Sicherungskopie.Create a backup copy first. Wenn Sie Formeln mit Bereichen haben, die sich auf den gelöschten Bereich ausdehnen oder darauf verweisen, werden diese Bereiche verkleinert oder in #N/ageändert.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.

Zusätzliche Daten zulassenAllow for extra data

Wenn Sie Ihren Arbeitsblättern häufig Datenzeilen oder -spalten hinzufügen, benötigen Sie eine Möglichkeit, über die Ihre Excel-Formeln automatisch auf den neuen Datenbereich verweisen, anstatt die Formeln jedes Mal suchen und ändern zu müssen.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.

Verwenden Sie hierzu einen großen Bereich in Ihren Formeln, der relativ weit über die aktuellen Datengrenzen hinausgeht.You can do this by using a large range in your formulas that extends well beyond your current data boundaries. Dies kann jedoch unter bestimmten Umständen für eine ineffiziente Berechnung und schwierige Datenpflege sorgen, da das Löschen von Zeilen und Spalten den Bereich verkleinern kann, ohne dass Sie es bemerken.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.

Ab Excel 2007 können Sie strukturierte Tabellenverweise verwenden, die automatisch erweitert und vertraglich abgeschlossen werden, wenn die Größe der referenzierten Tabelle zunimmt oder sinkt.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.

Diese Lösung hat mehrere Vorteile:This solution has several advantages:

  • Es gibt weniger Leistungsnachteile als die alternativen ganzer Spaltenverweise und dynamischer Bereiche.Fewer performance disadvantages exist than the alternatives of whole column referencing and dynamic ranges.

  • Es ist einfach, mehrere Tabellen mit Daten auf einem einzelnen Arbeitsblatt zu pflegen.It is easy to have multiple tables of data on a single worksheet.

  • Formeln, die in die Tabelle eingebettet sind, werden auch entsprechend den Daten vergrößert bzw. verkleinert.Formulas that are embedded in the table also expand and contract with the data.

Alternativ können Sie ganze Spalten-und Zeilenbezüge verwenden.Alternatively, use whole column and row references

Ein alternativer Ansatz besteht darin, einen ganzen Spaltenverweis zu verwenden, beispielsweise $A: $A.An alternative approach is to use a whole column reference, for example $A:$A. Diese Referenz gibt alle Zeilen in Spalte A zurück. Daher können Sie beliebig viele Daten hinzufügen, und der Verweis wird immer berücksichtigt.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.

Diese Lösung hat sowohl Vor- als auch Nachteile:This solution has both advantages and disadvantages:

  • Viele vordefinierte Excel-Funktionen (SUM, SUMIF) berechnen Verweise auf ganze Spalten effizient, da sie automatisch die letzte verwendete Spalte in der Zeile erkennen.Many Excel built-in functions (SUM, SUMIF) calculate whole column references efficiently because they automatically recognize the last used row in the column. Arrayberechnungsfunktionen wie SUMPRODUCT können entweder keine Verweise auf ganze Spalten verarbeiten oder berechnen alle Zellen in der Spalte.However, array calculation functions like SUMPRODUCT either cannot handle whole column references or calculate all the cells in the column.

  • Benutzerdefinierte Funktionen erkennen nicht automatisch die letzte verwendete Zeile in der Spalte und berechnen daher häufig Verweise auf ganze Spalten nicht effizient.User-defined functions do not automatically recognize the last-used row in the column and, therefore, frequently calculate whole column references inefficiently. Es ist jedoch einfach, benutzerdefinierte Funktionen so zu programmieren, dass die letzte verwendete Zeile erkannt wird.However, it is easy to program user-defined functions so that they recognize the last-used row.

  • Es ist schwierig, Verweise auf ganze Spalten zu verwenden, wenn mehrere Datentabellen auf einem einzelnen Arbeitsblatt vorhanden sind.It is difficult to use whole column references when you have multiple tables of data on a single worksheet.

  • In Excel 2007 und höheren Versionen können Arrayformeln ganze Spaltenbezüge verarbeiten, Dies erzwingt jedoch eine Berechnung aller Zellen in der Spalte, einschließlich leerer Zellen.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. Dies kann nur langsam berechnet werden, insbesondere für 1 Million Zeilen.This can be slow to calculate, especially for 1 million rows.

Alternativ können Sie dynamische Bereiche verwenden.Alternatively, use dynamic ranges

Mithilfe der Offset -oder Index -und COUNTA -Funktionen in der Definition eines benannten Bereichs können Sie festlegen, dass der Bereich, auf den der benannte Bereich verweist, dynamisch erweitert und vertraglich vergrößert wird.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. Erstellen Sie beispielsweise einen definierten Namen mit einer der folgenden Formeln: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)

Wenn Sie den Namen des dynamischen Bereichs in einer Formel verwenden, wird dieser automatisch zum Aufnehmen neuer Einträge vergrößert.When you use the dynamic range name in a formula, it automatically expands to include new entries.

Die Verwendung der Index Formel für einen dynamischen Bereich ist der Offset Formel im Allgemeinen vorzuziehen, da Offset den Nachteil hat, dass es sich um eine flüchtige Funktion handelt, die bei jeder Neuberechnung berechnet wird.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.

Die Leistung sinkt, da die COUNTA -Funktion innerhalb der dynamischen Bereichsformel viele Zeilen untersuchen muss.Performance decreases because the COUNTA function inside the dynamic range formula must examine many rows. Sie können diese Leistung herabsetzen, indem Sie den COUNTA -Teil der Formel in einer separaten Zelle oder einem definierten Namen speichern und dann auf die Zelle oder den Namen im dynamischen Bereich verweisen: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)

Sie können auch Funktionen wie indirekt verwenden, um dynamische Bereiche zu erstellen, aber indirekt ist flüchtig und berechnet immer Single-Thread.You can also use functions such as INDIRECT to construct dynamic ranges, but INDIRECT is volatile and always calculates single-threaded.

Dynamische Bereiche haben die folgenden Vor- und Nachteile:Dynamic ranges have the following advantages and disadvantages:

  • Dynamische Bereiche eignen sich gut zum Einschränken der Anzahl der Berechnungen, die von Arrayformeln durchgeführt werden.Dynamic ranges work well to limit the number of calculations performed by array formulas.

  • Für die Verwendung mehrerer dynamischer Bereiche in einer einzelnen Spalte sind spezielle Zählfunktionen erforderlich.Using multiple dynamic ranges within a single column requires special-purpose counting functions.

  • Das Arbeiten mit vielen dynamischen Bereichen kann die Leistung verringern.Using many dynamic ranges can decrease performance.

Verbessern der Such BerechnungszeitImprove lookup calculation time

In Office 365 Version 1809 und höher werden die Excel-Funktionen SVERWEIS, WVERWEIS und VERGLEICH zur Ermittlung genauer Übereinstimmungen in nicht sortierten Daten jetzt wesentlich schneller als je zuvor ausgeführt, wenn mehrere Spalten (oder Zeilen mit WVERWEIS) aus demselben Tabellenbereich durchsucht werden.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.

Für frühere Excel-Versionen sind Nachschlagevorgänge jedoch weiterhin häufig erhebliche Berechnungs Hindernisse.That said, for earlier Excel versions, Lookups continue to be frequently significant calculation obstructions. Glücklicherweise gibt es zahlreiche Möglichkeiten zum Beschleunigen der Berechnungszeit von Nachschlagevorgängen.Fortunately, there are many ways of improving lookup calculation time. Bei Wahl der Option Genaue Übereinstimmung entspricht die Berechnungszeit für die Funktion proportional der Anzahl der Zellen, die durchsucht wurden, bevor eine Übereinstimmung gefunden wird.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. Bei Nachschlagevorgängen in großen Bereichen kann dieser Zeitraum beträchtlich sein.For lookups over large ranges, this time can be significant.

Die Nachschlagezeit bei Verwenden der Optionen für eine ungefähre Übereinstimmung von VLOOKUP, HLOOKUP und MATCH bei sortierten Daten ist kurz und verlängert sich nicht wesentlich durch die Länge des Bereichs, der durchsucht wird.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. Die Merkmale entsprechen denen der binären Suche.Characteristics are the same as binary search.

Grundlegendes zu SuchoptionenUnderstand lookup options

Stellen Sie sicher, dass Sie die Optionen Match-Type und Range-Lookup in Match, VLOOKUPund WVERWEISverstehen.Ensure that you understand the match-type and range-lookup options in MATCH, VLOOKUP, and HLOOKUP.

Das folgende Codebeispiel zeigt die Syntax der MATCH-Funktion.The following code example shows the syntax for the MATCH function. Weitere Informationen finden Sie unter der Match-Methode des WorksheetFunction-Objekts.For more information, see the Match method of the WorksheetFunction object.

  MATCH(lookup value, lookup array, matchtype)
  • MatchType = 1 gibt die größte Übereinstimmung zurück, die kleiner als oder gleich dem Nachschlagewert ist, wenn das Nachschlage Array aufsteigend sortiert wird (ungefähre Übereinstimmung).Matchtype=1 returns the largest match less than or equal to the lookup value when the lookup array is sorted ascending (approximate match). Wenn das Nachschlage Array nicht aufsteigend sortiert ist, gibt Match eine falsche Antwort zurück.If the lookup array is not sorted ascending, MATCH will return an incorrect answer. Die Standardoption ist näherungsweise Übereinstimmung aufsteigend sortiert.The default option is approximate match sorted ascending.

  • matchtype=0 fordert eine genaue Übereinstimmung an und geht davon aus, dass die Daten nicht sortiert sind.Matchtype=0 requests an exact match and assumes that the data is not sorted.

  • matchtype=-1 gibt die größte Übereinstimmung größer gleich dem Nachschlagewert zurück, wenn das Nachschlagearray absteigend sortiert ist (ungefähre Übereinstimmung).Matchtype=-1 returns the smallest match greater than or equal to the lookup value if the lookup array is sorted descending (approximate match).

Das folgende Codebeispiel zeigt die Syntax der Funktionen VLOOKUP und HLOOKUP.The following code example shows the syntax for the VLOOKUP and HLOOKUP functions. Weitere Informationen finden Sie unter den Methoden VLOOKUP (SVERWEIS) und HLOOKUP (WVERWEIS) des WorksheetFunction-Objekts.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 gibt die größte Übereinstimmung kleiner gleich dem Nachschlagewert (ungefähre Übereinstimmung) zurück.Range-lookup=TRUE returns the largest match less than or equal to the lookup value (approximate match). Dies ist die Standardoption.This is the default option. Das Tabellenarray muss aufsteigend sortiert sein.Table array must be sorted ascending.

  • range-lookup=FALSE fordert eine genaue Übereinstimmung an und geht davon aus, dass die Daten nicht sortiert sind.Range-lookup=FALSE requests an exact match and assumes the data is not sorted.

Vermeiden Sie möglichst das Anwenden von Nachschlagevorgängen auf unsortierte Daten, da es langsam ist.Avoid performing lookups on unsorted data where possible because it is slow. Wenn Ihre Daten sortiert sind, Sie jedoch eine exakte Übereinstimmung wünschen, lesen Sie Verwenden von zwei Nachschlagevorgängen für sortierte Daten mit fehlenden Werten.If your data is sorted, but you want an exact match, see Use two lookups for sorted data with missing values.

Verwenden von Index und Match oder Offset anstelle von VLOOKUPUse INDEX and MATCH or OFFSET instead of VLOOKUP

Versuchen Sie, die Index -und Match -Funktionen anstelle von VLOOKUPzu verwenden.Try using the INDEX and MATCH functions instead of VLOOKUP. Zwar ist VLOOKUP etwas schneller (etwa 5 Prozent schneller), einfacher und weniger Arbeitsspeicher als eine Kombination aus Match und Indexoder Offset, aber die zusätzliche Flexibilität, die Match -und Index Angebote häufig zur Verfügung stehen, ermöglicht Ihnen, Zeit erheblich zu sparen.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. Sie können beispielsweise das Ergebnis einer exakten Übereinstimmung in einer Zelle speichern und in mehreren Index -Anweisungen wieder verwenden.For example, you can store the result of an exact MATCH in a cell and reuse it in several INDEX statements.

Die INDEX-Funktion ist schnell und unveränderlich, wodurch die Neuberechnung beschleunigt wird.The INDEX function is fast and is a non-volatile function, which speeds up recalculation. Die Offset -Funktion ist ebenfalls schnell; Es handelt sich jedoch um eine flüchtige Funktion, die manchmal die Verarbeitungszeit der berechnungskette erheblich erhöht.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.

VLOOKUP kann mühelos in INDEX und MATCH umgewandelt werden.It is easy to convert VLOOKUP to INDEX and MATCH. Die folgenden beiden Anweisungen geben dieselbe Antwort zurück: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)

Beschleunigen von SuchvorgängenSpeed up lookups

Da Nachschlagevorgänge nach genauen Übereinstimmungen langsam sein können, erwägen Sie die folgenden Optionen zur Verbesserung der Leistung:Because exact match lookups can be slow, consider the following options for improving performance:

  • Verwenden Sie ein Arbeitsblatt.Use one worksheet. Es ist schneller, Nachschlagevorgänge und Daten auf dem gleichen Blatt zu halten.It is faster to keep lookups and data on the same sheet.

  • Wenden Sie möglichst zuerst die SORT-Funktion auf die Daten an (SORT ist schnell), und suchen Sie nach ungefähren Übereinstimmungen.When you can, SORT the data first (SORT is fast), and use approximate match.

  • Wenn Sie mit einem Nachschlagevorgang nach einer genauen Überstimmung arbeiten müssen, beschränken Sie den Bereich der zu durchsuchenden Zellen auf ein Minimum.When you must use an exact match lookup, restrict the range of cells to be scanned to a minimum. Verwenden Sie Tabellen und strukturierte Verweise oder dynamische Bereichsnamen, anstatt auf eine große Anzahl von Zeilen oder Spalten zu verweisen.Use tables and structured references or dynamic range names rather than referring to a large number of rows or columns. Mitunter können Sie einen Grenzwert für den oberen und unteren Bereich für den Nachschlagevorgang vorab berechnen.Sometimes you can pre-calculate a lower-range limit and upper-range limit for the lookup.

Verwenden von zwei Nachschlagevorgängen für sortierte Daten mit fehlenden WertenUse two lookups for sorted data with missing values

Zwei ungefähre Übereinstimmungen sind bei einer Suche über mehr als nur einige wenige Zeilen wesentlich schneller als eine genaue Übereinstimmung zu finden.Two approximate matches are significantly faster than one exact match for a lookup over more than a few rows. (Die Grenze liegt bei etwa 10-20 Zeilen.)(The breakeven point is about 10-20 rows.)

Wenn Sie Ihre Daten sortieren, aber immer noch keine ungefähre Übereinstimmung verwenden können, da Sie nicht sicher sein können, dass der gesuchte Wert im Nachschlagebereich vorhanden ist, können Sie diese Formel verwenden: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")

Im ersten Teil der Formel wird eine Suche nach einer ungefähren Übereinstimmung in der Nachschlagespalte selbst durchgeführt.The first part of the formula works by doing an approximate lookup on the lookup column itself.

  VLOOKUP(lookup_val ,lookup_array,1,True)

Sie können überprüfen, ob die Antwort von der Nachschlagespalte mit dem Nachschlagewert identisch ist (in diesem Fall haben Sie eine exakte Übereinstimmung), indem Sie die folgende Formel verwenden: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,

Wenn diese Formel true zurückgibt, haben Sie eine exakte Übereinstimmung gefunden, sodass Sie die ungefähre Suche erneut durchführen können, dieses Mal jedoch die Antwort von der gewünschten Spalte zurückgibt.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)

Wenn die Antwort aus der Nachschlagespalte nicht mit dem Nachschlagewert übereinstimmt, liegt ein fehlender Wert vor, und die Formel gibt "NotExist" zurück.If the answer from the lookup column did not match the lookup value, you have a missing value, and the formula returns "notexist".

Wenn Sie einen Wert nachschlagen, der kleiner als der kleinste Wert in der Liste ist, erhalten Sie eine Fehlermeldung.Be aware that if you look up a value smaller than the smallest value in the list, you receive an error. Sie können diesen Fehler vermeiden, indem Sie IFERROR verwenden oder der Liste einen kleinen Testwert hinzufügen.You can handle this error by using IFERROR, or by adding a small test value to the list.

Verwenden der IFERROR-Funktion für unsortierte Daten mit fehlenden WertenUse IFERROR function for unsorted data with missing values

Wenn Sie exakte überein Stimmungs Suche für unsortierte Daten verwenden müssen und nicht sicher sind, ob der Nachschlagewert vorhanden ist, müssen Sie häufig die #N/a behandeln, die zurückgegeben wird, wenn keine Übereinstimmung gefunden wird.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. Beginnend mit Excel 2007 können Sie die IFERROR -Funktion verwenden, die sowohl einfach als auch schnell ist.Beginning with Excel 2007, you can use the IFERROR function, which is both simple and fast.

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

In früheren Versionen ist eine einfache, aber langsame Möglichkeit, das Verwenden einer IF-Funktion mit zwei Nachschlagevorgängen.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))

Sie können den doppelten Nachschlagevorgang nach einer genauen Übereinstimmung vermeiden, wenn Sie die MATCH-Funktion für eine genaue Übereinstimmung einmal verwenden, das Ergebnis in einer Zelle speichern und anschließend das Ergebnis testen, bevor Sie die INDEX-Funktion ausführen.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))

Wenn Sie keine zwei Zellen verwenden können, verwenden Sie ZÄHLENWENN.If you cannot use two cells, use COUNTIF. Es ist in der Regel schneller als eine exakte Übereinstimmung Suche.It is generally faster than an exact match lookup.

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

Verwenden von Match und Index für exakte überein Stimmungs Suche in mehreren SpaltenUse MATCH and INDEX for exact match lookups on multiple columns

Sie können den gespeicherten Wert einer MATCH-Funktion zum Auffinden einer genauen Übereinstimmung mehrmals wiederverwenden.You can often reuse a stored exact MATCH many times. Wenn Sie solche Nachschlagevorgänge auf mehrere Ergebnisspalten anwenden, können Sie Zeit sparen, indem Sie eine MATCH-Funktion und zahlreiche INDEX-Anweisungen anstelle vieler VLOOKUP-Anweisungen verwenden.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.

Fügen Sie eine zusätzliche Spalte für die Übereinstimmung hinzu, umstored_rowdas Ergebnis () zu speichern, und verwenden Sie für jede Ergebnisspalte Folgendes: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)

Alternativ können Sie die VLOOKUP-Funktion in einer Arrayformel verwenden.Alternatively, you can use VLOOKUP in an array formula. (Array Formeln müssen mit STRG + UMSCHALT + EINGABETASTE eingegeben werden.(Array formulas must be entered by using Ctrl+-Shift+Enter. Excel fügt die {und} hinzu, um Ihnen zu zeigen, dass es sich um eine Arrayformel handelt).Excel will add the { and } to show you that this is an array formula).

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

Verwenden von Index für eine Gruppe zusammenhängender Zeilen oder SpaltenUse INDEX for a set of contiguous rows or columns

Sie können auch mithilfe eines einzelnen Nachschlagevorgangs viele Zeilen zurückgeben.You can also return many cells from one lookup operation. Zum Nachschlagen mehrerer zusammenhängender Spalten können Sie die INDEX-Funktion in einer Arrayformel verwenden, um mehrere Spalten auf einmal zurückzugeben (wählen Sie 0 als Spaltennummer).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). Mithilfe der INDEX-Funktion können Sie auch mehrere Zeilen auf einmal zurückgeben.You can also use the INDEX function to return multiple rows at one time.

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

Hiermit wird Spalte A bis Spalte J aus der gespeicherten Zeile zurückgegeben, die von einer vorherigen MATCH-Anweisung erstellt wurde.This returns column A to column J from the stored row created by a previous MATCH statement.

Verwenden von Match zum Zurückgeben eines rechteckigen zellenblocksUse MATCH to return a rectangular block of cells

Mit den Funktionen MATCH und OFFSET können Sie einen rechteckigen Zellenblock zurückgeben.You can use the MATCH and OFFSET functions to return a rectangular block of cells.

Verwenden von Match und Index für die zweidimensionale SucheUse MATCH and INDEX for two-dimensional lookup

Sie können eine zweidimensionale Tabellensuche effizient ausführen, indem Sie getrennte Suchvorgänge für die Zeilen und Spalten einer Tabelle verwenden, indem Sie eine Index Funktion mit zwei eingebetteten Übereinstimmungs Funktionen verwenden, eine für die Zeile und eine für die Spalte.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.

Verwenden eines Teilmenge Bereichs für die Suche nach mehreren IndizesUse a subset range for multiple-index lookup

In großen Arbeitsblättern müssen Sie möglicherweise häufig mehrere Indizes nachschlagen, beispielsweise die Suche nach Produktdatensätzen in einem Land.In large worksheets, you may frequently need to look up by using multiple indexes, such as looking up product volumes in a country. Dazu können Sie die Indizes verketten und die Suche mithilfe von verketteten Nachschlagewerten durchführen.To do this, you can concatenate the indexes and perform the lookup by using concatenated lookup values. Dies ist jedoch aus zwei Gründen ineffizient:However, this is inefficient for two reasons:

  • Das Verketten von Zeichenfolgen ist ein berechnungsintensiver Vorgang.Concatenating strings is a calculation-intensive operation.

  • Der Nachschlagevorgang bezieht sich auf einen großen Bereich.The lookup will cover a large range.

Es ist häufig effizienter, einen Teilmengen Bereich für die Suche zu berechnen (beispielsweisedurch suchen der ersten und letzten Zeile für das Land und anschließendes Nachschlagen des Produkts innerhalb dieses untergeordneten Bereichs).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).

Optionen für die dreidimensionale Suche in Frage stellenConsider options for three-dimensional lookup

Zum Nachschlagen der zu verwendenden Tabelle zusätzlich zu der Zeile und der Spalte können Sie die folgenden Verfahren mit dem Schwerpunkt darauf verwenden, wie Excel zum Nachschlagen oder Auswählen der Tabelle veranlasst werden kann.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.

Wenn jede Tabelle, nach der Sie suchen möchten (die dritte Dimension), als eine Gruppe von benannten strukturierten Tabellen, Bereichsnamen oder als Tabelle mit Textzeichenfolgen gespeichert wird, die Bereiche darstellen, können Sie möglicherweise die Funktionen Choose oder indirekte verwenden.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.

  • Das Verwenden von CHOOSE und Bereichsnamen kann eine effiziente Methode sein.Using CHOOSE and range names can be an efficient method. CHOOSE ist nicht veränderlich, eignet sich jedoch am besten für eine relativ kleine Anzahl von Tabellen.CHOOSE is not volatile, but it is best-suited to a relatively small number of tables. In diesem Beispiel wird TableLookup_Value dynamisch verwendet, um den BereichsTableName1, TableName2, ...Namen () auszuwählen, der für die Nachschlagetabelle verwendet werden soll.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))
    
  • Im folgenden Beispiel wird die indirekte -Funktion verwendet und TableLookup_Value der Blattname dynamisch erstellt, der für die Nachschlagetabelle verwendet werden soll.The following example uses the INDIRECT function and TableLookup_Value to dynamically create the sheet name to use for the lookup table. Diese Methode hat den Vorteil, dass sie einfach ist und damit eine große Anzahl von Tabellen verarbeitet werden kann.This method has the advantage of being simple and able to handle a large number of tables. Da indirekte eine flüchtige Single-Thread-Funktion ist, wird die Suche bei jeder Berechnung mit einem einzelnen Thread berechnet, auch wenn keine Daten geändert wurden.Because INDIRECT is a volatile single-threaded function, the lookup is single-thread calculated at every calculation even if no data has changed. Die Verwendung dieser Methode ist langsam.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))
    
  • Sie können auch die VLOOKUP-Funktion zum Auffinden des Blattnamens oder der Textzeichenfolge nutzen, der/das für die Tabelle verwendet werden soll, und anschließend die INDIRECT-Funktion zum Umwandeln des resultierenden Texts in einen Bereich verwenden.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))
    

Ein weiteres Verfahren ist das Zusammenfassen aller Ihrer Tabellen in einer Riesentabelle, die eine zusätzliche Spalte enthält, in der die einzelnen Tabellen identifiziert werden.Another technique is to aggregate all your tables into one giant table that has an additional column that identifies the individual tables. Sie können anschließend die Verfahren für Nachschlagevorgänge unter Verwendung mehrerer Indizes aus den vorherigen Beispielen anwenden.You can then use the techniques for multiple-index lookup shown in the previous examples.

Verwenden der PlatzhaltersucheUse wildcard lookup

Mit den Funktionen Match, VLOOKUPund WVERWEIS können Sie die Platzhalterzeichen verwenden ?The MATCH, VLOOKUP, and HLOOKUP functions allow you to use the wildcard characters ? (beliebiges einzelnes Zeichen) * und (kein Zeichen oder eine beliebige Anzahl von Zeichen) in alphabetischen exakten Übereinstimmungen.(any single character) and * (no character or any number of characters) on alphabetical exact matches. Mitunter können Sie diese Methode verwenden, um mehrere Übereinstimmungen zu vermeiden.Sometimes you can use this method to avoid multiple matches.

Optimieren von Arrayformeln und SumProductOptimize array formulas and SUMPRODUCT

Array Formeln und die SumProduct -Funktion sind leistungsfähig, aber Sie müssen Sie sorgfältig behandeln.Array formulas and the SUMPRODUCT function are powerful, but you must handle them carefully. Für eine einzelne Arrayformel sind möglicherweise viele Berechnungen erforderlich.A single array formula might require many calculations.

Der Schlüssel zur Beschleunigung der Berechnung von Arrayformeln ist das Sicherstellen, dass die Anzahl der in der Arrayformel ausgewerteten Zellen und Ausdrücke so klein wie möglich ist.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. Denken Sie daran, dass eine Matrixformel ein bisschen wie eine flüchtige Formel ist: Wenn eine der Zellen, auf die Sie verweist, geändert wurde, flüchtig ist oder neu berechnet wurde, berechnet die Matrixformel alle Zellen in der Formel und wertet alle virtuellen Zellen aus, die für die Berechnung benötigt werden.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.

So beschleunigen Sie die Berechnung von Arrayformeln:To optimize the calculation speed of array formulas:

  • Verschieben Sie Ausdrücke und Bereichsbezüge aus den Arrayformeln in gesonderte Hilfsspalten und -zeilen.Take expressions and range references out of the array formulas into separate helper columns and rows. Auf diese Weise kann der intelligente Neuberechnungsprozess in Excel wesentlich besser zum Tragen kommen.This makes much better use of the smart recalculation process in Excel.

  • Verweisen Sie nicht auf vollständige Zeilen oder mehr Zeilen und Spalten, als Sie benötigen.Do not reference complete rows, or more rows and columns than you need. Array Formeln sind gezwungen, alle Zellbezüge in der Formel zu berechnen, auch wenn die Zellen leer sind oder nicht verwendet werden.Array formulas are forced to calculate all the cell references in the formula even if the cells are empty or unused. Wenn 1 Million Zeilen ab Excel 2007 verfügbar sind, ist eine Arrayformel, die auf eine ganze Spalte verweist, extrem langsam zu berechnen.With 1 million rows available starting in Excel 2007, an array formula that references a whole column is extremely slow to calculate.

  • Ab Excel 2007 können strukturierte Verweise genutzt werden, bei denen Sie die Anzahl der Zellen, die von der Arrayformel ausgewertet werden, auf ein Minimum begrenzen können.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.

  • Verwenden Sie in früheren Versionen als Excel 2007 nach Möglichkeit dynamische Bereichsnamen.In versions earlier than Excel 2007, use dynamic range names where possible. Wenngleich diese veränderlich sind, lohnt sich ihr Einsatz, da sie die Größe von Bereichen minimieren.Although they are volatile, it is worthwhile because they minimize the size of the ranges.

  • Seien Sie vorsichtig bei Arrayformeln, die sowohl auf eine Zeile als auch eine Spalte verweisen, da dadurch die Berechnung eines rechteckigen Bereichs erzwungen wird.Be careful with array formulas that reference both a row and a column: this forces the calculation of a rectangular range.

  • Verwenden Sie möglichst SUMPRODUCT, da diese Funktion schneller als die entsprechende Arrayformel ist.Use SUMPRODUCT if possible; it is slightly faster than the equivalent array formula.

Optionen für die Verwendung von Sum für Arrayformeln mit mehreren Bedingungen in Frage stellenConsider options for using SUM for multiple-condition array formulas

Sie sollten stets nach Möglichkeit die Funktionen SUMIFS, COUNTIFS und AVERAGEIFS anstatt Arrayformeln verwenden, da deren Berechnung wesentlich schneller erfolgt.You should always use the SUMIFS, COUNTIFS, and AVERAGEIFS functions instead of array formulas where you can because they are much faster to calculate. In Excel 2016 werden schnelle MAXIFS -und MINIFS -Funktionen eingeführt.Excel 2016 introduces fast MAXIFS and MINIFS functions.

In früheren Versionen als Excel 2007 werden Arrayformeln häufig verwendet, um eine Summe mit mehreren Bedingungen zu berechnen.In versions earlier than Excel 2007, array formulas are often used to calculate a sum with multiple conditions. Dies ist relativ einfach, vor allem, wenn Sie den bedingten Summen-Assistenten in Excel verwenden, es ist jedoch häufig langsam.This is relatively easy to do, especially if you use the Conditional Sum Wizard in Excel, but it is often slow. Normalerweise gibt es viel schnellere Möglichkeiten, dasselbe Ergebnis zu erzielen.Usually there are much faster ways of getting the same result. Wenn Sie nur wenige Summen mit mehreren Bedingungen haben, können Sie möglicherweise die DSum -Funktion verwenden, die wesentlich schneller ist als die entsprechende Matrixformel.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.

Wenn Sie Arrayformeln verwenden müssen, können Sie einige der folgenden Methoden zu deren Beschleunigung einsetzen:If you must use array formulas, some good methods of speeding them up are as follows:

  • Verwenden Sie dynamische Bereichsnamen oder Verweise auf strukturierte Tabellen, um die Anzahl der Zellen zu minimieren.Use dynamic range names or structured table references to minimize the number of cells.

  • Teilen Sie die verschiedenen Bedingungen in eine Spalte mit Hilfsformeln auf, die für jede Zeile true oder false zurückgeben, und verweisen Sie dann in einer SUMIF -oder Arrayformel auf die Spalte Helfer.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. Dadurch wird möglicherweise nicht die Anzahl der Berechnungen für eine einzelne Arrayformel reduziert. die meiste Zeit ermöglicht es jedoch den intelligenten neuberechnungs Prozess, nur die Formeln in der Spalte "Helfer" neu zu berechnen, die neu berechnet werden müssen.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.

  • Erwägen Sie das Verketten aller Bedingungen zu einer einzelnen Bedingung und das anschließend Verwenden von SUMIF.Consider concatenating together all the conditions into a single condition, and then using SUMIF.

  • Wenn die Daten sortiert werden können, zählen Sie Zeilengruppen, und schränken Sie die Arrayformeln so ein, dass Sie die Teilmengen Gruppen betrachten.If the data can be sorted, count groups of rows and limit the array formulas to looking at the subset groups.

Priorisieren von SUMMEWENNS, ZÄHLENWENNS und anderen IFS-Familien Funktionen mit mehreren BedingungenPrioritize multiple-condition SUMIFS, COUNTIFS, and other IFS family functions

Diese Funktionen bewerten jede der Bedingungen von links nach rechts im Gegenzug.These functions evaluate each of the conditions from left to right in turn. Daher ist es effizienter, zuerst die restriktivste Bedingung zu setzen, damit nachfolgende Bedingungen nur die kleinste Anzahl von Zeilen betrachten müssen.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.

Optionen für die Verwendung von SumProduct für Arrayformeln mit mehreren Bedingungen in Frage stellenConsider options for using SUMPRODUCT for multiple-condition array formulas

Ab Excel 2007 sollten Sie immer die SUMMEWENNS-, ZÄHLENWENNS-und AverageIfs -Funktionen sowie in Excel 2016- MAXIFS -und MINIFS -Funktionen anstelle von SumProduct -Formeln verwenden, wenn möglich.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.

In früheren Versionen gibt es einige Vorteile der Verwendung von SUMPRODUCT anstelle von SUM-Arrayformeln:In earlier versions, there are a few advantages to using SUMPRODUCT instead of SUM array formulas:

  • SumProduct muss nicht mithilfe von STRG + UMSCHALT + EINGABETASTE Array eingegeben werden.SUMPRODUCT does not have to be array-entered by using Ctrl+Shift+Enter.

  • SUMPRODUCT ist in der Regel etwas schneller (5 bis 10 %).SUMPRODUCT is usually slightly faster (5 to 10 percent).

Sie können SumProduct für Arrayformeln mit mehreren Bedingungen wie folgt verwenden:You can use SUMPRODUCT for multiple-condition array formulas as follows:

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

In diesem Beispiel Condition1 Condition2 sind bedingte Ausdrücke wie $A$1:$A$10000<=$Z4.In this example, Condition1 and Condition2 are conditional expressions such as $A$1:$A$10000<=$Z4. Da bedingte Ausdrücke True oder False anstelle von Zahlen zurückgeben, müssen Sie in der SUMPRODUCT-Funktion in Zahlen umgewandelt werden.Because conditional expressions return True or False instead of numbers, they must be coerced to numbers inside the SUMPRODUCT function. Sie können dies mit zwei Minuszeichen (--) oder durch Addieren von 0 (+ 0) oder durch Multiplikation mit 1 (x1) tun.You can do this by using two minus signs (--), or by adding 0 (+0), or by multiplying by 1 (x1). Die -- Verwendung ist etwas schneller als + 0 oder x1.Using -- is slightly faster than +0 or x1.

Beachten Sie, dass Größe und Form der Bereiche oder Arrays, die in den bedingten Ausdrücken und dem zu summierenden Bereich verwendet werden, identisch sein müssen und diese keine ganzen Spalten enthalten dürfen.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.

Sie können die Ausdrücke auch direkt in SumProduct multiplizieren, anstatt Sie durch Kommas zu trennen:You can also directly multiply the terms inside SUMPRODUCT rather than separate them by commas:

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

Dies ist normalerweise etwas langsamer als die Verwendung der Komma-Syntax, und es gibt einen Fehler, wenn der zu summierende Bereich einen Textwert enthält.This is usually slightly slower than using the comma syntax, and it gives an error if the range to sum contains a text value. Es ist jedoch etwas flexibler, da der zu summierende Bereich beispielsweise mehrere Spalten aufweisen kann, wenn die Bedingungen nur eine Spalte enthalten.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.

Verwenden von SumProduct zum Multiplizieren und Hinzufügen von Bereichen und ArraysUse SUMPRODUCT to multiply and add ranges and arrays

Bei Berechnungen des gewichteten Mittelwerts, bei denen Sie einen Zahlenbereich mit einem anderen Zahlenbereich multiplizieren und die Ergebnisse addieren, kann das Verwenden der Syntax mit Kommas für SUMPRODUCT 20-25 % schneller als eine als Array eingegebene SUM-Funktion sein.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)

Diese drei Formeln ergeben alle dasselbe Ergebnis, aber die dritte Formel, die die Kommasyntax für SumProductverwendet, benötigt nur etwa 77% der Berechnungszeit, die die beiden anderen Formeln benötigen.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.

Beachten Sie mögliche Hindernisse für Array-und Funktionsberechnungen.Be aware of potential array and function calculation obstructions

Das Berechnungsmodul in Excel ist für das Arbeiten mit Arrayformeln und Funktionen optimiert, die auf Bereiche verweisen.The calculation engine in Excel is optimized to exploit array formulas and functions that reference ranges. Doch eine bestimmte ungewöhnliche Anordnung dieser Formeln und Funktionen kann mitunter, jedoch nicht immer, zu einer beträchtlich längeren Berechnungszeit führen.However, some unusual arrangements of these formulas and functions can sometimes, but not always, cause significantly increased calculation time.

Wenn Sie ein Berechnungshindernis finden, das Arrayformeln und Bereichsfunktionen einschließt, sollten Sie Folgendes suchen:If you find a calculation obstruction that involves array formulas and range functions, you should look for the following:

  • Teilweise überlappende Verweise.Partially overlapping references.

  • Arrayformeln und Bereichsfunktionen, die auf einen Teil eines Blocks mit Zellen verweisen, die in einer anderen Arrayformel oder Bereichsfunktion berechnet werden.Array formulas and range functions that reference part of a block of cells that are calculated in another array formula or range function. Dies kommt häufig bei Zeitreihenanalysen vor.This situation can frequently occur in time series analysis.

  • Eine Gruppe mit Formeln, die mit Zeilenbezügen arbeitet, und eine zweite Gruppen mit Formeln, die auf die erste Gruppe nach Spalte verweist.One set of formulas referencing by row, and a second set of formulas referencing the first set by column.

  • Eine große Gruppe von Arrayformeln mit einer Zeile, die einen Block mit Spalten abdeckt, mit SUM-Funktionen am Fuß jeder Spalte.A large set of single-row array formulas covering a block of columns, with SUM functions at the foot of each column.

Effizientes Verwenden von FunktionenUse functions efficiently

Funktionen erweitern erheblich die Leistungsfähigkeit von Excel, aber die Art und Weise, wie Sie Sie verwenden, kann sich häufig auf die Berechnungszeit auswirken.Functions significantly extend the power of Excel, but the way in which you use them can often affect calculation time.

Vermeiden von Single-Thread-FunktionenAvoid single-threaded functions

Die meisten systemeigenen Excel-Funktionen funktionieren bei der Berechnung mit mehreren Threads gut.Most native Excel functions work well with multi-threaded calculation. Wenn möglich, sollten Sie jedoch die folgenden Single-Thread-Funktionen nicht verwenden:However, where possible, avoid using the following single-threaded functions:

  • VBA-und Automatisierungs benutzerdefinierte Funktionen (UDFs), aber XLL-basierte UDFs können Multithread-basiert seinVBA and Automation user-defined functions (UDFs), but XLL-based UDFs can be multi-threaded
  • PHONETICPHONETIC
  • ZELLE mit Argument „format“ oder Argument „address“CELL when either the "format" or "address" argument is used
  • INDIREKTINDIRECT
  • PIVOTDATENZUORDNENGETPIVOTDATA
  • CUBEELEMENTCUBEMEMBER
  • CUBEWERTCUBEVALUE
  • CUBEELEMENTEIGENSCHAFTCUBEMEMBERPROPERTY
  • CUBEMENGECUBESET
  • CUBERANGELEMENTCUBERANKEDMEMBER
  • CUBEKPIELEMENTCUBEKPIMEMBER
  • CUBEMENGENANZAHLCUBESETCOUNT
  • Adresse, an der der fünfte Parameter sheet_name(der) angegeben wirdADDRESS where the fifth parameter (the sheet_name) is given
  • Jede Datenbankfunktion (DBSUMME, DBMITTELWERT usw.), die auf eine PivotTable verweistAny database function (DSUM, DAVERAGE, and so on) that refers to a PivotTable
  • FEHLER.TYPERROR.TYPE
  • HYPERLINKHYPERLINK

Verwenden von Tabellen für Funktionen, die Bereiche verarbeitenUse tables for functions that handle ranges

Für Funktionen wie Sum, SUMIFund SUMMEWENNS , die Bereiche verarbeiten, ist die Berechnungszeit proportional zur Anzahl der verwendeten Zellen, die Sie summieren oder zählen.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. Nicht verwendete Zellen werden nicht untersucht, sodass ganze Spaltenverweise relativ effizient sind, es ist jedoch besser, sicherzustellen, dass Sie nicht mehr verwendete Zellen einbeziehen, als Sie benötigen.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. Verwenden Sie Tabellen, oder berechnen Sie Teilmenge Bereiche oder dynamische Bereiche.Use tables, or calculate subset ranges or dynamic ranges.

Reduzieren von flüchtigen FunktionenReduce volatile functions

Veränderliche Funktionen können Neuberechnungen verlangsamen, da sie die Anzahl der Formeln erhöhen, die bei jeder Berechnung neu berechnet werden müssen.Volatile functions can slow recalculation because they increase the number of formulas that must be recalculated at each calculation.

Sie können häufig die Anzahl veränderlicher Funktionen verringern, indem Sie INDEX anstatt OFFSET und CHOOSE anstatt INDIRECT verwenden.You can often reduce the number of volatile functions by using INDEX instead of OFFSET, and CHOOSE instead of INDIRECT. Offset ist jedoch eine schnelle Funktion und kann häufig auf kreative Weise verwendet werden, um eine schnelle Berechnung zu ermöglichen.However, OFFSET is a fast function and can often be used in creative ways that give fast calculation.

Verwenden von benutzerdefinierten C-oder C++-FunktionenUse C or C++ user-defined functions

Benutzerdefinierte Funktionen, die in c oder C++ programmiert sind und die c-API (XLL-Add-in-Funktionen) verwenden, werden in der Regel schneller ausgeführt als benutzerdefinierte Funktionen, die mithilfe von VBA oder Automation (XLA oder Automatisierungs-Add-Ins) entwickelt werden.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). Weitere Informationen finden Sie unter Developing Excel 2010 XLLs.For more information, see Developing Excel 2010 XLLs.

Die Leistung benutzerdefinierter VBA-Funktionen hängt sehr von ihrer Programmierung und dem Aufruf ab.The performance of VBA user-defined functions is sensitive to how you program and call them.

Schnellere Verwendung von benutzerdefinierten VBA-FunktionenUse faster VBA user-defined functions

Es ist normalerweise schneller, die Excel-Formelberechnungen und Arbeitsblattfunktionen zu verwenden, als mit benutzerdefinierten VBA-Funktionen zu arbeiten.It is usually faster to use the Excel formula calculations and worksheet functions than to use VBA user-defined functions. Der Grund ist zum einen der erhöhte Verarbeitungsaufwand für jeden Aufruf einer benutzerdefinierten Funktion und zum anderen der beträchtliche Verarbeitungsaufwand beim Übertragen von Informationen aus Excel in die benutzerdefinierte Funktion.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. Dennoch können überlegt entworfene und aufgerufene benutzerdefinierte Funktionen wesentlich schneller als komplexe Arrayformeln sein.But well-designed and called user-defined functions can be much faster than complex array formulas.

Vergewissern Sie sich, dass Sie alle Verweise auf Arbeitsblattzellen in die Eingabeparameter der benutzerdefinierten Funktion und nicht in den Hauptteil der benutzerdefinierten Funktion eingegeben haben, damit Sie Application.Volatile nicht unnötigerweise hinzufügen müssen.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.

Wenn Sie über viele Formeln verfügen müssen, die benutzerdefinierte Funktionen verwenden, stellen Sie sicher, dass Sie sich im manuellen Berechnungsmodus befinden und dass die Berechnung von VBA initiiert wird.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. Benutzerdefinierte VBA-Funktionen werden wesentlich langsamer berechnet, wenn die Berechnung nicht aus VBA aufgerufen wird (z. B. im automatischen Modus oder bei Drücken von F9 im manuellen Modus).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). Dies gilt insbesondere, wenn der Visual Basic-Editor (ALT + F11) geöffnet ist oder in der aktuellen Excel-Sitzung geöffnet wurde.This is particularly true when the Visual Basic Editor (Alt+F11) is open or has been opened in the current Excel session.

Sie können F9 abfangen und wie folgt an eine VBA-Berechnungsunterroutine weiterleiten.You can trap F9 and redirect it to a VBA calculation subroutine as follows. Fügen Sie diese Unterroutine zum ThisWorkbook -Modul hinzu.Add this subroutine to the Thisworkbook module.

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

Fügen Sie diese Unterroutine einem Standardmodul hinzu.Add this subroutine to a standard module.

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

Bei benutzerdefinierten Funktionen in Automatisierungs-Add-Ins (Excel 2002 und höher) fällt der Visual Basic Editor-Verarbeitungsaufwand nicht an, da sie nicht den integrierten Editor verwenden.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. Die weiteren Leistungsmerkmale benutzerdefinierter Visual Basic 6-Funktionen in Automatisierungs-Add-Ins entsprechen denen von VBA-Funktionen.Other performance characteristics of Visual Basic 6 user-defined functions in Automation add-ins are similar to VBA functions.

Wenn Ihre benutzerdefinierte Funktion alle Zellen in einem Bereich verarbeitet, deklarieren Sie die Eingabe als Bereich, weisen Sie sie einer Variante zu, die ein Array enthält, und fügen Sie eine Schleife hinzu.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. Wenn Sie Verweise auf ganze Spalten effizient verarbeiten möchten, müssen Sie einen Teilbereich des Eingabebereichs erstellen und diesen wie in diesem Beispiel am seinem Schnittpunkt mit dem verwendeten Bereich teilen.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

Wenn Ihre benutzerdefinierte Funktion Arbeitsblattfunktionen oder Methoden des Excel-Objektmodells zum Verarbeiten eines Bereichs verwendet, ist es meist effizienter, den Bereich als Objektvariable beizubehalten, anstatt alle Daten aus Excel in die benutzerdefinierte Funktion zu übertragen.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

Wenn die benutzerdefinierte Funktion früh in der berechnungskette aufgerufen wird, kann Sie als nicht berechnete Argumente übergeben werden.If your user-defined function is called early in the calculation chain, it can be passed as uncalculated arguments. Innerhalb einer benutzerdefinierten Funktion können Sie nicht berechnete Zellen ermitteln, indem Sie den folgenden Test für leere Zellen verwenden, die eine Formel enthalten: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

Für jeden Aufruf einer benutzerdefinierten Funktion und für jede Übertragung von Daten von Excel nach VBA gibt es einen Zeitaufwand.A time overhead exists for each call to a user-defined function and for each transfer of data from Excel to VBA. Mitunter kann mithilfe einer benutzerdefinierten Funktion mit einer sich auf mehrere Zellen beziehenden Arrayformel dieser Verarbeitungsaufwand verringert werden, indem mehrere Funktionsaufrufe zu einer einzelnen Funktion mit einem Eingabebereich mit mehreren Zellen zusammengefasst werden, der einen Bereich von Ergebnissen liefert.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.

Bereich von Zellen minimieren, die Sum und SUMIF ReferenceMinimize range of cells that SUM and SUMIF reference

Die Excel-Funktionen SUM und SUMIF werden häufig auf eine große Anzahl von Zellen angewendet.The Excel SUM and SUMIF functions are frequently used over a large number of cells. Die Berechnungszeit dieser Funktionen ist proportional zur Anzahl der abgedeckten Zellen, weshalb Sie den Bereich der Zellen minimieren sollten, auf die die Funktionen verweisen.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.

Verwenden von Platzhalter SUMIF, ZÄHLENWENN, SUMMEWENNS, ZÄHLENWENNS und anderen IFS-FunktionenUse wildcard SUMIF, COUNTIF, SUMIFS, COUNTIFS, and other IFS functions

Sie können die Platzhalterzeichen verwenden ?You can use the wildcard characters ? (beliebiges einzelnes Zeichen) * und (kein Zeichen oder eine beliebige Anzahl von Zeichen) in den Kriterien für alphabetische Bereiche als Teil der SUMIF-, ZÄHLENWENN-, SUMMEWENNS-, ZÄHLENWENNS-und anderen IFS -Funktionen.(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.

Choose-Methode für period-to-date und kumulierte SummenChoose method for period-to-date and cumulative SUMs

Es gibt zwei Methoden zum Ausführen von Perioden-zu-Datum-oder kumulativen Summen.There are two methods of doing period-to-date or cumulative SUMs. Angenommen, die Zahlen, die Sie kumulativ summieren möchten, sind in Spalte A enthalten, und Sie möchten, dass die Spalte B die kumulative Summe enthält. Sie können eine der folgenden Aktionen ausführen: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:

  • Sie können eine Formel in Spalte b wie erstellen =SUM($A$1:$A2) und nach unten ziehen, so weit Sie benötigen.You can create a formula in column B such as =SUM($A$1:$A2) and drag it down as far as you need. Die Anfangszelle der SUMME-Funktion ist in A1 verankert, doch da die Abschlusszelle einen relativen Zeilenbezug aufweist, wird sie automatisch für jede Zeile erhöht.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.

  • Sie können eine Formel wie =$A1 in Zelle B1 und =$B1+$A2 in Zelle B2 erstellen und nach Bedarf nach unten ziehen.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. Die kumulative Zelle wird berechnet, indem der Wert dieser Zeile der vorherigen kumulativen SUM-Funktion hinzugefügt wird.This calculates the cumulative cell by adding this row's number to the previous cumulative SUM.

Bei 1000 Zeilen muss Excel bei der ersten Methode ca. 500.000 Berechnungen, bei der zweiten Methode nur ca. 2000 Berechnungen durchführen.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.

Berechnen von Untermengen SummenCalculate subset sums

Wenn Sie mehrere sortierte Indizes für eine Tabelle haben (beispielsweise Standort im Bereich), können Sie häufig eine erhebliche Berechnungszeit sparen, indem Sie die Adresse eines Teilbereichs von Zeilen (oder Spalten), der in der Sum -oder SUMIF -Funktion verwendet werden soll, dynamisch berechnen.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.

So berechnen Sie die Adresse eines Teilmengen Bereichs von Zeilen oder Spalten:To calculate the address of a subset range of row or columns:

  1. Zählen Sie die Anzahl der Zeilen jedes Teilblocks.Count the number of rows for each subset block.

  2. Addieren Sie die Anzahlen für jeden Block kumulativ, um dessen Startzeile zu bestimmen.Add the counts cumulatively for each block to determine its start row.

  3. Geben Sie die OFFSET-Funktion mit der Startzeile und Anzahl an, um einen Teilbereich an die Funktion SUM oder SUMIF zurückzugeben, der nur den Teilblock der Zeilen abdeckt.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.

Verwenden von Teilergebnis für gefilterte ListenUse SUBTOTAL for filtered lists

Wählen Sie die SUBTOTAL-Funktion zum Anwenden von SUM auf gefilterte Listen.Use the SUBTOTAL function to SUM filtered lists. Die SUBTOTAL-Funktion ist sinnvoll, da diese Funktion im Gegensatz zu SUM Folgendes ignoriert:The SUBTOTAL function is useful because, unlike SUM, it ignores the following:

  • Ausgeblendete Zeilen, die aus dem Filtern einer Liste resultieren.Hidden rows that result from filtering a list. Ab Excel 2003 können Sie auch SUBTOTAL veranlassen, alle ausgeblendeten und nicht bloß gefilterte Zeilen zu ignorieren.Starting in Excel 2003, you can also make SUBTOTAL ignore all hidden rows, not just filtered rows.

  • Weitere SUBTOTAL-Funktionen.Other SUBTOTAL functions.

Verwenden der AggregatfunktionUse the AGGREGATE function

Die Aggregatfunktion ist eine leistungsstarke und effiziente Methode zum Berechnen von 19 verschiedenen Methoden zum Aggregieren von Daten (wie Summe, Median, Perzentil und groß).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 verfügt über Optionen zum Ignorieren von ausgeblendeten oder gefilterten Zeilen, Fehlerwerten und geschachtelten Teil Ergebnis -und Aggregat Funktionen.AGGREGATE has options for ignoring hidden or filtered rows, error values, and nested SUBTOTAL and AGGREGATE functions.

Vermeiden der Verwendung von DFunctionsAvoid using DFunctions

Die DFunctions DSum, DCount, DAverageusw. sind wesentlich schneller als äquivalente Arrayformeln.The DFunctions DSUM, DCOUNT, DAVERAGE, and so on are significantly faster than equivalent array formulas. Der Nachteil der DFunctions besteht darin, dass die Kriterien in einem separaten Bereich liegen müssen, wodurch Sie in vielen Fällen nicht mehr verwendet und verwaltet werden können.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. Ab Excel 2007 sollten Sie anstelle der DFunctions SUMMEWENNS-, ZÄHLENWENNS-und AverageIfs -Funktionen verwenden.Starting in Excel 2007, you should use SUMIFS, COUNTIFS, and AVERAGEIFS functions instead of the DFunctions.

Erstellen schnellerer VBA-MakrosCreate faster VBA macros

Verwenden Sie die folgenden Tipps, um schnellere VBA-Makros zu erstellen.Use the following tips to create faster VBA macros.

Deaktivieren Sie alles, aber das wesentliche, während Code ausgeführt wirdTurn off everything but the essentials while code is running

Um die Leistung für VBA-Makros zu verbessern, deaktivieren Sie explizit die Funktionalität, die beim Ausführen des Codes nicht erforderlich ist.To improve performance for VBA macros, explicitly turn off the functionality that is not required while your code executes. Häufig ist eine Neuberechnung oder ein Neuzeichnen nach dem Ausführen des Codes alles, was erforderlich ist, und kann die Leistung verbessern.Often, one recalculation or one redraw after your code runs is all that is necessary and can improve performance. Stellen Sie nach dem Ausführen des Codes die Funktionalität im ursprünglichen Zustand wieder her.After your code executes, restore the functionality to its original state.

Die folgende Funktionalität kann während der Ausführung Ihres VBA-Makros im Allgemeinen deaktiviert sein:The following functionality can usually be turned off while your VBA macro executes:

  • Application. ScreenUpdating deaktivieren Sie die Bildschirmaktualisierung.Application.ScreenUpdating Turn off screen updating. Wenn Application. ScreenUpdating auf falsefestgelegt ist, wird der Bildschirm von Excel nicht neu gezeichnet.If Application.ScreenUpdating is set to False, Excel does not redraw the screen. Während Ihr Code ausgeführt wird, wird der Bildschirm schnell aktualisiert, und es ist normalerweise nicht erforderlich, dass der Benutzer jedes Update sieht.While your code runs, the screen updates quickly, and it is usually not necessary for the user to see each update. Eine einmalige Aktualisierung nach Ausführung des Codes kann die Leistung verbessern.Updating the screen once, after the code executes, improves performance.

  • Application. Display StatusBar deaktivieren Sie die Statusleiste.Application.DisplayStatusBar Turn off the status bar. Wenn Application. Display StatusBar auf falsefestgelegt ist, wird die Statusleiste von Excel nicht angezeigt.If Application.DisplayStatusBar is set to False, Excel does not display the status bar. Die Statusleiste ist von der Bildschirmaktualisierungseinstellung getrennt, weshalb Sie weiter den Status des aktuellen Vorgangs sehen können, auch während der Bildschirm nicht aktualisiert wird.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. Wenn Sie jedoch nicht den Status jedes Vorgangs sehen müssen, wird durch Deaktivieren der Statusleiste bei der Ausführung Ihres Codes die Leistung verbessert.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 wechselt zur manuellen Berechnung.Application.Calculation Switch to manual calculation. Wenn Application. calculation auf xlCalculationManualfestgelegt ist, berechnet Excel die Arbeitsmappe nur, wenn der Benutzer die Berechnung explizit initiiert.If Application.Calculation is set to xlCalculationManual, Excel only calculates the workbook when the user explicitly initiates the calculation. Im automatischen Berechnungsmodus bestimmt Excel den Zeitpunkt der Berechnung.In automatic calculation mode, Excel determines when to calculate. Wenn sich beispielsweise ein Zellwert ändert, der sich auf eine Formel bezieht, berechnet Excel die Formel neu.For example, every time a cell value that is related to a formula changes, Excel recalculates the formula. Wenn Sie in den manuellen Berechnungsmodus wechseln, können Sie abwarten, bis alle zu der Formel gehörenden Zellen geändert wurden, ehe Sie die Arbeitsmappe neu berechnen.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. Wenn die Arbeitsmappe bei Bedarf nur neu berechnet wird, während der Code ausgeführt wird, können Sie die Leistung verbessern.By only recalculating the workbook when necessary while your code runs, you can improve performance.

  • Application. enableEvents Deaktivieren von Ereignissen.Application.EnableEvents Turn off events. Wenn Application. enableEvents auf falsefestgelegt ist, löst Excel keine Ereignisse aus.If Application.EnableEvents is set to False, Excel does not raise events. Wenn Add-Ins auf Excel-Ereignisse abhören, verbrauchen diese Add-Ins Ressourcen auf dem Computer, während Sie die Ereignisse aufzeichnen.If there are add-ins listening for Excel events, those add-ins consume resources on the computer as they record the events. Wenn das Add-in nicht erforderlich ist, um die Ereignisse aufzuzeichnen, die beim Ausführen des Codes auftreten, wird durch das Deaktivieren von Ereignissen die Leistung verbessert.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 deaktivieren Sie Seitenumbrüche.ActiveSheet.DisplayPageBreaks Turn off page breaks. Wenn ActiveSheet. DisplayPageBreaks auf falsefestgelegt ist, zeigt Excel keine Seitenumbrüche an.If ActiveSheet.DisplayPageBreaks is set to False, Excel does not display page breaks. Das Neuberechnen von Seitenumbrüchen während der Ausführung Ihres Codes ist nicht erforderlich, weshalb dieser Vorgang im Anschluss an die Ausführung Ihres Codes die Leistung verbessert.It is not necessary to recalculate page breaks while your code runs, and calculating the page breaks after the code executes improves performance.

Wichtig

Vergessen Sie nicht, diese Funktionalität nach der Ausführung Ihres Codes wieder in den Originalzustand zurückzuversetzen.Remember to restore this functionality to its original state after your code executes.

Das folgende Beispiel zeigt die Funktionalität, die während der Ausführung Ihres VBA-Makros deaktiviert sein kann.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

Lesen und Schreiben großer Datenblöcke in einem einzigen VorgangRead and write large blocks of data in a single operation

Optimieren Sie Ihren Code, indem Sie explizit die Häufigkeit verringern, mit der Daten zwischen Excel und Ihrem Code übertragen werden.Optimize your code by explicitly reducing the number of times data is transferred between Excel and your code. Anstatt die Zellen nacheinander in einer Schleife zu durchlaufen, um einen Wert abzurufen oder festzulegen, können Sie die Werte im gesamten Zellenbereich in einer Zeile abrufen oder festlegen, und zwar mithilfe einer Variante mit einem zweidimensionalen Array zum Speichern der Werte den Anforderungen entsprechend.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. In den folgenden Codebeispielen werden diese beiden Methoden verglichen.The following code examples compare these two methods.

Das folgende Codebeispiel zeigt nicht optimierten Code, der die Zellen nacheinander in einer Schleife durchläuft, um die Werte der Zellen A1:C10000 abzurufen und festzulegen.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. Diese Zellen enthalten keine Formeln.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

Das folgende Codebeispiel zeigt optimierten Code, der ein Array verwendet, um die Werte der Zellen A1:C10000 gleichzeitig abzurufen und festzulegen.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. Diese Zellen enthalten keine Formeln.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 

Verwenden. Value2 statt. Wert oder. Text beim Lesen von Daten aus einem Excel-BereichUse .Value2 rather than .Value or .Text when reading data from an Excel range

  • . Text gibt den formatierten Wert einer Zelle zurück..Text returns the formatted value of a cell. Dies ist langsam, kann # # # zurückgeben, wenn der Benutzer zoomt, und kann die Genauigkeit verlieren.This is slow, can return ### if the user zooms, and can lose precision.
  • . Wert gibt eine VBA-Währung oder eine VBA-Datumsvariable zurück, wenn der Bereich als Datum oder Währung formatiert wurde..Value returns a VBA currency or VBA date variable if the range was formatted as Date or Currency. Dies ist langsam, kann die Genauigkeit verlieren und kann zu Fehlern beim Aufrufen von Arbeitsblattfunktionen führen.This is slow, can lose precision, and can cause errors when calling worksheet functions.
  • . Value2 ist schnell und ändert nicht die Daten, die aus Excel abgerufen werden..Value2 is fast and does not alter the data being retrieved from Excel.

Vermeiden der Auswahl und Aktivierung von ObjektenAvoid selecting and activating objects

Das Auswählen und Aktivieren von Objekten ist verarbeitungsintensiver als das direkte Verweisen auf Objekte.Selecting and activating objects is more processing intensive than referencing objects directly. Wenn Sie direkt auf ein Objekt wie einen Bereich oder eine Form verweisen, können Sie die Leistung verbessern.By referencing an object such as a Range or a Shape directly, you can improve performance. In den folgenden Codebeispielen werden die beiden Methoden verglichen.The following code examples compare the two methods.

Das folgende Codebeispiel zeigt einen nicht optimierten Code, der jede Form im aktiven Blatt auswählt und den Text in "Hello" ändert.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

Das folgende Codebeispiel zeigt optimierten Code, der auf jedes Shape direkt verweist, und ändert den Text in "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

Verwenden Sie diese zusätzlichen VBA-LeistungsoptimierungenUse these additional VBA performance optimizations

Es folgt eine Liste mit weiteren möglichen Leistungsoptimierungen für Ihren VBA-Code:The following is a list of additional performance optimizations you can use in your VBA code:

  • Geben Sie Ergebnisse zurück, indem Sie ein Array direkt einem Range-Objekt zuweisen.Return results by assigning an array directly to a Range.

  • Deklarieren Sie Variablen mit expliziten Typen zum Vermeiden des Verarbeitungsaufwands zum Bestimmen des Datentyps (möglicherweise mehrfach in einer Schleife) während der Codeausführung.Declare variables with explicit types to avoid the overhead of determining the data type, possibly multiple times in a loop, during code execution.

  • Implementieren Sie für einfache Funktionen, die Sie häufig in Ihrem Code verwenden, die Funktionen selbst in VBA, anstatt mit dem WorksheetFunction-Objekt zu arbeiten.For simple functions that you use frequently in your code, implement the functions yourself in VBA instead of using the WorksheetFunction object. Weitere Informationen finden Sie unter use Faster VBA User-Defined Functions.For more information, see Use faster VBA user-defined functions.

  • Verwenden Sie die Range.SpecialCells-Methode zum Verringern der Anzahl der Zellen, mit denen Ihr Code interagiert.Use the Range.SpecialCells method to scope down the number of cells with which your code interacts.

  • Überprüfen Sie die Leistungssteigerungen, wenn Sie Ihre Funktionalität mithilfe der C-API im XLL-SDK implementiert haben.Consider the performance gains if you implemented your functionality by using the C API in the XLL SDK. Weitere Informationen finden Sie in der Dokumentation zum Excel 2010 XLL SDK.For more information, see the Excel 2010 XLL SDK Documentation.

Überprüfen der Leistung und Größe von Excel-DateiformatenConsider performance and size of Excel file formats

Beginnend mit Excel 2007 bietet Excel im Vergleich zu früheren Formaten eine große Vielfalt von Dateiformaten.Starting in Excel 2007, Excel contains a wide variety of file formats compared to earlier versions. Die drei Hauptformate sind XLS, XLSB und XLSX, wobei die Dateiformat Variationen Makro, Vorlage, Add-in, PDF und XPS ignoriert werden.Ignoring the Macro, Template, Add-in, PDF, and XPS file format variations, the three main formats are XLS, XLSB, and XLSX.

  • XLS-FormatXLS format

    Das XLS-Format hat das gleiche Format wie frühere Versionen.The XLS format is the same format as earlier versions. Wenn Sie dieses Format verwenden, sind Sie auf 256 Spalten und 65.536 Zeilen beschränkt.When you use this format, you are restricted to 256 columns and 65,536 rows. Wenn Sie eine Excel 2007-oder Excel 2010-Arbeitsmappe im XLS-Format speichern, führt Excel eine Kompatibilitätsüberprüfung aus.When you save an Excel 2007 or Excel 2010 workbook in XLS format, Excel runs a compatibility check. Die Dateigröße ist fast identisch mit früheren Versionen (einige zusätzliche Informationen werden möglicherweise gespeichert), und die Leistung ist etwas langsamer als bei früheren Versionen.File size is almost the same as earlier versions (some additional information may be stored), and performance is slightly slower than earlier versions. Jede Multi-Thread Optimierung von Excel in Bezug auf die Reihenfolge der Zellenberechnung wird nicht im XLS-Format gespeichert.Any multi-threaded optimization Excel does with respect to cell calculation order is not saved in the XLS format. Daher kann die Berechnung einer Arbeitsmappe langsamer nach dem Speichern der Arbeitsmappe im XLS-Format, beim Schließen und erneuten Öffnen der Arbeitsmappe sein.Therefore, calculation of a workbook can be slower after saving the workbook in the XLS format, closing, and re-opening the workbook.

  • XLSB-FormatXLSB format

    XLSB ist das Binärformat, das in Excel 2007 beginnt.XLSB is the binary format starting in Excel 2007. Er ist als komprimierter Ordner strukturiert, der viele Binärdateien enthält.It is structured as a compressed folder that contains many binary files. Es ist viel kompakter als das XLS-Format, aber die Komprimierungs Menge hängt vom Inhalt der Arbeitsmappe ab.It is much more compact than the XLS format, but the amount of compression depends on the contents of the workbook. Bei zehn Arbeitsmappen reicht der Reduzierungsfaktor von zwei bis acht, wobei die durchschnittliche Reduzierung den Faktor vier hat.For example, ten workbooks show a size reduction factor ranging from two to eight with an average reduction factor of four. Ab Excel 2007 ist das Öffnen und Speichern der Leistung nur geringfügig langsamer als das XLS-Format.Starting in Excel 2007, opening and saving performance is only slightly slower than the XLS format.

  • XLSX-FormatXLSX format

    XLSX ist das in Excel 2007 eingeführte XML-Format, das ab dieser Version zugleich das Standardformat ist.XLSX is the XML format starting in Excel 2007, and is the default format starting in Excel 2007. Das XLSX-Format ist ein komprimierter Ordner, der viele XML-Dateien enthält (wenn Sie die Dateinamenerweiterung in. zip ändern, können Sie den komprimierten Ordner öffnen und dessen Inhalt überprüfen).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). In der Regel sind im XLSX-Format erstellte Dateien größer als Dateien im XLSB-Format (durchschnittlich 1,5-mal), allerdings immer noch wesentlich kleiner als XLS-Dateien.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. Das Öffnen und Schließen dauert etwas länger als bei XLSB-Dateien.You should expect opening and saving times to be slightly longer than for XLSB files.

Öffnen, schließen und Speichern von ArbeitsmappenOpen, close, and save workbooks

Sie können feststellen, dass das Öffnen, schließen und Speichern von Arbeitsmappen viel langsamer ist als die Berechnung.You may find that opening, closing, and saving workbooks is much slower than calculating them. Dies liegt manchmal nur daran, dass Sie eine große Arbeitsmappe haben, aber es kann auch andere Gründe geben.Sometimes this is just because you have a large workbook, but there can also be other reasons.

Wenn eine oder mehrere Ihrer Arbeitsmappen langsamer geöffnet und geschlossen werden als gewohnt, kann eines der folgenden Probleme vorliegen.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.

  • Temporäre DateienTemporary files

    \Temporäre Dateien können sich in Ihrem Windows\-Temp-Verzeichnis ansammeln (in Windows 95, Windows 98 und Windows Me) oder \in Ihren Dokumenten\und Einstellungen\Benutzer Name\lokale Einstellungen Temp-Verzeichnis (in Windows 2000 und 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 erstellt diese Dateien für die Arbeitsmappe und für Steuerelemente, die von geöffneten Arbeitsmappen verwendet werden.Excel creates these files for the workbook and for controls that are used by open workbooks. Software Installationsprogramme erstellen auch temporäre Dateien.Software installation programs also create temporary files. Wenn Excel aus irgendeinem Grund nicht mehr reagiert, müssen Sie diese Dateien möglicherweise löschen.If Excel stops responding for any reason, you might need to delete these files.

    Zu viele temporäre Dateien können Probleme verursachen, daher sollten Sie Sie gelegentlich aufräumen. Wenn Sie jedoch Software installiert haben, die einen Neustart des Computers erfordert, und Sie dies noch nicht getan haben, sollten Sie den Neustart vor dem Löschen der temporären Dateien durchführen.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.

 Eine einfache Möglichkeit zum Öffnen des TEMP-Verzeichnisses finden Sie im Startmenü von Windows: klicken Sie auf Startund dann auf Ausführen.An easy way to open your temp directory is from the Windows Start menu: Click Start, and then click Run. Geben Sie in das Textfeld % Temp% ein, und klicken Sie dann auf OK.In the text box, type %temp%, and then click OK.

  • Nachverfolgen von Änderungen in einer freigegebenen ArbeitsmappeTracking changes in a shared workbook

    Das Nachverfolgen von Änderungen in einer freigegeben Arbeitsmappe sorgt für ein rapides Anwachsen der Dateigröße der Arbeitsmappe.Tracking changes in a shared workbook causes your workbook file-size to increase rapidly.

  • Fragmentierte AuslagerungsdateiFragmented swap file

    Vergewissern Sie sich, dass sich Ihre Windows-Auslagerungsdatei auf einem Datenträger mit viel Speicherplatz befindet, und defragmentieren Sie den Datenträger regelmäßig.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.

  • Arbeitsmappe mit kennwortgeschützter StrukturWorkbook with password-protected structure

    Eine Arbeitsmappe, deren Struktur durch ein Kennwort geschützt ist (MenüExtras > Schutz > Arbeitsmappe schützen > das optionale Kennwort eingeben) wird viel langsamer geöffnet und geschlossen, als ein Wert, der ohne das optionale Kennwort geschützt ist.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.

  • Verwendete Bereichs ProblemeUsed range problems

    Übergroße verwendete Bereiche können für ein langsames Öffnen und eine hohe Dateigröße sorgen, insbesondere wenn sie durch ausgeblendete Zeilen oder Spalten mit nicht standardmäßiger Höhe oder Breite verursacht werden.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. Weitere Informationen zu Problemen mit dem verwendeten Bereich finden Sie unter Minimieren des verwendeten Bereichs.For more information about used range problems, see Minimize the used range.

  • Große Anzahl von Steuerelementen auf ArbeitsblätternLarge number of controls on worksheets

    Eine große Anzahl von Steuerelementen (Kontrollkästchen, Hyperlinks usw.) auf Arbeitsblättern kann das Öffnen einer Arbeitsmappe aufgrund der Anzahl der temporären Dateien, die verwendet werden, verlangsamen.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. Dies kann auch dazu führen, dass beim Öffnen oder Speichern einer Arbeitsmappe in einem WAN (oder sogar einem LAN) Probleme auftreten.This might also cause problems opening or saving a workbook on a WAN (or even a LAN). Wenn Sie dieses Problem haben, sollten Sie die Neugestaltung Ihrer Arbeitsmappe in Betracht gezogen.If you have this problem, you should consider redesigning your workbook.

  • Große Anzahl von Links zu anderen ArbeitsmappenLarge number of links to other workbooks

    Öffnen Sie möglichst die Arbeitsmappen, zu denen eine Verknüpfung erstellt wird, bevor Sie die Arbeitsmappe öffnen, die die Verknüpfungen enthält.If possible, open the workbooks that you are linking to before you open the workbook that contains the links. Häufig ist es schneller eine Arbeitsmappe zu öffnen als die Verknüpfungen aus einer geschlossenen Arbeitsmappe zu lesen.Often it is faster to open a workbook than to read the links from a closed workbook.

  • Einstellungen für VirenscannerVirus scanner settings

    Einige Virenscannereinstellungen können Probleme oder Verlangsamungen beim Öffnen, Schließen oder Speichern verursachen, insbesondere auf einem Server.Some virus scanner settings can cause problems or slowness with opening, closing, or saving, especially on a server. Wenn Sie meinen, dass dies das Problem sein könnte, deaktivieren Sie den Virenscanner vorübergehend.If you think that this might be the problem, try temporarily switching the virus scanner off.

  • Langsame Berechnung verursacht langsames öffnen und speichernSlow calculation causing slow open and save

    Unter bestimmten Umständen berechnet Excel Ihre Arbeitsmappe beim Öffnen oder Schließen neu.Under some circumstances, Excel recalculates your workbook when it opens or saves it. Wenn die Berechnungszeit für Ihre Arbeitsmappe lang ist und ein Problem verursacht, stellen Sie sicher, dass die Berechnung auf manuellfestgelegt ist, und deaktivieren Sie die Option Calculate before Save (Options > BerechnungfürTools > ).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).

  • Toolbar-Dateien (. xlb)Toolbar files (.xlb)

    Überprüfen Sie die Größe der Symbolleistendatei.Check the size of your toolbar file. Die Größe einer typischen Symbolleistendatei ist 10-20 KB.A typical toolbar file is between 10 KB and 20 KB. Sie finden Ihre xlb-Dateien, indem *.xlb Sie mithilfe der Windows-Suche nach suchen.You can find your XLB files by searching for *.xlb by using Windows search. Jeder Benutzer hat eine eindeutige XLB-Datei.Each user has a unique XLB file. Durch Hinzufügen, Ändern oder Anpassen von Symbolleisten erhöht sich der Größe Ihrer Datei toolbar.xlb.Adding, changing, or customizing toolbars increases the size of your toolbar.xlb file. Durch Löschen der Datei werden alle Symbolleistenanpassungen entfernt (sicherer ist das Umbenennen in "toolbar.OLD").Deleting the file removes all your toolbar customizations (renaming it "toolbar.OLD" is safer). Eine neue XLB-Datei wird beim nächsten Öffnen von Excel erstellt.A new XLB file is created the next time you open Excel.

Vornehmen zusätzlicher LeistungsoptimierungenMake additional performance optimizations

In den folgenden Bereichen können Sie Leistungsverbesserungen vornehmen.You can make performance improvements in the following areas.

  • PivotTablesPivotTables

    PivotTables bieten eine effiziente Möglichkeit zum Zusammenfassen großer Datenmengen.PivotTables provide an efficient way to summarize large amounts of data.

    • Gesamtergebnisse als Endergebnis.Totals as final results. Wenn Sie Summen und Teilergebnisse als Endergebnisse Ihrer Arbeitsmappe erstellen müssen, erwägen Sie das Arbeiten mit PivotTables.If you need to produce totals and subtotals as part of the final results of your workbook, try using PivotTables.

    • Summen als Zwischenergebnisse.Totals as intermediate results. PivotTables eignen sich sehr gut zum Erstellen von Zusammenfassungsberichten. Versuchen Sie jedoch das Erstellen von Formeln zu vermeiden, die PivotTable-Ergebnisse als Zwischen- oder Teilergebnisse in Ihrer Berechnungskette verwenden, es sei denn, Sie gewährleisten Folgendes: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:

    • Die PivotTable wurde während der Berechnung ordnungsgemäß aktualisiert.The PivotTable has been refreshed correctly during the calculation.

    • Die PivotTable wurde nicht geändert, sodass die Informationen weiter angezeigt werden.The PivotTable has not been changed so that the information is still visible.

    Wenn Sie PivotTables dennoch als Zwischenergebnisse nutzen möchten, verwenden Sie die GETPIVOTDATA-Funktion.If you still want to use PivotTables as intermediate results, use the GETPIVOTDATA function.

  • Bedingte Formate und DatenüberprüfungConditional formats and data validation

    Bedingte Formate und Datengültigkeitsprüfungen sind sinnvoll, können jedoch bei verstärkter Nutzung Berechnungen beträchtlich verlangsamen.Conditional formats and data validation are great, but using a lot of them can significantly slow down calculation. Wenn die Zelle angezeigt wird, wird jede Formel für bedingte Formatierung bei jeder Berechnung ausgewertet, und wenn die Anzeige der Zelle, die das bedingte Format enthält, aktualisiert wird.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. Das Excel-Objektmodell bietet die Worksheet.EnableFormatConditionsCalculation-Eigenschaft, sodass Sie die Berechnung bedingter Formate aktivieren und deaktivieren können.The Excel object model has a Worksheet.EnableFormatConditionsCalculation property so that you can enable or disable the calculation of conditional formats.

  • Definierte NamenDefined names

    Definierte Namen sind eine der leistungsstärksten Features in Excel, die jedoch zusätzliche Berechnungszeit in Anspruch nehmen.Defined names are one of the most powerful features in Excel, but they do take additional calculation time. Durch die Verwendung von Namen, die sich auf andere Arbeitsblätter bezieht, wird der Berechnungsprozess um ein weiteres Maß an Komplexität erhöht.Using names that refer to other worksheets adds an additional level of complexity to the calculation process. Außerdem sollten Sie versuchen, geschachtelte Namen (Namen, die sich auf andere Namen berufen) zu vermeiden.Also, you should try to avoid nested names (names that refer to other names).

    Da Namen immer dann berechnet werden, wenn eine Formel, die auf sie verweist, berechnet wird, sollten Sie berechnungsintensive Formeln und Funktionen nicht in definierten Namen ablegen.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. Die Berechnung wird wesentlich beschleunigt, indem Sie die berechnungsintensive Formel oder Funktion in einer gesonderten Zelle ablegen und auf diese Zelle verweisen, entweder direkt oder mithilfe eines Namens.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.

  • Formeln, die nur gelegentlich verwendet werdenFormulas that are used only occasionally

    Viele Arbeitsmappen enthalten oft sehr viele Formeln und Nachschlagevorgänge mit dem Zweck, die Eingabedaten für Berechnungen in das entsprechende Format umzuwandeln oder werden als Schutzmaßnahmen gegen Änderungen von Größe oder Form der Daten eingesetzt.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. Wenn Sie über Formelblöcke verfügen, die nur gelegentlich verwendet werden, können Sie Spezialwerte kopieren und einfügen, um die Formeln vorübergehend zu deaktivieren, oder Sie können sie in einer gesonderten, nur selten geöffneten Arbeitsmappe ablegen.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. Da Arbeitsblattfehler oft dadurch verursacht werden, dass nicht bemerkt wird, dass Formeln in Werte umgewandelt wurden, kann das Arbeiten mit einer gesonderten Arbeitsmappe vorteilhafter sein.Because worksheet errors are often caused by not noticing that formulas have been converted to values, the separate workbook method may be preferable.

  • Genügend Arbeitsspeicher verwendenUse enough memory

    Die 32-Bit-Version von Excel kann bis zu 2 GB RAM oder bis zu 4 GB RAM für große Adress basierte 32-Bit-Versionen von Excel 2013 und 2016 verwenden.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. Doch auch der Computer, auf dem Excel ausgeführt wird, benötigt Arbeitsspeicherressourcen.However, the computer that is running Excel also requires memory resources. Wenn Ihr Computer nur 2 GB Arbeitsspeicher hat, kann Excel nicht die gesamten 2 GB nutzen, da ein Teil des Arbeitsspeichers dem Betriebssystem und anderen ausgeführten Programmen zugeordnet wird.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. Um die Leistung von Excel auf einem 32-Bit-Computer zu optimieren, wird empfohlen, dass der Computer mindestens 3 GB RAM hat.To optimize the performance of Excel on a 32-bit computer, we recommend that the computer have at least 3 GB of RAM.

    Die 64-Bit-Version von Excel hat keinen Grenzwert von 2 GB oder bis zu 4 GB.The 64-bit version of Excel does not have a 2 GB or up to 4 GB limit. Weitere Informationen finden Sie im Abschnitt "große Datensätze und die 64-Bit-Version von Excel" unter Excel Performance: Performance and Limit Improvements.For more information, see the "Large data sets and the 64-bit version of Excel" section in Excel performance: Performance and limit improvements.

SchlussbemerkungConclusion

In diesem Artikel wurden Möglichkeiten beschrieben, wie Sie Excel-Funktionen wie Links, Nachschlagevorgänge, Formeln, Funktionen und VBA-Code optimieren können, um häufige Hindernisse zu vermeiden und die Leistung zu verbessern.This article covered ways to optimize Excel functionality such as links, lookups, formulas, functions, and VBA code to avoid common obstructions and improve performance.

Weitere ArtikelSee also

Support und FeedbackSupport and feedback

Haben Sie Fragen oder Feedback zu Office VBA oder zu dieser Dokumentation?Have questions or feedback about Office VBA or this documentation? Unter Office VBA-Support und Feedback finden Sie Hilfestellung zu den Möglichkeiten, wie Sie Support erhalten und Feedback abgeben können.Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.