GASTBEITRAG: Carmen Canga

Excel erlaubt uns, Formeln zu erstellen, die mehrere Funktionen enthalten. Die Verwendung von Funktionen in anderen Funktionen ist als Verschachtelung bekannt.

Als jemand, der in der Finanzabteilung arbeitet, ist die Zusammenstellung der Daten für die Monats-, Halbjahres- und Jahresabschlüsse für die Präsentation der Geschäftsberichte, eine übliche Übung. Wenn Ihr wisst, wie man verschieden Funktionen zusammen nutzt, vereinfacht das die Arbeit enorm!

Am besten lässt sich das Konzept der Verschachtelung anhand von Beispielen verstehen…

Beispiel 1: Prozentualer Vergleich mit der Vorperiod Berechnen

Wenn ich die aktuellen Umsatzzahlen mit denen des Vorjahres vergleichen möchte, kann ich folgende Formel verwenden: (Actual-PY)/PY und auf “%” klicken, aber ich werde es etwas komplexeres machen und die “WENN”-Formel verwenden, da ich sagen möchte:

“WENN der Betrag des Vorjahres Null ist, lass die Zell leer, und wenn nicht, berechne die Formeln: (Actual-PY)/PY. In unsere Beispeil unten (D5-E5)/E5.”

=WENN (E5=0;““;(D5-E5)/E5

Jetzt mache ich ein „drop down“ meine Formeln und mache das gleiche Formeln für das YTD; Auf beiden klicke ich auf “%“ um die Prozentzahl zu sehen.

Damit Ihr sieht was passiert, wenn es keine Zahlen in PY gibt, lösche ich die PY Zahl von Division B, und so bekomme ich folgendes Ergebnis:

Die Zell ist leer, genauso wie ich möchte. Das Testen einer Formel ist wichtig, denn wenn man erst am schluss einen Fehler bemerkt, so kostet es einem mehr Aufwand den Fehler zu beheben!

Verschachtelte Formel

Ich merke jetzt, dass die % in YTD für Division A viel zu hoch ist und nicht so übersichtlich, deswegen will ich nur >1000% zeigen.

Dann verwende ich wieder die Formel WENN

=WENN(I5>1000%;“>1000%“;(G5-H5)/H5)

Die Formel in Zelle J5 verweist auf den Wert in Zelle I5, die wiederum eine weitere Formel enthält, die für eine weitere Kondition zuständig ist. Anstatt dass die zweite WENN Formel die Zelle I5 referenziert, können wir diese Referenz also wie folgt, durch die in dieser anderen Zelle enthaltene Formel ersetzen:

=WENN(WENN(H5=0;””;(G5-H5)/H5)>1000%;”>1000%”;(G5-H5)/H5)

Wir bekommen das gleiche Ergebnis mit einer Formel wie früher mit zwei!

Der Vorteil der Funktionsverschachtelung ist, dass wir nicht mehrere Zellen und mehrere Formeln verwenden müssen, um zum gleichen Endergebnis zu kommen.

Beispiel 2: Formatierung einer ID-Nummer

Unser Ziel ist es, einen Bindestrich nach den acht Ziffern der ID-Nummer und die Buchstaben einzufügen und eine ID Nummer zu generieren.

Eine Methode, dies zu tun, besteht darin, dass ich die 8 Ziffer links extrahieren mit den Formeln:

=LINKS(B2, 8)

Ich gebe diese Formel in Zelle D2 ein und kopiere sie nach unten, um die linken acht Ziffern jeder Zahl zu extrahieren:

Da alle ID Nummer in Spalte B die gleiche Anzahl von Ziffern haben, können wir die Funktion RECHTS verwenden, um die Buchstaben am Ende mit der folgenden Formel zu extrahieren:

=RECHTS(B2, 1)

Wenn wir die obige Formel eingeben und abschreiben, erhalten wir folgendes Ergebnis in Spalte E:

Mit den vorherigen Formeln haben wir die beiden Teile, die uns interessieren, für jede ID Nummer erhalten.

Jetzt müssen wir sie nur noch mit dem mittleren Strich verketten, um das gewünschte Format zu erhalten. Um dieses Ziel zu erreichen, werde ich die Funktion VERKETTEN wie folgt verwenden:

=VERKETTEN(D2, “-“, E2).

Mit dieser Formel können wir jeden der extrahierten Teile verketten, indem wir den Bindestrich zwischen sie setzen. Im folgenden Bild sieht Ihr das Ergebnis dieser Formel:

Wir haben unser Ziel erreicht, aber mit Hilfe von drei Spalten die wir eigentlich nicht sehen möchten. Diese extra Spalten können wir durch Funktionsverschachtelung vermeiden!

Wir können die Verweise auf die Zellen D2 und E2 durch die in diesen Zellen angegebenen Formeln ersetzen und haben dann eine Formel wie die folgende:

=VERKETTEN(LINKS(B2;8); “-“;RECHTS(B2;1))

Das erste Argument der Funktion VERKETTEN ist die Funktion LINKS, das zweite Argument ist der mittlere Strich und das dritte Argument ist die Funktion RECHTS. Mit dieser einzigen Formel erreichen wir das gleiche Ergebnis wie mit den drei vorherigen Formeln.

Wenn wir eine Formel mit verschachtelten Funktionen haben, beginnt Excel mit der Berechnung der Funktionen, die eine höhere Verschachtelungsebene haben. In diesem Fall befindet sich die Funktionen LINKS und RECHTS innerhalb der Funktion VERKETTEN und wird die erste Berechnung sein, die Excel durchführt.

Häufige Fehler bei Verschachtelten Funktionen

Es kommt häufig vor, dass wir bei der Eingabe von Formeln innerhalb anderer Formeln mit den Klammern durcheinanderkommen, was zu einem Fehler bei den erforderlichen Argumenten der jeweiligen Formeln führen kann.

Eine Möglichkeit, die Bearbeitung dieser Art von Formeln zu erleichtern, ist die Verwendung des Dialogfelds „Funktionsargumente“, dass wir durch Klicken auf die Schaltfläche Funktion einfügen in der Formelleiste öffnen können; Anhand des ersten Beispiels:

Wenn wir eine der Zellen, die die verschachtelten Funktionen enthalten, markieren und die Schaltfläche Funktion einfügen drücken, erscheint das folgende Dialogfeld.

Auf diese Weise können Sie die einzelnen Argumente der VERKETTEN-Formeln deutlich sehen und die notwendigen Änderungen vornehmen, sowie Fehler bei den Funktionsargumenten vermeiden.

Grenzen von Verschachtelten Funktionen

Zum Schluss muss ich Euch noch sagen, dass in sehr alten Versionen von Excel (2003 und früher) nur 7 Funktionen verschachtelt werden durften, aber ab Excel 2007 können wir bis zu 64 Funktionen in der gleichen Zell verschachteln.

Aber ich würde niemandem anraten bis an diese Grenze zu gehen!

Es ist klar, dass je größer die Anzahl der verschachtelten Funktionen ist, desto größer ist die Chance, einen Fehler bei den Argumenten der beteiligten Funktionen zu machen, aber Ihr müsst solchen Formeln einfach mehr Aufmerksamkeit schenken und sehr vorsichtig sein, beim Bearbeiten.

Die richtige Verwendung die Verschachtelung vereinfacht enorm die Arbeit und macht Eure Excel-Datei und Arbeit Tabellen viel sauberer und professioneller.

Viel Erfolg beim Einsetzen!

Carmen Canga

Die Autorin hat über 10 Jahre Erfahrung in verschiedenen Finanz Rollen, ehemals als Senior Managerin bei einem globalen Pharma Unternehmen, aktuell als Leiterin Finanzabteilung bei einem schweizer KMU. Sie hat ein Bachelor Abschluss in Wirtschaft und ein zweiten Bachelor in Betriebswirtschaft von der Universität Autonoma de Madrid, so wie auch ein Finance und Controlling Zertifikat der Universität St. Gallen. Sie kann via Xing kontaktiert werden.