excel
予実管理をエクセルで始めた時点では、たいてい何の問題も起きません。詰まるのは3か月目から6か月目で、実績の締めが間に合わなくなり、部門ごとに書式の違う表が増え、合計が合わない理由を探すだけで半日が消えます。この記事では、予実管理表に持つべき項目、エクセルでの組み方と関数、数字が合わなくなる原因、月次で回すときの段取り、そして道具を変える判断をどのサインで下すかまでを順に整理します。読み終えたときに、いまの表のどこを直すかが決まる状態を目指します。
予実管理という言葉は、予算と実績を並べて差を出すところまでを指すように見えます。実務で価値が出るのはその先で、差が出た理由に辿り着けたかどうかで表の値段が決まります。
Q1. 予実管理表とは何のために作るものですか？予実管理表は、設定した予算と実際の実績を同じ表で比較し、進捗率・達成率・差異を可視化するための表です。月次・四半期・年次など期間ごとに差異の原因を特定し、次のアクションにつなげるのが目的です。経営層には全社の達成度合いを、現場には部門別・科目別の課題を伝える役割を担うため、誰が何を判断するための表かを意識して項目を選定することが重要になります。 出典: diggle.jp
ここで押さえておきたいのは、誰が何を判断するための表なのかを先に決める、という点です。経営に上げる表と、現場が自分の持ち場を直すための表は、必要な細かさが違います。経営に上げる表は科目を大きくまとめて、全社の達成度が1画面に収まっていればよい。現場の表は、どの案件のどの費目が予定を超えたのかまで分かる必要があります。
この2つを1枚で兼ねようとすると、列が横に伸びます。全社合計に必要な集約列と、案件別に必要な明細列が同じ行に並び、どちらの用途でも読みにくい表になります。よく聞くのは、経営会議の前日に明細の表から集約の表を手で作り直しているという話です。作り直しの作業自体が、数字が合わなくなる最大の入口になります。
もう1つ、差異の理由に辿り着けない表には共通の欠けがあります。差異の金額しか持っていないことです。予算が300万円で実績が360万円なら、差異は60万円です。ここまではどの表でも出ます。しかし60万円のうち、数量が増えたことによる分と、単価が上がったことによる分を分けて持っていないと、次に何を変えればよいのかが決まりません。差異の欄を1つではなく2つ用意するだけで、表の使い道が変わります。
予実管理が重くなる原因の半分は、実は予算を立てた時点で作り込まれています。並べ方や関数の話より先に、ここを見ておく価値があります。
予算の立て方は大きく2つに分かれます。1つは、上から売上の目標を決めて部門に割り付ける形です。もう1つは、現場が案件と稼働を積み上げて合計する形です。前者は早く決まりますが、根拠が薄いので差異が出たときに説明できません。後者は根拠が残りますが、決まるまでに時間がかかります。
実務では、売上は上から割り付け、費用は積み上げる組み合わせが落ち着きます。費用は積み上げた根拠が残っているので、超えたときに「どの前提が外れたのか」に辿り着けます。積み上げの根拠は、予実管理表と同じファイルの別シートに置いておきます。別のファイルに置くと、半年後に行方が分からなくなります。
もう1つ決めておくのは、予算に含めない費目です。突発の修理費や、年に1回しか発生しない費用を月次の予算に入れると、その月だけ大きく外れます。こうした費目は別枠にして、予実の表では対象外と明示します。対象外の行を持たない表は、外れた月の説明に毎回同じ手間がかかります。
立てるときの粒度も先に決めます。案件ごとに予算を持つのか、部門の合計だけを持つのか。案件が月に30件を超えるなら、案件ごとの予算は埋まりません。その場合は部門と科目の合計で持ち、案件別は実績だけを追う形にします。
ステップ1は、期間の単位を決めることです。月次で締めるのか、四半期で締めるのかを先に固定します。月次で始めて途中から四半期に変える、あるいはその逆をやると、過去の行と比べられなくなります。多くのチームは月次から始めて問題ありません。
ステップ2は、科目の一覧を別シートに作ることです。売上、外注費、人件費、広告費といった科目名を、入力する人が手で打たない形にします。同じ「外注費」が「外注日」や「外注 費」と打たれると、集計で別の項目に分かれます。この事故は、あとから直すのが最も面倒な部類に入ります。
ステップ3は、入力の表と集計の表を分けることです。入力の表は縦に長い明細の形にします。1行が「年月、部門、科目、予算、実績」の5列で足ります。集計の表はここからピボットテーブルか関数で作ります。逆にしてはいけません。横に月が並んだ表に直接入力させると、月が増えるたびに列を追加することになり、式の参照範囲がずれます。
ステップ4は、入力規則を設定することです。部門と科目の列は、ステップ2で作った一覧から選ぶ形にします。年月の列は日付として入れさせ、表示形式で年月に見せます。文字列として「2026年9月」と打たせると、並べ替えも期間の絞り込みもできません。
ステップ5は、検出の式を置くことです。差異率が一定を超えた行に色を付けます。条件付き書式で、差異率の絶対値が10%を超えたセルを塗るだけで足ります。これがあると、表を開いた瞬間に見るべき行が分かります。この1つを入れていない表は、毎月すべての行を目で追うことになり、続きません。
項目は、増やすほど埋まらなくなります。最初に持つのは次の8つで足ります。
年月、部門、科目、予算額、実績額、差異額、差異率、差異の理由。この8つです。
このうち最も落ちやすいのは最後の「差異の理由」です。ここが空欄の表は、翌月に見返したときに何も思い出せません。理由は長く書く必要はなく、単語で足ります。数量増、単価上昇、計上月ずれ、発注取り消し、といった具合です。分類として使えるように、これも別シートの一覧から選ばせるのが実務的です。
「計上月ずれ」を分類として持っておくと効きます。予実がずれる原因のうち、実際に使いすぎたのではなく、請求が翌月に回っただけという場合が一定の割合を占めます。これを理由の欄で区別できていないと、使いすぎたと誤解して来月の予算を締めてしまいます。
逆に、最初から持たなくてよい項目もあります。前年同月の実績、累計の達成率、見込みの着地額です。どれも有用ですが、明細の行に持たせるものではありません。集計の表の側で計算します。明細に計算結果を持たせると、あとから予算を修正したときに計算結果だけ古いままになり、これも数字が合わない原因になります。
項目を増やすときの決め方も先に作っておきます。差異の理由の欄に同じ種類の情報が3回以上書かれたら、独立した列に昇格させる。このルールがあると、必要な列だけが自然に育ちます。使う前に「あったほうがよさそう」で追加した列は、ほぼ空欄のまま残ります。
予実管理表のために覚える関数は多くありません。次の6つで、ほとんどの表が組めます。
SUMIFSは、条件を付けた合計です。年月と部門と科目を指定して、明細から金額を拾います。予実管理表の集計はこれが主役で、SUMIFSが使えれば集計の8割は済みます。
XLOOKUPは、別の表から値を1つ引いてくるものです。科目の一覧から、その科目が費用か売上かの区分を引くといった使い方をします。古い環境ではVLOOKUPで代替できますが、列を追加してもずれない点でXLOOKUPのほうが安全です。
IFERRORは、参照が見つからないときの表示を決めるものです。予実管理表では、実績がまだ入っていない月に計算結果としてエラーが並びます。これを空欄か0に変えておかないと、上の合計まで巻き込んでエラーになります。
TEXTは、日付から年月の表示を作るものです。年月の列を日付で持ちつつ、見せ方を整えるために使います。
SUBTOTALは、絞り込みをかけた状態の合計を出すものです。部門で絞ったときに、その部門だけの合計が下に出ます。SUMで書いていると、絞り込んでも全部の合計が出たままになり、会議の場で誤読が起きます。
ROUNDは、丸めです。千円単位で見せる表では、丸める位置を式の中で決めておきます。表示形式だけで千円に見せると、画面の合計と実際の合計が1円単位でずれて、突き合わせのときに混乱します。
この6つを超えて式を凝り始めたときは、いったん手を止めるとよいでしょう。式が複雑になるほど、作った人以外が触れなくなります。触れない表は、その人が休んだ月に更新が止まります。
予実管理表の数字が合わなくなる原因は、だいたい4つに収まります。
1つ目は、表が複製されることです。誰かが自分の作業用に別名で保存し、そこで数字を直します。元の表に戻す作業が抜けると、2つの表が別の答えを出します。ファイルが人数分に増えた表は、もう1つの正解を持っていません。よく聞くのは、会議の場で2人が違う数字を読み上げ、その場でどちらが新しいかを確かめられないという話です。
2つ目は、行の挿入で式の範囲がずれることです。明細の途中に行を挿入すると、集計側のSUMIFSの範囲が伸びないことがあります。範囲を「A2:A1000」のように固定の行数で書いていると、1000行を超えた時点から静かに欠け始めます。範囲をテーブルとして定義しておけば、この事故は起きません。
3つ目は、締めのタイミングが揃っていないことです。営業の部門は請求ベース、制作の部門は発注ベースで実績を入れていると、同じ月の同じ案件が2つの月に分かれます。どちらが正しいという話ではなく、揃っていないことが問題です。基準は表の最初のシートに1行で書いておきます。
4つ目は、予算の修正履歴が残らないことです。期中に予算を組み替えたとき、元の値を上書きすると、達成率が突然良くなった理由が説明できません。予算の列を「当初予算」と「修正予算」の2列に分けるだけで、この説明がつきます。2列に分ける手間は最初の1回だけで、期末の説明の手間はずっと減ります。
道具を変える話の前に、エクセルのままがよい場合をはっきりさせておきます。
第一に、誰でも触れることです。新しく入った人に使い方を教える必要がなく、経理の担当者も営業の担当者も同じ画面を見られます。この点で代わりになるものは、実はあまりありません。
第二に、追加の費用がかからないことです。Microsoft 365をすでに契約しているなら、予実管理のために新しく払うものはありません。始めるのに稟議も要りません。
第三に、表の形を自由に決められることです。自社の科目の並びや、部門の切り方を、そのまま表に写せます。出来合いの仕組みだと、自社の科目を相手の分類に合わせて読み替える作業が発生します。
第四に、期間の締めが緩いことです。月をまたいで過去の数字を直すのが簡単で、監査の前に遡って整えることもできます。厳密さを求められる場面では弱点になりますが、動かしやすさという意味では利点です。
第五に、一時的な分析に強いことです。「この案件だけ抜いて見たい」という依頼にその場で応えられます。集計の仕組みが固定された道具では、こうした注文に対応できないことがあります。
チームが10人に届かず、部門も2つか3つで、案件の数が月に数十件までなら、この5つの利点のほうが後述するデメリットを上回ります。無理に道具を変える理由はありません。
エクセルの予実管理でつらくなるのは、表を作るときではありません。作った表を毎月同じ形で保ち続けるところです。
まず、入力を集める手間がかかります。部門ごとに実績を出してもらい、1つの表に貼り付ける。この作業が月末に集中し、提出が遅い部門を待つ間に他の作業が止まります。とりまとめる人が休むと、その月の予実は出ません。
次に、同時に触れないことです。誰かがファイルを開いている間、他の人は編集できません。クラウド上での共同編集を使えば緩和しますが、同じセルを同時に直したときの解決は人が判断することになります。
三つ目に、変更の跡が残らないことです。誰がいつどの数字を直したのかを追えません。差異の理由が書き換わっていても気づけず、会議で指摘されてから調べることになります。
四つ目に、集計の仕組みが属人化することです。ピボットテーブルとSUMIFSで組んだ表は、作った人の頭の中の設計に依存します。引き継ぎのときに、式の意味から説明することになります。
五つ目に、進行の状況とつながらないことです。予算を超えた原因が、工程の遅れによる追加の外注だったとしても、その工程がどこで止まったのかは予実の表には書かれていません。原因を探すときに、別の場所を見に行くことになります。
属人化した「エクセル集計作業」から脱却し、予実管理をスピード感ある「経営判断の武器」へと進化させましょう。 出典: diggle.jp
属人化という言葉で語られるこの状態は、担当者の能力の話ではありません。表の作りと、更新の経路の話です。作りを直せばエクセルのままでも軽くなる部分があり、経路の問題は道具を変えないと解けません。ここを切り分けずに道具の話に飛ぶと、新しい道具でも同じ場所で詰まります。
現場でしばしば出る失敗は、次の5つに集まります。
1つ目は、科目を細かくしすぎることです。費目を50個に分けた表は、入力する人がどれに当てはめるか迷い、毎月違う科目に入ります。科目の数は、部門の担当者が迷わず選べる範囲に抑えます。判断に迷うものが出てきたら、その時点で1つ増やします。
2つ目は、予算を細かい期間に割りすぎることです。年間の予算を12で割って月次の予算にすると、季節の偏りがある事業では毎月差異が出ます。差異が常に出る表は、差異の意味が薄まります。季節性があるなら、割り方も季節に合わせます。
3つ目は、差異をすべて説明しようとすることです。金額の小さい差異にまで理由を書かせると、入力の負担が増えて全体の提出が遅れます。しきい値を決めて、超えたものだけ理由を必須にします。
4つ目は、集計の表を手で作り直すことです。会議用の資料を毎月コピーと貼り付けで整えていると、その作業のたびに転記の誤りが入ります。集計の表は、明細を差し替えれば自動で更新される形にしておきます。
5つ目は、達成率だけを見ることです。達成率が100%を超えていても、売上は予算どおりで費用が予算内に収まっていないという組み合わせがあります。売上と費用は別に見ます。
見やすさは好みの問題に見えますが、続くかどうかに直結します。効くコツは4つあります。
1つ目は、列の並びを更新の頻度で決めることです。左からめったに変わらないもの、右に行くほどよく変わるものを置きます。年月、部門、科目が左、実績額と差異の理由が右です。頻繁に触る列が近くにまとまり、横スクロールの回数が減ります。
2つ目は、色を使う場所を2つに限ることです。差異率が基準を超えた行と、まだ実績が入っていない行。この2つだけにします。色が3種類を超えると、どの色が重いのか分からなくなります。
3つ目は、単位を列名に書くことです。「売上（千円・税抜）」のように書きます。単位を表の上のほうに1回だけ書いた表は、印刷して配ったときに読み違えが起きます。
4つ目は、1画面に収まる行数に絞ることです。会議で見る表は、スクロールせずに全体が見える量に抑えます。細かい明細は別のシートに置き、見たいときだけ開きます。
予実管理表のテンプレートは、無料で配布されているものが多数あります。使い始めるのは早くなりますが、選ぶときに見る場所があります。
第一に、入力の表と集計の表が分かれているかどうかです。横に月が並んだ1枚のシートに直接入力する形のテンプレートは、月が増えたときに壊れます。
第二に、科目の一覧が別シートにあるかどうかです。入力規則が仕込まれていないテンプレートは、配った先で表記が揺れます。
第三に、式の参照範囲がテーブルになっているかどうかです。固定の行数で書かれているものは、行が増えたときに静かに欠けます。
第四に、自社の科目に合わせて直せるかどうかです。シートに保護がかかっていて列を追加できないものは、使い続けられません。
第五に、配布元が今も更新しているかどうかです。数年前の書式のまま置かれているものは、新しいExcelの関数を使えない前提で組まれていることがあります。
この5つを満たすテンプレートが見つからないときは、自分で組むほうが早いこともあります。明細の5列から始めれば、最初の表は30分で作れます。
表ができたあとは、毎月の段取りを決めます。ここが決まっていない表は、3か月で止まります。
期日を2つ決めます。1つは部門が実績を出す期日、もう1つがとりまとめた表を配る期日です。前者を月初の第3営業日、後者を第5営業日に置くのが、多くのチームで現実的な線です。
出してもらう形も決めます。部門ごとに好きな形で送られてくると、貼り付ける前の整形に時間がかかります。明細の5列と同じ形のシートを配り、その形で返してもらいます。
差異の理由を書いてもらう範囲も決めます。しきい値を超えた行だけ必須、それ以外は任意にします。全部必須にすると提出が遅れ、全部任意にすると誰も書きません。
配ったあとに何を話すかも決めておきます。差異の大きい上位5行だけを議題にする、といった形です。すべての行を追う会議は長くなり、次の月から参加率が落ちます。
エクセルのままでよいのか、別の仕組みに移るのか。判断は感覚ではなく、次のサインで決められます。
1つ目のサインは、とりまとめる人が月に使う時間です。集める、貼る、突き合わせる、作り直す。この一連に月8時間を超えて使っているなら、その時間は分析ではなく転記に消えています。
2つ目のサインは、同じ数字を2人が違う値で持っている場面が、直近3か月で1回以上起きているかどうかです。1回起きた表は、また起きます。
3つ目のサインは、実績を締めたあとに予算を直す作業が毎月あるかどうかです。組み替えが常態化しているなら、履歴を残せる仕組みが必要になっています。
4つ目のサインは、差異の理由を工程の遅れで説明することが増えているかどうかです。予算の表と進行の記録が別の場所にあると、この説明のたびに2つを突き合わせる作業が入ります。
5つ目のサインは、部門の数です。部門が5つを超えると、提出の遅れが1か所でも出た時点で全体が止まります。
このうち2つ以上が当てはまるなら、表の作りを直すだけでは戻りません。逆に1つも当てはまらないなら、いまの表を手直しするほうが早く効きます。
移るとして、何で選ぶか。予実の話に限らず、進行の道具を選ぶときの軸は4つに絞れます。
1つ目は、入力する人の負担です。とりまとめる人がどれだけ楽になるかではなく、数字や進捗を入れる側が続けられるかで決まります。入力の画面が重い道具は、2週間で誰も更新しなくなると言われています。どの道具を検討するときも、実際に入れる人に触ってもらうのが確実です。
2つ目は、予算の話と進行の話が同じ場所にあるかどうかです。差異の理由を工程の遅れで説明する場面が多いなら、工程の板と費用の記録が離れていない形が効きます。カンバンやガント、カレンダーといった見せ方が最初から揃っている道具なら、費用の話と工程の話を同じ場所で追えます。どの見せ方が揃っているかはできることのページで確かめられます。
3つ目は、料金の増え方です。機能ごとに階段が分かれている道具は、必要な1つの機能のために上のプランへ上がることになります。機能で絞らず、区切るのは人数とボードの数だけという形なら、検討の段階で機能の表を読み比べる手間が省けます。実際の金額と区切り方は料金のページで確かめてください。
4つ目は、いまの表と板をどう持っていくかです。エクセルの明細はCSVで書き出せますが、いま使っているタスクの板を作り直す手間は見落とされがちです。Trelloからの移行のように、既存の板をそのまま取り込める経路があるかどうかは先に確かめておきます。
予実管理の表を軽くする方法は、実は表の中だけにはありません。差異の理由の多くが工程の話に帰着するなら、工程の記録の側を整えたほうが効きます。
差異の理由として現場から上がってくるものを並べると、追加の外注、手戻りによる作業の増加、承認待ちで発生した稼働、の3つが大半を占めます。どれも、予算の表を見ていても分かりません。分かるのは、その作業がいつ発生して誰が動いたかを記録している場所です。
ここで、予実の表に工程の情報を持たせようとするのは筋がよくありません。表が重くなり、入力する人が増えず、また数字が合わなくなります。逆に、工程の板の側に費用の手がかりを残すほうが軽く済みます。カードに発注の金額と発注日を書いておけば、月末に費目ごとに拾えます。
道具ごとに、この持たせ方の向き不向きがあります。カード1枚に自由な項目を追加できる道具なら費用の欄を持たせられますが、項目の追加に上のプランが必要な場合もあります。検討している道具の違いを見比べるなら比較の一覧が早く、いま使っている道具が決まっているならTrelloとの比較やNotionとの比較のように1対1で見るほうが判断しやすいでしょう。
もう1つ、予算の数字を扱う場所を移すときに確かめておくことがあります。誰がどこまで見られるかの設定です。部門ごとの費用は、全員が見てよい情報とは限りません。権限の分け方と、データの扱いの考え方は安全性の考え方にまとめられています。移る前に社内の規定と突き合わせておくと、あとで戻す作業を避けられます。
判断に迷う点が残る場合は、よくある質問に人数の数え方やボードの上限の扱いが書かれています。予実の表をいきなり全部移す必要はありません。まずは差異の理由がいちばん多く出ている1つの部門の工程だけを板に移し、翌月の予実で理由の欄が埋まりやすくなったかを見る。この1か月の試しで、移す価値があるかどうかは判断できます。
入力する人が5人前後、部門が3つまでなら、エクセルでも月次の運用は回ります。目安になるのは人数よりも、とりまとめる人が集める作業と突き合わせる作業に使う時間です。月8時間を超えたあたりから、転記の誤りと提出の遅れが同時に増えます。部門が5つを超えると、1か所の遅れで全体が止まるようになります。
年月、部門、科目、予算額、実績額、差異額、差異率、差異の理由の8つです。最も落ちやすいのが差異の理由の欄で、ここが空欄だと翌月に見返しても何も分かりません。理由は文章ではなく、数量増、単価上昇、計上月ずれといった単語で選ばせる形にすると、入力の負担が軽く、あとで分類として集計できます。
最初に疑うのは式の参照範囲です。行数を固定して書いた範囲は、明細が増えた時点から静かに欠けます。範囲をテーブルとして定義すれば解決します。次に疑うのは締めの基準で、部門ごとに請求ベースと発注ベースが混ざっていると、同じ案件が2つの月に分かれます。基準は表の先頭に1行で書いておきます。
入力の表と集計の表が分かれていて、科目の一覧が別シートにあり、参照範囲がテーブルになっているテンプレートなら、そのまま使って構いません。この3つを満たすものが見つからない場合は、自分で組むほうが早いです。明細を年月、部門、科目、予算、実績の5列で作るところから始めれば、最初の表は30分程度で用意できます。