엑셀로 배부하기

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

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

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

다운로드