Warum Excel-Tabellen benannten Bereichen überlegen sind, wenn Formeln lesbar bleiben sollen
Lesbare Excel-Formeln sind ausgesprochen praktisch — egal, ob jemand anderes oder Sie selbst sich später in eine Datei einarbeiten. In eine alte oder fremde Arbeitsmappe hineinzufinden, ist oft mühsam: Dann werden selbst einfache Rechnungen schwer verständlich. Umgekehrt gibt es komplexe Arbeitsmappen, die sich mühelos lesen lassen. Es kommt, wie immer, darauf an.
Der klassische Weg, eine Excel-Datei unverständlich zu machen: zahllose Bezüge über Blattgrenzen hinweg auf einzelne Zellen. Das passiert häufig, auch geübten Anwendern. Sie trennen etwa Rechenblätter von einem Parameterblatt. Der Gedanke ist richtig, die Umsetzung wird trotzdem schnell unübersichtlich.
Klassisch: Parameter direkt verknüpfen
Nehmen wir ein ordentlich angelegtes Blatt „Geschäftsparameter“. Es enthält viele Angaben, die überall in der Arbeitsmappe gebraucht werden.
Ein Blatt „Budgetrechnung“ braucht daraus einzelne Parameter, etwa „Margenziel (%)“. In den Formeln unten stehen alle Parameter als direkter Bezug, zum Beispiel 'Geschäftsparameter'!$C$5. Das ist korrekt — aber wer merkt sich all diese Adressen? In komplexen Modellen sind es Dutzende oder Hunderte. Solche Formeln sind schwer zu lesen und noch schwerer zu prüfen.
Benannte Bereiche
Benannte Bereiche verbessern die Lesbarkeit deutlich. Sie geben einzelnen Zellen, ganzen Bereichen und sogar formelbasierten dynamischen Bereichen einen Namen. Im Parameterblatt bekommt also jeder Parameter seinen eigenen Namen:
Die Formel wird dann geradlinig: Ersetzen Sie die absolute Adresse 'Geschäftsparameter'!$C$5 durch den Namen MargenzielZweitausendneunzehn:
Das ist weiterhin korrekt und viel besser lesbar. Bei sehr vielen Parametern kostet es allerdings erhebliche Zeit, all diese Namen anzulegen und zu pflegen.
Excel-Tabellen, erster Ansatz
Ein besserer Weg darf die Lesbarkeit nicht opfern. Er soll lesbar, pflegeleicht und einfach anzuwenden sein. Excel-Tabellen bieten das alles — mit einem kleinen Kniff. Zunächst zur Begriffsklärung: Eine Excel-Tabelle ist kein Arbeitsblatt. Sie ist eine besondere Art von Bereich mit vielen nützlichen Eigenschaften. Statt Daten und Formeln irgendwo auf dem Blatt abzulegen, kommen sie in eine Excel-Tabelle. Ihre Stärke zeigt sich beim Bezug auf Spalten: Statt absoluter Bereiche stehen Tabellenname und Spaltenname in der Formel, etwa =Produktliste[Umsatz 2013]. Dieser Bezug erfasst alle Zellen der Spalte — ob 100 oder 100.000. Oder eben genau eine.
Eine Excel-Tabelle mit genau einer Zeile verhält sich im Kern wie eine Sammlung einzelner benannter Bereiche. Für Geschäftsparameter ist das ideal.
Zuerst richten Sie die Parameter zeilenweise aus:
Der Screenshot enthält bereits Excel-Tabellen, angelegt auf diesem Weg:
Im Blatt „Budgetrechnung“ dienen die Tabellenbezüge dann als Parameter in der Formel:
Excel-Tabellen, zweiter Ansatz
Der erste Ansatz hilft, verändert aber das Parameterblatt: Die Angaben stehen jetzt in Zeilen statt in Spalten. Manchmal ist das unerwünscht. Der zweite Ansatz behält die ursprüngliche Anordnung bei. Auf die Budgetrechnung wirkt sich das nicht aus, nur auf das Parameterblatt. Der folgende Screenshot zeigt den ursprünglichen Parameterbereich und die Excel-Tabellen nebeneinander.
Niemand soll jeden Parameter zweimal eintragen. Damit die ursprüngliche Anordnung erhalten bleibt und die Tabellenfunktionen trotzdem zur Verfügung stehen, werden die Parameter nachgeschlagen. Mit INDEX und VERGLEICH kommen Sie ohne zellgenaue Bezüge aus. SVERWEIS funktioniert zwar auch, ist aber nichts, was ich empfehlen würde.
Hinweis: Der Bequemlichkeit halber und für eine lesbarere INDEX-VERGLEICH-Formel wurden auch die Parameterbereiche in Excel-Tabellen umgewandelt.
Ein weiterer Vorzug ist die automatische Vervollständigung: Beim Schreiben von Formeln schlägt Excel Tabellen- und Spaltennamen vor. Auch darin stehen Excel-Tabellen benannten Bereichen in nichts nach.
Fazit
Komplexe Excel-Modelle zu bauen, ist nicht schwer. Sie lesbar und pflegbar zu halten, schon. Der hier gezeigte Weg vermeidet absolute Bezüge auf Parameterblätter. Die Empfehlung: Verwenden Sie eine Excel-Tabelle. Sie bietet benannte Bezüge wie benannte Bereiche, kostet aber weniger Pflege. Der Kniff besteht darin, die Tabelle auf eine einzige Zeile zu beschränken — dann ist der Bezug auf die ganze Spalte in Wahrheit der Bezug auf eine einzelne Zelle.