엑셀 키워드 검색 기능 5분만에 만들어보기!!

엑셀 키워드 검색 포스트를 보고나면

데이터 시트 등에서 필터기능을 사용하지 않고 별도의 시트에서

함수만 사용하여 엑셀 키워드 검색을 할 수 있습니다!!

다음의 기능이 필요했던 분 들께 유용합니다!

데이터시트를 건드리지 않고 다른 시트등에서 데이터 시트를 참조하여 검색기능을 만들고 싶었던 분들

구글스프레드시트를 이용하는 분들 중 시트를 여러명과 공유하면서 데이터시트의 훼손없이 모두 조회가 가능한 기능이 필요했던 분들

엑셀 키워드 검색

KEYWORD예제.xlsx를 클릭하여 예제를 다운 받을 수 있습니다.

엑셀 2016이하 사용자분들은 FILTER함수를 사용하지 못하므로

엑셀 2019이상, OFFICE365 사용자분들만 정상적으로 사용가능합니다.

 

엑셀 2016이하 사용자 구글스프레드시트 링크 제공

엑셀 키워드 검색 예제

키워드검색 시트를 복사하여 사용 가능 합니다!~


이번 포스트에서 FILTER함수가 사용되었습니다.

FILTER함수는 엑셀 2019버전과 OFFICE365, 구글 스프레드시트에서만 지원

하고 있습니다!

엑셀 2016이하 버전 사용자들은 FILTER함수 대신 블로그내 VLOOKUP + SMALL/LARGE를 활용한 FILTER함수 효과내기 포스트를 참조하여 만들 수 있습니다!

엑셀 키워드 검색 기능을 만드는데 사용한 함수:

사용함수 : 클릭시 해당 링크 사용법 포스트를 보실 수 있습니다.

  1. FILTER함수
  2. SEARCH함수
  3. ISERR함수
  4. MID함수
  5. LEN함수
  6. IFERROR함수
  7. FIND -사용되진 않았지만 SEARCH함수와 사용법은 똑같기에 링크 참고하시기 바랍니다

31231231231zxc

예제에서는 한 시트에 빨간범위(데이터시트)와  파란범위(조회시트)가 같이 보이실텐데요,

실제로 쓰실 때는 데이터시트와 조회시트를 구분하여 만들어주시면 되겠습니다.

이번 포스트도 조회를 하는 시트와 데이터시트에서 조회할 수 있게 만드는 일종의 텍 작업을 하여 키워드 검색 기능을 만들어보도록 하겠습니다.


위에서 잠깐 설명 하였습니다만, 조회 시트에서 데이터 시트를 훼손 없이 키워드만으로 조회하려면 데이터시트 첫 번째 열에 텍작업을 할 열을 만들어 둡니다.(혹은 끝열)

아래에서 보는 F열 추출 항목을 보면 되겠습니다.

B7셀은 게임 이름으로 조회할 셀 입니다.

 

수식

=IF($B$7=””,”==”)

의 의미는

B7셀이 공백일시

“==”로 표시하는 것인데, 왜 굳이 공백으로 두어도 되는 것을 “==”로 써서 표시해두냐 하면

B7이 공백이면 조회시트에서 공백도 조회해버리기 때문입니다.

SEARCH함수는 찾으려는 텍스트열에서 찾을값을 못찾을 경우 오류를 반환합니다.

ISERR함수는 인수의 결과값이 오류면 TRUE를 반환하는 함수입니다.

오류를 반환하면 ISERR함수로 오류가 났는지 검사하여 TRUE가 됐다면 “==”로 표시하여 오류문구를 방지합니다.

키워드수식2
ISERR함수를 사용한 경우
키워드수식3
ISERR함수를 사용 안 한 경우

MID함수는 텍스트를 추출하는 함수로 많이 쓰이는 함수입니다.

이 MID함수로 텍스트열에서 키워드를 추출하는 것인데요,

간단하게 MID함수 사용법과 SEARCH,LEN함수를 알아보도록 하겠습니다.

 

MID(추출할 문자열,추출할 문자열의 시작위치, 추출할 문자열의 길이)

문자열에서 추출할 문자열의 시작위치를 설정하고 어디까지 추출할 것인지 길이를 설정하면 MID함수로 문자열 추출이 가능합니다.

SERACH함수는 찾을 텍스트가 찾으려는 텍스트열에서 몇 번째부터 시작하는 지

시작 위치를 구하는 함수며, LEN함수는 텍스트 길이를 반환하는 하는 함수입니다.

키워드수식4

수식을 보겠습니다.

MID(G9,

SEARCH($B$7,G9),

LEN($B$7)

문자열은 G9셀에 있는 문자열이고,

SEARCH함수로 문자열에서 키워드가 시작하는 시작 위치를 구하고,

 LEN함수로 키워드의 길이 만큼만 추출하여 추출 항목에서 표시합니다.


여기까지 왔다면 남은 조회 기능은 사실 FILTER함수를 사용하여 너무나도 쉽게 조회할 수가 있어요, 여기까지 오신 분들은 조금만 더 힘내시면 됩니다!!

그럼 바로 함수 사용법 알려드리고 설명하겠습니다.

FILTER(참조할 범위, 조건범위1=조건1,조건범위2=조건2…)

IFERROR(오류가 아닐시 실행할 수식, 오류일시 나타낼 문구)

수식을 보도록 하겠습니다.

FILTER

(G9:I22

->G9:I22범위는 조회할 범위입니다.

,F9:F22=B7)

->F9:F22는 위에서 작업했던 추출범위이며, B7은 키워드 조회셀 입니다.

위 필터함수를 보면 단 한 줄만 입력했는데도 조건에 맞는 모든 범위를 보여주는 것을

확인할 수 있는데요, FILTER함수는 정말 유용한 함수이니 꼭 알아두시길 바랍니다.

FILTER함수는 블로그내에 사용법과 활용법을 포스트해 둔 것이 있으니 아래 링크 참고하시기 바랍니다.

FILTER함수 사용법

FILTER함수 활용, 일자만 입력하면 자동 완성되는 자금일보 

이상으로 엑셀 키워드 검색 5분만에 만들어보기!! 포스트를 마치겠습니다!~