エクセル 該当する日付範囲への金額配賦方法

質問

初めての質問です。失礼な点があればお詫びいたしますので、ご教示下さい。
私の会社は、取引先に対してリベートを支払っています。支払いサイクルは顧客によってまちまちです。
例えば１0月１0日から11月１０日分販売に対してリベートを１０万円支払い
このデータはエクセルで手元にあります。
例
Ａ列　　　　Ｂ列　　　　　　　　Ｃ列　　　　　　　　　Ｄ列
顧客名　　対象期間（始）　　対象期間（終）　　　金額
なにわ　　　10/15/2010　　　　　11/10/2010　　　　　100,000
この内容を弊社の月次期間毎に割振る必要があります。
月次１　10/10-11/7　月次２　11/8/-12/5...
先程の例を月次単位で分けると
月次１　２４日分の　　88,889円　月次２　　3日分の　　　11,111円
と比率で計算すればなるのですが、これをエクセルで自動的に
計算したいと考えていますが、どうにも解決できていません。
何か良い方法があれば是非是非、教えていただけないでしょうか・
何方か、よろしくお願いいたします。

回答No.4

tom04

こんにちは！
すでに回答は出ていますが・・・
一例です。

↓の画像で説明します。
Sheet1のデータをSheet2に表示するようにしています。
Sheet1に作業用の列（月次毎の日数）を表示できればあとは簡単だと思います。
Sheet2のE～H列に月次の始と終を入力するようにしています。

かなり長い数式になってしまいますが・・・
Sheet1のE2セルに
=IF(AND(B2<Sheet2!$E$3,C2>Sheet2!$F$3),Sheet2!$F$3-Sheet2!$E$3+1,IF(AND(B2>=Sheet2!$E$3,C2<=Sheet2!$F$3),C2-B2+1,IF(AND(B2<Sheet2!$E$3,C2>=Sheet2!$E$3,C2<=Sheet2!$F$3),C2-Sheet2!$E$3+1,IF(AND(B2>=Sheet2!$E$3,B2<=Sheet2!$F$3,C2>Sheet2!$F$3),Sheet2!$F$3-B2+1,""))))

F2セルに
=IF(AND(B2<Sheet2!$G$3,C2>Sheet2!$H$3),Sheet2!$H$3-Sheet2!$G$3+1,IF(AND(B2>=Sheet2!$G$3,C2<=Sheet2!$H$3),C2-B2+1,IF(AND(B2<Sheet2!$G$3,C2>=Sheet2!$G$3,C2<=Sheet2!$H$3),C2-Sheet2!$G$3+1,IF(AND(B2>=Sheet2!$G$3,B2<=Sheet2!$H$3,C2>Sheet2!$H$3),Sheet2!$H$3-B2+1,""))))

という数式を入れ、E2・F2セルを範囲指定し、F2セルのフィルハンドルで下へコピーします。

最後にSheet2のB2セルに
=IF($A2="","",Sheet1!$D2*Sheet1!E2/SUM(Sheet1!$E2:$F2))
という数式を入れ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。

以上、長々と書きましたが、
参考になれば幸いです。m(__)m

お礼

画像まで付けていただき、ありがとうございます。
初心者の私でも、理解できる関数でここまでできるんですね！
自分も、もっと頭を使わなきゃと思いました。
作業シートと結果シートを分ける考え方も、非常に参考になります。
結果だけがきれいに印刷できるので、上司にも喜ばれそうです。

お忙しいところ、ご親切な回答ありがとうございました。

回答No.3

grumpy_the_dwarf

対象期間の初日と最終日が明らかなので、対象期間の日数は計算で
きますね。「最終日-(初日-1)」です。そうすると1日あたりの金額
は割り算で出ますね。

次に対象期間のうち月次1に含まれる日数を考えましょう。数直線を
引いてみると判りますが、これは「対象期間初日と月次初日のどち
らか遅い方」から「対象期間最終日と月次最終日のどちらか早い
方」までの日数になります。さらに、対象期間が月次に全く含まれ
ない場合に日数がマイナスになってしまうのを防止するため、最小
値をゼロに揃えます。で、こういう時に活躍するのがmin関数とmax
関数なんですね。たとえば「対象期間初日と月次初日のどちらか遅
い方」はmax(対象期間初日,月次初日)となるわけ。全体ではちょっ
と長いですが、「max(0,min(対象期間最終日,月次最終日)-(max(対
象期間初日,月次初日)-1))」というような感じになりますね。

あとはかけ算でおしまい。

お礼

シンプルながら、的を得たご回答ありがとうございます。
MaxMin、両関数とも、初めて知りました。
日付はとても扱いにくく、いつも頭を悩ませていたのですが、この
考え方を教えていただき、多くの課題も解決できそうです。
また、日数がマイナスになるのも、簡単に解決されていて、びっくりです。
なんでも、発想が大事なんですね！

本当に役立ちました、ありがとうございました。

回答No.2

KURUMITO

例えば次のようにしてはどうでしょう。
２行目にお示しの項目名が入力されており３行目から下方にデータがあるとします。
作業列としていくつかを用意します。
Ｅ1セルには月次1、Ｇ1セルには月次2、Ｉ1セルには月次3のように入力します。
Ｅ2セルには10/10と入力し、Ｆ2セルには11/7、Ｇ2セルには11/8、Ｈ2セルには12/5と入力します。
Ｅ3セルには次の式を入力して、例えば右横方向にＨ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(A3="","",IF(MOD(COLUMN(A1),2)=0,"",IF(AND($B3<=E$2,$C3>=E$2),MIN($C3,F$2)-E$2+1,IF(AND($B3>=E$2,$B3<=F$2),MIN($C3,F$2)-$B3+1,""))))
これでＥ列、Ｇ列には指定された範囲の期間における日数が表示されます。
そこでお金の配分ですが例えばＭ1セルに月次1、Ｎ1セルに月次2とＥ1セルやＧ1セルに入力したと同じ文字列を入力します。
Ｍ3セルには次の式を入力し横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(OR(M$1="",$A3=""),"",ROUND($D3*INDEX($A:$L,ROW(),MATCH(M$1,$1:$1,0))/SUM($E3:$L3),0))
これで、月次の期間で割り振られた金額が表示されます。
一度こちらの提案と同じにして試験してみてください。

お礼

ご回答、ありがとうございます。
試してみたら、きれいな表ができました。感激です。
自分でも、関数の中身を調べてみました。とても、勉強になります。
特に、Mon関すとＣｏｕｌｍｎ関数を使って、計算式の結果を空白にさせる
箇所は、びっくりしました。
今後、是非使ってみたいです。
また、金額だけが別に纏めて表示できるので、印刷するもの便利です。

結果を貼ったのですが、うまく貼れていません。
（エクセルをきれいに貼る方法があるのでしょうか？）

お忙しいところ、ありがとうございました！

顧客名 始め 終わり 金額 月次１ 月次２ 月次１ 月次２
11-Oct 7-Nov 8-Nov 5-Dec
なにわ 15-Oct 10-Nov 100,000 24 3 88,889 11,111
江戸 1-Nov 2-Dec 50,000 7 25 10,938 39,063

回答No.1

noname#204879

》 月次１　10/10-11/7　月次２　11/8/-12/5...

「月次期間」はどういうルールで決まっているのですか？
上の例で言えば、
「月次１」は28日間で日曜日から日曜日まで
「月次２」は27日間で月曜日から日曜日まで
となっており、テンデンバラバラのように見えますが・・・

「アレは単に例を挙げただけで、実はコーなっている」などと、情報の出し惜しみは止めてくださいネ。
それから、提示されるデータ例は１行だけでなく、少なくとも３～４行分くらいは示してください。そうすることによって、回答者は数式を考えるためのルールを推測できますから。

補足

ご指摘、ありがとうございます。次回から質問する際は気をつけます。
実は、アメリカの会社で働いているため、時差の関係上お返事が遅くなってしまいました。
月次期間も間違えていました。うちの会社は４週２８日が１月次期間となっています。
月次１は１０月１１日から１１月7日
月次２は１１月８はから１２月5日
というふうに２８日毎に区切られて、年１３回の月次があります。

いくつか回答を頂けましたので、先ずそちらにチャレンジして更に疑問があれば
できるだけ詳細に質問いたします。

ありがとうございます。