거래명세표.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 키를 누르면 매크로가 실행되어 딸기가 포함된 데이터가 표시된다.

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

 

 

 

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

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

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

 

 

 

 
 
 
 

 

Posted by 두여자 Y&S

댓글을 달아 주세요

  1. 평안

    잘보았습니다.. 문자열 ** 활용과 필터조건.. 그런데 보통 필터보다는 콤보상자 많이 쓰이는가싶습니다
    손에 익을정도는 어느정도여야하나.. 좋은주말 되세요

    2013.05.11 23:19 [ ADDR : EDIT/ DEL : REPLY ]
    • 아..이 글은 어떤 분이 부탁하셔서 급조하여 쓴 글이라 허술한 부분이 많죠 ㅎㅎㅎ
      그래도 잘 보셨다니 다행이네요^^
      좋은 주말 되세요^^

      2013.05.12 01:21 신고 [ ADDR : EDIT/ DEL ]
  2. 플레인

    궁금한게있는데 엑셀에 문자값을(숫자아님)작성하고 조건부서식을 넣으면 어떤 텍스트가 뜬다는데 그런경우에는 어떤 수식이 들어가나요?급해서 그러는데 자세히 알려주시면 감사하겠습니다

    2013.09.10 10:12 [ ADDR : EDIT/ DEL : REPLY ]
    • 질문이 어떤걸 말씀하시는 건지 모르겠네요.
      어떤 텍스트가 뜬다는게 무슨 말씀이신지 자세히 말씀해주시구요.
      조건부 서식에 대한 글은 따로 찾아보시면 세개의 포스팅으로 자세히 설명되어 있습니다.
      말씀하시는게 조건부 서식에 대한 거라면 그 세개의 포스팅에서 찾아보셔도 될 듯싶네요.

      2013.09.10 10:19 신고 [ ADDR : EDIT/ DEL ]
  3. 플레인

    죄송해요ㅠㅠ자세히얘기해드릴게요~엑셀을이용해서 활용하라는데밑에처럼 이값을 넣으면 맨밑에처럼 나온대요~근데 어떻게 수식을 써야하는지 모르겠어서요ㅜㅜ

    철수
    29
    우리은행
    철민
    시작


    철수,지금 당장 일해!!!
    인터넷이나 뒤적이지 말구!!
    우리은행에서 돈 받으면서 빈둥데고 있잖여 쯧쯧쯧!!
    29살이나 먹은 사람이!!! 니 나이를 생각해 ...
    아참→철민←이 사람이 너 싫다구 전해달래..^^;;

    2013.09.10 10:29 [ ADDR : EDIT/ DEL : REPLY ]
    • "철수"라는 텍스트가 A1 셀에 있다면 간단히 문장을 만드는 수식은
      =A1&",지금 당장 일해!!"
      이구요.

      만약 조건식을 이용한다면(철수, 29, 우리은행 세개만 데이터가 있고, 각각 A1, A2, A3 셀에 입력되어 있다고 가정한다면) B1 셀에 아래 수식을 적고 수식을 아래로 복사하면 되겠네요.

      =if(A1="철수", A1&",지금 당장 일해!!", if(A1="29", A1&"살이나 먹은 사람이!! 니 나이를 생각해...",A1&"에서 돈 받으면서 빈둥대고 있잖여 쯧쯧쯧!!"))

      물론 유효성 검사를 이용할수도 있고, 기타 옵션 도구를 이용할 수도 있고, 기타 데이터 기능을 이용할 수도 있겠지만, 그건 답글로만으로는 설명이 복잡해질거 같아서 위의 두 가지 식만 적어봤어요.
      혹시 정해진 기능을 사용하라고 했다면 그 기능을 말씀해주시면 간단히 설명은 가능하겠네요^^

      2013.09.10 10:56 신고 [ ADDR : EDIT/ DEL ]
  4. 플레인

    정해진기능은없구요~회사에서 원하는 방법은 메크로였어요~근데 제가 메크로를 할줄 몰라서ㅠㅠ 여기서 많이 배워야겠어요~^_^ 빠른답변감사드려요!

    2013.09.10 11:08 [ ADDR : EDIT/ DEL : REPLY ]
    • 매크로를 사용하면서, 정해진 텍스트에 따라 뜨는 텍스트가 다르다면 vlookup 함수나 배열수식으로 값을 찾아서 해당하는 텍스트가 표시되도록 해야겠네요. 열심히 공부하세요~~^^

      2013.09.10 11:15 신고 [ ADDR : EDIT/ DEL ]
  5. 플레인

    다시한번질문할게요~ㅠㅠ왕초보라
    회사상사분이 아래식으로하면 된다하던데 안되서요

    =IF(A1="","",(=IF(A1="","",,A1&F1),"지금당장일해"

    여기서어떤게잘못되었고 엑셀로하려고 하는데 알려주세요~

    2013.09.11 11:43 [ ADDR : EDIT/ DEL : REPLY ]
    • =IF(A1="","",IF(A1="철수",A1&"지금당장일해"))

      식은 고쳐드렸구요
      중첩IF문과 IF문에 대해 공부하시면 이해가 가실듯하네요.

      2013.09.11 12:32 신고 [ ADDR : EDIT/ DEL ]
  6. 플레인

    정말 죄송한데 고쳐주신 식으로 했는데 왜 자꾸 FALSE가 나오네요ㅜㅜ 소수점도 똑같이 했는데요ㅠㅠ 동영상이라도 있으면 좋으련만,,,,ㅠㅠ


    2013.09.11 13:33 [ ADDR : EDIT/ DEL : REPLY ]
    • 이 식은 A1이 빈셀이거나 '철수'라는 텍스트가 입력되어 있어야해요.
      두가지에 해당이 안되면 FALSE가 나옵니다.
      두가지가 아닐 경우에 표시될 값은
      =IF(A1="","",IF(A1="철수",A1&"지금당장일해","이부분이요"))
      '이부분이요' 이 부분에 넣으셔야합니다.

      일단 IF 함수 공부하시는게 급해보이네요.
      =IF(조건, 참, 거짓) 이라는 기본식에 대한 기본개념을 잡으셔야해요.
      댓글로 도움을 드릴 수 있는건 한계가 있으니까요

      2013.09.11 14:11 신고 [ ADDR : EDIT/ DEL ]
  7. Chris Lee

    잘 보고 많이 배워갑니다 :) 여쭤보고 싶은게 있는데요, 한 번 필터링을 하고 나면 다른 단어로는 검색이 되지 않는데 해결 방법이 있을까요? 이를테면 '딸기'라고 하면 딸기 소스, 딸기 잼 이렇게 나오는데 '포도'라고 입력하면 입력할 수 있는 단어가 제한되어 있다고 나와서요~
    그리고 또 한 가지는 여러 셀에 적용할 경우에는 매크로를 수정해야 한다고 하셨는데, 어떻게 수정해야 하나요?;; 한꺼번에 너무 많이 여쭤본 것 같아서 죄송합니다;;

    2014.11.20 17:42 [ ADDR : EDIT/ DEL : REPLY ]
    • 첫번째 문제는~
      13번 유효성 검사 지우기를 안하셔서 그런거구요, 매크로 마지막에 13번까지 추가하면 더 편하게 작업하실 수 있습니다.
      두번째 질문하신 건~
      따로 글로 답변드리기는 너무 복잡하고, http://yslife.tistory.com/117 글을 참조하시면 될거 같네요
      좋은 하루되세요^^

      2014.11.21 10:49 신고 [ ADDR : EDIT/ DEL ]
  8. Chris Lee

    답변 감사합니다 :) 유효성 검사 지우기도 빼놓지 않고 했는데도 그렇게 뜨더라구요;; 뭐가 잘 못 된 건지;; 두 번 째 질문 건은 봐도 잘 이해가 안돼서요;; ㅠㅠ 따로 참고 할 만한 자료가 있을까요? 좋은 하루 되시구요 :)

    2014.11.21 11:53 [ ADDR : EDIT/ DEL : REPLY ]
    • 유효성 검사 지우기를 하면 될텐데..매크로 글을 보셔도 이해가 안가신다면 매크로에 대해 좀더 공부하셔서(책 등으로) 이해를 높이신 다음에 다시 해보시는게 어떨지..
      그리고 이 예제는 '이렇게 하면 재밌겠다'라는 생각으로 만든 거라서 조금 더 보완이 필요하긴 할거예요~
      좋은 하루되세요~^^

      2014.11.22 03:58 신고 [ ADDR : EDIT/ DEL ]
  9. Chris Lee

    아, 네! 유효성 검사 지우기 확인 했습니다 :) 감사합니다~ 매크로는 조금 더 공부해야 겠네요ㅠ 그래도 친절하게 답변해 주셔서 감사드려요~ 좋은 하루 되세요 :)

    2014.11.24 08:39 [ ADDR : EDIT/ DEL : REPLY ]
  10. koomj92

    안되요!!!!!!! ㅠㅠ ㅠ ㅠ 도와주세여 ㅠㅠㅠㅠㅠㅠㅠ

    2017.02.09 17:07 [ ADDR : EDIT/ DEL : REPLY ]