Excel Nachhilfe: Bezug auf Bereich (nicht einzelne Zelle) dynamisch berechnen

[Mickey Mouse]

ich habe seit bestimmt 10 Jahren kaum noch was in Excel gemacht und es scheint zu warm zu sein, ich stehe auf dem Schlauch...

nehmen wir diese Formel:
=@TREND(B1:B28;A1:A28;A30)

je nachdem wie viele Werte ich da schon habe, muss das "B1:B28" und "A1:A28" dynamisch sein.
das A30 (das in diesem Fall "statisch" ist) könnte ich mir mit ADRESSE(...) berechnen aber für den Matrix Bereich funktioniert das nicht.

also den Text "B1:B28" bekomme ich ohne Probleme hingedängelt (die 28 ergibt sich hir z.B. durch "=ANZAHL2(B:B)"), ich kann das auch in einer "Hilfszelle" zwischen speichern, aber wie bekomme ich diesen "berechneten" Text dann der Trend-Funktion untergejubelt?

wenn ich das richtig in Erinnerung habe, dann ist das ganz einfach aber ich komme nicht drauf und beim Suchen wird man mit unpassenden Antworten erschlagen.

 

[gammaray]

Du meinst vielleicht "INDIREKT"?

Hinweis: Das @ brauchst Du nicht.

 

[Mickey Mouse]

nee, indirekt() ist schon eine Referenz-Stufe zu kompliziert.

Beispiel:
=@TREND(B1:B28;A1:A28;A30)
fuktioniert ja wie gewünscht.
=ANZAHL2(B:B)
ebenfalls und liefert in diesem Fall hier (aktuell) 28 zurück.

was NICHT (so direkt) funktioniert aber was ich gerne hätte wäre so etwas hier:
=@TREND(B1:B`ANZAHL2(B:B)`;A1:A`ANZAHL2(B:B)`;A30)
wenn man versteht was ich meine...

indirekt würde mir ja nicht A1:A28 einsetzen, sondern den Wert aus z.B. der Zelle A28, wie gesagt, schon eine Ebene zu kompliziert, ich stecke noch viel weiter am Anfang fest

oder nochmal mir anderen Worten:
statt in der Formel direkt "A1:A28" einzugeben, möchte ich dafür eine Funktion haben, die mir irgendwie aus dem errechneten Wert 28 den Text "A1:A28" so zurückgibt, dass ich das der Trend Funktion übergeben kann.
das ist "so primitiv", dass ich gar nicht genau weiß wie ich s beschreiben soll...

 

[moneymaker4ever]

Ich bin kein Excel Profi, ADRESSE kann helfen, wenn ich dich richtig verstehe:

INDIREKT("B1:"&ADRESSE(ANZAHL(B:B);2))

Hier gibt ="B1:"&ADRESSE(ANZAHL(B:B);2) "B1:$B$28" wieder bzw bis zum letzten Element in B.

Für Spalte A müsste es dann INDIREKT("A1:"&ADRESSE(ANZAHL(A:A);1)) heißen.

 

[gammaray]

Dann suchst Du evtl. nur VERKETTEN? Oder fügst den Text selbst mittels "&" zusammen, z.B. ="A1:A"&ANZAHL(B:B)

Du brauchst dann aber trotzdem INDIREKT:
=TREND(INDIREKT("B1:B"&ANZAHL2(B:B));INDIREKT("A1:A"&ANZAHL2(B:B));A30)
oder in einen Textblock zusammengefasst
=TREND(INDIREKT("B1:B"&ANZAHL2(B:B)&";A1:A"&ANZAHL2(B:B));A30)

Eleganter geht es noch mit BEREICH.VERSCHIEBEN


P.S.: Deine Frage wäre im Office-Faden besser aufgehoben gewesen
https://www.computerbase.de/forum/forums/office-und-text.139/

 

[Mickey Mouse]

danke für die Tipps!
ich habe es jetzt mit der Holzhammer-Methode hinbekommen, obwohl mir ehrlich gesagt noch nicht so 100% klar ist, warum es überhaupt funktioniert, bzw. ob "INDIREKT" irgendwie zwei verschiedene Bedeutungen hat, je nach Aufruf?

Beispiele:
ich habe in einer Tabelle folgendes stehen:
A1: das ist A1
B1: A2
C2: =INDIREKT(B1)
dann wird mir (wie ich es von INDIREKT erwarte) in C2 "das ist A1" angezeigt. Indirekt liest also den Wert aus der angebenen Zelle (B1), interpretiert den als "Zellen-Adresse" (ist das laut aktuellem Excel Deutsch der Bezug?) und liefert als Ergebnis den Wert der so referenzierten Zelle ("das ist A1").

mein ursprüngliches Problem funktioniert jetzt und sieht so aus (Debug Holzhammer-Methode!!!):

D28: =@TREND(INDIREKT(F28);INDIREKT(G28);A28)

F28: =TEXTKETTE("B$1:B";TEXT(E28;"#"))
G28: =TEXTKETTE("A$1:A";TEXT(E28;"#"))
E28: =WENN(ZEILE()<ANZAHL2(B:B);ZEILE();ANZAHL2(B:B))
oder als Werte:
F28: B$1:B28
G28: A$1:A28
E28: 28

HIER liefert INDIREKT(F28) also NICHT das zurück, worauf der Inhalt von F28 zeigt (wie im einfachen Beispiel oben), sondern liefert "einfach nur" den Inhalt von F28 als Zellen-Adresse, was ich in diesem Fall ja so haben möchte.

 

[gammaray]

INDIREKT liefert immer den Inhalt der Zeichenkette. Das Ergebnis muss aber für die gewünschte Funktion einen Sinn haben, z.B. mit =INDIREKT(A1:A5) bekommt man nichts sinnvolles heraus, bei einer Einzelzelle =INDIREKT(A6) geht es aber. Für die TREND-Funktion brauchst Du eben einen Bereich als Argument, den Du mit INDIREKT adressieren kannst.

Die Textketten habe ich in meinem Beispiel gleich in die Formel integriert, Du hast es eben über separate Zellen "Zwischengespeichert".