エクセル: 手配数量の自動入力化 (在庫管理)

質問

エクセルで在庫管理をしており、その在庫補充数量を自動入力出来るマクロをお教え下さい。

エクセルの項目には、
①Sheet1に、商品名・在庫レベル・SPQ・月末在庫・内示・手配数量があります。
②隣のシートに、在庫レベルのテーブルがあります。

製品の手配数量は、在庫レベルによって基準があるため、各月の在庫回転期間が、その在庫レベルを超え、且つ最小公倍数となる値を求めるようなマクロを組みたいと考えていますが、その方法がありましたら、ご教示下さい。
(手配数は、SPQの倍数になります。)

宜しくお願い致します。

回答No.5

HohoPapa · ベストアンサー

後記のコードで関数を作り、
セルにその関数を埋めるというのはいかがでしょうか。

Option Explicit

Function Get手配数量(ZLevel As String, APQ As Long, ZenZai As Long, Naiji As Long) As Double

　Dim BorderZaiKikan As Double
　Dim addCnt As Double

　Get手配数量 = 0
　addCnt = 0
　BorderZaiKikan = get在庫回転期間(ZLevel)

　Do
　　If BorderZaiKikan <= (ZenZai + addCnt - Naiji) / Naiji Then Exit Do
　　　addCnt = addCnt + APQ
　Loop
　Get手配数量 = addCnt

End Function

Function get在庫回転期間(ZLevel As String) As Double
　Dim i As Long '行カウンター
　i = 2
　get在庫回転期間 = 0
　With ThisWorkbook.Sheets("在庫レベル")
　　Do
　　　If .Cells(i, 1).Value = "" Then Exit Do
　　　If .Cells(i, 1).Value = ZLevel Then
　　　　get在庫回転期間 = .Cells(i, 2).Value
　　　End If
　　　i = i + 1
　　Loop
　End With
End Function

補足

HohoPapa様

ご教示頂きありがとうございます。

お教え頂きましたマクロは、
【Visual Basic】>【標準モジュール】>【Module1】にそのままコピペすれば宜しいでしょうか。

上記にそのままコピペしたところ、、「名前が適切ではありません:GET手配数量」とのエラーメッセージが表示されました。

お手数お掛け致しますが、マクロ初心者の為、ご教示頂きたくお願い致します。

回答No.8

HohoPapa

>G列の数量は、既に手配済の数量が入力されており、
>こちらに数量を入力する必要はございません。
これと
>G3セルには、=Get手配数量($C3,$D3,H3,J3)を入力しております。
これが矛盾しませんでしょうか。

G3セルには
=Get手配数量($C3,$D3,E3,F3)
K3セルには
=Get手配数量($C3,$D3,H3,J3)
を埋めることを想定しています。

お礼

度々ご教示頂きありがとうございました。
解決致しました。

回答No.7

HohoPapa

画像で示せば、こんな感じです。

補足

ご返信遅くなり申し訳ございません。

ご教示頂きましたマクロと関数を入力しましたが、「名前が適切ではありません」とのエラーメッセージが表示され数字が「0」になってしまいました。

G3セルには、=Get手配数量($C3,$D3,H3,J3)を入力しております。

お教え頂きましたマクロとエクセル1行目又は2行目に入力している項目名(APQ、内示等)に相違があるという意味でしょうか。

お手数お掛け致しますが、ご教示をお願い致します。

回答No.6

HohoPapa

>お教え頂きましたマクロは、
>【Visual Basic】>【標準モジュール】>【Module1】にそのままコピペすれば宜しいでしょうか。
はいそうです。
【Visual Basic】>【標準モジュール】>【Module1】にのみコピペします。
その後必要なのは、Ｇ３セルなどにサンプル提示した関数式埋める作業です。
それ以上に必要な作業はありません。

回答No.4

HohoPapa

>例えば、
>K6セル、O6に自動入力したい数量は、
>S6セルは、60になります。
日本語として正しくありません。

>在庫回転期間がゼロ(マイナスにならない)になる数値
であれば、
K6=120
O6=80
S6=80
になりませんでしょうか。

補足

HohoPapa様

申し訳ございません。

K6セル、O6に自動入力したい数量は、120
S6セルは、60になります.

数量は、D列のSPQに基づいて入力したい為でございます。
(バナナのSPQは60の為、この倍数になります。)

回答No.3

HohoPapa

>例えば、K3セルに自動入力したい数量は、940になります。
>みかんの在庫基準レベルが、3.5ヶ月になるため、
>M3セルが、3.5になる数(或いは3.5を超える最も近い数)をK3に入力したいです。

K3が940の時に、私の理解では、
L3=400+940-300=1040
M3=1040/300=3.4666 となり
3.5以上という条件を満たしません。

それとも、小数点以下第二位を四捨五入して3.5と比較するんでしょうか？

最初の発言には
>その在庫レベルを超え
とあるので、3.5超と読み取りましたが
>3.5になる数(或いは3.5を超える最も近い数)
この記述からは3.5以上と読み取れます。

いかがでしょうか？

補足

HohoPapa様

度々申し訳ございません。

ご指摘頂きました通り、3.5以上の数値を入力したく、
K3セルの正しい数は950になります。
(小数点以下第二位を四捨五入は致しません。)

回答No.2

HohoPapa

マクロのサンプルを提供することはやぶさかではないものの、
お求めの仕様がよくわからないのです。

>各月の在庫回転期間が、その在庫レベルを超え、
>且つ最小公倍数となる値を求めるようなマクロ
とのことであれば、
私の理解が正しければ、G3セルは600ではなく1960になりませんでしょうか。
また、在庫レベルがDの時の求め方がよくわかりません。

私なりの解釈で値を求めてきた結果が添付画像です。

これが期待通りか？を確認し
違っているようであれば、期待する値、計算方法を説明してほしいのです。

補足

HohoPapa様

説明が不足しており申し訳ございません。
G列の数量は、既に手配済の数量が入力されており、こちらに数量を入力する必要はございません。

数量を自動入力したい列は、K,O,S列になります。

例えば、K3セルに自動入力したい数量は、940になります。
みかんの在庫基準レベルが、3.5ヶ月になるため、M3セルが、3.5になる数(或いは3.5を超える最も近い数)をK3に入力したいです。

区分 在庫
A+ 3.5 ヶ月

また、K3セルが940の場合、
翌月、3月のQ3セルの在庫回転が 9.4となり、在庫レベルを超える為、
O3セルに自動入力したい数量は、ゼロになります。

在庫レベルがDの時につきましては、
在庫回転期間がゼロ(マイナスにならない)になる数値を入力したいです。
私の記載に不備があり、申し訳ございません。

例えば、
K6セル、O6に自動入力したい数量は、
S6セルは、60になります。

お手数お掛け致しますが、ご教示をお願い致します。

回答No.1

kkkkkm

どのような計算をしたいのかよくわかりませんが、最小公倍数は
A1とB1とした場合
WorksheetFunction.Lcm(Range("A1"), Range("B1"))
で求められます。