📊

マクロを使わないシンプルなガントチャート自動作成エクセルを使って90%シンドロームを予防する（無料）

2023/01/08に公開

ガントチャート

エクセル

作業日数からガントチャートを作るだけのシンプルな機能の必要性

エクセルでガントチャートを作ったことがある人は多いと思います。しかし、ほとんどの方は開始日と終了日を入力してそれに合わせてセルの範囲を選びセルの色を変えて塗っていくというほぼ手入力をしていたと思います。

せっかくエクセルを使っているのに見積もった作業日数からシンプルにガントチャートを作るということさえ自動的にできないのでしょうか。そこで作りました。この手のツールは機能が複雑であることが問題だと考えているので、作業日数から単純に並べるだけの機能に絞っています。シンプルなのでタスクを並列させるように変えるのも既存のエクセルの知識さえあれば簡単にできます。機能が複雑になるマクロすら使っていません。セキュリティ的にも安心です。

以下は Windows 版 Excel 2016 を使っています。無料で使え再配布や改変も自由です。なお、オンライン版エクセルでは動作しません。

シンプルエクセルガントチャートの使い方

作業日数から自動的にガントチャートが作られるエクセルの使い方を説明します。
ダウンロードするだけで使い始めることができますが、そのダウンロードできるファイル自体の作り方も後半で説明しています。

シンプルエクセルテンプレートをダウンロードします

シンプルエクセルテンプレートは下記からダウンロードできます。

このテンプレートは無料で使えます。再配布も改変も自由です。

ダウンロードしたファイルはエクセルのテンプレートです。ダブルクリックして開くと、ファイル名の末尾に数字が追加されたエクセルファイルが開きます。初めて保存するときにそのファイル名でいいかを確認するかのようにファイルを保存するダイアログが表示されます。

開始日の設定

エクセルテンプレートを開くと、プロジェクトの開始日が今日の日付になっています。プロジェクトの開始日のセルの内容を見ると、TODAY 関数が呼ばれていることが分かります。

=TODAY()

このままでは、明日開いたときに日付がずれてしまうため、開始日に固定しましょう。

=2023/1/1

祝日・休日を設定します

古いテンプレートを使っている場合、または計画的な休日がある場合、祝日シートの調整が必要です。

すべての祝日の年月日を含むようなセル範囲に対して「_祝日」という名前が付けられています。セル範囲の設定内容を確認するには、
数式メニュー >>（定義された名前グループ）>> 名前の管理
を選びます。

ガントチャートの土日祝日休日の列に斜線のパターンが表示されます。

タスクの名前と担当者と計画日数を入力します

それぞれのタスク（行）について、

タスク名（B列）
担当者（C列）
計画日数（H列）整数のみ

を入力します。

計画日数だけ入力しても、ガントチャートは作られます。

ただし、日数に小数を入力することはできません。丸め誤差が累積されて正しく計算されなくなることを防ぐために整数以外を入力できないうようにしています。

整数にするときに切り上げるべきか切り下げるべきかは、全体の日数が計画に合うように調整します。たとえば、タスクAが 1.5日、タスクBが 2.5日と予想したら、タスクAを 2日、タスクBを 2日と入力するか、タスクAを 3日、タスクBを 1日と入力します。日数を小数単位まで書いたほうが厳密に考えているように見えて良いと考える人がいるかもしれませんが、小数単位まで書いたからといって予定通り進むようになることはありません。

タスクの詳細や補足情報は、タスク名のセルのコメントに書くとよいでしょう。もちろん詳細の列を追加しても構いません。

インデントを深くするには、Excel >> ホームメニュー >>（配置グループ）>> インデントを増やすを選びます。

タスクを増やします

タスク（行）を増やすには、次の手順で行わなければガントチャートが壊れてしまいます。

行番号を右クリック >> 挿入
挿入した行の上の行の B列〜I列をコピー（タスク名〜計画日数の右隣）
挿入した行に B列〜I列を貼り付け
挿入した行の下の行の E列（開始日）が警告されるので、挿入した行の F列（先行タスクの終了日）を参照するように修正

タスク（行）を減らすときも、少し調整が必要です。

行番号を右クリック >> 削除
削除した行の下の行の E列（開始日）が警告されるので、削除した行の上の行の F列（先行タスクの終了日）を参照するように修正

もし、タスクの追加するときに「挿入して貼り付け」を行うと、ガントチャートの部分に設定されている条件付き書式の範囲が徐々に壊れていってしまいます。もし壊れたときは、条件付き書式の範囲をガントチャート全体、つまり、シンプルに J8（左上）〜 BM28ぐらい（右下端）に直してください。

タスクの依存関係を調整します

1つのタスクが終わったら次の行のタスクを始めるという依存関係が開始日の式に設定されていますが、並列するタスクに変更することもできます。

1つ目のタスク

=プロジェクトの開始（E3 セル）

2つ目以降のタスク

=WORKDAY(__前の終了日セル__,1,_祝日)

並列するタスクは、開始日に固定値もしくは先行するタスクを1つ上以外のタスクに設定します。

結合するタスクの開始日は、先行するいくつかのタスクのうち、最後に終わるタスクの終了日の次の日に設定します。具体的には、先行するそれぞれのタスクの終了日のうち最大値を計算する MAX 関数 を使います。

ただし、一人で並行する場合、計画日数は 50%などのリソースが使えるものとして設定します。たとえば、1日の100％をタスクAの作業に使ったときに3日かかると見積もった場合、1日の50％しかタスクAの作業に使えないのであれば6日を入力します。日数のセルのコメントに50％のリソースだけ使えることを書いておくとよいでしょう。

進捗状況として残り日数を入力し、ガントチャートを表示します

進捗状況は、% ではなく、予想される残り日数を入力します。最後で説明するように、進捗状況は % ではなく残り日数で入力することで 90% シンドローム を防ぎます。

残り日数見積（G列）整数のみ

残り日数を入力すると、進捗状況の % の値と、ガントチャートのグラフの一部の色が進捗状況に合わせて自動的に変化します。

最初に1日作業したら、1ではなく計画日数 - 1 を入力します。空欄にしたら進捗 0% になります。なお、残り日数に小数を入力することはできません。丸め誤差が累積されて正しく計算されなくなることを防ぐため整数以外を入力できないうようにしています。

進捗状況は、今日に合わせる必要はありません。むしろ今日に合っていることのほうが少ないです。

ガントチャートを作る目的は、予定通り進んでいるか遅れているかを早い段階で明らかにして対策を取れるようにするためです。 遅れていることを黙って日程通り進んでいるように見せておいて締め切り直前に遅れましたと爆弾発言すると嘘をついてきたことになり信頼が下がりますが、遅れはじめたときに機能削減や期間延長するなどの対策をしたほうが信頼は上がります。ただし、締め切り少し前に遅れをまとめて説明したほうがミスを小さく見せられる相手もいるのでその場合はガントチャートを2つ作るなどして使い分けましょう。そのもう1つのガントチャートはパワポかもしれません。

スクロールします

しばらく使っていると今日の位置が右の遠くのほうになってしまうのでスクロールしたいと思いますが、このガントチャートのエクセルは残念ながらスクロールする方法が特殊です。スクロールバーを使うのではなく、「週表示」のセルの数字を増減させます。 1増やすと 1週分右に（未来に）スクロールします。

エクセルガントチャートテンプレートの作り方

上記の最初でダウンロードしたエクセルはそのまま使えますが、非公式のページからダウンロードしたエクセルをそのまま使うことに抵抗がある方もいらっしゃると思います。そういう方のために、エクセルガントチャートの作り方も説明します。自作のエクセルガントチャートに組み込むときなどの参考にしてください。

元になるエクセルテンプレートをダウンロードします

マイクロソフトのホームページからガントチャートのエクセルテンプレートをダウンロードします。

ダウンロードしたファイルはエクセルテンプレートです。ダブルクリックして開くと、ファイル名の末尾に数字が追加されたエクセルファイルが開きます。ファイル名の末尾が win32 なので win321 という名前になります。変な前なのでエクセルテンプレートのファイル名を変えたほうがいいですね。たとえばこんなふうにします。

シンプル-ガント-チャート_tf16400962_win32_.xltx

開くとわかりますが Vertex42 さんが作ったファイルです。ガントチャートとしてのエクセルの使い方を説明されている動画も公開されていますので、そちらも一緒に参考にしてください。

なお、開始日と終了日を入力して自動的にガントチャートに色を塗るだけであれば、このテンプレートをそのまま使うことができます。

日数の列を作ります

残り日数を入力する列と、全体の日数を入力する列と、計算用の列を作ります。

全体の計画日数（H列）:
    H列を表示します:
        G列からI列まで選択 >>（右クリック）>> 再表示
    タイトル（H5セル）を変更します:
        計画日数
    デフォルト値を設定します:
        5 など （式ではなくします）
残り日数見積（G列）:
    列の幅を広げます:
    タイトル（G5セル）を入力します:
        残り日数見積
    デフォルト値を設定します:
        最初: 2  #// サンプル
        その他: 空欄のまま
計算用の列（I列）:
    列を追加します:
        I列を右クリック >> 挿入
    タイトル（I5セル）を入力します:
        計算
    式を設定します:
        =MIN(G9:H9)  （I9セルの場合）
    文字を小さくして色を薄くします:
全体の計画日数（H列の各タスク行）と残り日数見積（G列）:
    整数以外は入力できないようにします:
        データ >>（データツール グループ）>> データの入力規則 >> データの入力規則 >>
        入力値の種類＝リスト >> 0,1,2,3,4,5,...
週表示(E4セル):
    入力値の種類＝リスト >> -5,-4,-3,-2,-1,0,1,2,3,4,5,...

祝日シートを作ります

作業日数から自動的にガントチャートを作る場合、祝日・休日の設定が必須になります。

新しいシートを「祝日」という名前で作ります:
今年と来年の祝日の年月日を入力します:
すべての祝日・休日の年月日を含むようなセル範囲に対して「_祝日」という名前を付けます:

セル範囲に名前を付けるには、
数式メニュー >>（定義された名前グループ）>> 名前の管理 >> 新規作成
を選びます。

土日祝日を斜線等で表示させます

条件付き書式の条件の1つに、土日祝日を斜線等で表示させるルールを追加します。

メニュー: （ガントチャートのグラフの表示範囲 J8:BM25 を選択）>> ホーム メニュー >> 条件付き書式 >> 新しいルール >>
    数式を使用して、書式設定するセルを決定（最も下）
数式（次の数式を満たす場合に値を書式設定）:
    式: =NETWORKDAYS(__Day__,__Day__, _祝日)=0
    __Day__: 日付のセル。ただし、J$5形式（アルファベットの前の $ を削除すること）
書式 ボタン >> 塗りつぶし タブ:
    パターンの色: 灰色
    パターンの種類: 斜線（など）

NETWORKDAYS 関数は、稼働日数を計算します。

=NETWORKDAYS(__StartDay__,__LastDay__,__Holiday__)

    __StartDay__: 開始日。セルへの参照
    __LastDay__: 締切日。作業できる最後の日。セルへの参照
    __Holiday__: 祝日。複数セルへの参照

今日を示すラインを最も手前に表示させます

最も優先する条件付き書式にします。

（ルールがある範囲のうち1つのセルを選択）>> ホーム メニュー >> 条件付き書式 >> ルールの管理 >>
（今日を示す赤いラインの行を選択）>> 上に移動するボタン）

開始日・終了日・進捗状況のセルの内容を編集します

開始日・終了日・進捗状況を自動的に計算する式を入力します。

開始日（E列）:
    最初のタスク: =プロジェクトの開始（E3 セル）
    続いて行うタスク: =WORKDAY(__前の終了日セル__,1,_祝日)
        #// E10 セルの場合 =WORKDAY(F9,1,_祝日)
終了日（F列）:
    式: =WORKDAY(__StartDay__, __Days__-1, _祝日)
        #// F9 セルの場合 =WORKDAY(E9, H9-1, _祝日)
        #// 同様のセルにコピーします
    __StartDay__: 開始日のセル。ただし、$E$9 の $ を削除して E9 のようにすること
    __Days__: 日数のセル。ただし、$H$9 の $ を削除して H9 のようにすること
進捗状況（D列 ％）:
    式: =(__Days__ - IF(__LeftDays__="", __Days__, __LeftDays__)) / __Days__
        #// D9 セルの場合 =(H9 - IF(G9="", H9, G9)) / H9
日付の書式を日本の書式にします:

WORKDAY 関数は、稼働日のみの日付の加減算をします。

=WORKDAY(__StartDay__,__LastDayOffset__,__Holiday__)

    __StartDay__: 開始日。セルへの参照を推奨します
    __LastDayOffset__: 日数 - 1。 0= 当日 1日
    __Holiday__: 祝日。複数セルへの参照

フェーズの行を入力します

フェーズの行をそれぞれのタスクをまとめた内容にします。

開始日(E8セル):
    式: =MIN(__E列のタスクの範囲__)
終了日(F8セル):
    式: =MAX(__F列のタスクの範囲__)
計画日数, 計算(H8,I8):
    式: =SUM(__H列I列のタスクの範囲__)
残り日数見積(G8):
    式: =I8
フェーズの行を太字にします:

ガントチャートを表示するように設定します

自動的にガントチャートのグラフを表示するために、条件付き書式を編集します

ガントチャート全体:
    メニュー: （ガントチャートの左上端 J8 のセルを選択）>> ホーム メニュー >> 条件付き書式 >> ルールの管理
    すべての適用先: =$J$5:$BM$16
完了部分:  #// 灰色部分。日付範囲部分より手前
    メニュー: （ガントチャートの左上端 J8 のセルを選択）>> ホーム メニュー >> 条件付き書式 >> ルールの管理 >> （灰色をダブルクリック）
    数式（次の数式を満たす場合に値を書式設定）:
        式: =AND($E5<=J$5, J$5 <= WORKDAY($E5, IF($G5="", -1, $H5 - $G5 - 1),_祝日), $F5>0)  #// 条件付き書式の適用先の左上が J5 の場合
            #template_: =AND(__StartDay__<=__Day__, __Day__ <= WORKDAY(__StartDay__, IF(__LeftDays__="", -1, __Days__ - __LeftDays__ - 1,_祝日), __LastDay__>0)
        __StartDay__: 開始日のセル。ただし、$E5形式（$E$5 の $ を削除して $E5 のようにすること）
        __Day__: セル範囲の最も左の列の日付のセル。ただし、J$5形式（アルファベットの前の $ を削除すること）
        __LastDay__: 終了日のセル。ただし、$F5形式（$F$5 の $ を削除して $F5 のようにすること）
日付範囲部分:  #// 未完と完了の両方。ただし、完了部分より優先度が低い
    メニュー: （ガントチャートの左上端 J8 のセルを選択）>> ホーム メニュー >> 条件付き書式 >> ルールの管理 >> （紫色をダブルクリック）
    数式（次の数式を満たす場合に値を書式設定）:
        式: =AND($E5<=J$5, J$5<=$F5, $F5>0)  #// 条件付き書式の適用先の左上が J5 の場合
            #template_: =AND(__StartDay__<=__Day__, __Day__<=__LastDay__, __LastDay__>0)
            #// =AND(タスク_終了>=J$5,タスク_開始<J$5)
        __StartDay__: 開始日のセル。ただし、$E5形式（$E$5 の $ を削除して $E5 のようにすること）
        __Day__: セル範囲の最も左の列の日付のセル。ただし、J$5形式（アルファベットの前の $ を削除すること）
        __LastDay__: 終了日のセル。ただし、$F5形式（$F$5 の $ を削除して $F5 のようにすること）

進捗状況のグラフを計算するように変更します

進捗率は手入力するのではなく、残り日数から計算します。

進捗状況の列(D列):
    =(H9 - IF(G9="", H9, G9)) / H9
        #// D9セルの場合

データバー（セル内グラフ）を設定するには、
（セルを選択）>> ホームメニュー >> 条件付き書式 >> ルールの管理 >> 新規ルール >> 書式スタイル = データバー

エクセルテンプレートとして保存します

以上で完成したエクセルガントチャートをエクセルテンプレートの形式で保存します。拡張子は .xltx に変わります。テンプレートを編集するときは、ファイルを右クリックして開くを選びます。

効果の検証

もし、パーセントではなく残りの作業日数見積を入力でき、追加タスクやレビューなどの定義されたタスクやバッファーを追加したときに、進捗率やガントチャートがすぐに更新されれば、90% シンドローム を予防することができます。90%シンドロームとは、ゴールが見え始めたときや作業が進んでいることを見せたいときに進捗を 90% と報告してしまう心情的症状です。一度90%と報告するとパーセントを減らしにくく、かといって増やすのも限界があるので、実質スケジュール管理ができなくなります。

ネットを検索してみたところ、ガントチャートのエクセルテンプレートが見つかりました。誰でもダウンロードして無料で使うことができるのですがそのまま使うにはイマイチでした。そこで改良してみたところ、90% シンドロームを防ぐための手法がとれるガントチャートのエクセルができました。

ただし、使ってみると気づくと思いますが 90% シンドロームを防いだところで必要な作業量が減るわけではありません。スケジュールが甘い人より長めのスケジュールを最初から見せることになります。なので相手によっては正直に長めのスケジュールを見せるよりも、短めのスケジュールを見せたほうがいいでしょう。また、計画を変更することは滅多にせず、たとえば20％より遅れが大きくなった場合にスケジュールを変更する「リカバリー限界の法則」を採用しているところも多いです。それらの場合、正直ベースの最新スケジュールは自分だけが持っていることになるかもしれません。それらを踏まえつつ活用されることをおすすめします。