병합된 셀 부분합계 구하기

영상 강의

함수 공식

강의 요약

셀 병합이 적용된 범위에서는 일반적인 자동 채우기를 사용할 수 없습니다. 병합된 셀의 넓이가 모두 동일한 경우에만 자동 채우기가 정상 동작하며, 넓이가 서로 다른 경우에는 아래와 같이 '이 작업을 수행하려면 병합하려는 모든 셀의 크기가 동일해야 합니다'라는 오류가 발생하면서 작업이 중단됩니다.

따라서 이번 강의에서 다루는 병합된 셀 부분합 구하기 또한 일반적인 방법으로는 자동 채우기가 불가능한 셀 병합 환경에서 부분합을 한 번에 계산하기 위한 대안으로 활용됩니다.

이전 '셀병합의 모든것' 강의에서도 설명드렸듯이, '셀 병합'은 엑셀에서 계륵과 같은 기능입니다. 셀 병합은 서식을 정돈할 때는 유용하지만, '자동 채우기', '필터', '정렬' 등 엑셀의 핵심 기능 사용을 제한하여 데이터 관리 도구로서의 활용도를 크게 떨어뜨립니다. 셀 병합의 문제점과 해결 방법에 대한 자세한 설명은 아래 '셀병합의 모든것' 기초 강의를 참고하세요.

1. 부분합계를 출력할 셀 범위를 선택합니다. 이때 맨 위쪽 셀이 활성 셀이 되도록 선택합니다.

   

2. 아래 공식을 이용하여 수식을 입력합니다.
   =SUM(합계를구할범위)-SUM(부분합출력범위+1행)
   ' 합계를 구할 범위가 [ D5:D16 ]이라면 부분합 출력 범위는 출력 범위에서 각 1행씩 더한 [ E6:E17 ]로 입력합니다.

   

3. 키보드 Ctrl + Enter 키를 눌러 수식을 일괄 입력합니다.
   Ctrl + Enter로 수식을 입력하면 선택된 범위 전체에 동일한 수식이 한 번에 입력됩니다.

   

4. 부분합계가 계산된 범위를 복사한 뒤, '선택하여 붙여넣기' - '값' 형태로 붙여넣기합니다.

   

5. 이것으로 병합된 셀의 부분합계 계산이 완료됩니다.

   

병합된 셀 부분합 구하기 공식의 동작원리

1. 이 공식의 핵심 원리는 '병합된 셀에서는 맨 위쪽 첫 번째 셀에만 값이 입력·계산된다'는 점입니다.

   

2. 먼저 [ =SUM(합계를구할범위) ]를 병합된 셀에 입력하면, 병합된 셀의 첫 번째 셀을 기준으로 아래와 같이 합계가 계산됩니다.

   

3. 다음으로 [ =SUM(부분합출력범위+1행) ]은 합계를 구하는 대상이 다릅니다. 이 부분은 '병합된 셀', 즉 출력 범위 자체의 합계를 계산합니다.
   아래 그림처럼 셀 병합이 된 범위 [ D4:D9 ]의 합계를 구하면, 실제로 어떤 값이 계산될까요?

   

   정답은 800입니다. 실제 합계로 계산되는 범위는 '셀 병합이 시작된 첫 번째 셀'을 기준으로 하므로 [ D6:D9 ]의 합계인 800만 계산됩니다. [ D4:D5 ]는 외관상 병합된 것처럼 보이지만, 병합된 영역에서 첫 번째 셀을 제외한 나머지 셀은 실제로 비어 있는 셀이므로 계산에 반영되지 않습니다.

4. 따라서 [ =SUM(부분합출력범위+1행) ]의 계산 결과는 아래와 같이 출력됩니다.

   

5. 최종적으로 [ =SUM(합계를구할범위)-SUM(부분합출력범위+1행) ]의 계산 결과는 아래와 같이 정리되며, 병합된 셀 부분합 구하기 공식이 마무리됩니다.

   

자주묻는질문

자주묻는질문1. 출력 범위 아래쪽에도 값이 있어 위 방법을 그대로 사용할 수 없을 경우

출력 범위 아래쪽에 다른 값이 입력되어 있어 위 공식을 그대로 사용할 수 없는 경우에는, 합계를 구할 범위와 부분합 출력 범위의 마지막 셀을 절대 참조로 입력하면 문제를 해결할 수 있습니다.

예를 들어 예제 파일에서 사용한 공식을 아래와 같이 수정하면, 출력 범위 아래에 어떤 값이 입력되어 있더라도 병합된 범위의 부분합을 한 번에 계산할 수 있습니다. (내용을 다시 한번 짚어주신 찰스님께 감사의 말씀 드립니다.)