엑셀 배우기, SUMIF 함수로 재고 관리하기!

단일 조건의 합계를 구하는 SUMIF 함수는 지난 엑셀배우기, COUNTIF/SUMIF/COUNTIFS/AVERAGE 함수로 전체 평균 이상인 학생들의 평균 구하기! 포스팅에서 다루었습니다. 이번 포스팅에서는 SUMIF함수를 복습하는 차원에서, 그리고 어떠한 상황에서 이 함수를 사용할 수 있을지를 다시 설명해 드리고자합니다. 그만큼 SUMIF 함수는 컴퓨터활용능력 시험 뿐만이아니라, 엑셀 실무에서도 활용도가 굉장히 높기 때문입니다. 

여러분이 회사에 입사에 재고 관리 업무를 담당해야한다고 합시다. 어떤 형태로든 재고 관리 대장을 만들어야 할텐데,  일단 재고 관리에 있어서 가장 중요한 데이터가 무엇인지를 고려해야합니다. 당연히 '입고량'와 '출고량'입니다. 그리고 입고량과 출고량을 지속적으로 계산해서, 현재의 재고량을 파악할 수 있어야, 재고량이 완전 소진되기 전에 필요한 상품을 늦지 않게 주문할 수 있을 것입니다.

이렇게 필요한 서식을 만들 때는 필요한 정보를 얻기 위해서는 어떤 데이터가 필요한지, 각 데이터들이 어떠한 연관성을 갖는지, 그리고 어떠한 함수를 사용해서 원래의 데이터들을 필요한 정보로 변환할지를 고민해야합니다. 아래에서 만든 매출 장부이자, 재고 관리 대장을 보면서 설명을 이어가도록 하겠습니다.

아래의 매출 장부이자 재고 관리 대장에는 날짜별로 특정 상품의 입고량과 출고량을 기록하면, 자동으로 해당 상품의 재고량이 F열에 계산되도록 설계되었습니다. 그리고 동시에, H2:J7의 열에 각 상품별 재고량을 한눈에 확인할 수 있도록 별도로 재고 현황 표를 만들었습니다. 필요시에 현재 보유하고 있는 상품들의 재고량을 바로 보고할 수 있어야하니 말입니다. 

매출 장부의 내용을 상세하게 보면, 8월 1일에 A-1부터 A-5까지 다섯 종류의 상품이 각 10개씩 입고 되었고, 8월 6일부터 각 상품들이 판매되고 있있습니다. 매출 장부에는 위의 매출 내역이 하나씩 기록될 때마다 F열의 대응 행에 재고량이 자동으로 계산되고 있습니다.

SUMIF 함수의 구조는 익히 아시겠지만 한번더 언급하자면, '=SUMIF(조건의 범위,조건, [합계할 범위])'입니다. 참고로 옵션 인수인 [합계할 범위]를 생략하면, 조건의 범위에서 해당 조건에 맞는 셀 값들을 더하게 됩니다.

이에 맞춰서 아래의 F4셀의 수식을 풀어보도록 하겠습니다. 특히 절대 주소에 유의해서 아래 수식을 살펴 주셔야합니다. 당연하지만, F4셀에서 계산되어야할 재고량은 D3:D4 범위의 입고량과 E3:E4 범위의 출고량입니다. (D5 이상의 범위와 E5 이상의 범위는 F4셀의 재고량 계산의 범위가 아닙니다. F4셀에 재고량이 계산될때는 D5와 E5의 입고량과 출고량은 아직 발생하지 않은 미래의 데이터이기 때문입니다) 

그리고 이 두값을 빼면, '현재'의 재고량이 되겠지요. 마찬가지로 F5셀에서 계산되어야할 재고량은 D3:D5 범위의 입고량과 E3:E5 범위의 출고량입니다. (이또한 D6 이상의 범위와 E6 이상의 범위는 F5셀의 재고량 계산의 범위가 아닙니다)

재고량 계산에 있어서 범위를 설정하는데 공통되는 부분이 있지요? 바로 D3셀과 E3셀은 고정이 되어야 한다는 것입니다. 그 두셀이 입고의 출고 데이터가 생성되는 기준점이기 때문입니다. 그리고 F열의 각 행에서 SUMIF 함수를 활용해서 각 상품별 입고량과 출고량을 계산하고 이 둘의 차이를 재고량으로 계산하기 위해서는, 입고량 D열의 범위와 출고량 E열 범위가 각각 D4와 E4 이상에서 1씩 증가해야합니다. 그래야 채우기 핸들을 사용해서 해당 수식을 아래로 복사했을 때, 합계할 범위가 자동으로 하나씩 늘어날 테니까요.

좀 설명을 복잡하게 한듯한데, J3:J7 범위에 계산되는 재고량은 매출 장부의 범위 전체를 기준으로 해서 각 상품별 입고량과 출고량을 계산해서 둘의 차이를 구하고 있다는 점을 확인해 보시면, F4셀과 F5셀의 함수 수식이 어렵지 않게 이해가 되실거라 생각합니다. 

물론 B열도 같은 논리로 SUMIF 함수의 조건의 범위를 지정할 때, B3는 고정을 하고, 채우기 핸들을 사용해서 수식을 하래로 복사할 때는 B4 이상부터는 1씩 증가하도록 절대 주소를 지정하지 않은 것입니다. 

참고로 위의 매출 장부에서 노란색으로 표시한 데이터들과 H2:J7 범위에서 재고 내역을 비교하면 정확히 일치한다는 점을 알 수 있지요? 사실 위에 매출 장부에서는 상품의 종류가 다양하지 않기 때문에, F열에서의 실시간 재고를 계산할 필요가 있지는 않습니다. 

H2:J7에서 바로바로 확인이 가능하니까 말입니다. 하지만 서점의 도서 재고 대장을 만든다면 어떨까요? 수십권 수백권 종류의 상품이 있다면 실시간 재고 현황을 파악할 필요가 바로바로 생기겠지요. 물론 이 엑셀 파일보다는 더 자동화된 프로그램으로 재고관리를 하겠지만 말입니다.

그리고 위에서 재고량이 마이너스가 된 부분은 실수가 아닙니다. 출고량을 RANDBEWEEN 함수를 사용해서 만들다 보니, 입고량 이상으로 팔아버린 현상이 생겼을 뿐입니다. 실무에서 만약에 이렇게 마이너스 값이 생긴다면, 원인은 두가지가 될 것입니다. 

입출고 데이터를 잘못 입력한 경우와, 아니면 아예 입력이 되지 않는 경우입니다. 이렇게 실시간으로 재고량을 관리하면서 해당 문제도 한번에 파악할 수 있으니, F열의 효용은 더 이야기할 필요가 없겠습니다.