-->Halli hallo,

weiß zufällig jemand ob und wie es möglich ist, daß ich in Excel einen flexiblen Zellbezug hinbekomme?

Also zum Beispiel Beziehe ich mich in B1 mit irgendeiner Formal auf die Zellen A1 bis A5.

Wäre es möglich, daß ich zum Beispiel in B2 ein Eingabefeld habe in dem ich Zahlen eingebe, die den Zellbezug von B1 verändern?
Also zum Beispiel steht eine 5 für A1 bis A5, eine 1 für A1 eine 8 für A1 bis A8 und eine 27 für A1 bis A27 usw.?

Oder braucht man dazu VBA?
Oder geht das gar nicht?

Danke für sachdienliche Hinweise.

Toby

-->hi toby,

bin zwar nich unbedingt ein guru, aber es gibt if-then- bzw. wenn-dann-formeln, damit müsste das gehen.
wenn feld a1=27 dann blablabla. mit vba kenn ich mich gar nich aus.
gruß

>Halli hallo,
>weiß zufällig jemand ob und wie es möglich ist, daß ich in Excel einen flexiblen Zellbezug hinbekomme?
>Also zum Beispiel Beziehe ich mich in B1 mit irgendeiner Formal auf die Zellen A1 bis A5.
>Wäre es möglich, daß ich zum Beispiel in B2 ein Eingabefeld habe in dem ich Zahlen eingebe, die den Zellbezug von B1 verändern?
>Also zum Beispiel steht eine 5 für A1 bis A5, eine 1 für A1 eine 8 für A1 bis A8 und eine 27 für A1 bis A27 usw.?
>Oder braucht man dazu VBA?
>Oder geht das gar nicht?
>Danke für sachdienliche Hinweise.
>Toby

-->es geht leider nicht um Inhalte von Zellen oder um"wenn ich dann sage, dann berechne das", sondern um den Zellbezug (!). Also ich schaue zum Beispiel nach den Maximum von 10 verschiedenen Werten (Zellen). Jetzt möchte ich über ein Eingabefeld das so steuern, daß ich das Maximum von x Werten (Zellen) betrachten kann.
Jetzt muss ich in der Praxis reingehen und den Bezug manuell ändern, entweder in der Formel oder dadurch, daß ich die Strichel-Linien weiter auf- oder zusammenziehe.

Toby

-->=MIN(INDIREKT("A1:A"&D1))

damit kann ich schon mal einen Bezug zwischen einem fixen Punkt (a1) und einer flexiblen Zelle in der Spalte A (Definition der Zeile über d1)"flexibilisieren".

Es ist aber noch nicht so ganz das, was ich mir vorstelle.
Ich will nicht einen fixen und einen"definierten" Punkt haben, sondern einen fixen und einen"errechneten" - also irgendwie so"A1:(A1"&d1))
aber das geht nicht.

Also ich will A1 bis A(A(1+d1)) definieren?!

Toby

-->
ganz einfach über die Funktion"indirekt".

du gehst wie folgt vor:

1) angenommen deine Zahlen stehen in A1 bis A10.
2) du möchtest jetzt mal die Zellen A1 bis A5, dann wieder A1 bis A9 oder was auch immer sonst verarbeiten, zB in einer Summenformel
3) die Standard-Summenformel in Excel für einen Bereich lautet bekanntlich

= summe(A1:A5) für unser nachfolgendes Beispiel

4) stattdessen schreibst du zB in B1 als Text"A1" und in B2"A5"

5) in deine Ergebniszelle schreibst du folgende Formel:

=SUMME(INDIREKT(B1):INDIREKT(B2))


Voilà!

in B1 und B2 kannst du jetzt beliebige Zelladressen reinschreiben, deine Formel verarbeitet das automatisch




>hi toby,
>bin zwar nich unbedingt ein guru, aber es gibt if-then- bzw. wenn-dann-formeln, damit müsste das gehen.
>wenn feld a1=27 dann blablabla. mit vba kenn ich mich gar nich aus.
>gruß
>>Halli hallo,
>>weiß zufällig jemand ob und wie es möglich ist, daß ich in Excel einen flexiblen Zellbezug hinbekomme?
>>Also zum Beispiel Beziehe ich mich in B1 mit irgendeiner Formal auf die Zellen A1 bis A5.
>>Wäre es möglich, daß ich zum Beispiel in B2 ein Eingabefeld habe in dem ich Zahlen eingebe, die den Zellbezug von B1 verändern?
>>Also zum Beispiel steht eine 5 für A1 bis A5, eine 1 für A1 eine 8 für A1 bis A8 und eine 27 für A1 bis A27 usw.?
>>Oder braucht man dazu VBA?
>>Oder geht das gar nicht?
>>Danke für sachdienliche Hinweise.
>>Toby

-->Hallo, Toby,

ea gibt hier mehrere Möglichkeiten ohne VBA, wenn Deine Formel einen Zellenbereich als Rückbaewert akzeptiert.

Die Lösung, die ich am festeten in Erinnerung habe, verwendet die Zellenfunktion (deutsche Anwenderumgebung) BEREICH.VERSCHIEBEN(RefZelle, Zeilenverschub, Spaltenverschub, Bereichszeilenanzahl, Bereichszahlenspalten)

Für Deine Aufgabe sind alle Größen fest, bis auf die Spaltenanzahl des Bereichs
RefZelle = A1
Zeilenverschub = 0 'zur Startzelle des Bereichs bezogen auf die Referenzzelle Spaltenverschub = 0 'zur Startzelle des Bereichs, bezogen auf Referenzzelle
Bereichszeilenanzahl = 1 'nur die Zeile 1 ist bildet den Bereich

Bereichszahlenspalten = flexible gem. Wert in Zelle B2 (Anzahl der Spalten)

<table><tr style="vertical-align:top; text-align:center;"><tr><td> </td></tr><tr><td><table border=1 cellspacing=0 cellpadding=0 style="font-family:Arial,Arial; font-size:10pt; padding-left:2pt; padding-right:2pt;"> <style type ="text/css"> th {font-weight:normal} </style> <colgroup><col width=30 style="font-weight:bold;"><col width=79.999998 ><col width=79.999998 ><col width=79.999998 ><col width=79.999998 ><col width=79.999998 ><col width=79.999998 ><col width=79.999998 ><col width=79.999998 ></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt;"><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >1</td><td style="text-align:right;">1</td><td style="text-align:right;">2</td><td style="text-align:right;">3</td><td style="text-align:right;">4</td><td style="text-align:right;">5</td><td style="text-align:right;">6</td><td style="text-align:right;">7</td><td style="text-align:right;">8</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >2</td><td style="text-align:right;">28</td><td style="text-align:right;">7</td><td style=""> </td><td style=""> </td><td style=""> </td><td style=""> </td><td style=""> </td><td style=""> </td></tr></table><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#FFFCF9;"><tr><td>Formeln der Tabelle</td></tr><tr><td><table style="font-family:Arial; font-size:10pt;">A1: =SPALTE()
B1: =SPALTE()
C1: =SPALTE()
D1: =SPALTE()
E1: =SPALTE()
F1: =SPALTE()
G1: =SPALTE()
H1: =SPALTE()
A2: =SUMME(BEREICH.VERSCHIEBEN<span style=' color:008000; '>(A1;0;0;1;B2)</span>)
</table></td></tr></table></td></tr><tr><td> </td></tr></tr></table> <span style="font-family:'Arial'; font-size:9pt;font-weight:bold;">Diagramm - Grafik - Excel Tabellen einfach im Web darstellen  <a style ="font-family:'Arial'; font-size:9pt; color:#FCF507; background-color:#1506F7; font-weight:bold;" href='http://www.haserodt.de/ejh_do/ex_jean_info.htm' target='blank'>  Excel Jeanie HTML  3.0    Download  </a></span>


Der einsatz einer derartigen Formel eignet sich gut bei gleitenden Durchschnitten, für die die Beobachtungslänge flexible gehlten werden soll.

Wenn Du jedoch eine Formel hast, die den Rückabewert eines Bereiches nicht erwartet, dann gibt es noch andere Lösungen.

Gruß,
Uwe

-->habe das vorhin übersehen: wenn du unbedingt mit denominatoren arbeiten willst, also"5 steht für A1:A3" oder"6 steht für A1:A7" dann schreibst du dir einfach eine kleine Tabelle, in der in einer Spalte der denominator und daneben der enstprechende Zellbezug steht, und übergibst in deiner Formel mithilfe der Funktion"sverweis"

ich sehe aber adhoc nicht, was das bringen soll, denn genausogut kannst du natürlich gleich mit unterschiedlichen"indirekt"-verweisen (also nicht immer auf B1 bzw. B2 sondern zB in der nächsten Formel auf C1 und C2) verweisen, und schreibst eben in diese Zellen deine unterschiedlichen Zellbezüge, die du verarbeiten möchtest

>
>ganz einfach über die Funktion"indirekt".
>du gehst wie folgt vor:
>1) angenommen deine Zahlen stehen in A1 bis A10.
>2) du möchtest jetzt mal die Zellen A1 bis A5, dann wieder A1 bis A9 oder was auch immer sonst verarbeiten, zB in einer Summenformel
>3) die Standard-Summenformel in Excel für einen Bereich lautet bekanntlich
>= summe(A1:A5) für unser nachfolgendes Beispiel
>4) stattdessen schreibst du zB in B1 als Text"A1" und in B2"A5"
>5) in deine Ergebniszelle schreibst du folgende Formel:
>=SUMME(INDIREKT(B1):INDIREKT(B2))
>
>Voilà!
>in B1 und B2 kannst du jetzt beliebige Zelladressen reinschreiben, deine Formel verarbeitet das automatisch
>
>>hi toby,
>>bin zwar nich unbedingt ein guru, aber es gibt if-then- bzw. wenn-dann-formeln, damit müsste das gehen.
>>wenn feld a1=27 dann blablabla. mit vba kenn ich mich gar nich aus.
>>gruß
>>>Halli hallo,
>>>weiß zufällig jemand ob und wie es möglich ist, daß ich in Excel einen flexiblen Zellbezug hinbekomme?
>>>Also zum Beispiel Beziehe ich mich in B1 mit irgendeiner Formal auf die Zellen A1 bis A5.
>>>Wäre es möglich, daß ich zum Beispiel in B2 ein Eingabefeld habe in dem ich Zahlen eingebe, die den Zellbezug von B1 verändern?
>>>Also zum Beispiel steht eine 5 für A1 bis A5, eine 1 für A1 eine 8 für A1 bis A8 und eine 27 für A1 bis A27 usw.?
>>>Oder braucht man dazu VBA?
>>>Oder geht das gar nicht?
>>>Danke für sachdienliche Hinweise.
>>>Toby

-->sorry, aber dieses Problem ist ja total simpel zu lösen:

=max(indirekt(B1):indirekt(B2)), wobei in B1 und B2 die Zellkoordinaten stehen, die die Endpunkte deines gewünschten Bereichs markieren. Also ZB"A1" und"A10" oder was auch immer.



>es geht leider nicht um Inhalte von Zellen oder um"wenn ich dann sage, dann berechne das", sondern um den Zellbezug (!). Also ich schaue zum Beispiel nach den Maximum von 10 verschiedenen Werten (Zellen). Jetzt möchte ich über ein Eingabefeld das so steuern, daß ich das Maximum von x Werten (Zellen) betrachten kann.
>Jetzt muss ich in der Praxis reingehen und den Bezug manuell ändern, entweder in der Formel oder dadurch, daß ich die Strichel-Linien weiter auf- oder zusammenziehe.
>Toby

-->Hallo, Toby,

den Ansatz von weisgarnix kannst Du derart einsetzen, dass Du die Zellenfunktion Adresse einsetzt:

=MIN($A$1:INDIREKT(ADRESSE(1;$B$2)))

Gruß,
Uwe

-->Jetzt habe ich das Problem gelöst.
Ich habe einmal eine Zellenhöhenzahl eingegeben - also wenn die Tabelle in
E20 anfängt, dann steht in d20 die 1 und in d21 die 2 und in d 22 die 3 usw., dann habe ich einen Eingabewert eingegeben, wo ich die Anzahl der zu suchenden Zellen eingebe und dann bilde ich die Differenz der beiden zum Beispiel in F8 und dann gebe ich für das minimum ein: =MIN(INDIREKT("C17:C"&F8))

Ist da ein Denkfehler und scheint es nur richtig? Ist es zu kompliziert?

Toby

-->dann legst du einen Paramter deiner Formel quasi als Konstante fest. Warum wolltest du das tun? Wer sagt, dass du nicht morgen deinen Ziel-Zellbereich irgendwo in der Mitte einer Zellenreihe beginnen möchtest?

Ich nutze grundsätzlich für fast alle Parameter von Formel den"indirekt" befehl, weil dir das maximale Flexibilität gibt. Inbesondere wenn du mit XL-Datenbankformeln arbeitest, wo bestimmte Argumente in bestimmter Form angeordnet sein müssen (also zB: NAME und"Müller" direkt untereinander). Wenn du diese Formel 50 mal kopierst, und die Bezüge nicht voll variabel definierst, dann wirst du seeeehr viel Zeit vor dem Computi verbringen...

>Hallo, Toby,
>den Ansatz von weisgarnix kannst Du derart einsetzen, dass Du die Zellenfunktion Adresse einsetzt:
>=MIN($A$1:INDIREKT(ADRESSE(1;$B$2)))
>Gruß,
>Uwe

-->>Halli Hallo,

leider funktioniert das so nicht.

Natürlich kann ich über indirekt(b1) einen Wert"a34" bei b1 abbilden. Das Problem ist aber, daß ich das ganze auf riesige Tabellen anwenden will und ich will es vermeiden, daß ich genau die Zellen angeben muss, er soll den Bereich berechnen.

-->Also, damit die Problematik klarer wird:

Nehmen wir an in der Spalte A stehen lauter Wochenkurse.
In der Spalte B will ich nun den Höchstkurs der letzten x Wochen anzeigen lassen.
Die Definition von x mache ich zum Beispiel in C1.

Das Problem ist nun, daß ich in B13 auf die Zellen A12:A(12-x) angezeigt haben will.
In B14 brauche ich dann A13:A(13-x)

In B123 steht dann der Höchstkurs von A122:A(122-x).

Der Startpunkt ist also nur in unserem Beispiel immer der gleiche - am Ende will ich aber die Formel"runterziehen"....

Und dann will ich bei C1 eben die Variable x eingeben, damit ich verschiedene Sachen durchprobieren kann.

Toby

-->>Natürlich kann ich über indirekt(b1) einen Wert"a34" bei b1 abbilden. Das Problem ist aber, daß ich das ganze auf riesige Tabellen anwenden will und ich will es vermeiden, daß ich genau die Zellen angeben muss, er soll den Bereich berechnen.

aber damit berechnest du doch den Bereich, oder nicht? willst du mir sagen, du willst nicht"A27" in das Eingabefeld eingeben, sondern nur"27"? wenn dem so ist, dann mach doch einfach folgendes:

=MAX(INDIREKT(C1):INDIREKT(C2&C3)), wobei du in C1 deine Anfangskoordinate als Text einträgst (zB"A1"), in C2 die Spalte deiner Endzelle (zB"A") und in C3 die Zeile deiner Zielzelle (zB"27"). Nota bene: die 27 mußt du auch als Text eingeben, also mit einem vorangestellten Hochkomma.

letzteres liefert exakt genau das, was du in deiner ersten Anfrage beschrieben hast, oder verstehe ich dein Problem noch immer nicht richtig?

-->Hi Uwe,

also mit Bereich.verschieben das habe ich auch schon gehört. Aber ich verstehe ide Parameter nicht.
Ich weiß einfach nicht, was das Excel da macht.

Wenn ich nun in B2 zum Beispiel die 3 eingebe, dann berechnet mir Excel die Summe von A1 bis C3 - also 6?!

Aber wie kann ich das auf mein Problem (siehe Posting von gerade eben) anwenden, wenn ich eben keinen festn Ref-WErt habe?

Toby

-->Mein Vorschlag wäre:
=MAX(INDIREKT("A"&C2):INDIREKT("A"&C3))
1."Max" ist klar
2. Dann"INDIREKT":
a)"A" bedeutet, dass Du auf Spalte A zugreifen willst (kannst Du auch noch variabel gestalten - s. unten)
b) c2 bedeutet: Der Beginn des zu betrachtenden Zeilenbereiches hast du in Zeile c2 festgelegt (Beisp.: Schreibst Du in C2 eine 2 beginnt die Betrachtung in Zeile 2)


3. der Doppelpunkt (:) macht deutlich, dass jetzt der zweite Teil des Zeilenbereiches, den Du mit"MAX" bertrachtest

4.) wieder"INDIREKT"
a) wie 2a)
b)"c3" bedeutet, das Ende des betrachteten Zeilenbereiches ist in Zeile c3 niedergeschrieben

Hinweis: Steht in c2 und/oder c3 nichts bekommst Du die Fehlermeldung"Bezug"

-->Du kannst"A" auch mit einer Variablen belegen z. B"d2" für für die Spalte.
In"d2" trägst Du dann die zu betrachtende Spalte ein.

Ergänung:
Mit"Daten""Gültigkeit""Liste" (oberste Leiste) kannst Du über Auswahl die Zellsteuerung bequem gestalten.

Diezu betrachtenden Zellen trägst du dann in unter Liste ein und nimmst in den Feldern c2/c3 (Zahlen für Zeilen) und d2 (Buchstaben für Spalten) über eine Auswahlbox die Zugriffssteuerung vor.

Hoffentlich hilft es.

viele Grüße
Kalle

-->sorry, habe gerade gesehen, dass weissgarnix dasselbe geschrieben hat

Wenn Du einen fixen Bereich hast (z. b. 6 Zeilen oder 60 Zeilen weiter) gibst du in meinem Beispiel in c3 immer"=c2+6" oder =§c2+60" an

Die Größe des Zeilenbereiches kannst du auch über die im letzten Posting geschilderte Möglichkeit steuern.

also in c3 steht"=c2+c4" und c4 steuerst Du wie beschrieben.

Bye
Kalle

-->Also ich mache es so:

=MAX(INDIREKT("F"&A14):INDIREKT("F"&B15))


Wobei in diesem Fall in A14 =Zeile() steht und in B15 steht: =A15-$D$5 und in D5 gebe ich die gewünschte ANzahl der Wochen ein.

So funktioniert es zumindest - ob es eleganter geht, weiß ich nicht.

Wahrscheinlich.

WEiß leider nicht, wie man so eine Tabelle hier rein stellt, sonst könnte ich es zu Anschauungszwecken machen....


Toby

--><table><tr style="vertical-align:top; text-align:center;"><tr><td> </td></tr><tr><td><table border=1 cellspacing=0 cellpadding=0 style="font-family:Arial,Arial; font-size:10pt; padding-left:2pt; padding-right:2pt;"> <style type ="text/css"> th {font-weight:normal} </style> <colgroup><col width=30 style="font-weight:bold;"><col width=79.999998 ><col width=79.999998 ><col width=79.999998 ></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt;"><td> </td><td>A</td><td>B</td><td>C</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >1</td><td style="">Wochenkurs</td><td style="">Min3</td><td style="text-align:right;">3</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >2</td><td style="text-align:right;">100,00</td><td style=""> </td><td style=""> </td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >3</td><td style="text-align:right;">100,00</td><td style=""> </td><td style=""> </td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >4</td><td style="text-align:right;">103,00</td><td style="text-align:right;">100,00</td><td style=""> </td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >5</td><td style="text-align:right;">99,91</td><td style="text-align:right;">99,91</td><td style=""> </td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >6</td><td style="text-align:right;">99,91</td><td style="text-align:right;">99,91</td><td style=""> </td></tr></table><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#FFFCF9;"><tr><td>Formeln der Tabelle</td></tr><tr><td><table style="font-family:Arial; font-size:10pt;">B1: ="Min"&C1
A2: =100
B2: =WENN(ZEILE<span style=' color:008000; '>()</span><$C$1+1;"";MIN<span style=' color:008000; '>(BEREICH.VERSCHIEBEN<span style=' color:#0000ff; '>($A2;0;-$C$1;$C$1;1)</span>)</span>)
A3: =A2*(1+ZUFALLSBEREICH<span style=' color:008000; '>(-3;3)</span>/100)
B3: =WENN(ZEILE<span style=' color:008000; '>()</span><$C$1+1;"";MIN<span style=' color:008000; '>(BEREICH.VERSCHIEBEN<span style=' color:#0000ff; '>($A3;0;-$C$1;$C$1;1)</span>)</span>)
A4: =A3*(1+ZUFALLSBEREICH<span style=' color:008000; '>(-3;3)</span>/100)
B4: =WENN(ZEILE<span style=' color:008000; '>()</span><$C$1+1;"";MIN<span style=' color:008000; '>(BEREICH.VERSCHIEBEN<span style=' color:#0000ff; '>($A4;-$C$1+1;0;$C$1;1)</span>)</span>)
A5: =A4*(1+ZUFALLSBEREICH<span style=' color:008000; '>(-3;3)</span>/100)
B5: =WENN(ZEILE<span style=' color:008000; '>()</span><$C$1+1;"";MIN<span style=' color:008000; '>(BEREICH.VERSCHIEBEN<span style=' color:#0000ff; '>($A5;-$C$1+1;0;$C$1;1)</span>)</span>)
A6: =A5*(1+ZUFALLSBEREICH<span style=' color:008000; '>(-3;3)</span>/100)
B6: =WENN(ZEILE<span style=' color:008000; '>()</span><$C$1+1;"";MIN<span style=' color:008000; '>(BEREICH.VERSCHIEBEN<span style=' color:#0000ff; '>($A6;-$C$1+1;0;$C$1;1)</span>)</span>)
</table></td></tr></table></td></tr><tr><td> </td></tr></tr></table> <span style="font-family:'Arial'; font-size:9pt;font-weight:bold;">Diagramm - Grafik - Excel Tabellen einfach im Web darstellen  <a style ="font-family:'Arial'; font-size:9pt; color:#FCF507; background-color:#1506F7; font-weight:bold;" href='http://www.haserodt.de/ejh_do/ex_jean_info.htm' target='blank'>  Excel Jeanie HTML  3.0    Download  </a></span>


Zellenformel in B2 schreiben und nach unten durch ziehen kopieren.

Gruß,
Uwe

-->war gerade dabei, dir genau das zu beschreiben. in der nächsten zeile setzt du auf dein D5 einfach einen Bezug =D$5 und dann kopierst du das alles beliebig rauf und runter...

bravo!


>Also ich mache es so:
>=MAX(INDIREKT("F"&A14):INDIREKT("F"&B15))
>
>Wobei in diesem Fall in A14 =Zeile() steht und in B15 steht: =A15-$D$5 und in D5 gebe ich die gewünschte ANzahl der Wochen ein.
>So funktioniert es zumindest - ob es eleganter geht, weiß ich nicht.
>Wahrscheinlich.
>WEiß leider nicht, wie man so eine Tabelle hier rein stellt, sonst könnte ich es zu Anschauungszwecken machen....
>
>Toby

-->Hallo Uwe,

wie du unten siehst, habe ich eine Lösung gefunden. Wahrscheinlich ist deine deutlich eleganter - aber ich verstehe es nicht, daher nehme ich lieber meine Lösung her - außer du sagst mir, daß das Humbug ist, was ich da gemacht habe?!


Toby