엑셀 숨겨진 셀 빼고 수식을 계산하는 방법
엑셀에서 수식을 계산할 때 셀을 범위로 지정하게 되면 필터를 걸거나, 셀을 숨겨도 모두 포함되어 계산된다.
이러한 것이 싫다면 수식에서 범위를 지정할 할 때 직접 숨겨진 셀을 빼고 지정하거나 subtotal 함수를 사용하여 계산하면 되는데 범위를 직접 지정하는 것은 힘들고 귀찮을 수 있기 때문에 되도록이면 subtotal 함수를 사용하는 것이 좋다.
목차 (Contents)
엑셀 숨겨진 셀 빼고 수식을 계산하기
1. subtotal 함수.
데이터를 요약하고 총계를 계산하는 함수.
필터로 숨겨진 셀이나 사용자가 숨긴 행이나 열이 있다면 이를 빼고 수식을 계산할 수 있다.
함수의 기본 사용 방법은 아래와 같다.
| =SUBTOTAL(계산 옵션, 범위) |
계산 옵션은 1에서 9와 101에서 109가 있다.
얼핏 보면 같아 보이지만 1에서 9를 선택하고 계산하면 필터로 숨겨진 셀만 빼고 계산하며 사용자가 숨긴 행이나 열은 포함해서 계산한다.
101에서 109는 필터뿐만 아니라 사용자가 숨긴 행이나 열까지 빼고 화면에 보이는 셀만 계산한다.
2. 일반 함수와 subtotal 옵션 별 비교.
일반 함수와 subtotal 옵션이 어떻게 다른지 보기 위해 엑셀에서 사과, 배, 딸기의 가격의 합을 구하는 간단한 표를 작성해 봤다.
표를 보면 필터나 숨긴 행이 없을 때는 합계가 모두 같은 것을 확인할 수 있을 것이다.
여기서 이름의 필터 값을 사과로 변경해 봤다.
[일반 합계]는 그대로 지만 [subtotal]을 사용하여 합계를 구한 부분은 합계가 변경된 것을 볼 수 있다.
이번에는 필터를 풀고 6번, 7번 행을 숨겨봤다.
[일반 합계]와 [subtotal 옵션 9]의 합계는 그대로 지만 [subtotal 옵션 109]의 합계만 변경된 것을 볼 수 있다.
3. 필터와 행과 열 숨기기를 둘 다 적용한 경우.
그렇다면 필터를 적용한 상태에서 행을 숨겨보면 어떻게 될까?
[옵션 9]는 필터로 숨겨진 값만 빼고 계산되고, [옵션 109]는 필터와 행, 열 모두 숨겨진 값을 빼고 계산하기 때문에 결과가 다를 거라고 생각할 수 있다.
하지만 필터 값을 사과로 변경하고 7번 행을 숨겨 보면 subtotal 모두 합계가 변경되는데 필터를 적용한 범위는 행이나 열을 숨겨도 필터에서 숨긴 것으로 취급된다고 보면 되지 않을까 한다.
그 외 내용
subtotal는 필터나 행과 열 등 숨겨진 셀을 빼고 계산할 수 있다.
다만 아쉬운 점은 계산 옵션에서 지원되는 함수가 제한되어 있다는 것인데 이런 부분은 어쩔 수 없으니 수식에서 계산 범위를 직접 입력하거나 보이는 셀만 선택하여 복사한 뒤 따로 계산하는 등 다른 방법을 찾아보는 것이 어떨까 한다.