在庫管理表について。

質問

今、在庫管理表を作ってるのですが、計算式で行き詰まってます。Aと言う製品を作るのに最低3個以上のパーツを使います。たとえば製品Aを作るのにa,b,cの3個のパーツを使用するとして、Sheet1に製品Aの完成品の個数、Sheet2には製品Aに使用したパーツ（a,b,c)のそれぞれの個数が入力されています。ここで、製品Aを100個作ったとしてSheet1に完成品の個数100を入力したとき
Sheet2のパーツ（a,b,c）の個数も完成品Aが増えると自動的に100個減るようにするにはどうすればいいのでしょうか？

回答No.4

taisuke555 · ベストアンサー

docodemodoorさんのシートがどの様に入力されているのか分かりませんでしたので、
私なりの作り方で作ってみました。
ちょっと分かり難い関数を使っている所もありますが、
なるべく、コピーできるような形にしてあるので、
手作業で良ければ、もっと簡単な式になります。
（パーツ数、製品数が２５０以上あるとちょっと無理です。）
在庫管理のソフトは、検索すればありそうですけど・・・

１．パーツリスト（１行目：見だし、２行目以降：データ）
　　A列：パーツ名・・・入力列
　　B列：期首在庫・・・入力列
　　C列：入庫　　・・・=SUMIF(仕入れ!B:B,A2,仕入れ!C:C)
　　D列：出庫　　・・・=SUM(OFFSET(完成品!$D$2,0,ROW(A2)-2,65535,1))
　　E列：現在庫　・・・=B2+C2-D2
　　F列～：製品A　製品B　製品C・・・各製品に使われるパーツの数

　　★C.D.E列は２行目に式を入力し、３行目以降は２行目をコピー

２．完成品リスト（１行目：見だし、２行目以降：データ）
　　★ツール→オプション→「表示」のゼロ値のチェックをはずしてください。
　　A列：日付　　　・・・入力列
　　B列：製品名　　・・・入力列（入力規則でリストにしてもよい）
　　C列：個数　　　・・・入力列
　　D列～：パーツ名・・・見だし行：=INDEX(パーツリスト!$A:$A,COLUMN(B1))
　　　　　　　　　　　　データ行：=IF($B2="","",HLOOKUP($B2,パーツリスト!$F:$H,COLUMN(B2),0)*$C2)

　　★D列以降の列はパーツ分D列をコピー
　　★３行目以降は２行目をコピー

３．仕入れ（１行目：見だし、２行目以降：データ）
　　A列；日付　　・・・入力列
　　B列：パーツ名・・・入力列（入力規則でリストにしてもよい）
　　C列；数量　　・・・入力列

●以下は入力例（ずれていると思います）

１．パーツリスト
パーツ名　期首在庫　入庫　出庫　現在庫　製品A　製品B　製品C
a　　　　　　　250　　　　100　　82　　268　　　2　　　　　5

b　　　　　　　300　　　　200　　44　　456　　　4
c　　　　　　　150　　　　　0　　21　　129　　　1　　　　　　　　　　　2
d　　　　　　　80　　　　　　0　　40　　　40　　　　　　　　　　　　　　　8
e　　　　　　　53　　　　　　0　　36　　　17　　　　　　　　　　3

２．完成品リスト
日付　　　製品名　個数　　a　　b　　c　　d　　e
1月10日　　製品B　　3　　15　　　　　　　　　　9
1月15日　　製品A　　1　　2　　4　　1
1月20日　　製品A　　10　20　40　10
2月1日　　製品C　　　5　　　　　　　10　40
2月5日　　製品B　　　4　　20　　　　　　　　　12
3月1日　　製品B　　　5　　25　　　　　　　　　15

３．仕入れ
日付　　　パーツ名 個数
1月25日　　a　　　　100
2月10日　　b　　　　200

こんな感じので良ければ、わからない所など補足してください。
長々と失礼しました。

回答No.3

imogasi

３つの関係表が必要でしょう。
（１）部品構成表
設欄
完成品名　部品１　部品２　部品３　・・・
　　　　　a b c ・・・
Ａ　　　　10 20 5
Ｂ 5 10 15
・・・
（２）完成製品在庫表
　　　完成品名　　　　　　　　　　　　完成品名
日付　完成数量　出庫数量　在庫数量　日付　完成数量　出庫数量　在庫数量
　　　　　Ａ　　　　　　　　　　　　　　　　Ｂ
５／１　　　　　　　　　５０　　　　５／１０

５/１２　１００　　　　　１５０　　　
・・・
（３）部品在庫表
　　　　　部品名　　　　　　　　　　　　　部品名　　
日付　完成数量　出庫数量　在庫数量　日付　完成数量　出庫数量　在庫数量　
多分本件エクセルで考えているのでしょうが、列数２５６列の制約を考えると、上記などは不安有りです。
やはり一覧性は諦めても、アクセスなどの、関係のデータが必要な時は見れる方式にすべきではないでしょうか。
計算式は
在庫数量＝前行在庫＋入庫－出庫＝当行在庫
部品在庫変動において
部品構成表＊完成品（入庫＝製造）個数＝（部品在庫）出庫数
Ａとaから使用部品数の割り出しは
例えば部品構成表が
a b c
Ａ 10 20 5
Ｂ 5 10 15
Ｃ 20 15 10
の場合
Ａのb部品は
=INDEX(B2:D3,MATCH("Ａ",A2:A4,0),MATCH("b",B1:D1,0))
で「20」が採れます。

回答No.2

bhoji

使用するソフトがエクセルならば、まず項目を入れます。
Ａ１：年月日、Ａ２：仕入(入庫）数、Ａ３：倉出（出庫）数、Ａ４：在庫数　とします。
Ｄ２に現在の在庫数を入れます。
Ｄ３に数式：＝Ｄ２＋Ｂ３－Ｃ３　を入れます。
Ｄ３の数式はドラッグして下に向けてコピーしておきます。
またＡ列の表示を日付に設定しておきます。

次にシート全体をコピーして、次のシートに複写し、これを部品の種類分だけ
繰り返します。
シート２のＣ３へ数式で=Sheet1!B3*1を入れます。
＊１としたのは、ネジなど複数必要な場合のためです。
Ｃ３の数式をドラッグして下にコピーしておく。

続いてシート２の全体をコピーして次のシートへの複写を繰り返します。
これで基本は出来たと思います。

元のシート１に戻って、Ａ２：製作(製造）数　に変更します。
Ｂ３に１００を入れて確認してみてください。

回答No.1

tamatokuro

Sheet1のＢ２に１００
Sheet2の１の行に(A1)パーツ名、(B1)使用数、(C1)出庫数、
Ａ列に(A2)ａ、(A3)ｂ、(A4)ｃ
Ｂ列に個々の使用数を入力
(C2)に＝Sheet1！B2*B2と入力すると１台に使われた個数が表示されるので
在庫数の入ってるセルから引けばいい
(C3)、(C4)はSheet1！B2*B3とB4になります