Formatierte Tabellen, relative Bezüge und deren Probleme 9

Wie man Probleme mit relativen Bezügen in formatierten Tabellen vermeiden kann
 

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

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

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

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

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...

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

…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

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

Korrektes Ergebnis auch nach gelöschter Zeile

Einfügen einer neuen Zeile:

Ebenfalls korrekt mit neu eingefügter 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

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.

 

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 zu Martin Weiß Antworten abbrechen

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

9 Gedanken zu “Formatierte Tabellen, relative Bezüge und deren Probleme

  • neopa

    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
    ..,-…

    • Martin Weiß Autor des Beitrags

      Hallo Werner,

      ich bin begeistert! Das ist wirklich eine schöne und elegante Variante, vielen Dank dafür.

      Schöne Grüße,
      Martin

  • Andreas Neumann

    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.

    • neopa

      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
      .. , – …

    • Martin Weiß Autor des Beitrags

      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

    • Martin Weiß Autor des Beitrags

      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

  • Carola

    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

    • Martin Weiß Autor des Beitrags

      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