실전에서 바로 쓰는 엑셀 기능과 단축키 - 필터와 피벗테이블 편

미국 마이크로소프트에서 만든 MS Office 프로그램의 하나인 엑셀(Excel)은 전세계의 수많은 학생들과 직장인들이 이용하고 있는 MS의 킬러 컨텐츠입니다.

 

 

우리나라에서도 대학생이나 직장인, 사업자 분들에게는 데이터를 관리할 때 거의 필수적으로 이용하는 소프트웨어라고 할 수 있는데요,

 

저는 엑셀을 처음 익힐 때 엑셀을 알려주는 시중 서적은 양이 너무 방대하고 인터넷에 올라와있는 정보들은 이미 알고있는 기초적인 내용만 되풀이하는 경향이 있어 답답했던 경험이 있습니다.

 

 

이에 엑셀을 쉽고 빠르게 만들어주는 여러 단축키와 기능들, 특히 실전에서 당장 써먹고 과제시간을 줄여주거나 퇴근시간을 앞당겨줄 수 있는 꿀팁들을 알려 드리려고 합니다.

 

 

오늘은 그 중 "필터"기능과 "피벗테이블" 에 대하여 소개해 드리겠습니다.

 

 

[필터 기능]

 

필터 기능은 데이터베이스에서 내가 원하는 특정 기준에 해당하는 행만을 추출하여 보고 싶을때 이용하는 기능입니다.

 

예시를 통해서 살펴보겠습니다.

 

다음과 같은 데이터가 있을 때 조별과제 팀 3조 조원들만을 추려보고 싶은 경우가 있습니다.

 

 

이때 필터기능을 이용하면 매우 편리한데요, 데이터베이스를 블럭 설정한 뒤 Alt + A + T 를 누르면 다음과 같이 각각의 열 항목 오른쪽에 삼각형 표시가 나타나게 됩니다. 위와 같이 빈칸이 없이 연속적으로 이어진 데이터베이스에서는 블럭 설정도 필요 없이 아무 셀에서나 Alt + A + T를 누르면 엑셀이 자동으로 데이터베이스 범위를 인식하여 필터를 생성합니다.

 

 

위 사진에서 "조별과제 팀" 옆의 삼각형을 클릭해서 '3'에 체크표시한 후 확인을 누르면 다음과 같이 3조 조원들에 대한 정보만 추려지는데요, 마우스 클릭을 하기보다는 "조별과제 팀" 셀에서 Alt + ↓ 와 스페이스바를 이용하여 키보드로 작업하시는게 훨씬 더 능률적입니다.

 

 

(키보드 연습) Alt + ↓ 를 누르고 밑으로 내려가서 (모두 선택)에서 스페이스바 한번, "3"에서 스페이스바 한번, 엔터

 

필터를 풀고 싶을 때에는 단축키 Alt + A + C 를 눌러주시면 바로 원상태로 돌아옵니다.

 

 

[SUBTOTAL 함수]

 

필터 기능과 자주 쓰이는 함수가 있는데요, 바로 SUBTOTAL 함수입니다.

 

위와 같이 필터로 추려낸 사람들만의 중간고사 점수 총합이나 평균을 구할 때 쓰이는데요,

 

일반적인 총합 함수인 SUM이나 평균 함수인 AVERAGE를 이용하면 현재 필터에서 보이지 않는 데이터까지 계산하기때문에 오류가 나게 됩니다.

 

즉, 다음과 같이 출석번호 1부터 20까지 sum 함수와 subtotal 함수를 이용하여 총합을 구해보면 현재는 필터가 걸려있지 않고 모든 학생이 보이기 때문에 같은 결과값을 보여주지만

(참고로 SUBTOTAL(9,범위) 로 입력해야 총합 결과를 산출합니다. SUBTOTAL(1,범위)는 평균입니다.)

 

 

다음과 같이 3조만을 필터로 추려냈을 때, subtotal 함수는 현재 보이는 네 명만의 점수를 합산하여 보여주고, sum함수는 여전히 전체 범위의 합계결과를 결과값으로 보여줍니다.

 

보통 필터를 걸어서 살펴볼 때 합계와 평균을 확인할 일이 많으므로, 데이터베이스에 필터를 걸었을 때 습관적으로 위쪽에 SUBTOTAL 함수를 이용하여 미리 합계액과 평균 칸을 만들어두면 다음과 같이 빠르게 확인할 수 있습니다.

 

<기본 세팅>

 

<17학번 정보>

 

<경영학과 남학생 정보>

<조별과제 점수가 85점인 학생들의 정보>

 

 

 

[피벗테이블]

 

위와 같이 매번 필터를 걸어 확인하지 않고 한 범주(ex: 소속학과) 각각의 데이터를 한 눈에 확인하고 싶은 경우 이용할 수 있는 기능이 바로 피벗테이블 기능입니다.

 

 

예시로 살펴보는 것이 훨씬 이해가 빠르겠죠, 필터를 풀고 데이터베이스 전체를 블럭 설정한 후

 

Alt + N + V 키를 누르고 확인을 누르면 다음과 같이 피벗테이블이 생성됩니다.

 

여기서 "소속학과"를 "행"으로 드래그하고 "중간고사 점수"를 "값"으로 드래그하면 다음과 같이 학과별 점수 총합을 한눈에 볼 수 있습니다.

 

 

아무래도 학과 별 인원 수가 제각각이니 평균 점수가 좀더 의미있는 데이터겠죠?

 

"값"에 드래그해둔 "합계 : 중간고사 점수"를 클릭하여 "값 필드 설정"을 클릭하시고, "평균"을 클릭하면 다음과 같이 소속 학과별 중간고사 점수 평균 값을 정리하여 보여줍니다.

 

 

피벗테이블의 행 범주는 다음과같이 여러 항목을 중첩시킬 수도 있습니다.

 

 

또한 "행 레이블" 옆의 삼각형을 눌러 필터 기능을 이용할 수도 있습니다. 삼각형을 클릭한 뒤 "필드 선택"에서 "학번"을, 필터 항목으로 17, 18, 19학번을 누르면 다음과 같이 보고서가 출력됩니다.

 

 

피벗테이블은 사용자의 숙련 정도에 따라 굉장한 잠재력을 지닌 엑셀의 분석도구입니다. 제가 이 포스팅에서 알려드리는 부분은 빙산의 일각이라고 해도 무방할 정도로 많은 기능을 갖고 있으니, 엑셀을 이용한 데이터 분석에 관심이 있으신 독자 분들은 피벗테이블을 자주 이용해 보시길 권장드립니다.

 

 

 

이상으로 엑셀의 대표적인 데이터 분석 도구, 필터와 피벗 테이블에 대한 포스팅을 마치겠습니다.

 

긴 글 읽어주셔서 감사드리며, 독자 분들의 엑셀 효율 향상에 조금이나마 도움이 되었길 진심으로 기원합니다.

 

 

 

설명용으로 제작한 예제파일을 첨부드립니다.

 

예제파일.xlsx

0.12MB