☰

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 ポンド (2019 年 4 月まで) に反映されるようになります。

F6:Q9この計算式では、（2018 年の注文）とS6:AD9（2019 年の注文）の両方の注文を考慮する必要があります。

セルにI1、（1 か月の遅延を反映するために）入力しました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
  )
)}

INDEX()12 か月の期間と 1 か月の遅延の場合、最初の引数の 2 番目の引数が次の式とほぼ同等であることを考慮すると、この式ははるかに理解しやすくなります。

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)))は、Excelに配列を返させるために必要なハックです。2expression番目の引数はINDEX()デフォルトで単一の値に評価されるためです。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。この場合、以前に使用したハックによって の 2 番目の引数の配列評価がすでに強制されており、関数は配列に評価されるため、N(IF(ハックは必要ないことに注意してください。INDEXCOLUMN()

このIFERROR()関数は、数式がシートの左側に近いセルに存在する場合に必要となり、その結果、テキストにアクセスするか、列の左側のセルにアクセスしようとすることになりますA。

ノート：

  • 整形された数式は、入力すると実際に機能します。
  • 整形されたバージョンでは、 を独自の行に残すために括弧(6:6)が必要です。6:6

注意:

  • 順序の左側のn列のセルに数字を入れることはできません。(ここで、n は の値によって指定されますI2)。そこに数字がある場合は、数式はそのまま、収益計算にそれらを含めます。
  • 2018 年 12 月の収益列と 2019 年 1 月の収益列の間にギャップがあってはなりません。必要に応じて、そのようなギャップを許容するように数式を変更できます。
  • 2 つの注文テーブル ( ) 間のギャップは、R:R正確に 1 列の幅である必要があります。そうでない場合、数式はそのままでは壊れます。
  • 注文間のテーブルギャップには数字を含めることはできません。そうでない場合は、追加の注文として扱われます。

*なぜハックが機能するのかという正確な説明は、まず私自身が理解するまで待たなければなりません ;-)

関連情報