엑셀로 자재 실재고 및 예상 재고 수량 계산하기#2 (IF, SUM, INDIRECT 함수 활용)

★ 자재 관리 문서를 만드는 2번 째 포스팅이고, 앞의 #1 포스팅을 먼저 확인 후 읽어주시길 부탁드립니다.

 

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

 

이전 포스팅에 입고 예정 수량과 출고 예정 수량을 카운트하기 위한 셀주소 값 수식을 작성했었고, 간략히 다시 설명드리면, 예정 수량이기 때문에, 다음 날인 D + 1일 이전 자료는 제외하기 위해, 다음 날에부터 시작 가능한 셀 주소 값을 추출하기 위한 수식이었습니다.

그리고 이번 포스팅에선 입고, 출고 예상 카운트를 하기 위한 수식을 작성해보겠습니다.

 

 

  입고, 출고 수량을 카운트해 실재고 수량을 계산해보자 

 

입고, 출고는 오늘 까지를 기준으로 하기 때문에, D + 1(다음 날) 부터는 숫자가 0으로 처리되도록 작성했습니다.

 

실재고 수량 예시 : {=IF(TODAY()>=C11,SUM((F$6:F11) + (G$6:G11) - (H$6:H11)), 0)}

 

SUM(((F$6:F11) + (G$6:G11) - (H$6:H11))는 F열인 이월 재고 수량 + G열 입고 수량 - H열 출고 수량으로 작성된 식입니다. 그리고 주소값을 보시면 첫 번째 대상은 행에 절대주소가($6) 걸린 것을 보실 수 있습니다. 이는 아래 예제에서 일자가 늘어날수록 입고, 출고의 누적 수량을 계산하기 위한 방법입니다. 예로 G열 입고 수량의 6번째 줄엔 G$6:G6라는 값이 기입되어 있는데요. 6번 줄을 아래로 쭉 끌어서 복사하기를 해 G10행까지 이동되었다면, G$6:G11로 기입됩니다.

 

그렇다면 예제 결과로 보았을 때, 이월 재고 1000 + 입고 수량 1000 - 출고 수량 100이 돼서, 1900이란 결과가 나오게 됩니다. 만약 G6:G6을 드래그했다면, G11:G11로 기입됐을 것이고, 결과도 원하는 값이 나오지 않았을 것입니다.

 

{=IF(TODAY()>=C10,1900, 0)}은 TODAY() 함수를 사용해 오늘 날짜를 불러옵니다. 예로 2019-01-05일이라고 할 때, C10 >= 2019-01-05 인지를 판단하고, 참이면 1900을 표시하고, 거짓이면 0을 표시합니다. 2019-01-05를 초과하는.. 즉 다음 날인 값은 실재고가 아닌 예상 재고로 분류할 계획이기 때문에, 연산을 하지 않았다는 의미로 0을 기입했습니다.

 

그리고 배열 함수인 {}에 수식이 묶여 있는데요(배열 수식은 키보드의 Ctrl + Shift + Enter를 누르면 설정됩니다). 배열 수식은 한 줄의 식이지만 6행부터 11행까지의 5가지 조건을, 한 번에 연산한다고 보시면 되는데, 다음 기회에 좀 더 자세히 다루도록 하겠습니다.

만약 배열 수식을 지정하기 싫으시다면 =IF(TODAY()>=C11,SUM(F$6:F11) + SUM(G$6:G11) - SUM(H$6:H11), 0) 로 기입하셔도 무방합니다.

 

 

 

  예상 입고, 출고 수량을 기준으로 예상 재고 수량을 계산해보자. 

 

D + 1(다음 날) 기준으로 계산되고, 과거 ~ 오늘까진 계산을 하지 않는다는 의미로 0으로 표시합니다.

 

예상 재고 수량 예시 : {=IF(TODAY()>=C37,0,SUM((F$6:F37)+(G$6:G37)-(H$6:H37))+SUM((INDIRECT(J$1):I37)-(INDIRECT(J$2):J37)))}

 

SUM((F$6:F37)+(G$6:G37)-(H$6:H37)) 위에서 설명드렸던 실재고 수량 식을 그대로 사용했습니다. 실재고 수량에서 입고, 출고 예정 수량을 계산하기 위해 사용되었습니다. 그리고 37행이면 2019-01-31일 자 실재고 수량은 1900입니다

 

SUM((INDIRECT(J$1):I37)-(INDIRECT(J$2):J37)) J$1은 입고 예정 수량 셀주소 값이고, J$2 출고 예정 수량 셀주소 값입니다. 위에서도 말씀드렸지만, 셀 주소 값은 D + 1(다음 날)의 입고, 출고 예정 수량의 셀 주소를 파악하기 위함입니다.

그럼 수식을 풀어보면 INDIRECT(I$12), INDIRECT(J$12) 값을 얻을 수 있는데, INDIRECT는 문자 형식으로 된 주소값을, 실제 주소처럼 사용할 수 있게 해주기 때문에, SUM((I$12:I37) - (J$2:J37)) 의 수식을 얻을 수 있습니다. 그리고 이 수식은 배열 수식을 사용해야 정상적인 결과가 나옵니다. 그리고 해당 연산을 진행하면 -1587 이란 결과가 나오는데, 재고가 부족하다는 의미겠네요.

 

{=IF(TODAY()>=C37,0,1900 + (-1587) } 은 C37이 과거이거나 오늘이면 0을, 오늘 이상일 경우 1900-1587인 313을 표시합니다. 그리고 해당 함수도 배열 함수로 묶었습니다.

 

 

 

 

제가 직접 측정해본 것은 아니지만, 배열 수식을 사용하는 게, 연산 속도에 조금 더 나쁜 영향이 있다고 들은 적이 있습니다. 이런 생각이 있긴 하지만, 배열 수식을 자주 사용하는 편이다 보니, 습관적으로 배열 수식을 사용해 만들어본 것 같습니다. 

물론 일반 수식으로도 작성이 가능한 예제이니, 편하신 대로 연습하시는 게 좋을 것 같습니다.

 

그리고 첫 포스팅에도 말씀드리긴 했지만, 실무 업무가 아니다 보니 양식에 오류가 있을 수도 있습니다. 활용 목적 반, 학습 목적 반으로 만든 예제인 점을 감안 부탁드리며, 잘못되거나 수정 필요한 부분이 있다면 알려주시면 감사드립니다.

 

2019/01/08 - [엑셀 응용] - 자재 관리 요약 표지 만들기#3 (IFERROR, INDEX, MATCH 엑셀 함수 활용)

 

여기까지는 문서의 핵심인 수량 기입에 대한 함수를 구성해봤습니다. 3번째 포스팅에선 여러개의 자재를 한장의 시트에 요약해 표시하는, 표지 만들기 포스팅을 진행하도록 하겠습니다.

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