カレンダー型の進捗管理をエクセルで作る手順と、崩れ始める境目
カレンダー 進捗管理 エクセルで検索する人が作りたい表は、だいたい形が決まっています。横に日付が並んでいて、縦に担当者か作業が並んでいて、予定の期間に色が付いている。今日の位置に線が入っていて、遅れているものが赤くなる。その1枚を見れば、今週どこが詰まっているかが分かる表です。
この形は正しく、実際に多くの現場で機能しています。問題は作り方ではなく、作ったあとに起きることのほうにあります。日付をひとつずつ手で入れていると月をまたいだ瞬間に崩れ、色を手で塗っていると誰かが1行挿入した瞬間にずれ、そして最も多いのが、2週間後には作った人以外の誰も更新しなくなる、という結末です。
この記事では、崩れない作り方を数式のレベルまで具体的に扱ったうえで、エクセルの仕様上の限界と、それよりずっと手前で来る運用上の限界を分けて整理します。読み終えたときに、いまの表を直せばよいのか、それとも表の外に原因があるのかを判別できる状態を目指します。
カレンダー型で見えるものと、見えないもの
進捗を管理する表には大きく3つの型があります。1つめが一覧型で、作業名と担当者と期限が縦に並ぶ形です。2つめがカレンダー型で、日付が横または格子状に並び、その上に作業が乗る形です。3つめが板型で、未着手や進行中といった状態ごとに列を作り、そこにカードを置いていく形です。
カレンダー型が得意なのは、時間の混み具合を見ることです。来週の火曜に3人分の締切が重なっている、月末の1週間に作業が固まっている、といった偏りが目で分かります。一覧型では同じ情報が並んでいても偏りは見えません。日付をキーに並べ替えて初めて気づく形になります。
もう1つ得意なのが、空いている時間を見つけることです。誰かに追加の作業を頼むとき、その人の予定が横一列に並んでいれば、どこが空いているかがすぐ分かります。人ごとの行を作るカレンダー型が、負荷の調整に使われる理由がここにあります。
逆に苦手なのが、作業と作業の関係を見ることです。この作業が終わらないと次が始まらない、という前後関係はカレンダーの上には表現されません。日付が並んでいるだけなので、1つ遅れたときに後ろがどれだけずれるかは、人の頭の中で計算することになります。ここが必要になった時点で、カレンダー型ではなく工程表の形が要ります。
もう1つ苦手なのが、状態の管理です。「レビュー待ち」なのか「差し戻し中」なのかは、日付の上には乗りません。色で表現しようとすると凡例が増え、色の意味を覚えている人が作った本人だけになります。状態を追いたいなら板型が向いていて、複数の見方を切り替えられるかどうかは道具の設計次第です。何をどの形で見るのが向いているかはできることに整理されています。
実務では、この3つの型を1枚の表に全部詰め込もうとして失敗する例がよくあります。日付が横に並び、状態の列があり、前後関係を示す矢印が手描きで足され、担当者ごとの負荷を表す色分けまで乗る。作った本人には読めますが、他の人には読めません。凡例が5行を超えた時点で、その表は説明なしには使えなくなっています。
作る前にここを決めておくと、あとで表を作り直す回数が減ります。時間の混み具合を見たいならカレンダー型で正解です。詰まっている原因を追いたいなら、カレンダー型は入口であって、そこから別の見方に移る必要があります。
日付は手で入れず、数式で持たせる
崩れる表のほとんどは、日付が値として直接入力されています。1つのセルに「9/1」、隣に「9/2」と手で打っていく形です。この作り方だと、翌月に使い回すときに全部を打ち直すことになり、途中に1日挿入するとその先が全部ずれます。
日付は起点を1つだけ決めて、あとは足していきます。たとえば表の左上、B4のセルに開始日を入れ、C4に次の式を入れて右へコピーします。
=B4+1
これだけで、開始日を変えるだけで表全体の日付が動くようになります。翌月の表を作るときは、B4を書き換えるだけで終わります。
その下の行に曜日を出します。B5に次の式を入れて右へコピーします。
=TEXT(B4,"aaa")
これで月、火、水と1文字の曜日が出ます。「aaaa」にすると月曜日という表記になります。曜日を手で入れると、月をまたいだときに必ずずれるので、ここも数式にしておきます。
月の表示が必要なら、日付の行の上にもう1行足して、月が変わるところだけ表示させます。
=IF(OR(B4=$B$4,DAY(B4)=1),TEXT(B4,"m月"),"")
これで、表の左端と月初だけに月の表示が出ます。横に長い表でも、いま見ているのが何月なのかが分かります。
もう1つ、必ずやっておきたいのがウィンドウ枠の固定です。作業名と担当者の列、そして日付の行を固定しておかないと、横にスクロールした瞬間にどの行が誰の作業か分からなくなります。表示タブのウィンドウ枠の固定で、日付行のすぐ下かつ担当者列のすぐ右のセルを選んでから固定します。この一手間を省いた表は、幅が3か月分を超えたあたりで実用に耐えなくなります。
色は手で塗らず、条件付き書式で出す
セルを手で塗ると、行を1つ挿入した瞬間に意味がずれます。色は必ず条件付き書式で出します。ここが、崩れる表と崩れない表を分ける最大の分岐点です。
まず土日に色を付けます。日付の行から下の範囲を選択して、条件付き書式の新しいルールから数式を使って書式設定するセルを決定を選び、次の式を入れます。
=WEEKDAY(B$4,2)>=6
WEEKDAY関数の第2引数に2を指定すると、月曜が1、日曜が7になります。6以上が土日という判定です。列だけを固定して行を固定しない書き方（B$4）にしておくことで、範囲全体に同じルールが正しく当たります。ここのドルマークの付け方を間違えると、1列目だけ色が付いて他が付かない、という症状になります。
次に今日の位置に線を出します。同じ範囲に、次の式でルールを追加します。
=B$4=TODAY()
書式は塗りつぶしではなく左罫線にしておくと、下の予定バーの色を邪魔しません。この1本があるかどうかで、表を開いたときに視線が今日に飛ぶかどうかが変わります。
3つめが予定のバーです。作業の開始日を$C列、終了日を$D列に持たせておいて、次の式でルールを追加します。
=AND(B$4>=$C5,B$4<=$D5)
これで、開始日から終了日までのセルに自動で色が付きます。日付を変えればバーも動きます。手で塗った表との決定的な違いはここで、予定変更が1か所の日付の書き換えで済むようになります。
4つめが遅れの表示です。終了日を過ぎているのに状態が完了になっていない行を赤くします。状態を$E列に持たせておいて、行全体に次のルールを当てます。
=AND($D5<TODAY(),$E5<>"完了")
条件付き書式は上から順に評価されるので、遅れのルールを予定バーより上に置いてください。順番を間違えると、遅れているのに通常の色のままになります。
数式で持たせるべき列と、手で入れる列を分ける
表の左側に置く管理用の列も、全部を手入力にすると更新されなくなります。人が入れるのは最小限にして、計算で出せるものは計算させます。
手で入れる列は、作業名、担当者、開始日、終了日、状態の5つで足ります。ここから先は数式にできます。
残り日数は次のように出します。
=IF($E5="完了","",$D5-TODAY())
完了しているものには何も表示せず、それ以外は終了日までの日数を出します。マイナスになっていれば遅れです。
作業日数は終了日から開始日を引くだけですが、土日を除いた実働日数で見たい場合はNETWORKDAYS関数を使います。
=NETWORKDAYS($C5,$D5)
祝日を除きたい場合は、別のシートに祝日の一覧を作って、第3引数にその範囲を指定します。祝日の一覧は毎年更新が要るので、シート名を分けて誰でも直せる場所に置いておきます。
進捗率を数字で入れさせるかどうかは、判断が分かれるところです。人が入れる進捗率は、経験上あてになりません。80%と書かれた作業が2週間動かない、という状態はよく起きます。進捗率よりも、完了した作業の件数を数えたほうが実態に近くなります。件数はCOUNTIFS関数で出せます。
=COUNTIFS($E:$E,"完了")
担当者ごとに数えたいなら条件を足します。
=COUNTIFS($B:$B,$H5,$E:$E,"完了")
このあたりまで作ると、1枚の表として十分に機能します。作る手間は半日ほどです。
日単位で持つか、週単位に丸めるか
作り始める前に決めておくと後で作り直さずに済むのが、1列を何日分にするかです。ここを決めずに日単位で作り始めて、3か月目に横幅に耐えられなくなって全部作り直す、という流れがよく起きます。
日単位が向くのは、作業の粒度が1日から3日の現場です。制作や編集、短いスプリントで回している開発などがこれにあたります。1日ずれたことが目で分かる必要があるなら、日単位以外に選択肢はありません。
週単位が向くのは、作業の粒度が1週間から1か月の現場です。建設や設備、大きな導入案件などがこれにあたります。週単位にすると、1年分でも52列で収まります。画面に入る列数を考えると、四半期どころか半期を1枚で見られるようになります。
判断の目安は、いちばん短い作業の長さです。最短の作業が2日なら日単位、最短が1週間なら週単位でずれは表現できます。逆に、日単位で作った表に1か月かかる作業を並べると、バーが長すぎて全体像が見えなくなります。
両方を1つのファイルで持ちたい場合は、シートを分けます。日単位のシートを今月分だけ作り、週単位のシートで年間を見る形です。ただしこの構成にすると、同じ情報を2か所に持つことになります。片方だけ更新されて食い違う、という事故が必ず起きるので、どちらかを主にしてもう片方を数式で参照する作りにしてください。手で二重に入れる運用は続きません。
粒度を切り替えられるかどうかは、そもそも道具の設計に依存する部分でもあります。同じ予定を日で見たり週で見たり月で見たりできるなら、この悩みは発生しません。見方を切り替える設計になっているかどうかは、できることで確認できます。
印刷して配ると、そこで表は止まる
作った進捗表を印刷して会議で配る、あるいはPDFにしてメールで送る。この運用は驚くほど多く、そして表が止まる最も静かな原因になっています。
印刷した紙は、配った瞬間から古くなります。会議中に決まった変更は紙には反映されず、その場でメモが書き込まれます。そのメモを誰かがファイルに戻すまで、正しい表はどこにも存在しません。戻す人が忙しい週には戻らず、次の会議で配られる紙には前回の決定が入っていない、という状態になります。
PDFで送る場合も同じです。受け取った側は自分の環境でそれを開き、必要なら印刷し、気づいた点をメールで返します。返信が5人からばらばらに来ると、その全部を1枚に統合するのは送った人の仕事になります。表を配ることが、配った人の作業を増やす構造です。
それでも印刷が必要な場面はあります。現場に紙で貼る、顧客に提出する、監査のために期日時点の状態を残す。これらは正当な用途で、そのために印刷体裁を整えることには意味があります。印刷するなら、改ページプレビューで区切りを確認し、印刷タイトルの設定で見出し行を全ページに繰り返し、日付とバージョンを必ずヘッダーに入れてください。日付が入っていない紙は、1週間後に本物かどうか判断できなくなります。
避けたいのは、印刷が更新の代わりになることです。会議のたびに印刷する運用は、裏を返せば会議のときにしか表が更新されないということです。その頻度で足りるならそれでよく、足りないなら、配るのをやめて全員が同じ1枚を見る形に変えるしかありません。
ファイル名でバージョンを管理し始めたら黄信号
進捗表が機能しなくなる前触れとして、いちばん分かりやすい兆候があります。ファイル名に日付やバージョンが付き始めることです。
最初は良かれと思って付けます。壊したときに戻せるように、前の版を残しておく。ところがこれをやると、共有フォルダに似た名前のファイルが並び、どれが最新か分からなくなります。誰かが古い版を開いて編集すると、その編集は最新版には反映されません。2つの真実が同時に存在する状態になり、突き合わせる作業が発生します。
もっと厄介なのが、個人のパソコンにコピーを置く形です。共有フォルダのファイルを開くのが遅いから、あるいは外出先で見たいから、というもっともな理由でコピーが生まれます。そのコピーで編集された内容は、本人が忘れなければ戻されますが、忘れれば消えます。
バージョンは、ファイル名ではなく仕組みで持つべきものです。クラウド上に置いていればバージョン履歴が自動で残り、いつ誰が何を変えたかを後から追えます。ファイル名に日付を付ける運用は、その仕組みが無い環境で編み出された回避策であって、いま新しく始める理由はありません。
そして、履歴が要る本当の理由は復元ではありません。「なぜこの日程になったのか」を後から知りたいからです。工期が2週間延びたとき、その判断の背景がファイルのどこにも残っていないと、同じ議論を何度もやり直すことになります。変更の理由が記録と同じ場所に残る作りかどうかは、道具を選ぶときの判断材料になります。
エクセルの仕様上の上限と、その手前で来る限界
エクセルの仕様は公開されています。1つのワークシートは1,048,576行、16,384列まで扱えます。1つのセルに入る文字数は32,767文字、取り消しができる回数は100回です。
列の上限だけを見ると、日付を横に並べても16,384日分、つまり40年以上入る計算になります。仕様の上限に当たって困ることは、この用途ではまず起きません。
先に来るのは別の限界です。1つめが表示の限界で、日付を1列1日で持つと、3か月で90列を超えます。画面に入るのはせいぜい20列から30列なので、全体を1枚で見ることはできなくなります。週単位に丸めるか、月単位に丸めるかの判断がここで要ります。
2つめが条件付き書式の重さです。ルールを広い範囲に何本も当てると、ファイルを開くのに時間がかかるようになります。特に、列全体を範囲にしたルールを何本も持つと目に見えて遅くなります。範囲は使う分だけに絞ってください。
3つめが取り消しの回数です。100回という上限は、普段は意識しませんが、他の人が編集したあとに戻すことはできません。共同編集をしている場合、自分の操作を100回戻せても、他人が消した行は戻せません。
4つめが、最も現実的な限界です。誰かがファイルを開いたまま昼休みに出てしまうと、他の人が編集できなくなる。あるいは、メールで送り合っているうちに「進捗管理表_最新_v3_修正版.xlsx」が生まれる。この2つは仕様の話ではなく運用の話ですが、実際に表が使われなくなる原因のほとんどはここにあります。
共同編集は使えるが、条件がある
いまのエクセルは複数人で同時に編集できます。公式のサポートページには次のように書かれています。
同僚と一緒に同じ Excel ブックを開いて作業できます。 これは共同編集と呼ばれます。 共同編集を行うと、お互いの変更をわずか数秒ですばやく確認できます。 出典: support.microsoft.com
同じページには、この機能がMicrosoft 365のサブスクリプションを持っている場合にのみ使えるという注記も添えられています。買い切りのライセンスで使っている場合や、ファイルを社内のファイルサーバーに置いている場合は、この形にはなりません。ファイルはクラウド上の場所に置く必要があります。
条件を満たしていれば、進捗表の共同編集はかなり快適になります。ただし、快適になるのは編集の競合が起きなくなるところまでで、更新する人が増えるかどうかは別の問題です。
共同編集で気をつけたいのが、同時に編集できない機能があることです。シートの保護をかけている範囲、テーブルとして書式設定していない範囲での一部の操作、マクロを含むファイルなど、条件によっては共同編集の対象外になります。VBAのマクロで自動化した進捗表を持っている場合、その便利さと共同編集は両立しないことがあります。どちらを取るかは、更新する人が何人いるかで決めてください。1人で回すならマクロが速く、5人で回すなら共同編集のほうが効きます。
もう1つ、変更の履歴です。クラウド上に置いていればバージョン履歴が残りますが、残るのはファイル全体の版であって、どのセルを誰がなぜ変えたかではありません。日程が動いた理由を後から追いたい場合、その理由は表の外に書き残しておく必要があります。備考の列を作って、変更した日付と理由を1行ずつ足していく運用にしている現場もありますが、これを続けられるチームは多くありません。
現場でよく起きるのが、権限を絞りすぎる形です。壊されるのが怖いので編集できる人を2人に限る、という運用にすると、他の全員にとってその表は見るだけのものになります。見るだけのものは、そのうち見られなくなります。逆に全員が編集できるようにすると、数式が上書きされて壊れます。列の保護とシートの保護を使って、入力してよいセルだけを開けておくのが折衷案ですが、設定を維持する手間が増えます。
壊れやすい箇所を先に潰しておく
作った表が数か月もつかどうかは、次の5つを最初に処理したかどうかで決まります。どれも作った直後は問題にならず、他の人が触り始めてから表面化します。
・行の挿入で数式がずれる。参照が絶対と相対で混ざっていると、行を1つ足しただけで条件付き書式の当たる範囲が変わります。作り終えたあとに、わざと1行挿入して壊れないかを確かめてください ・状態の表記がばらつく。「完了」と「済」と「完了済み」が混在すると、集計も遅れ判定も効かなくなります。入力規則のリストを使って、選ぶだけで入るようにします ・担当者名がばらつく。姓だけ、フルネーム、ニックネームが混ざると、担当者ごとの集計が合いません。ここも入力規則で選ばせます ・日付が文字列で入る。他のシステムから貼り付けた日付が文字列になっていると、比較も計算もできません。セルの左寄せになっていたら文字列です。DATEVALUE関数で変換してから使います ・シートが増えすぎる。月ごとにシートを増やす作りは、3か月目までは快適で、1年後に集計できなくなります。データは1枚に縦に持ち、見せ方をフィルターで変えるほうが後が楽です
このうち最も事故が多いのが、状態の表記のばらつきです。集計が合わないと報告に使えなくなり、報告に使われない表は更新されなくなります。入力規則の設定は5分で終わるので、必ず最初に入れてください。
もう1つ、シートの保護も忘れずに設定します。人が入力する列だけロックを外し、それ以外をロックしてシートを保護すると、数式が上書きされる事故が止まります。ただし保護のパスワードを設定した本人しか解除できない状態にすると、その人が不在のときに誰も直せなくなります。パスワードは共有するか、そもそも設定しないかを決めておいてください。
更新されなくなる本当の理由は、表の作りではない
きれいに作られた進捗表が2週間で止まるのは、表の出来が悪いからではありません。入力するまでの距離が遠いからです。
作業が終わったことに気づくのは、たいてい移動中か、別の作業に取りかかる直前です。その瞬間にファイルを開けるかどうかで、記録されるかどうかが決まります。パソコンを立ち上げて、共有フォルダを開いて、正しいファイルを選んで、自分の行を探して、状態を書き換える。この5段階のうち、どこかで別の用事が入れば記録は消えます。
もう1つが、更新した結果が誰にも見られないことです。状態を完了に変えても、誰からも反応がないなら、変える理由がなくなります。進捗を書き込むことが誰かへの報告になっていて、その報告が受け取られている実感があるときにだけ、入力は続きます。
3つめが、話す場所と残す場所が分かれていることです。作業の話はチャットで進み、決定もチャットで下りるのに、記録は別のファイルにある。この状態だと、記録は誰かがまとめて転記する作業になります。転記する人が忙しくなった週から、表は嘘になり始めます。
だからこそ、表を直しても直らない問題があります。色をきれいにしても、数式を賢くしても、入力するまでの距離は縮みません。縮めるには、記録する場所と話す場所を近づけるか、記録そのものの手数を減らすしかありません。
表の外に原因があるなら、変えるのは表ではない
ここまで見てきた通り、エクセルでカレンダー型の進捗表を作ること自体には無理がありません。数式と条件付き書式を使えば、崩れない表は半日で作れます。問題が起きるのは、人が増えたときと、更新の頻度が上がったときです。
境目はおおむね次のように現れます。1人で作って1人で見ているうちは、エクセルが最も速い道具です。3人から5人で共有し始めると、共同編集の条件を満たしているかどうかで体験が大きく変わります。10人を超えて、しかも毎日更新が必要になると、ファイルという単位そのものが重くなります。誰が何を変えたのかが追えず、変更の理由がファイルの中に残らないためです。
このときに検討することになるのが、記録と会話を同じ場所に置く形の道具です。カードに状態を持たせて、そのカードの上でやり取りが起きれば、転記が要らなくなります。カレンダーの見方と板の見方と工程表の見方を、同じデータから切り替えられれば、目的ごとに表を作り直す必要もなくなります。こうした道具では、機能で段階を分けず、区切るのを人数とボードの数だけにしている設計もあり、その場合は工程表を使うために上位プランへ移るという判断が要りません。条件は料金で確認できます。
既存のサービスからの移し替えを考えている場合は、取り込みの経路があるかどうかで手間が変わります。自動で取り込める範囲はTrelloからの移行にまとまっています。他の選択肢と横並びで見たいなら比較の一覧があり、板型を中心に使っているチーム向けにはTrelloとの比較、課題管理を中心に使っているチーム向けにはBacklogとの比較がそれぞれ個別にまとめられています。社内の情報を外に置く判断が必要なら安全性の考え方を、契約や上限の細かい条件はよくある質問を先に見ておくと、情報システム部門との話が早く進みます。
最後に、エクセルをやめる必要はないという点も書いておきます。四半期の計画を1枚にまとめて経営に見せる、顧客に提出する工程表を清書する、といった用途ではエクセルのほうが速く、体裁も自由です。毎日の進捗を追うことと、節目で1枚にまとめることを、同じファイルでやろうとするから無理が出ます。目的ごとに道具を分けると、どちらも軽くなります。
Q1. エクセルでカレンダー型の進捗表を作るとき、最初にやるべきことは何ですか？
日付を手入力せず、起点のセルだけ決めて隣を「=B4+1」のような数式にすることです。これだけで、開始日を書き換えるだけで表全体の日付が動きます。あわせて曜日も「=TEXT(B4,"aaa")」で出しておくと、月をまたいだときのずれがなくなります。色は手で塗らず条件付き書式で出してください。
Q2. 今日の日付に線を入れて、遅れを赤くするにはどうしますか？
条件付き書式で数式のルールを追加します。今日の位置は「=B$4=TODAY()」で判定し、書式は塗りつぶしではなく左罫線にすると予定バーの色を邪魔しません。遅れは終了日と状態を組み合わせて「=AND($D5<TODAY(),$E5<>"完了")」で判定します。遅れのルールは予定バーのルールより上に置いてください。
Q3. エクセルの進捗表は何人くらいまで使えますか？
仕様上の上限より運用の限界が先に来ます。1人か2人で見るうちは最も速い道具です。3人から5人で共有するなら、共同編集がMicrosoft 365のサブスクリプションを前提とする点を確認してください。10人を超えて毎日更新が必要になると、誰が何をなぜ変えたのかがファイルに残らないことが効いてきます。
Q4. きれいに作ったのに誰も更新してくれません。表の作り方が悪いのでしょうか？
表の作りではなく、入力するまでの距離が原因であることがほとんどです。ファイルを開いて自分の行を探して書き換えるまでに5段階あると、途中で別の用事が入って記録は消えます。更新しても誰からも反応がないことも、続かない大きな理由です。記録する場所と話す場所を近づけるほうが効果があります。