ロット管理とトレーサビリティをエクセルで行う方法と製造番号追跡の限界

リード

製造ロットの追跡は「採番」「入荷→工程→出荷の紐付け」「双方向トレース」「先入先出」の4つで構成され、エクセルでも一定規模までは実装できます。ただしロット数・工程数が増えると、リコール時の遡及に時間がかかるという形で限界が表面化します。

ロット管理とトレーサビリティは「採番」「紐付け」「双方向トレース」「先入先出」の4要素で成り立つ

エクセルでロット管理をしている現場でよく聞く入口は、「リコール想定訓練で遡及に丸1日かかった」という悩みです。この記事では、エクセルでロット管理・トレーサビリティを組む具体的な実装方法と、規模が大きくなったときに何が壊れるかを、実務目線で先にお見せします。

ロット管理・トレーサビリティの全体像

ロット管理は「採番」「入荷→工程→出荷の紐付け」「フォワード/バックワードトレース」「先入先出（FIFO）」の4要素の組み合わせで成り立ちます。この4つが揃って初めて、リコール発生時に「どのロットの原材料が、どの製品に、どこまで出荷されたか」を追跡できます。

要素	目的	エクセルでの実装難度
ロット採番	原材料・仕掛品・製品を一意に識別する	低（採番ルールを決めれば実装可能）
入荷→工程→出荷の紐付け	ロット間の親子関係を記録する	中（シート間の関数突合が必要）
フォワード/バックワードトレース	原材料起点・製品起点の両方向で追跡する	高（双方向の参照列設計が必要）
先入先出（FIFO）	古いロットから使い切る運用を徹底する	中〜高（表示上の工夫＋現場運用の両輪）

多くの企業がまず躓くのは採番ではなく、2番目の「入荷→工程→出荷の紐付け」です。原材料ロットと製品ロットが1対1で対応するなら簡単ですが、実際には複数の原材料ロットを1つの製造バッチに投入し、そのバッチから複数の出荷ロットに分かれるという多対多の関係になるため、単純な表では表現しきれません。

採番の実装難度は低いが、紐付け・双方向トレース・先入先出は難度が上がる

本文中で扱う実装方法は、あくまでエクセルという単一ツールでどこまで対応できるかの検証であり、貴社の監査要件・取引先要求によって必要な精度は変わります。数値・体制の記載は目安・仮説として読み、実運用では自社の要件に照らして調整してください。

ロット採番のルール設計と実装

ロット番号は「年月日＋製造ライン（または工程）＋連番」の組み合わせで設計するのが実務上の基本形です。例えば 20260704-L1-003 であれば、製造日・ラインNo・その日3ロット目という情報を1つの文字列に持たせられます。

ロット番号は年月日・ライン・連番の組み合わせで一意性と可読性を両立させる

原材料側の採番は、仕入先が発行するロット番号をそのまま転記するケースと、自社側で独自にロット番号を振り直すケースの2通りがあります。転記する場合は仕入先ごとにロット番号の桁数・書式がバラバラなことが多く、エクセル上で文字列として扱う列の型を最初にテキスト形式に固定しておかないと、先頭の0が消えるなどの事故が起きます。自社で振り直す場合は、原材料の受入日ベースで採番し、仕入先ロットとの対応表を別シートで持つ形になります。

採番ルールを工程の途中で変更すると、過去ロットとの整合性が崩れます。桁数・区切り文字・連番のリセットタイミング(日次か月次か)は、運用を始める前に固定しておく必要があります。ここを曖昧にしたまま運用を始めてしまうと、半年後に工程が増えたタイミングでルールを作り直すことになり、過去データとの紐付けができなくなる、という事態に陥りがちです。

入荷から出荷までの紐付け方法

エクセルで入荷→工程→出荷を紐付ける最低限の構成は、「原材料受入シート」「製造実績シート」「出荷シート」の3枚に分け、それぞれにロット番号の列を持たせてVLOOKUPまたはXLOOKUPで突合する形です。

原材料受入・製造実績・出荷の3シートをロット番号で突合する構成が最低限のトレーサビリティの土台になる

製造実績シートには「使用した原材料ロット番号（複数可）」と「生成された製品ロット番号」の両方を記録する列が必要です。1つの製造バッチに複数の原材料ロットを投入する場合、この列はカンマ区切りやセミコロン区切りで複数値を入れることになりますが、この時点でVLOOKUPは1対1の検索しか行えないため、複数ロットの投入を正しく引き当てるには工夫が要ります。実務では、原材料ロットと製品ロットの組み合わせを1行1組で持つ「紐付け専用シート」を別に作り、そこを起点にフォワード・バックワード両方の検索を行う設計が現実的です。

出荷シートには、どの製品ロットを、いつ、どの取引先に、何個出荷したかを記録します。ここまで揃って初めて「原材料ロットA→製造バッチB→出荷ロットC→取引先D」という一連の流れが1本の線で追えるようになります。

フォワード・バックワードトレースとリコール対応

フォワードトレースは「この原材料ロットを使った製品はどこまで出荷されたか」を追う方向、バックワードトレースは「この製品ロットにはどの原材料ロットが使われたか」を追う方向です。リコール対応では両方向の検索が必要になります。

フォワードトレース（原材料起点）とバックワードトレース（製品起点）の両方向を追える設計が必須になる

原材料に問題が見つかった場合はフォワードトレースで「どの製品ロットに影響が及ぶか」を特定し、出荷後の製品に問題が見つかった場合はバックワードトレースで「同じ原材料ロットを使った他の製品ロット」まで遡って対象範囲を確定します。エクセルの紐付け専用シートが整備されていれば、フィルターや検索関数でこの作業は可能です。

ただし、シートの行数が数万行を超えてくると、関数の再計算に時間がかかり、検索漏れのリスクも増えます。リコール想定訓練で「遡及に丸1日かかった」という状況は、多くの場合この紐付け専用シートの検索性能とオペレーターの手作業への依存が組み合わさって生まれています。エクセル自体が遅いというより、行数が増えた状態で人がフィルターをかけながら手作業で範囲を絞り込むプロセスに時間がかかる、というのが実態に近い見立てです。

先入先出（FIFO）運用の実装と限界

先入先出は、入荷日順にロットをソートして表示する関数(SORT・SORTBY等)と、ロットごとの残数管理列を組み合わせれば、表示上の払出順は作れます。

表示上のFIFO順を作っても、現物の払出作業が別ロジックで動くと機能しない

問題は、この表がどれだけ正確でも、現場の払出作業者が実際にその順番通りに現物を取り出すとは限らないという点です。棚に複数ロットが混在している状態で、表を見ずに手前にあるロットから取り出してしまえば、先入先出は崩れます。これを防ぐには、エクセル側の表示順とは別に、棚札やロット表示ラベルで現物側にも入荷順が分かる仕組みを持たせる必要があり、結局は二重管理になります。

先入先出のエクセル管理は「システムとして正しい順番を計算できるか」ではなく「その順番を現場作業者がどう確認し実行するか」がボトルネックになります。ここはエクセルの機能改善では解決できない、運用設計の問題です。

エクセル管理の限界と脱エクセルの判断基準

エクセルでのロット管理・トレーサビリティは、ロット数・工程数が少ないうちは十分に機能します。限界が見えてくるのは、リコール想定訓練や監査対応で遡及にかかる時間が長くなってきたとき、あるいは紐付け専用シートの行数が増えて検索・更新に時間がかかるようになってきたときです。

遡及時間・監査対応工数・ロット数の3指標がエクセルからのシステム化を検討するタイミングの目安になる

判断の目安になる指標は次の3つです。

• 遡及にかかる時間: リコール想定訓練で原材料ロットから出荷先までの特定に半日以上かかる場合、実際のリコール対応では致命的な遅れになりえます。
• 監査対応の頻度と負荷: 取引先や認証機関からロット管理の証跡提出を求められる頻度が増えている場合、都度エクセルから手作業で資料を作る工数が積み上がります。
• 紐付け専用シートの行数: 数万行を超えて動作が重くなってきた場合、関数の再計算待ちや検索漏れのリスクが実務上の負担として表面化します。

これらの兆候が出てきた段階で、バーコードやQRコードによるロット読み取り、トレーサビリティ専用システムへの移行を検討するのが実務的な流れです。食品分野ではHACCPの実施状況によって遡及対応のスピードが問われる場面があり、自動車部品分野ではIATF16949などの取引先要求でロット管理の証跡提出を求められることがあります。どちらも「エクセルでは記録できない」のではなく、「エクセルでは遡及・提出のスピードと精度が要求水準に届かなくなる」という形で限界が現れます。

自社のどの工程で紐付けが崩れやすいか、遡及にどれくらい時間がかかっているかを一度整理したい場合は、初月無料の経営AI診断（通常30万円相当）で現状の業務フローを可視化し、システム化すべき箇所とエクセルのまま運用できる箇所を切り分ける提案までご一緒することもできます。

まとめ

エクセルでのロット管理・トレーサビリティは、採番ルールを固定し、原材料受入・製造実績・出荷の3シートをロット番号で紐付ける構成を組めば、一定規模までは実装可能です。限界が表面化するのは、リコール想定訓練での遡及時間、監査対応の頻度、紐付けシートの行数という3つの指標が実務上の負担として積み上がってきたときです。この段階に差し掛かっているかどうかは、実際の遡及時間を一度計測してみることで判断できます。自社の工程が今どの段階にあるか整理したい場合は、初月無料の経営AI診断（通常30万円相当）から着手できます。

よくある質問

ロット番号はどう採番すればいいですか？

最低限「年月日＋製造ライン＋連番」の組み合わせにします。例えば20260704-L1-003なら製造日・ライン・その日3ロット目という意味を持たせられます。原材料側は仕入先の出荷ロットをそのまま社内ロットに転記するか、自社ロットに紐付けキーとして併記します。桁数と区切り文字を最初に固定しないと、後から工程が増えたときに採番ルールが崩れます（一般的な採番設計の目安・仮説。自社の監査要件に応じて要調整）。

エクセルでフォワードトレースとバックワードトレースの両方に対応できますか？

技術的には可能ですが、原材料ロット・工程実績・出荷ロットの3枚のシートをロット番号でVLOOKUPやXLOOKUPで突合する構成が最低限必要です。フォワード（原材料→どの製品に使われたか）とバックワード（製品→どの原材料が使われたか）を両方引けるようにするには、各シートに双方向の参照列を持たせる必要があり、シート数と行数が増えるほど関数が重くなり検索漏れも増えます（実装可否は自社データ量・関数設計に依存する目安・仮説。要検証）。

先入先出（FIFO）をエクセルで徹底する方法はありますか？

入荷日順にロットをソートして表示する関数（SORT・SORTBY等）と、在庫ロットごとの残数管理列を組み合わせれば表示上のFIFO順は作れます。ただし実際の払出作業者がその表を見て正しいロットから取り出す運用が徹底されて初めて機能するため、エクセル側の工夫だけでは守れません。棚札や現物表示との二重管理が実務上は必須になります。

食品（HACCP）や自動車部品ではエクセル管理のままで問題ないですか？

HACCPの実施の程度によっては、原材料ロットから最終製品までの遡及を短時間で提示できる記録体制が求められる場面があり、自動車部品ではIATF16949などの取引先要求でロット管理の証跡提出を求められることがあります。エクセルでも記録自体は可能ですが、遡及にかかる時間や監査対応の負荷が実務上の分岐点になるため、要求水準と自社の運用体制を照らして判断する必要があります（目安・仮説。詳細は認証機関・取引先の指示を確認すること）。

関連記事

• 製造業の在庫管理改善事例 エクセル脱却で欠品・過剰在庫を減らした進め方 — 関連: 製造業の在庫管理とエクセルの限界
• 生産管理をエクセルで回す方法と限界 脱エクセルのタイミングと移行ステップ — 関連: 工程管理とエクセル運用の分岐点
• 製造業の原価計算をエクセルで行う方法と材料費・労務費・経費の配賦の考え方 — 関連: 製造業の実務データ管理
• 出荷管理をエクセルでやる方法 ピッキングと送り状連携の実務と限界 — 関連: 出荷工程の紐付けと限界
• 中小企業のAI利用ログ監査と証跡管理 生成AIを安全に使う記録設計の実務 — 関連: 証跡・監査対応という共通テーマ