Absolute und relative Bezüge in formatierten Tabellen 19

Mit ein wenig "Gewusst wie!" lassen sich auch relative Bezüge in formatierten Tabellen verwenden
 

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

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

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)

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

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)

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

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)

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

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

Korrekte strukturierte Verweise

Zusammenfassung

Damit auch tatsächlich das gewünschte Ergebnis herauskommt, sind zwei Dinge nötig:

  1. Die korrekte Verwendung der strukturierten Verweise
  2. 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!

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

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

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

19 Gedanken zu “Absolute und relative Bezüge in formatierten Tabellen

  • Robert

    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.

    • Martin Weiß Autor des Beitrags

      Hallo Robert,

      das stimmt, die normalen Bezüge funktionieren im Zweifelsfall natürlich immer.

      Schöne Grüße,
      Martin

  • Andreas Neumann

    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.

    • Martin Weiß Autor des Beitrags

      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

      • Tim

        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.

        • Martin Weiß Autor des Beitrags

          Hallo Tim,

          diese Einstellung war mir bisher überhaupt noch nicht bekannt, vielen Dank für den Hinweis!

          Schöne Grüße,
          Martin

  • Lutz Wörner

    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

    • Martin Weiß Autor des Beitrags

      Hallo Lutz,

      freut mich, wenn der Artikel auch noch zum richtigen Zeitpunkt erschienen ist 🙂

      Schöne Grüße,
      Martin

  • Daniel G.

    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 …

    • Martin Weiß Autor des Beitrags

      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

  • Gerhard Duscha

    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.

  • Inge Wilhelmina Scholz

    mal wieder ein sehr hilfreicher Excel-Tipp. So kann man sich auf jeden Newsletter vom Tabellenexperten Martin Weiß freuen! WEITER SO!

  • Holger Schmidt

    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

    • Martin Weiß Autor des Beitrags

      Hallo Holger,

      ein benannter Bereich ist natürlich auch eine sehr gute Idee. Vielen Dank für diesen Denkanstoß!

      Schöne Grüße,
      Martin