메뉴
실무 위키›응용 공식›엑셀 병합된 셀 기준으로 합계 구하는 공식

엑셀 병합된 셀 기준으로 합계 구하는 공식

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

병합 표를 유지한 채 부서별 지출액을 정확히 합칩니다.

예제파일 다운로드
엑셀 병합된 셀 기준으로 합계 구하는 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

01부서명이 적힌 첫 행만 합산하기

=SUMIF ( 부서 범위, 집계 부서, 지출액 범위 )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
부서 범위필수병합된 부서명이 각 그룹의 첫 행에만 남고 아래 행은 빈 셀인 범위입니다.
집계 부서필수합계를 구할 부서명이 입력된 셀입니다. 부서 범위의 표기와 띄어쓰기까지 같아야 하는 값입니다.
지출액 범위필수부서별로 합칠 지출액이 입력된 범위입니다. 부서 범위와 시작·끝 행이 같아야 하는 범위입니다.
E2=SUMIF($A$2:$A$13,D2,$B$2:$B$13)
A
B
C
D
E
F
G
1
부서
지출액
집계 부서
일반 합계
병합 셀 기준 합계
2
영업팀
120,000
영업팀
120,000
250,000
3
80,000
개발팀
200,000
500,000
4
50,000
관리팀
90,000
200,000
5
개발팀
200,000
기획팀
70,000
100,000
6
150,000
7
100,000
8
50,000
9
관리팀
90,000
10
60,000
11
50,000
12
기획팀
70,000
13
30,000
14
부서 열은 병합된 표의 저장 형태처럼 그룹 첫 행에만 값이 있고 아래 행은 비어 있습니다. 일반 합계는 영업팀이 적힌 첫 행의 120,000원만 더하므로 그룹 전체 합계와 다릅니다.

02빈 부서 셀을 위 부서명으로 이어 합산하기

=SUMPRODUCT ( ( LOOKUP ( ROW ( 지출액 범위 ), ROW ( 부서 범위 ) / ( 부서 범위 <> "" ), 부서 범위 ) = 집계 부서 ) * 지출액 범위 )
인수 설명 자세히 보기
인수구분설명
지출액 범위필수부서별로 합칠 지출액이 입력된 범위입니다. 부서 범위와 시작·끝 행이 같아야 하는 범위입니다.
부서 범위필수병합된 부서명이 각 그룹의 첫 행에만 남고 아래 행은 빈 셀인 범위입니다.
집계 부서필수합계를 구할 부서명이 입력된 셀입니다. 부서 범위의 표기와 띄어쓰기까지 같아야 하는 값입니다.
F2=SUMPRODUCT((LOOKUP(ROW($B$2:$B$13),ROW($A$2:$A$13)/($A$2:$A$13<>""),$A$2:$A$13)=D2)*$B$2:$B$13)
A
B
C
D
E
F
G
1
부서
지출액
집계 부서
일반 합계
병합 셀 기준 합계
2
영업팀
120,000
영업팀
120,000
250,000
3
80,000
개발팀
200,000
500,000
4
50,000
관리팀
90,000
200,000
5
개발팀
200,000
기획팀
70,000
100,000
6
150,000
7
100,000
8
50,000
9
관리팀
90,000
10
60,000
11
50,000
12
기획팀
70,000
13
30,000
14
부서 셀이 빈 행마다 가장 가까운 위쪽 부서명을 수식 안에서만 이어 적용합니다. 영업팀의 세 지출액 120,000원, 80,000원, 50,000원을 모두 더한 결과는 250,000원입니다.
이 공식이 사용하는 함수
SUMIF 함수 SUMPRODUCT 함수 LOOKUP 함수 ROW 함수

동작 원리

02빈 부서 셀을 위 부서명으로 이어 합산하기

ROW 함수가 부서명이 적힌 행 위치를 만들고, LOOKUP 함수가 빈 부서 셀마다 가장 가까운 위 부서명을 반환한 뒤 SUMPRODUCT 함수가 선택한 부서의 지출액만 합합니다.

예제 시트에서 영업팀의 병합 셀 기준 합계를 구하는 F2 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

부서명이 적힌 행 위치를 남깁니다

부서명이 있는 행은 자신의 행 번호를 남기고 부서 셀이 빈 행은 오류가 됩니다. LOOKUP 함수는 다음 단계에서 숫자로 남은 위치를 기준으로 사용합니다.

=ROW ( $A$2:$A$13 ) / ( $A$2:$A$13 <> "" )
= {2;#DIV/0!;#DIV/0!;5;#DIV/0!;#DIV/0!;#DIV/0!;9;#DIV/0!;#DIV/0!;12;#DIV/0!}
2

빈 부서 셀에 위 부서명을 이어 적용합니다

각 지출 행 번호에서 마지막으로 확인되는 부서명 위치를 찾습니다. 부서 셀이 빈 행도 가장 가까운 위쪽 부서명으로 계산되어 열을 추가하지 않아도 모든 행에 부서가 대응합니다.

=LOOKUP ( ROW ( $B$2:$B$13 ), ROW ( $A$2:$A$13 ) / ( $A$2:$A$13 <> "" ), $A$2:$A$13 )
= {영업팀;영업팀;영업팀;개발팀;개발팀;개발팀;개발팀;관리팀;관리팀;관리팀;기획팀;기획팀}
3

집계할 부서와 같은 행을 가립니다

이어 적용한 부서명 배열을 D2의 영업팀과 비교하면 앞의 세 행만 TRUE가 됩니다.

=LOOKUP ( ROW ( $B$2:$B$13 ), ROW ( $A$2:$A$13 ) / ( $A$2:$A$13 <> "" ), $A$2:$A$13 ) = D2
= {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
4

해당 부서의 지출액을 모두 더합니다

TRUE인 세 행의 120,000원, 80,000원, 50,000원만 남겨 더하면 영업팀 합계는 250,000원입니다.

=SUMPRODUCT ( ( LOOKUP ( ROW ( $B$2:$B$13 ), ROW ( $A$2:$A$13 ) / ( $A$2:$A$13 <> "" ), $A$2:$A$13 ) = D2 ) * $B$2:$B$13 )
= 250,000
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료