支払の計算式について【説明画像あり】

Question

タイトルの件、下記の図を使って、質問させていただきます。

図１【入力シート】に下記の【入力情報－その1】もしくは、【入力情報－その2】を入力した時に↓↓

【入力情報－その1】
■現在残高：\1,000,000　■初回支払：平成23年1月　■支払回数：12回　　　　
■ボーナス月&金額：6月【\100,000】、12月【\100,000】

【入力情報－その2】
■現在残高：\1,000,000　■初回支払：平成23年1月　■月額金額【初回以降】:\66,600　
■ボーナス月&金額：6月【\100,000】、12月【\100,000】

図２【反映シート】が図３【反映シート】のようになる関数を教えて下さい。
関数を入れる場所は、反映シートのセル【A1～L13】【全部で36回まで、表示可能】です。

大変、お手数ですが、部分的でも良いので、ご存知の方は、教えて下さい。宜しく、お願いします。
自分で考えましたが、知識がなく、出来ませんでした。

【参考情報】
◆入力とは、任意の数字を入力するという意味

◆条件：初回金額は、月額金額【初回以降】以上の金額

◆月額金額&初回金額の、１００円未満は、切捨て【例：\66,666⇒\66,600】

◆図2or図3は、回数が、36回まで表示できる表を想定しています。

◆最終支払日：B3セルの関数：=DATE(YEAR(B2&"1日"),MONTH(B2&"1日")+B4-1,25)
　⇒これ以外の関数は分かりません。

◆【入力情報－その1】の計算方法

★回数が12回と分かっているため、ボーナス回数は2回と分かる。
　　月額金額：1,000,000【現在残高】-200,000【ボーナス金額：￥100,000×2】÷12=66,666.6666・・・⇒66,600【100円未満切捨て】
　　初回金額：1,000,000【現在残高】-200,000【ボーナス金額：￥100,000×2】-(66,600×11)12=67,400

◆【入力情報－その2】の計算方法・・・計算式は分かりません。１つ１つ順に考えました。

1月：66,600　　         累計 66,600
 　　2月：66,600             累計133,200
 　　3月：66,600             累計199,800
 　　4月：66,600             累計266,400
 　　5月：66,600             累計333,000
 　　6月：66,600  100,000 累計499,600
 　　7月：66,600             累計566,200
 　　8月：66,600             累計632,800
 　　9月：66,600             累計699,400
　　10月：66,600            累計766,000
　　11月：66,600            累計832,600
　　12月：66,600  100,000 累計999,200

★999,200は1,000,000に近い⇒差額は800⇒１月【初回金額】に加算⇒67,400【66,600＋800】

KURUMITO · Accepted Answer

シート２を４列で２行目から下方にデータを表示させるとしたら次のようにします。

シート１のB３セルに入力する式は変わりませんが、B５セルには次の式を入力します。

=ROUNDDOWN(B1-B6*B4-COUNTIF(Sheet2!C:C,B7)*B8-COUNTIF(Sheet2!C:C,C7)*C8+B6,-2)

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

=ROUNDDOWN((B1-COUNTIF(Sheet2!C:C,B7)*B8-COUNTIF(Sheet2!C:C,C7)*C8)/B4,-2)

シート２のA２セルには次の式を入力します。

=IF(ROW(A1)>Sheet1!$B$4,"",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)))

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

=IF(C2="","",IF(A2=1,IF(C2=Sheet1!$B$7,Sheet1!$B$8+Sheet1!$B$5,IF(C2=Sheet1!$C$7,Sheet1!$C$8+Sheet1!$B$5,Sheet1!$B$5)),IF(C2=Sheet1!$B$7,Sheet1!$B$8+Sheet1!$B$6,IF(C2=Sheet1!$C$7,Sheet1!$C$8+Sheet1!$B$6,Sheet1!$B$6))))

たくさんの回答をしてきました。これ以上は式の内容をよく理解できるようにしてご自分で努力してみてください。

tom04 · Answer

No.1・2です。

補足を読ませていただきました。
前回のSheet配置で、Sheet2のB列を和暦「H23」のような表示にしたいということですので・・・

一例です。
この際ですので、ついでといっては失礼ですがSheet2のA列の数式も少し手を加えてみました。

Sheet2のA2セルは
=IF(OR(Sheet1!$B$4="",ROW(A1)>Sheet1!$B$4),"",ROW(A1))

B2セルは
=IF(A2="","",TEXT(EDATE(Sheet1!$B$2,ROW(A1)-1),"ge"))

としてA2・B2セルを範囲指定し、B2セルのフィルハンドルで下へずぃ～～～！っとコピーしておきます。
特に36行と限定する必要はありません。
Sheet1の支払回数だけ表示されますのでしっかり下へコピーしておいても構いません。

これで希望に近い形にならないでしょうか？

尚、Sheet2のB2セルの数式は、セルの表示形式をユーザー定義から　ge　としておけば
=IF(A2="","",EDATE(Sheet1!$B$2,ROW(A1)-1))
でも大丈夫だと思います。m(__)m

KURUMITO · Answer

式は次のように一部訂正してください。
NAMEなどの誤りの表示は関数やSheet1!などのスペルが間違って入力されているときに表示されます。

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

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

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

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

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

=IF(C2="","",IF(A2=1,IF(C2=Sheet1!$B$7,Sheet1!$B$8+Sheet1!$B$5,IF(C2=Sheet1!$C$7,Sheet1!$C$8+Sheet1!$B$5,Sheet1!$B$5)),IF(C2=Sheet1!$B$7,Sheet1!$B$8+Sheet1!$B$6,IF(C2=Sheet1!$C$7,Sheet1!$C$8+Sheet1!$B$6,Sheet1!$B$6))))

なお、４列のみで下方に行に伸ばす場合でも上の式が成功しなければできません。

KURUMITO · Answer

回答No5でシート2のD2セルに入力する式は次のようにしてください。

=IF(C2="","",IF(A2=1,IF(C2=Sheet1!$B$7,Sheet1!$B$8+Sheet1!$B$5,IF(C2=Sheet1!$C$7,Sheet1!$C$8+Sheet1!$B$5,Sheet1!$B$5)),IF(C2=Sheet1!$B$7,Sheet1!$B$8+Sheet1!$B$6,IF(C2=Sheet1!$C$7,Sheet1!$C$8+Sheet1!$B$6,Sheet1!$B$6)))

A2セルが絶対参照の形になっていましたが相対参照の形に変更しました。

KURUMITO · Answer

回答No3,4です。
エクセル2010で試験していましたのでエクセル2002で試験してみました。
シート2のA2セルに入力する式が長くなって機能しないことがわかりますた。
それを解消するためにシート2では次のようにしてください。
A2セルには次の式を入力します。

=IF(INT(COLUMN(A1)/4)*12+MOD(ROW(A1)-1,12)+1>Sheet1!$B$4,"",INT(COLUMN(A1)/4)*12+MOD(ROW(A1)-1,12)+1)

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

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

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

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

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

=IF(C2="","",IF($A2=1,IF(C2=Sheet1!$B$7,Sheet1!$B$8+Sheet1!$B$5,IF(C2=Sheet1!$C$7,Sheet1!$C$8+Sheet1!$B$5,Sheet1!$B$5)),IF(C2=Sheet1!$B$7,Sheet1!$B$8+Sheet1!$B$6,IF(C2=Sheet1!$C$7,Sheet1!$C$8+Sheet1!$B$6,Sheet1!$B$6)))

その後にA2セルからD2セルまでを範囲として選択しP2セルまでオートフィルドラッグしたのちに下方にも13行までオートフィルドラッグします。

以上のようにシート2での作業を行ってください。そうすることで期待する結果がシート2に表示されるでしょう。

KURUMITO · Answer

回答No3です。
シート2が式を入れてオートフィルドラッグしてもエラーとなるとのことですが、こちらの提案したとおりにしているのでしょうか。A1セルに式を入力するのではなくA2セルに式を入力するのですが。
また、入力の式は複雑ですからこちらの書いた式そのものをコピーしてA2セルに貼り付け右横方向にオートフィルドラッグしたのちに下方の13行までオートフィルドラッグしてみてはいかがでしょう。
シート1での計算が正しく表示されないとのことですが、式の一部はシート2での表示の結果を受けて計算している式ですのでシート2の表示が正しくなければ正しい答えとはなりません。
具体的にどんな数値を入力したら正しい答えが得られないのかを教えてください。

KURUMITO · Answer

表を1列では分かり易いですね。とのことですがご質問のような表にしたいとの前提で式を作りましたので多少式は複雑になっています。
また、初回の支払いがボーナス月に当たった場合なども考慮すればそれでも式は複雑になります。
お尋ねの式は次のようになりますね。
シート1のB3セルには次の式を入力します。

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

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

=ROUNDDOWN(B1-B6*B4-(COUNTIF(Sheet2!C2:C13,B7)+COUNTIF(Sheet2!G2:G13,B7)+COUNTIF(Sheet2!K2:K13,B7)+COUNTIF(Sheet2!O2:O13,B7))*B8-(COUNTIF(Sheet2!C2:C13,C7)+COUNTIF(Sheet2!G2:G13,C7)+COUNTIF(Sheet2!K2:K13,C7)+COUNTIF(Sheet2!O2:O13,C7))*C8+B6,-2)

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

=ROUNDDOWN((B1-(COUNTIF(Sheet2!C2:C13,B7)+COUNTIF(Sheet2!G2:G13,B7)+COUNTIF(Sheet2!K2:K13,B7)+COUNTIF(Sheet2!O2:O13,B7))*B8-(COUNTIF(Sheet2!C2:C13,C7)+COUNTIF(Sheet2!G2:G13,C7)+COUNTIF(Sheet2!K2:K13,C7)+COUNTIF(Sheet2!O2:O13,C7))*C8)/B4,-2)

そこでシート2ですが1行目にはお示しのような項目名が有るとしてA2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方の13行目までオートフィルドラッグします。

=IF(MOD(COLUMN(A1),4)=1,IF(INT(COLUMN(A1)/4)*12+MOD(ROW(A1)-1,12)+1>Sheet1!$B$4,"",INT(COLUMN(A1)/4)*12+MOD(ROW(A1)-1,12)+1),IF(OFFSET(A2,0,-1)="","",IF(MOD(COLUMN(A1),4)=2,TEXT(DATE(YEAR(Sheet1!$B$2&"1日"),MONTH(Sheet1!$B$2&"1日")+OFFSET(A2,0,-1)-1,25),"e"),IF(MOD(COLUMN(A1),4)=3,MONTH(DATE(YEAR(Sheet1!$B$2&"1日"),MONTH(Sheet1!$B$2&"1日")+OFFSET(A2,0,-2)-1,25)),IF(MOD(COLUMN(A1),4)=0,IF(OFFSET(A2,0,-3)=1,IF(OFFSET(A2,0,-1)=Sheet1!$B$7,Sheet1!$B$8+Sheet1!$B$5,IF(OFFSET(A2,0,-1)=Sheet1!$C$7,Sheet1!$C$8+Sheet1!$B$5,Sheet1!$B$5)),IF(OFFSET(A2,0,-1)=Sheet1!$B$7,Sheet1!$B$8+Sheet1!$B$6,IF(OFFSET(A2,0,-1)=Sheet1!$C$7,Sheet1!$C$8+Sheet1!$B$6,Sheet1!$B$6))))))))

tom04 · Answer

No.1です！
補足を読ませていただきました。

確かに支払月より1か月多い月が表示されてしまいますね。
初回月が1回目の支払になりますので、単純に支払回数を足し算してはいけませんでした。
1回分をマイナスする必要があります。

B3セルの数式は
=IF(OR(B2="",B4=""),"",DATE(YEAR(B2),MONTH(B2)+B4-1,25))
といった感じにしてみてください。

検証せずに投稿してごめんなさいね。m(__)m

tom04 · Answer

こんばんは！
一例です。
↓の画像のようにSheet1を入力用Sheetとし、Sheet2に表示するようにしています。
尚、お示しのように同項目を3列で表示すると数式がややこしくなりますので、敢えて36行で各項目1列ずつ表示するようにしています。
そして、Sheet2には「累計」の列も入れてみました。

（1）Sheet1の「初回支払月」B2セルの表示形式はユーザー定義から　ggge年m月　としておき、入力はシリアル値で　2011/1/1　のように入力します。

（2）「最終支払日」のB3セルの表示形式は「日付」にしておき、
=IF(OR(B2="",B4=""),"",DATE(YEAR(B2),MONTH(B2)+B4,25))
という数式を入れています。

（3）「初回支払」のB5セル・「2回目以降」のB6セルはSheet2と連動しています。
B5セルは
=IF(COUNTBLANK(B1:B3),"",B1-(COUNTIF(Sheet2!C:C,B7)*B8+COUNTIF(Sheet2!C:C,C7)*C8+B6*(B4-1)))

B6セルは
=IF(COUNTBLANK(B1:B4),"",ROUNDDOWN((B1-(COUNTIF(Sheet2!C:C,B7))*B8-COUNTIF(Sheet2!C:C,C7)*C8)/B4,-2))

次にSheet2の数式になります。
A列は単純に1～36までの連番を入力しておきます。
B2セルは
=IF(OR(Sheet1!$B$4="",A2>Sheet1!$B$4),"",YEAR(EDATE(Sheet1!$B$2,ROW(A1)-1)))

C2セルは
=IF(B2="","",MONTH(EDATE(Sheet1!$B$2,ROW(A1)-1)))

として、B2・C2セルを範囲指定し、C2セルのフィルハンドルで最終行までオートフィルでコピー！

D2セルは
=IF(C2="","",IF(COUNTIF(Sheet1!$B$7:$C$7,C2),Sheet1!$B$5+HLOOKUP(C2,Sheet1!$B$7:$C$8,2,0),Sheet1!B5))

E2セルは単に
=D2

D3セルは
=IF(C3="","",IF(COUNTIF(Sheet1!$B$7:$C$7,C3),HLOOKUP(C3,Sheet1!$B$7:$C$8,2,0)+Sheet1!$B$6,Sheet1!$B$6))

E3セルは
=IF(D3="","",E2+D3)
という数式を入れ、D3・E3セルを範囲指定し、E3セルのフィルハンドルで最終行までオートフィルでコピーすると画像のような感じになります。

尚、EDATE関数を使用するに当たり当方使用のExcel2003以前のバージョンでは
メニュー　→　ツール　→　アドイン　→　「分析ツール」にチェックを入れておく必要があります。

以上、長々と書きましたがお役に立てば良いのですが・・・m(__)m

支払の計算式について【説明画像あり】

シート２を４列で２行目から下方にデータを表示させるとしたら次のようにします。

No.1・2です。

式は次のように一部訂正してください。

この回答への補足

回答No5でシート2のD2セルに入力する式は次のようにしてください。

回答No3,4です。

この回答への補足

回答No3です。

この回答への補足

表を1列では分かり易いですね。

この回答への補足

No.1です！

この回答への補足

こんばんは！

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング