エクセル関数について

質問

エクセルの部品管理表を作成しています。
エクセルのシート1に入出庫表、シート2に、実在庫数表、シート3に発注表、シート4に部品名と作成しました。

シート1のＡ1セルに部品名、Ｂ1セルに部品サイズ、Ｃ1セルに日付、Ｅ1セルに、実在個数（箱数）、Ｆ1セルに実在個数（本数・入数）、Ｇ1セルに入庫、Ｈ1セルに出庫、と入力しました。

シート2にはＡ1セルに部品名、Ｂ1セルに部品サイズ、Ｃ1セルに期首在庫（箱数）、Ｄ1セルに期首在庫（本数・入数）、Ｅ1セルに実在個数（箱数）、Ｆ1セルに実在庫数（本数・入り数）と入力。

シート3のＡ1セルに部品名、Ｂ1セルに部品サイズ、Ｃ1セルに発注日、Ｄ1セルに発注数、Ｅ1セルに受取日（入庫日）、Ｆ1セルに受取個数（入庫数）、Ｇ1セルに受取本数（入庫本数・入数）と入力。

シート4のＡ1セルに部品名、Ｂ1セルに部品サイズ、と入力。（部品名は約200種類、部品名は同じでもサイズ違いの部品がある、部品コードは存在しない。）

教えて頂きたいことは、

・在庫管理を行うにあたり、上記のようなファイルの作り方でいいのか？。（縦に表を使うのか、横など）

・付け足したほうがいい項目があるか。

・シート1（入出庫表）には部品名と部品サイズは部品コードが無いため入力規則を使用し、日付、入庫数、出庫数は手入力で行うとし、実在個数（箱数）、のセルには関数を入れ値を表示したい。入庫数と、出庫数は箱数で入力するものとし、入庫があれば、実在個数（箱数）の値が増え、出庫があれば、実在個数（箱数）の値をへらしたい。実在庫数（本数・入数）は実際に入庫してみないと、1箱に何本入っているのか曖昧な為、大体の数値で設定した。（１箱に200本など）
実在個数（箱数）に値を表示するにはどのような関数を使えばいいのか。

・シート2（実在庫表）に、部品名と部品サイズが約200行程度、入力されており、実在個数（箱数）、実在個数（本数・入数）を在庫一覧としたい。期首在庫（箱数）と期首在庫（本数・入数）は在庫を確認し、数字を入力済み。実在個数（本数・入数）は、大体の数値で設定済み。
部品名、サイズ毎に実在個数（箱数）に値を表示するにはどの様な関数を使えばよいのか。

説明不足でしたら申し訳ございません。

よろしくお願いします。

回答No.3

hallo-2007

No1です。先の式でミスがありましたので訂正
日付をＢ１セルに入れているので
=SUMPRODUCT((入出庫表!B:B=B1)*(入出庫表!A:A<=B$1)*(入出庫表!C:C))
でしたね。
別案です、難しいことを省いて
入出庫表と発注表を一つにまとめて
　　Ａ　　Ｂ　　　　Ｃ　　Ｄ　　Ｅ
日付　　品名　　　入庫　出庫　発注
1/1　　ビスＭ５ 　　50
1/1　　ビスＭ６ 　　50
1/1　　ビスＭ７ 　　50
1/2　　ビスＭ５ 50
1/2　　ビスＭ６　30
1/2　　ビスＭ７　30
1/3　　ビスＭ５ 50
1/3　　ビスＭ６ 30
1/3　　ビスＭ７ 10
・・・・と一枚のシートに入力

実在庫数表と部品名
　Ａ　　　　Ｂ　　　　Ｃ
品名　　　在庫　　発注残
ビスＭ５
ビスＭ６
ビスＭ７
・・・
在庫数は　総入荷数-総出荷数（Ｂ列は）
=SUMIF(入出庫履歴!B:B,A2,入出庫履歴!D:D)-SUMIF(入出庫履歴!B:B,A2,入出庫履歴!C:C)
発注残は　総発注数-総入荷数（Ｃ列は）
=SUMIF(入出庫履歴!B:B,A2,入出庫履歴!E:E)-SUMIF(入出庫履歴!B:B,A2,入出庫履歴!D:D)

期末には、印刷して保存しておきます。
後は、発注、入荷、出荷の入力画面の準備や
発注依頼書の自動印刷など考えた方が使いやすい様な気がしますが如何でしょうか。

回答No.2

chayamati

補足願います
１．商品と呼ばずに部品と表現しているのは、数種の部品を組み合わせて一つの製品が出来上がるのですか
２．入出庫表の入庫は発注表の受け入れ個数とは同じですか
３．入出庫表の出庫はどのようなものですか(販売or工場への出庫)
４．1箱に何本入っているのか曖昧な為、これは発注品の入庫も曖昧なのですか

金額を計算するには単価が必要です。部品名に標準単価、発注表に発注単価または仕入単価
実在庫表は在庫表と改名し実在庫と期首繰越＋入庫累計－出庫累計＝？日現在の理論在庫数
？日は実在庫を調べた日(棚卸日)と呼ぶのでしょうか

補足

説明不足で申し訳ありません。

・１．商品と呼ばずに部品と表現しているのは、数種の部品を組み合わせて一つの製品が出来上がるのですか

1に対しては、出荷時に商品に付属する部品です。全部の商品に部品を付属するわけではありません。（実際にはビス・ワッシャー、レンチ等です。）数種の部品を組み合わせて一つの製品が出来上がるわけではありません。

２．入出庫表の入庫は発注表の受け入れ個数とは同じですか

2に対しては、同じです。発注し、実際に受け入れた個数を、入出庫表の入庫に入力する考えです。

３．入出庫表の出庫はどのようなものですか(販売or工場への出庫)

３に対しては、商品を出荷する際に、部品を付属しなくてはいけない商品があります。販売と理解しています。（商品を出荷→お客様の流れです。）

４．1箱に何本入っているのか曖昧な為、これは発注品の入庫も曖昧なのですか

4に対しては、付属品の種類やサイズによって、1箱に何本入ってくるのかは、確かに曖昧です。（中にははっきりしているものもあります。）
私はこの業務を引き継いだばかりなのですが、以前の担当者はエクセル等のファイルで管理はせず、目視で在庫を確認していたようです。発注も手書きで行っていたようです。
そこで、私はエクセルで管理をしようと思いました。
が、実際に部品の在庫を確認すると、箱数は目で確認できるのですが、同じ部品でも、一箱に1000本、１箱に2000本等、箱の大きさも違うし、本数も違う、というのを確認しました。
実際にはどの部品が何箱あるのは数えられるのですが、本数の確認は出来ず、なので曖昧な設定をしました。
なので、箱数で管理をし、本数・入り数は、目安程度に使おうと考えています。

金額に関しては、私の課では把握する必要がありません。（今のところですが）発注は私の課からＡ課に依頼し、Ａ課が業者に発注する流れです。金額の管理はＡ課が行っています。私の課で部品の管理を行っています。補充したい部品をＡ課に以来するだけです。

この部品は棚卸し対象外となっております。

ですので、私の考えでは、いつ、何の部品をいくつ発注し、実際にいつ入庫したか、いくつ入庫したか、を履歴として残し、

どの種類の部品が今、何箱あるのか、（本数は目安程度で）確認できるファイルを作成したいと思っています。

入庫、出庫は箱数単位で行う考えです。

実在庫表は在庫表と改名し実在庫と期首繰越＋入庫累計－出庫累計＝？日現在の理論在庫数
？日は実在庫を調べた日(棚卸日)と呼ぶのでしょうか

上記に関しては、部品は棚卸し対象外です。ただ、今、どの種類の部品が何箱あるのかを数えたものを、期首在庫となずけただけです。

回答No.1

hallo-2007

＞実在庫数（本数・入数）は実際に入庫してみないと、1箱に何本入っているのか曖昧な為、
＞大体の数値で設定した。（１箱に200本など）
そんな曖昧で良いのですか？現状がよくわからないので、簡素化してお答えします。
一例です
部品名、在庫数シート
　　Ａ　　　　　　　　Ｂ　　　　　Ｃ
１　日付　　　　　　2013/03/10
２　部品名＋サイズ　　在庫箱数
３　ナット　Ｌ
４　ナット　Ｍ
・・・
と部品名とサイズをひとつのセルに入力して準備します。
（たぶん、この方が入力が楽では？入力規則で一度で済みますし）
１行目のＢ１セルにでも在庫数を知りたい日付を入れます。
期末の在庫が知りたければ、期末の日付、現在の在庫が知りたければ今日の日付を入れるものとします。
本数は無視しています。

入出庫表
　　Ａ　　　Ｂ　　　　　　Ｃ　　　　Ｄ　　　
１　日付　品名＋サイズ　入庫数　　出庫数　
２　1/1　ナット　Ｌ　　　20
３　1/2　ナット　Ｍ　　　20
４　1/3　ナット　Ｌ　　　　　　　　10
・・・・・
と入力します。
在庫数の考え方は、総入庫数ー総出庫数　です。
部品名、在庫数シートのB３セル以下に

=SUMPRODUCT((入出庫表!B:B=G1)*(入出庫表!A:A<=B$1)*(入出庫表!C:C))
が、その日付までのその部品名の総入庫数
=SUMPRODUCT((入出庫表!B:B=G1)*(入出庫表!A:A<=B$1)*(入出庫表!D:D))
が、その日付までのその部品名の総出庫数

=SUMPRODUCT((入出庫表!B:B=G1)*(入出庫表!A:A<=B$1)*(入出庫表!C:C))-SUMPRODUCT((入出庫表!B:B=G1)*(入出庫表!A:A<=B$1)*(入出庫表!D:D))
が在庫数です。
但し、SUMPRODUCT関数は沢山使うとエクセルの動作が遅くなります。ピボットテーブルか
VBAを使った方が日頃の動作が楽かもしれません。