【k-means法とは?】アルゴリズムをExcelに実装して在庫管理状態をクラスタリング

2022年2月23日

ある消費財メーカーでは、物流センターの在庫がすべての商品について一律5日分になるように工場から在庫補充しています。

ある日、需給調整部門のマネージャーのAさんは某物流コンサルタントが主催する適正在庫セミナーに参加しました。

そこでAさんは、

理論適正在庫=安全在庫+需要予測在庫(サイクル在庫)の半分

安全在庫=1日当たり出荷数の標準偏差×安全係数×√(リードタイム)

需要予測在庫=1日当たりの出荷数の平均×リードタイム

であることを学びました。

リードタイムや発注サイクルを考慮した適正在庫の計算式を徹底解説!

 

次の日Aさんは部下のBさんを呼んで、セミナーの内容を簡単に説明しました。

既にこの考え方を知っていたBさんは、

「課長、そんなことも知らなかったのですか?」

とは言わずに、

「ということは、すべての商品について一律に5日分の在庫になるように在庫補充している今のやり方は見直した方がいいかもしれませんね。だってうちで一番売れている〇〇は毎日コンサルタントに1万個くらい出ていますが、ニッチ商品の△△は出ても100個くらいで全く出ない日もあるので変動幅が全然違いますもんね。」

するとAさんは、

「そうだな、まずはうちの在庫管理状況がどうなっているか調べてくれるか?」

と何とも曖昧な指示でしたが、優秀なBさんは次の日に次のようなデータをまとめてきました。

変動係数=1日当たり出荷数の標準偏差/1日当たりの出荷数の平均

理論在庫からのズレ=(実際の在庫数―理論在庫数)/理論在庫数

 

これを見たAさんは、

「これはわかりやすいな。過剰在庫と過少在庫が一目でわかる。早速対策を打とう!」

ということになり、この会社では商品ごとに在庫日数を設定することになりました。

 

スポンサーリンク

k-means法とは?

このようにデータセットをその特徴によりいくつかのグループ(クラスタ)に分けることをクラスタリングといいます。

この例では出荷数の変動係数理論在庫からのズレ2つの軸で、3つのクラスタにクラスタリングしました。

このくらいだとグラフにすれば簡単にグループ分けできますね。

ところがこの軸が4つ以上になると、グラフにして人の目でグループ分けすることは難しくなります。

そのような場合に機械的にクラスタリングする方法がいくつかあります。

中でもわかりやすく、ビッグデータになっても適用可能なk-meansについて説明し、Excelに実装して試してみます。

この方法は専用の統計ソフトを使わなくてもExcelでできますので、日常の分析で手軽に利用できます。

 

k-means法のアルゴリズム

k-means法のアルゴリズムでは、それぞれのクラスタの重心が最適になるように更新していきます。

まずはクラスタの数だけ重心をランダムに決め、それを順次更新していくのです。

簡単な例で見ていきましょう。

下記のようなデータセットを2つにクラスタにクラスタリングしたいとします。

 

まずは重心を適当な位置にランダムに打ちます。

 

次に、今打った2つの重心のうちどちらに近いかを基準にして、2つのクラスタに分類します。

 

次に、最初にランダム打った重心を、新たにできたクラスタの重心に移動(更新)します。

 

次に、更新した重心を基準にして、新しいクラスタに分類し直します。

 

するとまた重心の位置が変わりますので、新しい重心に更新します。

 

そして、新たな重心を基準にして、再度クラスタ分けを更新します。

 

再度、重心の位置を更新します。

 

すると、これ以上はクラスタ分けが変わらなくなりました。

これでクラスタリングは終了です。

 

このようにして、最初にランダムに打った重心を順次更新していくことによって、最適なクラスタ分けができるようになります。

 

Excelに実装して在庫管理状態をクラスタリング

2つにクラスタリング

このように見てくると、k-means法のアルゴリズムは①2点間の距離を求める、②それらの大小を比べる、③重心を求めるの3つの計算しかしていないので、Excelに簡単に実装することができます。

そこで、先ほどBさんが作ったデータセットを2つにクラスタリングしてみましょう。

Bさんが作ったデータセットは下記の通りです。

 

これを2つにクラスタリングするために、まずは2つの重心の初期値をランダムに決めます。

ExcelのRAND関数を使って決めてもいいのですが、データセットから余りに遠くの点ではわかりにくいため、今回は変動係数と理論在庫からのズレそれぞれの最大値と最小値を求め、RANDBETWEEN関数を使ってその間の乱数を求めることにします。

また、発生させた乱数はコピー+値だけ貼り付けをして確定させます。

クリックすると拡大します

 

次に、この確定させた重心から195個ある各点までの距離を求めます。

2点間の距離は(x座標の差)2+(y座標の差)2の平方根で計算できますので、次のように求められます。

クリックすると拡大します

 

ついでに、各点がどちらの重心に近いかもMATCH関数を使って求めています。

これで3つの計算のうち、①2点間の距離を求める、②それらの大小を比べるの2つができました。

残り1つの③重心を求めるのは、次のように計算できます。

クリックすると拡大します

 

これで重心が初期値の(6,-1)と(1,8)から(1.97,0.03)と(1.14,4.93)に更新されました。

後は同じように新しい重心から195個の点までの距離を求めて、どちらの重心に近いかを調べます。

どちらのクラスタに属するかを調べるとも言えます。

そして、属するクラスタが変わった点がゼロになるまで更新を続けますので、属するクラスタが変わった点が何点あるかも調べておきます。

それが下図のN列の収束条件でやっていることです。

クリックすると拡大します

 

この場合は第1回の更新で属するクラスタが変わった点が6個あったことを示しています。

これがゼロになるまで同じことを繰り返します。

これはただ単にI列~N列を右側にコピペしていくだけでできます。

その結果、9回目の更新で収束しました。

クリックすると拡大します

 

最終的に各点がどちらのクラスタに属するかはBO列に出ています。

それぞれのクラスタに属するデータを分けてプロットすると、次のようになります。

 

 

このように過剰在庫のデータをクラスタリングすることができました。

 

3つにクラスタリング

以上のやり方では2つにクラスタリングしましたが、重心の初期値を3つにすることで3つにクラスタリングすることもできます。

やり方は全く同じです。

結果は次のようになります。

 

 

 

このように今度は過少在庫(クラスタ2)もクラスタリングできました。

これから分かるように、最初に重心の初期値を増やすことによって所望のグラスタ数にクラスタリングできます。

また今回は出荷数の変動係数と理論在庫からのズレの2軸でデータの特徴を表していますが、これが3軸4軸に増えたとしても全く同じやり方でできます。

収束するまでの更新回数は多くなりますが、コピペするだけなので簡単です。

 

需給調整/業務改善の求人数が多いのはここだ!