生産管理をエクセルで自作。まず作るのは計画表ではない
結論から、いきます。生産管理をエクセルで自作するとき、最初に作るものは計画表ではありません。実績の記録です。
多くの場合、最初に作られるのはガントチャートです。色のついた帯が並んで、見た目はいちばん「それっぽい」表です。
でも、納期の遅れを教えてくれるのは、予定ではなく実績のほうです。
今日は、作る順番と、実際に使える表の形を置いていきます。
生産管理システムは、エクセルで自作できるのか？
「全部」をやろうとすると、続きません。
生産管理と呼ばれるものには、受注の管理、工程の進み具合、在庫、原価、購買までが含まれます。これを1つの表でやろうとすると、列が40も50もある表になります。そして誰も入力しなくなります。
1つに絞れば、回ります。最初に絞るなら、ここです。
今どの工程まで終わっていて、納期に間に合うのか。
在庫は別の表に分けてください。作り方は在庫管理システムをエクセルで自作に書きました。
自作のメリットは3つです。
- 費用がかからない … 手元のエクセルで始められます
- 自社のやり方に合わせられる … 工程の呼び方も、順番も、会社ごとに違います
- 要る機能が分かってから、既製品を選べる … 1か月回すと、何が足りないかがはっきりします
デメリットは、同時に入力する人が増えると回らなくなることと、作った人がいなくなると直せなくなることです。詳しくは、下の「限界」と「やめたほうがいい場合」に書きました。
自作の方法は、何を選べばいいのか？
入力する人の数と、入力する場所で決めます。
- エクセル … 入力する人が1〜2人で、事務所のパソコンで入れるなら、これで十分です。この記事の作り方は、エクセルを前提にしています
- Googleスプレッドシート … Googleアカウントがあれば無料で使えて、複数の人が同時に入力できます。現場からスマホで入れたい場合にも向いています
- Access・Python など … 件数が多い、帳票を細かく作りたい、といった場合の選択肢です。ただし作れる人・直せる人が社内に必要になります。そこが用意できないなら、選ばないほうが安全です
どれを選んでも、先に決める3つ（下に書きます）は同じです。道具から選ぶと、たいてい続きません。
なぜ、ガントチャートから作ると続かないのか？
計画表は、作った瞬間から古くなるからです。
前の工程が1日遅れる。すると、後ろの工程が全部ずれます。直すのは、たいてい一人です。その人が忙しくなった週から、表は止まります。
止まった計画表は、こう見えます。
- 予定では今日が組立の日。でも実際はまだ加工が終わっていない
- 表は「予定どおり」の顔をしたまま
- 気づくのは、納期の3日前
実績の記録は逆です。工程が終わった日を1行ずつ残していくだけなので、更新の手間がほとんどありません。そして、予定日と引き算すれば、遅れはその場で出ます。
⚠ ガントチャートを作るなとは言っていません。実績が続けて入るようになってから作れば、勝手に埋まります。順番の問題です。
自作する前に決める、3つのこと
① 1行を何にするか
ここを決めないと、あとで全部作り直しになります。
おすすめは「受注番号 × 工程」です。受注番号ごとに1行ではありません。1つの受注が4工程を通るなら、4行になります。
| 1行の単位 | どうなるか |
|---|---|
| 受注番号ごとに1行 | 途中の工程がどこまで進んだか書けない |
| 受注番号 × 工程で1行 | どの工程で止まっているかが分かる |
| 日付ごとに1行 | 日報にはなるが、納期は追えない |
② 誰が、いつ実績を入れるか
工程が終わった人が、終わったときに入れる。これ以外の形は続きません。
「事務所で夕方まとめて」「翌朝に日報から転記」。この形は、忙しい週から止まります。そして止まった週のぶんは、二度と埋まりません。
③ 遅れが見えたとき、誰が順番を決めるか
表は、遅れを教えてくれるところまでしかやってくれません。
3件が同時に遅れたとき、どれを先に流すか。決める人を先に決めておかないと、「見えるようになっただけ」で終わります。
⚠ ここが、いちばん飛ばされます。仕組みを入れても現場が変わらない原因は、たいていこれです。
表は、どう作ればいいのか？
6列だけです。
| A：受注番号 | B：品名 | C：工程 | D：予定日 | E：完了日 | F：数量 |
|---|---|---|---|---|---|
| 2609-012 | ブラケット | 加工 | 9/18 | 9/18 | 50 |
| 2609-012 | ブラケット | 溶接 | 9/22 | 50 | |
| 2609-013 | シャフト | 加工 | 9/19 | 30 |
完了日が空の行が、まだ終わっていない工程です。
遅れ日数は、G列にこの式を入れます。
=IF(E2="",TODAY()-D2,E2-D2)完了日が入っていれば「完了日 − 予定日」、空なら「今日 − 予定日」という意味です。プラスなら遅れ、マイナスなら前倒しです。
上の表なら、9/22予定の溶接が終わっていない状態で今日が9/25なら、3日の遅れと出ます。
あとは、G列がプラスの行だけ色を付けます（ホーム → 条件付き書式 → セルの強調表示ルール → 指定の値より大きい → 0）。
これで「遅れている工程の一覧」になります。朝、この表を開いて色の付いた行だけ見れば、その日の段取りが決まります。
この6列と遅れ日数の式、色付けまでを、そのまま使えるエクセルにしました。登録もメールアドレスも要りません。
予定日と完了日を入れると遅れ日数が自動で出て、遅れている工程の行だけ色が付きます。
⚠ 列を足したくなります。担当者、設備、ロット、良品数。足すほど入力が止まります。まず6列で1か月続けてから、足りないものだけ足してください。
納期が長いと、お客さんは離れるのか？
このまえ、トヨタのGRファクトリーを見学する機会がありました。GRヤリスやGRカローラといったスポーツカーを作っている工場です。
イメージと違ったのは、自動の組み立てラインが何本も並んでいなかったこと。一台ずつ、丁寧に作られていました。品質に合格した部品の中から、検査の情報をもとに、その車に合う部品を選んで組んでいるそうです。
「そら、納期も長いし、値段も高いはずやな」と、すっかり納得しました。それどころか、作っている人たちの想いを知って現場を見たら、欲しくなってしまった。完全に術中です。
ここに、小さな工場のヒントがあると思うんです。納期が長いこと自体で、お客さんは離れません。離れるのは、理由が分からないときと、約束した日に遅れたときです。
だから生産管理の表は、ラインを速くするためだけのものではありません。「この工程に何日かかるから、この日に出せます」と、根拠を持って答えるための表です。一品一様の仕事をしている会社ほど、大手の量産ラインを真似る必要はありません。答えた納期を守れること。それが、そのまま価値になります。
エクセルの自作は、どこで限界が来るのか？
3つあります。
- 同時に入力する人が増えたとき … 複数人で同時に編集するには、Microsoft 365と、OneDriveやSharePoint Onlineへの保存が条件です（Microsoft サポート「Excel ブックを共同編集で同時に共同作業する」）
- 現場でパソコンを開けないとき … 入力が後回しになり、実績が翌日以降になります。半日ずれるだけで、朝の判断には使えません
- 工程数が多く、差し替えが頻繁なとき … 行が増えるほど、並べ替えと目視の手間が増えます
この3つが見えてきたら、表の作り方ではなく、入力する場所を変える時期です。
AIやスマホを使うと、何が変わるのか？
実績を入れる場所が、事務所から現場に移ります。
スマホで「受注番号・工程・完了」の3つを押すだけで、表に1行積み上がる形です。上で作った6列は、そのまま使えます。
先日、これと同じ形で売上の入力の仕組みを作って納めました。在庫でも工程でもなく売上ですが、分かったことは同じです。項目を削るほど、記録が続きます。
もう1つ、製造業で効くのは紙を減らすことより、探す時間を減らすことです。
- 図面が探せない → 図面管理システムの自作。決めるのは3つだけです
- 作業指示書を毎回手で書き起こしている → 過去の指示書を見本に、図面から下書きを作らせる形を納めました。⚠ 判断が要る工程は自動化しません。確認の手間のほうが増えます
既製品を買うか自分で作るかの分かれ目は、AIツールは既製品か、自社で作るかに4つの観点で書きました。
自作をやめたほうがいいのは、どんな場合か？
4つあります。
- 社内に、表の面倒を見られる人がいない … 作った人が抜けると、式が壊れても誰も直せません
- 工程の差し替えが毎日起きる … 表の並べ替えだけで時間が溶けます
- 原価や購買まで、必ず1つにつなげたい … 既製品のほうが確実です
- 今すぐ必要で、試す時間がない … 既製品のほうが早いです
⚠ 既製の生産管理システムが不要という話ではありません。まずエクセルで1か月回すと、自社に何が要るのかが分かった状態で選べます。何から手をつけるかの判断は中小企業のAI導入、どこから手をつけるかにまとめました。
今日やること
紙に3行、書いてください。
① 1行の単位（受注番号 × 工程でよいか）：__________
② 工程が終わったとき、誰がその場で入れるか：__________
③ 3件同時に遅れたとき、順番を決めるのは誰か：__________この3行が埋まったら、表は半分できています。列も式も、あとから足せます。
建設業のように、職人や協力会社を押さえる連絡が先に来る仕事では、組み立て方が変わります。そちらは工事の工程表の作り方にまとめました。
逆に、③が埋まらないなら、まだ作らないほうがいいです。遅れが見えるようになるだけで、現場は変わりません。
「うちは毎日変わるから、表なんて追いつかない」と思われたかもしれません。毎日変わる会社ほど、実績の1行が効きます。予定は変わっても、終わった事実は変わらないからです。応援しています！
よくある質問
生産管理システムを自作するメリットは何ですか？
3つあります。手元のエクセルやスプレッドシートで始められるので費用がかからないこと、工程の呼び方や順番を自社のやり方に合わせられること、回してみてから既製品に何が必要かを判断できることです。デメリットは、同時に入力する人が増えると回らなくなることと、作った人がいなくなると直せなくなることです。まず1つの表で1か月回し、限界が見えてから次を決めると失敗が減ります。
生産管理システムはエクセルで自作できますか？
受注管理・工程管理・在庫・原価まで全部を1つの表でやろうとすると、まず続きません。ただし「今どの工程まで終わっていて、納期に間に合うか」だけに絞れば、エクセルでも十分に回ります。まず1つの目的に絞って作り、続いてから足すのが順番です。作る前に、1行を何にするか、誰がいつ実績を入れるか、遅れが見えたとき誰が順番を決めるかの3つを決めてください。
ガントチャートから作るのは、なぜうまくいかないのですか？
計画表は作った時点から古くなるからです。前工程が1日遅れるだけで後ろが全部ずれ、直す手間が誰かに集中します。更新が止まった計画表は、遅れを教えてくれません。先に作るべきは実績の記録です。工程が終わった日を1行ずつ残せば、予定日と引き算するだけで遅れが出ます。計画表は、実績が続けて入るようになってから作っても遅くありません。
エクセルの生産管理表は、どう作ればいいですか？
1行を「受注番号 × 工程」にして、受注番号・品名・工程・予定日・完了日・数量の6列で作ります。完了日が空なら未着手か進行中です。遅れ日数はIF関数で、完了日が入っていれば完了日から予定日を引き、空なら今日の日付から予定日を引きます。プラスになった行だけ条件付き書式で色を付ければ、遅れている工程が一目で分かります。列はこれ以上増やさないほうが続きます。
エクセルでの生産管理は、どこで限界が来ますか？
3つあります。同時に入力する人が増えたとき、現場でパソコンを開けず入力が後回しになるとき、そして工程数が多く差し替えが頻繁なときです。特に3つ目は、行数が増えるほど並べ替えと目視の負担が増えます。スマホから工程の完了を送れる形にするか、既製の生産管理システムに移る時期です。まずエクセルで回すと、自社に必要な機能が分かった状態で選べます。
納期が長いと言われたら、どう答えればいいですか？
工程ごとにかかる日数を示して、なぜその納期になるのかを説明します。納期が長いこと自体より、理由が分からないことと、約束した日に遅れることのほうが信用を落とします。生産管理の表で、工程ごとの予定と実績を持っていれば、根拠のある納期を答えられます。答えた納期を守り続けること自体が、取引先にとっての価値になります。
小さな会社の売上アップ・利益改善のご相談を承っています。
お問い合せはこちら