제품, 잔량별 생산일수 기준 불량 여부 판단

1. 문제

오른쪽 기준 표의 제품별, 잔량별 생산일수보다 왼쪽의 생산일수가 크다면 확인란에 "불량"이라고 표시하고, 아니면 공란으로 내버려 두려고 합니다.

 

생산일수기준 잔량체크.xlsx

0.01MB

 

 

2. 해법

여러 가지 함수로 해결할 수 있습니다.

 

가. Index + Match 함수

 

(1) 잔량을 0과 "있음"으로 구분하기

오른쪽 기준표를 보면 제품이 같더라도 잔량이 0인지 아닌지에 따라 생산일수가 다르다는 것을 알 수 있습니다.

그런데 왼쪽 표를 보면 잔량이 숫자로 표시되어 있으므로 

두 개를 Match 하려면 왼쪽 표의 잔량 수치를 0과 "있음"으로 바꿔줘야 합니다.

 

따라서, if 함수를 이용해 0보다 크다면 "있음" , 0이면 0이라고 표시하면 되므로

수식은 =if(d3>0,"있음",0)이 됩니다.

 

(2) 왼쪽 제품과 잔량에 해당하는 생산일수를 오른쪽 표에서 구하기

Match 함수를 사용하는데 찾을 값은 제품과 잔량이고, 찾을 범위는 오른쪽 제품과 잔량 전체입니다.

그리고, 정확히 일치해야 하는 것을 찾아야 하므로 세 번째 인수는 0이 됩니다.

 

이를 수식으로 표현하면

=match(a3&if(d3>0,"있음",0),$J$3:$J$8&$L$3:$L$8,0)가 됩니다.

 

찾을 값과 찾을 범위를 모두 열을 &로 연결하고,

찾을 범위는 변하면 안 되므로 절대참조 형식으로 바꿨습니다.

 

수식의 결과는 아래와 같이 1부터 6까지 표시되는데, 값이 일치하는 위치이고, 오른쪽과 왼쪽의 순서가 같다는 것을 알 수 있습니다.

 

(3) 오른쪽 표의 생산일수 찾기

index 함수는 구문이 INDEX(array, row_num, [column_num])이므로

array를 오른쪽의 생산일수로 지정하고,

row_num을 위에서 Match함수로 구한 숫자를 대입하면 됩니다.

 

따라서, 수식은

=INDEX($K$3:$K$8,MATCH(A3&IF(D3>0,"있음",0),$J$3:$J$8&$L$3:$L$8,0))이 됩니다.

그러면 아래와 같이 생산일수가 구해집니다.

 

(4) C열의 생산일수가 위에서 구한 생산일수보다 크다면 "불량"이라고 표시하기

수식은 아래와 같습니다.

=if(c3>INDEX($K$3:$K$8,MATCH(A3&IF(D3>0,"있음",0),$J$3:$J$8&$L$3:$L$8,0)),"불량","")

 

결과는 아래와 같이 5행부터 7행까지만 불량입니다.

 

 

나. Sumproduct 함수

'가'번 수식에서 INDEX($K$3:$K$8,MATCH(A3&IF(D3>0,"있음",0),$J$3:$J$8&$L$3:$L$8,0))을

SumProduct 함수를 이용해 구할 수 있습니다.

 

SumProduct 함수의 인수는 (조건1)*(조건2)*값 범위가 됩니다.

따라서, 수식은

=sumproduct(( $J$3:$J$8=a3)*( $L$3:$L$8= IF(D3>0,"있음",0))* $K$3:$K$8)

이 됩니다.

 

또는 조건 1과 조건 2를 &로 연결해서

=SUMPRODUCT(($J$3:$J$8&$L$3:$L$8=A3&IF(D3>0,"있음",0))*$K$3:$K$8)

라고 해도 됩니다.

 

C3와 비교하는 부분을 추가하면

=IF(C3>SUMPRODUCT(($J$3:$J$8&$L$3:$L$8=A3&IF(D3>0,"있음",0))*$K$3:$K$8),"불량","")

이 됩니다.

 

다. Filter 함수

Filter 함수의 구문은

=FILTER(array,include,[if_empty])

로서 배열, 포함(조건),[필터결과값이 비어 있을 때 값]으로서

배열은 머리글을 제외하고 데이터 영역만 지정합니다.

 

그러면 수식은

=FILTER($K$3:$K$8, ($J$3:$J$8&$L$3:$L$8=A3&IF(D3>0,"있음",0)))

이 되는데, 두 번째 인수가 위 Match 또는 SumProduct함수에 적용한 조건입니다.

 

C3와 비교하는 부분을 추가하면

=if(c3>FILTER($K$3:$K$8,($J$3:$J$8&$L$3:$L$8=A3&IF(D3>0,"있음",0))),"불량","")

이 됩니다.

 

생산일수기준 잔량체크(완성).xlsx

0.01MB