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
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
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
Wenn sich nun die aktive Zelle innerhalb der formatierten Tabelle befindet, erscheint die neue Registerkarte „Entwurf“, die spezielle Optionen anbietet:
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
Ü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
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
Zusätzlich gibt es noch ein paar besondere Namenselemente, die man innerhalb seiner Formeln nutzen kann:
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.
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.
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!
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
Pingback: Excel, das Streifenhörnchen | Der Tabellen-Experte
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…
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
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?
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
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?
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
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
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
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.
Hallo Carola,
die schlechte Nachricht ist: Dafür gibt es im Augenblick tatsächlich keine Lösung. Blattschutz und formatierte Tabellen schließen sich aus. Diese Feature wird auch von vielen anderen Anwendern vermisst (auch von mir :-)) und man kann auf der folgenden Seite von Microsoft sogar dafür abstimmen:
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/16452913-get-tables-working-on-protected-sheets-add-rows
Wenn genügend Anwender zustimmen, wird die Funktion vielleicht eingeführt.
Der einzige mir bekannte Workaround ist, die formatierte Tabelle von Anfang an so groß zu dimensionieren, wie man sie maximal brauchen wird. Danach kann man den Blattschutz aktivieren. Aber das ist wie gesagt nur ein unschöner Workaround.
Schöne Grüße,
Martin