엑셀 피벗 테이블, 필터 간단 정리

시작에 앞서 고백하자면, 필자는 엑셀을 데이터베이스로만 많이 사용하기 때문에, 세부적인 기능까지는 잘모른다.

 

물론, 구글링이 가능하다는 가정하에 일반인(컴퓨터에 대해 공부해본적 없는)보다 훨씬 더 퀄리티있는 성과를 낼 수 있다.

 

필자가 전에 업로드한 엑셀 함수 정리와 이번에 업로드한 피벗 테이블, 필터만 제대로 봐도 대부분의 사무업무는 막힘없이 수월하게 할 수 있으며, 앞으로 올릴 VBA와 Python을 사용한 사무자동화 프로그램 만드는 법, 알고리즘 강의에 대해 관심을 가진다면 장담하는데 지금 컴퓨터로 하고 있는 반복작업들이 사라져 컴퓨터 관련 업무시간이 적어도 1/3로 줄어들 것이다.

 

필자를 소개하자면, 필자는 엑셀 함수 공부로 시작하여 C언어를 공부하며 프로그래밍 언어의 원리를 깨우쳤고 Python을 공부하여 알고리즘 수준을 높였으며, 컴퓨터 관련 하드웨어에 대한 원리도 어느정도 인지하고있다. 또한 지금은 팀을 꾸려 Java언어를 통한 Spring 프레임워크 백엔드 프로젝트를 진행중이다.

 

더욱 발전해야하고 아직 공부할게 많지만, 그래도 조금이나마 사람들에게 보탬도되고 덩달아 실력도 키우고 싶어 블로그를 작성한다.

 

사담이 길었는데, 사실 피벗 테이블에 대한 설명과 필터에 대한 설명은 앞서 말한 사담보다 짧다.

 

1. 피벗 테이블

[그림 1] 학교 성적 데이터

예를들어 [그림 1]과 같은 데이터를 피벗 테이블로 정리하고 싶다면

[그림 2] 피벗 테이블 위치

표 내부 아무 셀이나 클릭하고(그러면 피벗 테이블을 만들 데이터 범위가 자동으로 표의 크기로 지정이 되는데, 피벗 테이블 창이 열리면 데이터 범위를 따로 수정할 수 있어서 필수 사항은 아니다.)

 

삽입 -> 피벗 테이블을 클릭한다.

[그림 3] 피벗 테이블 생성

그러면 [그림 3]과 같은 창이 뜨는데 위의 범위는 피벗 테이블을 만들 때 사용할 데이터 범위(항목 포함 시켜야됨), 아래 창은 피벗 테이블을 생성할 위치이다.

 

새 워크시트에 체크가 되어있으면 자동으로 새로운 워크시트가 생성되며 피벗 테이블을 생성할 수 있고

기존 워크시트를 클릭하여 범위를 지정할 수 도 있다.

[그림 4]새 워크시트에 피벗 테이블 생성

모든 설정이 끝나고 확인을 누르면 피벗 테이블 필드라는 것이 우측에 뜨게 되는데, 항목을 필터, 열, 행, 값에 넣어 자기가 원하는 대로 커스터마이징할 수 있다.

 

예를들어,

[그림 4] 피벗 테이블 형식 지정

[그림 4]와 같이 항목을 배치할 경우

[그림 5] 생성된 피벗 테이블

[그림 5]와 같은 피벗 테이블이 생성되는데, 이름을 행에 넣었기 때문에 행에 이름이 나열되어 있고 값에 대한 정보가 열에 있으며 값으로는 국어, 수학, 사회 과목에 대한 점수가 입력되어있다. 또한, 반이라는 필터가 존재하는 것도 볼 수 있다.

 

여기서

[그림 6] 1반 필터 적용

(모두)였던 필터를 1반으로 적용할 경우 1반의 점수와 합계만 조회되는 것을 알 수 있다.

 

하지만, 보통 성적같은 경우는 아이들의 평균을 구하지 합계를 구하는 경우는 드물다.

 

[그림 7] 평균으로 변경 과정 1

필드에 끌어 놓은 항목을 클릭하면 [그림 7]과 같은 선택지가 생기는데, 여기서 가장 아래에 있는 값 필드 설정을 클릭한다.

[그림 8] 평균으로 변경 과정

그러면 [그림 8]과 같은 창이 뜨고 여기서 평균을 클릭하면 된다. 그리고 평균 외에도 개수, 최대, 최소, 곱, 분산, 표준 분산 등등의 원하는 값으로 변경할 수 있다.

[그림 9] 평균으로 변경 완료

그렇게 수학, 사회도 위의 과정을 반복하여 평균으로 바꾸면 [그림 9]와 같이 데이터가 변경되는 것을 볼 수 있다.

 

2. 필터

필터는 고급 필터로 넘어가면 어렵겠지만, 사실 고급 필터를 실무에서 사용하는 경우를 잘못봤다. 만약에 고급 필터를 사용해서 자동화에 큰 도움이 된다는 것을 알게된다면 그때 다시 다루겠다.

 

때문에 사무직 수준에서는 일반 필터만 사용하면 된다고 생각하는데,

 

일반 필터는 너무 간단하다.

[그림 10] 필터 적용 전 데이터

아까의 데이터로 설명을 해보겠다.

[그림 10]과 같은 데이터를 필터를 사용해서 관리하고 싶다면,

[그림 11] 필터 적용 탭

표의 아무 셀을 클릭하든, 항목을 드래그하든 표 전체를 드래그한 뒤 [그림 11]과 같이 데이터 -> 필터(깔대기 모양)를 클릭하면 된다.

[그림 12] 데이터에 필터 적용 완료

그러면 반, 이름, 국어, 수학, 사회 항목 우측 하단에 역삼각형이 생긴 것을 볼 수 있는데,

[그림 13] 필터를 이용한 데이터 배치

역삼각형을 누르면 위와 같은 창이 뜨고(현재 '반'이라는 항목에 있는 필터를 클릭하였다.)

 

보고 싶은 데이터에 대해 체크할 수 있다.(피벗 테이블 필터와 똑같다.)

 

예를 들어 1반 데이터만 보고 싶으면,

[그림 14] 1반 데이터만 선택

[그림 14]와 같이 2반을 클릭하여 체크표시를 해제하면 된다.(다시 선택하려면 한번 더 누르면 된다.)

[그림 15] 1반 데이터만 조회

그러면 [그림 15]에서 보는 것처럼 1반 데이터만 볼 수 있다.

 

또한

[그림 16] 이름 항목에 필터 선택

[그림 16]에서 보이는 텍스트 오름차순 정렬을 선택하면,

[그림 17] 이름을 텍스트 오름차순으로 정렬

[그림 17]과 같이 사전순으로 정렬되며 [그림 16]에서 텍스트 내림차순 정렬을 선택하면,

[그림 18] 이름을 텍스트 내림차순으로 정렬

[그림 17]과는 반대로 사전 역순으로 정렬된 것을 볼 수 있다.

 

이는 이름뿐만 아닌 '반'이라는 항목도 마찬가지고

 

같은 과정으로 국어, 수학, 사회 항목에서 숫자 오름차순을 누르면 숫자의 크기가 작은 순서에서 큰 순서로

반대로 내림차순을 누르면 숫자가 큰 순서에서 작은 순서로 재배치된다.