エクセルでロット管理をする方法

 

工場勤務の方や商品管理業務をされている方、このようなお悩みはありませんか?

  • 在庫商品の使用期限切れで出荷できなくなった、日頃から使用期限を管理したい(期限管理)
  • 不良品のクレームが発生!どこの生産ロットの商品か調べたい(品質管理のためのトレーサビリティ)
  • 仕入れた時によって値段が違うから、同じ商品でも値段を区別したい(原価管理)

 

この問題を解決できるのがロット管理です。

ロット管理とは在庫管理に加えて、同じ条件で生産される製品(同一品番)に対しロット番号を生産や仕入れごとに割り当てて管理することです。
ロット管理をすれば、「どの日付で生産(仕入れ)した製品が該当しているのか」を遡って照合することができます。

ロット管理するとできるようになること

ロット管理ができれば、同じ商品や部品を別物としてSKU管理できるようになるため、上記のような問題は全て解決できます。

 

しかし、いざロット管理をしようとするとエクセルで管理するのは難しいといった声を多く聞きます。

たしかに、自分で一からエクセルでフォーマットを作成しようとすると非効率になったり、作表することが目標となりむしろ時間がかかってしまいます。

 

一度フォーマットさえ作ってしまえば、ルールに則って記入していくだけですので、慣れてしまえばそこまで難しくないはずです。

 

そこで、当記事で解説している練習用のExcelフォーマットをご用意いたしました!

エクセルでロット管理(フォーマット)

ほかにもたくさん在庫管理に役立つフォーマットも入手できます!

エクセルで管理するメリットは以下の3点です。

  • 開発や保守などの費用が発生しない
  • 自分の使いやすいようにカスタマイズできる
  • インターネット環境でなくても良い

システムを入れるほどでもない商品点数や規模の場合、エクセルによるロット管理をぜひ検討しましょう。

 

この記事ではエクセルでロット管理をする方法を詳しく解説します。

無料フォーマットに沿って解説するので、ぜひ実際に手を動かして練習しながら、読み進めるのがおすすめです。

 

管理表作成のためのデータの準備

管理表の元データを準備しましょう。以下のようなイメージです。

エクセルでロット管理(フォーマット)

必要な項目を記載し、格子で囲います。
ここれでは下記の項目を記載しています。

  • ロット番号
  • 入庫日
  • 出庫期限
  • 廃棄日
  • 入庫数
  • 出庫数
  • 廃棄数
  • 区分
  • 日付(※定期的に入荷の曜日が決まっている場合:【ホームタブ】→【数値】→【ユーザー定義】→”m/d(aaa)”を設定しておくと、曜日管理もしやすいのでおすすめです)

 

表をテーブルに変換する

次に作成した表を「テーブル」に変換します。

 

  1. 作成した表を全体をドラッグで選択
  2. 【挿入タブ】から【テーブル(Ctrl+Tでも可)】を押す
  3. 以下のような画面になり【先頭行をテーブルの見出しとして使用する】にチェックを入れて【OK】を押す。

エクセルでロット管理(フォーマット)

 

テーブルが完成します。(赤枠内でデザインやレイアウトは変更できます。)

エクセルでロット管理(フォーマット)-テーブルのデザイン変更

 

また念のため【テーブルデザイン】→【プロパティ】→【テーブル名】をわかる名前に変更しておきましょう。

エクセルでロット管理(フォーマット)

今回は「ロット管理」とします。

 

※テーブルに名前を付ける理由:

複数のテーブルを作ると、「テーブル1」、「テーブル2」と自動で命名され、何のためのテーブルかわからないようになるため。

 

表の体裁(見た目)を整える

表の見た目を整えることで、入力のミスや漏れを減らせます。

 

枠線を引く

まず、見やすいように太枠線を引きます。
これは、

  • 入庫枠(増えるグループ)
  • 出庫・廃棄枠(減るグループ)

に分けることにより計算ミスを防ぐためでもあります。
イメージは下図の通りです。

エクセルでロット管理(フォーマット)

太枠線を引く順番は、

  1. 赤枠
  2. 黄枠の順番で引きます。

この順番で線を引くと、最新入庫ロットを追加するときに入庫の一番下に行を挿入した際に、太枠線がずれないようにできます。

 

ウィンドウ枠の固定

枠線が引けたら、次にウインドウ枠の固定をします。

  1. セルI2(I列2行目)にカーソルを置く
  2. 【表示タブ】→【ウインドウ枠の固定】を選ぶ

ウィンドウ枠の固定をすると、日付の部分でカーソルを右に移動しても、H列より左の部分が固定されます。

これで、項目と日付を見失うことがなくなります。

 

入庫・出庫数・廃棄数を入力する

いよいよ数値入力していきます。

今回の商品は、消費期限(使用期限)の短いチルド食品か何かをイメージしていただけるとわかりやすいかと思います。

 

入庫数の入力方法

入庫数を入力していきます。まずは下の表をご覧ください。

エクセルでロット管理(フォーマット)

仮に同じ商品が3ロットあるとします。(B列の9/8、9/12、9/16入庫分)

  1. A列(ロット番号)に、今回入庫するロット番号を入力
  2. E列(入庫数)に、入庫数を入力
  3. にそれぞれの日付の列(ここではI列、M列、Q列のことです)に「=E列」を指示

 

出庫数の入力

次に出庫数の入力をします。
5行目に出庫数を最近の日付列まで入力します。

 

次にF列にSUM関数(合計)を入れて計算します。(下図の通り)

エクセルでロット管理(フォーマット)

今回、出庫期限を設けました。

B列(入庫日)とC列(出庫日)の期間がそのロットに対する出庫期間になります。

※ロット番号「AB165483」の場合、I列(9/8(金))からM列(9/12(火))の合計値を計算します。

 

廃棄数

次に廃棄数を入力していきます。(下図の通り)

エクセルでロット管理(フォーマット)

 

  1. G列(廃棄数)に、廃棄数を計算する「入庫数-出庫数」の式(=E列-F列)を入力します。
  2. D列(廃棄日)の廃棄日を確認して、該当する商品の「廃棄数」の行に、入力済のG列の数値を参照して当てはめていきます。(ここでは、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列の計算式をコピーペースト(またはオートフィルタ)してください。

 

ロット管理表の完成

エクセルでロット管理(フォーマット)

できあがりました。これで、ロットごとの入庫数、期限管理もできるようになりました。

新たなロットが発生した場合は、新しい行を追加してロット番号を追加します。

 

いかがでしたでしょうか?
今回はエクセルで活用できる「ロット管理表」の作り方・使い方について解説していきました。高額なソフトを使わずにロット管理が可能になるでしょう。
貴社の商品の生産状況や、入出庫の頻度に合わせて活用してみてください。

ロット管理はリスク管理、顧客の信頼を得るために絶対に必要

ロット管理はハッキリ言って面倒です。

しかし、面倒だからといって怠ってしまうと、次のようなリスクが発生します。

  1. 使用期限が過ぎてしまうものが多発し、廃棄が大量に発生する。(仕入れたお金を捨てることになる)
  2. トレーサビリティができなくなり、品質不良が発生したときに追えない。(品質管理ができず、商品の信頼性を失う)
  3. 仕入れ値に応じた販売価格を設定できず、利益管理ができない。(適切に利益が出なくなる)

どのリスクも発生すれば、会社の経営が傾く大きなリスクにつながる可能性があります。

 

リスクはセキュリティと同じで、発生しなければ、単なるコストだと思いがちです。

しかし、一度発生してしまうと、緊急の対応が必要になり、その後の対策も求められます。

対策を一歩間違えれば信頼性を失いますし、通常の業務ができなくなり、売上にも影響が出ます。

 

エクセルは大量の商品のロット管理には向かない

ごく少数のロット管理であれば、今回紹介したエクセルによるロット管理で十分機能します。

ただし、大量の商品だったり、ロット番号を頻繁に採番するようなものは、今回解説したエクセルによる管理は生産性がガタ落ちします。

 

  • 商品点数が多い
  • 商品点数が少なくてもロット番号を取る機会が頻繁

このような場合は、やはりシステムによるロット管理をお勧めします。

 

在庫管理110番では、ロット管理ができる「成長する在庫管理システム」を提供しています。

成長する在庫管理システム
成長する在庫管理システム

 

成長する在庫管理システムは、ロット管理する機能を標準装備しています。

今すぐにロット管理を始めたい場合はお勧めです。

 

成長する在庫管理システムの3つの特徴

成長する在庫管理システムは、60名規模くらいまでの中小企業が使いやすいように開発したシンプルな在庫です。

他のシステムにはない中小企業が導入しやすい3つの特徴があります。

  1. 自社に必要な機能だけを追加でき、シンプルで誰でも使いやすい
  2. 導入しやすい価格帯(一般的なパッケージシステムの30~60%の導入コスト)
  3. 実務を知る在庫管理アドバイザーが御社の運用に配慮した機能を提案できる

 

在庫管理アドバイザーが機能の助言できるのが一番の強みです。

システム会社は、要望をそのまま受け取ることしかしません。

しかし、在庫管理のプロである「在庫管理アドバイザー」は、御社の状況と将来の運用を考えた機能を提案できます。

 

たとえば、ロット管理をしすぎるのも現場の運用の生産性を落とす原因になります。

ロット管理の必要性と運用を考慮して、在庫管理アドバイザーがあなたの会社に合った必要最小限のロット管理を提案できます。

ロット管理についてよくある提案を簡単に挙げますと、次のようなものがあります。

  • ロット管理をする/しない商品を決める
  • ロット番号の自動生成
  • ロット番号が印字された帳票の作成
  • ロット番号が古いもの順に引当する(自動先入れ先出し)

お問い合わせはこちら