Ein extrem praktisches und leistungsfähiges Konzept in Excel sind die formatierten Tabellen („intelligente Tabellen“). Nicht nur im Zusammenhang mit Pivot-Tabellen und Power Query-Abfragen machen sie das Leben viel einfacher. Auch sonst sind die damit verbundenen Automatiken sehr nützlich.
Allerdings haben sie auch ihre Grenzen und Nachteile. Einer davon betrifft relative Zellbezüge. Wo genau das Problem liegt und wie man es umgehen kann, zeigt der heutige Artikel.
Und so geht’s:
Formeln in formatierten Tabellen
Ein wesentliches Merkmal von formatierten Tabellen ist, dass es dort sogenannte strukturierte Verweise
gibt. Das heißt, dass in Formeln normalerweise nicht die üblichen Zellbezüge (A2, C2:C14), sondern die aussagefähigeren Spaltennamen verwendet werden.
Im folgenden Bild sieht man das in der Spalte „Gesamt“. Hier steht eben nicht D3*E3, um den Gesamtumsatz zu errechnen, sondern ein strukturierter Verweis:
=[@Menge]*[@Einzel]
Formel mit strukturierten Verweisen
Das @-Zeichen steht dabei für die jeweils aktuelle Zeile. Der Vorteil dieser Schreibweise ist offensichtlich: Man sieht sofort, was hier gerechnet wird. Gerade in großen Tabellen mit sehr vielen Spalten ist das ein nicht zu unterschätzender Vorteil.
Sobald man aber auf eine andere als die aktuelle Zeile verweist, werden automatisch wieder die normalen Zellbezüge verwendet. In der Spalte „kumuliert“ wird zum aktuellen Gesamtumsatz der kumulierte Wert aus der Vorgängerzeile addiert:
Formel mit einem relativen Zellbezug
Das ist zunächst einmal kein großes Problem. Es kann aber schnell eines werden, wenn man in der Tabelle Zeilen einfügt oder löscht.
Hier noch ein wichtiger Hinweis:
Der erste Eintrag in der Spalte „kumuliert“ enthält eine andere Formel als alle anderen Zeilen. Hier wird nur der Wert aus der Spalte „Gesamt“ übernommen. Wenn du also die folgenden Beispiele selbst nachbaust und in der Spalte „kumuliert“ eine neue Formel eintippst, wird diese zunächst in alle Zeilen – auch in die erste – übertragen, was natürlich falsch ist. Daher musst du anschließend immer nochmal die Formel in der ersten Zeile manuell ändern!
Zeilen löschen
Um die folgenden Beispiele besser nachvollziehbar zu machen, habe ich in einer weiteren Spalte die Formel ausgeschrieben, die in der kumuliert-Spalte steht.
Zur Verdeutlichung: Die Formel aus der Spalte „kumuliert“ im Klartext
Löschen wir also jetzt innerhalb der Tabelle eine einzelne Zeile und schauen, was passiert:
Ergebnis, nachdem eine Zeile gelöscht wurde
Während die Formeln in Spalte F ohne Probleme korrekt arbeiten (zur Erinnerung: hier wurde ausschließlich mit den Spaltennamen gearbeitet), liefern die kumulierten Werte ab Zeile 8 nur noch Fehler. Da ich die Zeile 7 gelöscht habe, fehlt in der darauffolgenden Zeile 8 plötzlich der Zellenbezug. Alle nachfolgenden Formeln ab Zeile 9 hingegen verweisen zumindest auf die korrekte Vorgängerzelle, liefern aber aufgrund des #BEZUG!-Fehlers in Zeile 8 ebenfalls nur Fehlerwerte.
Die einzig „gute“ Nachricht ist, dass beim Löschen von Zeilen der Fehler sofort sichtbar wird.
Zeilen einfügen
Tückischer, da nicht auf den ersten Blick erkennbar, ist das Einfügen von einer neuen Zeile. Zunächst passt noch alles…
Eine neue Zeile wurde eingefügt…
…aber sobald man eine Menge und einen Einzelpreis eingibt, stimmt eben nichts mehr. Die Folgezeile ignoriert einfach die neu eingefügte Zeile 10 und addiert ebenfalls den kumulierten Wert aus Zeile 9:
…Menge und Einzelpreis wurden manuell ergänzt
Und dieser Fehler springt eben nicht sofort ins Auge! Wie lässt sich so etwas umgehen?
Anstatt direkt auf die Zelle der Vorgängerzeile zu verweisen und damit einen normalen Zellbezug zu erzeugen, kann die Vorgängerzeile über die BEREICH.VERSCHIEBEN-Funktion ermittelt werden:
BEREICH.VERSCHIEBEN([@kumuliert];-1;0)
Damit wird die aktuelle Position in der „kumuliert“-Spalte um eine Zeile noch oben versetzt (-1)
Anwendung von BEREICH.VERSCHIEBEN
Und wenn jetzt eine Zeile gelöscht oder eingefügt wird, stimmen die Berechnungen trotzdem.
Löschen der ursprünglichen Zeile 8 (Verkäufer Meyer):
Korrektes Ergebnis auch nach gelöschter Zeile
Einfügen einer neuen Zeile:
Ebenfalls korrekt mit neu eingefügter Zeile
Zugegeben, die Formel wird dadurch etwas länger und schwerer zu lesen, aber der Lohn dafür ist ein richtiges Ergebnis
Alternative Variante
Ein Nachteil der BEREICH.VERSCHIEBEN-Funktion soll jedoch nicht verschwiegen werden: Es handelt sich dabei um eine sogenannte volatile Funktion, die in großen Tabellen durchaus zu Performance-Einbußen führen kann. Wer etwas mehr über volatile Funktionen wissen möchte, sollte diesen Artikel lesen: Excel im Schneckentempo: Volatile Funktionen
Daher zeige ich noch eine Alternative mit der INDEX-Funktion, die deutlich performanter ist.
=[@Gesamt]+INDEX([kumuliert];ZEILE([@kumuliert])-1-ZEILE(Tabelle1[[#Kopfzeilen];[kumuliert]]))
Alternative Variante: Performantere INDEX-Funktion
Die INDEX-Funktion braucht im ersten Argument einen Bezug, also die zu durchsuchende Spalte. In unserem Beispiel also die Spalte „kumuliert“. Das zweite Argument gibt die gewünschte Zeile innerhalb dieses Bezugs an. Und hier wird es etwas tricky.
Zuerst nehmen wir die Zeilennummer der aktuellen Zeile und ziehen davon den Wert 1 ab, um auf die Vorgängerzeile zu kommen;
ZEILE([@kumuliert])-1
Wenn die formatierte Tabelle aber nicht in der absoluten Zeile 1 beginnt, sondern wie in meinem Beispiel erst ab Zeile 2, stimmt die Rechnung nicht. Daher müssen wir noch die Zeilennummer der Überschrift abziehen:
-ZEILE(Tabelle1[[#Kopfzeilen];[kumuliert]])
Sieht kompliziert aus. Aber wenn man beim Eintippen einfach auf die Überschrift klickt, wird dieser strukturierte Verweis von Excel automatisch eingesetzt.
(Die Beispieldatei kannst du bei Bedarf hier herunterladen).
Das war’s wieder für heute. Vielleicht hast du ja noch einen anderen, viel besseren Vorschlag, wie man mit solchen Tücken in formatierten Tabellen umgehen kann. Dann lass es uns unten in den Kommentaren wissen.
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.
Hallo Martin,
Alternativ zu Deiner INDEX()-Formelvariante wäre hier auch mE einfacher: =SUMME([@Gesamt];INDEX(G:G;ZEILE()-1)) möglich.
Für Nachleser: Die Funktion SUMME() anstelle einer Addition mit + ist hierbei notwendig, da man die Formel ja bereits ab der ersten Datenzeile nutzen möchte. Da jedoch die Feldüberschrift „kumuliert“ ein Textwert ist und dieser aber die mittels INDEX(G:G;ZEILE()-1) in der ersten Datenzeile als Vorgängerzellenwert ermittelte wird, würde eine Addition mit + zu einem Fehlerwert führen. Dagegen ignoriert SUMME() jegliche Textwerte ohne Fehlerwertergebnis.
Anstelle der SUMME()-Formel wäre deshalb auch: =WENNFEHLER([@Gesamt]+INDEX(G:G;ZEILE()-1);[@Gesamt]) möglich.
Gruß Werner
..,-…
Hallo Werner,
ich bin begeistert! Das ist wirklich eine schöne und elegante Variante, vielen Dank dafür.
Schöne Grüße,
Martin
Leider funktioniert die Lösung nicht, wenn eine neue erste Zeile eingefügt wird. Außerdem ist es unschön, dass in der Spalte nicht überall die gleich Formel steht. Beide Probleme können mit der folgenden Erweiterung gelöst werden:
=[@Gesamt]+WENN(ZEILE([@kumuliert])-ZEILE(Tabelle1[[#Kopfzeilen];[kumuliert]])=1;0;INDEX([kumuliert];ZEILE([@kumuliert])-1-ZEILE(Tabelle1[[#Kopfzeilen];[kumuliert]])))
Wenn die erste Zeile vorliegt (das ist der Fall wenn die Differenz zwischen der aktuelle Zeile und der Kopfzeile gleich 1 ist), dann muss 0 addiert werden sonst der Wert aus der vorhergehenden Zeile.
Übrigens die Idee mit INDEX statt BEREICH.VERSCHIEBEN finde ich sehr interessant. Danke!
Damit sind alle möglichen Fälle abgedeckt.
Hallo Andreas,
hast Du Dir mal meine beide alternativen Vorschläge angesehen? Bei diesen kannst Du auch problemlos eine neue erste Datenzeile einfügen.
Gruß Werner
.. , – …
Hallo Andreas,
ja, der Einwand mit der ersten Zeile ist berechtigt. Und danke für deine Erweiterung.
Werner hat in seinem Kommentar noch eine andere sehr schöne Variante beschrieben.
Schöne Grüße,
Martin
Nett. Nur wo bleibt da dann die angepriesene Lesbarkeit schon bei einer eigentlich ziemlich einfachen Berechnung? ?
Hallo Mm,
das stimmt, solche Formeln sind schon etwas schwieriger zu lesen. Aber immer noch etwas leichter, als wenn normale Zellbezüge verwendet werden.
Schöne Grüße,
Martin
Sorry, aber ich kumuliere auch in intelligenten Tabellen ganz simpel mit Zellbezügen, da die strukturierten Verweise hier nichts bringen: =SUMME($L$7:L8)
Zeilen einfügen und löschen ist damit kein Problem höchstens bei neuer 1. Zeile
Hallo Carola,
in dem von dir beschriebenen Fall würde ich das auch genauso machen. Aber es gibt auch Situationen, in denen man man eben nicht von ganz oben beginnend alle Werte summieren muss, sondern gezielt auf die Vorgängerzeile zugreifen möchte. Und dann kann es zu den beschriebenen Problemen kommen.
Schöne Grüße,
Martin
Wie kann man bei formatierten Tabellen jedoch Formeln auf andere Zellen „ziehen“, ohne dass die Bezguszellen mit verschoben werden?
Mit normalen Bezügen kann man ja durch das $-Zeichen Zeile oder Spalte fixen.
Hallo,
ja, $-Zeichen funktionieren hier leider nicht. Du musst stattdessen die zu fixierende Spalte so angeben:
Tabellenname[[Spaltenname]:[Spaltenname]]
Dann bleibt der Spaltenbezug fix, auch wenn die Formel nach rechts in andere Spalten kopiert wird.
Ich werde dazu bei Gelegenheit nochmal einen eigenen Artikel zu dem Thema schreiben.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die Info.
Ich bin in den letzten Tagen mit den Tabellen verzweifelt. Ich habe von z.B. auf Blatt1 eine Tabelle in Zeile 2 und auf Blatt 25 ebenfalls eine Tabelle ab Zeile 2, die sich teilweise auf die erste bezieht.
Wenn ich oben wegen der Optik Zeilen einfüge oder lösche, stimmen die ganzen Bezüge nicht mehr. Wenn die Tabelle nicht in Zeile 2 sondern in einer anderen begann, gab es kein Problem. Das ist doch irre!!!
Ich habe jetzt in den Optionen „Tabellennamen in Formeln benutzen“ ausgestellt. Das macht mehr Probleme, als dass es nützt.
Es ist mir ein Rätsel, was sich MS dabei gedacht hat… 🙁
Gruß André
Hallo Andre,
warum es bei dir mit Zeile 2 nicht funktioniert und mit anderen Zeilen schon, erschließt sich mir jetzt auch nicht. Aber ja, die Bezüge können manchmal sehr tückisch sein und eine Universallösung gibt es dafür leider nicht…
Schöne Grüße,
Martin
Hallo Martin,
Danke für die tolle Erläuterung.
Ich habe nun nur noch folgendes Problem.
Bislang hatte ich in meinen Formeln nur Verweise auf die eigene Zeile.
Wenn ich nun ein neue Zeile begonnen habe, wurden die Formeln automatisch übernommen.
Nun, wo ich auch auf eine vorherige Zeile mit meiner Formel zugreifen muss, klappt das mit der automatischen Formelübernahme icht mehr.
Hast Du da eventuell auch eine Idee?
Viele Grüße
Torste
Hallo Torsten,
die Formelautomatik kommt immer dann an ihre Grenzen, wenn nicht jede Zelle die exakt gleiche Formel enthält. Das wird bei dir vermutlich in der ersten Zeile der Fall sein, wo due wahrscheinlich beim Bezug auf die Kopfzeile einen Fehlerwert bekommst. Du kannst beispielsweise versuchen, die Formel mit Hilfe einer WENN-Bedingung so anzupassen, dass ein Fehlerfall abgefangen wird.
Schöne Grüße,
Martin
Die Alternativ-Variante könnte m.E. auch ein wenig einfacher, nämlich mit einer mittigen, schlichten Funktion ZEILE(), wasserdicht bewältigt werden:
=[@Gesamt]+INDEX([kumuliert];ZEILE()-1-ZEILE(Tabelle1[[#Kopfzeilen];[kumuliert]]))
Zugegeben, eine nur sehr geringe Vereinfachung.
Herzlichst
Martin
Hallo Martin,
du hast vollkommen recht, die ZEILE-Funktion benötigt hier gar kein weiteres Argument. Danke für den Hinweis!
Schöne Grüße,
Martin