엑셀 '모든 버전'에서 가능한 "실시간 검색, 자동화 보고서" 만들기
엑셀 실시간 검색 보고서 목차 바로가기
특정 단어 포함 필터링 공식 요약
엑셀 2021 이후 버전 (FILTER 함수 활용)
엑셀 2019 이전 버전
'$출력범위, $찾을값, $찾을범위 모두 절대참조로 입력합니다.
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀고급] 엑셀 초보자를 위한, 실시간 검색 보고서 만들기예제파일[엑셀고급] 엑셀 초보자를 위한, 실시간 검색 보고서 만들기완성파일
영상 강의
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
특정 단어 포함 필터링 공식 살펴보기
실무에서 데이터 분석을 할 때, '특정 단어를 포함하는 조건'으로 데이터를 필터링해야 하는 상황이 종종 발생합니다. 그럴 때에는 자동 필터나 피벗테이블을 사용할 수 있지만, 다양한 조건을 반복적으로 검색하려면 매번 필터를 초기화하는 것이 상당히 번거로울 수 있는데요. 그럴 때, 오늘 소개해드리는 '특정 단어 포함 필터링 공식'을 사용하면 더욱 편리하게 데이터를 분석할 수 있습니다.
오빠두Tip : 고급필터의 기초 사용법은 아래 5분 영상 강의에서 자세히 정리했습니다. 궁금하신 분은 아래 링크를 확인하세요!👇
- 특정 단어 포함여부 확인 : 특정 단어의 포함 여부를 확인해야 할 때, "있어?(ISNUMBER/SEARCH)" 공식을 사용합니다. 예제파일을 실행한 후, [단어포함여부검색] 시트로 이동합니다. C8셀을 선택한 후, 아래와 같이 SEARCH 함수를 작성합니다.
=SEARCH(C7,C8)
'=SEARCH(찾을단어,문장,[시작위치])
SEARCH 함수는 문장에서 단어의 시작 위치를 반환합니다. 오빠두Tip : SEARCH 함수는 단어가 문장의 몇번째 위치에서 시작하는지 순번을 반환합니다. 만약 단어가 없으면, #VALUE! 오류를 반환합니다. - SEARCH 함수를 입력하면 결과값으로 4가 반환되며, 찾을 단어인 "HP"가 문장에서 4번째로 시작하는 것을 확인할 수 있습니다.
HP는 문장에서 4번째 위치에서 시작합니다. - C7셀의 값을 "애플"로 변경하면, 찾을 단어인 "애플"이 문장 안에 없으므로 #VALUE! 오류를 반환합니다.
단어가 문장 안에 없으면 VALUE오류를 반환합니다. - 이제 SEARCH 함수를 ISNUMBER 함수로 묶어주면 특정단어 포함여부 확인 공식이 완성됩니다. C8셀에 아래와 같이 수식을 작성합니다.
=ISNUMBER(SEARCH(C7,B8))
'=ISNUMBER(값)
SEARCH 함수를 ISNUMBER 함수로 묶으면 단어가 문장에 있을 시 TRUE를 반환합니다. 오빠두Tip : ISNUMBER 함수는 값이 숫자일 경우 TRUE, 그렇지 않을 경우 FALSE를 반환합니다. SEARCH 함수는 찾을 단어가 문장에 있을 경우에만 숫자를 반환하므로, ISNUMBER/SEARCH(있어?) 공식으로 특정 단어의 포함 여부를 확인할 수 있습니다. - 이제 C7셀의 값을 "삼성, 애플, 모니터" 등으로 변경하면 문장에 포함된 단어일 때만 TRUE가 반환됩니다.
문장이 단어를 포함될 경우 TRUE를 반환합니다. - 특정 단어 포함 필터링 공식 : 이제 ISNUMBER/SEARCH 공식을 FILTER 함수와 함께 사용하면, 특정 단어를 포함하는 항목만 필터링할 수 있습니다. [단어포함여부검색] 시트에서 H23셀을 선택한 후, 아래와 같이 FILTER 함수를 작성합니다.
=FILTER(B23:E29,ISNUMBER(SEARCH(F22,B23:B29)))
' =FILTER( 전체범위, 조건 )
→ FILTER( 전체범위, ISNUMBER(SEARCH(단어,조건범위)) )
FILTER 함수의 조건으로 ISNUMBER/SEARCH 공식을 작성합니다. 오빠두Tip : FILTER 함수는 전체 데이터 중, 조건을 만족하는 항목의 필터링 결과를 반환합니다. 위 공식은 '특정 단어의 포함 여부'가 FILTER 함수의 조건으로 작성되었습니다. - 이제 F22셀의 값을 "에이서, 맥북, LG" 등으로 변경하면, 해당 단어를 포함하는 제품만 실시간으로 필터링됩니다.
단어를 포함하는 제품을 모두 필터링됩니다. 오빠두Tip : FILTER 함수의 기초 사용법은 아래 영상강의에서 자세히 정리했습니다. 궁금하신 분은 아래 링크를 확인하세요!👇
모든 항목을 검색하는 필터링 보고서 만들기
이전 단계에서 알아본 '특정 단어포함 필터링 공식'을 실무에 적용하면 필터링 보고서를 손쉽게 완성할 수 있습니다.
- 모든 항목 검색을 위한 조건열 만들기 : 예제파일에서 [제품목록시트]로 이동한 후, 작성된 데이터를 표로 변환합니다. 시트에서 임의의 셀을 선택한 후, [삽입] 탭 - [표]를 클릭하거나 단축키 Ctrl + T를 눌러 표 만들기를 실행합니다. [표 만들기] 대화상자가 실행되면, '머리글 포함'을 체크한 후 [확인] 버튼을 클릭해서 범위를 표로 변환합니다.
범위를 표로 변환합니다. - 표를 선택하면, 메뉴 상단에 [테이블 디자인] 탭이 활성화됩니다. [테이블 디자인] 탭으로 이동한 후, 좌측 '표 이름'에서 표의 이름을 "제품목록"으로 변경합니다.
오빠두Tip : 표 기능은 파워쿼리와 코파일럿 등의 최신 기능을 사용하려면 반드시 알아야 할 핵심 개념입니다. 표 기능에 대한 자세한 설명은 아래 5분 기초 영상강의를 참고하세요!👇
- 이제 표 오른쪽에 모든 항목을 검색하기 위한 조건열을 추가합니다. H2셀을 선택한 후, 아래와 같이 수식을 작성합니다. 함수를 작성하면 그림과 같이 표의 모든 항목이 결합된 조건열이 완성됩니다.
[2021 이후 버전]
=CONCAT(제품목록[@[제조사]:[가격]])
[2019 이전 버전]
=[@제조사]&[@[제품명(한글)]]&[@[제품명(영문)]]&[@CPU]&[@RAM]&[@HDD]&[@가격]
'표 이름과 머리글에 따라 적절히 수정해서 작성합니다.
표 우측에 모든 항목을 검색할 조건열을 추가합니다. 오빠두Tip : CONCAT 함수는 범위의 모든 값을 결합하는 함수로, 엑셀 2021 이후 버전에서만 제공됩니다. - 마지막으로 조건 열의 머리글을 "검색조건"으로 변경하면 표가 완성됩니다.
임시로 추가한 열의 머리글을 '검색조건'으로 변경합니다. - 실시간 필터링 보고서 만들기 : 이제 모든 항목을 실시간으로 검색하는 필터링 보고서를 작성합니다. [검색보고서] 시트로 이동한 후, B2셀에 검색할 조건 단어를 입력합니다. 이번에는 예제로 "삼성"을 작성하였습니다.
검색보고서 시트로 이동한 후, B2셀에 검색할 단어를 입력합니다. - 2021 이후 버전 사용자 : C6셀을 선택한 후, 아래와 같이 함수를 작성합니다.
=FILTER( 제품목록[[제조사]:[가격]] , ISNUMBER(SEARCH(B2,제품목록[검색조건])) )
'=FILTER( 필터링범위 , 조건 )
B6셀이 단어 포함 필터링 공식을 작성합니다. - 함수를 입력하면 모든 항목을 검색하는 실시간 필터링 보고서가 완성됩니다. 이제 B2셀의 값을 변경하면, 모든 항목에서 해당 단어를 포함하는 데이터가 필터링됩니다.
이제 단어를 변경하면 모든 열에서 단어를 포함하는 데이터가 필터링합니다. - 2019 이전 버전 사용자 : FILTER 함수가 제공되지 않는 엑셀 2019 이전 버전 사용자는, 예제파일에 미리 적어드린 공식을 사용해서 실시간 필터링 보고서를 만들 수 있습니다. [2019이전버전] 시트로 이동한 후, J5셀에 적어드린 공식을 복사합니다. 공식을 복사할 시, 공식 앞에 있는 작은 따옴표(')는 제외하고 복사하는 것을 주의하세요!
엑셀 2019 이전버전 사용자는 시트에 미리 적어드린 공식을 사용합니다. - 복사한 공식을 B6셀에 붙여넣기 한 후, $출력범위, $찾을값, $찾을범위를 적절히 변경합니다. 이번 예제에서는 범위를 표로 참조하기 때문에 참조방식이 중요하지 않지만, 일반 셀 주소(예: A1:A10)로 범위를 참조할 경우 반드시 절대참조로 작성하는 것을 주의합니다. 완성된 공식은 다음과 같습니다.
=IFERROR(INDEX(제품목록[[제조사]:[가격]], SMALL(IF(ISNUMBER(SEARCH($B$2,제품목록[검색조건])),MATCH(ROW(제품목록[검색조건]), ROW(제품목록[검색조건])), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"")
'$출력범위: 제품목록[[제조사]:[가격]] / $찾을값: $B$2 / $찾을범위: 제품목록[검색조건]
- 작성한 수식을 Ctrl + Shift + Enter 로 입력하면 필터 결과의 첫번째 항목이 출력됩니다.
B6셀에 작성한 수식을 Ctrl + Shift + Enter로 입력합니다. - 이제 수식을 오른쪽과 아래쪽으로 자동채우기해서 필터링 보고서를 완성합니다. 단, 표를 참조한 범위는 오른쪽으로 드래그해서 자동채우기하면 참조하는 열이 한칸씩 이동하므로 잘못된 결과가 반환됩니다. 따라서, 오른쪽으로 자동채우기 할 때에는 B6:H6 범위를 선택한 후 Ctrl + R을 눌러 단축키로 빠른채우기를 실행합니다.
작성한 수식을 오른쪽으로 자동채우기 할 시, 단축키 Ctrl + R 을 사용합니다. - 수식을 모두 자동채우기 한 후, B2셀의 단어를 변경해보세요! 모든 항목 중, 해당 단어를 포함하는 데이터만 필터링하는 보고서가 완성됩니다.
수식을 자동채우기로 모두 입력한 후, 단어를 변경하면 실시간 필터링 보고서가 완성됩니다.
네이버 쇼핑 스타일 검색 보고서 만들기
엑셀의 '셀 서식'에서 몇 가지 설정만 변경하면, 누구나 간단하게 "네이버 쇼핑 스타일"의 세련된 보고서를 만들 수 있습니다.
- 네이버 쇼핑 스타일의 보고서 만들기 : [검색 보고서] 시트 또는 [2019이전버전] 시트에서 보고서 머리글을 작성할 B3셀을 선택한 후, 우클릭 - [셀 서식]으로 이동합니다.
그라데이션을 적용할 셀을 우클릭한 후 셀 서식으로 이동합니다. - [셀 서식] 대화상자가 실행되면 [채우기] 탭 - [채우기 효과]로 이동한 후, 그라데이션 색 - [다른 색]을 선택합니다.
채우기 탭 - 채우기 효과에서 각각의 색을 선택 - 다른 색으로 이동합니다. - "색1"과 "색2"로 각각 아래 적어드린 색상을 적용합니다. RGB/HEX 모두 동일한 색상이며, 엑셀 2021 이후 버전에서는 HEX코드로 색상을 편리하게 변경할 수 있습니다.
① [녹색] : RGB: (1, 175, 53) / HEX: #01AF35
② [보라색] : RGB: (117,98,191) / HEX: #7562BF
색1과 색2의 색상을 미리 적어드린 RGB(또는 HEX)값으로 변경합니다. - 색을 모두 변경하였으면 음영스타일의 방향으로 '세로'를 선택한 후, [확인] 버튼을 클릭해보세요! 네이버 쇼핑 스타일의 머리글이 완성됩니다.
음영 스타일을 세로로 변경한 후, [확인] 버튼을 클릭합니다. 오빠두Tip : 보고서를 작성할 때, 적절한 색상을 고르기 어려우셨다면 아래 3분 영상강의를 확인해보세요! 누구나 "그림판과 엑셀"만으로 멋진 색감을 구현하는 방법을 정리했습니다.👇
사용자 양식 컨트롤로 실시간 검색 보고서 만들기
마지막 단계로, 양식 컨트롤을 사용하여 입력창에 단어를 입력하는 즉시 데이터가 필터링되는 보고서를 완성해보겠습니다.
- 입력 텍스트상자 추가하기 : 엑셀 탭 메뉴에서 [개발도구] 탭 - [삽입]을 선택한 후, Active X 컨트롤에서 '텍스트 상자'를 선택합니다.
개발도구 - 삽입에서 ActiveX 컨트롤의 텍스트 상자를 추가합니다. 오빠두Tip : 만약 탭 메뉴에 '개발도구'가 안 보일 경우, 리본 메뉴를 우클릭 - [리본 메뉴 사용자 지정]에서 개발도구를 활성화합니다. - 시트 위로 커서를 드래그하면 아래 그림과 같이 값을 입력하는 텍스트상자가 추가됩니다.
시트 위로 커서를 드래그하면 입력창이 추가됩니다. - 텍스트 상자 속성 변경하기 : 텍스트 상자의 속성을 변경하면, 시트와 연동해서 사용할 수 있습니다. 텍스트 상자를 우클릭 - [속성]으로 이동합니다.
입력창을 우클릭 - [속성]으로 이동합니다. - 속성 창이 실행되면, 중간에 [Linked Cell] 항목이 있습니다. 이 항목의 값을 텍스트 상자와 연동할 셀의 주소로 변경합니다. 이번 예제에서 연동할 셀인 "B2"를 입력하면 B2셀에 작성된 값이 텍스트 상자에 자동으로 입력됩니다.
속성의 'Linked Cell' 항목을 입력창과 연동할 셀의 셀 주소로 변경합니다. - 마지막 단계로 텍스트 상자의 디자인을 조금 더 보기 좋게 꾸며주겠습니다. 속성에서 [Special Effect] 옵션을 'Sunken' 에서 'Flat'으로 변경하면 텍스트 상자가 더욱 깔끔하게 변경됩니다.
Special Effect의 옵션을 Flat으로 변경하면 디자인이 더욱 깔끔해집니다. - 속성 창을 종료한 후 [개발도구] 탭에서 '디자인모드'를 선택하여 디자인 모드를 종료하고 텍스트 상자에 검색할 단어를 입력해보세요! 검색한 단어가 실시간으로 필터링됩니다.
개발도구에서 '디자인모드'를 선택해서 편집모드를 해제한 후, 입력창에 단어를 변경합니다. 오빠두Tip : 더 나아가 SORT 함수와 드롭다운 상자를 사용해 실시간으로 데이터를 정렬하는 방법은 영상 강의를 참고하세요!
