Formatierte Tabellen, relative Bezüge und deren Probleme 17

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.



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

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

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

    • Avatar-Foto
      Martin Weiß

      Hallo Werner,

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

      Schöne Grüße,
      Martin

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

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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Mm

    Nett. Nur wo bleibt da dann die angepriesene Lesbarkeit schon bei einer eigentlich ziemlich einfachen Berechnung? ?

    • Avatar-Foto
      Martin Weiß

      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

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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    ak

    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.

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    André

    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é

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Torsten

    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

    • Avatar-Foto
      Martin Weiß

      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

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

    • Avatar-Foto
      Martin Weiß

      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