사무직의 필수품 엑셀. 쉽게 알아보는 고급필터

안녕하세요. 백발청년입니다.

 

사실 지금 저는 자고 있어요.

 

그래서 몰래 포스팅을 해요.

 

맛집 포스팅하고 자려고했는데...

자려고 했는데 잠이 갑자기 안와...

주말이 되면 설레서 잠이 안와...

주말이 아까워서 잠이 안와...

 

그러나 주말엔 항상 낮잠을 잡니다.

낮잠을 자고 나면 주말이 사라집니다.

내 주말은 어디있는건지 모르겠습니다.

 

여담은 여기까지 하고 엑셀에 대해 포스팅을 좀 해봐야겠다 라고 생각한지 꽤 오랜시간이 지났습니다.

저는 엑셀을 배운게 처음은 책보고 독학이었어요.

사무직으로 일할 때 엑셀이 그렇게 어렵더라구요.

선임자가 "함수 알죠? 함수 쓰면 쉬워요."

 

야... 말이 쉽지 너는 문과한테 기하 벡터 이런거 갑자기 들이밀면 아니?????

 

물론 엑셀에서 우리는 기하, 벡터, 이런걸 쓸 일이 잘 없습니다. 근데 있나???

 

기껏해야 대학교때 파워포인트로 이것저것 만들어본게 전부였고

나름 컴퓨터를 잘했다고 자부했는데 OA 앞에서는 컴맹수준이 되더라구요.

 

저의 친누나에게 도움을 요청하니, 집에 있는 책을 주면서 이거 보고 해봐. 라고 했던 책이

 

뭐였더라......

 

무튼, 그 책을 보고 따라해서 독학을 했고, 사무실에서 주로 쓰는 vlookup이나 hlookup, large, max, 기타 등등을 익히게 되었다죠.

사실 아직도 그 회사, 제가 근무했던 그곳에선 저는 전설로 남아 있습니다.

대다수의 단순 반복작업을 엑셀을 통화 자동화한 양식을 만들어놓았고, 그 양식은 6년이 지난 지금까지도 사용되고 있다고 합니다.

가끔 그곳에 갔을때, 아직도 그곳에 남아있는 분들은 저를 보며

 

"이사람이 누구씨 쓰고 있는 그거 만든사람이야. 모르는거 있으면 물어봐!"

라며 불편하게 만들곤 합니다.

 

네, 자랑이었어요. 저 엑셀 좀 했습니다.

 

그리고 이후로 컴활1급 자격증을 준비하면서(데이터베이스 짱시러... 액세스때매 떨어졌어...) 인강을 들었고

인강에서 특별하게 배운거라곤 VBA 이용하는 방법, 매크로를 이용한 자동화 시스템의 대략적인 개요 정도?

컴활1급에서는 VBA를 이용해 직접 코딩을 하고 어떠한 프로그램적인 무언가를 만들지는 않아요.

그래서 인강에서도 깊이 있는 교육을 진행하지 않습니다.

 

그치만 저는 사람이 본래 귀찮은걸 싫어하고

단순한 반복작업을 싫어하는 귀차니스트 이기 때문에

(그래서 백발이 되었나)

 

VBA와 매크로 등을 이용해 저의 귀찮음을 기계에게 넘겨주려고 VBA도 독학을 하게 되었습니다.

 

여담은 여기까지 하구요. 나중에 시간이 되면 VBA 코딩에 대해서도 이야기 하고자 합니다.

귀차니스트가 어떻게 귀차니즘을 기계에게 넘기는지

그 기계가 어떤 작업을 해주는지 등등..

 

 

오늘은 고급필터에 대해 알아보고자 합니다.

 

 

쉽게 글을 써야 쉽게 읽을테고

쉽게 읽어야 쉽게 배우고

쉽게 배워야 쉽게 사용하고

쉽게 사용해야 자주 사용하고

자주 사용해야 내것이 됩니다.

 

그래서 쉽게 쓰려고 합니다. 고급필터는 무엇이고 이런거 설명 필요 없습니다.

우린 실전입니다. 고급필터를 쓰는 이유는 여러 데이터 중에 내가 필요한 영역을 추출해내기 위해서입니다.

즉 일반 자동필터에서 얻을 수 없는 필터링된 데이터들, 이를테면 조건을 충족시켜주는 데이터를 얻게됩니다.

 

아 배고파... 배고파서 단어가 생각이 안나요... 그럼 더 쉽게 글을 써야겠네요.

 

자, 무조건 따라하기라고 하면 표절이니까 "쉽게 따.라.해.보.기" 로 할게요.

예제를 준비했습니다.

 

 

 

파일은 제공하지 않습니다. 귀찮아서요.

이 포스팅에 댓글이 달리고 그래도 요청하고 하면

저작권법에 위배되지 않는 저만의 예제 파일을 "만들어야하나???" 만들어서 드리도록 하겠습니다.

이건 저만의 예제파일이 아니라서...

 

고객번호부터 이름, 성별, 지역, 최종주문일, 1월 구매부터 3월까지 구매, 구매실적의 합계까지 놓여있는 데이터가 있네요.

 

우리가 자동필터를 통해 이 데이터를 필터링하면 얻을 수 있는 데이터는

1. 각 영역의 오름차순 혹은 내림차순으로 정렬한 데이터

2. 같은 항목(이를테면 성별이나 지역)이 있는 경우 그 데이터들

3. 뭐 딱히 없네요.

 

자, 여기서 고급필터와 자동필터의 차이점을 볼게요.

예를 들어 성별 기준으로 자동필터로 남자만 나오게 정렬을 하면 아래와 같은 데이터가 나옵니다.

 

 

 

 

 

 

그런데 만약, 내가 궁금한건 이사람들의 이름과 성별과 최종주문일과 구매실적의 합계뿐이다. 라고 하면

이 경우에선 다른 열을 지워야겠죠. 이를테면 고객번호와 지역과 1~3월의 구매실적 이란 열들을요.

그런데 고급필터는 딱 이름과 최종 주문일과 구매실적의 합계만 표시를 해 줄 수 있습니다.

아래의 사진을 볼게요.

 

 

성별이 남자인 사람들의 이름과 성별, 최종주문일 그리고 구매실적합계가 나타났습니다.

단순하게 자동필터로 필터링을 하는거랑 고급필터를 이용하는거랑 조금 차이가 있겠죠?

귀차니스트인 저는 위와 같은 상황에선 고급필터를 이용하겠습니다. 지우기 귀찮아서.

 

그런데 고급필터가 만능은 아닙니다. 오름차순이나 내림차순의 정렬을 못해요. 그런 조건 자체가 없습니다.

그래서 그런 경우에는 고급필터를 이용해 필터링을 한 자료를 다시 자동필터를 이용해 정렬을 해야 합니다.

이런 귀찮은 행위는 아마 저라면 매크로를 코딩해서(매크로 녹화하기도 귀찮아서) 작동시키곤 합니다.

향후에, 귀차니즘이 극에 차지 않는다면 한개씩 한개씩 포스팅을 하며

최종적으론 매크로와  VBA를 포스팅하는 그날이 오지 않을까 합니다.

 

그럼 예제를 보면서 제가 낸 문제를 한번 풀어볼게요.

예제의 데이터는 얻어온건데 문제는 제가 낸거니까 이거 저작권에 문제되는거 아니죠?

데이터가 제가 알기론 컴활1급 시험문제중에 하나인거로 알고 있는데....

문제를 풀기 위해 데이터를 다시 가져왔습니다. 행과 열은 알아야 하니까요.

 

 

자. 문제 1번

고객번호가 PR로 시작하면서 지역이 강북인 경우의 데이터를 표시하랍니다.

컴활같은 자격증 시험에서 이런 경우에는 항목을 따로 언급하지 않는 이상 그대로 출력해주면 됩니다.

 

답을 먼저 작성해보겠습니다.

 

고객번호	지역
PR*	강북

 

 

고객번호가 PR로 시작한다. 즉 PR이후에는 어떤게 와도 상관이 없기 때문에 PR*으로 표기합니다.

고급필터에서 위와 같이 무엇으로 시작하거나 어떠한 특정 단어를 지칭할 때는 해당 열을 정확하게 표시해주어야 합니다.

아래와 같이요.

 

 

고급필터를 사용할때는 데이터(혹은 표)와 닿지 않는 위치에 필터링 해야 하는 항목을 써주고 그 하단에 조건을 작성합니다.

이번 문제는 PR로 시작하면서 강북이니까 둘다 동시에 만족시켜야 하네요. 즉 AND의 조건입니다.

AND의 조건일 경우에는 동일한 행에 위치해야 합니다. 위에 사진을 보시면 24행에 PR*과 강북이 놓여져 있죠?

 

또한 고객번호는 아래와 같이 정리할 수 도 있습니다.

단, 조건에 식이 들어갈 경우 "고객번호"처럼 항목의 이름을 쓰면 작동이 안됩니다.

조건1, 조건2 처럼 다른 이름을 써주어야 작동합니다.

 

조건	지역
=left(A3,2)="PR"	강북

 

A3 셀이 첫번째 고객번호가 나오는 란이죠.

left(a3,2) 즉 a3셀의 내용의 왼쪽에서 두번째까지의 텍스트를 표기해달라.

그리고 그게 PR이냐 라는 식입니다.

위의 값은 TRUE로 표기 됩니다. 실제로 A3의 텍스트는 PR-002였으니까요.

이와 같이 조건을 걸어 놓으면 아래로 내려가며 자동적으로 확인을 합니다. 엑셀이가요.

그래서 이와 같이 고급필터에서 조건식을 작성할때는 절대참조를 쓰시면 안됩니다.

 

절대참조는...... $A$3 과 같이 앞에 보기만해도 기분좋은 달러를 붙이는건데

이 절대참조를 해놓으면 복사해서 붙여넣기를 해도 값이 변하지 않아요.

이 경우에는 고급필터가 작동을 할때도 아래로 내려가며 값을 확인하는게 아니라

A3열만 계속 보는겁니다. 그래서 이 경우에는 절대참조를 쓰시면 안됩니다.

 

고급필터는 데이터 - 정렬 및 필터에 있습니다.

첨부한 사진들은 엑셀 2007이며 엑셀2007 기준으로 설명드립니다.

참고하시길...

 

 

내가 직접 캡쳐한건데 왜 퍼온거 같은 기분이냐...

 

 

 

배고파서 지금 닭가슴살 광양불고기 맛을 먹고 있어요. 배고플때는 뭘 먹어도 맛있는듯.

(다이어트는 글렀어)

 

 

아 맛있따...

일단 배고픈데 먹으면서 합시다.

근데 맛있네...

 

고급필터를 사용하는건 간단합니다.

 

항목의 제목? 이름? 열의 이름?을 포함한 범위를 블록 지정하고 고급필터를 누르면

위와 같은 화면이 뜹니다. 조건범위는 아까 위에서 만든것 처럼 A23~B24가 되겠지요.

A23~B23은 조건의 제목인거고

A24~B24는 조건의 내용인거죠.

현재 위치에 필터하기 하면 현재위치에서 필터가 될거고

다른장소에 복사하기를 선택한후 장소를 선택해주면 해당 자료가 쫘라락 나와줍니다.

 

 

 

그럼 이제 즐거운 문제풀이를 즐겨볼까요?

 

문제 1번 성공

문제 2번

최종 주문일의 일이 15일 이후이면서 구매실적의 합계가 10만이상 30만 이하인 고객

고객의 이름과 최종주문일, 구매실적 합계 필드만 순서대로 표시하랍니다.

 

이번에도 AND 조건입니다.

먼저 15일 이후 이면서 구매실적의 합계를 "동시에" 만족시키는 고객을 찾아야겠네요.

 

조건1	구매실적합계	구매실적합계
=DAY(E3)>=15	<=300000	>=100000

 

 

여기서 한가지. 15일 이후가 15일을 포함하느냐의 여부에 따라 답이 달라질 수는 있는데 일단 포함하는거로.

day 함수가 나왔습니다. 날짜가 표기되어 있는 셀에서 "일"을 표시해주는 함수입니다.

E3 셀에 있는 날짜의 "일"을 표시하자. 그 값이 >=15 즉 15보다 크거나 같니? 라고 물어봤습니다.

+

구매실적의 합계가 30만 이하인가

+

구매실적의 합계가 10만 이하인가

라고 쓰면 편안하게 해결이 되겠네요.

 

 

다른 장소에 복사를 누르고 내가 추출하기를 원하는 열의 이름만 적어주세요.

 

 

 

 

자 위와 같이 최종주문일의 일이 15일 이후면서 구매실적의 합이 10만이상 30만 이하인 고객을 찾고

그 고객의 이름과 최종주문일과 구매실적합계값을 얻었습니다.

 

문제 3번

아 벌써 이거 포스팅하는데 1시간 40분째

문제를 3개나 해서 문제.

2개나 1개로 할까봐요.

 

 

고객의 이름의 성이 "김"씨 이거나 3월의 구매실적이 10만 이상인 고객의 이름을 찾으랍니다.

 

김씨이거나(or)10만 이상

김씨 혹은 10만 이상인 사람을 찾으면 되니

or의 조건입니다.

 

or의 조건일 때는 같은 행에 쓰지 않고

한칸 아래에 쓰는 등, 다른 행에 쓰면 해결됩니다.

 

문제 풀어볼게요.

 

이름	3월 구매실적
김*
	>=100000

 

이거나(or)이기 때문에 다른 칸에 넣어주면 해결 완료!

 

 

김씨이거나 10만 이상인 사람들의 이름이 나왔네요.

확인해볼게요. 이미라씨는 김씨는 아니지만 3월 구매실적이 135000 이네요.

강흥석씨도 김씨는 아니지만 3월에 15만2천원을 사셨네,뭘 사셨을까...

 

무튼. 이렇게 쉽고 빠르고 아무것도 아닌걸 어려워하시는 분들이 많아요.

 

이를테면 영업하시는분들이 고객 DB를 정리할때 그냥 쌓아만 놓고 관리를 안하시는 분들이 계신데

그런분들이 고객의 직종별 분류를 한다거나 하는 식으로 관리하시면 굉장히 편한데도 불구하고

잘 모르기 때문에 안하는 분들이 많습니다.

 

이번기회에 도전해보시는 것도 좋을것 같네요.

 

 

이제 자야겠어요.

갑자기 졸려요.

사실 문제2번 쓰면서 졸립기 시작했어요.

다음번부턴 2개이상의 문제를 만들지 말아야겠어요.

 

이거 푸는건 10초도 안걸리는데

캡쳐하고 올리고 쓰고 하는데 시간이 너무 오래걸리네....;;

 

그럼 안녕! 담에 또만나요~