Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7全般 : Excel 2016)
不足分を求める方法
投稿日時: 19/08/29 13:25:40
投稿者: おばちゃん

不足分を求める計算方法を教えてください。

在庫数	8月	9月	10月	11月	12月	1月
A	169 	290 	90 	-110 	-410 	-290 
B	12 	117 	-218 	-50 	-1,318 	-818 
C	0 	0 	-500 	-700 	-1,000 	-1,150 
D	574 	-18 	32 	-468 	-1,258 	-1,158 
						
不足分	8月	9月	10月	11月	12月	1月
A	0 	0 	0 	-110 	-300 	0 
B	0 	0 	-218 	0 	-1,050 	0 
C	0 	0 	-500 	-200 	0 	0 
D	0 	-18 	0 	-450 	-772 	0

 
 
不足分のDの11月が-468と表示されないといけないはずが-450と表示されてしまいます。
8月の計算式は=IF(B2>0,0,B2)
9月以降の計算式は=IF(C2-SUMIF($B2:B2,"<0")>0,0,C2-SUMIF($B2:B2,"<0"))
 
在庫数がマイナス、プラス、マイナスの時の計算式はどう入れればよいのか教えてください。[/code]

回答
投稿日時: 19/08/29 14:35:54
投稿者: WinArrow
投稿者のウェブサイトに移動

>不足分のDの11月が-468と表示されないといけないはずが-450と表示されてしまいます。
B、Cは、問題無いのですか?
 
そもそも、何を目的とした表なんでしょうか?
 
不足とは、在庫数が、「例えば出荷数に対してたりない」という場合に
起こる現象ですよね?
 
下の表の計算式が理解できませんが・・・・

回答
投稿日時: 19/08/29 15:42:28
投稿者: WinArrow
投稿者のウェブサイトに移動

結果は、貴方が意図した答えにならないのは、
 計算式が、貴方が考えた計算式が違うからです。
Excelは、人間が考えていることまで理解できませんから
与えられた計算式通りに計算します。
 
計算式を展開すると
=-468-(-18)
になります。したがって、答えは、-450 です。
 
前レスでも書きましたが、数式の意味が理解できないので
日本語で説明できますか?

投稿日時: 19/08/29 16:07:21
投稿者: おばちゃん

WinArrow様
説明が足りなくてすみません。
 
不足表には月毎のリスクを表示させます
 
Aの場合は、
11月に在庫数-110と表示し、
12月の在庫数-410分の-110はすでに11月に表示済みなので12月には-410-110=-300のリスクを表示します。
今の数式はA〜Cまでは正しい値が表示されますが、
 
Dのようなデータの場合は、
9月マイナス在庫があり、10月で消化され、11月にマイナスがあった場合の表示の仕方が、
今の数式のままでは正しく表示されません。
 
不足表に表示したい値は、9月に-18、10月には0(リセット)、11月には0-468=-468と表示されてほしいのですが、
今の数式をどのように変更するべきか教えてください。

回答
投稿日時: 19/08/29 17:33:28
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

Aの場合は、
11月に在庫数-110と表示し、
12月の在庫数-410分の-110はすでに11月に表示済みなので12月には-410-110=-300のリスクを表示します。
 今の数式はA〜Cまでは正しい値が表示されますが、

 
まず、リセットする条件を整理してみましょう
当月の在庫数>=0 or 当月の在庫数>前月の在庫数

で、よいですか?
 
 

回答
投稿日時: 19/08/29 17:38:56
投稿者: んなっと

ま、どうせ違うでしょうが...
 
     A  B  C   D   E    F    G
 1 在庫数 8月 9月 10月 11月  12月   1月
 2    A 169 290  90 -110  -410  -290
 3    B  12 117 -218  -50 -1,318  -818
 4    C  0  0 -500 -700 -1,000 -1,150
 5    D 574 -18  32 -468 -1,258 -1,158
 6                       
 7 不足分 8月 9月 10月 11月  12月   1月
 8    A  0  0   0 -110  -300    0
 9    B  0  0 -218   0  -1050    0
10    C  0  0 -500 -200    0    0
11    D  0 -18   0 -468  -790    0
 
B8
=MIN(0,B2-IFERROR(SUM(INDEX(2:2,TEXT(AGGREGATE(14,6,COLUMN(A2:$B2)/(A2:$B2>=0),1)+1,"[<"&COLUMN()&"]0;;")):A2),))
右方向・→下方向・↓

回答
投稿日時: 19/08/29 19:14:52
投稿者: んなっと

これも疑ったほうがいいですね。

引用:
今の数式はA〜Cまでは正しい値が表示されます

 
-468にするだけならほかにもいくつかあります。
 
     A  B  C   D   E    F    G
 1 在庫数 8月 9月 10月 11月  12月   1月
 2    A 169 290  90 -110  -410  -290
 3    B  12 117 -218  -50 -1,318  -818
 4    C  0  0 -500 -700 -1,000 -1,150
 5    D 574 -18  32 -468 -1,258 -1,158
 6                       
 7 不足分 8月 9月 10月 11月  12月   1月
 8    A  0  0   0 -110  -300    0
 9    B  0  0 -218   0  -1318    0
10    C  0  0 -500 -200  -300  -150
11    D  0 -18   0 -468  -790    0
 
B8
=MIN(0,B2-IF(A8<0,A2,0))
右方向・→下方向・↓

投稿日時: 19/08/30 12:04:28
投稿者: おばちゃん

WinArrow様、んなっと様
考えていただき、ありがとうございます。
 
んなっと様の
=MIN(0,B2-IFERROR(SUM(INDEX(2:2,TEXT(AGGREGATE(14,6,COLUMN(A2:$B2)/(A2:$B2>=0),1)+1,"[<"&COLUMN()&"]0;;")):A2),))
でほしい値になりました。
 
ただ、理解するのに時間がかかりそうですが、解析頑張ります。
 
月曜日に解決済みにします。
 
 
 
 
 

回答
投稿日時: 19/08/30 12:48:09
投稿者: んなっと

数式のことは置いといて...
リスクなのか販売機会損失なのか知りませんが、赤字部分がおかしい。
 
     A  B  C   D   E    F    G
 1 在庫数 8月 9月 10月 11月  12月   1月
 2    A 169 290  90 -110  -410  -290
 3    B  12 117 -218  -50 -1,318  -818
 4    C  0  0 -500 -700 -1,000 -1,150
 5    D 574 -18  32 -468 -1,258 -1,158
 6                       
 7 不足分 8月 9月 10月 11月  12月   1月
 8    A  0  0   0 -110  -300    0
 9    B  0  0 -218   0  -1050    0
10    C  0  0 -500 -200    0    0
11    D  0 -18   0 -468  -790    0
 
11月でリセットされてリスクが0になったのだから、
12月のリスクは-1050ではなくて-1318(または10月の分は除外して-1100)になるべきではないですか?
この指摘に対して明確な根拠をもって反論できますか?
「うちではこういう決まりになっている」というのは明確な根拠ではありません。
 
質問文を読む限り、上の表が正しいのかどうかあなた自身もわかっていないような気がします。

投稿日時: 19/08/30 14:58:02
投稿者: おばちゃん

最初に貼り付けた不足分が間違っていました。
A1から貼り付け

在庫数	8月	9月	10月	11月	12月	1月
A	169 	290 	90 	-110 	-410 	-290 
B	12 	117 	-218 	-50 	-1,318 	-818 
C	0 	0 	-500 	-700 	-1,000 	-1,150 
D	574 	-18 	32 	-468 	-1,258 	-1,158 
E	-468 	-668 	-918 	-600 	-750 	-950 
						
不足分	8月	9月	10月	11月	12月	1月
A	0 	0 	0 	-110 	-300 	0 
B	0 	0 	-218 	0 	-1,100 	0 
C	0 	0 	-500 	-200 	-300 	-150 
D	0 	-18 	0 	-468 	-790 	0 
E	-468 	-200 	-250 	0 	0 	-32

   
 Bの9月は-218、
10月は9月に-218を表示してあるので0になり
11月は-1318の在庫に対して、-1318-218(9月の表自分)=-1100になります。
  
Eの例を追加したのですが、んなっと様に頂いた数式ではほしい値にならなかったので
 もう少し考えてみます。

回答
投稿日時: 19/08/30 15:37:42
投稿者: んなっと

引用:
今の数式はA〜Cまでは正しい値が表示されます
    ↓
最初に貼り付けた不足分が間違っていました。

何を信じて回答すればいいのですか?
 
     A   B   C   D   E    F    G
 1 在庫数  8月  9月 10月 11月  12月   1月
 2    A  169  290  90 -110  -410  -290
 3    B  12  117 -218  -50 -1,318  -818
 4    C   0   0 -500 -700 -1,000 -1,150
 5    D  574  -18  32 -468 -1,258 -1,158
 6    E -468 -668 -918 -600  -750  -950
 7 不足分  8月  9月 10月 11月  12月   1月
 8    A   0   0   0 -110  -300    0
 9    B   0   0 -218   0  -1100    0
10    C   0   0 -500 -200  -300  -150
11    D   0  -18   0 -468  -790    0
12    E -468 -200 -250   0    0   -32
 
B8
=MIN(0,B2-IFERROR(SUM(INDEX(8:8,TEXT(AGGREGATE(14,6,COLUMN($A2:A2)/($A2:A2>=0),1)+1,"[<"&COLUMN()&"]0;;")):A8),))
右方向・→下方向・↓

回答
投稿日時: 19/08/30 17:22:49
投稿者: 半平太

>不足分のDの11月が-468と表示されないといけないはずが-450と表示されてしまいます。
 
質問の発端となった、その見解がそもそも間違っていると思いますね。
 
他の在庫は、不足分の計上数値を考慮に入れて、その後の過不足を考えるのに、
その後プラスに変わった時だけ、過去の不足計上数値がリセットされるなんておかしいです。
 
上長に再確認して貰えませんか?
 
多分、これでいいハズです。
    ↓ 
B8セル =IF(B2>=0,0,MIN(0,B2-MIN($A2:A2)*(MIN($A2:A2)<0)))

回答
投稿日時: 19/08/30 18:51:10
投稿者: んなっと

「-450ではなくて-468と表示されないといけない」
に無理やり屁理屈をこじつけるなら、以下のようなものがあるかもしれません。
 
     A   B   C   D   E    F    G
 1 在庫数  8月  9月 10月 11月  12月   1月
 2    A  169  290  90 -110  -410  -290
 3    B  12  117 -218  -50 -1,318  -818
 4    C   0   0 -500 -700 -1,000 -1,150
 5    D  574  -18  32 -468 -1,258 -1,158
 6    E -468 -668 -918 -600  -750  -950
 7 不足分  8月  9月 10月 11月  12月   1月
 8    A   0   0   0 -110  -300   -0
 9    B   0   0 -218  -0  -1100   -0
10    C   0   0 -500 -200  -300  -150
11    D   0  -18   0 -468  -790   -0
12    E -468 -200 -250  -0   -0   -32
 
不足分のゼロには2種類ある。
 
1.在庫がプラスで業務が健全であることを意味する「安全期の0」
 ...リセット。
 
2.在庫はマイナスだが、
 先月などに比べてマイナスの後の数字(絶対値)が小さくなった「危険期の-0
 ...リセットせず、危険期のひとかたまりに入れて扱う。
   仕入れ数が売り上げ数を上回っている時期なので、予測自体は間違いではなかった。
   しかし在庫はマイナスであることには変わりないので、
   数か月単位で考える危険期は継続しているものとみなす。
   危険期の中で不足分を月ごとに分散させ、
   来年の仕入れ数決定(もしくはメーカーとの交渉)の参考にする。

回答
投稿日時: 19/08/30 18:59:00
投稿者: んなっと

流通業と仮定して書いていますが、用語は適当です。

投稿日時: 19/09/02 14:13:48
投稿者: おばちゃん

この表は
月ごとの仕入数それぞれ決まっており、
計画仕入数が各月ごとに入って来なかった場合
の売上の影響額を求めるものです。(後で単価を計算させます。)
 
なので

引用:
他の在庫は、不足分の計上数値を考慮に入れて、その後の過不足を考えるのに、
その後プラスに変わった時だけ、過去の不足計上数値がリセットされるなんておかしいです。

の指摘の意味が分かりません。
 
在庫数	8月	9月	10月	11月	12月	1月
D	574 	-18 	32 	-468 	-1,258 	-1,158 
不足分	8月	9月	10月	11月	12月	1月
D	0 	-18 	0 	-468 	-790 	0 

 
	7月	8月	9月	10月	11月	12月	1月
計画売上数		450 	600 	450 	500 	790 	400 
計画仕入数		1,000 	500 	500 	500 	500 	500 
仕入数		991 	8 	500 	0 	0 	500 
仕入差分		-9 	-492 	0 	-500 	-500 	0 
在庫数	33 	574 	-18 	32 	-468 	-1,258 	-1,158 
計画在庫数	33 	583 	483 	533 	533 	243 	343 
影響数		0 	-18 	0 	-468 	-790 	0 

 
Dの場合は
9月では600の計画売上数があったが、計画仕入数が500中8個しか来なかった場合、18個が売れないことになる。(-18)
10月は450の計画売上数があったが、計画仕入数が500中500個来たため、10月の売れる計画数通り売れることになる。(0)
11月は500の計画売上数があったが、計画仕入数が500中なにも来なかったため、468個が売れないことになる。(-468)
12月は790の計画売上数があったが、計画仕入数が500中なにも来なかったため、790個が売れないことになる。(-790)
1月は400の計画売上数があったが、計画仕入数が500中500個来たため、1月の売れる計画数通り売れることになる(0)。
 
つまり、ちゃんと計画通りに仕入されていれば、売れるはずたっだものが。
仕入れされなかった場合に、どのくらい売上から影響があるのか求められませんか?

回答
投稿日時: 19/09/02 16:18:13
投稿者: んなっと

在庫数は
先月の在庫数+今月の仕入れ数-今月の売り上げ数
で計算していますよね。
C10
=B10+C9-C8
右方向・→
 
     A  B  C  D   E   F   G   H
 7    D 7月 8月 9月 10月 11月  12月  1月
 8 売上数    450 600  450  500  790  400
 9 仕入数    991  8  500   0   0  500
10 在庫数  33 574 -18  32 -468 -1258 -1158
11 不足分     0 -18   0 -468  -790   0
 
不足分[売上機会損失の個数?]とやらもほとんど同じではないのですか?
MIN(0,MAX(0, を左に追加するだけで求められるような気がします。
こんな感じ。
 
C11
=MIN(0,MAX(0,B10)+C9-C8)
右方向・→
 
在庫数だけから求めようとするから複雑になる。
先月の在庫数,今月の仕入れ数,今月の売り上げ数の3つから改めて計算すれば簡単です。

投稿日時: 19/09/10 10:22:33
投稿者: おばちゃん

んなっと様
 
連絡遅くなって申し訳ありません。
検証してみたところ
以下のような場合に値がおかしくなってしまいました。
 

	A	B	C	D	E	F	G	H
1		7月	8月	9月	10月	11月	12月	1月
2	売上数		0	0	500	200	300	150
3	仕入数		0	0	0	300	0	0
4	在庫数	0	0	0	-500	-400	-700	-850
5	不足分(正)	0	0	-500	0	-200	-150
6	不足分(誤)	0	0	-500	0	-300	-150

 
11月は200の計画売上数があったが、計画仕入数が300個来たため、11月の売れる計画数通り売れることになる。(0)
問題の12月ですが、
12月は300の計画売上数があったが、11月の仕入数が残100あったため、200が売れないことになる(-200)
はずですが、
=MIN(0,MAX(0,B10)+C9-C8)で表示させると-300になってしまいました。
 
今のところ、んなっとさんの数式でおかしいところは目視で修正していますので、いい方法がありましたら助言よろしくお願いします。

回答
投稿日時: 19/09/10 19:44:37
投稿者: んなっと

どうも「その月に新しく発生した販売機会損失個数」ではないようですね。
では改めて...
 
    A  B  C  D   E   F   G   H
4     7月 8月 9月 10月 11月 12月  1月
5 売上数     0  0  500  200  100  400
6 仕入数     0  0   0  300  150   0
7 在庫数  0  0  0 -500 -400 -350 -750
8 不足分     0  0 -500   0   0 -250
 
C8
=MIN(0,C7-IFERROR(SUM(INDEX($A8:B8,AGGREGATE(14,6,COLUMN($A7:B7)/($A7:B7>=0),1)+1):B8),))
右方向・→
 
これで試してみてください。

投稿日時: 19/09/13 15:45:01
投稿者: おばちゃん

んなっと様
お返事が遅くなり申し訳ありません。
在庫と不足分だけで求まる数式になってよかったです。
正しい値が出たことを確認できましたが、
横バージョンにした場合の数式の変更方法がわかりません。
修正した数式が間違った値になってしまいました。
すみませんが、横バージョンにした時の数式を教えていただけないでしょうか?

B	C	D	E	F	G	H	I	J
6			在庫					
7			9月	10月	11月	12月	1月	2月
8	A		268	68	-132	-132	8	128
9	B		544	404	264	34	-106	-236
10	C		8	248	188	98	48	8
11	D		63	93	-57	-57	43	193
12	E		-1	-8	-608	-508	-508	-308

間違った結果
K	L	M	N	O	P
不足分					
9月	10月	11月	12月	1月	2月
0	0	-132	-132	0	0
0	0	0	0	-106	-236
0	0	0	0	0	0
0	0	-57	-57	0	0
-1	-8	-608	-508	-508	-308

K8
=MIN(0,E8)
L8
=MIN(0,F8-IFERROR(SUM(INDEX($K8:K8,AGGREGATE(14,6,COLUMN($E8:E8)/($E8:E8>=0),1)+1):E8),))
 
正しい結果
K	L	M	N	O	P
不足分					
9月	10月	11月	12月	1月	2月
0	0	-132	0	0	0
0	0	0	0	-106	-130
0	0	0	0	0	0
0	0	-57	0	0	0
-1	-7	-600	0	0	0

 

回答
投稿日時: 19/09/13 16:26:55
投稿者: んなっと

別のデータが右に来るときは、1列(下の場合K列)開けないといけません。
それから、INDEX( の直後だけは、INDEX($A...のように常にA列を指定してください。
 
    D   E   F   G   H   I   J K   L   M   N   O
 1 在庫  9月 10月 11月 12月  1月  2月   9月 10月 11月 12月
 2   A  169  290  90 -110  -410  -290   169  290  90 -110
 3   B  12  117 -218  -50 -1318  -818    12  117 -218  -50
 4   C   0   0 -500 -700 -1000 -1150    0   0 -500 -700
 5   D  574  -18  32 -468 -1258 -1158   574  -18  32 -468
 6   E -468 -668 -918 -600  -750  -950   -468 -668 -918 -600
 7 不足  9月 10月 11月 12月  1月  2月   9月 10月 11月 12月
 8   A   0   0   0 -110  -300   0    0   0   0 -110
 9   B   0   0 -218   0 -1100   0    0   0 -218   0
10   C   0   0 -500 -200  -300  -150    0   0 -500 -200
11   D   0  -18   0 -468  -790   0    0  -18   0 -468
12   E -468 -200 -250   0   0  -32   -468 -200 -250   0
 
E8
=MIN(0,E2-IFERROR(SUM(INDEX($A8:D8,AGGREGATE(14,6,COLUMN($D2:D2)/($D2:D2>=0),1)+1):D8),))
右方向・→下方向・↓
L8
=MIN(0,L2-IFERROR(SUM(INDEX($A8:K8,AGGREGATE(14,6,COLUMN($K2:K2)/($K2:K2>=0),1)+1):K8),))
右方向・→下方向・↓
 
これもほとんど試していません。手抜き回答です。

投稿日時: 19/09/13 17:01:03
投稿者: おばちゃん

んなっと様
 
表が長くて、んなっと様のように掲示板に貼りつかないので見づらくなってしまいました。
すみません。
在庫の表の横には別のデータではなく、不足の表になります。
 
在庫の表と不足の表は同じ行にするにはどうしたらよいでしょうか?

	K	L	M	N	O	P
6	不足分					
7	9月	10月	11月	12月	1月	2月
8	0	0	-132	0	0	0
9	0	0	0	0	-106	-130
10	0	0	0	0	0	0
11	0	0	-57	0	0	0
12	-1	-7	-600	0	0	0

回答
投稿日時: 19/09/13 17:20:22
投稿者: んなっと

   E   F   G   H   I   J  K   L   M   N   O   P
 7 9月 10月 11月 12月  1月  2月 9月 10月 11月 12月  1月  2月
 8 268  68 -132 -132   8  128  0   0 -132   0   0   0
 9 544  404  264  34 -106 -236  0   0   0   0 -106 -130
10  8  248  188  98  48   8  0   0   0   0   0   0
11  63  93  -57  -57  43  193  0   0  -57   0   0   0
12  -1  -8 -608 -508 -508 -308  -1  -7 -600   0   0   0
 
K8
=MIN(0,E8-IFERROR(MIN(INDEX($A8:D8,AGGREGATE(14,6,COLUMN($D8:D8)/($D8:D8>=0),1)+1):D8),))

投稿日時: 19/09/13 17:29:16
投稿者: おばちゃん

んなっと様
 
ありがとうございます!!!
 
いろいろな値で検証してみた結果、表示したい結果になりました。
これから数式の理解をしてみます。
助かりました。
自分で作成したものはIF分をいっぱい入れ子にしてかなり見づらい数式になっていたので
すっきりしました。
 
月曜日に締めたいと思います。
 
 

回答
投稿日時: 19/09/14 15:52:39
投稿者: WinArrow
投稿者のウェブサイトに移動

お願い事項
 
最初の質問に踊ってしまいますが、
>不足表には月毎のリスクを表示させます
このような曖昧な説明ではなく、
計算論理(計算の考え方)を説明できるようにしましょう。
曖昧な説明は、貴方が意図した回答にはならない可能性があります。
結局、貴方が損をすることに繋がります。

投稿日時: 19/09/17 09:40:39
投稿者: おばちゃん

んなっと様
 
いろいろな値で試した結果、ほしい値になったので、締めさせていただきます。
 
WinArrow様
 
いつも言葉が足りなくて申し訳ありません。
今度質問するときは、計算論理(計算の考え方)の説明をうまくできるようにしたいと思います。