엑셀분석강좌 3강




고급필터





고급필터에 대해 설명한 강의입니다.


▶ 고급필터

많은 양의 데이터가 있을 경우 원하는 조건의 데이터만 추출하는 기능이다. 

비슷한 기능으로 자동필터가 있긴 하지만 OR 조건식을 만들 수 없고, 

다른 위치에 결과를 추출할 수 없는 등 제약이 있기 때문에 고급필터를 더 많이 사용하게 된다.


▶ 고급필터 작성 순서

1. 조건식을 작성한다.

2. [데이터]-[정렬 및 필터]-[고급필터] 명령을 클릭한다.

3. '목록 범위'에는 전체 데이터 범위를 지정하고, '조건 범위'에는 본인이 작성한 조건을 범위 지정하고, '복사 위치'는 결과가 보여질 첫 번째 셀을 클릭한다


▶ 조건 작성법

1. 필드 제목을 무조건 옆으로 나란히 작성한다

2. 조건을 모두 만족해야 하는 경우 조건을 옆으로 나란히 작성한다.

3. 조건 중에 하나만 만족해도 되는 경우 조건을 대각선으로 작성한다.

4. 같은 필드에서 '이거나'라는 말이 나오면 위아래로 작성하면 된다.(문자일 경우)

5. 같은 필드에서 모두 만족해야 하는 두 개 이상의 조건일 경우 필드 이름을 여러번 쓰고 조건을 나란히 작성한다.(숫자일 경우)

6. 조건에 식을 사용하여 'TRUE', 'FALSE' 등이 표시될 경우 필드 제목을 비워놓거나 존재하지 않는 내용을 써야한다.






아래의 연습파일을 다운로드해서 연습하세요.


고급필터.xlsx











아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.
















 

 

 

 


Posted by Y&S

 

 

 

고급필터는 아래와 같이 많은 양의 데이터가 있을 경우 원하는 조건의 데이터만 추출하는 기능이다.

예를 들어 지점명이 '대륙전자'인 데이터만 추출하고자 할 경우 사용한다.

비슷한 기능으로 자동필터가 있긴 하지만 OR 조건식을 만들 수 없고, 다른 위치에 결과를 추출할 수 없는 등 제약이 있기 때문에 고급필터를 더 많이 사용하게 된다.

 

 

 

 

 

고급필터 작성 순서

 

1. 조건식을 작성한다.

2. [데이터]-[정렬 및 필터]-[고급필터] 명령을 클릭한다.

3. '목록 범위'에는 전체 데이터 범위를 지정하고, '조건 범위'에는 본인이 작성한 조건을 범위 지정하고, '복사 위치'는 결과가 보여질 첫 번째 셀을 클릭한다.

 

 

고급필터에서 가장 어려운 부분이 1번인 조건식을 작성하는 것이다.

원하는 조건이 복잡해질수록 조건식도 당연히 복잡해진다. 조건식의 규칙을 알아보자.

 

 

고급필터 조건 작성 방법

 

1. 필드 제목을 무조건 옆으로 나란히 작성한다. (지점코드, 단가)

2. 조건을 모두 만족해야 하는 경우 조건을 옆으로 나란히 작성한다.(JL*, <800000)

 

 

3. 조건 중에 하나만 만족해도 되는 경우 조건을 대각선으로 작성한다.(대륙전자, >=80)

4. 같은 필드에서 모두 만족해야 하는 두 개 이상의 조건일 경우 필드 이름을 여러번 쓰고 조건을 나란히 작성한다.(무게(Kg), 무게(Kg)), (>=80, <=90)

 

 

5. 같은 필드에서 '이거나'라는 말이 나오면 위아래로 작성하면 된다. (DR*, JL*)

 

 

4. 조건에 식을 사용하여 'TRUE', 'FALSE' 등이 표시될 경우 필드 제목을 비워놓거나 존재하지 않는 내용을 써야한다.

 

① 조건 필드 제목을 비워놓은 경우 

14 앞뒤에 큰 따옴표("")를 앞뒤에 넣은 이유는

left, right, mid 등의 문자열 함수로 추출한 숫자는 문자형 숫자이기 때문이다.

이 조건은 컴활1급실기에서 많이 나오는 문제유형이다.

 

② 조건 필드 제목에 기존의 필드 제목과 상관없는 내용을 적은 경우(8월 판매, 평균 이상)

평균 범위를 절대참조로 지정하지 않으면 제대로 추출되지 않는다.

 

 

 

고급 필터로 데이터 추출하기

 

 

 

1. 데이터 범위를 모두 선택한 후 [고급필터] 명령을 클릭한다.

 

 

 

 

2. [고급필터] 창이 나타나면

데이터는 그대로 두고 다른 위치에 필터링하기 위해

'다른 장소에 복사' 옵션을 클릭하여 체크한다.

'조건 범위'는 작성해놓은 조건을, '복사 위치'는 원하는 셀을 선택한다.

 

여기에서는 아래의 조건을 선택했다.

 

 

 

 

 

3. 데이터에서 조건에 맞는 결과만 추출되었다.

아래는 '매출번호가 14로 끝나고, 제품코드가 'TV'로 시작하는 데이터'만 추출한 경우다.

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

데이터 검색 완성1.xlsm

데이터 검색 완성2.xlsm

 

 

 

지난 실습

엑셀 데이터 자동 검색 실습 1 (옵션단추, 고급필터, 매크로) by Y

엑셀 데이터 자동 검색 실습 2 (옵션단추, 고급필터, 매크로) by Y

 

 

 

지난 시간에 이어서 검색된 데이터를 검색을 취소하여 모두 표시하는 실습을 해보자. 역시 매크로를 만들고 버튼에 지정하여 언제든지 모든 데이터를 표시할 수 있도록 작업해보자. '데이터 검색 완성1.xlsm' 첨부 파일은 2번 실습까지의 완성 파일이고, '데이터 검색 완성2.xlsm' 첨부 파일은 이번 실습까지 모두 완성된 파일이므로 첫 번째 첨부 파일을 다운받아 같이 실습하고 완성 파일과 비교해보자. 물론 실습 1부터 같이 따라해왔다면 본인이 완성한 파일로 작업해도 된다.

 

 

 

데이터를 모두 표시하는 매크로 만들어 버튼에 지정하기


 

 

1. 새로운 매크로를 만들기 위해 [개발 도구]-[매크로 기록] 명령을 클릭한다.

 

 

 

 

2. [매크로 기록] 대화 상자가 나타나면 '매크로 이름'에 '검색취소'를 입력한 후 [확인] 버튼을 클릭한다. [확인] 버튼을 클릭하고 난 후에는 좀 더 신중하게 작업을 하자.

 

 

 

 

3. [B8:H13] 범위를 모두 지정한 후 필터를 지우기 위해 [데이터] 탭의 [정렬 및 필터]-[지우기] 명령을 클릭한다.

 

 

 

 

4. 필터가 해제되어 데이터가 모두 표시된다.

 

 

 

 

5. 매크로 기록을 중지하기 위해 [개발 도구] 탭[기록 중지] 명령을 클릭한다.

 

 

 

 

6. [개발도구]-[컨트롤]-[삽입]-[단추] 명령을 클릭하고 시트에서 적당한 자리에서 그려준 다음 [매크로 지정] 대화 상자가 나타나면 방금 전에 만든 매크로인 '검색취소'를 클릭하고 [확인] 버튼을 클릭한다.

 

 

 

 

7. 버튼의 텍스트를 '검색 취소'로 변경한다.

 

 

 

 

8. 이제 모든 작업이 끝나고 완성되었다. 제대로 되는지 확인하기 위해 '영업3부' 옵션 단추를 클릭한 후 [검색] 버튼을 누른 결과이다. 당연히 '영업3부' 데이터만 검색된다.

 

 

 

 

9. 이제 [검색 취소] 버튼을 클릭하면 다시 모든 데이터가 표시된다.

 

이렇게 옵션 단추, 그룹 상자, 버튼 등의 컨트롤과 매크로 기능을 잘 조합하면 복잡한 실무 작업도 쉽게 할 수 있다는 말씀!!

 

 

동영상 강좌 보기

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

매크로와 고급필터.xlsx

데이터 검색 완성1.xlsm

 

 

실습 1

엑셀 데이터 자동 검색 실습 1 (옵션단추, 고급필터, 매크로) by Y

 

 

 

지난 실습에 이어서 매크로와 고급필터 기능을 추가하여 데이터가 자동으로 검색되도록 해보자. 지난 실습에서는 옵션 단추를 클릭하면 [B6] 셀에 해당하는 부서명이 입력되는 데까지 완성하였다. '매크로와 고급필터.xlsx' 첨부 파일 또한 여기까지 완성된 파일이고, '데이터 검색 완성1.xlsm' 파일은 데이터 검색하는 과정까지 완성된 파일이다. 다운받아 실습해보고 완성 파일과 비교해보자. 물론 실습 1부터 따라해왔다면 본인이 작업한 파일로 이어서 작업해도 된다.

 

 

고급 필터는 명령을 클릭하여 매번 실행하는 기능이므로 자동으로 필터가 되도록 하려면 반복 실행 기능인 매크로와 병행하여 사용해야 한다.

매크로와 고급필터에 대하여 잘 모른다면 다음 글을 참조하자.

엑셀 매크로 실습 (데이터를 원하는 위치로 이동하기) by Y

고급필터로 데이터 추출하기 (고급필터 조건 작성법) by Y

 

 

 

데이터를 자동으로 검색하는 매크로를 만들어 버튼에 지정하기

 

 

 

1. 매크로를 만들기 위해 [개발 도구]-[매크로 기록] 명령을 클릭한다.

 

 

 

 

2. [매크로 기록] 대화 상자가 나타나면 매크로 이름을 '데이터검색'으로 입력한 후 [확인] 버튼을 클릭한다. [확인] 버튼을 클릭하고 난 후의 모든 키보드와 마우스 동작이 기록되므로 조심해야 한다.

 

 

 

 

3. 고급 필터 작업을 매크로에 저장하기 위해 [데이터] 탭의 [정렬 및 필터]-[고급] 명령을 클릭한다.

 

 

 

 

4. [고급 필터] 대화 상자가 나타나면 '목록 범위'에는 [B8:H15] 범위를 지정하고, '조건 범위'에는 [B5:B6] 범위를 지정한 후 [확인] 버튼을 클릭한다.

 

 

 

 

5. 'B5:B6' 범위를 조건으로 받아들여 고급 필터가 실행되어 표에서 영업4부에 해당하는 데이터만 검색된다.

 

 

 

 

6. 고급 필터가 완료되었으므로 매크로 기록을 중지하기 위해 [개발 도구] 탭[기록 중지] 명령을 클릭한다. 매크로에서 기록 중지는 잊어먹기 쉬운 과정이므로 잊지 말고 실행하자.

 

 

 

 

7. 매크로는 단축키에 지정할 수도 있지만, 도형이나 컨트롤의 단추에 지정하여 버튼을 눌러 실행할 수도 있다. 여기에서는 [개발 도구]-[컨트롤]-[단추] 명령을 클릭한다.

 

 

 

 

8. 시트에서 임의의 자리에 단추를 드래그하여 그리면 [매크로 지정] 대화 상자가 자동으로 나타난다. 방금 전에 만든 '데이터검색' 매크로를 클릭하여 선택한 후 [확인] 버튼을 클릭한다.

 

 

 

 

9. 텍스트를 변경하기 위해 드래그한다.

 

 

 

 

10. '검색' 텍스트를 입력하고 임의의 셀을 클릭하여 편집을 완료한다.

 

 

 

 

11. 부서를 변경하여 검색하기 위해 옵션 단추 중에서 '영업3부' 옵션 단추를 클릭한다.

 

 

 

 

12. [검색] 버튼을 클릭하면 '영업3부'에 해당하는 데이터만 검색된다.

 

 

같은 방법으로 '영업1부'~'영업4부'의 데이터를 모두 검색해볼 수 있을 것이다.

 

 

다음 글에서는 검색된 데이터를 필터를 해제하여 모두 표시되도록 하는 작업을 연습해보자.

 

이어지는 다음 실습

엑셀 데이터 자동 검색 실습 3 (옵션단추, 고급필터, 매크로) by Y

 

 

동영상 강좌 보기

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

옵션단추.xlsx

 

 

옵션 단추는 여러개의 옵션 중에 하나만 선택할 경우 사용하는 컨트롤이다. 옵션 단추를 보기 좋게 묶어 주는 역할은 그룹 상자가 한다. 한 워크시트에 여러 그룹의 옵션 단추가 있는 경우에는 반드시 각 옵션 단추를 그룹으로 묶어줘야 한다.

 

이번 실습 1에서는 옵션 단추와 그룹 상자를 이용하여 선택한 부서가 B6 셀에 자동으로 표시되도록 하는 데까지만 실습해보자. 예제 파일은 첨부되어 있으니 따라서 같이 실습해보자.

 

옵션 단추와 그룹 상자와 같은 컨트롤은 리본 메뉴에 [개발 도구] 탭이 있어야 삽입할 수 있다. 엑셀에 [개발 도구]가 없다면 다음 글을 참조해보자.

엑셀 리본 메뉴에 [개발 도구] 탭 표시하기 by Y

 

 

 

옵션 단추 삽입하기

 

 

 

1. [개발 도구]-[컨트롤]-[삽입]-[옵션 단추] 명령을 클릭한다.

 

 

 

 

2. 적당한 위치에서 드래그하여 옵션 단추 컨트롤을 그려준다.

 

 

 

 

3. 텍스트를 드래그한다.

 

 

 

 

4. '영업1부'를 써준다.

 

 

 

5. 같은 방법으로 4개의 옵션 단추를 삽입하고 텍스트를 각 부서명으로 바꿔준다.

 

 

 

 

6. 옵션 단추를 그룹으로 묶어주기 위해 [개발 도구]-[컨트롤]-[삽입]-[그룹 상자] 명령을 클릭한다.

 

 

 

 

7. 옵션 단추가 모두 포함되도록 그린 후에 텍스트를 '부서 선택'으로 변경한다.

 

 

 

 

8. 옵션 컨트롤의 값을 셀과 연결시켜보자. 아무 옵션 단추에서 마우스 오른쪽 버튼을 누른 후 [컨트롤 서식] 메뉴를 클릭한다.

 

 

 

 

9. [컨트롤 서식] 대화 상자가 나타나면 '선택한 상태'를 클릭한 후 '셀 연결'을 [B4] 셀로 지정한다. 이제 B4셀에는 옵션 단추 선택값이 숫자로 자동으로 입력된다.

 

 

 

 

10. 두 번째 옵션 단추가 선택되어 있기 때문에 B4 셀에 2가 입력되어 있다.

 

 

 

 

11. 이제 B6 셀에서 B4 셀을 이용하여 각 부서명이 표시되도록 수식을 작성하자. 여기에서는 'choose(B4,"영업1부","영업2부","영업3부","영업4부")' 수식을 입력하였다.

 

==> choose 함수는 첫번째 인수값이 무엇이냐에 따라 다음 값들 중에 선택하는 함수이다. 즉, B4 셀에 2가 입력되어 있으니, 두 번째 값인 '영업2부'가 선택된다. 1이었으면 '영업1부'가 선택된다.

 

choose 함수 글을 참조해보자~!! 엑셀 함수 14 (선택 함수, CHOOSE 함수) by Y

 

 

 

 

12. 이런 과정을 통해 옵션 단추에서 선택한 부서명이 그대로 B6셀에 표시되도록 완성했다.

 

 

 

 

13. 옵션 단추에서 '영업4부'를 클릭하면 자동으로 B4, B6 셀의 값이 '4', '영업4부'로 변경된다.

 

다음 실습에서는 매크로와 고급필터를 이용하여 선택한 부서의 데이터만 검색되도록 하는 방법에 대해 배워보자.

 

 

이어지는 다음 실습

엑셀 데이터 자동 검색 실습 2 (옵션단추, 고급필터, 매크로) by Y

 

 

동영상 강좌 보기

 

 

 

 

 

 

 

 

 

Posted by Y&S