FILTER, SEARCH 함수를 이용한 원료 총 필요량 조회

이번에 소개 드릴 내용은 함수 사용법은 아닙니다.

 

함수를 응용하여 제가 만든 유용한 Tool을 공유하고자 하기에 이해하기에 조금 어려울 수 있습니다.

 

많이 생각해서 만들었고, 옆 사람에게 설명을 해주어도 바로 이해하지 못하였습니다.

(만드는 방법에 대해서 관심있는 사람도 적었습니다.)

 

엑셀365에서만 사용 가능하며, 어느 정도 함수에 익숙하신 분들이 참고하시면 좋을 듯 합니다.

 

구조를 이해하고 본인의 업무에 맞게 적용한다면 많은 시간을 지켜줄 나만의 무기가 될 것 입니다.

 

1. 장표 작성 목표

 

만들고자 하는 최종형태는 아래와 같습니다.

 

매월 고객에 납품하는 제품이 있고 제품에 투입되는 원료 총 소요량이 알고 싶을 때 사용하게 됩니다.

 

고객 출고 수량과 제품의 BOM(Recipe)에 따른 원료 투입량을 계산하여 월 총소요량을 산출해내는 방식입니다.

 

월별로 제품코드와 필요수량을 입력하게 되면 원료 사용량이 자동으로 계산이 되는 방식입니다.

월별 원료 사용량 계산

2. 엑셀 장표 작성

 

장표 사용 방법으로는 ⓐ에 원하는 원료명을 입력면, 입력 된 정보를 가지고 아래 ⓑ에서 FILTER 함수와 SEARCH 함수를 사용하여 제품별 BOM을 가져오고, 가지고온 BOM을 통해서 총사용량을 계산하게 됩니다.

 

VLOOKUP 함수의 경우 중복된 값이 있으면 가장 윗 줄의 데이타 하나만 가지고 오기에 Filter 함수를 통해 원하는 원료의 함량만 가지고 왔습니다.

원료명 입력

3. 상세 설명

 

많은 기업에서 ERP를 사용하고 있기에 제품코드를 통하여 제품을 관리하고 있습니다.

 

해당 1번 과정은 생략하여도 상관 없으나, 중간 제품이 있는 경우(마지막 제품 포장품만 다른 경우 등)에는 유용하게 사용이 가능합니다.

 

1번에서 VLOOKUP 함수를 통하여 제품명을 가지고 옵니다.

  ① =VLOOKUP(C4,M:N,2,0)

 

2번 함수에는 1번함수에서 가지고온 제품명으로 원료의 BOM(Recipe)에 해당하는 수량을 가지고 오게 됩니다.

  ② =IFERROR(VLOOKUP(G4,J:K,2,0),"미투입")

제품명, 원료함량 입력

FILTER 함수와 SEARCH\에서 입력한 부원료에 대하여 아래 3번에서는 부원료가 들어가는 제품명을 결과값으로 나타내 줍니다.

  ③ =FILTER(P:P,ISNUMBER(SEARCH(H3,Q:Q)),"입력해주세요.")

  ④ =FILTER(R:R,ISNUMBER(SEARCH(H3,Q:Q)),"입력해주세요.")

 

③과 ④는 어떠한 값을 가지고 오냐에 차이가 있을 뿐 ⓐ 값을 가지고 동일한 함수를 사용하여 기준정보에서 원하는 정보를 가지고 오게 됩니다.

FILTER, SEARCH 함수 사용

참고로 아래와 같이 기준 정보가 먼저 구성이 되어야 합니다.

(업무에 따라 기준정보가 더 많이 필요한 경우도 있고, 간소화 되는 경우도 있음)

기준정보

 

원하는 값이 다를 경우 그에 맞는 함수를 찾는 약간의 수고가 업무시간을 단축시켜 줍니다.

 

나에게 맞는 함수(기능)를 찾아 빠르고 쉽게 처리하여 시간을 아끼셨으면 합니다.

모두들 적게 일하고 많이 버세요~