【先着1,000名様!】1,000円分をプレゼント!

価格が変動する商品の、保有在庫の平均価格の計算について質問させていただきます。
下記のように、「先入先出」で購入と売却を繰り返した場合の、保有在庫の平均価格(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   

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

このQ&Aに関連する最新のQ&A

A 回答 (4件)

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
    • good
    • 0
この回答へのお礼

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

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

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

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

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

お礼日時:2015/01/20 09:24

こんばんは!



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

そこで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
「エクセル関数 平均価格の計算について」の回答画像3
    • good
    • 0

でしたら売却価格は何列になりますか?


数式を入れる以上、売却価格の列が必要かと思いますが。
    • good
    • 0

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

この回答への補足

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

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

補足日時:2015/01/19 10:22
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q勤続年数の平均を求めたい時の関数

エクセルで会社の従業員の勤続年数とその平均を求めたいです。
勤続年数のセルに
=DATEDIF(A1,TODAY(),"y")&"年"&DATEDIF(A1,TODAY(),"ym")&"ケ月"
といれて勤続年数を出してます。
それで次に従業員の勤続年数の平均を出したいのですが、AVERAGEを使うとエラー#DIV/0!が出ます。
どうすれば勤続年数とその平均が求められますか?
どんな方法でも良いので教えて下さい!!非常に困ってます(ー_ー)!!

Aベストアンサー

そりゃそうでしょう。計算対象が文字列扱いのデータなんですから、
エラーが出てアタリマエ、平均値が計算できワケがないです。

ですので、勤続年数の「数値」を持つセルを別に作る必要があります。
当然年/月のままじゃダメで勤続延べ何ヶ月という「月」の値にする
必要があります。ということで、上記のセルとは別に、

=DATEDIF(A1,TODAY(),"y")*12+DATEDIF(A1,TODAY(),"ym")

こうすれば、勤続延べ何ヶ月の数字が出てきます。この式を含むセル
についてAVARAGE関数で平均を取ってください。

で、出てくるのは勤続延べ何ヶ月ですから、こいつを12で割って「年」
余りを「月」にする必要があります。平均月が出たセルをB1として、

="平均"&INT(B1/12)&"年"&(B1-INT(B1/12)*12)&"ケ月"

こんな感じで表示できるはずです。
キモは「計算に使うセルと表示するセルを分ける」ということです。

計算に使うセルが表示上邪魔な場合は「書式」「列」「表示しない」
などで表示上隠す方法と、AA1~などの極端に離れたセルに記入して
おく方法などがあります。

そりゃそうでしょう。計算対象が文字列扱いのデータなんですから、
エラーが出てアタリマエ、平均値が計算できワケがないです。

ですので、勤続年数の「数値」を持つセルを別に作る必要があります。
当然年/月のままじゃダメで勤続延べ何ヶ月という「月」の値にする
必要があります。ということで、上記のセルとは別に、

=DATEDIF(A1,TODAY(),"y")*12+DATEDIF(A1,TODAY(),"ym")

こうすれば、勤続延べ何ヶ月の数字が出てきます。この式を含むセル
についてAVARAGE関数で平均を取ってください。

で...続きを読む


人気Q&Aランキング