【説明画像あり】エクセルで支払計算書の作成について

質問

タイトルの件、下記の画像を利用して、説明します。

手順１：図１の黄色セルに下記の情報を入力します。

■現在残高：1000000　　■初回支払：平成23年1月　　

■最終支払日：エクセル関数計算　■支払回数：？？？

■月額金額【初回月以降】\66,600　■ボーナス：６月、\１00,000　１２月、\１00,000

すると、支払回数を自動計算をしてくれる。答えは、■支払回数：１２回、初回支払額：67,400

さらに、図2が、図３のとおり、別シートに自動的に、支払表が作成される。

【参考】
●B3セル関数：=TEXT(DATE(YEAR(B2&"1日"),MONTH(B2&"1日")+B4-1,25),"ggge年m月d日")　　
　教えてGOO回答者様に、教えていただきました。

●初回金額は、月額金額【初回以上】の金額となる。

回答No.6

KURUMITO · ベストアンサー

回答No5です。
こちらで試験しましたが問題はありませんでした。途中までが正常に表示されて最後のあたりで表示がへんになってしまうなどといったことは式の上からは考えられません。
式が途中で正常に入力されなかったたことによるものでしょう。2行目に入力した式はかなりの下方まで十分に最初からオートフィルドラッグしておくのがよいでしょう。
なお、D2セルの表示が異常になった原因はE1セルに文字などが入力されている場合です。E1セルは空の状態にしておいてください。

回答No.5

KURUMITO

回答No4です。
E1セルは空の状態で式を立てていましたのでD2セルへの式はそのような式になっていますが次の式で結構です。

=IF(E2="","",IF(ROW(A1)=1,E2+Sheet1!B$1-MAX(E:E),E2))

補足

いつもご回答ありがとうございます。
上記D2の回答は利用せずに、１つ前の回答で数式を作らせて頂きました。
いくつか事例を試している内に、下記の事例の場合にエラーが表示される事がわかりました。

【ケース１】
月額４０，０００円　ボーナス６月【１００，０００】、１２月【１００，０００】
エラー内容：回数→１８回の行が空白となり、計算されない。
１９回の金額欄が、＃VALUEになる。

【ケース2】
月額２０，０００円　ボーナス６月【１００，０００】、１２月【１００，０００】
エラー内容：回数→30回の行が空白となり、計算されない。
31回の金額欄が、＃VALUEになる。

お時間がございましたら、検証をお願いします。

回答No.4

KURUMITO

回答No1です。前回にはシート2のD列の式を示していませんでしたね。失礼しました。ところで追加ご質問の件ですがシート2でのE列での作業列を利用して必要以上に下行への回数や年、月などの表示をしないで済むようにすることができました。それぞれの入力の式を以下にまとめます。

シート1での入力の式
B3セルには
=TEXT(DATE(YEAR(B2&"１日"),MONTH(B2&"１日")+B4-1,25),"ggge年m月d日")
B4セルには
=INDEX(Sheet2!A:A,MATCH(MAX(Sheet2!E:E),Sheet2!E:E,0))
B5セルには
=Sheet2!D$2

シート2での入力の式、いずれも下方にオートフィルドラッグします。
A2セルには
=IF(E2="","",ROW(A1))
B2セルには
=IF(E2="","",TEXT(DATE(YEAR(Sheet1!$B$2&"１日"),MONTH(Sheet1!$B$2&"１日")+A2-1,25),"e"))
C2セルには
=IF(E2="","",MONTH(DATE(YEAR(Sheet1!$B$2&"１日"),MONTH(Sheet1!$B$2&"１日")+A2-1,25)))
D2セルには
=IF(E2="","",IF(ROW(A1)=1,E2-E1+Sheet1!B$1-MAX(E:E),E2-E1))
作業列のE2セルには
=IF(MAX(E$1:E1)+Sheet1!B$6+IF(MONTH(DATE(YEAR(Sheet1!$B$2&"１日"),MONTH(Sheet1!$B$2&"１日")+ROW(A1)-1,25))=Sheet1!B$7,Sheet1!B$8,IF(MONTH(DATE(YEAR(Sheet1!$B$2&"１日"),MONTH(Sheet1!$B$2&"１日")+ROW(A1)-1,25))=Sheet1!C$7,Sheet1!C$8,0))>Sheet1!B$1,"",MAX(E$1:E1)+Sheet1!B$6+IF(MONTH(DATE(YEAR(Sheet1!$B$2&"１日"),MONTH(Sheet1!$B$2&"１日")+ROW(A1)-1,25))=Sheet1!B$7,Sheet1!B$8,IF(MONTH(DATE(YEAR(Sheet1!$B$2&"１日"),MONTH(Sheet1!$B$2&"１日")+ROW(A1)-1,25))=Sheet1!C$7,Sheet1!C$8,0)))

補足

いつも、ご回答ありがとうございます。

Sheet2のD2セル【初回金額】が【#value】となります。
ご教授いただいた、D2の関数の中にある
E1とは、作業列の項目【文字列】になりますが、これで、宜しいのでしょうか？？

D2=IF(E2="","",IF(ROW(A1)=1,E2-E1+Sheet1!B$1-MAX(E:E),E2-E1))

よろしくお願いします。

回答No.3

tom04

No.2です！
補足をいただきましたが・・・
「初回支払月」は任意入力となっていますが、
「初回支払額」（B5セル）には関数を入れていますので自動で表示されるはずです。

尚、この際ですので前回の数式で「最終支払日」のB3セルを配列数式にしていましたが、
作業用の列をよく見てみると配列数式にする必要はありませんでした。

B3セルは
=IF(COUNTBLANK(B1:B2),"",TEXT(DATE(MAX(Sheet2!B:B)+1988,INDEX(Sheet2!F2:F100,MATCH(MAX(Sheet2!H2:H100),Sheet2!H2:H100,0)),25),"ggge年m月d日"))

としてみてください。m(__)m

回答No.2

tom04

こんにちは！
横からお邪魔します。
今回は「残高」・「初回支払月」・「2回目以降の支払金額」・「ボーナス月（ボーナス金額）」だけを入力するわけですね？

一例です。↓の画像のようにSheet2に作業用の列を3列設けています。

Sheet2の作業列F2セルに
=IF(COUNTBLANK(Sheet1!$B$1:$B$2),"",TEXT(EDATE(Sheet1!$B$2,ROW(A1)-1),"m")*1)
としてオートフィルでずぃ～～～！っとかなり下までコピーしておきます。

G2セルは
=IF(Sheet1!B2="","",IF(COUNTIF(Sheet1!B7:C7,F2),HLOOKUP(F2,Sheet1!B7:C8,2,0)+Sheet1!B6,Sheet1!B6))
G3セルに
=IF(F3="","",IF(COUNTIF(Sheet1!$B$7:$C$7,F3),HLOOKUP(F3,Sheet1!$B$7:$C$8,2,0)+Sheet1!$B$6,Sheet1!$B$6))
（H2セルは空白のままにしておきます）
H3セルに
=IF(D3="","",H2+D3)
としてG3.H3セルを範囲指定　→　H3セルのフィルハンドルでダブルクリック！
これでとりあえず作業用の列は完成です。

Sheet2のA2セルは
=IF(D2="","",ROW(A1))
B2セルは
=IF(A2="","",TEXT(EDATE(Sheet1!$B$2,A2-1),"e")*1)
C2セルは
=IF(A2="","",F2)
とし、A2～C2セルを範囲指定　→　C2セルのフィルハンドルでオートフィルでずぃ～～～！っと下へコピー！

D2セルは
=Sheet1!B5
D3セルは
=IF(COUNTBLANK(Sheet1!$B$1:$B$2),"",IF(SUM($G$3:G3)+Sheet1!$B$6>Sheet1!$B$1,"",G3))
とし、D3セルをアクティブ　→　フィルハンドルでダブルクリック！
これでSheet2の方は完成です。

次にSheet1の方ですが、
B3セルに
=IF(COUNTBLANK(B1:B2),"",TEXT(DATE(MAX(Sheet2!B:B)+1988,INDEX(Sheet2!C1:C100,MAX(IF(Sheet2!C1:C100<>"",ROW(A1:A100)))),25),"ggge年m月d日"))
これは配列数式になってしまいますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
もしこの画面からSheet1にコピー＆ペーストする場合はB3セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキー＋Enterキーで確定！

B4セルは
=MAX(Sheet2!A:A)

B5セルは
=IF(COUNTBLANK(B1:B2),"",B1-MAX(Sheet2!H3:H100))

としてみてはどうでしょうか？m(__)m

補足

すいません、最初に入力する項目に、初回支払額は、入っていません。自動計算と考えています。
よろしくお願いします。

回答No.1

KURUMITO

関数で対応するとしたらシート2に作業列を作って対応することでしょう。
シート2では初めに回数のA列ではA2セルに次の式を入力して下方にオートフィルドラッグします。

=ROW(A1)

B2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",TEXT(DATE(YEAR(Sheet1!$B$2&"１日"),MONTH(Sheet1!$B$2&"１日")+A2-1,25),"e"))

C2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",MONTH(DATE(YEAR(Sheet1!$B$2&"１日"),MONTH(Sheet1!$B$2&"１日")+A2-1,25)))

A,B,C列は以前と違って数値がどこまでもドラッグした場所まで表示されます。できるだけ下の行までドラッグします。

作業列はE列でE2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(MAX(E$1:E1)+Sheet1!B$6+IF(C2=Sheet1!B$7,Sheet1!B$8,IF(C2=Sheet1!C$7,Sheet1!C$8,0))>Sheet1!B$1,"",MAX(E$1:E1)+Sheet1!B$6+IF(C2=Sheet1!B$7,Sheet1!B$8,IF(C2=Sheet1!C$7,Sheet1!C$8,0)))

なお、E列は作業列ですので表示が目障りでしたらE列を選択して右クリックし、「非表示」を選択すればよいでしょう。

そこで、シート1に戻ってB3セルには次の式を入力します。

=TEXT(DATE(YEAR(B2&"１日"),MONTH(B2&"１日")+B4-1,25),"ggge年m月d日")

B4セルには次の式を入力します。

=INDEX(Sheet2!A:A,MATCH(MAX(Sheet2!E:E),Sheet2!E:E,0))

B5セルには次の式を入力します。

=B6+B1-MAX(Sheet2!E:E)

これでお望みのデータが表示されることになります。

なお、シート2ではA,B,C列が金額で表示されている行よりもはるかに下の行まで表示されることになりますね。
これでは表としての見栄えがよくありませんので表示の必要のない下方の行は削除するか（この場合には金額の変更などがあると対応ができなくなります）あるいはセル自体の文字を白色などにしてあたかも数値がない状態にすることです。
そのためにはA,B,C列を選択してから「条件付き書式」を選択し、「数式を使用して・・・」で数式の入力の窓には次の式を入力し、「書式」では「フォント」のタブで「白色」を設定します。

=AND(ROW(A1)>1,$E1="")

補足

いつも、ご回答ありがとうございます。
やはり、必要な回数【事例：１２回】までしか、表示させないことは
不可能でしょうか？？

宜しくお願いします。