Ich versuche also, eine Gleichung so zu gestalten, dass sie mit einer variablen Anzahl von Zellen funktioniert. Ich habe also ein Blatt mit aktuellen und prognostizierten Kosten. Ich möchte, dass die Prognosezahl nur auf den aktuellen Kosten basiert. Dies wird durch ein ACT am Anfang des Monats angezeigt. Meine ursprüngliche Formel aus Sumifs/Countifs wurde abgelehnt, da sie einen Zirkelreferenzfehler erzeugte. Daher frage ich mich, ob es eine Möglichkeit gibt, Gleichungen dynamisch abhängig von einer anderen Zelle zu aktualisieren.
Ich würde die Verwendung dynamischer Bereiche lieber vermeiden, da es sich hier um etwa 400 Zeilen handelt, die aktualisiert werden müssen, und da die Bereiche meines Wissens nach einzeln hinzugefügt werden müssen.
Ein Beispiel für etwas, das darstellt, was ich mir von der Formel erhoffe: =Summe(A1:A(1+B1)), wobei der Bereich mit zunehmendem Wert von B1 zunimmt.
Beispieldaten:
Act Act Act Proj Proj
A: 1 2 3 2 2
B: 1 3 5 3 3
C: 4 5 6 5 5
- Projektion A: 2
- Projektion B: 3
- Projektion C: 5
Wenn ich dann die tatsächlichen Zahlen für den 4. Monat hinzufüge und „Proj.“ in „Ist.“ ändere, ändert sich der Bereich, auf dem Prognose A, Prognose B usw. basieren, von 3 auf 4, ohne dass ich weitere Eingaben mache.
Antwort1
Sie können damit OFFSET()einen Verweis auf einen X-mal-Y-Spaltenbereich erstellen, zum Beispiel:
=OFFSET(A1,0,0,10,10)erstellt eine 10x10-Referenz, die auf verweist A1:J10.
Sie können die fest codierten 10er in der obigen Formel durch ein ersetzen, COUNTIF()das angibt, auf wie viele Zeilen und Spalten sich der Bereich beziehen soll.
Um einen Zirkelfehler zu vermeiden, können Sie versuchen, AltMMDmit dieser Formel einen benannten Bereich () zu definieren und den benannten Bereich überall dort zu verwenden, wo Sie die Formel verwenden würden.
Alternativ können Sie eine Textzeichenfolge mit der X-mal-Y-Referenz erstellen, indem Sie OFFSET()die Parameter Breite und Höhe (also die 10er in der obigen Formel) weglassen und stattdessen die erste und letzte Zelle in einem Bereich suchen. Schließen Sie diese dann in einen CELL("address",...)Aufruf ein, der ihre Adresse zurückgibt.(NB: Wenn Ihr Startpunkt $A$1 ist, müssen Sie nur die Endzelle finden).
Verketten Sie diese mit einem :dazwischen und wickeln Sie dann alles um ein , INDIRECT()wo immer Sie es verwenden müssen. Zusammengefasst, unter der Annahme eines fest codierten Starts bei $A$1:
=INDIRECT("Sheet1!$A$1:"&CELL("address",OFFSET(Sheet1!$A$1,COUNTIF(..x-rows..),COUNTIF(..y-cols..))
COUNTIF()Logik zum Ermitteln der Anzahl der verbleibenden Zeilen und Spalten als Übung für den Leser