rand(life)
[엑셀Q&A] 고급 필터 본문
어떤 분이 아무렇지도 않게 질문했습니다.
"두 가지 조건을 OR 로 주어서 필터를 걸 수 있나요?"
실제로는 이것보다 좀 더 구체적으로 질문했지만, 어쨌든 같은 의미입니다.
위와 같은 표가 있을 때, 1반에 해당하는 학생만 걸러내는 것은 쉽습니다.
위 그림을 보시면, "반"에 필터가 걸려있는 것을 볼 수 있습니다.
또, 1반이면서 "해당자"에 O 표시 된 학생은 필터를 두 번 걸어주면 되므로, 역시 쉽습니다.
위 그림을 보시면, "반"과 "해당자"두 곳에 모두 필터가 걸려있는 것을 볼 수 있습니다.
이때, "1반"이라는 조건과 "해당자"라는 조건이 AND(논리곱) 으로 연결되었다라고 보통 표현합니다.
그런데, "1반"인 학생 모두와, 1반은 아닐지라도 "해당자"에 O 표시가 되어있는 학생 모두를 보고 싶다면 어떻게 할까요?
이때, "1반"이라는 조건과 "해당자"라는 조건이 OR(논리합)으로 연결되었다라고 표현합니다.
위에서처럼 "1반"과 "해당자"에 모두 필터를 걸면, 두 조건에 모두 해당하는 학생(1반이면서 해당자)만 결과로 나올 뿐, 2반중에서 해당자인 학생, 또는 1반이면서 해당자가 아닌 학생은 결과로 나오지 않습니다.
물론, 1반을 필터를 걸어서 나온 결과를 복사하여 새로운 시트에 붙여넣기 하고, "해당자"에 필터를 걸어서 나온 결과를 복사하여 그 시트에 붙여넣기 하는 식으로 하나씩 걸러서 복사해 나가면 해결이 가능합니다만, 엑셀에서 작업할 때는 언제나 많은 데이터를 사용한다고 생각해야 합니다. 지금 주어진 예에서는 두 가지 조건뿐이어서 필터-복사-붙여넣기의 작업을 두 번만 하면 되지만, 조건이 20가지라면 이 작업을 20번 반복해야 합니다.
간단해 보이지만 까다로운 이 문제를 해결하기 위해서, "고급필터"를 이용합니다.
(엑셀 2007의 도움말에서는 "고급 조건을 사용하여 필터링"이란 항목에 나와있으니 자세히 읽어보시기 바랍니다.)
먼저, 데이터를 한 4~5열정도 아래로 내리고, 맨 윗줄에 열 레이블(데이터가 있는 맨 위 항목, 데이터의 제목, 여기서는 "반,번,해당자,전화번호")을 복사합니다.
(복사하는 이유는, 데이터의 맨 첫 행(여기서는 5행)과 조건이 들어가는 공간의 맨 첫 행(여기서는 1행)은 똑같아야 하기 때문입니다. 고급필터가 원하는 결과를 제대로 못 내는 이유 중 제일 흔한 것이 바로 이 부분에서의 실수입니다.)
가운데 비어있는 부분에 우리가 찾을 조건이 들어갈 예정이니, 조건이 많다면 더 많은 공간을 확보해야겠네요.
A2셀에 1을 입력합니다. 1반을 조건으로 필터를 건다는 의미입니다.
C3셀에 ="= o" 를 입력합니다.
(화면상에는 = o 만 표시됩니다만, 수식입력줄을 보시면 등호가 두 개 들어가 있는 것이 보입니다. 왜 C2셀이 아니라 C3셀에 입력하는지, 왜 등호를 두 번 쓰는지는 나중에 설명하겠습니다. )
데이터가 있는 부분 (여기서는 파란색 사각형으로 표시한 부분)중 아무 셀이나 클릭하고
데이터 – (정렬 및 필터) – 고급을 클릭
고급필터 창이 뜨면 다음의 범위를 알맞게 지정해 줍니다.
목록범위 (대개 자동으로 입력됩니다. 위의 그림에서는 파란색 사각형 부분이 선택됩니다. 혹시 다르게 되어있다면 위의 그림과 같이 수정해야 합니다. )
조건범위 (위의 그림에 점선으로 된 부분입니다. 이때 주의할 점은, 맨 윗줄의 항목이름까지 범위로 함께 주어야 한다는 점입니다.)
확인을 누르면, 1반 학생 전체와, 1반은 아니지만 해당자에 O 표시된 학생 모두가 출력되었습니다. (필터를 OR로 결합한 데이터가 출력되었습니다.)
그런데, "해당자" 필터 내용을 C3셀이 아니라, "1반"필터 내용의 옆에 ( C2셀) 에 입력하여 고급필터를 설정하면 어떻게 될까요?
아까와 같이 작업을 하고서, 고급필터 창이 뜨면 범위를 A1:C2로 지정해 줍니다.
아까는 "해당자" 조건을 아랫줄에 따로 주었기에 범위가 C3까지였는데, 이번에는 "반"의 조건과 같은 행에 놓기 때문에 범위를 C2까지로 해야 합니다. 만약, C3까지 범위를 주게 되면 어떻게 되는지는 실습해 보세요.
이때 나온 결과는, 1반이면서 동시에 "해당자"에 O표시 된 학생들만 나왔습니다.
이는 자동필터를 걸고 "반"과 "해당자"에 모두 필터를 건 것과 동일한 결과입니다. 즉, "반"과 "해당자"가 AND(논리곱) 으로 연결되어 걸러진 결과가 나왔습니다.
그러면 이제 여러 가지 복잡한 경우도 함께 사용할 수 있겠지요. 위와 같은 데이터에서, 1반 학생중에서는 "해당자"에 O표시 된 학생들만 보여주고, 2반은 학생 전체를 다 보여주려면 어떻게 할까요?
정답은 위와 같습니다.
여기서 우리는 고급필터에서 같은 행에 있는 조건들은 AND(논리곱)연결을, 같은 열에 있는 조건들은 OR(논리합)연결을 의미한다는 것을 알 수 있습니다.
위에서 이야기했었던, "해당자" 에 O표시가 있다는 조건을 표현하기 위해 ="= o" 라고 복잡하게 써야 했던 이유를 살펴보겠습니다.
다음은 엑셀 도움말 고급 필터 내용 중 일부분입니다.
- 수식을 나타내는 데 등호(=)가 사용되므로 셀에 텍스트나 값을 입력할 때 등호를 사용하면 입력 내용이 계산되어 예기치 않은 필터 결과가 나타날 수 있습니다. 텍스트나 값에 대한 동등 비교 연산자를 나타내려면 조건 범위의 적절한 셀에 문자열 식으로 조건을 입력하십시오.
=''=entry''
여기에서 entry는 찾을 텍스트나 값입니다. 예를 들면 다음과 같습니다.
셀 입력 내용 | 계산 및 표시 내용 |
="=김소미" | =김소미 |
="=3000000" | =3000000 |
즉, 앞의 등호는 수식을 입력하고 있음을 나타내고, 큰 따옴표는 문자열이 뒤에 나온다는 것을, 두 번째 등호는 문자열이 같다는 것을 나타낸다는 의미입니다.
따라서, 두 개의 등호 중 하나라도 빠진다면 전혀 다른 결과가 나오거나 오류가 납니다.
예를 들어, = o라고 입력하면 #NAME오류가 발생합니다. 이것은 엑셀에서 등호 뒤에는 수식이나 숫자값이 나와야 하는데 영문자 o가 나왔기 때문입니다.
"=o" 라고 입력하면 보시다시피 1반 학생들은 하나도 나오지 않습니다. 이것은 "=o"라고 썼기 때문에 첫번째 조건이 "1반 학생 중에서 해당자란에 "=O"라고 쓰여진 학생들만 골라라"는 의미가 되어버렸기 때문입니다. 하지만 해당자란에는 O를 표기하지, (따옴표 두 개와 등호가 들어간) "=o"라는 영문자는 표기하지 않았습니다. 따라서 1반학생 중 결과값으로 걸러져 나올 학생들은 아무도 없는 것입니다.
마지막으로, ="o"을 입력하면 어떻게 될까요? (그냥 o만 입력해도 마찬가지입니다.)
결과값은 이렇게 나옵니다. 제대로 나왔네요? 지금 상황에서는 ="=o" 이나 ="o"이나 o 모두 결과 값이 같습니다.
하지만 상황을 바꾸면 달라집니다.
위의 그림처럼, 1반 학생 중 일원1동에 거주하는 학생과, 거주지 상관없이 2반 학생 모두를 보여주려고 합니다. 이때 거주지조건을 ="일원1"(또는 일원1)로 주고서 고급필터를 하면,
위와 같은 결과가 나옵니다. 즉, 정확히 "일원1"을 찾는 것이 아니라, "일원1"로 시작하는 모든 셀을 찾게 됩니다.
(이런 것이 유용한 경우도 있겠네요. 일원동, 일원1동, 일원2동…. 등의 구별 없이 "일원"이라고 시작하는 동명을 모두 찾는다던지 할 때는 쓸 만하겠습니다.)
마지막으로, 와일드카드의 사용법과 함께 이 글의 맨 첫머리에서 질문하셨던 분이 질문했던 내용이 구체적으로 무엇인지, 그리고 그 답은 무엇인지 알아보겠습니다.
해당자에 O표 되어있는 사람과, O표는 없더라도 전화번호가 표시된 사람을 모두 필터링하여 보고 싶다는 것입니다. (빨간색 별표로 표시한 사람들이 모두 나와야 합니다.)
위에서 설명한대로 하면 "해당자"부분의 조건을 주기 위해서 C2셀에는 ="=O"를 입력한다는 것을 알 수 있습니다.
두 번째 조건인, "해당자"에 O표시가 안되어있더라도, 전화번호가 입력된 사람을 걸러서 보기 위해서는 무엇을 어디에 입력해야 할까요?
일단, "전화번호"항목이 있는 D열에 입력해야겠고, 두 조건이 OR(논리 합) 연결이므로 2행이 아니라 3행에 입력해야 하겠지요. 입력할 위치는 D3셀입니다.
그런데, 무엇을 입력해야 할까요? 숫자라면 >1과 같은 식으로 주어서 1보다 큰 수를 걸러서 보여주라는 방법이 가능하겠습니다만, 010-1111-1111과 같은 데이터는 숫자가 아니라 문자열입니다. 따라서 문자열을 비교하도록 해야 하겠습니다.
그리고, 위에 보시면 "없음"이라고 표시된 항목도 문자열이고, 010-1111-1111처럼 표시된 것도 문자열이기에, 단순히 문자열인 것만을 찾지 않고, 문자열 중에서도 그 둘을 구분할 수 있도록 해야겠습니다.
다시 엑셀 도움말을 보겠습니다.
"고급필터"항목 중에 "일부 문자만 같은 텍스트값을 찾는 조건"을 보겠습니다.
일부 문자만 같은 텍스트 값을 찾으려면 다음 중 하나 이상을 실행하십시오.
- 열에서 특정 문자로 시작하는 텍스트 값을 찾으려면 등호(=) 없이 해당 문자를 입력합니다. 예를 들어 조건으로 김소를 입력하면 "김소미", "김소희", "김소영" 등이 찾아집니다.
- 와일드카드 문자를 사용합니다.
다음 와일드카드 문자를 비교 조건으로 사용할 수 있습니다.
사용할 와일드카드 문자 | 찾을 텍스트 값 |
?(물음표) | 한 문자 |
*(별표) | 개수에 상관없는 문자 |
~(물결표) 뒤에 ?, *, ~ 등 사용 | 물음표, 별표, 물결표 |
아무 문자나 해당하는 것을 찾도록 도와주는 "아무개 문자" 또는 "와일드 카드"에 해당하는 물음표, 별표 등은 엑셀뿐만 아니라 아래아 한글등 다른 프로그램에서도 사용되므로 잘 알아두시면 여러 곳에서 유용하게 쓰일 것입니다.
자, 그러면, 위의 문제를 해결하기 위해 D3셀에 어떤 식을 입력해야 할까요?
와일드카드를 사용할 줄 안다면 여러 가지 방법이 가능하겠습니다만 저는 다음과 같이 했습니다.
D3셀에 입력한 수식은 다음과 같습니다.
="=*-*"
전화번호에는 구분문자인 – 문자가 꼭 들어간다는 점을 이용해서,
"-라는 문자의 앞과 뒤에 아무 문자나 오는 문자열"을 찾도록 조건을 준 것입니다.
또는 모든 전화번호는 0으로 시작하니까
="=0*"
와 같은 방법도 가능하겠네요.
[연습문제]
다음 파일을 열어, 지시대로 따라해 봅시다. 위에서 설명한 내용에, 엑셀 도움말도 참고하여야 풀 수 있습니다.
연습문제-고급필터.xlsx
