Googleスプレッドシートで進捗をどう見せるか｜報告のために作り直さない組み方
Googleスプレッドシートで進捗を管理していて、いちばん時間を食っているのはどこか。表を作る作業でも、数字を集める作業でもなく、報告のたびに別の形へ作り直す作業です。同じ場所を全員が同時に見られるはずなのに、会議の前日にコピーを取り、要らない列を消し、体裁を整えている。この記事では、その作り直しをなくすための組み方を扱います。表を1から作り替える話ではなく、どこに一次データを置き、見え方をどう出し分けるかという置き方の話です。
貼り直しているなら、止めるのは貼り付けではなく参照の向き
報告用の表を作るとき、多くの現場では元の表からコピーして貼り付けています。この瞬間に、同じ数字が2か所に存在します。2か所あれば、必ずずれます。ずれた瞬間に「どちらが正しいのか」を確かめる時間が発生します。
止め方は、貼り付けをやめることではありません。参照の向きを固定することです。一次データが1枚あり、報告用の表はそこを参照するだけ、という向きにします。報告用の表には値を直接入れません。1つも入れません。
この原則を守ると、報告の前にやることが「開いて確認する」だけになります。数字が変わっていれば、報告用の表も変わっています。整える作業は最初に1回だけです。
守れなくなるのは、報告用の表に手で1か所だけ書き足したときです。「この行だけ注記を入れたい」という要望から始まり、次の週には3か所になり、1か月後には半分が手入力になっています。注記を入れたいなら、一次データの側に注記の列を作ります。例外を報告側に置かない、という一点だけを守れば、この組み方は崩れません。
見せる相手を3つに分けると、必要な粒度が決まる
進捗の見せ方が定まらないのは、宛先を1つだと思っているからです。実際には3つあり、知りたいことがまったく違います。
1つ目は、作業している担当者です。知りたいのは「自分が次に何をするか」だけです。他人の行も、全体の進捗率も要りません。この人に見せるべきなのは、自分の担当で未完了のものが期日順に並んだ一覧です。
2つ目は、社内で判断する人です。知りたいのは「どこが遅れていて、誰が動けば直るか」です。全部の行は要りません。遅れている行と、来週期日が来る行だけで足ります。件数と原因が分かれば、判断はできます。
3つ目は、社外の相手です。知りたいのは「約束した日に間に合うか」です。社内の担当者名も、内部の作業も要りません。むしろ見せないほうが話が早く終わります。見せるのは、相手に関係する納品物と、その予定日と、状態だけです。
この3つを1枚の表で満たそうとすると、列が20本並びます。列が20本ある表は、3つのどの相手にとっても読みにくい表です。分けるのは表ではなく、見え方です。
一次データは1枚。ここだけは増やさない
一次データのシートは1枚に固定します。案件が増えても、期間が延びても、ここは増やしません。増やすのは行だけです。
このシートに置く列は、入力してもらう列と計算する列に分かれます。入力してもらう列は、作業名、担当、区分、開始予定日、完了予定日、完了日、状態、注記。これで8つです。計算する列は、残り日数と遅れの有無です。合計10列に収まります。
10列に収まらないと感じるときは、たいてい2つの異なる管理を1枚に混ぜています。作業の進捗と、請求の状況は別の管理です。人の稼働と、物の納品も別です。混ぜると列が増え、どちらの目的でも使いにくくなります。
区分の列は後で効きます。案件名でも、工程の段階でも、納品物の種類でもかまいません。これがあると、あらゆる見え方を1つの条件で作れます。区分が無い表は、報告のたびに手で行を選ぶことになります。
行は削除しません。中止になった作業も、状態を「中止」にして残します。消すと、後から「あの話はどうなったか」を追えなくなります。ただし中止の行を進捗の分母に入れるかどうかは、先に決めて全員に伝えます。ここが曖昧だと、同じ表を見ている2人が違う進捗率を口にすることになります。
見え方は、式で作るものと画面で作るものを分ける
見え方の作り方は2つあり、使い分けの基準がはっきりしています。
式で作るのは、他人に渡す見え方です。別のシートにQUERY関数を1本置き、条件を指定します。作業のシートから、状態が完了以外の行だけを、期日順に並べる。こう書けば、一次データが更新されれば見え方も更新されます。渡した相手が開いた時点の最新が出ます。
画面で作るのは、自分のための見え方です。データメニューのフィルタ表示を使うと、絞り込みが自分の画面にだけ適用されます。通常のフィルタを使うと、絞った瞬間に他の全員の画面からも行が消えます。共同編集の場では、これが摩擦の最大の原因になります。同時に開く人が3人を超えたら、通常のフィルタは使わないと決めておくのが安全です。
フィルタ表示には名前を付けて保存でき、そのURLを渡せます。担当者に毎朝の確認を頼むなら、表のURLではなくフィルタ表示のURLを渡します。開いた瞬間に自分の担当だけが並んでいるほうが、開いてもらえます。
社外に渡すのは、式で作った別のスプレッドシートにします。同じファイルの中でシートを分けても、編集権限のある人はシートの再表示ができるため、見せない設計にはなりません。ファイルごと分けて、IMPORTRANGEで必要な列だけを引いてくる形が確実です。
「いまの状態」と「あのときの状態」を分ける
自動で更新される表には、弱点が1つあります。報告した時点の状態が残らないことです。会議で「先週は3件遅れていると言ったはずだ」と言われても、表は今日の姿になっています。
残し方は2通りあります。
1つ目は、変更履歴に名前を付ける方法です。ファイルメニューの変更履歴から最新の版に名前を付けると、その時点の状態が名前付きで残ります。ただしスプレッドシートは名前付きの版を15個までしか持てません。週次で付けると15週で埋まるので、月次の締めだけに絞ります。
2つ目は、報告した時点の数字を1行だけ別のシートに積む方法です。日付、全体の本数、完了の本数、遅れの本数。この4つを月に1回、手で1行足します。これだけで、遅れの推移が折れ線で描けます。表の姿を丸ごと残す必要はありません。残すべきは姿ではなく数字です。
推移が見えると、会議の話が変わります。「今3件遅れています」だけだと原因の話にしかなりませんが、「先月は1件、今月は3件です」だと、増えた理由の話になります。判断する人が知りたいのは後者です。
進捗を何で測るかを、先に1つに決める
進捗率をどう出すかで揉めるのは、測る単位が決まっていないからです。選べるのは3つです。
本数で測るのがいちばん簡単です。全体が40本で、完了が18本なら、45%。計算は関数1本で済みます。欠点は、重い作業と軽い作業が同じ1本として数えられることです。
日数で測ると、重みが入ります。予定の日数の合計に対して、完了した作業の日数の合計がどれだけか。実態に近くなりますが、日数の見積もりが甘いと、その甘さがそのまま進捗率に乗ります。
工数で測ると最も正確ですが、実績の工数を入力してもらう必要があります。入力が増えるほど、入力されなくなります。5人から10人のチームで、進捗の把握のためだけに工数を取るのは、たいてい割に合いません。
現実的なのは、本数で測り、遅れの本数を横に並べる形です。進捗率45%という1つの数字より、40本中18本完了で3本遅れ、という3つの数字のほうが、読む側は正しく状況をつかめます。1つの数字にまとめるほど、情報は落ちます。
遅れの定義を、表の中の1列に落とす
「遅れている」という言葉を、人によって違う意味で使っていると、報告は必ず食い違います。定義を決めて、列に落とします。
決めるのは3つです。基準にするのは完了予定日か開始予定日か。判定するのは今日か、締めの日か。着手していないだけのものを遅れに含めるか。
多くの現場で使いやすいのは、「完了予定日が今日より前で、状態が完了以外」という定義です。開始が遅れていても、期日に間に合うなら遅れとは呼ばない。この割り切りが、無駄な議論を減らします。
この判定を、計算する列に置きます。IF関数の中でAND関数を使い、完了予定日が今日より前であること、かつ状態が完了以外であることを条件にします。結果は「遅れ」か空欄の2通りにします。3通り以上にすると、集計の式が増えます。
列に落とすと、集計も色付けも1つの基準でそろいます。条件付き書式もこの列を見るようにすれば、判定の場所が1か所になります。判定が2か所にあると、片方だけ直したときに表と色が食い違います。
定義は、決めたら期の途中で変えません。変えると、前月との比較ができなくなります。どうしても変える必要が出たら、変えた日を推移のシートに1行書き残します。後から折れ線が急に折れている理由を、誰も説明できなくなる事態を防げます。
もう1つ、遅れの理由を入れる列は作らないほうがうまくいきます。理由を書く欄があると、担当者は理由を書くことで報告を終えた気持ちになり、期日を直さなくなります。直すべきは日付です。理由が要る行は数が限られるので、コメントで残せば足ります。
更新が集まらないのは、催促していないからではない
とりまとめる立場の人がいちばん時間を取られているのは、たいてい表を作る作業ではなく、埋まっていない行を埋めてもらう作業です。これは意思の問題ではなく、道具の性質から来ています。公式ヘルプには、はっきり書かれています。
通知は自分用にのみ設定できます。スプレッドシートに自分で加えた変更については通知されませんが、他のユーザーが加えた変更については通知が届きます。 出典: support.google.com
読み落としやすいのは、設定できるのが自分の分だけだという点です。とりまとめる側が「期日が近いのに未着手の人に知らせる」設定を代わりに入れることはできません。通知を受け取りたい人が、自分で設定する必要があります。
つまり、この道具には「入力を促す」機能が最初から入っていません。担当者は、自分から表を開かない限り、更新すべきことに気づきません。人は開かないものを更新しません。催促する声が必要になるのは、仕組みが無いからです。
さらに、閲覧の権限しか持たない人は、通知は受け取れますが誰が変更したかの名前は見えません。社外の相手に見せている場合、数字が変わったことは伝わっても、誰が動かしたかは伝わりません。
催促を、仕組みに置き換える3つの手
声を掛ける回数を減らす方法は、3つあります。どれも道具の外側にある工夫です。
1つ目は、更新の締め切りを決めることです。「随時更新してください」は、誰も更新しません。「金曜17時までに、自分の行の状態を更新してください」と決めると、埋まる率が上がります。締め切りが無い依頼は、締め切りのある仕事に必ず負けます。
2つ目は、開く場所を1つにすることです。担当者にフィルタ表示のURLを渡し、それを毎朝のチャットの定型文に貼っておきます。探す手間が1回でもあると、開かれません。
3つ目は、埋まっていないことを可視化することです。更新日の列を作り、最後に更新された日から3日以上経っている行に色を付けます。誰の行が止まっているかが、会議を開かずに分かります。責める道具にすると使われなくなるので、「止まっている行を一緒に見る」という使い方に留めます。
細かい条件での自動通知が本当に必要なら、Apps Scriptで書くことになります。公式ヘルプにも、特定のセル範囲が変更されたときなどの詳細な通知はApps Scriptを使うよう案内されています。ただし、書いた人しか直せない処理が1つ増えることは織り込んでおきます。
案件が複数あるときの、集め方と重さの折り合い
案件ごとにスプレッドシートを分けている現場では、全体を横断して見たいという要望が必ず出ます。集める方法は2つあり、どちらにも代償があります。
1つ目は、集計用のスプレッドシートを1枚作り、IMPORTRANGEで各案件の表から必要な列だけを引いてくる方法です。初回は引く側と引かれる側の両方で接続を許可する操作が要ります。引いた先でQUERYを重ねれば、全案件の遅れだけを並べた一覧も作れます。
代償は速度です。IMPORTRANGEは再計算のたびに引きに行くため、本数が増えるほど開くのが遅くなります。案件が5件までなら実用的ですが、10件を超えると開いてから表示されるまでに待ち時間が生まれ、その待ち時間がそのまま「開かれない理由」になります。
2つ目は、最初から1枚の表に全案件の行を積み、案件名の列で区別する方法です。集計は1本の式で済み、速度の問題も起きません。代償は権限です。1枚に集めた表を見せた相手には、他の案件の行も見えます。社外の協力者が複数の案件に別々に絡むなら、この方法は取れません。
判断の基準は、社外が絡むかどうかです。社内だけなら積む、社外が絡むなら分けてIMPORTRANGEで集める。どちらを選んでも、案件が10件を超えたあたりで表計算の組み方そのものが苦しくなります。そこが1つの目安になります。
グラフは1枚だけにする
進捗の報告にグラフを付けたくなりますが、増やすほど読まれなくなります。1枚に絞ります。
描くべきは、遅れの本数の推移です。横軸に月、縦軸に本数。折れ線で1本だけ。これが増えているか減っているかが、進行の健康状態そのものです。完了率の円グラフは見栄えはしますが、増えたか減ったかが分かりません。
グラフの元にするのは、月に1行ずつ積んでいる推移のシートです。一次データを直接参照すると、行が増えたときに範囲から外れます。推移のシートは行が月に1本しか増えないので、範囲の指定を列全体にしておけば壊れません。
グラフを資料に貼るときは、リンクを保つか画像にするかを先に決めます。社内で毎週使い回すならリンクを保ち、社外に出すなら画像で固定します。この判断は、その資料が「いまを見るもの」か「あのときを残すもの」かで決まります。
軸の目盛りは固定します。自動にしておくと、本数が減ったときに縦軸も縮み、減っているように見えません。改善が見えないグラフは、続ける理由を失わせます。
引き継ぐときに、何を渡せば足りるか
とりまとめる役目が交代するとき、表のURLだけを渡しても引き継ぎにはなりません。表には結果しか残っておらず、読み方が残っていないからです。
渡すべきものは4つです。
1つ目は、列の意味を書いた1行です。シートの見出し行の上に1行取り、区分の列に何を入れるか、状態の4つの選択肢がそれぞれ何を指すかを書いておきます。表の外に置いたメモは、必ず行方不明になります。
2つ目は、遅れの定義です。どの日付を基準に、いつ判定し、着手前をどう扱うか。これを表の中の列に落としてあれば、式を読めば分かります。落としていないなら、書いて渡します。
3つ目は、更新の締め切りと、誰が何を入力する担当かです。金曜17時までに担当者が自分の行を更新する、という取り決めが口約束のままだと、引き継いだ瞬間に消えます。
4つ目は、報告の宛先ごとにどのシートを見せているかです。担当者用、社内の判断用、社外用。それぞれのURLと、見せている範囲を並べて渡します。ここが分かっていないと、後任者は社外用の表に内部の列を足してしまいます。
社外に見せるときに、必ず確認する3つの設定
社外の相手に進捗を見せる場面では、共有ボタンを押す前に3つ確かめます。
1つ目は、ダウンロードと印刷とコピーの可否です。既定では許可されているため、閲覧の権限しか無い相手でも、表をそのまま手元に複製できます。単価や取引先名が入っているなら、共有の設定からこれを切ります。
2つ目は、リンクの配り方です。リンクを知っている全員に権限を付ける設定にすると、そのリンクが転送された先にも同じ権限が渡ります。社外が絡む表は、メールアドレスを指定して共有します。誰に渡したかが一覧で残り、後から外せます。
3つ目は、見せる範囲そのものです。フォルダに付けたアクセス権は、その中のファイルに継承されます。「フォルダは共有しているが、この1枚だけは見せない」という設計は取れないので、社外に見せる表は別のフォルダに置きます。共有の設計をどう考えるかは安全性の考え方に整理してあり、社外に出す前に一度読んでおく価値があります。
資料に貼るときに、リンクを切るかどうか
報告資料に表やグラフを貼るとき、リンクを保ったまま貼るか、画像として貼るかを選べます。
リンクを保つと、元の表が変われば資料の中身も更新できます。ただし更新は自動ではなく、資料を開いた人が更新のボタンを押す形です。押さなければ古いままなので、「更新されているはず」という思い込みが事故になります。
画像として貼ると、その時点の姿が固定されます。後から見返したときに、報告した内容と食い違いません。社外に出す資料は、こちらが安全です。
判断の基準は、その資料が「いまを見るもの」か「あのときを残すもの」かです。社内の週次で使い回す資料はリンクを保ち、社外に出す報告書は画像で固定する。この使い分けで、ほとんどの場面は足ります。
貼った資料を相手に渡すときは、元の表へのアクセス権を相手が持っているかも確認します。リンクを保った資料を権限の無い人が開くと、グラフの場所が空欄になります。
チャットに流れる報告と、表に残す報告を分ける
進捗が表に集まらない現場では、たいてい報告そのものは行われています。行き先が表ではなくチャットになっているだけです。「Aの件、今日終わりました」という一言が流れ、誰も表に書き写しません。
この状態を放置すると、表と実態が二重になります。とりまとめる側はチャットをさかのぼって表を埋める作業をすることになり、それが週に1時間を超えた時点で、管理そのものが仕事になります。
分け方の原則は1つです。あとで数えるものは表に、数えないものはチャットに置きます。状態と日付は数えるので表に入れます。相談、判断の経緯、細かい調整はチャットで足ります。
この原則を伝えるだけでは動かないので、置き換えの手順まで決めます。チャットで完了の報告を受けたら、その場で「表の状態を完了にしてください」と返す。これを2週間続けると、多くの人はチャットに書く前に表を触るようになります。書き写す役を引き受けてしまうと、いつまでも書き写す役のままです。
もう1つ、行に紐付けたい相談はセルのコメントに寄せます。セルを選んでコメントを追加し、プラス記号の後に相手のメールアドレスを入れると相手にメールが届き、割り当て先のチェックを入れればその人の担当として記録されます。どの行の話かが残るので、後から経緯をたどれます。3往復を超えるやり取りはコメントに向かないので、そこから先はチャットに移し、結論だけをコメントに戻します。
数字が合わないと言われたときの追い方
報告の場で「その数字は違う」と言われることは必ずあります。慌てて数え直す前に、追う順番を決めておきます。
まず、変更履歴を開きます。ファイルの右上から過去の版を開くと、誰がいつ何を変えたかが並びます。既定では変更されていない行が隠れるので、差分だけを追えます。相手が見たときの日時が分かっているなら、その時刻の版を開けば、当時の姿がそのまま出ます。
次に、集計の条件を確認します。食い違いの半分は、数え方の違いです。中止になった行を分母に入れているか、まだ着手していない行を遅れに数えているか。定義を1列に落としてあれば、ここはすぐ照合できます。
最後に、参照の範囲を見ます。行が増えたのに集計の式が古い範囲のままだと、新しい行が数えられません。列を丸ごと指定する書き方にしておけば、この事故は起きません。
追えたら、原因を表の上に1行残します。次に同じことが起きたとき、追う時間がゼロになります。残す場所は、報告用のシートではなく一次データの側にします。報告用のシートは作り直されることがありますが、一次データは残り続けるからです。
週に一度、20分で回すための手順
運用を続けるコツは、やることを減らすことです。週に一度、20分で終わる形にします。
最初の5分で、遅れの列に印が付いている行だけを見ます。全部は見ません。印が付いている行について、担当者に何が起きているかを確かめます。
次の5分で、更新日が3日以上止まっている行を見ます。止まっている理由は、たいてい2つです。作業が進んでいないか、進んだのに入力していないか。どちらかを確かめるだけで、表の信頼度が戻ります。
次の5分で、来週期日が来る行を見ます。ここで手を打てば、翌週の遅れが減ります。遅れを数えるより、遅れる前に気づくほうが価値があります。
最後の5分で、推移のシートに1行足します。日付、全体の本数、完了の本数、遅れの本数。これで終わりです。
会議で表を開いて全員で眺める時間は取りません。全員が同じ場所を見られる道具を使っているのに、全員で1つの画面を見るのは時間の使い方として逆です。会議で話すのは、遅れている3行の中身だけにします。
この組み方で足りる範囲と、限界の合図
ここまでの組み方は、5人から15人くらいのチームで、同時に動く案件が3件までなら十分に回ります。実際、この形で何年も続いている現場があります。
限界が近い合図は3つです。1つ目は、更新を促す時間が週に1時間を超えたとき。2つ目は、見せる相手ごとにファイルを分けた結果、同期のずれを直す作業が発生し始めたとき。3つ目は、「なぜこの判断をしたか」を後から誰も答えられなくなったときです。3つ目は表計算の設計では埋めにくく、決定の経緯を残す場所が別に要ります。
比べる相手は、いま困っている場所によって変わります。担当と期日を配って進捗を追う形が要るならAsanaとの比較が、案件ごとの進み具合を一覧で見比べたいならmonday.comとの比較が、複数をまとめて眺めたいなら比較の一覧が、それぞれ何を得て何を手放すかの整理になります。
費用の見方は、月額の数字を並べるだけでは足りません。人数が増えたときの増え方と、必要な機能が上のプランにしか無いかどうかが後で効きます。機能で絞らず、区切るのは人数とボードの数だけという組み方もあり、その考え方は料金で確かめられます。いま表で埋めている役割のうち、どれが最初から入っているかを突き合わせるにはできることが使えます。判断に迷う点はよくある質問にまとまっています。
Q1. 報告用の表を毎回コピーしているのですが、やめられますか？
やめられます。報告用のシートに値を直接入れず、QUERY関数などで一次データを参照する形にします。一次データが更新されれば報告用の表も更新されるため、報告前の作業は開いて確認するだけになります。例外を報告側に手入力した瞬間に崩れるので、注記も一次データ側に列を作って入れます。
Q2. 進捗率はどう計算するのがよいですか？
本数で測るのがいちばん扱いやすく、全体の本数と完了の本数と遅れの本数を並べて出す形をおすすめします。1つの割合にまとめるほど情報は落ちます。日数や工数で重み付けする方法もありますが、実績の入力が増えるほど入力されなくなるため、少人数のチームでは割に合わないことが多いです。
Q3. 担当者が更新してくれません。自動で催促できますか？
標準の通知は自分の分しか設定できないため、とりまとめる側から自動で催促する仕組みは用意されていません。更新の締め切りを曜日と時刻で決める、フィルタ表示のURLを固定して毎朝の定型文に貼る、最終更新日から3日以上経った行に色を付ける、という3つで実務上はかなり改善します。
Q4. 社外に進捗を見せるときの注意点は何ですか？
ダウンロードと印刷とコピーが既定で許可されている点、リンクを知っている全員に権限を付ける設定だと転送先まで権限が広がる点、フォルダのアクセス権が中のファイルに継承される点の3つです。社外用には別のファイルを用意し、必要な列だけを引いてくる形にするのが確実です。