필터가 이렇게 많은 조건에 데이터를 추출해 주긴 하지만 모든 조건을 다 해결해 주진 않습니다. 두 가지 항목의 or 조건은 해결할 수 없습니다.
"이건 또 뭔 소리야?"라고 생각하시는 분들이 있을 것 같은데 필터를 알고 있더라도 고급 필터를 사용해 본 적이 없다면 당연히 알 수 없는 얘기입니다. 어쨌든 하고 싶은 얘기는 필터로 추출할 수 없는 데이터는 고급 필터로 추출해야 한다는 얘기입니다. 고급 필터 역시 엑셀 기초 강좌에서 자세히 설명해 두었습니다. 이해해야 할 내용이 많은데 아래 6가지 강좌를 보시면 모두 이해할 수 있을 것으로 생각합니다. 꼭 확인하시고 돌아와 주세요.
문제 확인 전부터 링크를 보시고 질리진 않으셨으면 합니다. 많긴 하지만 고급 필터의 개념 정리를 위한 내용이니 꼭 보셨을 것으로 믿겠습니다.
다 보셨다면 문제가 어렵게 느껴지진 않을 것으로 생각합니다.
문제는 위와 같습니다. 고급 필터를 사용하려면 세 가지 범위가 필요합니다. 목록 범위, 조건 범위, 복사 위치였죠.
이 세 가지 범위 중 가장 문제가 되는 범위는 역시 "조건 범위"입니다. 목록 범위는 이미 만들어진 데이터 표를 활용할 것이니 크게 신경 쓸 필요는 없습니다. 복사 위치는 목록 범위에서 항목만 잘 복사해 오면 되는 것이고요. 결국 "조건 범위"를 어떻게 만드냐가 고급 필터의 핵심 내용이 됩니다. 즉 문제에서 노랑 형광펜 처리한 조건 범위를 잘 만들 수 있느냐는 것이죠.
1. 조건 범위 만들기
조건 범위를 만들기 위해 일단 목록 범위(원본 데이터 표)에서 필요한 항목부터 가져와야 합니다. 항목은 "종류"와 "판매가격(단위:원)"이라고 알려주고 있습니다.
그리고 조건 범위의 위치는 B14 셀부터 작성하라고 알려주네요.
- 목록 범위에서 조건으로 만들 항목 하나(종류)를 클릭하여 선택합니다.
- 다른 조건 항목(판매가격)은 ctrl 키를 누른 채 클릭하여 추가 선택한 후 ctrl+c를 눌러 복사합니다. ⇒ 선택한 셀이 초록 점선으로 바뀝니다.
- 떨어진 셀의 추가 선택은 ctrl+클릭(또는 클릭 드래그)하면 됩니다. - 조건 범위를 작성할 위치(B14) 셀을 클릭하여 선택하고 enter 키를 눌러 붙여넣기를 합니다. ⇒ 셀이 붙여넣기 되고 복사 대상이었던 셀의 초록 점선이 없어집니다.
- ctrl+v로 붙여넣기를 하면 복사된 대상이 남습니다. 계속해서 붙여넣기를 가능하게 하기 위함입니다. 반면 복사 후 enter로 붙여넣기를 하면 복사된 대상은 사라지고 작업이 완성됩니다. 작업이 완성되었으니 더 이상 붙여넣기를 진행할 수 없습니다. 단 한 번만 붙여넣기를 진행할 것이라면 지금처럼 복사 후 enter 키를 사용하는 것이 효율적입니다.
- 종류(B14) 밑에 촬영용이 아니다를 만들어야 합니다. "아니다"의 부호는 "<>"라 했습니다. B15셀에 "<>촬영용"으로 작성합니다.
- 이제 조건이 AND 조건인지 OR 조건인지를 판단해야 합니다. "~이면서" 또는 "~이고"는 여러 조건에 모두 만족해야 하는 것이니 AND 조건입니다. AND 조건은 이어서 옆으로 작성해야 합니다. 다음 조건인 판매가격(C14) 아래 바로 이어서 "이상"의 부호 ">="와 함께 C15 셀을 작성합니다.
- 이때 "~이면서" 또는 "~이고"가 아니라 "~이거나"라면 모두 만족하는 것이 아니라 하나만 만족하면 되는 조건이니 C15 셀이 아니라 C16 셀에 작성해야 한다는 것도 참고로 말씀드립니다.
조건 범위가 완성되었습니다.
2. 복사 위치
복사 위치는 목록 범위에서 필요한 항목만 복사하여 붙여넣기로 간단히 완성할 수 있습니다. 물론 입력하셔도 됩니다. 조건 범위를 만들 때도 역시 입력해도 되었지만, 복사 붙여넣기를 했죠. 입력이 귀찮기도 하고 오타가 나면 안 되기 때문입니다. 이때 역시 떨어진 셀의 추가 선택은 단축키 ctrl+클릭(또는 클릭 드래그)으로 하는 것이 효율적입니다.
- 모델명, 구매자는 한 번에 클릭 드래그로 범위 선택합니다.
- 판매가격(단위:원)과 판매수량은 떨어져 있으니 ctrl 키를 누른채 클릭 드래그하여 추가 선택하고 ctrl+c를 눌러 복사합니다.
- 붙여넣기를 할 셀(B18)을 클릭하여 선택하고 역시 ctrl+v가 아니라 enter로 붙여넣기를 합니다.
복사 위치도 만들었으니 이제 고급 필터를 사용할 모든 조건이 완성되었습니다.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
3. 고급 필터
- 목록 범위를 먼저 클릭 드래그하여 범위 선택합니다.
- 사실 목록 범위 역시 고급 필터를 적용한 후에 선택해도 되지만 현재 복사 위치를 만들면서 복사 위치가 범위로 선택된 상태이므로 일단 복사 위치의 범위에서 빠져나와야 합니다. 빈 셀을 클릭해야 하는 것이죠. 어차피 클릭 작업을 해야 한다면, 목록 범위를 선택하여 한 단계 작업을 건너뛰는 것이 좋습니다. - "테이터 탭 > 고급"을 클릭합니다. ⇒ 고급 필터 대화상자가 나옵니다.
- 이미 목록 범위를 선택해 두었으므로 대화상자 목록 범위에는 범위가 작성되어 있습니다. - "다른 장소에 복사"를 클릭합니다. ⇒ 복사 위치가 사용할 수 있게 활성화됩니다.
- 조건 범위 입력란을 클릭하고 커서가 깜박이는 걸 확인한 뒤 만들어 두었던 조건 범위(B14:C15)를 클릭 드래그하여 참조합니다. ⇒ 절대 참조로 값이 들어옵니다.
- 같은 방법으로 복사 위치 입력란을 클릭하고 커서 확인 뒤 만들어 두었던 복사 위치(B18:E18)를 클릭 드래그하여 참조합니다. ⇒ 절대 참조됩니다.
- 최종 확인 버튼을 클릭합니다.
결과 위 그림처럼 조건 범위에 만들었던 조건대로 복사 위치로 데이터가 필터링됩니다. 그런데 판매가격에 "#" 오류가 났습니다. 몇 번 보셨으니 왜 이런 오류가 나는지 아실 것 같습니다. 셀 값이 열의 너비보다 크게 작성된 상태이기 때문이죠.
열 머리글에서 D 열 우측 경계선을 오른쪽으로 클릭 드래그하거나 더블 클릭하여 값이 모두 표시되게 만들어 줍니다.
고급 필터가 잘 적용되고 열의 너비까지 조정하여 ITQ 엑셀 [제2작업]의 고급 필터를 완성했습니다.
문제 풀이는 끝났지만 여기서 "D~" 함수에 대해 조금 더 얘기하겠습니다. "D~" 함수의 세 번째 인수 criteria 역시 조건 범위였습니다. ITQ 엑셀 시험에서는 원본의 범위에서 선택하여 사용했었죠. 하지만 원칙적으로 criteria는 고급 필터의 조건 범위처럼 만들어야 합니다. 작성 방법과 AND, OR의 적용 방법도 완벽히 동일합니다. 그냥 같은 녀석이라 생각하시면 됩니다. 그래서 "D~" 함수에서 고급 필터의 개념을 알아야 한다고 설명했던 것이죠.
그런데 여태껏은 원본에서 일부만 범위로 선택하여 사용했습니다. 이 얘기는 "D~" 함수의 criteria 범위는 복잡하게 출제되지 않고 아주 단순하게 출제된다는 얘기입니다.
하지만 실무에서 "D~" 함수를 사용하고 다양한 조건을 만들어야 한다면, 행으로는 or 조건을, 열로는 and 조건을 사용하는 고급 필터의 조건 범위를 생각하시고 그대로 작성하시는 것이 올바른 사용법입니다.
의견글 입니다.
()
