메뉴
실무 위키›응용 공식›공통 비용을 인원수 비율로 오차 없이 배분하는 공식

공통 비용을 인원수 비율로 오차 없이 배분하는 공식

지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

인원수 비율로 비용을 나누고 마지막 부서에서 반올림 차액을 맞춥니다.

예제파일 다운로드
공통 비용을 인원수 비율로 오차 없이 배분하는 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

=IF ( ROWS ( 부서 순번 범위 ) < ROWS ( 전체 부서 범위 ), ROUND ( 총비용 * 현재 부서 인원수 / SUM ( 전체 인원수 범위 ), 0 ), 총비용 - SUM ( 이전 배분액 범위 ) )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
부서 순번 범위필수첫 배분 행에서 A1 한 칸으로 시작하고 아래로 채울 때 끝 셀이 한 행씩 늘어나는 범위입니다. 범위의 셀 개수가 현재 배분 순번입니다. 비교식 순번<전체 부서 수에서 <는 왼쪽 값이 오른쪽 값보다 작음을 뜻하며 마지막 행 전까지 반올림 계산을 선택하는 조건입니다.
전체 부서 범위필수비용을 배분할 부서명이 빈칸 없이 입력된 범위이며 전체 행 수로 마지막 배분 행을 구분하는 기준입니다.
총비용필수모든 부서에 나눌 0 이상의 전체 비용이 입력된 셀입니다.
현재 부서 인원수필수현재 행 부서의 0 이상 정수 인원수가 입력된 셀입니다.
전체 인원수 범위필수비용을 배분할 모든 부서의 0 이상 정수 인원수가 입력되며 합계가 0보다 큰 범위입니다.
이전 배분액 범위필수배분액 머리글부터 현재 행의 바로 위까지 이어지는 범위입니다. 마지막 부서에서 앞선 배분액 합계를 빼는 데 사용하는 범위입니다.
C2=IF(ROWS($A$1:A1)<ROWS($A$2:$A$5),ROUND($F$2*B2/SUM($B$2:$B$5),0),$F$2-SUM($C$1:C1))
C3=IF(ROWS($A$1:A2)<ROWS($A$2:$A$5),ROUND($F$2*B3/SUM($B$2:$B$5),0),$F$2-SUM($C$1:C2))
C4=IF(ROWS($A$1:A3)<ROWS($A$2:$A$5),ROUND($F$2*B4/SUM($B$2:$B$5),0),$F$2-SUM($C$1:C3))
C5=IF(ROWS($A$1:A4)<ROWS($A$2:$A$5),ROUND($F$2*B5/SUM($B$2:$B$5),0),$F$2-SUM($C$1:C4))
A
B
C
D
E
F
G
1
부서
인원수
배분액
기준 항목
값
2
영업팀
7
175,000
총비용
1,000,001
3
개발팀
11
275,000
전체 인원수
40
4
고객지원팀
13
325,000
배분액 합계
1,000,001
5
경영지원팀
9
225,001
배분 차이
0
6
앞의 세 부서는 인원수 비율 금액을 원 단위로 반올림하고, 마지막 부서는 총비용에서 앞선 배분액을 뺀 금액을 받습니다. 175,000원, 275,000원, 325,000원, 225,001원의 합계는 총비용 1,000,001원과 정확히 일치합니다.
이 공식이 사용하는 함수
IF 함수 ROWS 함수 ROUND 함수 SUM 함수

동작 원리

ROWS 함수가 마지막 부서를 구분하고, 앞선 부서는 ROUND 함수로 인원수 비율 금액을 반올림하며 마지막 부서는 총비용에서 앞선 배분액을 뺍니다.

예제 시트에서 C2 셀의 비율 배분과 C5 셀의 마지막 차액을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

마지막 배분 행인지 확인합니다

<는 왼쪽 값이 오른쪽 값보다 작음을 뜻합니다. C5 셀의 현재 배분 순번과 전체 부서 수는 모두 4이므로 비교식 4<4는 FALSE이며 마지막 부서에 적용할 계산으로 넘어갑니다.

=ROWS ( $A$1:A4 ) < ROWS ( $A$2:$A$5 )
= FALSE
2

앞선 부서의 비율 금액을 반올림합니다

영업팀 인원수 7명을 전체 40명으로 나눈 비율에 총비용을 곱하면 175,000.175원입니다. 원 단위로 반올림한 C2 셀의 배분액은 175,000원입니다.

=ROUND ( 1,000,001 * 7 / 40, 0 )
= 175,000
3

앞선 배분액의 합계를 구합니다

C2:C4 셀의 배분액 175,000원, 275,000원, 325,000원을 더하면 775,000원입니다. SUM 함수는 C1 셀의 문자 머리글을 계산에서 제외합니다.

=SUM ( $C$1:C4 )
= 775,000
4

남은 금액을 마지막 부서에 배분합니다

총비용 1,000,001원에서 앞선 배분액 775,000원을 빼면 마지막 부서의 배분액은 225,001원입니다. 이 계산으로 배분액 합계와 총비용이 일치합니다.

=$F$2 - SUM ( $C$1:C4 )
= 225,001
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료