엑셀팁 - 미니 ERP 만들기1(VLOOKUP으로 시트이름을 참조하여 BOM 데이터 가져오기)

직장생활을 하다보면 마이크로 소프트웨어 엑셀(EXCEL)의 사용능력이 많이 필요로 하게 됩니다. 대부분의 일반 기업체의 ERP를 구축하여 회사의 운영현황을 관리하고 있습니다.
자재생산업무 담당자의 경우는 생산량에 따른 자재의 소요량이 정확하게 산출되어야 하고 구매 담당자의 경우는 생산계획수량에 맞는 자재를 확보해야 하는 것이 기본원칙 입니다.

 이러한 업무를 하기 위해서는 반드시 BOM(Bill Of Material)을 이용하여 객관적인 데이터를 산출하게 됩니다. 만약에 회사의 규모가 작아서, 설립이 얼마되지 않은 신생 법인일 경우, 또는 ERP구축 비용이 없어서 등 여러가지 이유로 주먹구구식으로 자재 소요량을 산출하기도 합니다.(ERP 구축은 돈이 있다고 해서 가능한 부분은 아닙니다만)

 이와 같은 상황일때는 어쩔 수 없이 EXCEL의 더하기 빼기,나누기, 곱하기 등의 단순한 기능을 이용할 수 밖에 없을 겁니다. 그런데 제품 종류가 작게는 두가지에서 많게는 몇천가지나 되는데 이것을 EXCEL의 사칙연산만 이용하여 소요량을 산출한다는 것은 하루가 24시간이 아니라 아마도 240시간 정도인 사람에게나 그나마 유효한 방법이 아닐까 생각합니다.

시트 이름을 참조하여 자동으로 자재소요량BOM을 가져오기

아래의 내용에서 EXCEL을 이용하는 사람이라면 대부분 알고 있는 VLOOKUP함수를 사용하여 간단하게 자재소요량을 산출하는 방법을 소개하고자 합니다.

 

위의 예제 화면은 최종완성된 산출 데이터 입니다.

A4:A38까지는 자재전체 리스트, C1:L1까지는 생산일자, C2:L2는 생산품목, C3:L3은 생산수량입니다.
생산품목 입력란과 생산수량 입력란에 해당되는 데이터를 입력하면 C4:L38셀에서 소요량이 자동으로 산출되게 설정 했습니다. 해당품목의 BOM 데이터는 시트 A,B,C~Q에서 자동으로 가져와서 생산량에 대입하여 산출하게 된다는 것입니다.

VLOOKUP 함수와 INDIRECT함수를 혼합사용하여 각 셀에 입력되어 있습니다.
만일 Q제품의 BOM 내용을 VLOOKUP함수 가져 온다면
C4셀에 '=VLOOKUP($A4,A!$A$3:$E$44,5,FLASE)'을 이용하면 됩니다. 하지만 여기서 우리는 C2:L2영역에 제품명만 입력하면 자동으로 소요량 산출되는 결과물이 나오도록 설정해야 합니다. 즉 변화되는 값(C2:L2)을 자동으로 당겨오게 만들면 되는 것입니다.
수식 =VLOOKUP($A4,A!$A$3:$E$44,5,FALSE)에서 A!의 A는 시트이름을 의미하므로 A!만 자동을 변경되도록 INDIRECT함수를 이용하면 됩니다.


본 포스트에 사용된 파일을 원하시는 분은 http://raymond.tistory.com/288 에서 비밀댓글로 이메일 주소를 남겨주시면 발송토록 하겠습니다.