엑셀 검색창 만들기 (단어 검색창 만들기)

안녕하세요 오랜만에 글을 올리는 가래라 입니다.

오늘은 엑셀로 검색창을 한번 만들어 보는 방법에 대해서 알아 보도록 하겠습니다.

저도 몰라서 인터넷에서 이것저것 적용해 보고 제가 원하는 대로 만들고 해서 만들어 봤는데요 사용해 보니 나쁘지 않은것 같아요 엑세스로 만들어도 될 것 같지만 저는 엑셀로 한번 만들어 보았습니다.

자 그럼 한번 처음부터 어떻게 시작 하는지 한번 보도록 하겠습니다.

 

1. 기본이 되는 데이터를 입력 합니다. 

 

기본적인 틀을 대충 만들어 봤습니다.

제가 예전에 토익 단어 준비 할 때 제 나름대로 정리한 자료를 데이터로 한번 사용해 봤습니다.

이 자료를 가지고 이제 검색 기능을 활성화할 방법을 사용해 보도록 하겠습니다.

 

2. 단어별 점수 매기기

 

파란색의 창은 찾을 단어가 있는 셀입니다.

(원래는 다른 시트에 따로 넣어 놨는데 보기 편할라고 검색 데이터 시트에 추가해 놓았습니다.

나중에 맨마지막에 파일을 올려 놓을 테니 참고하시면 될것 같아요

빨간색 창은 검색할 위치를 나타내는 셀입니다.

수식을 보면서 한번 살펴 보도록 하겠습니다.

=IFERROR(SEARCH($G$1,C6)+ROW()/100000,"")

IFERROR : 수식이 오류로 평가될 경우 지정한 값 ("") = 0 으로 반환 시켜 주기 위해 사용

SEARCH : 단어가 들어간 위치에 따라 값을 반환 합니다. 찾는 단어가 첫번째 부터 있다면 1을 반환 3번째 부터 있다면 3을 반환 하는 식입니다.

+ROW()/100000 : 동일한 단어가 있을 경우 또는 단어가 포함되어 있는 경우 서치 함수로만 사용하게 되면 똑같은 숫자가 반환 되는 것을 방지 하기 때문에 소수점 이하로 행 번호를 추가 하기 위해 넣었습니다.

위의 예시 같은 경우 행의 번호는 6번째 이기 때문에 6/100000 = 0.00006 을 서치 값에 추가로 넣게 되어 있습니다.

이럴경우 단어가 중복되거나 단어가 포함되어 있는 경우에도 랭크 함수로 순위를 매길때 중복이 되지 않기 때문에 사용하였습니다.

 

3.  단어별 순위 매기기

단어별로 점수를 주었다면 이 점수를 토대로 순위를 매겨 보도록 하겠습니다.

수식으로 살펴 보면 다음과 같습니다.

=IFERROR(RANK(B6,$B$4:$B$10085,1),"")

IFERROR : 수식이 오류로 평가될 경우 지정한 값 ("") = 0 으로 반환 시켜 주기 위해 사용

RANK : 2에서 매긴 점수를 기준으로 순위를 매기기 위해 사용

 

자 이렇게 되면 데이터가 들어갈 시트에서 할 작업은 끝이 났습니다.

결과 값을 보시면 위의 사진 처럼 출력 되게 됩니다.

적색창 = 검색 단어 : a

자주색창 = 해당 단어의 점수

청색창 = 해당 단어의 점수에 따른 순위

자 그럼 데이터 시트에 대한 내용이 끝났으니 검색 시트를 한번 만들어 보도록 하겠습니다.

 

4. 검색 시트 만들기

위에 사진은 결과 값을 출력한 사진이고

아래의 사진은 사용된 수식입니다.

행의 높이 : 130

열의 넓이 : 크기에 맞게 설정

해 놓았습니다.

행의 높이의 경우 원본 데이터의 내용을 다 가져 오기 위해서는 어느 정도의 행의 높이가 있어야 하기 때문에 조금 크게 설정을 따로 해 주었습니다.

수식의 내용을 살펴 보면 VLOOKUP 함수를 사용해 데이터 시트에 있는 정보를 가져 온다고 보시면 될 것 같아요

VLOOKUP 함수에 대해 알아 보시고 싶은시면 아래의 글을 참고해 주시면 감사하겠습니다.

https://coming1007.tistory.com/10

그럼 이제 전체 적인 수식에 대해서 설명 드리겠습니다.

=IFERROR(VLOOKUP(A3,'단어 모음'!A6:G10085,3,0),"")

IFERROR : 수식이 오류로 평가될 경우 지정한 값 ("") = 0 으로 반환 시켜 주기 위해 사용

VLOOKUP : 순번의 번호를 기준으로 랭크 함수로 나온 데이터와 값이 같은 경우 해당하는 셀의 데이터를 반환 하기 위해 사용

 

이외에 디자인 적인 부분은 원하시는 대로 설정 하시면 될 것 같습니다.

점수 나 순위 상태, 순번 등을 숨기셔도 되고 레이아웃을 변경하시는등의 사항은 성향에 맞게 구성하시면 될 것 같아요

이상으로 엑셀로 검색창 만드는 방법에 대해서 한번 알아 보았습니다.

 

 

오늘 쓰인 함수를 정리해 보자면

- IFERROR

- RANK

- SEARCH

- ROW

- VLOOKUP

https://coming1007.tistory.com/10  <---- VLOOKUP에 대해 알고 싶으시다면...

 

요렇게 5가지 되겠습니다.

VLOOKUP 외의 나머지 함수들에 대한 설명은 나중에 차근차근 올려 보도록 하겠습니다.

감사합니다~

 

오늘 올린 포스팅의 자료 파일 입니다.

엑셀로검색창만들기.xlsx

0.30MB