메뉴
실무 위키›응용 공식›항목별 비중 구하기 공식

항목별 비중 구하기 공식

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

전체 합계를 고정해 아래 행까지 채웁니다.

예제파일 다운로드
항목별 비중 구하기 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

01전체 금액에서 각 항목의 비중 계산하기

=현재 금액 / SUM ( 전체 금액 범위 )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
현재 금액필수전체에서 차지하는 비중을 구할 현재 행의 금액 셀입니다.
전체 금액 범위필수수식을 아래로 채워도 움직이지 않도록 행과 열을 절대참조로 고정한 모든 항목의 금액 범위입니다.
C2=B2/SUM($B$2:$B$8)
A
B
C
D
1
부서
예산액
비중
2
영업팀
255,000,000
25.5%
3
개발팀
184,000,000
18.4%
4
운영팀
153,000,000
15.3%
5
기획팀
122,000,000
12.2%
6
마케팅팀
111,000,000
11.1%
7
인사팀
98,000,000
9.8%
8
재무팀
77,000,000
7.7%
9
합계
1,000,000,000
100%
10
비중 열에는 백분율 서식을 적용합니다. 분자는 아래 행으로 이동하고 전체 예산 범위는 고정되므로 같은 수식을 모든 부서에 채울 수 있습니다.

02반올림한 비중의 합계를 100%로 맞추기

=IF ( ROW ( ) = ROW ( 마지막 항목 ), 1 - SUM ( 이전 비중 누계 ), ROUND ( 현재 금액 / SUM ( 전체 금액 범위 ), 2 ) )
인수 설명 자세히 보기
인수구분설명
마지막 항목필수현재 수식이 잔여 비중을 넣을 마지막 행인지 ROW 함수로 판별할 때 참조하는 항목명 셀입니다.
이전 비중 누계필수수식을 아래로 채울수록 끝이 한 행씩 늘어나며, 첫 행의 머리글 문자는 SUM 함수가 합계에서 제외하는 범위입니다.
현재 금액필수반올림할 현재 행의 금액 셀입니다.
전체 금액 범위필수아래로 채워도 움직이지 않도록 절대참조로 고정한 모든 항목의 금액 범위입니다.
C2=IF(ROW()=ROW($A$8),1-SUM($C$1:C1),ROUND(B2/SUM($B$2:$B$8),2))
C3=IF(ROW()=ROW($A$8),1-SUM($C$1:C2),ROUND(B3/SUM($B$2:$B$8),2))
C4=IF(ROW()=ROW($A$8),1-SUM($C$1:C3),ROUND(B4/SUM($B$2:$B$8),2))
C5=IF(ROW()=ROW($A$8),1-SUM($C$1:C4),ROUND(B5/SUM($B$2:$B$8),2))
C6=IF(ROW()=ROW($A$8),1-SUM($C$1:C5),ROUND(B6/SUM($B$2:$B$8),2))
C7=IF(ROW()=ROW($A$8),1-SUM($C$1:C6),ROUND(B7/SUM($B$2:$B$8),2))
C8=IF(ROW()=ROW($A$8),1-SUM($C$1:C7),ROUND(B8/SUM($B$2:$B$8),2))
A
B
C
D
1
부서
예산액
비중
2
영업팀
255,000,000
26%
3
개발팀
184,000,000
18%
4
운영팀
153,000,000
15%
5
기획팀
122,000,000
12%
6
마케팅팀
111,000,000
11%
7
인사팀
98,000,000
10%
8
재무팀
77,000,000
8%
9
합계
1,000,000,000
100%
10
앞의 여섯 비중을 1% 단위로 반올림하면 합계가 92%입니다. 마지막 항목에는 남은 8%를 넣어 전체 합계를 정확히 100%로 맞춥니다.
이 공식이 사용하는 함수
SUM 함수 IF 함수 ROW 함수 ROUND 함수

동작 원리

02반올림한 비중의 합계를 100%로 맞추기

IF 함수가 마지막 항목 전까지 비중을 반올림하고, 마지막 항목에는 100%에서 앞 행의 비중 합계를 뺀 값을 넣습니다.

예제 시트에서 영업팀부터 재무팀까지 비중을 계산하는 C2:C8 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

현재 금액을 전체 금액으로 나눕니다

영업팀 예산 255,000,000원을 전체 예산 1,000,000,000원으로 나누면 반올림 전 비중은 25.5%입니다.

=B2 / SUM ( $B$2:$B$8 )
= 25.5%
2

마지막 항목 전까지 1% 단위로 반올림합니다

ROUND 함수로 소수 둘째 자리까지 남기면 백분율 서식에서 25.5%가 26%가 됩니다. 같은 방법으로 앞의 여섯 항목을 계산하면 합계는 92%입니다.

=ROUND ( B2 / SUM ( $B$2:$B$8 ), 2 )
= 26%
3

마지막 항목에 남은 비중을 넣습니다

재무팀 행에서는 100%에서 C2:C7의 합계 92%를 뺍니다. 마지막 비중은 8%가 되어 전체 합계가 100%로 맞춰집니다.

=1 - SUM ( $C$1:C7 )
= 8% 마지막 항목의 원래 비중과 차이가 지나치게 커지면 표시 자릿수를 늘려 반올림 차이를 줄입니다.
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료