エクセルの進捗管理で予定と実績を並べる｜差が見える表の作り方
エクセルの進捗管理で予定と実績を並べたいと考えるのは、たいてい「遅れているのは分かるが、どこがどれだけ遅れているのか説明できない」という場面に立ったときです。予定だけの表は計画書であり、実績だけの表は日報です。2つを同じ行に並べて初めて、差が数字になります。この記事では、何を予定と呼ぶかを決めるところから、列の並べ方、条件付き書式で予定と実績を2段のバーにする手順、差の読み方、そして実績が集まらないときの直し方までを順に整理します。
予定と実績を並べる目的を先に決める
作り始める前に確認しておきたいことがあります。この表を何のために作るのか、という点です。目的が違うと、必要な列が変わります。
目的1は、いま手を打つべき場所を見つけることです。 進行をとりまとめる立場の人が使う場合、これがほとんどです。全体として遅れているかどうかより、どの作業が止まっているかを知りたい。この場合、必要なのは作業単位の差であり、精度の高い工数の記録ではありません。
目的2は、次の見積もりの精度を上げることです。 同じ種類の仕事を繰り返し受けている場合、予定と実績の差を蓄積すると、次回の見積もりが現実に近づきます。この場合は、工数の記録が要ります。日数だけでは、人がどれだけ張り付いたかが分かりません。
目的3は、外部への説明です。 発注者や社内の別部署に、遅れの理由と回復の見込みを説明する。この場合は、差そのものより、差が生じた原因を書ける欄が要ります。
3つを1枚の表で全部やろうとすると、列が増えて誰も埋めなくなります。実務では目的1を主にして、必要になったときに目的2と3の列を足すのが現実的です。最初から完璧な表を作ろうとしないでください。 埋まらない列が並んだ表は、埋まっている列の信用まで落とします。
「予定」と「実績」が指すものを1つに決める
予定と実績という言葉は、少なくとも3つの意味で使われています。混ざったまま表を作ると、集計した数字の意味が分からなくなります。
系統1は、日付です。 予定の開始日と終了日、実績の開始日と終了日。いちばん作りやすく、誰でも入力できます。遅れが日数で出るため、説明もしやすい形です。
系統2は、工数です。 予定していた時間と、実際にかかった時間。見積もりの精度を上げたい場合はこちらが要ります。ただし、正確に記録するには入力の習慣が必要で、そこが続かないと数字が嘘になります。かかった時間を後から思い出して入れた記録は、実態より短く書かれる傾向があります。
系統3は、出来高や進捗率です。 その作業のうち何割が終わったか。工程の途中経過を見たい場合に使いますが、割合の判断が主観に寄るという弱点があります。8割まで終わったという申告が、実際には半分だった、ということが起きます。
3つのうちどれを主にするかは、仕事の性質で決めます。作業の順序が決まっていて、終わったかどうかがはっきりする仕事は系統1が向いています。人の時間そのものを売っている仕事は系統2、量で測れる仕事は系統3です。
混ぜる場合は、列の名前で明示してください。 「予定」とだけ書かれた列が2つあると、片方が日付で片方が工数だという状態が生まれます。「予定開始」「予定工数」のように、必ず何の予定かを書きます。
もう1つ、どの単位で比べるかも決めておきます。 作業単位で比べるのか、週単位で比べるのか、案件全体で比べるのか。作業単位は細かく見えますが件数が多くなり、案件全体は分かりやすい代わりに手を打つ場所が特定できません。実務では、作業単位で記録して、週単位に集計して見る形が扱いやすくなります。記録の粒度と、見る粒度を分けて考えるのがコツです。
1行1作業で列を並べる
基本の形は決まっています。左から順に、次のように置きます。
| 列 | 内容 | 入力する人 |
|---|---|---|
| 番号 | 通し番号 | 作成者 |
| 作業名 | 何をするか | 作成者 |
| 担当 | 誰がやるか | 作成者 |
| 予定開始 | いつ始める予定か | 作成者 |
| 予定終了 | いつ終える予定か | 作成者 |
| 実績開始 | 実際に始めた日 | 担当者 |
| 実績終了 | 実際に終えた日 | 担当者 |
| 状態 | 未着手、進行中、完了 | 担当者 |
| 差 | 数式で自動計算 | 自動 |
| 備考 | 遅れの理由など | 担当者 |
この並びには理由があります。作成者が埋める列を左に、担当者が埋める列をその右に置くことで、入力する場所が視覚的にまとまります。あちこちに分散していると、入力漏れが増えます。
差の列は数式で出します。終了日の遅れなら =F2-E2 のように、実績終了から予定終了を引きます。プラスなら遅れ、マイナスなら前倒しです。日付が入っていない行でエラーになるのを避けるには、=IF(F2="","",F2-E2) のように空欄を判定する形にしておきます。
工数も見る場合は、予定工数と実績工数の列を足し、差を =IF(H2="","",H2-G2) で出します。工数は時間単位か人日単位のどちらかに統一してください。人によって単位が違うと、合計が意味を失います。
行の粒度は、1日から5日で終わる作業が扱いやすい範囲です。1日未満まで割ると行数が増えすぎ、1週間を超えると途中の遅れが見えません。2週間かかる作業は、途中に確認できる区切りを入れて2行か3行に割ってください。
予定の側の立て方で、差の意味が変わる
実績の話をする前に、予定の立て方に触れておきます。ここが雑だと、出てくる差は「見積もりが甘かった」以上の意味を持ちません。
余裕の置き方を決めます。 各作業に少しずつ余裕を足す方法と、最後にまとめて余裕を置く方法があります。前者は、余裕があると分かっている作業が余裕を使い切るという性質があり、結局は全体が延びます。後者は、どこまで余裕が残っているかが1か所で見えるため、判断がしやすくなります。予定と実績を比べる表を作るなら、後者のほうが読みやすい数字が出ます。
担当者に予定を出してもらうか、こちらで置くかを決めます。 担当者に出してもらうと現実的な日数になりますが、余裕を含んだ数字が返ってきます。こちらで置くと計画としては締まりますが、実績が常に遅れる表になります。どちらでも構いませんが、途中で方針を変えないでください。前半と後半で予定の性質が変わると、差の推移を比べられなくなります。
予定を更新するかどうかも決めます。 遅れが出たときに予定日を書き換えると、差はゼロに戻ります。書き換えないと、遅れが積み上がって表が赤くなります。実務では、当初の予定を別の列に残したまま、現在の見込みを更新する列を作る形が使われます。列が1つ増えますが、当初計画からのずれと、直近の見込みからのずれを、両方見られるようになります。
予定に入れるべきなのは作業だけではありません。 確認、承認、先方からの返答待ち。これらを行として入れておかないと、実際には待ちで止まっているのに「作業が遅い」という読み方になります。待ちの行を明示しておくと、遅れの理由が自動的に分類されます。
予定と実績を2段のバーで見せる
数字の列だけでは、全体の重なりが読めません。日付の右側に日付の列を並べ、条件付き書式で色を塗ると、予定と実績を重ねたガントチャートになります。
作り方は次のとおりです。
手順1として、右側に日付の行を作ります。 1つの列を1日に割り当て、見出し行に日付を入れます。日数が多い場合は、1列を1週間にしても構いません。
手順2として、予定のバーを塗ります。 塗りたい範囲を選び、条件付き書式の数式ルールに =AND(K$1>=$D2,K$1<=$E2) のような条件を入れます。K列の1行目が日付、D列が予定開始、E列が予定終了です。列は相対参照、行は絶対参照という組み合わせを間違えると、思ったところが塗られません。
手順3として、実績のバーを別の色で塗ります。 同じ範囲に、=AND(K$1>=$F2,K$1<=$G2) の条件をもう1つ足します。条件付き書式は複数のルールを重ねられるので、予定を薄い色、実績を濃い色にしておくと、遅れが視覚的に読めます。実績が予定より右に伸びていれば遅れです。
手順4として、今日の位置に線を入れます。 =K$1=TODAY() の条件で列全体に色を付けると、今日がどこかが一目で分かります。この1本があるだけで、表を読む速度が変わります。
手順5として、遅れている行を目立たせます。 予定終了を過ぎているのに実績終了が空欄の行は、いま止まっている作業です。=AND($G2="",$E2<TODAY()) の条件で作業名のセルに色を付けると、表を開いた瞬間に止まっている行が浮かび上がります。バーの色だけに頼ると、日付の列がスクロールで見えていないときに気づけません。左端の列にも印を出しておくと、確実です。
手順6として、色数を絞ります。 予定、実績、今日、遅れ。この4つで足ります。分類や担当ごとにも色を付け始めると、どの色が何を意味するのか分からなくなり、結果として全部が無視されます。表の色は、判断に使うものだけに限ってください。
1行に2段のバーを表示したい場合は、作業ごとに2行使う方法もあります。上の行に予定、下の行に実績を置く形です。見やすさは上がりますが行数が倍になるので、作業が30件を超えるなら1行に重ねるほうが扱いやすくなります。
バーの見せ方には、もう1つ選択肢があります。日付の列を1日単位にせず、週単位にする方法です。3か月を超える案件では、1日単位だと横に長くなりすぎて全体が視野に入りません。週単位にすると、1画面で半年分が収まります。細かい遅れは読めなくなりますが、全体の流れを見る用途では、そのほうが役に立ちます。日単位の詳細版と週単位の全体版を、同じデータから2枚作っておく形も実務ではよく使われます。
条件付き書式を使う際に気をつけたいのは、適用範囲が広がりすぎると再計算が重くなる点です。日付の列を365日分用意して全部にルールを当てると、ファイルを開くだけで待たされます。表示する期間は3か月程度に絞り、必要になったら日付をずらす形にしてください。
差をどう読み、どこから手を打つか
差の列が埋まってきたら、次は読み方です。数字を並べただけでは判断になりません。
まず、全体の合計より、遅れている件数を見ます。 差の合計がゼロでも、大きく遅れている作業と大きく前倒しの作業が打ち消し合っているだけかもしれません。=COUNTIF(I2:I50,">0") のように、遅れている件数を数えます。
次に、遅れの大きい順に並べます。 上位の数件が全体の遅れの大半を占めていることが多く、そこに手を打てば全体が動きます。すべての遅れに均等に対応しようとすると、どれも中途半端になります。
そして、後続に影響するかどうかで分けます。 遅れているが後ろに何も控えていない作業と、遅れると次が始められない作業では、緊急度がまったく違います。表の上でこれを見分けるには、先行作業の番号を書く列を1つ足すのが簡単です。数式で自動判定するところまでやると維持が重くなるので、目視で足りる規模なら番号を書くだけで十分です。
差が出た理由も、分類しておくと後で効きます。 待ちが発生した、想定より作業量が多かった、担当者が別件に取られた、仕様が変わった。この4つに分けるだけでも、次回の計画に反映できます。理由を自由記述にすると集計できないので、備考欄とは別に分類の列を作り、ドロップダウンで選ばせる形が扱いやすくなります。
推移も見てください。 ある週の差だけを見ると、たまたまその週に集中しただけかもしれません。週ごとの遅れ件数を横に並べて記録しておくと、増えているのか収まりつつあるのかが分かります。記録は別のシートに、日付と件数と遅れ日数の合計を1行ずつ足していくだけで足ります。手間は1分ですが、3か月経ったときの情報量はまったく違います。
前倒しにも目を向けます。 予定より早く終わっている作業が続いているなら、その種類の作業は見積もりが過大です。次の計画で日数を詰められます。遅れだけを見ていると、計画は毎回甘いままになります。
注意しておきたいのは、この表を評価に使わないことです。 遅れの記録が担当者の評価に直結すると、実績の入力が甘くなります。終わっていないのに完了と入る、実際より短い工数が入る。そうなると表の数字は現実から離れ、判断の材料として使えなくなります。差は個人の成績ではなく、計画の精度を測るものだという位置づけを、最初に共有しておいてください。
遅れの理由を、外の要因と内の要因で分けます。 先方の返答待ち、資材の入荷待ち、承認待ちは外の要因です。作業量の見誤り、手戻り、担当者の割り当て不足は内の要因です。外の要因が大半を占めているなら、社内の頑張りでは解決しません。その場合に必要なのは、相手に早く返してもらうための仕組み、たとえば依頼の出し方を変えることや、待ち時間そのものを予定に織り込むことです。内と外を分けずに集計すると、直せない部分にも対策を立てようとして、時間だけが消えます。
週に一度、15分で回す運用の型
表は作った時点では機能しません。回す型が決まって初めて使われます。おすすめは、週に一度、決まった時刻に短く見る形です。
開始前に、実績の入力を締め切ります。 打ち合わせの前日までに各自が入力を済ませる、と決めておきます。打ち合わせの場で入力すると、その時間がすべて入力に消えます。
見る順番を固定します。 第一に、遅れている件数。第二に、遅れの大きい上位3件。第三に、今週始まる予定の作業。この3つだけで15分は埋まります。全部の行を順に読み上げる形にすると、時間がいくらあっても足りず、かつ誰も集中しません。
決めることを1つに絞ります。 遅れている作業について、担当を替えるのか、期限を動かすのか、範囲を削るのか。この3択のどれかを決めて終わります。決めずに「注意する」で終わった項目は、翌週も同じ状態で出てきます。
決めたことを、その場で表に書き戻します。 期限を動かしたなら予定の列を更新し、範囲を削ったなら作業名を書き換えます。口頭で決めて表に反映しないと、次の週には食い違いが起きます。
この型が回り始めると、表は報告のための書類ではなく、判断のための道具になります。逆に、月に一度しか見ない表は、見るころには手遅れになっています。差を早く見つけることが、この表の唯一の価値です。
作りでよくある6つの失敗
同じ失敗が繰り返し起きています。先に知っておくと避けられます。
失敗1は、列を増やしすぎることです。 進捗率、優先度、種別、関連資料、承認者、備考。あると便利そうな列を全部足すと、横に長い表ができます。横スクロールが必要な表は、入力する人が自分の行を見失います。列は、実際に埋まっているものだけを残してください。
失敗2は、日付を文字列で入れることです。 「9/4」や「9月4日ごろ」と手で書くと、引き算ができません。日付として入力し、表示形式で見た目を整えるのが正しい形です。入力の揺れを防ぐには、データの入力規則で日付だけを受け付ける設定にしておきます。
失敗3は、セルの結合です。 見出しを整えるために結合すると、並べ替えもフィルタも使えなくなります。進捗管理の表では、結合は使わないのが原則です。書式の設定にある選択範囲内で中央という配置を使えば、結合せずに同じ見た目になります。
失敗4は、行の途中に空行を入れることです。 見やすさのために区切りの空行を入れると、フィルタの範囲がそこで切れます。区切りたいなら、分類の列を作って色を変えるほうが安全です。
失敗5は、ファイル名で版を管理することです。 進捗表_v2、進捗表_最新、進捗表_9月4日。この形になると、どれが正しいのかを確かめる作業が毎回発生します。1つの場所に置いた1つのファイルを全員で開く形にできるなら、そのほうが確実です。できない環境なら、置き場所を1か所に決めて、そこにあるものだけを正とすると宣言してください。
失敗6は、同じ情報を2か所に持つことです。 進捗率の列と、状態の列と、チェックの列。3つとも進み具合を表しているのに、更新のタイミングが違うため、そのうち食い違います。進み具合を表す列は1つに決めてください。
これらはどれも、表が小さいうちは問題になりません。行が100行を超えたあたりから、一斉に効いてきます。最初から避けておくほうが、あとで直すより圧倒的に安く済みます。
実績が集まらない3つの理由
予定と実績の表がうまくいかないとき、原因のほとんどは実績側にあります。予定は作成者が一度書けば済みますが、実績は全員が繰り返し入力する必要があるためです。
理由1は、入力の手数が多いことです。 ファイルを開き、自分の行を探し、日付を入れ、保存し、閉じる。この一連の動作に3分かかるなら、忙しい日には飛ばされます。手数を減らす工夫としては、担当者ごとにフィルタを用意する、今日の日付を入れるショートカットを周知する、入力欄を左側にまとめる、といったものがあります。
入力の単位を粗くするのも有効です。開始日と終了日の両方を求めず、終了日だけにする。工数は分単位ではなく半日単位にする。精度を落とすと聞くと抵抗があるかもしれませんが、入らない正確な記録より、入る粗い記録のほうが判断には使えます。
理由2は、入力する意味が伝わっていないことです。 実績を入れることが管理のための作業だと受け取られると、優先度は最下位になります。差を見て何を変えたのかを、実際に共有してください。入力した結果として自分の負荷が減った経験があると、入力は続きます。
理由3は、同時に触れないことです。 ファイルを誰かが開いていると、他の人は読み取り専用でしか開けない。この状態では、入力しようとした人がその場で諦めます。Microsoft は、同時に作業する仕組みについて次のように案内しています。
同僚と一緒に同じ Excel ブックを開いて作業できます。 これは共同編集と呼ばれます。 出典: support.microsoft.com
同じページには、共同編集を使うための条件も書かれています。Microsoft 365 のサブスクリプションが必要であること、ブックを OneDrive、OneDrive for Business、または SharePoint Online のライブラリに置く必要があること、ファイル形式が .xlsx、.xlsm、.xlsb のいずれかであること。あわせて、Microsoft がホストしていない環境の SharePoint では共同編集がサポートされないことも明記されています。社内のファイルサーバーに置いたファイルを全員で開く運用では、この仕組みは使えません。
条件を満たしていない環境で「同時に触れない」問題を解決しようとすると、結局は誰か1人が代表で入力する形に落ち着きます。その形にした瞬間、実績の入力はその人の仕事になり、報告と入力の間に時差が生まれます。
回避策として使われるのが、担当者ごとにファイルを分けて、後で1つに集める方法です。同時に触る問題は消えますが、集める作業が毎週発生し、集めた結果を統合するときに書式のずれや行の重複が起きます。人数が4人を超えると、この統合作業だけで週に30分を使うことになります。分けるのは、あくまで一時的な策として考えたほうがよいです。
報告用に出すときの整え方
社内や社外に出す資料として使う場合、入力用の表をそのまま渡すのは避けたほうがよい場面があります。
渡す相手が見たい粒度は、入力する粒度より粗いのが普通です。 作業80行の表をそのまま出しても読まれません。工程の大分類ごとに集計した数行と、遅れている上位数件だけを載せた形にすると、伝わり方が変わります。集計はピボットテーブルで作れば、元の表を触らずに済みます。
印刷を前提にするなら、範囲と改ページを先に設定します。 印刷範囲を指定し、見出し行を各ページに繰り返す設定を入れておきます。日付のバーを含めると横に広がるため、印刷の向きを横にするか、表示する期間を絞ってください。
渡した資料は、その時点で止まります。 週次で配る運用にすると、受け取った側は古い情報を持ち歩くことになります。日付を必ず入れて、いつ時点のものかを明示してください。頻繁に変わる案件では、資料を配るより、同じ場所を見てもらうほうが食い違いが起きません。
社外に出す場合は、含まれる情報を確認します。 担当者の氏名、社内の見積工数、遅れの理由に書いた社内事情。入力用の表には、外に出すべきでない情報が混ざっていることがあります。列を非表示にしただけでは中身は残るので、別ファイルに必要な列だけを値として貼り付ける形にしてください。
どこまで表計算で、どこから別の形にするか
予定と実績を並べる表は、条件が合えば非常に強い道具です。続けやすい条件は次のとおりです。
・作業が50件程度までで、期間が数か月で終わる ・実績を入れる人が3人以内 ・全員がパソコンの前で作業している ・案件が1つ、または少数
この範囲であれば、表計算のほうが小回りが利きます。列を足すのも、集計の切り口を変えるのも自由です。特に、案件ごとに見たい切り口が違う仕事では、自由に列を作れることの価値は大きく、専用の道具より速く形にできます。すでに手元にあって追加の費用もかからないため、この条件に収まっているなら替える理由はありません。
一方、次の状態になると、表の維持そのものが仕事になります。
・実績の入力が週の終わりにまとめて行われ、日付が実態とずれている ・案件が増えてファイルが分かれ、横断して見るために別の集計表を作っている ・条件付き書式のルールが増えて、ファイルを開くのに時間がかかる ・誰がいつ入力したかを追えず、数字の食い違いを解決できない
このうち1つ目が本質的な問題です。実績は、発生した時点で記録されて初めて正確になります。まとめて入れた実績は、日付も工数も丸められます。丸められた実績から出した差は、次の見積もりの材料になりません。
2つ目も見過ごせません。案件ごとにファイルを作る形は、1件目と2件目までは快適ですが、3件を超えると横断して見る手段が無くなります。誰が何件抱えているのか、今週どの案件が危ないのかを知るために、別の集計表を作ることになり、その集計表の維持が新しい仕事として増えます。集計表が古くなると、元の表と食い違い、どちらが正しいのか確かめる作業まで発生します。
この点を解決する方向として、状態そのものを動かす形の道具があります。ボード型のタスク管理では、カードを次の列へ動かした時刻が自動で残るため、実績の開始日と終了日を人が入力する必要がありません。入力が要らないので、日付が丸められません。予定は期限として持たせ、実績は動かした記録として自動で溜める。この分担にすると、実績側の入力負担がほぼゼロになります。
道具を選ぶときは、必要な表示形式が上位のプランに閉じ込められていないかを確認してください。予定と実績を見比べたいだけなのに、そのための表示形式が最上位でしか使えない設計だと、試す段階で判断ができません。機能で絞らず、区切るのは人数とボードの数だけという考え方であれば、最初から必要な形で試せます。何が使えるかはできることに、区切りの条件は料金にまとまっています。
他のサービスとの設計の違いは比較の一覧にあります。付箋を並べる形から出発した作りとの違いはTrelloとの比較、仕事の割り当てと進行を主軸に置いた作りとの違いはAsanaとの比較、課題管理と開発の記録を重ねる作りとの違いはBacklogとの比較、表計算に近い感覚で表を組む作りとの違いはmonday.comとの比較にまとめています。すでにボードがある場合の移し替えはTrelloからの移行、社外の人を入れるときの権限やデータの扱いは安全性の考え方、判断に迷う点はよくある質問を確認してください。
最後に、道具を替えても解決しないことを1つ挙げます。予定が現実的でない場合、実績との差は必ず出続けます。毎回同じ方向に同じくらいずれているなら、それは実績の問題ではなく見積もりの問題です。差を記録する目的の半分は、そこに気づくことにあります。差が縮まらないまま数か月が過ぎているなら、直すべきなのは実績の入力ではなく、予定の立て方のほうです。
Q1. 予定と実績は日付と工数のどちらで管理すべきですか？
目的で決めます。いま手を打つ場所を見つけたいなら日付だけで足り、次回の見積もりの精度を上げたいなら工数の記録が要ります。両方を最初から入れると入力の負担が倍になり、続きません。まず日付で始めて、見積もりのずれが気になった時点で工数の列を足すのが現実的です。
Q2. 予定と実績をガントチャートのように色分けするにはどうしますか？
日付を横に並べた列を作り、条件付き書式の数式ルールで塗ります。予定は開始日と終了日の範囲、実績は実績開始と実績終了の範囲を条件にして、色を変えて重ねます。今日の列に色を付けるルールも足すと、遅れが視覚的に読めます。日付の列を増やしすぎると再計算が重くなるので3か月程度に絞ってください。
Q3. 実績を入れてもらえません。どうすれば集まりますか？
入力の手数を減らすこと、入力の意味を示すこと、同時に触れる環境にすることの3つです。特に、ファイルを誰かが開いていると他の人が入力できない状態は致命的です。共同編集にはサブスクリプションと保存場所の条件があるため、条件を満たせない環境では入力担当を1人に決めるか、別の形を検討することになります。
Q4. 予定と実績の差はどこから対応すべきですか？
遅れの大きい順に並べ、後続の作業に影響するものから手を打ちます。遅れていても後ろに何も控えていない作業は、優先度が下がります。差の合計だけを見ると、大きな遅れと前倒しが打ち消し合って実態が見えないため、件数と個別の大きさを合わせて確認してください。