Y&S 두 여자의 말랑말랑 생활공감

거래명세표.xlsm

방명록에 어떤 분이 수많은 품목이 엑셀에 저장되어 있는데 거래명세서에서 품목을 입력할 때 품목의 일부만 입력해도 그 일부가 포함된 품목의 목록이 나타나서 클릭만으로 쉽게 입력할 수 있는 방법을 물어보셨다.

생각해보니 꼭 거래명세서나 품목이 아니더라도 활용도가 많은 경우라고 생각해서 그 방법을 모색해보니 고급필터, 데이터유효성검사, 매크로의 3가지 방법을 모두 사용해야 가능해진다. 

아래와 같이 '품목'에 '딸기'를 입력하면 그 글자가 포함된 모든 품목의 목록이 '딸기'를 입력한 셀에 나타나 간단하게 원하는 품목을 선택할 수 있는 예제를 만들어보겠다.

어찌보면 네이버의 연관 검색어와 비슷한 면이 있는듯해서 포스팅 제목을 '엑셀 연관 검색어 실습'이라고 지어봤다.

이 파일은 '거래명세표' 시트와 '품목 목록' 시트의 2개의 시트가 있으며, '품목 목록' 시트에는 품목 데이터가 입력되어 있다. 실습용이라 그 목록의 개수는 적지만, 몇만 개의 데이터가 입력되어 있더라도 결과는 같다.

예제 파일이 첨부되어 있으니 다운받아서 같이 따라해보면 될듯~~~

실습 시작

1. Q9 셀에 고급 필터의 조건을 미리 입력해보자.

="*'&D16&"*" 식은 '*'을 이용하여

문자의 일부가 포함된 모든 데이터를 검색할 때 사용되던

'*문자열*'를 식을 이용하여 작성한 것이다.

예를 들어 '*딸기*'는 '딸기'가 포함된 모든 데이터란 의미이다.

어찌 보면 이번 실습에서 가장 중요한 부분이라고 볼 수 있을듯..

고급 필터가 궁금하다면 참조~!! 고급필터로 데이터 추출하기 (고급필터 조건 작성법) by Y

2. 식을 입력하고 엔터를 치면 D16셀이

비어 있으므로 *만 두개 표시될 것이다.

3. 하지만, D16셀에 품목의 일부분을 입력하면

당장 조건이 바뀌는 것을 볼 수 있다.

4. 고급 필터의 조건이 준비되었다면 고급 필터를 실행하고 그 결과를

데이터 유효성 검사로 품목 셀에 표시하는 일련의 과정은 매크로로 작성한다.

[개발 도구]-[매크로 기록] 명령을 클릭한다.

매크로가 궁금하다면 참조~!! 엑셀 매크로 실습 (데이터를 원하는 위치로 이동하기) by Y

5. 매크로 이름과 단축키를 지정하고 [확인] 버튼을 클릭한다.

6. 고급 필터는 조건과 결과를 표시할 시트에서 시작해야 하므로

'거래명세표' 시트의 임의의 셀을 클릭한 상태에서

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

7. '다른 장소에 복사'를 지정하고 '목록 범위'는 '품목 목록' 시트 탭을 눌러

목록 전체를 범위로 지정한다.

'조건 범위'를 클릭하면 자동으로 '거래명세표' 시트로 화면이 이동한다.

8. 나머지 조건 범위와 복사 위치를 지정한 후 [확인] 버튼을 클릭한다.

9. 고급 필터가 실행되어 '소스'가 포함된 모든 품목이 필터링된다.

10. 이제 고급 필터의 결과를 '품목' 셀에 목록으로 표시하기 위해

D16셀을 클릭한 후 [데이터]-[데이터 유효성 검사] 명령을 클릭한다.

데이터 유효성 검사가 궁금하다면 참조~!!

엑셀 데이터 유효성 검사 실습 (조건부 서식 자동으로 지정하기) by Y

11. '제한 대상'을 목록으로 선택한 후 '원본'에 고급 필터의 결과를 선택한다.

이때, 결과가 많을 경우를 대비하여 결과보다 훨씬 많은 부분을 선택한다.

어짜피 글자가 있는 부분만 표시되므로 상관 없다.

12. 자 이제 모든 작업이 끝났으므로 잊지 말고 기록을 중지하자.

13. 이제 잘 되는지 실습해보기에 앞서

앞에서 작성한 데이터 유효성 검사를 지우기 위해

D16셀이 선택된 상태에서 [데이터]-[데이터 유효성 검사] 명령을

클릭한 후 [모두 지우기] 버튼을 클릭한다.

**내용 추가**

생각해보니 위의 과정을 매크로 시작시 가장 처음에 실행하면

D16셀에 있던 유효성검사를 지울 수 있어 번거롭게 따로 지울 필요가 없다.

14. 이제 품목에 '딸기'를 입력하고 엔터를 친 후

Ctrl+q 키를 누르면 매크로가 실행되어 딸기가 포함된 데이터가 표시된다.

이때 원하는 품목을 클릭하면 품목이 입력된다.

마치, 네이버의 연관 검색어와 비슷하지 않남? ㅎㅎ

만약 다른 셀에서 또 다른 데이터를 입력할 경우

목록이 표시되는 셀도 이동되도록 매크로를 수정해야 한다.