[[20150330153541]]　『受注データから在庫一覧をもとに、上から順に在庫』（NAO）　ページの最後に飛ぶ

『受注データから在庫一覧をもとに、上から順に在庫を引き当てる（有る物出し）』（NAO）

Zのようになってほしいです。
この場合、D列にはどのような式を入れればいいでしょうか？
よろしくお願い致します。

X.在庫一覧

Y.出荷可能数調査票（現在）
D列に入れている式は次の通りです。
=VLOOKUP(A2,在庫一覧.xlsx!$A:$B,2,FALSE)-SUMIF($A$2:A2,A2,$B$2:B2)

	A	B	C	D
1	品番	受注数	受注日	出荷可能数
2	1111	20	3/1	-10
3	2222	11	3/1	-1
4	1111	10	3/2	-20
5	2222	3	3/2	-4
6	2222	6	3/3	-10

Z.出荷可能数調査票（こうなってほしい）

	A	B	C	D
1	品番	受注数	受注日	出荷可能数
2	1111	20	3/1	-10
3	2222	11	3/1	-1
4	1111	10	3/2	0
5	2222	3	3/2	7
6	2222	6	3/3	1

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

こうかな？　でも最後のデータが合わない…。

=VLOOKUP(A2,在庫一覧!$A:$B,2,FALSE)-SUMIFS($B$2:B2,$A$2:A2,A2,$C$2:C2,C2)
（???） 2015/03/30(月) 17:06

???さん、ありがとうございます。
優先順位は受注日が古い方が高いんですが、在庫がある分だけ先に出したいんです。
なんとかなりませんでしょうか？
（NAO） 2015/03/30(月) 17:20

実際にどういう計算をしたいのか、詳細に説明してください。

元の式だと、在庫の合計から、受注数の合計を引いていました。受注数は将来分まで足しては駄目だろうから、当日分に変えるよう条件追加しただけです。

例えば、1111は在庫10しかないのに、受注が30もあって、20足りませんが、出力例だと-10と0なので、なんで？、って感じです。
毎日10ずつ、という意味なのかなぁ、と解釈しましたが、そんな在庫管理は無いと思うんですよ。
（???） 2015/03/30(月) 17:29

それとも、こんなでしょうか？　なんかZ表の要望と全然違いますが…。
（これだと、出荷可能数というより、不足数ですね）

=VLOOKUP(A2,在庫一覧!$A:$B,2,FALSE)-SUMIF(A$2:A2,A2,B$2:B2)

あ、これだと元の式ですね。撤回。
（???） 2015/03/30(月) 17:55

ルールが明確でない事と、出力例がどう考えても非常識な結果な事から、やりたい事が判らないままです。
たとえば、1111は3/1の20個が優先でしょう。しかし在庫は10。このとき、10個だけ出荷？　それとも足りないから出庫せず、次の候補に在庫を回す？

日の早い分に少しでも回すルールなら、3/1に10出庫して、他は全部0ですよね？次に回すなら、3/2に10出庫して、他は全部0ですよね？
Z票は、そのどちらにもなっていないわけで、これでは回答が出せないです。実は、以下のようになれば良いのではないでしょうか？

	A	B	C	D
1	品番	受注数	受注日	出荷可能数
2	1111	20	3/1	0
3	2222	11	3/1	0
4	1111	10	3/2	10
5	2222	3	3/2	3
6	2222	6	3/3	6

（???） 2015/03/31(火) 09:34

???さん、色々お手数おかけして申し訳ありません。
ご質問いただいてた件ですが、1111は3/1の20個が優先ですが、在庫が10個なので、足りないので次の候補に在庫を回します。
おっしゃるとおり、

	A	B	C	D
1	品番	受注数	受注日	出荷可能数
2	1111	20	3/1	0
3	2222	11	3/1	0
4	1111	10	3/2	10
5	2222	3	3/2	3
6	2222	6	3/3	6
でも問題ありませんが、できれば、マイナスとなることで、在庫不足数量が分かればと思いました。
よろしくお願い致します。
（NAO） 2015/03/31(火) 10:27

=VLOOKUP(A2,在庫一覧!$A:$B,2,FALSE)-SUMIFS($B$2:B2,$A$2:A2,A2,$C$2:C2,C2) の場合だと、下記のようになってしまい、在庫がないにもかかわらず、他日の受注に対しても出荷可能となってしまいました。

	A	B	C	D
1	品番	受注数	受注日	出荷可能数
2	1111	20	3/1	-10
3	2222	11	3/1	-1
4	1111	10	3/2	0
5	2222	3	3/2	7
6	2222	6	3/3	4
7	2222	6	3/4	4
8	2222	6	3/5	4
（NAO） 2015/03/31(火) 10:32

在庫より多い場合を除いて、出荷数を決めないといけません。
更に、既に出庫が決まっている分は差し引いて、以降を判定しないといけないので、ちょっと面倒。
私の例で良いならば、以下。
=IF(SUMIFS(B$2:B2,A$2:A2,A2,B$2:B2,"<="&VLOOKUP(A2,在庫一覧!$A:$B,2,FALSE))>=B2,IF(VLOOKUP(A2,在庫一覧!$A:$B,2,FALSE)-SUMIFS(B$2:B2,A$2:A2,A2,B$2:B2,"<="&VLOOKUP(A2,在庫一覧!$A:$B,2,FALSE))>=0,B2,0),0)

不足在庫表示となると…、うーん、更に面倒。マクロなら楽なんですが…。
（???） 2015/03/31(火) 10:37

例えば不足在庫という項目をE列に追加して、E2:=D2-B2 で良かったりします？
（在庫分を引かないと駄目かな？）
（???） 2015/03/31(火) 10:43

=IF(SUMIFS(B$2:B2,A$2:A2,A2,B$2:B2,"<="&VLOOKUP(A2,在庫一覧!$A:$B,2,FALSE))>=B2,IF(VLOOKUP(A2,在庫一覧!$A:$B,2,FALSE)-SUMIFS(B$2:B2,A$2:A2,A2,B$2:B2,"<="&VLOOKUP(A2,在庫一覧!$A:$B,2,FALSE))>=0,B2,0),0)

これで出荷可能数は出ました！

これに不足在庫の列を追加して表示する方法でもいいですね！
ですが、=D2-B2だと受注総数に対してどれだけ足りていないかが分からないですね。
不足在庫がマイナスになっている品番だけをSUMIFで足せばいいですかね？
（NAO） 2015/03/31(火) 15:34

F列に不足合計を作り、=SUMIF(A:A,A2,E:E)を入れたのですが、被る品番があった場合、最初の品番だけを表示させて、その他を空白表示や、表示させない方法はありますか？
（NAO） 2015/03/31(火) 15:41

案だけですが、在庫管理しているシートの方に不足在庫数を表示するほうが、判りやすいかも知れませんね。
（???） 2015/03/31(火) 15:47

なるほど！
ありがとうございます！
（NAO） 2015/03/31(火) 16:54

コメント返信：

[ 一覧(最新更新順) ]

[[20150330153541]] 『受注データから在庫一覧をもとに、上から順に在庫』（NAO） ページの最後に飛ぶ

[[20150330153541]]　『受注データから在庫一覧をもとに、上から順に在庫』（NAO）　ページの最後に飛ぶ