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. 이제 [검색 취소] 버튼을 클릭하면 다시 모든 데이터가 표시된다.

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

동영상 강좌 보기

매크로와 고급필터.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

동영상 강좌 보기

옵션단추.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

동영상 강좌 보기