[xllimit4]
Maximale Länge von Formeln
Die maximale Länge einer Formel beträgt in allen
gängigen Excelversionen exakt 1'024 Zeichen. Das betritt Excel 97 bis 2003 für Windows
und Excel 98 bis 2004 für Macintosh. Bei Eingabe einer längeren Formel in eine Zelle
erscheint die Fehlermeldung "Formel ist zu lang":
Abbildung: Fehlermeldung "Formel ist zu lang"
Die in der Dokumentation von Microsoft Excel angegebene
maximale Formellänge von 1'024 Zeichen ist zwar korrekt, meiner Meinung aber zu ungenau.
Es stimmt grundsätlich, dass eine Formel bis zu 1'024 Zeichen lang sein darf - jedoch
nur, wenn sie in eine Zelle eingegeben wird bzw. in einer Zelle steht. Es existieren
nämlich mehrere Ausnahmen, unter anderem:
» Formeln von bedingten
Formatierungen
» Formeln von
Gültigkeitsprüfungen
» Formeln von
Namen
» Matrixformeln
» Formeln in Pocket Excel 1.0
Diese und weitere Ausnahmen werden weiter unten auf dieser
Seite vorgestellt.
Microsoft Excel Version 12
Bei Microsoft Excel Version 12 wurde die Grenze von 1'024 auf 8'192 Zeichen
erhöht. Eine Formel kann daher 8 mal länger sein als in den Vorgängerversionen.
Limitation bei Formeln
von bedingten Formatierungen
Unter anderen darf die in einer bedingten Formatierung verwendete Formel
maximal 255 Zeichen lang sein. Die folgende Abbildung zeigt das Formelfeld des
Dialogfensters "Bedingte Formatierung":
Abbildung: Dialogfenster "Bedingte Formatierung" mit
Bedingungsformel
Limitation bei Formeln von
Gültigkeitsprüfungen
Auf die Formeln von Gültigkeitsprüfungen können nur maximal 255 Zeichen
lang sein. Hier das Dialogfenster "Gültigkeit" mit der Formel eines
benutzerdefinierten Gültigkeitskriteriums:
Abbildung: Dialogfenster "Gültigkeit" mit Formel
Limitation
bei Formeln von Namen
Auch die Formel, die man einem Namen zuweist, kann höchstens 255 Zeichen
lang sein.
Abbildung: Dialogfenster "Namen festlegen" mit Formel
Probleme bei langen Formeln mit externen
Zellbezügen
Bitte beachten Sie auch, dass eine sehr lange Formel, die einen externen Zellbezug
enthält, unter Umständen zu Problemen führen kann.
Beispiel
Dieses Beispiel verdeutlicht, dass sich die Formellänge
verändert, wenn die Formel einen oder mehrere externe Bezüge zu Zellen einer anderen
Arbeitsmappe (die sogenannte Quellmappe) enthält.
Schritt 1:
Solange die Quellmappe "Gültigkeitsregel-Limitation.xls" in
der gleichen Excel-Instanz geöffnet ist, enthält die Verknüpfungsinformation in der
Zellformel nur den Dateinamen, jedoch nicht den Dateipfad der Mappe:
='[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$1+
'[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$2+
'[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$3+
'[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$4+
'[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$5+
'[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$6+
'[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$7+
'[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$8+
'[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$9+
'[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$10+
'[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$11+
'[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$12+
'[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$13
Die obige Formel ist exakt 641 Zeichen lang. Die Limitation
von 1'024 Zeichen ist somit noch lange nicht erreicht.
Schritt 2:
Wenn die Quellmappe geschlossen wird, wird
automatisch der Dateipfad der Verknüpfungsinformation hinzugefügt:
='D:\NeueDaten\XLimits\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$1+
'D:\NeueDaten\XLimits\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$2+
'D:\NeueDaten\XLimits\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$3+
'D:\NeueDaten\XLimits\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$4+
'D:\NeueDaten\XLimits\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$5+
'D:\NeueDaten\XLimits\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$6+
'D:\NeueDaten\XLimits\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$7+
'D:\NeueDaten\XLimits\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$8+
'D:\NeueDaten\XLimits\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$9+
'D:\NeueDaten\XLimits\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$10+
'D:\NeueDaten\XLimits\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$11+
'D:\NeueDaten\XLimits\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$12+
'D:\NeueDaten\XLimits\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$13
Die Formel wird dadurch unvermeidbar länger. Die obige
Formel ist nun 914 Zeichen lang, d.h. 273 Zeichen länger als vorher, da der Dateipfad
"D:\NeueDaten\XLimits" automatisch durch Excel hinzugefügt wurde. Beachten Sie
bitte, dass das abschliessende Backslash-Zeichen (\) ebenfalls zum vollständigen Pfad
gehört. Bei 914 Zeichen ist die Limitation von 1'024 Zeichen auch hier noch nicht
erreicht.
Schritt 3:
Wenn man nun die Quellmappendatei
"Gültigkeitsregel-Limitation.xls" verschiebt, beispielsweise in ein
Unterverzeichnis des ursprünglichen Verzeichnisses, wird dadurch der Dateipfad
automatisch länger. Die nächste Formel zeigt dies (die Quellmappe wird in das
Unterverzeichnis "Testdaten" verschoben):
='D:\NeueDaten\XLimits\Testdaten\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$1+
'D:\NeueDaten\XLimits\Testdaten\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$2+
'D:\NeueDaten\XLimits\Testdaten\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$3+
'D:\NeueDaten\XLimits\Testdaten\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$4+
'D:\NeueDaten\XLimits\Testdaten\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$5+
'D:\NeueDaten\XLimits\Testdaten\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$6+
'D:\NeueDaten\XLimits\Testdaten\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$7+
'D:\NeueDaten\XLimits\Testdaten\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$8+
'D:\NeueDaten\XLimits\Testdaten\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$9+
'D:\NeueDaten\XLimits\Testdaten\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$10+
'D:\NeueDaten\XLimits\Testdaten\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$11+
'D:\NeueDaten\XLimits\Testdaten\[Gültigkeitsregel-Limitation.xls]Tabelle1'!$A$12
'D:\NeueDaten\XLimits\Testdaten\[Gültigkeitsregel-Limitation
Wegen dem neuen Dateipfad
"D:\NeueDaten\XLimits\Testdaten" ist die obige Formel exakt 1'044 Zeichen lang.
Da jedoch die maximale Länge einer Formel 1'024 Zeichen beträgt, werden die
überzähligen Zeichen abgeschnitten. In diesem Fall muss man die Formel von Hand kürzen,
oder dann die Quellmappe in ein anderes Verzeichnis verschieben, damit der Pfad kürzer
wird. Bei Microsoft Excel 2002 gibt es hier allerdings ein Problem. Es kann passieren,
dass während dem Editieren der Zellformel Excel einfach hängen bleibt und nicht mehr
reagiert. Dieses Problem lässt sich durch das Office XP Service Pack 3 beheben.
Weitere Informationen über dieses Problem in Microsoft
Excel 2002 erhalten Sie hier:
XL2002: Excel May
Stop Responding When You Try to Edit a Long Formula That Contains a Link
http://support.microsoft.com/?scid=kb;en-us;331505
Lösungsmöglichkeiten
Es gibt mehrere Möglichkeiten, wie man das Problem mit der zu
langen Formel lösen kann.
Restliche Beschreibung folgt in Kürze...
Anmerkung
Man kann das automatische Hinzufügen des Dateipfades nicht
verhindern. Der Pfad wird auch dann hinzugefügt, wenn sich die Quellmappe im gleichen
Ordner wie die Mappe mit der enthaltenen Formel (die Zielmappe) befindet. Solange die
Quellmappe also geschlossen ist, wird immer der gesamte Dateipfad im Bezug verwendet.
Anders sieht es in der Exceldatei aus. Beim Speichern der Mappe mit der Formel wird in den
seltensten Fällen der gesamte Pfad in die Datei eingetragen. Wenn sich die Quelldatei im
gleichen Verzeichnis befindet, wird beispielsweise nur der Dateiname abgelegt. Beim
Öffnen der Zielmappe liest Excel die Verknüpfungsinformation ein und sieht, dass nur der
Dateiname der Quellmappe vorhanden ist. Folglich geht Excel davon aus, dass sich die Datei
im gleichen Ordner befinden muss. Je nach Ablageort der Quellmappe werden nur ganz
bestimmte Pfadteile in der Zielmappe gespeichert. Weitere Informationen dazu erhalten Sie
in dem ersten unter Lese-Tipps angegebenen Microsoft Knowledge Base-Artikel (siehe
nächster Abschnitt).
 Lese-Tipps:
Einsatz, Verwaltung und Speicherung von Verknüpfungen in Microsoft Excel
In der Microsoft Knowledge Base gibt es einen äusserst wertvollen Artikel zum
Thema "Dateipfade in Verknüpfungen". Wenn Sie dieses Thema interessiert,
sollten Sie den Artikel unbedingt lesen:
XL: Description of Link Management and Storage
in Excel
http://support.microsoft.com/?scid=kb;en-us;328440
Ein anderer, ebenfalls sehr guter Artikel zum Thema
'Globale und lokale Bezüge' ist dieser:
HOW TO: Use Global and Local References in
Formulas in Excel 2000
http://support.microsoft.com/?scid=kb;en-us;274504
Probleme beim Ersetzen von Zeichen einer Zelle mit
VBA
In Microsoft Excel 2000 und 2002 gibt es einen Bug, der zu einem Problem beim
Ersetzen von Zeichen in einer Zelle führt. In Microsoft Excel 97 ist dieser Bug nicht
vorhanden. Bei Zeichenketten, die länger als 911 Zeichen sind, erscheint in Microsoft
Excel 2000 die Fehlermeldung "Formel ist zu lang". In Microsoft Excel 2002 tritt
keine Fehlermeldung auf, die Zeichenkette wird aber auch nicht ersetzt.
Weitere Informationen zu diesem Problem sowie eine
Lösungsmöglichkeit finden Sie hier:
XL2000/XL2002: Ersetzen per VBA funktioniert nur
bis 911 Zeichen pro Zelle
http://support.microsoft.com/?scid=kb;de;509846
Probleme beim Ändern des Verknüpfungspfades zu
einem Add-In
Beschreibung folgt in Kürze...
Weitere Informationen zu diesem Problem finden Sie in
diesem Artikel:
XL: You cannot change a link path when an Excel
Add-In is not loaded
http://support.microsoft.com/?scid=kb;en-us;817578
Problem bei
Zuweisen und Abfragen einer Matrixformel mit VBA
Wegen eines Fehlers im Objektmodell von Excel 97, Excel 98 (Mac) und Excel
2000 muss bei der Benutzung von Matrixformeln mit VBA eine weitere Limitation
berücksichtigt werden. Bitte beachten Sie, dass dieser Bug nicht mit einem Service Pack
behoben werden kann (Stand vom 22.07.2004).
Der Bug betrifft die FormulaArray-Eigenschaft des Range-Objektes.
Matrixformeln können in Microsoft Excel genau wie gewöhnliche Formeln bis zu 1'024
Zeichen lang sein, nicht aber, wenn die Matrixformel einer Zelle anhand der FormulaArray-Eigenschaft
zugewiesen wird, weil wegen des Bugs eine Matrixformel nur maximal 255 Zeichen lang sein
darf.
Beispiel
Hier ein kleines Beispiel dazu:
Die aktive Zelle enthält die Matrixformel
=SUMME(WENN(B1:D20<>0;1))
Die Zellformel kann beispielsweise im Direktfenster des
VBA-Editors abgefragt werden und liefert als Ergebnis die englische
Formel:
=SUM(IF(B1:D20<>0,1))
Erstellt man eine mehr als 255 Zeichen lange Matrixformel
und weist sie einer Zelle beispielsweise mit dem VBA-Code
ActiveCell.FormulaArray =
"=<Formel mit mehr als 255 Zeichen>"
zu, so erhält man den Laufzeitfehler 1004 mit dem
Meldungstext "Die FormulaArray-Eigenschaft des Range-Objektes kann nicht festgelegt
werden" (in der englischen Excelversion "Unable to set the FormulaArray property
of the Range class"):
Abbildung: Laufzeitfehler 1004 wegen zu langer Matrixformel
In diesem Zusammenhang müssen Sie unbedingt auch
beachten, dass die FormulaArray einen falschen Inhalt zurückgibt, falls die abgefragte
Zelle eine Matrixformel von mehr als 255 Zeichen Länge enthält. Wenn Sie die
Matrixformel abfragen, beispielsweise mit
?ActiveCell.FormulaArray
im Direktfenster des VBA-Editors, so wird eine ungültige
bzw. "zerstörte" Formel angezeigt. Zum Beispiel erhalten Sie anstatt der Formel
=SUM(NameZelleB1:NameZelleB4,NameZelleC1:NameZelleC4,B1:B4,C100:C400)
eine defekte Formel dieser Art:
=NameZelleB1:NameZelleB4
NameZelleC1:NameZelleC4 B1:B4 C100:C400
Weitere Informationen zu diesem Problem erhalten Sie hier:
XL97: Run-Time Error Using FormulaArray Property
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q192989
XL98: Run-Time Error Using FormulaArray Property
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q192990
XL2000: Run-Time Error Using FormulaArray Property
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q213181
Limitation
bei Formel von exakt 256 Zeichen Länge (Pocket Excel 1.0)
In Pocket Excel 1.0 dürfen Formeln maximal 255 Zeichen lang sein. Wenn Sie eine
längere Formel eingeben, werden Sie vom Programm darauf hingewiesen, dass die Formel zu
lang ist beziehungsweise dass die maximale Anzahl Zeichen in einer Zelle überschritten
wurde. Die im Vergleich zu Microsoft Excel 97-2002 reduzierte Formellänge bei der
Pocket-Version ist nicht gerade ungewöhnlich; allerdings besitzt Pocket Excel bei der
Erreichung der 256 Zeichen-Grenze einen unscheinbaren Bug: Wenn eine Formel exakt 256
Zeichen lang ist, erhalten Sie die Fehlermeldung "Fehler in Formel. [...]". In
der Meldung steht zudem, dass möglicherweise zu viele oder zu wenige Argumente angegeben
wurden, eine Klammer fehlt oder ein anderer Syntaxfehler vorliegt. Diese Information ist
nicht ganz korrekt, da die Formel absolut fehlerfrei sein kann - nur ist sie genau ein
Zeichen zu lang.
Weitere Informationen erhalten Sie hier:
Pocket Excel 1.0: Error Message: "Error in
Formula"
http://support.microsoft.com/?kbid=166783
|
Home
Verwandte Limitation: