자재 관리 요약 표지 만들기#3 (IFERROR, INDEX, MATCH 엑셀 함수 활용)

앞전의 포스팅에선 자재의 입고, 출고 및 예상 입고, 출고 사항에 대한 재고관리 수식을 설명드렸습니다. 이번엔 관리해야 되는 자재가 여러 개 존재해, 시트를 여러 개로 관리하는 경우, 해당 시트별 예상 부족 일자와 수량을 한 장의 시트에 요약하는 수식을 작성해보도록 하겠습니다.

자재 관리 문서 만들기의 3번 째 포스팅입니다. 만약 3번 째 포스팅으로 검색해 접속하신 분들은, 아래 내용이 이해 안 가실 수도 있으신데요. 만약 이번 포스팅이 이해가 안가시는 분은 첫번 째 포스팅인 자재 실재고 및 예상 재고 수량 계산하기#1 부터 읽으시길 권장합니다. 그리고 예상 회복 일자와 예상 회복 수량은 다음 포스팅에 설명드리도록 하겠습니다.

 

 

2019/01/07 - [엑셀 응용] - 엑셀로 자재 실재고 및 예상 재고 수량 계산하기#1 (ADDRESS, MATCH, TODAY)

 

 

  자재 관리 요약 표지에 대한 간단한 설명

 

★ 이번 포스팅을 설명하기위해, 앞전에 포스팅했던 내용 중 제품#1 시트를 예제로 가져왔습니다

 

 

★ 이번 포스팅에 사용되는 예제입니다.

 

 

 

제품명 항목은 제품을 구분할 수 있는 고유 값인 품번, 품명 등을 기입하면 되지만, 실제 생성되어있는 시트 이름을 사용해야 됩니다. 4번째 포스팅을 할 때 설명드리겠지만, 다른 시트의 주소를 참조할 때, 시트 주소값이 문자로 3번씩 기입되었다고 하면(ex : '제품#4!A1:A10 & '제품#4!....'제품#4!.....), 주소 값의 변경이 발생되면 3개를 일일이 변경해 주어야 되지만, 지금 같은 경우는 제품명의 내용만 수정하면 되기 때문입니다. 이 방법은 동일한 시트 이름이 자주 사용될 때 유리하고, 1번 정도만 쓰일 경우엔 오히려 관리에 불리할 수도 있습니다.

예상 부족 일자는  다음 날(DAY+1)을 기준으로, 첫 자재 부족 시점의 일자를 가져옵니다. 예를 들면 오늘이 10일일 경우, 12일(자재 부족) 14일(자재 확보) 16일(자재 부족)이라고 가정할 시, 첫 부족 상황인 12일에 대한 결과를 가져오는 것입니다.

 

예상 부족 수량은 날짜와 마찬가지로 다음 날을 기준으로, 첫 번째 부족 수량 값을 가져옵니다.

 

D-DAY는 오늘자 기준으로 자재 부족 시점이, 며칠 후인지를 표시하는 항목입니다. PC의 오늘자가 기준이기 때문에, 날짜가 지나 갈수록 D-DAY 날짜는 자동으로 줄어들게 됩니다.

 

예상 회복 사항에 대해선, 다음 포스팅에 추가 설명드리도록 하겠습니다.

 

 

  예상 부족 수량 및 날짜 수식 기입하기

 

 

 

예상 부족 일자 예시 : {=IFERROR(INDEX('제품#1'!$C$6:$C$37,MATCH(TRUE,'제품#1'!$E$6:$E$37<0,0) ,0),"부족 수량 없음")}

 

예상 부족 수량 예시 : {=IFERROR(INDEX('제품#1'!$E$6:$E$37,MATCH(TRUE,'제품#1'!$E$6:$E$37<0,0),0),"부족 수량 없음")}

 

MATCH(TRUE,'제품#1'!$E$6:$E$37<0,0) 항목은 MATCH 함수를 통해, 행 값을 구하기 위해 사용됩니다. 행 값을 찾기 위한 조건은, 범위로 지정된 '제품#1'!$E$4:$E$37<0 이란 항목입니다. 이 항목을 예로 들면 E6 ~ E37 까지를 개별적으로 0 미만인지를 판단하게 됩니다. 만약 0 미만일 경우 TRUE를, 0 이상인 경우는 FALSE를 출력하게 됩니다

이 결과는 MATCH의 TRUE 조건과 E4 ~ E37까지의 개별적인 결과와 각각 비교되게 됩니다. 결과가 TRUE인 경우가 여러 개 출력될 수 있지만, 범위 내에 있는 첫 번째 TRUE 값이 있는 행 값을 가져오게 됩니다. 그리고 예제를 보면. E6를 1이라고 할 때, 제품#1 시트의 E28 셀에 첫 번째 부족 수량이 존재하기 때문에, MATCH는 23을 출력하게됩니다.

  

INDEX('제품#1'!$C$6:$C$37,23, 0) 항목은 제품#1 시트의 C6:C37 범위에서, 같은 줄(0)의 23번째 행 값을 가져온다는 의미인데요. 여기선 시작 위치가 C6이기 때문에, 제품#1 시트의 E28 셀 내용을 가져온다고 생각하시면 됩니다. 그리고 예제에선 E28 셀에 2019-01-22 날짜 값이 기입되어 있습니다.

 

{=IFERROR(2019-01-22,"부족 수량 없음")} 는 배열 수식을 사용한 수식이고, 예상 부족 일자의 전체 수식에 반영됩니다. 그리고 IFERROR을 사용한 이유는 부족 수량이 없을 경우 에러가 발생하기 때문입니다. 그래서 에러에 대한 방어 코드가 필요했고, IFERROR을 사용해, "부족 수량 없음" 이란, 에러 대체 문구가 표시되도록 처리했습니다.

 

 

  D-DAY 수식 지정하기

 

 

 

D-DAY 예시 : =IFERROR(TODAY()-C12, "-")

 

=IFERROR(TODAY()-C12, "-") 는 TODAY() 함수를 사용해, 오늘 일자를 추출한 후 예상 부족 일자와의 차이를 비교해 D-DAY 값으로 표시하게 됩니다. 그리고 IFERROR 함수를 사용한 이유는, 부족 수량이 없을 경우 C12(날짜) 셀에서 오류 값을 가져오기 때문에 문제가 발생하게 되고, 이에 대한 에러 방어 차원에서 에러 시 "-"가 출력되도록 했습니다.

 

2019/01/09 - [엑셀 응용] - 자재 관리 요약 표지 만들기#4 (INDIRECT, ADDRESS, IFERROR 엑셀 함수 활용)

 

자재 관리 시트를 만들기위한 3번째 포스팅을 작성했는데요. 나름 자세히 설명을 하려고 노력하긴 했는데, 내용이 어렵진 않았는지 모르겠습니다. 이제 마지막 포스팅인 표지 만들기 #4 포스팅만 읽으시면, 자재 관리 문서만드는 포스팅도 완료가됩니다.

이번 포스팅을 마지막까지 읽어주셔서 감사드립니다.