Ordnung ins Chaos bringen: Strukturierte Verweise 13

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.

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

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

13 Gedanken zu “Ordnung ins Chaos bringen: Strukturierte Verweise

  • Avatar-Foto
    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!

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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…

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Sebastian Peter

    Hi

    Ich stoße auf ein „Problem“:
    Entsprechend dem Tipp hab ich eine Tabelle generiert. Jetzt will ich in einer Formel eine Zelle auswerten.
    Eigentlich zB Tabelle1!$A2=$C14
    Das ist nun aber Tabelle1[Landkreis]=$C14
    Ziehe ich das nach rechts ändert sich die für den Vergleich verwendete Spalte. Wie kann ich das „konstant“ setzen?

    Ist das vielleicht das gleiche Problem wie im Kommentar von Sten?

    • Avatar-Foto
      Martin Weiß

      Hi Sebastian,

      damit in formatierten Tabellen die Spalte fix bleibt, muss die Formel so aussehen:
      =Tabelle1[[Landkreis]:[Landkreis]]
      Wenn du die Formel jetzt nach rechts kopierst, bleibt der Verweis erhalten.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Stefan

    Hallo Martin,
    was hälst du davon wenn man Dropdownlisten (Datenprüfung) auf diese Art umsetzt? =INDIREKT(„Tabellenname[SPALTE1]“)
    Habe gelesen dass man das auf jedenfall vermeiden sollte. Ich verstehe nur nicht warum. Ist das vielleicht ein Performacethema von Excel?

    • Avatar-Foto
      Martin Weiß

      Hallo Stefan,

      ich würde jetzt nicht pauschal sagen, dass man das auf jeden Fall vermeiden sollte. Es hängt immer von der Größe der Anwendung ab. Aber es ist schon richtig: Die INDIREKT-Funktion gehört zu den sogenannten volatilen Funktionen und kann bei exzessiver Nutzung durchaus Auswirkungen auf die Performance haben. In diesem Artikel habe ich mal zu dem Thema etwa geschrieben:
      https://www.tabellenexperte.de/excel-im-schneckentempo-volatile-funktionen/

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Stefan

        Hallo Martin,
        vielen Dank für deine Antwort. Jetzt verstehe ich auf jedenfall mal was es damit auf sich hat. Aktuell habe ich keine Performanceprobleme mit der INDIREKT Funktion. Ich setze aber aktuell alle Dropdownfelder auf diese Weise um und ich kann leider nicht einschätzen welches Ausmaß das Ganze noch bekommen wird.

        Die Vorteile für mich sind recht simpel:
        – Ich kann Dropdownlisten direkt auf Tabellenspalten (per Name) beziehen
        – Ich sehe sehr schnell wie eine Tabelle/Spalte heißt, im Gegensatz zu einem benamten Bereich den ich auf den ersten Blick nicht erkennen kann.

        Gibt es zu meiner Lösung vielleicht eine praktische Alternative die ebenfalls übersichtlich und nicht volatil ist? Ich sollte mir am besten nochmal deine Ganzen Artikel reinziehen… da lässt sich bestimmt was finden;)

        Danke und Gruß Stefan

        • Avatar-Foto
          Martin Weiß

          Hallo Stefan,

          ich würde über den Namensmanager für die benötigten Tabellenspalten einen Namen vergeben. Das ist zwar ein wenig „doppelt-gemoppelt“, aber es hilft. Damit umgehst Du die Volatilität und hast trotzdem den Vorteil von formatierten Tabellen. Denn der Bereich, auf den sich der Name bezieht, passt sich trotzdem dynamisch der formatierten Tabelle an.

          Schöne Grüße,
          Martin

  • Avatar-Foto
    Carola Lück

    Alles super!
    Das Problem mit der Gültigkeit habe ich auch. Deshalb heißen alle meine intelligenten Tabellen „TTabellenname“, damit mir der eigentliche Name noch für den Namensmanager übrig bleibt.
    Wofür ich noch keine Lösung habe, ist der Schutz von Formeln in diesen Tabellen.
    Sobald ich den Blattschutz aktiviere, ist die Intelligenz futsch. Hab schon alle erdenklichen Optionen ausprobiert, mir fällt nichts mehr ein.
    Gibts dazu schon Lösungen?
    Momentan arbeite ich mit Farbe: eingabe in graue Felder auf eigene Gefahr.