Excel 에서 검색 상자 만들기 – 단계별 가이드

Excel 에서 검색 상자를 만들면 스프레드시트의 기능이 향상되어 특정 데이터를 빠르게 필터링하고 접근할 수 있습니다. 이 가이드는 Excel 의 다양한 버전에 맞춰 검색 상자를 구현하는 여러 방법을 다룹니다。 초보자든 고급 사용자든 FILTER 함수， 조건부 서식 사용 및 다양한 수식을 사용하여 동적 검색 상자를 설정하는 단계를 안내합니다。

• FILTER 함수로 쉽게 검색 상자 만들기
  (Excel 2019 이상 및 Microsoft 365 용 Excel 에서 사용 가능)
• 조건부 서식 사용을(를) 사용하여 검색 상자 만들기
  (모든 Excel 버전에서 사용 가능)
• 수식 조합으로 검색 상자 만들기
  (모든 Excel 버전에서 사용 가능)

FILTER 함수로 쉽게 검색 상자 만들기

• 이 함수는 데이터가 변경될 때마다 결과를 자동으로 업데이트합니다。
• FILTER 함수는 설정한 조건과 일치하는 데이터셋의 항목 수에 따라 단일 행부터 수천 개의 결과까지 다양한 수의 결과를 반환할 수 있습니다。

여기서는 Excel 에서 FILTER 함수를 사용하여 검색 상자를 만드는 방법을 보여줍니다。

단계 1: 텍스트 상자 삽입 및 속성 구성

1. 「개발자」 탭으로 이동하여 「삽입」 > 「텍스트 상자(ActiveX 컨트롤)」을 클릭합니다。
   팁： 리본 리본 메뉴에 「개발자」 탭이 표시되지 않으면 다음 튜토리얼의 지침에 따라 활성화할 수 있습니다：Excel 리본에서 개발자 탭을 표시하는 방법은？
   
2. 커서가 십자 모양으로 바뀌면， 워크시트에서 텍스트 상자를 배치하려는 위치에 커서를 드래그하여 텍스트 상자를 그립니다。 텍스트 상자를 그린 후 마우스를 놓습니다。
   
3. 텍스트 상자를 마우스 오른쪽 버튼으로 클릭하고 상황에 맞는 메뉴에서 「속성」을 선택합니다。
   
4. 「속성」 창에서 「LinkedCell」 필드에 셀 참조를 입력하여 텍스트 상자를 셀에 연결합니다。 예를 들어， “J2"를 입력하면 텍스트 상자에 입력된 모든 데이터가 자동으로 J2 셀에 업데이트되며， 그 반대도 마찬가지입니다。
   
5. 「개발자」 탭에서 「디자인 모드」를 클릭하여 「디자인 모드」를 종료합니다。
   

이제 텍스트 상자에 텍스트를 입력할 수 있습니다。

단계 2: FILTER 함수 적용

1. FILTER 함수를 사용하기 전에 원본 헤더 행을 새 영역에 복사합니다。 여기에서는 검색 상자 아래에 헤더 행을 배치했습니다。
   팁： 이 방법을 사용하면 사용자가 원본 데이터와 동일한 열 머리글 아래에서 결과를 명확하게 확인할 수 있습니다。
   
2. 첫 번째 헤더 아래의 셀(예： 이 예제에서는 I5)을 선택하고， 다음 수식을 입력한 후 「Enter」 키를 눌러 결과를 확인합니다。

   =FILTER(Sheet2!$A$5:$G$281,Sheet2!$B$5:$B$281=J2,"No data found")

   
   위 스크린샷과 같이 텍스트 상자에 현재 입력이 없으므로 수식이 I5 에 「데이터를 찾을 수 없습니다」 결과를 표시합니다。

결과： 검색 상자 테스트

이제 검색 상자를 테스트해 보겠습니다。 이 예제에서는 검색 상자에 고객 이름을 입력하면 해당 결과가 즉시 필터링되어 표시됩니다。

조건부 서식 사용을 사용하여 검색 상자 만들기

조건부 서식 사용은 검색어와 일치하는 데이터를 강조 표시하여 간접적으로 검색 상자 효과를 낼 수 있습니다. 이 방법은 데이터를 필터링하지는 않지만 관련 셀을 시각적으로 안내합니다. 이 섹션에서는 Excel 에서 조건부 서식 사용을 사용하여 검색 상자를 만드는 방법을 보여줍니다。

단계 1: 텍스트 상자 삽입 및 속성 구성

1. 「개발 도구」 탭으로 이동하여 「삽입」 > 「텍스트 상자(ActiveX 컨트롤)」을 클릭합니다。
   팁： 「개발 도구」 탭이 리본에 표시되지 않으면 다음 자습서의 지침에 따라 활성화할 수 있습니다：Excel 리본에서 개발 도구 탭을 표시하는 방법은？
   
2. 커서가 십자 모양으로 바뀌면， 워크시트에서 텍스트 상자를 배치하려는 위치에 커서를 드래그하여 텍스트 상자를 그립니다。 텍스트 상자를 그린 후 마우스를 놓습니다。
   
3. 텍스트 상자를 마우스 오른쪽 버튼으로 클릭하고 상황에 맞는 메뉴에서 속성을 선택합니다。
   
4. 「속성」 창에서 「LinkedCell」 필드에 셀 참조를 입력하여 텍스트 상자를 셀에 연결합니다。 예를 들어， “J3"을 입력하면 텍스트 상자에 입력된 데이터가 자동으로 J3 셀에 반영되며， 그 반대도 마찬가지입니다。
   
5. 「개발자」 탭에서 「디자인 모드」를 클릭하여 「디자인 모드」를 종료합니다。
   

이제 텍스트 상자에 텍스트를 입력할 수 있습니다。

단계 2: 데이터 검색을 위한 조건부 서식 사용 적용

1. 검색할 전체 데이터 범위를 선택합니다。 여기서는 A3:G279 범위를 선택했습니다。
2. 「홈」 탭에서 클릭합니다 「조건부 서식 사용」 > 「새 규칙」。
   
3. 「새 서식 지정 규칙」 대화상자에서：
   1. 「규칙 유형 선택」 옵션에서 「수식을 사용하여 서식을 지정할 셀 결정」을 선택하세요。
   2. 「다음 수식이 TRUE인 경우 값을 서식 지정」란에 다음 수식을 입력합니다。

      =$B3=$J$3

      여기서 「$B3」은 범위 선택의 검색 조건과 일치시키려는 열의 첫 번째 셀을 나타내며， 「$J$3」는 검색 상자와 연결된 셀입니다。
   3. 「서식」 버튼을 클릭하여 검색 결과에 채울 색상을(를) 지정하세요。
   4. 「확인」 버튼을 클릭하세요。 스크린샷 참조：
      

결과

이제 검색 상자를 테스트해 보겠습니다. 이 예제에서는 검색 상자에 고객 이름을 입력하면 B 열에 해당 고객이 포함된 행이 지정된 채울 색상으로 즉시 강조 표시됩니다。

수식 조합으로 검색 상자 만들기

Excel 의 최신 버전을 사용하지 않거나 강조된 행 범위만 사용하고 싶지 않은 경우, 이 섹션에서 설명하는 방법이 유용할 수 있습니다. Excel 의 모든 버전에서 다양한 수식을 조합하여 기능적인 검색 상자를 만들 수 있습니다。 아래 단계를 따르세요。

단계 1: 검색 열에서 고유 값 목록 생성

1. 이 경우， 범위 “B4:B281"을 선택하여 새 워크시트로 복사합니다。
2. 새 워크시트에 범위를 붙여넣은 후 붙여넣은 데이터를 계속 선택한 상태에서 「데이터」 탭으로 이동하여 「중복 제거」을 선택합니다。
   
3. 열리는 「중복 제거」 대화상자에서 「확인」 버튼을 클릭합니다。
   
4. 「Microsoft Excel」 프롬프트 상자가 나타나 제거된 중복 항목 수를 보여줍니다。 「확인」을 클릭합니다。
   
5. 중복 항목 제거 후 헤더를 제외한 목록의 고유 값 전체를 선택하고， 「이름」 상자에 이름을 입력하여 이 범위에 이름을 지정합니다。 여기서는 범위 이름을 「Customer」로 지정했습니다。
   

단계 2: 콤보 상자 삽입 및 속성 구성

1. 검색하려는 데이터 집합이 포함된 워크시트로 돌아갑니다。 「개발 도구」 탭으로 이동하여 「삽입」 > 「콤보 상자(ActiveX 컨트롤)」을 클릭합니다。
   팁： 「개발 도구」 탭이 리본에 표시되지 않으면 다음 자습서의 지침에 따라 활성화할 수 있습니다：Excel 리본에서 개발 도구 탭을 표시하는 방법은？
   
2. 커서가 십자 모양으로 바뀌면， 검색 상자를 배치하려는 워크시트 위치에서 커서를 드래그하여 콤보 상자를 그립니다。 콤보 상자 그리기가 완료되면 마우스를 놓습니다。
   
3. 콤보 상자를 마우스 오른쪽 버튼으로 클릭하고 상황에 맞는 메뉴에서 「속성」을 선택합니다。
   
4. 「속성」 창에서：
   1. 「LinkedCell」 필드에 셀 참조를 입력하여 콤보 상자를 셀에 연결합니다。 여기서는 “M2"를 입력했습니다。
      팁： 이 필드를 지정하면 콤보 상자에 입력된 모든 데이터가 자동으로 M2 셀에 업데이트되며， 그 반대도 마찬가지입니다。
   2. 「ListFillRange」 필드에 단계 1 에서 고유 목록에 대해 지정한 「셀 이름」을 입력합니다。
   3. 「MatchEntry」 필드를 「2 – fmMatchEntryNone」으로 변경합니다。
   4. 「속성」 창을 닫습니다。
      
5. 「개발 도구」 탭에서 「디자인 모드」를 클릭하여 디자인 모드를 종료합니다。
   

이제 콤보 상자에서 항목을 선택하거나 검색할 텍스트를 직접 입력할 수 있습니다。

단계 3: 수식 적용

1. 원본 데이터 범위 옆에 세 개의 도우미 열을 만듭니다。 스크린샷 참조：
   
2. 첫 번째 도우미 열의 헤더 아래 셀(H5)에 다음 수식을 입력하고 「Enter」 키를 누릅니다。

   =ROWS($B$5:B5)

   여기서 “B5"는 검색할 열에서 첫 번째 고객 이름이 포함된 셀입니다。
   
3. 수식 셀의 오른쪽 하단 모서리를 두 번 클릭하면 아래 셀에 동일한 수식이 자동으로 채워집니다。
   
4. 두 번째 도우미 열 헤더 아래 셀(I5)에 다음 수식을 입력하고 「Enter」 키를 누릅니다。 그런 다음 수식 셀의 오른쪽 하단 모서리를 두 번 클릭하여 아래 셀에 동일한 수식을 자동으로 채웁니다。

   =IF(ISNUMBER(SEARCH($M$2,B5)),H5,"")

   여기서 “M2"는 콤보 상자와 연결된 셀입니다。
   
5. 세 번째 도우미 열 헤더 아래 셀(J5)에 다음 수식을 입력하고 「Enter」 키를 누릅니다。 그런 다음 수식 셀의 오른쪽 하단 모서리를 두 번 클릭하여 아래 셀에 동일한 수식을 자동으로 채웁니다。

   =IFERROR(SMALL($I$5:$I$281,H5),"") 

   
6. 원본 헤더 행을 새 영역에 복사합니다。 여기서는 검색 상자 아래에 헤더 행을 배치했습니다。
   
7. 첫 번째 헤더 아래 셀(예： 이 예제에서는 L5)을 선택하고， 다음 수식을 입력한 후 「Enter」 키를 누릅니다。

   =IFERROR(INDEX($A$5:$G$281,$J5,COLUMNS($L$4:L4)),"")

   여기서 “A5:G281"은 결과 셀에 표시하려는 전체 데이터 범위입니다。
   
8. 이 수식 셀을 선택하고 「채우기 핸들」을 오른쪽으로 드래그한 후 아래로 드래그하여 해당 열과 행에 수식을 적용합니다。
   
   참고:

   • 검색 상자에 입력이 없으므로 수식 결과는 원시 데이터를 표시합니다。
   • 이 방법은 대소문자를 구분하지 않으므로， 대문자나 소문자로 입력해도 텍스트를 일치시킵니다。

결과

이제 검색 상자를 테스트해 보겠습니다. 이 예제에서는 콤보 상자에서 고객 이름을 입력하거나 선택하면 B 열에 해당 고객 이름이 포함된 행이 필터링되어 결과 범위에 즉시 표시됩니다。

Excel 에서 검색 상자를 만들면 데이터와 상호작용하는 방식이 크게 향상되어 스프레드시트가 더욱 동적이고 사용자 친화적으로 변합니다. FILTER 함수의 간편함, 조건부 서식 사용의 시각적 도움, 또는 수식 조합의 다재다능함 중 어떤 방법을 선택하든 각각이 데이터 조작 능력을 강화하는 유용한 도구를 제공합니다. 이러한 기법들을 직접 실험해 보고 자신만의 특정 요구사항과 데이터 상황에 가장 적합한 방법을 찾아보세요. Excel 의 기능을 더 깊이 탐구하고 싶은 분들을 위해 당사 웹사이트에는 다양한 튜토리얼이 준비되어 있습니다。여기에서 더 많은 Excel 팁과 노하우를 확인하세요.

관련 문서