[xllimit8]  Maximale Verschachtelungstiefe in Funktionen

Inhalt
» Die Limitation und ihre Auswirkungen
» Ebenen, Verschachtelungsebenen und Verschachtelungen
» Wie viele Verschachtelungsebenen besitzt eine Formel?
» Umgehen der Limitation
» Weitere Informationen

Die Limitation und ihre Auswirkungen

Die Excelversionen ab Microsoft Excel 97 bzw. ab Microsoft Excel 98 für Macintosh erlauben eine Verschachtelungstiefe in Funktionen von maximal 7 Ebenen.

Wenn die Limitation überschritten wird, erscheint die nachfolgend abgebildete Fehlermeldung "Die eingegebene Formel enthält einen Fehler":

Abbildung: Fehlermeldung "Die eingegebene Formel enthält einen Fehler"

Die obige Fehlermeldung stammt aus Microsoft Excel 97. In den neueren Excelversionen tritt die gleiche Fehlermeldung auf, wobei der Meldungstext geringfügig von demjenigen von Microsoft Excel 97 abweicht. Schade bei dieser Fehlermeldung ist, dass der Text zu allgemein abgefasst ist. Wenn man allerdings auf "Hilfe" klickt, so wie es empfohlen wird, findet man die Problemursache recht schnell.

» Wenn Sie auf "Hilfe" klicken, wird die Online-Hilfe mit dem Thema "Problembehandlung beim Auftreten von Fehlern in Formeln" geöffnet. Der vierte Punkt dieses Hilfethemas besagt folgendes: "Sie können innerhalb einer Funktion maximal sieben Ebenen von Funktionen verwenden (verschachteln). [...]".

Abbildung: Auszug aus der Microsoft Excel 97 Online-Hilfe

Ein anderes Hilfethema enthält weitere nützliche Informationen. Hier ein Auszug:

Thema "Wissenswertes über das Verwenden von verschachtelten Funktionen, d. h. Funktionen innerhalb von Funktionen"

"Eine Formel kann bis zu sieben Ebenen verschachtelter Funktionen enthalten. Wenn Funktion B als Argument in Funktion A verwendet wird, ist Funktion B eine Funktion zweiter Ebene. Wenn zusätzlich Funktion B als Argument die Funktion C enthält, ist Funktion C eine Funktion dritter Ebene.

Sie können die Formelpalette verwenden, um Funktionen als Argumente zu verschachteln. Sie können beispielsweise Funktion B als ein Argument von Funktion A einfügen, indem Sie in der Bearbeitungsleiste auf den Dropdown-Pfeil klicken. Wenn Sie für Funktion A weitere Argumente eingeben möchten, klicken Sie in der Bearbeitungsleiste auf den Namen von Funktion A."

» Wenn Sie die obige Fehlermeldung mit "OK" bestätigen, wird das fehlerverursachende Formelelement markiert. In unserem Fall also die Funktion (bzw. die erste Funktion in der Formel), die sich auf der achten Ebene befindet. Das ist äusserst nützlich, wie Sie im Abschnitt "Wie viele Verschachtelungsebenen besitzt eine Formel?" (Beispiel 3) erfahren.

Ebenen, Verschachtelungsebenen und Verschachtelungen

Häufig werden die drei Begriffe Ebene, Verschachtelungsebene und Verschachtelung durcheinander gebracht und nicht sinngemäss verwendet. Daher hier eine kurze Begriffserklärung mit praktischen Beispielen.

Ebenen

Bei einer Formel, die eine einzelne, einfache Funktion enthält, befindet sich die Funktion auf der Ebene 1.
Beispiel:
  =HEUTE()
HEUTE befindet sich auf der Ebene 1.

Enthält die Formel eine Funktion, welche wiederum eine Funktion enthält, befindet sich die erste Funktion auf der Ebene 1 und die enthaltene Funktion auf der Ebene 2.
Beispiel:
  =WENN(A1=HEUTE();"Heute";"Nicht Heute")
WENN befindet sich auf der Ebene 1 und HEUTE auf der Ebene 2.

Verschachtelungen

Bei einer Formel mit einer einzelnen, einfachen Funktion gibt es keine Verschachtelung.
Beispiel:
  =HEUTE()

Bei einer Formel mit einer Funktion, welche ihrerseits eine einzelne, einfache Funktion enthält, gibt es 1 Verschachtelung.
Beispiel:
  =WENN(A1=HEUTE();"Heute";"Nicht Heute")

Diese Formel besitzt drei Funktionen, die jeweils drei Funktionen enthalten. Es gibt somit drei Verschachtelungen.
Beispiel:
  =WENN(A1=HEUTE();1;0)+WENN(B1=HEUTE();1;0)+WENN(C1=HEUTE();1;0)

Verschachtelungsebenen

Bei einer Formel, die eine einzelne, einfache Funktion enthält, gibt es keine Verschachtelungsebenen. Man kann aber auch sagen, dass sich die Funktion auf der Verschachtelungsebene 0 befindet.
Beispiel:
  =HEUTE()
HEUTE befindet sich auf der Verschachtelungsebene 0.

Enthält die Formel eine Funktion, welche eine andere Funktion enthält, befindet sich die erste Funktion auf der Verschachtelungsebene 0 und die enthaltene Funktion auf der Verschachtelungsebene 1.
Beispiel:
  =WENN(A1=HEUTE();"Heute";"Nicht Heute")
WENN befindet sich auf der Verschachtelungsebene 0 und HEUTE auf der Verschachtelungsebene 1.

Wie viele Verschachtelungsebenen besitzt eine Formel?

Bei langen Formeln ist nicht immer ganz einfach erkennbar, wie viele Verschachtelungsebenen bereits vorhanden sind. Ich selbst wende daher einen kleinen Trick an: Man kann die Anzahl Ebenen ermitteln, indem man bei jeder Funktion, die eine weitere Funktion enthält, die Nummer der Ebene angibt, auf der sich die Funktion befindet.

Tipp: Formel zuerst kopieren
Kopieren Sie die Formel zuerst in eine andere Zelle oder in ein Microsoft Word-Dokument und fügen dort die Ebenennummern hinzu. Dadurch bleibt die original Formel erhalten, und Sie müssen nicht die Nummern wieder entfernen.

Anmerkung
Einige Tabellenfunktionen besitzen bereits eine Zahl am Ende des Funktionsnamens, beispielsweise die Funktion ARCTAN2. Mit der Ebenennummer lautet die Funktion dann beispielsweise ARCTAN23. Lassen Sie sich dadurch nicht verwirren.

Beispiele

Beispiel 1:
Nehmen wir zuerst eine einfache Formel:
=WENN(UND(A2=HEUTE();NICHT(ISTZAHL(A3)));"Gut";"Böse")

Nun wird bei jeder Funktion, die eine weitere Funktion enthält, die Nummer der jeweiligen Ebene hinzugefügt:
=WENN1(UND2(A2=HEUTE();NICHT3(ISTZAHL(A3)));"Gut";"Böse")
Die Formel verwendet somit 3 Ebenen. Die beiden Funktionen HEUTE und ISTZAHL erhalten keine Nummer, weil sie keine Funktion enthalten.

Beispiel 2:
Nun ein etwas schwierigeres Beispiel:
=WENN(UND(A2=HEUTE();NICHT(ISTZAHL(A3)));WENN(ODER(A7=GERADE(A5);A8=UNGERADE(A6));"3";"4");
WENN(A4=GANZZAHL(A4);"1";"2"))

Nach Hinzufügen der Ebenennummern sieht die Formel so aus:
=WENN1(UND2(A2=HEUTE();NICHT3(ISTZAHL(A3)));WENN2(ODER3(A7=GERADE(A5);A8=UNGERADE(A6));"3";"4");
WENN2(A4=GANZZAHL(A4);"1";"2"))
Es werden wie beim ersten Beispiel 3 Ebenen benutzt. Auf den ersten Blick enthält die Formel scheinbar mehr Verschachtelungsebenen, was jedoch nicht der Fall ist.

Beispiel 3:
Dieses Beispiel verwendet eine zu grosse Anzahl Ebenen, nämlich 8 Ebenen:
=WENN(UND(E14>=HEUTE();NICHT(ISTZAHL(F13)));"Stop";WENN(UND(I14>=HEUTE();NICHT(ISTZAHL(J13)));"Stop";
WENN(UND(M14>=HEUTE();NICHT(ISTZAHL(N13)));"Stop";WENN(UND(Q14>=HEUTE();NICHT(ISTZAHL(R13)));"Stop";
WENN(UND(U14>=HEUTE();NICHT(ISTZAHL(V13)));"Stop";WENN(UND(Y14>=HEUTE();NICHT(ISTZAHL(Z13)));"Stop";""))))))

Mit den Ebenennummern sieht die Formel dann so aus:
=WENN1(UND2(E14>=HEUTE();NICHT3(ISTZAHL(F13)));"Stop";WENN2(UND3(I14>=HEUTE();NICHT4(ISTZAHL(J13)));"Stop";
WENN3(UND4(M14>=HEUTE();NICHT5(ISTZAHL(N13)));"Stop";WENN4(UND5(Q14>=HEUTE();NICHT6(ISTZAHL(R13)));"Stop";
WENN5(UND6(U14>=HEUTE();NICHT7(ISTZAHL(V13)));"Stop";WENN6(UND7(Y14>=HEUTE();NICHT8(ISTZAHL(Z13)));"Stop";""))))))

Die letzte NICHT-Funktion enthält eine weitere Funktion, die ISTZAHL-Funktion, und erhält somit eine Ebenennummer, in diesem Fall die Nummer 8. Weil Formeln maximal 7 Ebenen zulässt, erscheint bei dieser Formel die Fehlermeldung "Die eingegebene Formel enthält einen Fehler. [...]".

Bitte beachten Sie, dass nicht die NICHT-Tabellenfunktion das Problem darstellt, sondern die in der NICHT-Funktion enthaltene ISTZAHL-Funktion. Das dem so ist, sieht man auch daran, welche Funktion von Excel markiert wird, wenn man die Fehlermeldung mit OK bestätigt. Ersetzt man die ISTZAHL-Funktion durch etwas anderes als eine Funktion, beispielsweise durch die Zahl 0, so tritt kein Fehler auf, weil die NICHT-Funktion keine Funktion mehr enthält und somit keine achte Ebene verwendet wird.

Die angepasste Formel sieht folgendermassen aus:
=WENN(UND(E14>=HEUTE();NICHT(ISTZAHL(F13)));"Stop";WENN(UND(I14>=HEUTE();NICHT(ISTZAHL(J13)));"Stop";
WENN(UND(M14>=HEUTE();NICHT(ISTZAHL(N13)));"Stop";WENN(UND(Q14>=HEUTE();NICHT(ISTZAHL(R13)));"Stop";
WENN(UND(U14>=HEUTE();NICHT(ISTZAHL(V13)));"Stop";WENN(UND(Y14>=HEUTE();NICHT(0));"Stop";""))))))

Umgehen der Limitation

Es gibt Möglichkeiten, wie man eine Verschachtelung umgehen kann; und trotzdem auf das gleiche Resultat kommt. Oft wird die maximale Verschachtelungstiefe in einer Formel zum Problem, weil mehrere WENN-Funktionen mit UND bzw. ODER ineinander verschachtelt werden. Da die WENN-Funktion immer einen logischen Wert liefert (WAHR oder FALSCH), können Sie anstelle von WENN() in Verbindung mit UND() und ODER() auch mathematische Operatoren, sprich den Multiplikation-Operator '*' bzw. den Addition-Operator '+' einsetzen.

Multiplikation statt UND-Funktion

Anstelle der UND-Funktion kann eine Multiplikation verwendet werden. Diese beiden Formeln führen zum gleichen Ergebnis:

=UND(A1=1;B1=2)
=(A1=1)*(B2=2)<>0

Addition statt ODER-Funktion

Anstelle der ODER-Funktion kann man eine Addition verwenden. Diese beiden Formeln führen ebenfalls zum gleichen Resultat:

=ODER(A1=1;B1=2)
=(A1=1)+(B2=2)<>0

Anmerkung
Sie sollten sich dessen bewusst sein, dass verschachtelte Funktionen im Vergleich zu 'einfachen', d.h. nicht verschachtelten Formeln ziemlich rechenintensiv sind. Weil bei einer Neuberechnung der Arbeitsmappe beziehungsweise des Arbeitsblattes jede Formelzelle - sofern die Formelzelle oder eine in der Formel bezogene Zelle geändert hat - als Ganzes neuberechnet wird, müssen folglich immer die Resultate sämtlicher Teile der in der Zelle stehenden Formel neu berechnet werden. Wäre die Formel auf mehrere Zellen aufgeteilt, müssten weniger Zellen neu berechnet werden, was entsprechend weniger Rechenzeit benötigt.

Beispiel 1:
In der Zelle G1 steht die folgende Formel

  =GANZZAHL(A1)+RUNDEN(B1;2)+KÜRZEN(C1)+GERADE(D1)

Die Formel enthält vier Funktionen (GANZZAHL, RUNDEN, KÜRZEN und GERADE) sowie vier Bezüge auf andere Zellen (A1, B1, C1 und D1). Wenn nun irgend eine der vier Zellen A1, B1, C1 oder D1 ändert, muss die gesamte Formel von Zelle G1 und somit alle vier Funktionen neu berechnet werden.

Beispiel 2:
In der Zelle E1 steht die Formel

  =GANZZAHL(A1)+RUNDEN(B1;2)+KÜRZEN(C1)+GERADE(D1)

und in Zelle F1 die Formel

  =KÜRZEN(C1)+GERADE(D1)

und in Zelle G1 die Formel

  =E1+F1

Weitere Informationen

Hinweis zur Office Web Spreadsheet-Komponente
Das Tabellenblatt der Office Web Spreadsheet-Komponente besitzt keine Limitation bezüglich Verschachtelungstiefe von Funktionen. Mit anderen Worten können Funktionen in Formeln unbegrenzt ineinander verschachtelt werden.

Wenn Sie ein Office Tabellenblatt nach Microsoft Excel exportieren, und eine Zelle enthält eine Formel mit mehr als sieben Verschachtelungsebenen, so wird anstelle der Formel das Resultat der Formel übernommen.

Weitere Informationen über die Office Web Spreadsheet-Limitationen finden Sie hier:

Weitere Informationen Limitationen der Office Web Components

Hinweis zu Microsoft Excel 2007
Die kommende Version Microsoft Excel 2007 besitzt eine andere Spezifikation. In dieser Programmversion können bis zu 64 Verschachtelungsebenen verwendet werden. Das ist über 9 mal mehr als in Microsoft Excel bis Version 2003 (bzw. bis Version 2004 für Macintosh).

Eine Beschreibung aller Excel 12-Limitationen finden Sie hier:

Weitere Informationen Limitationen in Microsoft Excel 12