excel
予実管理をスプレッドシートで回し始めると、最初の1か月は快適で、3か月目から表が崩れ始めます。原因は関数の腕ではなく、誰がどこに何を入れるかを決めずに共有してしまったことにあります。この記事では、同時編集に耐える形の作り方、使う関数、権限の分け方、実績を集める経路、そして表の手直しでは戻らなくなったときの判断の基準までを順に整理します。
予実管理そのものは古くからある仕事です。変わったのは保存先で、手元のファイルから共有の表へと移りました。移った理由は3つに絞れます。
1つ目は、実績を入力する人が増えたことです。経費の申請も外注の発注も、担当者が自分で記録する形が広がりました。手元のファイルでは、入力のたびに誰かにファイルを送る手間が発生します。
2つ目は、締めの周期が短くなったことです。月次だけでなく、週の単位で数字を見るチームが出てきました。週の単位で見るには、貼り付けの工程が入っていない形が必要になります。
3つ目は、働く場所が分かれたことです。同じ部屋にいない相手と同じ表を見るには、ブラウザで開ける形がいちばん軽く済みます。
予実管理は、スケジュール管理やデータベース作成に活用されるExcelやGoogle スプレッドシートで実施可能です。 出典: hubspot.jp
できるかどうかで言えば、どちらでもできます。判断が分かれるのは、入力する人の数と締めの周期です。入力するのが1人か2人で月次だけなら、手元のファイルのままで困りません。入力する人が5人を超えたあたりから、共有の表に移す意味が出てきます。
同じ表を作っても、共有の表と手元のファイルでは、気をつける場所が違います。
共有の表で楽になるのは、配る工程が消えることです。最新がどれかという問いが発生せず、メールに添付したファイルの版が枝分かれすることもありません。変更履歴が残るため、数字が変わった経緯を後から辿れます。
反対に、共有の表で重くなるのが3つあります。1つは、誰でも列を増やせてしまうこと。もう1つは、行数が増えたときの反応の遅さ。三番目は、関数を使った参照が他のファイルを跨ぐときの重さです。
行数の目安を持っておくと判断が速くなります。明細が2万行を超えたあたりから、開くのに待ち時間が出始めます。予実の明細が年間でその行数に届くなら、年度ごとにファイルを分ける前提で設計します。
もう1つ違うのは、他の人が見ている最中に自分の操作が反映される点です。並べ替えやフィルタをかけると、相手の画面も動きます。この1点が、共有の表で表が崩れる原因のほとんどを作ります。
同時編集に耐える形には、決まった作り方があります。
原則の1つ目は、入力するシートと見るシートを分けることです。入力するシートは縦に積むだけの明細にし、集計や図は別のシートで関数から拾います。入力する人が触るのは明細だけ、という状態を作ります。
2つ目は、並べ替えを禁じることです。共有の表では、並べ替えが他の人の画面を動かします。代わりにフィルタ表示を使います。フィルタ表示は自分の画面だけに効くため、隣の人の作業を止めません。この1点を決めるだけで、事故の大半が消えます。
3つ目は、列を固定して増やさせないことです。列の意味を一覧のシートに1行ずつ書き、そこに無い列は作らないという決まりにします。列が勝手に増えると、集計の関数が拾う位置がずれ、先月の合計が変わります。
この3つは、技術の話ではなく取り決めの話です。取り決めを表のいちばん上の行に書いておくと、後から参加した人にも伝わります。
作り方は5つの工程です。
最初に、明細のシートを1枚用意します。列は「年月、部門、費目、予算、実績、案件番号、備考」の7列です。横に月を並べる形にはしません。横に並べると、月が増えるたびに列を足す作業が発生し、集計の関数も毎月直すことになります。
次に、一覧のシートを作ります。部門名と費目名を縦に並べ、明細の入力規則からこの範囲を参照します。手で打たせると、同じ費目が「交通費」と「旅費交通費」に分かれ、集計が合わなくなります。
三番目に、集計のシートを作ります。行に費目、列に月を並べ、中身はSUMIFSで明細から拾います。この形なら明細に行が増えても集計は自動で追いつきます。
四番目に、差異の欄を作ります。金額の差と率の差を両方持ちます。率だけを見ると、金額の小さい費目が上位に並んで判断を誤ります。金額だけを見ると、規模の違う部門を横に並べられません。
五番目に、色の条件を決めます。差異率が基準を超えた行だけを塗ります。基準は費目ごとに変えて構いません。広告費の20%の超過と外注費の20%の超過では、意味の重さが違います。
必要な関数は多くありません。5つ覚えれば予実の表は組めます。
SUMIFSは、年月と部門と費目の3つを条件にして明細から合計を拾います。予実管理の集計は、ほぼこの関数だけで足ります。
QUERYは、明細から条件に合う行だけを抜き出して並べます。部門ごとの明細を別シートに出すときに使います。同じ表を部門別に切り出して配る場面で効きます。
IMPORTRANGEは、他のファイルから範囲を読み込みます。部門ごとにファイルを分けている場合に使いますが、参照が増えると表が重くなります。読み込む先は5ファイル程度までにとどめ、それ以上になるなら1つのファイルに集める設計に変えたほうが軽く済みます。
ARRAYFORMULAは、1つの式で列全体を計算します。行ごとに式を貼る形だと、行が増えたときに式の無い行ができます。差異の列はこの形で作ります。
条件付き書式は、差異の行を目立たせます。数値そのものを装飾するのではなく、行の背景を変える形にすると、印刷したときも読めます。
実際に壊れるのは、次の4つの場面です。
1つ目は、誰かが行を挿入した瞬間です。集計の関数が固定の行番号を指していると、参照がずれます。範囲の指定は列全体にするか、テーブルの形で持ちます。
2つ目は、誰かが並べ替えをした瞬間です。他の人が入力の途中だと、入力中の行が別の場所へ移動します。フィルタ表示に統一します。
3つ目は、同じセルを2人が同時に直した場面です。後から確定したほうが残ります。予算の欄を編集するのはとりまとめる人だけ、という担当の分け方で避けられます。
4つ目は、コピーして貼り付けた瞬間です。書式ごと貼り付けると、入力規則も条件付き書式も上書きされます。貼り付けは値だけにする決まりを共有します。
4つのうち3つは、取り決めで防げます。取り決めを守ってもらうために、明細のシート以外を保護し、編集できる範囲を明細の入力の列だけに限る設定を入れます。
予実の数字は、社内の全員が見てよい情報とは限りません。部門ごとの費用や外注の単価は、見せる範囲を絞る対象になります。
分け方は3段です。とりまとめる人は全部を編集でき、部門の担当者は自分の部門の行だけを編集でき、その他の人は集計だけを見られる。この3段で足りるチームが多くあります。
段を作る方法は2つあります。1つは、1つのファイルの中でシートの保護と範囲の保護を組み合わせる方法です。もう1つは、部門ごとに入力のファイルを分け、集計のファイルから読み込む方法です。前者は管理が楽で、後者は見せない範囲を確実に切れます。扱う金額の性質で選びます。
見られる範囲を絞ったつもりで漏れる場所が1つあります。リンクを知っている人が開ける設定です。共有の設定を作ったあと、閲覧者の一覧を実際に開いて確かめます。権限の分け方とデータの扱いの考え方を先に整理しておきたい場合は、道具ごとの方針をまとめた安全性の考え方のようなページが参考になります。
表を作った目的は、数字を並べることではなく、次の動きを決めることです。見せ方で決まりやすさが変わります。
差異の大きい順に上から並べます。費目のコード順は、探す作業を毎回発生させます。
金額と率を隣に並べます。金額が大きく率が小さい行と、金額が小さく率が大きい行は、打つ手が違います。
累計の列を持ちます。単月の差異は季節の影響で揺れます。年度の累計で見ると、傾向なのか揺れなのかが分かれます。
残りの予算を1列で出します。予算から実績の累計を引いた金額です。この1列があると、会議で「あと何をやめるか」の話に直接入れます。
色は2色までにします。超過を赤、未消化を灰色。3色を超えると、どの色が重いのかを説明する手間が毎回かかります。
スプレッドシートは、個人の利用なら費用がかからない形で使えます。会社の利用でも、表計算の機能そのものに追加の料金はかかりません。有償になるのは、保存できる容量や管理の機能を増やす場合です。予実管理の表を回すだけなら、無料の範囲で始めて困りません。
テンプレートは、表計算ソフトの提供元や会計ソフトの会社から無料で配布されています。
エクセルやスプレッドシートは、柔軟な予実管理表のカスタマイズが可能です。関数やピボットテーブルを活用すれば、高度な分析ができるようになります。 出典: geniee.co.jp
自由に直せることは長所ですが、配布されたものをそのまま運用に乗せると詰まります。見るべき点は4つです。明細と集計が別のシートに分かれているか。費目の一覧が別シートにあり入力規則が仕込まれているか。参照の範囲が列全体で書かれているか。シートの保護が外せて自社の費目に合わせられるか。
4つのうち2つが欠けているなら、形だけを参考にして自分で組み直すほうが早く済みます。明細の7列から作るなら、最初の表は30分程度で用意できます。
予実管理が続かない原因は、表の出来ではなく実績が集まらないことにあります。集め方は3つです。
1つ目は、担当者が明細に直接入力する形です。手間が少なく、遅れも出にくい。ただし、費目の選び間違いが混ざります。入力規則と、月初のとりまとめる人による目視の確認で補います。
2つ目は、会計の仕組みから書き出して貼り付ける形です。数字は正確ですが、計上されるまでに時間差があります。発注した月に実績として見えないため、月次の判断には間に合いません。
3つ目は、承認の記録から自動で反映させる形です。発注の承認を記録した時点で行が増える形にします。この形がいちばん遅れが出ませんが、承認の場所と表の場所がつながっている必要があります。
3つを混ぜると、二重に計上されます。どの経路で入った数字なのかを1列で持ち、月末に重複を見る工程を入れます。
表ができたら、毎月の段取りを決めます。決まっていない表は3か月で止まります。
期日を2つ決めます。部門が実績を出す期日と、まとめた表を配る期日です。月初の第3営業日と第5営業日が、現実的な線になります。
出してもらう形を揃えます。明細と同じ列のシートを配り、その形で返してもらいます。形が違うものが集まると、整形だけで半日かかります。
会議では、差異の大きい上位5行だけを議題にします。すべての行を追う会議は長くなり、翌月から参加率が落ちます。
決めたことを表に書き戻します。備考の列に「来月に繰り延べ」「他の費目から充当」と1行残すだけで、翌月に同じ議論を繰り返さずに済みます。
共有の表で予実管理をする利点は4つあります。配る工程が消える。変更履歴から経緯を辿れる。部門ごとの明細を切り出して配れる。費用がかからない。
不利な点も4つあります。行数が増えると反応が遅くなる。誰でも列を増やせる。案件ごとの原価を追い始めると明細が膨らむ。会話の記録が残らないため、数字が動いた理由を表の外で探すことになる。
4つ目が、いちばん見落とされます。予算を超えた理由はチャットの中にあり、表には結果だけが残ります。半年後に同じ費目でまた超えたとき、前回何をしたのかを探す作業が発生します。備考の列に1行書く決まりが、ここで効きます。
続かなくなる表には、似た形があります。
列が増え続ける型。良さそうな列を足していった結果、半分が空欄の表になります。空欄の列がある表は、入力する人に軽く扱われます。
1枚に詰め込む型。明細と集計と図を同じシートに並べると、行の挿入で全部がずれます。
とりまとめる人が全部入力する型。楽に見えて、その人が休んだ月に止まります。
案件の原価まで追い始める型。全社の費目と案件の原価は、必要な列が違います。案件が月に40件を超えるあたりから、1つの表で両方を持つのは無理が出ます。
数字だけを見る型。差異の理由を書き残さないため、翌年の予算を作るときに根拠が残っていません。
長く回っている表には、共通する点が3つあります。
列が少ないこと。明細が10列を超えている表は、ほぼ止まっています。
入力する人が2手以内で終わること。ファイルを開いて1行足すだけ、という状態です。開くまでに3回クリックする形だと、後回しにされます。
見る場面が決まっていること。毎月決まった曜日に決まった顔ぶれで見る。見られない表は、入力も止まります。
3つとも、表の作りではなく運用の話です。関数を増やしても続きやすさは変わりません。
もう1つ挙げるなら、表を作った人が「入力する人の代わりに入れてあげる」のをやめている点です。代わりに入れてもらえると分かると、入力は戻ってきません。遅れた行は空欄のまま会議に出し、その場で誰が入れるかを決める。この運び方に変えたチームでは、翌月から提出の期日が守られるようになると言われています。
逆に、うまく回っていない表の多くは、とりまとめる人が善意で埋めています。埋めるほど本人の負担が増え、休んだ月に止まります。埋めない決まりを最初に共有しておくのが、いちばん効く手です。
集計が合わない原因のうち、関数の誤りは少数です。多いのは、同じ費目に違うものを入れていることです。
外注費と業務委託費の境目、交通費と旅費交通費の使い分け、会場費と会議費の区別。どれも、決めておかないと部門ごとに解釈が分かれます。分かれたまま集計すると、合計は出るのに前年との比較ができません。
一覧のシートに、費目名の隣に1行の説明を書きます。「外注費は制作物の対価。人の時間に対して払うものは業務委託費」といった具合です。説明は1行に収めます。長い定義は読まれません。
迷いやすい組み合わせだけ、例を2つ添えます。「印刷会社への支払いは外注費」「デザイナーへの月額の支払いは業務委託費」。例があると、判断の速さが変わります。
定義を変えた月は、備考の列に残します。年度の途中で境目を動かすと、前月との比較が合わなくなります。変えた事実が記録に残っていれば、あとから理由を説明できます。
全社の費目を月ごとに見る表と、案件1件の原価を追う表は、必要な列が違います。同じ表で両方をやろうとすると、どちらも中途半端になります。
案件の原価を追うなら、必要な列は「案件番号、見積額、外注費、経費、稼働時間、進行状況」です。月ごとの費目の表とは重なりません。案件番号だけを共通にして、2つの表をつなぐ形が軽く済みます。
社内の人の稼働を金額に換算するかどうかは、迷いどころです。換算するには社内の単価を決める必要があり、その議論が毎年発生します。チームが数十人までなら、金額に換算せず時間だけを別に持つほうが続きます。
稼働の入力は、日ごとではなく週ごとにします。日ごとに入れる形は、2週間で止まると言われています。週の終わりにまとめて入れる形なら、精度は落ちても記録が残ります。
案件が月に40件を超え、担当者が5人を超えたあたりから、案件の原価は表の外に出したほうが楽になります。案件ごとのカードに金額を持たせ、表はその集計だけを受け取る形です。
予算は、年度の途中で動きます。動いたときの記録の残し方を決めておかないと、期末に「当初の予算はいくらだったか」が分からなくなります。
列を2つに分けます。当初の予算と、修正後の予算です。差異は修正後の予算に対して計算し、当初の予算は参考として残します。上書きすると、期中の判断が正しかったかを後から検証できません。
組み替えた理由も1行で残します。「上期の未消化を下期の外注費へ充当」といった書き方です。金額の動きだけが残っていると、翌年の予算を作るときに根拠を思い出せません。
組み替えの回数にも目安を持ちます。四半期に1回までにすると、表が追いつきます。月ごとに動かすと、差異の意味が薄くなり、会議で見る価値が下がります。
共有の表の利点の1つは、誰がいつ何を変えたかが残ることです。ただし、履歴は変更の事実だけを記録し、理由は残しません。
履歴で足りるのは、事故の原因を辿る場面です。合計が急に変わったとき、どの行が動いたかは履歴から分かります。
足りないのは、判断の経緯を追う場面です。なぜ予算を増やしたのか、なぜ発注を止めたのかは、履歴には出てきません。この情報はチャットの中にあり、半年後には探せなくなります。
備考の列に1行書く決まりが、ここを埋めます。書く内容は決めておきます。「誰が決めたか」と「次にいつ見直すか」の2つです。この2つがあれば、あとから辿れます。
セルのコメントに残す方法もありますが、書き出したときに消えます。あとから集計や検索の対象にしたい情報は、列として持ちます。
共有の表は、保存先と開き方を決めておかないと、似たファイルが増えます。
保存先は、部門ごとのフォルダではなく、予実管理のフォルダを1つ作り、そこに年度ごとのファイルを並べます。部門ごとに分けると、全社の集計を作るときに参照が散ります。
ファイル名には年度を入れます。「予実管理_2026年度」のような形です。日付や版の番号を入れると、更新のたびに名前が変わり、参照が切れます。
開き方は、ブックマークではなく、チームが毎日見る場所にリンクを貼ります。チャットの固定の投稿でも、タスクの板の固定のカードでも構いません。探す手間が1回減るだけで、入力の回数が変わります。
コピーを作らせない決まりも必要です。手元にコピーを作ると、そのコピーが更新され、元の表が古いまま残ります。編集できない人には閲覧の権限を渡し、コピーは作らせません。
読まれる表にするために効く設定は、派手なものではありません。
1行目を固定します。スクロールしても列の名前が見えるようにします。これが無いと、下のほうの行で入力の位置を間違えます。
金額の書式を揃えます。桁区切りを入れ、単位を列の名前に書きます。セルの中に「円」を書くと、合計が計算できなくなります。
行の高さを揃え、折り返しを切ります。備考が長い行だけが縦に伸びると、一覧としての読みやすさが落ちます。備考は列幅で切り、全文はセルを選んだときに見る形にします。
印刷の範囲を先に決めます。会議で紙を配るチームでは、1枚に収まらない表は読まれません。印刷用のシートを1枚作り、上位の行だけを出す形にします。
ゼロの行を隠します。予算も実績もゼロの費目が並ぶと、見るべき行が埋もれます。フィルタ表示で隠すか、集計のシート側で除きます。
表の手直しでは戻らなくなるサインは4つです。入力する人が5人を超えた。明細が月に300行を超えた。承認した発注が表に入るまでに数日かかっている。同じ数字を2人が違う値で持っている場面が直近3か月で起きた。2つ以上が当てはまるなら、別の仕組みを見る段階です。
選ぶときの軸は3つあります。
1つ目は、入力する人の負担です。とりまとめる人が楽になるかではなく、担当者が続けられるかで決まります。どの見せ方や項目が最初から使えるかはできることのページで確かめられます。検討の段階で、実際に入れる人に触ってもらうのが確実です。
2つ目は、料金の増え方です。必要な1つの機能のために上のプランへ上がる形だと、比較の段階で機能の表を読み比べる手間がかかります。機能で絞らず、区切るのは人数とボードの数だけという形なら、判断が人数の話に収まります。金額と区切り方は料金のページで確かめてください。
3つ目は、いまの表と板をどう持っていくかです。明細はCSVで書き出せますが、いま使っているタスクの板を作り直す手間は見落とされがちです。Trelloからの移行のように既存の板を取り込める経路があるかは、先に確かめます。自動で取り込めるのはTrelloだけという道具もあり、他からは手で作り直すことになります。
予実管理を軽くする道は、表を良くすることだけではありません。実績が表に入るまでの経路を短くするほうが効きます。
遅れの原因を辿ると、ほとんどが承認の場所と入力の場所が離れていることに行き着きます。発注の相談がチャットで進み、承認もチャットで出て、そのあと誰かが思い出して表に入力する。この最後の1手が抜けるだけで、月末に実績が積み上がります。
直し方は2つです。1つは、入力を承認の条件にすること。表に1行入れてから承認する決まりにします。もう1つは、承認そのものを進行の板の上で行うことです。発注のカードを作り、金額と発注先を書き、承認の列に動かした時点で記録が残る形にします。後者のほうが入力する人の負担が軽く済みます。
道具ごとに、カードに金額のような独自の項目を持たせられるかどうかが違います。持たせられる場合でも、上のプランが必要になることがあります。検討している道具を横に並べて見るなら比較の一覧が早く、いま使っている道具が決まっているならTrelloとの比較やNotionとの比較のように1対1で見るほうが判断しやすいでしょう。
数字を扱う表と、進行を追う板は、無理に1つにしなくて構いません。金額の集計は表に残し、承認と進行は板に移す。この分け方でも、実績が届くまでの日数は短くなります。人数の数え方など細かい点はよくある質問にまとめられています。
全部をいきなり移す必要はありません。発注の件数がいちばん多い1つの部門だけを板に移し、翌月の実績が締めの日までに揃ったかどうかを見る。1か月の試しで、移す価値があるかどうかは判断できます。
入力する人が5人以内で、明細が月に300行以内であれば、共有の表のまま長く続きます。入力する人が増えるほど、列の増設や並べ替えによる事故が起きやすくなります。入力するシートと見るシートを分け、並べ替えを禁じてフィルタ表示に統一すると、10人程度までは耐えられます。
並べ替えを禁じてフィルタ表示に統一する、集計の参照を固定の行番号ではなく列全体で書く、明細以外のシートを保護する、貼り付けは値だけにする、の4つで大半が防げます。並べ替えは他の人の画面まで動かすため、入力中の行が別の場所へ移動する事故につながります。
明細と集計が別シートに分かれているか、費目の一覧に入力規則が仕込まれているか、参照の範囲が列全体で書かれているか、シートの保護を外して自社の費目に直せるか、の4点を見ます。2つ以上欠けているなら、形だけを参考にして自分で組み直すほうが早く済みます。
承認の場所と入力の場所が離れていることが原因の大半です。発注の承認をチャットで出したあと、誰かが思い出して表に入力する形になっていると、その1手が抜けます。表に1行入れてから承認する決まりにするか、承認そのものをカードの移動で行う形にすると、遅れは消えます。