엑셀로 배부하기

원가/관리 회계와 관련된 일을 하다보니 '배부'라는 일이 일상이 되어 버린지 오래되었네요. 

해서 이번에는 엑셀로 배부를 하는 방법들에 대해서 다뤄 봅니다. 

 

1. 배부 ?? 

 : 우선 이번 포스팅에서는 

"어떤 값을 나누는 기준에 따라 특정 대상에 나누어 놓는것. "

이라고 하도록 하죠. 

보통 

• 어떤 값         : 배부원천

• 나누는 기준   : 배부기준

• 특정대상 : 배부대상 

이라고 합니다. 배부를 성립되게 하는 요소가 위 3가지 인 것이죠. 

2. 배부를 해보자 

 - 2.1 누구나 다 할 수 있는 단순 배부 

배송비 10,000 원을 배부 대상인 Product 에 Sales Quentity 비율 대로 배부 하는것 입니다. 

배부 대상을 그대로 놓고 비율을 구해서 배부하면 됩니다. 

 

 - 2.2 제품 / 거래처 별 배부 (1) : sumifs

위의 배부 에 거래처 별이라는 조건 한가지가 더 붙습니다. 

 

이렇게 되면 배부기준에 Customer 가 추가 되었으므로 배부비율을 만들때 Customer 를 고려 해야 합니다. 

즉 Customer / product  별 비율이 필요합니다. 

이 경우에 부분합을 계산 하는 함수가 필요합니다. 

sumifs 를 사용하겠습니다. 

sumifs 는 복수의 조건에 해당 되는 값만을 더 할 수 있는 함수입니다. 

= sumifs( '합산할 범위' , '조건 범위1' , '조건 값1' , '조건 범위2' , '조건 값2' , .... ) 

이해를 돕기 위해서 계산 과정을 하나 씩 풀어보죠.

① 거래처별 부문합      : sumifs 함수로 거래처별 부분합을 계산
② 배부 비율              : Sales Quentity / ① 거래처별 부분합 
③ 거래처별 배송비      : [배부 원천] 의 거래처별 배송비를 vlookup 으로 집계 
④ 배부 결과 A            : ② x ③ 

⑤ 배부 결과 B             : 하나의 셀에 수식을 모두 넣어서 처리 함.

sumifs 는 복수의 조건이 가능하므로 배부 기준의 조건이 추가 되더라도 그대로 적용이 가능합니다. 

* 파일을 첨부 합니다. 다운 받아 확인 하세요.

 

 - 2.2 제품 / 거래처 별 배부 (2) : pivot 

: sumifs 를 사용해서 배부 기준의 조건별 비율을 구하는 것이 수식만으로 처리 할 수 있으므로 간단합니다. 

 하지만. 수식이 많이 걸리고 복잡해지면 속도가 문제가 됩니다.
 데이터가 너무 많은 경우 계산을 못하고 죽어버리는 경우도 있죠. 

그럴때는 pivot table 을 이용해서 배부 비율을 구하는 방법을 권장합니다. 
여기서 사용하는 pivot 주요 기능은 "값필드 표시 형식" 설정을 사용합니다. 

1) 아래 모양대로 피벗 테이블을 만듭니다. 

2) 테이블 형식으로 표시 하시고 Sales Quantity 를 한번 더 값필드에 추가 합니다. 

3) 추가된 Sales Quantity2 를 우클릭 해서 "값 표시 형식" 을 "상위 합계 비율" 로 설정하고
   기준필드는 "Customer no" 로 합니다. 

4) 이제 거래처 (Customer no) 별로 Product 의 비율이 완성되었습니다. 이걸 가지고 배부 비율로 사용하면 됩니다. 

5) 아래는 '부분합 표시' 제거하고 '모든 레이블 항목 반복' 처리 한 결과입니다. Vlookup 걸어서 곱하기만 하면 되겠네요.

 

이상입니다. 즐거운 하루 되시고 피벗 테이블 관련해서 궁금하신것 있으시면 제 블로그의 피벗 관련 글 참고하세요. 

배부하기_예시파일.xlsx

다운로드