【Excel】経理の表について

質問

Excel初心者です。
・・・・・・・・・・・・・・・・・・・・・・・・・
　契約開始　契約終了　契約金額　１0月　１1月
A
B
C
・・・・・・・・・・・・・・・・・・・・・・・・・
上記のような表を作成しているのですが、
契約開始が、2015/10/30
契約終了が、2015/11/21
といったように、契約期間によっては月がまたがることがあります。

その場合に、契約金額を入力したら、
「2015/10/30～2015/31」までの契約金額の「日割りの合計」
「2015/11/1～2015/11/21」までの契約金額の「日割りの合計」
が、「１0月」「１1月」の欄に出てくるようにしたいのですが、可能でしょうか？

・・・・・・・・・・・・・・・・・・・・・・・・・
【例】契約期間：2015/10/30～2015/11/2
　　　契約金額：10000円
　　　↓
　　　１０月：5000円　（2500円×2）
　　　１１月：5000円　（2500円×2）
・・・・・・・・・・・・・・・・・・・・・・・・・

説明が分かりにくくてすみませんが、分かる方いましたらご回答お願いします。

回答No.5

kagakusuki · ベストアンサー

・・・・・・・・・・・・・・・・・・・・・・・・・
　契約開始　契約終了　契約金額　１0月　１1月
A
B
C
・・・・・・・・・・・・・・・・・・・・・・・・・

という表のレイアウトに関してですが、「１0月」、「１1月」等の様に列ごとに月を指定してしまったのでは、A、B、Cの3件の内、もし契約開始日が10月ではない月になっているものがあった場合、例えば8月から契約が始まっている様な場合には、8月分や9月分を表示させる事が出来ません。
　それに、日割りですと「5000円　（2500円×2）」の様に割り切れる場合は良いのですが、例えば契約開始が2015/10/30、契約終了が2015/11/21、契約金額が10000円の場合は日割り金額が￥434.78260869565217391304347826・・・になってしまいますので、1円未満を切り捨てた場合、端数が発生して計算が合わなくなります。
　そこで契約金額の10000円が入力されているセルの右隣のセルに、文字列データで

10月 : ¥886
(¥434×2+端数¥18)

と1つのセル内に月や「日割り額」、日数等も合わせて表示する様にし、そのまた右隣のセルにも同様に

11月 : ¥9,114
(¥434×21)

という文字列を表示させる様にしては如何でしょうか？
　尚、日割り金額の端数を切り捨てた際に発生する差額は全て最初の月に合算するものとします。
　もしそれで宜しければ以下の様なやり方となります。

　御質問文には殿で田が何列の何行目に入力されているのか定かではありませんので、取り敢えず仮の話として「契約開始」という項目名が入力されているセルがB3セルであるものとします。
　その場合、まずE4セルに次の関数を入力して下さい。

=IF(IF(COUNT($B4,$C4,$D4)=3,AND($B4>1,$C4>=$B4),),TEXT($B4,"m月 : ")&TEXT($D4-INT($D4/(INT($C4)-INT($B4)+1))*MAX(INT($C4)-EOMONTH($B4,0),0),"¥#,##0;¥-#,##0")&CHAR(10)&TEXT(INT($D4/(INT($C4)-INT($B4)+1)),"(¥#,##0×;(¥-#,##0×")&MIN(EOMONTH($B4,0),INT($C4))-INT($B4)+1&"+端数"&TEXT($D4-INT($D4/(INT($C4)-INT($B4)+1))*(INT($C4)-INT($B4)+1),"¥#,##0);¥-#,##0)"),"")

　次に、F4セルに次の関数を入力して下さい。

=IF(IF(COUNT($B4,$C4,$D4)=3,AND($B4>1,$C4>=$B4),),IF(COLUMN()-COLUMN($E:$E)>DATEDIF(TEXT($B4,"yyyy/m"),TEXT($C4,"yyyy/m"),"m"),"",TEXT(EDATE($B4,COLUMN()-COLUMN($E:$E)),"m月 : ")&TEXT(INT($D4/(INT($C4)-INT($B4)+1))*(MIN(EOMONTH($B4,COLUMN()-COLUMN($E:$E)),$C4)-EOMONTH($B4,COLUMN()-COLUMN($E:$E)-1)),"¥#,##0;¥-#,##0")&CHAR(10)&TEXT(INT($D4/(INT($C4)-INT($B4)+1)),"(¥#,##0×;(¥-#,##0×")&MIN(EOMONTH($B4,COLUMN()-COLUMN($E:$E)),$C4)-EOMONTH($B4,COLUMN()-COLUMN($E:$E)-1)&")"),"")

　次に、E4セルとF4セルの書式設定の[配置]を[折り返して全体を表示する]に設定して下さい。
　次に、F4セルをコピーして、F4セルよりも右側にあるセル範囲に貼り付けて下さい。(全ての契約の中で最も長期に亘る契約期間内の全ての月のデータを表示させるのに十分な列数となるまで)
　次に、4行目の中の、E列から右手にあるセル範囲をコピーして、同じ列範囲の5行目以下に貼り付けて下さい。

　以上です。

お礼

kagakusukiさん

ご回答ありがとうございました。
こちらで作業を進めたいとおもいます。

丁寧なご説明のおかげで無事作業が完了しそうです。
また、画像も入れていただき、イメージがしやすく、助かりました。
本当にありがとうございました。

回答No.4

bunjii

>説明が分かりにくくてすみませんが、分かる方いましたらご回答お願いします。
分かり難いですね。
例では足掛け2ヶ月ですが開始月と終了月が共に日割りになっていますね。
足掛け3ヶ月のときについては考えなくて良いのですか？
2015/10/30～2015/12/20のようなときは11月分が1ヶ月分となりますので別に計算するのですか？
質問の【例】については次の数式で良いと思います。
A2セル=契約開始日=2015/10/30
B2セル=契約終了日=2015/11/2
C2セル=契約金額=10000
10月分=(EOMONTH(A2,0)+1-A2)*C2/(B2-A2+1)
11月分=(B2-EOMONTH(A2,0))*C2/(B2-A2+1)

お礼

bunjii さん
説明が下手にも関わらず、丁寧に回答していただきありがとうございます。
今後もご回答いただけたら嬉しいです。

本当にありがとうございました。

回答No.2

meitoku

契約開始　当月末　〇日　翌月開始　契約終了　〇日　契約金額　1日金額　10月　11月
として考えないと無理かと思います。
CAUNTA関数・日付けを数える関数等が必要です。
不要な項目（列）は非表示にすれば良いですね。
契約金額が異なる。1日金額が異なる。等だと手入力の部分が多くなりますね。

お礼

meitoku さん

ご回答ありがとうございます。
Excel自体得意ではないので、これから勉強していきたいと思います。

回答No.1

noname#249423

出来るかどうかで言えば出来るでしょう。
DATEDIF関数で日数をカウントすることが出来ますので契約金額をその求めた日数で按分してあげればよいです。

お礼

applepie1979 さん

ご回答ありがとうございます。
また、よろしくお願いいたします。