Wie kann man eine Formel in Excel WIRKLICH unveränderlich machen?

3 Antworten

Vom Fragesteller als hilfreich ausgezeichnet

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.

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!

0
@ralfcls

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

0

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.

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!)


0
@Iamiam

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.

0

Was möchtest Du wissen?