生産管理をスプレッドシートで回すときに、表をどう分けてつなぐか

生産管理をGoogleスプレッドシートで始めようとすると、手が止まるのは、シートを何枚に分けるのか、部署ごとにファイルを分けてよいのか、分けた表をどうつなぐのか、という部分ではないでしょうか。調べて見つかるのは、工程表のテンプレートや関数の使い方、システムへの乗り換えを勧める記事が多いように思います。

結論を先に書くと、受注から出荷までを番号でつなぎ、納期と残数を一覧で見るところまでは、表の設計次第でスプレッドシートでも行えます。そのための表は、部署や月ではなく情報の種類で、基準になるマスタ、日々足していく記録、見るための集計の3種類に分け、記録どうしを番号でつなぎ、入力する人を表ごとに決めます。本稿では、この分け方と最初の構成、ファイルを分ける場面、スプレッドシートで持ちきれなくなる場面の順に整理します。

スプレッドシートの強みは同時に触れること、弱みは崩しやすいこと

スプレッドシートが生産管理の入口に選ばれやすいのは、ブラウザで開け、同じファイルを複数の人が同時に編集できるためです。メールでファイルを回す運用に比べれば、どれが最新かで迷う場面は減ります。Googleのヘルプによれば、同時に編集できるのは最大100のタブまたは端末までで、中堅・中小の工場で人数が先に問題になることはあまりないはずです。

その裏返しとして、誰でもどこでも書き込めるので、表は崩れやすくなります。行が足され、列の意味が人によって変わり、手元にコピーされたファイルが別の版として更新され続ける、といったことが起こります。私たちが支援した、板金・切削加工のファブレスメーカー（自社で工場を持たないメーカー）では、受注番号が途中で変わる、受注と製造ロット・出荷番号が紐付かず遡れないといった課題が重なっていました。対策は、データベースの考え方でマスタを固定し、表の統制を取ることでした。その経緯は受注管理のエクセルをマスタと番号で出荷までつなぐ記事にまとめています。

表は、マスタ・記録・集計の3種類に分ける

1枚の大きな表に受注から出荷までを横へ並べる形は、作り始めは分かりやすいものの、分割生産や分納がある会社では長く持ちません。1件の受注が複数回に分けて作られ、分けて出荷されると、1行に収まらなくなるからです。そこで私たちは、情報の種類で表を3種類に分けることを勧めています。

先のファブレスメーカーでマスタとして固定したのは、取引先、品目、受注（案件の情報）、生産と出荷の標準リードタイムの4種類でした。受注は件数こそ日々増えるものの、製造や出荷の記録が指す先になるため、番号を動かさないという点でマスタと同じ扱いにします。

集計の表には手で入力しないと決めておきます。残数や進捗を集計の表の上で手直しできる状態だと、記録と集計が食い違ったときに、どちらが正しいかを判断する作業が生まれるためです。数字を直したくなったら、元の記録を直します。

最初の構成は、受注明細・計画・実績・出荷をシートで持つ

3種類の考え方を具体的な構成に落とすと、次のようになります。ここからは私たちが勧めている組み方で、会社の受注の形によって列は変わります。

標準リードタイムは、この構成では品目マスタの列に持たせています。複数の受注をまとめて1ロットで作る会社では、ロットと受注の対応を別の表にします。なお、本稿は受注から出荷までの流れに絞っているため、材料の在庫や外注先への発注は表に入れていません。足す場合も、発注と入荷を記録の表に1行ずつ足し、在庫の数は入出庫の記録から集計するという同じ考え方で組めます。

この構成で特に守りたいことが二つあります。一つ目は、計画と実績を同じ行に持たないことです。計画の行に実績日の列を足す形はよく見かけますが、作業が数回に分かれると1行に収まりません。実績を別の表へ1回分ずつ足していけば、ロットごとの合計は関数で出せます。完成数量は最終工程の実績だけを合計し、製造計画のロット番号をたどって受注明細へまとめます。全工程を足すと、工程の数だけ数量が膨らむためです。当初の計画とのずれを見たいときのために、完了予定日は当初と最新の2列に分けておきます。

二つ目は、月別・製品別・担当者別にシートを分けないことです。今月分だけを見たい、自分の担当だけを見たいという要望は、日付や担当の列で絞り込めば応えられます。シートを月ごとに増やすと、過去の受注の出荷が翌月のシートに入るたびに集計の範囲を足すことになり、製品別に分ければ、複数の製品を載せた受注の扱いに迷います。同じ種類の記録は1枚に積むのが基本です。スプレッドシートには、自分の画面だけに絞り込みを適用できるフィルタ表示もあるので、複数人で開いているときに互いの画面を乱さずに済みます。

工程表やガントチャートのテンプレートを使うなら、見せ方の一つとして、製造計画のシートから日付を読み込んで表示する形にします。工程表そのものを元データの置き場所にすると、受注や出荷の記録とつながらなくなるからです。テンプレートを選ぶときも、1行が何の単位か、名称を手で入力する形になっていないかを先に確かめます。

シートをつなぐのは番号で、番号は元の表から選ぶ

表を分けたら、つなぐのは番号とコードだけにします。受注明細には取引先名を手で書かず、取引先コードだけを入れて、名称はマスタから関数で引いて表示します。名称を手で入力する場所をマスタの1か所に絞れば、同じ取引先が表ごとに違う書き方で並ぶことは起きにくくなります。

先の構成表の列の順に作った場合の例を挙げると、関数は次のようになります。

使い方の詳細は、公式のヘルプのXLOOKUP、SUMIFS、FILTERをご覧ください。

番号を打ち間違えないように、入力はプルダウンにします。データの入力規則でリストを範囲で指定し、マスタのコード列や製造計画のロット番号の列を選べば、候補はその列の値から作られ、リストにない値は拒否されます。ただし、入力規則だけで番号の誤りをすべて防げるとは考えないほうが安全で、上の3つ目のような確認の欄を別のシートに置いておくことを勧めます。

番号をどの単位で振るか、途中で変わったらどう残すかは、スプレッドシートでもExcelと同じ考え方で扱えます。受注番号が変わるときの対応表と、残数の見方は、受注管理の記事で説明しています。

最初は1つのファイルに集め、分けるのは理由があるときだけ

シートとファイルのどちらで分けるか迷ったら、まず1つのファイルに集めることを勧めます。関数でのつなぎ方が単純で、マスタの場所も一つに決まるためです。ファイルを分ける理由になるのは、社外の協力会社が入力する、見せてよい範囲が部署で違う、重くなって操作に困る、といった場面です。

ファイルを分けると、別のファイルからデータを読み込むIMPORTRANGEを使うことになります。公式のヘルプを踏まえて、設計のときに押さえておきたいことが三つあります。

一つ目は読み込みの段数です。ヘルプでは、読み込んだ結果をさらに別のファイルが読む連鎖を制限し、読み込み先の数も絞ることが勧められています。そこで私たちは、マスタ専用のファイルを用意して各ファイルがそこを直接読む、1段の形にとどめることを勧めています。読み込むファイルと範囲も、必要なものに絞ります。

二つ目は反映の時間です。ヘルプによれば、元のファイルが更新されると開いている読み込み先も更新されますが、反映までにはタイムラグがあり、連鎖が長いほど時間がかかることがあります。開いている間の自動の更新確認は1時間ごととされています。入力した直後に別のファイルの数字が変わる前提で、納期回答のような業務を組まないようにします。

三つ目は読める範囲で、見落とされやすい点です。アクセスを許可すると、読み込み先の編集権限を持つ全員が、元のファイルのどの部分からでも読み込めるようになります。単価や原価を同じ元のファイルに置いたままだと、特定の列だけを読み込む設計にしていても、読み込み先の編集者は他の列も読めてしまいます。社外の人が編集するファイルへマスタを読み込ませるなら、単価や原価はマスタとは別のファイルに分けておきます。

誰が何を入力するかを、表ごとに決める

入力する人の決め方は、表が使い続けられるかどうかを左右すると私たちは考えています。記録の表はその予定や事実に責任を持つ担当が入力し、他の人は読むだけにします。出荷の数を現場からの電話で事務の担当が打ち直している、といった運用が残っていると、入力の遅れと転記の誤りがそのまま表に入るからです。

入力者を決めたら、シートや範囲の保護で編集できる人を絞ります。マスタのシートは決めた担当だけ、集計のシートは関数を作る担当だけ、という形です。ヘルプにも注記があるとおり、この機能はセキュリティ対策ではないため、見せてはいけない情報を守る目的では使わず、誤って書き換える事故を減らすためのものと考えます。

現場でパソコンを開きにくい場合は、Googleフォームの回答をスプレッドシートに保存する方法があります。回答は保存先のスプレッドシートに記録されるので、これを作業実績の表として使います。入力してもらう項目は、ロット番号、工程、数量、作業時間、作業日、作業者に絞ります。なお、フォームと保存先のシートの権限は別々に管理する必要があり、シート側の入力規則には頼れないものとして、フォームから入った番号も確認の欄で点検します。

誤って直された場合に備えて、変更履歴の見方を社内で共有しておくと安心です。行の追加や削除、数式による変更はセルごとの編集履歴に出ない場合があり、以前の版に戻すとセル単位の履歴が消えるとされているので、復元の前にコピーを取ります。

スプレッドシートで持ちきれなくなるのは、件数より計算と運用

スプレッドシートの上限は2,000万セルまたは100MBとされています。私たちの見立てでは、多くの場合、限界はこの上限より先に、次のような形で現れます。

マスタのコピーや入力の抜けが目立ち始めたときに、すべてを一度に新しいシステムへ置き換えようとすると、話が大きくなります。先のファブレスメーカーでは、Excelやスプレッドシートの段階でマスタを固定して統制を取ったあと、マスタをGoogleのBigQueryというデータベースに移し、新しく作るスプレッドシートもそこからマスタを使う形にしました。マスタの修正は、データベースを操作できる担当者が1人で行い、使う人たちは直してほしい点をコメントで伝える形です。スプレッドシートからBigQueryのデータを扱うには、コネクテッドシートという機能もあります。

移す先はBigQueryに限らず、AirtableやNotion、kintoneのようなノーコード（プログラムを書かずに作れる仕組み）のデータベースが合う会社もあります。ここで紹介したのは一社でとった順番であり、どの会社にも同じ形が合うとは言えません。いつ何へ移すかを考える順番は、生産管理の脱Excelで先に決めるべきことで整理しています。

まとめ

生産管理をスプレッドシートで回すときに最初に決めるのは、テンプレートや関数よりも、表の分け方です。要点を再確認します。

• 表はマスタ、記録、集計の3種類に分け、集計の表には手で入力しません
• 受注明細、製造計画、作業実績、出荷を別のシートに持ち、計画と実績を同じ行に持たせません。月別や担当者別にはシートを分けず、列で絞り込みます
• 表どうしは番号とコードでつなぎ、番号は元の表からのプルダウンで入れます
• ファイルは最初は1つにし、分けるときもIMPORTRANGEの読み込みは1段にとどめます。読み込み元に置く情報の範囲にも注意します
• 入力する人を表ごとに決め、シートの保護と変更履歴で事故に備えます
• マスタのコピーが増えてきたら、まずマスタの置き場所を移します。自動の割り付けや部品の融通が必要になったら、計算や記録の仕組みを別に考えます

まずは今お使いの表を、次の三つの問いで見直してみてください。集計の数字を手で直している場所はないか。1行が何の単位かを、シートごとに言えるか。取引先や品目の名称を手で入力している場所は1か所に絞れているか。

生産管理の表の分け方について、ご相談を承っています

オプトアップ株式会社は、AI導入・業務改善・事業開発の実行支援を行うコンサルティング会社です。表の分け方やマスタの置き場所の設計から、ノーコードやデータベースでの仕組みづくり、AIへの業務移行まで、動く仕組みを作るところまで伴走します（受注〜出荷のデータ管理の支援）。今の表のどこから手を付けるべきかを整理したいという段階でも構いません。無料相談（60分・オンライン）でお話を伺います。

よくある質問

生産管理はGoogleスプレッドシートでできますか？

受注から出荷までを番号でつなぎ、納期や残数を一覧で見るところまでは、表の設計次第で行えると考えています。一方で、複数の工程やラインへの負荷を自動で割り付ける計算や、製品のあいだで部品を融通する記録まで同じ表で持とうとすると、記録の漏れや修正の手間が増えていきます。

生産管理のシートは何枚に分ければよいですか？

枚数を先に決めるより、マスタ、日々足していく記録、見るための集計という情報の種類で分けることを勧めています。私たちが勧めている最初の構成は、取引先マスタ、品目マスタ、受注明細、製造計画、作業実績、出荷、集計の7枚です。受注明細は、番号を動かさないという点でマスタと同じ扱いにします。月別・製品別・担当者別にはシートを分けず、列を持たせて絞り込みます。

シートを分けるのとファイルを分けるのは、どちらがよいですか？

最初は1つのファイルの中でシートを分ける形を勧めています。ファイルを分けるのは、社外の人が触る、見せてよい範囲が違う、重くなって操作に困るといった理由があるときです。その場合もIMPORTRANGEの読み込みは1段にとどめ、読み込んだ結果をさらに別のファイルが読む形を避けます。

スプレッドシートのテンプレートをそのまま使ってよいですか？

使う前に、1行が何の単位か、取引先や品目の名称を手で入力する形になっていないかを確かめることを勧めています。工程表やガントチャートのテンプレートは見せ方としては便利ですが、そこを元データの置き場所にすると、受注や出荷の記録とつながりません。

スプレッドシートの生産管理は、いつまで続けられますか？

セルの上限より、マスタのコピーが増える、番号のない記録が増える、誰が直すかが曖昧になるといった運用の崩れが先に来ることが多いと私たちは見ています。こうした兆しが出たら、まずマスタの置き場所をデータベースへ移す順番を検討します。