一番上の入り口へ > excel > お手製 簡易ガントチャートを関数だけで作ってみる (レッツトライExcel)

お手製 簡易ガントチャートを関数だけで作ってみる
(レッツトライExcel)

--

このページで説明する内容

工程管理で有名なガントチャートを、Excelで気軽に実践してみます(マクロは使いません)。

どの方法で作るか


実際に作成する方法として、以下の5つほど思いつきました。
・関数式 *
・条件付き書式 *
・グラフ *
・手書き
・マクロでの方法

ガントチャート(Wikipedia) とは?

作成のダイジェスト

今回はそれらの内、3つ*を紹介します。

  • パターン1:関数式
  • パターン2:条件付き書式
  • パターン3:グラフ



まずは 関数式から説明します。

パターン1:関数式

全体の考え方

全体方針として、タスクごとに一定期間の範囲があるということなので、それをどう表現するかを考えます。
excel上の表現では、ある日付がタスクの期間内であるかを判断し、 その期間内であればマークをします。
条件 数式条件 表示
対象の期間内にある 開始日 <= 対象日付 <= 終了日 OK: ■
対象の期間内にない 対象日付 <開始日 or 終了日 <対象日付 NG: から


作り方

それでは実際に作ってみましょう。
(1)まずはタスク(ここでは、A〜Dタスク)を左側(表←)に並べます。


(2)日付(ここでは 16日〜24日)を上側に横軸で作成します(表示形式:d)。


(3)タスクごとの、開始・終了日付を埋めます。


(4)ここからは本題です。
タスクAの開始と終了の範囲内にある日付あれば■を表示するようにします。
中身のガントチャート部分は、日付と各タスクの日付を利用して自動的に表示されるように、 関数を入力します。

それでは、左上の隅(16日 タスクA)に式を入れてみましょう。

(完成図)

日付[E$3](16日の行)は、タスクA[5行目]の開始[$C5]と終了[$D5]の範囲内にあるかどうかのif文
赤吹き出しの部分
=if(and(E$3>=$C5,E$3<=$D5),"■","")

手を抜くコツとしては、=の把握と$の使い方です。
うまく左上のセルに入力すれば、そこからセルのドラッグだけで表全体の他のセルに適用できます
(開始・終了へのセル参照では右にずらさせないための固定として、列(図ではC,D列)の前に$をつけて絶対参照にし、
日付セルへの参照では下に展開しても動かさないため、同じく行(図では3の前)に$をつけます)。


その他の活用例:キャラベースの他の例REPT進捗説明ページ

パターン2:条件付き書式

全体の考え方

条件付き書式も全く同様にできます。
パターン1の関数式であったところを、条件付き書式に置き替えているだけで、本質は同じです。


作り方

(1) 条件付き書式を設定する対象範囲全体を左上から右下まで選択します。

(2) 条件付き書式の画面を開きます(下図 右側ダイアログ)。


 (2-1) 入力設定するダイアログで「数式」を選択します。
 (2-2) 「次の数式を〜」(図中 右側中程)の部分には上記(パターン1の関数式)と全く同じ判定式を入力しています。
 (2-3) また、書式としてセルの塗りつぶしにしています。


(完成図)



このようにいま説明したパターン1,2は 多少デザイン的にボテッとしていて気になる人は、
もう少し手間をかけるグラフ3の方が良いかもしれません。

パターン3:グラフ

全体の考え方

グラフ用のカラムを用意して、その値を使ってグラフ表示させます。 既出の2パターンは範囲内かどうかの式を作成しましたが、
こちらは期間をグラフ幅として用意するところが考え方として違います。
(最終的な表示方法と、そのための積み上げ棒グラフの設定の仕方を考えます)

データのイメージ
タスクA:開始日までの期間+ 終了日までの期間
タスクB:開始日までの期間+ 終了日までの期間
タスクC:開始日までの期間+ 終了日までの期間

作り方

@グラフ全体像の作成
開始日までの期間の日数と、開始から終了までの期間の日数を グラフの積み上げボリュームとして別の色で表示します。

(1)先ほど説明したパターンと同様に、事前に基本の表を作っておきます。 タスクを左側に並べ、開始、終了日付列を作ります。
(2)使用するグラフは積み上げ横棒グラフ [開始+終了の棒グラフとするので]とする予定です

図・グラフの最小値と最大値 (3),(4-b)

積み上げ用の差分 (植木算なので +1しています)

(3)幅のための基準日を用意します。
実際にはグラフ全体の開始と終了日付に対応する最小値、最大値を設定します(図 上の吹き出し)。
※日付についてmin,max関数を使い、表示形式を数値で表示させると値がわかります。例えば 2010/3/16を Excelの内部では数値の39523ぐらいで表しています。

A各タスクのグラフ用数値(積み上げ用の値)を用意
(4-a)各タスクについてのMINから開始までの差の日数(ちなみにExcelは1日=1.0換算です)部分を用意します。
図では非表示にしていますが、例えば終了の右側の列に MINとの差分を計算する式を入れておきます。

(4-b)各タスクで開始から終了までの差を用意します。
※上図では差分セルを作って、そこをグラフ範囲としています。

Bグラフの見た目の微調整
(5)開始まで部分(4-aで作成した数値を利用)は透明にします(左側を透明にすることで、チャートのように見せる)
※また、開始までの距離を出すため、グラフの開始基準として最小値を把握しておく必要があります( (3)で↑に書いてあります)。
(6)タスクの軸は反転させます[グラフ表示上のタスクが上下逆になってしまうので]
(7)日付軸はグラフ上側に表示されるようにします。
(8)グラフの縦サイズ等をマウスで微調整し、タスク幅、日付幅と合わせます。

図・グラフの終了範囲 (4-b),(5)
つまり、それぞれのタスクには、"開始日までの日数"(グラフ上透明の左側バー)、 "開始日から終了日までの日数"(グラフ上青い右側の積み上げバー)という、2つの列があります。 また、見栄えを考えて、完成したらそれらのカラム列を非表示にさせます。
数値の大きさ 数値の意味 グラフの箇所
最小値〜開始日付 全体の開始日〜タスクの開始日 グラフ左側の透明部分(4-aの部分)
開始〜終了日付 タスクの期間 表、グラフの青い部分

C完成
(完成図)

応用例として、終了予定日や、進捗も同様にグラフ幅用のカラムを用意することで作れますね。


グラフ作成のこのテクニックの透明化と積み上げのグラフを応用すれば例えばピラミッド構成の人口表など作れます。

グラフの手動作成をマクロした例のようです
VBA Excel:「ガントチャートをグラフで作る」 - 35歳の壁

まとめ

今回の場合のように、月日が進んでも自動的に表示が変わるように設定しておけば、手間が省けますね。このようにExcelでちょっとした内容を数値に連動させてグラフィカルに表示させることができるようになると、低コストで状況が把握できます。つまり見える化を維持したまま、手間を省くことができます。

学んだスキル

・ガントチャートの適応例
・数式による関数の利用方法
・相対/絶対参照($A$1など)の利用方法
・条件付き書式やグラフの違った使い方

それぞれの利点

・関数方式は初心者でも作りやすいです。慣れればグラフもまあまあ簡単です。一方追加などが意外と面倒で範囲が壊れた場合、新規に作り直すことも多いです。
・条件付き書式での方法は 2003以前のバージョンでは3つしか設定できないため制約が大きいです。
・関数のキャラクタで行う場合、セルの境目のスキマが気になります(こんな感じ■■□□)。
・グラフはやはり軽いようです。
・グラフの方式は、作る上で予定と実績の2つを組み合わせるのが難しいです。

発展

今回は簡単な例でしたので、物足りない人へもっと実際に活用できるような例を紹介します。
・タスクの進捗度を作ったり、対応日数を表示させる。
・開始/終了日付について予定と実績を分ける。
・タスクを多段階層にする(1-2-4のタスクとか、下位タスクとか、サブタスクとか)。
・開始日終了日を見分けやすくする(関数の場合 →を使うとか、当日の場合☆をつかうとか)。
・予定と実績を重ねて表示させる(条件付き書式の場合、薄い色が予定で、濃い色が実績など)。
・「今日」を印す。
・日付延伸やタスクの追加が簡単にできる。

タスクの因果関係までやろうとすると、関数ではきついです。マクロの方が楽です。
実績の描き方は意外と悩みます。


またテンプレートして一度作ったものを再活用したり、他のスケジュール管理に転用したりと応用できます。

類似リンク

進捗度をテキストで少しかっこ良く定量的に表示するちょいわざ

入力規則をセルによって自動的に変わるようにするちょいわざ

リストの過不足をチェック!(レッツトライExcel)

宴会幹事向け出欠確認アンケートを作る(レッツトライExcel)

ちょっと助かるショートカット (Excel操作編)

【無駄をなくす効率技】excelの新規作成でシートをひとつにするちょいわざ

一番上の入り口へ > excel > お手製 簡易ガントチャートを関数だけで作ってみる (レッツトライExcel)