加重平均と現在使用されている購入材料の求め方

質問

エクセル初心者です。
以下のような表でB列‐材料購入日、C列‐購入材料重量、D列‐購入材料単価、E列に本日の使用重量を入力すると、Ｄ列に加重平均単価を四捨五入で自動計算させたいと思います。
１行目の購入材料から使用していき、使い切ったらわかるように、A列に『済』と表示させたいと思います。自動計算での加重平均の求め方の式と、現在使用中の材料はいつ購入材料なのか？わかるように、使い掛け購入材料の残高がわかるように自動で計算する方法を教えていただけますか。
　　Ａ　　Ｂ　　Ｃ　　Ｄ　　　Ｅ　　Ｄ
１　済　1/30　　200kg　@100　 150kg @100　
２　済　2/10　　250kg　@60　　120kg　@77
３　　　3/16　　300kg　@75　　100kg　@60
４　　　4/2　　 100kg　@90　　220kg　@

回答No.6

okdeath

こんにちわ。
NO１の回答の者ですが、難しくて、私には無理でした。
ＶＢＡを使用すればループが使えるので出来ると思うのですが、ＶＢＡを覚える気が無いのですみません。

なので、質問の要望とはかなりずれますが、入力の仕方を下図のようにされてはどうでしょうか。「どの材料を使用したか」だけはわかると思います。
Ａ列だけ計算式を入れています。
Ａ２　：　=SUM($C$2:C2)-SUM($E$2:E2)
参考まで。ごめんなさいね。

お礼

何度もアドバイスいただき、ありがとうございます。
そうですね。その考えでするといいかもしれないです。
早速試してみます。ありがつございました。

回答No.5

popuplt

解答らしいのがないので試してみました。でも、質問が難しいので参考程度です。

質問は、求める項目が多いので作業列を使います。
G列に購入量の累計　G1=0　G2=IF(C2,G1+C2,"") 下へCopy
H列に使用量の累計　H1=0　H2=IF(E2,H1+E2,"") 下へCopy
I列に使用単価番号　I1=1　I2=IF(H2="","",MATCH(H2,購入累計)) 下へCopy
J列に購入別残量　　J1=0　J2=IF(I2=I3,"",INDEX(購入累計,I2+1)-H2) 下へCopy

F2=IF(I2="","",IF(I2=I1,INDEX(購入単価,I2),ROUND((INDEX(購入単価,I1)*J1+INDEX(購入単価,I2)*(E2-J1))/E2,0))) 下へCopy

A2=IF(MAX(使用累計)>G2,"済","")　下へCopy

なお、購入累計=G1:G5、使用累計=H1:H5、購入単価=D2:D5 の名前定義
ただし、どのような使用状況が考えられるのか不明ですので例示程度にしか対応しません。

お礼

体調不良により入院しておりお返事がおそくなり申し訳ございませんでした。上手く説明できず、又、列番号と下の数字がずれて表示された為、わかりづらくてすみません。
早速試してみましたところ、I列使用単価番号とJ列の購入別残量、及び
F2=IF(I2="","",IF(I2=I1,INDEX(購入単価,I2),ROUND((INDEX(購入単価,I1)*J1+INDEX(購入単価,I2)*(E2-J1))/E2,0))) が上手く表示されません。もうちょっと頑張ってみますが、もう少し詳しくお願いできないでしょうか。初心者ですみません。

回答No.4

ykskhgaki

済みません。先ほどのコメントは削除して、A1の式を下記に訂正してください。

=IF(C1>0,IF(SUM($C$1:C1)<=SUM($E$1:$E$10),"済",SUM($C$1:C1)-SUM($E$1:$E$10)),"")

お礼

はい。ありがとうございます。全体における加重平均ではなく、使用分がいつの購入材料であるかを算定し、加重平均単価を出したく思います。済み表示は出来るようになりました。

回答No.3

ykskhgaki

今回答した者です。
10行までと書いたのに、いつの間にか式が13行までに変わっていました。

お礼

はい。ありがとうございます。全体における加重平均ではなく、使用分がいつの購入材料であるかを算定し、加重平均単価を出したく思います。

回答No.2

ykskhgaki

10行までと仮定して書きます。

A1に以下のように書き、A2からA10までコピー
=IF(C4>0,IF(SUM($C$1:C4)<=SUM($E$1:$E$13),"済",SUM($C$1:C4)-SUM($E$1:$E$13)),"")

加重平均はF列の間違いだと思います。
F1に以下のように書き、
=SUM(($C$1:C1)*($D$1:D1))/SUM($C$1:C1)

ShiftとCtrlとEnterキーを同時に押す。
{=SUM(($C$1:C1)*($D$1:D1))/SUM($C$1:C1)}

これをF2行以下にコピーする。

お礼

体調不良により入院していてお返事がおそくなり申し訳ございませんでした。上手く説明できず　又、列番号と下の数字がずれて表示された為、わかりづらくてすみません。全体における加重平均ではなく、使用した分が何日と何日の単価いくらの分であるか？そしてその日使用分の購入材料の加重平均単価を知りたいのです。

回答No.1

okdeath

こんにちわ。
１行目と２行目はなぜ済（使い切った）になるのかか分かりません。
また、加重平均単価が不明なため、どのような計算なのでしょうか？
それがわかれば分かるのですが・・・

お礼

体調不良により入院しておりお返事がおそくなり申し訳ございませんでした。上手く説明できず、又、列番号と下の数字がずれて表示された為、わかりづらくてすみません。E列には使用の重量が入ります。この場合150gですので、1/30購入の＠100の200gの中から150g使用したと言う事になり、加重平均は＠100となります。二日目に120g使用していますので、前日使用分の残50gと2/10購入の＠60から100g使用し、加重平均は＠77、同じ考えで三日目100gの使用をすると、1.2行目が使い切った事になり【済】となるのです。