Ordnung ins Chaos bringen: Strukturierte Verweise 3

Mit Hilfe von strukturierten Verweisen werden Formeln durchschaubarer
 

Hast du auch schon öfter Formeln wie die folgende gesehen und dich gefragt, was hier eigentlich genau berechnet wird:
=SUMMEWENN(Tabelle1!G6:K18;Tabelle2!F9;Tabelle1!H6:H18)

Es wäre doch manchmal schön, wenn solche schwer lesbaren Formeln etwas mehr Klartext beinhalten würden.

Vielleicht kennst du die Menüoption, mit der man einen Bereich so schön als Tabelle formatieren kann und damit z.B. jede zweite Zeile in einer anderen Farbe hinterlegt wird.

Was hat nun das eine mit dem anderen zu tun? Ganz einfach: Diese formatierten Tabellen ermöglichen sogenannte strukturierte Verweise, die wiederum für eine bessere Lesbarkeit der oben genannten Formel sorgen.

Und so geht’s:

Als Tabelle formatieren

Da sie für die strukturierten Verweise die Grundvoraussetzung sind, gibt es diejenigen Leser, welche die Funktionalität der formatierten Tabellen noch nicht kennen, ein kurze Einführung. Nehmen wir einmal an, ich habe ein kleine Tabelle der folgenden Art:

Ausgangstabelle

Ausgangstabelle

Mit zwei Mausklicks lässt diese sich etwas “aufhübschen”. Einfach die aktive Zelle irgendwo innerhalb der Tabelle stellen, dann im Start-Menü die Schaltfläche “Als Tabelle formatieren” anklicken und je nach Geschmack eine der angebotenen Formatvorlagen auswählen:

Formatvorlage auswählen

Formatvorlage auswählen

Und schon sieht meine Tabelle etwas ansprechender aus. Als offensichtliche Veränderung wird neben dem Zebrastreifen-Muster automatisch auch ein Autofilter auf jedem Spaltenkopf gesetzt:

Als Tabelle formatiert

Als Tabelle formatiert

Wenn sich nun die aktive Zelle innerhalb der formatierten Tabelle befindet, erscheint die neue Registerkarte “Entwurf”, die spezielle Optionen anbietet:

Neue Registerkarte "Entwurf"

Neue Registerkarte “Entwurf”

So kann man z.B. den standardmäßig vergebenen Namen (hier: “Tabelle5”) durch einen eigenen, aussagekräftigeren (“Städte”) ersetzen. Sehen wir uns die Optionen kurz an:

Optionen im Entwurfsregister

Optionen im Entwurfsregister

Über die Checkbox “Ergebniszeile” wird automatisch am Ende der Tabelle eine Zeile eingefügt, in der man über eine Dropdown-Liste verschiedene Ergebnisfunktionen auswählen kann, wie Summe, Anzahl oder Mittelwert:

Eingefügte Ergebniszeile

Eingefügte Ergebniszeile

Strukturierte Verweise

Kommen wir nun zu der in der Einleitung beschriebenen Problematik der unleserlichen Formeln. Durch die Funktion “Als Tabelle formatieren” erhält meine Tabelle nicht nur eine optische Veränderung, sondern auch eine funktionale.

Die verschiedenen Tabellenbestandteile können nämlich ab sofort über einen Namen gezielt angesprochen werden. Jede Spalte erhält nämlich automatisch den Namen der jeweiligen Spaltenüberschrift. Die Spalte B ist also über den Namen “Einwohner” ansprechbar.

Die Schreibweise lautet dabei immer “Tabellenname[Spaltenname]”, also z.B.
=SUMME(Städte[Einwohner])
In meinem Beispiel habe ich der gesamten Tabelle den Namen “Städte” gegeben. Bei Berechnungen kann ich nun anstelle des wenig aussagefähigen Zellbezuges B2:B14 den strukturierten Verweis “Städte[Einwohner]” verwenden:

Strukturierte Verweise

Strukturierte Verweise

Zusätzlich gibt es noch ein paar besondere Namenselemente, die man innerhalb seiner Formeln nutzen kann:

Besondere Elemente

Besondere Elemente


Auch die besonderen Element werden einfach in die eckigen Klammern geschrieben. Um z.B. die Anzahl aller Datenelemente (ohne Kopf- und Ergebniszeile) meiner Tabelle zu ermitteln, lautet die Formel
=ANZAHL2(Städte[#Daten])

Auch wenn die Formel dadurch vielleicht etwas länger wird sieht man sofort, worum es eigentlich geht und das Rätselraten hat ein Ende.

Wie du siehst, verstecken sich hinter der harmlosen Tabellenformatierung ein paar interessante Funktionen.

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

3 Gedanken zu “Ordnung ins Chaos bringen: Strukturierte Verweise

  • Sven

    Moin!
    Ich versuche jetzt schon seit einer Stunde einen Verweis auch für die Gültigkeit einer Zelle zu definieren (also als Auswahl einer Auswahlliste).
    Dazu gebe ich unter Daten/Gültigkeit die ein:
    =Meta!Test[Key] (Tabellenblatt Meta, Excel-Tabelle Test, Spalte Key)

    übertragen auf das obige Beispiel wäre das:
    =Städte[Einwohner]

    Geht das nicht, oder mache ich etwas falsch?

    btw: Danke für den Blog!

    • Martin Weiß Beitragsautor

      Hallo Sven,

      die strukturierten Verweise funktionieren tatsächlich nicht innerhalb der Gültigkeitsprüfung. Hier kannst Du nur mit Zelladressen arbeiten oder Du vergibst zusätzlichen für die gewünschte Tabelle einen normalen Namen.
      Das ist leider ein kleines Ärgernis in Excel, das hoffentlich in zukünftigen Versionen geändert wird…

      Grüße,
      Martin