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

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
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
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 이전버전)

예제파일의 [대한민국영화목록(ㄱ)-365이전] 시트로 이동합니다. E3셀에 검색할 단어를 입력합니다. 예제로 '가문'을 입력하겠습니다.
예제파일 E3셀에 검색할 단어를 입력합니다.
B3셀에 아래 수식을 입력한 뒤, 아래 방향으로 자동채우기 합니다. 수식을 자동채우기 하면 영화제목에 '가문'을 포함할 경우 1씩 증가하는 순번이 반환됩니다.
=IF(ISNUMBER(SEARCH($E$3,C3)),MAX($B$2:B2)+1,0)
수식을 입력하면 단어를 포함하는 제목 옆에 순번이 출력됩니다.
예제파일의 G3셀에 아래 수식을 입력한 뒤, 아래방향으로 넉넉하게 자동채우기 합니다. 예제로 G31셀까지 자동채우기 하겠습니다. 수식을 자동채우기하면 '가문'을 포함하는 영화 제목만 나열되는 것을 확인할 수 있습니다.
=IFERROR(VLOOKUP(ROWS($G$2:G2),B:C,2,0),"")
특정 단어를 포함하는 영화 목록이 출력됩니다.
E3셀에 검색할 단어를 변경합니다. 단어를 변경하면 해당 단어를 포함하는 영화제목만 목록으로 반환되는 것을 확인할 수 있습니다.
단어를 변경하면 나타나는 영화 목록도 변경됩니다.
동적범위를 생성합니다. [수식] - [이름관리자]로 이동하거나, 단축키 CTRL + F3 키를 눌러 이름관리자를 실행합니다. 이후 [새로만들기] 버튼을 클릭하여 아래와 같이 새로운 이름정의범위를 생성합니다. 사용된 동적범위의 동작원리는 영상강의에서 자세히 설명해드렸습니다.
이름 : 유효성목록상자
참조대상 : =OFFSET('대한민국영화목록(ㄱ)-365이전'!$G$3,,,COUNTIF('대한민국영화목록(ㄱ)-365이전'!$G$3:$G$31,"*?"))
'유효성목록상자'라는 이름으로 이름정의범위를 생성합니다.
데이터유효성 목록 상자를 적용할 셀인 E3셀을 선택합니다. [데이터] - [데이터 유효성 검사]로 이동합니다. 제한대상은 '목록'으로 선택합니다.
데이터 유효성 검사로 목록을 적용합니다.
'원본' 을 선택한 뒤, 키보드 F3키를 누르면 방금 전에 생성한 이름정의범위가 나타납니다. '유효성목록상자'를 선택합니다.
원본 범위로 '유효성목록상자' 이름 정의범위를 선택합니다.
[오류메시지] 탭으로 이동한 뒤, '유효하지 않은 데이터를 입력하면 오류메시지 표시' 체크박스를 비활성화 합니다. [확인]을 눌러 종료합니다.
오류메시지 표시를 비활성화 합니다.
데이터 유효성 부분일치 검색 목록상자가 완성되었습니다. 원하는 단어를 검색한 뒤, ALT + ↓ 를 입력하면 해당 단어를 포함하는 영화제목이 목록상자로 출력됩니다.
부분일치 검색 데이터 유효성 목록이 완성되었습니다.

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

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

예제파일의 [대한민국영화목록(ㄱ)-365사용자] 시트로 이동합니다. D3셀에 검색할 단어를 입력합니다. 예제로 '결혼'을 입력하겠습니다.
부분일치로 검색할 단어를 입력합니다.
F3셀에 아래 수식을 입력합니다. 수식을 입력하면 '결혼'을 포함하는 영화제목이 동적배열로 반환됩니다.
=FILTER(B3:B132,ISNUMBER(SEARCH(D3,B3:B132)))
F3셀에 FILTER 함수를 입력합니다.
FILTER 함수 이외에도 UNIQUE 함수를 사용하여 고유값만 반환하거나, SORT 함수를 사용하여 영화제목을 오름차순 또는 내림차순으로 정렬할 수도 있습니다.
SORT 함수와 UNIQUE 함수를 같이 응용할 수도 있습니다.
데이터유효성 목록상자를 적용할 D3셀을 선택한 뒤, [데이터] - [데이터 유효성 검사]로 이동합니다. '제한대상은' 목록을 선택합니다. '원본'에는 아래 수식을 입력합니다. '#' 기호를 사용하면 배열로 반환된 범위를 동적으로 선택할 수 있습니다. (365 사용자)
=F3#
# 기호를 이용한 동적범위를 목록상자에 적용합니다.
[오류메시지] 탭으로 이동한 뒤, '유효하지 않은 데이터를 입력하면 오류메시지 표시' 체크박스를 비활성화 합니다. [확인]을 눌러 종료합니다.
유효성 오류메세지 표시를 비활성화 합니다.
데이터 유효성 부분일치 검색 목록상자가 마무리 되었습니다. 이처럼 365 사용자는 아주 간단하게 목록상자를 생성할 수 있습니다.
데이터 유효성 부분일치 검색 목록이 완성되었습니다.

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

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

예제파일의 [대한민국영화목록(ㄱ)-넓은범위적용] 시트로 이동합니다. D3셀과 D4셀에 검색할 단어를 입력합니다. 예제로 '가문'과 '결혼'을 입력하겠습니다.
예제파일의 D3, D4, D5... 셀에 검색할 단어를 입력합니다.
F3셀에 아래 수식을 입력한 뒤, 아래 방향으로 자동채우기 합니다.
=TRANSPOSE(FILTER($B$3:$B$132,ISNUMBER(SEARCH(D3,$B$3:$B$132))))
TRANSPOSE 함수와 FILTER 함수 응용공식을 F3셀에 입력한 뒤 자동채우기 합니다.
D3:D8 범위를 선택한 뒤, [데이터] - [데이터 유효성 검사]로 이동합니다. '제한대상은' 목록을 선택한 뒤, '원본'에는 아래 수식을 입력합니다.
=$F3#