엑셀 '모든 버전'에서 가능한 "실시간 검색, 자동화 보고서" 만들기
엑셀 실시간 검색 보고서 목차 바로가기
특정 단어 포함 필터링 공식 요약
엑셀 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 함수와 드롭다운 상자를 사용해 실시간으로 데이터를 정렬하는 방법은 영상 강의를 참고하세요!
또한 SEARCH 함수를 FIND로는 대체가 않되나요?
단어 순서에 상관없이 검색하려면 OR 조건으로 공식을 사용하면 됩니다.
실방에서는 이 내용을 다루었으나, 정제된 내용이 아니여서 편집영상에는 담지 않았습니다.
아래 공식을 한번 사용해보세요.
OR조건 필터링 :
감사합니다!
위 적어주신 것으로 하면 안나오네요...
아래로 하면 자료가 잘 나오는데
OR 조건으로 하려면 어느 부분을 수정해야 할까요...?
=FILTER(작업중.xlsm!발주확정[입고여부]:작업중.xlsm!발주확정[악성위치2],ISNUMBER(SEARCH(B1,작업중.xlsm!발주확정[검색조건])))
공식의 조건부분인 "ISNUMBER(SEARCH(B1,작업중.xlsm!발주확정[검색조건]))" 을 위에 남겨드린 BYROW 공식으로 수정해보세요.
감사합니다.
이런거 혹시 보신적있나요?
매크로를 추가기능으로 간편하게 지원하는 사이트같은데 국내사용후기는 없고 정보찾기가 어렵네요
추가기능 프로그램을 후기를 충분히 찾아보신 후 잘 사용하면 업무에 많은 도움을 얻으실 수 있으니, 잘 살펴본 후 구매해보세요. :)
감사합니다.
혹시 [개발도구] 탭에서 '디자인 모드' 버튼이 활성화되어 있는지 한번 확인해보시길 바랍니다.
감사합니다.
Active X컨트롤 삭제했다가 다시 설정하면 텍스트 입력이 되는데, 또 저장하거나 파일을 닫았다가 다시 열면 텍스트 입력이 되지 않습니다 ㅠㅠ
말씀만 들어서는 정확한 원인 파악이 어려울 것 같습니다.
예상에는 사용하고 계신 파일의 일시적인 오류일 것 같아 보이는데요. 시트만 다른 파일로 옮겨서 한번 테스트해보시고, 만약 그래도 계속 오류가 있다면 홈페이지의 Q&A 게시판을 통해 문의를 남겨보시겠어요?
감사합니다.
개발도구 텍스트상자에서 검색값 입력할때 왼쪽 상단으로 텍스트가 입력되는데 왼쪽 중간으로 정렬하는 방법이 있는지요?
네 글자크기와 폰트를 변경할 수 있습니다.
[속성] - [Font] 에서 변경해보세요.
단, ActiveX 컨트롤만 글꼴을 변경할 수 있으며 양식컨트롤은 폰트 변경이 불가합니다.
감사합니다.
영감이 떠오르는 자료네요!
근데, 구글 스프레드 시트로는 적용 불가능할까요~?
구글시트에서도 구현 가능합니다.
다만, 입력창과 연동하기 위한 앱스크립트 작성이 필요할 것으로 보이고, 함수만으로 구현이 가능한지는 살펴봐야 할 것 같습니다.
회사 공유폴더로 원드라이브를 사용하고 계실 경우, VBA 코드 실행이 제한될 수 있습니다. 그럴 경우 원드라이브의 그룹 권한 정책 중 오피스 파일의 경로를 물리적 경로로 저장하도록 설정을 변경하면 해결 가능한 것으로 알고 있습니다.
자세한 내용은 아래 두 링크를 확인해보세요.
https://answers.microsoft.com/en-us/windows/forum/all/onedrive-issues-whilst-programming-automation-in/9af302aa-cb42-48f3-adef-50ab08a47279
https://www.windowscentral.com/how-use-onedrive-files-demand-windows-10
감사합니다.
추가로 한가지 질문 드립니다. 만약에 제품리스트에 날짜가 입력이 되어있다고 가정하면, 검색할때 날짜로 검색도 가능할까요??(기간을 정하는걸 의미합니다)
이전 댓글에 남겨드린 답글을 한번 확인해보시겠어요?
https://www.oppadu.com/%ec%a7%84%ec%a7%9c%ec%93%b0%eb%8a%94-%ec%8b%a4%eb%ac%b4%ec%97%91%ec%85%80-7-5-2/
감사합니다.
네 가능합니다.
=SUM(INDEX(범위#,0,열번호))
해시기호 사용법은 아래 링크를 참고해보시길 바랍니다.
초보자를 위한 엑셀 함수 자동화 : '#' 기호 완벽 정리 - 오빠두엑셀 (oppadu.com)
를 사용해보세요.