엑셀 데이터 유효성 부분일치 검색 - 목록상자 만들기
컨텐츠 정보
- 9,258 조회
- 1 댓글
- 0 추천
- 목록
본문
데이터유효성 부분일치 검색 동작원리 이해하기
강의 추천 영상 /// 엑셀의 기본 사용자
ISNUMBER + SEARCH 부분일치 검색공식
ISNUMBER / SEARCH 부분일치 검색 공식의 동작원리는 아래 관련 포스트에서 단계별로 자세히 설명해 드렸습니다. 특정 단어 포함여부 검색을 하는 ISNUMBER/SEARCH 공식의 자세한 설명이 궁금하신 분은 아래 관련 링크를 참고하세요.
IF/MAX 함수 + 확장범위 응용하기
IF 함수와 MAX 함수 그리고 확장범위를 같이 응용하면, 특정 조건을 만족할 경우에 1씩 증가하는 순번이 출력되도록 만들 수 있습니다. 공식은 아래와 같습니다.
위 공식에 사용된 확장범위에 대한 내용은 아래 관련 포스트에서 자세히 설명해드렸습니다.
예를 들어, 아래 공식을 시트의 A2셀부터 아래방향으로 자동채우기 한다고 가정하겠습니다.
- 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씩 증가하는 순번을 만들 수도 있습니다.
'함수를 입력한 뒤 아래 방향으로 자동채우기 하면 1,2,3,4... 순번이 생성됩니다.
'함수를 입력한 뒤 오른쪽 방향으로 자동채우기 하면 1,2,3,4... 순번이 생성됩니다.
COUNTIF 함수 동적범위 만들기
이전 강의에서 OFFSET 함수와 COUNTA 함수를 사용한 동적범위를 알아봤습니다. 대부분의 경우는 OFFSET / COUNTA 동적범위를 사용할 수 있지만, '수식으로 반환되는 값이 빈칸인 값을 제외해야 할 경우' 에는 OFFSET / COUNTA 동적범위를 사용할 수 없습니다.
COUNTA 함수는 겉으로는 '빈칸' 이지만 안에 수식이 입력되어 있으면 해당 셀을 개수에 포함합니다.
따라서, 수식의 결과값으로 빈칸을 반환하는 셀을 동적범위에서 제외해야 할 경우에는 아래에 적어드린 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#
관련자료
-
링크
-
첨부등록일 2020.10.16 12:03등록일 2020.10.16 12:03