ここから質問投稿すると、最大4000ポイント当たる!!!! >>

初めての質問です。失礼な点があればお詫びいたしますので、ご教示下さい。
私の会社は、取引先に対してリベートを支払っています。支払いサイクルは顧客によってまちまちです。
例えば10月10日から11月10日分販売に対してリベートを10万円支払い
このデータはエクセルで手元にあります。

A列    B列        C列         D列
顧客名  対象期間(始)  対象期間(終)   金額
なにわ   10/15/2010     11/10/2010     100,000
この内容を弊社の月次期間毎に割振る必要があります。
月次1 10/10-11/7 月次2 11/8/-12/5...
先程の例を月次単位で分けると
月次1 24日分の  88,889円 月次2  3日分の   11,111円
と比率で計算すればなるのですが、これをエクセルで自動的に
計算したいと考えていますが、どうにも解決できていません。
何か良い方法があれば是非是非、教えていただけないでしょうか・
何方か、よろしくお願いいたします。

このQ&Aに関連する最新のQ&A

A 回答 (4件)

こんにちは!


すでに回答は出ていますが・・・
一例です。

↓の画像で説明します。
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
「エクセル 該当する日付範囲への金額配賦方」の回答画像4
    • good
    • 0
この回答へのお礼

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

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

お礼日時:2010/01/20 01:10

対象期間の初日と最終日が明らかなので、対象期間の日数は計算で


きますね。「最終日-(初日-1)」です。そうすると1日あたりの金額
は割り算で出ますね。

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

あとはかけ算でおしまい。
    • good
    • 0
この回答へのお礼

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

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

お礼日時:2010/01/20 01:13

例えば次のようにしてはどうでしょう。


2行目にお示しの項目名が入力されており3行目から下方にデータがあるとします。
作業列としていくつかを用意します。
E1セルには月次1、G1セルには月次2、I1セルには月次3のように入力します。
E2セルには10/10と入力し、F2セルには11/7、G2セルには11/8、H2セルには12/5と入力します。
E3セルには次の式を入力して、例えば右横方向にH2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=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,""))))
これでE列、G列には指定された範囲の期間における日数が表示されます。
そこでお金の配分ですが例えばM1セルに月次1、N1セルに月次2とE1セルやG1セルに入力したと同じ文字列を入力します。
M3セルには次の式を入力し横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(OR(M$1="",$A3=""),"",ROUND($D3*INDEX($A:$L,ROW(),MATCH(M$1,$1:$1,0))/SUM($E3:$L3),0))
これで、月次の期間で割り振られた金額が表示されます。
一度こちらの提案と同じにして試験してみてください。
    • good
    • 0
この回答へのお礼

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

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

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

顧客名始め終わり金額月次1月次2月次1月次2
11-Oct7-Nov8-Nov5-Dec
なにわ15-Oct10-Nov 100,000 24388,88911,111
江戸1-Nov2-Dec 50,000 72510,93839,063

お礼日時:2010/01/20 01:06

》 月次1 10/10-11/7 月次2 11/8/-12/5...



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

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

この回答への補足

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

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

ありがとうございます。

補足日時:2010/01/19 21:58
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!


人気Q&Aランキング