자재 관리 요약 표지 만들기#4 (INDIRECT, ADDRESS, IFERROR 엑셀 함수 활용)

자재 실재고 및 예상 재고 수량을 관리 문서를 만들기 위한 4번째(마지막) 포스팅입니다.

제 본업과는 조금 차이가 있는 내용이긴 하지만, 최대한 실무에서 응용해 사용 가능하도록 작성했는데요. 총 4편의 (시트 만들기 2편과 요약 표지 만들기 2편) 포스팅으로 작성 되다보니, 이번 포스팅으로 검색해서 오셨다면 내용이 이해 안 가실 수도 있습니다.

만약 내용이 이해 안 가시는 경우 첫 번째 포스팅인 "엑셀로 자재 실재고 및 예상 재고 수량 계산하기#1" 부터 읽으시면 내용이 이해되시리라 생각됩니다.

 

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

 

INDEX 함수에 대해서만 궁금하신 경우 재고수량 가져오는 엑셀 함수 포스팅을 먼저 확인해주세요.

 

2018/12/30 - [엑셀 응용] - 마지막 입고된 수량과 재고수량 가져오는 엑셀 함수 (INDEX, MATCH, MAX )

  예상 회복 일자와 수량에 대한 요약 설명

 

★ 이번 포스팅 설명을 위해, 이전 포스팅의 제품#1 시트의 내용을 예제로 가져왔습니다.

 

 

 

★이번 포스팅에 설명드릴, 통합 관리 시트의 예제입니다.

 

 

 

 

예상 회복 일자는 첫 재고 부족 시점 이후로, 양수가(회복) 되는 일자를 표시합니다.

예상 부족 일자를 시작으로, 첫 번째 재고보유 날짜를 가져오는 수식입니다. 예로 위의 재고#1 사진을 보시면, 첫 재고 부족 일자가 2019-01-22 일이고, 28번째 행(E28)에 존재하는 게 보이실 겁니다. 이렇게 되면 일자를 가져오는 범위는, 첫 번째 부족 일자가 있는 28행:마지막 날짜 행으로 설정하게 됩니다.  (자세한 건 아래에 조금 더 다루도록 하겠습니다)

 

예상 회복 수량은 회복 일자와 동일하게 동작하는 수식이며, 단지 일자가 아닌 부족한 수량을 가져오는 형태입니다.

 

예상 회복 일자 주소값은 회복 일자 적용 범위가, 첫 번째 부족 값이 있는 셀을 기준으로 시작한다고 말씀드렸는데요. 그 첫 번째 셀의 주소 값을 구하기 위해 사용됩니다.

 

예상 회복 수량 주소값은 회복 일자 주소값과 동일한 방식으로 구성되었으며, 일자가 아닌 수량에 대상이 되도록, 주소 값만 변경 되도록 만들어졌습니다. 

 

 

 

  예상 회복 주소값 수식 작성하기

 

 

 

예상 회복 일자 주소값 예시 : {=IFERROR("'"&B12&"'!"&ADDRESS(MATCH(TRUE,'제품#1'!$E$6:$E$37<0,0) + 5, 3) & ":C37", "-")}

예상 회복 수량 주소값 예시 : {=IFERROR("'"&B12&"'!"&ADDRESS(MATCH(TRUE,'제품#1'!$E$6:$E$37<0,0) + 5, 5) & ":E37","-")}

 

MATCH(TRUE,'제품#1'!$E$6:$E$37<0,0) + 5 은 첫 부족 시점을 찾기 위한 수식으로, 앞전에 설명드렸던 MATCH와 동일한 방법으로 사용됩니다. 이 수식 사용해, 위의 재고#1 시트의 첫 재고 부족 시점인 E28:E37까지의 범위를 설정할 수 있습니다.

 

이제 수식을 설명드리면, 첫 제품#1 시트 E6 ~ E37의 각각의 셀 값에 대해, 0 미만인지 판단합니다. 만약 0 미만일 경우는 해당 셀 값에 대한 TRUE 값을 출력하고, 0 이상일 경우는 FALSE를 출력하게 됩니다. (ex : E6가 6리라고 가정할 시 E6>0 이기 때문에 FALSE를, E7이 -1일 시 E7<0은 TRUE)

 

MATCH 함수를 통해 TRUE 값을 검색하게 되고, 첫 번째 0 미만인 TURE 값을 찾게 되면, 해당 행번호를 가져옵니다. 위 예제에선 제품#1 시트의 E28 셀 이 해당되는데요. E1이 아닌 E6을 1로 판단하기 때문에, 23이란 값을 출력하게 됩니다. 그리고 23 값을 실제 행 값인 E28과 일치화 시키기 위해 + 5 값을 해주었습니다.(상단의 1~5행은 사용 안 하는 줄이고, 6행부터 시작하다 보니 5라는 행 값의 차이가 발생된 것입니다)

 

그리고 여기서 주의하셔야 될 사항이, 제품#1은 시트 이름이기 때문에, 시트가 변경되는 경우 이름을 변경해야 됩니다. 지금 설명드리고 있는 예제를 기준으로 하면, 제품#2 , 제품#3 시트등이 포함될 수 있습니다.

 

ADDRESS(26, 3) & ":C37" 는 ADDRESS를 이용해 문자로 구성된 셀주소 값을, 실제로 접근하기 위해 사용되는 함수입니다. 26행에 3(C)이면 C26 셀로 접근하게 되고, C26 & ":C37" 이란 수식을 통해 C26:C37 범위를 지정하도록 작성되었습니다. 앞전에도 설명드렸지만 이 부분이 첫 부족 시점 이후의 첫 회복 시점의 범위를 구하기 위한 식입니다. 

 

{=IFERROR("'"&B12&"'!"&C26:C37, "-")} 에서 C26:C37은 아직 완전하지 않습니다. 왜냐하면 재고를 나눈 단위가 시트 단위인데, 시트 이름이 없을 경우 현재 있는 통합 관리의 영역으로 선택되기 때문입니다.

 

그래서 셀 주소 앞에 시트 이름을 기입해주기 위해, B12란 셀 주소를 참조(선택) 했고, B12는 제품#1 이란 이름이 기입되어 있기 때문에 수식에 따른 결과는 '제품#1'!C26:C37 가 되는 것입니다. 이 전 포스팅에서 제품명과 시트 이름을 동일하게 해야 된다고 했던 이유가 여기에 있습니다.

 

  예상 회복 일자/수량 수식 작성하기

 

 

 

예상 회복 일자 예시 : {=IFERROR(INDEX(INDIRECT(H12), MATCH(TRUE, INDIRECT(I12)>0,0), 0), "회복 예정 없음")}

예상 회복 수량 예시 : {=IFERROR(INDEX(INDIRECT(I12), MATCH(TRUE, INDIRECT(I12)>0,0), 0), "회복 예정 없음")}

 

MATCH(TRUE, INDIRECT(I12)>0,0) 이 항목에서 위에서 설명드렸던, 주소값을 사용할 차례입니다. 제품#1을 기준으로 설명드리면, 회복 수량의 주소값은 I12 셀에 기입되어 있습니다.(위에서도 언급 드렸지만, 회복 수량은 부족 수량 시작 셀 부터 ~ 마지막 날짜 셀까지의 범위에서 찾게 됩니다) 

 

INDIRECT(I12)를 통해 '제품#1'!$E$28:E37 문자를 실제 주소로 만들어준 후 0을 초과하는지 확인합니다. 만약 초과한다면 TRUE를 이하라면 FALSE를 표시할 것인데, 위의 예제들을 참고해보면 E28 ~ E37 범위 내의 첫 번째 TRUE 값(양수)은 E31이고, E28을 1로 계산하기 때문에 MATCH(4)라는 결과를 얻게 됩니다

 

INDEX(INDIRECT(H12), 4, 0) INDIRECT(H12)는 H12 셀에 있는 회복 일자 주소 값인 '제품#1'!$C$28:C37 를 실제 주소화하고, 해당 범위에서 같은 줄(0)의 4번째 행 값을 INDEX를 통해 가져오게 됩니다. 가져온 값은 4번 째인 C31 셀 값인 2019-01-25를 가져오게 됩니다.

 

{=IFERROR(2019-01-25, "회복 예정 없음")}배열 수식으로 구성된 수식이며, 날짜가 있는 경우는 날짜를 표시합니다. 하지만 날짜가 없을 경우 에러를 표시하기 때문에, 에러에 대한 대체 문자를 삽입하기 위해 IFERROR 함수를 사용했습니다. 여기선 오류가 나면 회복 예정 없음이라고 표시하게 됩니다.

 

 

자재 관리를 위한 예제 문서 만드는 방법은 이번 포스팅으로 완료되었습니다. 각각의 함수 동작 원리를 인지하고 계시지 않는다면, 내용이 조금 더 어려웠을 수도 있을 것 같습니다. 하지만 위 내용을 이해하시게 된다면, 좀 더 개인화하실 수도 있으실 거라 생각되고, 그에 따른 엑셀 실무 능력이 많이 향상되지 않을까 생각됩니다.