대용량 Microsoft Excel 작업 중에는 특정 데이터를 검색해야 하는 경우가 자주 발생합니다. Excel에서 검색 가능한 데이터베이스를 만드는 일은 어렵지 않으며, 시간도 크게 절약할 수 있습니다. 이번 글에서는 실제 예시와 함께 Excel에서 효율적으로 검색 가능한 데이터베이스를 구축하는 2가지 빠르고 실용적인 방법을 알아보겠습니다.
Excel에서 검색 가능한 데이터베이스를 만드는 2가지 방법
먼저, Armani School and College 학생들의 정보가 담긴 대형 워크시트가 있다고 가정해 보겠습니다. 학생의 이름, 학번(ID), 그리고 취득한 CGPA(학점 평점)가 각각 B, C, D열에 입력되어 있습니다. FILTER, ISNUMBER, SEARCH 함수를 조합하면 손쉽게 검색 가능한 데이터베이스를 만들 수 있으며, VBA 코드를 활용하는 방법도 있습니다. 아래는 오늘 실습에 사용할 데이터셋의 전체 모습입니다.
1. FILTER, ISNUMBER, SEARCH 함수를 조합하여 검색 가능한 데이터베이스 만들기
첫 번째 방법은 세 가지 함수를 조합하는 것입니다. 아래 지침을 하나씩 따라 해 보세요.
📌 1단계:
- Filter 시트에서 E5 셀을 선택합니다.
- 선택한 E5 셀에 다음 수식을 입력합니다.
=FILTER(B5:B16,ISNUMBER(SEARCH(Database!B4,B5:B16)),"Not Found")- 수식의 SEARCH 함수는 지정된 값을 검색하는 역할을 합니다.
- ISNUMBER 함수는 SEARCH 함수의 결과가 숫자이면 TRUE를 반환하고, 그렇지 않으면 FALSE를 반환합니다.
- FILTER 함수는 주어진 조건에 따라 데이터를 걸러 줍니다.
- 이제 키보드에서 Enter 키만 누르면 원하는 결과를 즉시 확인할 수 있습니다.
📌 2단계:
- Database 시트의 B4 셀을 선택한 후 다음 메뉴로 이동합니다.
데이터 → 데이터 도구 → 데이터 유효성 검사
- 데이터 유효성 검사 창에서 설정 탭을 선택하고, 드롭다운 화살표를 이용해 제한 대상 필드에서 목록을 고릅니다.
- 그다음 원본 필드에 아래 수식을 입력합니다.
=Filter!$E$5#- 이후 오류 메시지 탭으로 이동합니다.
- 잘못된 데이터가 입력되면 오류 메시지 표시 항목의 체크를 해제한 뒤 확인을 클릭합니다.
- 이제 검색 가능한 데이터베이스가 완성되었습니다. B4 셀에 검색어(예: R)를 입력하고 셀 오른쪽 아래에 나타나는 드롭다운 화살표를 클릭하면, 아래 스크린샷과 같이 관련된 모든 검색 결과가 표시됩니다.
함께 읽으면 좋은 글: Excel 데이터베이스 함수 활용법 (예제 포함)
관련 추천 자료
- Excel에서 데이터베이스 만드는 방법 (8단계 따라 하기)
- Excel에서 고객 데이터베이스 관리하기
- 자동으로 업데이트되는 Excel 데이터베이스 만드는 방법
2. VBA 코드를 실행하여 검색 가능한 데이터베이스 만들기
이번에는 드롭다운 화살표를 클릭하지 않고도, 구글 검색창처럼 검색 결과가 바로 표시되는 방식을 만들어 보겠습니다. 아래 단계를 따라 해 보세요.
📌 1단계:
- 먼저 앞선 방법과 마찬가지로 데이터 → 데이터 도구 → 데이터 유효성 검사까지 진행합니다.
- 그다음 Filter 시트의 E5 셀을 선택하고, 수식 → 이름 관리자로 이동합니다.
- 이름 관리자 창에서 새로 만들기를 클릭합니다.
- 새 이름 창에서 이름을 Dropdown_List로 변경합니다.
- 이어서 참조 대상 필드에 다음 수식을 입력한 후 확인을 누릅니다. 이 수식은 INDEX 함수와 COUNTIF 함수를 사용합니다.
=Filter!$E$5:$E$5:INDEX(States!$E$5:$E$17,COUNTIF(States!$E$5:$E$17,"?*"))
📌 2단계:
- Dropdown 시트로 이동한 뒤, 개발 도구 탭에서 삽입 → 콤보 상자(Combo Box)를 선택합니다.
- 마우스로 드래그하여 아래 그림처럼 콤보 상자의 크기를 적절히 조절합니다.
- 그러면 아래와 같이 새로운 콤보 상자가 생성된 것을 확인할 수 있습니다.
- 이제 콤보 상자를 마우스 오른쪽 버튼으로 클릭하고 속성(Properties)을 선택합니다.
- 속성 창에서 사전순(Alphabetic) 탭을 선택한 후 다음과 같이 값을 변경합니다: AutoWordSelect >> False, LinkedCell >> B4, MatchEntry >> 2 – fmMatchEntryNone.
- 아래의 VBA 코드를 복사합니다.
Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "Dropdown_List"
Me.ComboBox1.DropDown
End Sub- 그다음 콤보 상자를 더블클릭하면 Microsoft VBA 편집기의 새 모듈 화면으로 이동합니다. 빈 모듈에 복사한 코드를 붙여넣고 F5 키를 눌러 코드를 실행합니다.
- 마지막으로 완성된 검색 데이터베이스는 구글 검색과 같은 방식으로 동작합니다.
함께 읽으면 좋은 글: Excel VBA로 간단한 데이터베이스 만드는 방법
알아두면 좋은 팁
👉 Alt + F11 키를 동시에 누르면 Microsoft Visual Basic for Applications(VBA) 창을 바로 열 수 있습니다.
👉 리본 메뉴에 개발 도구 탭이 보이지 않는다면 직접 표시되도록 설정할 수 있습니다. 다음 경로로 이동하세요.
파일 → 옵션 → 리본 사용자 지정
👉 콤보 상자에 직접 입력하려면 개발 도구 탭에서 디자인 모드를 반드시 해제해야 합니다.
결론
지금까지 소개한 함수 조합과 VBA 코드를 활용한 검색 가능한 데이터셋 만들기 방법들이 여러분의 Excel 작업 생산성을 한층 높이는 데 도움이 되기를 바랍니다. 궁금한 점이나 문의 사항이 있다면 언제든지 댓글로 남겨주세요.
관련 글
- Excel에서 재고 데이터베이스 만드는 방법 (3가지 쉬운 방법)
- Excel에서 양식(Form)으로 데이터베이스 만들기
- 관계형 데이터베이스를 Excel로 만드는 방법 (쉬운 단계별 가이드)