
生産管理表をエクセルで作ろうとして調べると、見つかるのはガントチャートの作り方や、SUMIFやVLOOKUPといった関数の紹介、テンプレートの配布が中心です。ところが、実際に作り始めて手が止まるのは、受注と製造と出荷をどの表に分け、分けた表をどうつなぐかという部分です。
結論を先に書くと、エクセルで生産管理表を作るときは、関数より先に表のつなぎ方を決めます。順番は、表ごとに1行の単位を決める、マスタをテーブルにする、記録の表では番号をマスタから選ぶ、集計を関数の列で作る、工程表は最後に計画の表から作る、の5つです。本稿では、この順番をエクセルの機能に沿って説明し、最後にファイルの置き場所と、エクセルで持ちきれなくなる目安を整理します。
エクセルの生産管理表が崩れるのは、関数ではなく表のつなぎ方から
生産管理表がうまく回らなくなったとき、関数の知識が足りないことを原因に挙げる必要はあまりないと私たちは考えています。崩れ方を見ると、取引先や品目の名称が表ごとに違う書き方で入っていたり、同じ受注を指す番号が表によって違っていたりと、多くは表どうしのつなぎ方の問題として現れるためです。関数やガントチャートの解説は数多くある一方で、マスタをどこに置き、表どうしを何でつなぐかまで扱った解説は、私たちが調べた範囲では多くありませんでした。
私たちが支援した、板金・切削加工のファブレスメーカー(自社で工場を持たないメーカー)でも、受注番号が途中で変わる、受注と製造ロットや出荷番号が紐付かずに遡れない、受注に対して出荷できているかが分からない、という課題が重なっていました。対策の中心は、データベースの考え方で取引先や品目などのマスタを固定し、エクセルでもマスタのシートをほかの表から参照させることでした。受注番号の扱いを含めた経緯は、受注管理のエクセルをマスタと番号で出荷までつなぐ記事にまとめています。
作る順番は、1行の単位、マスタ、記録、関数の列、工程表
エクセルで生産管理表を作る順番を、私たちは次のように勧めています。
| 順番 | やること | エクセルで使う機能 |
|---|---|---|
| 1 | 表ごとに1行が何の単位かを決める | なし(紙に書き出す) |
| 2 | 取引先と品目のマスタを作り、テーブルにする | テーブル、テーブル名 |
| 3 | 受注・製造計画・作業実績・出荷の記録を作り、コードと番号はマスタから選ぶ | データの入力規則(リスト) |
| 4 | 名称の表示や残数の計算を、関数だけの列で作る | XLOOKUP、SUMIFS、ピボットテーブル |
| 5 | 工程表やガントチャートを、計画の表から日付を読んで作る | 条件付き書式 |
ガントチャートから作り始めると、見た目はすぐに整うものの、計画の日付が工程表のセルに直接入るため、受注や出荷の記録とつながりません。テンプレートを使う場合も、表の5番目、見せ方の部分として取り込み、元データの置き場所にはしないことを勧めます。
手順1:表ごとに、1行が何の単位かを決める
最初の作業は、エクセルを開く前に、作る表の名前と1行が何を表すかを書き出すことです。1行の単位を決めずに作ると、1件の受注を分割して作ったり分納したりしたときに、行を分けるのか列を足すのかで担当者ごとに判断が割れ、表の形が少しずつ崩れていきます。計画の行に実績日の列を足していく形もよく見かけますが、作業が数回に分かれると1行に収まらないため、計画と実績は別の表にし、実績は1回分ずつ行を足していきます。
私たちが勧めている最初の構成を、エクセルのテーブル名で書くと次のようになります。マスタ、日々行を足していく記録、見るための関数の列という3つの役割で表を分ける考え方は、生産管理をスプレッドシートで回す記事で詳しく説明しています。
| テーブル名 | 1行の単位 | 手で入力する列 |
|---|---|---|
| T_取引先 | 取引先1社 | 取引先コード、正式名称、使用中止 |
| T_品目 | 品目1つ | 品目コード、品名、図面番号、標準リードタイム、使用中止 |
| T_受注明細 | 受注番号+明細番号 | 受注番号、明細番号、取引先コード、品目コード、数量、回答納期 |
| T_製造計画 | ロット1つ | ロット番号、受注番号、明細番号、計画数量、開始予定日、当初の完了予定日、最新の完了予定日 |
| T_作業実績 | 作業の1回分 | ロット番号、工程、数量、作業日、作業者 |
| T_出荷 | 出荷の1明細 | 出荷番号、受注番号、明細番号、数量、出荷日 |
会社の受注の形によって列は変わります。複数の受注をまとめて1ロットで作る会社では、ロットと受注の対応を別のテーブルにします。完了予定日を当初と最新の2列に分けておくのは、あとで当初の計画とのずれを見られるようにするためです。
手順2:マスタを先に作り、エクセルのテーブルにする
1行の単位が決まったら、取引先と品目のマスタから作ります。先のファブレスメーカーでマスタとして固定したのは、取引先、品目、受注(案件の情報)、生産と出荷の標準リードタイムの4種類でした。上の構成では、標準リードタイムは品目の列に持たせ、受注はT_受注明細に置いています。受注は日々増えていく記録である一方、製造計画や出荷から指される先になるため、番号を後から変えないという点ではマスタと同じ扱いが必要だと私たちは考えています。
エクセルでは、マスタの範囲をテーブルに変換し、テーブルデザインのタブで名前を付けます。テーブルにすると列の見出しに名前が付き、数式の中でセル番地の代わりに列の名前で参照できるようになります。Microsoftのヘルプによれば、この構造化参照は、テーブルにデータを追加したり削除したりするたびに範囲が調整され、テーブル名や列名を変えた場合もブック内の構造化参照が自動で書き換わります。構造化参照で書いた数式であれば、行を足すたびに数式の範囲を直す作業がなくなるので、行が増え続ける生産管理表に向いています。
名前は、T_取引先やT_品目のように頭に記号を付け、役割が分かるものを勧めています。テーブル1、テーブル2のままにしておくと、数式を読んだときにどの表を指しているかが分からなくなるためです。また、取引先や品目の取引をやめたときに行を消すと、過去の受注からその名称が引けなくなります。行は残したまま、使用中止の列に印を付けて扱います。
手順3:記録の表では、コードと番号をマスタから選ぶ
受注明細、製造計画、作業実績、出荷の記録の表には、取引先や品目の名称を手で書かず、コードと番号だけを入れます。名称は、次の手順4で関数を使って表示します。
コードの入力は、データタブのデータの入力規則でプルダウンにします。入力値の種類にリストを選び、元の値の欄でマスタのテーブルのコード列のセル範囲を選択します。Microsoftのヘルプによれば、元のデータがテーブルにある場合は、テーブルの行を追加・削除するとプルダウンも自動で更新されます。同じ画面のエラーメッセージの設定で停止を選んでおけば、リストにない値は入力できなくなります。製造計画や出荷の表で受注番号を入れる列も、同じように受注明細のテーブルから選ばせます。ただし、受注明細では1つの受注番号が明細の数だけ並ぶため、受注番号と明細番号の組み合わせが実在するかは、確認用の列で2つの条件のCOUNTIFSを使って確かめます。
ただし、入力規則だけで番号の誤りをすべて防げるとは考えないほうが安全です。別の表からコピーして貼り付けた値には入力規則のチェックがかからないことがあるため、記録の表に確認用の列を1つ置いておくことを勧めます。たとえば受注明細のテーブルに =IF(COUNTIFS(T_取引先[取引先コード], [@取引先コード])=0, "未登録", "") という列を作り、未登録と出た行を週に一度見直す運用にします。
番号をどの単位で振るかと、途中で番号が変わったらどう残すかは、エクセルで表を作る前に決めておく必要があります。振る単位の決め方は製番管理の記事で、受注番号が途中で変わったときの対応表は受注管理の記事で説明しています。
手順4:名称の表示と残数は、関数だけの列で作る
表をつなぎ終えてから、ようやく関数を書きます。ここで決めておきたいのは、関数の列には手で値を入れないことです。出荷数量や残数を手で上書きできる状態だと、元の記録と表示が食い違ったときに、どちらが正しいかを確かめる作業が生まれます。表示がおかしいと気づいたら、関数の列ではなく元の記録の行を直す、という決まりを最初に共有しておきます。
テーブルの中で使う関数は、構造化参照で書くと、どの表のどの列を見ているかが数式から読み取れます。受注明細のテーブルの右側に足す、関数だけの列の例を挙げます。
| 足す列 | 関数の例 |
|---|---|
| 取引先名 | =XLOOKUP([@取引先コード], T_取引先[取引先コード], T_取引先[正式名称], "未登録") |
| 出荷数量 | =SUMIFS(T_出荷[数量], T_出荷[受注番号], [@受注番号], T_出荷[明細番号], [@明細番号]) |
| 残数 | =[@数量]-[@出荷数量] |
XLOOKUPは、見つからない場合に返す文字を指定できるため、マスタにないコードがあると未登録と表示されます。ヘルプにはExcel 2016とExcel 2019では使えないとの注記があるので、古い版のエクセルを使う人がいる場合は、VLOOKUPやINDEXとMATCHの組み合わせで同じ表示を作ります。
製造の完成数量を受注明細に戻すときは、作業実績のうち最終工程の行だけを合計します。全工程の実績を足すと、工程の数だけ数量が膨らむためです。手順としては、製造計画のテーブルに、そのロットの最終工程の数量を合計する列を作ります。工程の名前が全品目で同じなら、=SUMIFS(T_作業実績[数量], T_作業実績[ロット番号], [@ロット番号], T_作業実績[工程], "最終検査") のように最終工程の名前を条件に入れ、品目によって最終工程が違う場合は、品目のマスタに最終工程の列を足して条件に使います。受注明細のテーブルでは、受注番号と明細番号が一致するロットのその列をSUMIFSで合計します。月別や取引先別の出荷数量のように、見る切り口が変わる集計は、記録のテーブルを元にピボットテーブルで作れば、関数の列を増やさずに済みます。
手順5:工程表やガントチャートは、最後に計画の表から作る
工程表やガントチャートは、見せ方として最後に作ります。工程表のシートでは、A列にロット番号を入れ、B列とC列に製造計画のテーブルから開始予定日と最新の完了予定日をXLOOKUPで引き、1行目のD列から右へ日付を並べます。そのうえで、D2のセルを起点に、ロットの行と日付の列が交わる範囲を右下まで選択し、条件付き書式の新しいルールとして、数式を使用して書式設定するセルを決定する形式で =AND(D$1>=$B2, D$1<=$C2) を設定し、塗りつぶしの色を選びます。
この形にしておけば、計画の日付を変えるのは製造計画のテーブルの1か所だけで済み、工程表の色はそれに合わせて変わります。工程表のセルに直接色を塗って計画を管理する形は、作るのは簡単なものの、受注番号やロット番号と結び付かず、出荷との突き合わせができません。当初の計画とのずれを見たいときは、C列で引く日付を当初の完了予定日に切り替えた工程表をもう1枚作り、並べて見比べます。
ファイルは1つに決め、複数人で使うなら共同編集を検討する
エクセルの生産管理表で、関数以上に崩れやすいのがファイルの扱いです。共有フォルダに置いたファイルを、それぞれが名前を変えて保存し始めると、どれが正しい版か分からなくなります。最初に正式なファイルを1つに決め、手元にコピーして更新しないという約束を作ります。
複数人で同時に入力したい場合は、ファイルをOneDriveやSharePointに置き、Excelの共同編集を使う形を検討します。Microsoftのヘルプでは、古い共有ブックの機能は多くの制限があるため共同編集に置き換えられたと説明されています。また、共同編集のトラブルシューティングによれば、共同編集が使えるのは.xlsxなどの新しい形式のファイルで、パスワードで暗号化されたファイルや、社内に設置したSharePointサーバー上のブックなどでは使えない場合があります。導入する前に、自社の環境で共同編集ができるかを確かめておきます。
入力する人も、表ごとに決めておきます。マスタは決めた担当だけが直し、記録の表はその予定や事実に責任を持つ担当が入力します。シートの保護を使えば、関数の列やマスタを誤って書き換える事故を減らせます。ただし、ヘルプにもあるとおりシートの保護はセキュリティのための機能ではないため、単価や原価のように見せてはいけない情報は、保護に頼らず別のファイルに分けて管理します。
エクセルで持ちきれなくなる目安と、次の一歩
エクセルの生産管理表が限界を迎えるのは、行数の上限に届くより先に、運用の崩れとして現れることが多いと私たちは見ています。具体的には、マスタを含んだファイルのコピーが部署ごとに増えている、確認用の列に未登録の行が毎週並ぶ、入力の抜けを誰かが電話で確かめている、社内の環境の都合で共同編集が使えずファイルの受け渡しが続いている、といった状態です。設備や人の制約を見ながら、複数のラインや工程へ負荷を自動で割り付けたいという要望が出てきたときも、関数で組むのは苦しくなります。
こうした兆しが出たときに、すべてを一度に生産管理システムへ置き換えようとすると、話が大きくなります。先のファブレスメーカーでは、表の統制を取ったあとにマスタをデータベースへ移し、新しく作った表はそこからマスタを読む形に変えました。その手順はスプレッドシートの記事の最後の節で紹介しています。マスタの置き場所を先に移すという順番は、エクセルを使い続ける会社でも同じように考えられますが、移す先はデータベースのほか、ノーコード(プログラムを書かずに作れる仕組み)のツールなど会社ごとに合うものが違い、一社の順番がそのまま当てはまるとは言えません。何から移すかを決める順番は、生産管理の脱Excelで先に決めるべきことで整理しています。
まとめ
生産管理表をエクセルで作るときに最初に決めるのは、関数やガントチャートよりも、表の単位とつなぎ方です。要点を再確認します。
- エクセルを開く前に、表ごとに1行が何の単位かを書き出し、計画と実績は別の表にします
- 取引先と品目のマスタを先に作り、テーブルにして名前を付けます。取引をやめた取引先や品目は消さずに印を付けます
- 記録の表には名称を書かず、コードと番号をマスタからのプルダウンで選び、確認用の列も置きます
- 名称の表示や残数は関数だけの列で作って手で直さず、切り口が変わる集計はピボットテーブルで作ります
- 工程表は最後に、製造計画の表から日付を読んで条件付き書式で作ります
- 正式なファイルを1つに決め、複数人で使うなら共同編集を検討します。マスタのコピーが増えてきたら、マスタの置き場所を移す時期です
まずは今お使いの表について、シートごとに1行の単位を言えるか、取引先や品目の名称を手で入力する場所がマスタの1か所に絞れているか、計算結果を手で直している場所がないか、の3点を確かめてみてください。
生産管理の表の作り方について、ご相談を承っています
オプトアップ株式会社は、AI導入・業務改善・事業開発の実行支援を行うコンサルティング会社です。表の分け方やマスタの置き場所の設計から、ノーコードやデータベースでの仕組みづくり、AIへの業務移行まで、動く仕組みを作るところまで伴走します(受注〜出荷のデータ管理の支援)。今のエクセルのどこから手を付けるべきかを整理したいという段階でも構いません。無料相談(60分・オンライン)でお話を伺います。
よくある質問
生産管理表をエクセルで作るとき、最初に何をすればよいですか?
関数やガントチャートより先に、表ごとに1行が何の単位かを決め、取引先と品目のマスタを作ることを勧めています。受注や出荷の表はマスタのコードと番号だけを持ち、名称は関数でマスタから引いて表示します。
エクセルのテーブル機能は使ったほうがよいですか?
使うことを勧めています。テーブルにすると、行を足したり消したりしても構造化参照の範囲が自動で調整され、列の名前で数式を書けます。マスタをテーブルにしておくと、そこを元にしたプルダウンも、行の追加や削除に合わせて更新されます。
Excel 2019など古い版のエクセルでも作れますか?
作れます。表の分け方やテーブル、入力規則のプルダウンは古い版でも使えます。マスタから名称を引くXLOOKUPはExcel 2016とExcel 2019では使えないとMicrosoftのヘルプにあるため、その場合はVLOOKUP、またはINDEXとMATCHの組み合わせで同じ表示を作ります。
エクセルの生産管理表を複数人で使うにはどうすればよいですか?
ファイルをOneDriveやSharePointに置き、Excelの共同編集を使う形を検討します。古い共有ブックの機能は制限が多く、共同編集に置き換えられたとMicrosoftのヘルプに書かれています。どの方法でも、正式なファイルを1つに決め、別名で保存した版を作らないことが前提です。
