원하는 내용 찾기(찾기 기능, 고급 필터)

안녕하세요.

 

질문에 대한 답변을 정리해 올립니다.

 

실습 파일 다운로드

원하는 내용 찾기(찾기, 고급필터).xlsx

0.34MB

질문 내용은

예시파일에서 '찾을값1+찾을값2'의 문구가 포함되어 있는 '상품명'의 값을 VLOOKUP 함수처럼 되게 하고 싶은데요

입니다.

 

예제 파일입니다.

함수를 이용해서 처리할 방법은 없어 보입니다.

 

찾을 내용이 많지 않다면 가장 쉬운 방법이 찾기 기능을 이용하는 것입니다.

 

 

찾기 기능 이용

A 열문자를 클릭해 A열 전체를 선택한 상태에서 [홈]-[찾기 및 선택]-[찾기]를 선택합니다.

찾을 범위를 A열로 제한하기 위해 A열을 범위 선택한 상태에서 찾기를 실행하는 것입니다.

 

[찾기 및 바꾸기] 대화상자에서 [옵션] 단추를 눌러 '자세한 옵션'을 표시합니다.

확인할 부분이 하나 있는데, '전체 셀 내용 일치' 항목 입니다.

이 항목이 선택되어 있다면 셀 전체 내용이 일치하는 것을 찾고, 선택되어 있지 않다면 셀 내용 일부가 일치하는 경우도 해당하는 셀로 찾게 됩니다.

 

우리가 찾을 내용은 셀 내용 일부를 확인하는 것으로 선택되어 있지 않아야 합니다.

 

찾을값과 찾을값2를 동시에 찾을 수는 없기 때문에 찾을값을 [찾을 내용]에 입력해 [모두 찾기]를 선택합니다.

 

찾아진 결과는 1개 입니다.

[A2] 셀인데, [A2] 셀 내용엔 찾을값2가 포함되어 있지 않습니다.

아마도 예제 파일이라서 데이터를 모두 표시하지 않았기 때문인 것 같습니다.

 

이런 식으로 찾을 값을 하나씩 넣어 확인합니다.

 

찾기가 실행된 뒤에는 A열 범위 선택한 것이 해제되므로 다시 A 열문자를 클릭해 범위 선택하고 [찾을 내용]에 [C4]셀 내용을 입력해 [모두 찾기]를 선택합니다.

5개의 셀을 찾았다고 메시지가 표시됩니다.

 

[찾기 및 바꾸기] 대화상자 크기를 조절해 검색 결과를 자세히 볼 수 있도록 조절합니다.

찾은 내용을 확인해 보면 여기에도 찾을값2에 해당하는 셀이 없다는 것을 알 수 있습니다.

 

찾기 기능만으로 충분히 원하는 결과를 얻을 수 있습니다.

 

 

고급 필터, FIND 함수 이용

찾아진 결과가 많은 경우나 찾은 결과를 별도 셀에 보관해야 하는 경우엔 [고급 필터] 기능을 이용할 수 있습니다.

 

고급 필터는 조건을 별도 셀에 나타내야 합니다.

 

[C8] 셀부터 조건을 적기로 하고, [C8] 셀에는 조건이 적용될 필드명을 나타내겠습니다.

수식을 이용해 조건을 작성할 예정이라 실제 필드명인 '상품명'만 제외하고 어떤 이름을 적어도 됩니다.

 

보통 많이 사용되는 '조건'이라고 적겠습니다.

[C9] 셀에 수식을 작성합니다.

 

=fi를 입력하면 함수 목록이 표시되고 FIND를 더블 클릭합니다.

 

단축키 <Shift + F3>을 눌러 함수 인수 대화상자를 표시합니다.

첫번째 인수 Find_text에 커서를 두고 [C2] 셀을 클릭해 입력하고 단축키 <F4>를 눌러 절대 참조 합니다.

두번째 인수 Within_text에 커서를 두고 [A2] 셀을 클릭해 입력합니다.

 

FIND 함수를 고급 필터 조건으로 사용하는데, 찾으려는 텍스트는 절대 참조되고, 찾으려는 텍스트가 포함된 텍스트는 [A2] 셀부터 아래로 차례대로 비교되어야 합니다.

 

세번째 인수 Start_num는 생략하면 1로 지정된다.

여기까지 입력한 뒤 [확인]을 눌러도 되지만, 고급 필터 조건으로 함수를 입력하고 있기 때문에 참/거짓으로 판단되도록 수식을 작성하려고 합니다.

 

수식 입력줄에서 현재 입력된 수식 제일 뒤를 클릭해 '>0'을 입력하면 참/거짓으로 나타나는 명제가 됩니다.

[확인]을 눌러 마무리 합니다.

조건 수식입니다.

=FIND($C$2,A2)>0

[F1] 셀에 커서를 두고 [데이터]-[정렬 및 필터]-[고급]을 실행합니다.

'다른 장소에 복사'를 선택하고, [목록 범위] 항목에 커서를 두고 [A1] 셀을 선택한 뒤 단축키 <Ctrl + Shift + 아래쪽 화살표>를 눌러 범위 선택합니다.

[조건 범위]는 [C8:C9] 셀 범위를 선택하고, [복사 위치]는 [F1] 셀을 선택합니다.

[확인]을 눌러 고급 필터를 실행 합니다.

찾을값 두번째인 [C3] 셀 값으로 필터를 하려면 [C9] 셀 수식에서 $C$2를 $C$3으로 바꾼 뒤 현재 결과값이 표시되고 있는 F열을 선택해 삭제하고 고급 필터를 다시 실행합니다.

 

[C9] 셀 수식에서 $C$2를 $C$3으로 바꾼 뒤 <Enter>키를 누르면 수식에 #VALUE! 오류메시지가 표시되는데 상관없습니다.

수식에 오류가 표시된 이유는 FIND 함수로 문자를 찾을 때 찾는 내용이 없으면 오류메시지가 표시되기 때문입니다.

 

고급 필터를 실행해 결과를 표시합니다.

 

찾을값과 찾을값2 둘 다 찾는 경우엔 수식을 변경하면 됩니다.

=AND(FIND($C$2,A2),FIND($D$2,A2))

 

 

고급 필터에서 여러 개의 조건을 한 번에 수식으로 나타내기

[C1:D5] 셀 범위의 조건을 한 번에 처리하도록 [C9] 셀 조건 수식을 작성해 보겠습니다.

 

[C2:D5] 셀 범위 조건에서 같은 행, 다른 열은 And 연산이고, 다른 행은 Or 연산입니다.

 

여러 개의 조건을 한 번에 작성하려면 OR 함수로 묶어야 합니다.

똑 같은 수식이 여러 번 반복되고 행이 4개나 되어 수식 길이이 길어질 것 같으므로 수식 입력줄을 확장해 여러 행으로 작성하겠습니다.

 

[C9] 셀에 커서를 두고 단축키 <Ctrl + Shift + U>를 누르면 수식 입력줄이 확장 됩니다.

수식 입력줄의 =(이퀄) 뒤에 커서를 두고 OR(를 입력합니다.

함수 인수 대화상자를 이용해도 되지만 수식을 복사해서 사용하기 위해 확장된 수식 입력줄에서 바로 작성을 합니다.

앞에서 작성할 때는 FIND 함수를 실행한 결과로 오류메시지가 나왔어도 해당 항목은 어차피 일치하는 항목이 아니었기 때문에 상관없었습니다.

하지만 이번에는 여러 조건을 OR 연산으로 묶을 것이기 때문에 FIND 함수 결과가 오류메시지로 나오면 전체 수식 결과가 오류로 표시됩니다.

그래서 FIND 함수를 실행한 결과가 오류가 나오는 경우엔 거짓 값으로 나타내는 작업을 해야 합니다.

 

거짓 값은 FALSE로 나타내야 하지만, 수식을 간단하게 나타내기 위해 0을 넣습니다.

 

IFERROR 함수를 이용하면 수식에 오류가 발생한 경우 간단하게 대처할 수 있습니다.

 

수식 입력줄에서 FIND 함수가 사용된 두 부분을 IFERROR 함수로 묶어 오류 처리를 합니다.

 

수식 줄 끝에 ,(쉼표)를 넣어 첫번째 OR 함수 인수를 완성합니다.

=or(AND(iferror(FIND($C$2,A2),0),iferror(FIND($D$2,A2),0)),

AND 함수부터 줄 끝까지 범위 선택해 복사 합니다.

 

수식 줄 끝에서 단축키 <Alt + Enter>를 입력해 하나의 셀 안에서 줄바꿈을 하고 붙여넣기 합니다.

 

 

마찬가지 방법으로 단축키 <Alt + Enter>를 2번 더 입력하고 붙여넣기도 2번 더 합니다.

작성할 조건이 총 4개이기 때문입니다.

현재까지 만들어진 수식입니다.

=OR(AND(IFERROR(FIND($C$2,A2),0),IFERROR(FIND($D$2,A2),0)),
AND(IFERROR(FIND($C$2,A2),0),IFERROR(FIND($D$2,A2),0)),
AND(IFERROR(FIND($C$2,A2),0),IFERROR(FIND($D$2,A2),0)),
AND(IFERROR(FIND($C$2,A2),0),IFERROR(FIND($D$2,A2),0)),

마지막 줄 ,(쉼표)를 지우고 대신 )(닫는 괄호)를 입력해 앞에서 시작한 OR 함수를 끝냅니다.

 

두번째 줄 $C$2를 $C$3으로 바꾸고, $D$2를 $D$3으로바꿉니다.

세번째 줄도 같은 형태로 2를 4로 고치고, 네번째 줄은 2를 5로 고칩니다.

완성된 수식입니다.

=OR(AND(IFERROR(FIND($C$2,A2),0),IFERROR(FIND($D$2,A2),0)),
AND(IFERROR(FIND($C$3,A2),0),IFERROR(FIND($D$3,A2),0)),
AND(IFERROR(FIND($C$4,A2),0),IFERROR(FIND($D$4,A2),0)),
AND(IFERROR(FIND($C$5,A2),0),IFERROR(FIND($D$5,A2),0)))

수식 입력줄에서 <Enter>키를 눌러 수식 작성을 끝냅니다.

[A2] 셀 내용은 조건에 해당하는 값이 아니므로 수식 결과가 FALSE로 나타나는 것이 맞습니다.

 

앞에서 실행해 결과가 나타나 있는 F 열머리글을 선택해 모두 삭제합니다.

[F1] 셀에 커서를 두고 [데이터]-[정렬 및 필터]-[고급]을 선택해 고급 필터를 실행합니다.

 

'다른 장소에 복사'를 선택하고 앞에서 고급 필터를 실행했기 때문에 자동으로 표시되는 [목록 범위], [조건 범위] 내용은 그대로 두고 [복사 위치]만 다시 [F1] 셀을 선택해 입력 합니다.

[확인]을 누르면 결과가 표시됩니다.

현재 데이터에서 조건에 해당하는 데이터는 1개가 맞습니다.

 

 

작성한 수식이 꽤 복잡해 보이지만 실제로 작성할 때 한 줄 수식을 복사해 3번 붙여넣기한 것입니다.

 

수식 입력줄을 확장해 수식을 작성하면 복잡한 수식을 나눠서 알아보기 좋게 작성할 수 있습니다.

확장된 수식 입력줄 높이를 더 크게 하거나 작게 할 수 있고 수식 입력줄과 열 머리글 사이에 커서를 두면 마우스 커서 모양이 위/아래 화살표로 나타날 때 마우스 왼쪽 단추를 눌러 높이를 조절할 수 있습니다.

 

확장된 수식 입력줄을 원래대로 만들려면 다시 한번 단축키 <Ctrl + Shift + U>를 누르면 됩니다.

 

 

도움되길 바라며 강좌 마치겠습니다.

수고하셨습니다.