Formel zur Berechnung des stagnierenden Umsatzes über 12 Monate nach einer Verzögerung von einem Monat

Question

Die erforderliche Formel ist eineleichtkomplizierte Array-Formel.

Hier ist Ihre Tabelle mit der Formel, die die erwarteten Ergebnisse zeigt:

Diese Formel muss in das Array eingegeben AE6und dann nach unten und rechts, soweit erforderlich, ausgefüllt/kopiert und eingefügt werden):

{=SUM(IFERROR(INDEX(6:6,N(IF(1,COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1))+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R)))/$I$2,0))}

Erläuterung:

Die verschönerte Version der Formel lautet wie folgt:

{=
SUM(
  IFERROR(
    INDEX(
      (6:6),
      N(IF(1,COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1))
      +(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R))
    )/$I$2,
    0
  )
)}

Die Formel ist viel einfacher zu verstehen, wenn man bedenkt, dass bei einer Dauer von 12 Monaten und einer Verzögerung von einem Monat das zweite Argument des ersten INDEX()ungefähr folgendem entspricht:

COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-{1,2,3,4,5,6,7,8,9,10,11,12}-1

Die Formel funktioniert grundsätzlich durch die Generierung eines Arrays von Offsets, um auf die Bestellungen der vorherigen I2Anzahl von Monaten zuzugreifen, verzögert um I1Monate, relativ zur aktuellen Zelle.

Das Durchgehen der Formel AK6sollte das obige klarer machen:

COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))
→{1,2,3,4,5,6,7,8,9,10,11,12}
COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-{1,2,3,4,5,6,7,8,9,10,11,12}-$I$1
→ {37}-({31}-{6})-{1,2,3,4,5,6,7,8,9,10,11,12}-1
→{10,9,8,7,6,5,4,3,2,1,0,-1}
N(IF(1,{10,9,8,7,6,5,4,3,2,1,0,-1}))
→ N({10,9,8,7,6,5,4,3,2,1,0,-1})
→{10,9,8,7,6,5,4,3,2,1,0,-1}
{10,9,8,7,6,5,4,3,2,1,0,-1}+({10,9,8,7,6,5,4,3,2,1,0,-1}>=COLUMN($R:$R))
→ {10,9,8,7,6,5,4,3,2,1,0,-1}+({10,9,8,7,6,5,4,3,2,1,0,-1}>={18})
→ {10,9,8,7,6,5,4,3,2,1,0,-1}+{0,0,0,0,0,0,0,0,0,0,0,0}
→{10,9,8,7,6,5,4,3,2,1,0,-1}
INDEX((6:6),{10,9,8,7,6,5,4,3,2,1,0,-1})/$I$2
→ INDEX(6:6,{10,9,8,7,6,5,4,3,2,1,0,-1})/12→ {24000,0,20000,0,0,"Opportunity Name1","bWmd1","Col C val","Col B val","Col A val","Col A val",#VALUE!}/12
→{2000,0,1666.67,0,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}
SUM(IFERROR({2000,0,1666.67,0,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!},0))
→2666.67

INDEX(6:6,N(IF(1,expression)))ist ein erforderlicher Hack ^* , um Excel zu zwingen, ein Array für zurückzugeben, expressionda das zweite Argument von INDEX()standardmäßig als einzelner Wert ausgewertet wird. Die Verwendung von nur in würde zuINDEX(6:6,expression)AK6

INDEX((6:6),COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R)))
→ INDEX(6:6,37-(31-6)-COLUMN($A$6:$L$6)-1+(37-(31-6)-COLUMN($A$6:$L$6)-1>=18))
→ → →INDEX(6:6,12-1-1+(12-1-1>=18))
INDEX(6:6,10)
24000

da innerhalb eines Ausdrucks, der einen einzelnen Wert zurückgibt, die Spalte der ersten Zelle des Bereichs zurückgegeben wird.COLUMN(multi-cell-range)

Dies +(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R))gleicht die Lücke zwischen den Auftragstabellen für 2018 und 2019 aus (Spalte R). Beachten Sie, dass der N(IF(Hack in diesem Fall nicht erforderlich ist, da der zuvor verwendete Hack bereits eine Array-Auswertung des zweiten Arguments von erzwungen hat INDEXund die COLUMN()Funktion daher Arrays auswertet.

Die IFERROR()Funktion wird benötigt, wenn sich die Formel in einer Zelle nahe der linken Seite des Blattes befindet und dadurch entweder auf Text zugegriffen wird oder versucht wird, auf eine Zelle links von der Spalte zuzugreifen A.

Anmerkungen:

Die verschönerte Formel funktioniert tatsächlich, wenn sie eingegeben wird.
Die Klammern (6:6)in der verschönerten Version sind erforderlich, um zu erzwingen, 6:6dass das in einer eigenen Zeile bleibt.

Vorbehalte:

In den Zellen der n Spalten links von den Bestellungen dürfen keine Zahlen stehen.(wobei n durch den Wert in angegeben wird I2). Wenn dort Zahlen stehen, werden diese von der Formel unverändert in die Umsatzberechnung einbezogen.
Zwischen den Spalten „Umsatz Dezember 2018“ und „Umsatz Januar 2019“ darf keine Lücke bestehen.Die Formel kann geändert werden, um eine solche Lücke bei Bedarf zu ermöglichen.
Der Abstand zwischen den beiden Auftragstabellen ( R:R) muss genau eine Spalte breit sein. Andernfalls wird die Formel in ihrer jetzigen Form nicht funktionieren.
Die Lücke in der Tabelle zwischen den Aufträgen darf keine Zahlen enthalten. Andernfalls werden sie als zusätzliche Aufträge behandelt.

_{^* Die Erklärung, warum der Hack genau funktioniert, muss warten, bis ich es selbst herausgefunden habe ;-)}

Answer 1