엑셀

엑셀 자동필터 이해와 필터링 후 일련번호 자동으로 붙이기

콘파냐 2017. 5. 14. 04:20
반응형

엑셀 자동필터로 표의 값들을 걸러내면 레코드(행)의 개수가 줄어들게 된다. 왜냐면 필터링된 데이터의 레코드가 표에 표시되지 않기 때문이다.


그런데 이 레코드들에 대한 일련번호가 표에 포함되어 있다면 자동필터로 필터링한 후 일련번호가 자동으로 수정되지 않아서 난감할 수가 있다. 


그렇다면 어떻게 자동필터를 사용한 후에 일련번호를 붙일 수 있을까?


이를 해결하기 위해서 SUBTOTAL 함수나 AGGREGATE 함수를 이용할 수 있다. 이 두 함수는 동일하므로 어떤 것을 이용해도 상관없다.


우선 자동필터와 SUBTOTAL이 어떤 것인지에 대한 이해가 필요하다. 


(*자동필터와 SUBTOTAL 함수를 이미 이해고 있는 경우라면 아래 내용에서 이 두 가지에 대한 내용은 건너 뛰셔도 좋습니다.~)


자동필터

자동필터는 누차 강조하지만 Ctrl+Shift+L 단축키를 꼭 외워놔야 한다. 엑셀에서 가장 흔히 사용되는 단축키에 속하므로 다른 내용은 다 까먹어도 되니 이 단축키는 꼭 외우자. 


어쨌든 다음과 같은 표가 있다고 하자. 우리는 이 표에서 앞에서 강조한 자동필터 단축키를 누를 것이다.


※ 참고 :  또 한 가지 강조할 것은 표를 선택할 때는 표 전체를 선택하던지 표에 포함된 셀 하나!를 선택하던지 하면 된다. 애매하게 표에서 셀 두개 범위 이렇게 선택하면 안된다. 


자 이제 Ctrl+Shift+L을 눌러보자.▼ 깔대기(필터) 표시가 생겼다. (이 단축키는 토글키로 또 누르면 자동필터가 없어진다.)



다음 과정으로 직책에서 병장을 뺀 나머지를 제거해보도록 하겠다. ▼(깔대기 표시)를 눌러서 병장과 필드값 없음을 제외한 나머지 체크 박스의 체크 표시를 해제하도록 하자.



다음은 자동필터가 수행된 결과다.



이렇게 자동필터를 사용하면 원하는 데이터가 있는 행만 뽑아서 볼 수 있다. 오름차순 내림차순 정렬을 할 수 있는 메뉴도 보이는데 직접 해보면 쉽게 이해할 수 있으므로 여기에서는 넘어간다.


그런데 위에서 자동필터를 한 결과를 보면 합계의 값에 문제가 있는데 필터를 한 후에 합계가 변화가 없다는 점이다. 현재 합계는 SUM 함수(리본 메뉴의 자동합계)를 사용하여 계산되어 있다.

SUM 함수를 사용하면 이런 문제가 생기므로 합계를 구할 때는 SUM 함수 대신 SUBTOTAL 함수를 사용하는 것이 좋다.


SUBTOTAL

SUBTOTAL(function_num, ref1, ...)

SUBTOTAL 함수는 function_num(아래 그림 참고) 인수를 사용해서 다양한 함수의 기능을 할 수 있다. 

이 중에 우리는 SUM에 해당하는 9를 넣을 것이다. 

ref1는 합을 구할 셀 범위다.


앞에서 본 예제로 다시 돌아가 보자. 이 예제에서 자동필터를 해제(Ctrl+Shift+L)를 한 후에 아래 그림처럼 합계가 계산된 셀의 수식을 SUM에서 SUBTOTAL 함수로 대체해보자.

상여급과 상금에 해당하는 부분도 같은 작업을 해준다. 

다 마쳤다면 복습도할 겸 자동필터를 사용해서 앞에서 했던 작업을 그대로 다시해 보자. 앞에서 이미 한 내용이므로 자세한 과정 그림은 생략한다. (참고로 필터를 할 때 필드값 없음 항목이 보이지 않는데 상관없다.)


결과는 다음과 같은데 합계가 필터링한 결과에 맞게 달라져 있음을 알 수 있다.


엑셀 자동필터로 필터링된 표에 일련번호 붙이기

좀 장황하게 설명을 했는데 이번 포스팅의 주제인 자동필터를 한 후에 일련번호를 붙이는 방법에 대한 설명이다.


앞의 내용을 이해했다면 어려운 내용은 아니므로 단순화된 예제를 사용하겠다. 

Data 필드가 있고 값들에 대해서 일련번호 No가 있다. A만 표시되도록 자동필터로 필터링 했는데 일련번호는 자동으로 수정되지 않는다.

이 문제를 해결하기 위해서 일련번호를 SUBTOTAL 함수로 대체해야 한다.


그 전에 COUNTA에 대한 이해가 필요하므로 이 함수를 모른다면 다음 글을 참고하자.


2015/07/17 - [엑셀] - 엑셀 COUNT 함수 숫자가 있는 셀의 개수


= SUBTOTAL(3, $C$3:C3)

첫 번째 인수 3은 COUNTA 함수의 기능을 선택한 것이다. (※ 위에 SUBTOTAL에 대한 설명의 그림 참고)

두 번째 인수는 카운팅을 할 셀 범위를 나타내는데 카운팅을 할 범위(아래 그림 파란 박스 참고)의 시작 셀은 절대 주소다. 그리고 끝 셀은 상대주소로 현재 SUBTOTAL 함수를 가지고 일련번호를 계산하려고 하는 셀의 바로 옆 셀이된다.

이런 형태의 수식은 꽤 빈번히 나타나므로 완벽하게 이해하는 것이 좋다.


그런데 어떻게 이 값이 일련번호가 되는지 아직 감이 안올 수도 있다.


다음 그림을 보면 일련번호가 어떻게 붙여지는지 이해하기 좀 수월할 것이다.

카운팅을 할 범위는 파란색 테두리가 쳐져있다. 왼쪽 표는 $C$3에서 C7까지의 범위를 카운팅해서 5의 일련번호를 갖는 셀의 수식을 보여준다. 눈으로 봐도 파란 테두리의 셀은 5개의 데이터를 갖으므로 함수의 계산 결과는 5가 된다. 이렇게 카운팅한 값이 일련번호로 쓰인다는 것은 논리적이다.


두 번째 인수인 셀 범위를 왜 시작 셀은 $(절대주소)를 붙이는지, 그리고 범위의 끝을 가리키는 셀은 상대주소로 하는지 반드시 이해하길 바란다. 

위 함수를 일련번호가 시작되는 셀에 입력 후 아래로 쭉~ 드래그 해서 채워넣자.


그리고 다시 앞에서 했던 작업을 하면..

이렇게 멋지게 일련번호가 자동으로 수정되어 붙게 된다.

반응형