넓이가 다른 병합된 셀 부분합, 한번에 쉽게 구하는 방법
병합된 셀 부분합 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 병합된 셀 부분합 구하기예제파일
함수 공식
=SUM(합계를구할범위)-SUM(부분합출력범위+1행)강의 요약
셀 병합이 된 범위를 자동채우기 할 경우, 병합된 셀의 넓이가 반드시 동일해야만 오류없이 자동채우기를 할 수 있습니다. 그렇지 않을경우, 아래와 같이 '이 작업을 수행하려면 병합하려는 모든 셀의 크기가 동일해야합니다' 라는 오류가 발생하면서 진행을 할 수 없게 됩니다.
따라서, 이번 강의에서 알아볼 병합된 셀 부분합 구하기 또한 일반적인 방법으로는 자동채우기가 불가능한 셀병합 문제를 해결하기 위한 하나의 대안책이 되겠습니다.
자동채우기 작업은 병합된 셀의 넓이가 동일할 경우에만 가능합니다. 이전 '셀병합의 모든것' 강의에서도 설명을 드렸지만, '셀병합'은 엑셀의 '계륵과 같은 존재'라고 볼 수 있습니다. 셀병합은 엑셀로 서식을 작성할 때 필요한 기능이면서도, 엑셀의 중요기능인 '자동채우기', '필터', '정렬' 등의 사용을 제한하면서 '데이터 관리 프로그램'으로써의 기능을 상당히 축소하게 됩니다. '셀병합'의 문제점과 해결방법에 대한 자세한 설명은 아래 '셀병합의 모든것' 기초 강의를 참고하세요.
이번 강의에서는, '병합된 셀의 부분합계'를 구할 때, 병합된 셀의 넓이가 다르더라도 사용할 수 있는 '병합된 셀 부분합계 구하기' 공식의 사용법과 동작원리를 단계별로 알아보겠습니다.
- 부분합계를 출력할 셀 범위를 선택합니다. (맨 윗쪽 셀이 활성화 되도록 선택합니다.)
맨 윗셀이 활성화 된 상태로, 부분합계를 출력할 범위를 모두 선택합니다. - 아래 공식을 이용하여 수식을 입력합니다.
=SUM(합계를구할범위)-SUM(부분합출력범위+1행)
' 합계를 구할범위가 [ D5:D16 ]이라면 부분합출력범위는 출력범위에서 각 1행씩 더한 [ E6:E17 ]로 입력합니다.
부분합계 구하기 공식을 입력합니다. - 키보드 Ctrl + Enter 키로 수식을 입력합니다.
Ctrl + Enter 로 수식을 입력하면 선택된 범위 안에 수식이 한번에 입력됩니다.
입력한 수식은 Ctrl + Enter 로 입력합니다. - 부분합계를 구한 범위를 복사하여, '선택하여 붙여넣기' - '값' 형태로 붙여넣기합니다.
출력된 값을 복사하여 '값' 형태로 선택하여 붙여넣기 합니다. - 병합된 셀 부분합계 계산이 완료되었습니다.
병합된 셀 부분합 구하기가 완료되었습니다.
병합된 셀 부분합 구하기 공식의 동작원리
- 이번 공식에서 사용하는 핵심 원리는, '병합된 셀은 맨 위쪽 첫번째 셀에만 값이 입력/계산된다' 입니다.
병합된 셀의 실제 값은 맨 윗쪽 첫번째 셀에만 입력됩니다. - 첫번째, [ =SUM(합계를구할범위) ]를 병합된 셀에 입력하면, 병합된 셀의 첫번째 셀을 기준으로 아래와 같이 합계가 계산됩니다.
매출 합계를 아래로 자동채우기하면, 병합된 셀 기준으로 아래쪽 범위의 합계가 계산됩니다. - 두번째, [ =SUM(부분합출력범위+1행) ]는 합계를 구하는데, 이전과는 다르게 '병합된 셀', 즉 출력할 범위의 합계를 구합니다.
아래 그림처럼, 셀병합이 된 범위인 [ D4:D9 ] 합계를 구하면, 실제 합계는 얼마가 계산될까요?
병합된 셀의 비어있는 부분은 계산결과에 반영되지 않습니다. 네 맞습니다. 실제 합계로 계산되는 범위는 '셀 병합이 시작된 범위'를 기준으로 [ D6:D9 ] 의 합계인 800이 계산됩니다. [ D4:D5 ]는 병합된 것처럼 보이지만 실제로는 비어있는 셀이므로 계산에 반영되지 않는 것 이지요.
- 따라서 [ =SUM(부분합출력범위+1행) ]의 계산결과는 아래와 같이 출력됩니다.
따라서 병합된 셀을 참조하여 계산한 결과는 위와 같이 출력됩니다. - 최종적으로, [ =SUM(합계를구할범위)-SUM(부분합출력범위+1행) ] 의 계산결과는 아래와 같이 계산되어 병합된 셀 부분합 구하기 공식이 마무리 됩니다.
병합된 셀 부분합 구하기 공식이 완료되었습니다.
자주묻는질문
자주묻는질문1. 출력범위 아래로 값이 있어서 위 방법을 사용할 수 없을 경우
출력범위 아래로 값이 있어서 위 방법을 사용할 수 없을 경우, 합계를 구할 범위와 부분합 출력범위의 마지막 셀을 절대참조로 입력하면 문제를 해결할 수 있습니다.
예를들어 예제파일에서 사용된 공식을 아래와 같이 수정하면 출력범위 아래로 입력된 값에 상관없이 병합된 범위의 부분합을 한 번에 계산할 수 있습니다. (내용을 다시 한번 짚어주신 찰스님께 감사의 말씀 드립니다)
=SUM(D5:$D$16) - SUM(E6:$E$16)
'범위의 마지막셀을 절대참조합니다.
'부분합을 계산할 범위 바로 밑에 다른 값이 있을 경우, 부분합범위의 마지막셀을 합계범위와 동일하게 맞춰줍니다. - 부분합계를 출력할 셀 범위를 선택합니다. (맨 윗쪽 셀이 활성화 되도록 선택합니다.)
신기함이~~~
그래서 자동채우기 하려고 하면 셀의 크기가 동일해야 한다고 나오는 데 왜이럴까요 ㅠㅠ?
Ctrl+Enter가 되지 않는다는게 정확히 어떻게 안되시나요?
Ctrl+Enter는 선택한 범위에 함수를 한번에 입력하는 단축키라서, 넓게 범위를 먼저 선택하신 뒤, 수식을 입력하셔야 합니다.
다시 확인해주시겠어요?^^
감사합니다.
좀 더 정확한 상황설명을 해 주시겠어요?^^
위 내용과 관련하여 SUM의 ARRAY지정시 합계할 마지막 행에 절대참조를 해야 추후에 표 아랫부분에 추가적인 숫자 데이터가 입력되어도 변동없이, 의도한대로 계산이 적용될 것으로 보입니다 !
혹시 다른 의도가 있어서 상대 참조로 두신건지요??
네 말씀하신 내용이 정확합니다.
범위의 마지막행에 절대참조를 걸어주시면, 아랫부분에 데이터 변동에 상관없이 더 정확하게 적용할 수 있습니다.
엑셀프레소 강의이다 보니 간결하게 설명드리느라 미처 짚고넘어가지 못했네요..! 내용 보충해주셔서 감사드립니다.
이후에 내용을 다룰 기회가 된다면, 그때는 꼭 짚고 넘어가도록 하겠습니다.
감사합니다