エクセルの関数または、VBAでできますか?

質問

次のような表があったとします。

商品　　1日目　　2日目　　3日目 　　必要部品　　在庫　　過不足
A　　　　　20　　　　　　　　　 　 　　みかん　　　50
B　　　　　10　　　10　　　　　　 　　みかん 　　50
C　　　　　　　　　20　　 　20　　 　　みかん　 　50　　　不足
A　　　　　30　　　　　　　　　　 　 　　もも　 　100
B　　　　　15　　　15　　　　　　　　　　もも　 　100
C　　　　　　　　　30　　 　30　　　 　　もも　 　100
A　　　　 100　　　　　　　　　 　 　　りんご　　 200
B　　　　　50　　　50　　　　　　　　　りんご　 　200
C　　　　　　　　 100　　　100　　 　　りんご　 　200　　　不足

ここで、みかんの在庫は50個となり、
商品Aの必要数は、20個で足りることになります。
商品Bの必要数は、20個で1日目の必要数とあわせても足ります。
商品Cになると必要数はさらに40個増え、合計80個となり、
在庫の50個では足りなくなります。
ももの場合は、すべて足りることになり、
りんごの場合は、商品Bで在庫が無くなり、商品Cの分は、足りないことになります。

この表の右端に、これらの結果を計算式で求めることはできないでしょうか？
判りにくいかもしれませんが、よろしくお願いします。

回答No.6

mayomayo20

＃５の修正
F列ではなくG列に数値の書式設定、
F2～F4をオートフィルではなく、G2～G4をオートフィルでした（;_;）
これ以上は私では力不足でわからないので、他の回答者様が
答えてくれることを願います。

お礼

商品が少なければ、有効なのですが大量にありますので、
参考にさせていただきます。回答ありがとうございました。

回答No.5

mayomayo20

思いつかなかったので、ちょっと単純に式をいれてみました。
　　A　　　　　B　　　　　C　　　　　D　　　　　　E　　　　　　F　　　　　G
1　商品　　1日目　　2日目　　3日目 　　必要部品　　在庫　　過不足
2 　Ａ　　　　２０　　　　　　　　　　　 　 　　　　みかん　　　５０　　　　=F2-SUM(B2:D2) （結果３０）
3　 Ｂ　　　　１０　　　　１０　　　　　　　 　　　　みかん 　　５０　　　　=G2-SUM(B3:D3)　（結果１０）
4　 Ｃ　　　　　　　　　　２０　　 　　２０　　　 　 みかん　 　５０　　　　=G3-SUM(B4:D4)　（結果-３０）

上記のように数式を入れて、わかりやすいようにＦ列（過不足）には書式設定で数値がマイナスになったとき数字が赤くなるように設定。
それでＦ２～Ｆ４を選択して、したまでオートフィルで引っ張る。
今回のオートフィルはあくまでも商品が３種類の場合のみ有効ですが；

お礼

商品が少なければ、有効なのですが大量にありますので、
参考にさせていただきます。回答ありがとうございました。

回答No.4

Wendy02

こんにちは。

すみません、私は、勘違いしました。

私には経験ないし、うかつに教えるような内容とは思いませんので、分からないといったほうが無難かもしれません。正直いって、私が良く知っているものとは、まったく分野が違います。

私の知り合いで、長年部品管理をやってきていますので、どうやってするかは、多少、手ほどきを受けたのですが、感覚的に、ちょっとつかめないです。

それは、し係品としての在庫はあっても、部品在庫は、0になってから補充を行うような考え方だと思います。卸や小売は、まったく違います。棚卸の考え方も違います。

>問題は、在庫の数量は商品別ではなく共通在庫の為、需要を商品別に足してから、在庫と比較しなければなりません。

それは、データベースのような方式で行わなくてはならないはずで、おっしゃるように、商品に対する固有部品と、共有部品とがあるはずですから、部品自体の在庫の一括の集計をする表が必要です。同じ表の中では、不可能に近いです。

共有部品があれば、個々に使用したものを、その一括集計の場所から引き算していくようにしないと、今回のような横並びの表ではうまくいくとは思いません。

例えば、

[商品需要部品表]
商品　　1日目　　2日目　　3日目　　必要部品　
A　　　　　20　　　　　　　　　　　　みかん　
B　　　　　10　　　10　　　　　　　　みかん　
・　　　　　・　　　・　　　　　　　　　・
・　　　　　・　　　・　　　　　　　　　・

[部品在庫管理表シート]
部品名　　　　必要量　　トータル在庫　　過不足
みかん　　　　　150　　　　100　　　　　　不足

お礼

いろいろ考えていただきありがとうございます。
また、機会があればよろしくおねがします。

回答No.3

Wendy02

#1 です。

用語がヘンでした。

在庫適量　→適正在庫量
と読み替えてください。

>必要部品　　在庫

昨日、気がつかなかったのですが、私の書いているのは、あくまでも、卸業や一般販売店に対してで、それ以外の製造業には該当しません。

また、実際の在庫とは別に、適正在庫量というのを割り出さないといけません。その適正在庫量を使って管理していきます。製造業では、私は不勉強ですが、トヨタ方式などが有名です。

失礼な言い方に取れたら申し訳ないけれども、こういう公の掲示板でお聞きになっているようですから、単に数式だけをお聞きになっているかもしれませんね。ただ、今まで、会社の中で、在庫管理だけが、コンピュータの導入で一番最後になる部門でした。

しかし、最近の方たちで、コンピュータに頼りすぎというか、実際の商品を見ないで、在庫管理する方も増えているようです。セブンイレブンのようなPOSで集中管理できれば、それに越したことはないのですが、バルクや切り売り販売のような場合は、それが導入できないこともあります。

お礼

発注から納入までの期間や、適正在庫等の用語が使われており、すごく奥深く考えていただいたようで感謝いたします。

ここでの質問は、もっと単純で現在庫（商品別ではなく共通数が各行に入っている⇒みかんの在庫は、150個ではなく50個しかない）で、商品のどこまで作れるかを知りたいです。

回答者＃２さんの補足にも書きましたが、問題は不足数を算出する際に、必要部品を累計しなければならず、ここをどのように算出すればよいかわかりません。

わかりづらい質問で恐縮ですが、アドバイスをおねがします。（回答者＃２さんの指摘で、商品Cのももは、「ブランク」では無く「不足」になります）

回答No.2

mayomayo20

ものすごく単純に、不足数を出したいのでしたら在庫から必要数を引いていけばいいし、
足りなくなった場合"不足"という字を出したいならＩＦ関数を使えばいいと思ったのですが、
不明な点があるので補足をお願いいたします。
商品　　1日目　　2日目　　3日目 　　必要部品　　在庫　　過不足
Ａ　　　　２０　　　　　　　　　　　 　 　　　　みかん　　　５０
Ｂ　　　　１０　　　　１０　　　　　　　 　　　　みかん 　　５０
Ｃ　　　　　　　　　　２０　　 　　２０　　　 　 みかん　 　５０　　　不足

■補足要求１）質問者様が求めている「右端の計算式」が何を指しているのかわからなかったので、具体的に「ここにこの場合はこういう結果を出したい」と教えていただけますでしょか。
計算式で、足りなくなったら"不足"という文字を出したいのでしょうか？
それともこの"不足"の文字のところに実際に不足している数を出したいのでしょうか？
■補足要求２）みかんの場合、２日目に不足していると考えるのではなく、Ｃを作る時に不足していると考えてよろしいのでしょうか？
■補足要求３）この表の場合、ももの在庫は１００個、Ａ～Ｃで必要なのは１２０個なので不足になるのではないでしょうか？

補足

回答ありがとうございます。補足いたします。

> ものすごく単純に、不足数を出したいのでしたら在庫から必要数を引いていけばいいし、
> 足りなくなった場合"不足"という字を出したいならＩＦ関数を使えばいいと思ったのですが、
商品単独と在庫の比較だと簡単なのですが、問題は、在庫の数量は商品別ではなく共通在庫の為、需要を商品別に足してから、在庫と比較しなければなりません。

> ■補足要求１）質問者様が求めている「右端の計算式」が何を指しているのかわからなかったので、具体的に「ここにこの場合はこういう結果を出したい」と教えていただけますでしょか。
> 計算式で、足りなくなったら"不足"という文字を出したいのでしょうか？
> それともこの"不足"の文字のところに実際に不足している数を出したいのでしょうか？
みかんの在庫は、50個あり、商品Aを20個及び商品Bを20個作るのにあわせて40個となり、在庫でまかなえます。しかし、商品Cをつくる際には在庫でまかなえなくなり、不足なります。　　⇒要するに、在庫の数量でどこまでの商品が作れるかの目安としたいです。表示は、不足でも不足数でもかまいません。

> ■補足要求２）みかんの場合、２日目に不足していると考えるのではなく、Ｃを作る時に不足していると考えてよろしいのでしょうか？
そのとおりです。Cを作る際に不足と表示したいです。

> ■補足要求３）この表の場合、ももの在庫は１００個、Ａ～Ｃで必要なのは１２０個なので不足になるのではないでしょうか？
すみません、そのとおりです。Cを作る際に不足します。

以上よろしくお願いします。

回答No.1

Wendy02

こんばんは。

それは、三段階に分かれないでしょうか？
１段階は、○、２段階は、△、３段階は、×（不足）

G2 ～

=IF(SUM(B2:D2)>=(F2-ROUNDDOWN(F2/COLUMNS(B2:D2),0)),"不足",IF((ROUNDDOWN(SUM(B2:D2)/COUNT(B2:D2),0)*COLUMNS(B2:D2)/2)>SUM(B2:D2),"△",""))

これは、ご自身で、発注と納期を考えて数式を変えてください。
本当は、それぞれの商品の発注の納入期間のタイムラグがあるので、こんなに簡単ではありません。それぞれの商品係数（以下参照）を考えなくてはならないと思います。納期に対して、発注サイクルがあるはずです。

解説：
不足編

IF(SUM(B2:D2)>=(F2-ROUNDDOWN(F2/COLUMNS(B2:D2),0)),"不足"

ROUNDDOWN(F2/COLUMNS(B2:D2)
１日辺りの在庫適量（つまり、気がついて、１日で商品が入ってくる場合）

在庫とあるけれども、在庫適量のことだと思います。なければ、在庫適量を置いたほうがよいです。それは、発注単位と在庫適量数と在庫消費量を考えて作ります。

COLUMNS(B2:D2)　日にちのこと。

つまり、納期が4日も掛かるのだったら、4を掛けます
F2-ROUNDDOWN(F2/COLUMNS(B2:D2),0)*4

必ず、最後は、納期分の在庫は保管するという考えです。

注意：△編
IF(ROUNDDOWN(SUM(B2:D2)/COUNT(B2:D2),0)*COLUMNS(B2:D2)/2)>SUM(B2:D2),"△"

SUM(B2:D2)/COUNT(B2:D2) 　日数に対する平均

*COLUMNS(B2:D2)　その日数

/2　これは「係数」で、今回の場合は、日数が少ないので、残り一日分の余裕を持つ　　(3日-1日) ここが、ミソですね。実際の日数から、ひとひねりしないと、難しいです。つまり、私の考え方は、納期の掛かるものは、例えば、在庫が半分になった段階で、発注しなければ間に合わなくなります。納期の掛からないものは、在庫は少な目にし、ギリギリまで、待っても良いわけです。早い話、今日、発注して、今日入荷するなら、その日の朝の段階で決めても良いわけですね。在庫は置いておけば置くほど、損していきます。

SUM(B2:D2)　合計

ということで、現実は、どうなるかというと、

A　　　　　30　　　　　　　　　　 　 　　もも　 　100　△

になります。ところが、二日目に、20 個なら、△は消えます。

A　　　　 100　　　　　　　　　 　 　　りんご　　 200　△

ここも同様です。

私の考え方は、分かりますでしょうか？