엑셀 배우기(7) - 고급 필터

1. 필터와 비교

필터는 데이터 영역에서 필터를 설정하고, 그 결과 데이터 영역 내에서 해당되는 데이터만 표시되는데 비하여 고급 필터는 데이터 영역 밖에서 필터 조건을 지정할 수 있고, 필터와 동일하게 데이터 영역에 결과를 표시할 수 있을 뿐만 아니라 다른 영역에 표시할 수 있다는 장점이 있습니다. 또한 고급 필터에는 '동일한 레코드는 하나만' 표시하는 기능도 있습니다.

2. 고급 필터 명령

고급 필터 명령은 데이터 탭에서 필터 명령과 함께 자리 잡고 있습니다.

3. 고급 필터 명령 예제

필터 명령을 설명했던 최종 데이터를 사용하겠습니다. 필터 조건과 표시할 영역을 별도로 지정한다는 것 말고는 동일하기 때문입니다.

엑셀배우기7.xlsx

0.01MB

가. 현재 위치에 필터

보기 탭 - 틀고정에서 틀고정을 했습니다.

위와 같이 데이터 영역 아래 C29셀에 거래처라고 입력하고, C30셀에 나주라고 입력한 다음

데이터 탭의 고급 필터 명령을 누르면 아래와 같은 대화 상자가 표시됩니다.

① 결과
필터링된 결과를 현재 위치에 필터 해서 보여줄 것인지,
다른 장소에 복사할 것인지 선택하는 옵션입니다.

② 목록 범위 : 데이터가 있는 영역인데, 데이터 영역 내에 커서가 있다면 자동으로 세팅됩니다. 만약 위치가 안 맞다면 목록 범위 오른쪽의 위 화살표 키 부분을 누른 다음 설정하면 됩니다.

③ 조건 범위 : 필터 조건의 범위를 지정하는 것입니다. 여기서는 C29:C30이므로
조건 범위 오른쪽 입력 칸을 마우스로 클릭한 다음 마우스로 끌어서 C29:C30를 선택하거나(조건 범위 왼쪽에 !$가 보이는데, Sheet1!$가 숨겨져 있는 것입니다),

조건 범위 입력 칸 오른쪽의 위 화살표 키를 누른 후 마우스로 끌어서 C29:C30를 선택하고 오른쪽의 아래 화살표 키를 누르는 방법이 있습니다.

그리고 확인 버튼을 누르면 필터로 했을 때와 동일한 화면이 됩니다. 다만 2행 전체에 역삼각형 모양 표시가 없고, 거래처에도 깔때기 모양이 없습니다.

나. 다른 장소에 복사

위에서 '현재 위치에 필터'가 아니라 '다른 장소에 복사'를 선택하면 활성화되어 있지 않던 복사 위치가 활성화됩니다.

복사 위치 오른쪽 입력 칸을 마우스로 클릭한 다음 A32셀을 클릭하고 확인 버튼을 누릅니다.

그러면 A32셀부터 M35셀까지 필터링된 결과가 표시됩니다. 결과는 A3셀부터 M19셀까지와 같습니다.

다. 다중 필터 조건 설정

(1) OR 조건 1

위와 같이 거래처를 나주로 하고, 공급가액을 30만 이상으로 하는데, 나주와 30만 이상이 같은 줄이 아니라 다른 줄에 있다면 OR조건이고, 같은 줄에 있다면 AND 조건입니다. 위 화면은 OR 조건이므로 거래처가 나주이거나 공급가액이 30만 이상인 데이터를 추출하라는 의미가 됩니다.

결과 표시가 '현재 위치에 필터'로 선택되어 있으므로 '다른 장소에 복사'를 선택하고, 조건 범위를 C29에서 J31로 수정하고, 복사 위치는 맞으므로 확인 버튼을 누르면

아래와 같이 거래처가 나주이거나 공급가액이 30만 이상인 데이터가 A31셀부터 표시됩니다.

세 가지 이상의 조건을 OR 조건으로 엮으려면 32행 이상도 조건 범위 영역으로 확보했어야 했네요.

(2) OR 조건 2
J31셀의 30만 이상 조건을 마우스로 끌어서 J30셀로 옮긴 다음 고급 필터를 실행하는데 '현재 위치에 필터'가 선택되어 있으므로

'다른 장소에 복사'로 변경하고, 조건 범위를 J31셀까지 그대로 둔 상태에서 확인 버튼을 누르면

아래와 같이 모든 데이터가 표시됩니다. 이것은 31행에 조건이 없기 때문에 29행과 30행과 무관하게 모든 데이터가 표시되는 것입니다.

(3) AND 조건 1
아래와 같이 조건 범위를 J31에서 J30으로 수정하고 확인 버튼을 누르면

AND 조건이 성립되어 거래처가 나주이면서 공급가액이 30만 이상인 데이터만 표시되는데, 표시 영역이 줄어든 부분에 대해서는 테두리가 남아 있는 단점이 있습니다.

(4) AND 조건 2
공급가액을 30만이 아니라 60만으로 수정하고 고급 필터를 동일하게 실행하면 아래와 같이 1건만 표시됩니다.

라. 필터 지우기

데이터 영역에 필터가 설정되어 있는데, 필터를 해제하려면 데이터 탭의 필터 지우기 명령을 누르면 됩니다.

그러면 데이터 영역이 필터가 해제되어 원래의 모습으로 돌아옵니다.

마. 동일한 레코드는 하나만

동일한 데이터를 만들기 위해 19행을 마우스로 클릭한 다음 마우스 오른쪽 버튼을 눌러 복사 메뉴를 클릭한 후

다시 마우스 오른쪽 버튼을 누른 후 복사한 셀 삽입 메뉴를 누릅니다.

그러면 NO가 17인 것이 2개가 됩니다. 복사 영역을 해제하기 위해 B19셀을 클릭합니다.

고급 필터에서 먼저 '동일한 레코드는 하나만' 옵션에 체크하지 않고 실행하면

아래와 같이 NO 17이 두 개가 표시되는데,

'동일한 레코드는 하나만' 옵션에 체크하고 실행하면

아래와 같이 동일한 레코드가 제거되어 하나만 표시됩니다. 동일한 레코드의 기준은 나주가 아니라 NO부터 고가까지 모두 동일한 것을 의미합니다.

바. 필터 조건 이동
보기 좋게 거래처와 공급가액을 필드 아래에 배치했지만,
J30셀에서 마우스를 클릭한 후 J31셀까지 끌어서 J30셀과 J31셀을 선택한 다음 D30셀로 옮겨도

또한 조건 범위가 C30에서 J31이더라도, E열부터는 조건이 없기 때문에

고급 필터의 결괏값은 같습니다.

사. 원하는 필드만 표시
위에서는 데이터 영역에 있는 모든 필드가 표시됐지만 복사 위치에 원하는 필드만 지정하면 해당하는 데이터만 표시됩니다.

실중량부터 세액까지는 표지 하지 않기 위해 H33셀부터 K58셀까지 마우스를 끌어서 범위를 선택한 다음

마우스 오른쪽 버튼을 누른 후 삭제 메뉴를 누릅니다.

그러면 셀을 왼쪽으로 밀 것인지 위로 밀 것인지 등을 묻는데, 맞으므로 확인 버튼을 누릅니다.

그러면 총금액과 고가가 왼쪽으로 당겨져서 H열과 I열에 위치하는데, 총금액에 해당하는 H열이 좁아 숫자가 다 보이지 않습니다. 이 때는 H열과 I열 사이의 경계선을 마우스로 더블 클릭하면

총금액의 숫자 길이에 맞게 열 너비가 자동 조절되므로 총금액이 제대로 표시됩니다.

이제 커서를 데이터 영역 안에 넣고 고급 필터를 실행하는데, '현재 위치에 필터'로 되어 있으므로 '다른 장소에 복사'를 클릭합니다.

그리고, 복사 위치를 확인해 보니 범위를 삭제해서 자동으로 J열이 I열로 수정되었습니다. 이제 확인 버튼을 누르면

실중량부터 세액까지는 표시되지 않고, 복사 위치에 있는 필드에 해당하는 데이터만 표시됩니다.

이외에도 필터에서 적용하는 다양한 방법이 있으므로 아래 글을 참고해서
https://lsw3210.tistory.com/72

여러 가지로 연습해보기 바랍니다.