生産計画をエクセルで作る方法　所要量計算と負荷平準化の実装知・限界

生産計画は大日程・中日程・小日程の3階層に分け、所要量計算（何をどれだけ作るか）と能力所要計算（負荷×能力）を工程別・日別に突き合わせることで組めます。エクセルでもここまでは実装できますが、属人化と再計画の手間が必ず壁になります。

私たちが中小製造業の生産管理システムのご相談を受ける際、最初に見せていただくのは決まって生産計画のエクセルです。そこには何十枚ものシートと、担当者しか意味の分からない色分けと、名前だけが残った古いマクロが積み重なっています。この記事では、そのエクセルが実際にどういうロジックで動いているのかを実装レベルで整理し、どこまでは自作で戦えて、どこから先が限界なのかを具体的に線引きします。

生産計画の全体像　3階層で計画期間を分けて考える

生産計画は「大日程・中日程・小日程」の3階層に分けて組むと、エクセルでも破綻しにくくなります。

大日程計画は月次〜年次の粒度で、工場全体の生産能力枠と受注・需要予測を突き合わせ、設備投資や人員計画の前提を作ります。中日程計画は週次〜旬次で、工程別の負荷を山積みし、外注や残業の要否を判断する層です。小日程計画は日次〜時間単位で、実際の作業順序と機械・人の割当を決めます。

現場で見てきた失敗の多くは、この3階層を1枚のシートに詰め込もうとして起きています。大日程の前提が変わるたびに小日程の作業順序シートまで手で直す羽目になり、どこかの工程で更新漏れが発生します。3階層を別シート・別更新頻度で分離し、上位の数字だけを下位シートにリンクで渡す設計にするだけで、崩壊までの寿命はかなり延びます。

所要量計算のメカニズム　独立所要量と依存所要量をどう出すか

所要量計算の核心は、独立所要量（受注・需要予測から出る最終製品の数量）を部品表（BOM）で展開し、依存所要量（部品・原材料の必要数量）へ変換する処理です。

エクセルでの一般的な実装は、BOMを親品目・子品目・員数の3列で持つマスタシートを作り、SUMPRODUCTやSUMIFSで「この期間にこの子品目がいくつ必要か」を集計する形です。在庫を引き当てた後の正味所要量まで出すなら、期首在庫・入庫予定・引当済数量を別列に持ち、差し引き計算を横展開します。品目数が2〜3階層のBOMで数十点程度なら、この構成でも十分実務に使える精度が目安として出ます。

ただし品目が数百点を超え、BOM階層が4段5段と深くなると、SUMPRODUCTの再計算範囲が指数的に膨らみ、ファイルを開くたびに数十秒フリーズする症状が出始めます。ここが独立所要量ロジック単体の限界です。

負荷平準化の実装　山積み表と山崩しの具体手順

負荷平準化は、工程別・日別に負荷を積み上げる「山積み表」を作り、能力を超える日を「山崩し」でならす作業です。

まず各工程・各日の負荷（計画数量×標準時間）をピボットテーブルまたはSUMIFSで集計し、横軸に日付・縦軸に工程を並べた表を作ります。そこに能力（稼働時間×稼働率）の行を並べ、条件付き書式で負荷が能力を超えたセルを赤く塗ると、どの工程のどの日がボトルネックかが一目で分かります。山崩しは、赤いセルの作業を前後の余力がある日へ手作業でドラッグして計画数量をずらす作業で、多くの現場ではこれを担当者の経験と勘で行っています。

この山積み・山崩しの往復を自動化する数式は、Excelの標準関数だけでは組みにくく、多くの現場ではVBAマクロに頼っているのが実情です。マクロが担当者独自の書き方で組まれていると、次に説明する属人化の壁に直結します。

限界①　属人化とブラックボックス化のリスク

エクセルで組んだ生産計画の最大の弱点は、シート構造とマクロのロジックが担当者の頭の中にしかないことです。

山崩しの判断基準（どの工程を優先してずらすか、外注に出す閾値はいくつか）は、多くの場合ドキュメント化されておらず、担当者の経験則としてシートの外に存在しています。実際、生産管理担当者が急な退職・異動をした現場では、後任がシートの意味を解読するだけで数週間を要し、その間は計画更新が事実上止まってしまう、という事態に陥りがちです。エクセルという道具そのものが悪いのではなく、ロジックを構造化せず個人の暗黙知に依存させる運用が問題の本質です。

限界②　再計画の手間と需要変動への追従不可

もう一つの壁は、需要や受注が変わるたびに、所要量計算から山積み・山崩しまでを手作業でやり直す必要があることです。

BOM展開・能力所要計算・山崩しが別シート・別工程で分断されているため、受注が1件変わっただけでも影響範囲を担当者が手で追いかけ、複数シートを開き直して再計算する必要があります。この再計算に半日〜1日かかる現場では、月1回の計画更新が精一杯で、週次・日次で動く実需要の変動には構造的に追いつけません。需要予測をエクセルに組み込んでも、予測値の更新と山積み表の再計算が同期しないため、常に一歩遅れた計画になりがちです。

脱エクセルの判断基準　どこからシステム化を検討すべきか

エクセルの生産計画から次のステップに進むべきサインは、①担当者しかシート構造を把握していない②受注変更の再計算に半日以上かかる③需要変動に計画更新が追いつかない、の3つが重なったタイミングです。

これらが1つだけなら運用でカバーできますが、重なると計画自体が止まるリスクに変わります。自社の生産計画がこの3サインのうち何個当てはまるか整理したい場合は、初月無料の経営AI診断（通常30万円相当）で現状の計画フローを可視化し、どこをシステム化すべきかを一緒に見極めることもできます。

まとめ

生産計画は大日程・中日程・小日程の3階層に分け、所要量計算と負荷平準化（山積み・山崩し）を工程別・日別に回すことでエクセルでも実装できます。ただし品目数の増加とBOM階層の深まりで計算が重くなる限界、担当者の暗黙知に依存する属人化の限界、再計画の手間で需要変動に追いつけない限界の3つは、道具をどう工夫しても構造的に残ります。まずは自社の計画フローが3サインのうちどこに当てはまるか棚卸しし、初月無料の経営AI診断で次の一手を具体化するところから始めてみてください。

よくある質問

生産計画とはそもそも何を決めるものですか？

「いつ・何を・どれだけ作るか」を、大日程（月次〜年次の生産能力枠）・中日程（週次の工程別負荷）・小日程（日次〜時間単位の作業順序）の3階層に分けて決める計画です。中小製造業では大日程を担当者の頭の中、中日程・小日程だけエクセルで管理しているケースが目安として多く見られます。

所要量計算はエクセルのどんな関数・仕組みで組みますか？

受注・需要予測から必要数量を出す独立所要量と、部品表（BOM）展開で導く依存所要量に分け、SUMIFSやSUMPRODUCTで工程別・日別に集計するのが一般的な組み方です。品目数が数百を超えると関数の再計算が重くなり、シートが固まる症状が出始めます。

負荷平準化（山崩し）はエクセルでどう実装すればいいですか？

工程別・日別に負荷（計画数量×標準時間）を積み上げる山積み表を作り、能力（稼働時間×稼働率）を超えた日の作業を、条件付き書式で赤く塗って可視化し、前後の余力日へ手作業でずらすのが一般的な実装です。ずらした後の再計算漏れが起きやすい工程でもあります。

生産計画のエクセル管理には、いつ限界が来ますか？

担当者しかシート構造を把握していない・受注変更のたびに再計算に半日以上かかる・需要変動に月1回の更新しか追いつけない、の3つのサインが重なった時が目安の転換点です。退職や異動が重なると、計画自体が止まるリスクに直結します。

関連記事

• 生産管理をエクセルで組む方法と実装知・限界 — 関連: 生産管理システム全体の実装知
• 工程表をエクセルで作る方法と更新地獄からの脱却 — 関連: 小日程計画（工程表）の実装
• 製造業の原価計算をエクセルで行う実装知 — 関連: 計画数量から原価配賦への接続
• 町工場の見積・図面読み取りをAIで自動化する方法 — 関連: 製造業の属人業務AI化
• 業務自動化のROIを正しく測る方法 — 関連: システム化投資の判断基準