엑셀 '모든 버전'에서 가능한 "실시간 검색 보고서" 만들기

특정 단어 포함 필터링 공식 요약

엑셀 2021 이후 버전 (FILTER 함수 활용)

엑셀 2019 이전 버전

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.

특정 단어 포함 필터링 공식 살펴보기

실무에서 데이터를 분석하다 보면, 특정 단어를 포함하는 조건으로 데이터를 필터링해야 하는 상황이 자주 발생합니다. 자동 필터나 피벗테이블로도 처리할 수 있지만, 다양한 검색어를 반복적으로 적용하려면 매번 필터를 초기화해야 하는 번거로움이 따릅니다. 이때 오늘 소개하는 '특정 단어 포함 필터링 공식'을 활용하면 보다 효율적으로 데이터를 분석할 수 있습니다.

1. 특정 단어 포함여부 확인 : 특정 단어의 포함 여부를 확인할 때는 "있어?(ISNUMBER/SEARCH)" 공식을 사용합니다. 예제파일을 실행한 후 [단어포함여부검색] 시트로 이동하고, C8셀을 선택해 아래와 같이 SEARCH 함수를 작성합니다.
   =SEARCH(C7,C8)
   '=SEARCH(찾을단어,문장,[시작위치])

   

   오빠두Tip : SEARCH 함수는 찾을 단어가 문장에서 시작하는 위치를 순번으로 반환합니다. 단어가 없으면 #VALUE! 오류를 반환합니다.
2. SEARCH 함수를 입력하면 결과값으로 4가 반환되며, 찾을 단어인 "HP"가 문장의 4번째 위치에서 시작한다는 것을 확인할 수 있습니다.

   

3. C7셀의 값을 "애플"로 변경하면, 찾을 단어인 "애플"이 문장에 포함되지 않으므로 #VALUE! 오류가 반환됩니다.

   

4. 이제 SEARCH 함수를 ISNUMBER 함수로 감싸주면 특정 단어 포함 여부 확인 공식이 완성됩니다. C8셀에 아래와 같이 수식을 작성합니다.
   =ISNUMBER(SEARCH(C7,B8))
   '=ISNUMBER(값)

   

   오빠두Tip : ISNUMBER 함수는 값이 숫자이면 TRUE, 그 외에는 FALSE를 반환합니다. SEARCH 함수가 단어를 찾았을 때만 숫자를 반환하므로, ISNUMBER/SEARCH 조합으로 특정 단어의 포함 여부를 정확하게 확인할 수 있습니다.
5. 이제 C7셀의 값을 "삼성, 애플, 모니터" 등으로 변경하면, 문장에 포함된 단어일 때만 TRUE가 반환됩니다.

   

6. 특정 단어 포함 필터링 공식 : ISNUMBER/SEARCH 조합을 FILTER 함수와 함께 사용하면, 특정 단어를 포함하는 항목만 실시간으로 필터링할 수 있습니다. [단어포함여부검색] 시트에서 H23셀을 선택한 후, 아래와 같이 FILTER 함수를 작성합니다.
   =FILTER(B23:E29,ISNUMBER(SEARCH(F22,B23:B29)))
   ' =FILTER( 전체범위, 조건 )
   → FILTER( 전체범위, ISNUMBER(SEARCH(단어,조건범위)) )

   

   오빠두Tip : FILTER 함수는 전체 데이터 중 조건을 만족하는 항목만 반환합니다. 위 공식에서는 '특정 단어 포함 여부'가 FILTER 함수의 조건으로 작성되었습니다.
7. 이제 F22셀의 값을 "에이서, 맥북, LG" 등으로 변경하면, 해당 단어를 포함하는 제품만 실시간으로 필터링됩니다.

   

   오빠두Tip : FILTER 함수의 기초 사용법은 아래 영상강의에서 자세히 정리했습니다. 함께 확인해보세요!👇
   

모든 항목을 검색하는 필터링 보고서 만들기

앞서 살펴본 '특정 단어 포함 필터링 공식'을 실무에 적용하면 모든 항목을 동시에 검색하는 필터링 보고서를 손쉽게 완성할 수 있습니다.

1. 모든 항목 검색을 위한 조건열 만들기 : 예제파일에서 [제품목록시트]로 이동한 후, 작성된 데이터를 표로 변환합니다. 시트에서 임의의 셀을 선택한 후, [삽입] 탭 - [표]를 클릭하거나 단축키 Ctrl + T를 눌러 표 만들기를 실행합니다. [표 만들기] 대화상자가 실행되면, '머리글 포함'을 체크한 후 [확인] 버튼을 클릭해 범위를 표로 변환합니다.

   

2. 표를 선택하면 메뉴 상단에 [테이블 디자인] 탭이 활성화됩니다. [테이블 디자인] 탭으로 이동한 후, 좌측 '표 이름'에서 표의 이름을 "제품목록"으로 변경합니다.
   
   오빠두Tip : 표 기능은 파워쿼리와 코파일럿 등 최신 기능을 다루기 위해 반드시 알아야 할 핵심 개념입니다. 표 기능에 대한 자세한 설명은 아래 5분 기초 영상강의를 참고하세요!👇
   
3. 이제 표 오른쪽에 모든 항목을 검색하기 위한 조건열을 추가합니다. H2셀을 선택한 후 아래와 같이 수식을 작성하면, 그림과 같이 표의 모든 항목이 결합된 조건열이 완성됩니다.
   [2021 이후 버전]
   =CONCAT(제품목록[@[제조사]:[가격]])
   [2019 이전 버전]
   =[@제조사]&[@[제품명(한글)]]&[@[제품명(영문)]]&[@CPU]&[@RAM]&[@HDD]&[@가격]
   '표 이름과 머리글에 따라 적절히 수정해서 작성합니다.

   

   오빠두Tip : CONCAT 함수는 범위 안의 모든 값을 하나의 텍스트로 결합하는 함수로, 엑셀 2021 이후 버전에서만 제공됩니다.
4. 마지막으로 조건 열의 머리글을 "검색조건"으로 변경하면 표가 완성됩니다.

   

5. 실시간 필터링 보고서 만들기 : 이제 모든 항목을 실시간으로 검색하는 필터링 보고서를 작성합니다. [검색보고서] 시트로 이동한 후, B2셀에 검색할 조건 단어를 입력합니다. 이번 예제에서는 "삼성"을 입력했습니다.

   

6. 2021 이후 버전 사용자 : C6셀을 선택한 후 아래와 같이 함수를 작성합니다.
   =FILTER( 제품목록[[제조사]:[가격]] , ISNUMBER(SEARCH(B2,제품목록[검색조건])) )
   '=FILTER( 필터링범위 , 조건 )

   

7. 함수를 입력하면 모든 항목을 검색하는 실시간 필터링 보고서가 완성됩니다. 이제 B2셀의 값을 변경하면, 모든 항목 중 해당 단어를 포함하는 데이터가 즉시 필터링됩니다.

   

8. 2019 이전 버전 사용자 : FILTER 함수가 제공되지 않는 엑셀 2019 이전 버전 사용자는, 예제파일에 미리 작성해 둔 공식을 활용해 실시간 필터링 보고서를 만들 수 있습니다. [2019이전버전] 시트로 이동한 후, J5셀에 적어드린 공식을 복사합니다. 이때 공식 앞의 작은 따옴표(')는 제외하고 복사하는 점에 주의하세요.

   

9. 복사한 공식을 B6셀에 붙여넣은 후, $출력범위, $찾을값, $찾을범위를 적절히 변경합니다. 이번 예제에서는 범위를 표로 참조하기 때문에 참조방식이 큰 영향을 주지 않지만, 일반 셀 주소(예: A1:A10)로 범위를 참조할 때는 반드시 절대참조로 작성하는 것에 주의합니다. 완성된 공식은 다음과 같습니다.

   =IFERROR(INDEX(제품목록[[제조사]:[가격]], SMALL(IF(ISNUMBER(SEARCH($B$2,제품목록[검색조건])),MATCH(ROW(제품목록[검색조건]), ROW(제품목록[검색조건])), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"")

   ---

   '$출력범위: 제품목록[[제조사]:[가격]] / $찾을값: $B$2 / $찾을범위: 제품목록[검색조건]

10. 작성한 수식을 Ctrl + Shift + Enter 로 입력하면 필터 결과의 첫 번째 항목이 출력됩니다.

    

11. 이제 수식을 오른쪽과 아래쪽으로 자동채우기해 필터링 보고서를 완성합니다. 단, 표를 참조한 범위는 오른쪽으로 드래그해서 자동채우기하면 참조하는 열이 한 칸씩 이동해 잘못된 결과가 반환됩니다. 따라서 오른쪽으로 자동채우기 할 때는 B6:H6 범위를 선택한 후 Ctrl + R 단축키로 빠른채우기를 실행합니다.

    

12. 수식을 모두 자동채우기한 후, B2셀의 단어를 변경해보세요. 모든 항목 중 해당 단어를 포함하는 데이터만 실시간으로 필터링되는 보고서가 완성됩니다.

    

네이버 쇼핑 스타일 검색 보고서 만들기

엑셀의 '셀 서식'에서 몇 가지 설정만 변경하면, 네이버 쇼핑 스타일의 세련된 보고서를 어렵지 않게 완성할 수 있습니다.

1. 네이버 쇼핑 스타일의 보고서 만들기 : [검색 보고서] 시트 또는 [2019이전버전] 시트에서 보고서 머리글을 작성할 B3셀을 선택한 후, 우클릭 - [셀 서식]으로 이동합니다.

   

2. [셀 서식] 대화상자가 실행되면 [채우기] 탭 - [채우기 효과]로 이동한 후, 그라데이션 색의 [다른 색]을 선택합니다.

   

3. "색1"과 "색2"에 각각 아래 색상을 적용합니다. RGB와 HEX 모두 동일한 색상이며, 엑셀 2021 이후 버전에서는 HEX 코드로 색상을 편리하게 입력할 수 있습니다.
   ① [녹색] : RGB: (1, 175, 53) / HEX: #01AF35
   ② [보라색] : RGB: (117,98,191) / HEX: #7562BF

   

4. 색을 모두 변경했다면 음영스타일의 방향으로 '세로'를 선택한 후 [확인] 버튼을 클릭합니다. 네이버 쇼핑 스타일의 머리글이 완성됩니다.
   
   오빠두Tip : 보고서를 작성할 때 적절한 색상 조합이 어렵게 느껴진다면 아래 3분 영상강의를 확인해보세요. 누구나 "그림판과 엑셀"만으로 멋진 색감을 구현하는 방법을 정리했습니다.👇
   

사용자 양식 컨트롤로 실시간 검색 보고서 만들기

마지막 단계로, 양식 컨트롤을 활용해 입력창에 단어를 입력하는 즉시 데이터가 필터링되는 보고서를 완성해 봅니다.

1. 입력 텍스트상자 추가하기 : 엑셀 탭 메뉴에서 [개발도구] 탭 - [삽입]을 선택한 후, ActiveX 컨트롤에서 '텍스트 상자'를 선택합니다.

   

   오빠두Tip : 탭 메뉴에 '개발도구'가 보이지 않을 경우, 리본 메뉴를 우클릭 - [리본 메뉴 사용자 지정]에서 개발도구를 활성화합니다.
2. 시트 위에서 커서를 드래그하면 아래 그림과 같이 값을 입력할 수 있는 텍스트 상자가 추가됩니다.

   

3. 텍스트 상자 속성 변경하기 : 텍스트 상자의 속성을 변경하면 시트와 연동해 사용할 수 있습니다. 텍스트 상자를 우클릭 - [속성]으로 이동합니다.
   
4. 속성 창이 실행되면 중간에 [Linked Cell] 항목이 있습니다. 이 항목의 값을 텍스트 상자와 연동할 셀의 주소로 변경합니다. 이번 예제에서 연동할 셀인 "B2"를 입력하면, B2셀에 작성된 값이 텍스트 상자에 자동으로 입력됩니다.

   

5. 마지막 단계로 텍스트 상자의 디자인을 한층 깔끔하게 다듬어 봅니다. 속성에서 [Special Effect] 옵션을 'Sunken'에서 'Flat'으로 변경하면 텍스트 상자가 보다 깔끔한 형태로 표시됩니다.

   

6. 속성 창을 종료한 후 [개발도구] 탭에서 '디자인모드'를 선택해 디자인 모드를 종료하고 텍스트 상자에 검색할 단어를 입력해보세요. 입력한 단어가 실시간으로 필터링됩니다.

   

   오빠두Tip : 더 나아가 SORT 함수와 드롭다운 상자를 사용해 실시간으로 데이터를 정렬하는 방법은 영상 강의에서 확인할 수 있습니다.