Ich bin ja ein großer Freund von formatierten („intelligenten“) Tabellen. Wenn man einmal auf den Geschmack gekommen ist, möchte man sie nicht mehr missen. Die Vorteile sind vielfältig:
- Ansprechende Optik
- Automatische Erweiterung bei neuen Datensätzen
- Perfekt als Basis für Pivot-Tabellen
- Formelautomatik
- Sprechende Bezüge (sogenannte strukturierte Verweise)
Und gerade Letzteres führt manchmal zu Problemen. Während der geübte Excel-Anwender in normalen Listen je nach Bedarf absolute, relative oder gemischte Bezüge einsetzt (Stichwort $-Zeichen), scheint das in formatierten Tabellen nicht zu funktionieren.
Doch. Man muss nur wissen, wie!
Und so geht’s:
Absolute und relative Bezüge in Listen
Um das besagte Problem zu veranschaulichen, habe ich zwei Listen vorbereitet (die Beispieldatei kannst du dir hier herunterladen). Links befindet sich eine fortlaufende Umsatztabelle. In der Tabelle rechts daneben sollen die Umsätze nach Monat und Produkt verdichtet dargestellt werden:
Beispiel: Einfache Liste
In aller Regel würde ich dafür natürlich eine Pivot-Tabelle verwenden, aber in diesem Beispiel soll die SUMMEWENNS-Funktion zum Einsatz kommen.
=SUMMEWENNS(Summenbereich;Kriterienbereich 1;Kriterium 1;Kriterienbereich 2;Kriterium 2;....)
Damit ich anschließend die Formel ohne Änderungen in alle anderen Zellen kopieren kann, verwende ich absolute und relative bzw. gemischte Bezüge:
Absolute und gemischte Bezüge in Listen
Der Summenbereich und die beiden Kriterienbereiche enthalten nur absolute Bezüge, da sich diese Bereiche beim Kopieren nicht ändern sollen.
Kriterium 1 (= Monat) und Kriterium 2 (= Produkt) hingegen enthalten gemischte Bezüge. Der Monat soll immer fix auf Spalte A verweisen, jedoch auf die jeweilige Zeile. Das Produkt steht immer fix in Zeile 3, jedoch in unterschiedlichen Spalten. Damit funktioniert die Formel auch, wenn ich sie in die Spalten rechts und in die Zeilen darunter kopiere:
Absolute und gemischte Bezüge in Listen (Fortsetzung)
Etwas anders verhält sich die Sache in formatierten Tabellen
Formatierte Tabellen und strukturierte Verweise
Beide Listen habe ich nun in formatierte Tabellen umgewandelt (Menü „Start | Als Tabelle formatieren“) und die Namen tblUmsatz und tblVerdichtet dafür vergeben. Markiert man in der SUMMEWENNS-Formel bei der Eingabe die entsprechenden Bereiche, verwendet Excel automatisch anstelle der Zellbezüge sprechende Namen.
Die sogenannten strukturierten Verweise:
Bezüge in formatierten Tabellen
(Zum Thema „strukturierte Verweise“ hatte ich schon mal hier eine kleine Einführung veröffentlicht.)
Soweit, so gut.
Kopiere ich nun diese Formel in die anderen Zellen, sieht es leider nicht mehr so gut aus. Bleiben wir bei Produkt 1 und kopieren die Formel in die darunter liegenden Zeilen, stimmen die Ergebnisse noch, wie man hier sieht:
Bezüge in formatierten Tabellen (Fortsetzung)
Dabei macht es keinen Unterschied, ob ich die Formeln mit Strg+C und Strg+V kopiere oder über das kleine Ausfüllkästchen in der rechten unteren Zellenecke nach unten ziehe.
Wenn ich aber die Formeln nach rechts in die anderen Spalten kopieren, passt es leider überhaupt nicht mehr. Mit den Tastenkombinationen Strg+C und Strg+V wird die Formel ohne Änderung der Bezüge kopiert und liefert für alle 3 Produkte die gleichen Ergebnisse. Genauer gesagt, die gleichen Produktergebnisse:
Probleme mit strukturierten Verweisen
Aufgrund des @-Zeichens im Bezug [@Monat] wird zumindest noch die Zeile angepasst. Der Produktbezug bleibt aber fix auf dem ersten Produkt stehen.
Wenn ich hingegen die Formeln mit Hilfe des kleinen Ausfüllkästchens nach rechts ziehe, ist das Verhalten wieder anders. Jetzt wird zwar die richtige Produktspalte angesprochen, aber gleichzeitig verschiebt sich der Bezug von der eigentlich fixen Monatsspalte auf die erste Produktspalte, was natürlich verkehrt ist:
Probleme mit strukturierten Verweisen (Fortsetzung)
Eine verzwickte Situation. Aber es gibt eine Lösung!
Richtige Bezüge in strukturierten Verweisen
Um absolute Bezüge zu verwenden, muss die betreffende Spaltenbezeichnung in der Schreibweise „von“:“bis“ verwendet und in zusätzliche eckige Klammern gesetzt werden. Beispiel:
Tabellenname[[Umsatz]:[Umsatz]]
Für gemischte Bezüge (Spalte fix, Zeile variable) lautet die Schreibweise ähnlich, nur mit vorangestelltem @-Zeichen. Beispiel:
@Tabellenname[[Monat]:[Monat]]
Korrekte strukturierte Verweise
Wichtig:
Die Formel darf jetzt nicht mit Strg+C / Strg+V nach rechts kopiert werden. Stattdessen muss sie mit Hilfe des kleinen Ausfüllkästchens nach rechts gezogen werden. Nur dadurch bleibt der Verweis auf die aktuelle Zeile in der Spalte „Monat“ erhalten.
Korrekte strukturierte Verweise
Zusammenfassung
Damit auch tatsächlich das gewünschte Ergebnis herauskommt, sind zwei Dinge nötig:
- Die korrekte Verwendung der strukturierten Verweise
- Das Kopieren der Formeln mit Hilfe des Ausfüllkästchens
Wie man sieht, sind auch in formatierten Tabellen wahlweise absolute oder gemischte Bezüge möglich, auch wenn das zugegebenermaßen relativ umständlich ist. Wichtig ist aber, dass du nicht deswegen auf den Einsatz von formatierten Tabellen verzichten musst, weil auf den ersten Blick solche Bezüge nicht möglich erscheinen. Und ich hoffe sehr, du lässt dich davon nicht abschrecken!
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.
Zur Not bleibt einem ja auch immer noch die Möglichkeit, auch in formatierten Tabellen mit Zell-Adressen zu arbeiten, was aber wiederrum viele der Vorteile von formatierten Tabellen zunichte macht.
Hallo Robert,
das stimmt, die normalen Bezüge funktionieren im Zweifelsfall natürlich immer.
Schöne Grüße,
Martin
Noch ein kleiner Hinweis (den vermutlich viele kennen, aber vielleicht nicht alle): Wenn eine Formel in der ersten Zeile steht (oder auch einer anderen Zeile) und alle anderen Zellen dieser Zeile leer sind und dann die Formeleingabe mit der Zeilenrücklauftaste (retun) bestätigt wird, dann wird die Formel automatisch in alle Zellen übernommen. Das ist gerade bei Tabelle mit vielen Zeile schneller als das Ziehen des Ausfüllkästchens.
Hallo Andreas,
vielen Dank für die Ergänzung. Das funktioniert für alle Zellen innerhalb einer Spalte (also von oben nach unten), aber nicht über die Spalten innerhalb einer Zeile (von links nach rechts), richtig?
Schöne Grüße,
Martin
Das stimmt. Es gibt nur die Spaltenrichtung. Und das Verhalten kann man ein- und ausschalten unter:
Datei -> Optionen -> Dokumentprüfung -> AutoKorrektur -> AutoFormat während der Eingabe.
Hallo Tim,
diese Einstellung war mir bisher überhaupt noch nicht bekannt, vielen Dank für den Hinweis!
Schöne Grüße,
Martin
Das ist ja auch an einer Stelle versteckt, wo man es niemals suchen würde.
Hallo Martin,
wieder einmal ein toller Beitrag, welcher genau passend gekommen ist. Denn letzte Woche hatte genau dieses Problem meine Freude an der formatierten Tabelle ziemlich getrübt.
Bitte weiter so. Danke.
Gruß
Lutz
Hallo Lutz,
freut mich, wenn der Artikel auch noch zum richtigen Zeitpunkt erschienen ist 🙂
Schöne Grüße,
Martin
Hallo Martin,
kannte ich nicht, Danke! Allerdings würde ich zumindest die absoluten Bezüge auf formatierte Tabellen IMMER über die Vergabe von Namen für jede Spalte lösen: das ist sicher und die Formeln sind dann einfacher zu lesen …
Hallo Daniel,
die zusätzliche Vergabe von Namen (über den Namensmanager) ist natürlich auch eine gute Möglichkeit. Danke für den Tipp!
Schöne Grüße,
Martin
Super Lösung!!!
Ich habe mir bisher geholfen, indem ich die Funktion „Tabellennamen in Formeln verwenden“ ausschalte.
Datei, Optionen, Formeln, Arbeiten mit Formeln
Dann stellt EXCEL (bei Eingabe einer Funktion) die Bezüge wie in unformatierten Tabellen dar.
Du kannst das beliebig oft hin- und herschalten.
Hallo Gerhard,
auch eine interessante Lösung, vielen Dank dafür!
Schöne Grüße,
Martin
mal wieder ein sehr hilfreicher Excel-Tipp. So kann man sich auf jeden Newsletter vom Tabellenexperten Martin Weiß freuen! WEITER SO!
Hallo Inge,
vielen Dank für das nette Feedback!
Schöne Grüße,
Martin
Hi
Für mich sind das immer noch Bömische Dörfer.
Aber man kann ja noch lernen und das kann man hier gut.
mfg toni
Hallo
ich habe mir etwas anders beholfen, um einen absoluten Bezug zu bekommen,
was aber gut funktioniert und zB. die automatische Erweiterung nicht verhindert.
Bei deinem Beispiel, Daten in der Spalte „Umsatz“, habe ich einem Namen „Umsatz“ mit dem Bezug =Tabelle1[Umsatz] angelegt.
Ich benutze jetzt für den absoluten Spaltenbezug den angelegten Namen.
Gruß Holger
Hallo Holger,
ein benannter Bereich ist natürlich auch eine sehr gute Idee. Vielen Dank für diesen Denkanstoß!
Schöne Grüße,
Martin
Hallo Martin, gerne
Auf der Suche im Netz findet man doch immer wieder Coole Seiten, Daumen hoch !
Gruß Holger
Hallo Martin.
Dein Artikel und die Kommentare sind recht interessant, treffen zwar nicht ganz auf meine Problematik zu, aber vielleicht weißt du eine Lösung. Die Tabelle ist mit einem Power Query verknüpft. Wenn ich eine Spalte im Power Query Editor hinzu füge und die Tabelle neu lade, dann verschieben sind die strukturierten Verweise in Formeln (Summewenn usw.), egal wie man sie schreibt. Auch Bereichsnamen werden verschoben. Man kann keine neue Spalte hinzufügen, ohne die bereits vorhanden Berechnungen zu verlieren.
Gibt es da, deines Wissens, einen Trick dies zu verhindern?
Schöne Grüße,
Christian
Hallo Christian,
es ist immer problematisch, wenn über eine Abfrage die Tabellenstruktur verändert wird. Daher kann es gut möglich sein, dass hier die Formeln einfach nicht mehr funktionieren. Der einzige Tipp, den ich hier geben kann: Wenn irgendwie möglich, dann sorge erst dafür, dass die Struktur passt und erstelle dann die Bezüge. Einen besseren Hinweis habe ich leider auch nicht.
Schöne Grüße,
Martin
Moin,
folgendes Problem:
Ich bekomme aus dem Internet den Preis von Gold für (x)Tage. Und ich bekomme den Preis von EUR/USD für die selben (x)Tage. Jetzt berechne ich die Korrelation. Funktioniert prima! Zum Verständnis: ich betrachte 28 Währungspaare und die Korrelation zu viellen Assets (nicht nur Gold). Ich tüftle derweile selbst daran, aber vielleicht haben Sie eine adequate Lösung. (x)Tage soll veränderlich sein. Damit ich keine Macke bekomme beim Tippen benutze ich absolute Bezüge, und ziehe dann nach rechts. Währungspaar(fest/absolut) zu Asset(x). Problematisch wird es halt nur, wenn sich die Datenmenge ändert.
Die Lösung schein mir mit formatierter Tabell möglich zu sein, wie oben beschrieben.
Problem 2: formatierte Tabellen werden nur größer und nicht automatisch mit Datenmenge auch kleiner? -> Unbrauchbare Diagramme.
Was sind Ihre Gedanke zu Möglichkeiten?
Danke!
Sten
…ok, das Absolut setzen funktioniert schon einmal. Was mir jetzt noch fehlt ist:
ich benötige identische Tabellen (in Tabelle1 kommt ein Datensatz dazu, der soll automatisch auch in Tabelle2 dazu kommen) ist das möglich?
Wie sieht es aus mit Berechnungen (Operationen aus Tabelle1 und 2 werden in Tabelle3 berechnet, geht auch, allerdings passt sich die Größe nicht an) Gibt es da Möglichkeiten? -> Neue Daten in Tabelle1 = alle anderen Tabellen passen sich an und berechnen ggf. neu
Ich hoffe das ist nicht zu wirr beschrieben…
LG
Hallo Sten,
eine automatische Übernahme neuer Datensätze ist so ohne weiteres nicht möglich. Dafür wäre VBA-Programmierung oder – was ich empfehlen würde – Power Query notwendig. Mit Power Query könnte man die Original-Tabelle einlesen und praktisch unverändert an anderer Stelle nochmal ausgeben. Das funktioniert zwar nicht ganz automatisch, aber fast. Man muss dazu nur per Rechtsklick die Tabelle aktualisieren.
Schöne Grüße,
Martin
Hallo Martin,
Deinem Rat folgend nutze ich seit ein paar Jahren die formatierten Tabellen und konnte nach ein paar Anlaufschwierigkeiten Deine Begeisterung dafür absolut nachvollziehen. Erst recht in Kombination mit Pivottabellen ist das nicht nur eine absolute Erleichterung sondern der Turbogang.
Aktuell habe ich die Situation, dass ich aus einer Exceltabelle (Angebotsdatei) heraus Bezug auf eine zweite Datei (Kundenumsätze) nehme, in der die Umsätze in einer formatierten Tabelle vorliegen. Die Datei Kundenumsätze besteht aus diversen Blättern, unter anderem aus Pivottabellen, welche ebenfalls auf die formatierte Tabelle mit den Umsätzen zugreifen. Die kumulierten Umsätze sollen nach Produktbereich und Zeitraum in der Angebotsdatei angezeigt werden. Wenn beide Dateien geöffnet sind, kommen die Daten auch richtig an. Leider klappt die Aktualisierung nicht, wenn die Datei mit den Kundenumsätzen geschlossen ist.
Bisher war es so gelöst, dass die Angebotsdatei ebenfalls auf eine zweite Datei zugriff, aber dort nicht mit einer formatierten Tabelle gearbeitet wurde. Da hat die Aktualisierung auch ohne Öffnung der Datei (Kundenumsätze) geklappt.
Im Microsoftforum finde ich nur die Information, dass bei der Aktualisierung beide Dateien geöffnet sein müssen.
https://support.microsoft.com/de-de/office/verwenden-von-strukturierten-verweisen-f%C3%BCr-excel-tabellen-f5ed2452-2337-4f71-bed3-c8ae6d2b276e (recht weit unten)
Kennst Du einen Trick wie es doch geht?
Viele Grüße – Kay
Hallo Kay,
nein, da gibt es keinen Trick, verknüpfte Dateien müssen geöffnet sein. Ich rate aber ohnehin davon ab, mit direkten Verknüpfungen zwischen unterschiedlichen Arbeitsmappen zu arbeiten, der Ärger ist praktisch vorprogrammiert (ein Beispiel davon siehst du bereits). Stattdessen empfehle ich, die externen Daten über Power Query-Abfragen einzulesen. Das ist wesentlich robuster und weniger fehleranfällig, Änderungen können mit geringerem Aufwand umgesetzt werden (z.B. wenn die Quelltabellen umbenannt oder verschoben wurden) etc. Und die Daten können importiert werden, wenn die Quellen geschlossen sind (genauer gesagt ist das sogar eine Voraussetzung).
Schöne Grüße,
Martin