Formeln variabel

Bild

Betrifft: Formeln variabel
von: Barbara
Geschrieben am: 04.06.2015 11:44:05

Hallo,
eine Frage zu Formeln (ohne VBA):
A1: B20+C20
A2: C20*E21
A3: was hier steht, soll die Bezüge der oben stehenden Formeln steuern.
Ich möchte durch eine Änderung nur in A3 erreichen, dass dann die Formeln zB so aussehen:
A1: K44+L44
A2: L44*N45
(gleich wie oben, nur etwas verschoben)
Meine Frage ist nun, was soll in A1, A2 und A3 stehen?
A1 und A2 sollen dann für den Anwender unveränderlich bleiben.
Ich hoffe, mein Problem verständlich beschrieben zu haben und bitte um konkrete Hilfe.
LG,
Barbara

Bild

Betrifft: ist möglich, allerdings ...
von: der neopa C
Geschrieben am: 04.06.2015 12:32:28
Hallo Barbara,
... ich würde Dir bei dem von Dir angegebenen Excellevel zu einem grundsätzlich anderen Herangehen bzgl. Excel-Auswertung raten.
In A3 muss die erste Zelladresse stehen; also z.B. B20 oder K44 oder ...
In A1: =INDIREKT(A3)+INDEX(A:ZZ;ZEILE(INDIREKT(A3));SPALTE(INDIREKT(A3))+1)
In A2: =INDIREKT(A3)*INDEX(A:ZZ;ZEILE(INDIREKT(A3))+1;SPALTE(INDIREKT(A3))+3)
Gruß Werner
.. , - ...

Bild

Betrifft: AW: ist möglich, allerdings ...
von: Barbara
Geschrieben am: 04.06.2015 13:26:51
Danke für Eure Antworten,
ein kleiner Einwand:
Wenn dazwischen Zeilen eingefügt oder gelöscht werden, ist der Text in A3 nicht mehr richtig.
Kann man so etwas auch irgend wie berücksichtigen?
(wenn in A3 stehen würde "=K44")

Bild

Betrifft: Das passiert bei meiner Variante nicht! owT
von: Luc:-?
Geschrieben am: 04.06.2015 13:57:07
:-?

Bild

Betrifft: da ist aber die Auswert. bzgl. Zeilen starr! owT
von: der neopa C
Geschrieben am: 04.06.2015 14:52:46
Gruß Werner
.. , - ...

Bild

Betrifft: Klar, dazu hat sie ja auch nichts geschrieben! orT
von: Luc:-?
Geschrieben am: 04.06.2015 16:01:55
Gruß, Luc :-?

Bild

Betrifft: Ihre 2 Beispiele zeigen mir aber anderes owT
von: der neopa C
Geschrieben am: 04.06.2015 16:16:59
Gruß Werner
.. , - ...

Bild

Betrifft: Eben, ZWEI Beispiele! ;-) owT
von: Luc:-?
Geschrieben am: 04.06.2015 16:26:36
:-?

Bild

Betrifft: aber eben beide mit der zusätzl. Angabe "zB"! owT
von: der neopa C
Geschrieben am: 04.06.2015 16:59:27
Gruß Werner
.. , - ...

Bild

Betrifft: Andere Zeilen kamen nicht vor, nur Spalten! owT
von: Luc:-?
Geschrieben am: 04.06.2015 18:19:34
;-) :-?

Bild

Betrifft: in der Aufgabenstellung ...
von: der neopa C
Geschrieben am: 05.06.2015 07:50:21
Guten Morgen Luc,
... stehen in den Beispielen immerhin 2x2 verschiedene Zeilennummern.
Ich verstehe wirklich nicht, warum Du das nicht so gesehen hast. Sicherlich aber nun nach Daniels Beitrag.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: in der Aufgabenstellung ...
von: Barbara
Geschrieben am: 05.06.2015 17:56:13
Die wahre Aufgabenstellung:
An verschiedenen Stellen einer Tabelle steht folgende Formelgruppe:
"=B20", darunter
"=B20+C20" und darunter
"=C20*E21" stehen.
Diese kopiere ich nun an verschiedene Stellen der Tabelle und ändere den ersten Zellbezug "=B20" auf zB "=B73", weil ich das eben so brauche. Auch die anderen beiden Formeln muss ich nun entsprechend umändern:
"=B73", darunter
"=B73+C73" und darunter
"=C73*E74" stehen.
oder
"=R24", darunter
"=R24+S24" und darunter
"=S24*U24"
Es besteht kein Zusammenhang zwischen Position der ersten Formel und der Zelle, auf die sie sich bezieht.
Also: Diese Formelgruppen treten an unterschiedlichen Stellen auf, und die jeweils erstgenannte Zelle (hier B20, B73, R24) ist unterschiedlich, aber die Abstände der bezogenen Zellen zueinander sind immer gleich, ebenso die Formeln selbst (immer untereinander).
Ich muss also jedes Mal, wenn ich eine neue Formelgruppe erstellen will, eine bestehende kopieren, an die neue Stelle einfügen und händisch anpassen.
Und das ist mühsam. So dachte ich, vielleicht gibt es einen Weg, die Formelgruppe zu kopieren und nur die erste Formel anpassen. Die anderen beiden Formeln sollen so sein, dass sie sich auf die richtigen Zellen beziehen. Allerdings soll dabei auch ein späteres Einfügen und Löschen von Zeilen (oder Spalten) das Ergebnis nicht verändern.
Lösung ohne Namensgebung und ohne VBA gesucht.
Excel-Version 2010 (nicht 2013), "FORMELTEXT" gibt es da nicht.
LG,
Barbara

Bild

Betrifft: AW: in der Aufgabenstellung ...
von: Alexander
Geschrieben am: 05.06.2015 18:17:41
Hallo Barabra,
Wie kopierst du deine Formel? Ich habe das eben mal ausprobiert und der Zellbezug wandert mit. Kopiert habe ich Strg+C und Eingefügt mit Strg+V. Sind deine Abstände zum Zellbezug immer gleich?
Evtl. könntest du noch die Funktion Bereich verschieben verwenden, so in der Art.
Tabelle1

 BCDEFGHIJ
822   52  
9   3    3
10         
11B8G8       
1247       
13615       

Formeln der Tabelle
ZelleFormel
B12=BEREICH.VERSCHIEBEN(INDIREKT(B11); 0;0;1;1)+BEREICH.VERSCHIEBEN(INDIREKT(B11); 0;1;1;1)
C12=BEREICH.VERSCHIEBEN(INDIREKT(C11); 0;0;1;1)+BEREICH.VERSCHIEBEN(INDIREKT(C11); 0;1;1;1)
B13=BEREICH.VERSCHIEBEN(INDIREKT(B11); 0;0;1;1)*BEREICH.VERSCHIEBEN(INDIREKT(B11); 1;3;1;1)
C13=BEREICH.VERSCHIEBEN(INDIREKT(C11); 0;0;1;1)*BEREICH.VERSCHIEBEN(INDIREKT(C11); 1;3;1;1)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß
Alex

Bild

Betrifft: AW: in der Aufgabenstellung ...
von: Barbara
Geschrieben am: 05.06.2015 19:49:26
Hallo Alexander,
danke, Dein Beispiel habe ich nachvollzogen, geht zwar so, aber ich habe auch noch geschrieben:
"Allerdings soll dabei auch ein späteres Einfügen und Löschen von Zeilen (oder Spalten) das Ergebnis nicht verändern."
Wenn ich also Zeile 10 lösche oder vor Spalte F eine neue einfüge, dann liefern die Zellen B11 und C11 in Deinem Beispiel falsche Bezüge.
Doch ich sehe dass die Lösung hier gefunden wurde: Antwort von Daniel am 05.06.2015 18:55:42
Dennoch danke.
LG,
Barbara

Bild

Betrifft: AW: in der Aufgabenstellung ...
von: Alexander
Geschrieben am: 05.06.2015 19:56:06
Hallo Barbara,
Die Lösung von Daniel und mir liefern das gleiche Ergebnis.
Ich hatte nur unsinnigerweise in der ersten Formel den Berich verschieben verwendet.
Ich habe Auch nicht =B8 eingegeben sondern nur B8.
Dennoch danke für deine Rückmeldung und es freut mich das du zum Ziel gekommen bist
Gruß
Alex

Bild

Betrifft: AW: in der Aufgabenstellung ...
von: Alexander
Geschrieben am: 05.06.2015 20:06:44
Sorry Barbara,
Ich muss meine Aussage revidieren.
Beim einfügen von Spalte haut es die Bezüge zusammen
Gruß
Alex

Bild

Betrifft: AW: in der Aufgabenstellung ...
von: Barbara
Geschrieben am: 05.06.2015 20:11:36
Sicher, aber da steht immer noch h8, wenn ich vor Zeile 8 eine Zeile einfüge, obwohl die Zelle nach h9 verschoben wurde.
Dennoch hat auch Deine Lösung meinen Excel-Horizont erweitert.
Danke, LG,
Barbara

Bild

Betrifft: AW: in der Aufgabenstellung ...
von: Daniel
Geschrieben am: 05.06.2015 18:55:42
Hi
schreibe in A3 die Formel:
=ZELLE("adresse";B20)
und in A1 und A2:
A1: =Indirekt(A3)+Bereich.Verschieben(Indirekt(A3);0;1)
A2: =Bereich.Verschieben(Indirekt(A3);0;1)*Bereich.Verschieben(Indirekt(A3);1;3)
dann musst du, wenn du die Formelgruppe A1:A3 woanders hin kopierst, nur den zellbezug in der Formel in A3 ändern.
Gruß Daniel

Bild

Betrifft: AW: in der Aufgabenstellung ...
von: Barbara
Geschrieben am: 05.06.2015 19:51:46
Hallo Daniel,
genau das funktioniert. Tolle Idee.
Diese Funktion "ZELLE" birgt so einige Möglichkeiten.
Vielen Dank nochmals.
LG,
Barbara

Bild

Betrifft: ich hatte vorgewarnt ...
von: der neopa C
Geschrieben am: 04.06.2015 14:48:28
Hallo Barbara,
... um es so universell zu gestalten, wie Du es jetzt offensichtlich haben möchtest, könnte man es nur mit INDEX() lösen. Dies wiederum schließt aus, dass Du eine Zelladresse in A3 angibst. Dann kannst Du aber so ohne weiteres (nur verschlüsselt) eine völlig Zelladresse mit nur einer Zelle (A3) angeben Du müsstes anstelle dessen mit einer weiteren arbeiten.
Besser ist es aber, von Haus mit INDEX()und VERGLEICH() zu arbeiten. Um das konkret zu gestalten, fehlt mir die Kenntnis Deiner Datenstruktur.
Gruß Werner
.. , - ...

Bild

Betrifft: Mit dem vorletzten Satz stellt du das bisher ...
von: Luc:-?
Geschrieben am: 04.06.2015 12:45:46
…erreichte Verständnis wieder infrage, Barbara; ;-]
ansonsten ggf so:
A1:=SUMME(INDEX(B20:Z20;1;A3);INDEX(B20:Z20;1;A3+1))
A2:=PRODUKT(INDEX(B20:Z20;1;A3+1);INDEX(B21:Z21;1;A3+3))
A3:1 bzw 10 oder was immer erforderlich ist
Gruß, Luc :-?

Bild

Betrifft: AW: Formeln variabel mit Anpassungs beim Löschen
von: Daniel
Geschrieben am: 04.06.2015 20:38:11
Hi
wenn du erwartest, dass sich die Zellbezüge beim Löschen mit anpassen, musst du so vorgehen
1. vergib im Namensmanger für jede Zelle, auf die sich bezogen werden kann einen Namen, beispielsweise
"KarlHeinz" für B20
"Elfriede" für K44
2. in die Zelle A3 schreibgst du den jeweiligen Namen, also "KarlHeinz" oder "Elfriede" als einfachen Text
3. in die Zellen A1 und A2 kommt folgendes Formelwerk:
A1: =Indirekt(A3)+Bereich.Verschieben(Indirekt(A3);0;1)
A2: =Bereich.Verschieben(Indirekt(A3);0;1)*Bereich.Verschieben(Indirekt(A3);1;3)
vorteil ist, die Namen wandern mit wenn du Zeilen einfügst oder löschst, so dass auch die Zellbezüge und die Formeln mitwandern.
Nachteil ist, dass du auch für jeden möglichen Zellbezug einen Namen definieren musst, dh wenns viele sind, wirds aufwendig.
du kannst bei diesem Formelwerk auch die Zelladresse direkt in A3 schreiben, dh "B20" oder "K44"
dann kannst du im Prinzip wieder auf jede Zelle damit zugreifen.
Allerdings ist dass dann wiederum fix und passt sich nicht an, wenn du Zeilen löschst oder einfügst.
Dann müssen wieder Karlheinz und Elfriede ran.
Gruß Daniel

Bild

Betrifft: AW: Formeln variabel mit Anpassungs beim Löschen
von: Barbara
Geschrieben am: 04.06.2015 22:45:31
Hallo
danke für Eure rege Teilnahme und Lösungsvorschläge.
Dass die Sache auch nach Einfügen oder Löschen von Zeilen funktionieren sollte, habe ich erst nicht dazu geschrieben, nicht um Euch zu ärgern, sondern, weil mir das erst beim Ausprobieren bewusst geworden ist.
Eine andere Lösung wäre wahrscheinlich, mit der Funktion "FORMELTEXT" an den Zellbezug in A3 zu kommen. Gibt es erst ab Version 2013, wie meine Internetrecherchen ergeben haben. Leider habe ich leider nur Version 2010. Auch wenn ich fälschlicherweise 2013 in meiner Frage oben angegeben habe.
Mit Namen zu arbeiten (danke Daniel) wäre natürlich auch eine Lösung, wenn auch mit erheblichen Aufwand für Namensgebung.
LG,
Barbara

Bild

Betrifft: AW: Formeln variabel mit Anpassungs beim Löschen
von: Daniel
Geschrieben am: 04.06.2015 23:34:59
Andere Lösungsmöglichkeiten würden sich vielleicht ergeben, wenn man das Problem und die Aufgabenstellung besser kennen würde.
Ich vermute mal, dass der Anwender denkt, "ich will jetz die Werte von Zelle K44 verarbeiten"
sonden " ich will die Werte der Gruppe A aus dem Monat Januar 2015" verarbeiten und die stehen halt nunmal in K44.
aber sinnvoller wäre es dann wenn man "2015", "Januar" und "A" eingibt und sich die Formel dann die passende Stelle in der Tabelle sucht.
ob sowas funktioniert, hängt halt von der Aufgabenstellung der Tabelle ab.
Gruß Daniel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Formeln variabel"