티스토리 뷰
엑셀 고급필터의 활용 예제 및 조건식 설명
커피 한 잔의 여유 2013. 3. 19. 08:05
[링크]메모리스트 엑셀 함수 정리 목록 및 엑셀 팁 정리 목록
엑셀에서 입력 데이터 중 원하는 자료를 추출하는 방법으로 가장 많이 사용하는 방법은 함수이다. 하지만 함수는 사전에 입력된 함수식으로만 특정 데이터 추출이 가능하며, 많은 분들이 함수 사용법을 어려워하고 있다.
필터 기능은 엑셀에 입력된 데이터에서 사용자가 원하는 자료를 복잡한 검색 절차 없이 빠르게 검색할 수 있도록 제공해 주는 기능이다. 필터 기능은 크게 자동필터와 고급필터로 나뉜다.
자동필터는 데이터-필터-자동필터 메뉴를 선택해서 이용할 수 있으며, 이용방법이 직관적이어서 크게 어려움없이 사용이 가능하다.
엑셀 고급필터 기능
고급필터 기능을 이용해서 특정 데이터를 추출하기 위해 아래와 같이 입력한다. 일반적으로 많은 양의 데이터를 입력하게 되고, 그 데이터 중 특정 데이터를 추출하게 되는데 대량의 데이터를 캡처하면 포스팅 시 화면에 글씨가 잘 보이지 않는 관계로 몇 개의 데이터로만 예를 들어 설명한다.
아래의 데이터(A1:E10) 중 [부서]가 [기획부]이면서 [결제]가 [현금]인 항목만을 추출해 보자.
추출하고자 하는 조건을 데이터와 1열 정도 떨어진 위치에 입력을 한다. 이 때 조건을 직접 입력하는 것보다는 이미 입력된 셀을 복사해서 사용하기를 추천한다. 이유는 직접 [부서]라고 입력하면서 [부서 ]처럼 글자 뒤에 공백을 입력한다거나 [부 서]처럼 중간에 공백이 있는 경우 사람은 동일한 항목이라고 인식하지만 엑셀은 다른 항목으로 인식하기 때문이다.
데이터-필터-고급필터(엑셀 2003) 또는 데이터-정렬 및 필터-고급(엑셀 2010) 메뉴를 선택해서 조건식을 입력한다. 입력 시 데이터(A1:E10) 영역에 커서를 둔 상태에서 메뉴를 선택하면 [목록 범위]를 자동으로 추출해서 입력해 준다. 만약 [목록 범위]가 원하는 영역이 아닐 경우 원하는 영역을 범위로 지정하면 된다.
[조건 범위]는 입력해 놓은 조건 영역(G1:H2)을 지정해 주면 된다.
확인 버튼을 눌러주면 설정한 조건으로 고급 필터가 적용된다.
데이터 중 [부서] 항목이 [기획부]이면서 [결제] 항목이 [현금]인 항목만이 추출되었음을 확인할 수 있다.
추출된 데이터의 행을 보면 1번 행은 보이는데 2번 행은 보이지 않는 것을 확인할 수 있다. 또한 4, 5, 6, 7, 8번 행도 보이지 않는 것을 볼 수 있다. 그리고 행번호의 색이 푸른색으로 변해 있음을 확인할 수 있다.
이는 행과 행 사이에 필터링으로 인해 보이지 않는 행이 있음을 의미한다.
고급필터를 해제할 때는 데이터-필터-모두 표시(엑셀 2003) 또는 데이터-정렬 및 필터-지우기를 선택하면 된다.
고급필터의 AND조건과 OR조건
데이터 중 특정 데이터를 추출하다보면 조건식이 하나인 경우도 있지만 다수의 조건을 설정하는 경우가 대부분이다. 위의 예도 2개의 조건을 적용한 예이다.
고급필터를 적용하는 세부방법으로 AND 조건과 OR 조건이 있다.
AND 조건을 우리말로 표현하면 "그리고", 또는 "이면서"에 해당하며,
OR 조건을 우리말로 표현하면 "또는", 또는 "이거나"에 해당한다.
위의 예처럼 [부서]가 [기획]이면서 [결제]가 [현금]인 경우는 AND 조건에 해당하며,
[부서]가 [기획]이거나 [결제]가 [현금]인 경우는 OR 조건에 해당한다.
AND 조건과 OR 조건은 조건식을 수평으로 같은 줄에 두었느냐, 아니면 다른 줄에 놓았느냐에 따라 달라진다.
아래의 예는 [부서]가 [기획]이거나 [결제]가 [현금]인 경우를 고급필터로 적용한 예이다.
아래의 고급필터링 결과를 보면
3, 4, 6, 8, 9, 10번 행은 [부서]가 [기획부]이므로 표시된 것이며,
2, 3, 7, 9, 10번 행은 [결제]가 [현금]이어서 표시된 것이다.
두 조건 중 하나라도 만족하면 표시되므로 중복되는 데이터(3, 9, 10번 행)도 발생하게 된다.
고급필터 - 다른 장소에 복사
위의 설명은 현재 위치에 결과를 보여준 예이다. 필터링을 현재 위치에 적용할 경우는 원래의 데이터 중 일부가 보이지 않도록 해서 필터링을 적용하는 경우이다.
만약 특정 조건의 데이터를 추출해서 별도의 파일이나 다른 위치로 복사를 하려고 하는 경우는 [다른 장소에 복사]기능을 이용하면 된다.
아래의 그림은 고급필터 중 [다른 장소에 복사] 기능의 조건을 적용하는 과정이다.
선택 항목 중 [다른 장소에 복사] 항목을 선택하면 [복사 위치]항목을 입력할 수 있다.
[복사 위치] 항목에 아래의 예처럼 특정 셀(A12)을 선택해 주면 된다.
아래 그림은 [다른 장소에 복사] 조건을 적용한 결과 화면으로 12번 행에 구분란(일자, 부서, 비용과목, 금액, 결제)이 새로 생기면서 조건을 만족하는 데이터가 아래에 표시되는 것을 볼 수 있다.
이렇게 추출된 데이터의 영역을 복사하여 새로운 파일에 붙여넣기 하거나 다른 시트에 데이터를 생성해서 인쇄해도 된다.
이처럼 생성된 데이터는 고급필터를 제거할 때 위의 방법이 적용되지 않는다. 위에서는 고급필터를 해제할 때 데이터-필터-모두 표시(엑셀 2003) 또는 데이터-정렬 및 필터-지우기를 선택했지만, 방금의 경우는 별도의 위치에 데이터가 추출되었기 때문에 해제하는 별도의 방법이 없다.
추출된 데이터를 삭제하고자 한다면 12번 행부터 15번 행까지를 범위 지정후 삭제하면 된다.
[다른 장소에 복사]하는 방법이 또 하나가 있다. 이는 특정 항목만 선택해서 추출하는 것이다.
예를 들어 [부서], [금액], [결제] 항목만 필요하다면 해당 구분란을 미리 입력해 두고 해당 항목만 추출해 낼 수도 있다.
조건 입력 시 [복사 위치]를 선택할 때 미리 입력해 둔 [부서], [금액], [결제] 항목의 영역(A12:C12)을 지정해 주면 된다.
추출한 결과는 다음과 같다. 이처럼 데이터 영역 중 해당 조건을 만족하는 데이터만 특정 위치로 추출하되, 특정 항목만 추출할 수도 있다.
지금까지의 조건은 해당 항목을 만족하는 경우를 예로 들었다. 예로 들어 [부서] 항목이 [기획부]인 경우이다. 이처럼 특정 데이터를 추출하는 방법도 있지만, 수치 데이터에서 얼마 이상 또는 얼마 이하 등의 조건도 지정할 수 있다.
아래의 예는 [부서]가 [기획부]이면서 [금액]이 30,000원 이상인 경우를 조건으로 적용한 예이다.
엑셀에서는 조건 연산자를 자주 사용하게 된다. 연산자에는 [크다], [작다], [이상], [이하], [초과], [미만], [같다], [다르다] 등 다양하다.
연산자 중 가장 혼란스러운 것이 부등호의 방향이다. 꺾쇠가 왼쪽으로 꺾어졌는가, 오른쪽으로 꺾어졌는가에 따라 다르지만, 생각하기에 따라서는 반대의 의미로도 해석할 수 있기 때문이다.
엑셀을 비롯한 컴퓨터 연산(프로그래밍 언어 포함)은 해당 항목이 왼쪽에 있는 것을 기준으로 생각한다.
예를 들어 ">= 30,000" 이라는 조건식은 "A >= 30,000"으로 이해할 수 있으며, "A 항목이 30,000 보다 크거나 같은가?"라고 해석할 수 있다.
조건식을 정리하면 다음과 같다.
= : "같다."
< : "작다." 혹은 "미만"(왼쪽의 항목이 작다.)
> : "크다." 혹은 초과 (왼쪽의 항목이 크다.)
<= : "작거나 같다." 혹은 "이하"(왼쪽의 항목이 작거나 같다.)
>= : "크거나 같다." 혹은 "이상"(왼쪽의 항목이 크거나 같다.)
<> : "다르다."
초과 : 해당 숫자를 포함하지 않으면서 크다.
미만 : 해당 숫자를 포함하지 않으면서 작다.
이상 : 해당 숫자를 포함하면서 크다.
이하 : 해당 숫자를 포함하면서 작다.
저는 위 상품을 소개하면서 포인트를 받았습니다.