(Dynamische) Formeln über mehrere Tabellenblätter

Bei dynamischen Dateien ist es wichtig, dass Formeln möglichst auf Veränderungen reagieren und diese ohne weiteren Aufwand durch den Benutzer stets die korrekten Daten anzeigen. Ein Unternehmen weist betriebsintern beispielsweise Zahlen für die jeweiligen Regionen und Unternehmenseinheiten aus. Unten ist ein Beispiel dargestellt, bei dem unterschiedliche Regionen ("Basel", "Bern" und "Zürich") vorhanden sind. Zudem werden die Daten auf Konzernebene aggregiert (Tabellenblatt "Total").

SheetIndexSheetsA

Die einzelnen Regionen weisen jeweils die gleiche Struktur auf, wobei auf dem Total-Tabellenblatt einfach auf die einzelnen Zellen Bezug genommen werden kann. Die Formel "=SUMME(Basel:Zürich!C8)" summiert die Zelle "C8" von sämtlichen Tabellenblättern (von "Basel" bis "Zürich"). Sollte nun eine weitere Region (beispielsweise "Genf") hinzugefügt werden, wird der Wert in Zelle "C8" zusätzlich berücksichtigt - sofern das Blatt "Genf" zwischen Basel und Zürich eingeordnet wird. Falls jedoch "Aargau" dazukommen sollte und an der alphabetischen Reihenfolge festgehalten wird, müsste die SUMME-Formel entsprechend angepasst werden. Eine weitere Möglichkeit ist jedoch die nachfolgende: Um die SUMME-Formel keineswegs anpassen zu müssen, fügen wir links und rechts der Regionen weitere leere Tabellenblätter ein ("Kanton_AA" und "Kanton_ZZ") und blenden diese aus. Unsere SUMME-Formel sollte sich nun über diese Hilfsblätter erstrecken:

=SUMME(Kanton_AA:Kanton_ZZ!C8)

SheetIndexSheetsZ

Da die Hilfsblätter leer sind, wird zwar "C8" ebenfalls aggregiert, aber dies entspricht jeweils dem Wert 0 und hat deshalb keinen Einfluss auf das Gesamttotal. Mit dieser Herangehensweise werden die unterschiedlichen Szenarien bereits von Anfang an aufgefangen und die Daten in "Total" basieren jederzeit auf allen Regionen.

Fehler ignorieren: Formel "AGGREGAT"

Viele Auswertungen laufen über grosse Datenmengen. Diese stammen nicht nur direkt aus einer Datenquelle, sondern werden teilweise um weitere Formeln und Informationen angereichert. Aufgrund eingebetteter Formeln kann es zu Fehlermeldungen kommen. Als Beispiel: Mittels der Formel "SVERWEIS" wird die Tabelle um weitere Daten aus einer zweiten Liste ergänzt. Falls zum Suchkriterium kein entsprechender Wert gefunden werden kann, wird der Fehlerwert "#NV" ausgegeben. Die nachfolgende Abbildung stellt dies beispielhaft dar:

AGGREGAT_Fehler1

Zum Kunde "30004" (Zeile 5) konnten keine Umsatz- und Absatzwerte zugewiesen werden. Möchte man diese Spalten mit einer herkömmlichen "SUMME"-Formel auswerten, wird das Resultat ebenfalls "#NV" sein. Eine Möglichkeit, um dies zu umgehen, wäre eine "WENNFEHLER"-Formel einzubauen, wie dies im Beitrag (Suchmaschine programmieren) erläutert wurde. In diesem Beitrag stelle ich jedoch eine direktere Variante einer Formel vor, mit welcher Fehler ignoriert werden können. Die Formel lautet "AGGREGAT". Ähnlich wie bei der Formel "TEILERGEBNIS" wird zuerst die "Funktion" verlangt. Kurzum: Der Benutzer wählt, ob die Formel die "Summe", den "Mittelwert" oder eine andere statistische Funktion ausführen soll.

AGGREGAT_Funktion

Daraufhin sind die "Optionen" zu wählen. Um Fehlerwerte zu ignorieren, wählst Du die 6. Funktion "6 - Fehlerwerte ignorieren", gefolgt von dem Bereich, den es zu summieren gilt.

AGGREGAT_Optionen

Diese Formel funktioniert auf sämtlichen Fehlermeldungen, die es in Microsoft Excel gibt.

Finde die nte Position eines Zeichens innerhalb einer Zelle

Angelehnt an den Beitrag "Zählen eines bestimmten Zeichens innerhalb einer Zelle" wird hier eine Formel vorgestellt, wie die Position des nten Zeichens innerhalb einer Zelle ermittelt werden kann. So können ab einer bestimmten Position "platzsparend" bestimmte Informationen extrahiert werden, wie dies ansonsten beispielsweise mittels der Funktion "Text in Spalten" erledigt wird. Für die effektive Extraktion von Inhalten aus der jeweiligen Zeile kommt unter anderem die Formel "TEIL" zum Einsatz, die in dem Beitrag "Textfragmente aus einer Zelle ziehen" erläutert ist.

Mit "nte" Position wird eine beliebige Position innerhalb einer Zelle verstanden, wenn ein Zeichen vor allem mehrmals vorkommt. Als Beispiel habe ich hier einen Datensatz: "2018_08_21_RT_A_BA_1". Uns interessiert daraus beispielsweise der vierte Wert ("RT") - welcher das Kürzel des zuständigen Mitarbeiter darstellt. Demzufolge ist zu ermitteln, an welcher Position der dritte Underscore ("_") innerhalb der Zelle steht. Man könnte selbstverständlich die Zeichen selbst abzählen und eine TEIL-Formel anwenden, sofern die Datensätze konstant sind. Erfasst jedoch jemand beispielsweise das Datum nicht mit vorgängiger 0 (also lediglich eine "8" für den Monat August) und wird dies so vom System zugelassen, trifft die hinterlegte Regel nicht mehr zu. Hier ist eine Übersicht von diesen zwei Fällen. Durch die verwendete Formel kann die Position (10 oder 11) des dritten "_" stets ermittelt werden:

ListeUebersicht

Im bereits erwähnten und verlinkten Beitrag "Zählen eines bestimmten Zeichens innerhalb einer Zelle" ist die Formel "WECHSELN" bereits erklärt. Wir verwenden diese auch in diesem Anwendungsfall. Hier kommt zudem die Formel "FINDEN" zum Einsatz, bei welcher nach einem bestimmten Zeichen oder einer Zeichenfolge gesucht wird. Ich habe zum Ersetzen das ANSI-Zeichen "160" (Leerschritt ohne Zeilenumbruch) verwendet, doch können da auch beliebige Sonderzeichen verwendet werden, die sicher nicht in Eurem Datensatz vorkommen. Die vollumfängliche Formel in Spalte "D" lautet:

=FINDEN(ZEICHEN(160);WECHSELN(A2;C2;ZEICHEN(160);LÄNGE(A2)-LÄNGE(WECHSELN(A2;C2;""))-3))

Die gesamte Formel "WECHSELN(...)" zählt, wie häufig das entsprechende Zeichen vorkommt. Wichtig für die effektive Positionsermittlung ist die "-3". Dadurch wird gesteuert, welches "n" im Fokus steht. Hier könnte selbstverständlich ebenfalls eine dynamische Formel hinterlegt werden. Insgesamt enthält der Datensatz 6 Mal das Zeichen "_". Mit "-3" wird demnach die Position von n = 3 ermittelt. Mit dem ausgegebenen Wert könnte nun wie oben angedeutet die Formel "TEIL" angewandt werden, um die zwei Zeichen danach zu extrahieren. Abschliessend ist hier diese Formel ebenfalls angezeigt:

=TEIL(A2;FINDEN(ZEICHEN(160);WECHSELN(A2;C2;ZEICHEN(160);LÄNGE(A2)-LÄNGE(WECHSELN(A2;C2;""))-3))+1;2)

ListeKuerzel

Die vorgestellte Formel kann auch in einem anderem Kontext verwendet werden: So können Texte und Häufigkeiten sowie Positionen und Satzstellungen analysiert werden, um nur einige Anwendungsfälle zu nennen.