【食品製造】在庫ロットの賞味期限が近いものを一覧にして色分けする

想定する場面

食品の製造・物流の現場では、ロットごとの賞味期限を管理し、期限が近い在庫を優先して出荷（先入れ先出し）する必要があります。在庫一覧は毎日更新されますが、期限の近いロットを目で探すのは時間がかかり、見落としが廃棄ロスや出荷事故につながります。

このマクロは、在庫一覧から残り日数を計算し、注意が必要なロットだけを期限の近い順に並べて「期限アラート」シートに出力します。

データの形（例）

「在庫」シートを次の列構成とします。実際の在庫システムから出力した CSV を貼り付ける運用を想定しています。

• A列：品目コード
• B列：品名
• C列：ロット番号
• D列：賞味期限
• E列：在庫数量
• F列：保管場所

コード

残り日数が30日以内（期限切れを含む）のロットだけを集め、残り日数の短い順に並べ替えてから出力します。並べ替えはシートに書き込んだあと Excel の Sort で行っています。

Sub ExpiryAlert()
    Const WARN_DAYS As Long = 30
    Dim ws As Worksheet, outWs As Worksheet, data As Variant, out() As Variant
    Dim i As Long, n As Long, remain As Long, lastRow As Long

    Set ws = ThisWorkbook.Worksheets("在庫")
    Set outWs = ThisWorkbook.Worksheets("期限アラート")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub
    data = ws.Range("A2:F" & lastRow).Value
    ReDim out(1 To UBound(data, 1), 1 To 7)

    For i = 1 To UBound(data, 1)
        If IsDate(data(i, 4)) And Val(data(i, 5)) > 0 Then
            remain = CDate(data(i, 4)) - Date
            If remain <= WARN_DAYS Then
                n = n + 1
                out(n, 1) = remain
                out(n, 2) = data(i, 1): out(n, 3) = data(i, 2): out(n, 4) = data(i, 3)
                out(n, 5) = data(i, 4): out(n, 6) = data(i, 5): out(n, 7) = data(i, 6)
            End If
        End If
    Next i

    outWs.Cells.Clear
    outWs.Range("A1:G1").Value = Array("残り日数", "品目コード", "品名", "ロット", "賞味期限", "在庫数", "保管場所")
    If n = 0 Then MsgBox "30日以内に期限を迎える在庫はありません": Exit Sub
    outWs.Range("A2").Resize(n, 7).Value = out
    outWs.Range("A1").Resize(n + 1, 7).Sort Key1:=outWs.Range("A2"), Order1:=xlAscending, Header:=xlYes

    For i = 2 To n + 1                                  ' 3段階で色分け
        Select Case outWs.Cells(i, 1).Value
            Case Is < 0:  outWs.Rows(i).Interior.Color = RGB(255, 150, 150)   ' 期限切れ
            Case Is <= 7: outWs.Rows(i).Interior.Color = RGB(255, 220, 150)   ' 7日以内
            Case Else:    outWs.Rows(i).Interior.Color = RGB(255, 250, 200)   ' 30日以内
        End Select
    Next i
    outWs.Range("E:E").NumberFormatLocal = "yyyy/mm/dd"
    MsgBox n & " ロットが期限30日以内です"
End Sub

コードのポイント

日付どうしの引き算は日数になるので、「賞味期限 − 今日」で残り日数を求めています。在庫数が 0 のロットは対象外にして、すでに出荷済みのロットが一覧に混ざらないようにしています。

出力用の配列 out は、元データと同じ行数で用意し、条件に合った行だけを前から詰めて入れています。最後に n 行分だけをシートに書き込むので、空白行が出力されることはありません。

現場で使うときの工夫

しきい値（30日・7日）は、品目の賞味期限の長さや、取引先との「納品期限（いわゆる3分の1ルールなど）」によって変わります。品目ごとに許容日数が違う場合は、品目マスタに「出荷期限までの日数」の列を持たせて、品目ごとに判定すると実態に合います。

毎朝の朝礼前にこのマクロを実行して一覧を印刷し、出荷担当者と共有する運用にすると、期限の近い在庫の見落としを防ぎやすくなります。

ほかの記事