スケジュール管理表を作っています。数式があれば教えてください。

質問

たとえば、下記のような表があります。
E 総数量 に対し、A～B期間内の日数を F～の月ごとのカレンダーに数量を反映させる事は出来ますでしょうか。
現在、数式を入れた例をのせましたが、F2 ～ I2 までに入る数式に苦戦しています。現在、数式例を載せましたが、一日の本数が反映するようになってしまいました。
A、Bを読み取る事はできたのですが、下のように、G２＝９３　H２＝６というような結果になるように、どなたか知恵をお貸しください。
m( _ _ )m

　　　A 　　 B 　　　 C 　　　 D 　　　　E 　　　 F 　　 G　　 H　　 I
1 　開始　　終了　期間日数　一日本数　総数量本　7月　 8月　 9月 　10月
2　　 8/1　 9/2 　　　　33 　　　 3 　　　 100　　　　　　93 　6

C2=(DATEDIF(A3,B3,"d")+1)
D2=ROUNDDOWN(D3/C3,0)
F2=IF(AND(MONTH(G$1)>=MONTH($A3),MONTH(G$1)<=MONTH($B3)),$E3,"")

回答No.10

KURUMITO · ベストアンサー

解答Ｎｏ9です。
Ｆ2への入力の式はそちらで書かれた解答への補足で書かれた式をこちらでその間も使って試験したところ全く正常に作動します。
日付を入力しているセルで7月は2009/7/1とと入力して表示形式を使って7月と表示しているでしょうか？日付の入ったセルでは表示の文字がセルの右側にあって左側にはなっていませんね。右側になっているのが正常です。
ところで総量をできるだけ100にするのであればＤ2への入力の式は
=ROUND(E2/C2,2)とします。
Ｇ2セルには99.93とＨ2セルには6.06と表示されます。そこで、Ｇ2から右横方向のセルを選択状態にしてから、「ホーム」タブの「数値」グループにある「桁区切りスタイル」をクリックします。Ｇ2セルの値は94、Ｈ2セルの値は6となり全体では100と等しくなりますね。

お礼

出来ました！
この度はご丁寧にありがとうございました。

とても勉強になりました。本当にありがとうございます。

回答No.9

KURUMITO

解答Ｎｏ５，６です。
1行目にはＥ列（Ｅ１）まではお示しのように項目名が並べられ、Ｆ1セルには2009/7/1と入力し、Ｇ1セルには2009/8/1と入力します。その後にＦ1セルとＧ1セルを選択してからＧ1セルの右下にあるフィルハンドルを右横方向にドラッグします。これでＨ1セルには2009/9/1・・・・と表示されます。その後にこれらの日付の入ったセルを選択して右クリックし、「セルの書式設定」から「表示形式」タブの分類で「ユーザー定義」を選びます。種類の窓に　m"月"　と入力します。これでＦ1セルから右横のセルには7月、8月・・・と並びます。
その後にＦ2セルには解答Ｎｏ6に示した式を入力し、右横方向にオートフィルドラッグ(Ｆ2セルの右下にある■をクリックして右横方向にドラッグ）したのちに下方にもオートフィルドラッグします。
Ａ2セルに8/1、Ｂ2セルに9/2と入力すればＧ2セルには93、Ｈ2セルには6が表示されます。
ところでお示しの式で
C2=(DATEDIF(A2,B2,"d")+1)
D2=ROUNDDOWN(E2/C2,0)
Ｃ2セルへの式はわかりますが、Ｄ2の式から判断すると総数が抑えられており、それを指定の期間内の日にちで均等に配分するということですね。
難しいですね。お示しのケースでは総数が100に対して月ごとの総数は99になっています。本当は100にならないといけないのではないですか？
お示しのケースでは1日当たりの数量は3ですがどこかの日には4本の日を作らなければ100にはなりませんね。そのようなことを考える必要はないということでしょうか？

補足

とても丁寧に教えて頂いてありがとうございます。
なんだかここまでして頂いて、なんと言っていいのか。。。
教えて頂いたとおり、セルを一つ一つ確認してみましたが、
やはり結果が、＃NAME?と出てしまうんです。H２＝６になるのです。

Ｆ2には、下記の数式で良かったのでしょうか。
=IF(COUNT($A2:$B2)<>2,"",IF(AND(MONTH(F$1)=MONTH($A2),MONTH(F$1)<>MONTH($B2)),(DAY(EOMONTH($A2,0))+1-DAY($A2))*$D2,IF(AND(MONTH(F$1)<>MONTH($B2),F$1>$A2,F$1<$B2),DAY(EOMONTH(F$1,0))*$D2,IF(AND(MONTH(F$1)=MONTH($B2),F$1<=$B2),($B2+1-F$1)*$D2,""))))

100の総数に関しては、指摘の通り、本当は100にならないといけないのですが、どこかに、１本加える事は可能だったのでしょうか。
出来ない物とあきらめ、このような考えになってしまいました。<(_ _)>

回答No.8

kmetu

No2です
No7の式を一部訂正です　以下を採用してください。

=IF(AND(DATE(YEAR($A2),MONTH($A2),1)<G$1,DATE(YEAR($B2),MONTH($B2),1)>G$1),DAY(DATE(YEAR($A2),MONTH(G$1)+1,0))*$D2,IF(MONTH($A2)=MONTH($B2),$E2,IF(MONTH($A2)=MONTH(G$1),(DAY(DATE(YEAR($A2),MONTH($A2)+1,0))-(DAY($A2)-1))*$D2,IF(MONTH($B2)=MONTH(G$1),DAY($B2)*$D2,""))))

回答No.7

kmetu

No2です

> そのままG2に数式を入れてみましたが、
> 結果が　 値が空白の状態になってしまいました。(><)

G1の8月というのが表示だけで値としては2009/08/01となっている
(このデータの状態は式にとって重要です)のでしたら

=IF(AND(DATE(YEAR($A2),MONTH($A2),1)<G1,DATE(YEAR($B2),MONTH($B2),1)>G1),DAY(DATE(YEAR($A2),MONTH(G1)+1,0))*$D2,IF(MONTH($A2)=MONTH($B2),$E2,IF(MONTH($A2)=MONTH(G1),(DAY(DATE(YEAR($A2),MONTH($A2)+1,0))-(DAY($A2)-1))*$D2,IF(MONTH($B2)=MONTH(G1),DAY($B2)*$D2,""))))

にしてください。

> 最初の説明で、間違えて数式を伝えてしまい。正しくは、↓の通りなんです。m(_ _)m

式で参照先が間違って書かれているというのは見ればだいたい分かりますし
記載するときに書き間違えたとかなんかだろうなと想像できます
それに、この部分は本論と関係ない部分ですから気にしてはいませんでした。

回答No.6

KURUMITO

解答Ｎｏ5です。
Ｆ2セルへの入力の式は次の式に変更してください。
=IF(COUNT($A2:$B2)<>2,"",IF(AND(MONTH(F$1)=MONTH($A2),MONTH(F$1)<>MONTH($B2)),(DAY(EOMONTH($A2,0))+1-DAY($A2))*$D2,IF(AND(MONTH(F$1)<>MONTH($B2),F$1>$A2,F$1<$B2),DAY(EOMONTH(F$1,0))*$D2,IF(AND(MONTH(F$1)=MONTH($B2),F$1<=$B2),($B2+1-F$1)*$D2,""))))
もしも、Ｄ2の値が行毎に変わらないのでしたら$D$2に変更してください。

補足

至らない、説明でここまで考えて頂いてありがとうございます。
私の、作っていた数式も、間違いで↓の間違いだったのですが、
C2=(DATEDIF(A2,B2,"d")+1)
D2=ROUNDDOWN(E2/C2,0)

そのままF1に教えて頂いた、数式を入れればよかったのでしょうか。
結果が　 8/1= #NAME? 9/1 = 6 と出てしまいます。(><)
何か間違っていたのでしょうか。

回答No.5

KURUMITO

Ｆ1セルから右横への月の表示は例えば7月の表示は2009/7/1と入力して7月と表示するようにします。そのようにしているようですね。
そこでＦ2への入力の式ですがＭＯＮＴＨ関数のみの対応ですと年がまたがるような場合には不都合となりますので注意が必要でしょう。
複雑になりますがＦ２セルへは次の式を入力し右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(COUNT($A2:$B2)<>2,"",IF(AND(MONTH(F$1)=MONTH($A2),$A2>=F$1),IF($B2>=DATE(YEAR($A2),MONTH($A2)+1,1),(DAY(EOMONTH($A2,0))+1-DAY($A2))*$D2,($B2+1-$A2)*$D2),IF(AND(MONTH(F$1)<>MONTH($B2),F$1>$A2,F$1<$B2),DAY(EOMONTH($F1,0))*$D2,IF(AND(MONTH(F$1)=MONTH($B2),F$1<=$B2),($B2+1-F$1)*$D2,""))))

回答No.4

kmetu

No2です
始まりが8/1で終了が10/2になるような場合(月が2ヶ月以上またがる場合)
は以下のようにしてください。

=IF(AND(MONTH($A2)<VALUE(LEFT(G1,1)),MONTH($B2)>VALUE(LEFT(G1,1))),DAY(DATE(YEAR($A2),VALUE(LEFT(G1,1))+1,0))*$D2,IF(MONTH($A2)=MONTH($B2),$E2,IF(MONTH($A2)=VALUE(LEFT(G1,1)),(DAY(DATE(YEAR($A2),MONTH($A2)+1,0))-(DAY($A2)-1))*$D2,IF(MONTH($B2)=VALUE(LEFT(G1,1)),DAY($B2)*$D2,""))))

回答No.3

kmetu

No2です
10月以降12月までは
10月の場合
VALUE(LEFT(I1,2))
にしてください。
また
7月　 8月　 9月 　10月
が表示上だけ月になっているのなら（実際は日付が入っているのなら)
VALUE(LEFT(G1,1))
は
MONTH(G1)
でいけます。

回答No.2

kmetu

G2の式に

=IF(MONTH($A2)=MONTH($B2),$E2,IF(MONTH($A2)=VALUE(LEFT(G1,1)),(DAY(DATE(YEAR($A2),MONTH($A2)+1,0))-(DAY($A2)-1))*$D2,IF(MONTH($B2)=VALUE(LEFT(G1,1)),DAY($B2)*$D2,"")))

でいかがでしょう。

補足

そのままG2に数式を入れてみましたが、
結果が　 値が空白の状態になってしまいました。(><)
何か間違っていたのでしょうか。

最初の説明で、間違えて数式を伝えてしまい。正しくは、↓の通りなんです。m(_ _)m

C2=(DATEDIF(A2,B2,"d")+1)
D2=ROUNDDOWN(E2/C2,0)

回答No.1

rivoisu

D2=ROUNDDOWN(D3/C3,0)
D3、C3には何が入っているのですか
この式の意味は何ですか

なんとなく総本数100でそのうち8月分が93本、9月分が6本（合わない1は丸め誤差、これもOK）みたいに見えますが
C2の式は
C2=(DATEDIF(A3,B3,"d")+1)ではなくて
C2=(DATEDIF(A2,B2,"d")+1)じゃのいのですか
そう考えるとそのほかの式も同じ間違いでも2行目参照しているのですか
そのあたりを明確にしてください。

補足

C2=(DATEDIF(A2,B2,"d")+1)
D2=ROUNDDOWN(E2/C2,0)

指摘された通り、↑の間違いでした。m(_ _)m

総本数100に関しては、割れない数なので、四捨五入すると、100を超えてしまうので、このように考えてしまいました。