[ 파워쿼리 ] 다중 쿼리 병합을 활용한 재고 관리

파워쿼리 다중 쿼리 병합을 활용한 효율적인 재고 관리 방법을 알아보도록 하겠습니다.

해당 내용은 초심자에게는 재앙

중급이상에게는 희망

이 글을 보고 어떤 생각이 드는지 한번 생각해 보시면 좋을 것 같습니다.

아래와 같이 사과 24개의 재고가 있다고 가정해 보겠습니다.

판매는 우측과 같이 사과 2개, 사과 2입은 4개가 판매가 되었습니다.

그럼 사과의 현재 재고는 얼마일까요? 

우선 현재 재고는 아래와 같이 계산이 될 것 같습니다.

기존 재고 - 판매량 = 현재 재고

그리고 딱 보니 vlookup이나 sumif 함수를 써서 해결하면 좋을 것 같은 생각이 듭니다.

우선 엑셀로 재고를 구해 보도록 하겠습니다.

---

 

재고 관리 ( with 엑셀 )

 

우선 기존 재고에서 판매량을 빼주기 위해서 우선 vlookup으로 판매량을 가져와 보겠습니다.

 

 

vlookup의 한계 #.1 동일한 값이 있을 때 가장 위의 가져온다는 한계가 있습니다.

1:1로 매칭하는 경우 vlookup은 굉장히 좋은 함수인데 이러한 경우에는 활용도가 조금 떨어질 수도 있습니다.

 

 

 

vlookup의 한계 #.2 이거는 참고인데 찾고자 하는 값이 우측에 있을 때 활용하지 못하는 경우가 발생합니다.

이럴 경우는 xlookup으로 극복이 가능합니다

[ 엑셀 Tip ] xlookup 함수 활용법

 

vlookup의 제한 사항이 있다는 것을 알고 다른 방법을 활용하는 것을 알아 두는 것도 데이터 관리에 매우 유용합니다.

 

그럼 반대로 판매량에 재고를 붙인 다음에 빼도록 하겠습니다.

각각의 제품명의 24개의 재고가 들어와 있습니다. 그럼 재고는 48개인가요?

이 방법도 아닌 거 같은 생각이 듭니다.

 

그럼 이번에는 Sumif함수를 활용해서 풀어보도록 하겠습니다.

아래와 같이 판매량을 sumif로 가져와 봤습니다.

판매량은 6이 맞습니다 그런데 입수가 달라서 6이 아닌 (  1 * 2 ) + ( 2 * 4 ) = 10이 판매량이 될 것 같습니다.

굉장히 간단한 상황인 거 같았는데 생각보다 쉽지 않은 상황입니다.

 

 

아래와 같이 판매 환산 컬럼을 넣어 준 후 다시 sumif를 사용해 보도록 하겠습니다.

판매환산 = 입수 * 판매량

 

 

아래와 같이 판매량을 가져오면 재고 관리가 가능할 것 같습니다.

---

재고 관리 ( with 파워쿼리 )

 

이번에는 파워쿼리를 활용해서 재고 관리를 해 보록 하겠습니다.

파워 쿼리에 재고 및 판매를 올려주도록 하겠습니다.

 

우선 재고 - 판매량을 빼는 방법으로 접근해 보도록 하겠습니다.

쿼리 병합에 대해서 모르는 분들은 아래의 글을 참고해 주십시오.

[ 파워쿼리 ] 쿼리 병합 ( Nested Join )

반응형

 

쿼리 병합을 누른 후 재고 테이블의 구분과 판매 테이블의 전산명을 클릭 후 내부를 선택해 줍니다.

 

 

그럼 아래와 같은 창이 생성이 되고 판매 컬럼에 판매 테이블이 들어오는 것을 볼 수 있습니다.

우선 확장을 한번 해 보도록 하겠습니다.

 

확장을 하면 판매의 2가지 항목이 재고 테이블에 병합이 된 것을 볼 수 있습니다.

이 상태에서 입수 * 판매량 후 재고에서 차감하면 될 것 같지만

재고가 아까의 엑셀에서 처럼 24가 두 번 적용되는 상황이 발생하였습니다.

 

이전 단계로 돌아가서 다시 한번 생각해 보도록 하겠습니다. 

현재 단계에서 판매 테이블에서 판매량을 직접 구하는 것이 재고가 2배가 안되고 원하는 결과를 얻을 수 있을 것 같습니다.

 

 

판매 Table을 Table.TransformColumns 함수를 사용해서 전처리를 진행 후 판매량을 구해 보도록 하겠습니다.

판매 컬럼에 있는 테이블에 환산 수량을 넣기 위해서 Table.AddColumn 함수를 사용해서 환산 수량을 만들어 줍니다.

엑셀에서 환산수량을 만든 방법과 동일합니다.

728x90

 

해당 테이블에서 환산 수량만 남을 수 있도록 테이블을 리스트로 드릴다운해 보도록 하겠습니다.

[ 환산수량 ]을 뒤에 입력하면 해당 항목만 남는 것을 볼 수 있습니다.

 

이제 이 리스트의 합을 마지막으로 구해 줍니다.

아래와 같이 List.Sum을 입력하면 판매량이 구해지는 것을 볼 수 있습니다.

 

사용자 지정 열을 추가 후 [ 재고 ] - [ 판매 ]를 입력해 주면

 

 

아래와 같이 잔여 재고를 구할 수 있습니다.