エクセル関数 平均価格の計算について

質問

価格が変動する商品の、保有在庫の平均価格の計算について質問させていただきます。
下記のように、「先入先出」で購入と売却を繰り返した場合の、保有在庫の平均価格（E)を計算する数式に関しまして、ご存知の方がいらっしゃいましたらご教示願います。

　購入価格（A）　種目（B）　個数（C）　保有数（D)　平均価格（E)
1　　100　　　　購入　　　　　5　　　　　5　　　　　　100　　
2　　150　　　　購入　　　　　3　　　　　8　　　　　　118.75
3　　 　　　　　売却　　　　　　3　　　　　5　　　　　　130
4　　180　　　　購入　　　　　5　　　　　10 　　　　　155
5　　 　　　　　売却 　　　　　　6　　　　　4 　　 　　 180 　　

よろしくお願いいたします。

回答No.4

tom04 · ベストアンサー

No.3です。
たびたびごめんなさい。

前回のコードでは在庫（保有数）が「0」の場合、「0」で割ってしまうとエラーになり、
マクロそのものも止まってしまいます。
前回のコードは消去して↓のコードにしてください。
（★印の行を追加しました）

Sub Sample2() 'この行から
Dim i As Long, k As Long, cnt As Long
Dim lastRow As Long, myRow As Long, wS As Worksheet
Set wS = Worksheets("Sheet2")
With Worksheets("Sheet1")
For i = 2 To .Cells(Rows.Count, "B").End(xlUp).Row
If .Cells(i, "D") = 0 Then '★
.Cells(i, "E") = "-" '★
Else '★
If .Cells(i, "B") = "購入" Then
myRow = myRow + 1
wS.Cells(myRow, "A") = .Cells(i, "A")
wS.Cells(myRow, "B") = .Cells(i, "C")
lastRow = wS.Cells(Rows.Count, "A").End(xlUp).Row
Range(wS.Cells(1, "C"), wS.Cells(lastRow, "C")).Formula = "=A1*B1"
.Cells(i, "E") = WorksheetFunction.Sum(wS.Range("C:C")) / .Cells(i, "D")
Else
cnt = 0
For k = 1 To wS.Cells(Rows.Count, "A").End(xlUp).Row
If wS.Cells(k, "B") > 0 Then
Do While wS.Cells(k, "B") >= 0
If cnt = .Cells(i, "C") Or wS.Cells(k, "B") = 0 Then Exit Do
wS.Cells(k, "B") = wS.Cells(k, "B") - 1
cnt = cnt + 1
Loop
End If
Next k
.Cells(i, "E") = WorksheetFunction.Sum(wS.Range("C:C")) / .Cells(i, "D")
End If
End If '★
Next i
End With
wS.Cells.Clear
End Sub 'この行まで

※　本来であれば考えられるエラーに関しての対処が必要なのですが、
とりあえずはこの程度で・・・m(_ _)m

お礼

非常に丁寧なご回答をいただき大変感謝しております。

当初は、この程度の計算ならば、関数式で簡単に解決するだろうと丸一日、Excel関数を使って試行錯誤してみましたが、式が複雑になるばかりでどうにも上手くいかず、
また、「在庫の平均価格の計算」のカテゴリーであればネットで調べれば簡単に解決するだろうと時間をかけて調べるも一向に見つからず、とうとう今回の投稿に至った次第です。

実は、最終的にこの投稿をしようと思った時、関数式では解決するのは難しいのかもと感じておりました。
今年に入り、ちょうど本格的にマクロの勉強を始めたところでありましたので、いただいた回答は非常に勉強になりました。

今回の質問は、回答が付かないかもと諦めていたところでしたので、大変ありがたく思っております。

どうもありがとうございました。

回答No.3

tom04

こんばんは！

＞「先入先出」となると関数では結構面倒だと思います。

そこでVBAになりますが一例です。
元データは↓の画像のような配置でSheet1にあるとします。
尚、Sheet2を作業用のSheetとして使用していますので、Sheet2は何も使用していない状態にしておいてください。
Sheet1のB列は「購入」と「売却」の2項目だけという前提です。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）マクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, cnt As Long
Dim lastRow As Long, myRow As Long, wS As Worksheet
Set wS = Worksheets("Sheet2")
With Worksheets("Sheet1")
For i = 2 To .Cells(Rows.Count, "B").End(xlUp).Row
If .Cells(i, "B") = "購入" Then
myRow = myRow + 1
wS.Cells(myRow, "A") = .Cells(i, "A")
wS.Cells(myRow, "B") = .Cells(i, "C")
lastRow = wS.Cells(Rows.Count, "A").End(xlUp).Row
Range(wS.Cells(1, "C"), wS.Cells(lastRow, "C")).Formula = "=A1*B1"
.Cells(i, "E") = WorksheetFunction.Sum(wS.Range("C:C")) / .Cells(i, "D")
Else
cnt = 0
For k = 1 To wS.Cells(Rows.Count, "A").End(xlUp).Row
If wS.Cells(k, "B") > 0 Then
Do While wS.Cells(k, "B") >= 0
If cnt = .Cells(i, "C") Or wS.Cells(k, "B") = 0 Then Exit Do
wS.Cells(k, "B") = wS.Cells(k, "B") - 1
cnt = cnt + 1
Loop
End If
Next k
.Cells(i, "E") = WorksheetFunction.Sum(wS.Range("C:C")) / .Cells(i, "D")
End If
Next i
End With
wS.Cells.Clear
End Sub 'この行まで

※　関数でないので、Sheet1のデータ変更があるたびに
マクロを実行する必要があります。m(_ _)m

回答No.2

narunaru2000

でしたら売却価格は何列になりますか？
数式を入れる以上、売却価格の列が必要かと思いますが。

回答No.1

narunaru2000

売却価格がわからないのに、その後の平均価格がでますか？

補足

質問の一行目に記載させていただいたのですが、「保有在庫の平均価格」という趣旨です。
利益に関する情報が必要なのではなく、保有している(残存している）在庫の平均購入価格が知りたいということです。ということで、分かりにくくなることを防ぐために、あえて売却価格は記載しませんでした。

よろしくお願いいたします。