[[20200710155832]]　『前受金管理について。』（どん兵衛）　ページの最後に飛ぶ

『前受金管理について。』（どん兵衛）

前受金残高一覧表を作っています。
四半期決算で仕訳を起こすので３ヶ月単位で数字を出します。

売上計上(前受金から売上への振替)のタイミングが契約により１ヶ月～１２ヶ月まであります。

基準日(可変入力)に応じて残高・3ヶ月後・6ヶ月後・・・を自動で算出する関数をどなたかご教授いただけますでしょうか。
開始日・終了日・売上額・計上間隔・入金日が手入力。それ以外は関数としたい。

(結果サンプル)
2020年6月開始、4年にわたり毎年6月に41,250円売上計上する。
　　　　　　　　　　　　　　　　　　　　　　　　　　基準(U1):20200630
　B列～(順に列)

 開始日   終了日  　期間 　 売上額　計上間隔 回数 済 残 　入金日　 前受金   残高  3ヶ月後 6ヶ月後 9ヶ月後 1年後
20200630 20240531  48ヶ月 　41,250  　12ヶ月　 4   1  3  20200428 165,000 123,750 123,750 123,750 123,750 82,500

＜使用 Excel：Office365、使用 OS：Windows10 ＞

 2月1日　前受金発生
 3か月ごとに売上へ振り替え
 ↓
 5/31、8/31、11/30・・・・

 このばあい、3/31時点で
 5/31に売上へ振り替える金額の内、2/1～3/31分
 は実現していると思いますが、5/31は未到来なので
 売上へは振り替えないですか？
 それとも経過分は売上へ振り替えますか？
（OK） 2020/07/10(金) 18:07

 ↑間違いがありました。

 2月1日　前受金発生
 3か月ごとに売上へ振り替え
 ↓
 4/30、7/31、10/31・・・・

 このばあい、3/31時点で
 4/30に売上へ振り替える金額の内、2/1～3/31分
 は実現していると思いますが、4/30は未到来なので
 売上へは振り替えないですか？
 それとも経過分は売上へ振り替えますか？
（OK） 2020/07/10(金) 18:09

説明不足で申し訳ありません。

例示いただいた場合ですと、2/1に入金し3/31時点で入金から1ヶ月経過してるけども、
売上計上時期が4/30なので1ヶ月経過とはなりません。

よって、3/31時点では「前受金」列および「残高」列は変わらずです。
ただし、「3ヶ月後」列は1回分減、「6ヶ月後」列は2回分減・・・となります。

時点(U1)を4/30とすることで1回目の振り替えとなり、「残高」列が
1回分減ります。
（どん兵衛） 2020/07/10(金) 20:34

 １．サンプルでは、
 　「基準(U1):20200630、開始日20200630」
 　　つまり、基準日と開始日が同じで、１回目が済となっているのに、

 　　追加説明では
 　「2/1に入金・・で、時点(U1)を4/30とすることで1回目の振り替えと」なるのは何故ですか？

 ２．入金日って、今回の計算に関係するんですか？

 ３．日付は、シリアル値ですか？　
 　　随所に20200630と言うような単なる値で表現されていますが、
 　　セルの書式でそう見せているんですか？

（半平太） 2020/07/11(土) 12:54

半平太さま

1.すみません。最初のサンプルとOK様のサンプルを混同させてしまったようです。
　追加説明は別サンプルとみてください。

2.入金日は、基準日が入金日以前だったら残高を0としたいので残高列には関係すると思います。

3.日付はシリアル値です。セルの書式設定で見せているだけです。

以上、よろしくお願いします。

（どん兵衛） 2020/07/11(土) 16:43

 ＞1.すみません。最初のサンプルとOK様のサンプルを混同させてしまったようです。
 ＞　追加説明は別サンプルとみてください。

 混同したと思ってないですが・・

 では、当初サンプル(計上間隔12ヶ月）で、

 １回目(初回）　の済(1)になるのは基準日がいつの時か → ？
 ２回目(次回) 　の済(2)になるのは基準日がいつの時か → ？
 ４回目(最終回）の済(4)になるのは基準日がいつの時か → ？

 正解を具体的に回答してください。

（半平太） 2020/07/11(土) 17:15

１回目(初回）　の済(1)になるのは基準日がいつの時か → 2020/6/30
２回目(次回) 　の済(2)になるのは基準日がいつの時か → 2021/6/30
４回目(最終回）の済(4)になるのは基準日がいつの時か → 2023/6/30

です。

（どん兵衛） 2020/07/11(土) 19:11

 ＞2.入金日は、基準日が入金日以前だったら残高を0としたいので残高列には関係すると思います。

 しばらく考えてみましたが、
 開始日より後に入金があるなんて事態はちょっと考えにくいので、
 開始日と基準日の関係だけを表にすればいいハズで、
 そこに入金日をどう絡ませればいいのか分からなかったです。

 ・・と言うことで、色々お聞きした挙句に申し訳ないですが、私は退散します。m(__)m

（半平太） 2020/07/11(土) 23:36

誤解を与えてしまったようですみません。
今回求めたいのは、基準日(可変入力)に応じて残高・3ヶ月後・6ヶ月後・・・を自動で算出する関数
なので、この部分においては入金日は関係ありません。
（どん兵衛） 2020/07/11(土) 23:49

 気を取り直して・・

 (1) 基準日より、受入日が後なら、残高が０、済も０
 (2) 受入日が基準日以前で、開始日が基準日より後なら、残高あり、済０

   　　基準日(残高０、済０)　　基準日(残高あり、済０)
  　　　　↓　　　　　　　　　　　↓
  　　　　↓　　受入日　　　　 　 ↓　開始日

 数式
 (1) D2セル =DATEDIF(EOMONTH(B2,-1),EOMONTH(C2,0)+1,"m")
 (2) G2セル =IF(MOD(D2/F2,1),"期間不整合",D2/F2)
 (3) H2セル =IF($U$1<J2,0,MIN(G2,IFERROR(ROUNDUP(DATEDIF(EOMONTH(B2,-1),EOMONTH(U$1,0)+1,"m")/F2,0),0)))
 (4) I2セル =IF($U$1<J2,0,G2-H2)
 (5) K2セル =E2*G2
 (6) L2セル =(G2-IFERROR(ROUNDUP(DATEDIF(EOMONTH(B2,-1),EOMONTH(U$1,{0,3,6,9,12})+1,"m")/F2,0),0))*E2*(J2<EOMONTH(U$1,{0,3,6,9,12}))

 それぞれ下へコピー

 ※M列以降は、L列から自動スピル。自信度90% →そちらで十分検証してください。

 <結果図>
  行 ____B____ ____C____ _____D_____ ___ E ___ ______ F ______ __G__ _H_  I  ___ J ___ ___K___ __ L __ ___M___ ___N___ __ O __ __ P __ ____U____
   1 開始日    終了日    期間(ヶ月)  売上額/回 計上間隔(ヶ月)  回数  済  残  入金日    前受金  残高    3ヶ月後 6ヶ月後 9ヶ月後 1年後   2020/6/30
   2 2020/6/30 2024/5/31     48     　41,250         12     　　4    1   3 　2020/4/28 165,000 123,750 123,750 123,750 123,750 82,500

（半平太） 2020/07/12(日) 07:48

せっかく作表いただいたのに申し訳ありません。
実はＬ列残高のあとに２列あり、かつ１年後のあとにも３カ月単位で5年後(60ヶ月後)まであります。
(掲示板領域の都合上簡略化してアップしました)

　L 　　　　M 　　　　　　　N 　　　　　O 　　　　P 　　 Q 　R 　S・・・　　　
残高　Q売上額(税抜)　Q売上額(税込)　　3ヶ月後　6ヶ月後　9か月後 1年後　1年3カ月後・・・

また、本表はＤＢ化しておりテーブルとして書式設定しています。
この場合、スピル機能がうまく働かないようです。
（どん兵衛） 2020/07/12(日) 15:15

 そうなんですか。

 自信度90%程度のもので再考する価値も感じないので、私の関与はここまでとします。

 他の回答者のレスをお待ちください。m(__)m

（半平太） 2020/07/12(日) 17:15

 >また、本表はＤＢ化しておりテーブルとして書式設定しています。 
 この場合、スピル機能がうまく働かないようです。

 テーブル内でスピルさせると，#SPILL!エラーになります
 テーブル内では使用できないということです。
 テーブルを参照した結果はスピルすることはできます
（NANA） 2020/07/12(日) 17:57

みなさま返信ありがとうございます。

実際のＤＢシートは150行・ＡＳ列まであり前受金残高を5年後まで出しております。とてもそこまでこの欄に書けないので質問内容をうまく伝えられずもどかしいです。

実際のExcelファイルをアップして質問することはいろいろと難しいのですかね。

Excelの内容をこの欄に文章に起こすのは難しいですわ(>_<)

（どん兵衛） 2020/07/12(日) 19:42

 >3ヶ月後 6ヶ月後 9ヶ月後
 なぜ全て残高額と同じなの？
 >毎年6月に
 表では計上間隔が12か月になっているのはどうして？
 >　L 　　　　M 　　　　　　　N 　　　　　O 　　　　P 　　 Q 　R 　S・・・　　　 
 > 残高　Q売上額(税抜)　Q売上額(税込)　　3ヶ月後　6ヶ月後　9か月後 1年後　1年3カ月後・・・ 
 冒頭の表にはQ売上額(税抜)はないけど追加したの？ましてや同じものが2たつ必要なの？
 基準(U1):20200630はここにないどうしてか。開始日と同一日付なら必要ないよ。

（NANA） 2020/07/12(日) 22:20

 >4年にわたり毎年6月に41,250円売上計上する。
 2年後、3年後、4年後・・・・の残高はいくらになる？
 8250、41250、3年後には0この金額で正しい？
（CNA） 2020/07/12(日) 23:02

NANA 様

 >3ヶ月後 6ヶ月後 9ヶ月後
 なぜ全て残高額と同じなの？

　→　本サンプルの計上間隔が12ヶ月だからです。12ヶ月後に1回分41,250減ります。

 >毎年6月に
 表では計上間隔が12か月になっているのはどうして？

　→　2020年6月に1回目の計上、12ヶ月後の2021年6月に2回目の計上となるので計上間隔は12ヶ月です。

 > 残高　Q売上額(税抜)　Q売上額(税込)　　3ヶ月後　6ヶ月後　9か月後 1年後　1年3カ月後・・・ 
 冒頭の表にはQ売上額(税抜)はないけど追加したの？ましてや同じものが2たつ必要なの？
 基準(U1):20200630はここにないどうしてか。開始日と同一日付なら必要ないよ。

　→　はい。元々、税抜き額と税込み額がありました。今回の求めたいセルとは関係ないかなぁと思い
　　　はしょりました。基準(U1):20200630はあります。必ず月末日となっていて、ここを変えることで
　　　諸数値を自動算出出来る表を目指しています。

CNA 様

 >4年にわたり毎年6月に41,250円売上計上する。
 2年後、3年後、4年後・・・・の残高はいくらになる？
 8250、41250、3年後には0この金額で正しい？

　→　まさにここの関数を求めていまして、基準(U1):20200630 に応じて出したいのです。　
　　　基準(U1):20200630 なら、2年後(20220630)には売上が3回到達するので残高は165,000-(41,250*3)=41,250 3年後(20230630)には売上が4回(最終回)到達するので残高は0になります。
（どん兵衛） 2020/07/13(月) 10:02

　>ましてや同じものが2たつ必要なの？
　税抜と税込でしたね。見落としていました。
　失礼しました。
（NANA） 2020/07/13(月) 10:53

 >まさにここの関数を求めていまして
 基準(U1):20200630 がなくても売上額と前受金が分かっていれば普通に四則 演算でできるような気がします。
 なぜなら開始日と同一だからです。基準というのは開始日から1年後ですから。基準を途中に入れているのはここから2年目という目印ですね。
 あと5年分のデータを教えてください。
 とりあえず今日はここまて゛
（NANA） 2020/07/13(月) 11:31

説明不足で申し訳ありません。
基準日は可変でして、別シートから月末日を少なくとも5年分は選択出来るように設計しています。

<結果図>

  行 ____B____ ____C____ _____D_____ ___ E ___ ______ F ______ __G__ _H_  I  ___ J ___ ___K___ __ L __ ___M___ ＿＿___＿N___ __＿＿ O __ __ P __＿＿Q ＿＿＿R＿____U____
   1 開始日    終了日    期間(ヶ月)  売上額/回 計上間隔(ヶ月)  回数  済  残  入金日    前受金  残高    Q税抜売上高　Q税込売上高　3ヶ月後 6ヶ月後 9ヶ月後 1年後   2020/6/30
   2 2020/6/30 2024/5/31     48     　41,250         12     　　4    1   3 　2020/4/28 165,000 123,750 　37,500 　　　41,250 　　123,750 123,750 123,750 82,500

1年3ヶ月後　1年6ヶ月後 1年9ヶ月後 2年後 2年3ヶ月後 2年6ヶ月後 2年9ヶ月後

82,500 82,500 82,500 41,250 41,250 41,250 41,250 0 ・・・

2年後は41,250 3年後以降は0になります。

DATEIF関数やらMAX関数やらMIN関数やらを駆使して基準日・開始日・終了日・計上間隔をみて期間を比較してとかぼんやりとはイメージ出来るのですが、それを論理的に数式に落とし込むのがいかにも難しいです(>_<)
（どん兵衛） 2020/07/13(月) 14:33

数式を模索中です。
ここで確認
表中の金額は期間４８ヶ月での設定ですか。または期間内での設定ですか。
3年後以降＝ 2023/6/30 の解釈でいいですか。
（NANA） 2020/07/13(月) 20:42

 >→まさにここの関数を求めていまして、基準(U1):20200630 に応じて出した いのです。
 1年後以降の残高を求めたいということですか。。
（せせ） 2020/07/13(月) 21:10

NANA 様

はい、表中のサンプルは48ヶ月です。

3年後以降＝ 2023/6/30 の解釈でいいですか。

　はい。その通りです。

せせ様

1年後以降だけではなく、3ヶ月単位で5年後までを出したいのです。
(保守料前受金は5年契約が多いから)
（どん兵衛） 2020/07/14(火) 13:19

  >はい、表中のサンプルは48ヶ月です。
 とすると４年後2023/6/30に0にならないとおかしいですよね。
 計算してみると3年後2023/6/30に0になりましたけど。
 どこか違いまいすか。

      |[O]     |[P]     |[Q]     |[R]      
 [1] |３ヶ月後|６ヶ月後|９ヶ月後|2021/6/30
 [2] |  123750|  123750|  123750|    82500
 [3] |        |        |        |         
 [4] |３ヶ月後|６ヶ月後|９ヶ月後|2022/6/30
 [5] |   82500|   82500|   82500|    41250
 [6] |        |        |        |         
 [7] |３ヶ月後|６ヶ月後|９ヶ月後|2023/6/30
 [8] |   41250|   41250|   41250|        0
 [9] |        |        |        |         
 [10]|３ヶ月後|６ヶ月後|９ヶ月後|2024/6/30
 [11]|        |        |        |         
 [12]|        |        |        |         
（NANA） 2020/07/14(火) 19:18

いいえ、違ってません。
作表いただいた通りで合ってます。

（どん兵衛） 2020/07/14(火) 23:12

 間違いがありました
 >>とすると４年後2023/6/30に0にならないとおかしいですよね。
 ４年後2024/6/30に0に訂正します。
 >作表いただいた通りで合ってます。 
 合っているとすれば３６ヶ月ですよね。終了日に対して1年早く終了しますよね。なぜですか。
 >４回目(最終回）の済(4)になるのは基準日がいつの時か → 2023/6/30
 これと関係あるんですか。
（NANA） 2020/07/15(水) 10:30

契約期間は4年(48ヶ月)なのですが、
計上月が年一の頭月なので最終年度の6月末にはもう振替計上が終わってるので前受金残高は0円となる、と表現すればよろしいのでしょうか。

契約期間：48ヶ月
契約金額：165,000
計上間隔：1年
計上月　：6月
計上単価：41,250

1回目計上：2020/6/30(2020/6/30～2021/5/31分)
2回目計上：2021/6/30(2021/6/30～2022/5/31分)
3回目計上：2022/6/30(2022/6/30～2023/5/31分)
4回目計上：2023/6/30(2023/6/30～2024/5/31分)

1年毎の契約と見なし、最初の月に12ヶ月分の売上を残りの11ヶ月分は未経過だけど先に計上する、と言うイメージでしょうか。

（どん兵衛） 2020/07/15(水) 14:02

 >作表いただいた通りで合ってます。 
 でいくと
 >4回目計上：2023/6/30(2023/6/30～2024/5/31分)
 は計上されないということになりますがどうでしょうか。

（NANA） 2020/07/15(水) 17:29

うーん。
最終計上月としては2023/6/30なんですけど、同時に同じ日に(売上計上となり前受金は解消されるので)前受金残高としては0になる。

前受金残高としては、基準日2023/5/31が前受金残高に数字が入る最終月になります。

（どん兵衛） 2020/07/15(水) 17:54

作表第2回

    |[O]     |[P]     |[Q]     |[R]     |[S]      
 [1] |３ヶ月後|６ヶ月後|９ヶ月後|12ヶ月後|　1回目計上：2020/6/30(2020/6/30～2021/5/31分) 
 [2] |  123750|  123750|  123750|  123750|         
 [3] |        |        |        |        |         
 [4] |３ヶ月後|６ヶ月後|９ヶ月後|12ヶ月後|　2回目計上：2021/6/30(2021/6/30～2022/5/31分) 
 [5] |  123750|  123750|  123750|   82500|         
 [6] |        |        |        |        |         
 [7] |３ヶ月後|６ヶ月後|９ヶ月後|12ヶ月後|　3回目計上：2022/6/30(2022/6/30～2023/5/31分) 
 [8] |   82500|   82500|   82500|   41250|         
 [9] |        |        |        |        |         
 [10]|３ヶ月後|６ヶ月後|９ヶ月後|12ヶ月後|　4回目計上：2023/6/30(2023/6/30～2024/5/31分)
 [11]|   41250|   41250|   41250|       0|

 1年間の残高で計算するとこのようになりますがいかがでしょうか。
 計上回数と期間とももに一致しますけど。
 これでNGだと私は退散します。

（NANA） 2020/07/15(水) 18:08

NANA 様

計上＝前受金残高消去、なので
最初に作っていただいた表で合っているのですよね。

     |[O]     |[P]     |[Q]     |[R]      
 [1] |３ヶ月後|６ヶ月後|９ヶ月後|2021/6/30
 [2] |  123750|  123750|  123750|    82500
 [3] |        |        |        |         
 [4] |３ヶ月後|６ヶ月後|９ヶ月後|2022/6/30
 [5] |   82500|   82500|   82500|    41250
 [6] |        |        |        |         
 [7] |３ヶ月後|６ヶ月後|９ヶ月後|2023/6/30
 [8] |   41250|   41250|   41250|        0
 [9] |        |        |        |         
 [10]|３ヶ月後|６ヶ月後|９ヶ月後|2024/6/30
 [11]|        |        |        |         
 [12]|        |        |        |

ちょっと自分でも到達点がわかんなくなってきてしまった感があるので
もう一度最初から頭の中を整理したいと思います。

最後までお付き合いいただきありがとうございました。

（どん兵衛） 2020/07/15(水) 20:16

 >→　本サンプルの計上間隔が12ヶ月だからです。12ヶ月後に1回分41,250減ります。
 作表第2回はこれを元にしました。
 >計上＝前受金残高消去、なので
 また意味不明なことが出てきましたね。後出し質問ですか。
 作表第2回は対応していないので不可ですか。これが正解だと思ったけど残念でした。
 >最初に作っていただいた表で合っているのですよね。
 私の問いに答えて合っていると答えたではないですか。人のせいにしないでください。
 最初からの出直し頑張ってください。
 では失礼します。
 返信は必要ありません。
（NANA） 2020/07/15(水) 21:21

お世話になります。

3ヶ月後の関数が皆様のおかげで出来たようなので結果報告です。6ヶ月以降は"3"を"6"に数値を変える。

=MAX(($G2-IFERROR(ROUNDUP(DATEDIF(EOMONTH($B2,-1),EOMONTH($U$1,3)+1,"M")/$F2,0),0))*$E2*($J2<EOMONTH($U$1,3)),0)

<結果図>

  行 ____B____ ____C____ _____D_____ ___ E ___ ______ F ______ __G__ _H_  I  ___ J ___ ___K___ __ L __ ___M___ ＿＿___＿N___ __＿＿ O __ __ P __＿＿Q ＿＿＿R＿____U____
   1 開始日    終了日    期間(ヶ月)  売上額/回 計上間隔(ヶ月)  回数  済  残  入金日    前受金  残高    Q税抜売上高　Q税込売上高　3ヶ月後 6ヶ月後 9ヶ月後 1年後   2020/6/30
   2 2020/6/30 2024/5/31     48     　41,250         12     　　4    1   3 　2020/4/28 165,000 123,750 　37,500 　　　41,250 　　123,750 123,750 123,750 82,500

あとはＭ列(Q税抜売上高)とＮ列(Q税込売上高)の関数を知りたいです。
よろしくお願いします。

（どん兵衛） 2020/07/16(木) 12:18

 >あとはＭ列(Q税抜売上高)とＮ列(Q税込売上高)の関数を知りたいです。
 Q税込売上高は売上額/回そのものではないですか。何を考えているんですか。
 Q税抜売上高は関数はありませんけど。あなたが何か品物を購入したときを考えてください。
 それに消費税がかさんされますよね。売上額/回が内税込みだったら別ですけど。
 3ヶ月後のむずかしい関数ができたのにどうしてこんな優しいことができないのですか。
（DON） 2020/07/16(木) 14:58

N2/1.1=Q税抜売上高
M2*1.1=Q税込売上高
猿でもわかるやろ
（hh） 2020/07/16(木) 17:32

すみません。
1ヶ月売上高が初回だけ違うケースが出てきてしまいました。
この場合のＭ列以降の残高を求める式が知りたいです。
よろしくお願いします。

(1)初回売上高と次回以降売上高が同じの場合
　 N2セル =MAX(($H2-IFERROR(ROUNDUP(DATEDIF(EOMONTH($B2,-1),EOMONTH($U$1,3)+1,"M")/$G2,0),0))*$F2*($K2<EOMONTH($U$1,3)),0)

<結果図>

行 ____B____ ____C____ ____＿_D＿_____ ___＿ E＿ __ ____ ＿F _____ _______G_______ _＿＿H＿ I ＿J ____K___ ___＿L＿___ ___M___ _＿N___ __＿ O __ __ ＿P __ ＿Q ＿＿＿R＿____U____

   1 開始日    終了日    期間(ヶ月)  初回売上 次回以降売上  計上間隔(ヶ月) 回数 済  残  入金日    前受金  残高   3ヶ月後 6ヶ月後 9ヶ月後  1年後         2020/9/30
   2 2021/4/30 2025/3/31     48     　19,030     15,510           1     　　48   0  48 2020/5/29 748,000 748,000 748,000 748,000 697,950 651,420 
（どん兵衛） 2020/07/17(金) 12:09

 この調子じゃ、迷宮入りしそう・・

 １．テーブルとして書式設定しているんじゃなかったですか？ 
 ２．ついでにお聞きしますが、開始日は必ず月末ですか？

（半平太） 2020/07/17(金) 14:12

半平太様

リアクションありがとうございます。

１．テーブルとして書式設定しているんじゃなかったですか？

  　 はい。
　　 なので3ヶ月後は、
　　 =MAX(($H2-IFERROR(ROUNDUP(DATEDIF(EOMONTH($B2,-1),EOMONTH($U$1,3)+1,"M")/$G2,0),0))*$F2*($K2<EOMONTH($U$1,3)),0)
  　 6ヶ月後は、
　　 =MAX(($H2-IFERROR(ROUNDUP(DATEDIF(EOMONTH($B2,-1),EOMONTH($U$1,6)+1,"M")/$G2,0),0))*$F2*($K2<EOMONTH($U$1,6)),0)

     と言うように○ヶ月後に併せて数字を変えた式を各列に入力しています。

 ２．ついでにお聞きしますが、開始日は必ず月末ですか？

　　はい。必ず月末日です。終了日もです。

よろしくお願いします。

（どん兵衛） 2020/07/17(金) 18:59

 かなり込み入ってます。そちらで十分検証願います。

 (1) D2セル =DATEDIF(EOMONTH(B2,-1),C2+1,"m")
 (2) H2セル =IF(MOD(D2/G2,1),"期間不整合",D2/G2)
 (3) I2セル =IF(U$1<K2,0,IF(EOMONTH(B2,D2-G2)<U$1,0,H2-J2))
 (4) J2セル =IF(U$1<K2,0,IF(EOMONTH(B2,D2-G2)<U$1,0,INT(IFERROR(MIN(DATEDIF(EOMONTH($U$1,1)+1,EOMONTH($B2,$D2)+1,"m"),D2),0)/$G2)))
 (5) L2セル =E2+F2*(H2-1)
 (6) M2セル =IF(H2=J2,L2,J2*F2)
 (7) N2セル =O2/1.1
 (8) O2セル =IFERROR(IF($U$1=B2,E2,IF(EOMONTH(B2,D2-G2)<U$1,0,IF(MOD(DATEDIF(B2+1,MIN(U$1,EOMONTH($B2,$D2-$G2))+1,"m"),G2)=0,F2,0))),0)

  (9) P2セル =IF(EOMONTH($U$1,3)<$K2,0,IF(EOMONTH($B2,$D2-$G2)<EOMONTH($U$1,3),0,INT(IFERROR(MIN(DATEDIF(EOMONTH($U$1,3+1)+1,EOMONTH($B2,$D2)+1,"m"),$D2),0)/$G2)*$F2+(INT(IFERROR(MIN(DATEDIF(EOMONTH($U$1,3+1)+1,EOMONTH($B2,$D2)+1,"m"),$D2),0)/$G2)=$H2)*($E2-$F2)))
 (10) Q2セル =IF(EOMONTH($U$1,6)<$K2,0,IF(EOMONTH($B2,$D2-$G2)<EOMONTH($U$1,6),0,INT(IFERROR(MIN(DATEDIF(EOMONTH($U$1,6+1)+1,EOMONTH($B2,$D2)+1,"m"),$D2),0)/$G2)*$F2+(INT(IFERROR(MIN(DATEDIF(EOMONTH($U$1,6+1)+1,EOMONTH($B2,$D2)+1,"m"),$D2),0)/$G2)=$H2)*($E2-$F2)))
 (11) R2セル =IF(EOMONTH($U$1,9)<$K2,0,IF(EOMONTH($B2,$D2-$G2)<EOMONTH($U$1,9),0,INT(IFERROR(MIN(DATEDIF(EOMONTH($U$1,9+1)+1,EOMONTH($B2,$D2)+1,"m"),$D2),0)/$G2)*$F2+(INT(IFERROR(MIN(DATEDIF(EOMONTH($U$1,9+1)+1,EOMONTH($B2,$D2)+1,"m"),$D2),0)/$G2)=$H2)*($E2-$F2)))
 (12) S2セル =IF(EOMONTH($U$1,12)<$K2,0,IF(EOMONTH($B2,$D2-$G2)<EOMONTH($U$1,12),0,INT(IFERROR(MIN(DATEDIF(EOMONTH($U$1,12+1)+1,EOMONTH($B2,$D2)+1,"m"),$D2),0)/$G2)*$F2+(INT(IFERROR(MIN(DATEDIF(EOMONTH($U$1,12+1)+1,EOMONTH($B2,$D2)+1,"m"),$D2),0)/$G2)=$H2)*($E2-$F2)))

 <結果図>
  行 ____B____ ____C____ _____D_____ ___ E ___ _____ F _____ _______G_______ __H__  I   J  ____K____ __ L __ ___M___ _____N_____ ____ O ____ __ P __ __ Q __ ___R___ ____S____ _T_ ____U____
   1 開始日    終了日    期間(ヶ月)  初回売上  次回以降売上  計上間隔(ヶ月)  回数  済  残  入金日    前受金  残高    税抜売上高  税込売上高  3ケ月後 6ケ月後 9ケ月後 12ケ月後      2020/9/30
   2 2020/6/30 2024/5/31     48        41,250        41,250       12            4    1   3 2020/4/28 165,000 123,750      0           0      123,750 123,750  82,500   82,500               
   3 2021/4/30 2025/3/31     48        19,030        15,510        1           48    0  48 2020/5/29 748,000 748,000      0           0      748,000 748,000 697,950  651,420

（半平太） 2020/07/18(土) 11:28

半平太様

複雑な関数を考案いただき誠にありがとうございます！
本当にすごいですね。師匠と呼ばせていただきたいです(*^^)
十分に検証させていただきます。

まずもって御礼申し上げます。

ありがとうございましたm(__)m
（どん兵衛） 2020/07/18(土) 20:26

 １．P2以下がごちゃついているので、再考しました。
 　　P2セル =IFS(EOMONTH($U$1,COLUMN(A1)*3)<$K2,0,EOMONTH($U$1,COLUMN(A1)*3)<$B2,$L2,TRUE,($H2-MATCH(EOMONTH($U$1,COLUMN(A1)*3),EOMONTH($B2,(ROW($Z$1:INDEX($Z:$Z,$H2))-1)*$G2)))*$F2)
 　　右にコピー

 ２．I列(済)の数式がおかしかったです。
 　　修正
 　　　↓
  　 I2セル =IF(U$1<B2,0,IF(EOMONTH(B2,D2-G2)<U$1,H2,H2-J2))

（半平太） 2020/07/20(月) 08:16

半平太様

お世話になります。
旧式ではうまくいきました。確かに長いですね。
実際のファイルは5年後までみていて、

A　B ～　　Z　　　　AA　　～　AS　
_ 行　　　3ヶ月後　6ヶ月後　　5年後
_　1
_　2
_　3

となっており、Z列を使用しています。
COLUMN関数と(ROW($Z$1:INDEX($Z:$Z　のところでうまく働かないようです。
ご教示のほど、よろしくお願いいたします。
（どん兵衛） 2020/07/20(月) 22:25

 ・・と言われましても、
 どう旨く行かないのか具体的に説明いただかないと
 こちらの環境で検討できません。

 因みに、Z列を使用していることは支障にならないと思っています。

（半平太） 2020/07/20(月) 22:44

お世話になります。
すみません、4行目のケースでP4セルが0となってしまうことが判明しました。
本来、296,568となります。
最初の式ではうまくいきます。

<結果図>

  行 ____B____ ____C____ _____D_____ ___ E ___ _____ F _____ _______G_______ __H__  I   J  ____K____ ＿__L＿__ ___M___ _____N_____ ____ O ____ __ P __ __ Q __ ___R___ ____S____ _T_ ____U____
   1 開始日    終了日    期間(ヶ月)  初回売上  次回以降売上  計上間隔(ヶ月)  回数  済  残  入金日    前受金  　残高    税抜売上高  税込売上高  3ケ月後 6ケ月後 9ケ月後 12ケ月後      2020/9/30
   2 2020/6/30 2024/5/31     48        41,250        41,250       12            4    1   3 2020/4/28 　165,000 123,750      0           0      123,750 123,750  82,500   82,500               
   3 2021/4/30 2025/3/31     48        19,030        15,510        1           48    0  48 2020/5/29 　748,000 748,000      0           0      748,000 748,000 697,950  651,420  
　 4 2016/4/30 2021/3/31     60       296,568       296,568        3           20   18   2 2016/5/31 5,931,360 593,136      0           0      　　　0   　　0   　　0  　　  0

以上、よろしくお願いします。
（どん兵衛） 2020/07/21(火) 11:00

 ＞4行目のケースでP4セルが0となってしまうことが判明しました。

 こちらでは、296,568になります。

 P4セルの数式をコピーして、そのまま(加工せずに）掲示板に貼り付けてください。

（半平太） 2020/07/21(火) 13:43

=IFS(EOMONTH($U$1,COLUMN(A4)*3)<$K4,0,EOMONTH($U$1,COLUMN(A4)*3)<$B4,$L4,TRUE,($H4-MATCH(EOMONTH($U$1,COLUMN(A4)*3),EOMONTH($B4,(ROW($Z$1:INDEX($Z:$Z,$H4))-1)*$G4)))*$F4)

になります。
（どん兵衛） 2020/07/21(火) 14:26

 こちらの数式と有為な差はないです。

 そうなると、入力データがこちらと違う可能性があります。

 どこか空いているセルに下式を入力して、

  =TEXTJOIN(",",FALSE,U1,B4:P4)

 これと全く同じ文字列が返るかチェックしてください。
 ↓
 44104,42490,44286,60,296568,296568,3,20,18,2,42521,5931360,593136,0,0,296568
（半平太） 2020/07/21(火) 14:56

半平太様

お世話になります。
文字列の各文字自体は出現しました。

ただ、実際のファイルは売上税抜数値だとか顧客名だとか列が他にもあるので、
他にも諸々の文字が出現しています。

よろしくお願いします。
（どん兵衛） 2020/07/21(火) 15:48

 ＞ただ、実際のファイルは売上税抜数値だとか顧客名だとか列が他にもあるので、
 ＞他にも諸々の文字が出現しています。

 と言われましても、
 売上税抜数値は0のハズですし、TEXTJOINの数式に顧客名の列なんて含まれていないですから、
 訳が分かりません。

 こちらは千里眼じゃないので、そちらの情報が伏せられている状況下では検討できないです。
 自力で解明してください。

（半平太） 2020/07/21(火) 16:19

半平太様

お世話になります。
承知しました。新しい関数の理解は今後の課題としたいと思います。
先に作成いただいた関数をそれぞれの列に合わせて修正し活用させていただきます。

改めまして、ありがとうございました。
（どん兵衛） 2020/07/21(火) 21:59

コメント返信：

[ 一覧(最新更新順) ]

[[20200710155832]] 『前受金管理について。』（どん兵衛） ページの最後に飛ぶ

[[20200710155832]]　『前受金管理について。』（どん兵衛）　ページの最後に飛ぶ