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

質問

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

図１【入力シート】に下記の【入力情報－その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】

回答No.9

KURUMITO · ベストアンサー

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

シート１の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))))

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

お礼

いつも、圧倒的な知識で、教えていだきありがとうございました。

うまく、作る事ができました。本当に、ありがとうございました。

次回、質問は、希望、月額金額とボーナス月・金額を入力して、回数を割り出す
計算式を質問いたします。お時間がございましたら、アドバイスお願いします。

毎回、毎回、回答、いただくと、大変、お手数なので、お時間がある時がありましたら
お願いいたします。

どうも、ありがとう、ございました。

回答No.8

tom04

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

回答No.7

KURUMITO

式は次のように一部訂正してください。
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))))

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

補足

御回答ありがとうございました。

今、一発で成功しました。

お時間ございましたら、４列のみのも、教えて頂きます様
お願い致します。

また、行数を、かえるには、(現在１２行）です。
Ａ２セルの式の、＊１２を任意の数字に変更すれば、良いのでしょうか？

宜しく、お願いします。

回答No.6

KURUMITO

回答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セルが絶対参照の形になっていましたが相対参照の形に変更しました。

回答No.5

KURUMITO

回答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に表示されるでしょう。

補足

いつも、ご回答ありがとうございます。
ご指示のとおり、試しましたが、下記のエラーがでます。
エクセルのバージョンは2007or2003です。どちらでも、試しました。

　●A2セル→ＯＫです。回数が表示されます。Sheet1に、指定したとおりの回数が表に反映されます。　
　●B2セル→#NAME?　と表示される。　数式は、入力できます。
　●C2セル→#NAME?　と表示される。　数式は、入力できます。
　●D2セル→数式の修正が入り、下記の数式に書き換えられます。その後→#NAME?　と表示される。

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

ご提案ですが、表を今は、回数、年、月、金額が、１２行×３の表となっていますが
回数、年、月、金額は、全て１列表示されるものでも、ＯＫです。

以上、宜しくお願いします。

回答No.4

KURUMITO

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

補足

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

【Sheet2】について

エラーは下記のとおりです。

【Sheet2】の【A2】セルに数式をコピー＆ペーストすると、エラーが表示され、数式の入力自体できない。
エラー内容⇒入力した数式にはエラーがあります。
具体的事象⇒下から３行目の右から、見ていき、最初に出てくる、【OFFSET】が黒く反転表示される。

【考えられる事】
●コピペがうまく出来ていない。
⇒方法は、本ホームページより、数式をコピーし、Sheet2のA2セルにペースト

●エクセルのバージョンが2000である。
⇒本日、バージョン2007で、試してみます。

●シートの名前が違う
⇒入力シートをSheet1　、表のシートを　Sheet2　としています。

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

【Sheet1】について
●数式は全て、所定のセルに、コピペ完了しています。
●しかし、関数の答えが、下記の【■の項目】の情報では、【●の項目】が下記となる。
なお、■の情報は、本質問で、質問したとおりの情報です。

■現在残高：１，０００，０００、■初回支払：平成23年1月　■最終支払日：平成23年12月25日
■支払回数：12回　　■ボーナス：６月【１００，０００】、１２月【１００，０００】

●月額金額【初回月】：８３，７００
⇒答えは、６７，４００
●月額金額【初回以降】：８３，３００
⇒答えは、６６，６００

【考えられるエラー】
●Sheet2の数式が入力できていない
⇒表が完成していない

以上、大変、お手数ですが、宜しくお願いします。

回答No.3

KURUMITO

表を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))))))))

補足

御回答ありがとうございます。
質問ですが、Sheet1で、上記数式を入力すると、１回目はうまく計算されます。
しかし、２回目から、現在残高【B1セル】や、回数【B4】を変更していると
ボーナスの金額が加味した、計算ではなくなります。

また、Sheet2にへも、数式を入れましたが、反映されません。数式を入力するところで
エラーとなります。

私の検証不足ではありますが、何かヒントがありましたら、教えてください。
宜しくお願いします。

回答No.2

tom04

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

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

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

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

補足

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

もう１つ質問です。

Sheet２の西暦表示を平成に変更できますか？
例：２０１１→２３

よろしくお願いします。

回答No.1

tom04

こんばんは！
一例です。
↓の画像のように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

補足

tom04　様
ご回答、ありがとうございます。

B3セルの関数について質問します。
最終支払日が、１ヶ月少なく表示させたいのですが。

例：回数 1の場合:初回：H23.1⇒最終支払日は、H23. 1
　　回数12の場合:初回：h23.1⇒最終支払日は、H23.12

以上宜しくお願いします。

表を１列で、表示するのは、分かりやすいですね。
本当に、ありがとうございます。