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

21.PC_BBS_s21 

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

      

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

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

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

Comments

번호 제목 글쓴이 날짜 조회 추천
Malware Zero Report File 댓글+2 11qkr 02.22 591 0
최종/성공)Android-x86 프로젝트 - PC에서 Android 실행/ 실시간 tv 채널왕 설치 보기 댓글+8 shimss11q 2017.05.03 5200 0
사용편리) win 자체 제공 )EazyCopy, RoboCopy GUI Microsoft RoboCopy 도… 댓글+9 shimss11q 2017.04.02 3417 0
디스크바로접속 먼저 해놓고 바이두 보안문자 없이 접속_한글 baiduyun_속도업v5.27.zip..8m속도… 댓글+15 shimss11q 2015.08.25 4980 3
추천/적용)삼성 리커버리 AdminTool_for_SRS6.iso 파일및 사용법/ C-MOS 부팅에 F4 … 댓글+37 shim11q.kr관리자 2016.10.06 6345 6
정보)vmware workstation 12 pro에서 해상도 맟추기 변경하기 display setting 댓글+1 shim11q.kr관리자 2016.07.17 5062 0
pc기본)bootice_x86_x64_v134_20160617_pc응급유틸리티및 사용정보 입니다 댓글+18 shimss11q 2016.07.25 3812 3
pc기본)pc 초기설치 필수 인터넷설치 프로그램 및 기본 설치 프로그램 링크 정보 댓글+11 shim11q.kr관리자 2016.03.20 6260 2
Download Notepad++ 6.9 edit 에디터 한글 설정 및 compare plugin 설치 파… 댓글+4 shim11q.kr관리자 2016.03.17 5392 1
PC기본설치)startmenu10 시작메뉴 업 /사이트 프리웨어 다운 설치 StartMenu10_Setup_… 댓글+14 shimss11q 2016.03.14 4774 0
pc기본설치)초보자의 컴퓨터 사용기_윈도우 설치 WIMBOOT, VHD드라이브로 설치하기 만들기_win81자… 댓글+38 shim11q.kr관리자 2016.03.12 5946 3
소장2_강력추천)Win81PE_WB_v7_20160204_shimss.iso 파일에 USER-PE_VGA.i… 댓글+98 shim11q.kr관리자 2016.02.03 7981 11
작업관리자_network monitoring 인터넷속도 표시netspeedmonitor windows8에서 … 댓글+8 shim11q.kr관리자 2013.08.18 9492 1
삼성노트북 윈도우 8.1재설치 인증확인후 > 윈도우10 업데이트 후 인증 확인 댓글+3 shim11q.kr관리자 2015.08.02 8801 1
강추_사용기)월드 베스트 PE Lite 5.0 간단 사용기usb_grub4dos_iso_booting(수정) 댓글+53 shim11q.kr관리자 2015.06.19 11461 2
♣♣♣ 추천3)win81DREAM_PE_2015_V9_x64_xwim_shimss3.iso dream_pe_… 댓글+83 shim11q.kr관리자 2015.06.14 16923 2
추천)DreamPE_lite_x64_x_wimboot_shimss.wim 원도우 멀티부팅메뉴 추가 /win8… 댓글+109 11q.kr관리자 2015.06.07 13823 0
♣♣♣ 추천3으로이동)win81DREAM_PE_2015_V9_x64_xwim_shimss3.iso 및 바이두… 댓글+39 shimss@11q.kr 2015.06.14 9498 1
부팅시_Hotkey진입_menu선택_bootice_grun4dos로_winpe선택실행.rar 댓글+20 11q.kr관리자 2014.05.03 12799 1
열람중 엑셀 데이터 유효성 부분일치 검색 - 목록상자 만들기 11qkr 10.16 19 0
761 GOMCam_Manual_KOR / 곰캠 메뉴얼 / 동영상을 GIF 움직이는 사진 만들기 입니다 11qkr 10.15 13 0
760 윈도우 자동 로그인 간단 설정 AutoLogon 프로그램 입니다. 11qkr 10.14 20 0
759 원도우 폰트사이즈 변경하기)고해상도 화면디스플레이 변경으로 화면 폰트 사이즈 작음을 변경 사용하기 11qkr 10.14 20 0
758 해결)Windows 10 업그레이드 후 깜박이는 디스플레이 문제 해결 StartIsBack 설치 되어 있으면… 11qkr 10.14 31 0
757 윈도우 10 시작메뉴 초기화 또는 시작 메뉴 변경 하기 / StartIsBack 설치 조건 11qkr 10.13 20 0
756 Windows 10에서 Linux Bash 셸을 활성화하는 방법 / 리눅스 활성화 11qkr 10.13 24 0
755 윈도우 네트워크 한글 깨짐 11qkr 10.09 30 0
754 강제종료 procexp.exe process explore 11qkr 10.06 43 0
753 쓰기 방지를 제거하세요 11qkr 10.03 57 0
752 keyboard hangul input key change /한글 키보드 전환이 않돨때 변경후 k640 키보… 11qkr 09.23 95 0
751 MP3 Arranger v1.3 - 중복된 MP3 파일 정리 프로그램 11qkr 09.22 99 0
750 토탈커맨더 파일명 일괄 변경하기 프로그래밍/기타 2013. 9. 5. 22:44 11qkr 09.22 83 0
749 pc windows 자동실행 문제 정리 autorun 다운 pc정리하기 11qkr 09.17 106 0
748 윈도우에서 고급 시스템 설정을 엽니 다 10 (세 가지 솔루션)! 11qkr 09.09 158 0
747 Easy2Boot 을 이용하여 USB에서 ISO 파일 멀티 부팅 만들기 11qkr 09.04 156 0
746 일괄복사xxcopy 11qkr 07.22 260 0
745 ISO 파일 부팅 USB 만들기 - 다수의 ISO 파일 Ventoy 작업 댓글+1 11qkr 07.01 444 1
744 파일 삭제시 지정된 파일을 찾을 수 없습니다. 삭제 불가능 할때 11qkr 06.15 394 0
743 LEORY Q8 블루투스 고품질 음성 리모컨 무선 키보드 에어 마우스 TV 네트워크 셋톱 박스 용 새 버전 댓글+1 11qkr 06.11 280 0
742 Malware Zero Report File 댓글+2 11qkr 02.22 591 0
741 [긴급]윈도우 종료시 NET- broadcast event window 이라는 메세지가뜹니다 shimss@11q.kr 02.22 699 0
740 [MS Word ] 자동 파일명 업데이트 및 페이지 번호 추가 방법 및 CoreELEC Entware opk… shimss@11q.kr 02.20 522 0
739 Malware Zero Report File shimss@11q.kr 01.06 554 0
738 펌)가벼운 Windows 10 PE (Win10PEL.ISO) [2019-04-03]/원도우 설치용 댓글+9 shimss@11q.kr 2019.10.20 2449 1
737 원도우에서 데비안 설치 Linux용 Windows 하위 시스템 설치 후 bash 로 설치 상태 확인 작업 shimss@11q.kr 2019.10.13 886 0
736 Malware Zero Report File 20191007 shimss@11q.kr 2019.10.07 592 0
735 웹 사이트를 차단하거나 리디렉션하기 위해 Windows Hosts 파일 편집 shimss@11q.kr 2019.10.05 751 0
734 https://time.is/ 웹페이지에 현재 시간 표시 shimss@11q.kr 2019.09.30 603 0
733 윈도우 10 전원관리 (최고의 성능) 으로 변경하기 shimss@11q.kr 2019.09.29 836 1
21.PC_BBS_s21 

PHP 안에 HTML ☞ 방문 시간은 2020-10-20 12:35:57 입니다.
☞ Server uptime /volume1/web/g5s/thema/Basic/side/shimss_basic-side.php:69: string(69) " 12:35:57 up 2 days, 3:32, 0 users, load average: 0.32, 0.52, 0.59"
Category
State
  • 현재 접속자2 0 명
  • 신규 가입자 0 명
  • 오늘 방문자 1,897 명
  • 어제 방문자 1,227 명
  • 최대 방문자 1,990 명
  • 전체 방문자 2,321,465 명
  • 전체 게시물 5,453 개
  • 전체 댓글수 17,547 개
  • 전체 회원수 7,278 명

☞ Your IP : 3.237.61.235

☞ Your Mac : entries

Facebook Twitter GooglePlus KakaoStory NaverBand
.\thema\Basic\side\shimss_basic-side.php
+ ☆☆☆ Bookmark link1(S52)_tall.php ☆☆☆