Ordnung ins Chaos bringen: Strukturierte Verweise 5

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.



Schreibe einen Kommentar

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

5 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ß Autor des Beitrags

      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

  • Sten

    Folgende Problemstellung ergibt sich bei der Verwendung strukturierter Verweise im Excel 2010: (Die beiden Bilddateien würde ich bei Bedarf via Mail zur Verfügung stellen, wenn die Beschreibung des Problems nicht ausreicht…)

    In einem Excel-Tabellenblatt wurden vier Bereiche mit je einer Spalte als Tabellen formatiert, welche die einzelnen Abteilungen bzw.
    Sachbereiche darstellen. In jeder Tabelle sind die Namen der einzelnen Mitarbeiter des Bereiches aufgelistet. Die Tabellen wurden nebeneinander angeordnet, um in einzelnen Bereichen hinzukommendes Personal leichter ergänzen zu können und dennoch eine Übersicht über alle Bereiche zu behalten. Die Tabellen wurden mit dem Namen der Spaltenüberschrift benannt, wobei das Problem auch auftritt, wenn Tabellenname und Spaltenname sich unterscheiden. Um die Tabellennamen einfacher darzustellen erfolgte hier die namensgleiche Benennung (Bild Vorgabewerte)

    Nunmehr soll in einem anderen Tabellenblatt (der selben Arbeitsmappe) weitere Tabellen entstehen, deren erste Spalte die Daten aus den Vorgabetabellen enthalten. Leider funktioniert der strukturierte Verweis auf die Vorgabewerte nicht wie beabsichtigt. (Bild Zielwerte) Im als Tabelle formatierten Bereich WerteA sollten ab der markierten Zelle (A4) die Daten aus der Vorgabewerttabelle MitarbeiterA eingefügt werden, in der Tabelle WerteSB1 ab der Zelle A10 des Tabellenblattes die Vorgabewerte aus der Tabelle MitarbeiterSB1. Die dazu verwendete Formel: “=Tabellenname[# Daten]” führt ebenso zu dem unerwünschten Ergebnis wie die Formel “=Tabellenname[@Spaltenname]”, nämlich dazu, dass zwar die Werte aus der korrekten Spalte wiedergegeben werden (in WerteA die
    Spalte A der Vorgabewerte, in WerteSB1 die Spalte C der Vorgabewerte), jedoch nicht die Werte der korrekten Zeilen (Der Verweis führt dazu, dass der Wert aus der Zeile der Vorgabewerte übernommen wird, welche der Zeile in der Zielwerttabelle entspricht (In Zielwert A4 wird der Wert aus Vorgabe A4, in Zielwert A10 der Wert aus Vorgabe C10 übernommen Die Werte von C5 bis C9 gehen verloren…?

    Wie bring ich Excel über strukturierte Verweise dazu, in der Zielwerttabelle in Zellle A4 mit den Vorgabewerten aus der
    Vorgabetabelle MitarbeiterA und dort in Zeile 1 zu beginnen usw.? Wenn dies nicht möglich ist, wären die strukturierten Verweise nur zum umgruppieren von Tabellenspalten zu gebrauchen da Quell- und Zieltabellen zumindest von den Zeilen her immer auf dem selben Niveau strukturiert sein müssten…

    • Martin Weiß Autor des Beitrags

      Hallo Sten,

      es ist tatsächlich so, dass strukturierte Verweise auf andere Tabellen nur funktionieren, wenn sich der Verweis in der gleichen Zeile befindet. Ist dies nicht der Fall, bleibt nur der Weg mit normalen Zellbezügen. Zumindest ist mir keine andere Lösung bekannt.

      Schöne Grüße,
      Martin