☰

計算延遲 1 個月後 12 個月內固定收入的公式

tag-icon • tag-icon microsoft-excel worksheet-function
計算延遲 1 個月後 12 個月內固定收入的公式

我在設計一個公式時遇到困難，該公式使我能夠將收入延遲 1 個月，然後在 12 個月內保持收入不變來計算收入。

這是我的工作表：

工作表截圖

2018 年訂單的基礎資料位於儲存格中F6:Q9。

2019 年訂單的基礎資料位於儲存格中S6:AD9。

目前有 2018 年 3 月的訂單（單元格H6），收入概況應顯示1 個月的延遲，因此4 月份不會有收入，但從2018 年5 月開始，3 月份的20,000 英鎊訂單應開始反映1,667 英鎊（直到4 月）2019 年）。

該公式還需要考慮來自F6:Q9（2018 年訂單）和來自S6:AD9（2019 年訂單）的訂單。

在單元格中I1，我已輸入1（以反映一個月的延遲）。

在儲存格 中I2，我輸入了12（以反映收入需要持平的月數）。

收入概況從單元格開始AE6，這就是我無法確定要使用什麼公式的地方。

我嘗試使用偏移公式，但無法使其發揮作用。我不知道如何解決該問題。

答案1

所需的公式是輕微地複雜的數組公式。

這是您的電子表格，其中的公式顯示了預期結果：

工作表的螢幕截圖

公式需要透過陣列輸入AE6，然後根據需要向下和向右填充/複製貼上）：

{=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))}

解釋：

公式的美化版本如下：

{=
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
  )
)}

如果考慮到 12 個月的持續時間和 1 個月的延遲，第一個參數的第二個參數INDEX()大致相當於：

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

該公式的工作原理基本上是產生一系列偏移量，以存取相對於當前單元格I2延遲幾個月的前幾個月的訂單。I1


單步執行中的公式AK6應該會使上面的內容更加清晰：

  • 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)))是必需的 hack *以便強制 Excel 傳回一個數組，因為預設情況expression下，第二個參數INDEX()被評估為單一值。使用 just in會導致INDEX(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

由於在傳回單一值的表達式內，因此傳回該範圍的第一個儲存格的列。COLUMN(multi-cell-range)

+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R))針對 2018 年和 2019 年訂單表之間的差距進行了調整（列）R。請注意，N(IF(在這種情況下不需要 hack，因為先前使用的 hack 已經強制對第二個參數進行陣列評估，INDEX因此COLUMN()函數評估為陣列。

IFERROR()如果公式存在於靠近工作表左側的儲存格中，導致存取文字或嘗試存取列左側的儲存格，則需要該函數A。

筆記：

  • 如果輸入美化後的公式，它實際上會起作用。
  • 美化版本中的括號(6:6)需要強制6:6保持在自己的線上。

注意事項：

  • 訂單左側 n 列的儲存格中不能有數字（其中 n 由 中的值指定I2）。如果其中存在任何數字，則公式將按原樣將它們包含在收入計算中。
  • 2018 年 12 月收入和 2019 年 1 月收入列之間不能有差距。如果需要的話，可以修改公式以允許這樣的間隙。
  • 兩張訂單表之間的間隙 ( R:R) 必須恰好保持一列寬。否則，這個公式就會被破壞。
  • 訂單間表間隙不能包含任何數字。否則，將被視為追加訂單。

*這個黑客行為的確切原因的解釋必須等到我自己弄清楚之後才能解釋;-)

相關內容