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

Kombination von "wenn dann"-Befehlen in Excel

Bezug nehmen auf die beigefügte Bilddatei möchte ich in Excel einer beliebigen Zelle im Excelfeld folgenden Befehl zuordnen:

"Wenn x und u zutreffen soll Wert B2 in der Zelle erscheinen, wenn x und v zutreffen soll Wert von C2 in der Zelle erscheinen, wenn x und w zutreffen, soll Wert von D2 in der Zelle erschein, ... "

Ich habe das mal mit folgender Formel probiert, aber da kommt es immer zu einer Fehlermeldung:

=WENN(UND(x;u);B2;WENN(UND(x;v);C2;WENN(UND.....

Freu mich wie immer über hilfreiche Antworten.

VG Pau

...zur Frage

Standardabweichung in Excel-Diagramm einfügen

Hallo!

Ich möchte mit Excel ein Diagramm erstellen, wo ich meine Werte und meine Standardabweichung sichtbar machen kann, die jeweilige Abweichung habe ich, aber wie bekomme ich das Grafisch hin? Meine Vorstellung ist das der Bereich der Abweichung leicht transparent dargestellt wird, und zwar in beiden Richtungen. Bisher bekomme ich nur 3 Linien hin die Normale und die beiden Standardabweichungen +/- Wichtig ist außerdem, das der Abstand der jeweiligen x-Werte eingehalten wird

Ich habe hier auch noch mal die Werte!

...zur Frage

=WENN()-Funktion mit 3 Möglichkeiten zur ausgabe. Wichtig: In der Formel darf nur einmal auf eine Zelle referiert werden.Hilfe bei Excel?

Die Funktion =WENN(B2=1;"Hallo";WENN(B2=2;"Tschau";"GrüßGott") geht zB nicht weil ich mich hier zweimal auf eine Zelle beziehe!

...zur Frage

Wenn A1=A2="X" Dann "Ja" sonst "nein" Excel Wenn dann formel?

Hallo ich möchte ein bisschen mit Excel rumprobieren und komme grad nicht weiter. Ich möchte ein Tikatoospiel erstellen die Frage ist nur wie sage ich das wen A1 und A2 und A3 X ist gewonnen wenn nicht leer machen kann. =WENN((A1=B2=C3)="X";"Gewonnen";"…") =WENN((A1&B2&C3)="X";"Gewonnen";"…") =WENN(A1=B2=C3="X";"Gewonnen";"…") =WENN(A1&B2&C3="X";"Gewonnen";"…") gehen alle nicht

...zur Frage

Summe entweder oder

Bitte folgende Formale mitteilen:

Wenn Zelle B1 mit einem % Wert gefüllt dann summiere Summe A1 in Zelle C1, wenn Zelle B2 mit einem % Wert gefüllt dann summiere Summe A1 in Zelle C1mit.

...zur Frage

Wo finde ich Textkombinationen bei Excel?

Ich suche eine Formel. Wo ich verschiedene Textkombinationen aus einen Kästchen suchen kann. Wie z.B

A1, B2, C3, D4, F7, H3, E1 diese Informationen stehen in einen Kästchen.

Jetzt soll die Formel die Kombination A1 und C3 zählen =1 Dann z.B B2 und C3 =1 Oder H3 und D4 oder E1 =1 Oder B2 und D4 und F7 und H3 =1 Oder B2 oder F7 oder X0 =0

Wobei die Informationen in den Kästchen immer unterschiedliche Reihenfolge hat. Z.B H3, E1, F7, D4, A1, C3, B2

Hat jemand eine Lösung

Vielen Dank im voraus

...zur Frage

Was möchtest Du wissen?