ｴｸｾﾙでの進捗管理表について教えてください

ｴｸｾﾙにて進捗管理表を作成しております。

画像の通り、進捗度合いをﾃﾞｰﾀﾊﾞｰで表示しているのですが、計画のセルに目標の進捗率（ピンク表示）自動で表示させたいと思っています。

例）期間が1月~12月であれば、1月~3月までの期間は25％、4月~6月まで50％、7月~9月まで75％、10月~12月まで100％と表示したい

ピンク表示の条件式は
=AND($予定開始月<>"",$予定完了月<>"",$予定開始月<=月,月<=$予定完了月)
この条件で、予定日程をピンクにしております。

ここから計画を自動で割り出し、計画のセルに自動表記させる手立てはありますでしょうか？

月単位のガントチャートとなります。

聞いてばかりで申し訳ありません。
宜しくお願い致します。

質問者からの補足コメント

• 回答下さった皆様、ありがとうございました。
  解決いたしました。
  
  goomania様に頂いた回答にて作成しましたので、ベストアンサーとさせていただきます。

    補足日時：2020/11/23 10:08

No.5 ベストアンサー 回答者： goomania 回答日時： 2020/11/21 11:12

No.4です。

大変失礼しました。

>>月数計算だとかなりアバウトになると思いますが、

と書いておきながら、誤って、月数計算に修正する前の数式と添付画像を投稿してしまいました。
前回回答のQ6に記述する正しい数式は以下のとおりです。
=CEILING((DATEDIF(EOMONTH(B6,-1)+1,MIN(EOMONTH(TODAY(),-1)+1,EOMONTH(E6,-1)+1),"m")+1)/(DATEDIF(EOMONTH(B6,-1)+1,EOMONTH(E6,-1)+1,"m")+1),0.25)
上記の数式は、全ての日付を「その日が属する月の月初日」に修正して計算することで「日単位の計算」から「月単位の計算」に修正しています。
(※「『その日が属する月の月末日』でもよいのでは」との意見もあるかも知れませんが、月末日は月により異なります。月初日はどの月も｢1日」なので、月計算に向いています。)
添付画像も再投稿します。

この回答へのお礼

goomaniaさん

丁寧な説明ありがとうございます。
月曜に試してみます。

お礼日時：2020/11/21 16:55

No.4 回答者： goomania 回答日時： 2020/11/21 01:30

通常は期間を計算するには、スタート月の翌月から1ヶ月と数えるわけで

No.3さんの数式
DATEDIF(開始日, TODAY(), "m")/DATEDIF(開始日, 終了日, "m")
もそのような理屈で作成されているわけですが、

>期間が1月～12月であれば、1月～3月までの期間は25％、4月～6月まで50％、
>7月～9月まで75％、10月～12月まで100％と表示したい
ということなので、

スタート月から1と数えるということのようです。

極端に言えば、2020/1/31から2020/2/1までのプロジェクトであれば、
予定開始月=2020/1、予定完了月=2020/2 となり、
実質2日しかないのですが、ご質問者の意図に従えば、全期間は2ヶ月、開始日と同時に1ヶ月目とみなし進捗率50%が表示されるということになるのだと思います。
添付画像ではそのような主旨で設定してみました。
上記の説明でお判りかと思いますが、No.3さんのご指摘のように、

>月数計算だとかなりアバウトになると思いますが、

添付画像では「計画」の「進捗率」はQ6セルです。
ここに記述すべき数式は、
=CEILING((DATEDIF(B6,MIN(TODAY(),E6),"m")+1)/(DATEDIF(B6,E6,"m")+1),0.25)
となります。

この回答へのお礼

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

月曜日に試してみます。
月管理という時点で最初からつまづいたのですが^^;

希望はガントチャートとマイルストーンの組み合わせでしたので、項目が50近くあるため各項目の指標(中間地点ではありませんが…)を「計画」のセルに出したかったのです。

月曜日に試してみます。
ありがとうございました。

お礼日時：2020/11/21 07:53

No.3 回答者： fujillin 回答日時： 2020/11/20 16:35

こんにちは

月数計算だとかなりアバウトになると思いますが、それは良いものとして･･

開始日と終了日と計算日（今日で良いのでしょうか？）があれば計算できますね。
二つの日付の間の日数や月数は、DATEDIF関数で求めることができます。

ご提示の図では、セル位置が不明なので、名称で書きますが、
　DATEDIF(開始日, TODAY(), "m")/DATEDIF(開始日, 終了日, "m")
とすることで、経過月数の割合を求められます。

ご質問は、この値を25%単位にしたいとのことなので、
　=CEILING(上の関数式, 0.25)
とすれば、求める結果となるでしょう。

※ CEILING関数は切上げ側で評価しますので、四捨五入的にしたければ、半分の0.125をあらかじめ減じておく等が必要になります。
※ 開始日、終了日が同じ月だと、0割算になるのでご注意ください。
※ 今日が終了日以降だと100%を越える計算になってしまうので、必要であれば、最大100%になるようにしておいた方が良いのかも知れません。
　=MIN(計算値, 1)　とか。

この回答へのお礼

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

本日業務終了してしまったので、月曜日に確認致します。
途中から作成を変わったもので、なかなか理解できず説明下手で申し訳ありません。

お礼日時：2020/11/20 17:27

No.2 回答者： ぐーるぐる 回答日時： 2020/11/20 15:49

＞＞その期間から自動で表記できる方法があればと思っています。

＞＞予定を入力したら、36か月分のセルに対応するような？

これまた、どういう事だか、理解できているか不明ですが、、、(^_^;)

正直なことを言えば、毎日のルーチンワークでもないので、あえて自動化する必要性を感じないのだけれど、、、
なぜなら、何かのはずみで壊れた時に全体が崩れないためには、固定数字を手動で入力した方が安心だからね。
しかし、勉強の為という事であれば、あえて、やるなら、「全月数」の列を増やして、それを分母にするだけで分母は、自動で反映されますよね。

開始月　　完了月　　全月数
2020/11　2023/10　36（ヶ月）

もう一つ、スケジュール管理を個人的に指摘してもいいならば、3年間のスケジュールは、縦に表を作った方が、スクロールも楽なのでお勧めですよ。

がんばって

この回答へのお礼

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

手入力で対応しようと思います。

お礼日時：2020/11/20 16:06

No.1 回答者： ぐーるぐる 回答日時： 2020/11/20 15:00

進捗率を自動計算させたいのね。

これでわかると思います。
簡単な計算式なので、やってみてください。

この回答へのお礼

回答ありがとうございます。
計算式は理解しました。

予定の開始月、完了月を入力するとピンクにセルが染まるのですが、その期間から自動で表記できる方法があればと思っています。
予定を入力したら、36か月分のセルに対応するような？

またまた説明下手で申し訳ありません。

3年計画の管理シートで、データバーの行も36か月分（隠れています）あります。※月毎の進捗を振り返り確認するため
各項目ごとに設定するのが大変なので予め式を入れておきたいと思っています。

方法がありましたら、ご教示いただきたくお願い致します。

お礼日時：2020/11/20 15:34