Wie kann man eine Formel in Excel WIRKLICH unveränderlich machen?
Hallo, die absoluten Zellbezüge mit den $-Zeichen sind bekannt, aber die beziehen sich mit der Festlegung auf z.B. $B2 darauf, dass das gleiche Feld auch dann noch herangezogen wird, wenn die Formel selbst an eine andere Position verschoben/kopiert wird - soweit ist alles klar.
Meine Frage geht aber dahin, dass sich in dem Fall, wenn ich den "Wert" der Zelle B2 z.B. nach C3 verschiebe, automatisch auch der Bezug in der Formel auf $C3 verändert wird. Genau genommen bezieht sich also die Festlegung auf $B2 nicht auf die Zelle mit dieser Adresse, sondern auf den darin enthaltenen Wert - und wenn ich den verschiebe, verschiebt sich auch der Bezug in der Formel, trotz $ ! Gibt es eine Möglichkeit, den Bezug in der Formel auch dann auf B2 zu belassen, wenn die Daten aus der Zelle verschoben weden? Wenn die Zelle nach dem Verschieben des Inhalts leer ist, müßte jetzt der korrekte Inhalt "0" aus B2 geliefert werden, statt dass der Bezug in der Formel den Daten "hinterherläuft" - einmal B2, immer B2!
Weiß jemand, wie man diesen Automatismus abschalten kann? Vielen Dank für eine wirklich wertvolle Hilfe!
3 Antworten
Du kannst dafür die Funkion INDIREKT verwenden. Denn hier gelten Bezüge als Text und werden nicht angepasst.
=INDIREKT("B2") wird immer das liefern, was in B2 steht. Wenn die Zelle verschoben oder gelöscht wird, wird der Wert des neuen B2 berechnet.
Man kann INDIREKT auch in anderen Formeln verwenden, z.B.
=SUMME(INDIREKT("B2:B4"))
Achtung, häufiger Einsatz der Funktion kann dazu führen, dass der PC recht viel Zeit für die Berechnung der Datei braucht.
auch wenn es schon gelöst ist, der Vollständigkeit halber noch eine zweite Lösung (sofern Du eine Titelspalte hast, die unverrückt bleibt):
=INDEX(BEREICH.VERSCHIEBEN(A3;0;3;5;1);VERGLEICH(A6;BEREICH.VERSCHIEBEN(A3;0;1;5;1);0))
dabei ist A3 die "Ankerzelle",
A6 der LeitBegriff/dieLeitZahl,
im ersten Bereich.verschieben ist:
0 der Zeilenversatz,
3 der Spaltenversatz,
5 die neu definierte Zeilenzahl des auszulesenden Bereichs,
1 die Spaltenzahl des auszulesenden Bereichs.
im zweiten Bereich.verschieben ist:
0 der Zeilenversatz,
1 der Spaltenversatz (1. Spalte des neu definierten Bereichs=Vergleichsspalte),
5 wieder die neu und wie im 1 Argument von index() gleichgroß definierte Zeilenzahl des auszulesenden Bereichs,
1 die Spaltenzahl der Vergleichsbereichs (muss 1 sein)
ist allerdings etwas kompliziert zu handhaben, da schwierig, im Kopf sich die verschobenen Bereiche samt Spalten richtig vorzustellen
Noch eine Idee, wenn du verstehen willst wie und warum das so funktioniert: die Bezugsart in den Optionen auf Z1S1 umschalten.
Dann sehe ich dass Z2S2 eine feste Hausnummer ist, während Z(-1)S(-1) eine relative Hausnummer ist ausgehend von der momentanen Zelle.
@ MrProcess: ein guter Ansatz fürs Verständnis!
und noch ein anderer gegen ein Missverständnis:
Genau genommen bezieht sich also die Festlegung auf $B2 nicht auf die Zelle mit dieser Adresse, sondern auf den darin enthaltenen Wert
Nein, es ist wirklich die $Zelle und der Bezug auf sie! Verschiebst Du sie, wandert der Bezug mit, aber es wird an ihre Stelle sofort eine neue gesetzt. Früher wurde dann in vielen Formeln #Bezug! angezeigt, wenn es sich um die erste oder letzte Zelle eines auszuwertenden Bereichs handelte. Die neueren Versionen haben das korrigiert und beziehen sich weiterhin auf die alte Position.
(im nicht mehr existenten Lotus-123 wurde dann der auszuwertende Bereich umgeschrieben, ein ganz anderer Ansatz. Du siehst aber, dass man auf so ein Mehrdeutigkeit schaffendes Problem unterschiedlich umgehen kann, die xl-Programmierer haben sich eben für die vorliegende Lösung entschieden, auf irgendeine Weise MUSS ja Eindeutigkeit wieder hergestellt werden. Als Praktiker muss man nur wissen, wie es denn laufen soll, und das hat Subopt ja gut beschrieben!)
Bist du dir da sicher, dass da was "umgeschrieben" wird/wurde?
Der Glaubenskrieg von 25 / 30 Jahren war, ob die A1 oder Z1S1 Schreibweise die bessere wäre. Multiplan nutzte Z1S1, Lotus, Symphony haben die A1 Schreibweise genutzt. Bei XL hat man dann gesagt, dann mach mer halt beides. Bei der Z1S1 Schreibweise muss ich mehr tippen. Aber logischer ist es allemal, was da passiert. Und eindeutig. Was man dann daran sieht dass es in der Programmierung - sowohl bei den XLM Makros als auch bei VBA eben auf Präzision ankommt. Und da sind wir wieder bei Z1S1 Schreibweise.
Gibt es eine Möglichkeit, den Bezug in der Formel auch dann auf B2 zu belassen, wenn die Daten aus der Zelle verschoben weden?
Nicht verschieben, sondern kopieren. Dann funktioniert das.
DAS ist es! Auf dem Summen-Arbeitsblatt sollen die festen Formeln und Bezüge stehen, während auf dem Daten-Arbeitsblatt jeden Monat alle Inhalte nach rechts verschoben werden und in die linke Spalte die aktuellen Werte eingetragen werden. So kann man auch gut automatisch aktualisierte 3-Monats Zahlen in den entsprechenden Ergebnisfeldern präsentieren und muß immer nur eine Spalte neu befüllen.
Perfekt, so hatte ich mir das vorgestellt, besten Dank!