Tabellenverweise dynamisch

[JoeR]

Hallo, folgendes Szeario
A1: 100€
F1: 50€
G1: F1-A1

Soweit nicht schwierig.
Jetzt wird aber vor die Spalte G eine neue Spalte eingefügt.
Nun müsste es dann lauten in
H1: G1-A1

Wie kann ich dies dynamisch regeln, so dass, bei jedem neuen Einfügen einer weiteren Spalte, die Bezugsspalte sich automatisch anpasst ( also von G auf H)?

Liebe Grüße
JoeR

[miesepeter]

Wann relative, wann absolute Bezüge verwendet werden

Was zeichnet einen relativen Bezug aus? Angenommen, Sie möchten in Zelle E1 die Summe der Zellen im Bereich A1:B2 berechnen lassen. Die Formel, die Sie in E1 eingeben, wäre also =SUMME(A1:B2). Nun entscheiden Sie sich aber später, vor der Spalte A noch eine neue Spalte einzufügen. Die Elemente, die Sie summieren möchten, stehen dann plötzlich in B1:C2, und die Formel steht nicht mehr in E1, sondern in F1. Sie müssten also nach dem Einfügen der neuen Spalte alle Formeln in der Tabelle – und eventuell in anderen Tabellen – kontrollieren und korrigieren.

Diese Arbeit nimmt Ihnen LibreOffice Calc glücklicherweise ab. Nachdem eine neue Spalte A eingefügt wurde, wird die Formel =SUMME(A1:B2) automatisch auf =SUMME(B1:C2) aktualisiert. Ebenso werden beim Einfügen einer neuen Zeile 1 die Zeilennummern automatisch aktualisiert. Wann immer ein referenzierter Bereich verschoben wird, korrigiert LibreOffice Calc absolute und relative Bezüge. Beachten Sie aber, dass beim Kopieren von Formeln ausschließlich die relativen Bezüge aktualisiert werden. Die absoluten Bezüge bleiben unverändert.

Absolute Bezüge werden für Berechnungen eingesetzt, die sich auf eine bestimmte Zelle in der Tabelle beziehen. Wird eine Formel mit einem Bezug auf exakt diese Zelle in eine Zelle unter der ursprünglichen Zelle kopiert, so wird auch der Bezug nach unten verschoben, sofern Sie die Zellenkoordinaten nicht als absolut definiert haben.

@JoeR
Wieso probierst du das nicht einfach aus?!

[mikele]

Hallo,
das ist ein Problem, das sich nich tmit absoluter oder relativer Adressierung lösen lässt.
Probiere mal (in H1):

Code: Alles auswählen

=INDEX(A1:H1;1;SPALTE()-1)-A1

[miesepeter]

@mikele
Da kann ich jetzt keinen Unterschied erkennen. Beide Formeln liefern bei mir -50, auch wenn ich Spalten einfüge oder lösche.
Wieso arbeitest du mit =INDEX() ?
Erläuterst du deinen Ansatz näher? - Danke.

[Ferdinand]

Hallo JoeR,

die Fragestellung ist unlogisch: du fügst vor Spalte G eine neue ein, die beiden Werte 100€ und 50€ ändern ihren Ort dabei nicht, sie stehen nach wie vor in den Zellen A1 und F1. Warum sollte sich dann die Formel ändern? Etwas anderes wäre es, wenn du z. B. von Spalte E eine neue einfügen würdest, dann schiebt sich der 50€-Wert nach rechts und die Formel wird korrekt mit geändert.

Wichtig und richtig ist doch, dass die Formel in G1 nach wie vor die richtigen Werte findet und berechnet, oder?

Gruß
Ferdinand

[mikele]

Hallo,
meine Formel muss (für G1) natürlich so lauten:

Code: Alles auswählen

=INDEX(A1:G1;1;SPALTE()-1)-A1

Absolute und relative Adressierung sind relevant, wenn Formeln (aus einer Zelle) in eine andere Zelle kopiert werden.
Wenn in G1 die Formel

Code: Alles auswählen

=A1+F1

steht und dann die Zelle G1 nach H1 kopiert wird, wird daraus die Formel

Code: Alles auswählen

=B1+G1

Wäre es absolut adressiert

Code: Alles auswählen

=$A$1+$F$1

dann bleibt sie genauso, wenn sie nach H1 kopiert wird.
Etwas anderes ist es, wenn die Zellen, auf die sich die Formel bezieht, ihre Position ändern (verschoben werden oder Zeilen/Spalten eingefügt werden. Dann werden absolute und relative Bezüge so geändert, dass sie auf die neue Position zielen. Im dem Fall den JoeR beschrieben hat (Einfügen einer neuen Spalte G) bleibt die Formel natürlich so wie sie ist,
Wen sich die Fomel (ursprünglich) in G1 immer auf A1 und die Zelle links neben ihr (ursprünglich F1) beziehen soll, auch dann, wenn weitere Spalten eingefügt werden (zwischen F und G), dann ist das mit absoluter und relativer Adressierung nicht lösbar. (ob das inhaltlich sinnvoll ist und nicht durch einen anderen Tabellenaufbau vereinfacht werden könnte, sprich: was die eigentliche Aufgabe ist, kann nur JoeR beantworten).
Daher habe ich die Funktion Index() gewählt, um die Adressierung von F1 so umzuformulieren, dass es die zweite Spalte links neben der aktuellen ist:

Code: Alles auswählen

INDEX(A1:G1;1;SPALTE()-1)

Da die Formel (ursprünglich) in G1 steht, wir also aus dem Bereich A1:G1 die erste Zeile (es ist ja nur die Zeile 1 da) und die Spalte links neben G gewählt (die Formel adressiert also die Zelle F1).
Rutsch die Zelle durch einfügen einer Spalte nun nach H1, wird daruas die Formel

Code: Alles auswählen

INDEX(A1:H1;1;SPALTE()-1)

und adressiert nun die Zelle G1