21.PC_1




새창 작성 수정 목록 링크 Edit G카랜다 HDD HDD HDD 게시물 주소 복사


엑셀 데이터 유효성 부분일치 검색 - 목록상자 만들기

♨ 카랜더 일정 :
  • 링크

  • 첨부

  • 컨텐츠 정보

    본문

    데이터유효성 부분일치 검색 동작원리 이해하기

    강의 추천 영상 /// 엑셀의 기본 사용자 

    ISNUMBER + SEARCH 부분일치 검색공식

    ISNUMBER / SEARCH 부분일치 검색 공식의 동작원리는 아래 관련 포스트에서 단계별로 자세히 설명해 드렸습니다. 특정 단어 포함여부 검색을 하는 ISNUMBER/SEARCH 공식의 자세한 설명이 궁금하신 분은 아래 관련 링크를 참고하세요.

    IF/MAX 함수 + 확장범위 응용하기

     IF 함수와  MAX 함수 그리고  확장범위를 같이 응용하면, 특정 조건을 만족할 경우에 1씩 증가하는 순번이 출력되도록 만들 수 있습니다. 공식은 아래와 같습니다.

    =IF(조건,MAX($머릿글셀:머릿글셀)+1,0)

    위 공식에 사용된 확장범위에 대한 내용은 아래 관련 포스트에서 자세히 설명해드렸습니다.

    예를 들어, 아래 공식을 시트의 A2셀부터 아래방향으로 자동채우기 한다고 가정하겠습니다.

    =IF(ISEVEN(ROW()),MAX($A$1:A1)+1,0)
    1. A2셀인 경우 : =IF(ISEVEN(ROW()),MAX($A$1:A1)+1,0)
      ' ROW() 함수는 2를 반환, ISEVEN 함수는 TRUE를 반환합니다.
      ' MAX($A$1:A1) 으로 0이 계산됩니다. 따라서 MAX($A$1:A1)+1 은 1을 반환합니다.
      =IF(ISEVEN(ROW()),MAX($A$1:A1)+1,0)
      =IF(TRUE,0+1,0)
      =1
    2. A3셀인 경우 : =IF(ISEVEN(ROW()),MAX($A$1:A2)+1,0)
      ' ROW() 함수는 3을 반환, ISEVEN 함수는 FALSE를 반환합니다.
      ' -> 0을 반환하면서 마무리됩니다.
      =IF(ISEVEN(ROW()),MAX($A$1:A2)+1,0)
      =IF(FALSE,1+1,0)
      =0
    3. A4셀인 경우 : =IF(ISEVEN(ROW()),MAX($A$1:A3)+1,0)
      ' ROW() 함수는 4를 반환, ISEVEN 함수는 TRUE를 반환합니다.
      ' MAX($A$1:A3) 으로 1이 계산되며, MAX($A$1:A3)+1 은 2를 반환합니다.
      =IF(ISEVEN(ROW()),MAX($A$1:A3)+1,0)
      =IF(TRUE,1+1,0)
      =2
    ROWS 함수 + 확장범위 응용하기

     ROWS 함수와 확장범위를 응용하면 시작셀부터 아래 방향으로 1씩 증가하는 순번을 손쉽게 만들 수 있습니다. 동일한 원리로  COLUMNS 함수를 사용하면, 시작셀부터 오른쪽 방향으로 1씩 증가하는 순번을 만들 수도 있습니다.

    =ROWS($A$1:A1)
    '함수를 입력한 뒤 아래 방향으로 자동채우기 하면 1,2,3,4... 순번이 생성됩니다.
    =COLUMNS($A$1:A1)
    '함수를 입력한 뒤 오른쪽 방향으로 자동채우기 하면 1,2,3,4... 순번이 생성됩니다.
    COUNTIF 함수 동적범위 만들기

    이전 강의에서  OFFSET 함수와 COUNTA 함수를 사용한 동적범위를 알아봤습니다. 대부분의 경우는 OFFSET / COUNTA 동적범위를 사용할 수 있지만, '수식으로 반환되는 값이 빈칸인 값을 제외해야 할 경우' 에는 OFFSET / COUNTA 동적범위를 사용할 수 없습니다.

    COUNTA 함수는 겉으로는 '빈칸' 이지만 안에 수식이 입력되어 있으면 해당 셀을 개수에 포함합니다.

    따라서, 수식의 결과값으로 빈칸을 반환하는 셀을 동적범위에서 제외해야 할 경우에는 아래에 적어드린 OFFSET / COUNTIF 동적범위를 사용합니다.

    =OFFSET($시작셀,,,COUNTIF($범위,"*?")

    위 공식에서 사용된 "*?" 는 와일드카드입니다. "*?"를 조건으로 입력하면 '셀의 결과값으로 최소 한 글자를 포함하는 셀'을 선택하게 됩니다. 와일드카드에 대한 내용은 아래 관련 포스트에서 자세히 설명해 드렸습니다.

    데이터 유효성 부분일치 검색 목록상자 만들기 (365 이전버전)

    1. 예제파일의 [대한민국영화목록(ㄱ)-365이전] 시트로 이동합니다. E3셀에 검색할 단어를 입력합니다. 예제로 '가문'을 입력하겠습니다.

      데이터 유효성 검색할 단어 입력예제파일 E3셀에 검색할 단어를 입력합니다.
    2. B3셀에 아래 수식을 입력한 뒤, 아래 방향으로 자동채우기 합니다. 수식을 자동채우기 하면 영화제목에 '가문'을 포함할 경우 1씩 증가하는 순번이 반환됩니다.
      =IF(ISNUMBER(SEARCH($E$3,C3)),MAX($B$2:B2)+1,0)

      ISNUMBER SEARCH 부분일치 검색 공식수식을 입력하면 단어를 포함하는 제목 옆에 순번이 출력됩니다.
    3. 예제파일의 G3셀에 아래 수식을 입력한 뒤, 아래방향으로 넉넉하게 자동채우기 합니다. 예제로 G31셀까지 자동채우기 하겠습니다. 수식을 자동채우기하면 '가문'을 포함하는 영화 제목만 나열되는 것을 확인할 수 있습니다.
      =IFERROR(VLOOKUP(ROWS($G$2:G2),B:C,2,0),"")

      특정 단어 포함 목록특정 단어를 포함하는 영화 목록이 출력됩니다.
    4. E3셀에 검색할 단어를 변경합니다. 단어를 변경하면 해당 단어를 포함하는 영화제목만 목록으로 반환되는 것을 확인할 수 있습니다.

      단어 포함하는 목록 출력단어를 변경하면 나타나는 영화 목록도 변경됩니다.
    5. 동적범위를 생성합니다. [수식] - [이름관리자]로 이동하거나, 단축키 CTRL + F3 키를 눌러 이름관리자를 실행합니다. 이후 [새로만들기] 버튼을 클릭하여 아래와 같이 새로운 이름정의범위를 생성합니다. 사용된 동적범위의 동작원리는 영상강의에서 자세히 설명해드렸습니다.
      이름 : 유효성목록상자
      참조대상 : =OFFSET('대한민국영화목록(ㄱ)-365이전'!$G$3,,,COUNTIF('대한민국영화목록(ㄱ)-365이전'!$G$3:$G$31,"*?"))

      이름 정의 범위 만들기'유효성목록상자'라는 이름으로 이름정의범위를 생성합니다.
    6. 데이터유효성 목록 상자를 적용할 셀인 E3셀을 선택합니다. [데이터] - [데이터 유효성 검사]로 이동합니다. 제한대상은 '목록'으로 선택합니다.

      데이터 유효성 목록 생성데이터 유효성 검사로 목록을 적용합니다.
    7. '원본' 을 선택한 뒤, 키보드 F3키를 누르면 방금 전에 생성한 이름정의범위가 나타납니다. '유효성목록상자'를 선택합니다.

      데이터 유효성 목록 동적범위원본 범위로 '유효성목록상자' 이름 정의범위를 선택합니다.
    8. [오류메시지] 탭으로 이동한 뒤, '유효하지 않은 데이터를 입력하면 오류메시지 표시' 체크박스를 비활성화 합니다. [확인]을 눌러 종료합니다.

      데이터 유효성 유효하지 않은 데이터 입력 오류 메시지 표시오류메시지 표시를 비활성화 합니다.
    9. 데이터 유효성 부분일치 검색 목록상자가 완성되었습니다. 원하는 단어를 검색한 뒤, ALT +  를 입력하면 해당 단어를 포함하는 영화제목이 목록상자로 출력됩니다.

      데이터 유효성 부분일치 검색 목록 완성부분일치 검색 데이터 유효성 목록이 완성되었습니다.

    데이터 유효성 부분일치 검색 목록상자 만들기 (Microsoft 365 버전)

    Microsoft 365 버전 사용자는 FILTER 함수와 # 기호를 이용한 동적범위를 사용하여 아주 간단하게 부분일치 검색 목록상자를 만들 수 있습니다.

    1. 예제파일의 [대한민국영화목록(ㄱ)-365사용자] 시트로 이동합니다. D3셀에 검색할 단어를 입력합니다. 예제로 '결혼'을 입력하겠습니다.

      데이터 유효성 부분일치 단어 입력부분일치로 검색할 단어를 입력합니다.
    2. F3셀에 아래 수식을 입력합니다. 수식을 입력하면 '결혼'을 포함하는 영화제목이 동적배열로 반환됩니다.
      =FILTER(B3:B132,ISNUMBER(SEARCH(D3,B3:B132)))

      FILTER 함수 입력F3셀에 FILTER 함수를 입력합니다.
    3.  FILTER 함수 이외에도  UNIQUE 함수를 사용하여 고유값만 반환하거나,  SORT 함수를 사용하여 영화제목을 오름차순 또는 내림차순으로 정렬할 수도 있습니다.

      SORT 함수 정렬SORT 함수와 UNIQUE 함수를 같이 응용할 수도 있습니다.
    4. 데이터유효성 목록상자를 적용할 D3셀을 선택한 뒤, [데이터] - [데이터 유효성 검사]로 이동합니다. '제한대상은' 목록을 선택합니다. '원본'에는 아래 수식을 입력합니다. '#' 기호를 사용하면 배열로 반환된 범위를 동적으로 선택할 수 있습니다. (365 사용자)
      =F3#

      데이터 유효성 부분일치 동적범위# 기호를 이용한 동적범위를 목록상자에 적용합니다.
    5. [오류메시지] 탭으로 이동한 뒤, '유효하지 않은 데이터를 입력하면 오류메시지 표시' 체크박스를 비활성화 합니다. [확인]을 눌러 종료합니다.

      데이터 유효성 유효하지 않은 데이터 입력 오류 메시지 표시유효성 오류메세지 표시를 비활성화 합니다.
    6. 데이터 유효성 부분일치 검색 목록상자가 마무리 되었습니다. 이처럼 365 사용자는 아주 간단하게 목록상자를 생성할 수 있습니다.

      데이터유효성 부분일치 완료 365 버전데이터 유효성 부분일치 검색 목록이 완성되었습니다.

    넓은 범위에 부분일치 검색 목록상자 만들기 (Microsoft 365 버전)

    Microsoft 365 버전 사용자는  TRANSPOSE 함수 하나만 더 사용하면 부분일치 검색 목록상자를 넓은 범위에도 손쉽게 생성할 수 있습니다.

    1. 예제파일의 [대한민국영화목록(ㄱ)-넓은범위적용] 시트로 이동합니다. D3셀과 D4셀에 검색할 단어를 입력합니다. 예제로 '가문'과 '결혼'을 입력하겠습니다.

      데이터 유효성 부분일치 여러개예제파일의 D3, D4, D5... 셀에 검색할 단어를 입력합니다.
    2. F3셀에 아래 수식을 입력한 뒤, 아래 방향으로 자동채우기 합니다.
      =TRANSPOSE(FILTER($B$3:$B$132,ISNUMBER(SEARCH(D3,$B$3:$B$132))))

      데이터 유효성 부분일치 TRANSPOSE 함수TRANSPOSE 함수와 FILTER 함수 응용공식을 F3셀에 입력한 뒤 자동채우기 합니다.
    3. D3:D8 범위를 선택한 뒤, [데이터] - [데이터 유효성 검사]로 이동합니다. '제한대상은' 목록을 선택한 뒤, '원본'에는 아래 수식을 입력합니다.
      =$F3#

      %EC%9C%A0%ED%9A%A8%EC%84%B1-%EB%AA%A9%EB%A1%9D-%EC%83%81%EC%9E%90-%EB%8F%99%EC%A0%81%EB%B2%94%EC%9C%84-365%EB%B2%84%EC%A0%84.png
    [ 추가 정보 ... 더보기) ]
    뷰PDF 1,2



    office view

    관련자료

    댓글목록

    profile_image

    song님의 댓글

    no_profile song 쪽지보내기 메일보내기 자기소개 아이디로 검색 전체게시물 아이피 (210.♡.202.222) 작성일

    잘보았습니다.

    목록

    새창 작성 수정 목록 링크 Edit G카랜다 HDD HDD HDD 게시물 주소 복사


    • 일간 조회수
        • 게시물이 없습니다.
    • 주간 조회수
        • 게시물이 없습니다.
    • 월간 조회수
        • 게시물이 없습니다.


    Total 1,080 / 6 Page
    [ ● 킹스톤 M.2 2280 NVMe SSD PCle 4.0 구입 USB 장착 Speed 벤치마킹 및 저… ]

    ● 킹스톤 M.2 2280 NVMe SSD PCle 4.0 구입 USB 장착 Speed 벤치마킹 및 저장장치 속도측정하기Kingston NV2 …

    [ ● 해결)모니터 화면이 깜박입니다. (번쩍/떨림/울렁거림) 해결방법 Anynet+(HDMI-CEC) 기능을 … ]

    ● 해결) 모니터화면이 깜박임 니다. (번쩍/떨림/울렁거림)해결방법Anynet+(HDMI-CEC) 기능을 off로 해결간헐적으로 화면이 깜박거림…

    [ 라디오 어플 추천/ Resco Radio ] 댓글 1

    ♥ 출처 : link 참조\r\n라디오 어플 추천 \r\nResco Radio_안드로이드 최고의 라디오 어플...최고 추천 ◎ 네이버에서 [ht…

    [ MKV 동영상 자르기_mkvtoolnix-unicode-4.6.0(20110319) ] 댓글 1

    \r\n\r\nMKV 자르기_mkvtoolnix-unicode멀티미디어/UCC 조회 4174 추천 0 \r\nbluesea 2010/04/12,…

    [ skype설치,연락처찾기,skype등록자 찾기,친구추가,skype영상 사용법,주소록추가,무료인터넷전화,무료영… ]

    skyper는 무료 음성영화 통화 입니다인터넷이 가능한곳에서 어디서나 무료 통화 가능합니다skype는 무한대로 자기의 skype 를 만들수 있다…

    [ 나의 하드 디스크 ]

    Homepc.11q.kr\r\n=============================\n[이 게시물은 관리자님에 의해 2013-11-18 21:2…

    [ windows 원도우 이제 CD 버리고 USB/hdd로 설치 ]

    \r\n\r\n\r\n이제 CD 버리고 USB로 설치합시다\r\n\r\n\r\n\r\n조회 수 : 260\r\n2012.12.23(20:24:5…

    [ autocad viewer DWGTrue viewer / autocad 색상변경 color change ] 댓글 2

    아래의 링크에서 autocad viewer DWGTrue viewer 받을수 있다\r\nhttp://a.tk.co.kr/m/683Homepc.1…

    [ ● 어학녹음 녹음파일 [GoldWave] 사운드 볼륨 증폭시키기 ]

    ● 어학녹음 녹음파일 [GoldWave] 사운드 볼륨 증폭시키기 이번에는 골드웨이브를 이용해서 사운드의 볼륨을 크게하는 방법을 알아보겠습니다인터…

    [ 윈도우8 자동설치 방법, VHD / VHDX 지원 ] 댓글 1

    http://liverex.tistory.com/1421\r\n\r\n\r\n윈도우8, VHD 로 설치하는 간단한 방법- 포스팅으로 이미 유사한…

    [ 원도우 7 그 새로운 세계로 Windows 7 USB에 넣어서 설치 나도 해보자 ] 댓글 2

    원도우\r\n7 그 새로운 세계로 Windows 7 USB에 넣어서 설치 나도 해보자 춮처http://moonbogy.tistory.com/25…

    [ 추억의 게임 슈팅게임 라이덴3_비행슈팅게임_무설치.exe ]

    Homepc.11q.kr\r\n=============================추억의 게임 슈팅게임 라이덴3_비행슈팅게임_무설치.exe입니다…

    [ 동영상을 움직이는 gif 파일로 변환 ]

    \r\n\r\n \r\n \r\n 동영상을 움직이는 gif 파일로 변환 / 움짤만들기 :: 꿈을 만드는 곳, 몽공장\r\n ‎ - 201…

    [ USB HDD를 이용한 원도우8.1 설치 부팅으로 도스용 GHOST로 원도우 백업 하기 ]

     USB HDD를 이용한 원도우8.1 설치 부팅으로 도스용 GHOST로 원도우 백업 하기- USB HDD에 GHOST32.EXE를 카피 준비 …

    [ SRT 자막 구글번역기로 번역 자막 한글 자막으로 만들기 ] 댓글 2

    ###\r\n메모장으로 편집(그대로 저장)\r\n\r\n\r\n전체선택 복사\r\n구글번역\r\n번역하단 전체선택 복사\r\n메모장에 붙이기\r…

    [ WIN GHOST 원도우81에서 고스트 자동 백업 후 자동종료 ] 댓글 16

    WIN GHOST 원도우81에서 고스트 자동 백업 후 자동종료-------기본하드 정보를 알고 있을때 자동 백업 -----------------…

    [ 노트북 덮개 닫아도 안꺼지게 하는 방법 : 네이버 블로그 ]

    노트북 덮개닫아도안꺼지게 하는 방법 : 네이버 블로그blog.naver.com/PostView.nhn?blogId=bymoon1127&l…



    ♥간단_메모글♥


    최근글


    새댓글



    PHP 안에 HTML ☞ 홈페이지 화면갱신 시간은 ♨
    ▶ 2024-06-26 10:15:25

    오늘의 홈 현황


    • 현재 접속자♨ 825 명
    • 오늘 가입자※ 0 명
    • 어제 가입자※ 2 명
    • 주간 가입자※ 8 명
    • 오늘 방문자 1,411 명
    • 어제 방문자 2,051 명
    • 최대 방문자 13,042 명
    • 전체 방문자 4,396,018 명
    • 전체 게시물※ 8,654 개
    • 전체 댓글수※ 24,772 개
    • 전체 회원수 11,068 명

    QR코드


    ☞ QR코드 스캔은 kakao앱 자체 QR코드

    알림 0








    최신글↑