工場勤務の方や商品管理業務をされている方、このようなお悩みはありませんか？
- 在庫商品の使用期限切れで出荷できなくなった！
日頃から商品の使用期限を把握したい（期限管理） - 不良品のクレームが発生！
迅速に回収するため、生産ロットの商品を調べたい（品質管理のためのトレーサビリティ） - 仕入れるタイミングによって値段が違う！
利益を確保するために同じ商品でも値段を区別したい（原価管理）
- 在庫商品の使用期限切れで出荷できなくなった！
この問題を解決できるのがロット管理です。
ロット管理とは同じ条件で生産される製品（同一品番）に対して、生産や仕入れごとにロット番号を割り当てて、個別で在庫管理する方法です。
ロット管理をすれば、全く同じ商品であっても「どの日付で生産（仕入れ）した製品が該当しているのか」を区別できるので遡って照合することができます。
適切な管理を行うことで、同じ商品や部品を別物としてSKU管理できるようになるため、上記のような問題は全て解決できます。
しかし、「いざロット管理をしようとするとエクセルで管理するのは難しい」といった声を多く聞きます。
たしかに、自分で一からエクセルでフォーマットを作成しようとすると非効率になったり、作表することが目標となりむしろ時間がかかってしまいます。
一から資料を作って管理することは、担当者様の手間がかかり、デメリットとなる恐れがあります。
ただ、一度フォーマットさえ作ってしまえば、ルールに則って記入していくだけですので、慣れてしまえばそこまで難しくないはずです。
そこで、当記事では、解説している練習用のExcelフォーマットをご用意いたしました！
ほかにもたくさん在庫管理に役立つフォーマットも入手できます！
エクセルで管理するメリットは以下の３点です。
- 開発や保守などのコストが全く発生しない
- 自分の使いやすいようにカスタマイズできる
- インターネット環境でなくても良い
システムを入れるほどでもない商品点数や規模の場合、エクセルによるロット管理をぜひ検討しましょう。
この記事ではエクセルでロット管理をする方法を詳しく解説します。
無料フォーマットに沿って解説するので、実際に手を動かして練習しながら読み進めるのがおすすめです。
- ロット管理とは？
- エクセルでロット管理表を作る手順
- ロット管理はどんな時に導入すべきか？
- ロット管理のメリットとデメリット
- エクセルでロット管理ができる限界
目次
管理表作成のためのデータの準備
管理表の元データを準備しましょう。以下のようなイメージです。
必要な項目を記載し、格子で囲います。
ここれでは下記の項目を記載しています。
- ロット番号
- 入庫日
- 出庫期限
- 廃棄日
- 入庫数
- 出庫数
- 廃棄数
- 区分
- 日付（※定期的に入荷の曜日が決まっている場合：【ホームタブ】→【数値】→【ユーザー定義】→”m/d(aaa)”を設定しておくと、曜日管理もしやすいのでおすすめです）
表をテーブルに変換する方法と手順
次に作成した表を「テーブル」に変換します。
テーブル化するメリット
テーブルとは、データ管理しやすくするエクセルの機能です。
テーブル化する特に大きなメリットは、
- 数式を自動で入力できる（人為的な数式の入力漏れ、ミスを防げる）
- 表の体裁を保つことができる（データの扱いやすさを保つことができる）
エクセルで問題になりやすい、「時間がかかる」と「属人化」を防止を実現できます。
効率化と共有化ができるようになるので、ぜひ実施をお勧めします。
それでは、実際にテーブル化する手順を解説します。
テーブル化する手順
- 作成した表を全体をドラッグで選択
- 【挿入タブ】から【テーブル（Ctrl+Tでも可）】を押す
- 以下のような画面になり【先頭行をテーブルの見出しとして使用する】にチェックを入れて【OK】を押す。
テーブルが完成します。（赤枠内でデザインやレイアウトは変更できます。）
また念のため【テーブルデザイン】→【プロパティ】→【テーブル名】をわかる名前に変更しておきましょう。
今回は「ロット管理」とします。
※テーブルに名前を付ける理由：
複数のテーブルを作ると、「テーブル１」、「テーブル２」と自動で命名され、何のためのテーブルかわからないようになるため。
表の体裁（見た目）を整える
表の見た目を整えることで、入力のミスや漏れを減らせます。
枠線を引く
まず、見やすいように太枠線を引きます。
これは、
- 入庫枠（増えるグループ）
- 出庫・廃棄枠（減るグループ）
に分けることにより計算ミスを防ぐためでもあります。
イメージは下図の通りです。
太枠線を引く順番は、
- 赤枠
- 黄枠の順番で引きます。
この順番で線を引くと、最新入庫ロットを追加するときに入庫の一番下に行を挿入した際に、太枠線がずれないようにできます。
ウィンドウ枠の固定
枠線が引けたら、次にウインドウ枠の固定をします。
- セルI2（I列2行目）にカーソルを置く
- 【表示タブ】→【ウインドウ枠の固定】を選ぶ
ウィンドウ枠の固定をすると、日付の部分でカーソルを右に移動しても、H列より左の部分が固定されます。
これで、項目と日付を見失うことがなくなります。
入庫・出庫数・廃棄数を入力する
いよいよ数値入力していきます。
今回の商品は、消費期限（使用期限）の短いチルド食品か何かをイメージしていただけるとわかりやすいかと思います。
入庫数の入力方法
入庫数を入力していきます。まずは下の表をご覧ください。
仮に同じ商品が3ロットあるとします。（B列の9/8、9/12、9/16入庫分）
- A列（ロット番号）に、今回入庫するロット番号を入力
- E列（入庫数）に、入庫数を入力
- にそれぞれの日付の列（ここではI列、M列、Q列のことです）に「=E列」を指示
出庫数の入力
次に出庫数の入力をします。
５行目に出庫数を最近の日付列まで入力します。
次にF列にSUM関数（合計）を入れて計算します。（下図の通り）
今回、出庫期限を設けました。
B列（入庫日）とC列（出庫日）の期間がそのロットに対する出庫期間になります。
※ロット番号「AB165483」の場合、I列（9/8（金））からM列（9/12（火））の合計値を計算します。
廃棄数
次に廃棄数を入力していきます。（下図の通り）
- G列（廃棄数）に、廃棄数を計算する「入庫数－出庫数」の式（=E列-F列）を入力します。
- D列（廃棄日）の廃棄日を確認して、該当する商品の「廃棄数」の行に、入力済のＧ列の数値を参照して当てはめていきます。（ここでは、N列、R列、V列に”各ロット番号番号のG列”を参照）
在庫数
次に在庫数を入力していきますが、少しコツが必要です。
まずは下図をご覧ください。
在庫数＝入庫 －（出庫＋廃棄）となります。
全ロットの入庫数の合計と出庫数の合計から計算します。
上記の例では、I7（I列7行目）に”=SUM(I2:I4)-SUM(I5+I6)”を入力します。
次に、J列以降の在庫数を計算します。下図をご覧ください。
先ほどI列で計算入力した在庫数を頭に追加して、あとは先ほどの同じ段取りで黄色から青色を引きます。
分かりやすく言えば、
前日の在庫＋今日の入庫数合計－今日の出庫数合計
となります。
よって、セルに入力する計算式は”=[@[9/8(金)]]+SUM(J2:J4)-SUM(J5+J6)”になります。
K列以降は、J列の計算式をコピーペースト（またはオートフィルタ）してください。
ロット管理表の完成
できあがりました。これで、ロットごとの入庫数、期限管理もできるようになりました。
新たなロットが発生した場合は、新しい行を追加してロット番号を追加します。
いかがでしたでしょうか？
今回はエクセルで活用できる「ロット管理表」の作り方・使い方について解説しました。高額なソフトを使わずに倉庫のロット管理が可能になるでしょう。
貴社の商品の生産状況や、入出庫の頻度に合わせて活用してみてください。
ロット管理はリスク管理、顧客の信頼を得るために絶対に必要
ロット管理はハッキリ言って面倒です。
しかし、面倒だからといって怠ってしまうと、次のようなリスクが発生します。
- 使用期限が過ぎてしまうものが多発し、廃棄が大量に発生する。（仕入れたお金を捨てることになる）
- トレーサビリティができなくなり、品質不良が発生したときに追えない。（品質管理ができず、商品の信頼性を失う）
- 仕入れ値に応じた販売価格を設定できず、利益管理ができない。（適切に利益が出なくなる）
いずれも、会社の経営が傾く大きなリスクにつながる危険性があります。
リスクはセキュリティと同じで、発生しなければ、単なるコストだと思いがちです。
しかし、一度発生してしまうと、緊急の対応が必要になり、その後の対策も求められます。
対策を一歩間違えれば信頼性を失いますし、通常の業務ができなくなり、売上にも影響が出ます。
ロット管理の目的とメリット、デメリット
ロット管理は、リスク管理や信頼性確保のために役立つ管理手法です。
経営的、実務的なメリットがある一方で、デメリットもあります。
ロット管理のメリット
- 品質管理１（トレーサビリティ：製品がどの材料を使って生産されたかなどを遡って追うことができる）
- 期日管理２（期日管理：消費期限や品質保持期限を設定し、生産日や仕入れ日によって区別できる）
- 原価管理（同じものであっても仕入れ値（原価）が大きく違う時に個別で仕入れ値がわかる。適切な値付けも可能）
リスク管理や利益管理に役立てることができます。
ロット管理のデメリット
専門家の立場から言えば、ロット管理は、明らかに現場に大きな負担がかかります。
経営・管理側は、必要最小限にとどめることをお勧めします。
エクセルは大量の商品のロット管理には向かない
ごく少数のロット管理であれば、今回紹介したエクセルによる人力、差作業のロット管理で十分機能します。
ただし、大量の商品だったり、ロット番号を頻繁に手作業で採番にするような場合は、今回解説したエクセルによる管理は、現場の負担が大きすぎ生産性がガタ落ちします。
- 商品点数が多い（目安：50品目）
- 商品点数が少なくてもロット番号を採番する機会が頻繁（目安：トータルで1日50回 ※10分に1回採番）
エクセルによるロット管理で起こる問題
大量のロット管理を人力、手作業で行うと次のような問題が発生しやすいです。
- 採番漏れ
- 二重採番（2つ以上に同じロット番号を付けてしまうこと）
- 識別漏れ（ロット番号が違うことを区別する識別ができない）
そもそもロット管理は、「リスク管理」で行う側面が大きいです。
そのため、1回でも問題が発生するとリスク管理にならないため、「他のものは大丈夫か？？」という疑いが、取引先や社内から出てくるため、ロット管理をしている意味自体が無くなってしまいます。
何のためにロット管理をやるのか？を十分に吟味してエクセルによるロット管理を続けるかどうかを決めましょう。
システム化することによって、以下のようなことが実現します。
- 自動採番（採番漏れや二重採番を防げる）
- 識別可能（識別のための帳票を自動作成）
- ロット別在庫在庫管理（リアルタイムでロット番号ごとの在庫数量がわかる）
やはりシステムによるロット管理をお勧めします。
在庫管理110番では、ロット管理ができる「成長する在庫管理システム」を提供しています。
成長する在庫管理システムは、ロット管理する機能を標準装備しています。
今すぐにロット管理を始めたい方や、在庫の保管を適切に行いたい方、流通の過程を把握したい方などにおすすめです。
成長する在庫管理システムの3つの特徴
成長する在庫管理システムは、60名規模くらいまでの中小企業が使いやすいように開発したシンプルな在庫です。
他のシステムにはない中小企業が導入しやすい3つの特徴があります。
- 自社に必要な機能だけを追加でき、シンプルで誰でも使いやすい
- 導入しやすい価格帯（一般的なパッケージシステムの30～60％の導入コスト）
- 実務を知る在庫管理アドバイザーが御社の運用に配慮した機能を提案できる
【無料】在庫管理アドバイザーに相談する
在庫管理アドバイザーが機能の助言できるのが一番の強みです。
システム会社は、要望をそのまま受け取ることしかしません。
しかし、在庫管理のプロである「在庫管理アドバイザー」は、貴社の状況と将来の運用を考えた機能を提案できます。
たとえば、ロット管理をしすぎるのも現場の運用の生産性を落とす原因になります。
ロット管理の必要性と運用を考慮して、在庫管理アドバイザーがあなたの会社に合った必要最小限のロット管理を提案できます。
ロット管理についてよくある提案を簡単に挙げますと、次のようなものがあります。
- ロット管理をする／しない商品を決める
- ロット番号の自動生成
- ロット番号が印字された帳票の作成
- ロット番号が古いもの順に引当する（自動先入れ先出し）
まとめ
今回は、Excelでのロット管理方法についてご紹介しました。
ロット管理を適切に行うことは、効率的な作業やコスト削減、原材料や商品の物流の追跡に役立ち、生産性向上が実現できます。
Excelでの管理もよいですが、システムを導入し、より快適な管理をご検討してみてはいかがでしょうか？
貴社の取り扱う商品・規模にあった在庫管理システムの構築でお悩みなら、弊社へご相談ください。
IT導入補助金を利用した在庫管理システム導入や、コンサルティングも承っています。
ご相談からシステムの導入、サポートまで、一括で対応させていただきます。
ご不明点やご不安な事がございましたら、お気軽にご連絡くださいませ。