小売業における「商品振替」のマッチングをどのように自動化するか？

私はとある流通業で衣料品の販売に携わっています。
最近デジタルの勉強を始めました。

「商品振替」とは

ところで 「商品振替」 という言葉をご存じでしょうか？
その意味する目的は不振店舗から好調店舗に売れ筋商品を移動し、不振店舗の在庫を減らすことで売上と荒利益を最大化することです
特に衣料品ではシーズン終盤に在庫を効率的に集約することが非常に重要です。

ところが、この商品振替には課題があります。当社にはシステム的な補助ツールが無く、今まで担当者がそれぞれ時間を掛けて膨大な商品データや店頭状況をふまえ経験則で実施という非常に ＜属人化＞ した作業となっています。
私も振替するだけで売上と在庫効率の改善が目に見えているのに、ほとんど手作業のため処理できる範囲が限られていて非常に悔しく感じていました。こうした作業に　【デジタルの力】 を使えば、迅速かつ正確に進められるだけでなく、より多くの店舗をカバーできるようになると思います。

そこでこの課題を解決するために、私の持つ拙い デジタルの「力」 を活用して 「振替起案自動化支援ツール」 を作成することを考えてみました。
膨大な商品量に対して振替対象商品の実績取得を簡易化し、属人化した振替起案の作業は経験則なくても誰でも起案できるツールを目指してチャレンジします。

「商品振替」のマッチング

まずは振替のマッチング、不振店舗は商品振出店、好調店舗は商品受入店となるわけですが、その不振と好調を判断するのに、最も基本的な指標である 「残週数」＜在庫数と売上数の割合であと何週分の在庫があるかを図る＞ としました。

この「残週数」を均一化すれば、全体最適化を図れるのではと考えました。

残週数均一化のVBAコードです

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("sheet") ' シート名を指定

    ' 最終行の取得 (B列が基準)
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row ' B列の最終行を取得

    ' B4セルの値を目標残週数として取得
    Dim targetWeeks As Double
    If IsNumeric(ws.Cells(4, 2).Value) Then
        targetWeeks = ws.Cells(4, 2).Value
    Else
        MsgBox "B4セルの値が無効です。数値を入力してください。"
        Exit Sub
    End If

    ' 変数宣言
    Dim i As Long
    Dim transferQty As Double
    Dim totalTransfer As Double
    totalTransfer = 0

    ' 振替計算の実行 (B6行以降のデータを処理)
    For i = 6 To lastRow
        ' 売上数と在庫数がゼロでない場合、残週数計算を実行
        If IsNumeric(ws.Cells(i, 3).Value) And IsNumeric(ws.Cells(i, 4).Value) And ws.Cells(i, 3).Value > 0 Then
            ' 振替数の計算（目標残週数に基づいて振替数を求める）
            transferQty = (ws.Cells(i, 3).Value * targetWeeks) - ws.Cells(i, 4).Value
            transferQty = Round(transferQty, 2)
            ws.Cells(i, 6).Value = transferQty ' F列に振替数を入力

            ' 振替数の合計を計算
            totalTransfer = totalTransfer + transferQty

            ' G列に振出・振入の自動設定 (修正：I列→G列)
            If transferQty < 0 Then
                ws.Cells(i, 7).Value = "振出" ' G列に振出を入力
            ElseIf transferQty > 0 Then
                ws.Cells(i, 7).Value = "受入" ' G列に受入を入力
            Else
                ws.Cells(i, 7).Value = "" ' 振替数が0の場合は空白
            End If
        End If
    Next i

    ' 残週数の均一化のための最終調整
    If totalTransfer <> 0 Then
        AdjustTransferToBalance ws, totalTransfer, lastRow
    End If

    MsgBox "振替の計算と最終調整が完了しました。"
End Sub

Sub AdjustTransferToBalance(ws As Worksheet, totalTransfer As Double, lastRow As Long)
    ' 残週数の多い店舗を優先的に誤差調整
    Dim i As Long
    Dim adjustment As Double
    adjustment = totalTransfer

    ' 調整処理 (残週数が目標より多い店舗に振り分け)
    For i = 6 To lastRow
        If ws.Cells(i, 5).Value > ws.Cells(4, 2).Value Then ' 残週数が目標以上
            ws.Cells(i, 6).Value = ws.Cells(i, 6).Value - adjustment ' 調整を反映
            ws.Cells(i, 5).Value = ws.Cells(i, 4).Value / ws.Cells(i, 3).Value ' E列を再計算
            adjustment = 0 ' 調整が完了したら0にする
            Exit For
        End If
    Next i
End Sub

しかし、このように単純な1週間の残週数の均一化で対応できるのはごく限られたケースです。たいていの場合、その週の間に売り上げが無かったり、セールで異常値があったりと１週間の販売数と在庫数だけでのマッチングでは店舗の実情に合わず、そこに振替起案の属人性が起因します。

出来るだけ属人性を排除して、だれでも起案できる体制を作るにはさらに複雑なマッチングシステムが必要です。

そこで残週数を任意に指定できるモデルを作成しました。

残週数任意指定のVBAコードです

Sub AutoAssignTransferWithBalancedAdjustment_LtoQ()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("振替起案") ' シート名を指定

    ' 最終行の取得 (L列が基準)
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 12).End(xlUp).Row ' L列の最終行を取得

    ' L4セルの値を目標残週数として取得
    Dim targetWeeks As Double
    If IsNumeric(ws.Cells(4, 12).Value) Then
        targetWeeks = ws.Cells(4, 12).Value
    Else
        MsgBox "L4セルの値が無効です。数値を入力してください。"
        Exit Sub
    End If

    ' 変数宣言
    Dim i As Long
    Dim transferQty As Double
    Dim totalTransfer As Double
    Dim positiveTransfer As Double
    Dim negativeTransfer As Double
    totalTransfer = 0
    positiveTransfer = 0
    negativeTransfer = 0

    ' 各店舗の振替数を計算し、振出・振入を設定
    For i = 6 To lastRow
        ' 売上数と在庫数がゼロでない場合、残週数計算を実行
        If IsNumeric(ws.Cells(i, 13).Value) And IsNumeric(ws.Cells(i, 14).Value) And ws.Cells(i, 13).Value > 0 Then
            ' 振替数の計算（目標残週数に基づいて振替数を求める）
            transferQty = (ws.Cells(i, 13).Value * targetWeeks) - ws.Cells(i, 14).Value
            transferQty = Round(transferQty, 2)
            ws.Cells(i, 16).Value = transferQty ' P列に振替数を入力

            ' 振出・振入の自動設定
            If transferQty < 0 Then
                ws.Cells(i, 17).Value = "振出" ' Q列に振出を入力
                negativeTransfer = negativeTransfer + transferQty ' マイナスの振替数を加算
            ElseIf transferQty > 0 Then
                ws.Cells(i, 17).Value = "受入" ' Q列に受入を入力
                positiveTransfer = positiveTransfer + transferQty ' プラスの振替数を加算
            Else
                ws.Cells(i, 17).Value = "" ' 振替数が0の場合は空白
            End If

            totalTransfer = totalTransfer + transferQty
        End If
    Next i

    ' 残りの配分を調整して全体を最適化
    If totalTransfer <> 0 Then
        AdjustTransferToBalance_LtoQ ws, positiveTransfer, negativeTransfer, lastRow
    End If

    MsgBox "L列からQ列の振替計算と最終調整が完了しました。"
End Sub

Sub AdjustTransferToBalance_LtoQ(ws As Worksheet, positiveTransfer As Double, negativeTransfer As Double, lastRow As Long)
    Dim i As Long
    Dim excessOutput As Double
    Dim shortageInput As Double
    Dim transferAmount As Double

    ' マイナス（振出）分の残りを残週数の多い店舗に配分
    For i = 6 To lastRow
        If ws.Cells(i, 15).Value > ws.Cells(4, 12).Value And negativeTransfer < 0 Then ' 残週数が目標以上かつマイナス配分
            transferAmount = Abs(negativeTransfer) ' 調整する量
            ws.Cells(i, 16).Value = ws.Cells(i, 16).Value - transferAmount ' P列の振替数を調整
            negativeTransfer = 0 ' 調整完了
            Exit For
        End If
    Next i

    ' プラス（受入）分の残りを残週数の少ない店舗に配分
    For i = lastRow To 6 Step -1
        If ws.Cells(i, 15).Value < ws.Cells(4, 12).Value And positiveTransfer > 0 Then ' 残週数が目標以下かつプラス配分
            transferAmount = positiveTransfer
            ws.Cells(i, 16).Value = ws.Cells(i, 16).Value + transferAmount ' P列の振替数を調整
            positiveTransfer = 0 ' 調整完了
            Exit For
        End If
    Next i

    ' 最終的に振出と振入の合計が0になるように調整完了
End Sub

Sub ClearData_LtoQ()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("振替起案") ' シート名を指定

    ' P6以下、Q6以下のデータを消去
    ws.Range("P6:Q" & ws.Cells(ws.Rows.Count, 12).End(xlUp).Row).ClearContents

    MsgBox "P列とQ列のデータが消去されました。"
End Sub

当週残週数より少ない目標残週数を設定すると振替数のトータルがマイナスと振り出し過ぎになり、また残週数を平均化した後に余った＜振替数＞がマイナスなら「残週数が多い店舗」から順に、プラスなら「残週数が少ない店舗」から順に配分すると、極端な振出店と受入店（この場合は2行目の店舗）が出てしまいます。

逆に当週目標数より大きい残週数を目標にすると、振替数がプラスと受入数が多すぎて現状在庫より増えてしまう形になり全く現実的ではありません。

残週数の均一化というマッチングは「不振店舗から好調店舗に売れ筋商品を移動して売上・荒利の最大化を図る」という振替業務の目的には叶っていますが、均一化した残りの部分をどのように配分して全体最適を図るのかがポイントとなり、そのロジックを今の私のITスキルではVBAで表現するのが難しい状態です。

いづれにせよ、「ChatGPT」によれば振替については

振替に最適なタイミングのまとめ

売上の変動や季節イベント、外部要因を考慮。
目標在庫量に対する過不足が生じたとき。
ロジスティクスの効率やコストを最小化するタイミングを意識。
データ分析による予測に基づいたタイミング調整。

とのことで、これらを踏まえて自動化するのはかなりハードルが高く、なかなか属人化から抜け出るのは厳しそうです。
しかし、振替を「任意の数列の適切な組み合わせ」と考えるとシステマチックに解決出来るわけで、そこが＜勘と経験則＞からの突破口かもしれません。

改めて「ChatGPT」にこの振替業務のロジックの【デジタルツール】を使用した解決策を聞くと、以下のリストが出てきました。

振替の精度向上のためのITソリューション

1. Python + Pandas
2. Power BI + DAX
3. Google Sheets + Apps Script
4. Power Automate + AI Builder
5. Tableau
6. Excel Solver
7. R言語 + Shiny

より高度な最適化やデータのリアルタイム処理を求める場合、Python + Pandas や Power BI、Power Automate、Tableau などのツールが強力な候補です。また、業務の自動化と視覚化を重視する場合は、Power AutomateやGoogle Sheets + Apps Scriptのようなローコードツールが役立ちます。

リストの中には？？？の「ITツール」もあり、私にとっては遥かに難易度の高い壁です。振替マッチングを自動化するには「AI」や「機械学習」といった分野も視野に入れる必要があるということですね。

今回の「残週数」の振替ロジックで「当週目標数より大きい残週数を目標」とする場合、私が作成したVBAではマッチングが破綻したのですが、このリストの中で私が名前を知っている「Python」だとどうなるか？を「ChatGPT」に聞いてみました。

今回のケースで、アプローチ
残週数上位30%の店舗に振出（マイナス）を配分。
残週数下位30%の店舗に振入（プラス）を配分。
振出と振入の合計が必ず 0 になり、合計が 現状在庫 の数値を超えないように調整します。
を解決するアルゴリズムをpythonで出力したらどうなりますか？

今回のケースをPythonで解決するためには、pandas を使用してデータを処理し、最適化ロジックを実装する方法が考えられます。具体的には、残週数上位30%の店舗に対して振出（マイナス）を、下位30%の店舗に振入（プラス）を配分し、振出と振入の合計が0になるように調整するアルゴリズムをPythonで実装します。

Pythonコード

import pandas as pd
import numpy as np

# データの準備 (仮定: 店舗データ)
# L列 = 店舗名, M列 = 売上数, N列 = 在庫数
data = {
    'Store': ['Store_A', 'Store_B', 'Store_C', 'Store_D', 'Store_E', 'Store_F', 'Store_G', 'Store_H', 'Store_I', 'Store_J'],
    'Sales': [500, 600, 300, 400, 200, 800, 700, 500, 600, 900],
    'Stock': [1500, 1800, 900, 1300, 600, 2000, 2100, 1200, 1400, 2500]
}

df = pd.DataFrame(data)

# N5に相当する最大振替数
N5 = 1000

# 残週数の計算（N/M = 残週数）
df['Remaining_Weeks'] = df['Stock'] / df['Sales']

# 残週数でソートして上位30％と下位30％を抽出
df_sorted = df.sort_values(by='Remaining_Weeks', ascending=False)
top_30 = df_sorted.head(int(len(df_sorted) * 0.3))  # 上位30%
bottom_30 = df_sorted.tail(int(len(df_sorted) * 0.3))  # 下位30%

# 振出（上位30%）と振入（下位30%）の配分を計算する関数
def distribute_transfer(upper_df, lower_df, max_transfer):
    total_transfer_out = 0  # 振出の合計
    total_transfer_in = 0  # 振入の合計

    # 上位30%の店舗にマイナス配分
    for idx, row in upper_df.iterrows():
        max_out = min(row['Stock'], max_transfer)  # 在庫数とN5を比較し、最小値を振出
        df.loc[idx, 'Transfer'] = -max_out
        total_transfer_out += max_out

    # 下位30%の店舗にプラス配分
    for idx, row in lower_df.iterrows():
        max_in = min(row['Stock'], max_transfer)  # 在庫数とN5を比較し、最小値を振入
        df.loc[idx, 'Transfer'] = max_in
        total_transfer_in += max_in

    # 合計がN5を超えないか確認し、超えた場合は調整
    total_transfer = min(total_transfer_out, total_transfer_in, max_transfer)

    # 各店舗の調整後の振出・振入を反映
    upper_df['Transfer'] = -(total_transfer * (upper_df['Stock'] / upper_df['Stock'].sum()))
    lower_df['Transfer'] = total_transfer * (lower_df['Stock'] / lower_df['Stock'].sum())

    return upper_df, lower_df

# 上位30%と下位30%での振替処理を実行
top_30_adjusted, bottom_30_adjusted = distribute_transfer(top_30, bottom_30, N5)

# 振替結果をDataFrameに統合
df.update(top_30_adjusted)
df.update(bottom_30_adjusted)

# 最終結果の表示
print(df[['Store', 'Sales', 'Stock', 'Remaining_Weeks', 'Transfer']])

実行結果の例
実行すると、次のような結果が出力されます。

Store  Sales  Stock  Remaining_Weeks  Transfer
0  Store_A    500   1500              3.0   -300.0
1  Store_B    600   1800              3.0   -360.0
2  Store_C    300    900              3.0   -180.0
3  Store_D    400   1300              3.25   390.0
4  Store_E    200    600              3.0    180.0

このアプローチの利点
効率的な配分: 残週数が多い店舗に振出を、少ない店舗に振入を行い、全体のバランスを保つ。
制約条件を守る: N5 に基づき、配分の合計がその値を超えないように調整します。
柔軟性: このコードはデータ量に応じて動作し、店舗数が増えても対応できます。
このPythonコードを実行することで、今回の問題を効率的に解決できます。

「Python」凄いですね！　何の処理をしているのか全く理解が及びませんが、求めている結果が出ているのは分かります。

まとめ

「振替業務」の＜自動化＞を何とかしたいと考えたのですが、実務に取り入れるのはまだまだ 「デジタル」の学び を進めないといけません。
VBAだけでなく、「Python」などのプログラム言語や「Power BI」などITツールの活用も視野に入れて行く必要があります。とはいえ、現在は「ChatGPT」などの生成AIツールもあり、IT初心者でも取り組みは可能ではないかと思います。

「振替業務」の基本的なマッチングを「デジタルのちから」で＜自動化＞できれば、店頭販売活動や季節イベント、外部要因分析・ロジスティクスの効率など、大いに＜属人化＞するべき業務に傾注することが可能になり、売上・荒利益の拡大に繋がるのではと考えます。