Excel에서 검색 상자 만들기 – 단계별 가이드
Excel에서 검색 상자를 생성하면 데이터를 빠르게 필터링하고 특정 데이터에 접근하기가 쉬워지면서 스프레드시트의 기능이 향상됩니다. 이 가이드에서는 다양한 Excel 버전에 맞춰 검색 상자를 구현하는 여러 방법을 다룹니다. 초보자든 고급 사용자든, 이러한 단계는 FILTER 함수, 조건부 서식 및 다양한 수식과 같은 기능을 사용하여 동적인 검색 상자를 설정하는 데 도움이 될 것입니다.
- FILTER 함수로 쉽게 검색 상자 만들기 (Excel 2019 이상 및 Excel for Microsoft 365에서 사용 가능)
- 조건부 서식을 사용하여 검색 상자 만들기 (모든 Excel 버전에서 사용 가능)
- 수식 조합으로 검색 상자 만들기 (모든 Excel 버전에서 사용 가능)
FILTER 함수로 쉽게 검색 상자 만들기
- 데이터가 변경됨에 따라 이 함수는 출력을 자동으로 업데이트합니다.
- FILTER 함수는 데이터 세트에서 설정한 기준에 얼마나 많은 항목이 일치하느냐에 따라 하나의 행부터 수천 개까지 결과를 반환할 수 있습니다.
여기서는 FILTER 함수를 사용하여 Excel에서 검색 상자를 만드는 방법을 보여드리겠습니다.
1단계: 텍스트 상자 삽입 및 속성 구성
- "개발 도구" 탭으로 이동하여 "삽입" > "텍스트 상자 (ActiveX 컨트롤)"을 클릭합니다.
팁: 리본 메뉴에 "개발 도구" 탭이 표시되지 않는 경우, 다음 튜토리얼의 지침에 따라 활성화할 수 있습니다: Excel 리본 메뉴에서 개발 도구 탭을 어떻게 표시합니까?
- 커서가 십자 모양으로 바뀌며, 워크시트에서 원하는 위치에 텍스트 상자를 배치하기 위해 커서를 드래그해야 합니다. 텍스트 상자를 그린 후에는 마우스를 놓습니다.
- 텍스트 상자를 마우스 오른쪽 버튼으로 클릭하고 컨텍스트 메뉴에서 "속성"을 선택합니다.
- "속성" 창에서 "LinkedCell" 필드에 셀 참조를 입력하여 텍스트 상자를 셀에 연결합니다. 예를 들어, "J2"를 입력하면 텍스트 상자에 입력된 모든 데이터가 자동으로 J2 셀에 업데이트되고 반대로도 적용됩니다.
- "개발 도구" 탭 아래의 "디자인 모드"를 클릭하여 "디자인 모드"를 종료합니다.
이제 텍스트 상자에 텍스트를 입력할 수 있습니다.
2단계: FILTER 함수 적용
- FILTER 함수를 사용하기 전에 원래의 헤더 행을 새 영역에 복사합니다. 여기서는 검색 상자 아래에 헤더 행을 배치합니다.
팁: 이 방법을 사용하면 사용자가 원래 데이터와 동일한 열 제목 아래에서 결과를 명확하게 확인할 수 있습니다.
- 첫 번째 헤더 아래의 셀 (예: I5)을 선택하고 해당 수식을 입력한 후 "Enter" 키를 눌러 결과를 얻습니다.
=FILTER(Sheet2!$A$5:$G$281,Sheet2!$B$5:$B$281=J2,"No data found")
위 스크린샷에서 보이는 것처럼, 현재 텍스트 상자에 입력값이 없으므로 수식은 I5에 "데이터를 찾을 수 없습니다"라는 결과를 표시합니다.
- 이 수식에서:
- "Sheet2!$A$5:$G$281": $A$5:$G$281은 Sheet2에서 필터링하고자 하는 데이터 범위입니다.
- "Sheet2!$B$5:$B$281=J2": 이 부분은 범위를 필터링하는 기준을 정의합니다. Sheet2의 B열에서 5행부터 281행까지 각 셀을 확인하여 J2 셀의 값과 일치하는지 확인합니다. J2는 검색 상자와 연결된 셀입니다.
- "데이터를 찾을 수 없습니다": FILTER 함수가 B열의 값이 J2 셀의 값과 일치하는 행을 찾지 못하면 "데이터를 찾을 수 없습니다"를 반환합니다.
- 이 방법은 대소문자를 구분하지 않으므로, 대문자나 소문자로 입력하더라도 텍스트를 일치시킬 수 있습니다.
결과: 검색 상자 테스트
이제 검색 상자를 테스트해 보겠습니다. 이 예제에서는 검색 상자에 고객 이름을 입력하면 해당 결과가 즉시 필터링되어 표시됩니다.
조건부 서식을 사용하여 검색 상자 만들기
조건부 서식은 검색어와 일치하는 데이터를 강조 표시하여 간접적으로 검색 상자 효과를 만들어냅니다. 이 방법은 데이터를 필터링하지 않고 관련 셀을 시각적으로 안내합니다. 이 섹션에서는 Excel에서 조건부 서식을 사용하여 검색 상자를 만드는 방법을 보여드립니다.
1단계: 텍스트 상자 삽입 및 속성 구성
- "개발 도구" 탭으로 이동하여 "삽입" > "텍스트 상자 (ActiveX 컨트롤)"을 클릭합니다.
팁: 리본 메뉴에 "개발 도구" 탭이 표시되지 않는 경우, 다음 튜토리얼의 지침에 따라 활성화할 수 있습니다: Excel 리본 메뉴에서 개발 도구 탭을 어떻게 표시합니까?
- 커서가 십자 모양으로 바뀌며, 워크시트에서 원하는 위치에 텍스트 상자를 배치하기 위해 커서를 드래그해야 합니다. 텍스트 상자를 그린 후에는 마우스를 놓습니다.
- 텍스트 상자를 마우스 오른쪽 버튼으로 클릭하고 컨텍스트 메뉴에서 "속성"을 선택합니다.
- "속성" 창에서 "LinkedCell" 필드에 셀 참조를 입력하여 텍스트 상자를 셀에 연결합니다. 예를 들어, "J3"을 입력하면 텍스트 상자에 입력된 모든 데이터가 자동으로 J3 셀에 업데이트되고 반대로도 적용됩니다.
- "개발 도구" 탭 아래의 "디자인 모드"를 클릭하여 "디자인 모드"를 종료합니다.
이제 텍스트 상자에 텍스트를 입력할 수 있습니다.
2단계: 데이터 검색을 위한 조건부 서식 적용
- 검색할 전체 데이터 범위를 선택합니다. 여기서는 A3:G279 범위를 선택합니다.
- "홈" 탭 아래에서 "조건부 서식" > "새 규칙"을 클릭합니다.
- "새 서식 규칙" 대화 상자에서:
- "규칙 유형 선택" 옵션에서 "서식을 지정할 셀을 결정하는 수식 사용"을 선택합니다.
- "이 수식이 참인 경우 값을 서식 지정" 상자에 다음 수식을 입력합니다.
=$B3=$J$3여기서 "$B3"은 선택된 범위에서 검색 기준과 일치시키려는 열의 첫 번째 셀을 나타내며, "$J$3"은 검색 상자와 연결된 셀입니다. - "서식" 버튼을 클릭하여 검색 결과에 대한 채우기 색상을 지정합니다.
- "확인" 버튼을 클릭합니다. 스크린샷 보기:
결과
이제 검색 상자를 테스트해 보겠습니다. 이 예제에서는 검색 상자에 고객 이름을 입력하면 B열에 해당 고객이 포함된 행이 지정된 채우기 색상으로 즉시 강조 표시됩니다.
수식 조합으로 검색 상자 만들기
최신 버전의 Excel을 사용하지 않거나 행을 강조 표시하는 것 외에도 다른 기능을 원한다면, 이 섹션에서 설명하는 방법이 도움이 될 수 있습니다. Excel의 모든 버전에서 수식 조합을 사용하여 기능적인 검색 상자를 만들 수 있습니다. 아래 단계를 따르세요.
1단계: 검색 열에서 고유한 값 목록 만들기
- 이 경우, "B4:B281" 범위를 선택하고 새 워크시트에 복사합니다.
- 새 워크시트에 범위를 붙여넣은 후, 붙여넣은 데이터를 선택한 상태로 유지하고 "데이터" 탭으로 이동하여 "중복 제거"를 선택합니다.
- 열리는 "중복 제거" 대화 상자에서 "확인" 버튼을 클릭합니다.
- 그러면 "Microsoft Excel" 알림 상자가 팝업되어 제거된 중복 항목 수를 알려줍니다. "확인"을 클릭합니다.
- 중복을 제거한 후, 헤더를 제외한 목록의 모든 고유한 값을 선택하고 "이름" 상자에 입력하여 이 범위에 이름을 할당합니다. 여기서는 범위를 "Customer"로 명명했습니다.
2단계: 콤보 상자 삽입 및 속성 구성
- 검색하려는 데이터 세트가 있는 워크시트로 돌아갑니다. "개발 도구" 탭으로 이동하여 "삽입" > "콤보 상자 (ActiveX 컨트롤)"을 클릭합니다.
팁: 리본 메뉴에 "개발 도구" 탭이 표시되지 않는 경우, 다음 튜토리얼의 지침에 따라 활성화할 수 있습니다: Excel 리본 메뉴에서 개발 도구 탭을 어떻게 표시합니까?
- 커서가 십자 모양으로 바뀌며, 워크시트에서 원하는 위치에 콤보 상자를 배치하기 위해 커서를 드래그해야 합니다. 콤보 상자를 그린 후에는 마우스를 놓습니다.
- 콤보 상자를 마우스 오른쪽 버튼으로 클릭하고 컨텍스트 메뉴에서 "속성"을 선택합니다.
- "속성" 창에서:
- "LinkedCell" 필드에 셀 참조를 입력하여 콤보 상자를 셀에 연결합니다. 여기서는 "M2"를 입력합니다.
팁: 이 필드를 지정하면 콤보 상자에 입력된 모든 데이터가 자동으로 M2 셀에 업데이트되고 반대로도 적용됩니다.
- "ListFillRange" 필드에 1단계에서 고유한 목록에 지정한 "범위 이름"을 입력합니다.
- "MatchEntry" 필드를 "2 – fmMatchEntryNone"으로 변경합니다.
- "속성" 창을 닫습니다.
- "LinkedCell" 필드에 셀 참조를 입력하여 콤보 상자를 셀에 연결합니다. 여기서는 "M2"를 입력합니다.
- "개발 도구" 탭 아래의 "디자인 모드"를 클릭하여 디자인 모드를 종료합니다.
이제 콤보 상자에서 항목을 선택하거나 텍스트를 입력하여 검색할 수 있습니다.
3단계: 수식 적용
- 원래 데이터 범위 옆에 세 개의 도우미 열을 생성합니다. 스크린샷 보기:
- 첫 번째 도우미 열 헤더 아래의 셀 (H5)에 다음 수식을 입력하고 "Enter"를 누릅니다.
=ROWS($B$5:B5)여기서 "B5"는 검색할 열의 첫 번째 고객 이름이 있는 셀입니다.
- 수식 셀의 오른쪽 아래 모서리를 더블 클릭하면 아래 셀이 자동으로 동일한 수식으로 채워집니다.
- 두 번째 도우미 열 헤더 아래의 셀 (I5)에 다음 수식을 입력하고 "Enter"를 누릅니다. 그런 다음 수식 셀의 오른쪽 아래 모서리를 더블 클릭하여 아래 셀을 동일한 수식으로 자동 채웁니다.
=IF(ISNUMBER(SEARCH($M$2,B5)),H5,"")여기서 "M2"는 콤보 상자와 연결된 셀입니다.
- 세 번째 도우미 열 헤더 아래의 셀 (J5)에 다음 수식을 입력하고 "Enter"를 누릅니다. 그런 다음 수식 셀의 오른쪽 아래 모서리를 더블 클릭하여 아래 셀을 동일한 수식으로 자동 채웁니다.
=IFERROR(SMALL($I$5:$I$281,H5),"")
- 원래 헤더 행을 새 영역에 복사합니다. 여기서는 검색 상자 아래에 헤더 행을 배치합니다.
- 첫 번째 헤더 아래의 셀 (예: L5)을 선택하고 해당 수식을 입력한 후 "Enter" 키를 누릅니다.
=IFERROR(INDEX($A$5:$G$281,$J5,COLUMNS($L$4:L4)),"")여기서 "A5:G281"은 결과 셀에 표시하려는 전체 데이터 범위입니다.
- 이 수식 셀을 선택하고 "채우기 핸들"을 오른쪽으로 드래그한 다음 아래로 드래그하여 해당 열과 행에 수식을 적용합니다.
참고:- 검색 상자에 입력값이 없으므로 수식의 결과는 원본 데이터를 표시합니다.
- 이 방법은 대소문자를 구분하지 않으므로, 대문자나 소문자로 입력하더라도 텍스트를 일치시킬 수 있습니다.
결과
이제 검색 상자를 테스트해 보겠습니다. 이 예제에서는 콤보 상자에서 고객 이름을 입력하거나 선택하면 B열에 해당 고객 이름이 포함된 행이 필터링되어 결과 범위에 즉시 표시됩니다.
Excel에서 검색 상자를 생성하면 데이터와 상호 작용하는 방식이 크게 개선되어 스프레드시트가 더욱 역동적이고 사용자 친화적이 됩니다. FILTER 함수의 간결함, 조건부 서식의 시각적 지원 또는 수식 조합의 다양성을 선택하든, 각 방법은 데이터 조작 능력을 향상시키는 귀중한 도구를 제공합니다. 이러한 기술을 실험하여 자신의 특정 요구와 데이터 시나리오에 가장 적합한 방법을 찾아보세요. Excel의 기능을 더 깊이 탐구하고 싶은 분들을 위해 저희 웹사이트에는 다양한 튜토리얼이 준비되어 있습니다. 여기에서 더 많은 Excel 팁과 요령을 알아보세요.