エクセルで粗利管理表を作る——コピーできる数式と月次の回し方

請求書を出したあとに「この案件、結局いくら残ったんだろう」と考えて、見積書のフォルダと外注先への発注メールを行ったり来たりする。受託をしていると、月に何度かこの時間が発生します。

粗利管理表とは、案件ごとの売上から外注費を引いた粗利と粗利率を一覧で見るための表です。エクセルで作る場合、うまくいくかどうかは外注費をどう入れるかでほぼ決まります。案件シートに外注費を直接打ち込むと、発注が増えるたびに手で足し直すことになり、数字が合わなくなって見なくなります。外注費は発注シートに1行ずつ入れて、案件シートは SUMIF で集計するだけにする。この形にすると、発注を記録した時点で粗利が自動で更新されます。

この記事では、13列の案件シートと8列の発注シート、コピーしてそのまま貼れる数式、粗利率20%未満を赤くする条件付き書式、月次サマリーの作り方までを順番に書きます。

この記事の要点

• 外注費は手で入れず、発注シートから SUMIF で案件ごとに集計する
• 案件シートは13列。手で入れるのは8列で、粗利・粗利率・時間あたり粗利は数式に任せる
• 月次の粗利率は「案件ごとの粗利率の平均」ではなく「月の粗利÷月の売上」で出す

粗利管理表に必要な列——13列、手で入れるのは8列

シートは「案件」「発注」「月次サマリー」の3枚に分けます。まず案件シートの列です。

手で入れるのは A・B・C・D・E・G・H・L の8列だけです。残りの5列は数式なので触りません。数式の列は薄いグレーで塗っておくと、「ここは入力しない」が見た目で分かります。

列を増やしたくなったら、まず「その列を見て何かを決めるか」を考えてください。 決めないなら増やさない方がいい。列が20を超えたあたりから、横スクロールが発生して月末に開かなくなります。案件の進行そのものを管理する列（工程、担当、進捗％）は別表に分けたほうが続きます。案件表側の列設計は案件管理のスプレッドシートテンプレートにまとめてあります。

次に発注シートです。こちらは8列。

この表の要は B列の案件IDです。ここが案件シートのA列と一致していないと集計されないので、プルダウン（データの入力規則→リスト）にして手打ちさせないようにします。

外注費を二重入力しない——SUMIFで案件ごとに集計する

案件シートのI列（外注費）に入れる数式はこれだけです。2行目に入れて、下へコピーします。

=SUMIF(発注!$B:$B,$A2,発注!$F:$F)

「発注シートのB列（案件ID）が、この行のA列と一致する行の、F列（発注金額）を合計する」という意味です。範囲を $B:$B と列全体にしておくのがポイントで、こうしておくと発注シートに行を足しても数式を直す必要がありません。

これで、追加発注が発生したときにやることは「発注シートに1行足す」だけになります。案件シートを開いて外注費を書き換える作業がなくなり、書き換え忘れによる粗利のズレも起きません。発注そのものの管理の仕方は外注管理の方法に書いています。

コピーして使える数式——5列ぶん

案件シートの数式列です。すべて2行目の式なので、貼り付けてから下へコピーしてください。

N() は空欄や文字列を0として扱う関数で、受注金額や工数が未入力のときに #DIV/0! が並ぶのを防ぐために挟んでいます。エラー表示が並ぶ表は、それだけで開く気がなくなるので入れておく価値があります。

M列の「時間あたり粗利」は、入れておくと判断が変わる列です。粗利額だけで見ると大きい案件が良く見えますが、自社の工数で割ると、小さくて手離れのいい案件のほうが効率が良いことがあります。粗利率と粗利額と時間あたり粗利は別の指標で、粗利管理のはじめ方でも触れているとおり、値付けを見直すときはこの3つを並べて見ます。

粗利率20%未満を赤くする——条件付き書式の設定

数字が並んでいるだけの表は、結局どこを見ればいいのか分からず放置されます。危ない行が勝手に目立つようにします。

案件シートの K2:K200 を選んで、条件付き書式→「数式を使用して、書式設定するセルを決定」に次を入れます。

=AND($K2<>"",$K2<0.2)

書式は薄い赤の塗りつぶし＋濃い赤の文字にします。これで粗利率が20%を切った案件だけが赤くなります。

同じように、発注シートの A2:H200 に次を設定すると、未払いの発注が行ごと目立ちます。

=$H2="未払い"

支払予定日を過ぎた未払いを出したい場合は、条件をこう変えます。

=AND($H2="未払い",$G2<TODAY())

閾値の20%はきっかけを作るための線であって、業種の正解値ではありません。自社の過去12ヶ月の平均粗利率を出して、そこから10ポイント下を線にする、という決め方でもかまいません。大事なのは、線を決めて表に埋め込んでおくことです。

月次サマリーで「今月いくら残ったか」を出す

3枚目のシートです。A列に月を 2026-09 の形式でテキストで並べ、右にこの数式を置きます。

月の粗利率は、案件ごとの粗利率を平均してはいけません。 100万円の案件（粗利率60%）と10万円の案件（粗利率10%）の平均は35%ですが、実際の月の粗利率は約55%です。単純平均だと小さい案件に引っ張られて、実態より低い（あるいは高い）数字が出ます。上の表のように「月の粗利÷月の売上」で出してください。

G列の外注比率は、粗利率の裏返しですが並べておくと効きます。外注比率が上がっている月は、単価が下がったのか、外注単価が上がったのか、自社で巻き取れる工程を出しているのかのどれかです。見積もり時点で粗利を作れているかは受託の見積もりの作り方で扱っています。

月次の回し方——4ステップ

表は作った日がいちばん精度が高く、あとは放っておくと荒れます。回すのは月4回ではなく、日常1つと月末3つです。

1. 発注したその日に発注シートへ1行足す。 これだけは月末にまとめない。金額と支払予定日を後から思い出すのが一番つらい作業です
2. 月末に、納品した案件のステータスと納品予定日を実績に直す。 計上月が確定します
3. 月次サマリーで、その月の粗利率と外注比率を見る。 前月と比べて数字が動いていたら理由を1行メモに書く
4. 赤くなっている案件を1件ずつ見る。 仕様変更で工数が増えたのか、見積もりが甘かったのか、外注単価が上がったのか。原因を書き残すと次の見積もりに効きます

4の「仕様変更で粗利が消える」パターンは受託でいちばん多いので、仕様変更・追加対応の管理も合わせて読んでください。

Googleスプレッドシートで使うときの注意

作った表を共有のためにGoogleスプレッドシートへ持っていくことがあります。この記事で使った関数（SUMIF / COUNTIF / IF / N / TEXT / AND / TODAY）はどちらでも同じように動きます。違いが出るのは次の3点です。

エクセル側でテーブル機能を使っていると、Googleスプレッドシートへ移したときに「新しい行に数式が入らない」状態になります。共有する予定があるなら、最初からテーブルにせず、200行ぶんの数式をあらかじめ入れておくほうが安全です。

この表で足りなくなるサイン

エクセルの粗利管理表は、1人〜2人で年20〜30案件くらいまでは十分に回ります。次のことが起き始めたら、表の作り方ではなく置き場所の問題です。

• 発注シートが数百行になり、支払予定日順に見るたびに並べ替えている
• 2人で同時に開いて、片方の入力が消えたことがある
• 粗利は見えるが、入金がいつかは別の表にあり、月末に突き合わせている
• 数式の入った列を誰かが上書きして、気づかないまま数字がずれていた

3つ目については受託・フリーランスの資金繰り管理に、入金と支払を12ヶ月先まで見る考え方を書いています。

発注を入れた時点で粗利が出る仕組み

ここからは自社製品の紹介です。私たちが作っている Pipelia は、発注を登録するとその外注費が案件の粗利に反映され、粗利率20%未満の案件が自動で警告されます。この記事の数式でやっていることを、SUMIF を書かずに済ませる形です。

デモは登録もメールアドレスも不要で、開くとサンプルデータの入った状態を触れます。

よくある質問

粗利管理表はエクセルとGoogleスプレッドシートのどちらで作るべきですか？

1人で作って自分だけが見るならエクセル、外注先や共同経営者と同じ表を見るならGoogleスプレッドシートが向いています。この記事の列設計と数式はどちらでも同じように動きますが、エクセルのテーブル機能（Ctrl+T）だけはGoogleスプレッドシートへ移すときに解除されるため、共有予定があるならテーブルにせず数式を行数ぶん入れておいてください。

粗利率は何パーセントあれば大丈夫ですか？

業種ごとの適正値を示せる調査データを持っていないため、外部の目安を断定することはしません。実務的には、自社の過去12ヶ月の平均粗利率を出し、そこを基準線にする方法が使えます。この記事では「20%未満を赤くする」設定を紹介していますが、これは危ない案件に気づくためのきっかけであって、業種の正解値ではありません。

自分の人件費は外注費に含めますか？

この表の粗利は「売上−外注費」で計算しているため、自分や社員の人件費は含めていません。含めると、案件ごとにいくらの工数を割り当てたかの配賦計算が必要になり、月末の作業が一気に重くなるためです。代わりにL列（自社工数）とM列（時間あたり粗利）を置いて、自社の時間がどれだけ効率よく利益に変わっているかを別の指標として見ます。