【EXCEL2003】固定の数値を割り振りたい

質問

お手上げと言いますか、もうチンプンカンプンです。お助けください。

１ヶ月の売上を１日の売上で割ったのがＤ列です。
この割合を今年の同曜日の日に当てはめたのがＨ列です。
Ｈ列の金額はＨ１４（自分で決めた金額）を割り振っています。

こうすると、5月で例えれば31日が余ってしまいます。
なので、前年１日のＤ２を当てはめます。

問題なのは、大の月・小の月・２月（閏年）などあって
一筋縄ではいかないことです。
5月なら31日に前年1日を。
6月なら30日に前年1日を。
2月なら28日に前年1日を（閏年は29日）。

これを関数で出来ないでしょうか？

回答No.2

KURUMITO · ベストアンサー

次のようにしてはどうでしょう。
A2セルから下方にはA32までのセルに該当の月の一ヶ月分が１日から３０日、３１日、２８日、２９日など月末までの日にちが入力されるとします。
B２セルには次の式を入力してB３２セルまでオートフィルドラッグします。

=IF(A2="","",TEXT(A2,"aaa"))

コピーして曜日も貼り付けられているのであればB２セルへの操作は必要ありませんね。
C２セルから下方には売上高が入力されているとします。
C３４セルには次の式を入力してその月の売り上げの総額を表示します。

=SUM(C2:C32)

D2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(C2="","",C2/C$34)

F２セルには今年の該当月の日付を表示させることにします。そのためF２セルには次の式を入力して下方に(F32)オートフィルドラッグします。

=IF(A$2="","",IF(MONTH(DATE(YEAR(A$2)+1,MONTH(A$2),ROW(A1)))<>MONTH(A$2),"",DATE(YEAR(A$2)+1,MONTH(A$2),ROW(A1))))

また、G２セルには次の式を入力して下方に(G32)オートフィルドラッグします。

=IF(F2="","",TEXT(F2,"aaa"))

これでF列には１年後の日付が、G列には曜日が表示されます。
そこでご質問の本題に移るのですがH列にはA列、B列およびD列の値を取り込んでの割合を表示させることにします。H２セルには次の式を入力して下方にH32までオートフィルドラッグします。

=IF(F2="","",INDEX(D:D,IF(ROW()<COUNT(A$2:A$32)+1,IF(MATCH(G2,B:B,0)+COUNTIF(G$1:G1,G2)*7-ROW()>=0,MATCH(G2,B:B,0)+COUNTIF(G$1:G1,G2)*7,MATCH(G2,B:B,0)+(COUNTIF(G$1:G1,G2)+1)*7),IF(MATCH(G2,B:B,0)+COUNTIF(G$1:G1,G2)*7-ROW()>=0,MATCH(G2,B:B,0)+(COUNTIF(G$1:G1,G2)-1)*7,MATCH(G2,B:B,0)+COUNTIF(G$1:G1,G2)*7))))

この式では常にG列での曜日の行よりもB列では同じ行か、または、それよりも下の行での同じ曜日におけるD列のデータを表示させるようにし、月末の付近ではG列での曜日がB列の同じ行か、またはそれよりも下の行で同じ曜日がない場合にはその行を上にさかのぼって最初に一致する曜日のD列でのデータを表示させるようにしています。
これにより例えば月末でのデータを１日目のデータを用いることよりもより現実的であると考えます。
ところでH列にはD列のデータがそのまま使われています。そのためD列での値の合計は１になるのに対して、H列ではそうとなるとは限りません。当日の売り上げの割合を考慮してI列にデータを表示させるのでしたら、H列の合計を求めておくことが必要です。そのため、H３４セルには次の式を入力します。

=SUM(H2:H32)

売上の目標値をI３５セルに入力して各日付ごとの売り上げ目標値をI列に表示させるためにはI２セルに次の式を入力してI３２セルまでオートフィルドラッグします。

=IF(H2="","",I$35*H2/H$34)

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

=SUM(I2:I32)

参考になりましたら幸いです。

お礼

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

すげぇ～～～～～～～～～！！！！！！！って感じです。

＞これにより例えば月末でのデータを１日目のデータを用いることよりもより現実的であると考えます。
仰るとおりです！

これはもう私の及ぶところではございません。
お知恵を拝借して正解でした。
仕事が一歩前に進みます。

また機会がございましたら是非よろしくお願いいたします。
ありがとうございました。感謝いたします。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

ルールがしっかり決まっていればできると思います

↓規則の優先順位がわかりません
規則1
○月末は、前年○月1日の割合を持ってくる
△月末は、前年△月1日の割合を持ってくる

規則2(推測が混じっているけど)
○月第☆曜日は、同曜日の前年○月第☆曜日の割合を持ってくる
△月第♪曜日は、同曜日の前年△月第♪曜日の割合を持ってくる

規則3？というか質問
3月29日、3月30日、3月31日はいつの値を持ってくるか？

それとも、規則2に当てはまらないものを前年の同月1日にするのかな？

補足

コメントありがとうございます。

えーっと・・・

規則１と２はその通りです。
規則３は、
3/29は、前年3/30
3/30は、前年3/31
3/31は、前年3/1
になりますね。

ただ、今年はいいですが、来年閏年が来ます。
そうすると、それ以降1日ズレが2日ズレに変わります。
そのときは、また計算式を入れ替えればいいかなと思っています。
とりあえず、今（今年）通用するものを作りたいです。