進捗表を作成するには?

Question 22.4 Previous Next
進捗管理を行う時に時系列グラフを使用する場合があると思いますが、その作成はどのように行えばよろしいでしょうか?
シートa1
質問の図 マクロを使った方法がわからず、また、グラフにしようとした場合には、基点がゼロからになってしまいます。
(左図の事例で描くと、全部の基点が5日になってしまう)

そこで関数を使った(グラフではないのですが)グラフに似た感じで、セルにマークを記述するものを作ってみました。
こんな感じでグラフを作成するには、どうしたらよいでしょうか?
Answer   Copyright (C) 1999.11.15 永井善王
もし、Excelのグラフ機能が使えれば、手間暇かけずに作表できますね。私も以前は、他のグラフ・ソフトを随分使いました。
数式で作図 しかし、Excelのグラフ機能については詳しく知りませので、最新のExcel2000で可能性を探ってみました。あなたの希望に一番近いものは、株価グラフの中の俗にローソク足と呼ばれる左図かなと思います。
このグラフを右に90度回転した状態(X軸に年月日、Y軸に担当)に仕上げたかったのですが、上手にできませんでした。

作図中に気が付いたのですが、Y軸目盛りは月変わりするとどうなるのかな? 試してみるまでもなく、11月99日まで目盛られた後に、12月1日になるでしょう。新年への変わり目も同じことです。よって、あなたが採られた「DATE関数を使って求めた日付シリアル値」で作図することになります。
その場合、Y軸の目盛りラベルが当然、そのシリアル値で表示されることになりますが、普通の年月日表示に置換するには、どうすればよいでしょうか。また、棒の長さがあなたの図に比べて、各々1日分ずつ短いので、直感的に日数を見誤りそうです。 この点の補正はできなくはないですが、ややこしさが増すことになります。

グラフの精通者に相談することができれば、よい方法が見つかるかも知れません。しかし、とりあえずの結論としては、クリアすべき課題が多くて Excelのグラフ機能では、難しいのではないでしょうか。

質問の図に戻って、数式をよく見てみましょう。
  =IF(ISERR($G10),"",IF(N$186>=$G10,IF(N$186=$L10,"=>",IF(N$186<$L10,"==","")),""))
IF文でG10セルのシリアル値を、N186セルの値と比較しています。従って、縦方向にMAX176項目(行)が表示可能と判断できます。Excelシートに非常に多くの数式を埋め込んだ場合、ファイル・サイズが大きくなり、Excelの動きが遅くなることがありますが、この程度の行数ならば、まず心配不要と思います。
よって、数式で処理可能ならば、ムリしてマクロを組むこともないとも思われるので、可能性を検討しておきましょう。

(1) N列から始まって右方向に、月・日・曜日が表示されています。このカレンダー部分は作図するたびに、範囲を変えることが必要でしょう。であるならば、その都度、オートフィル機能を使いながら、手で入力することになるのでしょうか。

(2) あなたの例示には無いですが、仕掛かり中で終了年月日が入力されていないデータが、実務ではあるのではないでしょうか。そのケースに対応できる数式は、相当むつかしくなりそうですね。

(3) 逆のケースで、開始年月日が作図の基点年月日より前の場合への対応も、(2)と同様にむつかしそうです。

(4) もうすぐ2000年になります。DATE関数でシリアル値を求める場合の年に 00を使うと、どんな値が返るでしょうか。私のパソコンは、マイクロソフト社の「Year 2000 Update」がまだしてないからでしょうか、00年は 2000年とみなされないようです。 無論、4桁で入力すれば済むことですが、一過性の問題を避けるために永遠に、人に負荷をかけることになり、感心できません。

少々長くなりましたが以上の理由により、数式を駆使して、ワークシートを組み合わせて、マクロで作図することにしましょう。

先に、完成させる図のレイアウトを、決めておきます。

完成図 【完成図】
B~K列の間に、非表示の列を 8列設けてあります。これは予備で、将来、項目を追加したくなったときに、マクロコードに影響を及ぼさずに、レイアウト変更を可能にするためです。

Q~V列の間にも非表示の列がありますが、ここには年の補正とシリアル値を求める数式を隠しておきます。

K列以後はマクロコードに関係しますので、将来においても、レイアウトを変更しないものとします。

また、進捗状況を矢印風に表示するか、■で表示するか、矢印したセルを着色するかなどの細かい仕様は、好みで決めることに なるでしょうが、ここでの回答は、図の体裁で組んでいくことにします。

次に、数式シートを作ります。その理由は、数式をマクロでワークシートにセットすることは可能ですが、このテーマでは、かなり複雑な数式をたくさん使うことになるので、あらかじめワークシートに作っておいた数式を、コピーして使う手法をとります。

【数式シート】
数式シート

【数式のポイント】
(1) R2、S3、R7、S7セルは、年を4桁に変換する
(2) T2、U2、T7、U7セルは、日付シリアル値を求める
(3) T7セルの開始シリアル値は、「開始年月日」または、「開始年月日」が「基点年月日」より前の場合は「基点年月日」から求める
(4) U7セルの終了シリアル値は、「終了年月日」または、「終了年月日」が Null の場合は「現在年月日」から求める
(5) N7セルの所要日数は、開始・終了年月日のシリアル値を差し引きして求める。この場合は各々の4桁年を用いる。また、「終了年月日」がNull の場合は、Null とする
(6) W5セルには、マクロで年月日シリアル値が記入される。なお、「基点年月日」から「現在年月日」までの期間に対応して、W列以後に展開される
(7) W2、W3、W4セルの数式は、W5セルに記入された年月日シリアル値から、各々、月、日、年を計算する
(8) W6セルの数式は、W5セルに記入された年月日シリアル値から、曜日を計算する
(9) W7セルの数式は、W5セルに記入された年月日シリアル値と、開始シリアルおよび終了シリアルを比較して、矢印を記入する
 矢印は「開始シリアル」から「終了シリアル」までの各日に引くことになるが、矢印の形状は 「終了年月日」が Null でない「終了シリアル」には "=>"、それ以外は "==" とする

マクロを組む場合には、与えられるデータと希望する図を、どのように結びつければ良いのかを、事前に考えて整理しておくことが必要です。問題点が多いとか、処理が複雑な場合には、中間加工用のシートを設けて、段階的に処理することも検討します。

事前検討が不十分な状態で、いきなりマクロを組み始めると、何度も修正・組み直しを余儀なくされるでしょう。こうした意味から、上記(1)~(4)の問題点と、あなたが作った数式などは、貴重な事前検討資料と言えます。

それでは、マクロの概要をまとめてみましょう。

(1) 進捗シートに、作図に必要なデータ件数があるか確認する … この例では A7:Q11セルに5件あり
(2) 加工シートを初期化して数式シートの内容を写す。そして、進捗シートのデータ数分の行を用意する … A7から5行
(3) 進捗シートのデータを、項目シートに値だけ写す … A1:Q11セル
(4) 数式シートから加工シートへ、所要日数の数式を写す … N7セル
(5) 加工シート内で、年4桁補正とシリアル値と矢印の数式を、データ件数分コピーする … R7:W7セル
(6) 加工シート内で、基点年月日から現在年月日までの日付別エリアを展開する … T2の値をW5へ、W5の値を1増やしながらX5から右方向に日数分のセルへ
(7) 加工シートに曜日を記入し、月を見やすくして、矢印セルに着色する … W5から右方向に日数分のセルへ
(8) 加工シートの不要な網掛けを消してから進捗シートに写して、列幅を調整する … 列幅調整は W5から右方向に日数分

マクロコードはサンプルブック(下記)で見てください。

サンプルブックのダウンロードは ここをクリック  (YNxv9a7_progress.xls 60KB)
※ 一旦、ブックをハードディスクに保存し、後で改めて開いてから実行してください。

Excel VBA Macro