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