MS 엑셀에서 숨겨진 셀은 제외하고 합산하기

배경

엑셀은 정말 무궁무진하게 많은 기능들이 포함되어 있습니다. 지원되는 함수만 해도 엄청나게 많고 심지어 함수를 얼마든지 만들어서 사용할 수도 있으니까요. 그렇기 때문에 대부분의 엑셀 사용자 분들은 표를 만들고 합계, 평균, 표준편자 등 구하고 그래프 그리고 정도로 사용하실 겁니다. 저도 특별한 일 없으면 엑셀은 그저 표그리는 도구정도로만 사용합니다.

오늘 포스팅할 내용은 바로 앞에서 말한 정도의 깊이로만 엑셀을 사용하시는 대부분의 사용자들에게는 유용할 그렇지만 정말 간단한 그런 팁 되겠습니다.

예제용 Data 선택 (방탄소년단 앨범 판매량)

먼저 오늘의 팁을 설명하는데 예제로 사용하기 위해서 사용할 데이터를 하나 가져왔습니다. 바로 방탄소년단의 앨범 판매량 데이터입니다. 

2019년 6월 발표 가온차트 BTS 앨범 누적 판매량

이슈사항

엑셀 사용하시면 데이터의 합계를 내는 일이 정말 많습니다. 보통 "=SUM( )" 이라는 함수를 사용해서 주어진 범위의 데이터의 합산을 많이 하는데요. 그것보다 단순히 합산값 확인용도로 마우스로 데이터들을 주욱 긁어서 합산값 확인하는 기능도 많이 사용합니다.

다음과 같이 전체 엘범의 누적 판매량을 알고싶은 경우 간단하게 엘범 판매량이 저장되어 있는 셀 전체범위를 드래그해서 선택만 해주면 프로그램 하단에 그 합계 뿐만 아니라 총 데이터 개수와 평균값까지 바로 보여줍니다. 데이터 작업하실 때 검산용으로 많이들 사용하시죠? (우와~ BTS 앨범이 전부 천오백만장이 넘게 팔렸네요. 정말 대단합니다)

자 여기서 BTS앨범들을 보니 형태분류가 앨범, EP, 싱글 이렇게 3가지 종류가 있는걸로 나옵니다. 여기서 앨범이라고 분류가된 정규앨범의 총 판매량을 알고 싶어서 앨범이라고 된 행을 제외하고 나머지는 모두 숨기기를 했습니다. 그리고 판매량 부분을 모두 선택을 하면 위와 마찬가지로 정규앨범만의 판매량을 알수 있습니다. 

이경우 엑셀은 숨겨진 셀은 자동적으로 무시하고 알아서 내가 알고 싶은 결과값을 보여줍니다. 

그런데 말입니다!!

만약 데이터가 위에서 본것 처럼 세로방향으로 정리된것이 아니라 가로방향이라면 어떨까요?

이 표는 똑같은 표를 방향만 세로에서 가로로 변환한것입니다.

전체 합계를 보기위해서 아래 그림과 같이 판매량을 다 선택해보면 당연히 세로일때와 똑같은 값이 표시됩니다.

자 재미있는건 여기서 부터입니다.

똑같이 정규앨범의 누적 판매량을 알고싶어서 나머지 열들을 숨기기 처리한 후 합계를 보면 어떨까요?

어라?! 숫자가 뭔가 이상하죠?

분명 데이터의 개수는 'Heartbeat, IDOL, FAKE LOVE, 봄날, 피 땀 눈물, 불타오르네, Danger" 이렇게 7개인데 11개라고 나옵니다. 그렇습니다. 바로 'Heartbeat' 앨범이 있는 B열부터 'Danger'앨범이 있는 L열까지 숨겨진 열을 모두 포함해서 계산을 해 주고 있기 때문에 그렇습니다.

저는 처음에 이런 사실을 몰라서 데이터를 확인하던중에 엑셀에 버그가 있는 줄 알고 한참 당황했던 적이 있었습니다.

드래그를 하지말고 컨트롤 키를 사용해서 눈에 보이는 셀을 하나씩 눌러서 더하고 싶은 7개 셀만 선택해 보면,

이렇게 정확하게 7개의 합계와 평균값을 보여주게 됩니다.

엑셀의 함수로 표현하자면 다음과 같은 차이가 있는 것이죠.

그렇다면 가로로 된 데이터는 검산을 위해서는 이렇게 하나하나씩 셀을 눌러주어야 할까요? 만약 확인해야 할 열이 7개가 아니라 70개 아니면 700개라면 700번 클릭을 해야 할까요?

아닙니다. 엑셀에는 선택범위에서 숨겨진셀은 선택에서 해제시켜주는 단축키가 있습니다. (물런 "SUBTOTAL"이라는 함수도 있으나 그건 이번 주제와 그 용도가 다르니  패스~)

결론

바로, 알트키와 세미콜론 조합니다.

자 원하는 셀을 주~욱 선택하고 가볍게 알트 + 세미콜론 해 보세요. 선택영역이 숨겨진 셀이 있는 부분을 기점으로 분리가 되는것을 볼 수 있습니다.

복사 붙여넣기 할때도 이 기능은 아주 유용하게 사용 될 수 있으니 꼭 기억해두시고 사용해 보세요. ^^

 

끝!