납품 차수 조정

품목에 따른 행을 INDEX, MATCH로 잡아내면 수식이 더 길어지겠지만, 아래와 같이 로직은 있습니다.

 

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2		1회	2회	3회	4회	합계	단위			1회	2회	3회	4회	합계	단위		2
3	1차	4	0	0	2	6	5		1차	5	5	0	0	10	5		5
4	2차	20	100	30	47	197	5		2차	60	60	60	60	240	5
5	3차	100	20	30	47	197	5		3차	100	100	100	100	400	5
6	4차	0	6	0	10	16	5		4차	0	10	10	0	20	5
7	5차	0	0	23	10	33	5		5차	0	0	20	20	40	5

 

K3 =IF(C3=0,0,MAX(CEILING(G3/SUMPRODUCT(N($C3:$F3>0)),H3),CEILING(C3,H3),CEILING((C3+D3)/2,H3)))

L3 =IF((C3+D3=0)+(COUNTIF($K3:K3,">0")=SUMPRODUCT(N($C3:$F3>0))),0,MAX(CEILING(G3/SUMPRODUCT(N($C3:$F3>0)),H3),CEILING(C3,H3),CEILING((C3+D3)/2,H3)))

M3 =IF(COUNTIF($K3:L3,">0")=SUMPRODUCT(N($C3:$F3>0)),0,MAX(CEILING($G3/SUMPRODUCT(N($C3:$F3>0)),$H3),CEILING($C3,$H3),CEILING(($C3+$D3)/2,$H3))) ; M3 잡아 N3에 복사, K3:N3 잡아 아래로 데이타 행 만큼 복사하면 작업 끝.

 

수식 구조 :

R2 =SUMPRODUCT(N($C3:$F3>0))

R3 =MAX(CEILING($G3/R2,H3),CEILING($C3,$H3),CEILING(($C3+$D3)/2,$H3)) ; 합계/개수, 1차 값, (1차+2차 값)/2 중 최대값을 가져 옴.

K3 =IF(C3=0,0,R3)

L3 =IF((C3+D3=0)+(COUNTIF($K3:K3,">0")=$R2),0,$R3)

M3 =IF(COUNTIF($K3:L3,">0")=$R2,0,$R3) ; R2, R3을 K3:M3에 밀어 넣고 정리하면 위 수식이 됨.