Absolute und relative Bezüge in formatierten Tabellen 26

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.



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

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

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

    • Avatar-Foto
      Martin Weiß

      Hallo Robert,

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

      Schöne Grüße,
      Martin

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

    • Avatar-Foto
      Martin Weiß

      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

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

        • Avatar-Foto
          Martin Weiß

          Hallo Tim,

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

          Schöne Grüße,
          Martin

          • Avatar-Foto
            Tim

            Das ist ja auch an einer Stelle versteckt, wo man es niemals suchen würde.

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

    • Avatar-Foto
      Martin Weiß

      Hallo Lutz,

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

      Schöne Grüße,
      Martin

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

    • Avatar-Foto
      Martin Weiß

      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

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

  • Avatar-Foto
    Inge Wilhelmina Scholz

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

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

    • Avatar-Foto
      Martin Weiß

      Hallo Holger,

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

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Holger Schmidt

        Hallo Martin, gerne
        Auf der Suche im Netz findet man doch immer wieder Coole Seiten, Daumen hoch !
        Gruß Holger

  • Avatar-Foto
    Christian

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Sten Höhl

    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

    • Avatar-Foto
      Sten Höhl

      …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

      • Avatar-Foto
        Martin Weiß

        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

  • Avatar-Foto
    Kay

    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

    • Avatar-Foto
      Martin Weiß

      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